By 兰春

Updated:

Contents
  1. 1. 环境
  2. 2. DB症状
  3. 3. 定位问题
  4. 4. 分析问题
  5. 5. 总结
  6. 6. 展望

今天遇到一个非常神奇的sql执行计划时好时坏，我们一起来领略一下吧

废话不多说，直接进入实战

环境

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
* version：MySQL5.6.27 社区版
* 表结构
CREATE TABLE `xx` (
  `TagId` int(11) NOT NULL AUTO_INCREMENT COMMENT '',
  `TagType` int(11) DEFAULT NULL COMMENT '',
  `SubType` int(11) DEFAULT NULL COMMENT '',
  `CommId` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `TagFlag` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `TagName` varchar(255) DEFAULT NULL COMMENT '',
  `OrderId` int(11) DEFAULT '0' COMMENT '',
  `Unum` int(10) NOT NULL DEFAULT '0' COMMENT '',
  `IsBest` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `BrokerId` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `AddDate` int(11) DEFAULT NULL COMMENT '',
  `UpdateDate` int(11) DEFAULT NULL COMMENT '',
  `updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `tmpnum` int(10) DEFAULT '0' COMMENT '',
  `cityid` int(11) DEFAULT '0' COMMENT '',
  PRIMARY KEY (`TagId`),
  KEY `idx_4` (`IsBest`,`TagFlag`,`CommId`),
  KEY `idxnew` (`UpdateDate`),
  KEY `idx_lc_1` (`TagName`,`TagType`,`TagId`),
  KEY `idx_lc_2` (`CommId`,`TagName`,`TagType`),
  KEY `idx_tagName_brokerId_cityId` (`TagName`,`BrokerId`,`cityid`),
  KEY `idx_lc_3` (`SubType`,`TagType`,`cityid`),
) ENGINE=InnoDB AUTO_INCREMENT=20628140 DEFAULT CHARSET=utf8

DB症状

1
2
3
4
5
6
1. slow query 非常多
2. thread_running 非常多
3. cpu 90%
4. too many connection
多症齐发

定位问题

1
2
3
4
5
6
7
8
9
很明显就是去寻找slow query，毕竟slow是我衡量DB性能重要标准。
然后发现99%都是类似这样的语句：
# Time: 170304 10:32:07
# User@Host[] @  []  Id: 26019853
# Query_time: 0.251174  Lock_time: 0.000078 Rows_sent: 1  Rows_examined: 470135
SET timestamp=1488594727;
select `TagId`,`TagType`,`SubType`,`CommId`,`TagFlag`,`TagName`,`OrderId`,`Unum`,`IsBest`,`BrokerId`,`AddDate`,`UpdateDate`,`updatetime`,`tmpnum`,`cityid` from `xx` where `TagType` = '1'  and `TagName` = '**高'  order by `TagId` ASC limit 1 ;

分析问题

  • step1：查看执行计划
1
2
3
4
5
6
7
8
9
10
11
12
13
explain select `TagId`,`TagType`,`SubType`,`CommId`,`TagFlag`,`TagName`,`OrderId`,`Unum`,`IsBest`,`BrokerId`,`AddDate`,`UpdateDate`,`updatetime`,`tmpnum`,`cityid` from `xx` where `TagType` = '1'  and `TagName` ='**高'  order by `TagId`  limit 1;
+----+-------------+-----------------+-------+--------------------------------------+---------+---------+------+------+-------------+
| id | select_type | table           | type  | possible_keys                        | key     | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+-------+--------------------------------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | xx | index | idx_lc_1,idx_tagName_brokerId_cityId | PRIMARY | 4       | NULL |  175 | Using where |
+----+-------------+-----------------+-------+--------------------------------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
这条语句执行时间是： 0.99s
奇怪，从表结构上看，应该会使用idx_lc_1才对，为什么执行计划是错的呢？
  • step2：第二反应
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
会不会是TagType是int类型，但是sql语句确实字符串呢？隐士类型转换的导致的执行计划出错之前也是碰到过的。
试试吧，
explain select `TagId`,`TagType`,`SubType`,`CommId`,`TagFlag`,`TagName`,`OrderId`,`Unum`,`IsBest`,`BrokerId`,`AddDate`,`UpdateDate`,`updatetime`,`tmpnum`,`cityid` from `xx` where `TagType` = 1  and `TagName` ='**高'  order by `TagId`  limit 1;
+----+-------------+-----------------+-------+--------------------------------------+---------+---------+------+------+-------------+
| id | select_type | table           | type  | possible_keys                        | key     | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+-------+--------------------------------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | xx | index | idx_lc_1,idx_tagName_brokerId_cityId | PRIMARY | 4       | NULL |  175 | Using where |
+----+-------------+-----------------+-------+--------------------------------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
这条语句执行时间是： 0.89s
还是非常缓慢，看来不是这个原因。
  • step3：会不会是数据的问题呢？
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
因为从slow的分布看，基本上都是`TagName` ='**高' 的slow，其他的值也没发现，所以开始怀疑value，调整下看看呢
explain select `TagId`,`TagType`,`SubType`,`CommId`,`TagFlag`,`TagName`,`OrderId`,`Unum`,`IsBest`,`BrokerId`,`AddDate`,`UpdateDate`,`updatetime`,`tmpnum`,`cityid` from `xx` where `TagType` = 1  and `TagName` ='%%高'  order by `TagId`  limit 1;
+----+-------------+-----------------+------+--------------------------------------+----------+---------+-------------+------+------------------------------------+
| id | select_type | table           | type | possible_keys                        | key      | key_len | ref         | rows | Extra                              |
+----+-------------+-----------------+------+--------------------------------------+----------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | xx | ref  | idx_lc_1,idx_tagName_brokerId_cityId | idx_lc_1 | 773     | const,const |    3 | Using index condition; Using where |
+----+-------------+-----------------+------+--------------------------------------+----------+---------+-------------+------+------------------------------------+
这条语句执行时间：0.00s
哇塞，0s就解决战斗，但是这又是为什么呢？
再试一下：将‘**’高，换成‘*高’
explain select `TagId`,`TagType`,`SubType`,`CommId`,`TagFlag`,`TagName`,`OrderId`,`Unum`,`IsBest`,`BrokerId`,`AddDate`,`UpdateDate`,`updatetime`,`tmpnum`,`cityid` from `xx` where `TagType` = 1  and `TagName` ='*高'  order by `TagId`  limit 1;
+----+-------------+-----------------+------+--------------------------------------+----------+---------+-------------+------+------------------------------------+
| id | select_type | table           | type | possible_keys                        | key      | key_len | ref         | rows | Extra                              |
+----+-------------+-----------------+------+--------------------------------------+----------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | xx | ref  | idx_lc_1,idx_tagName_brokerId_cityId | idx_lc_1 | 773     | const,const |    3 | Using index condition; Using where |
+----+-------------+-----------------+------+--------------------------------------+----------+---------+-------------+------+------------------------------------+
执行计划也正确，执行时间也非常快。
然后笃定的认为问题找到了，竟然是 ‘**’导致的。
当我自己给自己sleep 10s 之后，开始思考，这是为什么呢？ 等值匹配跟*有关系吗？
  • step4: 再次调整语句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
* 去掉limit呢？ 因为limit是执行计划的杀手，这个我想大部分DBA知道的吧。。。
explain select `TagId`,`TagType`,`SubType`,`CommId`,`TagFlag`,`TagName`,`OrderId`,`Unum`,`IsBest`,`BrokerId`,`AddDate`,`UpdateDate`,`updatetime`,`tmpnum`,`cityid` from `xx` where `TagType` = 1  and `TagName` ='*高'  order by `TagId`  ;
+----+-------------+-----------------+------+--------------------------------------+-----------------------------+---------+-------+-------+----------------------------------
------------------+
| id | select_type | table           | type | possible_keys                        | key                         | key_len | ref   | rows  | Extra
                  |
+----+-------------+-----------------+------+--------------------------------------+-----------------------------+---------+-------+-------+----------------------------------
------------------+
|  1 | SIMPLE      | xx | ref  | idx_lc_1,idx_tagName_brokerId_cityId | idx_tagName_brokerId_cityId | 768     | const | 13854 | Using index condition; Using wher
e; Using filesort |
+----+-------------+-----------------+------+--------------------------------------+-----------------------------+---------+-------+-------+----------------------------------
------------------+
惊奇的发现，执行计划再次发生了改变。。。。
idx_tagName_brokerId_cityId 为什么又冒出来了呢？
那我们再回头看看表结构：
PRIMARY KEY (`TagId`),
  KEY `idx_4` (`IsBest`,`TagFlag`,`CommId`),
  KEY `idxnew` (`UpdateDate`),
  KEY `idx_lc_1` (`TagName`,`TagType`,`TagId`),
  KEY `idx_lc_2` (`CommId`,`TagName`,`TagType`),
  KEY `idx_tagName_brokerId_cityId` (`TagName`,`BrokerId`,`cityid`),
  KEY `idx_lc_3` (`SubType`,`TagType`,`cityid`)
去掉干扰项后：
PRIMARY KEY (`TagId`),
`idx_lc_1` (`TagName`,`TagType`,`TagId`),
`idx_tagName_brokerId_cityId` (`TagName`,`BrokerId`,`cityid`),
执行计划竟然没有选择idx_lc_1，而是idx_tagName_brokerId_cityId，那么这个肯定是干扰索引。
所以，就更加清晰的定位到idx_tagName_brokerId_cityId索引的问题，然后开始调整这个索引，主要是第一个字段TagName的干扰，选择性的问题。
将：  KEY `idx_tagName_brokerId_cityId` (`TagName`,`BrokerId`,`cityid`) =>  KEY `idx_tagName_brokerId_cityId` (`BrokerId`,`TagName`,`cityid`)
  • step 5: 再次观察执行计划
1
2
3
4
5
6
7
8
9
10
explain select `TagId`,`TagType`,`SubType`,`CommId`,`TagFlag`,`TagName`,`OrderId`,`Unum`,`IsBest`,`BrokerId`,`AddDate`,`UpdateDate`,`updatetime`,`tmpnum`,`cityid` from `xx` where `TagType` = 1  and `TagName` ='**高'  order by `TagId`  limit 1;
+----+-------------+-----------------+------+--------------------------------------+----------+---------+-------------+------+------------------------------------+
| id | select_type | table           | type | possible_keys                        | key      | key_len | ref         | rows | Extra                              |
+----+-------------+-----------------+------+--------------------------------------+----------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | xx | ref  | idx_lc_1,idx_tagName_brokerId_cityId | idx_lc_1 | 773     | const,const |    3 | Using index condition; Using where |
+----+-------------+-----------------+------+--------------------------------------+----------+---------+-------------+------+------------------------------------+
sql执行时间：0.00s

总结

  • 至此，问题已经解决，第一个前缀索引是如此的重要。

  • 索引调优是门艺术

展望

  • 以后如何调整和优化类似的索引执行计划呢？
1
原则： 高索引基数的filed，必须放前面。
  • 希望MySQL的优化器以后越来越强大
Contents
  1. 1. 环境
  2. 2. DB症状
  3. 3. 定位问题
  4. 4. 分析问题
  5. 5. 总结
  6. 6. 展望

幸福,不在于得到的多

而在于计较的少