一次簡單的分頁優化
今天對一個排序分頁查詢進行了一次優化，優化後效能得到明顯改觀。下面用例子說明優化步驟。
1、首先建立測試資料
SQL> create table t as select * from dba_objects;
Table created
SQL> insert into t select * from t;
12704 rows inserted
SQL> commit;
Commit complete
SQL> insert into t select * from t;
25408 rows inserted
SQL> insert into t select * from t;
50816 rows inserted
SQL> commit;
SQL> create index idx_t_object_name on t(object_name);
Index created
SQL> ANALYZE TABLE T COMPUTE STATISTICS;
Table analyzed
2、取第一頁資料
suk@ORA10G> SELECT *
2 FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM T ORDER BY OBJECT_NAME DESC) A WHERE ROWNUM <
16)
3 WHERE RN >=1;
已選擇15行。
已用時間: 00: 00: 00.21
執行計劃
----------------------------------------------------------
Plan hash value: 882605040
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 2850 | 308 (1)| 00:00:04 |
|* 1 | VIEW | | 15 | 2850 | 308 (1)| 00:00:04 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 101K| 17M| 308 (1)| 00:00:04 |
|* 4 | SORT ORDER BY STOPKEY| | 101K| 7940K| 308 (1)| 00:00:04 |
| 5 | TABLE ACCESS FULL | T | 101K| 7940K| 308 (1)| 00:00:04 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">0)
2 - filter(ROWNUM<16)
4 - filter(ROWNUM<16)
統計資訊
----------------------------------------------------------
189 recursive calls
0 db block gets
1363 consistent gets
0 physical reads
0 redo size
1440 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
15 rows processed
從執行計劃可以看出，oracle選擇了全表掃描，雖然有stopkey，但是需要有大資料量的排序操作，效率還是很低。
我們在object_name上已經建立了索引，如果走索引，避免了排序操作，並且只返回了少量資料，效率肯定很高。現在問題的關鍵是分析為什麼沒有用索引。
先看看錶結構：
SQL> desc t
Name Type Nullable Default Comments
-------------- ------------- -------- ------- --------
OWNER VARCHAR2(30) Y
OBJECT_NAME VARCHAR2(128) Y
SUBOBJECT_NAME VARCHAR2(30) Y
OBJECT_ID NUMBER Y
DATA_OBJECT_ID NUMBER Y
OBJECT_TYPE VARCHAR2(19) Y
CREATED DATE Y
LAST_DDL_TIME DATE Y
TIMESTAMP VARCHAR2(19) Y
STATUS VARCHAR2(7) Y
TEMPORARY VARCHAR2(1) Y
GENERATED VARCHAR2(1) Y
SECONDARY VARCHAR2(1) Y
原因出來了，OBJECT_NAME欄位定義為可以為空的，因為btree索引不會索引空值，而預設情況下，oracle排序時會把空值認為無窮大。
在這裡例子中，oracle無法知道OBJECT_NAME是否存在空值，所以，oracle會選擇全表掃描。
如果確認資料當前及以後不會存在空值，把欄位屬性改成非空就可以使oracle使用索引了。
SQL> alter table t modify object_name not null;
Table altered
3、再次查詢第一頁
suk@ORA10G> SELECT *
2 FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM T ORDER BY OBJECT_NAME DESC) A WHERE ROWNUM <
16)
3 WHERE RN >=1;
已選擇15行。
已用時間: 00: 00: 00.06
執行計劃
----------------------------------------------------------
Plan hash value: 3229612337
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 2850 | 14 (0)| 00:00:01 |
|* 1 | VIEW | | 15 | 2850 | 14 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 16 | 2832 | 14 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 101K| 7940K| 14 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN DESCENDING| IDX_T_OBJECT_NAME | 16 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">0)
2 - filter(ROWNUM<16)
統計資訊
----------------------------------------------------------
201 recursive calls
0 db block gets
49 consistent gets
0 physical reads
0 redo size
1440 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
15 rows processed
可以看到，oracle正確使用了索引，效率也提升了很多。
4、取靠後的記錄
我們知道，用以上方法在返回較靠前的記錄時效率很高，但是如果返回靠後的資料，效率就有很大問題了：
suk@ORA10G> SELECT *
2 FROM (SELECT A.*, ROWNUM RN
3 FROM (SELECT * FROM T ORDER BY OBJECT_NAME DESC) A
4 WHERE ROWNUM < 30000)
5 WHERE RN >= 30000 - 15;
已選擇14行。
已用時間: 00: 00: 00.29
執行計劃
----------------------------------------------------------
Plan hash value: 882605040
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29999 | 5566K| 308 (1)| 00:00:04 |
|* 1 | VIEW | | 29999 | 5566K| 308 (1)| 00:00:04 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 101K| 17M| 308 (1)| 00:00:04 |
|* 4 | SORT ORDER BY STOPKEY| | 101K| 7940K| 308 (1)| 00:00:04 |
| 5 | TABLE ACCESS FULL | T | 101K| 7940K| 308 (1)| 00:00:04 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">29985)
2 - filter(ROWNUM<30000)
4 - filter(ROWNUM<30000)
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
1338 consistent gets
0 physical reads
0 redo size
1499 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
15 rows processed
從執行計劃可以看到，如果取靠後的記錄，oracle甚至放棄了索引掃描，因為成本太高了。
像這種情況，還有一種解決方法：
suk@ORA10G> SELECT T.*
2 FROM T
3 WHERE ROWID IN (SELECT RID
4 FROM (SELECT A.RID, ROWNUM RN
5 FROM (SELECT ROWID RID FROM T ORDER BY OBJECT_NAME DESC) A
6 WHERE ROWNUM < 30000)
7 WHERE RN >= 30000 - 15);
已選擇15行。
已用時間: 00: 00: 00.07
執行計劃
----------------------------------------------------------
Plan hash value: 695960827
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 103 | 550 (1)| 00:00:07 |
| 1 | NESTED LOOPS | | 1 | 103 | 550 (1)| 00:00:07 |
| 2 | VIEW | VW_NSO_1 | 29999 | 351K| 408 (1)| 00:00:05 |
| 3 | HASH UNIQUE | | 1 | 732K| | |
|* 4 | VIEW | | 29999 | 732K| 408 (1)| 00:00:05 |
|* 5 | COUNT STOPKEY | | | | | |
| 6 | VIEW | | 101K| 1191K| 408 (1)| 00:00:05 |
| 7 | INDEX FULL SCAN DESCENDING| IDX_T_OBJECT_NAME | 101K| 2878K| 408 (1)| 00:00:05 |
| 8 | TABLE ACCESS BY USER ROWID | T | 1 | 91 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("RN">=29985)
5 - filter(ROWNUM<30000)
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
138 consistent gets
0 physical reads
0 redo size
1480 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
15 rows processed
這種方法是先取到當前頁需要的rowid，然後再回表掃描。它和上面的方法區別在於：原來的方法是先取資料，再分頁；這種方法是先分頁，再取資料。
最主要的差別在於排序原來的方法需要排序；後一種方法不需要排序。
用最後一種介紹的方法，無論是取靠前的頁數還是靠後的頁數，效能都比原來的高。但是最後一種方法有一個前提：
1) 只返回單個表的欄位
2) 排序欄位和返回的欄位屬於同一張表
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/231499/viewspace-243991/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 記一次分頁優化優化
- Oracle優化案例-單表分頁語句的優化（八）Oracle優化
- SQL優化案例-單表分頁語句的優化（八）SQL優化
- php 分頁 分頁類 簡單實用PHP
- React.js 一次動畫效能的簡單優化ReactJS動畫優化
- 分頁查詢優化優化
- MybatisPlus的分頁外掛簡單使用MyBatis
- MySQL分頁查詢優化MySql優化
- Android分頁元件Paging簡單使用Android元件
- 記一次想簡單化的單元測試
- Web 頁面優化專項 > Lighthouse > 效能分數優化Web優化
- 如何寫一個簡單的分頁元件（原理）元件
- django 網站實現簡單分頁Django網站
- nginx部署及簡單優化Nginx優化
- 簡單優化容器服務優化
- MySQL分優化之超大頁查詢MySql優化
- SPA單頁面應用、前後端分離專案SEO優化的方法後端優化
- MySQL全面瓦解21（番外）：一次深夜優化億級資料分頁的奇妙經歷MySql優化
- Python如何優化列表介面進行分頁Python優化
- [iOS]一次立竿見影的首頁渲染時間優化iOS優化
- 在大資料情況下MySQL的一種簡單分頁最佳化方法大資料MySql
- [譯]深度學習模型的簡單優化技巧深度學習模型優化
- JS 程式碼的簡單重構與優化JS優化
- Hive的壓縮儲存和簡單優化Hive優化
- 優化Go程式的簡單技巧 - stephen.sh優化Go
- Oracle最佳化之單表分頁最佳化Oracle
- 簡單的網頁登入頁面網頁
- 一次簡單的脫殼
- 簡單網頁網頁
- 原生 JS實現一個簡單分頁外掛JS
- 簡單使用Knockout.js和Datatables.js 分頁JS
- MySQL 海量資料的 5 種分頁方法和優化技巧MySql優化
- EasyDependency:簡單優雅管理元件化的依賴配置元件化
- [貝聊科技]一次立竿見影的首頁渲染時間優化優化
- 一次完整的 Web 請求和渲染過程以及如何優化網頁Web優化網頁
- SQL最佳化案例-單表分頁語句的最佳化（八）SQL
- MySQL——優化巢狀查詢和分頁查詢MySql優化巢狀
- mysql count函式與分頁功能極限優化MySql函式優化