首页 > 开发 > 综合 > 正文

创建索引对SQL语句执行的影响

2024-07-21 02:13:16
字体:
来源:转载
供稿:网友

一、创建索引对执行计划的影响

在sql开始执行之前,oracle会确定sql语句的执行计划,并按照执行计划的步骤访问相应的表和索引。

一旦执行计划确定下来,oracle会按照这个执行计划完成sql语句的执行,在sql语句执行开始之后建立的索引不会改变sql语句的执行计划。

因此,创建索引不会对执行计划有任何的影响,也就不会对运行中的sql语句有影响。下面通过一个例子简单验证一下:

sql> create table test (id number, fid number, name varchar2(30), bid_columns char(2000));

table created.

sql> insert into test values (1, 0, 'object', '0');

1 row created.

sql> insert into test values (2, 1, 'table', '0');

1 row created.

sql> insert into test values (3, 1, 'index', '0');

1 row created.

sql> insert into test values (4, 1, 'view', '0');

1 row created.

sql> insert into test values (5, 1, 'synonym', '0');

1 row created.

sql> insert into test values (6, 1, 'source', '0');

1 row created.

sql> insert into test select 20000+rownum, 2, table_name, '0' from dba_tables;

874 rows created.

sql> insert into test select 30000+rownum, 3, index_name, '0' from dba_indexes;

1074 rows created.

sql> insert into test select 40000+rownum, 4, view_name, '0' from dba_views;

2929 rows created.

sql> insert into test select 50000+rownum, 5, table_name, '0' from dba_synonyms;

2437 rows created.

sql> insert into test select 60000+rownum, 6, name, '0' from dba_source;

99717 rows created.

sql> commit;

commit complete.

sql> set timing on

sql> select count(*) from test

2 start with id = 1

3 connect by prior id = fid;

count(*)

----------

107037

elapsed: 00:02:03.84


构造一个树状查询,然后记录这个树状查询的运行时间。

sql> select count(*) from test

2 start with id = 1

3 connect by prior id = fid;

count(*)

----------

107037

elapsed: 00:05:26.15

再次运行查询,在查询运行开始,马上在另一个session创建索引。通过观察执行时间可以发现,创建索引不会对运行中的sql语句带来性能提升,而且很可能由于系统资源的争用造成查询速度变慢。如果在io分布的比较合理的系统中,可以看到,创建索引可以很快完成,而且随后执行同样的查询由于会使用索引,也会很快的返回结构,但是索引的创建不会加快已经处于运行状态的语句的速度。

session2:

sql> set timing on

sql> create index ind_test_id on test(id) tablespace users;

索引已创建。

已用时间:000: 01: 56.92

sql> create index ind_test_fid on test(fid) tablespace users;

索引已创建。

已用时间: 00: 02: 00.57

建立索引后,同样的查询速度得到明显的提升。

sql> select count(*) from test

2 start with id = 1

3 connect by prior id = fid;

count(*)

----------

107037

已用时间: 00: 01: 02.11

上面建立两个索引的语句和查询语句是在单独的session2上运行的。session2上的三个操作——创建两个索引和执行相同的查询语句——都执行完成了,而第一个会话的的运行结果仍然没有返回。

二、创建索引对oracle内部机制的影响

上面通过一个简单的例子说明,创建索引不会改变已经运行的sql的执行计划。但是并不是说,创建索引不能给已经运行的sql语句带来性能的提升。

下面看一个比较特殊的例子:

sql> create table test as select rownum id, a.* from dba_objects a;

表已创建。

sql> create table test1 as select rownum id, rownum fid, a.* from dba_synonyms a;

表已创建。

sql> alter table test add constraint pk_test primary key (id);

表已更改。

sql> alter table test1 add constraint fk_test1_fid foreign key (fid) references test(id);

表已更改。

sql> insert into test1 select * from test1;

已创建1616行。

sql> insert into test1 select * from test1;

已创建3232行。

sql> insert into test1 select * from test1;

已创建6464行。

sql> insert into test1 select * from test1;

已创建12928行。

sql> insert into test1 select * from test1;

已创建25856行。

sql> commit;

提交完成。

sql> delete test1;

已删除51712行。

sql> commit;

提交完成。

sql> set timing on

sql> delete test;

已删除6208行。

已用时间: 00: 00: 17.03

sql> rollback;

回退已完成。

已用时间: 00: 00: 00.06


构造两张表,test1的fid建立了参考test表id列的外键。但是这里并没有在外键列上建立索引。

向test和test1表中填入一定数据量的数据,开始测试。这里测试的是删除test表的执行时间。首先将test1用delete命令删除,提交后计算删除test表的时间,大约需要17秒,然后将数据回滚。

下面准备进行第二次删除测试,所不同的是,在删除操作开始后,马上在另一个session中给外键列增加索引,通过测试可以发现,几乎在索引创建完的同时,第一个session就返回了结果,删除需要的时间缩短到了3秒。

第一个session的删除语句:

sql> delete test;

已删除6208行。

已用时间:? 00: 00: 03.00

第二个session的索引创建语句:

sql> create index ind_test1_fid on test1(fid);

索引已创建

这个测试中索引的创建影响到了已经在运行的sql语句,并明显地提高了执行效率。这个现象和上一篇文章中描述的观点并不冲突。对于用户发出的sql语句,oracle的执行计划是不变的,但是为了执行用户发出的sql语句,oracle在内部做了大量的操作,包括权限的检查、语法的检查、目标对象是否存在,以及维护数据的完整性等等。这个例子中,用户发出的sql语句的执行计划没有改变,发生改变的是oracle内部维护操作语句的执行计划。

如果在第一个session执行delete操作的同时,通过下面的sql语句检查第一个session正在运行的语句,会发现下面的结果(9i及以前版本,如果是10g,则只能看到delete test)。

sql> select sql_text from v$session a, v$sql b

2 where a.sql_hash_value = b.hash_value

3 and a.sql_address = b.address

4 and a.sid = 17;

sql_text

----------------------------------------------------------------------------

select /**//*+ all_rows */ count(1) from "yangtk"."test1" where "fid" = :1

这个sql语句就是oracle用来维护完整性的内部sql。

回想一下我们的例子,建立了外键,但是没有建立索引。当每删除一条test的记录,oracle都要检查这个主键是否在test1中被引用。由于没有索引,oracle只能通过全表扫描来寻找test1中的记录。虽然test1没有记录,但是删除test时使用的是delete而不是truncate,因此test1的高水位线并没有下降,也就是说,每删除一条test的记录,都需要全表扫描一张拥有5万条数据的表,这就是为什么那个delete操作执行很慢的原因。

而我们建立的索引正是加快了这个步骤,oracle内部维护的sql语句在索引可用后选择了索引扫描,因此delete操作在索引创建后迅速返回。

三、小结

创建索引对于用户已发出的正在运行的sql不会带来性能的提升。这是由于用户执行的语句要按照执行计划来运行,而执行计划在运行开始的时候就确定下来了,且不会在sql语句的运行过程中发生变化。

对于sql执行过程中,oracle内部执行的用于维护的sql语句,是有可能从新创建的索引中获得性能提升的。

菜鸟学堂:
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表