M

[Mysql] Mysql的覆盖索引与索引下推

RoLingG 其他 2024-04-23

MySQL覆盖索引与索引下推

覆盖索引

简介

MySQL 中的覆盖索引是一种特殊的索引类型，它能够覆盖某个查询的所有需要的字段，而不需要去访问表的实际数据行。这种索引通常用于查询优化，可以减少查询的IO开销和提高查询性能。

具体来说，当一个查询可以完全使用一个索引来获取所需的数据时，MySQL 就可以利用覆盖索引。这样的查询称为覆盖查询。覆盖查询的优势在于，它不需要从磁盘读取表的实际数据行，而是直接从索引中获取所需的数据，因此可以显著减少IO开销和提高查询性能。

要实现覆盖索引，需要确保查询涉及的所有字段都包含在索引中。这样一来，MySQL 就可以直接从索引中获取数据，而不需要去访问表的实际数据行。通常情况下，覆盖索引可以用于包含索引字段的 SELECT 查询、ORDER BY 子句、以及部分 GROUP BY 子句等场景。

对比

覆盖索引和一般索引的主要区别在于它们在查询过程中是否需要访问表的实际数据行。下面是它们之间的区别：

  1. 查询覆盖：

    • 覆盖索引：当一个查询可以完全使用一个索引来获取所需的数据时，就可以利用覆盖索引进行查询。这样的查询称为覆盖查询，它不需要访问表的实际数据行，而是直接从索引中获取数据。
    • 一般索引：一般的索引在执行查询时需要访问表的实际数据行，因为索引只包含了部分或者全部查询条件所涉及的字段，无法完全覆盖查询所需的数据。

  2. IO开销：

    • 覆盖索引：由于覆盖查询不需要访问表的实际数据行，所以可以减少IO开销，提高查询性能。
    • 一般索引：一般索引需要访问表的实际数据行，可能会产生较大的IO开销，查询性能可能会受到影响。

  3. 查询性能：

    • 覆盖索引：覆盖索引可以显著提高查询性能，特别是对于包含大量数据的表和频繁执行的查询。
    • 一般索引：一般索引的查询性能取决于查询条件和索引的选择性，可能会受到表的大小和索引的质量等因素的影响。

例如有一个名为 orders 的表，包含以下字段：

  • order_id：订单ID（主键）
  • customer_id：客户ID
  • order_date：订单日期
  • total_amount：订单总金额

我们希望对 customer_id 和 order_date 字段创建一个覆盖索引，以加速对客户ID和订单日期的查询。

CREATE INDEX idx_covering_index ON orders(customer_id, order_date);

上面的 SQL 语句创建了一个名为 idx_covering_index 的覆盖索引，包含了 customer_id 和 order_date 两个字段。在这个索引中，这两个字段组成了索引的前缀，可以覆盖查询中涉及的这两个字段。

在建立了 customer_id 和 order_date 字段的覆盖索引后，假设我们有以下查询：

SELECT customer_id, order_date 
FROM orders
WHERE customer_id = 12345
  AND order_date BETWEEN '2024-01-01' AND '2024-04-01';

由于覆盖索引包含了查询中涉及的所有字段，MySQL 可以直接从索引中获取所需的数据，而不需要访问表的实际数据行。因此，这个查询可以利用覆盖索引，从而提高查询性能。

使用覆盖索引的时候，有两种情况是不会满足覆盖索引的使用条件：

  • SQL 的 WHERE 条件不合最左前缀匹配原则。即使是索引覆盖也是无法使用到索引的（会扫描索引树）

    例如下面这种找的是customer_id，但是WHERE 的条件是order_date的：

    CREATE INDEX idx_covering_index ON orders(customer_id, order_date);
    SELECT order_id
FROM orders
WHERE order_date = '2024-04-23';

    虽然覆盖索引 idx_covering_index 包含了 order_date 这个字段，但是查询条件只涉及 order_date，并没有涉及到覆盖索引的最左边的字段 customer_id。因此，查询条件不符合最左前缀匹配原则，MySQL 无法利用覆盖索引来加速查询。相反，MySQL 将不得不扫描整个索引树，以查找满足条件的数据行，从而导致性能下降。

  • SQL 查询的字段不属于联合索引

    联合索引（Composite Index）是指针对多个字段建立的索引，也称为复合索引或组合索引。与单列索引不同，联合索引是基于多个列的值来排序和检索数据的。

    例如下面：

    CREATE INDEX idx_covering_index ON orders(customer_id, order_date);
    SELECT customer_id, total_amount
FROM orders
WHERE customer_id = '12345' AND order_date = '2024-04-23';

    虽然覆盖索引 idx_covering_index 包含了 customer_id 和 order_date 这两个字段，但是查询中选择的字段是 customer_id 和 total_amount，total_amount这个字段并没有包含在覆盖索引中。

索引下推

索引下推是 MySQL 在 5.6 中引入的一种优化技术，它允许 MySQL 在索引上执行额外的过滤条件，减少了不必要的数据访问。索引下推在 MySQL 中默认是开启状态的。

索引下推可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

当然，可以通过 set optimizer_switch = index_condition_pushdown = off 进行关闭。

假设我们有一个表 products，包含以下字段：

  • product_id：产品ID（主键）
  • product_name：产品名称
  • category_id：产品分类ID
  • price：产品价格

我们创建了一个联合索引 idx_category_price，包含 category_id 和 price 两个字段。

现在我们想要查询某个特定分类下价格大于100的产品。我们可以这样查询：

SELECT product_id, product_name, price
FROM products
WHERE category_id = 123 AND price > 100;

MySQL 可以利用索引 idx_category_price，先根据 category_id 进行索引扫描定位到符合条件的行，即 MySQL 会在 idx_category_price 索引中扫描 category_id 为 123 的记录。然后再在这些索引内的行中进行价格判断，进一步过滤掉价格不大于100的产品，从而避免了扫描整个表的操作。

如果没有索引下推，MySQL 在执行上面的查询时会先根据 category_id 来定位到符合条件的行，需要回表访问这些符合条件的原始数据行，然后再逐行检查每一行的 price 是否大于100。这意味着即使我们只对价格大于100的产品感兴趣，MySQL 也需要检查所有符合 category_id 条件的行，无论价格是多少。

有了索引下推，MySQL 可以在索引层面完成更多的过滤工作，减少了对表数据的访问，从而提高了查询性能。

综上我们可以看出：

  • 没有了索引下推，就会需要进行回表操作，导致额外的数据访问和处理，从而降低了查询性能。如果查出来有 n 条符合category_id = 123的数据，那么就要回表 n 次去一条条判断它们是否符合price > 100这个条件。
  • 索引下推可以减少回表操作的需求，因为MySQL可以在索引上执行额外的过滤条件，尽可能地满足查询的要求，减少了对原始数据行的访问。因此，索引下推可以提高查询性能，特别是对于涉及到大量数据的查询操作。

注：这里要注意范围索引的联合索引的影响。索引下推要满足联合索引的最左匹配原则的情况下才能使用。

官方其实是只提到了Like，但其实如还是那个面的例子一样，可以不只是Like，索引下推其实是解决索引失效带来的效率低的问题的一种手段。所以在联合索引中，由于某个前导列因为索引失效而要进行扫表并回表时，就可以进行索引下推优化了。

例如：

Select a From b Where c = "123" And d = 123

上面这段 SQL 语句中c和d都是varchar类型，但可以明显的看出d的类型值是错的，导致了字段类型不匹配的问题，使得索引失效。但这时候因为有索引下推，SQL 可以通过c进行索引下推优化其实是可以减少回表的次数的。

补充

回表：当 MySQL 无法通过索引直接获取所需的数据，而需要再次访问表中的原始数据行时发生的操作。

在 InnoDB 里，索引 B+ Tree 的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。而索引 B+ Tree 的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引。

在存储的数据方面，主键（聚簇）索引的 B+ Tree 的叶子节点直接是我们要查询的整行数据了。而非主键（非聚簇）索引的 B+ Tree 的叶子节点是主键的值。

当我们根据非聚簇索引查询的时候，会先通过非聚簇索引查到主键的值，之后，还需要再通过主键的值再进行一次查询才能得到我们要查询的数据。而这个过程就叫做回表。

也就是说聚簇索引是在主键上，我们通过非聚簇索引找到主键的值，再通过聚簇索引根据这个主键获取一整行的数据。

所以，在 InnoDB 中，使用主键查询的时候，是效率更高的， 因为这个过程不需要回表。另外，依赖覆盖索引、索引下推等技术，我们也可以通过优化索引结构以及 SQL 语句减少回表的次数。

暂无标签
PREV
[Redis学习] Redis集群相关模式
NEXT
[算法学习] 二维区域和检索 - 矩阵不可变

评论(0)

发布评论