一张图搞懂MySQL的索引失效

索引对于MySQL而言是非常重要的篇章。索引知识点也巨多要想掌握透彻需要逐个知识点一一击破今天来先来聊聊哪些情况下会导致索引失效。图片总结版全值匹配索引最佳explain select * from user where name zhangsan and age 20 and pos cxy and phone 18730658760;和索引顺序无关MySQL底层的优化器会进行优化调整索引的顺序 explain select * from user where name zhangsan and age 20 and pos cxy and phone 18730658760;1、违反最左前缀法则如果索引有多列要遵守最左前缀法则 即查询从索引的最左前列开始并且不跳过索引中的列 explain select * from user where age 20 and phone 18730658760 and pos cxy;2、在索引列上做任何操作如计算、函数、自动or手动类型转换等操作会导致索引失效从而全表扫描 explain select * from user where left(name,5) zhangsan and age 20 and phone 18730658760;3、索引范围条件右边的列索引范围条件右边的索引列会失效 explain select * from user where name zhangsan and age 20 and pos cxy;4、尽量使用覆盖索引只访问索引查询索引列和查询列一致减少select* explain select name,age,pos,phone from user where age 20;5、使用不等于!、mysql在使用不等于!、的时候无法使用索引会导致全表扫描除覆盖索引外 explain select * from user where age ! 20; explain select * from user where age 20;6、like以通配符开头%abc索引失效 explain select * from user where name like %zhangsan;索引生效 explain select * from user where name like zhangsan%;7、字符串不加单引号索引失效explain select * from user where name 2000;8、or连接少用or explain select * from user where name 2000 or age 20 or pos cxy;9、order by正常索引参与了排序 explain select * from user where name zhangsan and age 20 order by age,pos; 备注索引有两个作用排序和查找导致额外的文件排序会降低性能 explain select name,age from user where name zhangsan order by pos;//违反最左前缀法则 explain select name,age from user where name zhangsan order by pos,age;//违反最左前缀法则 explain select * from user where name zhangsan and age 20 order by created_time,age;//含非索引字段10、group by正常索引参与了排序 explain select name,age from user where name zhangsan group by age; 备注分组之前必排序排序同order by导致产生临时表会降低性能 explain select name,pos from user where name zhangsan group by pos;//违反最左前缀法则 explain select name,age from user where name zhangsan group by pos,age;//违反最左前缀法则 explain select name,age from user where name zhangsan group by age,created_time;//含非索引字段使用的示例数据mysql show create table user \G ****************************************************** Table: user Create Table: CREATE TABLE user ( id int(10) NOT NULL AUTO_INCREMENT, name varchar(20) DEFAULT NULL, age int(10) DEFAULT 0, pos varchar(30) DEFAULT NULL, phone varchar(11) DEFAULT NULL, created_time datetime DEFAULT NULL, PRIMARY KEY (id), KEY idx_name_age_pos_phone (name,age,pos,phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci​编辑mysql