层层升入:SQL极限调优之一次更新操作的N种优化可能

杨廷琨,网名 yangtingkun

云和恩墨技术总监,Oracle ACE Director,ACOUG 核心专家

最近进行了一次更新操作,整个处理和优化的过程很有意思,于是将这个过程记录了下来。

首先描述一下更新的要求:根据远端数据库中几张表的关联结果来刷新本地表中的一个字段的值。如果本地表中记录的ID在远端表关联中可以查询到,则这条记录的相应字段更新为1;如果对应记录在远端无法查询到记录,则这个字段更新为0。

这个需求比较简单,但是被更新的表是物化视图复制的基表,这张表的所有修改都会同步到多个远端的物化视图中。为了避免将大量不必要的修改同步到远端站点,更新应该针对当前状态不正确的记录。简单地说就是要判断这条记录的当前值和更新后的值是否一致,只有二者不一样的记录才须更新。

此外还有一点要求就是不建立临时表,使用SQL或PL/SQL来尽量高效地实现这个功能。不使用临时表主要出于两点考虑:一是由于需求本身很简单,写SQL或PL/SQL最多也就十几行语句而已,为这么简单的需求建立一个临时表没有太大必要;另外一点是由于当前数据库版本为9204,在这个版本中,以INSERT INTO SELECT方式插入临时表存在Bug。一般来说,临时表的优点之一就是产生很少的REDO,但是由于这个Bug的存在会导致这个版本的临时表在插入时产生的REDO比普通表还要高。

由于原始的SQL相对比较复杂,因此构造了一个相对简单的例子来模拟问题:

SQL> CONN YANGTK/YANGTK@YTK102

已连接。

SQL> CREATE TABLET1 AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;

表已创建。

SQL> ALTER TABLE T1ADD PRIMARY KEY (ID);

表已更改。

SQL> CREATE TABLET2 AS SELECT ROWNUM ID, B.* FROM DBA_SYNONYMS B;

表已创建。

SQL> CREATE INDEXIND_T2_ID ON T2(ID);

索引已创建。

SQL> ALTER TABLE T2MODIFY ID NOT NULL;

表已更改。

SQL> CREATE TABLET3 AS SELECT ROWNUM ID, C.OWNER, C.TABLE_NAME, C.COLUMN_NAME

2 FROM DBA_TAB_COLUMNS C;

表已创建。

SQL> ALTER TABLE T3ADD PRIMARY KEY (ID);

表已更改。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T1')

PL/SQL 过程已成功完成。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T2')

PL/SQL 过程已成功完成。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T3')

PL/SQL 过程已成功完成。

SQL> CONN YANGTK/YANGTK@YTK92

已连接。

SQL> CREATE TABLET AS SELECT ROWNUM ID, OBJECT_NAME, MOD(ROWNUM, 2) TYPE FROM DBA_OBJECTS A;

表已创建。

SQL> ALTER TABLE TADD PRIMARY KEY (ID);

表已更改。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T')

PL/SQL 过程已成功完成。

SQL> CREATE DATABASELINK YTK102 CONNECT TO YANGTK IDENTIFIED BY YANGTK USING 'YTK102';

数据库链接已创建。

在这个例子中,本地数据库是YTK92,要更新的是T表的TYPE字段。更新的依据是远端数据库YTK102中的T1、T2和T3表。如果T表中一条记录的ID可以在远端T1、T2、T3表的联合查询中得到,则这条记录的TYPE应该更新为1;如果查询不到对应的记录,则要更新TYPE的值为零。此外如果要更新需要更新的记录,则要判断当前表中的TYPE是否已经是正确的结果,如果TYPE的值本身就是正确的,则这条记录不需要更新。

最简单的方法莫过于更新两次,每次只更新一部分数据:

SQL> SET TIMING ON
SQL> BEGIN
 2   UPDATET SET TYPE = 1
 3   WHERETYPE = 0
 4   ANDID IN
 5    (
 6     SELECTT1.ID
 7    FROM T1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3
 8     WHERET1.ID = T2.ID
 9     ANDT2.ID = T3.ID
10    );
11  
12   UPDATET SET TYPE = 0
13   WHERETYPE = 1
14   ANDNOT EXISTS  
15    (
16     SELECT1
17     FROMT1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3
18     WHERET1.ID = T2.ID
19     ANDT2.ID = T3.ID
20     ANDT.ID = T1.ID
21    );
22  END;
23  /

PL/SQL 过程已成功完成。

已用时间: 00: 00: 44.28

SQL> ROLLBACK;

回退已完成。

已用时间: 00: 00: 01.10

这是最简单的思路,但是要通过PL/SQL来实现,而且是两条UPDATE语句,此外效率还有点低:对于测试的例子来说,只有几万条记录,而更新就用了44秒。

上面的语句可以通过一个UPDATE来实现更新,只不过逻辑略微复杂了一些:

SQL> UPDATE T SETTYPE =
 2   (
 3     SELECTTYPE
 4     FROM
 5     (
 6       SELECTT.ID, DECODE(T1.ID, NULL, 0, 1) TYPE
 7       FROMT,
 8       (
 9          SELECTT1.ID
10          FROM T1@YTK102T1, T2@YTK102 T2, T3@YTK102 T3
11          WHERE T1.ID= T2.ID
12          AND T2.ID= T3.ID
13       ) T1
14       WHERET.ID = T1.ID(+)
15       AND T.TYPE!= DECODE(T1.ID, NULL, 0, 1)
16    ) A
17    WHERE T.ID= A.ID
18   )
19   WHERE EXISTS
20   (
21     SELECT1
22     FROM
23     (
24       SELECTT.ID, DECODE(T1.ID, NULL, 0, 1) TYPE
25       FROM T,
26       (
27          SELECT T1.ID
28         FROM T1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3
29          WHERE T1.ID = T2.ID
30          ANDT2.ID = T3.ID
31       ) T1
32       WHERET.ID = T1.ID(+)
33       AND T.TYPE!= DECODE(T1.ID, NULL, 0, 1)
34     ) A
35     WHERE T.ID= A.ID
36   )
37   ;

已更新15407行。

已用时间: 00: 01: 18.03

SQL> ROLLBACK;

回退已完成。

已用时间: 00: 00: 00.15

有的时候,一个复杂的SQL并不比两个简单的SQL效率要高,上面就是一个例子。在这个例子中造成一个SQL效率更低的主要原因是:无论是前面的两次更新,还是一个UPDATE语句,对远端对象的两次访问是无法避免的,且后一个UPDATE的逻辑更加复杂,选择执行计划更加困难。

现在的瓶颈在于访问远端对象的代价相对较大,因此下面通过PL/SQL的方式来避免对远端对象的多次访问:

SQL> DECLARE
 2     V_TYPENUMBER;
 3   BEGIN
 4     FOR I IN(SELECT ID, TYPE FROM T) LOOP
 5       SELECTDECODE(COUNT(T1.ID), 0, 0, 1) INTO V_TYPE
 6      FROM T1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3
 7       WHERET1.ID = T2.ID
 8       AND T2.ID= T3.ID
 9       AND T1.ID= I.ID;
10    
11       IF I.TYPE != V_TYPE THEN
12        UPDATET SET TYPE = V_TYPE WHERE ID = I.ID;
13       END IF;
14     END LOOP;
15   END;
16   /

PL/SQL 过程已成功完成。

已用时间: 00: 00: 10.67

SQL> ROLLBACK;

回退已完成。

已用时间: 00: 00: 00.07

到目前为止,UPDATE的执行效率已经基本可以接受了,但是这只是一个简单的例子,对于数据量比较大的情况,这种方式效率仍然比较低。虽然对远端表只读取一次,但是这个读取在循环中完成,肯定有不少的交互开销,操作效率肯定要低于通过一个SQL来完成,而且对于每个匹配的记录都要执行一次UPDATE,这也是比较低效的。修改PL/SQL代码,通过批量处理的方式来执行:

SQL> DECLARE
 2     TYPE T_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 3     TYPE T_TYPEIS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 4     V_IDT_ID;
 5    V_TYPET_TYPE;
 6   BEGIN
 7  
 8     SELECTT.ID, DECODE(T1.ID, NULL, 0, 1) TYPE
 9     BULK COLLECTINTO V_ID, V_TYPE
10     FROM T,
11     (
12       SELECTT1.ID
13      FROM T1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3
14       WHERE T1.ID = T2.ID
15       AND T2.ID= T3.ID
16     ) T1
17     WHERE T.ID= T1.ID(+)
18     AND T.TYPE!= DECODE(T1.ID, NULL, 0, 1)
19     ;
20    
21     FORALL I IN 1..V_ID.COUNT
22       UPDATET SET TYPE = V_TYPE(I) WHERE ID = V_ID(I);
23  
24   END;
25   /

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.35

SQL> ROLLBACK;

回退已完成。

已用时间: 00: 00: 00.12

通过运用PL/SQL减少远端对象的访问次数并利用FORALL进行批量更新。UPDATE语句的执行时间已经从原来的50多秒优化到了0.35秒。

这个执行效率没有任何的问题,但这并不意味着上面的方法就是最优的。如果这时检查执行计划就可以发现:由于是对本地表进行更新,Oracle选择当前站点作为驱动站点,而对远端三个表的查询采用了NESTEDLOOP。如果使用HINT来指定驱动站点并使用HASH JOIN连接方式,还能获得一定的性能提升:

SQL> DECLARE
 2     TYPE T_IDIS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 3     TYPE T_TYPEIS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 4    V_ID T_ID;
 5    V_TYPE T_TYPE;
 6   BEGIN
 7  
 8     SELECTT.ID, DECODE(T1.ID, NULL, 0, 1) TYPE
 9     BULK COLLECTINTO V_ID, V_TYPE
10     FROM T,
11     (
12       SELECT/*+ DRIVING_SITE(T1) USE_HASH(T1 T2) USE_HASH(T3) */ T1.ID
13      FROM T1@YTK102 T1, T2@YTK102 T2, T3@YTK102 T3
14       WHERE T1.ID = T2.ID
15       AND T2.ID= T3.ID
16     ) T1
17     WHERE T.ID= T1.ID(+)
18     AND T.TYPE!= DECODE(T1.ID, NULL, 0, 1)
19     ;
20    
21     FORALLI IN 1..V_ID.COUNT
22       UPDATET SET TYPE = V_TYPE(I) WHERE ID = V_ID(I);
23  
24   END;
25   /

PL/SQL 过程已成功完成。 已用时间: 00: 00: 00.31 SQL> ROLLBACK;

回退已完成。

已用时间: 00: 00: 01.12

从0.35秒提高到0.31秒,仅优化了0.04秒,效果似乎并不明显。不过这0.04秒的执行时间已经超过了总执行时间的10%,对于大数据量的情况,10%的性能提升也是十分可观的。

通过这个例子可以说明几个问题:

第一,Tom所说的能使用一条SQL就用一条SQL完成,不能使用SQL的话,可以使用PL/SQL完成。这句话在大部分的情况下是正确的,但是并不意味着SQL一定比PL/SQL快,单条SQL一定比两条SQL快,上面的例子就是很好的说明。

第二,批量操作一般情况下要比PL/SQL循环效率高。上面的例子中通过循环和批量两种方法对比很好地说明了这一点。但是不要认为批量操作就一定比循环操作快。对于例子中的一个UPDATE语句的实现,它本身就是一个批量操作,但是由于对远端表访问了两次,效率却远远低于只访问远端对象一次的循环操作。

第三,优化的方法是多种多样的,但是优化思路是固定的。这个例子中优化的原则无非是尽量减少对远端对象的访问、将单条操作转化为批量操作、尽量减少交互次数等几种。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-04-08

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

深入剖析 Group Replication内核的引擎特性

小编寄语 主库master与从库slave的切换不管是主动的还是被动的都需要外部干预才能进行,这与数据库内核本身是按照单机来设计的理念悉悉相关,并且数据库系统本...

3638
来自专栏Java编程技术

MySQL 中基于 XA 实现的分布式事务

Xa主要规定了RM与TM之间的交互,下面来看下XA规范中定义的RM 和 TM交互的接口:

533
来自专栏Python攻城狮

PostgreSQL语法、连接

博客地址:https://ask.hellobi.com/blog/zhiji 欢迎大家来交流学习。

771
来自专栏散尽浮华

Nginx/Apache服务连接数梳理

统计连接数,使用netstat命令或ss命令都可以 1)统计连接数(80端口) [root@wang ~]# netstat -nat|grep -i "80"...

1758
来自专栏iMySQL的专栏

MySQL FAQ 系列 : 如何安全地关闭 MySQL 实例

本文分析了 mysqld 进程关闭的过程,以及如何安全、缓和地关闭 MySQL 实例,对这个过程不甚清楚的同学可以参考下。

1660
来自专栏散尽浮华

如何查询一个进程下面的线程数(进程和线程区别)

在平时工作中,经常会听到应用程序的进程和线程的概念,那么它们两个之间究竟有什么关系或不同呢? 一、对比进程和线程 1)两者概念 . 进程是具有一定独立功能的程序...

2109
来自专栏数据库

MySQL线程池问题个人整理

见识了智能合约以及以太坊的工作方式,现在我们就尝试将它部署到两种测试网络里面。

68010
来自专栏杨建荣的学习笔记

很多人比较纠结的约束和索引的关系(r7笔记第75天)

最近有不少朋友公众号留言或者微信私信问我一个问题,出乎我的意料,问题竟然都是很相似的,所以我统一答复一下。 之前写了一篇文章 一个清理和查询都要兼顾的简单方案,...

3326
来自专栏魏琼东

基于DotNet构件技术的企业级敏捷软件开发平台 - AgileEAS.NET - ORM访问器

    上一篇文章AgileEAS.NET之数据关系映射ORM简单介绍了一下AgileEAS.NET平台中ORM对象的组织机构体系,但并没有对其所执行的数据存取...

1766
来自专栏后端技术探索

mysql问题排查实例

最近遇到应用频繁的响应缓慢,无法正常访问。帮忙一起定位原因,最后定位到的问题说起来真的是很小的细节问题,但是就是这些小细节导致了服务不稳定,真是细节决定成败。这...

542

扫描关注云+社区