索引优化:索引失效(应避免)

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

推荐阅读更多精彩内容