适用范围

1.DB2等其他关系型数据库。 2.适用于SQL结果集相对比较大，走索引扫描回表成本比较高的SQL. 3.适用于整条SQL 涉及到的字段数量比较少的场景。 例如本例主要SQL： select CASE WHEN CUST_LVL_B is NULL THEN '00' ELSE CUST_LVL_B END AS CUST_LVL_B ,count(1) AS now FROM W_Y_H_CUSTOMER_REPORT WHERE data_date = '2023-02-28' GROUP BY CUST_LVL_B

只涉及到两个字段。

问题概述

1.当一条SQL 返回结果集比较大，SQL性能低下，建索引后走索引扫描，因回表随机读I/O消耗比较大，SQL性能仍然低下，故可以考虑通过建索引/组合索引用索引覆盖来消除回表（即：直接从索引取数。）以提升SQL性能。

2.单表访问的覆盖索引的字段顺序按照SQL 的执行顺序。以本例：先where 条件，再group by 再select list 。（本例Select list 字段同group by 字段。）

3.多表访问的覆盖索引的字段顺序参考：SQL优化 - 表连接的 索引覆盖优化方式 例如本例中： 如下查询： select CASE WHEN CUST_LVL_B is NULL THEN '00' ELSE CUST_LVL_B END AS CUST_LVL_B ,count(1) AS now FROM W_Y_H_CUSTOMER_REPORT WHERE data_date = '2023-02-28' GROUP BY CUST_LVL_B

只建单列index

执行时间：0m2.452s 建组合index 消除回表 执行时间：0m0.464s

详细过程

原始SQL

with TMP1(CUST_LVL_B,CUST_LVL_B_NAME) AS (values('00','无'),('01','小燕'),('02','飞燕'),('03','金燕'),
('04','私行'),('05','钻石'))
SELECT t1.CUST_LVL_B ,
		coalesce(t2.now ,
		0) ,
		coalesce(t2.now ,
		0) - coalesce(t3.lastyear ,
		0) ,
		coalesce(t2.now ,
		0) - coalesce(t4.lastmonth ,
		0)
FROM TMP1 t1
LEFT JOIN (select
	CASE
	WHEN CUST_LVL_B is NULL THEN
	'00'
	ELSE CUST_LVL_B
	END AS CUST_LVL_B ,count(1) AS now
FROM W_Y_H_CUSTOMER_REPORT
WHERE data_date = '2023-02-28' --and MNG_ID = '002338'--and org_id IN ('103200','101233')
GROUP BY  CUST_LVL_B ) t2
	ON t1.CUST_LVL_B = t2.CUST_LVL_B
LEFT JOIN (select
	CASE
	WHEN CUST_LVL_B is NULL THEN
	'00'
	ELSE CUST_LVL_B
	END AS CUST_LVL_B ,count(1) AS lastyear
FROM W_Y_H_CUSTOMER_REPORT
WHERE data_date = '2022-12-31' --and MNG_ID = '002338'--and org_id IN ('103200','101233')
GROUP BY  CUST_LVL_B ) t3
	ON t1.CUST_LVL_B = t3.CUST_LVL_B
LEFT JOIN (select
	CASE
	WHEN CUST_LVL_B is NULL THEN
	'00'
	ELSE CUST_LVL_B
	END AS CUST_LVL_B ,count(1) AS lastmonth
FROM W_Y_H_CUSTOMER_REPORT
WHERE data_date = '2023-01-31' --and MNG_ID = '002338'--and org_id IN ('103200','101233')
GROUP BY  CUST_LVL_B ) t4
	ON t1.CUST_LVL_B = t4.CUST_LVL_B;

原始SQL的执行时间及执行计划

-- 执行时间  0m4.727s
-- 执行计划

Section Code Page = 1208

Estimated Cost = 116021.750000
Estimated Cardinality = 6.000000

Table Constructor
|  6-Row(s)
Right Outer Hash Join
|  Estimated Build Size: 4000   
|  Estimated Probe Size: 4000   
|  Access Table Name = WYHTST.W_Y_H_CUSTOMER_REPORT  ID = 4,1103
|  |  #Columns = 1
|  |  Skip Inserted Rows
|  |  Avoid Locking Committed Data
|  |  Currently Committed for Cursor Stability
|  |  May participate in Scan Sharing structures
|  |  Scan may start anywhere and wrap, for completion
|  |  Fast scan, for purposes of scan sharing management
|  |  Scan can be throttled in scan sharing management
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  |  Sargable Predicate(s)
|  |  |  #Predicates = 1
|  |  |  Hashed Partial Predicate Aggregation
|  |  |  |  Group By
|  |  |  |  Column Function(s)
|  |  |  Insert Into Sorted Temp Table  ID = t1
|  |  |  |  #Columns = 2
|  |  |  |  #Sort Key Columns = 1
|  |  |  |  |  Key 1: (Ascending)
|  |  |  |  Sortheap Allocation Parameters:
|  |  |  |  |  #Rows     = 6.000000
|  |  |  |  |  Row Width = 16
|  |  |  |  Piped
|  Hashed Partial Aggregation Completion
|  |  Group By
|  |  Column Function(s)
|  |  Residual Predicate(s)
|  |  |  Sorted Temp Table Completion  ID = t1
|  Sorted Temp Table Completion  ID = t1
|  Access Temp Table  ID = t1
|  |  #Columns = 2
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  Final Aggregation
|  |  Group By
|  |  Column Function(s)
Right Outer Hash Join
|  Estimated Build Size: 4000   
|  Estimated Probe Size: 4000   
|  Access Table Name = WYHTST.W_Y_H_CUSTOMER_REPORT  ID = 4,1103
|  |  #Columns = 1
|  |  Skip Inserted Rows
|  |  Avoid Locking Committed Data
|  |  Currently Committed for Cursor Stability
|  |  May participate in Scan Sharing structures
|  |  Scan may start anywhere and wrap, for completion
|  |  Fast scan, for purposes of scan sharing management
|  |  Scan can be throttled in scan sharing management
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  |  Sargable Predicate(s)
|  |  |  #Predicates = 1
|  |  |  Hashed Partial Predicate Aggregation
|  |  |  |  Group By
|  |  |  |  Column Function(s)
|  |  |  Insert Into Sorted Temp Table  ID = t2
|  |  |  |  #Columns = 2
|  |  |  |  #Sort Key Columns = 1
|  |  |  |  |  Key 1: (Ascending)
|  |  |  |  Sortheap Allocation Parameters:
|  |  |  |  |  #Rows     = 6.000000
|  |  |  |  |  Row Width = 16
|  |  |  |  Piped
|  Hashed Partial Aggregation Completion
|  |  Group By
|  |  Column Function(s)
|  |  Residual Predicate(s)
|  |  |  Sorted Temp Table Completion  ID = t2