|
分頁語句是數(shù)據(jù)庫開發(fā)和應(yīng)用場景比較常見的需求，即按照特定的where條件進(jìn)行過濾，然后在按照一個或者多個條件進(jìn)行排序（如果不進(jìn)行排序無法確執(zhí)行時候無法返回相同的結(jié)果），最后取其中的前十行或者幾十行。 一般分頁語句消耗資源的地方有兩點： 1、返回where條件過濾的結(jié)果集； 2、是對這個結(jié)果集進(jìn)行排序，如果表過大同時對返回的結(jié)果集排序勢必導(dǎo)致性能嚴(yán)重下降，針對分頁語句性能低下的原因。 優(yōu)化分頁語句的核心思想： 1、創(chuàng)建效率高的索引返回盡量少的結(jié)果集排序； 2、因為索引是有序的，直接讓數(shù)據(jù)庫讀取有序索引數(shù)據(jù)避免進(jìn)行排序。 下面就針對不同的分頁語句場景做如何優(yōu)化。 分頁場景一：針對分頁語句的優(yōu)化 首先我們要確定正確的分頁語句框架，如果不按照正確的分頁語句框架編寫，會嚴(yán)重影響oracle選擇正確的執(zhí)行計劃，正確的分頁語句框架如下： select * from ( select * from ( select a.*,rownum rn from ( 寫好的sql語句 ) a ) where rownum<=m ) where rn>=n; 針對正確的分頁語句和錯誤的分頁語句會產(chǎn)生不同的執(zhí)行計劃，舉例如下： SQL> create table t as select * from dba_objects; Table created. SQL> select count(*) from t; COUNT(*) ---------- 497070 我們要寫好的sql語句，如下： 針對這個sql語句，如果T表比較大的話，全表掃描就會非常消耗資源，我們針對object_id列創(chuàng)建索引即可。object_id列選擇性非常高，對1000列進(jìn)行排序性能也很高。創(chuàng)建如下索引： 然后再套用正確的分頁語句框架，去執(zhí)行高級執(zhí)行計劃： Set linesize 200 pagesize 200 alter session set statistics_level=all; select * from ( select * from ( select a.*,rownum rn from ( select * from t where object_id<1000 ) a ) where rownum<=10 ) where rn>=0; select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); 采用正確的分頁語句框架執(zhí)行計劃走的是T_IDX_ID索引，分頁語句顯示10行，執(zhí)行計劃中A-ROWS是10行。我們再看看采用其他錯誤分頁語句顯示的高級執(zhí)行計劃： 從錯誤的執(zhí)行語句框架中我們可以看到，只要不是正確分頁語句框架，oracle都會掃描<1000行最后顯示10行數(shù)據(jù)。 針對上述語句的優(yōu)化方案我們需要注意2點： 1、采用正確的分頁語句框架； 2、針對where條件創(chuàng)建選擇性高、效率高、索引返回少的結(jié)果集。 分頁場景二： select * from t order by object_id 基于某列排序再分頁 因為索引是排序的，我們可以利用索引的排序功能。在排序的分頁語句中如果我們讓分頁語句直接按照升序或者降序掃描索引，這樣的話就避免了全表掃描再排序的這種消耗資源操作。但是我們不確定object_id列是否有非空約束，由于索引是不存空值的，為了能夠保證可能為空的object_id列也存在索引中，我們要在索引中添加一個組合列的常量索引，創(chuàng)建索引語句如下： 執(zhí)行計劃如下： 注：如果有些優(yōu)化器沒走索引可以在sql語句中增加一個索引的hint。 分頁場景三： select * from t where owner=’SYS’ order by object_id 有where條件過濾，然后基于某列排序再分頁 這類分頁語句我們要如何創(chuàng)建索引？ 因為oracle對這類語句執(zhí)行過程是先過濾where條件再排序，所以我們創(chuàng)建一個組合索引，給予OWNER,OBJECT_ID列組合（不能顛倒） 以此類推，where owner='SYS' order by object_id, object_name 這類基于 owner,object_id,object_name列建組合索引。 分頁場景四： select * from t where where object_id<100000 order by owner 語句中的where條件是非等值，然后order by 其他列 這種情況我們就不能按照【分頁場景三】進(jìn)行優(yōu)化，這類語句我們要分兩種情況：
執(zhí)行計劃如下： 注：以上兩種情況沒有明顯的分界線，特別是針對反對結(jié)果集比較適中的情況，還要綜合比較兩種創(chuàng)建索引方法誰的執(zhí)行效率更高而采用哪種方案。 分頁場景五：多表關(guān)聯(lián)的分頁如何優(yōu)化select * from a,b where a.id=b.id order by a.id; 這類分頁語句的優(yōu)化思想是：既然是多表關(guān)聯(lián)的分頁語句，一定是走嵌套循環(huán)，不能走h(yuǎn)ash連接，最后要order by 某個表，一定是 order by的那個表做驅(qū)動表，同時驅(qū)動表的 order by列必須有索引。 無法優(yōu)化的分頁場景：但是如果是這種需求select * from a,b where a.id=b.id order by a.xx，b.xxx 這種需要對兩個表排序情況下就無解了（為什么會搞基于兩個表排序的需求，淘寶京東的商品排序大多數(shù)是只按照一種屬性排序，如按照銷量排序，按照價格排序，綜合排序），這種情況需要干掉一個 order by 的列。 如果分頁語句中有distinct， group by等需要把表數(shù)據(jù)全部掃描之后再去排序分頁的，這種就無法用專門分頁語句進(jìn)行優(yōu)化了。 以上幾種分頁場景基本包含了目前主要的分頁語句的需求和實現(xiàn)，不同的分頁語句有一種或者幾種優(yōu)化方案。首先根據(jù)【優(yōu)化場景一】的內(nèi)容，先選擇標(biāo)準(zhǔn)的分頁語句框架，然后判斷whete條件過濾后的結(jié)果集條數(shù)是多還是少。如果返回結(jié)果集少，則創(chuàng)建效率高的索引；如果返回結(jié)果集非常多，則考慮【分頁場景二，三，四】，為分頁語句創(chuàng)建一個排序過濾好的索引直接返回結(jié)果。對于【無法優(yōu)化的分頁場景】，就要考慮其他手段了，比如說調(diào)整分頁需求，增加where過濾條件，對大表進(jìn)行分區(qū)和瘦身等其他優(yōu)化方案。 |
|
|
