mysql索引优化

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

作者：编程界的小学生
链接：https://www.jianshu.com/p/d5b2f645d657
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。