mysql 组合索引

基础概念

MySQL中的组合索引（也称为复合索引或多列索引）是指在一个索引中包含两个或多个列。这种索引可以显著提高多条件查询的性能，因为它允许数据库引擎在一个索引中同时查找多个列的值。

优势

1. 提高查询性能：对于多条件查询，组合索引可以减少磁盘I/O操作，从而提高查询速度。
2. 减少索引数量：通过创建一个组合索引，可以替代多个单列索引，从而减少索引的数量和维护成本。
3. 优化排序和分组：如果查询涉及到多个列的排序或分组，组合索引可以提供更好的性能。

类型

• 非唯一组合索引：允许索引列中有重复值。
• 唯一组合索引：要求索引列的组合值是唯一的。

应用场景

假设我们有一个订单表orders，包含以下列：

• order_id (主键)
• customer_id
• order_date
• total_amount

如果我们经常执行以下查询：

SELECT * FROM orders WHERE customer_id = 123 AND order_date BETWEEN '2023-01-01' AND '2023-12-31';

在这种情况下，创建一个组合索引(customer_id, order_date)会非常有用。

创建组合索引的语法

CREATE INDEX idx_customer_order ON orders (customer_id, order_date);

遇到的问题及解决方法

1. 索引选择性不高

问题：如果组合索引中的某一列选择性不高（即该列的值非常重复），那么索引的效果会大打折扣。

解决方法：选择具有较高选择性的列作为组合索引的前导列。例如，customer_id通常比order_date具有更高的选择性。

2. 索引覆盖不完全

问题：如果查询的列不在索引中，数据库引擎仍然需要回表查询数据，这会降低性能。

解决方法：确保查询的列尽可能在索引中，或者使用覆盖索引（Covering Index）。

3. 索引维护成本高

问题：随着数据量的增加，索引的维护成本也会增加。

解决方法：定期分析和优化索引，删除不必要的索引，保持索引的精简和高效。

示例代码

假设我们有一个订单表orders，结构如下：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

我们可以创建一个组合索引：

CREATE INDEX idx_customer_order ON orders (customer_id, order_date);

然后执行查询：

SELECT * FROM orders WHERE customer_id = 123 AND order_date BETWEEN '2023-01-01' AND '2023-12-31';

参考链接

• MySQL官方文档 - 组合索引

通过合理使用组合索引，可以显著提高多条件查询的性能，减少磁盘I/O操作，并优化排序和分组操作。