Oracle--17、索引


Oracle的索引相当复杂,由几十种索引。索引的只要功能是进行数据查询优化的,提升数据库操作性能的。为了更好的观察出性能问题,那么下面首先针对于一些查询做一个分析。

范例:查询所有工资高于3000的雇员信息

SELECT * FROM emp WHERE sal>3000 ;

但是现在这个代码严格来讲性能是很差的,如果要想观察出性能(提示信息)问题,那么需要打开查询分析器

CONN sys/change_on_install AS SYSDBA ;

SET AUTOTRACE ON ;

SELECT * FROM myemp ;

此时为了观察出问题只能够在sys用户下观察查询的情况,可以发现此时的查询采用的是“TABLE ACCESS FULL”(全表扫描,逐行扫描)。那么这样的查询有可能出现这样一个问题:

假设现在emp表之中存在有50W条记录,而在第20W条记录之后就不会再有满足条件的数据了(sal>3000,但是如果是全表扫描,意味着,要继续查询后面的30W行记录,而且都是逐行扫描(逐行判断),那么性能一定不高。

此时,如果想要解决此问题,那么唯一的途径就是数据排序,按照工资排序,但是这个时候的排序不是简单的由高到低的排序。那么唯一的方式是按照“树”的形式保存排序数据。

现在假设数据表之中所包含的数据顺序如下:150012502850300024502975110095080016005000”,数据检索的时候一定是查询工资数据,而后由工资数据找到对应的的数据记录。每一个数据对应有一个ROWID

??36.png



那么此时如果存在了这样树的结构,在新建数据表查询的时候就不再需要进行全表扫描的操作了,只需要查询部分数据即可。而我们这样的操作就可以成为索引,在Oracle数据库里面如果想要创建索引,有两种方式:

·当一个列上设置了主键约束或者是唯一约束的时候会自动创建索引;

·用户可以自己通过语法在指定的列上创建索引;

范例:在sal字段上创建索引

CONN scott/tiger ;

CREATE INDEX emp_sal_ind ON emp(sal) ;

 

SELECT * FRO scott.emp WHERE sal >1500 ;

此时索引创建完成。随后再次进行sal查询的时候显示的不再是全表扫描,而是根据一个基数扫描“TABLE ACCESS BY INDEX ROWID INDEX RANGE SCAN”。通过索引的查询可以明显的提升性能,而索引实现的关键是这棵树的维护(树是由Oracle内部自行维护的)。那么继续以本程序为例,如果说此时修改了某些雇员的工资呢?那么这棵树一定要发生变化,所有的数据都需要重新排列。如果数据量很大,则创建索引很费时。那么此时配置的索引不仅没有提升性能,反而降低了性能,所以索引是一种相对的手段,而且永恒都要记住,没有绝对的性能提升方式。

思考题:现在有一个新闻的检索数据库,里面保存了全世界大概100亿条的数据,但是此数据库由于信息的维护问题,所以每秒种都会发出10~20次的更新指令,但是为了保证查询的性能又需要设置索引,请问,你该如何设计此数据库,以达到查询性能优秀,更新的影响又降低到最小?

·矛盾点:如果要想提升查询性能只能够使用索引,但是如果频繁更新,索引又会出现性能严重降低。

·在所有的设计上只有一个原则:“以时间换空间,以空间换时间”。可以准备出两张表,一张表(表A)作为索引的数据,另外一张表(表B)作为接收传入数据;

·白天所有的数据都保存在表B之中(这之中可能包含有一些重复的数据,或一些错误的数据),而在访问量小的时候(1:00~6:00访问量小)将一些新的数据保存在表A之中,而后给它充足的时间进行索引的生成。相当于牺牲了实时性,但是提升了整体的操作性能,而这样的设计在很多地方都可以见到,例如:你们所有APP的访问记录。或者最早的sinablog有一个访问统计。






oracle

2020.11.18 20:49

https://www.meihaocloud.com.com/237.html , 欢迎转载,请在文章页标出原文连接 !


Copyright © 2020 千夕网 联系站长

粤公网安备 44030302001408号 粤ICP备19099833号-1