OrderBy使用索引的情況分析

一、Order by是否使用索引？

在某些情況下，MySQL可以使用索引來滿足子句，并避免執(zhí)行操作時(shí)涉及的額外排序。即使與索引不完全匹配，也可以使用索引，只要索引的所有未使用部分和所有額外列都是子句中的常量。如果索引不包含查詢?cè)L問的所有列，則僅當(dāng)索引訪問比其他訪問方法代價(jià)低時(shí)才使用索引。也就是說如果查詢的結(jié)果中包含了索引中包含的字段以外的字段時(shí)則不使用索引。

二、創(chuàng)建表

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索引中所以無法使用索引，此時(shí)需要進(jìn)行文件排序，即使Extra: Using filesort。

四、使用索引場(chǎng)景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、分析

此時(shí)可以使用索引，因?yàn)椴樵兊淖侄沃邪亩及?code>idx_name_age索引中。

五、使用索引場(chǎng)景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、分析

此時(shí)排序也可以使用索引，由于查詢的字段都是包含在索引中，雖然排序的字段并非索引中的第一個(gè)字段，但是使用索引對(duì)age進(jìn)行排序的代價(jià)還是比純使用文件排序要小，所以可以使用索引。但是不能完全依賴索引還是需要文件排序來輔助。

六、使用索引場(chǎng)景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是符合索引條件下推的，可以直接將需要的記錄查詢出來并且排序即可。但是這個(gè)過程需要進(jìn)行回表操作。

索引中斷不能使用索引：

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)

七、總結(jié)

在order by子句中能否使用索引需要考慮兩個(gè)問題就可以了。第一個(gè)就是select后面的字段列表是否可以直接從索引中獲得全部字段，另外一個(gè)就是where條件是否可以使用索引來限定一個(gè)范圍，有了這個(gè)查詢范圍即便是再次回表代價(jià)也是可以接受的。