优化组合索引的语句优化

一、‌遵循最左前缀原则‌
优化原理‌：组合索引需包含最左列且查询条件顺序与索引定义一致，否则无法触发索引。
示例‌：
sql

-- 索引 (a, b, c)
WHERE a=1 AND b=2 ✅ 有效
WHERE b=2 AND a=1 ✅ 优化器自动调整顺序后有效
WHERE a=1 AND c=3 ❌ c跳过中间列b，仅a使用索引

二、‌合理选择列顺序‌

高选择性列前置‌
将区分度高的列放在组合索引左侧，快速缩小扫描范围。

sql

-- 若 user_id 选择性0.95，status 选择性0.3
ALTER TABLE orders ADD INDEX idx_comb (user_id, status);

范围查询列后置‌
范围查询（>、BETWEEN）的列应置于索引末尾，避免后续列失效。

sql

-- 索引 (status, created_at)
WHERE status=1 AND created_at>'2025-01-01' ✅ 两列均生效

三、‌使用覆盖索引减少回表‌
优化原理‌：若组合索引包含查询所需全部字段，可避免访问主表，性能提升显著。
示例‌：
sql

-- 索引 (phone, name)
SELECT phone, name FROM users WHERE phone='138xxxx'; ✅ 覆盖索引
SELECT * FROM users WHERE phone='138xxxx'; ❌ 需回表查询其他字段

四、‌避免冗余索引与过度组合‌
删除冗余索引‌
若已有组合索引(a, b)，单独为a建索引则冗余。
控制列数‌
组合索引列数建议≤3，过多会增加写操作开销。
五、‌动态匹配查询模式‌
高频查询优先‌：根据业务高频条件设计组合索引。
sql

-- 80%查询含 city 和 age 条件
ALTER TABLE user ADD INDEX idx_city_age (city, age);

排序优化‌：若查询含ORDER BY，将排序字段加入组合索引末尾。
sql

-- 索引 (city, age)
SELECT * FROM user WHERE city='北京' ORDER BY age; ✅ 避免排序临时表

六、‌规避索引失效操作‌
禁止函数与计算‌
对索引列使用函数或表达式会导致失效。
sql

WHERE YEAR(created_at)=2025 ❌ 改为范围查询
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31' ✅

避免隐式类型转换‌
确保查询条件与索引列数据类型一致。
sql

-- 若 phone 为 VARCHAR
WHERE phone=13800000000 ❌ 数值隐式转字符串
WHERE phone='13800000000' ✅

七、‌定期维护统计信息‌
执行ANALYZE TABLE table_name更新索引基数（Cardinality），确保优化器准确选择索引。
删除低频或无用的组合索引，减少存储和写入开销。
八、‌验证执行计划‌

通过EXPLAIN命令检查索引实际使用情况：

sql

EXPLAIN SELECT * FROM orders WHERE user_id=1001 AND status=2;

观察key字段是否命中目标组合索引。
若Extra显示Using filesort或Using temporary，需优化索引或查询条件。
总结

优化组合索引需综合‌最左前缀原则‌、‌列顺序策略‌、‌覆盖索引设计‌及‌查询模式匹配‌，通过EXPLAIN验证并定期维护统计信息。高频查询场景下，组合索引性能可比单列索引提升数倍。