🚀 MySQL大表优化实战：从千万级数据到性能飞升的完整指南

用户376370013187
2 阅读9分钟

本文基于真实业务场景，分享MySQL大表优化的完整实践路径，包含性能对比数据和踩坑经验。

一、实战背景：当5000万数据遇上性能瓶颈

1.1 真实案例：订单表的性能危机

某电商平台的订单表（order_table）在业务快速发展中遇到了严重性能问题：

CREATE TABLE `order_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(64) NOT NULL COMMENT '订单号',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `total_amount` decimal(10,2) NOT NULL COMMENT '订单金额',
  `order_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单状态',
  `pay_time` datetime DEFAULT NULL COMMENT '支付时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `remark` text COMMENT '订单备注',
  `extra_info` text COMMENT '扩展信息',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

问题现象：

  • 表数据量：5200万行
  • 表大小：45GB
  • 查询订单详情：平均响应时间 2.3秒
  • 按用户查询订单：平均响应时间 1.8秒
  • 按时间范围查询：平均响应时间 3.5秒
  • 库存扣减锁等待：高峰期超10秒

1.2 性能分析：EXPLAIN揭示真相

-- 慢查询SQL示例
EXPLAIN SELECT * FROM order_table
WHERE user_id = 123456
ORDER BY create_time DESC
LIMIT 20;

执行计划分析：

+----+-------------+-------------+------------+------+------------------+---------+---------+-------+----------+-----------------------+
| id | select_type | table       | partitions | type | possible_keys    | key     | key_len | rows  | filtered | Extra                 |
+----+-------------+-------------+------------+------+------------------+---------+---------+-------+----------+-----------------------+
|  1 | SIMPLE      | order_table | NULL       | ref  | idx_user_id     | idx_user_id | 8       | 1258  |   100.00 | Using filesort        |
+----+-------------+-------------+------------+------+------------------+---------+---------+-------+----------+-----------------------+

问题诊断：

  1. ✅ 使用了索引 idx_user_id
  2. ❌ 出现 Using filesort，需要额外排序
  3. ❌ 扫描行数虽不多，但全表扫描导致大量磁盘I/O

二、分层优化策略：从易到难的渐进式方案

2.1 第一层：SQL优化（立竿见影）

优化1：索引优化 - 解决filesort问题

问题：  ORDER BY create_time 导致filesort
方案：  创建联合索引

-- 原索引
KEY `idx_user_id` (`user_id`)

-- 优化后
KEY `idx_user_create` (`user_id`, `create_time` DESC)

性能提升：

优化前：平均响应时间 1.8秒
优化后：平均响应时间 0.3秒 ⚡
提升幅度：83.3%

优化2：覆盖索引 - 避免回表

-- 优化前：SELECT * 导致需要回表
SELECT * FROM order_table
WHERE user_id = 123456
ORDER BY create_time DESC
LIMIT 20;

-- 优化后：只查询需要的字段
SELECT id, order_no, total_amount, order_status, create_time
FROM order_table
WHERE user_id = 123456
ORDER BY create_time DESC
LIMIT 20;

-- 更优：创建覆盖索引
KEY `idx_cover` (`user_id`, `create_time` DESC, `order_no`, `total_amount`, `order_status`)

性能提升：

优化前：平均响应时间 0.3秒
优化后：平均响应时间 0.08秒 ⚡⚡
提升幅度：73.3%

2.2 第二层：表结构优化（源头治理）

优化3：字段类型优化

-- 优化前
CREATE TABLE `order_table` (
  `user_id` bigint(20) NOT NULL,
  `order_status` varchar(20) NOT NULL,
  `pay_time` datetime DEFAULT NULL,
  ...
);

-- 优化后
CREATE TABLE `order_table` (
  `user_id` bigint(20) UNSIGNED NOT NULL COMMENT '用户ID',
  `order_status` tinyint(2) NOT NULL DEFAULT '0' COMMENT '0:待支付 1:已支付 2:已取消',
  `pay_time` timestamp NULL DEFAULT NULL COMMENT '支付时间',
  ...
);

优化效果：

字段存储空间减少：约40%
索引体积减小：约35%
查询速度提升：约15%

优化4：大字段分离

-- 优化前：大字段在主表中
ALTER TABLE `order_table` DROP COLUMN `remark`;
ALTER TABLE `order_table` DROP COLUMN `extra_info`;

-- 优化后：拆分到扩展表
CREATE TABLE `order_ext` (
  `order_id` bigint(20) NOT NULL,
  `remark` text COMMENT '订单备注',
  `extra_info` text COMMENT '扩展信息',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

优化效果：

主表行大小：从 512字节 → 256字节
查询I/O减少：约50%
缓存利用率提升：约60%

2.3 第三层：数据归档（降低活跃数据量）

优化5：冷热数据分离

-- 创建归档表
CREATE TABLE `order_table_archive` LIKE `order_table`;

-- 归档策略：归档3个月前的已完成订单
INSERT INTO `order_table_archive`
SELECT * FROM `order_table`
WHERE create_time < DATE_SUB(NOW(), INTERVAL 3 MONTH)
AND order_status IN (2, 3, 4)  -- 2:已完成 3:已取消 4:已退款
LIMIT 10000;

-- 分批删除，避免锁表
DELETE FROM `order_table`
WHERE id IN (
  SELECT id FROM (
    SELECT id FROM `order_table`
    WHERE create_time < DATE_SUB(NOW(), INTERVAL 3 MONTH)
    AND order_status IN (2, 3, 4)
    LIMIT 10000
  ) AS t
);

归档效果：

归档前：5200万行，45GB
归档后：2800万行，18GB
活跃数据减少：46.2%
平均查询速度提升：65%

使用pt-archiver工具

# 使用Percona Toolkit进行在线归档
pt-archiver \
  --source h=127.0.0.1,D=orders,t=order_table \
  --dest h=127.0.0.1,D=orders_archive,t=order_table_archive \
  --where "create_time < DATE_SUB(NOW(), INTERVAL 3 MONTH) AND order_status IN (2,3,4)" \
  --limit 1000 \
  --sleep 0.1 \
  --progress 5000 \
  --statistics \
  --purge \
  --bulk-insert 1000

pt-archiver优势：

  • 在线操作，不锁表
  • 自动分批处理
  • 支持断点续传
  • 实时进度显示

2.4 第四层：分区表（MySQL 8.0+特性）

优化6：按时间范围分区

-- 创建分区表
CREATE TABLE `order_table_partitioned` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(64) NOT NULL COMMENT '订单号',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `total_amount` decimal(10,2) NOT NULL COMMENT '订单金额',
  `order_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单状态',
  `pay_time` timestamp NULL DEFAULT NULL COMMENT '支付时间',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`, `create_time`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (TO_DAYS(create_time)) (
  PARTITION p202410 VALUES LESS THAN (TO_DAYS('2024-11-01')),
  PARTITION p202411 VALUES LESS THAN (TO_DAYS('2024-12-01')),
  PARTITION p202412 VALUES LESS THAN (TO_DAYS('2025-01-01')),
  PARTITION p202501 VALUES LESS THAN (TO_DAYS('2025-02-01')),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

分区查询优化：

-- 查询自动裁剪，只扫描对应分区
EXPLAIN PARTITIONS SELECT * FROM order_table_partitioned
WHERE create_time >= '2024-12-01' AND create_time < '2025-01-01';

-- 执行计划显示：只扫描 p202412 分区

分区效果：

查询特定月份：只扫描1个分区，性能提升 85%
数据归档：直接 DROP PARTITION，秒级完成
维护效率：索引重建时间减少 70%

2.5 第五层：分库分表（架构级突破）

分片策略选择

场景分析：

  • 数据量：预计1年内增长到2亿
  • 查询模式：90%按user_id查询，10%按order_no查询
  • 写入频率：高峰期2000 TPS

分片方案：按user_id哈希分片

-- 分片数量：16个分片
-- 分片键：user_id
-- 分片算法：user_id % 16

-- 创建分片表（以分片0为例）
CREATE TABLE `order_table_0` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(64) NOT NULL COMMENT '订单号',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `total_amount` decimal(10,2) NOT NULL COMMENT '订单金额',
  `order_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单状态',
  `pay_time` timestamp NULL DEFAULT NULL COMMENT '支付时间',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

三、性能监控与持续优化

3.1 慢查询监控

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过1秒的查询记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

-- 使用pt-query-digest分析慢查询
pt-query-digest /var/log/mysql/mysql-slow.log --since '24h' > slow_report.txt

3.2 索引使用监控

-- 查看索引使用情况（MySQL 8.0+）
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'orders'
AND OBJECT_NAME = 'order_table'
ORDER BY SUM_TIMER_WAIT DESC;

-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'orders';

四、避坑指南与最佳实践

4.1 常见错误

错误1：过度索引

-- ❌ 错误：创建过多索引
ALTER TABLE order_table ADD INDEX idx_1 (user_id);
ALTER TABLE order_table ADD INDEX idx_2 (create_time);
ALTER TABLE order_table ADD INDEX idx_3 (order_status);
ALTER TABLE order_table ADD INDEX idx_4 (total_amount);
ALTER TABLE order_table ADD INDEX idx_5 (pay_time);

-- ✅ 正确：只创建必要的索引
ALTER TABLE order_table ADD INDEX idx_user_status (user_id, order_status);
ALTER TABLE order_table ADD INDEX idx_create_time (create_time);

错误2：索引失效

-- ❌ 错误：索引列使用函数
SELECT * FROM order_table
WHERE DATE(create_time) = '2024-12-01';

-- ✅ 正确：改写为范围查询
SELECT * FROM order_table
WHERE create_time >= '2024-12-01 00:00:00'
AND create_time < '2024-12-02 00:00:00';

错误3：OR条件导致全表扫描

-- ❌ 错误：OR条件
SELECT * FROM order_table
WHERE user_id = 123456 OR order_no = 'ORDER123456';

-- ✅ 正确：使用UNION ALL
SELECT * FROM order_table WHERE user_id = 123456
UNION ALL
SELECT * FROM order_table WHERE order_no = 'ORDER123456';

4.2 最佳实践

  1. 先监控后优化

    • 收集性能数据
    • 分析瓶颈根因
    • 制定优化方案
    • 验证优化效果

  2. 小步快跑

    • 一次只优化一个维度
    • 测试验证后再进行下一步
    • 保留回滚方案

  3. 持续监控

    • 建立性能基线
    • 定期检查慢查询
    • 监控索引使用情况

五、优化效果对比

综合优化效果

优化阶段数据量查询响应时间写入TPS索引大小
优化前5200万2.3秒80018GB
SQL优化后5200万0.3秒85020GB
表结构优化后5200万0.2秒95013GB
数据归档后2800万0.12秒12007GB
分区表后2800万0.08秒13007.5GB
分库分表后1250万/表0.05秒25003GB/表

总体提升：

查询性能提升：97.8%
写入性能提升：212.5%
存储空间优化：83.3%
索引空间优化：81.7%

六、工具推荐

性能分析工具

  • pt-query-digest - 慢查询分析
  • pt-index-usage - 索引使用分析
  • pt-mysql-summary - MySQL健康检查
  • mysqldumpslow - 慢查询日志分析

监控工具

  • Prometheus + Grafana - 性能监控
  • Percona PMM - 专业数据库监控
  • Zabbix - 综合监控平台

数据归档工具

  • pt-archiver - 在线数据归档
  • mydumper - 快速数据导出

分库分表中间件

  • ShardingSphere - 开源分布式数据库中间件
  • MyCat - 开源数据库中间件
  • Vitess - YouTube的数据库分片方案

七、总结

MySQL大表优化是一个系统工程，需要从多个维度综合考量：

  1. SQL优化：最快速、成本最低的优化方式
  2. 表结构优化：从源头减少数据量
  3. 数据归档：降低活跃数据量
  4. 分区表：提升查询和维护效率
  5. 分库分表：突破单表性能瓶颈

优化过程中要遵循"先易后难、先软后硬"的原则，优先采用低成本、低风险的方案，再根据业务需求逐步升级到架构级优化。

记住：没有银弹，只有最适合的方案。每个业务场景都有其特殊性，需要结合实际情况制定优化策略。

关于作者：

本文作者专注于后端技术架构，擅长MySQL性能优化、分布式系统设计。欢迎在评论区交流讨论，点赞收藏支持一下！

avatar
文章
阅读
粉丝