数据库访问性能分层优化连接池、批处理、索引与慢查询的联合排查一、先把问题说成一个具体的事假设你负责一个订单查询与批量写入服务某天收到反馈订单详情接口 P99 从 80ms 涨到 900ms批量导入接口在高峰期偶尔超时监控上数据库 CPU 没有明显飙升应用侧线程却大量处于等待状态。很多人第一反应是“加连接池”“加索引”“加缓存”。这三个动作都可能有效也都可能完全无效因为它们是不同层次的修复手段。数据库访问性能问题往往不是一个点坏了而是几个环节叠在一起应用线程在等连接拿到连接后一条 SQL 被拆成很多次网络往返单条 SQL 的执行计划选错了扫描方式缺索引或索引不匹配导致扫描行数远大于返回行数事务开得太长把锁持有时间放大其他请求排队。如果不分层很容易出现这样的循环看到慢就调大连接池数据库连接数上去后锁竞争更严重于是更慢再看到慢就加索引索引加上去但 SQL 没走到它于是还是慢。最终改动了一堆参数却说不清哪个改动真正起了作用。这篇文章要完成的一件事是给出一套可复用的分层排查顺序让你在面对“数据库访问变慢”时能先定位在哪一层再做最小改动并用可观察的证据验证改动有效。二、环境与兼容范围本文的示例围绕以下版本展开其他版本的行为需要单独确认组件版本说明JDKJava 21使用虚拟线程做对照实验时需要 21Spring Boot3.5.x默认连接池为 HikariCP数据库MySQL 8.0 系列EXPLAIN 输出与 innodb 锁视图以 8.0 为参照构建Maven示例只列必要依赖需要明确区分几类事实官方保证HikariCP 的maximumPoolSize是池允许的最大连接数超时由connectionTimeout控制这是库文档层面的行为。特定条件下的行为MySQL 的EXPLAIN输出、索引选择、innodb_lock_wait_timeout的触发都依赖表结构、数据分布和隔离级别不是跨数据库通用的结论。工程建议连接池大小、批大小、事务粒度这些数字本文给出的是推导方法与示意值不是可以直接照抄的生产参数。待确认假设任何“改成这样就会快 N 倍”的说法都需要在你自己的数据和流量下测量。本文没有引用外部资料节选因此涉及版本敏感结论时会明确写出适用范围并把需要你复核的问题单独列出而不是编造链接或声称已实测。三、核心机制数据库访问的性能由哪几段时间组成一个典型的 JDBC 查询从应用发出到拿到结果可以粗略拆成应用线程 |-- 1. 从连接池借连接可能排队 |-- 2. 发送 SQL 与参数网络往返 |-- 3. 数据库解析、生成执行计划 |-- 4. 执行扫描、过滤、排序、回表 |-- 5. 等待锁或等待 IO |-- 6. 返回结果集网络往返 |-- 7. 归还连接每一段都有不同的观测手段和不同的修复方式。把它们混在一起谈“慢”就无法判断该改哪里。下面用一张 ASCII 图表示分层与对应证据[接口变慢] | -- 应用侧线程等待多 -- 看连接池指标活跃/空闲/等待 | -- 单次调用往返多 -- 看 SQL 条数、批处理是否生效 | -- 单条 SQL 慢 -- 看 EXPLAIN、扫描行数、慢查询日志 | -- 偶发超时 -- 看锁等待、事务时长、隔离级别3.1 连接池它解决的是“复用”不是“加速”连接池connection pool解决的问题是建立数据库连接要经过网络握手和认证代价高不能每个请求都新建。池在启动时或按需创建若干连接请求来的时候借出用完归还。很多人误以为连接池越大吞吐越高。实际上数据库能同时高效执行的并发查询数是有限的连接数超过这个限度后多出来的连接只是在数据库里排队还会增加内存与上下文切换开销。HikariCP 的官方文档里有一个常被引用的近似估算思路连接数 ≈ CPU 核数 × 2 有效磁盘数这个公式来自对“IO 等待期间可以让出 CPU”的观察是一个起点不是定律。它假设查询以短事务为主、数据库是主要瓶颈。如果查询里有大量长事务、跨库调用或批量操作实际可用并发会显著低于这个估算。真正该做的不是背公式而是回答两个问题池里同时有多少连接在被使用有多少线程在等连接如果等待线程长期为 0扩大池不会提升吞吐如果等待线程持续很高先确认是不是慢 SQL 占住了连接而不是直接扩池。扩池会把压力转移到数据库可能让每个查询都变慢。3.2 批处理与预编译减少往返和重复解析批处理batch解决的问题是把多条结构相同的 SQL 合并成一次或少数几次网络往返减少客户端与服务端之间的往返次数。预编译prepared statement解决的问题是SQL 文本只解析一次后续只传参数同时避免字符串拼接带来的注入风险。两者常一起出现但不是一回事预编译减少解析开销并让执行计划可复用批处理减少往返次数。在 MySQL 上是否真正复用执行计划还取决于服务端预编译是否开启、驱动参数如何配置。默认情况下很多驱动在客户端做参数替换服务端看到的仍是完整 SQL。因此“用了 PreparedStatement 就一定复用计划”是不成立的需要看具体驱动配置。批处理也有边界批太大单次事务持有锁的时间变长回滚代价变大内存占用上升。批次大小需要在吞吐和锁竞争之间权衡常见起点是几十到几百条具体数值要测量。3.3 执行计划数据库打算怎么取数据执行计划execution plan是数据库优化器对一条 SQL 给出的取数方案包含访问方式、使用的索引、连接顺序、预估行数等。EXPLAIN是查看它的常用命令。读执行计划时最需要关注的是访问类型全表扫描还是索引扫描使用的键实际用了哪个索引扫描行数预估要读多少行过滤条件哪些条件在索引层完成哪些要回表后再过滤额外信息是否出现文件排序、临时表。一个常见误区是看到typeALL就断定必须加索引。全表扫描在小表上完全合理反过来看到走了索引也不代表高效如果索引选择性差、扫描行数接近全表性能可能比全表扫描还差因为多了回表成本。优化器是基于统计信息做选择的。统计信息过期、数据分布倾斜、条件写法导致索引失效都会让计划偏离预期。所以执行计划要结合真实数据分布看不能只看 SQL 文本。3.4 索引让扫描行数接近返回行数索引index解决的问题是不用逐行扫描整张表就能定位满足条件的行。一个理想的索引应该让数据库读取的行数接近最终返回的行数。判断一个索引是否有效不是看“有没有建”而是看查询条件是否能用上索引的前缀列排序和分组是否能借助索引顺序回表次数是否可控联合索引的列顺序是否匹配查询模式。联合索引多列索引的列顺序很关键。它遵循最左前缀原则查询条件必须从索引第一列开始连续匹配才能有效利用。把选择性低的列放在前面往往让后续列无法发挥过滤作用。索引不是越多越好。每个索引都会增加写入成本、占用空间并让优化器在更多候选计划中做选择。写入密集的表上索引数量需要克制。3.5 事务边界与锁等待把并发问题放大或收敛事务transaction解决的问题是一组操作要么全部生效要么全部不生效。事务边界就是这组操作的范围。事务边界开得过大会带来两个后果锁持有时间变长其他事务等待连接被占用时间变长连接池可用连接减少。锁等待lock wait指一个事务想修改或读取被其他事务锁住的行只能等待。等待超过阈值会超时或报错。在 InnoDB 中行锁的持有时间与事务提交时间直接相关所以“把事务写短”往往比“调大锁等待超时”更有效。需要区分行锁等待不同事务改同一行间隙锁与临键锁在可重复读隔离级别下范围条件可能锁住间隙导致插入互相阻塞元数据锁DDL 与长查询互相阻塞。这些机制在小流量下不容易暴露高并发时才显现。因此锁等待问题通常表现为“偶发超时”而不是稳定慢。四、贯穿示例一个订单服务的排查实验为了让上面的机制落地构造一个最小可复现实验。场景是订单表上有一个按用户和状态查询的接口以及一个批量写入的导入接口。说明以下代码是为讲解构造的示例不是生产代码数据库为本地 MySQL 8.0表结构与初始数据由脚本生成。预期结果基于机制推演实际数值需你在自己的环境测量。4.1 前置环境与依赖JDK 21Maven本地 MySQL 8.0库名perf_labSpring Boot 3.5 项目。pom.xml关键依赖dependenciesdependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-jdbc/artifactId/dependencydependencygroupIdcom.mysql/groupIdartifactIdmysql-connector-j/artifactIdscoperuntime/scope/dependency/dependenciesapplication.yml的连接池配置数值为示意spring:datasource:url:jdbc:mysql://127.0.0.1:3306/perf_lab?useSSLfalseserverTimezoneUTCusername:labpassword:labhikari:maximum-pool-size:10minimum-idle:2connection-timeout:3000pool-name:perf-lab-poollogging:level:com.zaxxer.hikari:DEBUG先把com.zaxxer.hikari日志级别调成 DEBUG是为了在实验里直接观察借还连接与等待。生产环境通常不需要长期开这个级别。4.2 建表与初始数据CREATETABLEorders(idBIGINTPRIMARYKEYAUTO_INCREMENT,user_idBIGINTNOTNULL,statusTINYINTNOTNULL,amountDECIMAL(12,2)NOTNULL,created_atDATETIMENOTNULL,remarkVARCHAR(200)NOTNULLDEFAULT)ENGINEInnoDB;-- 生成示意数据10 万行user_id 在 1..1000 之间status 只有 0/1/2-- 这里用存储过程或外部脚本填充具体语法按你的 MySQL 版本调整注意status只有三个取值选择性很低单独给它建索引意义不大。user_id有 1000 个取值相对分散是更合适的索引候选。4.3 第一个观察点没有合适索引时会发生什么查询语句SELECTid,user_id,status,amount,created_atFROMordersWHEREuser_id123ANDstatus1ORDERBYcreated_atDESCLIMIT20;在没有任何二级索引时执行EXPLAINSELECTid,user_id,status,amount,created_atFROMordersWHEREuser_id123ANDstatus1ORDERBYcreated_atDESCLIMIT20;预期可以看到访问类型为全表扫描扫描行数接近总行数。原因很直接数据库没有能按user_id定位的索引只能逐行读取再过滤。这里出现第一个反例如果只看“查询能返回结果”就认为没问题那么在数据量小时完全正常数据量上来后才暴露。性能问题常常是数据量放大的问题不是 SQL 语法的问题。4.4 建索引并观察计划变化CREATEINDEXidx_user_status_createdONorders(user_id,status,created_at);再次执行EXPLAIN预期访问类型变为索引范围扫描使用的键为idx_user_status_created扫描行数明显下降。为什么列顺序是user_id, status, created_atuser_id等值条件选择性较好放第一列status也是等值条件放在第二列进一步缩小范围created_at用于排序放在第三列让排序可以借助索引顺序。这个顺序服务于“等值 等值 排序”的查询模式。如果换成“范围 排序”顺序可能需要调整因为范围条件之后的列无法继续用于索引定位。需要强调这条索引只对匹配该查询模式的语句有效。如果实际查询是WHERE status 1 ORDER BY created_at它用不上这个索引的前缀计划可能仍然是全表扫描加排序。所以索引设计要从真实 SQL 集合出发而不是从单条 SQL 出发。4.5 第二个观察点批量写入的往返成本导入接口逐条插入// 示意代码逐条插入每次一条 SQL 往返publicvoidinsertOneByOne(JdbcTemplatejdbc,ListOrderRowrows){StringsqlINSERT INTO orders(user_id, status, amount, created_at, remark) VALUES (?,?,?,?,?);for(OrderRowrow:rows){jdbc.update(sql,row.userId(),row.status(),row.amount(),row.createdAt(),row.remark());}}这段代码每次update都是一次独立往返。假设每行插入耗时 1ms其中大部分是网络与解析开销1000 行就要约 1 秒。改成批处理// 示意代码使用 BatchPreparedStatementSetter 分批提交publicvoidinsertBatch(JdbcTemplatejdbc,ListOrderRowrows,intbatchSize){StringsqlINSERT INTO orders(user_id, status, amount, created_at, remark) VALUES (?,?,?,?,?);jdbc.batchUpdate(sql,newBatchPreparedStatementSetter(){OverridepublicvoidsetValues(PreparedStatementps,inti)throwsSQLException{OrderRowrowrows.get(i);ps.setLong(1,row.userId());ps.setInt(2,row.status());ps.setBigDecimal(3,row.amount());ps.setTimestamp(4,Timestamp.valueOf(row.createdAt()));ps.setString(5,row.remark());}OverridepublicintgetBatchSize(){returnMath.min(batchSize,rows.size());}});}这里需要说明输入、状态变化和边界输入rows是待插入数据batchSize是每批条数状态变化驱动把多条语句合并发送数据库逐条执行结果原因往返次数从 N 次降到约 N/batchSize 次边界批太大时单事务时间长、锁持有久、失败回滚成本高不同驱动对批处理的实现不同是否真正合并发送要看驱动配置与日志。在连接串上MySQL 驱动常用rewriteBatchedStatementstrue让多条 INSERT 合并成多值 INSERT这是版本相关的驱动行为需要在你的驱动版本下验证不能默认开启就有效。4.6 第三个观察点连接池等待把maximum-pool-size临时设为 2然后并发发起 20 个请求同时观察 HikariCP DEBUG 日志。预期会看到类似“等待连接”的告警接口耗时上升但数据库 CPU 不一定高。这说明瓶颈在应用侧排队而不是数据库计算。此时正确动作是先确认每条查询本身是否慢如果查询很慢占住连接应该优化 SQL 或缩短事务而不是扩池如果查询很快但请求量超过池容量再考虑扩池并同步评估数据库连接上限。这里可以做一个对照使用 Java 21 虚拟线程承载请求。虚拟线程能降低线程等待的成本但它不会让数据库多出连接也不会让慢 SQL 变快。虚拟线程解决的是线程资源问题不是数据库容量问题。把这两者混为一谈是常见的误判。4.7 第四个观察点事务与锁等待模拟两个事务更新同一行-- 会话 ABEGIN;UPDATEordersSETstatus2WHEREid1;-- 不提交-- 会话 BBEGIN;UPDATEordersSETstatus1WHEREid1;-- 预期阻塞直到 A 提交或回滚或超过 innodb_lock_wait_timeout预期观察会话 B 阻塞超过阈值后报锁等待超时。原因A 持有该行排他锁B 需要同一行的锁。这个实验的价值在于让你看到“事务边界”如何直接影响并发如果 A 在更新后还做了远程调用、文件写入等耗时操作才提交持锁时间就被拉长B 的等待时间随之增加。修复方向通常是缩短事务、把非数据库操作移出事务而不是单纯调大超时时间。可以通过performance_schema.data_locks和performance_schema.data_lock_waits查看当前锁与等待关系MySQL 8.0 提供。这些视图的具体列名和可用性依赖版本与配置需要在你的实例上确认。4.8 慢查询定位流程慢查询slow query指执行时间超过阈值的 SQL。定位流程建议按顺序打开慢查询日志设置合理的long_query_time找出出现频率高且单次耗时高的 SQL优先处理“高频 × 高耗时”对目标 SQL 执行EXPLAIN看访问方式与扫描行数对照表结构、索引与数据分布判断计划是否合理做最小改动加索引、改 SQL 写法、缩短事务再次测量对比改动前后的扫描行数、执行时间与连接池等待指标。第 2 步常被忽略一条偶尔慢的 SQL 和一条每秒执行上千次的稍慢 SQL后者对整体影响更大。所以排序要看总耗时而不只是单次耗时。五、执行与观察把实验跑成一个闭环下面是建议的执行顺序每一步都要有可观察的结果。注意以下命令与结果是实验设计不是本文已执行过的记录。5.1 启动与验证连接mvn-qspring-boot:run预期观察日志中出现 HikariCP 启动信息包含池名perf-lab-pool、最大连接数 10。验证方法调用一个简单查询接口确认能返回数据同时观察 DEBUG 日志里借出与归还连接的记录。5.2 对比有无索引步骤删除idx_user_status_created执行目标查询并记录耗时执行EXPLAIN记录访问类型与扫描行数重建索引重复查询与EXPLAIN。预期可观察结果重建索引后访问类型从全表扫描变为索引扫描扫描行数下降查询耗时下降。具体下降幅度取决于数据分布不能预设倍数。边界如果user_id 123对应的数据本身占全表很大比例优化器可能仍然选择全表扫描因为走索引再回表反而更贵。这不是索引失效而是优化器基于成本的判断。5.3 对比批处理步骤用insertOneByOne插入 1000 行记录耗时清空数据用insertBatch以batchSize100插入 1000 行记录耗时观察数据库侧语句计数或驱动日志。预期可观察结果批处理版本往返次数减少耗时下降。若驱动未开启合并发送下降幅度有限此时应检查驱动配置而不是断定批处理无效。5.4 对比连接池容量步骤设maximum-pool-size2并发 20 个请求记录接口耗时与等待日志设maximum-pool-size10重复保持 10 不变但把目标查询改回无索引版本重复。预期可观察结果小池 快查询耗时受排队影响上升大池 快查询排队减少耗时下降大池 慢查询连接被慢查询占住扩池收益有限甚至变差。这个对照说明池大小要与查询速度和并发量一起看单独调池没有意义。5.5 对比事务边界步骤写一个事务方法更新一行后Thread.sleep(500)再提交并发调用两个不同请求更新同一行观察第二个请求的等待时间把sleep移出事务重复。预期可观察结果第一种写法下第二个请求明显等待第二种写法下等待时间接近更新本身的时间。5.6 资源清理实验结束后删除实验表或清空数据恢复连接池参数关闭慢查询日志或恢复原阈值关闭应用进程。清理不是形式步骤。遗留的大表、长时间开启的 DEBUG 日志、未恢复的池参数都可能影响后续实验的判断。六、失败与反例哪些做法看起来对但会误导6.1 反例一看到慢就扩连接池扩池只在“等待连接是主要瓶颈”时有效。如果慢查询占住连接扩池会让更多并发查询同时压到数据库锁竞争和 IO 竞争加剧整体可能更慢。判断依据是连接池等待指标与单条 SQL 耗时不是接口总耗时。6.2 反例二加索引就一定走索引优化器按成本选择计划。条件写法、类型转换、函数包裹列、统计信息过期都可能让索引失效。例如WHERE DATE(created_at) 2024-01-01在列上套函数通常无法使用该列的普通索引。应改成范围条件让列保持“裸用”。6.3 反例三把批处理当成万能加速批处理减少往返但如果单条语句本身慢例如触发大量索引维护或锁等待合并发送不会解决根本问题。批太大还会拉长事务增加锁等待和回滚成本。6.4 反例四用EXPLAIN的预估行数当真实行数EXPLAIN给的是优化器的预估。统计信息不准时预估值可能与实际差很远。要判断真实扫描量可以结合EXPLAIN ANALYZEMySQL 8.0.18 起提供具体可用性看版本或执行时的状态计数。预估和实际要分开看。6.5 反例五把锁等待超时调大当作修复调大innodb_lock_wait_timeout只是让事务等更久不改变持锁时间。如果持锁方是因为事务里有耗时操作等待方只是被拖得更久。正确方向是缩短持锁事务、减少热点行更新、必要时调整更新顺序。6.6 反例六把虚拟线程当成数据库并发方案虚拟线程降低的是线程创建与阻塞等待的成本。数据库连接数、锁、IO 能力没有变化。在虚拟线程下如果仍然使用固定大小的连接池并发请求会在池上排队。把它当成“数据库能承受更多并发”是错误推断。七、适用条件与结论7.1 这套分层方法适用的条件瓶颈主要在数据库访问路径上而不是应用内计算你能拿到连接池指标、SQL 日志或慢查询日志你能在可控环境里做前后对比而不是只能改生产参数。如果不满足这些条件第一步应该是补观测而不是改配置。7.2 不适用的情形数据库本身资源饱和CPU、IO、内存此时先处理容量与慢查询而不是在应用侧调参问题来自网络或代理层数据库指标正常业务逻辑本身有 N1 查询单条 SQL 都很快但条数太多需要改的是访问模式。7.3 结论数据库访问性能优化不是“把池调大、把索引加上”的动作集合而是一条因果链先确认在哪一段耗时再改那一段再用同一组指标验证。连接池解决复用与排队不解决单条 SQL 慢批处理与预编译减少往返与解析不解决扫描行数索引让扫描行数接近返回行数但受查询模式与数据分布约束执行计划是优化器的判断需要结合真实数据看事务边界决定锁持有时间直接影响并发下的等待。把这五点分开观察、联合验证才能在一次改动后说清楚“为什么变快”而不是碰巧变快。7.4 需要你复核的问题你的 MySQL 版本下EXPLAIN输出字段与EXPLAIN ANALYZE可用性你的驱动版本是否默认开启服务端预编译或批量重写你的隔离级别下范围条件是否产生间隙锁你的连接池指标采集方式与采样频率。这些问题的答案依赖你的环境本文不替你做假设。 数据库访问性能分层优化：连接池、批处理、索引与慢查询的联合排查
数据库访问性能分层优化连接池、批处理、索引与慢查询的联合排查一、先把问题说成一个具体的事假设你负责一个订单查询与批量写入服务某天收到反馈订单详情接口 P99 从 80ms 涨到 900ms批量导入接口在高峰期偶尔超时监控上数据库 CPU 没有明显飙升应用侧线程却大量处于等待状态。很多人第一反应是“加连接池”“加索引”“加缓存”。这三个动作都可能有效也都可能完全无效因为它们是不同层次的修复手段。数据库访问性能问题往往不是一个点坏了而是几个环节叠在一起应用线程在等连接拿到连接后一条 SQL 被拆成很多次网络往返单条 SQL 的执行计划选错了扫描方式缺索引或索引不匹配导致扫描行数远大于返回行数事务开得太长把锁持有时间放大其他请求排队。如果不分层很容易出现这样的循环看到慢就调大连接池数据库连接数上去后锁竞争更严重于是更慢再看到慢就加索引索引加上去但 SQL 没走到它于是还是慢。最终改动了一堆参数却说不清哪个改动真正起了作用。这篇文章要完成的一件事是给出一套可复用的分层排查顺序让你在面对“数据库访问变慢”时能先定位在哪一层再做最小改动并用可观察的证据验证改动有效。二、环境与兼容范围本文的示例围绕以下版本展开其他版本的行为需要单独确认组件版本说明JDKJava 21使用虚拟线程做对照实验时需要 21Spring Boot3.5.x默认连接池为 HikariCP数据库MySQL 8.0 系列EXPLAIN 输出与 innodb 锁视图以 8.0 为参照构建Maven示例只列必要依赖需要明确区分几类事实官方保证HikariCP 的maximumPoolSize是池允许的最大连接数超时由connectionTimeout控制这是库文档层面的行为。特定条件下的行为MySQL 的EXPLAIN输出、索引选择、innodb_lock_wait_timeout的触发都依赖表结构、数据分布和隔离级别不是跨数据库通用的结论。工程建议连接池大小、批大小、事务粒度这些数字本文给出的是推导方法与示意值不是可以直接照抄的生产参数。待确认假设任何“改成这样就会快 N 倍”的说法都需要在你自己的数据和流量下测量。本文没有引用外部资料节选因此涉及版本敏感结论时会明确写出适用范围并把需要你复核的问题单独列出而不是编造链接或声称已实测。三、核心机制数据库访问的性能由哪几段时间组成一个典型的 JDBC 查询从应用发出到拿到结果可以粗略拆成应用线程 |-- 1. 从连接池借连接可能排队 |-- 2. 发送 SQL 与参数网络往返 |-- 3. 数据库解析、生成执行计划 |-- 4. 执行扫描、过滤、排序、回表 |-- 5. 等待锁或等待 IO |-- 6. 返回结果集网络往返 |-- 7. 归还连接每一段都有不同的观测手段和不同的修复方式。把它们混在一起谈“慢”就无法判断该改哪里。下面用一张 ASCII 图表示分层与对应证据[接口变慢] | -- 应用侧线程等待多 -- 看连接池指标活跃/空闲/等待 | -- 单次调用往返多 -- 看 SQL 条数、批处理是否生效 | -- 单条 SQL 慢 -- 看 EXPLAIN、扫描行数、慢查询日志 | -- 偶发超时 -- 看锁等待、事务时长、隔离级别3.1 连接池它解决的是“复用”不是“加速”连接池connection pool解决的问题是建立数据库连接要经过网络握手和认证代价高不能每个请求都新建。池在启动时或按需创建若干连接请求来的时候借出用完归还。很多人误以为连接池越大吞吐越高。实际上数据库能同时高效执行的并发查询数是有限的连接数超过这个限度后多出来的连接只是在数据库里排队还会增加内存与上下文切换开销。HikariCP 的官方文档里有一个常被引用的近似估算思路连接数 ≈ CPU 核数 × 2 有效磁盘数这个公式来自对“IO 等待期间可以让出 CPU”的观察是一个起点不是定律。它假设查询以短事务为主、数据库是主要瓶颈。如果查询里有大量长事务、跨库调用或批量操作实际可用并发会显著低于这个估算。真正该做的不是背公式而是回答两个问题池里同时有多少连接在被使用有多少线程在等连接如果等待线程长期为 0扩大池不会提升吞吐如果等待线程持续很高先确认是不是慢 SQL 占住了连接而不是直接扩池。扩池会把压力转移到数据库可能让每个查询都变慢。3.2 批处理与预编译减少往返和重复解析批处理batch解决的问题是把多条结构相同的 SQL 合并成一次或少数几次网络往返减少客户端与服务端之间的往返次数。预编译prepared statement解决的问题是SQL 文本只解析一次后续只传参数同时避免字符串拼接带来的注入风险。两者常一起出现但不是一回事预编译减少解析开销并让执行计划可复用批处理减少往返次数。在 MySQL 上是否真正复用执行计划还取决于服务端预编译是否开启、驱动参数如何配置。默认情况下很多驱动在客户端做参数替换服务端看到的仍是完整 SQL。因此“用了 PreparedStatement 就一定复用计划”是不成立的需要看具体驱动配置。批处理也有边界批太大单次事务持有锁的时间变长回滚代价变大内存占用上升。批次大小需要在吞吐和锁竞争之间权衡常见起点是几十到几百条具体数值要测量。3.3 执行计划数据库打算怎么取数据执行计划execution plan是数据库优化器对一条 SQL 给出的取数方案包含访问方式、使用的索引、连接顺序、预估行数等。EXPLAIN是查看它的常用命令。读执行计划时最需要关注的是访问类型全表扫描还是索引扫描使用的键实际用了哪个索引扫描行数预估要读多少行过滤条件哪些条件在索引层完成哪些要回表后再过滤额外信息是否出现文件排序、临时表。一个常见误区是看到typeALL就断定必须加索引。全表扫描在小表上完全合理反过来看到走了索引也不代表高效如果索引选择性差、扫描行数接近全表性能可能比全表扫描还差因为多了回表成本。优化器是基于统计信息做选择的。统计信息过期、数据分布倾斜、条件写法导致索引失效都会让计划偏离预期。所以执行计划要结合真实数据分布看不能只看 SQL 文本。3.4 索引让扫描行数接近返回行数索引index解决的问题是不用逐行扫描整张表就能定位满足条件的行。一个理想的索引应该让数据库读取的行数接近最终返回的行数。判断一个索引是否有效不是看“有没有建”而是看查询条件是否能用上索引的前缀列排序和分组是否能借助索引顺序回表次数是否可控联合索引的列顺序是否匹配查询模式。联合索引多列索引的列顺序很关键。它遵循最左前缀原则查询条件必须从索引第一列开始连续匹配才能有效利用。把选择性低的列放在前面往往让后续列无法发挥过滤作用。索引不是越多越好。每个索引都会增加写入成本、占用空间并让优化器在更多候选计划中做选择。写入密集的表上索引数量需要克制。3.5 事务边界与锁等待把并发问题放大或收敛事务transaction解决的问题是一组操作要么全部生效要么全部不生效。事务边界就是这组操作的范围。事务边界开得过大会带来两个后果锁持有时间变长其他事务等待连接被占用时间变长连接池可用连接减少。锁等待lock wait指一个事务想修改或读取被其他事务锁住的行只能等待。等待超过阈值会超时或报错。在 InnoDB 中行锁的持有时间与事务提交时间直接相关所以“把事务写短”往往比“调大锁等待超时”更有效。需要区分行锁等待不同事务改同一行间隙锁与临键锁在可重复读隔离级别下范围条件可能锁住间隙导致插入互相阻塞元数据锁DDL 与长查询互相阻塞。这些机制在小流量下不容易暴露高并发时才显现。因此锁等待问题通常表现为“偶发超时”而不是稳定慢。四、贯穿示例一个订单服务的排查实验为了让上面的机制落地构造一个最小可复现实验。场景是订单表上有一个按用户和状态查询的接口以及一个批量写入的导入接口。说明以下代码是为讲解构造的示例不是生产代码数据库为本地 MySQL 8.0表结构与初始数据由脚本生成。预期结果基于机制推演实际数值需你在自己的环境测量。4.1 前置环境与依赖JDK 21Maven本地 MySQL 8.0库名perf_labSpring Boot 3.5 项目。pom.xml关键依赖dependenciesdependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-jdbc/artifactId/dependencydependencygroupIdcom.mysql/groupIdartifactIdmysql-connector-j/artifactIdscoperuntime/scope/dependency/dependenciesapplication.yml的连接池配置数值为示意spring:datasource:url:jdbc:mysql://127.0.0.1:3306/perf_lab?useSSLfalseserverTimezoneUTCusername:labpassword:labhikari:maximum-pool-size:10minimum-idle:2connection-timeout:3000pool-name:perf-lab-poollogging:level:com.zaxxer.hikari:DEBUG先把com.zaxxer.hikari日志级别调成 DEBUG是为了在实验里直接观察借还连接与等待。生产环境通常不需要长期开这个级别。4.2 建表与初始数据CREATETABLEorders(idBIGINTPRIMARYKEYAUTO_INCREMENT,user_idBIGINTNOTNULL,statusTINYINTNOTNULL,amountDECIMAL(12,2)NOTNULL,created_atDATETIMENOTNULL,remarkVARCHAR(200)NOTNULLDEFAULT)ENGINEInnoDB;-- 生成示意数据10 万行user_id 在 1..1000 之间status 只有 0/1/2-- 这里用存储过程或外部脚本填充具体语法按你的 MySQL 版本调整注意status只有三个取值选择性很低单独给它建索引意义不大。user_id有 1000 个取值相对分散是更合适的索引候选。4.3 第一个观察点没有合适索引时会发生什么查询语句SELECTid,user_id,status,amount,created_atFROMordersWHEREuser_id123ANDstatus1ORDERBYcreated_atDESCLIMIT20;在没有任何二级索引时执行EXPLAINSELECTid,user_id,status,amount,created_atFROMordersWHEREuser_id123ANDstatus1ORDERBYcreated_atDESCLIMIT20;预期可以看到访问类型为全表扫描扫描行数接近总行数。原因很直接数据库没有能按user_id定位的索引只能逐行读取再过滤。这里出现第一个反例如果只看“查询能返回结果”就认为没问题那么在数据量小时完全正常数据量上来后才暴露。性能问题常常是数据量放大的问题不是 SQL 语法的问题。4.4 建索引并观察计划变化CREATEINDEXidx_user_status_createdONorders(user_id,status,created_at);再次执行EXPLAIN预期访问类型变为索引范围扫描使用的键为idx_user_status_created扫描行数明显下降。为什么列顺序是user_id, status, created_atuser_id等值条件选择性较好放第一列status也是等值条件放在第二列进一步缩小范围created_at用于排序放在第三列让排序可以借助索引顺序。这个顺序服务于“等值 等值 排序”的查询模式。如果换成“范围 排序”顺序可能需要调整因为范围条件之后的列无法继续用于索引定位。需要强调这条索引只对匹配该查询模式的语句有效。如果实际查询是WHERE status 1 ORDER BY created_at它用不上这个索引的前缀计划可能仍然是全表扫描加排序。所以索引设计要从真实 SQL 集合出发而不是从单条 SQL 出发。4.5 第二个观察点批量写入的往返成本导入接口逐条插入// 示意代码逐条插入每次一条 SQL 往返publicvoidinsertOneByOne(JdbcTemplatejdbc,ListOrderRowrows){StringsqlINSERT INTO orders(user_id, status, amount, created_at, remark) VALUES (?,?,?,?,?);for(OrderRowrow:rows){jdbc.update(sql,row.userId(),row.status(),row.amount(),row.createdAt(),row.remark());}}这段代码每次update都是一次独立往返。假设每行插入耗时 1ms其中大部分是网络与解析开销1000 行就要约 1 秒。改成批处理// 示意代码使用 BatchPreparedStatementSetter 分批提交publicvoidinsertBatch(JdbcTemplatejdbc,ListOrderRowrows,intbatchSize){StringsqlINSERT INTO orders(user_id, status, amount, created_at, remark) VALUES (?,?,?,?,?);jdbc.batchUpdate(sql,newBatchPreparedStatementSetter(){OverridepublicvoidsetValues(PreparedStatementps,inti)throwsSQLException{OrderRowrowrows.get(i);ps.setLong(1,row.userId());ps.setInt(2,row.status());ps.setBigDecimal(3,row.amount());ps.setTimestamp(4,Timestamp.valueOf(row.createdAt()));ps.setString(5,row.remark());}OverridepublicintgetBatchSize(){returnMath.min(batchSize,rows.size());}});}这里需要说明输入、状态变化和边界输入rows是待插入数据batchSize是每批条数状态变化驱动把多条语句合并发送数据库逐条执行结果原因往返次数从 N 次降到约 N/batchSize 次边界批太大时单事务时间长、锁持有久、失败回滚成本高不同驱动对批处理的实现不同是否真正合并发送要看驱动配置与日志。在连接串上MySQL 驱动常用rewriteBatchedStatementstrue让多条 INSERT 合并成多值 INSERT这是版本相关的驱动行为需要在你的驱动版本下验证不能默认开启就有效。4.6 第三个观察点连接池等待把maximum-pool-size临时设为 2然后并发发起 20 个请求同时观察 HikariCP DEBUG 日志。预期会看到类似“等待连接”的告警接口耗时上升但数据库 CPU 不一定高。这说明瓶颈在应用侧排队而不是数据库计算。此时正确动作是先确认每条查询本身是否慢如果查询很慢占住连接应该优化 SQL 或缩短事务而不是扩池如果查询很快但请求量超过池容量再考虑扩池并同步评估数据库连接上限。这里可以做一个对照使用 Java 21 虚拟线程承载请求。虚拟线程能降低线程等待的成本但它不会让数据库多出连接也不会让慢 SQL 变快。虚拟线程解决的是线程资源问题不是数据库容量问题。把这两者混为一谈是常见的误判。4.7 第四个观察点事务与锁等待模拟两个事务更新同一行-- 会话 ABEGIN;UPDATEordersSETstatus2WHEREid1;-- 不提交-- 会话 BBEGIN;UPDATEordersSETstatus1WHEREid1;-- 预期阻塞直到 A 提交或回滚或超过 innodb_lock_wait_timeout预期观察会话 B 阻塞超过阈值后报锁等待超时。原因A 持有该行排他锁B 需要同一行的锁。这个实验的价值在于让你看到“事务边界”如何直接影响并发如果 A 在更新后还做了远程调用、文件写入等耗时操作才提交持锁时间就被拉长B 的等待时间随之增加。修复方向通常是缩短事务、把非数据库操作移出事务而不是单纯调大超时时间。可以通过performance_schema.data_locks和performance_schema.data_lock_waits查看当前锁与等待关系MySQL 8.0 提供。这些视图的具体列名和可用性依赖版本与配置需要在你的实例上确认。4.8 慢查询定位流程慢查询slow query指执行时间超过阈值的 SQL。定位流程建议按顺序打开慢查询日志设置合理的long_query_time找出出现频率高且单次耗时高的 SQL优先处理“高频 × 高耗时”对目标 SQL 执行EXPLAIN看访问方式与扫描行数对照表结构、索引与数据分布判断计划是否合理做最小改动加索引、改 SQL 写法、缩短事务再次测量对比改动前后的扫描行数、执行时间与连接池等待指标。第 2 步常被忽略一条偶尔慢的 SQL 和一条每秒执行上千次的稍慢 SQL后者对整体影响更大。所以排序要看总耗时而不只是单次耗时。五、执行与观察把实验跑成一个闭环下面是建议的执行顺序每一步都要有可观察的结果。注意以下命令与结果是实验设计不是本文已执行过的记录。5.1 启动与验证连接mvn-qspring-boot:run预期观察日志中出现 HikariCP 启动信息包含池名perf-lab-pool、最大连接数 10。验证方法调用一个简单查询接口确认能返回数据同时观察 DEBUG 日志里借出与归还连接的记录。5.2 对比有无索引步骤删除idx_user_status_created执行目标查询并记录耗时执行EXPLAIN记录访问类型与扫描行数重建索引重复查询与EXPLAIN。预期可观察结果重建索引后访问类型从全表扫描变为索引扫描扫描行数下降查询耗时下降。具体下降幅度取决于数据分布不能预设倍数。边界如果user_id 123对应的数据本身占全表很大比例优化器可能仍然选择全表扫描因为走索引再回表反而更贵。这不是索引失效而是优化器基于成本的判断。5.3 对比批处理步骤用insertOneByOne插入 1000 行记录耗时清空数据用insertBatch以batchSize100插入 1000 行记录耗时观察数据库侧语句计数或驱动日志。预期可观察结果批处理版本往返次数减少耗时下降。若驱动未开启合并发送下降幅度有限此时应检查驱动配置而不是断定批处理无效。5.4 对比连接池容量步骤设maximum-pool-size2并发 20 个请求记录接口耗时与等待日志设maximum-pool-size10重复保持 10 不变但把目标查询改回无索引版本重复。预期可观察结果小池 快查询耗时受排队影响上升大池 快查询排队减少耗时下降大池 慢查询连接被慢查询占住扩池收益有限甚至变差。这个对照说明池大小要与查询速度和并发量一起看单独调池没有意义。5.5 对比事务边界步骤写一个事务方法更新一行后Thread.sleep(500)再提交并发调用两个不同请求更新同一行观察第二个请求的等待时间把sleep移出事务重复。预期可观察结果第一种写法下第二个请求明显等待第二种写法下等待时间接近更新本身的时间。5.6 资源清理实验结束后删除实验表或清空数据恢复连接池参数关闭慢查询日志或恢复原阈值关闭应用进程。清理不是形式步骤。遗留的大表、长时间开启的 DEBUG 日志、未恢复的池参数都可能影响后续实验的判断。六、失败与反例哪些做法看起来对但会误导6.1 反例一看到慢就扩连接池扩池只在“等待连接是主要瓶颈”时有效。如果慢查询占住连接扩池会让更多并发查询同时压到数据库锁竞争和 IO 竞争加剧整体可能更慢。判断依据是连接池等待指标与单条 SQL 耗时不是接口总耗时。6.2 反例二加索引就一定走索引优化器按成本选择计划。条件写法、类型转换、函数包裹列、统计信息过期都可能让索引失效。例如WHERE DATE(created_at) 2024-01-01在列上套函数通常无法使用该列的普通索引。应改成范围条件让列保持“裸用”。6.3 反例三把批处理当成万能加速批处理减少往返但如果单条语句本身慢例如触发大量索引维护或锁等待合并发送不会解决根本问题。批太大还会拉长事务增加锁等待和回滚成本。6.4 反例四用EXPLAIN的预估行数当真实行数EXPLAIN给的是优化器的预估。统计信息不准时预估值可能与实际差很远。要判断真实扫描量可以结合EXPLAIN ANALYZEMySQL 8.0.18 起提供具体可用性看版本或执行时的状态计数。预估和实际要分开看。6.5 反例五把锁等待超时调大当作修复调大innodb_lock_wait_timeout只是让事务等更久不改变持锁时间。如果持锁方是因为事务里有耗时操作等待方只是被拖得更久。正确方向是缩短持锁事务、减少热点行更新、必要时调整更新顺序。6.6 反例六把虚拟线程当成数据库并发方案虚拟线程降低的是线程创建与阻塞等待的成本。数据库连接数、锁、IO 能力没有变化。在虚拟线程下如果仍然使用固定大小的连接池并发请求会在池上排队。把它当成“数据库能承受更多并发”是错误推断。七、适用条件与结论7.1 这套分层方法适用的条件瓶颈主要在数据库访问路径上而不是应用内计算你能拿到连接池指标、SQL 日志或慢查询日志你能在可控环境里做前后对比而不是只能改生产参数。如果不满足这些条件第一步应该是补观测而不是改配置。7.2 不适用的情形数据库本身资源饱和CPU、IO、内存此时先处理容量与慢查询而不是在应用侧调参问题来自网络或代理层数据库指标正常业务逻辑本身有 N1 查询单条 SQL 都很快但条数太多需要改的是访问模式。7.3 结论数据库访问性能优化不是“把池调大、把索引加上”的动作集合而是一条因果链先确认在哪一段耗时再改那一段再用同一组指标验证。连接池解决复用与排队不解决单条 SQL 慢批处理与预编译减少往返与解析不解决扫描行数索引让扫描行数接近返回行数但受查询模式与数据分布约束执行计划是优化器的判断需要结合真实数据看事务边界决定锁持有时间直接影响并发下的等待。把这五点分开观察、联合验证才能在一次改动后说清楚“为什么变快”而不是碰巧变快。7.4 需要你复核的问题你的 MySQL 版本下EXPLAIN输出字段与EXPLAIN ANALYZE可用性你的驱动版本是否默认开启服务端预编译或批量重写你的隔离级别下范围条件是否产生间隙锁你的连接池指标采集方式与采样频率。这些问题的答案依赖你的环境本文不替你做假设。