【笔记】索引失效

索引失效

1. 避免WHERE语句中用OR进行条件拼接，可能导致索引失效，尽量用IN代替；
2. 尽可能不用DISTINCT，DISTINCT结合ORDER BY会导致索引失效，去重可用GROUP BY或EXISTS；
3. 避免使用NOT条件，!=、<>、NOT IN、NOT EXISTS；
4. 避免NULL判断；
5. LIKE查询尽量不要以%开头；
6. 避免对字段进行函数、计算、类型转换等操作，如WHERE A*2 >= B => WHERE A >= B/2；
7. 关联查询的关联字段类型要一致；
8. 索引字符串类型不加单引号；
9. IN后面参数太长时索引会失效，使用EXISTS替代，有明确查询条件的使用IN，否则建议使用EXISTS，SQL语句中IN包含的值不应过多，如果数值连续，可使用BETWEEN；

• EXISTS和IN的区别，驱动顺序不一样：EXISTS用外层表驱动，IN先进行子查询，所以IN适合外大内小，EXISTS则反之；
• NOT EXISTS优于NOT IN，如果使用IN，将出现最频繁的值放在最前面，出现得最少的值放最后面，减少判断次数；

10. 联合索引使用，如TEST表的索引A_B_C：

• AND查询
• SELECT A,B,C FROM TEST WHERE A=? AND B=? AND C=? --> 索引全覆盖，效率最高
• SELECT A,B,C FROM TEST WHERE A=? AND B=? --> 索引覆盖A和B
• SELECT A,B,C FROM TEST WHERE B=? AND A=? --> 索引覆盖A和B，查询优化器优化
• SELECT A,B,C FROM TEST WHERE B=? AND C=? --> 不走索引
• SELECT A,B,C FROM TEST WHERE C=? --> 不走索引
• 范围查询
• SELECT A,B,C FROM TEST WHERE A=? AND B BETWEEN ? AND ? AND C=? --> 索引覆盖A和B，B是范围查询，导致C不能走索引
• SELECT A,B,C FROM TEST WHERE A BETWEEN ? AND ? AND B=? --> 索引覆盖A，A是范围查询，导致B不能走索引
• 排序查询
• SELECT A,B,C FROM TEST WHERE A=? AND B=? ORDER BY C --> 索引全覆盖，效率最高
• SELECT A,B,C FROM TEST WHERE A=? AND B BETWEEN ? AND ? ORDER BY C --> 索引覆盖A和B，B是范围查询，导致C不能走索引，会出现file sort
• 联合索引总结
• 1、联合索引的使用与写WHERE条件的顺序无关，优化器会调整，最好顺序一致；
• 2、遵循最左侧远侧，遇到范围查询列之后的列索引会失效；
• 3、合理使用索引排序，避免出现file sort。