MySql索引失效的几种原因分析

测试数据准备

建表语句
CREATE TABLE t_user_demo (
id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘自增id’,
name VARCHAR(20) DEFAULT NULL COMMENT ‘姓名’,
phone char(11) DEFAULT NULL COMMENT ‘手机号’,
age TINYINT DEFAULT NULL COMMENT ‘年龄’,
sex CHAR(1) DEFAULT NULL COMMENT ‘性别’,
PRIMARY KEY (id),
UNIQUE KEY idx_name_phone (name, phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
建立一个联合索引：idx_name_phone (name, phone)

表中插入一些数据：

1、联合索引不满足最左匹配原则

select * from t_user_demo where name = ‘zz’ and phone = ‘13933333333’;
严格按照索引顺序执行，肯定走索引

select * from t_user_demo where phone = ‘13933333333’ and name = ‘zz’;
这个语句虽然没有按照索引的建立顺序，但是依旧可以走索引，因为mysql会对这样的语句进行排序优化，或者交换两个条件的顺序并不影响结果。

select * from t_user_demo where phone = '13911111111’
这个语句就不会走索引了，因为不满足最左匹配原则。

select * from t_user_demo where name = 'zz’
交换一下顺序，只要符合最左匹配原则，单查联合索引中的列也是可以的。

select * from t_user_demo where name like ‘%zz%’ and phone = ‘13933333333’;
同样最左查询条件索引中断了，也不能走索引了。

建立一个phone，age，name的联合索引。
CREATE INDEX idx_phone_age_name ON t_user_demo (phone,age,name);

select * from t_user_demo where phone = ‘13944444444’ and age = 15 and name = 'qq’

走索引没问题。

select * from t_user_demo where phone = ‘13944444444’ and age > 15 and name = ‘qq’;

范围条件之后的列不走索引了。

2、隐式转换

select * from t_user_demo where name = ‘zz’ and phone = ‘13933333333’;

select * from t_user_demo where name = ‘zz’ and phone = 13933333333;
当把phone的引号去掉，则只走一个name列的索引，这是因为发生了隐式转换，phone列是char类型需要转换成相应类型进行比较，需要注意的是如果phone是数值类型，则右边无论是带引号还是不带引号都可以走索引。

3、like查询

select * from t_user_demo where name like '%zz%'
左右都有%的查询，是不会走索引的。

select * from t_user_demo where name like 'zz%'
左边没有%的情况下，会走范围类型的索引。

4、索引列存在运算或者使用函数

在age列上建立索引：
CREATE INDEX idx_age ON t_user_demo (age);

select * from t_user_demo where age = 15;
走索引没问题。

select * from t_user_demo where age-1 = 15;
索引列存在计算，不走索引了。

select * from t_user_demo where ABS(age) = 15;
一样不会走索引。

5、优化器

还有一些不走索引的原因可能是因为mysql优化器的原因，优化器会去为我们找到一个最优的执行方案，然而可能这个方案就是走全表的方式，所以也就不会走索引了。

最后附上索引检查的口诀

全值匹配我最爱，最左前缀要遵守。
带头大哥不能死，中间兄弟不能断。
索引列上少计算，范围之后全失效。
Like百分写最右，覆盖索引不写星。
不等空值还有or，索引失效要少用。
VAR引号不可丢，SQL高级也不难！