OrderBy使用索引的情况分析
一、Order by是否使用索引？
在某些情况下，MySQL可以使用索引来满足子句，并避免执行操作时涉及的额外排序。即使与索引不完全匹配，也可以使用索引，只要索引的所有未使用部分和所有额外列都是子句中的常量。如果索引不包含查询访问的所有列，则仅当索引访问比其他访问方法代价低时才使用索引。也就是说如果查询的结果中包含了索引中包含的字段以外的字段时则不使用索引。
二、创建表
DROP TABLE IF EXISTS `t_orderby`;
CREATE TABLE `t_orderby` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
`address` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_name_age`(`name`, `age`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t_orderby
-- ----------------------------
INSERT INTO `t_orderby` VALUES (1, 'zhangsan', 18, '北京');
INSERT INTO `t_orderby` VALUES (2, 'zhangsan2', 20, '北京');
INSERT INTO `t_orderby` VALUES (3, 'zhangsan3', 23, '北京');
INSERT INTO `t_orderby` VALUES (4, 'zhangsan4', 13, '北京');
INSERT INTO `t_orderby` VALUES (5, 'zhangsan5', 19, '北京');
INSERT INTO `t_orderby` VALUES (6, 'zhangsan6', 30, '北京');
INSERT INTO `t_orderby` VALUES (7, 'lisi1', 30, '北京');
INSERT INTO `t_orderby` VALUES (8, 'lisi2', 32, '北京');
INSERT INTO `t_orderby` VALUES (9, 'lisi3', 12, '北京');
INSERT INTO `t_orderby` VALUES (10, 'lisi4', 19, '北京');
INSERT INTO `t_orderby` VALUES (11, 'wangwu', 14, '北京');
INSERT INTO `t_orderby` VALUES (12, 'wangwu2', 18, '北京');
INSERT INTO `t_orderby` VALUES (13, 'wangwu3', 17, '北京');
INSERT INTO `t_orderby` VALUES (14, 'wangwu4', 47, '北京');
INSERT INTO `t_orderby` VALUES (15, 'wangwu5', 37, '北京');
INSERT INTO `t_orderby` VALUES (16, 'wangwu6', 38, '北京');
三、不使用索引情况
1、sql语句
mysql> explain select * from t_orderby order by name \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_orderby
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2090301
filtered: 100.00
Extra: Using filesort
1 row in set, 1 warning (0.00 sec)
2、分析
由于select中使用了*需要查询表中的所有字段，由于address字段是不包含在idx_name_age索引中所以无法使用索引，此时需要进行文件排序，即使Extra: Using filesort。
四、使用索引场景1
1、SQL语句
mysql> explain select id,name,age from t_orderby order by name \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_orderby
partitions: NULL
type: index
possible_keys: NULL
key: idx_name_age
key_len: 38
ref: NULL
rows: 2090301
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
2、分析
此时可以使用索引，因为查询的字段中包含的都包含在idx_name_age索引中。
五、使用索引场景2
1、SQL语句
mysql> explain select id,name,age from t_orderby order by age \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_orderby
partitions: NULL
type: index
possible_keys: NULL
key: idx_name_age
key_len: 38
ref: NULL
rows: 2090301
filtered: 100.00
Extra: Using index; Using filesort
1 row in set, 1 warning (0.00 sec)
2、分析
此时排序也可以使用索引，由于查询的字段都是包含在索引中，虽然排序的字段并非索引中的第一个字段，但是使用索引对age进行排序的代价还是比纯使用文件排序要小，所以可以使用索引。但是不能完全依赖索引还是需要文件排序来辅助。
六、使用索引场景3
1、SQL语句
mysql> explain select * from t_orderby where name='zhangsan' order by age \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_orderby
partitions: NULL
type: ref
possible_keys: idx_name_age
key: idx_name_age
key_len: 33
ref: const
rows: 277562
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
2、分析
此语句虽然在select后使用了*查询的字段并非索引中包含的全部字段，但是where条件中使用了name，order by中使用了age是符合索引条件下推的，可以直接将需要的记录查询出来并且排序即可。但是这个过程需要进行回表操作。
索引中断不能使用索引：
mysql> explain select * from t_orderby where name>'zhangsan' order by age \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_orderby
partitions: NULL
type: ALL
possible_keys: idx_name_age
key: NULL
key_len: NULL
ref: NULL
rows: 2090301
filtered: 50.00
Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)
七、总结
在order by子句中能否使用索引需要考虑两个问题就可以了。第一个就是select后面的字段列表是否可以直接从索引中获得全部字段，另外一个就是where条件是否可以使用索引来限定一个范围，有了这个查询范围即便是再次回表代价也是可以接受的。
