一次mysql慢查询事故分析_MySQL

年前项目组接微信公众号。上线之后，跟微信相关的用cid列的查询会话的SQL变慢了几十倍！思考这个问题思考了很久，从出现以来一直是我心头的一个结。cid这一列是建了索引的，普通的cid列更新都没问题，为何只有微信的有问题？相同的前缀又是如何影响索引的？
分析过程 1.explain下微信cid的查询，微信的cid会以mid-qqwanggou001为前缀插入数据

explain

select *

from analysis_sessions

where cid = "mid-qqwanggou001-b99359d9054171901c0"

分析结果如下：

从explain分析可以看出，这个查询使用了索引，但是innodb认为有165万行数据需要给mysql服务器筛选（也就是用where条件过滤）。如果这些庞大的数据在内存，遍历一遍花不了多少时间。但是极有可能，这些数据是在磁盘上的。这么多的数据从磁盘读取然后载入内存，大量磁盘IO必然是十分的耗时的。

2.分析普通cid的查询

取数据进行explain，cid = "sid-a2f9047ddf528d837e5f60843c83aae9"。这个数据是不带公共前缀的。

explain

select *

from analysis_sessions

where cid = "sid-a2f9047ddf528d837e5f60843c83aae9"

 

分析结果如下：

相同的列，相同的索引，这次存储引擎向mysql服务器仅仅返回了一行数据。也就是说innodb仅仅需要读取一个二级索引的叶子节点。相对于上面那个sql的IO，压力显然小很多。

初步分析结论：带有长前缀的cid查询，innodb存储引擎会向mysql上端服务器返回百万级别的数据。这只是现象，我还是想问，相同的表，相同的列，相同的索引结构（B+树索引），相同的查询，仅仅不同的数据，结果为何有差么大的差别？

近一步分析

纠结这个问题很久了，直到前天晚上散步时候，无意的会想到了 explain结果的key_len这一列。这一列我从来不看，觉得没用，但是27与cid这一列50个varchar的定义格格不入。27明显小于50，首先可以肯定，这个索引用的是前缀索引，说白了，截取了字符串的前面一部分作为索引数据。analysis_session表用的gbk编码，也就是说，索引需要2个字节表示一个varchar。解释一下key_len

27 = 2 * 12 + 2 + 1

27位的索引，仅仅索引了前面12个字符。中间的2存储长度，后面的一个字节存储Null信息，因为这一列是允许Null的。

最终结论：问题到这已经很明了了，微信cid的前缀是17个字符的，大于前缀索引的12个字符，也就是说，所有存储微信cid数据（百万级别）B+树叶子节点将只有一个B+树非叶节点的指针指向这里。于是，当你查微信cid相关的数据时，所有微信cid将被返回给mysql服务器进行where过滤了，效率上讲，这是很恐怖的。索引确实还是被用上了，不然会造成全表扫描。但是这个数据设计的有问题，B+树的查找效率是O(LogN)的，但是遇上这个数据，立刻变成O(N),相当于一个局部全表扫描。

那么合理的推测，只要有新增的微信cid，微信cid的查询只会变的更慢！

引申，更佳的代码 practice：

varchar，blob, text等边长数据建索引的时候，数据库会自动建前缀索引，于是B+树不会索引整个字段的部分。很多同学喜欢用前缀作为字符串的标志，这次要注意了，有前车之鉴了。前缀存入mysql之后会降低检索效率，前缀越长，B+树查询的效率越低。

这里给出代码的建议：

1.将前缀作为后缀，startWith改为endWith

2.不要尝试后缀模糊搜索，like "%.com",这种做法更糟糕，完全用不了索引，于是全表扫描。