索引覆盖分析

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

以下内容以某个保单增量处理程序逻辑进行分析。当前增量保单事实提取过程分为以下两步：
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