第 12 章 MySQL 最佳化應用實務

第12章MySQL最佳化應用實務 主從式資料庫系統 - CH12

本章宗旨 • 介紹系統最佳化之觀念 • 簡介造成系統瓶頸的原因 • 介紹資料庫改善實務 • 介紹快取的觀念與應用方法 • 介紹MySQL最佳化工作原理 • SQL指令稿分析與改善實務 • 簡介資料表上鎖方式與優缺點比較 • 說明資料表開啟與關閉之處理方式主從式資料庫系統 - CH12

大綱 • 12-1 最佳化總覽 • 12-2 資料庫結構改善實務 • 12-3 快取的應用 • 12-4 MySQL伺服器最佳化處理 • 12-5 SQL指令最佳化處理 • 12-6 資料表上鎖應用與問題探討 • 12-7 資料表開啟與關閉主從式資料庫系統 - CH12

12-1 最佳化總覽 • 系統最佳化的因素 - 基礎設計 • 了解系統要求 • 了解系統限制 • 了解主要瓶頸所在主從式資料庫系統 - CH12

12-1 最佳化總覽(續) 12-1-1 系統瓶頸原因 12-1-2 MySQL的限制和替代方案 12-1-3 可攜性問題主從式資料庫系統 - CH12

12-1-1 系統瓶頸原因 • 磁碟搜尋 • 磁碟讀寫 • CPU速度 • 記憶體及頻寬主從式資料庫系統 - CH12

12-1-2 MySQL的限制和替代方案 • MyISAM儲存引擎以極快的速度鎖定資料表 • MySQL允許多個讀取連線和一個寫入連線 • 處理資料之唯一問題，將發生在單一資料表之混合式更新(mixed update)和緩慢的選取動作 • 處理資料表若經常發生緩慢的問題，需要考慮改用其他儲存引擎主從式資料庫系統 - CH12

12-1-2 MySQL的限制和替代方案(續) • MySQL處理資料的方式，可分成交易式和非交易式兩種 • 採用非交易模式時，發生錯誤資料無法捲回起始狀態（Roll Back） • 為了平穩的處理資料，需要搭配非嚴格模式（Strict Mode）或是使用IGNORE屬性來處理INSERT 或UPDATE指令主從式資料庫系統 - CH12

12-1-2 MySQL的限制和替代方案(續) • 搭配非嚴格模式（Strict Mode）將使用下列預設規則 • 所有欄位加上預設值 • 插入不正確的資料時，MySQL將以最接近的資料代替 • 所有運算式發生錯誤時，將傳回一個替代值來取代錯誤信息主從式資料庫系統 - CH12

12-1-2 MySQL的限制和替代方案(續) • 採用非交易式的模式時，不要使用MySQL之欄位內容檢查模式，這樣會拖慢處理速度 • 欄位內容之檢查工作不要放在MySQL伺服端，而應放在應用程式端來處理，亦即在主控端先做檢查，正確的資料才寫回伺服端，這樣可以大幅提升伺服器的效率主從式資料庫系統 - CH12

12-1-3 可攜性問題 • MySQL伺服器會應用一些非標準的SQL指令，因此發展MySQL應用系統時，需要考慮將來可能移植的SQL伺服器種類 • 對於簡單的SELECT和INSERT應用介面，通常不會發生相容性的問題 • 隨著應用介面複雜度的增加，應用系統的可攜性就愈來愈低 • 指令稿內含MySQL專屬的註解、函數、資料型態和限制時，可能會發生可攜性問題主從式資料庫系統 - CH12

12-2 資料庫結構改善實務 12-2-1 MySQL資料庫結構 12-2-2資料表設計實務 12-2-3 欄位索引主從式資料庫系統 - CH12

12-2-1 MySQL資料庫結構 • MySQL將紀錄與索引鍵值分開儲存在不同的檔案 • 分開儲存更適合現代的磁碟及檔案管理系統主從式資料庫系統 - CH12

12-2-1 MySQL資料庫結構(續) • 將紀錄與索引鍵值儲存在一起的缺點 • 查詢資料時，無法單靠索引表取得所需資料 • 若需掃描資料表時，因為需透過索引表才能取得資料，速度將緩慢許多 • 刪除資料時，在節點上的索引無法立即更新，因此刪除會拖累資料表一點時間 • 需耗用更多的空間，因為索引儲存在節點時，經常發生重複問題，而紀錄也無法儲存在節點上主從式資料庫系統 - CH12

12-2-2資料表設計實務 • 提升資料處理效率的最高原則，乃將資料表設計得越精巧越好，除了可增快磁碟的讀取速度，所需記憶體空間也可以節省 • 索引鍵越短越好，索引表因較小的欄位而更精簡，耗用資源及處理負荷更少主從式資料庫系統 - CH12

12-2-2資料表設計實務 • 設計資料表時儘可能遵照下列原則 • 選用更有效的（較小的）資料型態 • 欄位儘可能加上Not NULL預設值，除了可增快處理速度，每個欄位可節省一個位元的空間 • 儘量不要使用變動長度的欄位，如此將可使得紀錄長度為定長，好處在於處理速度可更快，但是較浪費空間 • 如果要用Varchar型欄位，可以在建立資料表時，選用”ROW_FORMAT = Fixed”選項主從式資料庫系統 - CH12

12-2-2資料表設計實務(續) • 主索引鍵愈短愈好 • 假如經常需要透過某些欄位處理資料，可以為這些欄位建立索引表，但太多的索引表會拖慢儲存速度 • 假如字元型資料的前幾碼具有唯一性，可以針對這個欄位之部分字元建立索引 • 經常需要掃描一個動態紀錄格式的資料表時，將動態紀錄格式（不定常長紀錄）資料表分割成動態和靜態兩部份主從式資料庫系統 - CH12

12-2-3 欄位索引 • MySQL之欄位都可加以索引 • 執行Select 指令時，相關的欄位若加上索引，將可增快資料讀取速度主從式資料庫系統 - CH12

12-2-3 欄位索引(續) • 索引限制 • 每個資料表至少可建立16個以上的索引表，索引長度至少256以上位元組 • 索引表的結構有多種 • Primary Key, Unique, Index, Fulltext等型式索引使用B樹 • Spatial型欄位使用R樹 • 而Memory(Heap)型索引則使用雜湊(Hash) 及B樹 • CHAR及VARCHAR型欄位，可以使用部分前導字元來索引，前導字元長度可達255位元組主從式資料庫系統 - CH12

1. 索引限制(續) • MyISAM支援CHAR、VARCHAR及TEXT型欄位之”FULLTEXT”全文索引 • MySQL也支援多重欄位索引（多鍵型），欄數最多可達15個 • Where子句雖只用到多鍵型索引之第一個欄位，該索引表將自動被選用來比對紀錄主從式資料庫系統 - CH12

2. MySQL引用索引檔之方式 • 在下列情況下MySQL將自動引用索引表： • 有Where子句的SQL指令，將可增快讀取資料速度 • 有多個索引表可引用時，MySQL將選用讀取最少紀錄之索引表 • 以關聯型式處理資料時 • 執行統計函數時，例如Min(Key_Col)，Max(Key_Col)等，MySQL將自動引用以Key_Col為索引鍵之索引表主從式資料庫系統 - CH12

2. MySQL引用索引檔之方式(續) • 有Order By或Group By子句的SQL指令，將自動選用合乎最左欄位之索引表 • 查詢資料時，若相關欄位可直接從索引表取得時，MySQL將略過原始紀錄資料 • B樹型索引表可搭配＝、＞、＞＝、＜、＜＝、Between等關係運算子來比對紀錄 • 可以使用Like比對”%String%” 樣板，其中String必須大於三個字元 • Where子句若有And邏輯運算子時，索引最佳化運算機制不一定會啟用，端看And前後之鍵值欄是否衝突主從式資料庫系統 - CH12

12-3 快取的應用 • 快取(Cache)的功能，主要在於減少磁碟的I/O次數，從而效提升系統執行效率 12-3-1 MyISAM資料表的快取用法 12-3-2共用快取空間 12-3-3 多重索引快取的設定和使用 12-3-4 預先載入索引策略 12-3-5 快取空間調整主從式資料庫系統 - CH12

12-3-1 MyISAM資料表的快取用法 • 處理方式分成兩類 • 對於索引區塊，MySQL使用一個稱為”Key Cache”（或稱Key Buffer）之特殊結構，會一直被維護並用來存放使用最為頻繁的索引區塊 • 對於資料區塊，MySQL並不使用特殊的快取，快取處理將交由作業系統之檔案系統快取來管理主從式資料庫系統 - CH12

12-3-1 資料表的快取使用法(續) • MyISAM快取使用方式： • 多個執行緒可同步使用快取，不需要予以序列化 • 同時可以設定多個快取，不同資料表的索引表可指定存放在專用的快取內主從式資料庫系統 - CH12

12-3-1 資料表的快取使用法(續) • 快取空間設定 • 可用系統變數 ”key_buffer_size” 設定快取空間，此變數值設為0時，表示不使用快取 • 若設定值太小，以致於無法容納最小的索引區塊數(8)時，快取就無法使用 • 假如快取無法正常工作，索引檔將回歸檔案系統之緩衝區來管理 • 通常索引區塊的大小是等於索引樹的節點數，樹端的節點(最底層)被稱為葉節點(leaf node)，非底層的節點被稱為非葉節點(non-leaf node) 主從式資料庫系統 - CH12

12-3-1 資料表的快取使用法(續) • 資料存取順序 • 假如有可用的索引區塊時，伺服器將直接從快取存取資料，而不需要回至磁碟 • 若無可用索引區塊時，伺服器將選取一快取緩衝區來存放從資料表索引區塊之副本，然後再以快取來存取資料 • 當被選定的區塊要以置換方式(replacement)存入時，則該區塊會視為”用過了(dirty)”，在替換前，快取資料會被沖回(flush)，以便更新索引表主從式資料庫系統 - CH12

12-3-1 資料表的快取使用法(續) • 快取區塊處理方式 • MySQL是採用LRU(Least Recently Used)原則，所謂LRU是指最久未再使用的索引區塊 • 為了便於管理快取區塊，MySQL使用一個佇列結構(queue)來存放用過的索引區塊，當一個區塊被存取時，該區塊將置於佇列底端 • 當需要替換區塊時，佇列開頭之區塊將優先被替換主從式資料庫系統 - CH12

12-3-2 共用快取空間 • 下列情況MySQL允許共用快取資料： • 當緩衝區資料未被更新時，快取可被多個執行緒共用 • 緩衝區資料被更新時，所有執行緒均須等候，直到完成更新處理 • 只要執行緒所使用的快取區塊不互相干擾，各執行緒均可啟動快取區塊的替換請求主從式資料庫系統 - CH12

12-3-3 多重索引快取設定和使用 • 共用快取雖可提升系統執行效率，但是難免會發生執行緒相互競爭的問題 • 為避免相互競爭的問題的發生， MySQL採用多重快取技術，使得不同的執行緒可指定專用的快取來存取資料主從式資料庫系統 - CH12

12-3-3 多重索引快取設定和使用(續) 1. 建立快取 • 指令語法： Set Global keycach1.key_buffer_size=128*1024; • 說明：代表意義為建立一個名稱叫”keycach1”的快取，快取空間設定為128*1024位元組 • 未建立指名的快取時，MySQL將使用預設的快取空間主從式資料庫系統 - CH12

12-3-3 多重索引快取設定和使用(續) 2. 取消快取 • 取消快取指令語法： Set Global keycach1.key_buffer_size=0; 3. 快取的指派 • 指派快取空間指令語法： CACHE INDEX tbl1, tbl2, tbl3 IN keycach1; • 說明：將資料表tbl1, tbl2, tbl3之索引區塊存放在名稱叫”keycach1”之快取內主從式資料庫系統 - CH12

12-3-3 多重索引快取設定和使用(續) 4. 多重快取空間之分配策略一個非常忙碌的伺服器，MySQL建議將快取空間分割成三塊： • 熱鍵區(hot key cache) • 冷鍵區(cold key cache) • 平常區(warm key cache) 主從式資料庫系統 - CH12

4. 多重快取空間之分配原則 • 熱鍵區：分配20％的空間，指派給只被查詢但不需更新的資料表使用 • 冷鍵區：分配20％的空間，指派給資料需要稍為密集異動的資料表使用，例如一些暫存表 • 平常區：分配60％的空間做為預設區，未指派快取空間的資料表均使用此區域主從式資料庫系統 - CH12

12-3-3 多重索引快取的設定和使用(續) 5. 中點插入策略 • 所謂中點插入策略（Midpoint Insertion Strategy），是MySQL提升處理效率的另一種索引表存取技術，處理過程說明如下： • 將原來使用LRU技術之資料鏈分割成兩部份：hot sub-chain(HSC)和warm sub-chain(WSC)，兩部份不需等分，但WSC也不能太短，分割點可在系統變數 ”key_cache_division_limit” 設定 • 初次讀取之索引區塊將置於WSC之末端主從式資料庫系統 - CH12

5. 中點插入策略 • 置於WSC之索引區塊經數次之檢索使用之後（現階段之MySQL設定值為3次），將被轉存於之HSC之末端 • 在HSC之索引區塊若長時間未被檢索，則會被移回WSC之開頭，其比對時間可在系統變數 ”key_cache_age_threshold” 設定 • threshold可用來決定被移回WSC之區塊數，若起始HSC有N個區塊，則被移回數等於 N * key_cache_age_threshold / 100。被移回之區塊將優先被替換，由於它是位於WSC之開頭處主從式資料庫系統 - CH12

5. 中點插入策略(續) • 中點插入策略可確保更有價值之區塊才置於快取。假如你不想使用此策略，你可以沿用LRU策略，只要將key_cache_division_limit改回預設值100即可 • 要提高中點插入策略之效率，只需將key_cache_division_limit 設為小於100之值主從式資料庫系統 - CH12

12-3-3 多重索引快取的設定和使用(續) 6. 重建快取 • 建立一個新的快取，只需重設其大小即可，重建指令範例如下： Set Global cold_cache.key_buffer_size = 5*1024*1024 • 只要重新設定 ”key_buffer_size” 或 “key_buffer_size” 之大小，MySQL立即將舊的快取毀掉，然後重新建立一個新的快取主從式資料庫系統 - CH12

12-3-4 預先載入索引策略 • 假如快取空間足夠存放所有的索引區塊，或是至少可存入所有非葉節點之索引區塊，在處理資料前，事先將索引區塊載入快取，將可提升資料處理效率 • 預先載入索引是以循序方式從磁碟讀取索引資料 • 未預先載入索引區塊，當需要搭配索引表來讀取資料時，索引區塊將以隨機方式從磁碟讀取主從式資料庫系統 - CH12

12-3-4 預先載入索引策略(續) • 事先載入索引區塊之指令如下： Load Index Into Cache tbl1, tbl2 Ignore Leaves; 說明：上述指令意義表載入tbl1之全部索引區塊，而tbl2只載入非葉節點之索引區塊 • 事前已執行Cache Index指令，則索引區塊將被存入指定之快取，否則將存入預設之快取區主從式資料庫系統 - CH12

12-3-5 快取空間調整 • 系統變數 “key_cache_block_size ” 用於設定索引區塊緩衝區，可用來調整I/O之效率 • 當讀取緩衝區之大小設成和原作業系統之I/O緩衝區一樣時，將使得I/O之效率最好 • 將索引鍵節點數調整成和I/O緩衝區相同，並不能確保整體效率會最好，當讀取一個有大量葉節點的索引資料時，伺服器將需要提供大量無用的資料，因而產生排擠效應主從式資料庫系統 - CH12

12-4 MySQL伺服器最佳化處理 • MySQL伺服器的最佳化處理，可從調整系統啟動參數和系統作業環境參數兩方面來著手 12-4-1 系統因素與啟動參數 12-4-2 系統作業環境參數主從式資料庫系統 - CH12

12-4-1 系統因素與啟動參數 • 使用多重CPU之作業系統可提昇系統效率 • 假如有足夠的RAM可使用，可將所有的SWAP裝置移除 • 使用 ”--skip-external-locking” 啟動參數以避免外部鎖定主從式資料庫系統 - CH12

12-4-2 系統作業環境參數 • 可用下列指令檢視作業環境參數設定值 • C:\mysql\bin> mysqld --verbose --help • C:\mysql\bin mysqladmin variables • C:\mysql\bin mysqladmin status • Show Variables; (啟動伺服器後) • Show Status; (啟動伺服器後) • 使用Set指令來設定作業環境參數值主從式資料庫系統 - CH12

12-5 SQL指令最佳化處理 • 以SELECT指令當範例，介紹常見的最佳化應用技巧 • 指令的處理效率將隨著系統的權限設定值的複雜度而改變，權限設定越複雜，系統的負擔也就越重 • 建議將授權工作置於應用程式介面上，擁有權限的人方能開啟對應的應用程式，這種作法將能大幅減少伺服端的查核負荷 • 有時系統執行速度減慢的原因，並非導因於權限查核，而是來至於MySQL之運算式或函數本身主從式資料庫系統 - CH12

12-5 SQL指令最佳化處理 12-5-1 SELECT指令稿分析 12-5-2 查詢效率分析 12-5-3 SQL指令加速法則 12-5-4 最佳化工作原理 12-5-5 避免掃描資料表之基本法則主從式資料庫系統 - CH12

12-5-1 SELECT指令稿分析 1. 指令稿分析指令 • 使用【EXPLAIN】指令來分析指令稿，MySQL會提供一序列的分析結果，讀者可依據分析結果來改善指令稿 • EXPLAIN指令用法有兩種： • 語法一：Explain 【資料表名】 • 語法二：Explain 【SELECT指令稿】 • 語法一可用來分析資料表結構，語法二可用來分析SELECT指令稿結構主從式資料庫系統 - CH12

12-5-1 SELECT指令稿分析(續) • 關聯資料表之連結方式 • MySQL是採用”Single-sweep Multi-join”的方式，讀取關聯資料表 • 讀取資料之順序是以順向從第一個資料表開始，讀取第一筆紀錄後，接著依鍵值來尋找第二個資料表之對應資料，然後依此原則，逐步讀取最後資料表主從式資料庫系統 - CH12

12-5-1 SELECT指令稿分析(續) • 當到達最後資料表並完成一筆資料輸出後，接著檢查最後資料表是否有後續符合鍵值的紀錄，有則繼續輸出，無則退回上一層，繼續檢查是否有後續符合鍵值的紀錄，並依此原則逐步退回最上層 • 前述原則將繼續處理，直到讀取完所有合乎指定條件的紀錄主從式資料庫系統 - CH12

12-5-1 SELECT指令稿分析(續) 2. 分析結果表示意義 • 執行語法二之指令後，MySQL會以表格型式輸出分析結果，一列資料代表一個資料表之讀取屬性，而每一列都包含10個欄位 • 分析結果如表12-2所示主從式資料庫系統 - CH12

第 12 章 MySQL 最佳化應用實務