【Oracle】也来记一次慢SQL的优化

本文创意来自：微信公众号“占小狼的博客”21年9月13日博文 “SQL要快:记一次慢SQL优化”

下文将用到的表：

create table transfer(
id int,
from_account_id nvarchar2(20),
to_account_id nvarchar2(20),
amount int,
free int,
status int,
comments nvarchar2(20),
create_time timestamp default sysdate,
update_time timestamp default sysdate,
primary key(id));

给表填充两百万测试数据：

insert into transfer(id,from_account_id,to_account_id,amount,status,comments)
select rownum,
       dbms_random.string('*',dbms_random.value(6,20)),
       dbms_random.string('*',dbms_random.value(6,20)),
       dbms_random.value(1,91000),
       dbms_random.value(1,12),
       dbms_random.string('*',dbms_random.value(6,20))
from dual
connect by level<2000001;

然后先找点数据出来下面查询要用：

SQL>  select status,to_account_id from transfer where rownum<10;

    STATUS TO_ACCOUNT_ID
---------- ----------------------------------------
         7 YWSRBVSVWUVKUNZRFNHE
         7 IQNFNHLHQ
         5 QBXOPTUDODVILMSCC
         3 OQZDUIJ
         6 SZSAGORE
         4 UKFCUVCHULUKEQODLP
         2 YAAFOMVO
        10 UQHSEHGAUTNJBINHB
        10 XRXJMDYP

已选择9行。

首先把原作者要优化的目标SQL拿出来跑一遍，现在是没有索引（除了主键索引）的情况。

无index查询
select id,from_account_id,to_account_id,amount,status,comments,create_time,update_time
from transfer
where status=10 and to_account_id='XRXJMDYP' and rownum<1500
order by amount desc,create_time asc

已用时间:  00: 00: 01.99

接近两秒，有点长，这还是Oracle数据库，换MySQL会更长。

现在打算给查询字段status，to_account_id和排序字段amount，create_time加联合索引，因为status的范围是1-12，按上面的筛法，取status=10的数据，那么无论其它条件如何选中的数据必然小于20%（1/12<0.2），那么加上的索引一定会用上的，不会出现所选数据大于两成而导致全表查询的情况发生。

 create index idx_tf_4 on transfer(status,to_account_id,amount,create_time);
 已用时间:  00: 00: 15.36

加索引花的时间不少。

再查一次看优化了多少：

有index查询
select id,from_account_id,to_account_id,amount,status,comments,create_time,update_time
from transfer
where status=7 and to_account_id='YWSRBVSVWUVKUNZRFNHE' and rownum<1500
order by amount desc,create_time asc
 已用时间:  00: 00: 00.06

已经是秒出，看来索引确实奏效了。

让我们再把索引删除。

drop index idx_tf_4;

删除这个索引是因为不想让它影响下面的新查询效果。

 

先筛选后连接，先筛选后排序是基本的优化SQL思想，按这个思想我们可以把符合的记录找出再排序。

select id,from_account_id,to_account_id,amount,status,comments,create_time,update_time
from transfer where id in (select id from transfer where status=10 and to_account_id='XRXJMDYP' and rownum<1500 )
order by amount desc,create_time asc

已用时间:  00: 00: 00.43

看，没加索引，只是用了id身上的固有索引，查询结果就是秒出，从此可以看出“先筛选后连接，先筛选后排序”的有效性。

前文说过Oracle CBO的强大，但CBO毕竟是机器，写出节省时间的SQL还需要人的积极参与。

现在秒出，再加个索引试试：

create index idx_tf_2 on transfer(status,to_account_id);
已用时间:  00: 00: 05.86

花的时间比四字段索引少三分之二，加完此索引后再查一次：

select id,from_account_id,to_account_id,amount,status,comments,create_time,update_time
from transfer where id in (select id from transfer where status=5 and to_account_id='QBXOPTUDODVILMSCC' and rownum<1500 )
order by amount desc,create_time asc
已用时间:  00: 00: 00.17

加索引确实有效果。

删除此索引把前面的索引加上：

SQL> drop index idx_tf_2;

索引已删除。

已用时间:  00: 00: 00.06
SQL> create index idx_tf_4 on transfer(status,to_account_id,amount,create_time);

索引已创建。

已用时间:  00: 00: 09.87

再看查询效果：

select id,from_account_id,to_account_id,amount,status,comments,create_time,update_time
from transfer where id in (select id from transfer where status=6 and to_account_id='SZSAGORE' and rownum<1500 )
order by amount desc,create_time asc
已用时间:  00: 00: 00.17

和加两字段索引差不多，这是因为idx_tf_2里的两字段也出现在了idx_tf_4中。

但是，加索引对插入更新的效果是负面的，换了SQL写法后已经是秒出，未必有再优化的必要，加了索引也只是在查询上锦上添花，插入更新是有损的，因此就本例来说，换新的先筛选后排序的SQL是最优解。

-END-