面试题：MySQL性能调优中的索引优化策略

Question

假设你有一个包含大量数据的订单表orders，有字段order_id（主键）, customer_id, order_date, total_amount等。现在有一个频繁执行的查询语句：SELECT * FROM orders WHERE customer_id =? AND order_date BETWEEN? AND? ORDER BY total_amount LIMIT 10; 请详细说明如何通过索引优化来提升该查询的性能，分析不同索引策略的优缺点，并指出在MySQL执行计划中，哪些指标能反映索引使用是否合理。

Answer 1

索引优化提升查询性能

为提升该查询性能，可创建复合索引：

CREATE INDEX idx_customer_date_amount ON orders (customer_id, order_date, total_amount);

1. 复合索引原理：MySQL会按照索引列顺序依次查找，先定位customer_id，再在该范围内找order_date，最后排序total_amount。

不同索引策略优缺点

1. 单个索引：
   • 优点：简单易创建。若只按customer_id或order_date单个条件查询，有一定优化效果。
   • 缺点：对于当前复合条件查询，无法同时利用多个索引优化。若对每个字段都建单个索引，会增加索引维护成本和存储空间。
2. 复合索引：
   • 优点：能有效覆盖当前查询，提高查询性能。MySQL可使用索引直接定位数据，减少全表扫描。
   • 缺点：索引列顺序很关键。若顺序不合理（如(order_date, customer_id, total_amount)），对当前查询优化效果不佳。并且，插入、更新、删除操作时，索引维护成本比单个索引高。

MySQL执行计划反映索引使用合理性的指标

1. key：显示MySQL实际使用的索引。若为NULL，表示未使用索引。
2. key_len：表示使用索引的长度。长度越接近索引定义长度，索引使用越充分。
3. ref：显示哪些列或常量被用于查找索引列上的值。若查询条件都被有效利用，可看到对应列或常量。
4. rows：估计为找到所需行而要读取的行数。值越小，说明索引使用越有效，查询效率越高。