MySQL索引优化与慢查询排查实战：从执行计划到索引设计全流程

MySQL性能调优中，索引优化是投入产出比最高的手段。约80%的性能问题源于不合理的索引设计和低效SQL查询。本文通过实际案例展示从慢查询定位、执行计划分析到索引优化的完整排查流程。

慢查询日志配置与采集

慢查询日志是SQL查询优化的入口。MySQL 8.0的配置方式：

SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;
SET GLOBAL min_examined_rows_limit = 100;

生产环境中long_query_time不宜设得太小。0.1秒甚至0.01秒的阈值会产生大量日志，淹没真正需要优化的慢查询。推荐先设为1秒，根据采集情况逐步下调。log_queries_not_using_indexes会捕获全表扫描的查询，即使执行时间很短，这类查询在数据量增长后必然成为性能隐患。

使用pt-query-digest分析慢查询日志，按执行频率和总耗时排序：

pt-query-digest /var/log/mysql/slow.log --report --statistics

# 输出示例
# Rank Query ID           Response time  Calls R/Call
# ==== ================== ============== ===== =========
#    1 0xABC123...         1250.5467 45.2%  3421 0.365667
#    2 0xDEF456...          680.1234 24.6%  1567 0.434084
#    3 0x789ABC...          320.5678 11.6%   893 0.359023

Response time列是重点关注对象，它表示该查询类型的累计执行时间。排名靠前的查询是优化的优先目标。

执行计划分析与索引诊断

对定位到的慢查询使用EXPLAIN分析执行计划：

EXPLAIN 
SELECT u.username, u.email, o.order_no, o.amount, o.created_at
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active' 
  AND o.created_at >= '2026-07-01' 
  AND o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 20;

关键字段解读：type列表示访问类型，最优到最差依次为const、eq_ref、ref、range、index、ALL。ALL表示全表扫描，是性能瓶颈。key列显示实际使用的索引，NULL表示没有使用任何索引。rows列是预估扫描行数。Extra列中Using filesort表示需要额外排序操作，Using temporary表示使用临时表，两者都是需要消除的性能信号。

联合索引设计与最左前缀原则

上述查询的核心问题在于users表没有合适索引。根据WHERE条件和JOIN条件，设计联合索引：

ALTER TABLE users ADD INDEX idx_status_id (status, id);
ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);

索引列顺序的设计逻辑遵循最左前缀匹配原则。以orders表的索引为例：

-- 能使用索引的查询
SELECT * FROM orders WHERE user_id = 100;
SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';
SELECT * FROM orders WHERE user_id = 100 AND status = 'paid' 
  AND created_at >= '2026-07-01';

-- 不能完整使用索引的查询
SELECT * FROM orders WHERE status = 'paid';
-- 跳过user_id，无法使用索引
SELECT * FROM orders WHERE user_id = 100 AND created_at >= '2026-07-01';
-- 只使用user_id，status被跳过

索引列顺序的设计原则：等值查询列在前、范围查询列在后、排序列在末尾。因为范围查询会使后续列无法使用索引。在原查询中，user_id是JOIN等值条件，status是WHERE等值条件，created_at是范围条件且用于排序，所以顺序为(user_id, status, created_at)。

添加索引后，users表扫描行数从500000降到5000，orders表使用联合索引且Using filesort消失（索引本身有序，无需额外排序）。查询从原来的3.2秒降至8毫秒。

覆盖索引与Index Condition Pushdown

当查询的所有字段都能从索引中获取时，MySQL直接从索引树返回数据，无需回表查询聚簇索引。这被称为覆盖索引：

-- 查询只需要user_id和status
SELECT user_id, status FROM orders 
WHERE user_id = 100 AND status = 'paid';
-- 现有索引已覆盖查询字段，Extra列显示Using index

Index Condition Pushdown（ICP）是MySQL 5.6以上的优化。没有ICP时，存储引擎根据索引取出所有满足最左前缀的行，再由Server层过滤其他条件。启用ICP后，WHERE条件中涉及索引列的部分下推到存储引擎层执行，减少回表次数：

SELECT * FROM orders 
WHERE user_id = 100 
  AND status LIKE 'pa%'
  AND created_at >= '2026-07-01';
-- 有ICP：存储引擎在索引层直接过滤status LIKE 'pa%'
-- 只对满足条件的行回表

EXPLAIN中Extra列显示Using index condition表示启用了ICP。

分库分表场景下的索引策略

数据迁移实战中，分库分表方案的索引策略与单表有本质区别。以ShardingSphere的分表场景为例，分片键为user_id：

-- 问题：以下查询需要扫描全部分表（广播路由）
SELECT * FROM orders WHERE status = 'paid' AND created_at >= '2026-07-01';

-- 方案1：保证查询条件包含分片键
SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';
-- 只路由到orders_4一张表，索引可用

-- 方案2：建立异构索引表
-- 将高频查询字段同步到Elasticsearch，异步更新
-- 先从ES查出user_id列表，再回源分表查全量数据

分库分表后，跨分片查询的性能急剧下降。设计阶段就应该确认：所有高频查询都包含分片键。如果业务确实存在不带分片键的查询需求，应使用异构索引或CQRS模式，而非强制全表扫描。

索引维护与在线DDL

每增加一个索引，INSERT、UPDATE、DELETE操作都需要同步维护索引树。监控索引使用率，清理冗余索引：

SELECT 
    OBJECT_NAME AS table_name,
    INDEX_NAME AS index_name,
    COUNT_READ, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
  AND OBJECT_SCHEMA = 'your_db'
ORDER BY COUNT_READ ASC;

大表添加索引的在线DDL推荐使用pt-online-schema-change，避免锁表：

pt-online-schema-change \
  --alter "ADD INDEX idx_user_status_created (user_id, status, created_at)" \
  --execute \
  --chunk-size=5000 \
  --max-lag=1 \
  D=your_db,t=orders,h=127.0.0.1,u=root,p=password

pt-online-schema-change的工作原理是创建影子表，在影子表上执行DDL，通过触发器同步增量数据，最后通过RENAME TABLE原子切换。–max-lag参数控制同步速度，当主从延迟超过1秒时暂停复制，避免影响从库读取。

数据库高可用架构中的索引同步

在主从复制架构下，索引变更通过binlog同步到从库。DDL语句在MySQL中是隐式提交的，一旦执行无法回滚。因此索引变更前必须做好数据备份恢复的预案。安全的索引变更流程：先在从库上执行DDL观察性能影响，确认无问题后在主库低峰期执行，使用在线DDL工具避免锁表。如果索引变更导致性能下降，DROP INDEX回滚是即时的，不影响数据。

对于MySQL Group Replication或多主架构，索引变更需要在所有节点执行。由于DDL语句不支持回滚，建议在维护窗口期统一执行，并确保所有节点的MySQL版本一致，避免DDL行为差异导致的复制中断。