MySQL大表优化是数据库运维和开发中常见的需求，当单表数据量超过一定阈值后，常规的增删改查操作都会出现性能下降，需要从多个层面逐步推进治理工作。
大表优化的前置评估
在动手优化前，首先需要明确大表当前的状态，避免盲目调整。可以从以下几个维度做评估：
- 统计表的基础信息：数据量、表大小、单条记录平均长度、字段数量
- 梳理高频访问的SQL语句，记录其执行耗时、扫描行数、是否走索引
- 确认表的读写比例，是读多写少还是写多读少，或是读写均衡
- 查看表的碎片率，判断是否需要做碎片整理
可以通过如下SQL查询表的基础信息：
-- 查询指定数据库下所有表的信息，包含数据量、表大小等
SELECT
table_name,
table_rows,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS table_size_mb,
ROUND((data_free / 1024 / 1024), 2) AS free_size_mb
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
ORDER BY table_size_mb DESC;
第一步：优化表结构与索引
不合理的表结构和索引是大表性能问题的最常见原因，优先从这个层面调整成本最低，收益也最明显。
表结构优化
首先需要检查表的字段设计是否合理：
- 避免使用过大的字段类型，比如能用
VARCHAR(50)就不要使用VARCHAR(255)，能用INT就不要使用BIGINT - 尽量把字段设置为NOT NULL，避免NULL值带来的存储和索引额外开销
- 如果表中有大文本、二进制字段，且不是高频访问，可以考虑拆分到单独的扩展表
- 删除表中不再使用的冗余字段
索引优化
索引优化需要结合高频SQL的执行计划来做调整：
- 为where条件、join关联字段、order by、group by涉及的字段添加合适的索引
- 避免冗余索引，比如已经有(a,b)的联合索引，就不需要单独给a字段加索引
- 控制单表的索引数量，一般建议不超过5个，避免写入时索引维护开销过大
- 定期删除长期未使用的无效索引
可以通过如下方式查看SQL的执行计划，判断索引使用情况：
-- 查看SQL的执行计划，关注type、key、rows字段 EXPLAIN SELECT * FROM your_table WHERE user_id = 100 AND status = 1;
第二步：优化SQL语句
即使表结构和索引合理，低效的SQL语句也会导致大表查询性能极差，需要针对性调整。
- 避免使用
SELECT *，只查询需要的字段，减少数据传输和解析开销 - 避免全表扫描，尽量通过索引过滤数据，减少扫描行数
- 避免使用函数、表达式对索引字段做处理，比如
WHERE DATE(create_time) = '2024-01-01'会导致索引失效 - 分页查询时，避免使用大的偏移量，比如
LIMIT 1000000, 10，可以改成通过主键游标分页 - 减少不必要的join操作，尤其是多张大表的join，尽量把部分逻辑放到应用层处理
分页优化的示例代码如下：
-- 低效的大偏移量分页 SELECT * FROM your_table ORDER BY id LIMIT 1000000, 10; -- 优化后的游标分页，假设上次查询的最大id是1000000 SELECT * FROM your_table WHERE id > 1000000 ORDER BY id LIMIT 10;
第三步：数据归档与清理
如果大表中有大量历史冷数据，且业务上不需要实时访问，可以通过数据归档减少表的有效数据量。
归档的常见方案：
- 按时间维度归档，比如把3个月前的数据迁移到历史归档表，归档表可以放在单独的数据库实例，或者存储到成本更低的存储引擎中
- 按业务状态归档，比如已经完结的订单数据迁移到归档表
- 定期清理表中无用的测试数据、重复数据
归档操作需要注意：
- 归档过程尽量避开业务高峰期，避免影响线上服务
- 归档前做好数据备份，防止数据丢失
- 归档后验证归档数据的完整性和线上查询的正确性
第四步：分库分表
如果经过前面的优化后，单表数据量仍然过大，性能无法满足需求，就需要考虑分库分表。
分表策略
- 水平分表：把同一张表的数据按一定规则拆分到多个结构相同的表中，比如按用户ID取模、按时间范围拆分
- 垂直分表：把表的字段按访问频率拆分，高频字段放在主表，低频字段放在扩展表
分库策略
如果单实例的数据库性能瓶颈无法突破，可以把不同的表拆分到不同的数据库实例，或者把同一个表的不同分片放到不同的数据库实例，分摊单实例的读写压力。
分库分表需要结合业务场景选择合适的分片键，同时需要考虑分片后的查询、事务、扩容等问题，一般建议优先使用成熟的中间件，避免自己实现复杂的分片逻辑。
第五步：其他辅助优化
除了上面的核心步骤，还可以结合其他方案进一步提升大表的性能：
- 开启MySQL的查询缓存，不过需要注意查询缓存的失效机制，适合读多写少且查询结果重复度高的场景
- 调整MySQL的配置参数，比如
innodb_buffer_pool_size，尽量让热点数据缓存在内存中 - 对于读多写少的场景，可以搭建读写分离架构，把读请求分流到从库，减轻主库压力
- 定期做表的碎片整理，通过
OPTIMIZE TABLE命令回收碎片空间，不过该操作会锁表，需要谨慎执行
大表优化是一个持续的过程，不是一次操作就能一劳永逸，需要定期监控表的性能变化，结合业务增长及时调整优化策略。