wix做的网站在国内访问不,推一手新闻发稿平台,成都网站开发公司排名,wordpress添加商品分类页mysql视频教程栏目索引失效的情况。索引对于MySQL而言#xff0c;是非常重要的篇章。索引知识点也巨多#xff0c;要想掌握透彻#xff0c;需要逐个知识点一一击破#xff0c;今天来先来聊聊哪些情况下会导致索引失效。图片总结版相关免费学习推荐#xff1a;mysql视频教程…mysql视频教程栏目索引失效的情况。索引对于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连接少用orexplain 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: userCreate 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
