sql优化-6(索引)

标签: sql 优化 索引 | 发表时间:2014-06-23 16:57 | 作者:杨振华
出处:http://www.iteye.com
<一>索引开销
1.访问开销
访问集中导致热块竞争
索引查询要尽可能的避免回表,如果不可避免,需要关注聚合因子是否过大。
举个例子:
select a from test_db where b=5
A、假设b上没有索引
那么该条SQL将进行表扫描,扫描所有该表的数据块
从数据块中找到记录,并且进行过滤
可想而知,没有索引将会导致扫描该表所有数据块,性能低下
B、 假设b上有索引
那么该条SQL将进行索引扫描,在索引中找到b=5的位置,一般只需要扫描3个块左右就找到了
获得所有b=5的行的rowid
根据rowid再查询数据(这就是回表),如果数据量少,那么回表次数就少,如果需要的数据全部在索引中,那么就不会再回表了,例如a也在索引中,如果a不在索引中,那么仍然要回表一次查出a。
2.更新开销
在无索引的情况下,表的记录越大,插入的速度只会受到很小的影响,基本不会越慢。
在无索引的情况下,分区表的插入要比普通表更慢,因为插入的数据需要做判断落在哪个分区。有这方面的开销。
在有索引的情况下,表记录越大,索引越多,插入速度越慢。
在有索引的情况下,如果表非常大,索引也很多,在条件允许下,可以将索引先失效再生效,速度可以更快。
在有索引的情况下,分区表如果只是有局部索引,一般来说,分区表的插入速度比普通表更快,因为每个分区的分区索引都比较小,更新的开销自然也小。
3.建立开销
建索引过程产生全表锁
普通的对表建索引将会导致针对该表的更新操作无法进行,需要等待索引建完。更新操作将会被建索引动作阻塞
ONLINE建索引的方式,这种建索引的方式不会阻止针对该表的更新操作,与建普通索引相反的是,ONLINE建索引的动作是反过来被更新操作阻塞。
建索引的过程会产生排序,排序的开销一般比较大,所以要尽量避免在生产缓慢的时候建索引。

<二>索引失效
逻辑失效:
1.类型转换导致索引失效(是存放字符的字段就设varchar2类型,是存放数值的字段就设置number类型,是存放日期的字段就设置date类型)
2.列上使用函数运算导致索引失效,比如substr(列),trunc(列)
3.单独引用符合索引非第一位置的索引列
4.not in,not exist
5.表未进行分析
6.索引遇到like '%LJB' 或者是'%LJB%'的查询,是用不到索引,不过like 'LJB%'是可以用到索引
7.索引能够消除排序,但是如果排序是部分升序部分降序,就必须建对应部分升降序的索引,否则无法用这个来消除排序。
比如order by col1 desc col2 asc,我们可以建(col1 desc,col2 asc)的索引。
物理失效:
1.LONG修改为CLOB,居然会导致其他列的索引失效
2.用ALTER TABLE MOVE的方式来降低高水平,这个操作会导致索引失效
3.alter table t shrink的方式降低表的高水平位,也不会导致索引失效,却无法消除索引的大量空块。最终导致虽然索引不失效,查询依然不用索引
4.分区表操作导致全局索引失效
truncate分区会导致全局索引失效,不会导致局部索引失效。如果truncate 增加update global indexes,全局索引不会失效。
drop分区会导致全局索引失效,局部索引因为drop分区,所以也不存在该分区的局部索引了。如果drop分区增加update global indexes,全局索引不会失效。
split分区会导致全局索引失效,也会导致局部索引失效。如果split分区增加update global indexes,全局索引不会失效。
exchange会导致全局索引失效,不会导致局部索引失效。如果exchange分区增加update global indexes,全局索引不会失效。

<三>索引需注意问题:
1.一个表索引个数不宜过多(低于5);
2.外键应创建索引;
3.组合索引列不宜过多(小于4);
4.大表应该建索引(大于2G);
5.索引聚合因子不应该很大(聚簇因子是指,按照索引列值进行了排序的索引行序和对应表中数据行序的相似程度);
如果越有序,即相邻的键值存储在相同的block,那么这时候ClusteringFactor 的值就越低。
如果不是很有序,即键值是随即的存储在block上,这样在读取键值时,可能就需要一次又一次的去访问相同的block,从而增加了I/O.
Clustering Factor 的计算方式如下:
扫描一个索引(large index range scan)
比较某行的rowid和前一行的rowid,如果这两个rowid不属于同一个数据块,那么cluster factor增加1
整个索引扫描完毕后,就得到了该索引的cluster factor。
如果ClusteringFactor接近于表存储的块数,说明这张表是按照索引字段的顺序存储的。
如果ClusteringFactor接近于行的数量,那说明这张表不是按索引字段顺序存储的。
6.索引是否失效(普通表、分区表);
7.组合索引与单列索引存在交叉情况;
8.索引高度不宜过高(低于5);
9.索引是否设置并行属性(影响查询性能);
10.索引统计信息太旧


已有 0 人发表留言,猛击->> 这里<<-参与讨论


ITeye推荐



相关 [sql 优化 索引] 推荐:

索引SQL优化

- - SQL - 编程语言 - ITeye博客
(一)深入浅出理解索引SQL优化 (转).         实际上,您可以把索引理解为一种特殊的目录. 微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引). 下面,我们举例来说明一下聚集索引和非聚集索引的区别:  .

sql优化-6(索引)

- - 数据库 - ITeye博客
索引查询要尽可能的避免回表,如果不可避免,需要关注聚合因子是否过大. 那么该条SQL将进行表扫描,扫描所有该表的数据块. 从数据块中找到记录,并且进行过滤. 可想而知,没有索引将会导致扫描该表所有数据块,性能低下. 那么该条SQL将进行索引扫描,在索引中找到b=5的位置,一般只需要扫描3个块左右就找到了.

SQL Server--索引

- - CSDN博客推荐文章
         1,概念:  数据库索引是对数据表中一个或多个列的值进行排序的结构,就像一本书的目录一样,索引提供了在行中快速查询特定行的能力..             2.1优点:  1,大大加快搜索数据的速度,这是引入索引的主要原因..                             2,创建唯一性索引,保证数据库表中每一行数据的唯一性..

SQL Server 查询性能优化——堆表、碎片与索引(一)

- - 博客园_首页
      SQL Server在堆表中查询数据时,是不知道到底有多少数据行符合你所指定的查找条件,它将根据指定的查询条件把数据表的全部数据都查找一遍. 如果有可采用的索引,SQL Server只需要在索引层级查找每个索引分页的数据,再抓出所需要的少量数据分页即可. 访问数据表内数以万计的数据分页与只访问少数索引的分页两者间的差异,让索引变成效能调校的最佳工具.

SQL Server 查询性能优化——创建索引原则(一)

- - 博客园_首页
索引是提高查询性能的一个重要工具,索引就是把查询语句所需要的少量数据添加到索引分页中,这样访问数据时只要访问少数索引的分页就可以. 但是索引对于提高查询性能也不是万能的,也不是建立越多的索引就越好. 索引建少了,用WHERE子句找数据效率低,不利于查找数据. 索引建多了,不利于新增、修改和删除等操作,因为做这些操作时,SQL SERVER除了要更新数据表本身,还要连带地立即更新所有的相关索引,而且过多的索引也会浪费硬盘空间.

sql优化策略之索引失效情况二

- - Oracle - 数据库 - ITeye博客
接第一篇索引失效分析: http://grefr.iteye.com/blog/1988446. 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: .

sql优化

- - 数据库 - ITeye博客
是对数据库(数据)进行操作的惟一途径;. 消耗了70%~90%的数据库资源;独立于程序设计逻辑,相对于对程序源代码的优化,对SQL语句的优化在时间成本和风险上的代价都很低;. 可以有不同的写法;易学,难精通. 固定的SQL书写习惯,相同的查询尽量保持相同,存储过程的效率较高. 应该编写与其格式一致的语句,包括字母的大小写、标点符号、换行的位置等都要一致.

sql 优化

- - SQL - 编程语言 - ITeye博客
转:数据库SQL优化大总结之 百万级数据库优化方案. 2014-07-18 09:33 雲霏霏 雲霏霏的博客 字号:. 网上关于SQL优化的教程很多,但是比较杂乱. 近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充. 网上关于SQL优化的教程很多,但是比较杂乱. 近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充.

优化sql查询

- - 数据库 - ITeye博客
1、 首先要搞明白什么叫执行计划. 执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的,比如一条SQL语句如果用来从一个 10万条记录的表中查1条记录,那查询优化器会选择“索引查找”方式,如果该表进行了归档,当前只剩下5000条记录了,那查询优化器就会改变方案,采用 “全表扫描”方式.

sql语句优化

- - 数据库 - ITeye博客
性能不理想的系统中除了一部分是因为应用程序的负载确实超过了服务器的实际处理能力外,更多的是因为系统存在大量的SQL语句需要优化. 为了获得稳定的执行性能,SQL语句越简单越好. 对复杂的SQL语句,要设法对之进行简化. 1)不要有超过5个以上的表连接(JOIN). 2)考虑使用临时表或表变量存放中间结果.