手机版
你好,游客 登录 注册
背景:
阅读新闻

一次利用位图索引进行SQL优化的案例

[日期:2019-07-26] 来源:Linux社区  作者:wggj [字体: ]

最近用户报告某操作极为耗时,经查,是取一个较复杂的视图的记录数引起的,相应select语句及视图定义类似于:

select count(*) from my_view;

create or replace my_view
as select
  tab1.ID, tab1.f1, tab1.f2,
  tab2.f3, tab2.f4,
  tab3.f5, tab3.f6
from tab1
left join tab2 on tab1.ID=tab2.ID
left join tab3 on tab1.ID=tab3.ID
where tab1.FLAG<>1;

三个表tab1, tab2, tab3的主键均为ID,其中tab1的字段FLAG只有0,1,2等有限个值。当三个表的数据达到2000万级时,耗时在100s以上。分析执行计划,发现因为有了条件“tab1.FLAG<>1”,而需要执行对tab1的全表扫描。

考虑到FLAG的情况,首先在其上创建了一个位图索引以期进行优化。但不幸的是,FLAG=0的记录大约占全部记录的98%以上,FLAG=1的情况不足1%,导致优化器根本不考虑使用该位图索引。

在进行多次尝试之后,终于找到一种方法实现了优化的目标。修改视图定义如下:

create or replace my_view
as select
  tab1.ID, tab1.f1, tab1.f2,
  tab2.f3, tab2.f4,
  tab3.f5, tab3.f6
from tab1
left join tab2 on tab1.ID=tab2.ID
left join tab3 on tab1.ID=tab3.ID
where tab1.ID NOT IN (select ID from tab1 where FLAG=1);

再查看select count(*) from my_view的执行计划,不再有tab1的全表扫描,并且已经利用上了刚创建的位图索引。在2000万级的情况下,用时约为2.1s。用户对此表示认可,问题解决。

 再进一步延伸,对于不支持位图索引的数据库(如MySQL),可以另建一张小表存储FLAG=1的记录,再将视图定义里的条件的子查询改为从该小表取ID即可。

Linux公社的RSS地址https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址https://www.linuxidc.com/Linux/2019-07/159595.htm

linux
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款