MySQL索引有序性分析

说明：本文中所有叙述均基于MySQL 5.6版本 ，Innodb引擎 ，数据库隔离级别为可重复读

业务需求中经常会遇到排序要求，可以使用SQL提供的order by关键字解决。利用索引有序性，可以提升order by的效率，避免出现file_sort。

在阿里的编程规范中特别有一条对于索引有序性的规范

本文主要针对这条规范进行深入说明及示例验证。

DROP TABLE IF EXISTS `test4`;

CREATE TABLE `test4` (
  `a` int NOT NULL,
  `b` int NOT NULL,
  `c` int  NOT NULL,
  `d` int  NOT NULL,
  KEY `ind_test4` (`a`,`b`,`c`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `test4` (`a`, `b`, `c`,`d`) VALUES(1,2,3,4);
INSERT INTO `test4` (`a`, `b`, `c`,`d`) VALUES(5,6,7,8);
INSERT INTO `test4` (`a`, `b`, `c`,`d`) VALUES(9,10,11,12);
INSERT INTO `test4` (`a`, `b`, `c`,`d`) VALUES(13,14,15,16);
INSERT INTO `test4` (`a`, `b`, `c`,`d`) VALUES(17,18,19,20);

以上述test4表为例

示例	结果	备注
SELECT * FROM test4  WHERE a=1 ORDER BY c	USING filesort
SELECT * FROM test4  WHERE a=1 ORDER BY a,c	USING filesort
SELECT * FROM test4  WHERE a IN (1,5) ORDER BY b	USING filesort
SELECT * FROM test4  WHERE a>1 ORDER BY a	USING filesort
SELECT * FROM test4  WHERE a>1 ORDER BY b	USING filesort
SELECT * FROM test4  WHERE a=1 AND b>1 ORDER BY c	USING filesort
SELECT * FROM test4  WHERE a=1 ORDER BY a	无需filesort
SELECT * FROM test4  WHERE a=1 ORDER BY b	无需filesort
SELECT * FROM test4 WHERE a=1 ORDER BY b,c	无需filesort
SELECT * FROM test4  WHERE a=1 ORDER BY a,b	无需filesort
SELECT * FROM test4  WHERE a=1 ORDER BY a,b,c	无需filesort
SELECT * FROM test4  WHERE a=1 AND b>1 ORDER BY b	无需filesort

规律：

1、idx_a_b_c，那么如何确定某个字段是有序的呢？a在索引的最前面，肯定是有序的，b在第二个位置，只有在a唯一确定一个值的时候，b才是有序的，如果a有多个值，那么b 将不一定有序，同理，c也是类似