索引覆盖分析

系统优化期间，需要找到优化后最能带来效益的点进行优化，这样才能从根本上提升系统运行效率。

以下内容以某个保单增量处理程序逻辑进行分析。当前增量保单事实提取过程分为以下两步：
a. 通过upd_time增量查询出各个关联表的增量cntr_id主键并插入tmp_f_cntr_v8_cntrrange临时表;
b. 通过tmp_f_cntr_v8_cntrrange表与std_contract等表JOIN操作，获取增量保单数据。


1. 增量范围确定
1.1 业务行为共用增量控制表：
当前现状描述：互不交叉的业务行为（例如新契约个单、合同团单、新契约团单等）共用同一张tmp_f_cntr_v8_cntrrange增量控制表，如下：

问题：
不同业务行为共用tmp_f_cntr_v8_cntrrange增量表，同时都采用tmp_f_cntr_v8_cntrrange表提取增量数据，无端成倍地增加了增量数据范围。


解决方案：
应该为各业务行为创建单独的增量表（例如tmp_f_cntr_v8_cntrrange_appl_psn、tmp_f_cntr_v8_cntrrange_appl_grp、tmp_f_cntr_v8_cntrrange_cntr_grp等），并通过这些增量表提取保单数据。


1.2 upd_time确定增量保单范围：
当前现状描述：
代码如下：

select cntr_idas CNTR_NO,'999'as POL_CODE,'2'as APP_STD_FLAG from std_contract where upd_time>=?and upd_time<?
union
select distinctcntr_id as CNTR_NO,'999'as POL_CODE,'2'as APP_STD_FLAG from cntr_basic_state whereupd_time>=?and upd_time<?
union
select distinctcntr_id as CNTR_NO,'999'as POL_CODE,'2'as APP_STD_FLAG from cntr_sub_state where upd_time>=?and upd_time<?
union
select distinctcntr_id as CNTR_NO,'999'as POL_CODE,'2'as APP_STD_FLAG from insured whereupd_time>=? and upd_time<?
union
select distinctcntr_id as CNTR_NO,'999'as POL_CODE,'2'as APP_STD_FLAG from psn_cntr_holder whereupd_time>=?and upd_time<?
union
select distinctcntr_id as CNTR_NO,'999'as POL_CODE,'2'as APP_STD_FLAG from if_of_alt whereupd_time>=? and upd_time<?
union
select distinctstd_contract.cntr_idas CNTR_NO,'999'as POL_CODE,'2'as APP_STD_FLAG from grant_tranlst,std_contractwhere std_contract.cntr_no=grant_tranlst.cntr_noand grant_tranlst.upd_time>=?and grant_tranlst.upd_time<?
union
select distinctcntr_id as CNTR_NO,'999'as POL_CODE,'2'as APP_STD_FLAG from insur_appl_cv_task,std_contractwhere insur_appl_cv_task.rel_key_no=std_contract.appl_noand insur_appl_cv_task.upd_time>=?and insur_appl_cv_task.upd_time<?

问题：
目前在相关表上的upd_time字段（也只此字段）均加了索引，但是要获得cntr_id时，仍然需要通过lookup rid到原始表中取出cntr_id字段（索引叶子节点没有cntr_id）。这里存在三点问题:
1). lookup rid以得到cntr_id的操作，是upd_time索引查找范围语句资源消耗的大头，lookup rid只适合于小数据量；
2). lookup rid查找原表（因为有很多其他任务访问原表）会增加系统阻塞的概率及降低I/O量；
3). 可能会因为返回数据量大，使得upd_time索引失效。

优化方法：
每张表修改upd_time索引，通过在索引中包含相关字段（例如cntr_id），消除lookup rid操作，将整个增量查找过程转换为索引查找，可以彻底解决以上三个问题。


2. 保单增量事实获取：
当前现状描述：
获取保单增量事实的代码可参见后文《优化前.sql》。

问题：
所有的相关表都是全表（这些表都是大表。当然，语句中有where语句，但仍然是从全表中过滤数据）JOIN，并通过tmp_f_cntr_v8_cntrrange增量范围表提取出增量数据。这里面存在以下问题：
1). 所有表（例如std_contract、cntr_sub_state等）均通过where语句，以及与tmp_f_cntr_v8_cntrrange表关联得到增量数据。这依赖于where语句中的索引，以及和tmp_f_cntr_v8_cntrrange表的关联索引情况，否则很可能引起全表扫描（这种情况非常可怕）；
2). 在上文1.2确定增量范围时，upd_time索引以及lookup rid已经得到了各个表的增量数据，获取保单事实的时候对同一张表又做了一次重复的访问，没有必要；
3). 经过分析，对绝大部分表（都是大表）使用的字段都非常的少，进行全表操作非常的不划算。

解决方案：
整合“确定保单增量范围”、“提取保单事实”两个步骤：
绝大部分表的“保单事实使用到的字段”上创建索引（如果包含upd_time，可同时解决增量范围的问题），可以将表扫描转换为索引扫描（字段少所以索引会很小），完全可以降低系统阻塞（因为只需要访问索引，不需要和其它任务争抢原表），降低I/O量；

优化前：

SELECT e.PROV_BRANCH_CODE                                     AS PROV_BRANCH_CODE,
       a.appl_no                                              AS APPL_NO,
       a.cntr_no                                              AS CNTR_NO,
       CASE
         WHEN a.mr_type = 'M' THEN a.cntr_no
         ELSE (SELECT TOP 1 cntr_no
               FROM   std_contractwith(nolock)
               WHERE  a.master_cntr_id = std_contract.cntr_id
                      AND std_contract.incr_flag <> '2')
       END                                                    ASMASTER_CNTR_NO,
       a.sg_no                                                ASSG_NO,
       Ltrim(Rtrim(CONVERT(VARCHAR(6), a.mgr_branch_no)))     AS TOWN_BRANCH_CODE,
       dbo.Pub_salesno(a.n_sales_branch_no, a.n_sales_code, 8)AS KEY_SALER_ID,
       Isnull(a.n_sales_branch_no, '!')                       ASBRANCH_NO,
       Isnull(a.n_sales_code, '!')                            AS AGENT_NO,
       a.sales_channel                                        AS SALES_CHANNEL_CODE,
       '!'                                                    ASMANAGE_CHANNEL_CODE,
       '!'                                                    ASCENTER_CODE,
       a.pol_code                                             ASPOL_CODE,
       CASE
         WHEN a.moneyin_itrvl = 'W' THEN 1
         ELSE b.moneyin_dur
       END                                                    ASMONEY_DUR,
       a.moneyin_itrvl                                        AS ITRVL_CODE,
       d.ipsn_cust_no                                         AS KEY_IPSN_CUST_ID,
       (SELECT TOP1 hldr_cust_no
        FROM   psn_cntr_holderwith(nolock)
        WHERE  a.cntr_id = psn_cntr_holder.cntr_id
               AND psn_cntr_holder.incr_flag <> '2'
        ORDER  BY psn_cntr_holder.upd_timeDESC)               ASKEY_HLDR_CUST_ID,
       CASE
         WHEN b.insur_dur_unit = 'W' THEN 999
         ELSE b.insur_dur
       END                                                    ASINSUR_DUR,
       a.in_force_date                                        AS IN_FORCE_DATE,
       a.sign_date                                            AS SIGN_DATE,
       a.cntr_term_date                                       AS TERM_DATE,
       CASE
         WHEN a.cg_no IS NULL THEN (SELECT TOP 1 Cast(ext_key12 AS DATETIME)
                                    FROM   insur_appl_cv_task h WITH(nolock)
                                    WHERE  a.appl_no = h.rel_key_no
                                           AND a.cntr_no = h.ext_key9
                                           AND Isdate(h.ext_key12) = 1
                                           AND h.incr_flag <> '2'
                                    ORDER  BY task_seq DESC)
         ELSE (SELECT TOP 1 Cast(ext_key12 AS DATETIME)
               FROM   insur_appl_cv_taskh WITH(nolock)
               WHERE  a.appl_no = h.rel_key_no
                      AND a.cg_no = h.ext_key9
                      AND Isdate(h.ext_key12) = 1
                      AND h.incr_flag <> '2'
               ORDER  BY task_seq DESC)
       END                                                    ASRESP_DATE,
       a.mr_type                                              AS MR_TYPE_CODE,
       a.cntr_stat                                            AS CNTR_STAT,
       a.cntr_type                                            AS CNTR_TYPE_CODE,
       8                                                      ASDATA_SRC_ID,
       z.std_premium                                          AS FACT_STD_PREM,
       z.face_amnt                                            AS FACT_FACE_AMNT,
       d.ipsn_num                                             AS FACT_IPSN_NUM,
       a.cntr_term_cause                                      AS CNTR_STOP_CODE,
       z.stop_moneyin_date                                    AS STOP_MONEYIN_DATE,
       a.cntr_expiry_date                                     AS EXPIRY_DATE,
       f.out_force_date                                       AS OUT_FORCE_DATE,
       f.re_in_force_date                                     AS RE_IN_FORCE_DATE,
       Substring(a.appl_no, 1, 4)                             AS CARD_CODE,
       CASE
         WHEN a.Sales_channelin('OA', 'SP') THEN dbo.Pub_salesno(a.n_sales_branch_no, a.n_sales_code, 8)
         ELSE '-1'
       END                                                    ASKEY_SITE_ID,
       Dateadd(day, -1, auto_in_force_date)                   ASENROLL_DATE,
       (SELECT TOP1 contact_seq
        FROM   psn_cntr_holderwith(nolock)
        WHERE  a.cntr_id = psn_cntr_holder.cntr_id
               AND psn_cntr_holder.incr_flag <> '2'
        ORDER  BY psn_cntr_holder.upd_timeDESC)               ASCONTACT_SEQ,
       CASE
         WHEN g.cntr_no IS NOT NULL THEN 1
         ELSE 0
       END                                                    ASBANK_FLAG,
       a.renew_flag                                           AS RENEW_FLAG,
       a.renew_times                                          AS RENEW_TIMES,
       '!'                                                    ASKEY_MERG_CUST_ID,
       1                                                      ASAPPL_STD_FLAG,
       a.incr_flag                                            AS INCR_FLAG,
       a.cntr_id                                              AS EXT_KEY,
       a.moneyin_type                                         ASMONEYIN_TYPE,
       a.bank_code                                            AS BANK_CODE,
       a.bank_acc_no                                          AS BANK_ACC_NO,
       a.acc_cust_name                                        AS ACC_CUST_NAME,
       a.appl_date                                            AS APPL_DATE,
       a.lose_reg_num                                         AS LOSE_NUM
FROM   tmp_f_cntr_v8_cntrrangeewith(nolock)
       INNER JOIN std_contract a WITH(nolock)
         ON a.cntr_id = e.cntr_no
       LEFT JOIN cntr_basic_statebwith(nolock)
         ON a.cntr_id = b.cntr_id
            AND b.incr_flag <> '2'
       LEFT JOIN(SELECT cntr_id,
                        Min(ipsn_cust_no)AS ipsn_cust_no,
                        Count(cntr_id)   AS ipsn_num,
                        e.PROV_BRANCH_CODE,
                        e.BATCH_NO
                 FROM   tmp_f_cntr_v8_cntrrangee WITH(nolock)
                        INNER JOIN insured WITH(nolock)
                          ON cntr_id = e.cntr_no
                 WHERE  incr_flag <> '2'
                        AND e.APP_STD_FLAG = '2'
                        AND ipsn_cust_no IS NOT NULL
                 GROUP  BY cntr_id,
                           e.PROV_BRANCH_CODE,
                           e.BATCH_NO)AS d
         ON a.cntr_id = d.cntr_id
            AND d.BATCH_NO = e.BATCH_NO
            AND d.PROV_BRANCH_CODE = e.PROV_BRANCH_CODE
       LEFT JOIN(SELECT cntr_id,
                        Sum(std_premium)      AS std_premium,
                        Sum(face_amnt)        AS face_amnt,
                        Max(stop_moneyin_date)AS stop_moneyin_date,
                        e.PROV_BRANCH_CODE,
                        e.BATCH_NO
                 FROM   tmp_f_cntr_v8_cntrrangee WITH(nolock)
                        INNER JOIN cntr_sub_state WITH(nolock)
                          ON cntr_id = e.cntr_no
                 WHERE  incr_flag <> '2'
                        AND e.APP_STD_FLAG = '2'
                 GROUP  BY cntr_id,
                           e.PROV_BRANCH_CODE,
                           e.BATCH_NO)AS z
         ON a.cntr_id = z.cntr_id
            AND z.BATCH_NO = e.BATCH_NO
            AND z.PROV_BRANCH_CODE = e.PROV_BRANCH_CODE
       LEFT JOIN(SELECT cntr_id,
                        Max(out_force_date)  AS out_force_date,
                        Max(re_in_force_date)AS re_in_force_date,
                        e.PROV_BRANCH_CODE,
                        e.BATCH_NO
                 FROM   tmp_f_cntr_v8_cntrrangee WITH(nolock)
                        INNER JOIN if_of_alt WITH(nolock)
                          ON cntr_id = e.cntr_no
                 WHERE  incr_flag <> '2'
                        AND e.APP_STD_FLAG = '2'
                 GROUP  BY cntr_id,
                           e.PROV_BRANCH_CODE,
                           e.BATCH_NO)AS f
         ON a.cntr_id = f.cntr_id
            AND f.BATCH_NO = e.BATCH_NO
            AND f.PROV_BRANCH_CODE = e.PROV_BRANCH_CODE
       LEFT JOIN(SELECT DISTINCT grant_tranlst.cntr_no,
                                 e.PROV_BRANCH_CODE,
                                 e.BATCH_NO
                 FROM   tmp_f_cntr_v8_cntrrangee WITH(nolock)
                        INNER JOIN grant_tranlst WITH(nolock)
                          ON grant_tranlst.cntr_no = e.cntr_no
                 WHERE  incr_flag <> '2'
                        AND e.APP_STD_FLAG = '2')AS g
         ON a.cntr_no = g.cntr_no
            AND g.BATCH_NO = e.BATCH_NO
            AND g.PROV_BRANCH_CODE = e.PROV_BRANCH_CODE
WHERE  a.cntr_no IS NOT NULL
       AND a.cntr_type <> 'M'
       AND a.pol_code <> '263'
       AND e.APP_STD_FLAG = '2'
       AND Isnumeric(a.renew_flag) = 1
       AND e.PROV_BRANCH_CODE = ?
       AND e.BATCH_NO = ? 

优化后：

SELECT a.appl_no                                              AS APPL_NO,
       a.cntr_no                                              AS CNTR_NO,
       CASE
         WHEN a.mr_type = 'M' THEN a.cntr_no
         ELSE (SELECT TOP 1 cntr_no
               FROM   std_contract WITH(nolock)
               WHERE  a.master_cntr_id = std_contract.cntr_id)
       END                                                    ASMASTER_CNTR_NO,
       a.sg_no                                                ASSG_NO,
       Ltrim(Rtrim(CONVERT(VARCHAR(6), a.mgr_branch_no)))     AS TOWN_BRANCH_CODE,
       dbo.Pub_salesno(a.n_sales_branch_no, a.n_sales_code, 8)AS KEY_SALER_ID,
       Isnull(a.n_sales_branch_no, '!')                       AS BRANCH_NO,
       Isnull(a.n_sales_code, '!')                            AS AGENT_NO,
       a.sales_channel                                        AS SALES_CHANNEL_CODE,
       '!'                                                    ASMANAGE_CHANNEL_CODE,
       '!'                                                    ASCENTER_CODE,
       a.pol_code                                             AS POL_CODE,
       CASE
         WHEN a.moneyin_itrvl = 'W' THEN 1
         ELSE b.moneyin_dur
       END                                                    ASMONEY_DUR,
       a.moneyin_itrvl                                        AS ITRVL_CODE,
       d.ipsn_cust_no                                         AS KEY_IPSN_CUST_ID,
       (SELECT TOP1 hldr_cust_no
        FROM   psn_cntr_holderWITH(nolock)
        WHERE  a.cntr_id = psn_cntr_holder.cntr_id
               AND psn_cntr_holder.incr_flag <> '2'
        ORDER  BY psn_cntr_holder.upd_time DESC)              ASKEY_HLDR_CUST_ID,
       CASE
         WHEN b.insur_dur_unit = 'W' THEN 999
         ELSE b.insur_dur
       END                                                    ASINSUR_DUR,
       a.in_force_date                                        AS IN_FORCE_DATE,
       a.sign_date                                            ASSIGN_DATE,
       a.cntr_term_date                                       AS TERM_DATE,
       CASE
         WHEN a.cg_no IS NULL THEN (SELECT TOP 1 Cast(ext_key12 AS DATETIME)
                                    FROM   insur_appl_cv_task hWITH(nolock)
                                    WHERE  a.appl_no = h.rel_key_no
                                           AND a.cntr_no = h.ext_key9
                                           AND Isdate(h.ext_key12) = 1
                                           AND h.incr_flag <> '2'
                                    ORDER  BY task_seq DESC)
         ELSE (SELECT TOP 1 Cast(ext_key12 AS DATETIME)
               FROM   insur_appl_cv_taskh WITH(nolock)
               WHERE  a.appl_no = h.rel_key_no
                      AND a.cg_no = h.ext_key9
                      AND Isdate(h.ext_key12) = 1
                      AND h.incr_flag <> '2'
               ORDER  BY task_seq DESC)
       END                                                    ASRESP_DATE,
       a.mr_type                                              AS MR_TYPE_CODE,
       a.cntr_stat                                            AS CNTR_STAT,
       a.cntr_type                                            ASCNTR_TYPE_CODE,
       8                                                      ASDATA_SRC_ID,
       z.std_premium                                          AS FACT_STD_PREM,
       z.face_amnt                                            ASFACT_FACE_AMNT,
       d.ipsn_num                                             AS FACT_IPSN_NUM,
       a.cntr_term_cause                                      AS CNTR_STOP_CODE,
       z.stop_moneyin_date                                    AS STOP_MONEYIN_DATE,
       a.cntr_expiry_date                                     AS EXPIRY_DATE,
       f.out_force_date                                       AS OUT_FORCE_DATE,
       f.re_in_force_date                                     ASRE_IN_FORCE_DATE,
       Substring(a.appl_no, 1, 4)                             AS CARD_CODE,
       CASE
         WHEN a.Sales_channelin('OA', 'SP') THEN dbo.Pub_salesno(a.n_sales_branch_no, a.n_sales_code, 8)
         ELSE '-1'
       END                                                    ASKEY_SITE_ID,
       Dateadd(day, -1, auto_in_force_date)                   ASENROLL_DATE,
       (SELECT TOP1 contact_seq
        FROM   psn_cntr_holderWITH(nolock)
        WHERE  a.cntr_id = psn_cntr_holder.cntr_id
               AND psn_cntr_holder.incr_flag <> '2'
        ORDER  BY psn_cntr_holder.upd_timeDESC)               ASCONTACT_SEQ,
       CASE
         WHEN g.cntr_no IS NOT NULL THEN 1
         ELSE 0
       END                                                    ASBANK_FLAG,
       a.renew_flag                                           AS RENEW_FLAG,
       a.renew_times                                          AS RENEW_TIMES,
       '!'                                                    ASKEY_MERG_CUST_ID,
       1                                                      ASAPPL_STD_FLAG,
       a.incr_flag                                            AS INCR_FLAG,
       a.cntr_id                                              AS EXT_KEY,
       a.moneyin_type                                         AS MONEYIN_TYPE,
       a.bank_code                                            AS BANK_CODE,
       a.bank_acc_no                                          ASBANK_ACC_NO,
       a.acc_cust_name                                        AS ACC_CUST_NAME,
       a.appl_date                                            AS APPL_DATE,
       a.lose_reg_num                                         ASLOSE_NUM
FROM   csipdb.std_contract_tmpa WITH(nolock)
       LEFT JOIN csipdb.cntr_basic_state_tmpbWITH(nolock)
         ON a.cntr_id = b.cntr_id
            AND b.incr_flag <> '2'
       LEFT JOIN(SELECT cntr_id,
                        Min(ipsn_cust_no)AS ipsn_cust_no,
                        Count(cntr_id)   AS ipsn_num
                 FROM   csipdb.insured_tmpWITH(nolock)
                 WHERE  incr_flag <> '2'
                        AND ipsn_cust_no IS NOT NULL
                 GROUP  BY cntr_id)AS d
         ON a.cntr_id = d.cntr_id
       LEFT JOIN(SELECT cntr_id,
                        Sum(std_premium)      AS std_premium,
                        Sum(face_amnt)        AS face_amnt,
                        Max(stop_moneyin_date)AS stop_moneyin_date
                 FROM   csipdb.cntr_sub_state_tmpWITH(nolock)
                 WHERE  incr_flag <> '2'
                 GROUP  BY cntr_id)AS z
         ON a.cntr_id = z.cntr_id
       LEFT JOIN(SELECT cntr_id,
                        Max(out_force_date)  AS out_force_date,
                        Max(re_in_force_date)AS re_in_force_date
                 FROM   csipdb.if_of_alt_tmpWITH(nolock)
                 WHERE  incr_flag <> '2'
                 GROUP  BY cntr_id)AS f
         ON a.cntr_id = f.cntr_id
       LEFT JOIN(SELECT DISTINCT cntr_no
                 FROM   csipdb.grant_tranlst_tmp WITH(nolock)
                 WHERE  incr_flag <> '2')AS g
         ON a.cntr_no = g.cntr_no
WHERE  a.cntr_no IS NOT NULL
       AND a.cntr_type <> 'M'
       AND a.pol_code <> '263'
       AND Isnumeric(a.renew_flag) = 1