通过索引提升SQL性能案例一则

浅浅的花香味﹌ 2022-10-29 03:18 10阅读 0赞

最近有个应用，前端调用后台的一个逻辑很慢，请开发提供了对应逻辑使用的SQL，进行脱敏，示例如下，

select t.AGENT as agent,
            nvl(sum(case
            when t.operation_type = 'A' then 1 else 0 end),0) as
            DflCount,
            nvl(sum(case
            when t.operation_type = 'B' then 1 else 0 end),0) as
            IfCount,
            nvl(sum(case
            when t.operation_type = 'C' then 1 else 0 end),0) as
            AecCount,
            nvl(sum(case
            when t.operation_type = 'D' then 1 else 0 end),0) as
            BsCount        
            from OP_LOG t        
            where 
            t.code  = 'AA'
            and t.ORI_CODE =  'ABC'
            and t.T_DATE BETWEEN to_date('20201209','yyyymmdd') and to_date('20201209','yyyymmdd')
            and IS_VALID = 1
            and t.operation_type in ('A','B','C','D')        
            group by t.agent         
            order by agent;

生产环境这张表的统计信息如下，3000多万的数据，

NUM_ROWS    BLOCKS  AVG_ROW_LEN
    36181236    866883  118

这张表存在一个主键唯一索引，两个非唯一索引，

SQL> create index idx_op_log_01 on op_log(t_date);
    Index created.
    
    SQL> create index idx_op_log_03 on op_log(t_no, ori_code, t_code, t_date);
    Index created.
    
    SQL> alter table op_log add constraint pk_op_log_id primary key (id);
    Table altered.

这三个索引对应的统计信息，

BLEVEL    LEAF_BLOCKS DISTINCT_KEYS   AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTORY  NUM_ROWS
    3         277636      152             1793                    16031                   2436821             30765015
    3         270751      1429772         1                       23                      33314457            34989358
    3         177428      38178216        1                       1                       37102260            38178216

生产环境SQL的执行计划如下所示，可以看到，选择的是idx\_op\_log\_03的索引跳跃扫描，成本值是2585，buffer是115，

------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name                          | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
    ------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                               |      1 |        |  2585 (100)|          |      0 |00:00:00.01 |     115 |
    |   1 |  SORT GROUP BY               |                               |      1 |      4 |  2585   (1)| 00:00:32 |      0 |00:00:00.01 |     115 |
    |*  2 |   TABLE ACCESS BY INDEX ROWID| OP_LOG                        |      1 |      4 |  2583   (0)| 00:00:32 |      0 |00:00:00.01 |     115 |
    |*  3 |    INDEX SKIP SCAN           | IDX_OP_LOG_03                 |      1 |     25 |  2560   (0)| 00:00:31 |      0 |00:00:00.01 |     115 |
    ------------------------------------------------------------------------------------------------------------------------------------------------
    2 - filter((INTERNAL_FUNCTION("T"."OPERATION_TYPE") AND "IS_VALID"=1))
       3 - access("T"."ORI_CODE"='ABC' AND "T"."T_CODE"='A' AND "T"."T_DATE"=TO_DATE(' 2020-12-09
                  00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
           filter(("T"."T_DATE"=TO_DATE(' 2020-12-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."ORI_CODE"='ABC' AND
                  "T"."T_CODE"='A'))

这张表是3000万的数据量，单表查询，一个主键索引，一个t\_date单键值索引，一个复合索引(t\_no，ori\_code，t\_code，t\_date)。原始SQL的条件中包含了idx\_op\_log\_03复合索引除前导列的另外三个字段，常规上可供Oracle优化器选择的执行计划，一个是全表扫描，一个就是idx\_op\_log\_03的索引跳跃扫描，经过计算，Oracle认为索引跳跃扫描的成本值更低，这是他认为当前情况下最优的执行计划。

全表扫描，会多块读3000万数据所有大约86万个数据块，索引跳跃扫描，其实是构建遍历了所有前导列值的索引，伪代码如下，大约27万个数据块，而且还得单块读回表，实际读的数据块会更高，而且t\_no的dinstinct，不是很少，因此这两种执行计划，算是半斤八两，

...
    where 
    t_no='1' and ori_code='A' and t_code='ABC' and t_date ...
    and 
    t_no='2' and ori_code='A' and t_code='ABC' and t_date ...
    and
    ...
    and
    t_no='...' and ori_code='A' and t_code='ABC' and t_date ...

这个查询条件还是很简单的，如果根据当前的条件，创建新的索引，会提高效率么？

如果是DG，通常情况，我们可以开启备库的snapshot，模拟创建索引，测试他的效率，但是现在不让做，只可以在测试系统进行测试了。

测试系统这张表的数据量就几万，如果要贴近生产，增加数据量是一种形式，但是增加到几千万的量级，一个是需要消耗时间，另一个就是消耗空间。其实，Oracle是通过统计信息计算执行计划成本值的，因此我们可以借助改造统计信息，来达到让Oracle认为这是“生产”量级的计算，验证我们的调整。

首先，通过dbms\_stats.set\_table\_stats设置表的统计信息值，

SQL> exec dbms_stats.set_table_stats('BISAL','OP_LOG',numrows=>36181263,numblks=>866883);      
    
    PL/SQL procedure successfully completed.

可以看到，当前表的统计信息和生产基本一致了，

SQL> select table_name,num_rows,blocks,avg_row_len from user_tables
      2  where table_name='OP_LOG';
    
    TABLE_NAME       NUM_ROWS   BLOCKS   AVG_ROW_LEN
    ---------------- ---------- -------- -----------
    OP_LOG           36181263   866883   129

接着，通过dbms\_stats.set\_index\_stats设置idx\_op\_log\_03的索引统计信息，

SQL> EXEC dbms_stats.set_index_stats('BISAL','IDX_OP_LOG_03',indlevel=>3,numlblks=>270751,numdist=>1429772,avglblk=>1,avgdblk=>23,clstfct=>33314457,numrows=>34989358);
    
    PL/SQL procedure successfully completed.

执行SQL，在测试环境下，其执行计划和生产的相同，都是idx\_op\_log\_03的索引跳跃扫描。根据SQL条件，我们会创建两个索引，索引01是调整了原始索引03的字段顺序，将t\_no置在尾部，索引02是删除t\_no，增加了agent，

1. t\_code, ori\_code, t\_date, t\_no

2. t\_code, ori\_code, t\_date, agent

SQL> create index idx_op_log_01 on op_log(t_code, ori_code, t_date, t_no);
    
    Index created.
    
    SQL> create index idx_op_log_02 on op_log(t_code, ori_code, t_date, agent);
    
    Index created.

执行SQL，他采用的是idx\_op\_log\_02，cost是7，

---------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name                    | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
    ---------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                         |      1 |        |     7 (100)|          |      0 |00:00:00.01 |       2 |      1 |
    |   1 |  SORT GROUP BY               |                         |      1 |    111 |     7  (15)| 00:00:01 |      0 |00:00:00.01 |       2 |      1 |
    |*  2 |   TABLE ACCESS BY INDEX ROWID| OP_LOG                  |      1 |   1545 |     6   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      1 |
    |*  3 |    INDEX RANGE SCAN          | IDX_OP_LOG_02           |      1 |  10817 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      1 |
    ---------------------------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter((INTERNAL_FUNCTION("T"."OPERATION_TYPE") AND "IS_VALID"=1))
       3 - access("T"."T_CODE"='A' AND "T"."ORI_CODE"='ABC' AND "T"."T_DATE"=TO_DATE(' 2020-12-09 00:00:00',
                  'syyyy-mm-dd hh24:mi:ss'))

为了对比，通过HINT使用idx\_op\_log\_01索引，cost是8，01和02索引相差很小，

---------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name                    | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
    ---------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                         |      1 |        |     8 (100)|          |      0 |00:00:00.01 |       2 |      1 |
    |   1 |  SORT GROUP BY               |                         |      1 |    111 |     8  (13)| 00:00:01 |      0 |00:00:00.01 |       2 |      1 |
    |*  2 |   TABLE ACCESS BY INDEX ROWID| OP_LOG                  |      1 |   1545 |     7   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      1 |
    |*  3 |    INDEX RANGE SCAN          | IDX_OP_LOG_01           |      1 |  10817 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      1 |
    ---------------------------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter((INTERNAL_FUNCTION("T".OPERATION_TYPE") AND "IS_VALID"=1))
       3 - access("T"."T_CODE"='A' AND "T"."ORI_CODE"='ABC' AND "T"."T_DATE"=TO_DATE(' 2020-12-09 00:00:00',
                  'syyyy-mm-dd hh24:mi:ss'))

因此，单就这个场景，最佳选择是idx\_op\_log\_02，但是保险起见，还是得结合程序中对该表的使用形式，以及数据结构，做下决策，例如选择索引01，除了其成本值，和01相差很小外，还可以删除原始索引03，降低索引个数。还可以考虑是否能将operation\_type加入到索引中？

从这个案例，能体会到提升SQL语句的性能，看着好像就是创建索引，但是往细了抠，还是有很多知识点能让我们挖掘的，考虑因素越多，虽然脑壳疼，但是很可能让我们的应用运行起来更顺畅，少些烦恼，这可能就是”磨刀不误砍柴工“，还得持续学习。

**近期更新的文章：**

《[如何手动添加jar包到maven本地库？][jar_maven]》

《[1元股权转让的一点思考][1]》

《[如何打造一个经常宕机的业务系统？][Link 1]》

《[Linux恢复误删文件的操作][Linux]》

《[Linux的scp指令使用场景][Linux_scp]》

《[Oracle处理IN的几种方式][Oracle_IN]》

《[如何搭建一支拖垮公司的技术团队？][Link 2]》

《[IP地址解析的规则][IP]》

《[MySQL的skip-grant-tables][MySQL_skip-grant-tables]》

《[国产数据库不平凡的一年][Link 3]》

《[Oracle要求顺序的top数据检索问题][Oracle_top]》

《[日常工作中碰到的几个技术问题][Link 4]》

《[了解一下sqlhc][sqlhc]》

《[Oracle的MD5函数介绍][Oracle_MD5]》

《[Oracle 19c的examples静默安装][Oracle 19c_examples]》

《[sqlplus登录缓慢的解决][sqlplus]》

《[VMWare 11安装RedHat Linux 7过程中碰到的坑][VMWare 11_RedHat Linux 7]》

《[COST值相同？是真是假？][COST]》

《[Oracle 11g的examples静默安装][Oracle 11g_examples]》

《[同名的同义词和视图解惑][Link 5]》

《[v$和v\_$的一些玄机][v_v]》

**文章分类和索引：**

《[公众号700篇文章分类和索引][700]》

[jar_maven]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4faddf52dec09683e7b533b56ed0430b99cda91ba3b41df893504d77690f68b8447c68a&idx=1&mid=2247491849&scene=21&sn=d502e38f03e1a222353544d1ce0aaaee#wechat_redirect
[1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4f6ddf52de04ccf3ab1219e1be19c9fa6a38cf8e3e74682d1f31b82cb1d8333692c1cce&idx=1&mid=2247491845&scene=21&sn=da9d31fd892da7e76671f0720721847e#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a51eddf52c085623fb43d61a2201e44477e04169098dd533076dbdf99377b931d671964d&idx=1&mid=2247491821&scene=21&sn=8169bff24df141481de7dfa370b91909#wechat_redirect
[Linux]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a514ddf52c02eb450efabec94a91e5f5e85ab76653cc4dd9d4cf87e8e15f766f5821f8b7&idx=1&mid=2247491815&scene=21&sn=d3954508c6e9ed2acc10a936aecc92b2#wechat_redirect
[Linux_scp]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a512ddf52c04b580ef998bcf292225635f04c743efee72fcc04fa0ffa8834e8f5351ea2f&idx=1&mid=2247491809&scene=21&sn=4b6e5ad829253bb6522f97c241f19d37#wechat_redirect
[Oracle_IN]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a528ddf52c3ed5a5613456abcaad5af328c5e8c59ed2fcf3a8ae21f0e87f2705545c80b6&idx=1&mid=2247491803&scene=21&sn=d40ee2f3334435f933b881e43389b716#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a527ddf52c310590250aea3a7f50e6ef0f3239a097a68a2f5506bae22bc618ce000e3440&idx=1&mid=2247491796&scene=21&sn=3fedb602e4d5acf03fc5460f288bf43c#wechat_redirect
[IP]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a53fddf52c29969fa924ed4644ebfabb1ce3d117443134060e4f7cc8f37e8177f5338700&idx=1&mid=2247491788&scene=21&sn=2790176fc4bd58abb337fd0ce786a190#wechat_redirect
[MySQL_skip-grant-tables]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a535ddf52c23f84ef003cdbadc868463c274971da2b6c79b5fe78660fc013b696c4f4339&idx=1&mid=2247491782&scene=21&sn=40c079eb9f84430e163ead1ee7b9fc85#wechat_redirect
[Link 3]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a549ddf52c5fb741cde2dad195f245871b0d7226a612ca326524d5af0968d674b64f0ac8&idx=1&mid=2247491770&scene=21&sn=d706bcda7de477177a15b14f64c99517#wechat_redirect
[Oracle_top]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a541ddf52c572d7c9c90b93b9f37674c07761a26b7b2566302f24fcb86636cae4e1c4cb8&idx=1&mid=2247491762&scene=21&sn=e92bdd8616515e6403c3ae3280142373#wechat_redirect
[Link 4]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a559ddf52c4f288da18bb70439b1e213a90f0587fdf2cd1d00c948f33f35be40de33bd05&idx=1&mid=2247491754&scene=21&sn=95533b540fe5809c9516468ad2f58c3e#wechat_redirect
[sqlhc]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a555ddf52c43093e82ff00b137c136c5aade5e902f84b523e609429a2de7bfacbd18ad67&idx=1&mid=2247491750&scene=21&sn=6ef9cc04a1a88217277d96072aa86fe2#wechat_redirect
[Oracle_MD5]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a57addf52c6c9d61af3dcf4f1ec9f6b93e51e6fd2d606c36fb8a243d70562c14cc525554&idx=1&mid=2247491721&scene=21&sn=8bdb65a2e11ef287f48d3d213642e4a8#wechat_redirect
[Oracle 19c_examples]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a588ddf52c9e16329e277f2dfdc74734b91bfde0770c545933fca6fb4d7cb224825169c4&idx=1&mid=2247491707&scene=21&sn=569f50f91b58c7cbcb652089e420ea11#wechat_redirect
[sqlplus]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a582ddf52c949d97894f8a2defe4b7fc21d58defa73dcae13f1cfe56d0cec41935b81c88&idx=1&mid=2247491697&scene=21&sn=ecb0e5cc2dc189be23279649b6aee945#wechat_redirect
[VMWare 11_RedHat Linux 7]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a593ddf52c85d1c3e8c3a8767e05f32dd38b61f3da14f3f8fd5521b60a6ca6fa081878c0&idx=1&mid=2247491680&scene=21&sn=27acee6fbe440c573488b4155314944d#wechat_redirect
[COST]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5afddf52cb9bd94da2c5abd6f5bdcefad9035eacccf0fb96db79ff09ec8c38ae9f1941a&idx=1&mid=2247491676&scene=21&sn=4c9c289836ef2d1774136a88705960c7#wechat_redirect
[Oracle 11g_examples]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5ccddf52cda2812771d563eea1db4c3b66f9891869920171bbe5650a45a2865bde0100e&idx=1&mid=2247491647&scene=21&sn=eca45fd1d7dffbf91ab199198adc1591#wechat_redirect
[Link 5]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5c9ddf52cdfe10f04528a52dc3522a0bce8e452ad8f28aa72546ac95c83399c23fbf563&idx=1&mid=2247491642&scene=21&sn=9f4541a6750ff754c17d6d9ddd1da09c#wechat_redirect
[v_v]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5c2ddf52cd4ed4a1a6de5d89f7a6638d8d9378c66739e63ebfd7f670002d76212eb88a2&idx=1&mid=2247491633&scene=21&sn=56f7465a50792828e5c679c69c2f6182#wechat_redirect
[700]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5dfddf52cc949a5b8b316ee905927ff399713b34aec7d806c656e8ada09bfb562c039ca&idx=1&mid=2247491628&scene=21&sn=83bd320bd28da84501fada2e8d515e77#wechat_redirect