MySQL 覆盖索引与延迟关联详解-技术圈

本期来谈谈覆盖索引与延迟关联。在此之前，我们先简单建立一个订单表 Orders 用于举例说明。表中共包含 3 个字段：

id：订单 ID，int 类型，主键自增长
product_id：商品 ID，在此列上建立索引
name：订单名称

CREATE TABLE `orders` (
  `id` int(10) NOT NULL COMMENT '订单 ID',
  `product_id` int(10) DEFAULT NULL COMMENT '商品 ID',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '订单名称',
  PRIMARY KEY (`id`),
  KEY `product_idx` (`product_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

覆盖索引

什么是覆盖索引？

我们知道，如果 MySQL 根据索引查找到数据，但索引的叶子结点中并不包含我们所需要的数据字段，那么仍然需要进行回表查询。

如果一个索引包含（覆盖）我们所需要查询的所有字段值，我们就称之为「覆盖索引」。

MyISAM

当使用 MyISAM 存储引擎时，由于我们在 product_id 建立了索引，所以 SELECT product_id FROM orders 将使用覆盖索引：

mysql> EXPLAIN SELECT product_id FROM orders;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | index | NULL          | product_idx | 5       | NULL |    2 |   100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set (0.00 sec)

如果我们在查询字段中加入 id 列，即执行 SELECT id, product_id FROM orders WHERE product_id = 1，查询轨迹如下：

在 product_id 索引树中找到 product_id = 1 子结点
通过该子结点指针读取磁盘上的数据行
取出数据行中的 id 字段

由于 MyISAM 的叶子结点存储着指向数据行的指针，该查询多了一步回表操作，无法使用覆盖索引。

mysql> EXPLAIN SELECT id, product_id FROM orders WHERE product_id = 1;
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders | NULL       | ref  | product_idx   | product_idx | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set (0.00 sec)

InnoDB

InnoDB 与 MyISAM 的不同之处在于，InnoDB 的主键使用聚簇索引，而其二级索引的叶子结点保存着行的主键值。也就是说，二级索引不仅能覆盖其本身，也能覆盖到该行的主键值。

由于 InnoDB 不同的数据存储方式，若使用 InnoDB 作为存储引擎，我们执行 SELECT id, product_id FROM orders WHERE product_id = 1 将得到如下结果：

mysql> EXPLAIN SELECT id, product_id FROM orders WHERE product_id = 1;
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ref  | product_idx   | product_idx | 5       | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
1 row in set (0.01 sec)

可以看到 Extra 显示 Using index，表示该查询使用了覆盖索引。该查询语句的查询轨迹如下：

在二级索引 product_id 的索引树中找到 product_id = 1 的叶子结点
取出该叶子结点的行主键值 id 一并返回

查询轨迹并未进行回表取值。

延迟关联

延迟关联（deferred join）指「延迟了对列的访问」，不直接获取所有需要的列。

在查询的第一阶段 MySQL 使用覆盖索引，再通过该覆盖索引查询到的结果到外层查询匹配需要的所有列值。

这样说有些抽象，我们来看看下面的例子。

用延迟关联优化分页（LIMIT）

当使用 LIMIT 碰上较大偏移量时，例如 LIMIT 10000, 20 这样的查询，MySQL 需要查询 10020 条记录然后再返回最后的 20 条。前面的 10000 最终都会被抛弃，这样的代价非常高。

mysql> EXPLAIN SELECT * FROM orders LIMIT 10000, 20;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+

优化此类分页查询的一个最简单的办法就是尽可能使用索引覆盖扫描，而不是查询所有列。然后根据需要再做一次关联，返回所需要的列。

mysql> EXPLAIN SELECT * FROM orders AS o1 JOIN (SELECT id FROM orders LIMIT 10000, 20) AS o2 ON o1.id = o2.id;
+----+-------------+------------+------------+-------+---------------+-------------+---------+------------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key         | key_len | ref        | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-------------+---------+------------+------+----------+-------------+
|  1 | PRIMARY     | o1         | NULL       | ALL   | PRIMARY       | NULL        | NULL    | NULL       |    2 |   100.00 | NULL        |
|  1 | PRIMARY     |  | NULL       | ref   |    |  | 4       | test.o1.id |    2 |   100.00 | Using index |
|  2 | DERIVED     | orders     | NULL       | index | NULL          | PRIMARY     | 4       | NULL       |    2 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-------------+---------+------------+------+----------+-------------+