DBA笔记-第十部分（复合索引和覆盖索引）

1. 复合索引（Composite Index）

定义：复合索引（也称为多列索引或联合索引）是一个索引，它包含了表中多个列的组合。与单列索引不同，复合索引能够同时支持多个列的查询条件，提高这些查询的性能。

特点：

• 多列组合：复合索引涉及到多个列，它将这些列的值组合在一起，以创建一个复合的索引结构。
• 查询优化：复合索引能够优化涉及这些列组合的查询。当查询条件包括复合索引中的前缀列时，复合索引能够提高查询效率。
• 列顺序：复合索引中的列顺序非常重要。只有在查询条件中涉及到索引的前缀列（从复合索引的第一个列开始），该索引才能被有效使用。例如，如果一个复合索引是 (A, B, C)，那么可以有效地支持如下查询：
  • WHERE A = ?（前缀列）
  • WHERE A = ? AND B = ?（前缀列和第二列）
  • WHERE A = ? AND B = ? AND C = ?（完整的复合索引）

复合索引例子

假设一个表t中查找小明，在最近发的十条博客（实际上它发过几十万条）。t  中索引为name

如果按照

select * from t where name = '小明'  order by date limit 10

那么第一次查到的是几十万条数据，第二次进行排序然后取10条数据，速度较慢，因为对于date字段来说第一次查到的几十万条数据是无序的。

那么增加复合索引（name，date）

同样执行上述语句来查询那么第一次查到的就是有序的几十万条数据，直接取前十行数据即可，大大加快了查询速度。

对于复合索引中多个列的情况（a，b）  ：（a ）有排序，（a，b ）有排序，（b）无排序

对于复合索引中多个列的情况（a，b，c） ：（a ）有排序，（a，b ）有排序，（a，b，c）有排序，（a，c），（b，c）无排序

思考题：为何创建（name，date），而不是（date，name）呢？

2. 覆盖索引（Covering Index）

定义：覆盖索引是一个索引，它包含了查询中所需的所有列。通过包含查询所需的所有列，覆盖索引可以避免回表操作，即不需要从表中读取额外的行数据，从而提高查询效率。

特点：

• 包含所有列：覆盖索引中的所有列都被查询所需要，能够完全覆盖查询所需的列。
• 提高性能：由于覆盖索引包含了查询的所有列，查询可以直接从索引中获取数据，从而避免访问数据表，这可以显著提高查询性能。
• 避免回表：覆盖索引可以消除回表的需求，这意味着所有查询数据都可以直接从索引中检索，而不需要额外的 I/O 操作。

示例：

假设有一个 Orders 表，包含列 OrderID、OrderDate 和 CustomerID。假设你经常执行以下查询：

SELECT OrderID, OrderDate FROM Orders WHERE CustomerID = 123;

为了提高这个查询的性能，你可以创建一个覆盖索引：

CREATE NONCLUSTERED INDEX IX_CustomerOrders ON Orders (CustomerID)
INCLUDE (OrderID, OrderDate);

在这个例子中，IX_CustomerOrders 是一个覆盖索引，它包括了 CustomerID 列（用于索引），以及 OrderID 和 OrderDate 列（被包括在索引中，以覆盖查询）。

总结

• 复合索引：针对多个列的组合，能够优化对这些列组合的查询。列的顺序在复合索引中很重要，只能有效支持前缀列的查询。

• 覆盖索引：包含了查询所需的所有列，使查询能够完全通过索引完成，避免了回表操作，从而提高查询效率。