MySQL性能调优实战：分库分表方案设计与SQL查询优化全流程

分库分表决策：什么时候该拆

MySQL单表数据量超过2000万行或单库超过500GB时，查询性能会明显退化——这不是MySQL的bug，而是B+Tree索引在数据量增长后，树的层级加深、磁盘IO放大。但分库分表有代价：分布式JOIN、跨片聚合、数据迁移复杂度陡增。决策框架如下：

# 分库分表决策树
单表行数 < 500万    → 不拆，优化索引和查询
单表行数 500万-2000万 → 评估QPS和慢查询，读写分离优先
单表行数 > 2000万    → 必须拆，选择分片策略
单库数据量 > 500GB   → 分库，每个库控制在200GB以内

# 不该拆的场景
- 大量跨表JOIN查询
- 全表扫描型统计报表
- 数据增长缓慢（年增 < 100万行）
- 团队无分库分表运维经验

分片策略选择与ShardingSphere配置

ShardingSphere-JDBC是目前Java生态中最成熟的分片中间件，以JAR包方式嵌入应用，无需独立Proxy进程，性能损耗小。

# ShardingSphere 5.5 分片配置
# application-sharding.yml
rules:
  - !SHARDING
    tables:
      t_order:
        actualDataNodes: ds_${0..3}.t_order_${0..15}
        tableStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: order-table-mod
        databaseStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: order-db-mod
        keyGenerateStrategy:
          column: id
          keyGeneratorName: snowflake
      t_order_item:
        actualDataNodes: ds_${0..3}.t_order_item_${0..15}
        tableStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: order-table-mod
        databaseStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: order-db-mod

    shardingAlgorithms:
      order-table-mod:
        type: MOD
        props:
          sharding-count: 16
      order-db-mod:
        type: MOD
        props:
          sharding-count: 4

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

分片键选择是整个方案成败的关键。订单表用user_id做分片键（而不是order_id），因为查询几乎总是按用户维度。支付记录、物流信息等关联表必须使用相同的分片键，否则跨片JOIN不可用。

跨分片查询的解决方案

分库分表后，非分片键查询需要扫描所有分片。常见解法：

# 方案一：广播表（小维度表全量冗余）
# 如：商品分类、地区编码等变更频率低的表
broadcastTables:
  - t_product_category
  - t_region

# 方案二：冗余索引表（基因法）
# 在order_id中嵌入user_id的路由信息
# order_id = snowflake_id | (user_id % 4 << 2) | (user_id % 16)
# 解析order_id即可直接定位分片，无需广播

# 方案三：ES异构索引
# 将订单宽表同步到Elasticsearch
# 非分片键查询走ES，拿到order_id后再回查MySQL

# 配置Canal同步到ES
canal.instance.master.address=127.0.0.1:3306
canal.instance.dbUsername=canal
canal.instance.dbPassword=canal_pwd
canal.instance.filter.regex=ds_0\.t_order_.*,ds_1\.t_order_.*

# Canal Adapter ES同步配置
dataSourceKey: defaultDS
destination: order_es
groupId: es_sync
outerAdapterKey: es_order
esIndex: order_index
sql: "SELECT o.id AS _id, o.order_no, o.user_id, o.status,
      o.total_amount, o.create_time, p.product_name,
      u.nickname FROM t_order o LEFT JOIN t_order_item i
      ON o.id = i.order_id LEFT JOIN t_product p
      ON i.product_id = p.id LEFT JOIN t_user u
      ON o.user_id = u.id"

SQL查询优化实战案例

分库分表只是手段，SQL本身的优化才是基本功。以下是几个高频慢查询场景的优化路径。

场景一：深度分页

# 问题SQL：按创建时间倒序分页，翻到第1000页
SELECT * FROM t_order WHERE status = 'PAID'
ORDER BY create_time DESC LIMIT 999000, 1000;
# 扫描100万行只返回1000行，效率极低

# 优化方案：游标分页（推荐）
SELECT * FROM t_order WHERE status = 'PAID'
  AND create_time < '2026-07-20 10:30:00'
ORDER BY create_time DESC LIMIT 1000;
# 每次查询都带上上一页最后一条记录的时间戳作为游标

# 优化方案：覆盖索引 + 延迟关联（兼容传统分页）
SELECT o.* FROM t_order o
INNER JOIN (
  SELECT id FROM t_order WHERE status = 'PAID'
  ORDER BY create_time DESC LIMIT 999000, 1000
) AS tmp ON o.id = tmp.id;
# 子查询走覆盖索引，避免回表100万次

场景二：索引失效

# 隐式类型转换导致索引失效
# user_id 是 VARCHAR(32)，传入参数是整数
SELECT * FROM t_user WHERE user_id = 12345;
# MySQL会将user_id转为数字比较，无法走索引

# 修复：确保参数类型一致
SELECT * FROM t_user WHERE user_id = '12345';

# OR条件导致索引失效
SELECT * FROM t_order WHERE user_id = 'U001' OR status = 'PAID';
# 优化：拆分为UNION ALL
SELECT * FROM t_order WHERE user_id = 'U001'
UNION ALL
SELECT * FROM t_order WHERE status = 'PAID' AND user_id != 'U001';

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

分库分表环境下，高可用架构比单库更复杂。每个分片都需要独立的主从复制和故障切换机制：

# MySQL Group Replication配置（单主模式）
# my.cnf
[mysqld]
plugin_load_add = 'group_replication.so'
group_replication_group_name = "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
group_replication_start_on_boot = OFF
group_replication_local_address = "192.168.1.10:33061"
group_replication_group_seeds = "192.168.1.10:33061,192.168.1.11:33061,192.168.1.12:33061"
group_replication_single_primary_mode = ON
group_replication_enforce_update_everywhere_checks = OFF

# 自动化备份脚本（每个分片独立备份）
#!/bin/bash
BACKUP_DIR=/data/backup/mysql
DATE=$(date +%Y%m%d)

for db in ds_0 ds_1 ds_2 ds_3; do
    mysqldump --single-transaction --quick \
      --compress --flush-logs --master-data=2 \
      -h $db.host -u backup -p$BACKUP_PASS \
      $db > $BACKUP_DIR/${db}_${DATE}.sql.gz &
done

wait
# 清理7天前的备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

数据迁移实战中，双写方案（新老库同时写入，通过Canal比对数据一致性）是分库分表扩容的标准做法。国产数据库（如OceanBase、TiDB）的兼容模式可以直接替换MySQL协议，但SQL兼容性和执行计划差异需要逐条验证。