EsgynDB 关于带索引的DELETE性能提升

EsgynDB在金融行业的分支版本钱库（QianBase）已经问世有段时间，在钱库版本中，对相关的功能、性能部分做了大量的增强。这其中包括插入、更新及删除的性能提升。

在早期的版本中，当目标表只有主键的情况下，EsgynDB的增删改的性能是相当不错的，因为默认情况下执行计划走的是批量提交的方式，在执行计划中我们可以看到诸如VSBB_UPSERT、VSBB_DELETE这样的字眼，VSBB即批量执行。但当表上有一个或多个索引的时候，相比较于没有索引的情况下，性能会下降很大。

针对此问题，钱库中做了相关的性能增强，包括插入、更新及删除，都做了相应的增强。关于插入和更新，我们会在后续文章中慢慢介绍，本文中我们先对比一下DELETE的性能提升。

在早期版本中，在有索引的表上做DELETE操作时，对于表记录的删除，执行计划并没有采用VSBB_DELETE，而是走TRAFODION_DELETE，这严重限制了删除的性能，以下是一个DELETE有索引表的执行计划，

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

5    .    6    root                            x                     1.19E+006
3    4    5    nested_join                                           1.19E+006
.    .    4    trafodion_vsbb_delet            IDX_TEST_DELETE       1.00E+000
1    2    3    nested_join                                           1.19E+006
.    .    2    trafodion_delete                TEST_DELETE           1.00E+000
.    .    1    trafodion_scan                  TEST_DELETE           1.19E+006

虽然对于索引的执行计划是TRAFODION_VSBB_DELET，但由于对于主表的删除是TRAFODION_DELETE，导致语句执行效率相当低下，从以下运行时的统计信息可以看出，EX_TRAF_DELETE的OperCPUTime是71,645,451。此表总记录数100万，删除记录为99万多，删除时长超过2小时未结束。

>>get statistics for qid current default;
Qid                      MXID11000016667212447900982995646000000000206U3333302T000_335___SQLCI_DML_LAST__
Compile Start Time       2020/02/08 14:11:01.268632
Compile End Time         2020/02/08 14:11:01.279867
Compile Elapsed Time                 0:00:00.011235
Execute Start Time       2020/02/08 14:11:01.280240
Execute End Time         2020/02/08 16:11:01.894070
Execute Elapsed Time                 2:00:00.613830
State                    DEALLOCATED
Rows Affected            184,938
SQL Error Code           -8448
Stats Error Code         0
Query Type               SQL_DELETE_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           1
Transaction Id           72339069014678765
Source String            delete from test_delete where a>100;
SQL Source Length        36
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
Query hash               0
SLA Name                 defaultSLA
Profile Name             defaultProfile
No. of times executed    1
Min. Execute Time        7,200.613830 secs
Max. Execute Time        7,200.613830 secs
Avg. Execute Time        7,200.613888 secs
Stats Collection Type    OPERATOR_STATS

   LC   RC   Id PaId ExId Frag TDBName                    DOP   Dispatches        OperCPUTime        EstRowsUsed        ActRowsUsed        ActDataUsed Details

    5    .    6    .    0    0 EX_ROOT                      1            2                  8                  0                  0                  0 72297143|0|0|11333|
    3    4    5    6    0    0 EX_ONLJ                      1          366             22,537        1.19382e+06                  0                  0
    .    .    4    5    0    0 EX_TRAF_VSBB_DELETE          1          181            166,885                  1            183,118                  0 TRAF_RSRVD_3:TRAFODION.SEABASE.IDX_TEST_DELETE|10496397|7195172250|0|
    1    2    3    5    0    0 EX_ONLJ                      1          368             49,313        1.19382e+06            184,938         10,726,404
    .    .    2    3    0    0 EX_TRAF_DELETE               1          182         71,645,451                  1            184,938         10,726,404 TRAF_RSRVD_3:TRAFODION.SEABASE.TEST_DELETE|20713164|7166976921|0|
    .    .    1    3    0    0 EX_TRAF_SELECT               1          182            412,949        1.19382e+06            186,188         10,798,904 TRAF_RSRVD_3:TRAFODION.SEABASE.TEST_DELETE|20108412|112207|186368|

在新的版本中，执行计划有了变化，

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

5    .    6    root                            x                     3.99E+005
3    4    5    nested_join                                           3.99E+005
.    .    4    trafodion_vsbb_delet            IDX_TEST_DELETE       1.00E+000
1    2    3    nested_anti_semi_joi                                  3.99E+005
.    .    2    trafodion_vsbb_delet            TEST_DELETE           1.00E+000
.    .    1    trafodion_scan                  TEST_DELETE           3.99E+005

我们可以看到，现在对于主表的删除动作也变为了TRAFODION_VSBB_DELET，运行时的统计信息如下，执行时长为21秒。

>>get statistics for qid current default;
Qid                      MXID11000014915212447930791902416000000000206U3333302T000_109___SQLCI_DML_LAST__
Compile Start Time       2020/02/08 22:16:13.997512
Compile End Time         2020/02/08 22:16:14.012970
Compile Elapsed Time                 0:00:00.015458
Execute Start Time       2020/02/08 22:16:14.014781
Execute End Time         2020/02/08 22:16:35.745718
Execute Elapsed Time                 0:00:21.730937
State                    DEALLOCATED
Rows Affected            999,900
SQL Error Code           0
Stats Error Code         0
Query Type               SQL_DELETE_NON_UNIQUE
Sub Query Type           SQL_STMT_NA
Estimated Accessed Rows  0
Estimated Used Rows      0
Parent Qid               NONE
Parent Query System      NONE
Child Qid                NONE
Number of SQL Processes  1
Number of Cpus           1
Transaction Id           72339069014638598
Source String            delete from test_delete where a>100;
SQL Source Length        36
Rows Returned            0
First Row Returned Time  -1
Last Error before AQR    0
Number of AQR retries    0
Delay before AQR         0
No. of times reclaimed   0
Cancel Time              -1
Last Suspend Time        -1
Query hash               0
SLA Name                 defaultSLA
Profile Name             defaultProfile
No. of times executed    1
Min. Execute Time        21.730937 secs
Max. Execute Time        21.730937 secs
Avg. Execute Time        21.730936 secs
Stats Collection Type    OPERATOR_STATS

   LC   RC   Id PaId ExId Frag TDBName                    DOP   Dispatches        OperCPUTime        EstRowsUsed        ActRowsUsed        ActDataUsed Details

    5    .    6    .    0    0 EX_ROOT                      1            1                  1                  0                  0                  0 4252678|0|0|10469|
    3    4    5    6    0    0 EX_ONLJ                      1        2,567            101,380             399960                  0                  0
    .    .    4    5    0    0 EX_TRAF_VSBB_DELETE          1        2,149            790,288                  1            999,900                  0 TRAF_RSRVD_3:TRAFODION.SEABASE.IDX_TEST_DELETE|56996256|20548235|0|
    1    2    3    5    0    0 EX_ONLJ_ANTI_SEMI_JOIN       1        2,490            183,404             399960            999,900         57,994,200
    .    .    2    3    0    0 EX_TRAF_VSBB_DELETE          1        1,585          1,175,555                  1            999,900                  0 TRAF_RSRVD_3:TRAFODION.SEABASE.TEST_DELETE|5001456|13902261|0|
    .    .    1    3    0    0 EX_TRAF_SELECT               1          976          2,002,050             399960            999,900         57,994,200 TRAF_RSRVD_3:TRAFODION.SEABASE.TEST_DELETE|107989200|4402094|999900|

对比以上两个执行计划，我们看到还有一个区别，就是有一个nested_join被改为了nested_anti_semi_join，这是因为trafodion_vsbb_delet没有返回结果但trafodion_delete有返回，当nested_join在一边有返回结果另一边没有返回结果时就会走semi join，再由于这里的nested_join是SELECT与DELETE操作符的关联，类似于NOT EXISTS或NOT IN，因此变成anti semi join的方式了。