MySQL组合索引优化策略

优化MySQL组合索引需要综合考虑查询模式、索引结构及数据库特性。以下是关键优化策略及示例：

1. 遵循最左前缀原则

• 策略：确保查询条件包含组合索引最左侧列。
• 示例：索引(a,b,c)生效场景：

  WHERE a=1 AND b=2      -- ✔️ 使用a和b
  WHERE a=1 ORDER BY b    -- ✔️ 排序利用索引
  WHERE a>1 AND b=2       -- ✔️ 范围查询后b可能无法走索引（ICP优化）
  

2. 优先高选择性列

• 策略：将区分度高的列放在索引左侧。
• 示例：用户表user中user_id比gender选择性高：

  ALTER TABLE user ADD INDEX idx_user_gender (user_id, gender); -- 更优
  

3. 覆盖索引减少回表

• 策略：索引包含所有查询字段，避免访问数据行。
• 示例：

  SELECT a, b FROM table WHERE a=1 AND b=2; -- 索引(a,b)覆盖查询
  

4. 利用索引下推（ICP）

• 策略：MySQL 5.6+ 允许在索引层过滤数据。
• 示例：索引(a,b,c)

  WHERE a=1 AND b>10 AND c=2; -- ICP在索引中过滤c=2
  

5. 优化排序和分组

• 策略：索引顺序与ORDER BY/GROUP BY一致。
• 示例：索引(a,b)

  SELECT a, b FROM table ORDER BY a ASC, b DESC; -- 可能需指定索引排序方向
  -- 创建索引时指定排序
  ALTER TABLE table ADD INDEX idx_a_b (a ASC, b DESC);
  

6. 避免冗余索引

• 策略：删除重复或前缀相同的索引。
• 示例：已有(a,b,c)，则(a,b)冗余。

7. 范围查询列置后

• 策略：范围查询后的列无法使用索引查找。
• 示例：索引(a, range_col, c)

  WHERE a=1 AND range_col>10 AND c=2; -- c只能过滤，无法索引查找
  -- 更优索引：(a, c, range_col)
  

8. 控制索引大小

• 策略：避免过长列，使用前缀索引。
• 示例：

  ALTER TABLE table ADD INDEX idx_name (name(10)); -- 前10字符
  

9. EXPLAIN分析执行计划

• 关键指标：
  • type: ref/range > index > ALL
  • Extra: Using index (覆盖索引), Using filesort (需优化)

10. 实际场景优化案例

场景：订单表查询user_id+时间段，按金额排序。

• 原始查询：

  SELECT order_id, amount FROM orders 
  WHERE user_id=100 AND create_time BETWEEN '2023-01-01' AND '2023-12-31'
  ORDER BY amount DESC;
  

• 低效索引：(user_id, create_time)，排序导致filesort。
• 优化索引：

  ALTER TABLE orders ADD INDEX idx_user_amount (user_id, amount DESC, create_time);
  

  • 利用user_id定位，amount排序，create_time过滤。

11. 定期监控与调整

• 使用慢查询日志定位低效SQL。
• 监控索引使用率：

  SELECT * FROM sys.schema_unused_indexes WHERE object_schema='your_db';
  

总结：优化组合索引的决策流程

1. 分析查询模式：WHERE、JOIN、ORDER BY、GROUP BY、SELECT字段。
2. 确定关键列：高选择性、频繁查询、排序字段。
3. 排列顺序：等值查询列在前，范围/排序列在后。
4. 验证覆盖：尽可能包含查询字段，减少回表。
5. 测试调整：通过EXPLAIN和实际执行时间验证效果。

通过系统化分析查询需求，合理设计组合索引，可显著提升MySQL性能，降低响应时间与资源消耗。