MySQL性能调优实战：SQL查询优化与分库分表方案完整指南

MySQL性能调优是数据库运维中高频出现的实战课题。本文从执行计划分析、索引优化、SQL重写到分库分表方案，系统梳理MySQL在高并发写入和复杂查询场景下的调优方法论。

执行计划分析：EXPLAIN深度解读

SQL查询优化的第一步是阅读执行计划。MySQL的EXPLAIN输出包含12个关键字段，重点关注type、key、rows和Extra：

-- 查看执行计划
EXPLAIN SELECT o.id, o.amount, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID' AND o.created_at > '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

-- 查看实际执行代价
EXPLAIN FORMAT=JSON SELECT ...;

-- 查看优化器改写后的SQL
EXPLAIN FORMAT=TREE SELECT ...;

执行计划各字段含义：

-- type字段(从优到差)：
-- system > const > eq_ref > ref > range > index > ALL
-- 生产环境要求至少达到range级别，禁止ALL全表扫描

-- Extra字段关键提示：
-- Using index: 覆盖索引，理想状态
-- Using where: 需要回表过滤
-- Using temporary: 使用临时表，需优化
-- Using filesort: 额外排序，需优化
-- Using join buffer: 关联查询无索引，需加索引

当Extra中出现Using filesort时，说明ORDER BY字段未命中索引。解决方案是为WHERE条件和ORDER BY字段建立联合索引：ALTER TABLE orders ADD INDEX idx_status_created(status, created_at)。

索引优化：联合索引与覆盖索引设计

索引设计遵循最左前缀原则和选择性优先原则。联合索引的列顺序按选择性从高到低排列，等值查询条件在前，范围查询条件在后：

-- 错误示例：范围查询在等值查询之前
CREATE INDEX idx_wrong ON orders(created_at, status);
-- 优化器只能用到created_at，status无法走索引

-- 正确示例：等值查询在前，范围查询在后
CREATE INDEX idx_correct ON orders(status, created_at);

-- 覆盖索引：查询字段全部包含在索引中，避免回表
CREATE INDEX idx_covering ON orders(status, created_at, user_id, amount);

-- 查询只需访问索引，不回表
SELECT user_id, amount FROM orders
WHERE status = 'PAID' AND created_at > '2026-01-01';

索引不是越多越好。每增加一个索引，写入性能下降约5-10%。单表索引数量建议控制在5-7个以内。冗余索引检测：

-- 查找冗余索引
SELECT
    a.TABLE_NAME,
    a.INDEX_NAME AS index1,
    b.INDEX_NAME AS index2,
    a.COLS AS index1_cols,
    b.COLS AS index2_cols
FROM (
    SELECT TABLE_NAME, INDEX_NAME,
           GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS COLS
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = 'your_db'
    GROUP BY TABLE_NAME, INDEX_NAME
) a
JOIN (
    SELECT TABLE_NAME, INDEX_NAME,
           GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS COLS
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = 'your_db'
    GROUP BY TABLE_NAME, INDEX_NAME
) b ON a.TABLE_NAME = b.TABLE_NAME
    AND a.INDEX_NAME != b.INDEX_NAME
    AND b.COLS LIKE CONCAT(a.COLS, '%');

SQL重写：慢查询排查与优化技巧

开启慢查询日志，捕获执行时间超过阈值的SQL：

-- 慢查询配置
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = ON;

-- 分析慢查询日志
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

常见SQL优化模式：

-- 1. 避免SELECT *，只查需要的列
-- 差: SELECT * FROM orders WHERE user_id = 100;
-- 优: SELECT id, amount, status FROM orders WHERE user_id = 100;

-- 2. 避免在索引列上使用函数
-- 差: WHERE DATE(created_at) = '2026-07-23'
-- 优: WHERE created_at >= '2026-07-23 00:00:00'
--     AND created_at < '2026-07-24 00:00:00'

-- 3. 避免隐式类型转换
-- 差: WHERE phone = 13800138000 (phone是varchar)
-- 优: WHERE phone = '13800138000'

-- 4. 分页优化：深分页使用游标
-- 差: SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- 优: SELECT * FROM orders WHERE id > 1000000
--     ORDER BY id LIMIT 20;

-- 5. 批量插入替代循环单条插入
-- 差: 循环执行 INSERT INTO logs VALUES (...)
-- 优: INSERT INTO logs VALUES (...),(...),(...);

分库分表方案：ShardingSphere实战

单表数据量超过1000万行后，查询性能显著下降。分库分表是横向扩展的标准方案。以Apache ShardingSphere-JDBC为例，配置订单表按user_id取模分片：

# application.yml
spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://db0:3306/order_db_0
        username: root
        password: ${DB_PASSWORD}
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://db1:3306/order_db_1
        username: root
        password: ${DB_PASSWORD}
    rules:
      sharding:
        tables:
          orders:
            actual-data-nodes: ds${0..1}.orders_${0..3}
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: db-mod
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: table-mod
            key-generate-strategy:
              column: id
              key-generator-name: snowflake
        sharding-algorithms:
          db-mod:
            type: MOD
            props:
              sharding-count: 2
          table-mod:
            type: MOD
            props:
              sharding-count: 4
        key-generators:
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 1

分片键选择是分库分表设计的核心。订单表按user_id分片，同一用户的订单落在同一库表，避免跨库JOIN。对于需要按订单ID查询的场景，使用Snowflake算法生成ID，将库表编号编码在ID中，通过ShardingSphere的广播表或绑定表关系处理跨分片查询。

数据库高可用架构与数据备份恢复

MySQL高可用常用方案为主从复制+MHA(Master High Availability)自动故障切换。主从复制配置：

-- 主库配置 (my.cnf)
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = ON
binlog-row-image = MINIMAL

-- 从库配置 (my.cnf)
[mysqld]
server-id = 2
log-bin = mysql-bin
relay-log = relay-bin
read-only = ON
gtid-mode = ON
enforce-gtid-consistency = ON

-- 从库配置复制
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='master-db',
    SOURCE_USER='repl',
    SOURCE_PASSWORD='Repl@2026',
    SOURCE_AUTO_POSITION=1;
START REPLICA;

-- 复制状态检查
SHOW REPLICA STATUS\G

数据备份采用Percona XtraBackup做物理热备，全量备份每周一次，增量备份每天一次。恢复演练每月执行，验证备份可用性。国产数据库方面，OceanBase和TiDB兼容MySQL协议，在分布式高可用和水平扩展上原生支持，适合超大规模数据场景。