Chinaunix首页 | 论坛 | 博客
  • 博客访问: 204325
  • 博文数量: 21
  • 博客积分: 1546
  • 博客等级: 上尉
  • 技术积分: 290
  • 用 户 组: 普通用户
  • 注册时间: 2006-10-10 14:54
文章分类

全部博文(21)

文章存档

2020年(1)

2019年(3)

2015年(2)

2014年(1)

2011年(1)

2009年(7)

2008年(4)

2007年(2)

我的朋友

分类: Mysql/postgreSQL

2015-05-28 15:37:04

熟悉数据库的大家知道,一个属性要不要建立btree索引,判断条件是其键值分布是否够离散,比如主键、唯一键,可以建立索引。如果这个属性有大量重复的值,则建立索引没有意义。

举个例子:一个表含有sex(性别)字段,只包含M、F两个值,假设这张表有10000条记录,可想而知,M或F各占5000左右,此时,你即使在该性别字段建立索引,查询语句select ... where sex='M'也是利用不到此索引的,因为该索引中含有大量重复的M值,与其去搜索索引,还不如来个全盘扫描。所以在有大量重复键值的字段建立索引没有什么意义。

现实例子中,常会碰到键值分布不均匀的属性,如表test一个名为FLAG的属性,有A、B、C三个值,其中值为A的记录占95%,值为B的占3%,C占2%。在FLAG上建立索引,搜索FLAG=‘B’或‘C’可利用到此索引,而搜索FLAG=‘A’则因有大量的重复值而利用不到此索引。也就是说此索引有95%的内容是无效的,白白浪费了存储等资源。

PostgreSQL有种索引,叫Partial Index(部分索引)可以很好的解决以上问题, 它是在表的纵向子集上建立索引,可以认为是带where表达式的索引。
如以上例子,我们在FLAG上建立部分索引flag_index:
create index flag_index on test ( flag ) where flag='B' or flag='C'
这个索引只包含B和C值,不包含A值,大大减少了存储空间,提高了运行效率。

只有匹配where 表达式的查询语句才可利用到此部分索引,如查询语句
select ...where flag='B'   。。。。可利用到索引
select ...where flag='C'   。。。。可利用到索引
select ...where flag='A'   。。。。利用不到索引。

注意一点:由于PostgreSQL在决定是否使用索引,是在SQL语句的分析阶段,而不是在运行阶段,假如在分析阶段还无法确定其值的语句,它无法匹配到where语句,也是无法用到索引的,即使运行结果是匹配到where 语句。

如一个例子 select .... where flag = FUNC(.....)  , func是个很复杂的表达式,这个表达式结果只有设计者知道只返回B值,然而在分析阶段,Postgresql算不出其值,此时postgresql也是无法利用其索引,结果发现此语句运行得很慢,explain一下发现是全表扫描。

解决的方法是显式地加上索引中的where 语句:
select .... where flag = FUNC(.....)  and FLAG='B'  (假设FUNC只返回B值)

类似的情况还有某些JOIN、LEFT JOIN的右表,请显的加上索引中的where 语句。



 
阅读(2806) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~