如何優化資料庫物件

狗福發表於2016-09-28
優化資料庫物件
優化表的資料型別
    應用設計的時候需要考慮欄位長度留有一定冗餘，但是不能留有大量的冗餘。這樣浪費磁碟儲存空間，同時在應用程式操作時也浪費實體記憶體。
    資料型別通常更小的更好。一般應該儘可能使用能正確儲存資料的最小資料型別，更小的資料型別通常更快，因為他們佔用更少的磁碟、記憶體和CPU快取，並且處理需要的CPU週期更少。
     整型比字串操作代價更低，一般整型>字串>時間

    在MySQL中 可以使用函式PROCEDURE ANALYSE()對當前應用的表進行分析
    語法 select * from tb1_name procedure analyse();
        輸出的每一列資訊都會對資料表中的列的資料型別提出優化建議
    select * from tb1_name procedure analyse(16,256);
        不要為那些包含的值多餘16個或者256個位元組的ENUM型別提出建議 
        如果沒有這樣的限制 輸出資訊可能很長 ENUM定義通產很難閱讀

通過拆分提高表的訪問效率
    MyISAM型別的表拆分
        1 垂直拆分 按照不同的表（或者Schema）來切分到不同的資料庫（主機）之上，這種切可以稱之為資料的垂直（縱向）切分，即把主碼和一些列放到一個表 然後把主碼和另外的列放到另一個表中。
            適用情況:一個表中的某些列常用，而另一些列不常用，則可以採用垂直拆分 
            優點:垂直拆分可以使得資料行變小，一個資料頁就能存放更多的資料，減少I/O次數。這樣使得資料模組更清楚，資料維護起來更方便。
            缺點:需要管理冗餘列，查詢所有資料需要聯合操作，有些查詢無法再資料庫層面解決，如果依然有大表造成的效能問題，解決不了。並且切分到一定程度的話，擴充套件性就會遇到限制。過度切分可能會帶來系統過渡複雜而難以維護。
        2 水平拆分 將某個訪問極其頻繁的表再根據一列或多列資料的某種規則來分散到多個獨立表之中，每個表中包含一部分資料。
            適用情況:
                1 表很大 分割後可以降低在查詢時需要讀的資料和索引的頁數 同時也降低了索引的層數提高查詢速度
                2 表中的資料本來就有獨立性
                3 需要把資料存放到多個介質上

逆規範化
    資料庫規範化越高 產生的關係越多 關係過多的直接結果就是導致表之間的連線操作越繁瑣 而表之間的連線操作室效能較低的操作 直接影響到查詢的速度 所以 對於查詢較多的應用 就需要根據實際情況運用逆規範化對資料進行設計 通過逆規範化來提高查詢的效能
    反規範的好處是降低連線操作的需求降低外碼和索引的資料 還可能減少表的數目 相應帶來的問題是可能出現資料的完整性問題 加速查詢速度 但會降低修改速度
    使用反規範要仔細分析應用的資料存取需求和實際的效能特點 能用索引和其他方法解決問題就不使用反規範
    常用反規範方法
        1 增加榮譽列 在表中具有相同列 他常用來在查詢時避免連線操作
        2 增加派生列 增加的列來自其他表中的資料 由其他表中的資料經過計算生成 增加的派生列 其作用是在查詢時減少連線操作 避免使用集函式
        3 重新組表  如果許多使用者需要檢視兩個表連線出來的結果資料 則把這兩個表重新組成一個表來減少連線而提高效能
        4 分割表 水平分割和垂直分割
    管理和維護資料的完整性的常用方法
        1 批處理維護是指對複製列或派生列的修改積累一定時間後執行一批處理作業或儲存過程對複製或派生列進行修改 這隻能在對實時性要求不高的情況下使用
        2 資料的完整性也可由應用邏輯來實現 這就要求必須在同一事物中對所有涉及的表進行增刪改操作 而應用邏輯來實現資料的完整性分先較大 因為統一邏輯必須在所有的應用中使用和維護 容易遺漏特別是在需求變化時 不易於維護
        3 觸發器 對資料的任何修改立即觸發對複製列或派生列的相應修改 觸發器是實時的 而相應的處理邏輯只在一個地方出現 易於維護 一般來說 是解決此類問題比較好的辦法

使用中間表提高查詢速度
    場景 在資料量較大的表 在其上進行查詢通常會效率很低 並且還要考慮統計查詢是否會對線上的應用產生負面影響 
    優點
        1 中間表複製源表部分資料 並且與源表相隔離 在中間表上做統計查詢不會對線上應用產生負面影響
        2 中間表上可以靈活地新增索引或者增加臨時用的新欄位 從而達到提高統計查詢效率和輔助統計查詢作用


相關文章