MySQL性能调优实战：从SQL查询优化到分库分表方案全解析

数据库运维的核心诉求是稳定和性能。MySQL作为使用最广泛的关系型数据库，其性能瓶颈往往不在硬件而在配置和SQL。本文从SQL查询优化、索引设计、高可用架构到分库分表方案，提供可执行的调优指南。

一、SQL查询优化：执行计划分析

SQL查询优化始于EXPLAIN执行计划分析。EXPLAIN输出的type、key、rows、Extra四个字段是判断SQL健康度的核心指标。

-- 1. 查看执行计划
EXPLAIN SELECT o.id, o.total_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;

-- 执行计划关键字段解读：
-- type: 访问类型，从好到差：system > const > eq_ref > ref > range > index > ALL
-- key: 实际使用的索引
-- rows: 预估扫描行数
-- Extra: Using index（覆盖索引）、Using filesort（文件排序）、Using temporary（临时表）

-- 2. 开启慢查询日志定位问题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';

-- 3. 查看当前正在执行的SQL
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 5
ORDER BY time DESC;

常见SQL调优场景与方案：

-- 场景1: 深度分页优化
-- 问题：LIMIT 1000000, 20 需要扫描100万行再丢弃
-- 优化：基于游标的延迟关联
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders
    WHERE status = 'PAID'
    ORDER BY created_at DESC
    LIMIT 1000000, 20
) t ON o.id = t.id;

-- 更优方案：基于主键游标
SELECT * FROM orders
WHERE id > #{last_id} AND status = 'PAID'
ORDER BY id ASC
LIMIT 20;

-- 场景2: 避免隐式类型转换导致索引失效
-- 问题：phone字段是varchar但查询传了整数
SELECT * FROM users WHERE phone = 13800138000;        -- 索引失效
SELECT * FROM users WHERE phone = '13800138000';      -- 索引生效

-- 场景3: OR条件优化为UNION ALL
-- 问题：OR可能导致无法使用索引
SELECT * FROM orders WHERE user_id = 100 OR status = 'PAID';
-- 优化：拆分为UNION ALL
SELECT * FROM orders WHERE user_id = 100
UNION ALL
SELECT * FROM orders WHERE status = 'PAID' AND user_id != 100;

-- 场景4: 聚合查询优化 - 预计算
-- 问题：实时COUNT在大表上性能差
SELECT status, COUNT(*) FROM orders GROUP BY status;
-- 优化：维护汇总表
SELECT status, cnt FROM order_status_summary
WHERE stat_date = CURDATE();

二、MySQL索引设计与优化

索引是SQL查询优化的物理基础。索引设计有三个原则：最左前缀匹配、覆盖索引优先、避免冗余索引。联合索引的列顺序必须与查询条件匹配。

-- 联合索引设计示例
-- 业务查询模式：
-- 1. WHERE user_id = ? AND status = ? ORDER BY created_at
-- 2. WHERE user_id = ? AND created_at >= ?
-- 3. WHERE status = ? AND created_at >= ?

-- 最优联合索引：user_id, status, created_at
-- 覆盖查询1: 完全匹配最左前缀
-- 覆盖查询2: 匹配user_id前缀 + created_at范围扫描
-- 查询3无法使用该索引（缺少user_id）

-- 创建索引
ALTER TABLE orders ADD INDEX idx_user_status_created
    (user_id, status, created_at);

-- 覆盖索引：查询字段都在索引中，避免回表
SELECT user_id, status, created_at FROM orders
WHERE user_id = 100 AND status = 'PAID';

-- 查看索引使用情况
SELECT
    object_schema AS db,
    object_name AS table_name,
    index_name,
    count_read AS read_count,
    count_fetch AS rows_fetched
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
ORDER BY count_read DESC;

MySQL性能调优还需要调整Buffer Pool。InnoDB Buffer Pool是影响性能最关键的参数，建议设置为物理内存的60-75%：

# /etc/my.cnf - InnoDB核心参数调优
[mysqld]
# Buffer Pool
innodb_buffer_pool_size = 32G         # 服务器64G内存分配50%
innodb_buffer_pool_instances = 8      # 多实例减少锁竞争
innodb_buffer_pool_load_at_startup = ON  # 重启后预加载热数据

# 日志与刷盘
innodb_log_file_size = 2G             # Redo Log大小
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1    # 每次事务提交刷盘（最安全）
innodb_flush_method = O_DIRECT        # 跳过OS缓存直接写磁盘

# 并发控制
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 2000             # SSD建议2000以上
innodb_io_capacity_max = 4000

# 连接管理
max_connections = 500
thread_cache_size = 64
table_open_cache = 4096
open_files_limit = 65535

# SQL模式
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

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

数据库高可用架构的核心是主从复制 + 自动故障切换。MHA（Master High Availability）和Orchestrator是常用方案，但更现代的选择是MySQL InnoDB Cluster based on Group Replication。

-- 主从复制配置

-- Master节点 my.cnf配置
[mysqld]
server_id = 1
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = MINIMAL
gtid_mode = ON
enforce_gtid_consistency = ON

-- 创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'StrongReplPass!23';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- Slave节点 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='10.0.1.10',
    SOURCE_PORT=3306,
    SOURCE_USER='repl',
    SOURCE_PASSWORD='StrongReplPass!23',
    SOURCE_AUTO_POSITION=1;  -- GTID自动定位

START REPLICA;

-- 验证复制状态
SHOW REPLICA STATUS\G
-- 关键指标:
-- Replica_IO_Running: Yes
-- Replica_SQL_Running: Yes
-- Seconds_Behind_Source: 0

数据备份恢复策略需要满足RPO（数据恢复点目标）和RTO（恢复时间目标）：

#!/bin/bash
# 全量物理备份脚本 (xtrabackup)
BACKUP_DIR="/data/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
FULL_DIR="${BACKUP_DIR}/full_${DATE}"

# 全量备份
xtrabackup --backup \
    --target-dir=${FULL_DIR} \
    --user=backup --password=BackupPass!23 \
    --compress --compress-threads=4

# 增量备份（基于全量）
xtrabackup --backup \
    --target-dir=${BACKUP_DIR}/incr_${DATE} \
    --incremental-basedir=${FULL_DIR} \
    --user=backup --password=BackupPass!23

# 恢复流程
# 1. 解压
xbstream -x -C /var/lib/mysql_restore/ < full_backup.xb
# 2. 准备
xtrabackup --prepare --target-dir=/var/lib/mysql_restore/
# 3. 恢复
xtrabackup --copy-back --target-dir=/var/lib/mysql_restore/
# 4. 修改权限并启动
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

四、分库分表方案：ShardingSphere实战

单表数据量超过千万行后查询性能开始退化，分库分表是必然选择。ShardingSphere-JDBC是Java生态最成熟的分库分表中间件，以无侵入方式实现数据分片。

# application.yml - ShardingSphere分库分表配置
spring:
  shardingsphere:
    mode:
      type: Cluster                # 集群模式，配置存于ZooKeeper
      repository:
        type: ZooKeeper
        props:
          namespace: governance
          server-lists: zk1:2181,zk2:2181,zk3:2181
    datasource:
      names: ds0,ds1,ds2,ds3
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://10.0.1.20:3306/order_db_0
        username: app
        password: AppPass!23
      # ds1, ds2, ds3配置类似...

    rules:
      sharding:
        tables:
          orders:
            actualDataNodes: ds${0..3}.orders_${0..15}
            databaseStrategy:
              standard:
                shardingColumn: user_id
                shardingAlgorithmName: db_mod
            tableStrategy:
              standard:
                shardingColumn: order_id
                shardingAlgorithmName: table_mod
            keyGenerateStrategy:
              column: order_id
              keyGeneratorName: snowflake

        shardingAlgorithms:
          db_mod:
            type: MOD
            props:
              sharding-count: 4
          table_mod:
            type: MOD
            props:
              sharding-count: 16

        keyGenerators:
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 1

    props:
      sql-show: true               # 打印实际执行SQL（调试用）

分库分表后跨库查询和分布式事务是新挑战。ShardingSphere支持基于XA和BASE的分布式事务：

// Java代码 - 分布式事务示例
@Service
@RequiredArgsConstructor
public class OrderTransactionService {

    private final OrderMapper orderMapper;
    private final InventoryMapper inventoryMapper;

    @ShardingSphereTransactionType(TransactionType.XA)  // XA事务
    @Transactional
    public void createOrderWithDeduct(Order order, Long productId, int qty) {
        orderMapper.insert(order);                 // 可能落到ds0
        inventoryMapper.deduct(productId, qty);     // 可能落到ds2
        // ShardingSphere自动协调XA事务，保证原子性
    }
}

数据迁移实战中，双写方案最为稳妥。新分片库与旧单库并行写入，通过数据同步工具（如Canal监听binlog）保证一致，灰度切读，最终下线旧库。NoSQL选型应用方面，Redis处理热点缓存和分布式锁，MongoDB适合文档型不固定结构数据，Elasticsearch专攻全文检索。国产数据库如OceanBase、TiDB在兼容MySQL协议的同时提供分布式能力，是应对超大规模数据场景的有效方案。数据库运维没有银弹，MySQL性能调优需要结合具体业务场景和监控数据持续迭代。