通过Snapshot Standby来精确评估SQL性能 (r9笔记第73天)

最近处理了一个需求，比较紧急，映射到数据库层面是需要更新17万id的值，听起来是不少，根据数据架构进行了分析，发现目前是做了分库分表的方式，所以这17万的id在这些分库中都可能存在，而跨部门的数据交付中，也没有做分库的区分，所以拿到的id是一个笼统的概念，即哪个id对应哪个分库没有事先过滤甄别，这个工作就自然而然的下落到了DBA头上。 分库分表的方式，目前有12个分库，是以十二个用户的形式体现出来的，所以如果要整体更新，那么每个分库都需要更新一遍，有匹配的数据就更新，否则忽略。如此一来，更新的数据规模是就有几种计算方式，一种是每个id更新对应一条sql语句，那么语句就有17000*12=2040000条，200多万条， 规模是相当惊人了。执行期间的锁暂且不考虑，光是执行时间在毫秒，百万的基数也会把差距放大。还有一种思路是提供12个sql语句，每个分库各一条sql 语句，把17万的id放入一个临时表中，关联更新，这种方式执行时间肯定相比单条语句要长，但是具体多多少还是未知数。 尤其是线上系统，关键的业务系统，这类的操作就尤其敏感。如果有几种方案，需要给出一个基本的分析和评定，哪种更好，有什么准确的数据呢，主库中是万万使不得，需要有把握再动手。所以我隆重推荐使用Snapshot Standby来实现这类需求，评估性能，预估影响范围和操作时间，在完全一致的数据基础上操作，得到的数据更加有说服力。 我想了几个方案作为备用方案： 方法: 每个id对应1个sql，17万sql*12个分库 方法2: 一个临时表，12个分库，12个sql 方法3: 一个临时表，12个分库，并行执行 方法4: 每个id对应1个sql,12个分库并行执行 这些方案在Snapshot Standby的环境中都可以轻松实现。主要原理是基于闪回日志，而亮点则在于备库可读可写，测试完毕之后可以继续闪回，应用最新的数据变更。而对于上面方案中的临时表，我的考虑是基于外部表，因为本身要把这些数据导入，用完之后还得删除，热插拔的方式更加实惠。 创建外部表的语句如下： CREATE TABLE test_uin (uin varchar2(30) ) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY batch_query_dir ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE ) LOCATION ('uin.txt') ); 开启备库为Snapshot Standby DGMGRL> convert database s2test0 to snapshot standby; Converting database "s2test0" to a Snapshot Standby database, please wait... Database "s2test0" converted successfully DGMGRL> show configuration; Configuration - test0_dg Protection Mode: MaxPerformance Databases: stest032 - Primary database stest0 - Physical standby database s2test0 - Snapshot standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS 首先我测试了临时表通过insert填充数据的步骤，17万的id，用了大概5分钟。由此可想如果是12个分库，17万id更新，那么串行下来，少说也要1个小时，这个对于线上系统来说是很严重的延迟了。 然后我在这个备库中进行关联更新。 原来的语句如下: update TEST_USER_INFO set status=-99 where uin=?; 改为临时表的结果集来处理。 update TEST_USER_INFO set status=-99 where uin in (select uin from test_uin); 这种方式大概用了30秒的时间就在12个分库顺利完成，平均每个分库大概是2秒钟的执行效率。 如此一来有了很精确的评估，所以实施起来就会很得心应手，而在部署前，得到的临时调整，需要修改的id变为了38万，当然看起来数据翻了一倍，但是执行效率还是杠杠的。大概是30秒就顺利完成。所以通过这种方式还是能够很精准的分析潜在的性能问题，而对于上面逐步分析的集中测试场景，其实有了这些数据就了然于胸。