1. 从一次慢查询排查说起先说我自己的经历。有段时间我在某项目里做数据清洗每天要处理几千万行的日志表最初用的是几个零散的SQL脚本跑一次全量清洗大概要四十分钟。后来业务侧要求把每天的调度窗口压缩到十五分钟以内否则凌晨的计算任务会跟白天的报表查询打架。被逼着优化了一轮核心手段其实特别朴素批量提交、并行度控制、索引策略再加上一条改写后的MERGE语句。最终清洗时长从四十分钟压到了九分钟效果立竿见影。这里面的工具就是YashanDB。可能有朋友还不熟悉它简单说这是一款国产的分布式关系型数据库兼容Oracle的不少语法习惯同时也在往MySQL生态上靠。它最打动我的点是你不需要为了性能去改业务逻辑大多数时候只要调整SQL写法、参数配置、提交策略就能拿到很明显的收益。这篇文章我不会讲太多理论重点是把我在项目里实际用过、验证过有效的5个技巧拆开揉碎每一步都告诉你当时是怎么想的、踩过什么坑、最后怎么调的。无论你是刚接触YashanDB还是已经在生产环境里跑了一段时间这几个技巧应该都能直接抄作业。2. 批量提交技巧别让事务成为隐形的性能杀手2.1 单行提交为什么慢很多从Oracle或者MySQL转过来的同学写数据入库时习惯用一个循环每处理一条就执行一次INSERT然后马上COMMIT。在小数据量下这么写没毛病但一旦单次任务要写入几百万行这个写法的代价就非常明显。问题出在事务提交本身。每次COMMIT都会触发一次日志落盘、锁释放、事务状态更新这些操作的CPU和IO开销是固定的。你写一万条数据就要付一万次固定开销这个开销跟数据本身关系不大纯粹是“次数税”。我见过一个极端例子某同学用循环单条插入的方式导入两百万行数据跑了将近两个小时后来我把循环去掉改成一次性攒够五千条再提交同样的数据不到三分钟就完事了。差距就是这么夸张。在YashanDB里事务提交的机制跟主流数据库类似每次提交都需要把重做日志刷到磁盘确保事务的持久性。所以减少提交次数本质上就是减少磁盘同步的次数。磁盘同步是毫秒级的操作单看一次不觉得慢乘上一万次、十万次就成了灾难。2.2 合理的批量尺寸怎么定那是不是一次攒的越多越好也不是。事务太大会带来两个新问题一是事务长时间持有锁其他会话的读写会被阻塞二是如果中途失败回滚回滚的代价也会很大因为undo信息同样要管理。我常用的经验值是每批次两千到一万行之间具体要看单行大小。行的平均长度越大批次就应该越小。举个例子如果一行只有几十个字节比如纯ID和时间戳那一万行一次提交是没问题的如果一行里还有大字段比如JSON、TEXT甚至BLOB那两千行左右就要提交一次避免单事务占用内存过多。实际操作时我会先按五千行跑一遍观察监控面板上的事务耗时和锁等待。如果锁等待明显增加就往小调如果IO还比较空闲就往大调。这个调参的过程不难但一定要有意识去做不要想当然选一个固定值就再也不管。2.3 实操示例用数组绑定代替单行循环YashanDB的JDBC驱动支持标准的批量提交接口。以前我写数据是这样for (Record r : records) { PreparedStatement ps conn.prepareStatement(INSERT INTO t VALUES (?, ?)); ps.setLong(1, r.getId()); ps.setString(2, r.getName()); ps.executeUpdate(); conn.commit(); }这个写法本身没什么语法错误但性能上非常吃亏。后来改成conn.setAutoCommit(false); PreparedStatement ps conn.prepareStatement(INSERT INTO t VALUES (?, ?)); int count 0; for (Record r : records) { ps.setLong(1, r.getId()); ps.setString(2, r.getName()); ps.addBatch(); count; if (count % 5000 0) { ps.executeBatch(); conn.commit(); } } ps.executeBatch(); conn.commit();关键改动是两个一是把自动提交关掉自己控制提交节奏二是用addBatch把多条INSERT攒进同一个批量操作一次网络往返发给数据库。这样既减少了提交次数也减少了网络交互次数两方面的开销都降下来了。很多新手容易忽略一个细节批量提交之后PreparedStatement里的参数并不会自动清空所以下一次循环前必须重新set值否则会出现数据重复或脏数据。我第一次写批量提交时就在这里栽过跟头整批数据错位排查了好久才发现是参数没刷新。3. 并行度与负载控制让压满的CPU喘口气3.1 并行不是万能药一提到提高数据处理效率很多人第一反应就是“上并行”。在YashanDB里确实有并行查询和并行DML的能力但并行是不是开得越大越好答案是否定的。我做过一个测试一张千万行的表做一次全表更新。并行度设为4时耗时大约40秒并行度调到8耗时不降反升来到55秒调到16直接涨到80秒。原因并不复杂并行度上去了每个并行服务进程都要分配内存、上下文切换开销增加同时磁盘IO带宽是固定的多路并行争抢同一块磁盘反而互相拖慢。所以并行度要用但必须结合机器资源来定。我个人的经验是如果数据库服务器是16核、64GB内存的配置并行度控制在4到6之间比较稳妥如果是32核以上的机器可以试探性给到8但一定要观察CPU使用率。如果发现CPU已经接近100%而且磁盘等待在上升那说明并行度给大了应该往回压。3.2 YashanDB里怎么控制并行YashanDB支持通过Hint来控制语句级别的并行也可以在会话级别设置并行参数。我用得比较多的是后者因为一条清洗任务通常包含多条SQL逐个加Hint太繁琐。会话级别设置并行度的方式大致如下ALTER SESSION SET parallel_degree_policy MANUAL; ALTER SESSION SET parallel_degree 4;这样设置之后当前会话里的查询和DML操作只要优化器认为可以并行就会尽量按4路并行来执行。不过要注意并行DML是有前提条件的并不是任何一条UPDATE都能自动并行。如果目标表上有未提交事务、或者表结构比较复杂如包含某些特殊索引或触发器优化器可能会放弃并行执行计划这时候就需要手动加Hint来干预。我不止一次遇到“明明设置了并行度执行计划却显示串行”的情况排查到最后都是因为表上有触发器。3.3 结合批量提交使用的实测效果在我的日志清洗场景里我会先把批量提交做好再叠加并行。具体做法是把大表按ID区间拆成四个分片每个分片单独起一个会话每个会话里再按五千行一个批次提交。这样既享受了并行带来的吞吐提升又不会因为单个事务过大而拖垮系统。实测下来单会话批量提交的写入速度大约是每秒7万行开了4个会话并行处理后总速度并没有线性变成每秒28万行而是稳定在每秒18万行左右。这个数字说明一个问题并行确实有用但系统的瓶颈逐渐转移到了别的环节比如磁盘IO和网络带宽。所以并行不是银弹它是在其他瓶颈都被优化掉之后才能发挥最大作用的加速手段。4. 索引策略优化为大数据量查询装上导航4.1 索引不是越多越好很多刚接触数据库优化的同学一遇到查询慢第一反应就是“给查询涉及的字段都建上索引”。这个思路有道理但实践中很容易失控。索引本质上是额外的存储结构和额外的写入开销。你每插入一行数据除了写表还要同步更新所有相关索引索引多了数据写入速度会被明显拖累。我在一个清洗任务里就有过教训。目标表本来有三个索引写入速度还行。后来为了一个报表查询又加了两个复合索引结果数据导入环节慢了将近40%。加索引的本意是让查询快一点结果因为导入是整个环节的瓶颈反而拖累了整体效率。所以在建索引之前先要搞清楚这张表是读多写少还是写多读少。如果是清洗、导入这类重写场景索引要克制如果是查询报表类场景索引就需要精心设计。不要一上来就追求“每个查询字段都有索引”要基于实际SQL模式来设计。4.2 复合索引的字段顺序YashanDB的索引机制遵循最左前缀原则也就是说复合索引中字段的排列顺序直接决定了这个索引能被哪些查询用到。很多人在这里容易犯迷糊觉得反正都建了复合索引查询条件随便写都能走索引。实际上并不完全是这样的。我的经验法则是区分度高的字段放前面等值查询的字段优先于范围查询的字段。举个例子如果查询条件是WHERE status ? AND create_time ?那复合索引应该设计成(status, create_time)而不是(create_time, status)。因为status是等值查询区分度高放前面能让索引快速定位到一小部分数据再在局部范围里用create_time做范围过滤效率会高很多。反过来如果把create_time放前面索引要先按时间扫一大段再在结果里过滤status效率就差不少。4.3 覆盖索引的隐性收益覆盖索引是我在YashanDB里用得最多但最容易被忽略的一个技巧。所谓覆盖索引就是查询需要的所有字段都能从索引本身拿到不需要回表读取数据行。对于高频的统计类查询这个收益非常可观。比如某个报表要统计每种状态的数据量SELECT status, COUNT(*) FROM orders GROUP BY status;如果只在status上建普通索引执行时可能会遍历索引后回表取数据再聚合。如果建立(status, id)的复合索引或者包含其他查询需要的字段那查询引擎可以直接在索引里完成扫描和聚合省掉回表这一步耗时往往能下降一个量级。当然我刚也说了索引是有代价的。所以覆盖索引只建议加在真正高频的查询上不要为了覆盖而覆盖。我的做法是先跑一段时间的慢查询日志把TOP 10高频SQL拎出来逐个分析它们的访问路径再决定要不要加覆盖索引。这样加索引才是有依据的优化而不是拍脑袋。5. MERGE语句的妙用一条语句完成比对更新与插入5.1 传统写法的痛点数据清洗场景里经常要做这样一件事如果某条记录在目标表里存在就更新它如果不存在就插入。以前我习惯分两步走先查一遍存在与否再决定是UPDATE还是INSERT。这个写法在数据量小的时候问题不大数据量一大问题就来了。首先每次判断存在性都要走一次查询几千几万条数据就要查几千几万次开销非常可观。其次查询和更新之间有时间差如果数据源本身在变可能出现判断时不存在、插入时却已经存在的竞态问题导致唯一约束冲突。这些问题在YashanDB里都可以用MERGE语句来解决。5.2 MERGE的核心语法YashanDB的MERGE语法跟Oracle的用法非常接近基本的形态是这样的MERGE INTO target_table t USING source_table s ON (t.id s.id) WHEN MATCHED THEN UPDATE SET t.name s.name, t.update_time SYSDATE WHEN NOT MATCHED THEN INSERT (id, name, update_time) VALUES (s.id, s.name, SYSDATE);这条语句的逻辑很清晰根据ON条件判断目标表和源表中哪些记录匹配匹配了就更新没匹配就插入。整个过程是一次操作数据库内部完成判断和写入不需要应用层反复查询。我在实际项目中经常把source_table换成子查询而不是一个物理表。比如先JOIN两张临时表再对结果做MERGE灵活性非常高。这里有几个细节值得注意第一ON条件里的字段一定要能唯一标识一条记录通常用主键或业务唯一键。如果ON条件有多条匹配MERGE会报错因为数据库不知道该更新哪一条。第二WHEN MATCHED分支的UPDATE可以只更新部分字段不用把整行都覆盖。这样能减少不必要的写入开销。第三如果源表里有重复记录也会导致MERGE失败。所以执行MERGE之前最好先确认源数据是去重后的这是一个典型的坑。5.3 用MERGE替代“先查后写”实测对比我做过一次小实验数据量是一百万条记录一部分需要更新一部分需要插入。传统“先查后写”的方式总共跑了大约7分钟用MERGE一次性完成耗时大约2分半。差距不是一点半点主要省下来的就是反复查询的开销。而且MERGE还有一个隐性优点它的事务边界非常清晰。传统方式里如果更新成功了一批、插入的时候失败了你可能要自己去协调哪些数据已经处理过哪些没有。MERGE把更新和插入放在同一条语句里要么全部成功要么全部回滚状态管理简单可靠。这也是为什么我在规整上游数据、做增量同步这类任务里特别偏爱MERGE的原因。6. 临时表与中途落盘的取舍让清洗过程更可控6.1 什么时候该用临时表做数据处理时很多人喜欢一口气用大SQL把所有逻辑写完表A关联表B再跟表C做聚合再嵌套一层子查询最后更新到目标表。这种写法看起来很“高级”但实际执行时数据库优化器可能会选择一种并不理想的执行计划比如重复扫描同一张大表或者生成的中间结果集大到内存都放不下被迫频繁落盘。YashanDB支持会话级临时表和全局临时表。临时表的语义是数据生命周期只在当前会话或当前事务内不需要手动清理会话结束自动消失。合理使用临时表可以把复杂逻辑拆成若干个简单步骤每一步的结果物化到临时表里这样不仅执行计划更可控中间过程也更容易排查。我通常会把临时表用在两类场景。一类是数据清洗的中间结果比如先把源数据标准化、去重写入临时表再拿临时表去JOIN目标表做更新。这样JOIN的时候源数据已经是干净、紧凑的关联效率要高不少。另一类是复杂聚合的中间层比如先按月聚合到临时表再基于临时表做周聚合或年聚合避免在一条SQL里做多层嵌套。6.2 临时表的使用技巧与注意事项在YashanDB里临时表的定义方式跟普通表很相似但有一个重要区别临时表的数据是会话级或事务级的不会被其他会话直接看到。我在清洗任务里的用法通常是CREATE GLOBAL TEMPORARY TABLE tmp_clean_data ( id NUMBER, name VARCHAR2(100), status NUMBER, create_time DATE ) ON COMMIT PRESERVE ROWS;ON COMMIT PRESERVE ROWS表示提交事务后数据仍然保留适合在一个会话里先写入、再多次查询的场景。如果你希望事务提交后数据自动清空可以用ON COMMIT DELETE ROWS。使用临时表时我发现两个容易踩的坑。第一个是临时表上不要建立过多索引临时表本身数据量通常不会特别大而且生命周期很短建索引反而浪费时间和空间。第二个是临时表的统计信息不一定准确因为创建时是空的写入数据后统计信息不会自动更新可能会影响优化器的判断。如果发现用了临时表之后执行计划反而更差可以手动收集一下统计信息再跑。6.3 中途落盘内存与磁盘的博弈有时候我会有意让中间结果落盘而不是一直在内存里连续计算。这个想法看起来跟“追求高效”有点矛盾但实际操作中有它的道理。YashanDB在处理超大JOIN或聚合时如果内存不够本身就会把中间结果写到磁盘临时文件。与其让数据库自主决定什么时候落盘、以什么格式落盘不如我在关键步骤主动物化一份临时表。这样做的优势是第一中间结果可以被复用避免同一个子查询被反复执行多次第二我可以显式地在临时表上做分区、采样或者加索引精细控制后续步骤的性能。当然临时表也不是用得越多越好。每落盘一次就多一次写磁盘、读磁盘的开销。所以这里要有一个度如果中间结果只有几千行完全没必要建临时表直接用子查询就好如果中间结果有几百万行、而且后续要被复用两三次那临时表的收益就很明显了。7. 常见问题与排查技巧实录7.1 批量提交后数据重复或错位我在一开始做批量提交时就遇到过这个问题而且现象很隐蔽数据的总行数是对的但里面的个别字段串了。排查到最后发现是PreparedStatement在批量提交后没有重新绑定参数。因为同一批数据里的参数值如果跟上一批不一样而你没有重新set数据库就沿用上一批的值导致数据错位。这个问题的排查思路是每次进入循环时先对所有占位符重新set一遍哪怕值跟上一批一样也务必重新赋值。另外在执行executeBatch之后可以调用clearParameters清空参数再进入下一批。这样做虽然多几行代码但能彻底杜绝这种隐蔽的数据错乱。7.2 MERGE执行报错源表重复数据导致匹配歧义MERGE语句报ORA-30930或类似的“行过多”错误十有八九是因为源数据里有重复的关联键。比如你用订单号做ON条件但源表里同一个订单号出现了两行数据库就不知道到底该把哪一行更新到目标表。解决办法是在MERGE之前做一次去重。常见做法是用ROW_NUMBER()窗口函数给源数据编号只保留编号为1的行再执行MERGE。我当时排查这个问题的速度还算快因为错误信息比较明显但如果你没往“重复数据”方向想可能会浪费很多时间。建议在写MERGE任务的代码时永远把去重步骤放在前面。7.3 并行度设置了但没生效前面讲过并行DML在某些条件下会被优化器放弃。最常见的四个原因是第一目标表上有触发器并行更新会被禁止第二目标表上有未提交事务并行操作需要等待第三会话级参数被语句级Hint覆盖第四表本身太小优化器认为并行不值得。我在排查时一般先看执行计划EXPLAIN PLAN FOR加目标SQL确认计划里有没有PXParallel eXchange字样。如果没有再去查表上有没有触发器或外键约束。这个思路基本能覆盖绝大多数“并行不生效”的情况。7.4 临时表统计信息过期导致执行计划偏差临时表数据是动态写入的统计信息很难自动维护。有时候第一天任务跑得很快第二天同样的任务突然变慢很可能就是因为临时表里的数据量变化了而执行计划还是按旧的统计信息生成的。我的处理方式是在写入临时表之后、正式执行查询之前手动收集一次统计信息。YashanDB支持DBMS_STATS包用法跟Oracle的非常接近EXEC DBMS_STATS.GATHER_TABLE_STATS(用户名, 临时表名);这一步操作虽然会花一点点时间但能有效避免执行计划跑偏。8. 个人实操心得这几个技巧单独拎出来每一个都不算高深但组合在一起确实能改变数据处理环节的整体体验。我最深的感受是数据库优化的核心不是某一个神奇参数而是一套“观察-假设-验证”的循环先观察瓶颈在哪再针对瓶颈做调整最后用数据验证调整的效果。批量提交解决的是固定开销问题索引策略解决的是查询路径问题MERGE解决的是业务逻辑冗余问题临时表解决的是执行计划失控问题并行度解决的是资源利用率问题。把它们按顺序排好基本上可以覆盖绝大多数数据处理场景的优化空间。最后再分享一个小技巧。每次做优化之前我都会先记录优化前的耗时和系统负载优化后做对比。这不是形式主义而是因为数据库优化的收益很可能被其他因素掩盖。比如你明明优化了SQL但当时IO本身就有波动测出来的提升就不准。先有一组基线数据再来判断优化是否真的有效这是所有经验里最值得养成的一个习惯。
