热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
登录
注册
SQL优化案例——全局索引
技术分享/
文章详情
/
SQL优化案例——全局索引
PYZ
2024/11/22
491
2
0
全局索引和局部索引是分区技术中经常需要用到的优化手段，本文结合一个实际案例来讨论这两者的区别和适用场景。
--问题SQL（简化后），耗时1.5-2s左右，执行频率较高，其中T_PART表为分区表(数据量1000w)，字段ID存在索引；T为普通表(数据量100w)，DEPTNO,AGE为组合索引；
SELECT COUNT(*) AS CNT
FROM T_PART TP
LEFT JOIN T
ON TP.ID = T.ID
WHERE T.DEPTNO = ?
AND T.NAME LIKE '%'|| ? || '%'
AND T.AGE > ? ;
输入参数1:10
输入参数2:A
输入参数3:50
执行成功, 执行耗时1秒 65毫秒. 执行号:7226
--执行计划
1 #NSET2: [16, 1->1, 196]
2 #PRJT2: [16, 1->1, 196]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [16, 1->1, 196]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
4 #NEST LOOP INDEX JOIN2: [16, 18->21790, 196]
5 #SLCT2: [11, 18->10877, 148]; (T.NAME LIKE var4 AND T.NAME >= var5 AND T.NAME < var6)
6 #BLKUP2: [11, 9808->58522, 148]; IDX_T_01(T)
7 #SSEK2: [11, 9808->58522, 148]; scan_type(ASC), IDX_T_01(T), scan_range((exp_param(no:0),exp_cast(exp_param(no:2))),(exp_param(no:0),max))
8 #PARALLEL: [1, 1->21790, 48]; scan_type(FULL)
9 #SSEK2: [1, 1->21790, 48]; scan_type(ASC), IDX_TP_ID(T_PART), scan_range[T.ID,T.ID]
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
720137 logical reads
1 physical reads
0 redo size
336 bytes sent to client
114 bytes received from client
2 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
0 io wait time(ms)
1658 exec time(ms)
--ET
OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- -------------------- -------------------- ----------------- --------------- --------------------
PRJT2 7 0% 10 2 4 0 0 0 0 NULL NULL 0
DLCK 32 0% 9 0 2 0 0 0 0 NULL NULL 0
NSET2 58 0% 8 1 3 0 0 0 0 NULL NULL 0
SSEK2 1921 0.12% 7 7 197 0 0 0 0 NULL NULL 0
AAGR2 5109 0.32% 6 3 20867 0 0 0 0 NULL NULL 0
IJI2 7440 0.47% 5 4 52803 0 0 0 0 NULL NULL 0
SLCT2 10211 0.64% 4 5 394 0 0 0 0 NULL NULL 0
PLL 60564 3.80% 3 8 248391 0 0 0 0 NULL NULL 0
BLKUP2 153738 9.66% 2 6 394 0 0 0 0 NULL NULL 0
SSEK2 1353138 84.98% 1 9 216650 0 0 0 0 NULL NULL 0
问题分析
SQL逻辑很简单，按T表的条件先过滤数据，实际过滤出10877条行记录，再与分区表T_PART关联，走了被驱动表T_PART的ID字段索引，进行索引嵌套循环，从et结果看84.98%的代价都在执行计划第9步的SSEK索引定位，总的逻辑读720137，执行耗时1-2s左右，但在高并发场景下这条SQL也是相对低效的，极端情况也会消耗大量的CPU资源，而从执行计划上看执行顺序、关联方式都没问题，只是嵌套循环时索引定位效率低，这种情况是否还有调优空间？答案是肯定的。
优化思路
1、首先查看第9步使用的索引IDX_TP_ID的索引属性，PARTITIONED为YES说明这个索引是个局部索引，而局部索引会在每个分区子表上建立索引，索引数据存储在子表的局部索引上，这说明需要将T表过滤出来的数据，依次到每个子表索引上进行关联匹配，因此检索的效率自然比较差。
SQL> select index_name,index_type,partitioned from dba_indexes where index_name='IDX_TP_ID';
INDEX_NAME INDEX_TYPE PARTITIONED
---------- ---------- -----------
IDX_TP_ID NORMAL YES
2、调整索引结构
（1）修改T_PART表IDX_TP_ID索引为global全局索引
SQL> create or replace index idx_tp_id on t_part(id) global;
--PARTITIONED为NO，已改为全局索引
SQL> select index_name,index_type,partitioned from dba_indexes where index_name='IDX_TP_ID';
INDEX_NAME INDEX_TYPE PARTITIONED
---------- ---------- -----------
IDX_TP_ID NORMAL NO
（2）调整驱动表T的组合索引，避免执行计划第6步的BLKUP回表
SQL> create or replace index idx_t_01 on t(deptno,age,name,id);
问题验证
--优化后执行计划
1 #NSET2: [126, 1->1, 196]
2 #PRJT2: [126, 1->1, 196]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [126, 1->1, 196]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
4 #NEST LOOP INDEX JOIN2: [126, 2924->21790, 196]
5 #SLCT2: [10, 2924->10877, 148]; exp11 > 0
6 #SSEK2: [10, 58488->58522, 148]; scan_type(ASC), IDX_T_01(T), scan_range[('10',50,max,min),('10',max,max,max))
7 #PARALLEL: [24, 1->21790, 48]; scan_type(FULL)
8 #SSEK2: [24, 1->21790, 48]; scan_type(ASC), IDX_TP_ID(T_PART), scan_range[T.ID,T.ID]
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
32751 logical reads
0 physical reads
0 redo size
133 bytes sent to client
215 bytes received from client
1 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
0 io wait time(ms)
97 exec time(ms)
--et
OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
----- -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- -------------------- -------------------- ----------------- --------------- --------------------
PRJT2 4 0% 9 2 4 0 0 0 0 NULL NULL 0
DLCK 8 0.01% 8 0 2 0 0 0 0 NULL NULL 0
NSET2 50 0.05% 7 1 3 0 0 0 0 NULL NULL 0
AAGR2 1564 1.62% 6 3 10880 0 0 0 0 NULL NULL 0
SLCT2 2204 2.28% 5 5 394 0 0 0 0 NULL NULL 0
IJI2 3311 3.43% 4 4 32829 0 0 0 0 NULL NULL 0
SSEK2 4448 4.60% 3 6 197 0 0 0 0 NULL NULL 0
PLL 5418 5.61% 2 7 43508 0 0 0 0 NULL NULL 0
SSEK2 79601 82.40% 1 8 21754 0 0 0 0 NULL NULL 0
通过优化后的执行计划可以看到，虽然执行顺序、关联方式还是一样的，但被驱动表的SSEK效率得到了很大的提升，优化后耗时97ms，逻辑读32751，整体效率提升了20倍左右。
全局索引和局部索引的区别
- 全局索引
全局索引和局部索引均为二级索引，专门用于水平分区表中。当分区子表个数较多、索引列的选择率较好，并且业务SQL没有使用分区裁剪优化等因素时，执行计划会倾向于使用全局索引进行查询。当指定 GLOBAL 关键字时创建的索引即为全局索引。创建全局索引时，会在水平分区表的主表上创建全局索引和每个子表上创建全局本地索引，索引数据存储在主表的全局索引上。但由于全局索引是以整张表的数据为对象而建立的索引，因此全局索引的日常维护成本会高于局部索引。
需要注意的是全局索引包括全局非分区索引和全局分区索引。指定了<PARTITION 子句>的索引即为全局分区索引。只有 DMDPC 支持全局分区索引。
- 局部索引
局部索引是在分区表的每个分区上创建的索引。未指定 GLOBAL 关键字时都会默认创建为局部索引。局部索引暂时不支持分区。创建局部索引时，会在水平分区表的主表上创建局部索引和在每个子表上创建一个子表局部索引。索引数据存储在子表局部索引上。
总结
- 全局索引适用于谓词条件过滤性高或高效的索引范围扫描的场景，索引字段不一定要包含分区键；
- 局部索引适用于语句中包含分区字段谓词条件，通常以等值条件或范围条件进行分区裁剪；
- 当对分区表进行DDL操作时，达梦的全局非分区索引不会像Oracle的全局分区索引一样导致索引失效；
评论
后发表回复
作者
文章
阅读量
获赞
扫一扫
联系客服
联系客服
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1