专栏首页编程拯救世界MySQL 覆盖索引与延迟关联

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,查询轨迹如下:

  1. product_id 索引树中找到 product_id = 1 子结点
  2. 通过该子结点指针读取磁盘上的数据行
  3. 取出数据行中的 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)

MyISAM 索引结构

InnoDB

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

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,表示该查询使用了覆盖索引。该查询语句的查询轨迹如下:

  1. 在二级索引 product_id 的索引树中找到 product_id = 1 的叶子结点
  2. 取出该叶子结点的行主键值 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     | <derived2> | NULL       | ref   | <auto_key0>   | <auto_key0> | 4       | test.o1.id |    2 |   100.00 | Using index |
|  2 | DERIVED     | orders     | NULL       | index | NULL          | PRIMARY     | 4       | NULL       |    2 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-------------+---------+------------+------+----------+-------------+

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

总结

如果使用覆盖索引,MySQL 只需扫描索引,无须回表,这极大地减少了数据访问量,能让查询更快、更高效。

延迟关联(deferred join)是覆盖索引的实际应用,可用于优化分页或其他场景。

参考资料

  • 《高性能 MySQL》[1]

参考资料

[1]

《高性能 MySQL》: https://book.douban.com/subject/23008813/

本文分享自微信公众号 - 编程拯救世界(CodeWarrior_),作者:江子抑

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-09-01

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 搞定面试算法系列 | 贪心算法与正确性归纳证明

    贪心算法就是让计算机模拟一个「贪心的人」来做出决策。这个贪心的人是目光短浅的,他每次总是:

    江不知
  • 开源世界大冒险 | 第 1 期:初识 GitHub

    如果你在 GitHub 上有自己的开源项目或是参与过开源项目,都会成为面试的加分项!

    江不知
  • 图解算法 | 摩尔投票法求多数元素

    摩尔投票法(Boyer–Moore majority vote algorithm),也被称作「多数投票法」,算法解决的问题是:如何在任意多的候选人中(选票无序...

    江不知
  • 面向全栈的技术管理

    在中生代和飞马网的技术嘉年华上,我斗胆披上吹牛的嫌疑,分享了面向全栈的技术管理,现赘述如下。

    半吊子全栈工匠
  • python爬虫小工具--快速获得请求头

    我们在写爬虫脚本的时候经常要获取请求头,但是每次从浏览器粘贴到代码里时,都要费一番功夫来处理格式。

    渔父歌
  • 关于rxjs里operators filter和map的详细讨论

    product$: Observable = this.currentProductService.getProduct().pipe(this.jerryfi...

    Jerry Wang
  • win10无法使用内置管理员账户打开应用怎么办

    Win10管理员账户Administrator默认关闭和隐藏,就是防止这个高权限账户被“滥用”影响系统安全。不过我们有时候也需要开启该账户,以便 完成一些特殊任...

    似水的流年
  • 手把手撸PHP扩展 0x06: 协程创建(二)

    首先,我们需要对传给接口的参数进行解析。解析参数需要使用PHP提供给我们的宏来完成,分别是开头的和结尾的宏:

    桶哥
  • 新一代互联网视频压缩AV1最新进展【2017.8】

    前几天,开放媒体联盟(AOM,Alliance for Open Media)举行了董事会议,对AV1编解码器的发布日期进行了讨论,Netflix和YouTub...

    用户1324186
  • 如何突破Windows环境限制打开“命令提示符”

    如今,许多企业或组织都会通过使用受限的windows环境来减少系统表面的漏洞。系统加固的越好,那么也就意味着能被访问和使用到的功能就越少。 我最近遇到的情况是,...

    FB客服

扫码关注云+社区

领取腾讯云代金券