1)没有查询条件,或者查询条件的列没有索引,则不走索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
2)最佳左前缀法则
索引文件具有 B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引
3)主键插入顺序
对于一个使用InnoDB存储引擎的表来说,在我们没有显式的创建索引时,表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的,数据页和记录又是按照记录主键值从小到大的顺序进行排序,所以如果我们插入的记录的主键值是依次增大的话,那我们每插满一个数据页就换到下一个数据页继续插,而如果我们插入的主键值忽大忽小的话,就比较麻烦了,假设某个数据页存储的记录经满了,它存储的主键值在1~100之间:
如果此时再插入一条主键值为 9 的记录,那它插入的位置就如下图:
可这个数据页已经满了,再插进来咋办呢?
- 我们需要把当前 页面分裂 成两个页面,把本页中的一些记录移动到新创建的这个页中。
- 页面分裂和记录移位意味着什么?意味着: 性能损耗 !
- 所以如果我们想尽量避免这样无谓的性能损耗,最好让插入的记录的 主键值依次递增 ,这样就不会发生这样的性能损耗了。
- 建议:让主键具有 AUTO_INCREMENT ,让存储引擎自己为表生成主键,而不是我们手动插入
#4)计算、函数、类型转换(自动或手动)导致索引失效
能够使用上索引EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';#函数运算,索引失效EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
5)类型转换导致索引失效
如果不带引号,就是数字,字符类型不匹配,不走索引。
#索引失效EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = 123;#走索引EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = '123';
6)范围条件右边的列索引失效
student.classId>20; 范围索引失效
#走了age索引,范围后面的索引失效EXPLAIN SELECT SQL_NO_CACHE * FROM studentWHERE student.age = 30 AND student.classId>20 AND student.name = 'abc' ;EXPLAIN SELECT SQL_NO_CACHE * FROM studentWHERE student.age=30 AND student.name = 'abc' AND student.classId>20;
7)不等于(!= 或者<>)索引失效
#走索引EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name = 'abc' ;#索引失效EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc' ;EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc' ;
8)is null可以使用索引,is not null无法使用索引
#使用索引EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;#无法使用索引EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;
9)like以通配符%开头索引失效
#走索引EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%';#索引失效EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';
10)查询条件包含or且字段列不含索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
辅助索引+or+无索引的列:会先走索引列,但无索引的列会进行全表扫描,所以还不如不走索引,直接都全表扫描完事。
如果or前后都有索引,那么可能走索引,也可能不走索引。
11)数据库和表的字符集统一使用utf8mb4
统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好,统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。
一般性建议:
●对于单列索引,尽量选择针对当前query过滤性更好的索引
●在选择组合索引的时候,当前query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。
●在选择组合索引的时候,尽量选择能够包含当前query中的where子句中更多字段的索引。
●在选择组合索引的时候,如果某个字段可能出现范围查询时,尽量把这个字段放在索引次序的最后面。
总之,书写SQL语句时,尽量避免造成索引失效的情况。
来源地址:https://blog.csdn.net/weixin_45783741/article/details/127034372