mysql延迟关联为什么快_MySQL 覆盖索引与延迟关联详解

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

id

int

product_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

COLLATE

DEFAULT

NULL

COMMENT

'订单名称'

KEY

`id`

KEY

`product_idx`

`product_id`

USING

ENGINE

InnoDB

DEFAULT

CHARSET

COLLATE

覆盖索引

什么是覆盖索引？

根据索引

不包含

覆盖索引

MyISAM

product_id

SELECT product_id FROM orders

EXPLAIN

SELECT

FROM

----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+

----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+

----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+

set

0.00

id

SELECT id, product_id FROM orders WHERE product_id = 1

product_id

product_id = 1

通过该子结点指针读取磁盘上的数据行

id

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

EXPLAIN

SELECT

id

FROM

WHERE

1

----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

set

0.00

MyISAM 索引结构

InnoDB

二级索引的叶子结点保存着行的主键值

InnoDB 二级索引的叶子结点包含行主键值

SELECT id, product_id FROM orders WHERE product_id = 1

EXPLAIN

SELECT

id

FROM

WHERE

1

----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+

----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+

----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+

set

0.01

Extra

Using index

product_id

product_id = 1

id

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

延迟关联

deferred join

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

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

用延迟关联优化分页(LIMIT)

LIMIT

LIMIT 10000, 20

EXPLAIN

SELECT

FROM

LIMIT

10000

20

----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+

----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+

----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+

尽可能使用索引覆盖扫描，而不是查询所有列

EXPLAIN

SELECT

FROM

AS

JOIN

SELECT

id

FROM

LIMIT

10000

20

AS

ON

----+-------------+------------+------------+-------+---------------+-------------+---------+------------+------+----------+-------------+

----+-------------+------------+------------+-------+---------------+-------------+---------+------------+------+----------+-------------+

----+-------------+------------+------------+-------+---------------+-------------+---------+------------+------+----------+-------------+

这样一来，MySQL 在 SQL 语句的「内层」进行扫描时使用了覆盖索引，「外层」再通过索引树找到相关的数据行，直接减少了扫描的数据量。

总结

只需扫描索引，无须回表

deferred join

参考资料

《高性能 MySQL》

[1]

参考资料

[1]

https://book.douban.com/subject/23008813/

更多阅读

5分钟掌握在 Cython 中使用 C++

5 分钟掌握 Python 中常见的配置文件

5 分钟掌握 Python 中的 Hook 钩子函数

点击下方阅读原文加入

社区会员