MySQL(一)：详解使用索引扫描来做排序

一、MySQL生成有序结果的方式

1）直接通过排序操作

2）按索引顺序扫描

如果explain执行计划的type列值为index，这说明mysql使用了索引扫描来做排序。

二、索引扫描的特点

扫描索引本身是很快的，因为只需要顺序从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那么每扫描到一条索引记录就得进行随机IO回表查询一次对应的行；因此在这种情况下，按索引顺序读取数据的速度通常要比顺序地全表扫描慢，因为顺序IO速度远快于随机IO。

1. 多表查询时

只有当索引的列顺序和order by 子句的顺序完全一致、并且所有列的排序方式都一样时，mysql才能够使用索引来对结果进行排序。如果查询需要关联多张表时，这只有当order by 子句引用的字段全部为第一张表时，才能使用索引做排序。

order by 子句和查找型查询的限制是一样的，需要满足索引的最左前缀要求，否则mysql都需要执行顺序操作、无法利用索引排序。

三、样例

1. 表结构

DROP TABLE IF EXISTS `rental`;
CREATE TABLE `rental`  (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime(0) NOT NULL,
  `inventory_id` mediumint(8) UNSIGNED NOT NULL,
  `customer_id` smallint(5) UNSIGNED NOT NULL,
  `return_date` datetime(0) NULL DEFAULT NULL,
  `staff_id` tinyint(3) UNSIGNED NOT NULL,
  `last_update` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`rental_id`) USING BTREE,
  UNIQUE INDEX `rental_date`(`rental_date`, `inventory_id`, `customer_id`) USING BTREE,
  INDEX `idx_fk_inventory_id`(`inventory_id`) USING BTREE,
  INDEX `idx_fk_customer_id`(`customer_id`) USING BTREE,
  INDEX `idx_fk_staff_id`(`staff_id`) USING BTREE,
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 16050 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

2. 数据

INSERT INTO `rental` VALUES (1, '2005-05-24 22:53:30', 367, 130, '2005-05-26 22:04:30', 1, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (2, '2005-05-24 22:54:33', 1525, 459, '2005-05-28 19:40:33', 1, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (3, '2005-05-24 23:03:39', 1711, 408, '2005-06-01 22:12:39', 1, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (4, '2005-05-24 23:04:41', 2452, 333, '2005-06-03 01:43:41', 2, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (5, '2005-05-24 23:05:21', 2079, 222, '2005-06-02 04:33:21', 1, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (6, '2005-05-24 23:08:07', 2792, 549, '2005-05-27 01:32:07', 1, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (7, '2005-05-24 23:11:53', 3995, 269, '2005-05-29 20:34:53', 2, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (8, '2005-05-24 23:31:46', 2346, 239, '2005-05-27 23:33:46', 2, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (9, '2005-05-25 00:00:40', 2580, 126, '2005-05-28 00:22:40', 1, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (10, '2005-05-25 00:02:21', 1824, 399, '2005-05-31 22:44:21', 2, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (11, '2005-05-25 00:09:02', 4443, 142, '2005-06-02 20:56:02', 2, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (12, '2005-05-25 00:19:27', 1584, 261, '2005-05-30 05:44:27', 2, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (13, '2005-05-25 00:22:55', 2294, 334, '2005-05-30 04:28:55', 1, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (14, '2005-05-25 00:31:15', 2701, 446, '2005-05-26 02:56:15', 1, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (15, '2005-05-25 00:39:22', 3049, 319, '2005-06-03 03:30:22', 1, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (16, '2005-05-25 00:43:11', 389, 316, '2005-05-26 04:42:11', 2, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (17, '2005-05-25 01:06:36', 830, 575, '2005-05-27 00:43:36', 1, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (18, '2005-05-25 01:10:47', 3376, 19, '2005-05-31 06:35:47', 2, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (19, '2005-05-25 01:17:24', 1941, 456, '2005-05-31 06:00:24', 1, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (20, '2005-05-25 01:48:41', 3517, 185, '2005-05-27 02:20:41', 2, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (21, '2005-05-25 01:59:46', 146, 388, '2005-05-26 01:01:46', 2, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (22, '2005-05-25 02:19:23', 727, 509, '2005-05-26 04:52:23', 2, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (23, '2005-05-25 02:40:21', 4441, 438, '2005-05-29 06:34:21', 1, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (24, '2005-05-25 02:53:02', 3273, 350, '2005-05-27 01:15:02', 1, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (25, '2005-05-25 03:21:20', 3961, 37, '2005-05-27 21:25:20', 2, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (26, '2005-05-25 03:36:50', 4371, 371, '2005-05-31 00:34:50', 1, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (27, '2005-05-25 03:41:50', 1225, 301, '2005-05-30 01:13:50', 2, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (28, '2005-05-25 03:42:37', 4068, 232, '2005-05-26 09:26:37', 2, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (29, '2005-05-25 03:47:12', 611, 44, '2005-05-30 00:31:12', 2, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (30, '2005-05-25 04:01:32', 3744, 430, '2005-05-30 03:12:32', 1, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (31, '2005-05-25 04:05:17', 4482, 369, '2005-05-30 07:15:17', 1, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (32, '2005-05-25 04:06:21', 3832, 230, '2005-05-25 23:55:21', 1, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (33, '2005-05-25 04:18:51', 1681, 272, '2005-05-27 03:58:51', 1, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (34, '2005-05-25 04:19:28', 2613, 597, '2005-05-29 00:10:28', 2, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (35, '2005-05-25 04:24:36', 1286, 484, '2005-05-27 07:02:36', 2, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (36, '2005-05-25 04:36:26', 1308, 88, '2005-05-29 00:31:26', 1, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (37, '2005-05-25 04:44:31', 403, 535, '2005-05-29 01:03:31', 1, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (38, '2005-05-25 04:47:44', 2540, 302, '2005-06-01 00:58:44', 1, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (39, '2005-05-25 04:51:46', 4466, 207, '2005-05-31 03:14:46', 2, '2006-02-15 21:30:53');
INSERT INTO `rental` VALUES (40, '2005-05-25 05:09:04', 2638, 413, '2005-05-27 23:12:04', 1, '2006-02-15 21:30:53');

3. 索引结构

4. 使用索引排序demo

1）使用组合索引进行查询排序1

explain select rental_id,staff_id from rental where rental_date='2005-05-25' order by inventory_id,customer_id;

虽然order by 子句不满足索引的最左前缀要求，但是因为组合索引（rental_date, inventory_id, customer_id）中的rental_date字段被指定为一个常数。

mysql> explain select rental_id,staff_id from rental where rental_date='2005-05-25' order by inventory_id,customer_id;
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | rental | NULL       | ref  | rental_date   | rental_date | 5       | const |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

从explain执行计划中可以看出，Extra参数的值为Using index condition，也就是使用到了索引扫描进行排序。

2）使用组合索引进行查询排序2

explain select rental_id,staff_id from rental where rental_date='2005-05-25' order by inventory_id desc;

组合索引（rental_date, inventory_id, customer_id）中的第一列被指定为一个常数，第二列做为排序条件；将两列组合在一起，也形成了索引的最左前缀。

mysql> explain select rental_id,staff_id from rental where rental_date='2005-05-25' order by inventory_id desc;
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | rental | NULL       | ref  | rental_date   | rental_date | 5       | const |    1 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

从explain执行计划中可以看出，Extra参数的值为Using where，说明索引被用来执行索引键值的查找。

3）不走索引扫描排序的情况1

explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by rental_date, inventory_id;

组合索引（rental_date, inventory_id, customer_id）中的第一列被指定为一个常数，第一列和第三列做为排序条件；违背索引的最左前缀要求，索引不会走索引扫描进行排序。

mysql> explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by rental_date,inventory_id;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | rental | NULL       | ALL  | rental_date   | NULL | NULL    | NULL | 16008 |    50.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

从explain执行计划中可以看出，Extra参数的值为Using where; Using filesort，说明索引被用来执行索引键值的查找，但进行了全表扫描排序；所以type参数的值是ALL。

4）不走索引扫描排序的情况2

explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by inventory_id desc,customer_id asc;

order by 子句中包含两种不同的排序方向，但索引列排序都是正序排序的。

mysql> explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by inventory_id desc,customer_id asc;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | rental | NULL       | ALL  | rental_date   | NULL | NULL    | NULL | 16008 |    50.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

从explain执行计划中可以看出，Extra参数的值为Using where; Using filesort，说明索引被用来执行索引键值的查找，但进行了全表扫描排序；所以type参数的值是ALL。

5）不走索引扫描排序的情况3

explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by inventory_id,staff_id;

order by 子句 或 select子句中引用了一个不是索引的列。

mysql> explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by inventory_id,staff_id;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | rental | NULL       | ALL  | rental_date   | NULL | NULL    | NULL | 16008 |    50.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

从explain执行计划中可以看出，Extra参数的值为Using where; Using filesort，说明索引被用来执行索引键值的查找，但进行了全表扫描排序；所以type参数的值是ALL。

**总结：**可以适当使用索引扫描做排序进而进行SQL查询优化。