mysql索引失效的情况怎么解决（为什么mysql 使用函数 索引失效）

今天给各位详细介绍关于“mysql索引失效的情况怎么解决”的核心内容以及“为什么mysql 使用函数 索引失效”的相关知识，希望对各位有所帮助。

　　面试的时候被问到了：导致索引失效的原因有哪些？顿时哑口无言，平时不多注意，而支支吾吾的回答了几点，虽然问题不大，但是表达还是欠缺。今天在这里做一个总结，给自己长点记性。也可以当做是面试后总结的经验与笔记吧，保证下次不再犯错，同样在开发过程中能注意到这些问题。希望也能帮助到大家！

　　下面结一些示例来给大家讲解

　　首先先新建临时表,这个表有四个字段 主键 、名字、年龄、职位

　　CREATE TABLE `sys_user` (

　　`id` varchar(64) NOT NULL COMMENT '主键',

　　`name` varchar(64) DEFAULT NULL COMMENT '名字',

　　`age` int(64) DEFAULT NULL COMMENT '年龄',

　　`pos` varchar(64) DEFAULT NULL COMMENT '职位',

　　PRIMARY KEY (`id`),

　　KEY `idx_sys_user_nameAgePos` (`name`,`age`,`pos`)

　　) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

　　1.全值匹配

　　全值匹配意思就是联立的复索引的顺序和个数要和检索的条件顺序和个数相同。

　　2.左前缀法则（重要）

　　左前缀法则是指，如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

　　下面我们给这个表建立一个复索引

　　SELECT * FROM sys_user WHERE name='小明' AND age = 22 AND pos ='java';

　　以下是我们的检索语句：

　　SELECT * FROM sys_user WHERE name='小明' AND age = 22 AND pos ='java';

　　我们通过在检索语句前面加关键字 EXLAIN，可以知道是否使用的索引

　　（1）EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age = 22 AND pos ='java';

　　（2）EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age = 22 ;

　　（3）EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND pos ='java';

　　通过上面的结果我们可以知道，个复索引的三个字段我们都用了，第二个复索引我们只用到两个字段，第三个复索引我们只用到一个字段。三个语句我们都用到索引，显然种是的。

　　我们再看看哪种情况会失效：

　　（4）EXPLAIN SELECT * FROM sys_user WHERE age = 22;

　　（5）EXPLAIN SELECT * FROM sys_user WHERE pos ='java';

　　（6）EXPLAIN SELECT * FROM sys_user WHERE age = 22 AND pos ='java';

　　以上三种情况都变成了全表扫描，原因是违反了最左左前缀原则，因为复索引最左边的是name，当检索条件name没在前面索引将失效，种情况满足了全值匹配，第二种满足了两个字段name和age，第三种因为只满足了name，所以索引只用到name。

　　3.不在索引列上做任何操作（计算、函数（自动或手动）类型转换），会使索引失效转为全表扫描

　　（7）EXPLAIN SELECT * FROM sys_user WHERE LEFT(name,1)='小明';

　　第七种情况失效是因为索引列做了计算或者函数的操作，导致了全表扫描。

　　4.存储引擎不能使用索引中范围条件右边的列

　　可能大家关看上面的文字不知道是什么意思，下面我们执行一下查询语句就清楚了

　　（8）EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age

　　从上图我们可以知道type变成了范围级别，也就是说age