记一次慢查询优化

1、问题描述
– SQL last time occurred

select count(id) `count`, plat, reminderSubjectId from SendingPlanWHERE status = 1and sendingTime >= '2017-11-01 21:25:00.0'and sendingTime <= '2017-11-01 22:25:00.0' GROUP BY plat,reminderSubjectId

SendingPlan表，在查询某一时间段内的数据时，查询较慢，需要1.5s左右。

2、查找原因
查看explain sql

这张表有一个联合索引，包括(status, plat, reminderSubjectId, sendingTime)
explain的结果中，type为ref，说明使用了索引，但是key_len为1，说明并没有使用全部的联合索引（否则应该为12？tinyint=1，int=4，date=6）。
由于mysql的最左前缀匹配原则，在执行此条语句时，只使用了status这一个索引，并没有使用到sendingTime索引。
而在此条语句中，sendingTime索引的区分度明显较高。
3、解决思路
→ 由于此张表的其他查询中，确实有(status, plat, reminderSubjectId, sendingTime)这四个字段全都涉及的语句，因此决定保留此索引。
新建只包含(status, sendingTime)两个字段的联合索引，查看explain sql结果。

发现mysql执行时依然选择了原来的索引，而没有选择新建的索引。
使用force index (ix_status_sendingTime)，运行此条语句，查询时间456ms，说明使用sendingTime索引时确实可以提高效率，但是为什么mysql选择了另外一个呢？
强制使用索引后explain 结果如下

group by的两个字段没有索引时，会产生临时表，这时mysql依然选择第一种索引。
强制使用第二种索引，此时type为range，extra中using temporary和using filesort，这几个对于mysql来说均是效率较低的方式（在执行group by语句时，产生了临时表），因此在进行两个索引的对比时，mysql选择了前者，这种产生临时表的方式也不是最佳选择。
→ 现在的目标是，需要使用sendingTime索引，同时group by时可以使用plat和reminderSubjectId字段索引，因此再次调换索引位置，变为(plat, reminderSubjectId, status, sendingTime)。
explain结果如下，可以看到此时key_len=12, 说明使用了联合索引中的全部索引，没有产生临时表。

然而更新索引后，查询效率依然没有提高，维持在1.6s左右。
→ 去掉group by语句，并将plat和reminderSubjectId变为等值查询
select count(id) count, plat, reminderSubjectId from SendingPlan
WHERE status = 1
and sendingTime >= ‘2017-11-01 21:25:00.0’
and sendingTime <= ‘2017-11-01 22:25:00.0’
and plat = 1
and reminderSubjectId = 3
此时运行效率大幅提高，变为119ms。
似乎是一个可行的方案。
关于Mysql优化的一点点资料
1、相关规范
注释：枚举类型需要self-explain 状态字段
给出状态码及解释
表设计规范：必须有无意义的自增主键&有UpdateTime字段和索引
使用Group by时，如无排序的需求，尽量加上order by null
Join中实用的关联字段使用统一数据类型
尽量不要在查询语句中指定强制索引force index（QAQ）
尽量减少使用Text字段，占用的磁盘空间过大
多语句事务要注意
2、索引
二级索引：叶子节点存放索引自身的值和索引对应的主键的值
联合索引：index(a,b,c)会覆盖index(a)，index(a,b)，index(a,b,c)三个索引
查看使用了什么索引 -→ ken_len
索引使用遵循最左匹配原则
建立原则：
a）区分度高的列作为索引
b）字段长度小的列作为索引
c）选用NOT NULL的列
d）尽量扩展索引，避免新增索引
索引并不是越多越好！
3、explain
type字段（优→ 差）
const（只匹配一行，主键／唯一键）, eq_ref（单行关联查询，主键／唯一键进行join）, ref（索引键值查询）, range（索引范围查询）, index（这个也不好，是扫描整个二级索引）, all （全表扫描）
extra字段
禁止using filesort（额外排序）和using temporary（临时表）
4、优化小技巧
分页查询优化
select * from ‘t’ inner join (select id from ‘t1’ where type=1 order by id desc limit XX,XX) t2 using(id);
order by优化
如果order by中的字段在where中也有，可以提高效率
查询中的in语句
in后的值过多， 导致查询变慢。
in后的值个数>=200(5.7)/10(5.6), type就会变为all，这个是数据库层面设计的，因为每次查询是否in时，sql优化器会去扫描一遍索引查看大概对应的数据条数，若允许过多的in值，则一次查询会导致扫描很多次索引页面，开销很大)
数据库监控平台：http://sqlreview.dba.dp 查看慢查询报表&历史监控数据

TODO
sql优化器选择索引的策略？

参考：
1、https://tech.meituan.com/mysql-index.html