联合索引和单个索引

在数据库中，联合索引（Composite Index）和单个索引（Single-column Index）有不同的作用和优势。它们适用于不同的查询场景，具体区别如下：

联合索引的优势：

1. 优化复杂查询：

   • 联合索引是由多个列组成的索引，它可以提高涉及多个列的查询性能。例如，查询条件同时涉及 columnA 和 columnB，如果创建了 (columnA, columnB) 的联合索引，数据库可以使用该索引来加速查询。

2. 覆盖索引：

   • 联合索引可以覆盖多个列的查询，从而减少了数据的读取量。如果查询只涉及联合索引中的列，数据库可以直接从索引中获取所有必要的数据，而无需访问数据表本身，这种情况称为覆盖索引。

3. 优化排序和分组：

   • 如果查询涉及对多个列的排序（ORDER BY columnA, columnB）或分组（GROUP BY columnA, columnB），联合索引可以显著提高查询性能，因为索引已经按照指定的顺序排列数据。

单个索引的局限性：

1. 性能不如联合索引：

   • 如果查询条件涉及多个列，单个索引只能利用一个列的索引，可能无法充分利用其他列的信息。例如，如果你有 columnA 和 columnB 的单独索引，但查询条件涉及 columnA 和 columnB，单个索引可能无法高效地组合这两个列的数据，需要使用索引合并解决（索引合并是通过对一个表同时使用多个索引进行条件扫描，并将满足条件的多个主键集合取交集或并集后再进行回表，可以提升查询效率。）。

2. 查询性能下降：

   • 当涉及多个列的查询时，使用单个索引的效率可能较低，因为数据库需要扫描多个索引并进行合并，这比使用联合索引的效率低。

例子说明：

假设你有一个表 users，包含以下列：id, name, age, city。你可以创建以下索引：

• 单个索引：CREATE INDEX idx_age ON users (age);
• 联合索引：CREATE INDEX idx_age_city ON users (age, city);

查询 1：SELECT * FROM users WHERE age = 30 AND city = 'New York';

• 联合索引 idx_age_city 能有效地加速此查询，因为它可以同时利用 age 和 city 列。
• 单个索引 idx_age 可能会导致性能较差，因为它只能在 age 列上加速查询，对 city 列的筛选则需要额外的处理。

查询 2：SELECT * FROM users WHERE age = 30;

• 单个索引 idx_age 可以有效地加速此查询。
• 联合索引 idx_age_city 也可以加速此查询，但它在处理时会涉及到 city 列的索引结构，可能不会像单列索引那样高效。

总结：

• 联合索引 适用于频繁涉及多个列的查询，可以优化复杂的查询性能。
• 单个索引 适用于单列查询，但对于多列查询的性能不如联合索引。

在设计索引时，需要根据实际查询需求来决定是使用联合索引还是单个索引，或者结合使用它们，以达到最佳的查询性能。