面试题：MySQL索引列顺序对查询优化的影响

Question

在一个复杂的电商数据库中，有一张 `orders` 表，包含 `order_id`、`customer_id`、`order_date`、`total_amount`、`status` 等字段。常见查询包括：1. 根据客户ID和订单状态查询订单；2. 根据订单日期范围和订单金额范围查询订单。请阐述如何根据MySQL索引列顺序选择原则创建复合索引以优化这些查询，并分析不同索引列顺序可能带来的性能差异。

Answer 1

创建复合索引优化查询

1. 针对根据客户ID和订单状态查询订单：
   • 复合索引应包含 customer_id 和 status 字段。推荐的索引顺序为 (customer_id, status)。因为在MySQL中，索引是按照从左到右的顺序匹配的。当查询条件为 WHERE customer_id =? AND status =? 时，先使用 customer_id 进行过滤，能快速缩小数据范围，再使用 status 进一步筛选，这样能充分利用索引的快速查找能力。
2. 针对根据订单日期范围和订单金额范围查询订单：
   • 复合索引应包含 order_date 和 total_amount 字段。推荐的索引顺序为 (order_date, total_amount)。当查询条件为 WHERE order_date BETWEEN? AND? AND total_amount BETWEEN? AND? 时，先按 order_date 进行范围查找，能快速定位到相关日期的数据，再根据 total_amount 进一步筛选，可有效利用索引。

不同索引列顺序的性能差异分析

1. 对于客户ID和订单状态查询：
   • 正确顺序 (customer_id, status)：查询时能高效利用索引。例如有大量客户数据，先根据 customer_id 能快速定位到特定客户的订单数据子集，再在该子集中根据 status 筛选，索引能直接命中，查询性能高。
   • 错误顺序 (status, customer_id)：如果查询时先按 status 过滤，由于 status 可能值较少（如常见订单状态可能就几种），无法有效缩小数据范围，可能导致全表扫描。即使后续有 customer_id 条件，因为索引未按 customer_id 优先顺序，也不能充分利用索引，查询性能会大幅下降。
2. 对于订单日期范围和订单金额范围查询：
   • 正确顺序 (order_date, total_amount)：按日期范围查询时，索引能快速定位到符合日期条件的数据，再在这些数据中根据金额范围筛选，能充分利用索引，提升查询效率。
   • 错误顺序 (total_amount, order_date)：由于金额范围查询通常比较离散，先按金额范围过滤很难有效缩小数据范围，可能导致扫描大量数据。即使后续有日期范围条件，由于索引顺序问题，也无法充分利用索引，查询性能变差。