MySQL学习笔记:索引失效

EXPLAIN中的type为range,而且rows过大时:

MySQL学习笔记:索引失效

  • 查询临界值示例:

MySQL学习笔记:索引失效

一般情况:

以下参考:索引失效的情况(很棒的一篇文章,不过有些不准确,比如:使用 >= 不会失效,此处做个总结记录)

WHERE子句中

  • 未遵循最左原则
  • 在索引列上计算,使用函数
  • 在索引列上自动(如:`phone`=18172874277,`phone`是字符串型,所以等号后面的值应加引号),手动转换类型
  • 某列使用'>','<',右侧的索引列(按照组合索引字段顺序)
  • LIKE以通配符开头
  • 组合索引列之间使用OR

GROUP BY子句中(WHERE条件的索引是走的,之后的GROUP BY或者ORDER BY的索引是不走的)

不走索引的标准可以观察执行计划中的Extra: GROUP BY将使用额外的文件排序,以及产生临时表

  • 使用组合索引,须遵守最左原则(须按照索引顺序),WHERE子句中的索引字段是等值查询时,可以在GROUP BY子句中省略;

MySQL学习笔记:索引失效

MySQL学习笔记:索引失效

  • 存在一个组合索引和一个单索引时,GROUP BY的索引字段在WHERE子句中都有,且WHERE子句中的组合索引是等值查询,且WHERE子句中的单索引是等值查询或者执行计划中的key是单索引时,是用到索引的

MySQL学习笔记:索引失效

MySQL学习笔记:索引失效

ORDER BY子句中,

不走索引的标准可以观察执行计划中的Extra: ORDER BY将使用额外的文件排序

  • 不使用WHERE子句时

MySQL学习笔记:索引失效

  • 使用组合索引,须遵守最左原则(须按照索引顺序),且WHERE子句中有效的组合索引;WHERE子句中的有效索引字段是等值查询时,可以在ORDER BY子句中省略;

MySQL学习笔记:索引失效

  • 存在多个单索引,或存在非索引字段时

MySQL学习笔记:索引失效

  • 存在一个组合索引和一个单索引时,当表中只有一个组合索引,ORDER BY的索引字段在WHERE子句中都有,且WHERE子句中的组合索引是等值查询,且WHERE子句中的单索引是等值查询或者执行计划中的key是单索引时,是用到索引的

MySQL学习笔记:索引失效

注意:

  • 使用IN时,只有一个值时,相当于'=',有多个值时,索引也是有效的

MySQL学习笔记:索引失效

  • 索引列各自使用OR时,相当于IN

MySQL学习笔记:索引失效

  • LIKE以通配符结尾时,索引有效

MySQL学习笔记:索引失效

  • 使用'<=',索引有效

MySQL学习笔记:索引失效

  • 上述情况,EXPLAIN中的type为range,在rows过大时,索引均会失效。
本作品采用《CC 协议》,转载必须注明作者和本文链接
讨论数量: 1

我认为您第一、二张图的实验是不够严谨的,特别是第二张图,将大量记录的 dept_id 更新为 92,这时候 dept_id 字段的辨识度不高,那么mysql的执行计划就可能会放弃使用索引转而使用全表扫描。
所以说,您上面出现索引失效的原因不一定是因为行数太多。

1个月前 评论
surpass (楼主) 1个月前
Brad-Wen (作者) 1个月前
surpass (楼主) 1个月前

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!