php 排序不影响索引,为什么不能通过索引避免排序操作呢??

申国华

于 2021-04-08 07:49:59 发布

阅读量89

点赞数

文章标签： php 排序不影响索引

本文探讨了如何通过创建索引来优化SQL查询，特别是在使用CASE语句进行排序时对逻辑I/O的影响。作者详细解释了不同查询策略下，如使用不同的CASE重写和索引，对性能的提升和逻辑IO的减少。重点展示了创建针对特定条件的索引如何显著改善查询效率。

摘要由CSDN通过智能技术生成

谢谢各位的回复,尤其是棉花糖ONE!

忘了说了,我的数据库版本是:10.2.0.4 的

刚才做了一个10053事件.CASE WHEN STOCK_NUM>0 THEN 1 ELSE 0 END=1 确实被重写成了STOCK_NUM>0.

在这种情况下:

SELECT * FROM

(

select /*+ INDEX(zsj_test) */price

from zsj_test

where productname=rpad('A',2000,'A')

AND STOCK_NUM>0 ORDER BY CASE WHEN STOCK_NUM>0 THEN 1 ELSE 0 END,price

)

WHERE ROWNUM<=1;

PRICE

----------

Execution Plan

----------------------------------------------------------

Plan hash value: 3473024397

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 10 (0)| 00:00:01 |

|* 1 | COUNT STOPKEY | | | | | |

| 2 | VIEW | | 2 | 26 | 10 (0)| 00:00:01 |

|* 3 | TABLE ACCESS BY INDEX ROWID| ZSJ_TEST | 50798 | 97M| 10 (0)| 00:00:01 |

|* 4 | INDEX RANGE SCAN | IND_ZSJ_TEST | 2 | | 9 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<=1)

3 - filter("STOCK_NUM">0)

4 - access("PRODUCTNAME"='AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

43 consistent gets

0 physical reads

0 redo size

512 bytes sent via SQL*Net to client

492 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

oracle找到productname=rpad('A',2000,'A'),顺着这个索引走,直至找到第一个满足"STOCK_NUM">0的数据,

因为是ORDER BY CASE WHEN STOCK_NUM>0 THEN 1 ELSE 0 END,price,而这个索引中case when=0的排在前头,必须走完case when=0的才能找到第一个满足条件的数据

所以它的逻辑读大致应该是:

height + case when=0的叶块 + 这些叶块的回访表的IO= 9 + ceil(16943*25/(50803+25)) + 25 =9+9+25=43

基本上是一样的.

所以create index ind5_zsj_test on zsj_test(productname,CASE WHEN STOCK_NUM>0 THEN 1 ELSE 0 END desc,price); 后

SELECT * FROM

(

select /*+ INDEX(zsj_test ind5_zsj_test) */price

from zsj_test

where productname=rpad('A',2000,'A')

AND STOCK_NUM>0 ORDER BY CASE WHEN STOCK_NUM>0 THEN 1 ELSE 0 END desc,price

)

WHERE ROWNUM<=1;

应该是找到的第一个索引条目就是满足条件的,所以的逻辑io基本上就是: height + 一个回访表的操作=10

PRICE

----------

Execution Plan

----------------------------------------------------------

Plan hash value: 4033270375

-----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 10 (0)| 00:00:01 |

|* 1 | COUNT STOPKEY | | | | | |

| 2 | VIEW | | 2 | 26 | 10 (0)| 00:00:01 |

|* 3 | TABLE ACCESS BY INDEX ROWID| ZSJ_TEST | 50798 | 97M| 10 (0)| 00:00:01 |

|* 4 | INDEX RANGE SCAN | IND5_ZSJ_TEST | 2 | | 9 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<=1)

3 - filter("STOCK_NUM">0)

4 - access("PRODUCTNAME"='AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAA')

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

10 consistent gets

0 physical reads

0 redo size

512 bytes sent via SQL*Net to client

492 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

确实是这样的.

*******************

而且

SELECT * FROM

(

select /*+ INDEX(zsj_test ind4_zsj_test) */price

from zsj_test

where productname=rpad('A',2000,'A')

AND sign(stock_num)=1 ORDER BY price

)

WHERE ROWNUM<=1;

访问所有sign(stock_num)=1的叶块,造成很高的逻辑IO,近1.7W

改写成

SELECT * FROM

(

select /*+ INDEX(zsj_test ind4_zsj_test) */price

from zsj_test

where productname=rpad('A',2000,'A')

AND sign(stock_num)=1 ORDER BY sign(stock_num),price

)

WHERE ROWNUM<=1;

PRICE

----------

Execution Plan

----------------------------------------------------------

Plan hash value: 16471340

------------------------------------------------------------------------------------

------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 9 (0)| 00:00:01 |

|* 1 | COUNT STOPKEY | | | | | |

| 2 | VIEW | | 2 | 26 | 9 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN| IND4_ZSJ_TEST | 508 | 997K| 9 (0)| 00:00:01 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<=1)

3 - access("PRODUCTNAME"='AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' AND

SIGN("STOCK_NUM")=1)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

9 consistent gets

0 physical reads

0 redo size

512 bytes sent via SQL*Net to client

492 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

不回访表,不排序,逻辑io:9,确实是我想要的结果.

虽然说,AND sign(stock_num)=1 ORDER BY sign(stock_num),price和

AND sign(stock_num)=1 ORDER BY price应该是等价的,后者也应该是9这个逻辑读的,但不明白为什么不是这样.

但至少找到了我想要的结果,找到了一种解决方案.

申国华

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
php 排序不影响索引,为什么不能通过索引避免排序操作呢??

谢谢各位的回复,尤其是棉花糖ONE!忘了说了,我的数据库版本是:10.2.0.4 的刚才做了一个10053事件.CASE WHEN STOCK_NUM>0 THEN 1 ELSE 0 END=1 确实被重写成了STOCK_NUM>0.在这种情况下:SELECT * FROM(select /*+ INDEX(zsj_test) */pricefrom zsj_testwhere pro...
复制链接

扫一扫