索引及优化之表分析

本课内容属于Oracle性能调优部分的初级内容，为常规的工作内容，包括 索引的使用和 表分析。

环境准备

概述

针对前面章节中用到的数据库表teachers：

1.建立一个1:1的表记录其银行账户记录表；

2.再建立一个1:n的账户交易信息表，用来记录用户从账户中存钱和取钱的交易记录。

3.编写一个程序，生成该交易表的记录信息，要求至少要达到100万以上记录。

•表“教师账户信息表（TEACHER_ACCOUNT）”

•表“账户交易表（ACCOUNT_TRADE）”

•存储过程“P_INIT_ACCOUNT_TRADE”

 

使用PD设计表及初始化账户数据

 

  

sql代码：

查看复制到剪贴板打印

1. --3.初始化账户信息(节选代码)  
2. delete from  teacher_account;  
3. insert into teacher_account(jsbh,yhkh,dqye) values('001','6002780207013762981',15000.00);  
4. insert into teacher_account(jsbh,yhkh,dqye) values('002','6002780207013762923',15000.00);  
5. insert into teacher_account(jsbh,yhkh,dqye) values('003','6002780207013760181',15000.00);  
6. insert into teacher_account(jsbh,yhkh,dqye) values('004','6002780207013739826',15000.00);  
7. insert into teacher_account(jsbh,yhkh,dqye) values('005','6002780207313762981',15000.00);  
8. insert into teacher_account(jsbh,yhkh,dqye) values('006','6002780207413762923',15000.00);  
9. insert into teacher_account(jsbh,yhkh,dqye) values('007','6002780207513760181',15000.00);  
10. insert into teacher_account(jsbh,yhkh,dqye) values('008','6002780207613739826',15000.00);  
11. insert into teacher_account(jsbh,yhkh,dqye) values('009','6212780207013762981',25000.00);  

存储空间估算及表空间大小调整

1.数据类型所占最大存储容量表

类型

字节数

平均字节数

CHAR(n)

n

n

VARCHAR(n)

n

已经存在数据的表，可以通过统计得出；

刚设计的表，一般取值在30%-60%，建议40%

DATE

7

7

NUMBER(p,s)

最低(p+1)/2+1

一般采用最低

NUMBER

19

 

 

2.估算结果表(ACCOUNT_TRADE)

列名

类型

最大

大小

平均

大小

列名

类型

最大

大小

平均

大小

YHKH

CHAR(19)

19

19

ND

INTEGER

19

4

JYSJ

CHAR(19)

19

19

YD

CHAR(2)

2

2

JYJY

NUMBER(16,2)

9

9

RQ

CHAR(2)

2

2

JYQYE

NUMBER(16,2)

9

9

XSS

CHAR(2)

2

2

JYHYE

NUMBER(16,2)

9

9

FZS

CHAR(2)

2

2

XM

VARCHAR2(10)

10

4

MS

CHAR(2)

2

2

XB

CHAR(1)

1

1

DRJYCS

INTEGER

19

8

XL

VARCHAR2(10)

10

4

XRSJ

DATE

7

7

SSXB

CHAR(3)

3

3

 

   

每行最大大小：144 bytes

每行平均大小：104 bytes

 

3.表空间容量估算

•按最大容量估算，100万行记录存储空间为：

   (144*1000000)/(1024*1024) ≈ 140M

•按平均容量估算，100万行记录存储空间为：

   (106*1000000)/(1024*1024) ≈ 100M

•考虑建立索引占存储容量的20%(此为最大)：

   最大为 30M，平均为20M

• 按200万行最大记录算，考虑还要备份表，则为：

   (140+30) * 2 * 2 = 680M ≈ 700M

 

1.请通过OEM调整用户默认表空间和索引表空间大小分别为500M和200M

2.调整方法本处不再赘述

3.从上一页表格可以看出，存储尚有优化空间，请各位思考哪儿可以再优化？

 

交易数据准备

方法一：通过程序生成：

   1).假定24个老师，共22年每天产生10次交易，则交易数据量为：

      24*22*36*10 = 1,927,200万行；

   2).每日交易的时间和金额随机生成，但日存钱的最大金额为10万，

      取钱的最大金额为余额 – 1；至于存钱还是取钱也是随机生

成；

   3).由于一次写入百万行级的数据，会产生很大的回滚段，易造成

数据库崩溃，故每写完2000行记录提交一次；

   4).附件的程序故意留有取钱不能超过余额的BUG，请学有余力的

      学员修改之；

   5).警告：根据Oracle服务器性能和资源不同，此程序的执行可能

需要长达3天以上的时间才能完成！

  

 

方法二：导入数据：

   1).在数据库中删除表ACCOUNT_TRADE；

   2).将日志模式调整为非归档模式，方法参考前面的课程，原因是：

    a.一次导入的数据量非常大，同样可能产生重做日志和回滚段空

间不足导致的数据库崩溃问题；如果不调整，需要修改系统参

      数 db_recovery_file_dest_size参数值，一般是加大到至少要

      4G，从而要求更多的磁盘空间；

    b.此时归档日志模式导入会比非归档日志模式导入慢很多。

   3).使用IMP命令导入数据，方法也请参考前面的课程内容。  

 

 

为什么需要索引(优点和缺点)

 

 

索引的概念

在关系数据库中，行存储的物理位置是无关紧要的，但当需要找到该行时 行位置却又是至关重要的。Oracle对每一行数据使用一个RowID来标示，其中存 储了行的准确位置（行所在的文件、在该文件中的块、以及块中的行地址）。 索引是一种提供在表中快速定位某一行数据的一种Oracle内部结构。

 

•索引是Oracle内部的一种数据结构，其中存放多行 数据（包括 一个索引的列的拷贝和在 被索引的表的相应的RowID ），可以理解为Oracle表的一个小型化拷贝；

•索引的目的是为了加快数据检索速度。

 

索引的创建、修改和删除

1.索引的创建

CREATE [UNIQUE | BITMAP]  INDEX   index_name  ON table_name

 ( column_name[ ASC | DESC][,c olumn_name [ASC | DESC]] … )

[CLUSTER cluster_name ]    [INITRANS n] [MAXTRANS n] [PCTFREE n]

[STORGE storage]  [TABLESPACE t ablespace_name ]  [NO sort]

 

l 其中UNIQUE指定索引所基于的列(或多列)值必须唯一；

l 默认的索引是非唯一索引;

l BITMAP指定建立位图索引而不是b-tree索引；

l index_name表示创建的索引名字；

l table_name指要创建索引的表；

l cluster_name指创建索引的簇；

l n可以为任意正整数值；

l tablespace_name表示要用于该索引的表空间;

l No sort告诉Oracle该表已经排序因此不需要再重新排序。

 

必须具有CREATE ANY INDEX系统权限

存储参数选项建议使用默认，不要随便修改或征求DBA的意见

 

l 除使用语句、CASE工具外，还可以使用OEM创建索引

l 一般建议使用PD创建索引

l 具体的举例请参考老师的现场讲解

  

 

2.索引的修改

常用语法为：

ALTER  INDEX    index_name       REBUILD;

ALTER  INDEX    index_name       REBUILD   ONLINE;

 

 

l 此语句代表对某个索引进行重构，必须具有ALTER ANY INDEX系统权限

l 不带ONLINE表示只扫描现有的索引块来实现索引的重建

l 带ONLINE表示扫描表而不是扫描现有的索引块来实现索引的重建

l 一般的， 推荐 每隔一个长时期对索引进行重建，即先删除后建立;在此长时期范围内， 可以多次进行索引重构

l 修改索引建议使用语句，此为最快的途径，而不是OEM工具

 

 

3.索引的删除

 

常用语法为：

 

DROP INDEX    index_name    ;

 

l 此语句代表对某个索引进行删除，删除后则相应的索引存储空间会被释放

l 删除索引必须有DROP ANY INDEX系统权限

l 一般建议使用语句删除索引，当然也可以使用PL/SQL Developer、Toad、OEM等 工具，但显然语句语法简单，操作效率也更高

 

 

索引的类型

1.b-tree索引

b-tree索引，即平衡树索引（balanced tree）,是最常见的一种数据索引形式，是通过采用 带有值的顺序的列表范围来组织数据。其中包括三种组件：

1.叶子节点（Leaf node）:包括数据行的键值、键值对应数据行的 ROWID。

2.分支节点（Branch node）:最小的键值前缀，用于在（本块的）两个键值之间做出分支选择，指向包含所查找键值的子块的指针所有的键值-ROWID 对都与其左右的兄弟节点向链接，并按照的顺序排序。

3.根节点（Root node）:一个B树索引只有一个根节点，它实际就是位于树的最顶端的分支节点。

 

 

 

(1).两种节点（块）

  A.分节点用来搜索，叶子节点用来存储数据。根节点存储索引的低层分支节点的数据。 由于所有的叶子节点均会自动的存储成相同的深度，所以称为“平 衡树索引”， 故此，从任何叶子处检索数据消耗的时间都是相同的。

B.平衡树的高度，指的是从根节点都叶子节点所经过的节点数；分支节点的高度等于平衡树的高度减1。

C.对于分支节点块（包括根节点块）来说，其所包含的索引条目都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。每个索引条目（也可以叫做每条记录）都具有两个字段。第一个字段表示当前该分支节点块下面所链接的索引块中所包含的最小键值；第二个字段为四个字节，表示所链接的索引块的地址，该地址指向下面一个索引块。 在一个分支节点块中 所能容纳的记录 行数由数据块大小以及索引键值的长度决定。例如上图的根节点解释如下：

根节点包含5个索引条目(记录），0、201…801为这5个分支节点所链接的节点的最小值，B1、 B2…B5为5个分支节点的地址。

D.对于叶子节点块来说，其所包含的索引条目与分支节点一样，都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。每个索引条目（也可以叫做每条记录）也具有两个字段。第一个字段表示索引的键值，对于单列索引来说是一个值；而对于多列索引来说则是多个值组合在一起的。第二个字段表示键值所对应的记录行的ROWID，该ROWID是记录行在表里的物理地址。 如果索引是创建在非分区表上或者索引是分区表上的本地索引的话，则该ROWID占用6个字 节；如果索引是创建在分区表上的全局索引的话，则该ROWID占用10个字节。

 

 

(2).索引大小的计算

默认的PCTFREE=10%，则代表可用空间为90%；从9i开始，此90%也最多只能使用87%，则不同大小数据块能实际用来存放索引的数据的空间为：

  8k   8*1024*90%*87% ≈  6414个字节

   16k  16*1024*90%*87% ≈  12828个字节

  32k  16*1024*90%*87% ≈  25657个字节

  64k  64*1024*90%*87% ≈  51314个字节

 

以上几种块是常用的几种块

A.叶子节点。 叶子节点中每个索引条目（记录）都会在数据块中占一行空间。

  

B.分支节点。分支节点的一行中所存放的所链接的最小键值所需空间与上面所描述的叶子节点相同。

 

C.举例

   表account_trade的记录数为：1,546,240条，块的大小为64k，如果yhkh上建立一个非分区的索引，则：

 a).一个数据块可使用的字节数为： 64*1024*90%*87% ≈  51314个字节

 b).叶子节点的大小为：3+1+19+1+6=30 个字节

 c).分支节点的大小为： 3+1+19+1+4=28 个字节

 d).一个叶子节点块可存放的索引条目数(记录数)：51314/30 ≈ 1710

 e). 1,546,240条记录需要叶子节点块数： 1546240/1710 ≈ 904

 f).一个分支节点块可存放的索引条目数(记录数)：51314/28 ≈ 1832

 g).904个叶子节点需要的分支节点数为：904/1832 = 1个

 

   如果块的大小为8k，则：

 a).一个数据块可使用的字节数为： 8*1024*90%*87% ≈  6414个字节

 b).叶子节点的大小为：3+1+19+1+6=30 个字节

 c).分支节点的大小为： 3+1+19+1+4=28 个字节

 d).一个叶子节点块可存放的索引条目数(记录数)：6414/30 ≈ 213

 e). 1,546,240条记录需要叶子节点块数： 1546240/213 ≈ 7260

 f).一个分支节点块可存放的索引条目数(记录数)：6414/28 ≈ 229

 g).7260个叶子节点需要的分支节点数为：7260/229 = 32个

 

l结果：索引有两层，第一层有一个根节点和32个分支节点，叶子节点7260个

l注意：在 oracle 的索引中，层级号是倒过来的，也就是说假设某个索引有 N 层，则根节点的层级号为 N ，而根节点下一层的分支节点的层级号为 N-1 ，依此类推。对本例来说， 32 个分支节点所在的层级号为 1 ，而根节点所在的层级号为 2 。

 

(3).索引扫描

Oracle检索一行数据时，通过索引来驱动，称为索引扫描。数据扫描索引值时，所发生的I/O的次数与b-tree树索引的深度数相同。

  如果检索的列仅仅是包含在索引中的列，则Oracle会直接从索引中读取，而不会从表中读取；如果检索的列包含除了索引中以外的列，则Oracle先从索引中读取，然后使用从索引中拿到的rowid从表中读取数据。因此， 典型的扫描方 式是从索引数据块和表数据块中交替读取。

A.全索引扫描(full index scan、FIS)

   全索引扫描只有在CBO(cost-based SQL optimizer)模式下才有效，当优化器认为全索引扫描比全表扫描更有效时，才使用全索引扫描。此时，Oracle会读取全部的索引。一般地，在WHERE子句中含有索引列时可能才会发生全索引扫描，当然，有时在某些特殊情况下，没有上述WHERE子句，也可能发生全索引扫描。

全索引扫描中会忽略掉排序，因为此时读取的数据已经是按照索引键值排序了。

A.全索引扫描(FIS)

 全表扫描：

select jsbh from  teachers where gzje > 3000

 全索引扫描：

select jsbh from  teachers where gzje > 3000 order by jsbh

  

 

B.快速全索引扫描(fast full index scan、FFIS)

   快速全索引扫描仅仅检索索引而不检索表的一种全索引扫描方式，并且Oracle读取索引块时也不再具有特定的排序。

当同时满足下列条件是，Oracle用FFIS替代FIS:

    *.查询的所有列均包含在索引中

*.查询的结果集中没有任何null值(一般是在WHERE子句中使用is not null或者使用not null约束来实现)

 

B.快速全索引扫描(fast full index scan、FFIS)

   在表account_trade上建立一个b-tree索引，包含yhkh,jysj，则：

 FFIS：

    select a.yhkh,a.jysj  from account_trade a  order by a.yhkh;

 全表扫描：

    select a.yhkh,a.jysj  from account_trade a

            where a.jyje > 2000 order by a.yhkh;

C. 索引范围扫描(index range scan)

   索引范围扫描是按顺序的对某个索引进行扫描。当满足下列条件时，会发生索引范围扫描:

    *.在唯一索引上使用范围操作符(>、<、>=、<=、<>、BETWEEN)

      select a.ckh,a.CKMC from course a where a.ckh > 'C005'

*.在组合索引上使用部分列进行查询，导致查处多行

       在表account_trade上建立一个b-tree索引，包含yhkh,jysj，jyje。

select a.yhkh,a.jysj from account_trade a

where a.yhkh = '6002780207313762981'

C. 索引范围扫描(index range scan)

    *.在非唯一索引列上进行的任何查询

     在表account_trade上建立一个b-tree索引，包含nd。

select * from account_trade a where a.nd = 2012;

               select * from account_trade a where a.nd > 2012;

   D. 索引唯一扫描 (index unique scan)

  

D. 索引唯一扫描 (index unique scan)

   区别索引范围扫描的是索引唯一扫描要么返回0行数据要么返回1行数据，不会返回多行数据。Oracle使用索引唯一扫描时，一旦发现时数据，则停止继续扫描，因此效率很高。如果唯一索引是组合索引，则至少需要参与索引的列作为引导列。

select * from productsale_singnle a

 where a.sale_date = '20120103' and a.provice_name = '上海' and

       a.city_name = ‘徐汇’ and a.product_name = ‘iPhone 4’  --索引唯一扫描

 

select * from productsale_singnle a

 where a.sale_date = '20120103' and a.provice_name = '上海' and

       a.city_name = ‘徐汇’ --索引范围扫描

 

select a.ckh,a.CKMC from course a where a.ckh = 'C005‘--索引唯一扫描

 

E.索引跳跃式扫描 (index skip scan)

   当表有一个复合索引，而在查询中有除了索引中第一列的其他列作为条件，并且优化器模式为CBO，这时候查询计划就有可能使用到索引跳跃式扫描 。此外，还可以通过使用提示index_ss(CBO下)来强制使用索引跳跃式扫描。

索引跳跃式扫描是从9i开始新增的，但Oracle没有公布更多的官方的技术 细节，模拟此种索引跟多个条件相关。

 

(4).反转键索引

反转键索引就是将索引键的值反转过来进行索引，索引的结构没什么变化，只是索引值的存储方式相反的一种索引。例如一个索引的键是20，假如在标准的b-tree中存储在该键中的两个十六进制字节为C1、15，则反转键存储的是15、C1。

    Oracle中，对于长事务的大表、存在对相同的磁盘块区重复读写的问题的，随着时间的推移，此种I/O竞争会带来越来越低的效率，如果数据以反转键索引存储，这些数据的值就会与原先存储的数值相反，会有效改善这一问题。

反转键索引不能对位图索引和索引组织表进行反转键处理,详细情况请参阅Oracle官方文档。

 

(5).升序及降序索引

Oracle默认的是升序索引，如果在列名后跟上DESC，则是对该列进行降序索引，降序索引在叶子节点中的存储从左到右是按照从大到小排序的。按列指定降序和升序索引，在复合索引中往往比较有用。

例如将表account_trade上建立的一个b-tree索引修改成，包含yhkh ASC,jyje ASC,jysj DESC。 由于我们经常需要访问的是最新的数据，故按交易 时间降序排列是有意义的。

2.bitmap索引

 

    位图索引适用于低基数(low-cardinality)列,所谓低基数列就是指这个列只有很少的可取值。不同于b-tree索引，位图索引的一个键指向多行，可能数以百计甚至更多（b-tree索引一个键指向一行）。如果更新一个位图索引键，那么这个键指向的数百条记录会与你实际更新的那一行一同被有效地锁定。

   位图索引的适用场景: 
 *. 位图索引是针对那些值不经常改变的字段的

 *. 如果某个字段的值需要频繁更新，那么就不适合在它上面创建位图索引。

 

(1).单表位图索引

在列XB上建立一个位图索引，其结构图如下：

 

值

Row1

Row2

Row3

Row4

…

Row23

Row24

男

1

0

0

1

 

1

0

女

0

1

1

0

 

0

1

 

  

 

 

(2). 位图连接索引

位图连接索引是基于两个或两个以上数据表建立的一种位图索引。对以一个表中某个列的每一个值，索引存储了索引相应行的rowid在索引表中，而标准位图索引是建立在单表上的。

位图连接索引比位图索引更进了一步。这些索引将位图化的列完全从表数据中抽取出来，并将其存储在索引中。其假定条件是这些列集合必须一起查询。同样的，这 也是为数据仓库数据库而设计的。除了在句法最后有一个WHERE子句之外，位图连接索引的创建指令就像创建位图索引的CREATE BITMAP INDEX一样。

位图连接索引一般用于数据仓库，请参阅oracle官方文档。

 

3.基于函数的索引

  如果对一个表的经常查询的WHERE子句中包含基于列的函数或者表达式计算，则可以对该列建立基于函数的索引。函数索引能够计算出函数或表达式的值，并将其保存在索引中。用户创建的函数索引既可以是b-tree类型的，也可以是bitmap 类型的。

只有当查询语句包含该函数或者表达式时，基于函数的索引才会被调用。建立了基于函数的索引索引以后，执行INSERT或者UPDATE语句，数据库同步也需要执行该索引的计算工作。

建设建立索引如下：

    drop   index idx_teachers_ngzje;

    create index idx_teachers_ngzje on teachers(12 * gzje);

 

3.基于函数的索引

  则：

select * from teachers where  12 * gzje < 20000   --调用基于函数的索引

    select * from teachers where   6 * gzje < 10000    --全表扫描

    select avg(12*gzje) from teachers    --调用基于函数的索引

    select avg(6*gzje) from teachers    --全表扫描

  可见Oracle优化器在SELECT子句或者WHERE子句中包含该函数或者表达式时会使用范围索引扫描来调用该索引。

 

怎样选择索引（8建议）

1.由于唯一索引扫描效率最高，因此能使用唯一索引，尽量使用唯一索引；

2.对于取值基数较少的字段，如性别、状态、Check项，尽量使用位图索引；

3.由于NULL值在索引中没有定义，因此建议尽量不要在允许NULL值的列上建立索引，虽然Oracle宣称在允许NULL值的列上位图索引，也是有效的，但我们依然强烈建议可以将null指定为一个默认的值后再建立索引；

4.根据实际情况，一般对于列不超过10个，数据不超过1万行的表不要建立索引，此种情况在应用中往往能占40%以上；

5.一个表中不是建立的索引越多越好，相反过多的索引可能影响整体的性能，至少影响INSERT、DELETE和UPDATE的性能；

6.对于查询中的WHERE子句经常需要使用多个AND条件一起使用时，应建立联合索引，一般情况下，此种联合索引是标示一行唯一记录的，而主键使用代理主键，并且尽量将值少的列放在前面；

7.对于多表联合查询，及表连接的问题，对于连接的字段，需要建立索引；

8.平衡索引与更新的关系。如果一个大表的更新操作多于查询操作，建议可以少建立索引；如果一个大表的查询操作多于更新操作则可以考虑多使用索引。

 

索引之性能体验和监测案例

1.Oracle Explain Plan结果中几个重要名次解释

 （1）COST： Oracle依据其查询优化器选择的执行路径估算的操作开销（也称为成本、耗费）。开销并不决定表的访问操作，其值不具备任何特定的度量单位，仅仅代表该执行计划的加权值，是通过IO_COST和CPU_COST计算得出的(oracle文档中未公布算法）。

 （2）CPU_COST： 查询优化器需要的CPU操作开销估算值，其值与操作所需的机器运转周期成正比。

 （3）IO_COST：查询优化器需要的IO操作开销估算值，其值与读取的数据块成正比。

 

1.Oracle Explain Plan结果中几个重要名次解释

 （4）CARDINALITY： 查询优化器访问的数据行的估算基数。

 （5）TIME：查询优化器消耗的时间估算值（单位为秒）。

2.删除索引

  将表account_trade上的所有索引均删除掉

3.性能监测体验

  使用SQL语句

 

3.性能监测体验--使用SQL语句

 

  

 

  

 

l开销降低47%、执行时间提高31%

l此外还有多种查看方式：SQL窗口查询、PL/SQL DEVELPOER 解释计划窗口、通过toad查看等

 

 

  

 

 

何时需要重建索引

1.叶子节点中浪费的空间大于20%时

lanalyze index IDX_ACCOUNT_TRADE_YHKH validate structure;

lselect a.name,(a.del_lf_rows_len/a.lf_rows_len) * 100 from  index_stats a

 其中del_lf_rows_len是叶子节点中被删除的行数; lf_rows_len是叶子节点的行数

lalter index IDX_ACCOUNT_TRADE_YHKH rebuild或者删除该索引再创建

 

2.监视索引的使用情况，对从来没使用的索引进行删除

 

 

性能优化更多的高级内容超出了本课范围，请参见oracle官方文档

 

 

要点及习题

1.使用索引的优点和缺点分别包括哪些？

2.分别绘图说明b-tree索引和bitmap索引，并解释其存储结构，重点要求阐述清楚b-tree索引两种节点的存储单元和构成以及bitmap索引适用的情况。

3.结合第2题的理论，举例说明表productsale_star如果有10万行记录，其索引PK_productsale_start的存储大小估算是多少？

4.解释oracle查询数据时，一般有几种扫描方式，为什么在大表中要避免全表扫描，而在小表中尽量少使用索引？

5.降序索引和基于函数的函数索引各有什么好处，分别在什么情况下才适合使用？

6.推荐的索引使用方式主要包括哪些（要求至少要回答出5种情况）。

7.在实践中，对索引的监测主要使用的是EXPLAIN PLAN，那么监测的重点是什么？

8.什么情况下一般要求重建索引，怎么重建？

转载请注明【 http://sishuok.com/forum/blogPost/list/6370.html】