记一次（失败的）优化慢sql的过程

背景

新leader要求每人每周找个系统优化点并解决，于是周一早上在kibana通过exception关键字搜索，找到了一条sql超时的记录。这个超时之前在报警群里看到过很多次了，但一直没人管。别的报错和优化不一定搞得来，我想着慢sql大概就是加索引吧，搞起！

原sql涉及表的连接查询，没走索引的部分提炼一下，大概长这样：

where a = const, b = const
group by c,d
order by c

数据类型

a：tinyint not null
b、c、d：bigint not null

取值范围

a [1,2,3]
b [1,2,3,4]
c [十几个值]
d [几十个值]

全表22w数据，其中(a=1,b=1)和(a=3,b=1)时数据占比最大，分别有1w多

怎么看是否使用索引查询

一个例子

在网上查文档，得到信息：
group by 使用的索引是连接在 where之后的，比如这种情况建立索引(a,b,c,d)，能走全。
但是！我的小伙伴lsq同志告诉我，建立索引(a,b,c,d)后，group by走不了索引，并给我一个例子：
有索引(a,b,c)，其中：

a：tinyint not null 1 byte
b：bigint not null 8 byte
c：tinyint not null 1 byte

select a,b,c,d
where a
group by b,c

没走全索引的explain

explain后结果如图

几个重要的字段^[1]：
possible_keys：建议使用的索引，mysql可能给出错误建议。
key：实际使用的索引。
key_len：实际使用索引的大小，需要计算^[2]。该例中，由于a是非空tinyint，大小为1 byte，key_len中的1 byte就是指a。
rows：扫描行数。
filtered：最大100，越大越好。
extra：额外信息。

走全索引的explain

select a,b,c,d
where a
group by a,b,c

更改为以上sql后的explain结果：

可以直观地从rows看出，扫描行数从百万级减少到了几十行。
key_len = 1 + 8 + 1 = 10

优化

于是我直接加了(a,b,c,d)的索引，但发现不管是group by a, b 还是group by a,b,c,d，都不能走全索引。
加索引前：

加索引（a,b,c,d）后：

从key_len里能看到，只用了（a,b）c和d没用到。

没达到预期的分析

为什么上面的例子可以走到索引而该例不行呢？
因为例子中，mysql索引底层是B+树，where a=1之后，大树变小树，还是在一棵完整的树上搜索，所以可以继续使用b,c索引。
而我想优化的慢sql中，where a=1,b=1之后，已经不是完整的树了，所以数据的聚合只能由mysql来完成。
where a，b之后的数据：

a b c d
1 1 1 1
1 1 2 2
1 2 1 1
1 2 2 2

group by c，d时，mysql需要帮我把1,3行聚合，2,4行聚合，无法走索引。

除非

where a=1,b=1,c=1,d>1 
group by a,b,c,d

这样where后的顺序和group by的顺序相同，自然用到了索引。explain结果如下。

结论

当where语句只有1个条件时，索引可以顺着加，如下可以加（a,b,c）

where a
group b
order by c

当where语句有多个条件时，只加到where就好，如下可以加(a,b)

where a,b
group c,d
order by c

后续

观察到加索引前后的查询时间都在5s左右，感觉索引没起作用。分析如下：
由于数据离散程度较小，对大于全表20%的数据做聚合，因此加索引后的优化效果不明显。以后加索引优化时需要考虑：

1. where里有多个条件的情况，group by 走不到索引，只能在where里走；
2. 建索引还得考虑下数据分布，离散程度小的情况建索引优化意义不大

Reference

[1] lsq推荐我的explain详解
[2] 如何计算key_len
[3] explain解读
[4] lsq推荐我的mysql调优之索引——ORDER BY（GROUP BY）