【MySQL】MySQL SQL慢和索引不生效的一些场景

SQL慢的一些原因分析：
1.没有索引
2.索引不生效
3.limit深度分页查询
4.单表数据量太大
5.多表join或者子查询太多
6.in查询条件条件太多：条件太多可以考虑分组，比如500个条件一组
7.数据库在写脏数据
8.order by使用了磁盘文件排序
9.锁阻塞
10.delete语句带有in子查询不走索引(MySQL5.7中，delete in子查询不走索引，但是select in子查询却走索引)
11.group by使用临时表排序(使用临时表，排序)
12.参数配置不合理
13.资源问题(硬件或网络)

 

索引不生效的场景：
1.隐式的类型转换，索引失效
2.查询条件包含or，可能导致索引失效；所有字段都有索引，也许会走索引;OR查询左右有未命中索引的会失效
3.like模糊匹配可能导致索引失效，比如Like以%开头
4.查询条件不满足复合索引的最左匹配原则
5.索引列上使用了函数
6.对索引字段进行运算
7.索引字段上使用(!=或<>),not in索引可能失效
8.索引字段上使用is null、is not null，索引可能失效
9.关联字段编码格式不一致
10.优化器选错索引，比如统计信息不对
11.某些bug

 

一些优化方案：

1.尽量避免使用子查询。低版本对子查询的优化能力比较差
2.用in来替换or；如果in条件过多，对于连续的值，可以考虑使用between,或者使用连接来替换
3.读取适当的记录LIMIT M,N，过多的便宜量的场景，应该考虑需求是否合理
4.减少不必要的order by，考虑使用order by null来优化
5.避免随机获取记录，因为索引可能会无效
6.区分in和exists。exists，以外层表为驱动表，先被访问；如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
7.in查询在某些情况下有可能会查询返回错误的结果，因此，通常是建议在确定且有限的集合时，可以使用in。如 IN(0，1，2)
8.group by的一些优化方案：
·group by 后面的字段加索引
·order by null 不用排序
·尽量只使用内存临时表,适当调大tmp_table_size参数
·使用SQL_BIG_RESULT,如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法（直接用磁盘临时表）得到group by的结果。
9.使用where子句替换Having子句：避免使用having子句，having只会在检索出所有记录之后才会对结果集进行过滤，这个处理需要排序分组，如果能通过where子句提前过滤查询的数目，就可以减少这方面的开销。