一个线上MySQL表查询引发的报警

AsiaYe

发布于 2020-03-26 13:48:52

9000

发布于 2020-03-26 13:48:52

文章被收录于专栏：DBA随笔

一个线上MySQL表查询引发的报警

今天遇见了一个线上的MySQL问题，问题的内容是某个阿里云ECS频繁报警，报警的内容是：CPU使用率超过阈值。下面是具体的Grafana报警中负载、CPU和磁盘使用率的图像：

这种问题，之前也遇到过一些，都是因为有些大的select操作导致的，之前就遇到过一个，有兴趣可以看看当时的文章：

《CPU、负载、磁盘同时飙升的问题分析》

当时我的第一反应就是慢查询，看了下慢查询日志，果然，发现的慢日志SQL如下：

mysql  14:32:25>>explain select * from w_log where flag=0 or flag is null and server=21000 order by id;
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | w_log       | NULL       | index | NULL          | PRIMARY | 8       | NULL | 5651819 |    10.90 | Using where |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

查看一下表结构，表结构中只有id列是主键，而flag列和server列都没有二级索引，也没有其他的二级索引列。也就是说，这个表只有一个主键id。表的数据量有500w，咨询了一下业务方，他们会每3分钟，在这个表上运行一遍上面的SQL查询数据。

好了，现在问题描述基本上清楚了：

1、CPU报警

2、慢查询导致的报警

3、表数据量500w，只有一个id主键，没有其他索引

4、where条件中flag字段有is null的判断逻辑，还有sever字段的判断逻辑

5、表查询走的是主键上的全表扫，然后过滤出来了部分条件。（这里对type=index做下简单说明，它是指当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index，此案例中，我们需要扫描所有的聚集索引）

那么现在的解决方案就是对这个SQL做优化了，怎么优化？

首先，得确定server和flag能否添加索引。

经过和业务方沟通，500w数据中，server有可能出现的值有75种情况，而flag中的值只有可能是3中情况，分别是null，0，或者1，之所有flag有可能是null，是因为建表的过程中该字段是flag int(11) default null的属性，经过沟通，和业务方确认这个默认值能够该为0，这样一来，就可以在查询语句中将null值的条件给去掉了。所以，接下来的步骤是:

清理flag字段中的null值。

这里，为了测试null值直接改为default 0之后，原来的记录，会不会被修改，我首先做了一个小的测试:

mysql 17:07:56>>create table test_flag (id int,flag int default null);
Query OK, 0 rows affected (0.00 sec)

mysql 17:08:21>>insert into test_flag (id) values (1),(2),(3);  
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql  17:08:26>>insert into test_flag (id,flag) values (1,4);          
Query OK, 1 row affected (0.01 sec)

mysql 17:08:44>>select * from test_flag;
+------+------+
| id   | flag |
+------+------+
|    1 | NULL |
|    2 | NULL |
|    3 | NULL |
|    1 |    4 |
+------+------+
4 rows in set (0.01 sec)

mysql 17:08:54>>alter table test_flag modify flag int default 0;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 17:09:03>>select * from test_flag;                        
+------+------+
| id   | flag |
+------+------+
|    1 | NULL |
|    2 | NULL |
|    3 | NULL |
|    1 |    4 |
+------+------+
4 rows in set (0.00 sec)

经过上面的测试，首先确认，null值是不能通过设置字段的default 属性来修改的。所以在我们修改完字段属性的时候，需要手动将这些flag字段为null值的记录给update成0。这样才能保证该表中的flag字段不会有null值了。（注意，线上的表，尽量使用pt工具进行表结构变更：《MySQL大表删除工具pt-osc》）

修复完null值之后，现在flag中只有0和1两个可能了。问题似乎变的简单了起来。因为考虑到server字段的值的差异性比较多，于是我在server字段上创建了一个二级索引。

执行完成之后，监控图变成了下面的样子：

从图中可以看到，虽然CPU使用率的问题得到了解决，但是磁盘util和负载都飙升了。查看了一下执行计划：

mysql  16:11:06>>explain select * from w_log  where flag=0 and  server=65000 order by id ;        
 +----+-------------+-------------+------------+------+---------------+------------+---------+-------+-------+----------+------------------------------------+
| id | select_type | table       | partitions | type | possible_keys | key        | key_len | ref   | rows  | filtered | Extra                              |
+----+-------------+-------------+------------+------+---------------+------------+---------+-------+-------+----------+------------------------------------+
|  1 | SIMPLE      | w_log       | NULL       | ref  | idx_server    | idx_server | 5       | const | 34204 |    10.00 | Using index condition; Using where |
+----+-------------+-------------+------------+------+---------------+------------+---------+-------+-------+----------+------------------------------------+

执行计划显示，过滤的条数变少了，过滤了34204行，貌似优化了100倍，但是实际效果是磁盘打满了，负载上升了。使用show processlist查看数据库的链接，发现连接都是sending data状态，都在跑这条SQL，只不过server的值略有差异。显然，这不是解决问题的最好办法。

当时由于负载爬升较快，也来不及分析为什么，就跟同事讨论了一下，在server和flag上建立了一个联合索引。

然后看了下目前的负载情况，情况如下：

可以看到，负载和CPU使用率都有了一个明显的下降。此时我们再来查看执行计划：

mysql  17:56:30>>explain select * from w_log  where flag=0 and server=8000 order by id;
+----+-------------+-------------+------------+------+----------------------------+-----------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table       | partitions | type | possible_keys              | key             | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+-------------+------------+------+----------------------------+-----------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | w_log       | NULL       | ref  | idx_server,idx_server_flag | idx_server_flag | 10      | const,const |    1 |   100.00 | Using index condition |
+----+-------------+-------------+------------+------+----------------------------+-----------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

可以看到，过滤的行数已经大大降低了，而且查找方法都变成const了，查询的速度也变快了，过滤的行数变为1.也就是说只有一条记录满足这个条件。使用show processlist查看当前数据库的连接，发现阻塞的那些select操作都已经没有了。

冷静下来分析一波原因：

1、当我们设置server为二级索引的时候，过滤行数大大减少，但是由于server所在的B+树上并没有flag字段，所以查出来的server这3w行记录都需要回表来查看对应的flag字段。

2、由于select后面跟的是*,也就是所有的记录，所以这3w行记录都需要从聚集索引上获取其他字段的值，也就是说聚集索引上有3w条记录会被扫描到。而且还要根据id排序，当然，聚集索引本身是根据id排序的，所以排序方面不会消耗什么资源。

3、当我们设置server和flag为二级索引的时候，由于满足条件的rows只有1，而且二级索引上有server和flag两个字段，所以只需要扫描二级索引上的1条记录就能够得到目标记录，然后再回表一次，查询所有的其他字段，这样效率当然就提高了。

最后，冷静下来分析，可能思路比较清晰，但是当时这个负载激增的时候，确实是没有想到，只是单纯的认为flag字段的差异性较小，就不需要包含在索引里面，并没有仔细分析flag字段的情况，其实他里面大部分是1，只有很少一部分是0，而恰好搜索条件是flag=0，这样，创建server和flag的索引是比较合适的。

不得不说，纸上得来终觉浅，绝知此事要躬行啊~今天的内容就到这里吧，回家了。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2020-03-20，如有侵权请联系 cloudcommunity@tencent.com 删除

sql