MySQL什么情况下会导致索引失效
MySQL什么情况下会导致索引失效
索引（Index）是数据库中一种用于快速查找和访问表中数据的结构，它类似于书的目录，通过索引可以快速定位到目标数据，而无需遍历整个表，索引的存在可以显著提高查询速度，尤其是在处理大量数据时
有时我们为了避免出现回表查询，我们就会以多个字段来创建索引，即覆盖索引，具体可看：聚簇索引、非聚簇索引、覆盖索引-CSDN博客
使用覆盖索引最容易遇到的问题就是索引失效问题，那么哪些情况下会出现索引失效，又该如何避免索引失效呢？
索引命中
以一个tb_users表为例子：
我们以其中三个字段建立索引：
CREATE INDEX tb_user_index ON tb_users(name, status,username)
可以利用show语句可以查看我们刚刚为tb_users建立的索引：
SHOW INDEX FROM tb_users
索引字段也是有顺序的，顺序就是我们创建索引时的顺序，使用explain可以查看SQL语句的执行计划：
1.单个条件
EXPLAIN SELECT * FROM tb_users WHERE name = '崔秀英'
命中索引的长度是43，此时我们的查询条件只有name，因此命中的索引也只有name
此时查询条件中添加一个status字段：
2.两个字段
EXPLAIN SELECT * FROM tb_users WHERE name = '崔秀英' AND status = 1
此时命中索引的长度为48，表示命中了name和status
再次添加一个字段：
3.三个字段条件
EXPLAIN SELECT * FROM tb_users WHERE name = '崔秀英' AND status = 1 AND username = 'Joe Edwards'
此时三个索引字段全部命中，索引长度为131
因此命中索引的数量不一样，key_len也不一样，可以利用它来判断索引是否失效
索引失效
1.违反最左前缀法则会导致索引失效
指的是查询从索引的最左前列开始，中间不跳过索引中的列，比如：
EXPLAIN SELECT * FROM tb_users WHERE name = '崔秀英' AND username = 'Joe Edwards'
这条查询语句中，我们用两个索引字段作为了查询条件，那么理论上应该命中两个索引
但是key_len的值为43，只命中了name字段，username字段索引失效了，因为中间跳过了索引中的一列status
2.范围查询某个索引，其右边索引失效
当where条件中用索引范围查询，这个索引右边的字段会失效
EXPLAIN SELECT * FROM tb_users WHERE name = '崔秀英' AND status > 1 AND username = 'Joe Edwards'
这条查询语句使用了三个索引条件，理论上key_len为131
但是实际上命中索引长度为48，username字段未命中，原因是status使用了范围查询，因此他右边的username失效了
3.在索引列上进行运算操作，会导致索引失效
EXPLAIN SELECT * FROM tb_users WHERE SUBSTRING(0,1,name) = '崔秀英'
这里对name字段进行了截取运算操作
因此name字段并未命中，导致索引失效
4.字符串不加单引号，导致索引失效
比如说status字段，上表中我们使用的是int类型，假如我们使用了varchar类型，在查询的时候就要加单引号，如：
EXPLAIN SELECT * FROM tb_users WHERE name = '崔秀英' AND status = '1'
如果没有加单引号，MySQL就会进行多余的类型转换步骤，该步骤会导致索引失效
5.以%开头的like模糊查询，会导致索引失效
如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引会失效
EXPLAIN SELECT * FROM tb_users WHERE name LIKE '%秀英'
可以看到索引命中长度为null，索引失效了