第 12 章
MySQL 最佳化應用實
務
本章宗旨
介紹系統最佳化之觀念
簡介造成系統瓶頸的原因
介紹資料庫改善實務
介紹快取的觀念與應用方法
介紹 MySQL 最佳化工作原理
SQL 指令稿分析與改善實務
簡介資料表上鎖方式與優缺點比較
說明資料表開啟與關閉之處理方式
大 綱
12-1 最佳化總覽
12-2 資料庫結構改善實務
12-3 快取的應用
12-4 MySQL 伺服器最佳化處理
12-5 SQL 指令最佳化處理
12-6 資料表上鎖應用與問題探討
12-7 資料表開啟與關閉
12-1 最佳化總覽
系統最佳化的因素 - 基礎設計
了解系統要求
了解系統限制
了解主要瓶頸所在
12-1 最佳化總覽
( 續 )12-1-1 系統瓶頸原因
12-1-2 MySQL 的限制和替代方案
12-1-3 可攜性問題
12-1-1 系統瓶頸原因
1.
磁碟搜尋
2.
磁碟讀寫
3.
CPU 速度
4.
記憶體及頻寬
12-1-2 MySQL 的限制和替代方案
MyISAM 儲存引擎以極快的速度鎖定資料表
MySQL 允許多個讀取連線和一個寫入連線
處理資料之唯一問題，將發生在單一資料表之 混合式更新 (mixed update) 和緩慢的選取動作
處理資料表若經常發生緩慢的問題，需要考慮
改用其他儲存引擎
12-1-2 MySQL 的限制和替代方案
( 續 )
MySQL 處理資料的方式，可分成交易式和非 交易式兩種
採用非交易模式時，發生錯誤資料無法捲回起 始狀態（ Roll Back ）
為了平穩的處理資料，需要搭配非嚴格模式
（ Strict Mode ）或是使用 IGNORE 屬性來處
理 INSERT 或 UPDATE 指令
12-1-2 MySQL 的限制和替代方案
( 續 )
搭配非嚴格模式（ Strict Mode ）將使用下列 預設規則
所有欄位加上預設值
插入不正確的資料時， MySQL 將以最接近的 資料代替
所有運算式發生錯誤時，將傳回一個替代值來 取代錯誤信息
12-1-2 MySQL 的限制和替代方案
( 續 )
採用非交易式的模式時，不要使用 MySQL 之 欄位內容檢查模式，這樣會拖慢處理速度
欄位內容之檢查工作不要放在 MySQL 伺服端
，而應放在應用程式端來處理，亦即在主控端
先做檢查，正確的資料才寫回伺服端，這樣可
以大幅提升伺服器的效率
12-1-3 可攜性問題
MySQL 伺服器會應用一些非標準的 SQL 指令
，因此發展 MySQL 應用系統時，需要考慮將 來可能移植的 SQL 伺服器種類
對於簡單的 SELECT 和 INSERT 應用介面，
通常不會發生相容性的問題
隨著應用介面複雜度的增加，應用系統的可攜 性就愈來愈低
指令稿內含 MySQL 專屬的註解、函數、資料
型態和限制時，可能會發生可攜性問題
12-2 資料庫結構改善實務
12-2-1 MySQL 資料庫結構 12-2-2 資料表設計實務
12-2-3 欄位索引
12-2-1 MySQL 資料庫結構
MySQL 將紀錄與索引鍵值分開儲存在不同的 檔案
分開儲存更適合現代的磁碟及檔案管理系統
12-2-1 MySQL 資料庫結構
( 續 )
將紀錄與索引鍵值儲存在一起的 缺點
查詢資料時，無法單靠索引表取得所需資料
若需掃描資料表時，因為需透過索引表才能取 得資料，速度將緩慢許多
刪除資料時，在節點上的索引無法立即更新，
因此刪除會拖累資料表一點時間
需耗用更多的空間，因為索引儲存在節點時，
經常發生重複問題，而紀錄也無法儲存在節點 上
12-2-2 資料表設計實務
提升資料處理效率的最高原則，乃將資料表設 計得越精巧越好，除了可增快磁碟的讀取速度
，所需記憶體空間也可以節省
索引鍵越短越好，索引表因較小的欄位而更精
簡，耗用資源及處理負荷更少
12-2-2 資料表設計實務
設計資料表時儘可能遵照下列原則
選用更有效的（較小的）資料型態
欄位儘可能加上 Not NULL 預設值，除了可增 快處理速度，每個欄位可節省一個位元的空間
儘量不要使用變動長度的欄位，如此將可使得 紀錄長度為定長，好處在於處理速度可更快，
但是較浪費空間
如果要用 Varchar 型欄位，可以在建立資料表 時，選用” ROW_FORMAT = Fixed” 選項
12-2-2 資料表設計實務
( 續 ) 主索引鍵愈短愈好
假如經常需要透過某些欄位處理資料，可以為 這些欄位建立索引表，但太多的索引表會拖慢 儲存速度
假如字元型資料的前幾碼具有唯一性，可以針 對這個欄位之部分字元建立索引
經常需要掃描一個動態紀錄格式的資料表時，
將動態紀錄格式（不定常長紀錄）資料表分割 成動態和靜態兩部份
12-2-3 欄位索引
MySQL 之欄位都可加以索引
執行 Select 指令時，相關的欄位若加上索引
，將可增快資料讀取速度
12-2-3 欄位索引
( 續 )1. 索引限制
每個資料表至少可建立 16 個以上的索引表，索 引長度至少 256 以上位元組
索引表的結構有多種
Primary Key, Unique, Index, Fulltext 等型式索引 使用 B 樹
Spatial 型欄位使用 R 樹
而 Memory(Heap) 型索引則使用雜湊 (Hash) 及 B 樹
CHAR 及 VARCHAR 型欄位，可以使用部分前
1. 索引限制
( 續 ) MyISAM 支援 CHAR 、 VARCHAR 及 TEXT 型欄位之” FULLTEXT” 全文索引
MySQL 也支援多重欄位索引（多鍵型），欄數 最多可達 15 個
Where 子句雖只用到多鍵型索引之第一個欄位
，該索引表將自動被選用來比對紀錄
2. MySQL 引用索引檔之方式
在下列情況下 MySQL 將自動引用索引表：
有 Where 子句的 SQL 指令，將可增快讀取資 料速度
有多個索引表可引用時， MySQL 將選用讀取 最少紀錄之索引表
以關聯型式處理資料時
執行統計函數時，例如 Min(Key_Col) ， Max(K ey_Col) 等， MySQL 將自動引用以 Key_Col 為索引鍵之索引表
2. MySQL 引用索引檔之方式
( 續 ) 有 Order By 或 Group By 子句的 SQL 指令，將自 動選用合乎最左欄位之索引表
查詢資料時，若相關欄位可直接從索引表取得時，
MySQL 將略過原始紀錄資料
B 樹型索引表可搭配＝、＞、＞＝、＜、＜＝、 B etween 等關係運算子來比對紀錄
可以使用 Like 比對” %String%” 樣板，其中 Strin g 必須大於三個字元
Where 子句若有 And 邏輯運算子時，索引最佳化 運算機制不一定會啟用，端看 And 前後之鍵值欄 是否衝突
12-3 快取的應用
快取 (Cache) 的功能，主要在於減少磁碟的 I/
O 次數，從而效提升系統執行效率
12-3-1 MyISAM 資料表的快取用法 12-3-2 共用快取空間
12-3-3 多重索引快取的設定和使用 12-3-4 預先載入索引策略
12-3-5 快取空間調整
12-3-1 MyISAM 資料表的快取用法
處理方式分成兩類
對於索引區塊， MySQL 使用一個稱為” Key Cache” （或稱 Key Buffer ）之特殊結構，會一 直被維護並用來存放使用最為頻繁的索引區塊
對於資料區塊， MySQL 並不使用特殊的快取
，快取處理將交由作業系統之檔案系統快取來 管理
12-3-1 資料表的快取使用法 ( 續 )
MyISAM 快取使用方式：
多個執行緒可同步使用快取，不需要予以序列 化
同時可以設定多個快取，不同資料表的索引表 可指定存放在專用的快取內
12-3-1 資料表的快取使用法 ( 續 )
快取空間設定
可用系統變數 ” key_buffer_size” 設定快取空 間，此變數值設為 0 時，表示不使用快取
若設定值太小，以致於無法容納最小的索引區 塊數 (8) 時，快取就無法使用
假如快取無法正常工作，索引檔將回歸檔案系 統之緩衝區來管理
通常索引區塊的大小是等於索引樹的節點數，
樹端的節點 ( 最底層 ) 被稱為葉節點 (leaf node)
，非底層的節點被稱為非葉節點 (non-leaf nod e)
12-3-1 資料表的快取使用法 ( 續 )
資料存取順序
假如有可用的索引區塊時，伺服器將直接從快 取存取資料，而不需要回至磁碟
若無可用索引區塊時，伺服器將選取一快取緩 衝區來存放從資料表索引區塊之副本，然後再 以快取來存取資料
當被選定的區塊要以置換方式 (replacement) 存 入時，則該區塊會視為”用過了 (dirty)” ，在替 換前，快取資料會被沖回 (flush) ，以便更新索 引表
12-3-1 資料表的快取使用法 ( 續 )
快取區塊處理方式
MySQL 是採用 LRU(Least Recently Used) 原 則，所謂 LRU 是指最久未再使用的索引區塊
為了便於管理快取區塊， MySQL 使用一個佇 列結構 (queue) 來存放用過的索引區塊，當一 個區塊被存取時，該區塊將置於佇列底端
當需要替換區塊時，佇列開頭之區塊將優先被 替換
12-3-2 共用快取空間
下列情況 MySQL 允許共用快取資料：
當緩衝區資料未被更新時，快取可被多個執行 緒共用
緩衝區資料被更新時，所有執行緒均須等候，
直到完成更新處理
只要執行緒所使用的快取區塊不互相干擾，各 執行緒均可啟動快取區塊的替換請求
12-3-3 多重索引快取設定和使用
共用快取雖可提升系統執行效率，但是難免會 發生執行緒相互競爭的問題
為避免相互競爭的問題的發生， MySQL 採用
多重快取技術，使得不同的執行緒可指定專用
的快取來存取資料
12-3-3 多重索引快取設定和使用
( 續 )
1. 建立快取
指令語法：
Set Global keycach1.key_buffer_size=128*1024;
說明：代表意義為建立一個名稱叫” keycach1
” 的快取，快取空間設定為 128*1024 位元組
未建立指名的快取時， MySQL 將使用預設的 快取空間
12-3-3 多重索引快取設定和使用
( 續 )
2. 取消快取
取消快取指令語法：
Set Global keycach1.key_buffer_size=0;
3. 快取的指派
指派快取空間指令語法：
CACHE INDEX tbl1, tbl2, tbl3 IN keycach1;
說明：將資料表 tbl1, tbl2, tbl3 之索引區塊存放 在名稱叫” keycach1” 之快取內
12-3-3 多重索引快取設定和使用
( 續 )
4. 多重快取空間之分配策略
一個非常忙碌的伺服器， MySQL 建議將快取空 間分割成三塊：
熱鍵區 (hot key cache)
冷鍵區 (cold key cache)
平常區 (warm key cache)
4. 多重快取空間之分配原則
熱鍵區：分配 20 ％的空間，指派給只被查詢 但不需更新的資料表使用
冷鍵區：分配 20 ％的空間，指派給資料需要 稍為密集異動的資料表使用，例如一些暫存表
平常區：分配 60 ％的空間做為預設區，未指
派快取空間的資料表均使用此區域
12-3-3 多重索引快取的設定和使用
( 續 )
5. 中點插入策略
所謂中點插入策略（ Midpoint Insertion Strateg y ），是 MySQL 提升處理效率的另一種索引表 存取技術，處理過程說明如下：
將原來使用 LRU 技術之資料鏈分割成兩部份：
hot sub-chain(HSC) 和 warm sub-chain(WSC)
，兩部份不需等分，但 WSC 也不能太短，分 割點可在系統變數 ” key_cache_division_limi t” 設定
初次讀取之索引區塊將置於 WSC 之末端
5. 中點插入策略
置於 WSC 之索引區塊經數次之檢索使用之後
（現階段之 MySQL 設定值為 3 次），將被轉 存於之 HSC 之末端
在 HSC 之索引區塊若長時間未被檢索，則會被 移回 WSC 之開頭，其比對時間可在系統變數
” key_cache_age_threshold” 設定
threshold 可用來決定被移回 WSC 之區塊數，
若起始 HSC 有 N 個區塊，則被移回數等於 N
* key_cache_age_threshold / 100 。被移回之 區塊將優先被替換，由於它是位於 WSC 之開 頭處
5. 中點插入策略
( 續 ) 中點插入策略可確保更有價值之區塊才置於快 取。假如你不想使用此策略，你可以沿用 LRU 策略，只要將 key_cache_division_limit 改回預 設值 100 即可
要提高中點插入策略之效率，只需將 key_cach e_division_limit 設為小於 100 之值
12-3-3 多重索引快取的設定和使用
( 續 )
6. 重建快取
建立一個新的快取，只需重設其大小即可，重 建指令範例如下：
Set Global cold_cache.key_buffer_size = 5*1024*1 024
只要重新設定 ” key_buffer_size” 或 “ key_
buffer_size” 之大小， MySQL 立即將舊的快取 毀掉，然後重新建立一個新的快取
12-3-4 預先載入索引策略
假如快取空間足夠存放所有的索引區塊，或是 至少可存入所有非葉節點之索引區塊，在處理 資料前，事先將索引區塊載入快取，將可提升 資料處理效率
預先載入索引是以循序方式從磁碟讀取索引資 料
未預先載入索引區塊，當需要搭配索引表來讀
取資料時，索引區塊將以隨機方式從磁碟讀取
12-3-4 預先載入索引策略
( 續 )
事先載入索引區塊之指令如下：
Load Index Into Cache tbl1, tbl2 Ignore Leaves;
說明：上述指令意義表載入 tbl1 之全部索引區塊
，而 tbl2 只載入非葉節點之索引區塊
事前已執行 Cache Index 指令，則索引區塊將
被存入指定之快取，否則將存入預設之快取區
12-3-5 快取空間調整
系統變數 “ key_cache_block_size ” 用於設 定索引區塊緩衝區，可用來調整 I/O 之效率
當讀取緩衝區之大小設成和原作業系統之 I/O 緩衝區一樣時，將使得 I/O 之效率最好
將索引鍵節點數調整成和 I/O 緩衝區相同，並
不能確保整體效率會最好，當讀取一個有大量
葉節點的索引資料時，伺服器將需要提供大量
無用的資料，因而產生排擠效應
12-4 MySQL 伺服器最佳化處理
MySQL 伺服器的最佳化處理，可從調整系統 啟動參數和系統作業環境參數兩方面來著手
12-4-1 系統因素與啟動參數 12-4-2 系統作業環境參數
12-4-1 系統因素與啟動參數
使用多重 CPU 之作業系統 可提昇系統效率
假如有足夠的 RAM 可使用，可將所有的 SWA P 裝置移除
使用 ” --skip-external-locking” 啟動參數以避
免外部鎖定
12-4-2 系統作業環境參數
可用下列指令檢視作業環境參數設定值
C:\mysql\bin> mysqld --verbose --help
C:\mysql\bin mysqladmin variables
C:\mysql\bin mysqladmin status
Show Variables; ( 啟動伺服器後 )
Show Status; ( 啟動伺服器後 )
使用 Set 指令來設定作業環境參數值
12-5 SQL 指令最佳化處理
以 SELECT 指令當範例，介紹常見的最佳化應用技巧
指令的處理效率將隨著系統的權限設定值的複雜度而 改變，權限設定越複雜，系統的負擔也就越重
建議將授權工作置於應用程式介面上，擁有權限的人 方能開啟對應的應用程式，這種作法將能大幅減少伺 服端的查核負荷
有時系統執行速度減慢的原因，並非導因於權限查核
，而是來至於 MySQL 之運算式或函數本身
12-5 SQL 指令最佳化處理
12-5-1 SELECT 指令稿分析 12-5-2 查詢效率分析
12-5-3 SQL 指令加速法則 12-5-4 最佳化工作原理
12-5-5 避免掃描資料表之基本法則
12-5-1 SELECT 指令稿分析
1. 指令稿分析指令
使用【 EXPLAIN 】指令來分析指令稿， MyS QL 會提供一序列的分析結果，讀者可依據分析 結果來改善指令稿
EXPLAIN 指令用法有兩種：
語法一： Explain 【資料表名】
語法二： Explain 【 SELECT 指令稿】
語法一可用來分析資料表結構 ，語法二可用來 分析 SELECT 指令稿結構
12-5-1 SELECT 指令稿分析
( 續 )
關聯資料表之連結方式
MySQL 是採用” Single-sweep Multi-join” 的方 式，讀取關聯資料表
讀取資料之順序是以順向從第一個資料表開始
，讀取第一筆紀錄後，接著依鍵值來尋找第二 個資料表之對應資料，然後依此原則，逐步讀 取最後資料表
12-5-1 SELECT 指令稿分析 ( 續 )
當到達最後資料表並完成一筆資料輸出後，接 著檢查最後資料表是否有後續符合鍵值的紀錄
，有則繼續輸出，無則退回上一層，繼續檢查 是否有後續符合鍵值的紀錄，並依此原則逐步 退回最上層
前述原則將繼續處理，直到讀取完所有合乎指 定條件的紀錄
12-5-1 SELECT 指令稿分析
( 續 )2. 分析結果表示意義
執行語法二之指令後， MySQL 會以表格型式 輸出分析結果，一列資料代表一個資料表之讀 取屬性，而每一列都包含 10 個欄位
分析結果如表 12-2 所示
2. 分析結果表示意義
表 12-2 EXPLAIN 分析結果範例
--- table type possible_keys key key_len ref rows Extra --- et ALL Primary NULL NULL NULL 74
do ALL Primary NULL NULL NULL 2135 eta ALL Primary NULL NULL NULL 74 mt ALL AsgnPC, NULL NULL NULL 3872 ClientID,
ActPC
range checked for each record (key map: 35)
---
2. 分析結果表示意義
( 續 )
Id 欄：此欄用來標示第幾個 SELECT ，以流水號 型式顯示
select_type 欄：提示使用之 SELECT 型態
table 欄：分析結果所參照之資料表
type 欄：表示 join 的關聯型式，共有下列 11 種
possible_keys 欄：表示搜尋資料時可能使用到 的索引表
key 欄： 提示被選用的索引表
2. 分析結果表示意義
( 續 )
key_len 欄： 提示 MySQ 使用的索引鍵比對長度
ref 欄：表示該資料表將以 ref 欄之對應欄位資料 來挑選紀錄
rows 欄：表示 MySQL 預定要比對的紀錄總數
Extra 欄：表示 MySQL 解析完指令稿後，提示相
關的說明文字
3. 案例探討
假如分析結果之 Type 欄出現 all ，而 key 欄顯示 Null
，請先檢討是否未建立適當的索引表
若子表有索引表， Type 欄出現 all ，問題可能出在索 引表已不正確，請重新整理索引表，然後重新分析
若父表與子表都有對應之索引表， Type 欄仍提示 all
，問題可能出在兩個資料表之索引鍵不相配，請修改 資料表之索引鍵，使之相同
儘可能搭配 Where 子句以縮小比對範圍
12-5-2 查詢效率分析
使用 B 樹索引表前提下，搜尋一筆紀錄所需碟 搜尋數可用下列公式估算：
log(Row_Count)/log(Index_Block_ Length/3*2/
(Index_Length + Data_pointer_Length ))+1
12-5-2 查詢效率分析
( 續 )
MySQL 索引區塊長度大都是 1024 位元組，資 料指標長度為 4 位元組。假如索引鍵長度為 3 位元組，一個擁有 50 萬筆紀錄的資料表，平 均搜尋一筆紀錄所需磁碟搜尋數需 4 次：
log(500000)/log(1024/3*2/ (3+4 ))+1 = 4
假如索引表緩衝區使用率為 2/3 ，則 50 萬筆 紀錄需要的索引儲存空間約需：
500000*(3+4)*3/2 = 5.2MB ，
12-5-2 查詢效率分析
( 續 )
在足夠的記憶空間儲存索引表下
一筆紀錄的讀取約需 1 至 2 次的呼叫
寫回資料時，需要 4 次搜尋（依上述計算式）
來找尋新索引存放位置
次搜尋來更新索引和寫回紀錄
12-5-3 SQL 指令加速法則
1. 選取查詢
”Select … Where ” 指令，若是速度非常緩慢
，第一個被優先考慮的改善法則，就是替這些 資料表建立適當的索引表
已建立對應的索引表，而速度仍然非常緩慢，
問題可能發生在索引鍵順序已經不正確，潛在 原因可能是前次加入新的紀錄後，索引表未同 步更新所致，此類問題可用【 Analyze Tabl e 】指令重建索引表來克服
12-5-3 SQL 指令加速法則
( 續 )2. Insert 指令
新增資料時間的長短，決定於下列幾個因素：
連線（ 3 ）
傳送查詢指令制伺服端（ 3 ）
剖析查詢指令（ 3 ）
插入紀錄（ 1× 紀錄數）
插入索引（ 1× 索引數）
關閉資料表（ 1 ）
2. Insert 指令
可使用下列技巧來加速 Insert 指令：
在一個主控端執行 Insert 指令時，儘可能搭配多個 值組（ Values ），事前可調整系統變數【 bulk_in sert_buffer_size 】，加大值組暫存空間。
同時有多個主控端執行 Insert 指令時，改用 Insert Delayed 可加快速度
2. Insert 指令
( 續 )– 使用 Load Data Infile 指令代替 Insert ，速度可提升 約 20 倍。使用此道指令時，須搭配下列程序：
暫時停止索引功能： myisamchk --keys-use d=0 -rq 【含路徑之資料表名】
載入資料： Load Data Infile ….;
重建索引： myisamchk --keys-used=0 -r -q
【含路徑之資料表名】
更新資料及索引： Flush Tables;
2. Insert 指令
( 續 ) 先鎖住資料表再新增資料，也能加快速度，語 法如下
Lock Tables 資料表名 Write;
Insert Into 資料表名 Values(…);
……….
Insert Into 資料表名 Values(…);
Unlock Tables;
適當的調整快取空間之 (key_buffer_size) 變數 值，也能增快 Load Data Infile 及 Insert 的速度
3. Update 指令
提升 Update 速度之作法有下列三種：
採用 Delayed 模式
多筆紀錄一次更新
搭配鎖表模式
4. Delete 指令
刪除個別紀錄之時間與索引表個數成正比，要 增快 Delete 指令的速度，唯一作法就是調整 K ey Cache 空間
若要刪除整個資料表的紀錄，請改用【 Trunca
te Table 資料表名】，此道指令會比【 Delete
From 資料表名】快許多
5. 其他增快技巧
使用持續連線以避免伺服器過負荷
避免使用複雜的 Select 在經常需要更新資料之 MyISA M 資料表，以避免資料讀寫連線之鎖表
未曾刪除資料之 MyISAM 資料表，可以同時進行新增 記錄在資料表後端和查詢資料。經常要以此方式工作 者，在刪除記錄後，須執行【 Optimize Table 資料表 名】指令來整理資料表
5. 其他增快技巧
( 續 ) 假如經常需要以 Expr1, Expr2,… 型式處理紀錄，請定期 執行【 Alter Table … Order By Expr1, Expr2,… 】指令，
重新整理紀錄順序
儘可能避免使用全部欄位都是變動長度之 MyISAM 資料表
將動態格式紀錄予以分割成定長和不定長兩個部份，或是 經常需要掃描資料時，儘量不要使用全部欄位
5. 其他增快技巧
( 續 ) 假如經常需要從一個異動日誌檔（ log table ）以計算 或統計方式取得資料，可以新增一個綜合資料用的資 料表（ Summary Table ），在異動日誌檔資料異動後
，立即將資料更新至綜合表，查閱綜合表的速度將快 於異動日誌檔
統計型報表，資料儘可能取自於綜合資料表，而不要 取自於日誌檔
欄位資料儘可能設計成有預設值，可增快 Insert 剖析 速度
5. 其他增快技巧
( 續 ) 應儘可能避免重複資料（ non-redundant ）（換句話 說，資料表應儘可能合乎 3NF 型式）
新增資料若不須立即顯示，或是允許同步查閱，請使 用 ” Insert Delayed” 模式
若 Select 指令要先於 Insert 指令時，請使用 ” Insert Low_Priority” 模式
使用 ” Select High_Priority” 模式，將可改變 Select 指令優先權，可高於其他主控端之寫入指令
5. 其他增快技巧
( 續 ) 欄名儘可能簡短，除了可以提高其可攜性，也可以縮 短剖析時間
執行 MySQL 低階介面指令之速度，通常會比 SQL 介 面指令快 2 至 5 倍。必要的話，應用介面可置於伺服 端，資料表也儘可能置於同一個伺服器上
將備援資料（ Replicate Database ）備份在 Slave Ser ver 上，如此可以將某些主控端連線在 Slave 伺服器以 減輕主伺服器之負荷
12-5-4 最佳化工作原理
MySQL 以內建的最佳化運算器 (Optimizer) 來 改進資料處理速度，它內含許多最佳化演算法
，針對不同的 SQL 指令，經過剖析後，可以
自行判定資料最快的讀取方式、讀取順序，或
合理的讀取範圍
1. Where 子句最佳化原理
除去不必要的括弧
合併常數
除去常數範圍
常數型運算子搭配索引表，資料只需檢索一次
Having 子句未搭配 Group By 子句時，將以搭配之 W here 子句運算式來小計資料
Join 關連資料時，所有資料表搭配之 Where 子句，其 運算式之型式越簡單，所須剖析時間越短
1. Where 子句最佳化原理
( 續 ) 擁有常數值的資料表，在查閱資料時將優先被讀取
搭配 Order By 和 Group By 子句，若兩者之運算式參 照同一資料表，則該資料表優先被讀取
搭配 Order By 和 Group By 子句，兩者之運算式參照 不同的資料表，或參照欄位非第一個資料表時， MyS QL 將自動建立一個暫存資料表
1. Where 子句最佳化原理
( 續 ) 使用 SQL_SMALL_RESULT 模式， MySQL 將自動使 用記憶體型暫存表 (in-memory temporary table)
查詢資料時， MySQL 會自動選配適當的索引表，除 非它認定採掃描資料表方式更有效率。
2. 界限值 (Range) 最佳化原理
使用界限值當範圍來過濾資料時， MySQL 會自動選配合 適的索引表
選用索引表時，可分成單鍵型：界限欄只有一個，以及多 鍵型：界限欄有兩個或以上
界線設定格式可分成三種：
– 搭配 B 樹或 Hash 型式索引表：比較運算子可選用＝、
＜＝＞、 IN 、 IS NULL 、或是 IS NOT NULL
– 搭配 B 樹索引表：比較運算子可選用＞、＜、＞＝、＜
＝、！＝、 BETWEEN 、＜＞，或是 Like
– 以 And 或 Or 連結之單鍵過濾運算式
2. 界限值 (Range) 最佳化原理
( 續 )
MySQL 在剖析界限運算式時，將自動以下列 規則化減運算式
無法建立界限條件部分將被抽換
重疊之部分被合併
空值條件被忽略
3. Order By 最佳化工作原理
MySQL 使用 ” filesort” 最佳化演算法，將鍵 值欄和紀錄位置排序，如此可以避免紀錄被讀 取兩次
filesort 工作原理如下
首先依 Where 子句過濾資料
依選出的紀錄，記下含排序欄值及紀錄位置，
以及查詢之欄位集合
將紀錄值組排序
依排序值組直接讀取資料（不再透過資料表）
3. Order By 最佳化工作原理
( 續 )
提升 Order By 之執行效率原則
考慮的是使用索引表而非額外的排序
假如無法搭配索引表時，你可以試著調整下列 系統變數設定值：
增加” sort_buffer_size”
增加” read_rnd_buffer_size”
將” tmpdir” 指向一至多個空目錄
12-5-5 避免掃描資料表之基本法則
資料表掃描（ Table Scan ）發生原因
料表只有 10 筆以下的紀錄，且每筆紀錄不長
On 或 Where 子句運算式未包含索引表欄位
比對索引鍵值後，值組範圍甚大，改採資料表 掃描速度較快
索引表跨越數個欄位，改採資料表掃描速度較 快
12-5-5 避免掃描資料表之基本法則
( 續 )
避免發生掃描資料表原則
定期執行【 Analyze Table 】指令，重建索引 表
使用 ” Force Index” 子句，強制使用索引表
啟動 Mysqld 伺服器時，搭配 ” --max-seeks-f or-key=1000” ，通知最佳化運算器，搜尋索引 表時，不可能超過 1000 個
12-6 資料表上鎖應用與問題探 討
12-6-1 上鎖的方法
MySQL 使用下述方法將資料表上鎖
鎖定資料表： ISAM ， MyISAM 及 Memory(Hea p) 資料表
鎖定資料頁： BDB 資料表
鎖定資料錄： InnoDB 資料表
12-6-1 上鎖的方法
MySQL 在鎖定資料表時，可以完全排除互鎖（ dead-loc k ）的死結問題
對於寫入資料之上鎖方式， MySQL 是以下列演算法來處 理
若該資料表未被鎖定，則立即上鎖
否則，將上鎖請求置入 ” write lock” 佇列中
對於讀取資料之上鎖方式， MySQL 是以下列演算法來處 理：
若該資料表未以寫入鎖定，則立即上鎖
否則，將上鎖請求置入 ” read lock” 佇列中
12-6-1 上鎖的方法
( 續 )
無法同步處理多個 Insert 和 Select 程序時，
可以將新增資料存入暫存資料表，俟後，再將 暫存表資料轉存至原資料表，處理程序如下
Lock Tables 原資料表 Write, 暫存資料表 Writ e;
Insert Into 原資料表 Select * From 暫存資料表 ; Truncate Table 暫存資料表 ;
Unlock Tables;
12-6-1 上鎖的方法
( 續 )
紀錄鎖定模式之優缺點說明如下：
優點
在多執行緒下，處理不同的紀錄時，不太可能發生上 鎖衝突問題
異動資料時，不太可能發生需要捲回的情形
一筆紀錄上鎖時間可較長
缺點（與另外兩種模式比較）
需要較大的記憶體
速度較為緩慢（處理大型資料表時，需要較多的鎖
12-6-1 上鎖的方法
( 續 )
下列情況時，宜採資料表鎖定模式
處理指令多數屬讀取資料
更新或刪除資料可用單一索引鍵比對資料
Select 伴隨同步式 Insert 指令，偶而有少許的 Update 和 Delete
需要掃描資料表時，例如處理 Group By 程序
12-7 資料表開啟與關閉
MySQL 允許開啟的資料表總數是受到限制的
，開啟總數由下列三個系統變數來設定：
table_cache
max_conncetions
max_tmp_tables
12-7 資料表開啟與關閉
( 續 )
不再使用的資料表，將依下列原則予以關閉，
同時會從快取移除：
快取已滿，執行緒需開起一個不在快取之新資 料表
快取資料表數已超過 table_cache 輸入屬性設 定值，執行緒不再使用該資料表
資料表已被【 Flush Tables 】指令處理過
12-7 資料表開啟與關閉
( 續 )
快取已被填滿時，伺服器將依下列原則來挑選 適當的快取空間，然後將新開起的資料表存入 該快取：
不再使用的資料表會依最久未被使用之時間，
作為關閉優先順序
快取已滿而無資料表可加以關閉時，此時將使 用暫存快取區。置於暫存快取的資料表，當不 再使用時，將立即被關閉及移除
