MySQL 覆盖索引 —— by 久米泷

释义

在查询SQL中，所有的待查字段都有索引，即称为“覆盖索引”（Covering Index）。

示例

表结构：

CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL COMMENT 'PK',
  `name` varchar(16) NOT NULL COMMENT '姓名',
  `age` int(4) NULL DEFAULT 18 COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
);

• 当执行 explain select id, name from t_user ，Extra 字段为 Using index，表示使用了覆盖索引
• 当执行 explain select id, name, age from t_user ，Extra 字段为 NULL，因为 age 字段没有索引。

优势

由于MySQL的索引分为”聚簇索引“（Clustered Index，默认是主键）和”二级索引“（Secondary Index，也叫非聚簇索引），其中聚簇索引节点包含了所有字段数据，而二级索引节点只包含当前字段和聚簇索引字段。所以当只命中二级索引时，就会发生“回表” —— 先遍历二级索引树找到聚簇索引值，再遍历聚簇索引树找到待查字段的值。

为了解决两次遍历带来的性能损耗，覆盖索引粉墨登场。结合前文的示例，name 字段有二级索引，并且在该索引节点上也包含了聚簇索引字段 id 的值，所以无需回表就能查询到 id 和 name。

mysql-index-tree.png

应用场景

• 大表分页：当分页查询大数据量的表时，越接近尾页会越慢，此时可以通过覆盖索引先只查询主键或有索引的字段，然后再根据索引字段查询完整数据。例如：

-- 先只查询id和name
select id, name from t_user limit 5000, 10;

-- 再完善数据
select * from t_user where id in (...);