sql语句cost花费判断 -m6米乐安卓版下载

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

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

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

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、通过表格排序

有意思的是 走专属的  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成时，那还不如不走索引，直接全表扫描更快