7. 索引优化注意事项2 - 详解

本篇通过复合索引为主，在排序和分组时候分析会不会走索引与为什么索引就失效了来展开。

测试sql

CREATE TABLE test03
(
	id INT PRIMARY KEY AUTO_INCREMENT,
	c1 CHAR(10),
	c2 CHAR(10),
	c3 CHAR(10),
	c4 CHAR(10),
	c5 CHAR(10)
);	

INSERT INTO test03 ( c1, c2, c3, c4, c5 ) VALUES ( 'a1', 'a2', 'a3', 'a4', 'a5' );
INSERT INTO test03 ( c1, c2, c3, c4, c5 ) VALUES ( 'b1', 'b2', 'b3', 'b4', 'b5' );
INSERT INTO test03 ( c1, c2, c3, c4, c5 ) VALUES ( 'c1', 'c2', 'c3', 'c4', 'c5' );
INSERT INTO test03 ( c1, c2, c3, c4, c5 ) VALUES ( 'd1', 'd2', 'd3', 'd4', 'd5' );	
INSERT INTO test03 ( c1, c2, c3, c4, c5 ) VALUES ( 'e1', 'e2', 'e3', 'e4', 'e5' );

CREATE INDEX idx_test03_c12345 ON test03( c1, c2, c3, c4 );

记好正常情况索引的长度，后面会说明几个列走了索引

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1';
EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2';
EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2' AND c3 = 'a3';
EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2' AND c3 = 'a3' AND c4 = 'a4';

示例1

复合索引顺序全乱情况下会走索引嘛，而且会走几个列的索引？

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2' AND c4 = 'a4' AND c3 = 'a3';
EXPLAIN SELECT * FROM test03 WHERE c4 = 'a4' AND c3 = 'a3' AND c2 = 'a2' AND c1 = 'a1' ;

根据2条sql 的执行计划发现，4个列使用了索引。优化器没有那么傻，他会给你排好序

可以发现这一条就没有走索引，因为复合索引的开头都没有

EXPLAIN SELECT * FROM test03 WHERE c4 = 'a4' AND c3 = 'a3' AND c2 = 'a2'  ;

示例2

1. 范围之后全失效

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2' AND c3 > 'a3' AND c4 = 'a4'; 

根据索引长度发现在 c3 > ‘a3’ 与之前的都走了索引，但是之后的没有走索引

2. 范围之后居然没失效

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2' AND c4 > 'a4' AND c3 = 'a3' ;

根据执行计划发现，4个列都走了索引，这也是因为优化器帮你更改了顺序，但是写一般建议按顺序写

示例3

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2'  ORDER BY c3; 
EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2' AND c4 > 'a4' ORDER BY c3; 

这个2个执行计划一样，发现没有进行外部排序，而且2个列使用到了索引。c4 > ‘a4’ 没用到那是因为前面没有 c3, 出现了断层了。而没有出现外部排序是因为按顺序使用了复合索引的列。

示例4

出现外部排序了

情况1

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2' AND c4 = 'a4' ORDER BY c3; 

下面执行计划出现了 filesort 了，然后只有 c1 = ‘a1’ AND c2 = ‘a2’ 走了索引，至于为什么出现外部排序，很明显就是之前强调了N遍的，顺序中间断了，没按索引定义的数据来

情况2

-- 没出现
EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1'   ORDER BY c2, c3; 

-- 出现了
EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1'   ORDER BY c3, c2; 

-- 出现了
EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1'   ORDER BY c3, c4; 

发现才 c1 = ‘a1’ 使用了索引，但是一个排序时候按着顺序没出现外部排序。一个顺序断了 排序没走索引导致出现了外部排序。

示例5

情况一

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c5 = 'a5'  ORDER BY c2, c3; 

发现使用了索引，并且也索引排序了。因为使用了 c1 = ‘a1’ 之后，order by 后面就可以按照索引列表去定义排序了

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c5 = 'a5'  ORDER BY c3, c2;

发现 order by 列表没有使用索引列表，所以导致没有filesort。

情况二

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2' ORDER BY c3, c2;

突然后的突然，发现你的三观被颠覆了。为什么上面示例就 filesort 了，但是这里没有。这是因为这里有常量，在排序时候 c3, c2 ，其中c2在下图中以及已经确定了值，所以就相当于：order by c3, ‘a2’，常量排序不排序无所谓，所以就相当于 where c1, c2 order by c3

示例6

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c4 = 'a4' GROUP BY c3, c2;

可以发现，这个sql已经烂成翔了。不仅出现了 外部排序，还出现了临时表。因为分组前必选先排序，而由于 where 后的复合索引顺序已经断了，然后分组时候已经没法再走索引了。