SQL调优之十:访问路径(Access Path)四
一,位图索引访问路径
对于一个普通的B树索引,一个索引条目指向单一行。
在位图索引里面,键值是索引的数据和rowid范围的结合。
数据库会为每一个索引键存储最少一个位图。位图里面的值是一系列0和1组成的值,指向一个rowid范围内的某一行。
索引,在一个位图索引里面,一个索引条目指向的是一个行集而不是单单一行数据。
位图索引和B树索引的不同
位图索引使用了和B树索引不同的键,但是也是以B树索引的结构存储的。
索引类型 | 键值 | 数据 | 举例 |
唯一型B树 | 仅仅是被索引的数据 | rowid | 在employee.employy_id上创建的索引的一个条目里,101是键值,rowid则是数据 |
非唯一型B树 | 被索引的数据加上rowid | none | 在employee.last_name上的索引里,Smith,AAAPvCAAFAAAAFaAAa就是索引键值, 没有数据 |
位图 | 被索引的数据加上rowid范围 | bitmap | 在customers.cust_gender上的位图索引里,M,low-rowid,high-rowid是键值, 而1000101010101010则是数据 |
PS: 键值是指索引的值,也就是索引中用于排序,索引的值
因为位图索引也是以B树索引的结构存储的,因此数据库可以快速根据键值的第一部分来定位叶子块,也就是获取rowid的范围,以及位图(1000101010101010)。
使用位图索引的目的
位图索引适合于那些非重复值少,并且更新频率低的数据。
数据的非重复值很少的时候,它和总的行数之间的关系就很小,比如说1000行数据里面,非重复值只有两个,那么就不能说这两个值只占总行数的千分之二。最常见的就是性别列。
同时,通过压缩技术,位图索引可以以很少的I/O来返回很多rowid。
位图索引可以用于在数据仓库中加快临时的查询。
在以下情况的时候,位图索引尤其有用:
1,WHERE子句里面包含了多个条件,在这种情况下,在访问表本身之前,数据库已经可以过滤出符合某些条件的行
2,低基数的列上的And,Or和Not操作,数据库可以很快的从位图索引上合并位图,比如说以下情况,M假设是1,数据库可以很快地把1转换成相应的rowid。
SELECT *
FROM customers
WHERE cust_gender = 'M'
AND cust_marital_status = 'single'
3,count函数,不需要扫描表,数据库直接扫描位图索引即可
4,查询null值,和B树索引不同的是,位图索引可以包含Null值。因此可以使用位图索引来查询一个包含null的列的count值。
位图索引对于频繁更新的表用处不大。原因是一个索引条目指向了很多行。所以如果一个会话更新了索引的数据,那么数据库没办法只锁住位图的一个位,相反的,数据库要锁住整个索引条目,也就是这个条目指向的所有行。
比如说,如果把cust_marital_status从single更新为married,那么数据库必须获得位图索引里面,single条目和married条目的排他性访问。包含这两个值的所有行都没办法被修改,直到提交或者回滚。
位图和rowid
对于位图里面的某个值来说,如果它匹配位图的条件,那么在位图里它的值就是1,如果不匹配,值就是0。
一个位图条目包含了被索引的值,ROWID范围(开始和结束的rowid) 以及一个位图。
位图里面的每一个0或者1都是rowid范围里的偏移量,能够匹配表里面可能存在的行,即使实际上并没有这一行。(这个可真绕)
因为一个数据块可能存储的行数是预估的,数据库可以使用范围端点来决定范围内任意一行的rowid。
事实上,位图所使用的优化器的索引算法会限制oracle数据库所假设的每一个数据块里面存储的行数,行数少,则rowid范围小,位图记录就少,位图的大小就会变小。
上面这个图就是位图记录,第一列是值,2,3列则是rowid的起始跟结束范围,然后在这个范围里有6个rowid,对应6行。
可能得值有3个,根据0或者1来进行匹配,匹配到的再从范围里获取到相应的rowid。
从图里面还能看出,位图是可以对null值进行索引的,所以位图索引可以用于某些聚合函数,比如说count。
位图连接索引
位图连接索引是建立在两个或者更多个表的连接上的位图索引。优化器可以使用这个索引来减少或者消除数据量。
这种索引比物化连接视图在存储上更高效。
例子:
CREATE BITMAP INDEX cust_sales_bji ON sales(c.cust_city)
FROM sales s, customers c
WHERE c.cust_id = s.cust_id LOCAL;
这种匹配的是在两个表中都有的,同时还是以位图索引形式存储。
针对这种索引的查询,执行计划显示如下:
SELECT COUNT (*)
FROM sales s, customers c
WHERE c.cust_id = s.cust_id
AND c.cust_city = 'Smithville';
位图存储
位图索引是以B树索引的结构,Branch数据块,leaf数据块。
比如说,如果customers.cust_marital_status列有12个非重复值,那么一个branch数据块可能会包含married,rowid-range和single,rowid-range,然后另一个branch数据块则包含widowed,rowid-range。
或者,一个branch数据块可以直接指向一个包含了12个数值的leaf数据块。
每一个被索引的列的值可能有一个或多个位图片,每一个都有它自己的rowid范围,包含了在一个或多个数据分区(extent)里相连的行集。
数据库可以使用位图片来把一个大的索引条目拆分开来。比如说值为married的人数特别多,包含了特别多记录的时候。
比如说,数据库可以把单个索引条目拆分成三个位图片,其中头两个位图片在同一个分区里面,但是不同的数据块,而另一个位图片则在另一个分区里的数据块。
另外,数据块可以对连续的0值进行压缩来减少空间的使用。
二,Bitmap Conversion to Rowid
位图转换会在位图的条目和表行之间进行翻译。
它可以把一个位图记录转换成一个行(to rowid),也可以把行转换成条目(from rowid)。
优化器什么时候会考虑使用位图转换ROWID?
当优化器需要通过位图索引从表里返回行数据的时候,就会发生这个操作。
Bitmap Conversion to Rowid的工作机制
理论上来说,位图可以被认为是一个表。只不过其存储的值是0跟1。
位图转换rowid是通过使用内部算法,把一个位图记录,匹配的1,转换成比如数据库表的第几个数据块的第几行数据。
例子:
SELECT cust_last_name, cust_first_name
FROM customers
WHERE cust_year_of_birth < 1918;
三,Bitmap Index Single Value
位图索引单值扫描,这种方式是使用位图索引来索取单个键值。
优化器什么时候会考虑使用Bitmap Index Single Value?
谓词包含等式操作
Bitmap Index Single Value的工作机制
查询优化器会扫描单个位图,找到包含值为1的位置。然后数据库把1转换成rowid,接着使用rowid找到行数据。
数据库仅仅需要扫描单个位图,比如说,下图是为了索引widowed这个值的位图(分成了两个位图片),数据库可以搜索这个位图值为1的记录来获取相对应的rowid。
例子:
SELECT *
FROM customers
WHERE cust_marital_status = 'Widowed';
四,Bitmap Index Range Scans
位图索引范围扫描,顾名思义,使用位图来索取范围数据。
优化器什么时候会考虑使用Bitmap Index Range Scans?
当谓词指定了索取范围数据的时候,类比B树索引范围扫描。
Bitmap Index Range Scans的工作机制
同样是类似于B树索引范围扫描。
比如说,下图需要查询出生日期在1917年之前的所有用户,数据库可以扫描键值低于1917的位图条目,获取所有值为1的数据。
例子:
SELECT cust_last_name, cust_first_name
FROM customers
WHERE cust_year_of_birth < 1918;
五,Bitmap Merge
这个访问方式合并多个位图然后返回单个位图作为结果。
优化器什么时候会考虑使用Bitmap Merge?
当优化器需要对位图索引范围扫描所返回的位图进行合并的时候。
Bitmap Merge的工作机制
Bitmap Merge相当于在两个位图之间使用or操作。
比如说,查询所有出生日期在1918年之前的用户,那么就要查询1915,1916,1917中匹配条件的位图。
那么就会对这三个位图的记录进行或or操作,只要有一列式1就返回。如下图所示:
例子:
SELECT cust_last_name, cust_first_name
FROM customers
WHERE cust_gender = 'F'
AND cust_year_of_birth < 1918;
从以下的执行计划来看,所谓的位图合并,就是将上一步位图范围扫描返回的多个位图,合并成单一的一个位图,然后传递给下一步操作。