数据库与运维双管齐下：从慢查询到索引调优的系统性能优化实践

性能优化这件事一旦业务量上来你根本绕不开。我之前参与过一个业务系统的性能排查应用层代码看了半天逻辑上挑不出什么大毛病接口响应就是慢从 100 毫秒慢慢爬到 800 多毫秒最后定位到问题全在数据库和运维层面——索引没建对、连接池打满、磁盘 IO 出现规律性抖动。那次之后我得出的结论是很多性能问题看上去是代码问题根子却在下游的数据存储和基础设施上。所以今天这篇我想专门聊聊从数据库和运维这两个容易被忽略的层面怎么系统地做性能优化。我尽量不写教科书式的东西所有内容都是实操层面的经验和踩坑记录适合正在维护线上业务的开发、DBA 或者运维同学阅读。你会看到我们是怎么一步步排查、怎么选型、怎么压测验证的也会看到我们踩过的坑和事后总结的避坑清单。正文开始前先说清楚这篇的覆盖范围数据库层面我主要讲的是关系型数据库以 MySQL 为主同时会泛称 PostgreSQL运维层面主要讲服务器、连接管理、监控和容量评估。这两个方向交叉在一起基本能覆盖绝大多数中小型业务系统的性能痛点。1. 从数据库层面开始先搞清楚慢在哪一环很多人一说到数据库优化第一反应就是加索引。但“加索引”这三个字解决不了所有问题而且如果索引加错了反而会拖慢写入速度、占额外磁盘空间。我们在做性能分析时的思路是先把慢查询找出来再分析执行计划最后才决定要不要动索引或者改 SQL。1.1 第一步必须是慢查询日志和分析而不是拍脑袋慢查询日志是数据库给我们的最直接的线索。我们当时就在测试环境和生产环境都开启了慢查询记录阈值先设为 1 秒后来逐步调低到 300 毫秒、200 毫秒。因为有些 SQL 平时不慢但高峰期一上来就变慢1 秒的阈值根本抓不到这些潜伏问题。开启慢查询的方式很简单-- 临时开启重启后失效 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log; -- 永久生效需要写入配置文件 my.cnf [mysqld] slow_query_log 1 long_query_time 1 slow_query_log_file /var/log/mysql/mysql-slow.log log_queries_not_using_indexes 1有一点要注意阈值不是越低越好。如果业务量很大把阈值设为 100 毫秒慢日志会爆炸式增长反而影响数据库性能。我们的经验是先粗后细先用 1 秒阈值跑三天收集明显的慢 SQL再降到 500 毫秒跑两天最后稳定在 300 毫秒左右作为常规监控线。拿到慢日志之后不要用肉眼一条条看效率太低。我当时用了一个开源工具把慢日志里的 SQL 聚合起来按“总执行时间 / 平均执行时间 / 出现次数”排序。最优先处理的 SQL 特征是出现次数多、单次执行时间长、扫描行数和返回行数差距巨大。这种 SQL 通常就是典型的“全表扫描”问题。1.2 用 EXPLAIN 看执行计划重点盯 rows 和 type慢日志找到具体 SQL 之后下一步就是用 EXPLAIN 分析执行计划。这个步骤看起来基础但很多人只是扫一眼有没有走索引实际上不够要重点看几列列名重点关注内容type从好到差依次是 system const eq_ref ref range index ALL。出现 ALL 就是全表扫描必须警惕rowsMySQL 预估要扫描的行数这个数字远大于实际返回行数时就说明过滤性不好Extra出现 Using filesort、Using temporary 时说明排序或去重使用了临时文件/临时表通常是性能隐患举个例子有一条查询语句SELECT user_id, amount, create_time FROM order_log WHERE user_id 10086 ORDER BY create_time DESC LIMIT 20;如果 EXPLAIN 的结果显示 type 是 ALLrows 是 50 万Extra 里有 Using filesort那问题就很明显了。user_id 没走索引而且排序也没法用索引数据库把 50 万行捞出来之后又做了一次磁盘排序不慢才怪。这时候的优化思路就不是盲目加索引而是分析这个查询的常见模式。如果这个查询主要按 user_id 过滤再按时间排序那应该建一个联合索引ALTER TABLE order_log ADD INDEX idx_user_time (user_id, create_time);建完联合索引之后再看执行计划type 从 ALL 变成 refrows 从 50 万变成几十行Extra 里的 Using filesort 消失了。这就是最典型的、性价比最高的优化路径。1.3 索引设计的取舍不是越多越好要会做减法索引能解决很多问题但索引也会带来副作用。每次 INSERT、UPDATE、DELETE 时索引都需要同步更新索引太多会导致写入变慢而且占用的磁盘空间不小。我见过一个业务表总共 30 多个字段索引建了 18 个其中好几个索引只是前缀不同实际查询根本用不上。结果就是写入性能差到离谱单表每秒只能写几百行。后来我们做了索引清理从 18 个砍到 7 个写入性能直接翻了三倍线上查询也没受影响。索引设计我的经验是三个方面第一区分 OLTP 和 OLAP 场景。OLTP 场景下单表索引控制在 5 个以内比较合理优先满足高频查询OLAP 场景可以适当放宽但还是那句话只建有用的索引别为了“万全”而建一堆冗余索引。第二联合索引遵守最左前缀原则。MySQL 的联合索引是按字段顺序排列的查询条件里如果没包含最左字段索引就用不上。所以联合索引的字段顺序要按照查询条件的区分度来排区分度高的字段放前面。第三能用覆盖索引解决的就不回表。还是上面那个订单表的例子如果查询只涉及 user_id、amount、create_time 三个字段而联合索引恰好包含这三个字段数据库可以直接从索引里拿到全部数据连回表查询都省了这种查询在压力测试里表现非常优秀。2. 数据库层面的硬核调优从参数到架构逐层推进如果索引和 SQL 已经优化过一轮慢查询还是有那问题就可能出在数据库实例本身的配置或者架构设计上。这块牵扯到的东西比较多我按“单机参数调优 — 读写分离 — 垂直拆分 — 水平分表分库”的顺序来说你不会一上来就要做分库分表那是最后手段。2.1 InnoDB 引擎参数调优先把缓冲池和刷盘策略讲清楚绝大多数业务表用的是 InnoDB 引擎它的核心机制是缓冲池Buffer Pool。简单理解数据在内存里读写最快缓冲池就是数据库在内存里准备的一块大缓存区放热数据。读的时候优先查缓冲池命中就不走磁盘写的时候也先写缓冲池由后台线程慢慢把脏页刷到磁盘。所以缓冲池够不够大直接影响数据库性能。我们的一个 16G 内存的数据库实例缓冲池默认值是 128M这显然不合理。按照通用经验把 innodb_buffer_pool_size 调整为物理内存的 60% 到 70%最好在配置文件里直接设置具体值[mysqld] # 以 16G 内存为例设置 10G 给缓冲池 innodb_buffer_pool_size 10G # 如果是 MySQL 5.7 以上可以设置多个实例减少内部锁竞争 innodb_buffer_pool_instances 8这里提一句修改后要观察监控。缓冲池命中率保持在 95% 以上算正常如果长期低于 90%说明内存不够或者数据访问模式不友好光调参数解决不了根本问题。再讲刷盘策略。innodb_flush_log_at_trx_commit 这个参数控制事务日志的刷盘时机有 0、1、2 三个值参数值含义优缺点0每秒刷一次日志到磁盘性能最好但崩溃时可能丢失最多 1 秒的事务1每次事务提交都刷盘最安全性能最差2每次事务提交只写到系统缓存每秒刷盘性能较好崩溃时可能丢数据但影响比 0 小很多开发同学为了追求写入性能直接设成 0 或 2这很危险。金融、支付、订单类系统不能这么干必须设为 1。非核心业务、允许少量数据丢失的场景才考虑 2。我当时负责一个订单系统妥协的方案是核心支付表用 1日志类、统计类表所在的实例用 2兼顾安全和性能。2.2 读写分离不是银弹要选对同步方式当单库的读压力越来越大很多人自然而然地想到读写分离。主库负责写从库负责读听起来很完美但实际落地时有很多细节。我参与过一个项目初期读写分离用了异步复制结果在业务高峰期出现了主从延迟从库上的报表数据比主库落后好几秒。业务团队反馈“刚创建的订单在列表里看不到”最后产品经理都找过来了挺被动的。解决思路有两个方向。第一个是强制读主库适合对实时性要求极高的场景——比如用户刚下单后立刻查订单详情这个查询可以路由到主库通过账号 ID 哈希或按功能模块做强制路由。第二个是优化同步链路减少主从延迟。比如对于单线程复制导致的延迟可以开启并行复制让多个 worker 线程同时应用 binlog[mysqld] # 开启并行复制适用于 MySQL 5.7 以上 slave_parallel_type LOGICAL_CLOCK slave_parallel_workers 8这个参数改完效果很明显从库延迟一度从十几秒降到一两秒以内。但还是那句话没有银弹如果业务对一致性要求极高不要盲目读写分离先评估能不能通过缓存兜住读流量。2.3 数据量大不是拆分借口先用归档和分区缓解做数据库优化最忌讳的就是“动不动就分库分表”。我见过一张订单表数据量 2000 万行这数字说实话根本不是必须拆表的量级。正确的做法是先做数据生命周期管理把历史数据归档走。我们的操作是按业务要求把 3 个月之前的订单数据迁移到一张归档表或者另一个库线上只保留热数据。归档程序用定时任务跑每天晚上低峰期执行每次处理一小批比如 5000 行避免锁表和主从延迟。还有一个思路是分区表Partitioning。MySQL 的 InnoDB 支持 RANGE、LIST、HASH 等分区方式我们当时用的最多的是按日期 RANGE 分区ALTER TABLE order_log PARTITION BY RANGE (YEAR(create_time)) ( PARTITION p2024 VALUES LESS THAN (2025), PARTITION p2025 VALUES LESS THAN (2026) );分区的好处是按分区裁剪数据查询只扫描对应分区不扫全表清理历史数据直接 DROP 分区效率极高不用一条条 DELETE。但要注意分区不是万能钥匙分区字段必须出现在查询条件里才有用否则还是会全分区扫描。2.4 必须动手做的分表分库浅谈单库单表的极限如果业务真的到了单表每天新增几百万行且历史归档也做完了、读写分离也上了还是扛不住那才考虑分表分库。分表分库的复杂度是指数级上升的一旦引入所有涉及这张表的联表查询、事务、分页逻辑全部要重写。我们的建议是除非万不得已不要走这一步。真要拆的话有几种常见思路垂直拆分把一个业务拆成几个业务库订单库、用户库、商品库分开水平拆分按某个字段的哈希值或取模把同一张表的数据分散到多个库的多个表里。拆完以后必须引入中间件或者改造成分布式事务方案复杂度完全不一样。当时我们拆过一个查询量巨大的流水表按用户手机号后四位取模分到 16 个库里每个库又拆 4 张表总表数 64 张。查询逻辑全部改造成先按用户取模定位到具体表再查询。效果是单次查询的扫描行数从几百万降到了几万级响应时间从秒级降到毫秒级。但开发量大概占了一个版本的三分之一这还不算后续的运维复杂度。所以分表分库永远不是第一选择它只是最后的退路。3. 运维层面的优化把底子打好比什么都重要数据库调优做得再漂亮底层服务器资源跟不上也是白搭。运维层面的优化我划分成三块监控体系建设、服务器层参数调优、容量与连接管理。这一层看似离业务代码很远但往往是决定系统能不能稳定扛住峰值流量的关键所在。3.1 监控不是装个面板就行要能从数据里看出问题很多团队都部署了监控系统但在实际工作里我发现大部分监控页面只是“看起来有数据”没人看或者看不懂。我们的经验是监控体系至少要覆盖三个层次服务器层、数据库实例层、业务查询层。服务器层主要看 CPU、内存、磁盘 IO、网络带宽和负载。注意不要只看平均值要看峰值行为。之前有个案例某台数据库服务器 CPU 平均使用率只有 30%表面看着很稳但在夜间定时任务触发时CPU 会短暂飙到 90%恰好这个时段有业务高峰流量进来结果就是卡顿和超时。数据库实例层重点看连接数、慢查询数量、缓冲池命中率、临时表数量、行锁等待时间。这几个指标基本能反映数据库的健康度。我们当时设置了三级告警黄色告警代表连接数超过阈值的 60%橙色是 80%红色是 90% 以上并直接打电话给值班人。业务查询层要能追踪到具体 SQL 的执行时间分布。不是只看有没有慢查询还要看 P95、P99 响应时间比如订单查询 P99 达到 800 毫秒那就有 1% 的请求在实际体验里是明显卡顿的这就很有优化空间。我们常用的追踪方法是把请求 ID 贯穿应用日志和 SQL 日志出了性能问题可以快速定位到具体是哪一条 SQL 拖慢的。3.2 操作系统层参数调优文件句柄和网络队列数据库跑在 Linux 上操作系统的一些默认参数对数据库性能影响很大但很多运维同学会忽略。举两个最常见的。第一个是文件句柄限制。MySQL 在运行过程中会打开大量文件包括表文件、binlog、慢查询日志、错误日志等。如果操作系统的文件句柄上限太低数据库会在高频访问时报 “Too many open files” 错误。调大方式是在 /etc/security/limits.conf 中加配置mysql soft nofile 65535 mysql hard nofile 65535如果你用的是 systemd 管理服务还需要在 service 文件里设置 LimitNOFILE。第二个是网络连接队列。高并发场景下TCP 的 backlog 队列如果太小新连接会被内核直接丢弃应用层表现为“偶发性连接失败”或者“连接超时”。调整方式# 查看当前值 sysctl net.core.somaxconn # 临时调整 sysctl -w net.core.somaxconn65535 # 永久写入 echo net.core.somaxconn65535 /etc/sysctl.conf sysctl -p这类参数属于“平时感觉不到、出事就是大事”的类型建议在服务器初始化阶段就统一配置不要等服务出问题了再回头补。3.3 连接数管理连接池并不是越大越好数据库连接数是运维层面最容易踩坑的地方。曾经有个系统运维同学为了提升并发处理能力把数据库的最大连接数调到了 2000应用侧的连接池也开着很大的值。结果高峰期一到数据库 CPU 直接打满所有请求反而比之前更慢。原因很简单数据库连接是重资源每条连接都占用内存和 CPU 上下文切换的开销连接数太多系统开销会成倍增长。我们的调优实践是应用连接池保持在一个克制的水平核心服务连接池上限设置为 50加上备用连接总体不会超过 200数据库端最大连接数设置为 500 左右留足余量给管理工具和其他临时连接。关于连接池参数以常见的连接池为例参数建议值说明initialSize5初始连接数避免高峰临时建连minIdle5最小空闲连接保持下限maxActive50最大活跃连接这个值要谨慎maxWait3000获取连接超时时间毫秒避免无限等待validationQuerySELECT 1验证连接是否存活另外一个容易被忽略的坑是应用连接池的获取连接超时设置。如果超时时间太长数据库一旦出现锁等待应用层会一直排队拿不到连接表现为接口全部超时。建议把 maxWait 设置为 1 到 3 秒超时快速失败至少能保住一部分请求。3.4 磁盘与备份策略IO 不够性能再优化也是白搭有时候数据库参数、SQL 都优化过了还是慢一查磁盘 IO 进入 80% 以上利用率了。这种情况再怎么调 SQL 都没用得从磁盘和备份策略入手。如果是机械硬盘换成 SSD 是质的提升。我们当时把一台 MySQL 实例的数据目录从机械盘迁到 SSD 之后同样的 SQL执行时间直接减少了一半以上。如果是云服务器至少选择 SSD 云盘或者更高性能的 ESSD。备份策略方面很多人忽视了备份对线上性能的影响。物理备份工具在备份过程中会占用大量磁盘 IO如果备份时间正好在业务高峰那线上性能必受影响。我们的经验是备份时间尽量安排在业务低峰期比如凌晨 2 点到 4 点同时备份方式优先选择增量备份避免每天做全量备份。全量备份可以放到周末凌晨增量备份每天做。另外备份文件的存放要跟数据库实例落在不同的磁盘或机柜上防止磁盘故障时备份也跟着遭殃。这个坑我们踩过后来数据恢复的时候发现备份文件也损坏了整个人差点崩溃。多留一份异地备份不是什么高科技但真的是救命的东西。不过要重点说明上面这些运维层调整属于“环境优化”的范畴它一定是配合数据库和应用的整体设计来运行的。离开业务场景谈参数调优容易陷入“为调优而调优”的误区。4. 一次真实的性能优化案例复盘从 800ms 到 80ms 的蜕变上面讲了很多方法论可能有点抽象。这里我完整复盘一次我们实际做过的数据库与运维联合优化案例你可以对号入座看看自己是不是也有类似问题。4.1 背景和症状一个查询接口响应时间持续上涨业务背景是某电商后台的订单列表接口。上线初期响应时间平均在 200 毫秒用户量增长到一定程度后响应时间开始持续上涨某天大促预热期间已经涨到平均 800 毫秒左右P99 超过 2 秒。遇到问题的第一反应肯定先查应用层通过日志我们发现应用层处理时间只有 50 毫秒剩下 750 毫秒全消耗在数据库查询阶段也就是 SQL 执行时间全在数据库上。数据库侧排查发现接口对应的主查询 SQL 执行时间在 SQL 层就要 600 到 700 毫秒扫描行数平时在 20 万到 30 万行高峰期能到 80 万行。4.2 定位与优化从 SQL 到索引到架构逐层做第一步是分析那条 SQL 的执行计划发现订单表用 create_time 时间范围过滤但有个关键的关联字段 user_id 没有索引。订单表数据量大约 1500 万行查询条件按 store_id create_time 范围取数再做关联因为关联字段没有索引每次关联都要扫描表这部分代价最大。优化第一步很直接给关联字段和过滤字段建设联合索引。改完以后扫描行数立刻从 80 万降到 2 万以内SQL 执行时间从 700 毫秒降到 150 毫秒左右。第二步是优化查询逻辑。原 SQL 中的子查询带着 DISTINCT 和 ORDER BY这触发了临时表排序。我们改造成先取出主表 ID 集合再分批关联明细砍掉不必要的去重逻辑。这一步之后SQL 执行时间进一步降到 80 毫秒左右。第三步是处理更高频的访问模式增加了一层缓存。列表页前 3 页的数据对实时性要求没那么高我们把前 3 页的查询结果缓存 30 秒缓存命中的时候接口响应时间直接降到 15 毫秒以内数据库压力骤减。4.3 运维层面同步调整缓存预热和数据库实例规格这个案例里运维层面我们做了三件事第一给数据库实例开启慢查询日志并用监控系统持续跟踪确保类似问题能第一时间发现第二调整连接池之前连接池最大开到 100高峰期创建了大量新连接改到 40 后配合预热脚本反而稳定很多第三把数据库实例规格从标准型升到 IO 优化型磁盘 IOPS 提升了两倍大促前的压测整体平稳。最后这个接口在大促峰值期的表现是平均响应 80 毫秒P99 控制在 300 毫秒以内数据库 CPU 使用率从之前的 70% 降到 30% 左右。整个过程从开始排查到优化完成大概用了两周主要时间花在慢日志分析和 SQL 逻辑梳理上真正动配置的时间没超过两天。4.4 复盘总结这次优化我们做对了什么回看整个过程我们做对的核心是一个“顺序”先定位再有针对性优化最后验证。没有一上来就拆表也没盲目调参而是从慢日志到执行计划一步一步下来每一步都有数据支撑。这次优化的方法论可以抽象成一个决策顺序表优化层级优化动作验证指标第一步慢查询日志抓取 EXPLAIN 分析SQL 扫描行数、Extra 列内容第二步索引设计与 SQL 重构SQL 执行耗时、扫描行数变化第三步缓存与查询逻辑调整接口响应时间、缓存命中率第四步服务器参数与连接池调优CPU、连接数、IO 等待在你想实行更大规模、更复杂的方案之前建议先按这个顺序走一遍很可能你根本不用走到第四步就已经解决问题了。5. 常见问题与排查技巧实录这些坑你可能也会踩最后这部分是纯干货合集都是我们日常运维和调优中遇到的真实问题我整理成几个高频场景每个场景写下排查思路和解决办法希望能帮你省下一些排查时间。5.1 慢查询日志没抓到但业务确实慢怎么查有次业务方反馈订单查询慢但慢查询日志里一条记录都没有。后来排查发现问题不在 SQL 执行本身而是应用层拿连接等待耗时过长。当时连接池的连接被其他慢事务占满了新请求排队拿连接等拿到时已经过去 300 多毫秒不算在 SQL 执行时间里所以慢查询日志根本不会记录。排查办法在应用层埋点记录完整的请求耗时拆解——连接获取时间、SQL 执行时间、结果转换时间。听到“连接获取时间”普遍超过 200 毫秒时问题基本就锁定了。对应解决方案就是前面讲过的连接池参数调优尤其是 maxActive 和 maxWait。5.2 索引加了但还是慢问题出在哪这个场景我遇到不止一次很多人给表加了索引但查询还是慢怀疑索引没用上。用 EXPLAIN 一看提示可以用的索引有好几个实际用的却不是我们期望的那个。这种情况在 MySQL 优化器判断不准确时比较容易出现比如数据分布不均匀、或者统计信息过期。解决办法是可以强制指定索引来做验证SELECT ... FROM order_log FORCE INDEX (idx_user_time) WHERE ...;如果 FORCE INDEX 之后速度明显变快说明优化器选错了索引可以考虑用 ANALYZE TABLE 更新统计信息或者重新审视索引结构看看是不是存在冗余索引干扰了选择。5.3 数据库 CPU 突然 100%怎么办CPU 打满通常有三种可能大量慢 SQL 并发执行、连接数爆炸、定时任务和业务高峰撞车。第一件事不是杀进程而是先拍快照查看当前活跃的连接和正在执行的 SQL用 SHOW PROCESSLIST 或者查询 information_schema.processlist 表。只要拿到正在跑的 SQL 列表基本能瞬间定位是哪一类查询引起的。我们的处理策略是紧急情况下先 kill 掉占用资源最严重的连接让系统先恢复然后分析 SQL 慢的原因最后针对性地加索引或者改 SQL防止复发。5.4 磁盘空间告急别只想着扩容磁盘空间满了当然要扩容但如果只扩容不治理问题迟早会再出现。磁盘空间增长最猛的两个来源是 binlog 和慢查询日志尤其是 binlog。我们当时遇到过一个数据库实例binlog 文件堆积了几百 GB。排查发现是因为从库挂了主库为了保证复制恢复一直没清理历史 binlog。处理办法是先把从库恢复让复制追平再确认 binlog 保留期策略。合理的策略是按照保留小时数或大小自动清理[mysqld] expire_logs_days 7 # MySQL 8.0 使用这个参数 binlog_expire_logs_seconds 604800 max_binlog_size 256M5.5 数据库优化和运维调优的优先级怎么排最合理最后用我自己的经验总结一下优先级。我的排序是慢 SQL 定位和分析优先于所有参数调整索引设计优先于 SQL 重构缓存优先于水平扩展架构拆分放在最后。有人可能会问为什么参数调优优先级那么低因为参数调优解决的是“资源利用效率”问题如果你的 SQL 本身要扫全表给再多内存也只是把全表扫描从磁盘挪到内存治标不治本。反过来一条好的 SQL 加上合适的索引即使服务器资源一般也能跑出很好的性能。至于缓存它能解决 80% 的重复查询压力性价比极高。但缓存也有副作用——数据一致性问题所以只适合对实时性要求不高的查询。核心写操作和强一致读必须走数据库这点不能妥协。选择工具和方案的时候我建议大家养成记性能基线的好习惯每次做一项优化都记录优化前后的响应时间、吞吐量、资源使用率有了这些数据后续优化才能有的放矢。性能优化最怕的不是没有方案而是不知道现状。数据库和运维层面的优化不像写代码那样可以随时重构它牵一发而动全身所以一定要谨慎、有策略、以数据为依托地推进。我能分享的实操经验基本就是这些希望你能在你的业务场景里落地验证。数据库和运维层面的优化专注细节和方法系统稳定、查询快才是最终目标。