数据库索引——索引列选择以及空值问题

最新推荐文章于 2022-12-08 11:27:46 发布

weixin_30678821

最新推荐文章于 2022-12-08 11:27:46 发布

阅读量223

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/iuranus/archive/2012/07/15/2592695.html

版权

学习了园子里关于数据库索引的一些文章，我也来摘抄一些关注度不高但对于理解索引概念以及掌握它的用法比较重要的一些知识点。不妥之处还望指正交流。

典型的基于索引的访问路径通常包含下面三步：
1、遍历索引树并在将SQL语句中的谓语应用到索引列后搜集叶子块的行编号。
2、使用行编号从表数据块中获取数据行。
3、在所获取的数据行上应用其余的谓语来得出最终结果集。
如果在第1步中返回了大量的行编号，第2步访问表数据块的代价就会更高，对于来自索引叶子块的每一个行编号，都需要访问表数据块，并且这可能会导致多次物理I/O从而引起性能问题。

所以，选择进行索引的最佳列对于提高SQL访问性能是非常关键的。对于索引列的选择应该与SQL语句中所使用的谓语相匹配，一般有以下需要考虑的内容：
1、如果应用代码访问某张表的时候在某一列上使用了等式或者范围谓语，考虑对这一行进行索引就是一个很好的策略。对于多列索引，引导列应该是在大多数谓语中被使用的列。
2、考虑谓语的基数^[1]以及列的选择度^[2]也是很重要的。例如，如果某个列只有两个唯一值并且是均匀分布的，那么这一列可能就不适合建立B-树索引，因为在这一列上使用等式谓语将会获取50%的数据行。另一方面，如果这个列有两个唯一值但不是均匀分布的，也就是说有一个值仅在很少的数据行中出现且应用使用这个不常出现的列值来访问表，这种情况下最好在这一列上建立索引。
3、举个例子：一张work-in-progress表上经过处理的具有3个唯一值（P、N和E）的Processed列。应用通过谓语Processed='N'来访问这张表。在Processed列中仅有几行状态为'N'的未处理数据，因此通过索引来访问是最优的。反之，谓语为'Y'的查询就不应该使用索引，因为使用这个谓语几乎所有行都将被取出。
4、考虑列的排序，并安排好索引中列值的顺序以使其与应用访问模式相适应。例如，在SH模式下的Sales表中，Prod_id列的选择度为1/2，而Cust_id列的选择度为1/7059。看上去似乎Cust_id列是进行索引更好的候选因为该列的选择度比较低。但是，如果应用声明了Prod_id列上的等式谓语而没有在谓语中声明Cust_id列，那么Cust_id列就不必进行索引，即使Cust_id列具有更好的选择度。如果应用在Prod_id列和Cust_id列都使用了谓语，那么最好是在这两列都建立索引并将Cust_id列作为引导列。而要考虑的是列是否在谓语中使用，而不是完全依赖于列的选择度。
5、还需要考虑索引的成本。插入、删除以及更新（更新索引列）都需要维护索引。如果索引列需要进行大量更新的话，这个索引的维护成本就更高，因为建索引的列的更新会导致索引内部发生删除和插入。这也有可能会引入额外的资源争夺点。
6、考虑列的长度。建有索引的列越长，索引也就越大。索引的成本就可能会超过有索引带来的全部好处。较大的索引尺寸也会增加撤销和重做区的大小。
7、在多列索引中，如果引导列只有很少的唯一值，考虑将该索引建立为压缩索引。这些索引的尺寸将会变得更小，因为压缩索引中不保存重复值。
8、如果谓语在索引列上使用函数，这一列上的索引就不会被选用。例如，谓语to_char(prod_id)=:B1就在Prod_id列上应用了一个to_char函数。不太可能为这个谓语选用Prod_id列上的常规索引，需要在to_char(prod_id)列上来建立一个基于函数的索引。
9、不要在需要大幅修改的列上建立位图索引。位图索引的内部实现更适合于只有很少唯一值的只读列。如果索引进行了更新，位图索引的大小可能会迅速增大。对一个位图索引的过多修改还可能会导致大量的锁资源争夺。位图索引在数据仓库应用中的使用更普遍。

空值问题：
在SQL语句中经常会声明IS NULL谓语。空值不存储在某个单独列的索引中，因此谓语IS NULL将不会使用单列索引。空值索引是存储在多列索引中的，一般通过使用另一个虚拟列来创建多列索引，就可以在IS NULL子句中启用索引。
select * from t1 where n1 is null;　　//未启用
create index t1_n10 on t1(n1,0);　　//使用虚拟列创建多列索引
select * from t1 where n1 is null;　　//启用索引

备注：

[1][2]基数、选择度：基数被定义为一个谓语或执行步骤预期获取的数据行数。考虑一个假设列值均匀分布的列上的简单等式谓语。通过表中的数据行数与表中唯一值的个数相除来算出基数。例如，在Sales表中，有918K行数据而Prod_id列上72个唯一值，因此Prod_id列上的等式谓语基数就是918K/72=12 750。那么，换句话说，谓语Prod_id=:b1预计将取出12750行数据。具有较低基数的列更适合作为索引的候选，因为索引的选择度要更好。对于每个值都是唯一的列，等式谓语的基数为1。选择度是一个从0到1的度量值，简单定义为1/NDV，其中NDV表示唯一值的数目。因此，一个位于的数据可以定义为选择度乘以表中的数据行数。

参考资料：

1、《Oracle SQL高级编程》人民邮电出版社

转载于:https://www.cnblogs.com/iuranus/archive/2012/07/15/2592695.html