MySQL--索引的优化--ORDER BY

简介

说明

本文介绍MySQL的ORDER BY索引的优化方案。

ORDER BY的两种实现方法

1. 1. 
      
2. 利用有序索引自动实现。（利用有序索引的有序性而不再另做排序了（速度快））

1. 也就是explain结果的Using index

2. 把结果选好之后再排序。（速度慢）

1. 也就是explain结果的Using filesort

3. 
   

是否走索引的情景

查询和排序都走索引的情景

1. 1. 
      
2. SELECT字段与ORDER BY字段完全相同（或者组成联合索引）。

1. 例：SELECT col1 FROM tb1 ORDER BY col1;

2. WHERE字段与ORDER BY字段完全相同（或者组成联合索引）。

1. 例：SELECT col1 FROM tb1 WHERE col2=2 ORDER BY col2;

3. 
   

查询不走索引的情景

1. 1. 
      
2. SELECT字段不包含 ORDER BY 字段。

1. 例：SELECT col1 FROM tb1 ORDER BY col2

2. SELECT字段包含 ORDER BY 字段 + 其他字段。

1. 例：SELECT col1,col3 FROM tb1 ORDER BY col2

3. WHERE字段不包含 ORDER BY 字段。

1. 例：SELECT col1 FROM tb1 WHERE col2=2 ORDER BY col3

4. 
   

实例

DROP TABLE IF EXISTS test;
CREATE TABLE test(
    id int primary key auto_increment,
    c1 varchar(10),
    c2 varchar(10),
    c3 varchar(10),
    c4 varchar(10),
    c5 varchar(10)
) ENGINE=INNODB default CHARSET=utf8;

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

CREATE INDEX idx_c1234 ON test(c1,c2,c3,c4);

结果：

等值查询

案例1：SELECT *，WHERE遵循最左前缀，WHERE与ORDER BY遵循最左前缀

EXPLAIN SELECT * FROM test WHERE c1 = 'b1' AND c2 = 'b2' ORDER BY c3;

结果：查询走索引；排序走索引

案例2：SELECT *，WHERE不遵循最左前缀，ORDER BY不遵循最左前缀，WHERE与ORDER BY不遵循最左前缀

EXPLAIN SELECT * FROM test WHERE c2 = 'b2' AND c3 = 'b3' ORDER BY c4;

结果：查询不走索引；排序不走索引

案例3：SELECT *，WHERE不遵循最左前缀，ORDER BY遵循最左前缀，WHERE与ORDER BY不遵循最左前缀

EXPLAIN SELECT * FROM test WHERE c2 = 'b2' AND c3 = 'b3' ORDER BY c1;

结果：查询不走索引；排序不走索引（跟上边一样）

案例4：SELECT与ORDER BY相同，WHERE不遵循最左前缀，ORDER BY遵循最左前缀，WHERE与ORDER BY不遵循最左前缀

EXPLAIN SELECT c1 FROM test WHERE c2 = 'b2' AND c3 = 'b3' ORDER BY c1;

结果：查询不走索引；排序走索引（覆盖索引）

范围查询

案例1：SELECT *，且WHERE与ORDER BY不一样

EXPLAIN SELECT * FROM test WHERE c2 > 'b1' ORDER BY c1;

结果：查询不走索引；排序不走索引

案例2：SELECT 不是*，WHERE与ORDER BY不一样，ORDER BY最左前缀

EXPLAIN SELECT c3 FROM test WHERE c2 > 'b1' ORDER BY c1;

结果：查询不走索引，排序走索引。这种情况相当于只有排序使用索引

（注意：将c3换成c1,c2等，结果都是一样的）

案例3：SELECT *，WHERE与ORDER BY一样且遵循最左

EXPLAIN SELECT * FROM test WHERE c1 > 'b1' ORDER BY c1;

结果：查询走索引；排序不用操作，直接用查询的结果即可

案例4：SELECT *，WHERE与ORDER BY不一样且遵循最左

EXPLAIN SELECT * FROM test WHERE c1 > 'b1' ORDER BY c2;

结果：查询走索引；排序不走索引

其他网址

​​Mysql 在 order by 时索引的使用机制 - 程序员大本营​​​