数据库性能优化实战：YashanDB五大调优思路，从2.3秒到30毫秒

一次生产环境压测业务那边报了个痛点某个核心查询接口在并发冲到 200 的时候平均响应时间从 80 毫秒一路涨到了 2.3 秒。我接手排查执行计划一拉出来问题一目了然——一张 2000 万行的流水表条件列上没走索引全表扫描把 I/O 直接打满。改动其实很小补一个索引、把 SQL 里的隐式类型转换去掉再顺手调了一下数据库的缓冲区缓存压测数据就从 2.3 秒掉回了 30 毫秒以内。这种事在 YashanDB 的日常运维里太常见了。很多人一听到“性能调优”就觉得是高深莫测的玄学动不动就归咎于数据库不行。但以我实际调优的经验来看绝大多数性能问题都出在一些很朴素的环节上I/O 规划不合理、内存参数没有按业务特征调、SQL 写法有问题、索引设计拍脑袋、并发控制没做好。今天这篇文章我就把这几年围绕 YashanDB 做性能优化过程中最常用、见效最快的 5 条思路整理出来结合我踩过的坑和实测数据希望能帮你少走几趟弯路。1. 存储与 I/O 优化先把地基夯实很多性能优化的文章一上来就讲 SQL、讲索引但我要反着来——先从存储和 I/O 说起。原因很简单数据库所有数据最终都要落到磁盘上如果 I/O 层是瓶颈上层做再多优化都是隔靴搔痒。1.1 数据文件与日志文件必须物理分离我在不少项目里见过一种配置数据文件、重做日志、归档日志全塞在同一块盘上甚至和操作系统共用一块盘。这种部署方式在低压力下看不出问题一旦业务进入高峰期磁盘队列深度直接飙升整个数据库的写入延迟瞬间恶化。YashanDB 的日志写入是顺序 I/O特点是频繁、单次数据量小数据文件的写入则偏随机 I/O特点是单次数据量大、地址分散。两者混在一起磁头在顺序写和随机写之间反复切换磁盘利用率再高也白搭。我的建议是至少分成三块独立的物理存储数据文件单独放一块盘建议 SSD 或 NVMe在线重做日志单独放一块盘这块盘对延迟最敏感归档日志和备份文件放一块盘性能要求可以适当放宽如果是云环境就需要根据实例规格选择对应的云盘类型并且注意 IOPS 和吞吐量的上限。我曾经帮一个客户做压测他们用的是某云的通用型 SSD数据文件、日志、系统盘共用一个云盘。我把日志文件迁移到单独的 ESSD 之后同一套压测脚本数据库每秒事务数提升接近 35%。这个动作本身不花一分钱软件成本只是重新规划了存储布局。1.2 块大小与预分配容易被忽略的两个小参数YashanDB 在创建表空间时可以指定数据块大小。如果业务以 OLTP 为主行数据普遍偏小默认的块大小问题不大但如果是分析类业务单条记录动辄几百字节甚至几 KB适当调大块大小能减少单次 I/O 跨块的数量降低 I/O 次数。具体设置成多少需要根据你的实际数据特征来测试不要盲目照搬别人的值。另一个值得说的是数据文件预分配。我见过不少新建的表空间数据文件大小设置为“自动扩展每次增量 100MB”。这个配置在业务初期没有问题但等数据库跑了一段时间文件频繁扩展会产生大量磁盘碎片更重要的是扩展过程中偶尔会出现短暂的 I/O 停顿。我的习惯是建表空间的时候直接按预估容量一次性分配比如预测半年内会用到 2TB就预先分配 2TB。磁盘空间充沛的情况下这种做法能显著减少运行期的 I/O 抖动。1.3 I/O 压测别等线上出问题才想起来存储到底能扛多少压力不是看厂商给的参数要自己动手测。我在新环境上线前都会先跑一轮 fio 测试重点看两个指标随机读的 IOPS直接影响索引扫描和数据文件读取能力顺序写的带宽直接影响重做日志写入能力进而限制事务提交速率实测的时候用一个典型的 4KB 随机读测试如果 IOPS 连几千都上不去这个存储跑核心 OLTP 库基本没戏。测出来的数据心里有数后续做容量规划、判断瓶颈在哪一层时就有一份可靠的基线数据可以参考。2. 内存参数调优让热数据尽量留在内存里存储的问题解决之后第二道关口是内存。数据库的 buffer cache 相当于给磁盘数据做了一层热缓存——内存命中了就不用去碰磁盘。这层缓存的命中率直接决定了大部分读请求是微秒级响应还是毫秒级响应。2.1 缓冲区命中率的判断方法YashanDB 提供了一些动态性能视图可以查看逻辑读和物理读的统计信息。简单说逻辑读是数据库从内存缓冲区直接拿到数据块的次数物理读是必须从磁盘读取数据块的次数。命中率 (逻辑读 - 物理读) / 逻辑读这个值低于 95% 的时候你就要警惕了。低于 90% 则基本可以判定 buffer cache 偏小或者 SQL 访问的数据量超出预期。有一次我接手一个性能问题现象是业务高峰期磁盘读负载很高但 CPU 使用率只有 20% 左右。我一查命中率只有 87%。当时数据库的 buffer cache 参数给的是 4GB而业务每天活跃数据大概有 15GB明显是小马拉大车。我调整参数把缓存扩到 12GB重启实例后命中率回到了 98% 以上同样的业务流量下磁盘读压力降了一大半查询响应时间也平稳了。2.2 排序区与临时段别让小查询拖垮大内存除了 buffer cache排序内存也是一个容易被低估的配置点。OLAP 类查询经常涉及 order by、group by、distinct 这些排序操作。如果排序内存给得不够数据库会把这些中间结果溢写到临时表空间性能下降不是一点半点。我遇到过一条月报 SQL单次执行时间 42 秒。排查后发现它的排序操作全部落盘了临时表空间的读写量非常大。我把排序内存从默认值调高到 256MB 之后这条 SQL 的执行时间直接缩短到了 6 秒左右。这个调整也不是越大越好。排序内存分配得太大高并发下总内存可能不够用反而触发内存压力。建议的做法是先观察正常业务时段内 v$sort_usage 这类视图里排序空间的使用峰值再按峰值乘以预计并发数来估算留 20% 的余量。2.3 参数修改之后的验证方法内存参数修改不像 SQL 优化那样每次都有明确的前后对比。我的习惯是每次只调整一个参数然后观察至少一个完整的业务高峰周期。一次改多个参数的做法最忌讳——出了问题你根本不知道是哪一步引起的。改完后重点对比三个指标缓冲命中率、磁盘读次数、Top SQL 的平均响应时间。只有这三个指标同时变好或者至少两个指标明显变好且第三个不恶化才算一次成功的调整。3. SQL 与执行计划优化一行写法天壤之别说实话我在一线处理过的性能问题里超过一半最后定位到的是 SQL 写法或执行计划走偏。数据库再强也得靠 SQL 去表达意图。SQL 写得烂优化器再怎么智能也救不回来。3.1 先学会看执行计划YashanDB 兼容 Oracle 的使用习惯查看执行计划的常用方法是 explain plan。我平时排查慢 SQL都是先把目标 SQL 的执行计划拉出来重点看三件事有没有全表扫描特别是大表上的全表扫描有没有不合理的嵌套循环连接小表驱动大表才合理反了就会产生天文数字的访问次数操作顺序是否合理一个真实的例子有个客户的核心查询关联了 5 张表其中一张表 5000 万行。执行计划显示优化器选择了一张 200 万行的表作为驱动表再去嵌套循环访问那张 5000 万行的表单次查询耗时就到了 30 多秒。我通过统计信息刷新让优化器重新评估了各表的行数分布执行计划改成了哈希连接查询时间降到了 200 毫秒。3.2 高频 SQL 的典型坑有几个非常常见的 SQL 写法问题我在 YashanDB 环境里反复遇到隐式类型转换。条件列是 VARCHAR2 类型但传入的参数是数字或者反过来。这种情况下索引大概率失效因为数据库需要对每一行做一次转换才能比较。解决方式就是统一类型或者加上显式转换函数。深分页查询。比如 limit 1000000, 20 这种写法数据库要先把前 100 万行扫出来再丢弃。解决思路是改成基于上次查询的最大值来做条件过滤在有序索引的辅助下效率能提升几个数量级。select * 滥用。如果查询只需要三个字段就别把全表所有字段拉出来。宽度大的表做 select *不仅增加网络传输还会让执行计划更容易走向全表扫描。查询条件中的函数包裹。在索引列上套函数例如 where to_char(create_time, yyyy-mm-dd) 2024-06-01这会让普通索引失效。正确的写法是直接按原始列做范围查询。3.3 统计信息是优化器的眼睛如果 SQL 写法没问题但执行计划还是走偏那就要检查统计信息了。没有准确的统计信息优化器就是闭着眼睛选路。YashanDB 提供类似 DBMS_STATS 的包来做统计信息收集。我上线新库或者批量导入大量数据之后第一件事就是重新收集相关表的统计信息然后立刻抽查几条核心 SQL 的执行计划有没有变化。这个动作排除了一个很大的变量后面排查问题就安心很多。另外绑定变量的使用值得多说一句。OLTP 场景下如果 SQL 文本每次都不同硬解析的 CPU 开销会很高。使用绑定变量让相同模式的 SQL 走软解析对高并发的短事务非常友好。4. 索引设计不是越多越好是越准越好索引是性能优化里最直接、最立竿见影的手段。但索引也是一把双刃剑——加索引让查询变快但写入时要维护索引会让 DML 变慢索引太多还浪费存储。这里面讲究的是平衡。4.1 组合索引的列顺序是门学问单列索引的设计相对简单真正考验功力的地方是组合索引。组合索引的列顺序如果不合理索引的效率会大幅度缩水。核心原则是等值查询的列放前面范围查询的列放后面。举个例子业务上有两个查询条件user_id等值和 create_time范围。设计组合索引时应该建 (user_id, create_time)而不是反过来。前者可以将范围条件压缩在一个很小的索引区间内后者则需要扫描一个很大的范围再过滤。条件索引和函数索引也是 YashanDB 里能救场的特性。有些字段大量值是空值只有少量非空值需要查询此时用条件索引能显著缩小索引体积。类似地如果业务场景必须对列做函数运算函数索引可以在不改变 SQL 写法的情况下解决普通索引失效的问题。4.2 索引失效的常见原因自查我在排查慢 SQL 时碰到过太多“明明有索引但就是不走”的情况。常见的几个原因条件列上用了函数或计算比如 where col 1 100条件列上做了隐式类型转换组合索引的前导列没出现在查询条件里优化器判断全表扫描比走索引更便宜你以为是失效其实是行数和数据分布导致的合理选择最后一条需要单独说说。不是所有不走索引都是坏事。如果一张表只有几百行数据走全表扫描比走索引更快优化器的选择没有问题。判断标准不是“有没有用到索引”而是“代价是不是最小”。4.3 清理冗余索引的实操方法索引不是建了就能一劳永逸。业务在演进曾经的常用索引可能早就没人用了。长期不用的索引每一条都在拖累写入性能还占着磁盘空间。我的做法是开启索引监控功能设置一个观察周期一般是半个月到一个月覆盖业务的完整周期。到期后检查监控结果把从未被使用过的索引先标记为不可用再观察一段时间确认没有业务报错最后再物理删除。这套流程比直接删索引安全得多出了问题也能快速恢复。5. 事务并发与锁等待流量大了以后的最大瓶颈很多系统的性能问题不是单条查询慢而是并发一上来整个数据库的吞吐量就断崖式下跌。这时候问题大概率出在锁等待、事务冲突和资源争用上。5.1 热点行更新引发的并发地狱YashanDB 的行级锁设计本身没什么问题但行级锁挡不住业务设计的缺陷。最典型的就是“热点账户”问题——用户余额放在一张表的一个账户里所有充值、扣费操作都更新同一行。即使数据库再快同一行上的更新只能串行执行并发一高等待队列就排起来了。我曾经处理过一个积分系统的案例某个平台的积分发放集中在每天 0 点进行大量用户在几十秒内同时更新同一批账户行数据库的锁等待事件数量瞬间暴涨。当时的解决方案是给热点账户做拆分——把一个大账户的数据拆成 100 个小账户每次更新时随机映射到其中一个子账户。更新冲突的概率立刻下降了 99%吞吐量上去了账务的一致性通过后续汇总计算来保证。5.2 长事务与短事务的拉扯事务的粒度同样影响并发。一个事务长时间不提交它持有的锁就会挡住其他事务。我见过很多业务代码里一个事务里既查了报表数据又做了十几轮循环更新还要调用一个外部接口等 3 秒返回——这种事务不慢才怪。最理想的状态是“短事务”——把事务控制在真正需要原子写操作的范围内查询和外部调用尽量放在事务外。这个设计原则在哪个数据库上都适用。YashanDB 默认的隔离级别和 MVCC 机制在读写并发上有比较大的空间但前提是你别把业务逻辑的锅甩给数据库。排查锁等待问题时我一般先看 v$lock 或相应的锁视图识别出阻塞链的源头再去反查源头事务在执行什么 SQL。绝大多数情况下找到的都是一条不该出现在事务里的长查询或外部调用。5.3 你会发现 CPU 居高不下的另一个原因并发高的时候CPU 飙高不一定全在算数据。频繁的 SQL 解析、锁等待重试、上下文切换都会大量消耗 CPU。我遇到过一种情况并发从 100 涨到 300 时数据库 CPU 使用率直接逼近 100%但真正执行 SQL 的占比并不高大量消耗在锁等待和重试上。解决思路有两个方向一个是上一条说的减少锁冲突另一个是提高连接池复用的效率避免大量短连接反复建立和销毁会话。把连接池最小空闲连接数设置到合理范围后会话创建开销明显下降CPU 占用率也回落了。5.4 一个被低估的并发参数redo 日志组大小在线日志大小直接影响日志切换频率。日志太小切换就频繁每次切换都会产生短暂的 checkpoint 停顿。特别是在高写入并发场景下过小的 redo 日志组会让数据库每隔几分钟就停顿一下业务表现为周期性的响应时间尖刺。我的建议是适当调大 redo 日志文件大小让日志切换频率维持在 15-30 分钟一次左右不要频繁切换。这个调优动作看起来很小但对写入密集型的业务效果非常明显——你可以想象成很多人排队走一扇小门门宽一点人流自然就顺畅了。6. 常见问题速查与定位工具清单上面五章说的是思路最后整理一份我在 YashanDB 性能排查中反复用到的速查表。症状最可能的原因优先排查方向CPU 高但 SQL 不快硬解析频繁或锁等待严重检查绑定变量使用情况、查看锁等待事件磁盘 I/O 繁忙内存很空buffer cache 命中率低检查命中率、调整缓冲区参数单条 SQL 偶发超时redo 日志切换或 checkpoint查看日志切换频率、调整日志组大小并发一高就掉吞吐热点行更新或长事务查看阻塞链、优化事务拆分查询时快时慢执行计划不稳定检查统计信息时效、固定执行计划响应时间尖刺数据文件扩展或存储争用检查自动扩展配置、I/O 独立部署排查问题时我习惯的路径是先看系统层CPU、内存、磁盘 I/O 三张图再看数据库层慢查询日志、锁等待、命中率最后才落到 SQL 层执行计划、索引使用情况。从外层往内层一层层剥比上来就抓一条 SQL 分析要高效得多。YashanDB 的运维体系里还提供了一些动态性能视图和监控工具日常巡检时花十几分钟看一下 Top SQL 和等待事件很多隐患都能在业务受影响之前提前发现。毕竟等用户投诉了再去查压力是完全不一样的。7. 关于这些优化思路我最后想补充几句上面聊的五种思路都是我在 YashanDB 实际运维和优化过程中反复验证过的方向。排个优先级的话我个人的经验是先确认存储 I/O 没有明显短板再看内存和缓冲配置然后投入精力去梳理 SQL 和执行计划索引设计放在持续迭代的过程里慢慢打磨最后在业务增长到一定规模后重点解决事务并发和锁竞争。优化不是一次性的事情。业务在变数据量在变访问模式也在变。这次压测没问题不代表半年后还能保持同样的性能水位。我习惯每次大版本迭代或数据量翻倍之后重新跑一遍上面的检查清单重新评估一下配置是否还需要调整。定期做性能体检比等问题爆发再补救要省心得多。说到底数据库性能优化的大部分工作不是什么高不可攀的黑科技就是把每个基础的环节都做到位。I/O 规划合理、内存给足、SQL 写规范、索引设计有依据、并发控制别失控——这些做到了性能基本不会差到哪里去。希望这篇文章能给你一些可以落地的参考。