【SQL优化】四月份培训的一个sql优化案例1

来源：互联网发布：net域名后缀排名高吗编辑：程序博客网时间：2024/04/27 10:40

2015年4月，在对项目组里进行sql优化培训的时候，提到过一个生产环境的sql优化，下面搬过来记录在这里。下面是当时的培训材料：

请问大家：

在accept_id列上建立有普通索引，然后使用如下三种形式的like操作符，哪些会不走索引？

1，where accept_id like '%0419%' ---查询04月19日的工单；
2，where accept_id like '20150419%' ---查询2015年04月19日的工单；
3，where accept_id like '2015041900121' ---查询工单号为2015041900121的工单；

下面看线上的真实的sql，修改前：

SQL> select count(1)
2 from cct_log_secondcsc_201409 a
3 where a.appeal_type = 'C09'
4 and a.description <> 'B_SUCCESS'
5 and a.send_time > sysdate - 1;

已用时间: 00: 00: 04.30

执行计划
----------------------------------------------------------
Plan hash value: 2238348104

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 11870 (1)| 00:02:23 |
| 1 | SORT AGGREGATE | | 1 | 37 | | |
|* 2 | TABLE ACCESS FULL| CCT_LOG_SECONDCSC_201409 | 1 | 37 | 11870 (1)| 00:02:23 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("A"."APPEAL_TYPE"='C09' AND "A"."DESCRIPTION"<>'B_SUCCESS' AND
"A"."SEND_TIME">SYSDATE@!-1)

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
43270 consistent gets
43261 physical reads
0 redo size
430 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

分析：
表cct_log_secondcsc_201409上有(accept_id,appeal_type)的组合索引
很明显，由于谓词只有a.appeal_type = 'C09'，并且accept_id列上的值分布太广
而无法使用这个索引。那么我们可以想象，如果a.appeal_type = 'C09'的值占比很小，那么
我们是不是应该要使用走索引的方式来读取表呢？

修改后：

SQL> select count(accept_id)
2 from cct_log_secondcsc_201409 a
3 where a.accept_id like '201409%'
4 and a.appeal_type = 'C09'
5 and a.description <> 'B_SUCCESS'
6 and a.send_time > sysdate - 1;

已用时间: 00: 00: 00.04

执行计划
----------------------------------------------------------
Plan hash value: 756090656

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 368 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 37 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| CCT_LOG_SECONDCSC_201409 | 1 | 37 | 368 (1)| 00:00:05 |
|* 3 | INDEX RANGE SCAN | IDX111 | 8 | | 360 (1)| 00:00:05 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("A"."DESCRIPTION"<>'B_SUCCESS' AND "A"."SEND_TIME">SYSDATE@!-1)
3 - access("A"."ACCEPT_ID" LIKE '201409%' AND "A"."APPEAL_TYPE"='C09')
filter("A"."APPEAL_TYPE"='C09' AND "A"."ACCEPT_ID" LIKE '201409%')

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
369 consistent gets
0 physical reads
0 redo size
430 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

效果：时间从4s下降到0.04秒，一致读从4w下降到不到400。不管是时间还是一致读取，都下降了100倍，优化效果非常明显。

其实这个表的组合索引建立都有问题，前导列应该选择唯一值较小的appeal_type。

拓展，请分析如下三种操作的优劣：

1，where ring = 128；
2，where ring = 100+28；
3，where ring-28 = 100；

说明，ring列是number类型，并且有在ring列上建立普通索引。

===========

下面逐个回答上面的问题：

1，where accept_id like '%0419%' ---查询04月19日的工单；---绝不走索引（函数索引除外）
2，where accept_id like '20150419%' ---查询2015年04月19日的工单；----要走索引
3，where accept_id like '2015041900121' ---查询工单号为2015041900121的工单；---要走索引

1，where ring = 128；---要走索引
2，where ring = 100+28；---也要走索引，只是cpu要提前计算一次100+28=128
3，where ring-28 = 100；---不走索引，cbo无法把28移动到等号右边进行100+28=128的计算。

0 0