线上发现一个分页页面较之前明显变慢,随即对这个功能进行了优化。优化的过程很简单,但从这个问题中引出一个较经典的分页问题,需要以培训听形式向组员传播、打疫苗,因为这个问题在开发阶段会往往被忽视掉,却能引发严重的后果。

“ 简单优化”

SELECT *
  FROM (SELECT WMMATDOCSA0_.*, ROWNUM R
           FROM (SELECT WMMATDOCSA0_.*
                    FROM TI_WM_MAT_DOC_SAP_WM WMMATDOCSA0_
                   WHERE NOT EXISTS (SELECT 1
                            FROM TT_WM_MAT_DOC_HEADER MATDOCHEAD1_
                           WHERE TO_CHAR(MATDOCHEAD1_.WM_YEAR_NO) || '-' ||
                                 MATDOCHEAD1_.WM_MAT_DOC_NO =
                                 WMMATDOCSA0_.WM_DOC_NO)
                   ORDER BY WMMATDOCSA0_.ID DESC) )
 WHERE R > 1 AND R < 15 

该SQL使用一个not exists子句作为主要的查询条件,这类SQL的性能严重依赖于exists内子句的性能(外层有多少条数据,exists子句就要执行多少次)。由于TT_WM_MAT_DOC_HEADER数据的增长,内层子句的性能已经明显降低,导致整个SQL效率急剧下降。系统中这条SQL的时间需要使用2秒左右。

方案一:增加索引,提升子句性能
注意SQL第7-9行:

TO_CHAR(MATDOCHEAD1_.WM_YEAR_NO)||'-'||MATDOCHEAD1_.WM_MAT_DOC_NO
              = WMMATDOCSA0_.WM_DOC_NO

这个过滤条件是一个没有索引的函数化列,随着数据量的增加这个过滤条件的效率会降低。可以在这里建立一个函数索引,提高exists子句的执行效率从而提高整个语句的效率。(若exists子句性能慢0.1秒,外层数据有1000条,则总共慢100秒,当然oracle会根据统计信息选择优化的执行计划,这里只是举个例子)

CREATE INDEX FX_MAT_DOC_NO_YEAR ON 
       TT_WM_MAT_DOC_HEADER(to_char(WM_YEAR_NO)||'-'||WM_MAT_DOC_NO);

此外exsit子句有“短路”的特性,一旦匹配到记录就中断返回。整个语句的执行效率相当不错。

方案二:使用连接
相比较exists,多数情况我愿意选择外连接解决此类问题,原因如下:

  • 虽然exists子句也可以非常高效,某些场景下比join更快,但exists内子句性能些微下降都可能严重影响整个语句的性能。
  • 虽然对大表做join是比较危险的,但可以先求出小结果集后再做join避免。另外连接也较exists子句在逻辑上更加清晰。

由于篇幅,这里就略过先求出小结果集再做连接的过程

SELECT *
          FROM (SELECT WMMATDOCSA0_.*, ROW_NUMBER() OVER(ORDER BY WMMATDOCSA0_.ID DESC) RN
                   FROM TI_WM_MAT_DOC_SAP_WM WMMATDOCSA0_
                   LEFT JOIN TT_WM_MAT_DOC_HEADER MATDOCHEAD1_ ON TO_CHAR(MATDOCHEAD1_.WM_YEAR_NO) || '-' ||
                                                                MATDOCHEAD1_.WM_MAT_DOC_NO =
                                                                WMMATDOCSA0_.WM_DOC_NO
              WHERE MATDOCHEAD1_.WM_YEAR_NO IS NULL)
       WHERE RN >= 1 AND RN < 15

根据具体情况,这条SQL可能会较第一种方案更慢,但效率上的波动,不会像第一种方案那么大。


 真正的问题!!

针对这个例子,效率上的优化已经完成了,单独执行时速度已经很不错了,不过有严重的内存问题,在并发环境下甚至是灾难性的。

先看一下上面任意一种优化方案的执行计划。

----------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                      |   768K|   677M|       | 42569   (1)| 00:08:31 |
|*  1 |  VIEW                     |                      |   768K|   677M|       | 42569   (1)| 00:08:31 |
|   2 |   COUNT                   |                      |       |       |       |            |          |
|   3 |    VIEW                   |                      |   768K|   668M|       | 42569   (1)| 00:08:31 |
|   4 |     SORT ORDER BY         |                      |   768K|   161M|   171M| 42569   (1)| 00:08:31 |
|*  5 |      HASH JOIN RIGHT ANTI |                      |   768K|   161M|       |  5918   (1)| 00:01:12 |
|   6 |       INDEX FAST FULL SCAN| FX_MAT_DOC_NO_YEAR   | 37436 |   987K|       |    41   (0)| 00:00:01 |
|   7 |       TABLE ACCESS FULL   | TI_WM_MAT_DOC_SAP_WM |   768K|   141M|       |  5871   (1)| 00:01:11 |
----------------------------------------------------------------------------------------------------------

注意看Bytes列,该条语句对内存的使用是677M.

想像一下,如果系统在并发阶段,各各模块都在争抢内存的话,有这么一条SQL存在,会导致数据库性能极剧下降;又或者在老系统的生产机上内存较小,这样一条SQL或者根本执行不了(oracle会根据这些因素改变执行计划,比如使用nested loop做连接,但效率肯定是大大折扣)。

分析其原因:我们先对TI_WM_MAT_DOC_SAP_WM先做了全表的排序,加上行号后再筛选出指定页数内的记录。在排序加行号时,我们使用的是select * from————访问了这张所有行的所有字段,这就是内存使用过多的原因。
之前在于同事交流时,大家都只能说到将select * 改为 select具体的字段,但这样仍然会占用大量内存,特别是在显示“最后一页”时:需要访问所有记录的select字段。而在分页页面中,系统只需要展示出指定页内记录所有的字段,并非所记录的。
使用如下SQL:

SELECT *
  FROM TI_WM_MAT_DOC_SAP_WM
 WHERE ROWID IN (SELECT RID
                   FROM (SELECT WMMATDOCSA0_.ROWID RID, ROW_NUMBER() OVER(ORDER BY WMMATDOCSA0_.ID DESC) RN
                            FROM TI_WM_MAT_DOC_SAP_WM WMMATDOCSA0_
                            LEFT JOIN TT_WM_MAT_DOC_HEADER MATDOCHEAD1_ ON TO_CHAR(MATDOCHEAD1_.WM_YEAR_NO) || '-' ||
                                                                           MATDOCHEAD1_.WM_MAT_DOC_NO =
                                                                           WMMATDOCSA0_.WM_DOC_NO
                           WHERE MATDOCHEAD1_.WM_YEAR_NO IS NULL)
                  WHERE RN >= 1 AND RN < 15)

其执行计划为:

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                         |     1 |   205 |       | 18472   (1)| 00:03:42 |
|   1 |  NESTED LOOPS               |                         |     1 |   205 |       | 18472   (1)| 00:03:42 |
|   2 |   VIEW                      | VW_NSO_1                |   768K|  9010K|       | 14941   (1)| 00:03:00 |
|   3 |    HASH UNIQUE              |                         |     1 |    18M|       |            |          |
|*  4 |     VIEW                    |                         |   768K|    18M|       | 14941   (1)| 00:03:00 |
|*  5 |      WINDOW SORT PUSHED RANK|                         |   768K|    34M|    41M| 14941   (1)| 00:03:00 |
|*  6 |       HASH JOIN RIGHT ANTI  |                         |   768K|    34M|       |  5891   (1)| 00:01:11 |
|   7 |        INDEX FAST FULL SCAN | PK_TT_WM_MAT_DOC_HEADER | 37436 |   548K|       |    34   (0)| 00:00:01 |
|   8 |        TABLE ACCESS FULL    | TI_WM_MAT_DOC_SAP_WM    |   768K|    23M|       |  5851   (1)| 00:01:11 |
|   9 |   TABLE ACCESS BY USER ROWID| TI_WM_MAT_DOC_SAP_WM    |     1 |   193 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

现在的内存使用只有几十M了。这里有一个技巧,在对全表做排序加行号的查询中,只选择出rowid(而不是所有列),等确定出具体的行号后,将指定页数内的rowid取出,再直接使用少量的rowid去访问数据。

这是一个很简单的技巧,但由于在系统开发阶段,一般只会考虑SQL的执行时间,只要SQL执行在本机合格了,便提交代码。而类似于这种隐藏较深的,在系统运行环境中才能暴露出来的问题,往往会被忽略。


正如开头所说的,这种问题在单元测试、集成测试中都很难被发现。即使是压力测试,如果覆盖度不够的话,也是很容易被忽略的问题。除了加强压力测试的覆盖度,个人认为最好的方式还是需要将这类常见的“坑”以培训的形式向组员传播,提前打好疫苗,把这类问题在开发阶段消除最好不过。