写在前面这是一篇写给“被数据库慢查询折磨过”的人的文章。不管你是后端开发、DBA还是自己一个人扛全栈的独立开发者只要你跟数据库打过交道一定遇到过类似的场景接口突然从 50ms 变成 5s页面转圈转到用户骂娘数据量一上来原来跑得好好的 SQL 突然就像老牛拉破车。这时候你打开搜索引擎输入“数据库 性能调优”出来的文章要么是面试八股要么是云厂商的软文真正能拿来直接用的实战手册少之又少。我这些年维护过几套线上系统MySQL、PostgreSQL、Oracle 都踩过坑从单表几万行到几亿行的场景都扛过。这篇文章我想把数据库性能调优这件事从头到尾捋一遍不是背书式的罗列知识点而是按照我实际处理线上问题的流程来写问题怎么定位、瓶颈怎么判断、索引怎么设计、SQL 怎么改写、参数怎么调每一步都给出可落地的操作和判断依据。这篇内容的适用对象是已经会写 SQL、能跑通 CRUD但一遇到性能问题就心里没底的开发者。看完之后你至少应该能独自搞定 90% 的常见数据库慢查询问题。1. 性能调优的整体思路先定位再动手1.1 为什么你的数据库越来越慢很多人的调优姿势是错的。一上来就改配置、加索引、升级硬件搞了半天问题没解决反而引入了新故障。其实数据库变慢这件事背后往往是资源瓶颈、SQL 质量、锁竞争、数据分布变化这四类问题交织在一起你得先搞清楚到底是哪一种。资源瓶颈最常见CPU 打满、内存不足、磁盘 IO 排队这些通过系统监控就能看到。SQL 质量问题是大多数慢查询的根源索引没建对、查询条件写得不满足最左前缀、不必要的全表扫描这些属于“明明可以避免却一直在发生”的浪费。锁竞争则是另一个大坑尤其是在高并发写入的场景下行锁、间隙锁、表锁之间互相等待事务迟迟不提交直接把整个库拖死。数据分布变化则比较隐蔽——一张表刚上线时数据量小什么查询都秒回等数据量涨到千万级、亿级原来的执行计划可能就会失效。我遇到过最典型的一个案例某个订单查询接口上线三个月都在 100ms 以内第四个月突然飙到 3s。排查下来不是 SQL 变了也不是并发上来了就是订单表的数据量跨过了一个量级之前优化器选择的索引在新数据分布下选择性变差走了全表扫描。这种问题你要是没有“先定位、再动手”的习惯折腾一整天也找不到方向。1.2 调优的第一步建立性能基线没有基线就没有调优。你不知道“正常状态”长什么样就没法判断“异常状态”到底是哪里出了问题。所以正式调优之前第一件事是采集一组性能基线数据。基线数据至少要包含这几个维度数据库的 QPS/TPS、CPU 使用率、内存使用率、磁盘 IO 的读写延迟和吞吐量、活跃会话数、缓存命中率以及核心业务的接口响应时间。采集工具方面MySQL 可以用 Performance Schema 加 sys 库PostgreSQL 可以用 pg_stat_statements通用的办法是定期抓取 SHOW GLOBAL STATUS 和 SHOW ENGINE INNODB STATUS 的快照存到一张历史表里。有了基线之后你还需要建立一张“慢查询台账”。把慢查询日志打开记录下所有超过阈值建议线上先设为 1s后续根据业务调整的 SQL归档到一张专门的表里记录出现时间、SQL 文本、执行计划、扫描行数、返回行数。这张表就是一个长期的参考坐标系——每次优化完你都能拿旧数据和新数据做对比量化出优化效果。注意基线数据至少要采集一周以上覆盖业务的高峰期和低谷期。只取某一天的数据容易失真比如周一和周末的流量模式完全不同。1.3 定位慢 SQL 的常用工具链工欲善其事必先利其器。我常用的定位工具按层级分有三类第一类是数据库自带的诊断工具。MySQL 的慢查询日志是基础但光有文本不够我习惯配合 mysqldumpslow 来做聚合分析它能把相同的 SQL 模板归并直接告诉你哪些 SQL 出现频率最高、累计耗时最长。PostgreSQL 自带 pg_stat_statements 扩展可以按 queryid 聚合统计 SQL 的调用次数、总耗时、平均耗时和缓存命中情况。第二类是操作系统层面的工具。vmstat 看 CPU 上下文切换和 IO 等待iostat 看磁盘的利用率与 await 时间sar 采集历史负载这些是判断资源瓶颈的直接依据。很多时候你以为数据库慢是 SQL 的问题结果 iostat 一查磁盘 util 已经 100%是底层硬件撑不住了。第三类是第三方监控系统。Prometheus 加 Grafana 是目前最主流的开源组合配合 mysqld_exporter 或者 postgres_exporter能把慢查询数量、活跃连接数、缓冲池命中率等核心指标可视化出来。我的习惯是建好几个固定看板数据库概览、慢查询趋势、锁等待分析、InnoDB 引擎状态。这样每次出现故障先在 Grafana 上看曲线基本能锁定大致方向再往下钻取具体 SQL。2. 索引优化最容易见效也最容易翻车的地方2.1 先搞清楚索引为什么能加速索引能加速的本质是减少了数据扫描的量。全表扫描相当于在一本没有目录的厚书里逐页找内容而索引相当于书的目录能直接告诉你想要的内容在第几页。具体实现上主流数据库用的都是 B 树索引。B 树的叶子节点有序存放着索引列的值以及指向实际数据行的指针主键值数据库根据查询条件在 B 树里做二分查找几层就能定位到目标数据。为什么不用二叉树因为 B 树是多路搜索树每个节点能放很多个键值树的高度很矮三层左右就能覆盖百万级数据磁盘 IO 的次数极少。索引失效的场景很多人背过但没理解背后的原理。最常见的有对索引列使用函数WHERE UPPER(name) ABC、隐式类型转换字符串列用数字查询、模糊匹配的前导通配符LIKE %abc、联合索引不满足最左前缀原则。这些场景失效的原因各不相同但归根结底是一件事索引列被“加工”过之后原来的有序结构不再匹配查询条件。2.2 联合索引设计:顺序决定成败联合索引是索引优化里最深的水。我见过太多人把查询条件涉及的字段一股脑全塞进索引结果效果很差。联合索引的字段顺序是有讲究的核心原则是区分度高的放前面经常作为等值查询的放前面范围查询的放后面。举个例子假设有一个订单表常见查询是 WHERE status PAID AND created_at BETWEEN 2024-01-01 AND 2024-01-31。status 的区分度很低可能只有 5 种值created_at 的区分度很高。这时候联合索引的正确建法是 (status, created_at) 还是 (created_at, status)答案是 (status, created_at)。原因很微妙虽然 status 区分度低但它是等值查询先按 status 过滤掉 80% 的数据再在剩下的数据里做 created_at 的范围扫描效率很高。如果把 created_at 放前面则 status 无法参与索引过滤数据库只能在所有满足时间范围的数据里一一核对 status扫描量会大得多。还有一种是“索引覆盖”的用法。如果一个查询的所有字段都在索引里数据库就完全不需要回表查数据行直接扫描索引就能返回结果速度会快一个量级。比如 WHERE user_id ? 只需要返回 order_no那 (user_id, order_no) 这个联合索引就能覆盖这个查询。设计联合索引时把常用的查询列和返回列都考虑进去能显著提升性能。2.3 索引冗余与维护:加索引不是越多越好索引不是免费的。每建一个索引写入时就要多维护一棵 B 树插入、更新、删除的性能都会受到影响而且索引占用的磁盘空间也是成本。很多表上有大量冗余索引比如 (a, b) 和 (a) 同时存在后者完全就是浪费因为前者已经能覆盖所有以后者开头的查询。排查冗余索引MySQL 可以从 information_schema.statistics 里导出所有索引信息手动分析。也可以用 pt-duplicate-key-checker 这个 Percona Toolkit 工具自动检测它会列出重复和冗余索引并给出删除建议。索引的维护和表的数据增长是同步的。长期反复增删改之后索引的叶子节点会变得稀疏产生碎片查询性能会下降。解决办法是重建索引或优化表MySQL 对应的是 OPTIMIZE TABLE 或 ALTER TABLE ... ENGINEInnoDBPostgreSQL 对应的是 REINDEX。实操心得线上执行索引变更务必选择业务低峰期。MySQL 8.0 之前ALTER TABLE 加索引会锁表虽然 InnoDB 支持 Online DDL但大批量数据处理阶段依然会产生锁等待。建议先用 pt-online-schema-change 这类工具在不停机的情况下完成大表的索引变更。3. SQL 改写与执行计划看懂数据库的想法3.1 执行计划到底怎么读定位到慢 SQL 只是第一步真正要解决的问题是“为什么慢”。执行计划就是回答这个问题的关键。它能把数据库优化器选择的执行路径展示出来告诉你是全表扫描、走索引了扫描了多少行、有没有排序、有没有临时表。以 MySQL 的 EXPLAIN 为例我会按这个顺序看先是 type 列它反映访问类型从好到差依次是 system const eq_ref ref range index ALL。看到 ALL 就要警觉这代表全表扫描通常是性能问题的根源。ALL 和 index 的区别是index 也是全量扫描但它扫的是索引树而不是数据行比 ALL 好一点但仍然要尽量避免。然后看 rows 列这是优化器预估的需要扫描的行数值越大执行时间越长。注意它是预估不是实际值有时候会跟实际偏差很大这时需要结合 EXPLAIN ANALYZEMySQL 8.0.18 支持看实际执行代价。Extra 列的信息也很关键。出现 Using filesort 代表发生了文件排序即结果集需要额外排序才能满足 ORDER BY出现 Using temporary 代表使用了临时表多出现于 GROUP BY 和 DISTINCT出现 Using index 是好事说明查询被索引覆盖了。3.2 常见的 SQL 改写技巧SQL 改写不是让你背一堆奇技淫巧而是理解数据库优化器的工作方式帮它做出更好的决定。我常用且验证有效的改写方式有这几种多个小查询合并成一个大查询。当一个循环里反复执行单条查询时比如在 Java for 循环里查用户信息每次查一条1000 个用户就是 1000 次网络来回。改成 WHERE id IN (...) 一次查回来然后再内存中组装整体耗时往往能降一个数量级。用 EXISTS 替代 IN 的争论。这个要看具体情况没有绝对。小表驱动大表的原则依然有效——当子查询结果集很小比如小于主表一个量级用 IN 或 EXISTS 的性能差异不大当子查询结果集巨大用 EXISTS 配合关联条件往往更好。但更本质的解决思路是能 JOIN 就不要用子查询能提前过滤就不要让数据膨胀。避免 SELECT * 显式列出字段。SELECT * 有几个坏处一是可能破坏索引覆盖数据库必须回表才能取到所有列二是网络传输的数据量变大三是在表结构变更时容易产生不可控的影响。把 SELECT 里的字段精确到你要用的列是最简单也最容易忽略的优化。用 UNION ALL 替代 UNION。UNION 会做去重这需要对结果集进行排序或哈希开销很大。如果你能确定两个查询结果不会重复直接用 UNION ALL省掉去重这一步性能提升非常明显。分页深翻页优化。LIMIT 1000000, 20 这种写法数据库要扫描 1000020 行然后丢弃前 1000000 行代价极高。常见的优化方法是记录上一页的最大 IDWHERE id last_max_id ORDER BY id LIMIT 20。这个方法依赖连续自增主键或单调递增的唯一键适用于以时间线为主轴的列表查询。3.3 从执行计划到 SQL 改写的实战案例说一个我处理过的真实案例。某个报表系统有个查询要统计每个城市在某个时间段内的订单金额SQL 大概是SELECT city_id, SUM(amount) FROM orders WHERE created_at BETWEEN 2024-06-01 AND 2024-06-30 GROUP BY city_id;orders 表有 5000 万行created_at 上有索引但执行计划显示走了全表扫描。原因在于查询的返回列是 city_id 和 SUM(amount)而索引 (created_at) 只覆盖了 created_at 一列数据库如果要走这个索引需要回表拿 city_id 和 amount对于 6 月份占总量 20% 的数据约 1000 万行优化器一算盘认为回表代价太大干脆全表扫描。改写方案是建一个覆盖索引ALTER TABLE orders ADD INDEX idx_created_city_amount (created_at, city_id, amount);索引建好后查询可以直接在索引树上完成按 created_at 定位到 6 月的数据区间然后顺序扫描这 1000 万条索引记录顺便拿到 city_id 和 amount无需回表。实测这个查询从全表扫描的 18s 降到了 0.3s效果立竿见影。这个案例的核心启发是索引设计不能只顾 WHERE 条件还要考虑 SELECT 的列和 GROUP BY / ORDER BY 的列。查询的所有字段都覆盖在索引里就是性能的一层保底。4. 数据库配置参数调优理解比照搬重要4.1 几个影响最大的关键参数配置参数的修改其实很危险因为网上流传的配置模板参差不齐照搬大概率出问题。我建议先理解再动手重点关心这几个影响面最大的参数。MySQL 的 InnoDB 缓冲池innodb_buffer_pool_size排在第一位。它是 InnoDB 缓存数据和索引的内存区域直接决定了内存能容纳多少热数据。官方推荐设置为机器物理内存的 70%-80%但实际操作要看实际情况如果一台机器上只跑 MySQL可以按这个比例如果同时还跑着应用或其他服务就得控制比例留足余量。我常用一个判断依据观测缓冲池命中率通过 SHOW GLOBAL STATUS 计算 (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%线上建议保持在 99.5% 以上如果低于这个值优先考虑增大缓冲池或是优化查询。连接数max_connections也是个高发坑。默认值通常是 151高并发场景下很容易打满报 Too many connections 错。但注意无脑调大连接数不是好办法——每建一个连接MySQL 都要分配线程和内存连接数过多会导致上下文切换频繁性能不升反降。合理的做法是先看活跃连接数的基线值再结合SHOW PROCESSLIST观察连接空闲情况最终确定一个既能撑住峰值又不会拖垮系统的值。临时表大小tmp_table_size / max_heap_size经常被忽略但对分组排序类查询影响巨大。当临时结果集超过这个阈值MySQL 会把内存临时表转为磁盘临时表性能暴跌好几个数量级。如果执行计划里频繁出现 Using temporary除了改写 SQL还要检查这两个参数是否太小。PostgreSQL 用户重点看 shared_buffers 和 work_mem。shared_buffers 默认值偏保守官方建议是物理内存的 25%但通常还需要配合操作系统层级的 cache 考虑work_mem 决定单个排序或哈希操作能使用的内存太大会导致内存被多个并发进程打满太小会让排序走磁盘。4.2 参数调优怎么验证效果调参不是改一改配置文件就完事必须用数据验证。我的标准流程是改参数前记录基线数据确认修改后观察 24 到 72 小时然后对比关键指标。验证时重点看三个指标慢查询数量的变化、缓冲池命中率的变化、业务接口的 P95/P99 延迟。慢查询数量下降说明执行层面受益命中率上升说明缓存效果改善P95/P99 延迟下降说明用户真实体验变好。这三个指标单独看都有盲区但合在一起能形成一个比较完整的验证闭环。注意每次只改一个参数观察稳定后再动下一个。多参数一起改一旦出问题你根本不知道是谁的锅。这跟排查线上故障是一个道理——变量越少定位越容易。5. 事务、锁与并发让数据库“不打架”5.1 死锁是怎么发生的数据库性能不只是索引和参数的事高并发场景下锁和事务往往才是拖垮系统的元凶。死锁的本质是两个或多个事务各自持有对方需要的锁互相等待谁也不让谁。经典的死锁案例是转账场景。事务 A 先更新账户 1再更新账户 2事务 B 先更新账户 2再更新账户 1。如果两个事务同时执行A 持有账户 1 的锁等账户 2B 持有账户 2 的锁等账户 1就形成了死锁。解决死锁的方向有三个一是调整事务的加锁顺序所有事务都按同一个顺序比如先更新 ID 小的记录加锁死锁概率能大幅降低二是把大事务拆成小事务锁持有时间越短发生冲突和死锁的概率越低三是设置合理的锁等待超时时间innodb_lock_wait_timeout让数据库快速报错而不是无限期等待。5.2 连接池参数和事务边界连接池也是性能调优里容易忽略的一环。连接池太小高并发时请求会排队等待获取连接连接池太大数据库服务端维护大量空闲连接内存和线程资源白白浪费。最常用的配置是 HikariCPSpring Boot 默认。它的关键参数是 maximum-pool-size 和 minimum-idle。我的经验是maximum-pool-size 设置为 CPU 核心数的 2-4 倍是一个合理的起点然后用压测验证。注意连接池的最大值需要和数据库的 max_connections 配套考虑避免应用层的连接池总和超过数据库上限。事务边界是另一个微妙的场景。我见过很多人把 RPC 调用、文件操作放在数据库事务里一条更新语句执行完之后事务迟迟不提交锁就一直在那里握着。事务应该在最短时间内完成原则是数据库操作尽量集中非数据库的操作挪到事务外面。6. 常态化监控与容量规划别再等出事了才救火6.1 监控指标的取舍与告警设置出了事故再排查永远是成本最高的方式。性能调优的价值不仅在于解决眼前的慢查询更在于建立一套“提前发现问题”的机制。监控指标不需要面面俱到但核心的几条必须有。我线上长期盯着的指标有这么几类数据库层面的慢查询数量趋势、活跃会话数、InnoDB 缓冲池命中率、锁等待时长、主从复制延迟操作系统层面的 CPU、内存、磁盘 IO 使用率。这些指标和业务表现是强关联的——一旦曲线异常业务大概率已经在受影响。告警规则要避免“狼来了”效应。告警太灵敏半夜起来处理假警报几次之后就没人重视了告警太迟钝真出事时来不及反应。我的做法是把告警分为两级一级告警需要立即处理比如活跃会话数超过基线的 200%持续时间超过 5 分钟二级告警记录观察比如磁盘空间使用率超过 75%。6.2 容量规划数据增长是绕不开的坎容量规划是很多人忽视但迟早要面对的功课。数据库不会永远待在舒适区数据量上来了之前调好的优化可能全部失效。所以监控的另一个重要任务是预测数据增长对性能的影响。我建议按月做好数据量趋势统计推到未来 6-12 个月的量级。如果预计半年后某张表要从 1 亿行涨到 5 亿行就需要提前规划是分区、分库还是归档历史数据。分区表适合按时间维度的数据管理可以把一张大表拆成多个物理分区查询命中分区后扫描量会大幅减少归档则是把冷数据迁移到历史库线上只保留热数据。冷热数据分离是我处理大表最常用的手段。比如订单表90 天前的订单基本不会被频繁查询可以把它们迁移到归档表或者独立的历史库中。线上数据量降低索引变小缓存命中率提升整体性能都受益。写在最后的几条心得这行干久了最大的感受是数据库性能调优不是一门“死记硬背”的技术而是一套“观察—假设—验证”的思维方式。慢查询不可怕可怕的是不看基线就瞎调参数、不读执行计划就乱加索引。有几个习惯我至今保持着接手任何一个系统先花半天时间把监控体系补全任何一次调优都记录下改动前的基线和改动后的效果每次线上事故都复盘成一篇文档沉淀到团队的运维手册里。这些习惯在长期来看远比任何一次具体的调优技巧更有价值。最后分享一个小技巧如果你不知道怎么开始定位一个慢查询最笨也最有效的办法是把慢查询日志打开把阈值设低一点跑一天然后看看占用总耗时最多的 Top 10 SQL 是哪些。解决完这 10 条你系统的性能问题大概率已经解决了 80%。