阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

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

193次阅读
没有评论

共计 882 个字符,预计需要花费 3 分钟才能阅读完成。

最近用户报告某操作极为耗时,经查,是取一个较复杂的视图的记录数引起的,相应 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 即可。

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计882字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中