跳到主要内容

大表优化

问题​

单表数据量达到千万甚至亿级别，查询和写入越来越慢，如何优化？

答案​

一、大表问题诊断​

症状阈值影响
查询变慢单表 > 2000 万行B+ 树层级增加，IO 增大
DDL 锁表数据量大时 ALTER TABLE 耗时阻塞业务写入
备份慢表数据 > 50GBmysqldump 耗时过长
InnoDB Buffer Pool 不足热数据 > 内存缓存命中率下降
-- 查看表行数和大小
SELECT
    table_name,
    table_rows,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.TABLES
WHERE table_schema = 'mydb'
ORDER BY data_length DESC;

二、优化方案全景​

三、SQL 层面优化​

-- 1. 覆盖索引：避免回表
-- ❌ SELECT * 导致回表
SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC LIMIT 20;
-- ✅ 只查需要的列，建联合索引覆盖
CREATE INDEX idx_user_time ON orders(user_id, created_at, order_no, amount);
SELECT order_no, amount, created_at FROM orders
WHERE user_id = 100 ORDER BY created_at DESC LIMIT 20;

-- 2. 深分页优化（延迟关联）
-- ❌ OFFSET 100 万
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- ✅ 延迟关联
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t
ON o.id = t.id;

-- 3. 范围查询加分区条件
-- ✅ 查询时带上分区键（时间），减少扫描范围
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01'
  AND status = 'completed';

四、分库分表​

分表时机
  • 单表行数 > 2000 万～5000 万
  • 单表数据量 > 10GB
  • 单表并发写入 > 2000 QPS
拆分方式策略适用场景
水平分表按 ID 取模、按时间范围数据量大，结构相同
垂直分表常用字段与不常用字段拆开宽表，列多
水平分库按用户 ID 分片到不同库单库性能瓶颈
// 水平分表：按 user_id 分 16 张表
function getTableName(userId: number): string {
  const shard = userId % 16;
  return `orders_${shard.toString().padStart(2, '0')}`;
}

// 插入数据
async function createOrder(userId: number, order: Order) {
  const table = getTableName(userId);
  await db.query(`INSERT INTO ${table} (user_id, ...) VALUES (?, ...)`, [userId]);
}

分表带来的挑战：

问题解决方案
跨表查询聚合层/中间件（ShardingSphere）
全局排序各分片排序后归并
全局 COUNT维护计数表
分布式 ID雪花算法 / 号段模式
扩容一致性哈希 / 翻倍扩容

五、冷热分离与数据归档​

-- 归档脚本：将 3 个月前的数据移到归档表
-- 分批操作，避免大事务
INSERT INTO orders_archive
SELECT * FROM orders
WHERE created_at < DATE_SUB(NOW(), INTERVAL 3 MONTH)
LIMIT 10000;

DELETE FROM orders
WHERE id IN (
    SELECT id FROM orders_archive
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 3 MONTH)
    LIMIT 10000
);
-- 循环执行直到完成

六、Online DDL​

对大表做 DDL（加列、加索引）必须使用在线方案：

工具原理推荐度
MySQL Online DDLALGORITHM=INPLACE小表可用
gh-ost影子表 + binlog 同步推荐
pt-osc影子表 + 触发器同步可用
# gh-ost 添加索引
gh-ost \
  --host=master \
  --database=mydb \
  --table=orders \
  --alter="ADD INDEX idx_status(status)" \
  --allow-on-master \
  --execute

常见面试问题​

Q1: 单表多大时需要分表？​

答案：

没有绝对标准，一般参考：

  • InnoDB：2000 万~5000 万行（取决于行大小）
  • 核心依据：B+ 树 3~4 层时 IO 次数可控
    • 假设每页 16KB，主键 8B + 指针 6B → 每页约 1170 个指针
    • 3 层：1170 × 1170 × 16 ≈ 2190 万行
    • 4 层会增加一次磁盘 IO

更重要的是看实际查询 RT，如果 P99 < 50ms 且 Buffer Pool 够用，2 亿行也可以不分。

Q2: 分表后如何处理跨表查询？​

答案：

场景方案
带分片键查询路由到指定表
不带分片键查询所有分表查询后合并（scatter-gather）
全局排序每张表 Top N → 归并排序
聚合统计异步汇总到统计表 / OLAP 引擎

推荐使用中间件（ShardingSphere、Vitess）屏蔽分片细节。

Q3: 如何进行大表的不停机迁移？​

答案：

  1. 双写阶段：新旧表同时写入
  2. 历史数据迁移：分批将旧表数据同步到新表
  3. 数据校验：pt-table-checksum 对比数据一致性
  4. 切读：逐步将读流量切到新表
  5. 停旧写：确认无误后停止旧表写入
  6. 清理：下线旧表

关键：全程有回滚方案，灰度切换。

相关链接​