mysql索引添加要慎重

最近调整了下mysql表的部分索引，本次项目也算阶段性结束了。

那本次索引修改范畴是什么呢？使用log_queries_not_using_indexes找出 没有使用索引 的sql，它们也属于慢查询日志的一部分，手动找出来后再进行分析。

本次不修改应用的sql，而是基于现有sql进行索引调整，一直没有轻易调整索引，只有理解清楚了才应该下手，否则就是想当然。

关于索引调整主要借鉴的工具就是explain，本次不说对于它的理解，还是基于实战进行描述。

1：不要添加不必要的索引，个人认为记录数少于200的表没必要添加索引，进行全表扫描的成本可能比基于索引的查询还低，毕竟维护索引也是有代价的。

2：尽量避免filesort和临时表

对于filesort就是内部排序，主要是order by上的字段没有索引，所以建议加上，这样至少第二索引本身就已经排序了，不一定用到检索，但却用到了索引排序。

explain SELECT * FROM `test` WHERE sortfield > 0 AND `status` = 1 ORDER BY `sortfield` desc , `id` asc LIMIT 4;

比如这句还是会用到一定的排序，但比sortfield没有索引好了很多。

临时表要尽量去避免的，主要是group by字段无索引导致的。

3：尽量使用ref类型的查询

这个例子只是为了理解explain。

select * from test where uid=450022 and status=1 order by id desc;

上述语句如果没有uid索引，explain type是index，key是主键，就是rows返回比较多，Extra还是会用到where（因为还有status=1）。

从优化大家角度看，给uid加索引就能解决大部分问题（没有必要为每个where字段添加索引），explain type是ref，key是uid怂恿，rows返回就几条，Extra还是会用到where

4；时间范围查询

如果有两个时间比较的查询，建议不用使用复合索引，或者每个时间都弄索引，找一个合适的加就行，比如：

SELECT count(*)  FROM `test` WHERE status = 1 and taskend < '2020-04-08 11:52:21' and taskstart > '2020-04-07 00:00:00' LIMIT 1;

考虑到时间一直在往前走，给taskstart加索引就可以了，taskend没必要加索引。

5：sql索引选择

有的时候数据量太少，mysql会自行判断用不用索引，比如下面的sql：

KEY `so` (`start_time`,`or`)

explain select * from `test` where `start_time` < '2020-04-06 09:13:18' and `end_time` > '2020-04-06 09:13:18' and `status` = 1 order by or asc;

由于记录数太少（认为还不如全表扫描，因为没有limit语句），mysql会选择不使用索引，如果用force index则看到能使用到索引：

explain select * from `test` force index (`so`) where `start_time` < '2020-04-06 09:13:18' and `end_time` > '2020-04-06 09:13:18' and `status` = 1 order by or asc;

6：两个排序

有filesort不代表就有问题，有的时候也避免不了，比如两个order条件

SELECT * FROM `test` WHERE filesort > 0 AND `status` = 1 ORDER BY `filesort` desc , `id` asc LIMIT 4;

二级索引拿到结果后，还是会基于主键在内部进行排序。

7：索引合并

这条语句用于私信：

select * from test where `status`=1 and (a =  or b = ) and (a =  or b = ) order by time desc, id desc;

原来虽然a有索引，还是做了全表扫描，rows也非常大，且有filesort，我一直没到怎么优化，因为uid和time做复合索引也没有用，后来同事给b加了索引，效果非常好。explain的type是index_merge，同时用到了a和b索引，最终进行了合并，Extra是Using union(a,b); Using where; Using filesort.