数据库索引笔记(SQL Server, Postgres, Oracle)

声明:本人对数据库并不十分精通,只是在工作中经常需要用到.下面只是一些工作中的思考,可能会有很多错误,希望大家帮忙指出.

1)Hash索引
发现在Postgres中索引是有Hash索引这种类型,但是我一直没有发现在Oracle中有.但是在我的实践经验中发现这个应该是必须有的.今天发现,原来oracle是通过表的类型来实现这个效果,也就是散列聚簇表.

如果查询仅仅是针对特定的列使用=,而不是区间查找(比如大于,小于之类)使用Hash索引会极大的提高性能,因为好的Hash算法可以保证一次查找的时间复杂度为O(1).而使用B树索引的话就需要O(H),H是树的高度.

为什么说在我的实践经验中,这个是必须有的呢,因为我最近就在一个压力比较大的表上(Postgres数据库)通过把Btree索引换成了Hash索引性能提高了快100倍.在产品上线后发现产品在一个功能上有瓶颈,发现是在一个查询语句上,每次查询都要200ms.在查询条件上是有B树索引的.但是是函数索引,把函数索引去除之后发现性能有些微的提升(100ms+).还是没有解决问题.最后我突然想到在哪看过,如果查询仅仅是=,而不是大于,小于或者between,应该使用hash索引.于是尝试换成HASH索引,结果没次查询都只要5ms左右.

2)聚集索引/非聚集索引
以前使用SQLServer 2K数据库时,知道它支持2种索引,一种是聚集索引,一种是非聚集索引.聚集索引就像英文字典一样,数据排列直接安装字典顺序,我猜应该表直接就是索引,索引空间都省下了.但是非聚集索引就像汉语字典了,如果你知道一个字怎么读,你需要根据拼音查找到这个字在字典中的位置,最后再直接定位检索到这个字.

这样就会造成,一个表只能有一个聚集索引,聚集索引的查询非常迅速,因为它"差不多"可以直接定位检索.而非聚集就不行了,至少需要多一次的检索,并且每次检索都是跨越了不同的磁盘Block.

在使用Oracle数据库时,我一直奇怪,Oracle好像大部分都使用B树索引,没发现有聚集索引这个东西,这么简单实用的东西Oracle不可能没有.后来有一次静下心看看Thomas写的那本Oracle书,发现Oracle也是和Hash索引一样,把他通过表的类型实现了,也就是索引组织表.

并且Oracle还有一种索引聚簇表,这个可以在一个数据块上面存储多个数据库的数据.如果这几个表经常需要连接操作,这样放在物理存储接近的地方,肯定会较大的提高性能.

3)B树索引
最初我一直凭感觉的以为这个是二叉树索引,后来知道是平衡树,我又凭感觉的猜测是树的左右2边比较平均,这样就是平衡了,现在想想蛮可笑的.如果你想详细的了解B树结构,可以参考Blog: http://blog.csdn.net/v_july_v/article/details/6530142

B树索引的平衡表现数据增多的时候,数据的查询性能不受影响,表现的比较平稳.因为一个树的查询性能主要取决于树的高度,而B树通过扩展树的广度,来保证树不会太高,这样数据的查询性能就能得到保障.

并且B树的叶子节点在设计的时候还考虑到了,硬盘的Block.因为硬盘在读取数据时是一个Block一个Block读取的.如果你的查询刚好是区间查询,那么就非常合适B书索引,你的一次查询只需要扫描到一个Block或者几个Block就可以了.如果你需要扫描的Block太多了,那么这个查询也许就不合适使用索引了,直接扫描全表"也许更合适".


4)并不是每次查询都能用上索引,或者是有了索引性能一定会提升
只有当你检索的信息,相对于整表比较小时,这个时候才有用.

索引是依赖于数据分析的结果进行的,如果最近表数据变化比较大,那么你可能需要重新对数据进行Analysis.

如果你使用的是组合索引,但是查询的时候又只是使用了部分条件(这种情况有时也会用到索引)

为什么count(*)性能很慢,有索引应该会很快啊?这多半是因为你的索引对应的列是允许为空.如果一个索引对应的列都是空,那么索引是不会收录这一行数据的.

另外还有几种情况会导致索引不会被使用,比如在查询条件中使用函数,或者是查询时类型不匹配.

为什么有了索引,对有的大表进行coutn(*)操作还是很慢
不是


写到这全文完,有点类似读书笔记,希望能帮助一些人.