数据库性能优化实战：从慢查询到架构卸载的八种手段

最近帮朋友处理一个后台系统的数据库性能问题，数据量爬到几千万条之后，接口响应从 100 毫秒一路涨到两秒多，后台管理页面都开始转圈。他们团队之前也做过“优化”，无非是从网上找几个技巧套上去，加了个索引发现没效果，又改了点缓存配置还是卡，折腾一星期,问题原封不动。

数据库优化这件事，从来不是靠一两个“绝招”解决的。真正有效的方式，是有一套完整的分析、定位、优化、验证流程。这些年我自己做过不少系统性能治理，踩过的坑不少，也沉淀出八种最实用的优化手段——从“先找到慢在哪”到“从架构上卸载压力”，一次说清楚。

1. 动手优化之前，先把慢查询日志用好

很多人拿到数据库变慢的问题，第一反应是“加个索引”，或者“看看是不是内存不够”。但如果没有定位到具体是哪条 SQL、哪个环节慢，这些操作基本属于瞎猜。我处理性能问题的习惯只有一个：先看日志，数据不会说谎。

1.1 慢查询日志怎么开启、怎么看

慢查询日志是数据库自带的“行车记录仪”，它会记录所有执行时间超过指定阈值的 SQL。很多生产库默认没开这个开关，因为记录日志本身有一点额外开销，但说实话，这点代价换来的是排查问题时的巨大便利，完全值得。

在 MySQL 里，可以这样开启：

-- 开启慢查询日志 SET GLOBAL slow_query_log = ON; -- 设置阈值，执行超过 1 秒的 SQL 会被记录 SET GLOBAL long_query_time = 1; -- 确认日志文件位置 SHOW VARIABLES LIKE 'slow_query_log_file';

long_query_time设成多少需要拿捏一下。设成 0.1 秒会记录大量 SQL，日志文件膨胀得很快，反而不好筛重点；设成 5 秒又会漏掉很多潜伏问题。我一般先用 1 秒观察一到两天，等日志里积累了足够样本再分析，这个阈值比较能反映出“有明显性能问题”的查询。

拿到日志文件之后，直接打开看往往很费劲，日志多的时候几万条根本看不过来。MySQL 自带的mysqldumpslow工具可以帮你按耗时排序，只看最耗时的前几条：

mysqldumpslow -s at -t 10 /var/lib/mysql/mysql-slow.log

-s at表示按平均耗时排序，-t 10表示只显示前 10 条。输出里会直接把 SQL 的文本、执行次数、平均耗时列出来，谁是“头号嫌疑犯”一目了然。

1.2 用执行计划定位“病根”

慢日志只是告诉你“哪条 SQL 慢”，但它没说“为什么慢”。要搞清楚原因，就得用EXPLAIN看执行计划。这也是我每次分析慢查询都会做的事——不看执行计划就改 SQL，大概率是白忙。

EXPLAIN SELECT * FROM orders WHERE user_id = 1024 ORDER BY created_at DESC LIMIT 20;

执行结果里最需要关注的几列：

• type：访问类型。看到ALL（全表扫描）基本可以断定问题出在这里；range或ref属于走了索引，正常。
• key：实际用到的索引。如果是NULL，说明这条 SQL 没有命中任何索引。
• rows：预估扫描的行数。这个数字越大，执行越慢，优化空间也越大。
• Extra：这里面的信息特别关键。看到Using filesort表示排序没走索引，Using temporary表示用了临时表，这俩都是性能杀手；看到Using index反而是好事，说明查询完全被索引覆盖，不需要回表。

我记得有一次排查某接口的慢查询，日志里每天固定出现近千条相同 SQL，都是查某个大报表。拿EXPLAIN一看，type是ALL，rows将近 400 万。这就很明确了，这张表从设计之初就没建过组合索引，数据量涨上来之后必慢无疑。后来补了一个联合索引，接口从 1.8 秒降到 120 毫秒，效果立竿见影。

提醒一下：慢查询日志的开关和阈值本身也是配置，如果你用的是云数据库，很多管理控制台能直接看慢查询趋势，不用自己手动开日志。自己维护的实例，记得确认一下磁盘空间够用，日志文件被撑爆的事也发生过。

2. 索引优化：见效最快，也最容易踩坑

如果说慢查询定位是“找出病根”，那索引调整就是“对症下药”。这是数据库优化里性价比最高的手段，没有之一。但也正因为人人都知道索引好，很多人乱建索引，结果建了一堆没用的索引，查询没变快，写入反而变慢了。

2.1 索引为什么能让查询变快

要理解索引，可以先把它想象成一本书的目录。没有目录的时候，你要找一个词，只能从第一页翻到最后一页，这就是全表扫描。有了目录，你直接翻到对应的页码就行，效率完全不同。

数据库里最常用的索引结构是 B+ 树。它把数据按顺序组织成多层的树形结构，查询时从根节点往下走，每次都能排除掉一大半数据，最终定位到目标记录只需要少数几次磁盘 IO。相比全表扫描要读几千上万个数据页，B+ 树索引的速度自然快几个数量级。

但这里有一个很重要的点：索引不是建得越多越好。每建一个索引，数据库在写入数据（INSERT、UPDATE、DELETE）时都要额外维护这棵 B+ 树，相当于每本书多了一本目录，每次增删内容都要同步更新所有目录。所以索引的建立原则是“精准投放”，而不是“多多益善”。

2.2 建索引的三个核心原则

我建索引的时候，基本会按下面三个原则来评估：

第一，高频查询字段优先。如果一个字段经常出现在WHERE、JOIN、ORDER BY里，那它就是索引的候选者。特别是ORDER BY字段，有索引的情况下排序可以直接走索引完成，避免Using filesort。

第二，用组合索引覆盖多个条件。很多时候单字段索引不够用，更好的做法是把多个查询条件拼成一个组合索引。比如订单表上经常按user_id查、还要按created_at排序，就可以建(user_id, created_at)这个联合索引，一次索引同时解决过滤和排序问题。组合索引有一个“最左前缀原则”，也就是查询条件必须从联合索引的最左边字段开始匹配才有效。比如建了(user_id, status)索引，那WHERE status = 1是走不了这个索引的，但WHERE user_id = 1024 AND status = 1可以。

第三，用区分度高的字段做前缀。区分度指字段里不同值的比例，越高越好。比如“性别”字段只有男、女两种值，区分度就很低，建索引几乎没意义；而“订单号”这种字段几乎一条记录一个值，区分度极高，建索引效果就很好。

2.3 索引失效的几种典型写法

索引建对了，不代表查询就一定能用上。我见过太多人建了索引发现没生效，跑来问我怎么回事。看了一圈，基本都是下面这几种写法把索引给“废掉”了：

注意：索引失效问题不一定只是 SQL 写法的锅，参数类型也很常见。比如前端传过来的是字符串，后端没做转换直接拼进 SQL，恰好字段又是整型，索引就悄悄失效了。排查的时候多看一眼执行计划，type列从ref变成ALL就说明索引没走成。

3. SQL 改写：一条烂 SQL 能拖垮整个库

有些 SQL 即便建了索引，执行效率依然很低，问题出在写法本身。一条写得糟糕的 SQL，和一个写法合理的 SQL，数据量一大性能差距能到几十倍。这一节聊几个我在实际项目里反复遇到的 SQL 问题。

3.1 深分页查询别用 LIMIT 大偏移

后台管理系统最常见的慢查询场景之一，就是带分页的列表页。比如：

SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

这条 SQL 的逻辑是“跳过前 10 万条，从第 10 万零一条开始取 20 条”。但数据库在执行的时候，可没法“跳”过这 10 万条，它需要把前面 10 万条都读出来，然后丢掉，再取后面的 20 条。偏移量越大，查询越慢，到后面几千万数据时，翻到第几十页基本就卡死了。

优化的思路是“先快速定位到要取的位置，再拿数据”。比较实用的两种写法：

延迟关联（先取主键再回表）：

SELECT o.* FROM orders o INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) t ON o.id = t.id;

子查询里只查主键，不碰业务字段，这样前面的深度偏移只发生在一个轻量查询上，速度会快很多。

游标式分页（记住上一页的最后位置）：

SELECT * FROM orders WHERE id > 100030 ORDER BY id LIMIT 20;

这种写法适合用户按顺序翻页的场景，把“上一页最后一条记录的 id”传进来，通过主键范围直接定位，无论翻多少页性能都是稳定的。很多移动端的下拉加载列表，就是用这种方案。

3.2 少用 SELECT *，避免不必要的回表

SELECT *是很多新手写 SQL 的习惯，但在数据量大时这是一个不小的性能隐患。原因很直接：第一条，*会把所有字段都查出来，如果表里有几个大的TEXT或VARCHAR(1000)字段，磁盘 IO 和内存开销都会被放大；第二条，如果查询条件命中的是辅助索引，而SELECT *要拿全部字段，数据库只能一个个回表，去聚簇索引里捞完整的数据行。

只查你真正需要的字段，也给了“覆盖索引”发挥作用的机会。所谓覆盖索引，就是索引本身就包含了你要查询的所有字段，数据库不需要再去数据表里取一遍。比如：

SELECT user_id, status FROM orders WHERE user_id = 1024;

如果建立了(user_id, status)的组合索引，这条查询直接走索引就能拿到全部结果，Extra列会显示Using index，这种查询速度快得离谱。

3.3 大事务拆小，别让锁堆成山

数据库的事务并不是越大越好。一个事务里一次性更新几万行数据，不仅会把内存里的 undo 日志涨得老大，还会持有大量行锁，其他想改这些行的请求只能干等。更麻烦的是，事务越久，锁被持有的时间就越长，死锁的概率也越高。

我之前接过一个批量导入功能，导 10 万条数据时卡了十几秒，原因就是代码里把所有更新包在了一个事务里。后来改成每 500 条一个事务提交一次，整个导入只用了不到 3 秒，应用日志里也不再有等了半天的操作记录。

# 伪代码示意 batch_size = 500 for i in range(0, len(data), batch_size): session.execute("BEGIN") for row in data[i:i + batch_size]: session.execute(update_sql, row) session.execute("COMMIT")

事务拆小有两个直接好处：一是锁的持有时间缩短，并发冲突减少；二是万一某一段失败，回滚的代价也小，不需要把整批数据重新来一遍。这属于写操作优化里比较重要的一个意识。

4. 表结构设计：从根上优化数据模型

SQL 和索引层面的优化，治的是“表已经建成以后”的病。如果表结构本身设计得有问题，那后面怎么调都事倍功半。表结构优化属于“治未病”，但它也能在已有系统上做局部改良。

4.1 字段类型够用就好

很多业务表在设计时，建表的人贪图省事，所有数字字段都上BIGINT，所有字符串字段都用VARCHAR(255)，存 IP 地址的用VARCHAR(15)，存状态码的也用VARCHAR(20)。这种设计看着都差不多，但数据量大了之后差别非常大。

数据库是按页存取数据的，一个数据页默认 16KB。字段越短，一个页能装下的记录数就越多，扫描同样的数据量需要读的页就越少，IO 开销就越低。如果一个只需要存 0~9 的状态字段用了BIGINT（8 字节）而不是TINYINT（1 字节），那一张 1000 万行的表凭空多占了几十 MB，查询时读的页也更多。

几个常见的字段优化经验：

• 状态、类型、标志位：用TINYINT或SMALLINT，别用INT或VARCHAR。
• 时间字段：DATETIME够用就别上TIMESTAMP之外的花活，两者存储和范围有差异，按业务场景选。
• 小数金额：用DECIMAL，别用FLOAT/DOUBLE，避免精度问题。
• 字符串长度：不要太长，VARCHAR(50)能装下的别写成VARCHAR(255)，更短的字段索引也更快。

4.2 大字段和宽表的处理

有些表为了省事，把用户的基本信息、订单记录、一大段备注文本全塞在一张表里。查询的时候哪怕你只需要其中两个字段，数据库也得把包含大文本的数据页读出来，这也是性能慢的来源。

我自己处理过一个案例：一张日志表里有个TEXT字段存接口返回的完整报文，一行的数据量将近几千字节，结果平时统计用的查询全都变得很慢。后来把那段报文拆到单独的日志详情表，主表只保留时间、级别、接口名和关联 ID，统计查询立刻变快。查询详情时再通过关联 ID 去附属表拿，又不丢数据。

拆表的原则是：把高频查询的字段留在一张瘦表里，把低频但体积大的字段放到附属表。这比开发阶段做好表设计省事，但对已经上线的系统，这种“瘦身手术”做起来要小心，尽量选在业务低峰期迁移，并且测试好关联查询的正确性。

4.3 冷热数据分离与归档

任何业务系统，数据都有一个“热冷之分”。三个月以内的订单被频繁查询，两年前的订单一年也翻不到一次。如果把所有历史数据堆在一起，查询会被大量冷数据拖慢，索引也会变得很庞大。

常用的方案是做数据归档：把超过一定时限的历史数据从主表迁出，放到历史库或者按月分区的归档表里。线上主表只保留近期热数据，查询性能会明显提升。如果数据量实在太大，还可以考虑按时间做分区表，让数据库自动把不同时间段的数据放到不同物理分区里，查询只扫对应分区。

这里要特别说一句：我遇到的很多业务系统还没到真正需要分库分表的地步，但数据一多就有人急着上分库分表，结果引入一堆分布式事务、跨库查询的复杂度。实际上，先做归档、做冷热分离，往往就能解决 80% 的问题。分库分表是最后的手段，不是默认选项。

5. 缓存层挡在数据库前面

数据库的磁盘 IO 是有物理上限的，再好的索引、再优的 SQL，也架不住同一份数据被成千上万个请求反复读。这时候就该在数据库前面加一层缓存，把高频读请求挡在数据库之外。

5.1 什么数据适合进缓存

缓存不是一个“放上去就能变快”的工具，它只适合特定类型的数据。我判断一条数据适不适合加缓存，主要看两点：读多写少，以及重复读很高。

典型的适合进缓存的数据有：

• 商品详情、热门文章这类被大量用户重复读的内容；
• 用户会话信息、权限配置、系统参数这类几乎不变的配置数据；
• 首页推荐、排行榜这类计算结果可以短时间复用的数据。

不适合进缓存的则是那些更新特别频繁、每次读到的数据都要求绝对最新的场景，比如实时库存、交易流水详情等。硬要缓存的话，要么不停清缓存导致命中率极低，要么读出脏数据引发资损类问题。

5.2 缓存更新与经典缓存问题

缓存最容易出错的地方在“更新策略”。很多人习惯“先更新缓存，再更新数据库”，这个顺序是有坑的。如果应用先改了缓存但数据库更新失败，缓存里就是一份不存在于数据库的脏数据，后面的请求全都读到错的东西。

我自己用的策略一直是 Cache Aside（旁路缓存）：读的时候先查缓存，没命中再去查数据库并回填；写的时候先更新数据库，然后主动让缓存失效（删除缓存，而不是更新缓存）。这样下次读请求没命中缓存，会从数据库拉到最新数据回填，不会出现明显的缓存和数据库不一致窗口。

缓存层还有三个经典问题，简单说一下应对思路：

• 缓存穿透：请求查一个缓存和数据库里都不存在的数据，每次都打到数据库。可以在查询结果为空时也缓存一个空值，或者用布隆过滤器做前置拦截。
• 缓存击穿：某个热点 key 突然失效，大量并发请求同时打到数据库。通常对热点 key 的重建加锁，只允许一个请求去数据库回填，其他请求等待。
• 缓存雪崩：大量 key 在同一时间过期，数据库被瞬间打垮。给过期时间加一个随机值，让过期时间分散开。

这里也给个劝告：缓存一致性永远做不到“绝对完美”，只能做到“业务可接受”。如果你的业务不能接受几毫秒或几秒内的缓存延迟，那这个数据就不该进缓存，强用缓存反而天天出事故。

6. 读写分离：把读压力从主库卸下来

数据库优化的路径，前面几招把 SQL 和结构问题解决了之后，如果读压力还是很大，那就得考虑从架构层面做文章了。读写分离是其中性价比很高的一招，但前提是你确实到了那个量级。

6.1 什么时候做读写分离

一个典型的信号：你的监控上读 QPS 是写 QPS 的十几倍甚至几十倍，主库的 CPU 长期居高不下，且慢查询已经优化得差不多了，还是扛不住。这时候可以考虑做读写分离。

读写分离的思路很简单：一台主库负责写，多台从库负责读。主库把 binlog 同步给从库，从库回放日志保持和主库的数据一致。业务上把读请求路由到从库，只有写请求才打到主库。

实现上有两种常见思路。一是用数据库中间件，只需要在配置里声明读写规则，对业务代码透明；二是在应用内部自己封装数据源，比如配置一个主库数据源和多个从库数据源，通过注解或路由规则决定某条 SQL 走哪个库。

6.2 主从延迟怎么处理

读写分离最大的对手是“主从延迟”。从库同步主库的 binlog 是有时间差的，主库刚写入一条数据，立刻去从库读，可能读不到。

之前做过一个社区系统的读写分离，用户发完评论马上刷新页面，结果评论没显示出来，用户就开始疯狂点“发布”按钮，制造出一堆重复数据。问题就是写走后主库，读却走了从库，而延迟还没追上。

解决主从延迟的常用思路：

• 关键读走主库：用户自己刚写入的数据，强一致要求高的查询，直接走主库读。
• 半同步复制：主库等待至少一个从库确认收到 binlog 才提交事务，缩小延迟窗口。
• 监控延迟阈值：如果从库延迟超过设定值（比如 5 秒），就把流量暂时切回主库，避免读到太旧的数据。

读写分离要注意的是，它解决的是“读吞吐量”的问题，不是慢 SQL 的问题。SQL 本身写得稀烂，从库照样会被拖垮。所以顺序很重要：先把慢查询和索引整治完，再上读写分离。

7. 数据库参数与连接池调优

有时候 SQL 和索引都没问题，但数据库就是感觉不得劲，这时候问题可能出在实例配置和连接管理上。数据库默认参数是“保守模式”，适合通用环境，不一定适合你的负载特点。

7.1 连接数、超时与缓冲池

我见过不少生产事故，原因简单粗暴：应用实例扩容，连接数涨上去，数据库最大连接数设置得太小，应用开始疯狂报“获取连接超时”。但也有另一种情况，连接数设得太高，数据库光处理连接和线程切换就疲于奔命，吞吐量反而下降。

关键参数里，max_connections不要拍脑袋设。先算个账：应用实例数 × 每个实例的连接池大小，得出的就是正常情况下数据库需要承受的连接数，留 30%~50% 的余量。

MySQL 的innodb_buffer_pool_size也很重要，它是 InnoDB 在内存里缓存数据和索引的缓冲池，命中率越高，磁盘 IO 越少。建议值一般是物理内存的 60%~70%，但如果机器上还跑着其他服务，得留出余量，别把内存占满。

连接池参数也要合理调整，以常见的 HikariCP 为例：

maximum-pool-size: 20 minimum-idle: 5 connection-timeout: 30000 idle-timeout: 600000 max-lifetime: 1800000

maximum-pool-size不是越大越好，设太大反而容易把数据库连接占满，而且每个连接都要占用内存和线程资源。我在实践中一般先把池大小设为 10 到 20，观察监控里的“等待获取连接”指标，如果经常出现等待，再往上调。

7.2 定时维护任务不能少

数据库用久了，会出现一些“慢性病”：统计信息过时导致优化器选错索引、数据页碎片变多导致扫描变慢、日志表膨胀占用大量空间。这些都需要定期的维护任务来处理。

比较常用的两个维护操作：

-- 更新统计信息，帮助优化器做出正确选择 ANALYZE TABLE orders; -- 重建表并整理碎片，适用于频繁删除、更新的大表 OPTIMIZE TABLE orders;

ANALYZE TABLE一般没什么风险，可以在业务高峰期之外的任意时间执行。OPTIMIZE TABLE会锁表，一定要选在凌晨低峰期执行，而且要留意执行时长，提前评估好磁盘空间能够容纳重建过程中的临时文件。

另外，日志类、临时类的数据表要建立数据清理机制。很多公司的数据库之所以越跑越慢，不是因为核心业务表出了问题，而是因为一堆没人管的中间表、日志表在几十 GB 地疯长，把磁盘空间吃光，所有查询都跟着变慢。

8. 建立监控巡检机制，把问题消灭在爆发前

数据库性能的治理，最高级的境界不是等出了问题再去救火，而是让问题在爆发之前就暴露在监控屏上。前面几种方法讲的都是“怎么修”，这最后一种讲的是“怎么防”。

8.1 需要盯住的核心指标

我从实践里筛选出一批最值得盯的指标，不会太多，盯住了就足够发现问题苗头：

这些指标用开源的监控组件就能实现采集和告警。监控的价值不在于“挂了才报警”，而在于指标逐步恶化的时候就能提前介入。比如慢查询数量连续一周每天涨 10%，你就能提前查到是哪条 SQL 出了问题，而不是等它某天把数据库拖垮。

8.2 备份与容量规划

备份不属于性能优化，但它应该出现在任何数据库治理体系里。没有可用的备份，就谈不上下线、重构、迁移，很多优化动作也就不敢做。我见过有人把备份文件放在同一台服务器的同一块磁盘上，磁盘坏道那一瞬间备份和数据一起没了，这才是真正的灾难。

备份策略尽量满足“可验证恢复”：定期做恢复演练，确认备份文件真的能恢复出可用的数据，而不是备份成功就以为万事大吉。

容量规划也要提前做。数据库的数据量增长不是线性的，很多时候是业务一好，数据翻倍增长。每个月看一眼最大表的增长趋势，就能估算出还有几个月需要扩容磁盘、是否需要做归档，免得 binlog 或数据文件把磁盘填满，造成整个实例不可用。

我自己在优化数据库时，习惯按这样的顺序来排查：先开慢查询日志定位问题 SQL，再用执行计划分析为什么慢；能通过改 SQL、调索解决的，坚决不动架构；SQL 和索引都优化完还是扛不住，才考虑表结构调整、上缓存、上读写分离。做过这么多次性能治理，最终发现七八成的问题，靠前几种方法就能解决，架构级的方案往往是在前面的路全走完之后才需要的。

如果接到一个数据库变慢的问题，别急着动手改配置，先去把慢日志打开，把执行计划拉出来看一眼。等你看到那条“万恶之源”SQL 的时候，问题其实已经解决了一半。