12月1日,IBM数据库解决方案架构师马远老师,在DBA+社群DB2用户群进行了一次主题为“闲谈索引、谓词和DB2运行架构”的线上分享。小编特别整理出其中精华内容,供大家学习交流。同时,也非常感谢马远老师对DBA+社群给予的大力支持。
数据库解决方案架构师
就职于IBM中国实验室,支持中国区的DB2主要合作伙伴,同时具有多年主机DB2(DB2 for z/OS)和DB2工具的研发经验
DB2索引的节点都是页(page),由一个根节点,若干中间节点(非叶子节点),还有叶子节点构成。
我们先回顾一下DB2所用索引的数据结构--B+数,B+树和B树有一项主要区别是B+树的最底层叶子节点是通过指针连在一起的。
我们来看一个两个键值(key)的索引的叶子节点示例,索引都是按照键值排序的,键值后面挂上RID。
通常我们用三角形来简化索引,三角形的底边就是有序的键值外挂上RID。
这是一个静态的谓词分析,我们不用考虑将这个谓词放在SQL中,也不用考虑是不是有索引可以使用,仅仅是谓词的一个属性。
这里解释一下Indexable,可索引加速,这个谓词如果使用对应的索引,可以在索引上确定一段连续的范围。
Sargable(Search ARGument ABLE),这词很晦涩,只在RDBMS里才用,衡量的标准很简单,对比与Indexable,Sargable的谓词无法确定索引上的一段连续范围。也许是两段,也许是多段。
这就是DB2的运行架构(runtime)。一般大家会比较熟悉存放数据页的缓冲池(Bufferpool)。以一条查询(Query)的运行为例,DB2从表和索引的读取到返回给用户结果集,会从下到上走过BM,IM,DM,RDS这些模块。
我们今天讨论话题的重点是红框中的IM和DM。
从上往下看,最高级的,功能最强的RDS模块,希望下面的DM和IM能帮它尽量多的承担机械化的工作,比如SELECT * FROM T1 WHERE C1 = 5,RDS会把'C1','=','5'扔给DM(1)。由DM把满足条件的每一条记录返回给RDS(4)。如果执行计划中使用了索引,DM还会去调用IM。
再往下看,DM和IM会根据表扫描还是索引扫描的需求(2),让BM提供表的数据页(Table Page)和索引页(Index Page)(3)。BM负责管理缓冲区。
对于数据管理器DM,我们已经知道它是在表扫描的时候工作。但是对于索引管理器,通过什么策略去拿索引页,如何应用谓词?通常情况下,索引会有两种扫描方式,一种是匹配扫描,另一种是筛选扫描。
匹配扫描对应的是索引上一段连续的范围,扫描从根节点开始遍历,找到叶子节点上满足条件的键值,一直到结束的条件,都是我们需要的,直接返回就可以了。
筛选扫描对应不了一段连续的范围,可能是两端或者多段,所以扫描叶子节点之后,不能无脑返回结果,我们还需要在每条索引的键值上应用谓词。
如果我们使用的索引有多个键值,谓词也是多个布尔条件,那么匹配和筛选就可以结合起来使用,我们来看一个例子。
大家注意,拿到一个SQL之后,我们就可以对其中的谓词进行静态分析,像上图中的Indexable和Sargable谓词。和有无索引,是否有统计信息,优化器如何选择执行计划都没有关系。
如果我们建立了索引,并且使用了索引之后,才有所谓的Matching和Screening。
假设使用了索引1,C1,C2上的谓词组合起来可以确定索引上一段连续的范围,所以C1,C2都匹配上了。我们选出一段较窄的范围,再拿C3进行筛选。
假设使用了索引2,因为C2,C1的键值排列顺序,无法通过C2,C1的组合谓词确定一段连续范围。只能有C2匹配上,可以看出需要扫描较大的一段开口范围,然后再通过C1,C3进行筛选。
显而易见,使用索引1的效率要高。
对于布尔条件组合的谓词(AND连接起来的谓词) ,匹配扫描会从索引的第一个键开始,停在第一个非等于的Indexable谓词。
举例:C1 = 1 & C2 = 'B' & C3 > 10 & C 4 = 2, IX(C2,C1,C3,C4),那么C2,C1,C3是匹配,C4是筛选。
我们刚才没提到的剩余的(Residual)谓词,所谓剩余是指DM和IM都无法处理,只能在RDS中处理,这意味着我们需要DM提供更多的记录,相应的,我们也需要读取更多的数据页,所以效率肯定不好。
最好的谓词肯定是Indexable,只有是Indexable的谓词才可能做匹配扫描。但是如果没有对应的索引,或者优化器没有选择对应的索引,Indexable和Sargable的谓词都可以做索引的筛选扫描,或者在DM中进行表扫描。
总结:
尽量写Indexable的谓词
尽量写“=”的操作符
索引的前几个键值尽量包含“=”谓词所在的列
Q1:联合索引的话,DB2会跳过索引的第一个字段而直接使用后面的吗?
A:如果是匹配扫描的话,是不行的。但是筛选扫描可以,因为筛选只是把在键值上进行筛选,避免了表扫描去访问更多的Table Page。
Q2:谓词使用有没有先后顺序?例如:是不是能过滤大数据量的放前面?
A:谓词的使用是有先后顺序的,但是顺序不是这样,一般会先用local predicate,本地的,只涉及本表的,然后才是subquery,join等等。如果都是local predicate,只会有我刚才讲的Matching先进行完再筛选这个顺序。如果是在DM里做的,都是表扫描的话,我们可以认为所有谓词是同时做的。
Q3:那些所谓的“不好的谓词”有什么改造的方法?在不变更表结构的前提下。
A:这个要具体分析,但是可以抛砖引玉。我举个例子,Year(DateCol) = 2005。这是个加了function的谓词,只能在RDS中才能应用。但是我们也可以通过DateCol Between 2005-1-1 and 2005-12-31.这样的改写,写成一个indexable的谓词。
Q4:如果是只有索引1,且查询条条件是 c2=50 and c3 like '%PAM%' 那么这个语句还走索引1吗?
A:这就要看优化器了,如果走的话都会是筛选。
Q5:索引压缩和列存储DB2支持吗?
A:压缩是支持的,列存储也支持,DB2 BLU是一个大功能。
再次感谢IBM数据库解决方案架构师马远老师,对DBA+社群活动给予的大力支持!
“DBA+社群”将陆续在各大城市群进行线上专题分享活动,以后每周一、周三晚上为【DBA+专业群】的固定时间,每周二、周四晚上为【DBA+各城市群】的固定时间,每周五晚上为【DAMS架构师精英群】的固定时间,欢迎大家积极加入我们。无论是内容还是形式,有好的建议我们都会积极采纳。
想入群的小伙伴们请关注DBA+社群微信公众号:dbaplus,回复“加群”即可。
小编精心为大家挑选了近日最受欢迎的几篇热文:
回复001,看丁俊的《【重磅干货】看了此文,Oracle SQL优化文章不必再看!》;
回复002,看《灾备故障上了红头文件,容灾技术到底哪家强?》;
回复003,看吕海波的《去不去O,谁说了算?》;
回复004,看胡怡文《PG,一道横跨oltp到olap的梦想之桥》;
回复005,看付新《达梦专家解读:国产数据库也疯狂》;
回复006,看郭耀龙《假事务之名,深入研究UNDO与REDO》;
回复007,看宋日杰《Oracle后台专家解决library cache锁争用的终极武器》;
回复008,看周俊《被埋没的SQL优化利器——Oracle SQL monitor》;
回复009,看楼方鑫《数据库中间层,这样定制可能更好》;
回复010,看朱贤文《数据库与储存系统》。
DBA+社群是全中国最大的涵盖各种数据库、中间件及架构师线条的微信社群!有100+专家发起人,建有15大城市微信群,6大专业产品群,多达10000+跨界DBA加入队伍。每天1个热议话题,每周2次线上技术分享,不定期线下聚会与原创专家团干货分享,更多精彩,欢迎关注dbaplus微信订阅号!
扫码关注
DBAplus社群
超越DBA圈子,连接的不仅仅是DBA
如果字段的最大可能长度超过255字节,那么长度值可能…
只能说作者太用心了,优秀
感谢详解
一般干个7-8年(即30岁左右),能做到年入40w-50w;有…
230721