Loading

复合索引和最左前缀原则

复合索引

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

列的顺序

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

  • 查询模式:考虑哪些列经常一起出现在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_idorder_date来检索数据。创建如下索引可以优化这类查询:

CREATE INDEX idx_customer_id_order_date ON orders (customer_id, order_date);

这个索引适用于:

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

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

posted @ 2024-03-12 15:13  viazure  阅读(75)  评论(0编辑  收藏  举报