MySQL select慢查询

发表于 2020-08-10 更新于 2024-07-13 分类于樱桃沟本文字数： 12k 阅读时长 ≈ 11 分钟

近日碰到一个MySQL的cpu使用率非常高，经常到100%，但是iops这些都比较低，只有innodb的缓存读的次数也非常高。

mysql的版本是5.6.16

监控如下
cpu跑满

同时期Innodb读缓存

登录数据库看了下慢sql

| 678108 | enquiry   | 10.AA.BB.CC:36958 | enquiry   | Query   |   12 | Sending data                         | select id as id1_6_, createDateTime as createDa2_6_, fromUserId as fromUser3_6_, serviceSessionId as serviceS4_6_, tenantId as tenantId5_6_, toUserId as toUserId6_6_ from invite_enquiry  where tenantId=8888 and (SessionId in ('UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID'))

但是这里的条件tenantid和sessionid我记得都是有索引的啊，为什么会慢呢？难道是没有加上？

查了一下确实没有问题

mysql> show index from invite_enquiry;
+----------------+------------+-------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name                      | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+-------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| invite_enquiry |          0 | PRIMARY                       |            1 | id               | A         |  xxxxxxxxxx |     NULL | NULL   |      | BTREE      |         |               |
| invite_enquiry |          0 | UK_SESSIONID                  |            1 | SessionId        | A         |  xxxxxxxxxx |     NULL | NULL   |      | BTREE      |         |               |
| invite_enquiry |          1 | IDX_TENANTID_SESSIONID        |            1 | tenantId         | A         |     yyyyyyy |     NULL | NULL   | YES  | BTREE      |         |               |
| invite_enquiry |          1 | IDX_TENANTID_SESSIONID        |            2 | SessionId        | A         |  xxxxxxxxxx |     NULL | NULL   |      | BTREE      |         |               |
+----------------+------------+-------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.06 sec)

那我们explain这个查询看下

mysql> explain select id as id1_6_, createDateTime as createDa2_6_, fromUserId as fromUser3_6_, serviceSessionId as serviceS4_6_, tenantId as tenantId5_6_, toUserId as toUserId6_6_ from invite_enquiry where tenantId=8888 and (serviceSessionId in ('UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID'));
+----+-------------+--------------+------+---------------------------------------------------+-------------------------------+---------+-------+------+-----------------------+
| id | select_type | table        | type | possible_keys                                     | key                           | key_len | ref   | rows | Extra                 |
+----+-------------+--------------+------+---------------------------------------------------+-------------------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      |              | ref  | UK_SESSIONID,IDX_TENANTID_SESSIONID               | IDX_TENANTID_SESSIONID        | 5       | const |  169 | Using index condition |
+----+-------------+--------------+------+---------------------------------------------------+-------------------------------+---------+-------+------+-----------------------+
1 row in set (0.01 sec)

这就比较奇怪了，我这个索引的长度可不是5位啊，tenantid是int类型，sessionid是32位的。这个key的长度明显就只包含了tenantid的部分。而没有sessionid什么事情啊。

但是我要把in条件里的条件从100个改成34个，他居然走另外一个索引了。

mysql> explain select id as id1_6_, createDateTime as createDa2_6_, fromUserId as fromUser3_6_, serviceSessionId as serviceS4_6_, tenantId as tenantId5_6_, toUserId as toUserId6_6_ from invite_enquiry where tenantId=8888 and (serviceSessionId in ('UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID' , 'UUID'));
+----+-------------+--------------+-------+---------------------------------------------------+---------------------+---------+------+------+------------------------------------+
| id | select_type | table        | type  | possible_keys                                     | key                 | key_len | ref  | rows | Extra                              |
+----+-------------+--------------+-------+---------------------------------------------------+---------------------+---------+------+------+------------------------------------+
|  1 | SIMPLE      |              | range | UK_SESSIONID,IDX_TENANTID_SESSIONID               | UK_SESSIONID        | 144     | NULL |   34 | Using index condition; Using where |
+----+-------------+--------------+-------+---------------------------------------------------+---------------------+---------+------+------+------------------------------------+
1 row in set (0.00 sec)

而这2个查询不同点就是使用了不同的索引，然后type一个是range，另外一个是ref

ref: 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询.
range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中. 当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个.

从实际查询来看，key_len是决定了查询的效率。一个是2.7秒，一个是0.27秒。相差了10倍的差距了。

但是其实mysql用那个索引只扫描了169行而已，再从169行里选出100行。那为什么会有那么大的差距呢？

这里可能的问题应该是索引里有个唯一索引导致的。而且正常情况下ref的效率要比range要高。但是为什么这个索引的长度只有5呢，刚好覆盖tenantid。

这个明天再做个测试看看吧。

比较特殊的是这里的UK_SESSIONID这是一个唯一索引。

为了验证一下这个问题，那我把唯一索引删除，重新建一个普通索引。

1 2	alter table invite_enquiry drop index UK_SESSIONID; alter table invite_enquiry add index UK_SESSIONID(SessionId);

出来的结果是：

+----+-------------+--------------+-------+---------------------------------------------------+-------------------------------+---------+------+------+-----------------------+
| id | select_type | table        | type  | possible_keys                                     | key                           | key_len | ref  | rows | Extra                 |
+----+-------------+--------------+-------+---------------------------------------------------+-------------------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | inviteenqu0_ | range | IDX_TENANTID_SESSIONID,UK_SESSIONID               | IDX_TENANTID_SESSIONID        | 149     | NULL |   91 | Using index condition |
+----+-------------+--------------+-------+---------------------------------------------------+-------------------------------+---------+------+------+-----------------------+

这下key_len居然又对了，这次查询就没有问题了。

那我如果都是唯一索引是不是还是会有问题，如果是那样的话就是不是可以证明给多列建索引的最左的那个索引是不是应该是散列层度最高的那个。

mysql> alter table invite_enquiry drop index UK_SESSIONID;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table invite_enquiry drop index IDX_TENANTID_SESSIONID;
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `invite_enquiry` ADD UNIQUE (`SessionId`);
Query OK, 0 rows affected (33 min 29.83 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> ALTER TABLE `invite_enquiry` ADD UNIQUE (`tenantId`,`SessionId`);
Query OK, 0 rows affected (38 min 15.60 sec)
Records: 0  Duplicates: 0  Warnings: 0


+----+-------------+--------------+------+---------------------------+----------+---------+-------+------+-----------------------+
| id | select_type | table        | type | possible_keys             | key      | key_len | ref   | rows | Extra                 |
+----+-------------+--------------+------+---------------------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | inviteenqu0_ | ref  | SessionId,tenantId        | tenantId | 5       | const |  114 | Using index condition |
+----+-------------+--------------+------+---------------------------+----------+---------+-------+------+-----------------------+

哈哈，果然如我们想象的那样。

看来以后碰到有唯一索引的表一定要注意了，一个是要注意散列层度的，然后对于唯一性是不是由数据库来保证，这个单独来讨论了。毕竟从研发角度来看是自己不干最好了。

剩下待解的问题是innodb buffer读的是什么东西？