数据库生产问题排查

逃离我推掉我的手 2023-01-11 03:51 156阅读 0赞

### 故障描述 ###

在7月24日11点线上某数据库突然收到大量告警，慢查询数超标，并且引发了连接数暴增，导致数据库响应缓慢，影响业务。看图表慢查询在高峰达到了每分钟14w次，在平时正常情况下慢查询数仅在两位数以下

赶紧查看慢SQL记录，发现都是同一类语句导致的慢查询(隐私数据例如表名，我已经隐去)：

select
      *
    from
      sample_table
    where
        1 = 1
        and (city_id = 565)
        and (type = 13)
    order by
      id desc
    limit
      0, 1

看起来语句很简单，没什么特别的。但是每个执行的查询时间达到了惊人的44s。

简直耸人听闻，这已经不是“慢”能形容的了…

接下来查看表数据信息，如下图：

![20210125220721851.png][]

可以看到表数据量较大，预估行数在83683240，也就是8000w左右，**千万数据量的表**

### 问题原因排查 ###

首先当然要怀疑会不会该语句没走索引，查看建表DML中的索引：

KEY `idx_1` (`city_id`,`type`,`rank`),
    KEY `idx_log_dt_city_id_rank` (`log_dt`,`city_id`,`rank`),
    KEY `idx_city_id_type` (`city_id`,`type`)

**可以看到是有idx\_city\_id\_type和idx\_1索引的，**既然有索引，接下来就该看该语句实际有没有走到索引了，MySQL提供了Explain可以分析SQL语句。Explain 用来分析 SELECT 查询语句。

Explain比较重要的字段有：

*  select\_type : 查询类型，有简单查询、联合查询、子查询等
 *  key : 使用的索引
 *  rows : 预计需要扫描的行数

## 使用Explain分析 ##

select * from sample_table where city_id = 565 and type = 13 order by id desc limit 0,1

![2021012522125098.png][]

可以看出，虽然possiblekey有我们的索引，但是最后走了主键索引。而表是千万级别，**并且该查询条件最后实际是返回的空数据**，也就是MySQL在主键索引上实际检索时间很长，导致了慢查询。

我们可以使用force index(idx\_city\_id\_type)让该语句选择我们设置的联合索引

这次明显执行的飞快，分析语句：

select * from sample_table force index(idx_city_id_type)  where ( ( (1 = 1) and (city_id = 565) ) and (type = 13) ) order by id desc limit 0, 1

实际执行时间0.00175714s，走了联合索引后，不再是慢查询了。

问题找到了，总结下来就是：**MySQL优化器认为在limit 1的情况下，走主键索引能够更快的找到那一条数据，并且如果走联合索引需要扫描索引后进行排序，而主键索引天生有序，所以优化器综合考虑，走了主键索引。实际上，MySQL遍历了8000w条数据也没找到那个天选之人（符合条件的数据），所以浪费了很多时间。**

## MySQL索引选择原理 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70][]

首先要知道，选择索引是MySQL优化器的工作。

而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。

**当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70 1][]

走了**主键索引**的查询语句，rows预估行数1833，而强制走**联合索引**行数是45640，并且Extra信息中，显示需要Using filesort进行额外的排序。所以在不加强制索引的情况下，**优化器选择了主键索引，因为它觉得主键索引扫描行数少，而且不需要额外的排序操作，主键索引天生有序。**

*  索引要考虑 order by 的字段

为什么这么说？因为如果我这个表中的索引是`city_id`,`type`和`id`的联合索引，那优化器就会走这个联合索引，因为索引已经做好了排序。

*  更改limit大小能解决问题？

把limit数量调大会影响预估行数rows，进而影响优化器索引的选择吗？

答案是会。

我们执行limit 10

select * from sample_table where city_id = 565 and type = 13 order by id desc limit 0,10

![format_png][]

## 为何突然出现异常慢查询 ##

问：这个查询语句已经在线上稳定运行了非常长的时间，为何这次突然出现了慢查询？

答：以前的语句查询条件返回结果都不为空，limit1很快就能找到那条数据，返回结果。而这次代码中查询条件实际结果为空，导致了扫描了全部的主键索引

## 解决方式 ##

*  强制选择索引：force index

select * from sample_table force index(idx_city_id_type)  where ( ( (1 = 1) and (city_id = 565) ) and (type = 13) ) order by id desc limit 0, 1

*  干涉优化器选择：增大limit

通过增大limit，我们可以让预估扫描行数快速增加，比如改成下面的limit 0, 1000

SELECT * FROM sample_table where city_id = 565 and type = 13 order by id desc LIMIT 0,1000

*  干涉优化器选择：增加包含order by id字段的联合索引

我们可以新建`city_id`,`type`和`id`的联合索引，来解决这个问题。

这样也有一定的弊端，比如我这个表到了8000w数据，建立索引非常耗时，而且通常索引就有3.4个g，如果无限制的用索引解决问题，可能会带来新的问题。表中的索引不宜过多

[20210125220721851.png]: /images/20221119/e3a034ea9c844156b1d99fef2cc21781.png
[2021012522125098.png]: /images/20221119/dae67da0c48b4f7d9f9521ea9f309e74.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210125222034184.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210125222553406.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4,size_16,color_FFFFFF,t_70
[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWdrci5jbi1iai51ZmlsZW9zLmNvbS80N2M5ODliZC1hZWM0LTRmNjMtYThjNS1hNjYxYzM5NmEwM2UucG5n?x-oss-process=image/format,png