MySQL索引优化实战：执行计划分析与慢查询诊断全流程

场景1：对索引列使用函数或表达式

-- 索引失效：对created_at使用了函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-25';

-- 修复：改写为范围查询
SELECT * FROM orders 
WHERE created_at >= '2026-07-25 00:00:00' 
  AND created_at < '2026-07-26 00:00:00';

场景2：隐式类型转换

-- 索引失效：customer_id是INT类型，但传入字符串
SELECT * FROM orders WHERE customer_id = '12345';

-- 修复：使用正确类型
SELECT * FROM orders WHERE customer_id = 12345;

场景3：LIKE前缀通配符

-- 索引失效：以通配符开头
SELECT * FROM products WHERE name LIKE '%手机';

-- 修复：使用前缀匹配，或考虑全文索引
SELECT * FROM products WHERE name LIKE '华为%';

场景4：OR条件导致索引合并失败

-- 可能失效：OR条件中一个列无索引
SELECT * FROM orders WHERE customer_id = 100 OR status = 'PENDING';

-- 修复1：为status列添加索引
ALTER TABLE orders ADD INDEX idx_status (status);

-- 修复2：改写为UNION
SELECT * FROM orders WHERE customer_id = 100
UNION
SELECT * FROM orders WHERE status = 'PENDING';

场景5：联合索引最左前缀原则违反

-- 联合索引：idx_status_created (status, created_at)
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

-- 生效：使用最左前缀
SELECT * FROM orders WHERE status = 'PAID';  ✅
SELECT * FROM orders WHERE status = 'PAID' AND created_at > '2026-07-01';  ✅

-- 失效：跳过最左列
SELECT * FROM orders WHERE created_at > '2026-07-01';  ❌

场景6：NOT条件

-- 索引可能失效：NOT/EQUALS不等于
SELECT * FROM orders WHERE status != 'CANCELLED';

-- 修复：改写为IN
SELECT * FROM orders WHERE status IN ('PENDING', 'PAID', 'SHIPPED');

联合索引的设计原则

联合索引（Composite Index）是索引优化的核心技能。设计原则直接影响查询性能：

原则1：等值条件列在前，范围条件列在后

-- 查询：WHERE status = 'PAID' AND created_at > '2026-07-01'
-- 索引设计：status在前（等值），created_at在后（范围）
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

-- 这样MySQL可以先通过status精确定位，再在范围内使用created_at的索引排序
-- 如果created_at在前，status的等值过滤就无法利用索引

原则2：覆盖索引避免回表

-- 需要回表：索引只包含status和created_at，查询还需要total列
SELECT order_id, total FROM orders 
WHERE status = 'PAID' AND created_at > '2026-07-01';

-- 覆盖索引：把查询需要的列都包含在索引中
ALTER TABLE orders ADD INDEX idx_cover_paid (
  status, created_at, order_id, total
);

-- EXPLAIN中Extra显示Using index表示覆盖索引生效，无需回表

原则3：避免冗余索引

-- 冗余：如果已有(status, created_at)，单独的(status)索引是冗余的
ALTER TABLE orders ADD INDEX idx_status (status);  -- 冗余！

-- 检查冗余索引
SELECT s.table, s.index_name, GROUP_CONCAT(s.column_name ORDER BY s.seq_in_index) AS columns
FROM information_schema.statistics s
WHERE s.table_schema = 'your_db' AND s.table = 'orders'
GROUP BY s.table, s.index_name;

-- 使用sys.schema_unused_indexes查看未使用的索引
SELECT * FROM sys.schema_unused_indexes 
WHERE object_schema = 'your_db';

慢查询诊断全流程

慢查询日志是发现性能问题的第一道防线：

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过1秒的查询记入日志
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 未用索引的查询也记录

-- 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

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

诊断流程：

1. 从慢查询日志中提取TOP N慢查询
2. 对每条慢查询执行EXPLAIN分析
3. 检查type是否为ALL或index
4. 检查Extra是否有Using filesort或Using temporary
5. 检查rows预估扫描行数是否远大于实际结果集
6. 根据诊断结果添加或调整索引

-- 实战诊断：发现Using filesort
EXPLAIN SELECT * FROM orders 
WHERE customer_id = 100 
ORDER BY created_at DESC LIMIT 10;

-- 如果显示：Using where; Using filesort
-- 原因：索引(customer_id)无法支持created_at排序

-- 优化：添加联合索引
ALTER TABLE orders ADD INDEX idx_customer_created (customer_id, created_at);

-- 优化后EXPLAIN应显示：Using where; Backward index scan
-- Backward index scan表示MySQL利用索引的降序扫描替代filesort

索引监控与持续优化

索引优化不是一次性工作，需要持续监控：

-- 查看索引使用统计（MySQL 8.0+）
SELECT 
  index_name,
  rows_examined,
  rows_returned,
  ROUND(rows_examined / NULLIF(rows_returned, 0), 2) AS selectivity
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_db'
ORDER BY rows_examined DESC;

-- 查看索引大小
SELECT 
  index_name,
  ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'your_db'
  AND stat_name = 'size'
ORDER BY stat_value DESC;

MySQL索引优化的完整路径：先用EXPLAIN读懂执行计划，再用六种失效场景逐一排查，按联合索引三原则设计索引，通过慢查询日志持续监控。索引不是越多越好，精准的索引设计远比堆砌索引有效。