暂无图片
暂无图片
12
暂无图片
暂无图片
暂无图片
首页 / SQL语句Cost花费判断

SQL语句Cost花费判断

原创 三石 All China Database Union 2023-04-23
1417

一、结论

为什么有的时候SQL语句会走索引，有的会走全表扫描，这是根据COST成本来判断的，也就是说当全表扫描的花费成本比走索引的底，那走全表扫描很正常。

当你要查询的这个列符合条件的数值，当他的值大于等于所占比例其中的9成时，那还不如不走索引，直接全表扫描更快。


二、基础知识

序号

命令

解释

1

set autotrace off

默认值，关闭Autotrace

2

set autotrace on explan

只显示执行计划

3

set autotrace on statistics

只显示执行的统计计划

4

set autotrace on

包含2、3两项内容

5

set autotrace traceonly


与ON相似，但是不显示语句的执行结果

6

set timing on

显示执行时间

查看数据库SQL语句真实的执行计划需要通过上述命令来达到，主要用到5和6

三、SQL语句信息

1、全表扫描（当un列和gs列具体的数值不一样时）

SELECT yt.km bh,
       SUM(yt.jf) nc
  FROM mj_cx yt
 WHERE cl = 101
   AND (un = '18')
   AND (fl = '01')
   AND (gs = 'G')
 GROUP BY km;

2、使用索引——INX（当un列和gs列具体的数值不一样时）

SELECT yt.km bh,
       SUM(yt.jf) nc
  FROM mj_cx yt
 WHERE cl = 101
   AND (un = '30')
   AND (fl = '01')
   AND (gs = 'S')
 GROUP BY km;

3、表的收集统计信息时间（时间很近，也就是说和统计信息无关）

SQL> select last_analyzed from dba_tables where table_name='MJ';

LAST_ANALYZE
------------
17-APR-23

4、该表拥有索引，其中跟语句相关的条件列，只有  INX 和  INX_CX

create index INX on mj_cx (CL,UN,GS,FL,CO,KM,BK,WB);
create index INX_CX on mj_cx (CL,UN,GS,FL,KM);
create index INX_CX1 on mj_cx (CL,UN,FL,KM);
create index INX_CX2 on mj_cx (CL,UN,FL,CO,KM);

创建一个索引专属于四列

create index INX_CX3 on mj_cx(CL, UN, FL,GS);

四、判断思路

1、这个是创建了只属于四个列的合适索引的执行sql语句——INX_CX3

Execution Plan
----------------------------------------------------------
Plan hash value: 3169905893
--------------------------------------------------------------------------------------------------------------
| Id | Operation 　　　　　　　　　　　　　| Name 　　　　　　　　 | Rows | Bytes      | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
| 0  | SELECT STATEMENT 　　　　　　　　　 | 　　　　　 　　　　　 | 16162 | 615K      | 10231 (1)  | 00:00:01 |
| 1  | HASH GROUP BY 　　　　　　　　　　　| 　　　　　 　　　　　 | 16162 | 615K 　   | 10231 (1)  | 00:00:01 |
| 2  | TABLE ACCESS BY INDEX ROWID BATCHED | MJ           　　　　 | 75837 | 2888K     | 10228 (1)  | 00:00:01 |
|* 3 | INDEX RANGE SCAN 　　　　 　　　　  | INX_CX3 　　　　　　　| 75837 |           | 333 (0)    | 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("F_CLIENT"=101 AND "F_UNITID"='18' AND "F_FLZBH"='01' AND "F_GSDMBH"='G')
Statistics
----------------------------------------------------------
3 recursive calls
0 db block gets
13623 consistent gets
0 physical reads
0 redo size
119375 bytes sent via SQL*Net to client
3314 bytes received via SQL*Net from client
248 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3698 rows processed

###########################################################################################################################

2、走的索引——INX

select /*+index(t pk_emp)*/* from emp t

--强制索引，/*.....*/第一个星星后不能有空格，里边内容结构为：加号index(表名 空格 索引名)。

--如果表用了别名，注释里的表也要使用别名

SELECT  /*+index(yt INX)*/yt.km bh,
       SUM(yt.jf) nc
  FROM mj_cx yt
 WHERE cl = 101
   AND (un = '18')
   AND (fl = '01')
   AND (gs = 'G')
 GROUP BY km;
Execution Plan
----------------------------------------------------------
Plan hash value: 1540358647
---------------------------------------------------------------------------------------------------------
| Id | Operation 　　　　　　　　　　　　| Name 　　　　     | Rows  | Bytes　 | Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT 　　　　　　　　　| 　　　　　　　　  | 16162 | 615K  　 | 40262 (1)| 00:00:02 |
| 1 | HASH GROUP BY 　　　　　　　　     | 　　　　　　  　　| 16162 | 615K     | 40262 (1)| 00:00:02 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| MJ  　　　　      | 75837 | 2888K    | 40259 (1)| 00:00:02 |
|* 3 | INDEX RANGE SCAN　　　　　　　　  |  INX 　　　　     | 75837 | 　　　　 | 761 (1)  | 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CL"=101 AND "UN"='18' AND "GS"='G' AND "FL"='01')
Statistics
----------------------------------------------------------
2 recursive calls
0 db block gets
81927 consistent gets
1085 physical reads
0 redo size
119375 bytes sent via SQL*Net to client
3314 bytes received via SQL*Net from client
248 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3698 rows processed

###########################################################################################################################

3、走的索引——INX_CX

SELECT /*+index(yt INX_CX)*/yt.km bh,
       SUM(yt.jf) nc
  FROM mj_cx yt
 WHERE cl = 101
   AND (un = '18')
   AND (fl = '01')
   AND (gs = 'G')
 GROUP BY km;
Execution Plan
----------------------------------------------------------
Plan hash value: 3877613795
-----------------------------------------------------------------------------------------------------
| Id  | Operation　　　　　　　　  | Name 　　　　 | Rows | Bytes | Cost (%CPU)| Time 　  |
-----------------------------------------------------------------------------------------------------
| 0  | SELECT STATEMENT 　　　   　| 　　 　　　　 | 16162 | 615K | 72878 (1)  | 00:00:03 |
| 1  | SORT GROUP BY NOSORT 　　　 |     　　　　  | 16162 | 615K | 72878 (1)  | 00:00:03 |
| 2  | TABLE ACCESS BY INDEX ROWID |  MJ　　　　   | 75837 | 2888K| 72878 (1)  | 00:00:03 |
|* 3 | INDEX RANGE SCAN 　　　　   |  INX_CX 　　　| 75837 | 　　 | 490 (0)    | 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CL"=101 AND "UN"='18' AND "GS"='G' AND "FL"='01')
Statistics
----------------------------------------------------------
2 recursive calls
0 db block gets
103294 consistent gets
695 physical reads
0 redo size
128327 bytes sent via SQL*Net to client
3314 bytes received via SQL*Net from client
248 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3698 rows processed

###########################################################################################################################

4、强制全表扫描

表名

(1) 若表有 '别名'，则是 '别名'

(2) 若表没有 '别名'，则是 '表名' 全称

SELECT /*+ full(yt)*/yt.km bh,
       SUM(yt.jf) nc
  FROM mj_cx yt
 WHERE cl = 101
   AND (un = '18')
   AND (fl = '01')
   AND (gs = 'G')
 GROUP BY km;
Execution Plan
----------------------------------------------------------
Plan hash value: 1975139930
------------------------------------------------------------------------------------
| Id | Operation 　　　　| Name 　　　　 | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT   | 　　　　  　　| 16162 | 615K | 10542 (1)  | 00:00:01 |
| 1 | HASH GROUP BY 　　 | 　　　　　　　| 16162 | 615K | 10542 (1)  | 00:00:01 |
|* 2 | TABLE ACCESS FULL |  MJ  　　　　 | 75837 | 2888K| 10540 (1)  | 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("UN"='18' AND "GS"='G' AND "CL"=101 AND "FL"='01')
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
392606 consistent gets
0 physical reads
0 redo size
119375 bytes sent via SQL*Net to client
3314 bytes received via SQL*Net from client
248 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3698 rows processed

5、通过表格排序

序号

sql语句执行使用索引情况

涉及列

花费成本

花费最少排序

1

INX_CX3（四列索引）

CL, UN, FL, GS

10231

第一

2

INX（八列索引）

CL, UN, GS, FL, CO, KM, BK, WB

40262

第三

3

INX_CX（五列索引）

CL, UN, GS, FL, KM

72878

第四

4

强制全表扫描


10542

第二

有意思的是 走专属的  INX_CX3（四列索引）其实就比全表扫描 Cost花费少 311，不管是花费还是执行时间都相差不多

6、梳理解释

select distinct cl,count(*) from mj_cx group by cl;
select distinct un,count(*) from mj_cx group by un;
select distinct fl,count(*) from mj_cx group by fl;
select distinct gs,count(*) from mj_cx group by gs;

两个sql语句的得出数量值不一样，相差五倍

全表扫描 107850
走索引   22228

当某列查询数据时全表扫描和索引扫描相差不多时（甚至索引产生的伪列 rowid，产生IO）

  CL count(*)
1 101 354165

  UN COUNT(*)
2 30 22228
9 18 107850

  FL COUNT(*)
1 01 354165

  GS COUNT(*)
1 S  22228
3 G  249040

走全表扫描时，distinct值不明显

select count(*) from ackmje_cx2022 where f_gsdmbh='G'

249040

也就是说当你要查询的这个列符合条件的数值，当他的值大于等于所占比例其中的9成时，那还不如不走索引，直接全表扫描更快

墨力计划
最后修改时间：2023-04-23 14:42:18
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论