最佳化資料庫物件

標籤：

1. 最佳化表的資料類型

      在MySQL 中，可以使用函數PROCEDURE ANALYSE()對當前應用的表進行分析，該函數可以對資料表中列的資料類型

提出最佳化建議，使用者可以根據應用的實際情況酌情考慮是否實施最佳化。

使用方法：

SELECT * FROM tbl_name PROCEDURE ANALYSE();

SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);

（不要為那些包含的值多於16 個或者256 位元組的ENUM 類型提出建議）

 

2. 通過拆分提高表的訪問效率

（1）垂直分割：把主碼和一些列放到一個表，把主碼和另外的列放到另一個表中。

       如果一個表中某些列常用，而另外一些列不常用，則可以採用垂直分割，另外垂直分割可以使得資料行變小，一個資料

頁就能存放更多的資料，在查詢時就會減少I/O 次數。其缺點是需要管理冗餘列，查詢所有資料需要聯合（JOIN）操作。

（2）水平分割：根據一列或多列資料的值把資料行放到兩個獨立的表中。

    通常在以下幾種情況下使用：

I. 表很大，分割後可以降低在查詢時需要讀的資料和索引的頁數，同時也降低了索引的層數，提高查詢速度。

II. 表中的資料本來就有獨立性，例如，表中分別記錄各個地區的資料或不同時期的資料，特別是有些資料常用，而另外一些

資料不常用。

III. 需要把資料存放到多個介質上。

      水平分割會給應用增加複雜度，它通常在查詢時需要多個表名，查詢所有資料需要UNION 操作。在許多資料庫應用中，

這種複雜性會超過它帶來的優點，因為只要索引關鍵字不大，則在索引用於查詢時，表中增加2 至3 倍資料量，查詢時也就增

加讀一個索引層的磁碟次數，所以水平分割要考慮資料量的增長速度，根據實際情況決定是否需要對錶進行水平分割。

 

3. 逆正常化

       反規範的好處是降低串連操作的需求、降低外碼和索引的數目，還可能減少表的數目，相應帶來的問題是可能出現資料

的完整性問題。加快查詢速度，但會降低修改速度。因此決定做反規範時，一定要權衡利弊，仔細分析應用的資料存取需求和

實際的效能特點，好的索引和其他方法經常能夠解決效能問題，而不必採用反規範這種方法。

       常用的反規範技術有增加冗餘列、增加衍生的資料行、重新組表和分割表。

（1）增加冗餘列：指在多個表中具有相同的列，它常用來在查詢時避免串連操作。

（2）增加衍生的資料行：指增加的列來自其他表中的資料，由其他表中的資料經過計算產生。增加的衍生的資料行其作用是在查詢時減少

串連操作，避免使用集合函式。

（3）重新組表：指如果許多使用者需要查看兩個表串連出來的結果資料，則把這兩個表重新組成一個表來減少串連而提高效能。

（4）分割表：根據需求將一個表分割成多個表格儲存體。

        逆規範技術需要維護資料的完整性。無論使用何種反規範技術，都需要一定的管理來維護資料的完整性，常用的方法是

批處理維護、應用邏輯和觸發器。

（1）批處理維護：指對複製列或衍生的資料行的修改積累一定的時間後，運行一批處理作業或預存程序對複製或衍生的資料行進行修改，

這隻能在對即時性要求不高的情況下使用。

（2）資料的完整性也可由應用邏輯來實現，這就要求必須在同一事務中對所有涉及的表進行增、刪、改操作。用應用邏輯來

實現資料的完整性風險較大，因為同一邏輯必須在所有的應用中使用和維護，容易遺漏，特別是在需求變化時，不易於維護。

（3）另一種方式就是使用觸發器，對資料的任何修改立即觸發對複製列或派生列的相應修改。觸發器是即時的，而且相應的

處理邏輯只在一個地方出現，易於維護。一般來說，是解決這類問題比較好的辦法。

 

4. 使用中間表提高統計查詢速度

        對於資料量較大的表，在其上進行統計查詢通常會效率很低，並且還要考慮統計查詢是否會對線上的應用產生負面影響。

通常在這種情況下，使用中間表可以提高統計查詢的率。

       使用中間表的優點：

（1）中間表複製源表部分資料，並且與源表相“隔離”，在中間表上做統計查詢不會對線上應用產生負面影響。

（2）中間表上可以靈活的添加索引或增加臨時用的新欄位，從而達到提高統計查詢效率和輔助統計查詢作用。

 

最佳化資料庫物件