postgres中复合索引和include索引的区别

1. 复合索引（Composite Index）

• 定义：复合索引是基于多个列的组合创建的索引。

• 特点：

  • 索引键包含多个列的值。

  • 查询条件必须使用索引的前缀列（最左前缀原则）才能有效利用索引。

  • 索引本身可以用于查询的过滤条件（WHERE 子句）和排序（ORDER BY）。

• 适用场景：

  • 查询条件涉及多个列。

  • 需要对多个列进行排序。

• 示例：
  

  CREATE INDEX idx_composite ON your_table (col1, col2);

  • 以下查询可以利用复合索引：
    

    SELECT * FROM your_table WHERE col1 = 'value1' AND col2 = 'value2';
    SELECT * FROM your_table WHERE col1 = 'value1' ORDER BY col2;

  • 但以下查询无法充分利用复合索引（未使用最左前缀）：
    

    SELECT * FROM your_table WHERE col2 = 'value2';

2. INCLUDE 索引（Covering Index）

• 定义：INCLUDE 索引是一种扩展的 B-tree 索引，允许在索引中包含额外的列（非索引键列），这些列不会用于索引查找，但可以避免回表操作。

• 特点：

  • 索引键只包含查询条件中使用的列。

  • 包含列（INCLUDE 列）存储在索引的叶子节点中，不参与索引查找。

  • 适合查询只需要索引列和包含列的场景，避免访问表数据（回表）。

• 适用场景：

  • 查询只需要索引列和包含列。

  • 需要优化查询性能，减少回表操作。

• 示例：
  

  CREATE INDEX idx_include ON your_table (col1) INCLUDE (col2);

  • 以下查询可以利用 INCLUDE 索引：
    

    SELECT col1, col2 FROM your_table WHERE col1 = 'value1';

  • 由于 col2 包含在索引中，查询无需访问表数据。

3. 复合索引 vs INCLUDE 索引的区别

4. 如何选择

• 使用复合索引：

  • 查询条件或排序涉及多个列。

  • 需要同时优化过滤和排序。

• 使用 INCLUDE 索引：

  • 查询只需要索引列和包含列。

  • 需要避免回表操作，提升查询性能。

5. 示例对比

• 复合索引：
  

  CREATE INDEX idx_composite ON orders (customer_id, order_date);

  • 适合查询：
    

    SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

    
    

• INCLUDE 索引：
  

  CREATE INDEX idx_include ON orders (customer_id) INCLUDE (order_date);

  • 适合查询：
    

    SELECT customer_id, order_date FROM orders WHERE customer_id = 123;

总结

• 复合索引适合多列查询和排序。

• INCLUDE 索引适合避免回表操作，提升查询性能。

• 根据查询需求选择合适的索引类型，可以有效优化数据库性能。