MySQL 大表优化实战指南

当数据量达到千万级甚至亿级时，MySQL 的性能会急剧下降。本文详细介绍大表优化的各种策略和实战技巧。

大表的性能瓶颈

单表数据量与性能关系

sql复制
-- 数据量与性能对照表
-- < 1000 行：   毫秒级，任意操作都快
-- < 100万行：  毫秒~秒级，需要合理索引
-- < 1000万行： 秒~十秒级，需要分区/分表
-- > 1000万行： 分钟级，必须综合优化
-- > 1亿行：    困难模式，需要架构级优化

性能下降的典型表现

sql复制
-- 查询突然变慢
SELECT * FROM orders WHERE user_id = 12345;
-- 之前: 10ms
-- 现在: 3000ms

-- 插入变慢
INSERT INTO orders (...) VALUES (...);
-- 之前: 5ms
-- 现在: 500ms

-- 添加索引超时
ALTER TABLE orders ADD INDEX idx_user_id(user_id);
-- ERROR: Lock wait timeout exceeded

优化策略一：索引优化

1. 复合索引的最左前缀原则

sql复制
-- 创建复合索引
ALTER TABLE orders ADD INDEX idx_user_status_time(user_id, status, created_at);

-- ✅ 命中索引的查询
SELECT * FROM orders WHERE user_id = 123;          -- 命中 user_id
SELECT * FROM orders WHERE user_id = 123 AND status = 1;  -- 命中 user_id + status
SELECT * FROM orders WHERE user_id = 123 AND status = 1 AND created_at > '2024-01-01';  -- 全部命中

-- ❌ 不命中索引
SELECT * FROM orders WHERE status = 1;              -- 不命中（缺少 user_id）
SELECT * FROM orders WHERE user_id = 123 AND created_at > '2024-01-01';  -- 只命中 user_id

2. 覆盖索引避免回表

sql复制
-- ❌ 回表查询
CREATE INDEX idx_user_id ON orders(user_id);

SELECT id, user_id, total_amount, status  -- id 和 user_id 在索引中，但 total_amount、status 需要回表
FROM orders
WHERE user_id = 123;

-- ✅ 使用覆盖索引
CREATE INDEX idx_user_id_cover ON orders(user_id, id, total_amount, status);

SELECT id, user_id, total_amount, status  -- 全部在索引中，无需回表
FROM orders
WHERE user_id = 123;

3. 索引列选择

sql复制
-- 高选择性列放前面
-- 假设 user_id 选择性高，status 选择性低
-- 正确顺序：
ALTER TABLE orders ADD INDEX idx_user_status(user_id, status);

-- ❌ 错误顺序（选择性低的放前面）
ALTER TABLE orders ADD INDEX idx_status_user(status, user_id);

4. 前缀索引

sql复制
-- 对长字符串列使用前缀索引
ALTER TABLE users ADD INDEX idx_email_prefix(email(10));

-- 缺点：无法使用覆盖索引优化排序
-- 适用场景：字符串前缀区分度高的列（邮箱、手机号等）

优化策略二：分区表

1. RANGE 分区（按范围）

sql复制
-- 按时间范围分区（最常用）
ALTER TABLE orders PARTITION BY RANGE(created_at) (
    PARTITION p2024_q1 VALUES LESS THAN ('2024-04-01'),
    PARTITION p2024_q2 VALUES LESS THAN ('2024-07-01'),
    PARTITION p2024_q3 VALUES LESS THAN ('2024-10-01'),
    PARTITION p2024_q4 VALUES LESS THAN ('2025-01-01'),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 查询优化：MySQL 自动只扫描相关分区
SELECT * FROM orders WHERE created_at BETWEEN '2024-06-01' AND '2024-08-31';
-- 只扫描 p2024_q2 和 p2024_q3 分区

2. LIST 分区（按枚举值）

sql复制
-- 按状态分区
ALTER TABLE orders PARTITION BY LIST(status) (
    PARTITION p_pending VALUES IN (0),      -- 待支付
    PARTITION p_paid VALUES IN (1),         -- 已支付
    PARTITION p_shipped VALUES IN (2),      -- 已发货
    PARTITION p_completed VALUES IN (3),    -- 已完成
    PARTITION p_refunded VALUES IN (4, 5)   -- 退款相关
);

3. HASH 分区（均匀分布）

sql复制
-- 按 user_id 哈希分区
ALTER TABLE orders PARTITION BY HASH(user_id) PARTITIONS 16;

-- 线性 HASH 分区
ALTER TABLE orders PARTITION BY LINEAR HASH(user_id) PARTITIONS 16;

4. KEY 分区（按主键）

sql复制
-- 按主键分区（MySQL 自动管理）
ALTER TABLE orders PARTITION BY KEY(id) PARTITIONS 32;

5. 分区管理操作

sql复制
-- 拆分分区（将一个大分区拆成两个）
ALTER TABLE orders REORGANIZE PARTITION p_future INTO (
    PARTITION p2025_q1 VALUES LESS THAN ('2025-04-01'),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 合并分区
ALTER TABLE orders REORGANIZE PARTITION p2024_q1, p2024_q2 INTO (
    PARTITION p2024_h1 VALUES LESS THAN ('2024-07-01')
);

-- 删除分区（数据也跟着删除，谨慎使用）
ALTER TABLE orders DROP PARTITION p2023_q1;

-- 清空分区（只清数据，保留分区结构）
ALTER TABLE orders TRUNCATE PARTITION p2024_q1;

优化策略三：分表

1. 垂直分表（按列拆分）

sql复制
-- 将大表按冷热数据分离
-- orders 表拆分：
-- orders_hot: 频繁访问的字段 (id, user_id, status, total_amount, created_at)
-- orders_cold: 冷门字段 (备注、附件、扩展信息等)

CREATE TABLE orders_hot (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    status TINYINT NOT NULL,
    total_amount DECIMAL(10,2),
    created_at DATETIME,
    INDEX idx_user_id(user_id),
    INDEX idx_status(status)
) ENGINE=InnoDB;

CREATE TABLE orders_cold (
    id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,  -- 关联 hot 表
    remark TEXT,
    attachments JSON,
    extra_data JSON,
    INDEX idx_order_id(order_id)
) ENGINE=InnoDB;

2. 水平分表（按行拆分）

sql复制
-- 按 user_id 取模分表（简单但扩容困难）
-- 分成 4 张表：orders_0, orders_1, orders_2, orders_3
-- user_id % 4 = table_index

-- 应用层路由逻辑
function getOrderTable(userId) {
    tableIndex = userId % 4;
    return "orders_" + tableIndex;
}

-- 按时间分表（适合日志类数据）
-- orders_2024_01, orders_2024_02, ...

-- 按地区分表
-- orders_beijing, orders_shanghai, orders_guangzhou

3. 分片中间件方案

sql复制
-- MyCAT / ShardingSphere 配置示例
-- 分片规则：按 user_id 取模分到 8 个表

<schema name="order_db" sqlMaxLimit="100">
    <table name="orders" 
           dataNode="dn$0-7" 
           rule="mod-long" />
</schema>

优化策略四：读写分离

1. 主从复制配置

sql复制
-- 主库配置
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
sync_binlog = 1

-- 从库配置
[mysqld]
server-id = 2
relay-log = relay-bin
read_only = 1
super_read_only = 1

2. 读写分离路由

sql复制
-- 读操作（走从库）
SELECT * FROM orders WHERE id = 123;  -- 读多写少，读从库

-- 写操作（走主库）
INSERT INTO orders (...) VALUES (...);  -- 写操作，必须主库
UPDATE orders SET status = 1 WHERE id = 123;  -- 更新后立即读取，走主库

-- 强制读主库（刚写入的数据）
SELECT * FROM orders WHERE id = 123 /*MASTER*/;

3. GTID 复制模式

sql复制
-- 启用 GTID
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_gtid_simple_recovery = ON

-- 主从切换时自动定位
CHANGE MASTER TO 
    MASTER_HOST = 'new_master',
    MASTER_AUTO_POSITION = 1;

优化策略五：冷热数据分离

1. 归档历史数据

sql复制
-- 将一年前的订单归档到历史表
INSERT INTO orders_archive SELECT * FROM orders 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- 从主表删除已归档数据
DELETE FROM orders WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- 释放空间
OPTIMIZE TABLE orders;

2. 定时归档脚本

bash复制
#!/bin/bash
# 归档脚本 - 每天凌晨 3 点执行

DATE=$(date +%Y%m%d)
BEFORE_DATE=$(date +%Y-%m-%d -d '1 year ago')

# 导出数据
mysql -uroot -p123456 orders -e "
INSERT INTO orders_archive 
SELECT * FROM orders 
WHERE created_at < '${BEFORE_DATE}'
  AND id NOT IN (SELECT id FROM orders_archive);
"

# 确认数据条数一致后删除
ROW_COUNT=$(mysql -uroot -p123456 orders -N -e "
SELECT COUNT(*) FROM orders WHERE created_at < '${BEFORE_DATE}'
")

echo "Will delete $ROW_COUNT rows"

# 删除前确认
mysql -uroot -p123456 orders -e "
DELETE FROM orders WHERE created_at < '${BEFORE_DATE}';
"

# 优化表
mysql -uroot -p123456 orders -e "OPTIMIZE TABLE orders;"

3. 使用分区快速清理

sql复制
-- 如果使用 RANGE 分区，直接 DROP 分区即可
ALTER TABLE orders DROP PARTITION p2023_q1;

-- 这比 DELETE 快 100 倍以上，不锁表，不产生大量 binlog

优化策略六：SQL 语句优化

1. 避免 SELECT *

sql复制
-- ❌ 低效
SELECT * FROM orders WHERE user_id = 123;

-- ✅ 只查需要的字段
SELECT id, status, total_amount, created_at 
FROM orders WHERE user_id = 123;

2. 分页优化

sql复制
-- ❌ 深度分页（OFFSET 很大时极慢）
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

-- ✅ 方案 1：利用主键 ID 游标分页
SELECT * FROM orders 
WHERE id > 1000000 
ORDER BY id 
LIMIT 20;

-- ✅ 方案 2：子查询优化
SELECT * FROM orders 
WHERE id >= (SELECT id FROM orders LIMIT 1000000, 1) 
LIMIT 20;

-- ✅ 方案 3：延迟关联
SELECT o.* 
FROM orders o 
INNER JOIN (SELECT id FROM orders WHERE user_id = 123 LIMIT 1000000, 20) t 
ON o.id = t.id;

3. IN 查询优化

sql复制
-- ❌ IN 中包含大量值
SELECT * FROM orders WHERE user_id IN (1,2,3,...,10000);

-- ✅ 拆分成多个小 IN
SELECT * FROM orders WHERE user_id IN (1,2,3,...,1000);
SELECT * FROM orders WHERE user_id IN (1001,1002,...,2000);
-- ...

-- ✅ 或者使用临时表
CREATE TEMPORARY TABLE temp_users (user_id INT);
INSERT INTO temp_users VALUES (1),(2),(3)...;

SELECT o.* FROM orders o
INNER JOIN temp_users t ON o.user_id = t.user_id;

4. JOIN 优化

sql复制
-- ✅ 小表放左边（大表放驱动表）
SELECT o.*, u.name 
FROM orders o  -- 小表放左边
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 1;

-- ✅ 添加合适的索引
-- orders.user_id 上需要有索引
-- users.id 上需要有主键索引

-- ✅ 避免 N+1 查询
-- ❌ 循环内查询
for user_id in user_ids:
    SELECT * FROM orders WHERE user_id = user_id

-- ✅ 使用 JOIN 一次查询
SELECT o.* FROM orders o
WHERE o.user_id IN (1,2,3,...);

实战案例

案例一：亿级订单表优化

sql复制
-- 原始情况
-- orders 表: 1.2 亿行，120GB
-- 查询慢：SELECT * FROM orders WHERE user_id = ? 超时

-- 优化步骤：
-- 1. 分析查询模式
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

-- 2. 创建合适的索引
ALTER TABLE orders ADD INDEX idx_user_id(user_id);

-- 3. 确认索引生效（rows 减少）
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

-- 4. 实施分区（按月分区）
ALTER TABLE orders PARTITION BY RANGE(YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p2023_01 VALUES LESS THAN (202302),
    PARTITION p2023_02 VALUES LESS THAN (202303),
    -- ... 省略
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 5. 冷热分离
-- 创建只保留最近 6 个月数据的主表
-- 历史数据归档到 orders_archive

-- 优化效果：
-- 单用户查询：3000ms → 15ms

案例二：用户行为日志表

sql复制
-- 原始情况
-- behavior_logs 表: 5 亿行，无分区无索引
-- 写入 QPS: 10000/s，磁盘 IO 100%

-- 优化方案：
-- 1. 改用 MySQL 分区（按天）
ALTER TABLE behavior_logs PARTITION BY RANGE(create_date) (
    PARTITION p2024_01_01 VALUES LESS THAN ('2024-01-02'),
    PARTITION p2024_01_02 VALUES LESS THAN ('2024-01-03'),
    -- ...
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 2. 关闭唯一检查（如果业务允许）
ALTER TABLE behavior_logs DISABLE KEYS;

-- 3. 批量插入优化
INSERT INTO behavior_logs VALUES 
(1, 'click', '2024-01-01', ...),
(2, 'view', '2024-01-01', ...);
-- 批量 1000 行比逐行插入快 50 倍

-- 4. 调整 innodb_flush_log_at_trx_commit
-- 对于日志类数据，可以设为 2（每秒刷新）
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

-- 优化效果：
-- 写入 QPS: 10000 → 50000
-- 磁盘 IO: 100% → 30%

常见面试问题

Q1: 分区表和分表的区别？

Q2: 分区键如何选择？

1. 经常作为查询条件的列
2. 数据分布均匀的列
3. 不会频繁更新的列

Q3: 大表添加索引的正确姿势？

sql复制
-- ❌ 直接 ALTER TABLE（会锁表）
ALTER TABLE orders ADD INDEX idx_user_id(user_id);

-- ✅ 使用 pt-online-schema-change（Percona Toolkit）
pt-online-schema-change \
    --alter "ADD INDEX idx_user_id(user_id)" \
    --user=root \
    --password=xxx \
    D=orders,t=orders

-- ✅ MySQL 5.7+ 使用 ALGORITHM=INPLACE
ALTER TABLE orders ADD INDEX idx_user_id(user_id), ALGORITHM=INPLACE, LOCK=NONE;

Q4: 如何判断是否需要分库分表？

sql复制
-- 单表行数 > 5000 万
-- 单表大小 > 50GB
-- 索引树高度 > 3（磁盘随机 IO 多）
-- 慢查询比例 > 1%

-- 查看表大小
SELECT 
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS '数据大小(MB)',
    ROUND(index_length / 1024 / 1024, 2) AS '索引大小(MB)'
FROM information_schema.tables 
WHERE table_schema = 'your_database'
ORDER BY data_length DESC;

总结

大表优化是一套组合拳：

1. 索引优化：复合索引、覆盖索引
2. 分区表：按时间 RANGE 分区最常用
3. 分表：水平拆分 + 中间件路由
4. 读写分离：主从复制 + 读写分离
5. 冷热分离：归档历史数据
6. SQL 优化：避免 SELECT *、深度分页、N+1

优先考虑索引和分区，通常能解决 80% 的问题。