IT策士 10余年一线大厂经验专注 IT 思维、架构、职场进阶。我会在各个平台持续发布最新文章助你少走弯路。随着业务增长数据库中某些表会快速膨胀到百万、千万甚至亿级。此时即便是最简单的查询也可能变得缓慢索引维护和备份恢复也会成为噩梦。今天我们就来攻克大表优化这个硬骨头重点学习分区表如何将大表化整为零以及如何用在线 DDL 工具安全地修改大表结构。1. 准备环境制造一张大表先创建一张订单表并灌入 100 万条模拟数据用于后续对比。importmysql.connectorimporttimeimportrandom connmysql.connector.connect(host127.0.0.1,port3306,userroot,passwordMyNewPass123!,databaseshop)cursorconn.cursor()# 普通大表无分区cursor.execute(DROP TABLE IF EXISTS orders_big)cursor.execute( CREATE TABLE orders_big(idINT AUTO_INCREMENT, order_date DATE NOT NULL, customer_id INT NOT NULL, amount DECIMAL(10,2), PRIMARY KEY(id, order_date))ENGINEInnoDB)# 批量插入 100 万条数据演示时可用 10 万条这里写 100 万逻辑但可根据环境调整print(⏳ 正在生成 100 万条订单数据...)batch_size10000total1000000# 可改为 100000 节省时间start_date2020-01-01foriinrange(0, total, batch_size): data[]forjinrange(batch_size): idxi j 1day_offsetrandom.randint(0,2000)order_datef2020-01-01# 简化实际可根据 offset 计算# 简单生成日期from datetimeimportdate, timedelta ddate(2020,1,1) timedelta(daysrandom.randint(0,2000))customer_idrandom.randint(1,50000)amountround(random.uniform(10,5000),2)data.append((d,customer_id,amount))cursor.executemany(INSERT INTO orders_big (order_date, customer_id, amount) VALUES (%s,%s,%s), data)conn.commit()print(f 已插入 {min(ibatch_size, total)}/{total} 条,end\r)print(\n✅ 测试数据准备完毕)2. 大表优化的常规手段在考虑分库分表或分区之前应先使用以下技术尽可能减小表的“有效体积”。2.1 合理的数据类型用INT代替BIGINT当主键范围允许时用TIMESTAMP代替DATETIME节省 1 字节但注意 2038 问题避免VARCHAR(255)的滥用使用适合的长度2.2 归档历史数据对于日志、流水等按时间递增的表定期将老数据迁移到归档表或冷存储保持热表行数在可控范围内。# 示例将 2020 年的数据迁移到归档表cursor.execute(CREATE TABLE orders_big_archive LIKE orders_big)cursor.execute( INSERT INTO orders_big_archive SELECT * FROM orders_big WHERE order_date2021-01-01)cursor.execute(DELETE FROM orders_big WHERE order_date 2021-01-01)conn.commit()print(✅ 历史数据已归档)2.3 索引优化删除未使用的索引参考第 27 篇的sys.schema_unused_indexes联合索引遵循最左前缀原则减少冗余索引对于大字段TEXT/BLOB使用前缀索引3. 水平分区把大表拆成小片分区Partitioning是将一张大表在物理存储上分为多个独立的小表分区但逻辑上仍是一张表。应用层无需修改 SQL。3.1 MySQL 支持的分区类型最常用的是RANGE 分区尤其是按日期分区。3.2 创建 RANGE 分区表cursor.execute(DROP TABLE IF EXISTS orders_partitioned)cursor.execute( CREATE TABLE orders_partitioned(idINT AUTO_INCREMENT, order_date DATE NOT NULL, customer_id INT NOT NULL, amount DECIMAL(10,2), PRIMARY KEY(id, order_date))ENGINEInnoDB PARTITION BY RANGE(YEAR(order_date))(PARTITION p2020 VALUES LESS THAN(2021), PARTITION p2021 VALUES LESS THAN(2022), PARTITION p2022 VALUES LESS THAN(2023), PARTITION p2023 VALUES LESS THAN(2024), PARTITION p2024 VALUES LESS THAN(2025), PARTITION p2025 VALUES LESS THAN(2026), PARTITION p_future VALUES LESS THAN MAXVALUE))print(✅ RANGE 分区表创建成功)分区键必须是主键或唯一索引的一部分。因此主键包含了(id, order_date)。3.3 将数据导入分区表cursor.execute( INSERT INTO orders_partitioned SELECT * FROM orders_big WHERE order_date2020-01-01)conn.commit()print(✅ 数据已导入分区表)3.4 分区带来的性能提升分区裁剪Partition Pruning查询时只扫描相关分区跳过不相关的分区。# 对比普通表和分区表的查询速度starttime.time()cursor.execute(SELECT COUNT(*) FROM orders_big WHERE order_date BETWEEN 2024-01-01 AND 2024-12-31)print(f普通表查询耗时: {time.time()-start:.4f} 秒)starttime.time()cursor.execute(SELECT COUNT(*) FROM orders_partitioned WHERE order_date BETWEEN 2024-01-01 AND 2024-12-31)print(f分区表查询耗时: {time.time()-start:.4f} 秒)预期输出示例普通表查询耗时:0.3200秒 分区表查询耗时:0.0120秒EXPLAIN 验证分区裁剪cursor.execute(EXPLAIN SELECT * FROM orders_partitioned WHERE order_date 2024-06-15)forrowincursor.fetchall(): print(row)partitions列会显示只扫描p2024而非所有分区。3.5 分区的管理与维护# 查看分区信息cursor.execute( SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAMEorders_partitioned)print(\n 分区信息)forrowincursor.fetchall(): print(f {row[0]}: {row[1]} 行)# 添加新分区保留未来数据cursor.execute(ALTER TABLE orders_partitioned ADD PARTITION (PARTITION p2026 VALUES LESS THAN (2027)))# 删除旧分区按分区快速清理数据比 DELETE 快得多# cursor.execute(ALTER TABLE orders_partitioned TRUNCATE PARTITION p2020)删除分区的巨大优势DROP PARTITION是 DDL 操作直接删除分区文件不影响其他分区速度极快。4. 分区 vs 分库分表何时用哪个选择建议分区单表数据量大但单机性能足够希望通过透明方式加速按时间/区域的查询简化数据生命周期管理。分库分表数据量超出单机承载能力需要水平扩展写/存储容量。下一篇将深入讨论。5. 在线 DDL大表结构变更的救星当需要对大表执行ALTER TABLE如添加列、修改索引时默认的ALGORITHMCOPY会锁表并全表复制导致长时间业务阻塞。5.1 MySQL 8.0 的原子 DDL 与 INPLACE 算法MySQL 8.0 支持ALGORITHMINSTANT极速加列和ALGORITHMINPLACE原地修改避免了全表复制但仍可能持有元数据锁。# 尝试 INSTANT 加列需 MySQL 8.0.29try: cursor.execute(ALTER TABLE orders_partitioned ADD COLUMN remark VARCHAR(200), ALGORITHMINSTANT)print(✅ INSTANT 加列成功)except Exception as e: print(fINSTANT 不支持: {e})5.2 第三方工具 pt-online-schema-change对于需要全表复制的操作如修改主键、修改列类型Percona 的pt-online-schema-change简称 pt-osc是标准工具。工作原理创建一个与原表结构相同的新表_new在新表上执行ALTER TABLE在原表上创建触发器将变更同步到新表分批将原表数据复制到新表原子性地重命名表完成切换# 用 pt-osc 安全修改表结构pt-online-schema-change\--host127.0.0.1--port3306\--userroot--passwordMyNewPass123!\--alterADD COLUMN remark VARCHAR(200)\Dshop,torders_big\--executePython 中可调用该工具importsubprocessimportos def online_ddl(host, port, user, password, database, table, alter_sql): cmd[pt-online-schema-change, f--host{host}, f--port{port}, f--user{user}, f--password{password},--alter, alter_sql, fD{database},t{table},--execute]resultsubprocess.run(cmd,capture_outputTrue,textTrue)returnresult.returncode, result.stdout# 使用示例需安装 percona-toolkit# ret, out online_ddl(127.0.0.1, 3306, root, MyNewPass123!, shop, orders_big, ADD COLUMN comment TEXT)# print(out)6. 动手试试优化你的大表创建分区表将orders_big改为按月 RANGE 分区然后查询某个月的数据观察 EXPLAIN 中的分区裁剪。对比归档效率用 DELETE 和 TRUNCATE PARTITION 分别清理历史数据对比耗时。尝试 INSTANT 加列在你的 MySQL 版本上测试是否能对分区表执行ALGORITHMINSTANT。用 pt-osc 模拟如果没有真实环境可阅读 Percona 文档理解其触发器和复制机制。7. 总结今天我们攻克了大表优化的核心武器常规手段合理数据类型、归档冷数据、索引瘦身。水平分区RANGE/LIST/HASH/KEY将大表物理切片加速查询并简化数据清理。分区 vs 分库分表分区是单机内的透明方案分库分表解决水平扩展。在线 DDLMySQL 8.0 的 INSTANT/INPLACE以及 pt-online-schema-change 的安全变更。下一篇我们将进入分库分表与分布式扩展探讨当单机极限被突破时如何用 ShardingSphere 等中间件实现真正的水平扩展。想了解更多还可以去各个平台搜索「IT策士」一起升级 IT 思维