Oracle-index索引解读
概述
为什么需要索引数据在磁盘上是以块的形式存储的。为确保对磁盘操作的原子性,访问数据的时候会一并访问所有数据块。磁盘上的这些数据块与链表类似,即它们都包含一个数据段和一个指针,指针指向下一个节点(数据块)的内存地址,而且它们都不需要连续存储(即逻辑上相邻的数据块在物理上可以相隔很远)。 鉴于很多记录只能做到按一个字段排序,所以要查询某个未经排序的字段,就需要使用线性查找,即要访问N/2个数据块,其中N指的是一个表所涵盖的所有数据块。如果该字段是非键字段(也就是说,不包含唯一值),那么就要搜索整个表空间,即要访问全部N个数据块。 然而,对于经过排序的字段,可以使用二分查找,因此只要访问log2 N个数据块。同样,对于已经排过序的非键字段,只要找到更大的值,也就不用再搜索表中的其他数据块了。这样一来,性能就会有实质性的提升。 什么是索引索引是对记录按照多个字段进行排序的一种方式。对表中的某个字段建立索引会创建另一种数据结构,其中保存着字段的值,每个值又指向与它相关的记录。这种索引的数据结构是经过排序的,因而可以对其执行二分查找。 索引的缺点是占用额外的磁盘空间。所以如果为同一个表中的很多字段都建立索引,那这个文件可能会很快膨胀到文件系统规定的上限。 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。 索引的原理首先,来看一个示例数据库表的模式: 注意:这里用char而不用varchar是为了精确地描述数据占用磁盘的大小。 这个示例数据库中包含500万行记录,而且没有建立索引。 接下来我们就分析针对这个表的两个查询:一个查询使用id(经过排序的键字段),另一个查询使用firstName(未经排序的非键字段)。 示例分析一对于这个拥有r = 5 000 000条记录的示例数据库,在磁盘上要为每条记录分配 R = (4+50+50+100)204字节的固定存储空间(4个字段所占空间的总和)。 默认的数据库块大小为 B = 1024字节。于是,我们可计算出这个表的分块因数为 bfr = (B/R) = 1024/204 = 5,即磁盘上每个数据块保存5条记录。那么,保存整个表所需的数据块数就是 N = (r/bfr) = 5000000/5 = 1 000 000。 使用线性查找搜索id字段——这个字段是键字段(每个字段的值唯一),需要访问 N/2 = 500 000个数据块才能找到目标值。不过,因为这个字段是经过排序的,所以可以使用二分查找法,而这样平均只需要访问log2(1000000 )= 19.93 = 20 个块。显然,这会给性能带来极大的提升。 再来看看firstName字段,这个字段是未经排序的,因此不可能使用二分查找,况且这个字段的值也不是唯一的,所以要从表的开头查找末尾,即要访问 N = 1 000 000个数据块。这种情况通过建立索引就能得到改善。 如果一条索引记录只包含索引字段和一个指向原始记录的指针,那么这条记录肯定要比它所指向的包含更多字段的记录更小。也就是说,索引本身占用的磁盘空间比原来的表更少,因此需要遍历的数据块数也比搜索原来的表更少。 以下是firstName字段索引的模式: 示例分析二对于这个拥有r = 5 000 000条记录的示例数据库,每条索引记录要占用 R = 54字节磁盘空间,而且同样使用默认的数据块大小 B = 1024字节。那么索引的分块因数就是 bfr = (B/R) = 1024/54 = 18。最终这个表的索引需要占用 N = (r/bfr) = 5000000/18 = 277 778个数据块。 现在,再搜索firstName字段就可以使用索引来提高性能了。对索引使用二分查找,需要访问 log2 277778 = 18.09 = 19个数据块。再加上为找到实际记录的地址还要访问一个数据块,总共要访问 19 + 1 = 20个数据块,这与搜索未索引的表需要访问277 778个数据块相比,不啻于天壤之别。 什么时候用索引创建索引要额外占用磁盘空间(比如,上面例子中要额外占用277 778个数据块),建立的索引太多可能导致磁盘空间不足。因此,在建立索引时,一定要慎重选择正确的字段。 由于索引只能提高搜索记录中某个匹配字段的速度,因此在执行插入和删除操作的情况下,仅为输出结果而为字段建立索引,就纯粹是浪费磁盘空间和处理时间了;这种情况下不用建立索引。 另外,由于二分查找的原因,数据的基数性(cardinality)或唯一性也非常重要。对基数性为2的字段建立索引,会将数据一分为二,而对基数性为1000的字段,则同样会返回大约1000条记录。在这么低的基数性下,索引的效率将减低至线性查找的水平,而查询优化器会在基数性小于记录数的30%时放弃索引,实际上等于索引纯粹只会浪费空间。 另外需要说明: 创建了索引并不一定就会使用,oracle自动统计表的信息后,决定是否使用索引,表中数据很少时使用全表扫描速度已经很快,没有必要使用索引 索引的语法创建索引CREATE UNIUQE | BITMAP INDEX <schema>.<index_name>
ON <schema>.<table_name>
(<column_name> | <expression> ASC | DESC,<column_name> | <expression> ASC | DESC,...)
TABLESPACE <tablespace_name>
STORAGE <storage_settings>
LOGGING | NOLOGGING
COMPUTE STATISTICS
NOCOMPRESS | COMPRESS<nn>
NOSORT | REVERSE
PARTITION | GLOBAL PARTITION<partition_setting>
参数说明:
修改索引重命名索引alter index index_sno rename to bitmap_index;
合并索引表使用一段时间后在索引中会产生碎片,此时索引效率会降低,可以选择重建索引或者合并索引,合并索引方式更好些,无需额外存储空间,代价较低 alter index index_sno coalesce;
重建索引方式一:删除原来的索引,重新建立索引 方式二:使用rebuild方式 alter index index_sno rebuild [online];
rebuild 和 rebuild online的区别alter index rebuild online:实质上是扫描表而不是扫描现有的索引块来实现索引的重建 alter index rebuild:只扫描现有的索引块来实现索引的重建。 rebuild index online在执行期间不会阻塞DML操作,但在开始和结束阶段,需要请求模式为4的TM锁。因此,如果在rebuild index online开始前或结束时,有其它长时间的事物在运行,很有可能就造成大量的锁等待。也就是说在执行前仍会产生阻塞,应该避免排他锁. 而rebuild index在执行期间会阻塞DML操作,但速度较快. 两者重建索引时的扫描方式不同, 删除索引drop index index_sno;
查看索引查询all_indexes
select index_name,index_type,tablespace_name,uniqueness from all_indexes where table_name = 'tablename';
或者查询user_indexes
select a.* from user_indexes a ;
B树索引的index_type为 NORMAL;而位图索引的index_type类型值为BITMAP 索引分类
B树索引说明B树索引在Oracle中是一个通用索引。在创建索引时它就是默认的索引类型。B树索引可以是一个列的(简单)索引,也可以是组合/复合(多个列)的索引。 B树索引最多可以包括32列。 特点1.oracle中最常用的索引;B树索引就是一颗二叉树;叶子节点(双向链表)包含索引列和指向表中每个匹配行的ROWID值 2.所有叶子节点具有相同的深度,因而不管查询条件怎样,查询速度基本相同 3.能够适应精确查询、模糊查询和比较查询 创建B树索引的单一索引单一索引: Create Index <Index-Name> On <Table_Name>(Column_Name)
B树索引的 复合索引复合索引: Create Index i_deptno_job on emp(deptno,job); —>在emp表的deptno、job列建立索引。
select * from emp where deptno=66 and job='sals' ->走索引。 select * from emp where deptno=66 OR job='sals' ->将进行全表扫描。不走索引 select * from emp where deptno=66 ->走索引。 select * from emp where job='sals' ->进行全表扫描、不走索引。
如果在where 子句中有OR 操作符或单独引用Job 列(索引列的后面列) 则将不会走索引,将会进行全表扫描。 适合使用场景
位图索引说明创建位图索引时,oracle会扫描整张表,并为索引列的每个取值建立一个位图(位图中,对表中每一行使用一位(bit,0或者1)来标识该行是否包含该位图的索引列的取值,如果为1,表示对应的rowid所在的记录包含该位图索引列值),最后通过位图索引中的映射函数完成位到行的ROWID的转换. 位图索引非常适合于决策支持系统(Decision Support System,DSS)和数据仓库,它们不应该用于通过事务处理应用程序访问的表。 它们可以使用较少到中等基数(不同值的数量)的列访问非常大的表。 比如:某个表可能包含一个称为Sex的列,它有两个可能值:男和女。这个基数只为2,如果用户频繁地根据Sex列的值查询该表,这就是位图索引的基列。当一个表内包含了多个位图索引时,您可以体会到位图索引的真正威力。如果有多个可用的位图索引,Oracle就可以合并从每个位图索引得到的结果集,快速删除不必要的数据。 特点
创建例子: Create bitmap Index <Index-Name> On <Table_Name>(Column_Name)
适合使用场景
注意事项
函数索引说明
创建例子: CREATE INDEX index ON table (FUNCTION(column));
举例:
select * from student where upper(name) ='XGJ';
分区索引分区索引就是简单地把一个索引分成多个片断。通过把一个索引分成多个片断,可以访问更小的片断(也更快),并且可以把这些片断分别存放在不同的磁盘驱动器上(避免I/O问题)。 B树和位图索引都可以被分区,而HASH索引不可以被分区。 可以有好几种分区方法:表被分区而索引未被分区;表未被分区而索引被分区;表和索引都被分区。 不管采用哪种方法,都必须使用基于成本的优化器。分区能够提供更多可以提高性能和可维护性的可能性 有两种类型的分区索引:本地分区索引和全局分区索引。 每个类型都有两个子类型,有前缀索引和无前缀索引。表各列上的索引可以有各种类型索引的组合。如果使用了位图索引,就必须是本地索引。 把索引分区最主要的原因是可以减少所需读取的索引的大小,另外把分区放在不同的表空间中可以提高分区的可用性和可靠性。 在使用分区后的表和索引时,Oracle还支持并行查询和并行DML。这样就可以同时执行多个进程,从而加快处理这条语句。 创建索引的一些规则 1. 权衡索引个数与DML之间关系,DML也就是插入、删除数据操作。 2. 把索引与对应的表放在不同的表空间。 3. 最好使用一样大小的块。 4. 如果一个表很大,建立索引的时间很长,因为建立索引也会产生大量的redo信息,所以在创建索引时可以设置不产生或少产生redo信息。只要表数据存在,索引失败了大不了再建,所以可以不需要产生redo信息。 5. 建索引的时候应该根据具体的业务SQL来创建,特别是where条件,还有where条件的顺序,尽量将过滤大范围的放在后面,因为SQL执行是从后往前的。 索引应该经常建在Where 子句经常用到的列上。如果某个大表经常使用某个字段进行查询,并且检索行数小于总表行数的5%。则应该考虑。 6. 如果有两个或者以上的索引,其中有一个唯一性索引,而其他是非唯一,这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引 7. 至少要包含组合索引的第一列(即如果索引建立在多个列上,只有它的第一个列被where子句引用时,优化器才会使用该索引) 8. 小表不要建立索引 9. 对于基数大的列适合建立B树索引,对于基数小的列适合建立位图索引 10. 列中有很多空值,但经常查询该列上非空记录时应该建立索引 11. 经常进行连接查询的列应该创建索引 12. 使用create index时要将最常查询的列放在最前面 13. LONG(可变长字符串数据,最长2G)和LONG RAW(可变长二进制数据,最长2G)列不能创建索引 14. 限制表中索引的数量(创建索引耗费时间,并且随数据量的增大而增大;索引会占用物理空间;当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低了数据的维护速度) 15 .对于两表连接的字段,应该建立索引。如果经常在某表的一个字段进行Order By 则也应该建立索引。 索引失效的情况1.使用不等于操作符(not 、<>、!=)oracle碰到not或者 <> !=会停止使用索引,而采用全表扫描 select * from student where not (score=100);
select * from student where score <> 100;
--替换为
select * from student where score>100 or score <100
通过把不等于操作符改成OR条件,就可以使用索引,以避免全表扫描。 2. 使用IS NULL 或IS NOT NULL使用IS NULL 或IS NOT NULL同样会限制索引的使用。 select * from student where score is not null;
索引上使用空值比较将停止使用索引. 3.使用函数如果不使用基于函数的索引,那么在SQL语句的WHERE子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。 下面的查询不会使用索引(只要它不是基于函数的索引) select empno,ename,deptno from emp where trunc(hiredate)='01-MAY-81';
--把上面的语句改成下面的语句,这样就可以通过索引进行查找。
select empno,deptno from emp where hiredate<(to_date('01-MAY-81')+0.9999);
4.索引列上进行计算索引列上不能进行计算 SELECT Col FROM tbl WHERE col / 10 > 10
则会使索引失效,应该改成 SELECT Col FROM tbl WHERE col > 10 * 10
5.比较不匹配的数据类型假设account_number是一个VARCHAR2类型,在account_number字段上有索引。 select bank_name,address,city,state,zip from banks where account_number = 990354;
Oracle可以自动把where子句变成to_number(account_number)=990354,这样就限制了索引的使用。 改成下面的查询就可以使用索引: select bank_name,zip from banks where account_number ='990354';
特别注意:不匹配的数据类型之间比较会让Oracle自动限制索引的使用,即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。 6.通配符在搜索词首出现时,oracle不能使用索引--我们在name上创建索引;
create index index_name on student('name');
--下面的方式oracle不适用name索引
select * from student where name like '%xgj%';
--如果通配符出现在字符串的其他位置时,优化器能够利用索引;如下:
select * from student where name like 'xgj%';
7.用UNION替换OR(适用于索引列)union:是将两个查询的结果集进行追加在一起,它不会引起列的变化。 由于是追加操作,需要两个结果集的列数应该是相关的,并且相应列的数据类型也应该相当的。 union 返回两个结果集,同时将两个结果集重复的项进行消除。 如果不进行消除,用UNOIN ALL. 通常情况下,用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意,以上规则只针对多个索引列有效. 如果有column没有被索引,查询效率可能会因为你没有选择OR而降低. 在下面的例子中,LOC_ID 和REGION上都建有索引. 高效: SELECT LOC_ID,LOC_DESC,REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID,REGION FROM LOCATION WHERE REGION = “MELBOURNE”
低效: SELECT LOC_ID,REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
如果你坚持要用OR,那就需要返回记录最少的索引列写在最前面. 8. 用EXISTS替代IN、用NOT EXISTS替代NOT IN在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接. 在这种情况下,使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS. 例子: 高效: SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
低效: SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)
Sql优化的一点建议Sql 优化: 当Oracle数据库拿到SQL语句时,其会根据查询优化器分析该语句,并根据分析结果生成查询执行计划。 查询优化器有rule-based-optimizer(基于规则的查询优化器) 和Cost-Based-optimizer(基于成本的查询优化器)。 其中基于规则的查询优化器在10g版本中消失。 对于规则查询,其最后查询的是全表扫描。而CBO则会根据统计信息进行最后的选择。
(编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |