SQL如下：
CREATE TABLE IF NOT EXISTS 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 `test`.`staffs` (`name`, `age`, `pos`, `add_time`) VALUES ('z3', 22, 'manager', now());
INSERT INTO `test`.`staffs` (`name`, `age`, `pos`, `add_time`) VALUES ('July', 23, 'dev', now());
INSERT INTO `test`.`staffs` (`name`, `age`, `pos`, `add_time`) VALUES ('2000', 23, 'dev', now());
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);
索引失效的十大原因：
-
1、全值匹配我最爱
建立几个复合索引字段，最好就用上几个字段。且按照顺序来用。 -
2、最佳左前缀法则
如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始，不跳过索引中间的列。（带头大哥不能死，中间兄弟不能丢）
例如：
(1)、EXPLAIN SELECT * FROM staffs WHERE name = 'July';
Paste_Image.png
(2)、EXPLAIN SELECT * FROM staffs WHERE name = 'July' AND age = 25;
Paste_Image.png
(3)、EXPLAIN SELECT * FROM staffs WHERE name = 'July' AND age = 25 AND pos = 'dev';
Paste_Image.png
(1)、(2)和(3)的SQL都完美的用了索引，没有失效。看如下的SQL：
(4)、EXPLAIN SELECT * FROM staffs WHERE age = 23 AND pos = 'dev';
Paste_Image.png
(5)、EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';
Paste_Image.png
(6)、EXPLAIN SELECT * FROM staffs WHERE name = 'July' AND pos = 'dev';
Paste_Image.png
可以发现只用到了第一个索引，第三个pos索引失效了，怎么看的？key_len长度和只用到第一个索引长度一样长，如果pos也用到了，肯定大于74，ref只有一个常量，如果pos用到了，肯定是两个值。
总结：(4)和(5)都去掉了复合索引的第一个索引字段(name)，可以发现索引失效了。换句话说：第一个索引一定要用，否则注定失效，第一个好比是火车头，另外两个是车厢，火车头都没了还怎么跑？(6)虽然没丢到车头，但是中间车厢断了，他尾厢还怎么跑？
- 3、不再索引列上做任何操作(计算、函数、（自动or手动）类型转换)，会导致索引失效而转向权标扫描
例如
EXPLAIN SELECT * FROM staffs WHERE LEFT(name, 4) = 'July';
Paste_Image.png
从结果可知，我只是用了left左截取函数（对name左截取4个长度的值为July的，与不写LEFT得出的结果一模一样。），结果集际返回的与不写left一样，却发现索引失效了。
- 4、存储引擎不能使用索引中范围条件右边的列。（范围之后全失效）
- 若中间索引列用到了范围（>、<、like等），则后面的所以全失效。*
例如
(1)、EXPLAIN SELECT * FROM staffs WHERE name = 'July' AND age = 25 AND pos = 'dev';
Paste_Image.png
(2)、EXPLAIN SELECT * FROM staffs WHERE name = 'July' AND age < 25 AND pos = 'dev';
Paste_Image.png
总结：(2)中将age字段条件从=改成了<，查出的是个范围，所以可发现第三个字段pos索引失效了，因为type类型低了，key_len短了。ref也空了。
- *5、尽量使用覆盖索引（只访问索引的查询(索引列和查询列一致)），减少select **
例如：
(1)、EXPLAIN SELECT * FROM staffs WHERE name = 'July';
Paste_Image.png
(2)、EXPLAIN SELECT name FROM staffs WHERE name = 'July';
Paste_Image.png
可以发现，若将替换成索引列的话会用到Using index，直接从索引读，效果更佳，数据量大的时候更明显 *
(3)、EXPLAIN SELECT * FROM staffs WHERE name = 'July' AND age < 25 AND pos = 'dev';
Paste_Image.png
(4)、EXPLAIN SELECT name, age FROM staffs WHERE name = 'July' AND age < 25 AND pos = 'dev';
Paste_Image.png
可以发现，范围查找时，若将替换成索引列的话不仅会用到Using index索引级别还会是ref，key_len也短，效果更佳，数据量大的时候更明显 *
-
6、Mysql在使用不等于(!=、<>)或like的左模糊的时候无法试用索引会导致全表扫描。
例如：
(1)、EXPLAIN SELECT * FROM staffs WHERE name != 'July';
Paste_Image.png
(2)、EXPLAIN SELECT * FROM staffs WHERE name LIKE '%July';
Paste_Image.png
- 7、IS NULL和IS NOT NULL也无法使用索引
例如：
(1)、EXPLAIN SELECT * FROM staffs WHERE name IS NULL;
Paste_Image.png
(2)、EXPLAIN SELECT * FROM staffs WHERE name IS NOT NULL;
Paste_Image.png
- 8、字符串不加单引号索引失效
例如：
EXPLAIN SELECT * FROM staffs WHERE name = 2000;
Paste_Image.png
- 9、少用or，用它来连接时索引会失效。
本篇总结：
Paste_Image.png
口诀
Paste_Image.png
若有兴趣，欢迎来加入群，【Java初学者学习交流群】：458430385，此群有Java开发人员、UI设计人员和前端工程师。有问必答，共同探讨学习，一起进步！
欢迎关注我的微信公众号【Java码农社区】，会定时推送各种干货：
qrcode_for_gh_577b64e73701_258.jpg
网友评论