SQL優化案例-單表分頁語句的優化（八）
分頁語句分為單表分頁與多表關聯分頁。分頁語句的優化首先要選對分頁框架，錯誤的分頁框架會導致掃描表中全部的數據，正確的分頁框架會利用到rownum的count stopkey特性較少數據訪問。
如下錯誤的分頁框架：
SELECT *
FROM (SELECT T.*, ROWNUM RN
FROM (SELECT * FROM S_DEPART ORDER BY DEPARTID) T)
WHERE RN >= 1
AND RN <= 10;
邏輯讀22558
創建索引：
create index idx_did on S_Depart (departId,0);
因為沒有過濾條件，走index full scan，性能反而不如table access full。大量回表邏輯讀翻倍，執行計劃中有index full scan就不是最優的。
| 下面代入正確的分頁框架
SELECT *
FROM (SELECT *
FROM (SELECT A.*, ROWNUM RN
FROM (SELECT * FROM S_DEPART ORDER BY DEPARTID) A)
WHERE ROWNUM < 10)
WHERE RN >= 1;
可以看到時間消耗的sort order by上，那麼我們在order by上建立索引消除排序操作。
SELECT *
FROM (SELECT *
FROM (SELECT A.*, ROWNUM RN
FROM (SELECT * FROM S_DEPART ORDER BY DEPARTID) A)
WHERE ROWNUM < 10)
WHERE RN >= 1;
到這裡，我們知道了分頁語句的優化首先要建立在正確的分頁框架上，那麼當需要分頁的語句中有where條件的時候怎麼建立索引呢？
SELECT *
FROM (SELECT *
FROM (SELECT A.*, ROWNUM RN
FROM (SELECT *
FROM S_DEPART
WHERE DEPARTNAME = SALES
ORDER BY DEPARTID) A)
WHERE ROWNUM < 10)
WHERE RN >= 1;
create index idx_name_idon S_DEPART(DEPARTNAME,departId,0);
建立索引的優化法則中我們知道，要在索引中盡量的包含所需要的數據，減少回表的次數，同時利用索引排序的特性，消除order by，因此就需要建立組合索引。組合索引的建立方式where列做引導列，order by部分放在索引後，反之則要邊掃描索引邊過濾數據，產生的邏輯讀是要高於前者，當order by部分有多列數據時候，索引也都需要包含order by的列，並且注意acs和desc。
另外還有其他一些情形，當where條件中既有等值連接又有非等值連接，建立索引的規則如下，為什麼非等值連接要放在order by後呢？
因為當非等值條件在前時，按照索引查詢出的數據，order by的列要重新排序。
create index idx_name on table_name(=,order by,<>,0);
當where條件中沒有等值連接，需要按照如下規則建立索引：
create index idx_name on table_name(order by,<>,0);
當分頁語句中沒有排序條件時，只需要在where列上建立相關索引即可。
後續我們將會分析多表關聯分頁語句的優化方法。
| 作者簡介
姚崇·沃趣科技高級資料庫技術專家
熟悉Oracle、MySQL資料庫內部機制，豐富的Oracle、MySQL故障診斷、性能調優、資料庫備份恢復、複製、高可用方案及遷移經驗。
推薦閱讀：