深入剖析数据库索引-Composite Index（复合索引）

时间：2024-07-11 07:18:04

MySQL 允许您在多个列上定义索引，最多可以是 16 列。这种索引称为多列索引、复合索引或组合索引。

假设我们在 4 列上定义了一个索引 — col1、col2、col3、col4。使用复合索引，我们可以在 col1、(col1, col2)、(col1, col2, col3)、(col1, col2, col3, col4) 上进行搜索。因此，可以使用索引列的任何左侧前缀，但不能省略中间的列，例如 (col1, col3) 或 (col1, col2, col4)，或仅使用 col3 或 col4 等。这些组合是无效的。

以下命令在我们的表中创建了两个复合索引：

CREATE INDEX composite_index_1 ON index_demo (phone_no, name, age);
CREATE INDEX composite_index_2 ON index_demo (pan_no, name, age);

Maple

  • 查询优化：如果您的查询包含多个列的 WHERE 子句，请按照复合索引的列顺序编写子句。这样的索引将有助于优化查询性能。在决定复合索引的列顺序时，可以分析系统的不同使用案例，并尝试确定对大多数案例最有利的列顺序。

  • 联接和选择查询：复合索引也对 JOIN 和 SELECT 查询有帮助。例如，在以下的 SELECT * 查询中，将使用 composite_index_2。

当定义了多个索引时，MySQL 查询优化器会选择能够消除最多行或尽可能少扫描行的索引，以提高效率。

为什么使用复合索引？为什么不定义多个次要索引？

在 MySQL 中，每个查询仅使用一个表的一个索引，除了 UNION 操作（在 UNION 操作中，每个逻辑查询会分别执行，然后将结果合并）。因此，即使在查询中定义了多个列的多个索引，也不能保证这些索引都会被使用。

MySQL 维护了称为索引统计信息（index statistics）的东西，这些统计信息帮助 MySQL 推断系统中数据的外观。索引统计信息是一种概括性的描述，但基于这些元数据，MySQL 决定哪个索引适合当前的查询。

复合索引的优势

  • 综合查询需求：复合索引允许您在多个列上定义一个索引，以支持多条件的查询优化。通过正确定义复合索引的列顺序，可以显著提高查询性能，因为MySQL可以更有效地使用这些索引来快速定位和检索数据。

  • 避免索引冗余：定义多个独立的次要索引可能会导致索引冗余和性能损耗，因为每个查询只能使用一个索引。复合索引通过将多个列的查询条件组合成一个索引，避免了这种冗余。

复合索引的工作原理

复合索引将多个列的索引键连接在一起，并使用 B+ 树按排序顺序存储这些连接的键。当执行搜索时，您的搜索键的连接会与复合索引的连接键进行匹配。如果您的搜索键的顺序与复合索引列的顺序不匹配，则无法使用该索引。

在我们的示例中，对于以下记录，复合索引键通过连接 pan_no、name 和 age 形成 — HJKXS9086Wkousik28。

工作流程概述：

  1. 连接索引键：复合索引会将指定的多个列值连接成一个键。

  2. 排序存储：这些连接的键按照 B+ 树的排序规则在索引结构中存储。

  3. 匹配搜索：当执行查询时，系统将您的搜索条件连接为索引键，然后在 B+ 树中查找与之匹配的索引键。

  4. 顺序匹配：为了有效使用复合索引，查询中指定的搜索键顺序必须与复合索引定义的列顺序一致，这样系统才能利用索引进行快速定位和检索数据。

如何确定是否需要使用复合索引

在决定是否需要创建复合索引之前，请根据您的使用情况先分析您的查询模式。如果您发现某些字段经常一起出现在多个查询中，那么可能需要考虑创建一个复合索引。

分析查询模式

  1. 字段共现性：观察哪些字段在多个查询中经常同时出现。如果某些字段经常一起使用，考虑创建一个包含这些字段的复合索引。

  2. 单列索引与复合索引：如果您已经在 col1 上创建了索引，而现在又想创建一个 (col1, col2) 的复合索引，那么只创建复合索引就足够了。因为单列索引 col1 实际上已经包含在复合索引的左侧前缀中，可以直接利用复合索引。

  3. 基数（Cardinality）：如果在复合索引中使用的列具有高基数（即唯一值数量较多），这些列是复合索引的良好候选项。高基数的列可以帮助数据库更有效地定位和过滤数据。

示例考虑

考虑一个 (col1, col2) 的复合索引：

  • 如果查询经常涉及到 col1，那么复合索引 (col1, col2) 可以直接满足这类查询的需求。
  • 如果查询需要同时使用 col1 和 col2，那么复合索引 (col1, col2) 可以有效地优化这类联合条件查询的性能。

通过合理分析查询模式和字段共现性，结合基数的考量，可以更精确地确定是否需要创建复合索引以优化数据库查询性能。

相关文章