这是我参与8月更文挑战的第4天，活动详情查看：8月更文挑战
索引失效问题
情况一：最佳左前缀
SQL ：建表并添加一个复合索引，索引有三个字段name、age、pos
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`)
使用索引中的所有字段进行查询
EXPLAIN SELECT
*
FROM
staffs
WHERE
`name` = "July"
AND age = 23
AND pos = "dev"
结果：
这种情况看着一切正常，索引都使用上了。
但是如果查询条件换一下
EXPLAIN SELECT
*
FROM
staffs
WHERE
age = 23
AND pos = "dev"
结果为：
可以发现实际建立的索引没有使用到，违背了最佳左前缀法则，也就是创建的索引必须从左到右的按顺序使用到，索引才会生效。
在使用到索引的第一个和第三个字段的情况
EXPLAIN SELECT
*
FROM
staffs
WHERE
name = "July"
AND pos = "dev"
结果：
虽然使用到索引了，但是只用到了第一个索引，第三个明明使用了固定值也没有用到索引，还是因为违背了最佳左前缀的原则。
情况二：索引列使用方法函数等操作
使用EXPLAIN SELECT * FROM staffs WHERE name = "july";查询结果为
使用EXPLAIN SELECT * FROM staffs WHERE left(name,4) = "july";查询结果为
在索引列上使用了函数就会导致索引的失效。
情况三：存储引擎不能使用索引范围条件最右边的列
使用EXPLAIN SELECT * FROM staffs WHERE name = "july" AND age > 20 AND pos = "manager";查询结果为：
该查询从age开始到后面的索引都没有用上。索引中范围条件右边的会失效。
情况四：尽量覆盖索引
使用EXPLAIN SELECT name,age,pos FROM staffs WHERE name = "july" AND age > 20 AND pos = "manager";查询结果为
情况五：like 以通配符开头的索引会失效
两边都有 '%' 的情况 EXPLAIN SELECT * FROM staffs WHERE name LIKE "%july%" 的结果
只有左边有 '%' 的情况 EXPLAIN SELECT * FROM staffs WHERE name LIKE "%july" 结果
只有右边有 '%' 的情况 EXPLAIN SELECT * FROM staffs WHERE name LIKE "july%" 结果
只有把 '%' 加在右边索引才没有失效，但是某些场景下必须使用 '%' 。
解决方法：
建表和索引
CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');
CREATE INDEX idx_user_nameAge ON tbl_user(`name`,age);
使用覆盖上索引避免全表扫描 EXPLAIN SELECT name ,age FROM tbl_user WHERE name LIKE "%aa%" 结果
总结
- 全值匹配最好
- 最佳左前缀
- 不在索引列上做任何操作，否则会导致索引失效而导致全表扫描
- 存储引擎不能使用索引范围条件最右边的列
- 尽量使用覆盖索引，减少 select *
- MySQL 在使用不等于 (!= 或 <>) 的时候无法使用索引，会导致全表扫描
- is、null、is not null 也无法使用索引
- 使用 like 以通配符开头的索引也会失效
- 字符串不加单引号索引失效
- 尽量少用 or，or 连接也会导致索引失效
