《Oracle效能優化求生指南》-第四章：資料庫邏輯設計和物理設計-學習小結-1

bisal發表於2014-03-15
Oracle優化資料庫
1、建立邏輯資料模型為第一階段，包括對應用程式需要處理和儲存的資訊進行建模，並確保所有必要的資料都能夠正確、完整且無歧義地表示。在關聯式資料庫的實現中，這通常是指構造一個標準化的實體-關係(E-R)模型。
2、將邏輯資料模型對映為物理資料模型為第二階段。對關聯式資料庫來講，物理資料模型描述的是表、索引、檢視、鍵和其他一些資料庫特性。
3、第三正規化：
實體(表)的所有資料完全依賴於主鍵。
不能有重複的屬性(列)或屬性組。
不存在僅依賴部分主鍵的實體資料。
不存在依賴於其他非主鍵的實體資料。
用一條格言描述：”鍵，完整的鍵，除了鍵沒有其他東西。“
4、無論從文件或定義角度看，邏輯模型中精確定義屬性的資料型別、長度、精度都有優勢。由於Oracle在資料型別的物理儲存上採用的是通用且靈活性很強的內部實現方式，因此從儲存或效能角度看，使用限制性很強的資料型別或精度並沒有優勢。
5、不管如何限制資料型別，大部分情況下，Oracle內部都會使用大範圍高精度的浮點方式進行儲存。
6、固定長度型別的字串都會佔用一個固定長度的儲存空間，而不管字串的真實長度是多少。採用固定的航長度可以降低碎片，但是它會導致較大的平均行長，這樣就會增加全表掃描的開銷。因此，除非資料的長度確實是固定的，否則就應該優先選擇變長字串型別(VARCHAR)。
7、人造鍵：
是由Oracle sequence產生的一個數字型別的列。
沒有任何含義，只是為了唯一地標識實體中的記錄。
從來不會被更新。
自然鍵：
可由多列組成並可包括任何資料型別。是由實體中具有唯一性的自然屬性構成的。
如果自然鍵被更新，則引用它的外來鍵也需要更新，這將顯著增加IO開銷和鎖爭用。
顯然，必須基於自然鍵的列進行索引查詢的需求也是很常見的，為滿足這種需求，可以在這些列上建立普通索引或唯一約束。
8、邏輯階段的主要目的是確保設計能夠滿足應用的功能需求。物理設計階段的目的才是確保資料庫能夠滿足應用的效能需求。將邏輯自雷轉化為表的時候，要避免採用分開的父類表和子類表的方法，而要選擇將所有子類對映為一個表，或採用單獨的子類表而沒有上一級表的方式。
9、表的型別有堆表、索引組織表、聚簇等。
雜湊聚簇(Hash Cluster)：能夠有效優化大小相對固定的表的主鍵查詢，與B*樹索引相比，雜湊聚簇還能降低熱點塊上的閂鎖爭用。
索引聚簇(Index Cluster)：共享聚簇鍵值的多個表中的記錄儲存在一起，這樣可以優化多表聯結。雖然多表聯結性增強了，但僅針對聚簇中某個表的全表掃描的效能卻降低了。
巢狀表(Nested Table)：能夠針對主表中的某一行優化對明細行的檢索。但是，如果要跳過主錶行檢索明細行的話，效能通常會大打折扣。
索引組織表：如果大部分表訪問都是通過主鍵進行查詢，並且表資料量的變動幅度較大而不適合使用雜湊聚簇，使用索引組織表將會更高效。
物件表(Object Table)：行都被定義為一個Oracle物件資料型別，需要使用物件表的場景非常少。
10、精度的意義更多在於約束資料或定義文件，而不是為了優化效能。但是，如果一個高精度的數值被偶然指定給一個精度限制較弱的數字欄位，這時候設定一個精度就可能對效能有所幫助。例如NUMBER(*,2)那麼不必要的精度將會被截斷，行的長度也將相應地減少。設定該數值列的精度有時可以減少行的長度。
11、DATE可以精確到秒，TIMESTAMP可以亞秒級，可以配置到納秒級的精度，其預設精度則為微秒。
12、字串若小於4000位元組，優先選擇VARCHAR2型別。
字串若大於4000位元組，推薦使用LOB型別，CLOB儲存字元資料，BLOB儲存二進位制資料。
11g提供高效能的LOB儲存型別，稱為SecureFiles。
每個表只能有一個LONG資料型別的列，和早期的Oracle版本相容。
優先選擇VARCHAR2不是VARCHAR，VARCHAR將來會受到修改，以符合ANSI的標準。
13、B*樹索引不能儲存NULL，因此需要全表掃描來查詢NULL值。點陣圖索引和(部分列為NULL的)多列組合索引就能儲存NULL值。
採用NULL可以降低行的平均長度，從而一定程度上提高全表掃描的效能。
如果該列的數值大部分是NULL，並且查詢僅需檢索非NULL的值，則該列上的索引會比較緊湊並很高效。
因此，決定一列是否可以為NULL的時候，要考慮在該列上是否有使用B*樹索引查詢NULL的需求。如果有，則不能指定該列為NULL，取而代之的是定義該列為NOT NULL(非空)，並指定一個預設值。
如果要查詢那些未知的值，則不能定義列為空，相反，要定義列為非空並指定一個預設值。
對於字元型別的資料，預設值可以是一個字串，例如UNKNOWN或N/A。
對於數字型別，選擇合適的預設值會有難度，例如，統計包含AGE欄位，基於索引掃描或查詢AGE列時，可能需要找出年齡不確定(UNKNOWN)的記錄。如果給AGE指定一個預設值，當查詢平均年齡、最小年齡和最大年齡的時候，可能會得到不正確的結果。這種情況，使用NULL時必要的，但查詢不能快速返回那些AGE不確定的記錄，要麼進行反規範化，增加一個標記列來標記年齡是否已知，並在該標記列上建立索引，以便於查詢AGE不確定(AGEKNOWN=N)的記錄。NULL值可以儲存在點陣圖BITMAP索引中，上述中或許不會僅因為這個理由選擇點陣圖索引，如果該AGE列上存在點陣圖索引，可以高效地檢索NULL值。
14、除非表中每一個欄位都是固定長度的字串，否則Oracle無法知道某列在行物理儲存結構中的具體位置。
訪問表中靠後的列相比訪問靠前的列，需要額外消耗少量的CPU資源。
因為Oracle必須順序掃描行結構以獲得某個特定的列的位置，基於這個原因，將經常需要訪問的列儲存在表的前面，會帶來一些正面的效能影響。
那些值為NULL的欄位通常需要一個位元組的儲存空間，但如果該行中隨後的列的數值都為NULL，則Oracle不需要為這些NULL分配任何空間。如果將那些大部分為NULL的列儲存在表的末尾，則行的實際長度會變小，這樣有助於提高表掃描的效能。
這些調整帶來的效能改進比較小，因此列的邏輯順序應使資料模型更易於理解和維護，而不是為了這些微小的優化而使邏輯模型中列的順序變得混亂。
15、反規範化：是指在物理模型中重新引入冗餘、重複或其他非規範化結構的過程，主要意圖是為了提高效能。
16、概要表
如果實時彙總資料是必需的，則每當源資料被更新時，必需同時更新彙總資料。通過資料庫觸發器或物化檢視來手動實現這一點，但若更新十分頻繁，則可能導致鎖爭用。
如果實時彙總資訊不實必需的，則可以通過定期的作業排程更新概要表-費業務高峰期進行。使用Oracle物化檢視機制實現。但會導致不實時的不準確的彙總資訊。
17、Oracle 11g的結果集快取。
18、進行聚合操作的查詢通常會佔用大量資料庫資源，最好是使用物化檢視來維護反規範化的彙總資訊。
19、通常，將邏輯子型別實現為從表會降低常見SQL操作的效能。
垂直分割槽(Vertical Partitioning)：經常在全表掃描操作中訪問到的列需要保留在主表中，較少訪問的列則儲存在第二章表中。
20、檢視：給使用者只有一張表的假象。通常在應用程式查詢子表的時候用以獲得最佳效能，代價是使SQL應用複雜化。
21、Oracle有時會允許將一張表物理地分割成多個資料段，同時讓使用者保留一個單一邏輯表的印象，例如：
LOB資料通常會儲存在單獨的資料段。
索引組織表中，一些資料將被儲存在溢位段中。
如果表很大，且預計會有頻繁表掃描，可以考慮將欄位較長且不常訪問的列遷移到一個單獨的子表中，以減少長度和提高表掃描的效能。
22、優先使用資料庫觸發器來保證反規範化資料的一致性，避免通過應用程式碼來維護。資料庫觸發器降低了資料不一致的風險，簡化了應用程式碼，同時更加高效。
23、事實表的外來鍵幾乎都是由序列生成的人造數字鍵。從儲存的觀點看，如果事實表的外來鍵都是有意義的字串的話，則儲存成本比較高：例如，一個產品的具體名稱佔用的空間可能是產品ID佔用空間的10倍。保持較短的行長度對事實表來說是重要的，因為事實表上會經常有全表(或分割槽)掃描。
24、儘量避免使用雪花模式。當維度表不包括外來鍵的時候，查詢效能通常會得到優化。
25、通過建立層次化的維，可以提高那些需要使用較高層次聚集資料的查詢的效能，例如，查詢每月總收入將不再需要累加該月份中每天的收入記錄，從而大大提高了效能。
可以考慮針對多層次的聚集資料採用多個事實表的方式，採用多個事實表是常用的資料倉儲解決方案，但Oracle還提供了一個更復雜的稱為物化檢視的方法。
物化檢視本質是一個物理表，它包含了將會由檢視的定義返回的記錄。如果將普通的檢視看做儲存在資料庫中的查詢語句，物化檢視就可以被看作是資料庫中儲存的查詢結果。
Oracle可以自動維護物化檢視，保證是最新的或是定期更新。
26、物化檢視最佳實踐：
針對物化檢視所包括的每一張表，都要建立物化檢視日誌。
使用CREATE DIMENSION語句來標識各維度之間的層次關係。
設定引數QUERY_REWRITE_ENABLED，啟動查詢重寫功能。
選擇合適的物化檢視重新整理策略。推薦使用ON DEMAND重新整理策略，不是ON COMMIT重新整理策略，適用於大量資料更新時重新整理。
選擇引數QUERY_REWRITE_INTEGRITY。
合理建立物化檢視。如果有疑慮，則需要比較並評估建立與不建立物化檢視對查詢和DML語句的效能影響，以衡量物化檢視帶來的額外開銷能否從查詢效能的提高中得到足夠的補償。

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

相關文章