MySQL索引优化实战：从慢查询定位到联合索引设计的完整路径

慢查询定位与EXPLAIN执行计划分析

MySQL索引优化的起点是慢查询日志。开启slow_query_log并设置合理的long_query_time阈值，收集需要优化的SQL语句。生产环境建议阈值设为0.1秒：

# my.cnf配置
slow_query_log = 1
long_query_time = 0.1
slow_query_log_file = /var/log/mysql/slow.log
log_queries_not_using_indexes = 1

# 实时分析慢查询
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s t: 按查询时间排序
# -t 10: 显示前10条

拿到慢SQL后，用EXPLAIN查看执行计划。关注以下字段：

• type：访问类型，从好到差依次是system > const > eq_ref > ref > range > index > ALL。出现ALL说明全表扫描，必须优化
• key：实际使用的索引名，NULL表示未使用索引
• rows：预估扫描行数，越小越好
• Extra：额外信息，Using filesort和Using temporary是危险信号

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;
+----+-------------+--------+------+---------+------+-------+--------------------------+
| id | select_type | table  | type | key     | rows | Extra                        |
+----+-------------+--------+------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | orders | ref  | idx_uid |  500 | Using where; Using filesort |
+----+-------------+--------+------+---------+------+-------+--------------------------+

上述EXPLAIN结果显示使用了idx_uid索引，但Extra中出现Using filesort，说明ORDER BY无法利用索引排序，需要额外排序操作。

联合索引的最左前缀原则与列顺序

联合索引（复合索引）是MySQL索引优化的核心。最左前缀原则决定了索引生效的条件：查询条件必须从索引最左列开始匹配。索引列的顺序直接影响索引的利用率。

列顺序的确定依据三个因素：

• 等值条件列在前：WHERE中的等值条件（=、IN）应该放在索引前面
• 范围条件列在后：>、<、BETWEEN、LIKE前缀匹配等范围条件放在后面
• 排序列靠后：ORDER BY的列放在等值和范围条件之后

# 常见查询模式
SELECT * FROM orders
WHERE user_id = 100 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;

# 最优联合索引
ALTER TABLE orders ADD INDEX idx_uid_status_created(user_id, status, created_at);

# EXPLAIN验证
# type: ref, key: idx_uid_status_created
# Extra: NULL（无filesort，排序走索引）

这个索引可以同时满足WHERE过滤和ORDER BY排序，避免filesort。如果created_at不在索引末尾，排序仍需额外操作。

索引失效的常见陷阱

即使表上有合适的索引，某些SQL写法也会导致索引失效：

# 1. 对索引列使用函数
-- 索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2026-08-07';
-- 索引生效
SELECT * FROM orders WHERE created_at >= '2026-08-07' AND created_at < '2026-08-08';

# 2. 隐式类型转换
-- phone是VARCHAR类型，传入整数导致隐式转换，索引失效
SELECT * FROM users WHERE phone = 13800138000;
-- 应传入字符串
SELECT * FROM users WHERE phone = '13800138000';

# 3. OR条件中有一列无索引
-- status有索引但source无索引，整个查询走全表扫描
SELECT * FROM orders WHERE status = 'paid' OR source = 'mobile';
-- 拆分为UNION ALL
SELECT * FROM orders WHERE status = 'paid'
UNION ALL
SELECT * FROM orders WHERE source = 'mobile' AND status != 'paid';

# 4. LIKE左模糊
-- 索引失效
SELECT * FROM products WHERE name LIKE '%手机';
-- 索引生效（前缀匹配）
SELECT * FROM products WHERE name LIKE '华为%';

覆盖索引减少回表查询

当SELECT的列全部包含在索引中时，MySQL可以直接从索引返回数据，无需回表查询聚簇索引。这叫覆盖索引，Extra列显示Using index：

# 原查询需要回表（SELECT * 包含索引外的列）
SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC LIMIT 20;

# 覆盖索引优化：只查需要的列
SELECT id, user_id, status, created_at
FROM orders WHERE user_id = 100 ORDER BY created_at DESC LIMIT 20;

# 配合联合索引 idx_uid_status_created(user_id, status, created_at)
# id是聚簇索引自动包含在二级索引中，上述查询完全走覆盖索引

覆盖索引对分页查询优化效果显著。一个扫描50万行回表的查询，改为覆盖索引后可能只扫描100行索引数据。

索引维护与性能监控

索引不是建完就不管了。随着数据分布变化，索引统计信息可能失真，导致优化器选择错误的执行计划。定期维护：

# 查看索引使用统计
SELECT
    index_name,
    rows_examined,
    rows_returned,
    ROUND(rows_examined/NULLIF(rows_returned,0), 2) AS selectivity
FROM sys.schema_index_statistics
WHERE table_schema = 'your_db'
ORDER BY rows_examined DESC LIMIT 20;

# 重建索引统计信息
ANALYZE TABLE orders;

# 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = 'your_db';

selectivity（选择率）过高的索引意味着扫描行数远大于返回行数，是低效索引的标志。冗余索引浪费空间并拖慢写入性能，需要定期清理。在8.0版本中，invisible index特性可以先将索引设为不可见观察影响，确认无业务依赖后再删除。