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

性能优化这件事，一旦业务量上来，你根本绕不开。我之前参与过一个业务系统的性能排查，应用层代码看了半天，逻辑上挑不出什么大毛病，接口响应就是慢，从 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 分析执行计划。这个步骤看起来基础，但很多人只是扫一眼有没有走索引，实际上不够，要重点看几列：

举个例子，有一条查询语句：

SELECT user_id, amount, create_time FROM order_log WHERE user_id = 10086 ORDER BY create_time DESC LIMIT 20;

如果 EXPLAIN 的结果显示 type 是 ALL，rows 是 50 万，Extra 里有 Using filesort，那问题就很明显了。user_id 没走索引，而且排序也没法用索引，数据库把 50 万行捞出来之后又做了一次磁盘排序，不慢才怪。

这时候的优化思路就不是盲目加索引，而是分析这个查询的常见模式。如果这个查询主要按 user_id 过滤再按时间排序，那应该建一个联合索引：

ALTER TABLE order_log ADD INDEX idx_user_time (user_id, create_time);

建完联合索引之后再看执行计划：type 从 ALL 变成 ref，rows 从 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 或 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.somaxconn=65535 # 永久写入 echo "net.core.somaxconn=65535" >> /etc/sysctl.conf sysctl -p

这类参数属于“平时感觉不到、出事就是大事”的类型，建议在服务器初始化阶段就统一配置，不要等服务出问题了再回头补。

3.3 连接数管理：连接池并不是越大越好

数据库连接数是运维层面最容易踩坑的地方。曾经有个系统，运维同学为了提升并发处理能力，把数据库的最大连接数调到了 2000，应用侧的连接池也开着很大的值。结果高峰期一到，数据库 CPU 直接打满，所有请求反而比之前更慢。原因很简单：数据库连接是重资源，每条连接都占用内存和 CPU 上下文切换的开销，连接数太多，系统开销会成倍增长。

我们的调优实践是：应用连接池保持在一个克制的水平，核心服务连接池上限设置为 50，加上备用连接，总体不会超过 200；数据库端最大连接数设置为 500 左右，留足余量给管理工具和其他临时连接。

关于连接池参数，以常见的连接池为例：

另外一个容易被忽略的坑是应用连接池的获取连接超时设置。如果超时时间太长，数据库一旦出现锁等待，应用层会一直排队拿不到连接，表现为接口全部超时。建议把 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 复盘总结：这次优化我们做对了什么

回看整个过程，我们做对的核心是一个“顺序”：先定位，再有针对性优化，最后验证。没有一上来就拆表，也没盲目调参，而是从慢日志到执行计划一步一步下来，每一步都有数据支撑。

这次优化的方法论可以抽象成一个决策顺序表：

在你想实行更大规模、更复杂的方案之前，建议先按这个顺序走一遍，很可能你根本不用走到第四步就已经解决问题了。

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 = 256M

5.5 数据库优化和运维调优的优先级，怎么排最合理

最后用我自己的经验总结一下优先级。我的排序是：慢 SQL 定位和分析优先于所有参数调整；索引设计优先于 SQL 重构；缓存优先于水平扩展；架构拆分放在最后。

有人可能会问，为什么参数调优优先级那么低？因为参数调优解决的是“资源利用效率”问题，如果你的 SQL 本身要扫全表，给再多内存也只是把全表扫描从磁盘挪到内存，治标不治本。反过来，一条好的 SQL 加上合适的索引，即使服务器资源一般，也能跑出很好的性能。

至于缓存，它能解决 80% 的重复查询压力，性价比极高。但缓存也有副作用——数据一致性问题，所以只适合对实时性要求不高的查询。核心写操作和强一致读必须走数据库，这点不能妥协。

选择工具和方案的时候，我建议大家养成记性能基线的好习惯：每次做一项优化，都记录优化前后的响应时间、吞吐量、资源使用率，有了这些数据，后续优化才能有的放矢。性能优化最怕的不是没有方案，而是不知道现状。

数据库和运维层面的优化不像写代码那样可以随时重构，它牵一发而动全身，所以一定要谨慎、有策略、以数据为依托地推进。我能分享的实操经验基本就是这些，希望你能在你的业务场景里落地验证。数据库和运维层面的优化，专注细节和方法，系统稳定、查询快，才是最终目标。