主页 / dba / 问题 / 342823
Accepted
Sidharth Samant
Asked: 2024-10-06 13:41:51 +0800 CST

即使第一列未被查询，MySQL 也会使用多列索引

  • 772

我有 MySQL 版本 8.0.37。

据我了解，此版本中的多列索引仅当查询包含从第一列开始的所有列的子集时才会被 MySQL 使用。

例如，我的 InnoDB 表中有这个索引

mysql> show indexes from my_table;
+------------------------+------------+------------------------------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name                        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------------------+------------+------------------------------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| my_table |          0 | PRIMARY                         |            1 | id          | A         |       32643 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| my_table |          1 | my_table_entity_id              |            1 | entity_id   | A         |       20160 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| my_table |          1 | my_table_entity_id_sub_id_value |            1 | entity_id   | A         |       18222 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| my_table |          1 | my_table_entity_id_sub_id_value |            2 | sub_id      | A         |       32985 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| my_table |          1 | my_table_entity_id_sub_id_value |            3 | value       | A         |       32545 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+------------------------+------------+------------------------------------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

使用索引，我可以对、 或两者或所有 3 列my_table_entity_id_sub_id_value运行查询。这也是 MySQL文档所说的。entity_identity_idsub_id

但是，这只是explain analyze针对第 2 列和第 3 列的查询的输出，即和sub_id，value并且仍然使用索引。

mysql> explain analyze select distinct entity_id from my_table where sub_id = 107 and value  = 'd90e7a26-2fc5-4e16-87c5-a2e9da5a26f7';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Group (no aggregates)  (cost=3552 rows=330) (actual time=3.52..14.7 rows=3103 loops=1)
    -> Filter: ((my_table.`value` = 'd90e7a26-2fc5-4e16-87c5-a2e9da5a26f7') and (my_table.sub_id = 107))  (cost=3519 rows=330) (actual time=3.44..14.3 rows=3103 loops=1)
        -> Covering index scan on my_table using my_table_entity_id_sub_id_value  (cost=3519 rows=32985) (actual time=0.0741..10.4 rows=33202 loops=1)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.39 sec)

我意识到这是一个“覆盖索引”扫描。我对它们的理解是，它们用于直接从索引中检索值，因此我知道entity_id我所要select查找的是该索引中的哪个值。但是，where仍然只覆盖第 2 列和第 3 列，这就是过滤标准。

我是不是漏掉了什么？关于覆盖索引扫描，我有什么不明白的？

mysql

4 个回答

  1. Best Answer

    因为您的查询没有引用索引的最左边的列，所以它执行覆盖索引扫描，这意味着虽然它能够单独从索引中读取，但它必须读取该索引中的每个条目。

    如果在查询条件中包含索引最左边的列，则会得到索引查找，这允许查询仅检查匹配的条目。

    mysql> explain analyze select distinct entity_id from my_table where entity_id = 123 and sub_id = 107 and value  = 'd90e7a26-2fc5-4e16-87c5-a2e9da5a26f7'\G

*************************** 1. row ***************************
EXPLAIN: -> Group (no aggregates)  (cost=0.45 rows=1) (actual time=0.0187..0.0187 rows=0 loops=1)
    -> Filter: ((my_table.sub_id = 107) and (my_table.`value` = 0))  (cost=0.35 rows=1) (actual time=0.0177..0.0177 rows=0 loops=1)
        -> Index lookup on my_table using entity_id (entity_id=123)  (cost=0.35 rows=1) (actual time=0.017..0.017 rows=0 loops=1)
                 ^^^^^^
    • 8

  2. “覆盖”（又名“使用索引”）意味着所有列都在一个单独的 中找到INDEX。这避免了在索引 BTree 和数据 BTree 之间来回切换，从而提高了性能。

    您找到的引文不完整。它仅提到了“覆盖”有益的最佳情况。（请参阅下面我的第 3 点内容。）

    我期望以下内容：

    • 查看索引中的每一“行”（“覆盖索引扫描”）
    • 不触碰数据BTree。
    • 检查每个索引行是否为value= 'd90e7a26...' 和 sub_id = 107`
    • entity_id从索引 BTree 中收集值。
    • 可能它意识到索引是按 排序的entity_id，因此可以避免重复数据删除。但我无法很好地理解 Explain 以推断出这一点。

    尖端：

    • INDEX(entity_id)与其他索引重复，可以删除。

    • 如果这对搭档entity_id, sub_id很独特，而且除了成为 PK 之外没有其他用处id，那么就摆脱它id，拥有PRIMARY KEY(entity_id, sub_id)

    • 以下方法可以加快您的查询速度：

      `INDEX(sub_id, value,  -- either order
       entity_id)      -- last
    • 6

  3. 数据库实际上可以利用索引查询指定的索引，这可能会提高性能。我不确定 mysql 是否会为您做到这一点。

    诀窍是使用一个索引访问来枚举 的所有可能值entity_id，并使用第二个（或至少一个与第一个值相干扰的）my_table_entity_id_sub_id_value来检查三元组是否在您的表中。如果 的值足够少，entity_id索引查找比完整索引扫描更快，这将节省时间。由于 上有冗余索引entity_id，因此这些信息应该可用。

    您可能能够使用类似以下方法强制此行为：

    select distinct entity_id from my_table 
        where sub_id = 107 
        and value  = 'd90e7a26-2fc5-4e16-87c5-a2e9da5a26f7' 
        and entity_id in (select distinct entity_id from my_table);

    您可能需要缓存内部选择......

    • 1

  4. 它正在进行覆盖索引扫描。根据文档：

    在某些情况下，可以优化查询以检索值而无需查阅数据行。（为查询提供所有必要结果的索引称为覆盖索引。）如果查询仅使用表中某些索引中包含的列，则可以从索引树中检索所选值，以提高速度

    您的查询仅使用该索引中的列。优化器肯定得出了结论，使用该索引并进行扫描比使用其他索引之一进行查找然后直接读取完整数据行更快。我不确定为什么会这样。当然，如果您有一个以 和 开头sub_id但也value包括 的索引entity_id，则会使用该索引，并且将进行查找而不是扫描。

    但是请考虑一下您的表包含非常大的行的情况。假设有 100 个不同的列和一些大字符串或二进制数据。假设它接近最大行大小 64k，并且您的索引每行仅占用 32 个字节。从您的输出猜测，总共有 33,000 行，其中 330 行或 1% 符合您的条件。假设 sub_id 和 value 也有不同的匹配，因此sub_id107 有 10 个不同的value值与之关联，总共有 800 行，而您指定的单个value有 4 个不同的 sub_id 值与之关联，总共有 500 行。

    如果使用 上的索引进行查找sub_id，它会找到需要检查的 800 行。您将获得行列表以及如何在磁盘上访问它们。然后它将加载这 800 行的所有数据，因为它需要的数据多于索引包含的数据。由于该索引未聚集，因此这些行可以分散在磁盘上，如果每行 60k，则每行都需要读取多个扇区，总共 48mb 的数据。然后它必须检查每一个value以根据其他条件进行过滤并捕获唯一entity_id值。
    这些操作对 CPU 缓存不是很友好，因为数据是分散的。未来使用不同 和 值的查询sub_id将value访问磁盘上的数百个其他区域并占用另外的 48mb。这个例子还不算太糟，因为数字很低，但在更大的系统中，最终可能会产生大量数据，而且缓存效果不是很好。

    如果使用包含所有数据的索引进行扫描，则必须加载整个索引。这是 33,000 行，而不是 800 行，但每行要小得多，可能只有 32 字节。它只需要从磁盘读取 1mb，并且可能需要 250 次读取，而不是 13,000 次。缓存 1mb 很容易，扫描这 33,000 行以查找所需数据对 CPU 来说并不难。

    这是一个不完美的类比。假设您有公司 33,000 名员工的档案。主键是 EmployeeID。员工档案包含大量数据，包括地址、电话号码、就业日期、社会保险号、生物特征数据等。每个员工档案占据一整页，并按 EmployeeID（主键）排序存储在文件柜中，以便于访问。

    您的任务是找到蓝眼睛员工的唯一姓氏。您还有另外两个文件柜。文件柜 A 包含按眼睛颜色组织的员工列表。此列表仅包含 EyeColor 和 EmployeeId。找到您要查找的 EmployeeId 值非常快，但对于每个值，您必须转到主文件柜并拉出员工的文件，然后从那里获取姓氏。文件柜 B 包含按姓氏组织的员工列表，但每行都有 LastName、EyeColor 和 EmployeeId。每页可以容纳大约 120 条这样的记录。要完成您的任务，您必须拉出整个 275 页的文件，但您不必查看其他任何内容。您只需扫描每一页并查找 EyeColor = 'blue' 并记录姓氏，因为它就在那里。不用跑到主文件柜来拉出每个文件。

    • 1

相关问题