一、SQL

1.1、创建表

CREATE TABLE test03(
id INT PRIMARY KEY NOT NULL auto_increment,
c1 CHAR(10),
c2 CHAR(10),
c3 CHAR(10),
c4 CHAR(10),
c5 CHAR(10)
)

1.2、添加数据

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')

1.3、创建索引

CREATE INDEX idx_test03_c1234 ON test03(c1, c2, c3, c4)

二、ORDER BY

2.1、全索引查询

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

全索引查询.png

2.2、ORDER BY - 索引断链

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

image.png

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

ORDER BY.png

• c3没有起到查询作用
• c3的作用只起到了排序

2.3、ORDER BY - 连续索引

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c2 = 'a2'

image.png

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

image.png

2.4、ORDER BY - 索引跳隔

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

image.png

• 索引链中索引有间隔时出现 filesort

2.5、ORDER BY - 索引顺序跌倒

2.5.1、ORDER BY中索引顺序执行

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

ORDER BY中索引顺序执行.png

2.5.2、ORDER BY中索引顺序跌倒

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

ORDER BY中索引顺序跌倒.png

2.5.3、WHERE 中索引顺序跌倒

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

image.png

• 在 WHERE 条件总 索引顺序不会影响效率
• 在 ORDER BY 中 索引的顺序会影响效率

2.5.4、WHERE 中索引与参与ORDER BY

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

WHERE 中索引与参与ORDER BY.png

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

image.png

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

image.png

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c3 = 'c3' ORDER BY c3, c4

image.png

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c3 = 'c3' ORDER BY c4, c3

image.png

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c3 = 'c3' ORDER BY c3

image.png

EXPLAIN SELECT * FROM test03 WHERE c1 = 'a1' AND c3 = 'c3' ORDER BY c4

image.png

• 在索引没有缺失的情况下
  • ORDER BY 中的顺序跌倒不会导致filesort
• 在索引缺失的情况下
  • ORDER BY中索引缺失，会造成 filesort
  • WHERE 中 索引缺失，在有ORDER BY情况下有可能也会造成 filesort