MySQL性能调优与分库分表实战：从SQL查询优化到数据库高可用架构搭建

MySQL性能调优的冷启动检查清单

数据库慢不是因为MySQL本身慢，90%的性能问题源自Schema设计不合理、索引使用错误、SQL写法低效。拿到一个慢查询场景，先走一遍这个检查清单：慢查询日志→执行计划→索引命中率→锁等待分析。逐层排除，定位根因。

慢查询诊断与SQL查询优化

开启慢查询日志——这是调优的起点，所有优化必须从数据出发：

# my.cnf配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5      # 超过500ms记录
min_examined_row_limit = 100  # 扫描少于100行的不记录
log_queries_not_using_indexes = 1  # 未使用索引的查询也记录

EXPLAIN执行计划解读——每个SQL优化都要从EXPLAIN开始，重点关注type、rows、Extra三列：

EXPLAIN SELECT o.id, o.total_amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID' AND o.created_at >= '2026-07-01'
ORDER BY o.created_at DESC
LIMIT 20;

-- 理想的执行计划特征：
-- type: ref/range（非ALL全表扫描）
-- key: 使用了正确的复合索引
-- rows: 预估扫描行数与结果集匹配
-- Extra: 无 Using filesort 或 Using temporary

常见优化场景和修复方案：

场景一：复合索引失效——最左前缀原则违反导致索引无法使用：

-- 错误：索引为(user_id, status, created_at)
-- 但查询条件跳过了status，直接用created_at
SELECT * FROM orders WHERE user_id = 100 AND created_at >= '2026-07-01';
-- 索引只用到user_id列

-- 正确：调整索引列顺序或修改查询
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);
-- 或确保查询条件遵循索引列顺序

场景二：隐式类型转换导致索引失效——字段类型和查询参数类型不匹配：

-- 错误：order_no是VARCHAR类型，但传入整数
SELECT * FROM orders WHERE order_no = 20260730001;
-- MySQL对order_no做了隐式转换，索引失效，走全表扫描

-- 正确：保持类型一致
SELECT * FROM orders WHERE order_no = '20260730001';

场景三：分页深翻页性能暴跌——LIMIT 1000000, 20需要扫描1000020行：

-- 错误：深分页
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

-- 方案一：游标分页（推荐）
SELECT * FROM orders WHERE id > ? ORDER BY id LIMIT 20;

-- 方案二：延迟关联
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t
ON o.id = t.id;

分库分表方案设计

单表超过5000万行或单库超过100GB时，需要考虑分库分表。ShardingSphere是当前最成熟的方案：

分片策略选择——分片键决定了数据分布和查询路由：

# ShardingSphere-JDBC配置
spring:
  shardingsphere:
    rules:
      sharding:
        tables:
          orders:
            actual-data-nodes: ds0.orders_${0..15},ds1.orders_${0..15}
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: orders-table-mod
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: orders-db-mod
        sharding-algorithms:
          orders-table-mod:
            type: MOD
            props:
              sharding-count: 16
          orders-db-mod:
            type: MOD
            props:
              sharding-count: 2

分布式主键——分库后不能用自增ID，推荐Snowflake算法：

// ShardingSphere内置的Snowflake生成器
@Bean
public ShardingKeyGenerator snowflakeKeyGenerator() {
    return new SnowflakeKeyGenerator();
}

// 配置中使用
key-generate-strategy:
  column: id
  key-generator-name: snowflake

跨分片查询——分库分表后，跨分片的聚合查询是最大痛点。应对方案：

/* 方案一：广播表——小数据量的维度表复制到所有库 */
/* 适合：用户信息表、配置表等数据量小、更新少的表 */
spring.shardingsphere.rules.sharding.tables.user_info.actual-data-nodes: ds0.user_info,ds1.user_info

# 方案二：冗余字段——在订单表中冗余用户名称
# 避免跨库JOIN查询用户表

# 方案三：归档宽表——异步归档到分析库
# 用Canal监听binlog，归档到ClickHouse做OLAP查询

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

主从复制+自动故障切换——MHA或Orchestrator实现主库故障自动切换：

# MySQL主从复制配置（从库）
[mysqld]
relay_log = /var/log/mysql/relay-bin
read_only = 1
super_read_only = 1
log_slave_updates = 1
gtid_mode = ON
enforce_gtid_consistency = ON

# 半同步复制（至少1个从库确认后才返回）
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 3000  # 3秒超时降级为异步

备份策略——全量+增量+binlog三级备份：

# 每日全量备份
xtrabackup --backup --target-dir=/backup/full/$(date +%Y%m%d) \
  --user=backup --password=<password>

# 增量备份（基于昨日全量）
xtrabackup --backup --target-dir=/backup/incr/$(date +%Y%m%d) \
  --incremental-basedir=/backup/full/$(date -d yesterday +%Y%m%d) \
  --user=backup --password=<password>

# 恢复流程
# 1. 准备全量备份
xtrabackup --prepare --target-dir=/backup/full/20260730
# 2. 合并增量
xtrabackup --prepare --target-dir=/backup/full/20260730 \
  --incremental-dir=/backup/incr/20260730
# 3. 恢复数据
xtrabackup --copy-back --target-dir=/backup/full/20260730

数据库运维没有捷径，每一条慢查询的优化、每一个分片策略的选择、每一次故障切换的验证，都是在降低线上事故的概率。做好数据备份和恢复演练，比任何优化都重要——数据丢了，其他都是零。