复合索引和最左前缀原则

复合索引

复合索引是根据多个列创建的数据库索引。它们对于优化涉及这些列的查询性能非常有用。在创建复合索引时，这些列的顺序至关重要，因为它决定了数据库如何利用该索引。

列的顺序

确定复合索引中列的顺序时，应考虑以下因素：

• 查询模式：考虑哪些列经常一起出现在WHERE子句中。
• 选择性：选择性是指一列中不同值的数量。通常，选择性较高的列应放在索引的前面。

最左前缀原则

最左前缀原则指出，查询必须使用复合索引中从最左侧开始的一个或多个列。数据库引擎将从索引的最左边开始匹配这些列。

例如：

假设有一个复合索引 (A, B, C)，以下是一些查询条件和它们是否能有效利用索引的例子：

• WHERE A = 1：✅ 可以使用索引。
• WHERE A = 1 AND B = 2：✅ 可以使用索引。
• WHERE A = 1 AND B = 2 AND C = 3：✅ 可以使用索引。
• WHERE B = 2：❌ 不能充分利用索引。
• WHERE B = 2 AND C = 3：❌ 不能充分利用索引。
• WHERE A = 1 AND C = 3：⚠️ 部分使用索引，但不是最优。

如何确定复合索引的列顺序？

为了确定最优的复合索引列顺序，你需要：

1. 分析常见的查询模式。
2. 评估涉及的列的选择性。
3. 使用查询执行计划工具来测试不同索引策略对性能的影响。

示例

考虑一个orders表，我们常常需要根据customer_id和order_date来检索数据。创建如下索引可以优化这类查询：

CREATE INDEX idx_customer_id_order_date ON orders (customer_id, order_date);

这个索引适用于：

• 根据 customer_id 过滤订单。
• 根据 customer_id 和 order_date 过滤订单。

但它不适用于只基于 order_date 进行的查询，因为 order_date 不是最左边的列。