复合索引和最左前缀原则
复合索引
复合索引是根据多个列创建的数据库索引。它们对于优化涉及这些列的查询性能非常有用。在创建复合索引时,这些列的顺序至关重要,因为它决定了数据库如何利用该索引。
列的顺序
确定复合索引中列的顺序时,应考虑以下因素:
- 查询模式:考虑哪些列经常一起出现在
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
:⚠️ 部分使用索引,但不是最优。
如何确定复合索引的列顺序?
为了确定最优的复合索引列顺序,你需要:
- 分析常见的查询模式。
- 评估涉及的列的选择性。
- 使用查询执行计划工具来测试不同索引策略对性能的影响。
示例
考虑一个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 不是最左边的列。