覆盖索引

这是我参与11月更文挑战的第14天，活动详情查看：2021最后一次更文挑战

当一个索引包含了所有需要查询出来的列，那么这个索引就被称为“覆盖索引”，对应的查询被称为“索引覆盖查询”。覆盖索引的好处显而易见，直接从索引中返回查询结果，不需要进行回表。由于覆盖索引需要存储索引列的值，因此只有B树类型的索引才能作为覆盖索引。

以下面的SQL为例，

explain select col1,col2 from layout_test;

如果col1和col2是索引列，那么执行计划Extra列的值为Using index，表示这是一个索引覆盖查询；但如果为只有col1是索引列，那么Extra的结果不会像前者一样，在实际的查询中出现了回表操作。

使用延迟关联查询应用覆盖索引

对于下面的SQL，由于被查询的列是所有列，因此无法使用覆盖索引

EXPLAIN
SELECT *
FROM account_batch
where source_system = 'JL_MANUAL_PRC'
  and source_system_name like '%酒旅手工%';

其Extra为Using where，这表示表示优化器需要通过索引回表查询数据，如果我们将索引列扩展至（source_system、source_system_name、id），使用如下查询代替

EXPLAIN
SELECT *
FROM account_batch
         JOIN (SELECT id
               FROM account_batch
               WHERE source_system = 'JL_MANUAL_PRC'
                 and source_system_name like '%酒旅手工%') AS t1
              ON t1.id = account_batch.id;

可以发现查询结果为Using where; Using index，这样通过延迟关联使得第一阶段可以使用覆盖索引，然后根据第一阶段查询出的结果匹配出需要的所有列值。

使用“延迟关联查询”的优化效果需要区分场景来看

1. 对于返回很大结果集的查询来说，大部分时间在读取和发送数据上，优化效果不明显。
2. 对于数据集很小的查询，子查询带来的成本反而比从表中直接提取完整行更高。
3. 对于数据集大但第一阶段返回的数据集较小，才能获得较好的优化效果。