【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-