大表优化是系统性治理，核心为“减量”和“提速”。需综合QPS、慢查占比等识别真实瓶颈；通过归档、分区、冷热分离实现数据瘦身；优化索引与执行路径提升查询效率；结合读写分离、缓存、异步化进行架构收敛，并建立持续监控闭环。
大表优化不是单纯调优SQL语句，而是从数据生命周期、访问模式、存储结构到查询逻辑的系统性治理。核心在于“减量”和“提速”：减少无效数据、降低单次扫描成本、提升命中效率。
一、识别真正的大表与瓶颈点
不能只看行数或体积，要结合QPS、慢查占比、锁等待、IO压力综合判断。例如一张5亿行的订单表，若90%查询都带order_status = 'completed'且该字段有索引，实际压力可能远小于一张2000万行但全表扫描频繁的用户标签表。
建议做法：
- 用information_schema.tables查表大小、行数，但必须配合performance_schema或慢日志分析真实负载
- 重点关注全表扫描次数、临时表生成量、排序溢出磁盘（Using filesort）、JOIN未走索引等指标
- 对高频慢SQL做EXPLAIN FORMAT=JSON，看是否出现rows_examined远大于rows_sent
二、数据瘦身：归档、分区、冷热分离
很多大表问题本质是“不该存在的数据还在库里”。治理优先级：先减数据，再调SQL。
常见有效手段：
- 归档历史数据：按时间（如6个月前订单）导出并物理删除，保留归档库供偶尔审计；避免用DELETE大事务，改用pt-archiver分批操作
- Range/List分区：适合时间字段或状态字段稳定、查询常带分区键的场景（如PARTITION BY RANGE (TO_DAYS(create_time))），注意MySQL 8.0+才支持二级分区优化
- 冷热表拆分：将近3个月热数据放主表，历史数据移至archive_order_2023等独立表，应用层路由，降低主表膨胀速度
三、查询提速：索引优化与执行路径干预
大表上索引不是越多越好，关键是匹配高频查询的过滤+排序+JOIN条件组合。
关键原则：
- 联合索引遵循最左前缀 + 过滤优先 + 覆盖索引：比如查询WHERE shop_id = ? AND status = ? ORDER BY created_at DESC，建(shop_id, status, created_at)比(status, shop_id)更高效
- 警惕隐式类型转换和函数操作导致索引失效：如WHERE DATE(create_time) = '2024-01-01' → 改为create_time >= '2024-01-01' AND create_time
- 对无法避免的深度分页（如OFFSET 1000000），改用游标分页（WHERE id > last_seen_id LIMIT 100）或延迟关联（SELECT * FROM t1 JOIN (SELECT id FROM t1 WHERE ... LIMIT 1000000,100) AS tmp USING(id)）
四、架构层收敛：读写分离、缓存、异步化
当单库单表优化已达瓶颈，需跳出数据库本身思考。
实用策略：
- 将统计类、报表类、后台管理类查询路由到从库，主库专注高一致性事务
- 对结果稳定、更新不频繁的聚合查询（如“各城市昨日订单量”），用Redis缓存结果+定时刷新，避免每次查大表
- 把非实时强依赖的写操作（如日志记录、行为埋点、通知触发）抽离为消息队列异步处理，减轻主表写压力
- 必要时推动业务改造：用宽表预计算替代多表JOIN，用ES替代模糊搜索，用列存引擎（如Doris/StarRocks）承载分析型大表
不复杂但容易忽略：大表治理不是一次性项目，而需建立监控闭环——定期扫描新增慢SQL、跟踪归档进度、验证索引使用率、评估分区裁剪效果。持续迭代，才能让大表始终可控。
