Skip to content

06 · 数据库优化的分层答法:数据多了怎么办 ​

学完你能说出:

  • 「数据库数据多了怎么优化」的 6 层答法,按成本从低到高。
  • 每一层的具体做法,以及什么时候才需要往上走一层。
  • 为什么 5000 条数据不需要分区,分区表和分库分表有什么区别。

先看地图 ​

这是面试高频题,也是很多人只答出「分区」一个词就卡住的题。前五篇已经讲了单条 SQL 怎么查、怎么改,本篇把它们组织成一个有层次的回答:

⓪ 先定位 → ① SQL 和索引 → ② 写入方式 → ③ 表设计 → ④ 架构 → ⑤ 换工具

已经讲过的细节(索引失效、EXPLAIN、深分页)直接链接过去,这里只讲「放在答案的哪一层、怎么说」。

一句话本质 ​

先定位瓶颈,再按成本从低到高优化:能改 SQL 解决的,不改表;能改表解决的,不加机器。每一层都说一个具体做法。

生活类比 ​

家里东西越来越多,屋子乱了:

层收拾屋子数据库
⓪先看看到底是哪儿乱、找什么东西最费劲慢查询日志、EXPLAIN
①给常用东西贴标签、放固定位置建对索引、改写 SQL
②买菜一次买一周的,别一天跑十趟批量写入
③过季衣服收进箱子放到储藏室冷热分离、归档
④再租一个储藏室、请个帮手缓存、读写分离、分库分表
⑤专门的东西交给专门的地方(书捐给图书馆)分析查询交给 OLAP 工具

没人会因为抽屉乱就先去租仓库。

一步步看 ​

← 左右滑动看完整图 →⓪ 先定位慢查询日志 + EXPLAIN(不定位就优化 = 瞎猜)代价:几乎为零① SQL 和索引建对索引、避免失效、不写 SELECT *、深分页改写代价:改几行代码② 写入方式批量插入、一个事务提交多行代价:改调用方式③ 表设计字段类型选对、冷热分离、历史数据归档代价:要迁移数据④ 架构缓存、读写分离、分区表、分库分表代价:要加机器、改架构⑤ 换工具分析查询交给 Hive / Spark / ClickHouse代价:另建一套系统越往上:效果越大,成本也越大
数据库优化按成本从低到高往上爬:先定位(慢查询日志、EXPLAIN),再改 SQL 和索引,再改写入方式和表设计,最后才动架构(缓存、读写分离、分库分表)或换工具。每往上一层,效果越大,代价也越大;下面的层能解决,就不要往上爬。

⓪ 先定位:别猜 ​

  • 打开慢查询日志,找出最慢、最频繁的 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分割分区:一张表在同一台机器上拆成几块
ShardingShard 碎片 + ing分片:数据拆到多台机器
Read-Write SplittingRead 读 + Write 写 + Splitting 分开读写分离:写主库、读从库
Replica复制品从库:主库的副本
Archive档案归档:把旧数据挪到便宜的地方
OLAPOn-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 条不需要分区」的理由
  • 能对比分区表和分库分表
  • 能说出缓存和读写分离各自的代价

下一步该懂什么 ​

  • 第 ④ 层的组件怎么工作:缓存、读写分离、分库分表 → 分布式与中间件
  • 第 ⑤ 层:数据交给大数据平台之后怎么处理 → 大数据平台全景