深色模式
06 · 数据库优化的分层答法:数据多了怎么办
学完你能说出:
- 「数据库数据多了怎么优化」的 6 层答法,按成本从低到高。
- 每一层的具体做法,以及什么时候才需要往上走一层。
- 为什么 5000 条数据不需要分区,分区表和分库分表有什么区别。
先看地图
这是面试高频题,也是很多人只答出「分区」一个词就卡住的题。前五篇已经讲了单条 SQL 怎么查、怎么改,本篇把它们组织成一个有层次的回答:
⓪ 先定位 → ① SQL 和索引 → ② 写入方式 → ③ 表设计 → ④ 架构 → ⑤ 换工具
已经讲过的细节(索引失效、EXPLAIN、深分页)直接链接过去,这里只讲「放在答案的哪一层、怎么说」。
一句话本质
先定位瓶颈,再按成本从低到高优化:能改 SQL 解决的,不改表;能改表解决的,不加机器。每一层都说一个具体做法。
生活类比
家里东西越来越多,屋子乱了:
| 层 | 收拾屋子 | 数据库 |
|---|---|---|
| ⓪ | 先看看到底是哪儿乱、找什么东西最费劲 | 慢查询日志、EXPLAIN |
| ① | 给常用东西贴标签、放固定位置 | 建对索引、改写 SQL |
| ② | 买菜一次买一周的,别一天跑十趟 | 批量写入 |
| ③ | 过季衣服收进箱子放到储藏室 | 冷热分离、归档 |
| ④ | 再租一个储藏室、请个帮手 | 缓存、读写分离、分库分表 |
| ⑤ | 专门的东西交给专门的地方(书捐给图书馆) | 分析查询交给 OLAP 工具 |
没人会因为抽屉乱就先去租仓库。
一步步看
⓪ 先定位:别猜
- 打开慢查询日志,找出最慢、最频繁的 SQL;
- 用 EXPLAIN 看它是不是全表扫描、扫了多少行;
- 看监控:CPU、磁盘、连接数哪个先满。
详见 SQL 执行与优化 的排查顺序。
① SQL 和索引(成本最低,收益往往最大)
| 做法 | 例子 |
|---|---|
| 给 WHERE、JOIN、ORDER BY 用到的列建索引 | 订单按 user_id 查,就给 user_id 建索引 |
| 避免索引失效 | 不对索引列用函数、注意类型一致,见 索引与 B+ 树 |
用覆盖索引减少回表,不写 SELECT * | 只查 id, status 就建 (user_id, status) |
| 深分页改成按 id 翻页 | WHERE id > 上一页最后 id LIMIT 20 |
② 写入方式
| 做法 | 例子 |
|---|---|
| 批量插入 | 1 万条数据,一条 SQL 插 1000 行,分 10 次 |
| 一个事务提交多行 | 每条单独提交要刷 1 万次日志,合起来只刷 10 次 |
| 大批量更新拆小批 | 每批 500 行,减少锁的持有时间 |
③ 表设计
| 做法 | 例子 |
|---|---|
| 字段类型选对、选小 | 状态用 TINYINT 不用 VARCHAR;金额用 DECIMAL 或「分」存整数 |
| 冷热分离 | 3 个月内的订单在主表,更早的挪到历史表 |
| 归档 | 3 年前的日志导出到便宜的存储,主库删掉 |
| 适度反范式 | 订单表冗余商品名,查列表不用 join |
④ 架构(要加机器、改代码,代价大)
| 做法 | 解决什么 | 代价 |
|---|---|---|
| 缓存(Redis) | 读太多:热门数据放内存,挡掉大部分读请求 | 数据可能不一致,要处理缓存失效 |
| 读写分离 | 读太多:一主多从,写走主库、读走从库 | 主从有延迟,刚写完可能读不到 |
| 分区表 | 单表太大:一张表按时间拆成多个分区,查询只扫相关分区 | 还在一台机器上,只解决「扫描范围」 |
| 分库分表 | 单库扛不住:按 user_id 取模拆到多个库、多张表 | 跨库 join、分布式事务、全局 id 都变复杂 |
经验数字(大约):MySQL 单表在千万行以内、索引设计得当时,一般不用拆;到了几千万甚至上亿行、或者单机写入扛不住,才考虑分库分表。具体要看机器配置和查询模式。
⑤ 换工具
报表、统计这类「一次扫几百万行再汇总」的查询,不该在业务库上跑:
- 把数据同步到 Hive、Spark 做离线计算;
- 或者用 ClickHouse 这类列式数据库做实时分析。
业务库(OLTP)只管在线读写,分析交给 OLAP,见 一张总图。
为什么 5000 条数据不需要分区?
- 5000 行、每行 1 KB,一共约 5 MB,整张表都能放在内存里;
- 有索引时 B+ 树只有 2 层;就算全表扫描,在内存里也只要几毫秒;
- 分区是为了「只扫一部分数据」,而这张表本来就很小,分区只会增加管理成本,还可能让跨分区的查询更慢。
结论:建对索引就够了。 回答优化题时说出「数据量多大、所以该在哪一层解决」,比堆名词更显得懂行。
为什么这么设计
为什么要按成本从低到高优化?
- 问题:一上来就分库分表,代价极大(改代码、迁数据、跨库查询变复杂),而很多慢问题只是缺一个索引。
- 方案:先定位,再从最便宜的层开始,每层解决不了才往上走。
- 好处:用最小代价解决问题;回答也天然有层次,面试官能看出你分得清轻重。
一句话答法
优化要先定位瓶颈,再按成本从低到高:先改 SQL 和索引,再改写入和表设计,最后才动架构或换工具,能用低成本解决的就不上高成本方案。
为什么读写分离、缓存不能随便加?
- 问题:两者都引入了「数据有两份」:缓存和数据库、主库和从库,可能不一致。
- 方案:读多写少、能容忍短暂延迟的数据才用;写完马上要读的,强制读主库或更新后删缓存。
- 好处:在一致性和性能之间做有意识的取舍,而不是盲目加组件。
一句话答法
缓存和读写分离都会让数据有两份,可能短暂不一致,所以只用在读多写少、能容忍延迟的场景,对一致性要求高的读要走主库。
分区表和分库分表有什么区别?
- 问题:两者都叫「拆」,但解决的瓶颈不同。
- 方案:分区表是一张逻辑表在同一台机器上按规则拆成多个物理分区,解决「扫描范围太大」;分库分表是拆到多台机器,解决「单机的存储、写入、连接数扛不住」。
- 好处:分清楚瓶颈在「查得太多」还是「一台机器不够」,才能选对。
一句话答法
分区表还在一台机器上,只是让查询少扫数据;分库分表是拆到多台机器,解决单机扛不住的问题,但会带来跨库查询和分布式事务的麻烦。
英文术语拆解
| 术语 | 拆开看 | 一句话意思 |
|---|---|---|
| Partition | 分割 | 分区:一张表在同一台机器上拆成几块 |
| Sharding | Shard 碎片 + ing | 分片:数据拆到多台机器 |
| Read-Write Splitting | Read 读 + Write 写 + Splitting 分开 | 读写分离:写主库、读从库 |
| Replica | 复制品 | 从库:主库的副本 |
| Archive | 档案 | 归档:把旧数据挪到便宜的地方 |
| OLAP | On-Line Analytical Processing | 联机分析处理:报表、统计类查询 |
面试怎么问
Q:数据库数据量越来越大,查询变慢了,你怎么优化?
先定位:看慢查询日志,用 EXPLAIN 确认是不是全表扫描、扫了多少行。然后按成本从低到高:第一,SQL 和索引,给 WHERE、JOIN、ORDER BY 的列建索引,避免索引失效,不写 SELECT *,深分页改成按 id 翻页;第二,写入方式,批量插入、一个事务提交多行;第三,表设计,字段类型选对,冷热数据分离、历史数据归档;第四,架构,读多就加 Redis 缓存、做读写分离,单表到几千万行以上再考虑分区或分库分表;最后,报表类分析查询交给 Hive、Spark 或 ClickHouse,不压在业务库上。
Q:一张表只有 5000 条数据,需要分区吗?
不需要。5000 行大约几 MB,整张表都能放进内存,B+ 树也只有两层,建对索引查询就是毫秒级。分区是为了让大表查询只扫部分数据,小表分区只会增加管理成本。
Q:分库分表会带来什么问题?
跨库 join 做不了,要在应用层拼或者做冗余;跨库事务要用分布式事务或者最终一致性方案;自增 id 不再全局唯一,要用雪花算法这类全局 id;按非分片键查询要扫所有分片;扩容时还要迁移数据。所以不到单机扛不住,不轻易分库分表。
容易混的地方
- 分区 ≠ 分库分表:一个在一台机器内拆,一个拆到多台机器。
- 缓存不是优化的第一步:先把 SQL 和索引做好;缓存掩盖的慢 SQL,在缓存失效时会一起爆发。
- 「单表 2000 万行」不是硬规则:只是一个常被引用的经验值,真实上限取决于行大小、索引、机器内存和查询方式。
- 优化不只是查询:写入慢(单条插入、大事务)也是常见瓶颈。
和其他知识的连接
- ← 来自 SQL 执行与优化:⓪ 和 ① 层的细节。
- ← 来自 听题与答题:「优化类」题用流程走查法,这就是示范。
- → 影响 分布式与中间件:缓存、读写分离、分库分表在那里展开讲。
- → 影响 大数据平台全景:⑤ 层交给 OLAP,就是大数据平台要做的事。
自测 3 问
Q1:说出数据库优化的 6 层,每层一个具体做法。
点开看答案
⓪ 定位:慢查询日志 + EXPLAIN;① SQL 和索引:给 WHERE 列建索引、避免失效、深分页改写;② 写入:批量插入、一个事务提交多行;③ 表设计:冷热分离、归档、字段类型选对;④ 架构:Redis 缓存、读写分离、分区或分库分表;⑤ 换工具:分析查询交给 Hive / Spark / ClickHouse。
Q2:为什么 5000 条数据不需要分区?
点开看答案
数据只有几 MB,整表能放进内存,B+ 树两层,建对索引就是毫秒级。分区是为了让大表少扫数据,小表分区只增加管理成本。
Q3:分区表和分库分表分别解决什么问题?
点开看答案
分区表:同一台机器上把大表按规则拆开,查询只扫相关分区,解决扫描范围太大;分库分表:拆到多台机器,解决单机存储、写入、连接数扛不住,但带来跨库 join、分布式事务、全局 id 等问题。
✅ 验收清单
- 能不看资料按顺序说出 6 层,每层至少一个具体做法(2 分钟内)
- 能说出「5000 条不需要分区」的理由
- 能对比分区表和分库分表
- 能说出缓存和读写分离各自的代价
下一步该懂什么
- 第 ④ 层的组件怎么工作:缓存、读写分离、分库分表 → 分布式与中间件
- 第 ⑤ 层:数据交给大数据平台之后怎么处理 → 大数据平台全景