SQL优化之Local index or Global index

0    69    1

👉 本文共约1358个字,系统预计阅读时间或需6分钟。

某物流客户系统查询快递单的SQL,IO消耗为TOP 1:img

SQL代码如下:

select id,

​ op_code,

​ to_char(create_time, :"SYS_B_1") as create_time,

​ ……

from T_EXP_OP_RECORD_CONTAINER A

where status <> :"SYS_B_4" and ID = :1 and rownum = :"SYS_B_5";

其中T_EXP_OP_RECORD_CONTAINER 表是一个在Create_time字段按天一级分和op_code字段按地区二级分区的分区表,ID字段保存的是快递单号信息,字段上存在索引。

SQL代码中出现了"SYS_B_n" 字样的绑定变量,这是因为数据库参数的cursor_sharging被设置为FORCE(强烈建议保持默认值EXACT),SQL中使用的常量值被强制转换成了绑定变量。rownum=后面的常量被强制转换成了绑定变量,这个值根据常识可以判断为1,因为只有1才有意义。

快递单号基本上是唯一的,这样的SQL,正常执行时间应该在1毫秒左右。

而下图使用awrsqrpt收集的SQL实际执行情况是:每次执行耗时1.236秒。

img

SQL执行计划如下:

img

看到上面的执行计划后,就会明白平均执行时间是1秒多就正常了:这个查询要到6030个local index里面检索数据,平均每个local index至少要扫描3个buffers 才能判断记录是否存在,因为有rownum=1 谓词条件,最好的情况是扫描local index的第一个分支就找到了结果,不再继续扫描下去;最差的情况是扫描到local index的最后一个分支才找到结果,或是没有找到结果。

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

一般情况下,local index索引的使用,需要配合分区字段一起做谓词条件,才能只扫描少数的索引分支。而这个SQL由于业务原因,不能增加分区字段作为谓词条件。这种情况就需要将local index改成Global index,才会使SQL性能达到最佳。

但是,因为该表非常庞大(表和索引占用的空间达到T级),需要定期删除(转移)历史分区,只保留最近一年的数据,如果创建的是global index,删除历史分区后,需要对global index进行重建,维护时间窗口很难完成(有多个类似表)。这是个两难的问题。

针对快递业务的特点,老虎刘给出的建议是:

1、仍使用local index,重建表,减少分区数量:按天分区改为按月分区,不要子分区;

2、因为很少有用户会查询1个月以上的快递单,该表只保留最近2个月分区数据,其他数据转移到历史分区,正常情况只需要最多扫描2个分区,而不是原来的6030个分区。

3、通过plsql实现查询:当前分区没有查询到结果,再去查询历史分区。这样也能保证超过2个月的快递单也能正常查询。

总结:

分区表,到底选择global index还是local index,需要根据具体的业务和运维的实际需求而定。不需要删除历史分区数据的分区表,可以创建global index(如基础数据表);需要定期删除历史分区的分区表,最好是创建local index,如果遇到分区字段无法成为查询条件时,建议尽量减少分区数,避免过多的local index 扫描,影响SQL性能。

参考

参考:老虎刘谈SQL优化

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

5 × 5 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部