MySQL SQL优化之覆盖索引（索引覆盖）

发表于 | 分类于 ， ，

术语

覆盖索引（索引覆盖）

  • 解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

  • 解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

  • 解释三： 是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。

实战

背景

  • 表结构
1
2
3
4
5
6
7
CREATE TABLE `t_order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_code` char(12) NOT NULL,
  `order_amount` decimal(12,2) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_order_code` (`order_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

在 order_code 上创建了唯一性索引 uni_order_code

  • 数据条数：32W条

  • SQL：select order_code, order_amount from t_order order by order_code limit 1000;

  • 执行计划

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t_order ALL NULL NULL NULL NULL 316350 Using filesort

全表扫描、文件排序，注定查询慢！

那为什么MySQL没有利用索引（uni_order_code）扫描完成查询呢？

因为MySQL认为这个场景利用索引扫描并非最优的结果。我们先来看下执行时间，然后再来分析为什么没有利用索引扫描。

  • 执行时间：260ms

如果表数据量继续增长下去，性能会越来越差。

分析

MySQL为什么使用全表扫描、文件排序，而没有使用索引扫描、利用索引顺序？

  • 全表扫描、文件排序

虽然是全表扫描，但是扫描是顺序的（不管机械硬盘还是 SSD 顺序读写性能都是高的），并且数据量不是特别大，所以这部分消耗的时间应该不是特别大，主要的消耗应该是在排序上。

  • 利用索引扫描、利用索引顺序

uni_order_code 是二级索引，索引上保存了（order_code,id），每扫描一条索引需要根据索引上的 id 定位（随机 IO）到数据行上读取order_amount，需要1000次随机IO才能完成查询，而机械硬盘随机 IO 的效率是极低的（机械硬盘每秒寻址几百次）。

既然我们已经知道是因为随机IO导致无法利用索引，那么有没有办法消除随机IO?

解决办法：覆盖索引。

优化方案

  • 创建索引
1
2
ALTER TABLE `t_order` 
ADD INDEX `idx_ordercode_orderamount` USING BTREE (`order_code` ASC, `order_amount` ASC);

创建了复合索引 idx_ordercode_orderamount(order_code,order_amount)，将 select 的列 order_amount 也放到索引中。

  • 执行计划

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t_order index NULL idx_ordercode_orderamount 42 NULL 1000 Using index

  • 执行时间：13ms

从执行时间来看，SQL 的执行时间提升到原来的 1/20，已经达到我们的预期。

总结

覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。索引的字段不只包含查询列，还包含查询条件、排序等。

要写出性能很好的SQL不仅需要学习SQL，还要能看懂数据库执行计划，了解数据库执行过程、索引的数据结构等。

Refer to

聚集索引：https://www.cnblogs.com/chillsrc/archive/2012/09/04/2671092.html