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条件是否可以使用索引来限定一个范围，有了这个查询范围即便是再次回表代价也是可以接受的。