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故障診斷、效能調優、資料庫備份恢復、複製、高可用方案及遷移經驗。