MySQL慢查询诊断与索引优化实战：执行计划、覆盖索引与分库分表

MySQL慢查询诊断从哪里入手

MySQL慢查询诊断的第一步是确认慢查询日志已开启，并合理设置阈值。默认long_query_time为10秒，线上环境建议调至0.1秒甚至更低，捕获更多潜在问题查询：

-- 查看当前慢查询配置
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.1;
SET GLOBAL log_queries_not_using_indexes = ON;

-- MySQL 8.0+ 性能模式替代方案（无需重启）
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%statement/%';

SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

events_statements_summary_by_digest是慢查询分析的核心表，按SQL摘要聚合，可直接定位高频耗时SQL，比翻日志高效得多。

EXPLAIN执行计划深度解读

拿到慢SQL后，用EXPLAIN分析执行计划。重点看四个字段：

EXPLAIN FORMAT=JSON
SELECT o.order_id, o.total_amount, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.create_time >= '2026-07-01'
  AND o.status = 'PAID'
  AND c.region = 'EAST'
ORDER BY o.total_amount DESC
LIMIT 50;

type列：访问类型，从好到差排序system > const > eq_ref > ref > range > index > ALL。出现ALL意味着全表扫描，必须优化。

key列：实际使用的索引。为NULL表示未走索引。

rows列：预估扫描行数。这个值和实际行数可能有数量级偏差，但趋势上准确。

Extra列：关键信息来源：

• Using filesort：额外排序操作，大数据量下严重拖慢查询
• Using temporary：使用临时表，GROUP BY无索引时常见
• Using index：覆盖索引，理想状态
• Using index condition：索引下推（ICP），减少回表次数

索引优化实战：从全表扫描到覆盖索引

上面的查询如果orders表有1000万行，全表扫描代价极高。逐步优化：

第一步：创建复合索引

-- 在orders表上创建(status, create_time)复合索引
ALTER TABLE orders ADD INDEX idx_status_createtime (status, create_time);

-- 此时查询走idx_status_createtime，type=range
-- 但仍需回表获取total_amount和customer_id

第二步：扩展为覆盖索引

-- 覆盖索引包含查询所需的所有列，避免回表
ALTER TABLE orders ADD INDEX idx_status_createtime_cover 
  (status, create_time, total_amount, customer_id, order_id);

-- 此时Extra出现Using index，查询完全在索引中完成

第三步：处理ORDER BY

-- ORDER BY total_amount DESC仍触发filesort
-- 调整索引列顺序，让排序也能走索引
ALTER TABLE orders ADD INDEX idx_status_createtime_amount
  (status, create_time, total_amount DESC);

-- 现在ORDER BY也能走索引，filesort消失
-- 但需MySQL 8.0+才支持降序索引

索引优化不是越多越好。每增加一个索引，写入性能下降约5%-10%，索引占用的磁盘空间也不容忽视。建议单表索引不超过6个，复合索引列数不超过5列。

分库分表方案与SQL查询优化

当单表数据量超过2000万行，B+Tree索引层级增加导致查询性能非线性下降，分库分表成为必要手段。

ShardingSphere分片配置

# ShardingSphere JDBC分片配置
spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://db0:3306/order_db
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://db1:3306/order_db
    rules:
      sharding:
        tables:
          orders:
            actual-data-nodes: ds$->{0..1}.orders_$->{0..15}
            table-strategy:
              standard:
                sharding-column: customer_id
                sharding-algorithm:
                  type: MOD
                  props:
                    sharding-count: 32
            database-strategy:
              standard:
                sharding-column: customer_id
                sharding-algorithm:
                  type: MOD
                  props:
                    sharding-count: 2

分片键选择是分库分表方案成败的关键。订单场景下用customer_id分片，保证同一用户的订单在同一分片上，避免跨分片查询。如果业务存在按时间范围查订单的需求，需要额外的异构索引表或Elasticsearch搜索集群来补偿。

数据库高可用架构下的查询优化

MySQL主从架构中，慢查询治理需要区分主库和从库：

-- 主库慢查询：影响写入性能，优先级最高
-- 从库慢查询：影响读服务，可先通过读写分离缓解

-- 从库并行复制配置（MySQL 8.0+）
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 8;
SET GLOBAL slave_parallel_workers = 'consistent';

-- 从库读权重配置（ProxySQL）
-- 将慢查询路由到专用分析从库，不影响在线读服务
INSERT INTO mysql_query_rules (rule_id, active, digest, destination_hostgroup, apply)
VALUES (1, 1, '慢查询SQL的digest值', 20, 1);

主库优化写入性能的核心手段：

• 批量INSERT替代逐行INSERT，单事务提交
• 避免大事务：单事务影响行数控制在5000行以内
• innodb_flush_log_at_trx_commit = 2：降低每次事务的磁盘fsync开销（非金融场景可接受1秒数据丢失风险）
• sync_binlog = 100：减少binlog刷盘频率（同理，非零意味着有丢失风险）

慢查询治理的长效机制

单次优化解决不了根本问题，需要建立长效治理机制：

1. 慢查询基线管理：每周统计Top 20慢查询，与上周对比，新增或恶化查询立即跟进
2. SQL审核流程：新上线SQL必须通过EXPLAIN审核，全表扫描和filesort查询不得上线
3. 自动化索引建议：使用sys.schema_index_usage_statistics识别未使用索引和冗余索引

-- 查找从未使用的索引
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY object_schema, object_name;

-- 查找冗余索引（主键或唯一索引已覆盖的场景）
SELECT t.table_schema, t.table_name, t.index_name, 
       GROUP_CONCAT(t.column_name ORDER BY t.seq_in_index) AS index_columns
FROM information_schema.statistics t
JOIN information_schema.statistics r
  ON t.table_schema = r.table_schema
  AND t.table_name = r.table_name
  AND t.index_name != r.index_name
  AND t.column_name = r.column_name
  AND t.seq_in_index = r.seq_in_index
GROUP BY t.table_schema, t.table_name, t.index_name
HAVING COUNT(*) = (SELECT COUNT(*) FROM information_schema.statistics 
                   WHERE table_schema = t.table_schema 
                   AND table_name = t.table_name 
                   AND index_name = r.index_name);

定期清理无用索引释放写入性能，同时避免优化器选错索引。数据库运维不是一次性的工作，持续监控与迭代优化才能保持系统在最佳状态运行。