收藏私塾在线
 

欢迎您来到私塾在线网!   

请登录! 

免费注册 

交流首页 » 数据库 »为何我的索引无效呢  XML
发表人 内容
Chinajiyonga
[头像]

交流经验:
总积分:100
级别:普通会员
注册时间: 2013-11-15
文章: 2
离线

为何我的索引无效呢

一个古老的问题:为什么我的索引没有被使用呢。可能经常问道,即使是一些数据库专家也不例外。[@Lesue注:所提及的原因不仅仅适用于PostgreSQL]

在这篇文章中,我们将会覆盖最常见的原因,根据概率统计的高低排序。

那些对这不知情的人,你们怎么知道什么时候索引被使用?你可以使用EXPLAIN, EXPLAIN ANALYZE或者PgAdmin的漂亮中执行计划,详情请查看Reading PgAdmin Graphical Explain Plains 来了解。

现在你的查询没有使用索引,为什么呢?你为它做点什么呢?

  1. 问题:静态统计信息过期了。如果你把autovacuuming设置为on时这倒是少见的问题,但是如果你最近才批量加载一个表或者添加很多新索引就试着去查询时,那么这静态统计信息可能会过期。

    解决方案: 
    vacuum analyze verbose sometable
    当你的表很大时,如果你跟我一样的有耐心,那就添加verbose选项主要是因为它方便查看vacumm到底做了什么它确实很棒,可以确认到底发生了什么。如果也想对所有的表都使用,你可以这样: 
    vacuum analyze verbose
  2. 问题: 分析器决定使用扫描表更快而不是扫描索引。发生这种情况有两种原因:a) 你的表相对较小。 b)你索引的字段重复的很多。
    解决方案:记住一点,索引布尔值字段是相当的没用,因为50%的数据是一样的,而另外50%是另一样。然而,它们对部分索引确是很好的候选者。例如,只索引在线(存活)数据。
  3. 问题:你建立的索引与你实际过滤的字段不兼容 。这里有一些这种情况的变体。常见的
    • LIKE "%me"是不会使用索引的,但是LIKE 'me%'是有可能使用索引的
    • 大小写陷阱 - 你可能像这样定义索引:
      CREATE INDEX idx_faults_name ON faults USING btree(fault_name);
      但是你像这样运行查询: 
      SELECT * FROM faults where UPPER(fault_name) LIKE 'CAR%'
      可以像这样修正:
      CREATE INDEX idx_faults_name ON faults USING btree(upper(fault_name));
    • 这个问题我以前也没注意到。通过新闻组可以看到很多其他人的问题很爽,因为你会发现所有这些问题是你知都不知道的。如果你的服务器运行在不是C的本地环境,像上面做还是不起作用。这个在pgsql-novice(pgsql初学者)新闻组上提出来的,由Tom Lane回答了正确的答案。我想很多人都不知道吧[@Lesus 我能说我知道吗,因为在PostgreSQL up and running这本书上提到过,推荐一下,值得一读]
      解决方案:

      CREATE INDEX idx_faults_uname_varchar_pattern ON faults USING btree(upper(fault_name) varchar_pattern_ops);
      

      然而,即使知道了上面的情形,你也应该了解下面在精确的匹配和IN语句中的变体:

      我们还没有证明这个问题是否由数据库编码导致,在8.2和8.3版本中数据本身就不同。好像对于8.3在UTF8下的特殊的数据集任需要精确匹配,然而同样的情况对于8.2在SQL-ASCII下这个varchar_pattern_ops对精确和模糊匹配都行。
      CREATE INDEX idx_faults_uname ON faults USING btree(upper(fault_name));
      如:
    • SELECT fault_name from  faults
      WHERE upper(fault_name) IN('CASCADIA ABDUCTION', 'CABIN FEVER');
    • 对于新手使用不相容的数据类型。如在一个日期字段上创建一个索引,然后使用通过把日期转换为文本来比较。
  4. 并不是所有的索引都会使用。尽管自从PostgreSQL 8.1(含)以上版本支持所谓的位图索引扫描,它通过创建内存位图索引允许在一个查询使用多个索引。如果你的查询命中了很多索引,不要期望索引可能的候选索引都会使用。有时表扫描才是最有效的。
  5. 问题:分析器不是完美的。
    解决方案:  祈祷明天更美好吧。实际上,和其它数据库相比,我已深深的被PostgreSQL的执行能力感动。有人会说,我提出的建议,会让分析器更快[If only I could provide hints, I could make this faster] 。我倾向于将建议视为不好的主意,最好的解决方式仅仅只有让分析器更好。建议的所带来的问题是与数据库之美背道而驰。即数据库比你更懂数据,并且知识时常更新。那么建议很可能很快就变得陈旧,分析器会随着数据库的改变而时常改变策略 ,就导致数据库编程各式各样的,不统一。

推广链接
精品视频课程推荐

Java Web开发理论部分视频教程
系统掌握开发实际的Java Web应用所需的理论知识和技能(Servlet、Jsp、JavaBean、TagLib、EL、JSTL、MVC模式、连接池DataSource、JNDI等知识)。涉及项目:在线投票计数、在线人数统计、登录检查、购物车、商品管理、非法字符替换等多个随堂演示小应用。

Spring3开发实战-独家视频教程
从零到精通Spring3的开发知识;IoC/DI的思想、IoC/DI的运行流程、IoC/DI的开发指导、AOP的思想、AOP的运行流程、AOP应用的设计、Spring对JDBC和Hibernate的支持、Spring的事务、SSH的集成应用

深入浅出学Spring Web MVC视频教程
系统、完整的学习Spring Web MVC开发的知识。包括:Spring Web MVC入门;理解DispatcherServlet;注解式控制器开发详解;数据类型转换;数据格式化;数据验证; 拦截器;对Ajax的支持;文件上传下载;表单标签等内容;最后以一个综合的CRUD带翻页的应用示例来综合所学的知识

 
交流首页 » 数据库
前往:   

关于我们 | 联系我们 | 用户协议 | 私塾在线服务协议 | 版权声明 | 隐私保护

版权所有 Copyright(C)2009-2012 私塾在线学习网