创建索引后
3.2 最佳左前缀
-- 创建索引 CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME); -- 缺少联合索引的第一个字段 explain select sql_no_cache * from emp where deptId = 4 and name = 'abcd'; -- 联合索引的第一二个字段，缺少最后一个字段 explain select sql_no_cache * from emp where age = 30 and deptId = 4; -- 联合索引的第一三各字段，缺少第二个字段 explain select sql_no_cache * from emp where age = 30 and name = 'abcd' ;
查询字段与索引字段顺序的不同会导致， 索引无法充分使用， 甚至索引失效！
原因： 使用复合索引， 需要遵循最佳左前缀法则， 即如果索引了多列， 要遵守最左前缀法则。 指的是查询从索引的最左前列开始并且不跳过索引中的列。
结论： 过滤条件要使用索引必须按照索引建立时的顺序， 依次满足， 一旦跳过某个字段， 索引后面的字段都无法被使用
3.3 索引计算
不要在索引上做任何计算！
不在索引列上做任何操作（计算、 函数、 (自动 or 手动)类型转换）， 会导致索引失效而转向全表扫描。
1.在查询列上使用函数
-- 索引不带计算 EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age=30; -- 索引字段计算 EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(age,3)=30;
2. 在查询列上做了转换
-- 创建单值索引，字符串类型 name create index idx_name on emp(name); -- 字符串加单引号情况 explain select sql_no_cache * from emp where name='30000'; -- 字符串不加单引号， 则会在 name 列上做一次转换！ explain select sql_no_cache * from emp where name=30000;
3.4 索引范围：索引列上不能有范围查询
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=5 AND emp.name = 'abcd'; explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid<=5 AND emp.name = 'abcd';
建议： 将可能做范围查询的字段的索引顺序放在最后
3.5 索引覆盖：尽量使用覆盖索引
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt'; explain SELECT SQL_NO_CACHE age,deptId,name FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt';
3.6 不等： 使用不等于（!= 或者 <>）的时候
mysql 在使用不等于(!= 或者<>)时， 有时会无法使用索引会导致全表扫描。
3.7 null：字段的is not null 与is null
当字段允许为 Null 的条件下：
is not null 用不到索引， is null 可以用到索引。
3.8 like：like的前后模糊匹配
前缀不能出现模糊匹配！
3.9 or：减少使用or
使用 union all 或者 union 来替代：
总结
到此这篇关于MySQL数据库索引以及失效场景的文章就介绍到这了,更多相关MySQL数据库索引失效内容请搜索
更多Mysql内容来自木庄网络博客
标签：Mysql
相关阅读 >>
【mysql数据库】第四章解读：schema与数据类型优化（下）
更多相关阅读请进入《mysql》频道 >>
数据库系统概念 第6版
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。
