分而治之：Oracle 18c及12.2分割槽新特性的N種優化實踐

本文根據楊廷琨2018年5月11日在【第九屆中國資料庫技術大會】上的演講內容整理而成。

　　講師介紹：

　　楊廷琨，高階諮詢顧問， ITPUB Oracle 資料庫管理版版主 ，人稱“楊長老”，十數年如一日堅持進行Oracle技術研究與寫作，號稱“Oracle的百科全書”。迄今已經在自己的部落格上發表了超過3000篇技術文章。2010年，與 Eygle 共同主編出版了《Oracle DBA手記》一書，2007年被 Oracle 公司授予ACE 稱號。

　　分享大綱：

　　·分割槽基本概念

　　·分割槽演進歷史

　　·分割槽最佳實踐

　　·分割槽最新特性

　　正文演講：

　　很高興，又和大家重聚在DTCC 2018的資料庫效能優化專場。這次我想和大家分享與分割槽相關的優化特性，主要會和大家介紹一些Oracle最新的分割槽技術以及我們在日常運營中的最佳實踐。

　　一.分割槽基本概念

　　Oracle對於分割槽的定義是根據內部定義的規則，將一張表的資料拆分到多個資料段中。分割槽之後，每個分割槽都是獨立的資料段。Oracle分割槽的最大好處是透明性，應用無需瞭解底層資料，訪問方式也與之前無異。換句話說，當把表做成分割槽表之後，程式不做任何的修改調整就可以直接跑，同樣如果把分割槽表改回普通表，也不要做任何調整。但是，這並不意味著做了應用之後我們就不需要了解表是否進行了分割槽。如果你想要分割槽帶來額外的效能好處，那麼分割槽策略一定是要和應用程式、業務訪問方式相結合。

　　Oracle提供了幾種分割槽的訪問方式，最常用直觀的方式是通過分割槽擴充套件語句直接指定到某個分割槽。但我們不推薦這種方式，我們更推薦的方式是通過真正控制訪問資料、增加規範條件，讓Oracle幫你定位到需要訪問的某些資料。

　　分割槽的好處是什麼?原來所有的操作都是基於一張大表去做的，而分割槽之後，一張大表化成了多個小單元，我們可以基於這種小單元去做刪除、截斷、遷移、索引等操作，分割槽提供了很好的細粒度操作手段。

　　提到分割槽，很多人第一反應是它帶來的效能優勢，但其實我認為分割槽最大的優勢是在可管理性和可維護性方面，管理很多小表或者小資料段的成本要遠低於管理一張大表，而且平均維護時間也會變少，但速度卻會更快。

　　第二個好處是可用性的增強，這也是Oracle官方宣稱的好處。原來，如果一張表對應的某個資料檔案出現問題，影響的是整個全表，但現在可能影響的只是其中一個分割槽，因為它在邏輯上是相隔離的。

　　基於以上兩點，我們才能開始講效能的提升。在早期的版本中，Oracle只是直接說效能有一定提升，但其實這種說法是不準確的，因為分割槽如果設計不好，你的效能是會下降的。後來Oracle自身也意識到了，所以從Oracle 11版本之後，Oracle就把效能拆成了OLTP和OLAP兩種不同的情況去考慮。

　　OLTP處理的更多的是短時間內的大量併發，所以這時分割槽能帶來的好處是降低共享資源的爭用，消除熱點塊;OLAP面臨的是海量資料的處理，所以我們要更好的利用並行來提升效能。

　　二.Oracle分割槽演進歷史

　　Oracle最早是從版本 8引進分割槽的，上圖這張表給大家列出了各個版本的分割槽功能，並通過功能、效能和管理性三個角度解讀。很多人雖然在用分割槽，但是據我瞭解他們應用的很多特性都還集中在8、9版本，例如常用的範圍分割槽、歷史分割槽、列表分割槽等等功能。但其實之後的版本中，Oracle一直都有很多更好更新的功能提供給我們。

　　Oracle 12和18c中新增了很多非常實用的新功能，極大的簡化了大家的日常操作，接下來我會著重為大家介紹這些功能。

　　三.分割槽最佳實踐

　　接下來，我們介紹一下分割槽的最佳使用場景。

　　範圍分割槽的最佳使用場景是針對具有天然時間屬性的資料。例如，系統裡記錄的訂單時間、生成時間、啟動時間等等。當然並不是只要有時間屬性資料就可以用範圍分割槽，還要看業務對時間是否有可見性的要求。另外，業務如果更關心近期資料就再好不過了，這樣範圍分割槽對效能提升以及過期歷史資料清理都會是很好的幫助。

　　隨著資料的不斷積累，我們都會擔心資料庫越來越大。如果業務對於資料的生命週期有明確要求的話，那麼我們可以通過清理資料來讓現有系統維持在一個相對穩定的狀態。

　　如何去清理資料呢?傳統的Delete資料清理會面臨很多問題，例如效率低下、無法釋放空間等等。但如果我們的資料儲存有一個明確的時間限定條件，那麼分割槽就是一個很好的選擇。

　　分割槽的清理成本和速度都很值得期待。在清理資料時，我們建議儘可能建立全域性索引。資料清除操作是會影響全域性索引的使用度，甚至導致索引失效。但如果我們是定期做分割槽資料清理的話，那麼就不會影響全域性索引的作用。

　　範圍分割槽的好處是什麼呢?首先，資料分佈是相對平均的，因為是按照時間等分的，數量也是可控的;通過DDL清理資料的速度很快，不會產生大量redo、undo的問題。同時，在設計時還要考慮儘量讓一個查詢集中在一個分割槽中完成，提升查詢效率。通過定期DDL方式清理分割槽，可以保證分割槽、表的大小維持在穩定的量級，同時索引也不會隨著時間迅速增長。

　　剛才我們介紹了範圍分割槽是最常用的清理過期資料的方式，但是在真正的產品環境中，我們會面臨各種不同的場景。例如，我們不能把所有資料都簡單的刪掉，因為刪除的資料中可能有少量的資料是需要保留的。

　　面對這樣的場景，如果使用Delete方式去刪除，你會發現雖然我們使用了分割槽，但是卻沒有享受到分割槽的好處。而且資料清理使用了原來的方式，那麼必然會碰到原來的問題。

　　那我們有沒有更好的解決辦法呢?我們可以用Insert+Exchange的方式來做，我先把這部分資料從表中刪掉，如果這其中有少量需要的資料，再插回來就可以了。這種方式既保證了效率，也避免了遇到之前的問題。

　　上圖是我通過程式碼簡單的給大家演示一下整個過程。

　　這裡有一張T_PART表和P(3)分割槽，假設這其中有七八千條資料都是不重要的，但其中可能需要保留30條資料，那麼我就會採用之前提到的Insert+Exchange方式。這裡還有一個小竅門，我們是先Insert，再Exchange。為什麼這樣做，如果這張表一致性非常重要，我們就要在操作之前，先把這張表鎖起來，避免別人對它進行操作，然後把需要保留的資料Insert到一張臨時表中，之後再去做Exchange。通過這種方式我們可以時刻保持資料的一致性。

　　除了上面的挑戰，我們可能還會面臨主子表的挑戰。假設我的主表和子表都做了分割槽，那我們可能會面臨以下挑戰，首先子表可能不存在主表的分割槽時間列，例如，有訂單表和訂單詳細表兩個表，並且兩者是主子表關係。其中訂單表是以訂單時間來分割槽的，這時訂單詳細表的分割槽時間列就會產生爭議，如果是按照訂單明細的建立時間，那麼它和訂單時間可能是不一致的，且二者本身就是一對多的關係，所以在資料清理的時候，可能主表清理不了，如果要用訂單時間去分割槽，那麼你就需要在表中冗餘訂單時間。

　　如果只是冗餘訂單時間，相信很多人都是可以接受的，但是挑戰還不止於此，一旦有了外健約束，主表無法執行truncate操作，必須先將約束disable掉，給運維增加很多不便。

　　如何解決這個問題?Oracle 11g就給我們提供了一個新功能叫參考分割槽，它是這麼解決的：主表的欄位還是按照主表時間列去分割槽，但子表不再需要冗餘主表欄位，而是直接依賴與主表的主外來鍵關係去做分割槽。

　　參考分割槽適用於主子表建立相同的資料策略，同時子表沒有合適的分割槽欄位，且主子表經常關聯訪問的場景。另外，Oracle 12還對此做了增強，支援級聯，換句話說，當我有主子表的情況時，不用先去子表做truncate，直接在主表做truncate，它就會遞迴的把所有子表truncate。

　　雜湊分割槽相對來說比較簡單，它的適用場景是沒有時間屬性、缺少區分資料的業務欄位的場景。如果系統面臨著共享資源的爭用，也可以使用雜湊分割槽。

　　我建議雜湊分割槽鍵值列儘量選擇重複度不高的欄位，這樣不容易導致資料分佈不均;分割槽數量最好是2的冪次方，這也是為了避免分割槽數量分佈不均;針對沒有時間屬性和明確業務屬性的表，通常不會去做定期清理的策略，我更建議使用全域性索引;另外，雜湊分割槽索引可以有效的解決索引熱點塊的問題。

　　有人可能會有這樣的疑問，既然我的資料沒有業務特點，為什麼要分割槽呢?我們之前碰到過這樣一個案例，客戶的表量級非常大，400T的資料可能有395T的資料都在同一張表中 ，客戶面臨的問題是表可能存不下這麼多資料，Oracle對於表容量沒有限制，但是對於表空間的容量是有限制的，這時你會發現如果不用分割槽，這個問題就是無解的。

　　雜湊分割槽可以解決的一個問題是熱點塊問題。為什麼會產生熱點塊問題呢?對於OLTP系統來說，會有大量的資料插入。插入資料的型別一般有兩種，一種是主鍵，另一種是時間類。這兩組資料的共同點是新插的資料永遠是最大的，當多人同時做插入時，因為表是無序的，所以沒有影響，但索引是有序的，所以更新索引時會產生資源爭用。如果你是RAC架構，由於GC多節點之間的相互爭用，會導致熱點塊問題進一步加劇。

　　傳統的解決方案是利用Oracle提供的逆鍵索引，把鍵值反過來，分散熱點塊。但逆鍵索引有一個很大的缺點，就是它雖然可以解決熱點問題，但卻不支援範圍掃描。

　　雜湊分割槽如何解決呢?我們建立索引，指定索引按照雜湊方式分割槽，然後指定分割槽數量。這樣做的好處是什麼呢?原來我是一個索引，只有一個最高值，大家都去爭搶這一個最高值，但是現在我變成了32個分割槽，有32個索引最高值，這時的資源爭搶就會少很多。當然，技術都是有兩面性的，在這種情況下，你再做索引範圍掃描時，就不只是要掃一棵索引樹，而是32棵索引樹，引入的額外開銷就會大許多。所以分割槽數量的選擇也是十分有講究的。

　　列表分割槽最常見的是針對有某些業務屬性資料的場景。我們可以根據明確的業務特點去做分割槽。

　　地區欄位是列表分割槽常見的候選鍵值列，資料分佈和訪問方式確定分割槽鍵值劃分，同時我還建議設定一個DEFAULT分割槽，假設，我們把國內的大部分省到列出來了，但是有一天有個沒有對應分割槽的省的資料進來了，如果沒有DEFAULT分割槽就會直接報錯，而有了這個分割槽，資料就有容身之所了。

　　列表分割槽與業務的匹配度更好，業務可以清楚的知道資料存在哪裡，並且高效的找到，不用太多的使用Oracle內部的關聯和查詢。

　　常見的索引有兩種，一種是全域性索引，一種是本地索引。分割槽的全域性索引和單表上的索引沒有區別，不管表上有多少個分割槽，只有一棵索引樹，所有的資料來自分割槽。這樣做的好處了，即使表做了分割槽，訪問代價也不會增加，但缺點是如果你對下面的分割槽表做了一些DDL操作，那麼很容易導致索引失效。

　　本地索引和表分割槽是一一對應的關係，當你在對錶做資料操作時，Oracle同時也會對索引分割槽做操作，不會導致分割槽索引的不可用，同時本地索引還支援並行掃描和建立。

　　本地索引的缺點是如果你要把主鍵建立成本地索引，那麼主鍵必須包含在索引裡面，而且無法保證每棵獨立的索引樹之間的資料一致性，所以必須把鍵值列加入其中來保證唯一性，但是這樣未限定分割槽的查詢將掃描全部索引分割槽，這會增加額外的開銷。

　　最後，我們看一下用來消除熱點塊的雜湊分割槽索引，索引雖然也會做分割槽，但是與表資料沒有任何關聯關係，任何一個索引分割槽都可以去訪問所有的表，它的優勢就是分散熱點。但缺點是訪問索引時需要訪問索引的每個分割槽才能得到完整記錄。

　　四.分割槽最新特性

　　Oracle 12提供了很多新特性，這些新特性對於日常維護是非常有價值的，所以我們來為大家詳細介紹一下。

　　首先，我們來介紹部分分割槽。剛才我們講到常用的索引有兩種，全域性索引和本地索引，那麼部分分割槽的索引是什麼呢?在實際環境中，我沒必要對所有分割槽都建立索引，很多歷史資料分割槽的訪問頻度是非常小的，沒有必要關注，部分分割槽索引剛好滿足這樣的應用場景，它可以把索引範圍壓縮到一個合理範圍內。

　　假設建立了一張分割槽表，裡面有9個分割槽，分別儲存了17年和18年的資料，其中儲存17年資料的四個分割槽我們做了INDEX OFF操作，在建立索引時，我們會發現雖然建立了9個索引段，但做了操作的四個分割槽是不可用的，實際只建立了5個索引段。

　　當你訪問部分分割槽索引時，它會直接把執行計劃分成兩部分，一部分是在索引段裡掃描，還有一部分是在對應內容裡掃描。部分分割槽索引其實最大的改變是它可以分辨哪些索引是可用的，哪些是不可用的。

　　如果在指定鍵值的同時再加上一個時間，那這個時間就是我們分割槽線。當我們訪問資料時，Oracle就可以根據時間知道我要訪問哪個區，其它無需訪問的區雖然也是有資料的，但Oracle會在這裡有個設定恆為假條件的filter，當訪問到這裡時會直接跳到下一部分。

　　Oracle 12.1非常強大的一個功能點是索引非同步維護。當建立範圍分割槽時，由於經常要做DDL的清理，所以不建議大家去建全域性索引。但是Oracle 12解決了這個問題，假設1月4日對應資料分割槽裡有31萬條記錄，3月5日對應的資料分割槽有近30萬條記錄，當我們去做建立分割槽操作時，花費時間大約為0.18秒，然後再檢查狀態時，不出意外，全域性索引已經失效了。重建之後，我們在分割槽建立時加上一個Update Index的操作，因為要同步DDL操作，它會更新所有狀態，很多人認為它會變得非常慢，其實它只用了0.17秒，比之前的0.18秒還要快。這是因為Oracle 12之後，它會自動給資料打標識，並不是真正同步去維護，而是在後臺非同步維護，不僅提高了索引的可用性，同時還提高了效率。

　　Oracle 12.2很有意思的一個特性是自動列表分割槽，我們之前建議在列表分割槽時一定要加上DEFAULT值，否則資料插入會報錯，尤其是當一張表只有一個Keyboard，在自動列表分割槽中插入資料是非常困難的。而Oracle提供的很方便的功能是線上把一張普通錶轉換成分割槽表，這樣我就不會因為是分割槽表而引入停機、維護等。

　　Oracle 18c中針對這種情況提供了更強的改變，例如我建立了一個分割槽表和三個索引，當我從普通表變成分割槽表之後，但我對分割槽表策略不滿意，可以直接通過Oracle語句來改變資料表策略，而且這個操作可以在DDL發生時線上去做。

　　最後一個功能是針對變更之後的索引，我們可以線上去做分割槽的MERGE操作，通過線上的方式把其中幾個分割槽合併。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31137683/viewspace-2156125/，如需轉載，請註明出處，否則將追究法律責任。