12 mysql
This presentation is the property of its rightful owner.
Sponsored Links
1 / 87

第 12 章 MySQL 最佳化應用實務 PowerPoint PPT Presentation


  • 31 Views
  • Uploaded on
  • Presentation posted in: General

第 12 章 MySQL 最佳化應用實務. 本章宗旨. 介紹系統最佳化之觀念 簡介造成系統瓶頸的原因 介紹資料庫改善實務 介紹快取的觀念與應用方法 介紹 MySQL 最佳化工作原理 SQL 指令稿分析與改善實務 簡介資料表上鎖方式與優缺點比較 說明資料表開啟與關閉之處理方式. 大 綱. 12-1 最佳化總覽 12-2 資料庫結構改善實務 12-3 快取的應用 12-4 MySQL 伺服器最佳化處理 12-5 SQL 指令最佳化處理 12-6 資料表上鎖應用與問題探討 12-7 資料表開啟與關閉. 12-1 最佳化總覽.

Download Presentation

第 12 章 MySQL 最佳化應用實務

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


12 mysql

第12章MySQL最佳化應用實務

主從式資料庫系統 - CH12


12 mysql

本章宗旨

  • 介紹系統最佳化之觀念

  • 簡介造成系統瓶頸的原因

  • 介紹資料庫改善實務

  • 介紹快取的觀念與應用方法

  • 介紹MySQL最佳化工作原理

  • SQL指令稿分析與改善實務

  • 簡介資料表上鎖方式與優缺點比較

  • 說明資料表開啟與關閉之處理方式

主從式資料庫系統 - CH12


12 mysql

大 綱

  • 12-1 最佳化總覽

  • 12-2 資料庫結構改善實務

  • 12-3 快取的應用

  • 12-4 MySQL伺服器最佳化處理

  • 12-5 SQL指令最佳化處理

  • 12-6 資料表上鎖應用與問題探討

  • 12-7 資料表開啟與關閉

主從式資料庫系統 - CH12


12 mysql

12-1 最佳化總覽

  • 系統最佳化的因素 - 基礎設計

    • 了解系統要求

    • 了解系統限制

    • 了解主要瓶頸所在

主從式資料庫系統 - CH12


12 mysql

12-1 最佳化總覽(續)

12-1-1 系統瓶頸原因

12-1-2 MySQL的限制和替代方案

12-1-3 可攜性問題

主從式資料庫系統 - CH12


12 1 1

12-1-1 系統瓶頸原因

  • 磁碟搜尋

  • 磁碟讀寫

  • CPU速度

  • 記憶體及頻寬

主從式資料庫系統 - CH12


12 1 2 mysql

12-1-2 MySQL的限制和替代方案

  • MyISAM儲存引擎以極快的速度鎖定資料表

  • MySQL允許多個讀取連線和一個寫入連線

  • 處理資料之唯一問題,將發生在單一資料表之混合式更新(mixed update)和緩慢的選取動作

  • 處理資料表若經常發生緩慢的問題,需要考慮改用其他儲存引擎

主從式資料庫系統 - CH12


12 1 2 mysql1

12-1-2 MySQL的限制和替代方案(續)

  • MySQL處理資料的方式,可分成交易式和非交易式兩種

  • 採用非交易模式時,發生錯誤資料無法捲回起始狀態(Roll Back)

  • 為了平穩的處理資料,需要搭配非嚴格模式(Strict Mode)或是使用IGNORE屬性來處理INSERT 或UPDATE指令

主從式資料庫系統 - CH12


12 1 2 mysql2

12-1-2 MySQL的限制和替代方案(續)

  • 搭配非嚴格模式(Strict Mode)將使用下列預設規則

    • 所有欄位加上預設值

    • 插入不正確的資料時,MySQL將以最接近的資料代替

    • 所有運算式發生錯誤時,將傳回一個替代值來取代錯誤信息

主從式資料庫系統 - CH12


12 1 2 mysql3

12-1-2 MySQL的限制和替代方案(續)

  • 採用非交易式的模式時,不要使用MySQL之欄位內容檢查模式,這樣會拖慢處理速度

  • 欄位內容之檢查工作不要放在MySQL伺服端,而應放在應用程式端來處理,亦即在主控端先做檢查,正確的資料才寫回伺服端,這樣可以大幅提升伺服器的效率

主從式資料庫系統 - CH12


12 1 3

12-1-3 可攜性問題

  • MySQL伺服器會應用一些非標準的SQL指令,因此發展MySQL應用系統時,需要考慮將來可能移植的SQL伺服器種類

  • 對於簡單的SELECT和INSERT應用介面,通常不會發生相容性的問題

  • 隨著應用介面複雜度的增加,應用系統的可攜性就愈來愈低

  • 指令稿內含MySQL專屬的註解、函數、資料型態和限制時,可能會發生可攜性問題

主從式資料庫系統 - CH12


12 mysql

12-2 資料庫結構改善實務

12-2-1 MySQL資料庫結構

12-2-2資料表設計實務

12-2-3 欄位索引

主從式資料庫系統 - CH12


12 2 1 mysql

12-2-1 MySQL資料庫結構

  • MySQL將紀錄與索引鍵值分開儲存在不同的檔案

  • 分開儲存更適合現代的磁碟及檔案管理系統

主從式資料庫系統 - CH12


12 2 1 mysql1

12-2-1 MySQL資料庫結構(續)

  • 將紀錄與索引鍵值儲存在一起的 缺點

    • 查詢資料時,無法單靠索引表取得所需資料

    • 若需掃描資料表時,因為需透過索引表才能取得資料,速度將緩慢許多

    • 刪除資料時,在節點上的索引無法立即更新,因此刪除會拖累資料表一點時間

    • 需耗用更多的空間,因為索引儲存在節點時,經常發生重複問題,而紀錄也無法儲存在節點上

主從式資料庫系統 - CH12


12 2 2

12-2-2資料表設計實務

  • 提升資料處理效率的最高原則,乃將資料表設計得越精巧越好,除了可增快磁碟的讀取速度,所需記憶體空間也可以節省

  • 索引鍵越短越好,索引表因較小的欄位而更精簡,耗用資源及處理負荷更少

主從式資料庫系統 - CH12


12 2 21

12-2-2資料表設計實務

  • 設計資料表時儘可能遵照下列原則

    • 選用更有效的(較小的)資料型態

    • 欄位儘可能加上Not NULL預設值,除了可增快處理速度,每個欄位可節省一個位元的空間

    • 儘量不要使用變動長度的欄位,如此將可使得紀錄長度為定長,好處在於處理速度可更快,但是較浪費空間

    • 如果要用Varchar型欄位,可以在建立資料表時,選用”ROW_FORMAT = Fixed”選項

主從式資料庫系統 - CH12


12 2 22

12-2-2資料表設計實務(續)

  • 主索引鍵愈短愈好

  • 假如經常需要透過某些欄位處理資料,可以為這些欄位建立索引表,但太多的索引表會拖慢儲存速度

  • 假如字元型資料的前幾碼具有唯一性,可以針對這個欄位之部分字元建立索引

  • 經常需要掃描一個動態紀錄格式的資料表時,將動態紀錄格式(不定常長紀錄)資料表分割成動態和靜態兩部份

主從式資料庫系統 - CH12


12 2 3

12-2-3 欄位索引

  • MySQL之欄位都可加以索引

  • 執行Select 指令時,相關的欄位若加上索引,將可增快資料讀取速度

主從式資料庫系統 - CH12


12 2 31

12-2-3 欄位索引(續)

  • 索引限制

    • 每個資料表至少可建立16個以上的索引表,索引長度至少256以上位元組

    • 索引表的結構有多種

      • Primary Key, Unique, Index, Fulltext等型式索引使用B樹

      • Spatial型欄位使用R樹

      • 而Memory(Heap)型索引則使用雜湊(Hash) 及B樹

    • CHAR及VARCHAR型欄位,可以使用部分前導字元來索引,前導字元長度可達255位元組

主從式資料庫系統 - CH12


12 mysql

1. 索引限制(續)

  • MyISAM支援CHAR、VARCHAR及TEXT型欄位之”FULLTEXT”全文索引

  • MySQL也支援多重欄位索引(多鍵型),欄數最多可達15個

  • Where子句雖只用到多鍵型索引之第一個欄位,該索引表將自動被選用來比對紀錄

主從式資料庫系統 - CH12


2 mysql

2. MySQL引用索引檔之方式

  • 在下列情況下MySQL將自動引用索引表:

    • 有Where子句的SQL指令,將可增快讀取資料速度

    • 有多個索引表可引用時,MySQL將選用讀取最少紀錄之索引表

    • 以關聯型式處理資料時

    • 執行統計函數時,例如Min(Key_Col),Max(Key_Col)等,MySQL將自動引用以Key_Col為索引鍵之索引表

主從式資料庫系統 - CH12


2 mysql1

2. MySQL引用索引檔之方式(續)

  • 有Order By或Group By子句的SQL指令,將自動選用合乎最左欄位之索引表

  • 查詢資料時,若相關欄位可直接從索引表取得時,MySQL將略過原始紀錄資料

  • B樹型索引表可搭配=、>、>=、<、<=、Between等關係運算子來比對紀錄

  • 可以使用Like比對”%String%” 樣板,其中String必須大於三個字元

  • Where子句若有And邏輯運算子時,索引最佳化運算機制不一定會啟用,端看And前後之鍵值欄是否衝突

主從式資料庫系統 - CH12


12 mysql

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

12-3-1 MyISAM資料表的快取用法

  • 處理方式分成兩類

    • 對於索引區塊,MySQL使用一個稱為”Key Cache”(或稱Key Buffer)之特殊結構,會一直被維護並用來存放使用最為頻繁的索引區塊

    • 對於資料區塊,MySQL並不使用特殊的快取,快取處理將交由作業系統之檔案系統快取來管理

主從式資料庫系統 - CH12


12 3 1

12-3-1 資料表的快取使用法(續)

  • MyISAM快取使用方式:

    • 多個執行緒可同步使用快取,不需要予以序列化

    • 同時可以設定多個快取,不同資料表的索引表可指定存放在專用的快取內

主從式資料庫系統 - CH12


12 3 11

12-3-1 資料表的快取使用法(續)

  • 快取空間設定

    • 可用系統變數 ”key_buffer_size” 設定快取空間,此變數值設為0時,表示不使用快取

    • 若設定值太小,以致於無法容納最小的索引區塊數(8)時,快取就無法使用

    • 假如快取無法正常工作,索引檔將回歸檔案系統之緩衝區來管理

    • 通常索引區塊的大小是等於索引樹的節點數,樹端的節點(最底層)被稱為葉節點(leaf node),非底層的節點被稱為非葉節點(non-leaf node)

主從式資料庫系統 - CH12


12 3 12

12-3-1 資料表的快取使用法(續)

  • 資料存取順序

    • 假如有可用的索引區塊時,伺服器將直接從快取存取資料,而不需要回至磁碟

    • 若無可用索引區塊時,伺服器將選取一快取緩衝區來存放從資料表索引區塊之副本,然後再以快取來存取資料

    • 當被選定的區塊要以置換方式(replacement)存入時,則該區塊會視為”用過了(dirty)”,在替換前,快取資料會被沖回(flush),以便更新索引表

主從式資料庫系統 - CH12


12 3 13

12-3-1 資料表的快取使用法(續)

  • 快取區塊處理方式

    • MySQL是採用LRU(Least Recently Used)原則,所謂LRU是指最久未再使用的索引區塊

    • 為了便於管理快取區塊,MySQL使用一個佇列結構(queue)來存放用過的索引區塊,當一個區塊被存取時,該區塊將置於佇列底端

    • 當需要替換區塊時,佇列開頭之區塊將優先被替換

主從式資料庫系統 - CH12


12 3 2

12-3-2 共用快取空間

  • 下列情況MySQL允許共用快取資料:

    • 當緩衝區資料未被更新時,快取可被多個執行緒共用

    • 緩衝區資料被更新時,所有執行緒均須等候,直到完成更新處理

    • 只要執行緒所使用的快取區塊不互相干擾,各執行緒均可啟動快取區塊的替換請求

主從式資料庫系統 - CH12


12 3 3

12-3-3 多重索引快取設定和使用

  • 共用快取雖可提升系統執行效率,但是難免會發生執行緒相互競爭的問題

  • 為避免相互競爭的問題的發生, MySQL採用多重快取技術,使得不同的執行緒可指定專用的快取來存取資料

主從式資料庫系統 - CH12


12 3 31

12-3-3 多重索引快取設定和使用(續)

1. 建立快取

  • 指令語法:

    Set Global keycach1.key_buffer_size=128*1024;

  • 說明:代表意義為建立一個名稱叫”keycach1”的快取,快取空間設定為128*1024位元組

  • 未建立指名的快取時,MySQL將使用預設的快取空間

主從式資料庫系統 - CH12


12 3 32

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 33

12-3-3 多重索引快取設定和使用(續)

4. 多重快取空間之分配策略

一個非常忙碌的伺服器,MySQL建議將快取空間分割成三塊:

  • 熱鍵區(hot key cache)

  • 冷鍵區(cold key cache)

  • 平常區(warm key cache)

主從式資料庫系統 - CH12


12 mysql

4. 多重快取空間之分配原則

  • 熱鍵區:分配20%的空間,指派給只被查詢但不需更新的資料表使用

  • 冷鍵區:分配20%的空間,指派給資料需要稍為密集異動的資料表使用,例如一些暫存表

  • 平常區:分配60%的空間做為預設區,未指派快取空間的資料表均使用此區域

主從式資料庫系統 - CH12


12 3 34

12-3-3 多重索引快取的設定和使用(續)

5. 中點插入策略

  • 所謂中點插入策略(Midpoint Insertion Strategy),是MySQL提升處理效率的另一種索引表存取技術,處理過程說明如下:

  • 將原來使用LRU技術之資料鏈分割成兩部份:hot sub-chain(HSC)和warm sub-chain(WSC),兩部份不需等分,但WSC也不能太短,分割點可在系統變數 ”key_cache_division_limit” 設定

  • 初次讀取之索引區塊將置於WSC之末端

主從式資料庫系統 - CH12


12 mysql

5. 中點插入策略

  • 置於WSC之索引區塊經數次之檢索使用之後(現階段之MySQL設定值為3次),將被轉存於之HSC之末端

  • 在HSC之索引區塊若長時間未被檢索,則會被移回WSC之開頭,其比對時間可在系統變數 ”key_cache_age_threshold” 設定

  • threshold可用來決定被移回WSC之區塊數,若起始HSC有N個區塊,則被移回數等於 N * key_cache_age_threshold / 100。被移回之區塊將優先被替換,由於它是位於WSC之開頭處

主從式資料庫系統 - CH12


12 mysql

5. 中點插入策略(續)

  • 中點插入策略可確保更有價值之區塊才置於快取。假如你不想使用此策略,你可以沿用LRU策略,只要將key_cache_division_limit改回預設值100即可

  • 要提高中點插入策略之效率,只需將key_cache_division_limit 設為小於100之值

主從式資料庫系統 - CH12


12 3 35

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

12-3-4 預先載入索引策略

  • 假如快取空間足夠存放所有的索引區塊,或是至少可存入所有非葉節點之索引區塊,在處理資料前,事先將索引區塊載入快取,將可提升資料處理效率

  • 預先載入索引是以循序方式從磁碟讀取索引資料

  • 未預先載入索引區塊,當需要搭配索引表來讀取資料時,索引區塊將以隨機方式從磁碟讀取

主從式資料庫系統 - CH12


12 3 41

12-3-4 預先載入索引策略(續)

  • 事先載入索引區塊之指令如下:

    Load Index Into Cache tbl1, tbl2 Ignore Leaves;

    說明:上述指令意義表載入tbl1之全部索引區塊,而tbl2只載入非葉節點之索引區塊

  • 事前已執行Cache Index指令,則索引區塊將被存入指定之快取,否則將存入預設之快取區

主從式資料庫系統 - CH12


12 3 5

12-3-5 快取空間調整

  • 系統變數 “key_cache_block_size ” 用於設定索引區塊緩衝區,可用來調整I/O之效率

  • 當讀取緩衝區之大小設成和原作業系統之I/O緩衝區一樣時,將使得I/O之效率最好

  • 將索引鍵節點數調整成和I/O緩衝區相同,並不能確保整體效率會最好,當讀取一個有大量葉節點的索引資料時,伺服器將需要提供大量無用的資料,因而產生排擠效應

主從式資料庫系統 - CH12


12 4 mysql

12-4 MySQL伺服器最佳化處理

  • MySQL伺服器的最佳化處理,可從調整系統啟動參數和系統作業環境參數兩方面來著手

    12-4-1 系統因素與啟動參數

    12-4-2 系統作業環境參數

主從式資料庫系統 - CH12


12 4 1

12-4-1 系統因素與啟動參數

  • 使用多重CPU之作業系統 可提昇系統效率

  • 假如有足夠的RAM可使用,可將所有的SWAP裝置移除

  • 使用 ”--skip-external-locking” 啟動參數以避免外部鎖定

主從式資料庫系統 - CH12


12 4 2

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

12-5 SQL指令最佳化處理

  • 以SELECT指令當範例,介紹常見的最佳化應用技巧

  • 指令的處理效率將隨著系統的權限設定值的複雜度而改變,權限設定越複雜,系統的負擔也就越重

  • 建議將授權工作置於應用程式介面上,擁有權限的人方能開啟對應的應用程式,這種作法將能大幅減少伺服端的查核負荷

  • 有時系統執行速度減慢的原因,並非導因於權限查核,而是來至於MySQL之運算式或函數本身

主從式資料庫系統 - CH12


12 5 sql1

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

12-5-1 SELECT指令稿分析

1. 指令稿分析指令

  • 使用【EXPLAIN】指令來分析指令稿,MySQL會提供一序列的分析結果,讀者可依據分析結果來改善指令稿

  • EXPLAIN指令用法有兩種:

    • 語法一:Explain 【資料表名】

    • 語法二:Explain 【SELECT指令稿】

  • 語法一可用來分析資料表結構 ,語法二可用來分析SELECT指令稿結構

主從式資料庫系統 - CH12


12 5 1 select1

12-5-1 SELECT指令稿分析(續)

  • 關聯資料表之連結方式

    • MySQL是採用”Single-sweep Multi-join”的方式,讀取關聯資料表

    • 讀取資料之順序是以順向從第一個資料表開始,讀取第一筆紀錄後,接著依鍵值來尋找第二個資料表之對應資料,然後依此原則,逐步讀取最後資料表

主從式資料庫系統 - CH12


12 5 1 select2

12-5-1 SELECT指令稿分析(續)

  • 當到達最後資料表並完成一筆資料輸出後,接著檢查最後資料表是否有後續符合鍵值的紀錄,有則繼續輸出,無則退回上一層,繼續檢查是否有後續符合鍵值的紀錄,並依此原則逐步退回最上層

  • 前述原則將繼續處理,直到讀取完所有合乎指定條件的紀錄

主從式資料庫系統 - CH12


12 5 1 select3

12-5-1 SELECT指令稿分析(續)

2. 分析結果表示意義

  • 執行語法二之指令後,MySQL會以表格型式輸出分析結果,一列資料代表一個資料表之讀取屬性,而每一列都包含10個欄位

  • 分析結果如表12-2所示

主從式資料庫系統 - CH12


12 mysql

2. 分析結果表示意義

表12-2EXPLAIN分析結果範例

---------------------------------------------------------------------------------

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)

---------------------------------------------------------------------------------

主從式資料庫系統 - CH12


12 mysql

2. 分析結果表示意義(續)

  • Id欄:此欄用來標示第幾個SELECT,以流水號型式顯示

  • select_type欄:提示使用之SELECT型態

  • table欄:分析結果所參照之資料表

  • type欄:表示join的關聯型式,共有下列11種

  • possible_keys欄:表示搜尋資料時可能使用到的索引表

  • key欄: 提示被選用的索引表

主從式資料庫系統 - CH12


12 mysql

2. 分析結果表示意義(續)

  • key_len欄: 提示MySQ使用的索引鍵比對長度

  • ref欄:表示該資料表將以ref欄之對應欄位資料來挑選紀錄

  • rows欄:表示MySQL預定要比對的紀錄總數

  • Extra欄:表示MySQL解析完指令稿後,提示相關的說明文字

主從式資料庫系統 - CH12


12 mysql

3. 案例探討

  • 假如分析結果之Type欄出現all,而key欄顯示Null,請先檢討是否未建立適當的索引表

  • 若子表有索引表,Type欄出現all,問題可能出在索引表已不正確,請重新整理索引表,然後重新分析

  • 若父表與子表都有對應之索引表,Type欄仍提示all,問題可能出在兩個資料表之索引鍵不相配,請修改資料表之索引鍵,使之相同

  • 儘可能搭配Where子句以縮小比對範圍

主從式資料庫系統 - CH12


12 5 2

12-5-2 查詢效率分析

  • 使用B樹索引表前提下,搜尋一筆紀錄所需碟搜尋數可用下列公式估算:

    • log(Row_Count)/log(Index_Block_ Length/3*2/ (Index_Length + Data_pointer_Length ))+1

主從式資料庫系統 - CH12


12 5 21

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,

主從式資料庫系統 - CH12


12 5 22

12-5-2 查詢效率分析(續)

  • 在足夠的記憶空間儲存索引表下

    • 一筆紀錄的讀取約需1至2次的呼叫

    • 寫回資料時,需要4次搜尋(依上述計算式)來找尋新索引存放位置

    • 次搜尋來更新索引和寫回紀錄

主從式資料庫系統 - CH12


12 5 3 sql

12-5-3 SQL指令加速法則

1. 選取查詢

  • ”Select … Where ” 指令,若是速度非常緩慢,第一個被優先考慮的改善法則,就是替這些資料表建立適當的索引表

  • 已建立對應的索引表,而速度仍然非常緩慢,問題可能發生在索引鍵順序已經不正確,潛在原因可能是前次加入新的紀錄後,索引表未同步更新所致,此類問題可用【Analyze Table】指令重建索引表來克服

主從式資料庫系統 - CH12


12 5 3 sql1

12-5-3 SQL指令加速法則(續)

2. Insert 指令

  • 新增資料時間的長短,決定於下列幾個因素:

    • 連線(3)

    • 傳送查詢指令制伺服端(3)

    • 剖析查詢指令(3)

    • 插入紀錄(1×紀錄數)

    • 插入索引(1×索引數)

    • 關閉資料表(1)

主從式資料庫系統 - CH12


2 insert

2. Insert 指令

  • 可使用下列技巧來加速Insert指令:

    • 在一個主控端執行Insert指令時,儘可能搭配多個值組(Values),事前可調整系統變數【bulk_insert_buffer_size】,加大值組暫存空間。

    • 同時有多個主控端執行Insert指令時,改用Insert Delayed可加快速度

主從式資料庫系統 - CH12


2 insert1

2. Insert 指令(續)

  • 使用Load Data Infile指令代替Insert,速度可提升約20倍。使用此道指令時,須搭配下列程序:

    • 暫時停止索引功能:myisamchk --keys-used=0 -rq 【含路徑之資料表名】

    • 載入資料:Load Data Infile ….;

    • 重建索引:myisamchk --keys-used=0 -r -q 【含路徑之資料表名】

    • 更新資料及索引:Flush Tables;

主從式資料庫系統 - CH12


2 insert2

2. Insert 指令(續)

  • 先鎖住資料表再新增資料,也能加快速度,語法如下

    • Lock Tables 資料表名 Write;

    • Insert Into資料表名 Values(…);

    • …………………………………….

    • Insert Into資料表名 Values(…);

    • Unlock Tables;

  • 適當的調整快取空間之(key_buffer_size)變數值,也能增快Load Data Infile及Insert的速度

主從式資料庫系統 - CH12


3 update

3. Update指令

  • 提升Update速度之作法有下列三種:

    • 採用Delayed模式

    • 多筆紀錄一次更新

    • 搭配鎖表模式

主從式資料庫系統 - CH12


4 delete

4. Delete指令

  • 刪除個別紀錄之時間與索引表個數成正比,要增快Delete指令的速度,唯一作法就是調整Key Cache空間

  • 若要刪除整個資料表的紀錄,請改用【Truncate Table 資料表名】,此道指令會比【Delete From資料表名】快許多

主從式資料庫系統 - CH12


12 mysql

5. 其他增快技巧

  • 使用持續連線以避免伺服器過負荷

  • 避免使用複雜的Select在經常需要更新資料之MyISAM資料表,以避免資料讀寫連線之鎖表

  • 未曾刪除資料之MyISAM資料表,可以同時進行新增記錄在資料表後端和查詢資料。經常要以此方式工作者,在刪除記錄後,須執行【Optimize Table 資料表名】指令來整理資料表

主從式資料庫系統 - CH12


12 mysql

5. 其他增快技巧(續)

  • 假如經常需要以Expr1, Expr2,…型式處理紀錄,請定期執行【Alter Table … Order By Expr1, Expr2,…】指令,重新整理紀錄順序

  • 儘可能避免使用全部欄位都是變動長度之MyISAM資料表

  • 將動態格式紀錄予以分割成定長和不定長兩個部份,或是經常需要掃描資料時,儘量不要使用全部欄位

主從式資料庫系統 - CH12


12 mysql

5. 其他增快技巧(續)

  • 假如經常需要從一個異動日誌檔(log table)以計算或統計方式取得資料,可以新增一個綜合資料用的資料表(Summary Table),在異動日誌檔資料異動後,立即將資料更新至綜合表,查閱綜合表的速度將快於異動日誌檔

  • 統計型報表,資料儘可能取自於綜合資料表,而不要取自於日誌檔

  • 欄位資料儘可能設計成有預設值,可增快Insert 剖析速度

主從式資料庫系統 - CH12


12 mysql

5. 其他增快技巧(續)

  • 應儘可能避免重複資料(non-redundant)(換句話說,資料表應儘可能合乎3NF型式)

  • 新增資料若不須立即顯示,或是允許同步查閱,請使用 ”Insert Delayed” 模式

  • 若Select指令要先於Insert 指令時,請使用 ”Insert Low_Priority” 模式

  • 使用 ”Select High_Priority” 模式,將可改變Select指令優先權,可高於其他主控端之寫入指令

主從式資料庫系統 - CH12


12 mysql

5. 其他增快技巧(續)

  • 欄名儘可能簡短,除了可以提高其可攜性,也可以縮短剖析時間

  • 執行MySQL低階介面指令之速度,通常會比SQL介面指令快2至5倍。必要的話,應用介面可置於伺服端,資料表也儘可能置於同一個伺服器上

  • 將備援資料(Replicate Database)備份在Slave Server上,如此可以將某些主控端連線在Slave伺服器以減輕主伺服器之負荷

主從式資料庫系統 - CH12


12 5 4

12-5-4 最佳化工作原理

  • MySQL以內建的最佳化運算器(Optimizer)來改進資料處理速度,它內含許多最佳化演算法,針對不同的SQL指令,經過剖析後,可以自行判定資料最快的讀取方式、讀取順序,或合理的讀取範圍

主從式資料庫系統 - CH12


1 where

1. Where 子句最佳化原理

  • 除去不必要的括弧

  • 合併常數

  • 除去常數範圍

  • 常數型運算子搭配索引表,資料只需檢索一次

  • Having子句未搭配Group By子句時,將以搭配之Where子句運算式來小計資料

  • Join關連資料時,所有資料表搭配之Where子句,其運算式之型式越簡單,所須剖析時間越短

主從式資料庫系統 - CH12


1 where1

1. Where 子句最佳化原理(續)

  • 擁有常數值的資料表,在查閱資料時將優先被讀取

  • 搭配Order By 和Group By子句,若兩者之運算式參照同一資料表,則該資料表優先被讀取

  • 搭配Order By 和Group By子句,兩者之運算式參照不同的資料表,或參照欄位非第一個資料表時,MySQL將自動建立一個暫存資料表

主從式資料庫系統 - CH12


1 where2

1. Where 子句最佳化原理(續)

  • 使用SQL_SMALL_RESULT模式,MySQL將自動使用記憶體型暫存表(in-memory temporary table)

  • 查詢資料時,MySQL會自動選配適當的索引表,除非它認定採掃描資料表方式更有效率。

主從式資料庫系統 - CH12


2 range

2. 界限值(Range)最佳化原理

  • 使用界限值當範圍來過濾資料時,MySQL會自動選配合適的索引表

  • 選用索引表時,可分成單鍵型:界限欄只有一個,以及多鍵型:界限欄有兩個或以上

  • 界線設定格式可分成三種:

    • 搭配B樹或Hash型式索引表:比較運算子可選用=、<=>、IN、IS NULL、或是IS NOT NULL

    • 搭配B樹索引表:比較運算子可選用>、<、>=、<=、!=、BETWEEN、<>,或是Like

    • 以And或Or連結之單鍵過濾運算式

主從式資料庫系統 - CH12


2 range1

2. 界限值(Range)最佳化原理(續)

  • MySQL在剖析界限運算式時,將自動以下列規則化減運算式

    • 無法建立界限條件部分將被抽換

    • 重疊之部分被合併

    • 空值條件被忽略

主從式資料庫系統 - CH12


3 order by

3. Order By 最佳化工作原理

  • MySQL使用 ”filesort” 最佳化演算法,將鍵值欄和紀錄位置排序,如此可以避免紀錄被讀取兩次

  • filesort工作原理如下

    • 首先依Where子句過濾資料

    • 依選出的紀錄,記下含排序欄值及紀錄位置,以及查詢之欄位集合

    • 將紀錄值組排序

    • 依排序值組直接讀取資料(不再透過資料表)

主從式資料庫系統 - CH12


3 order by1

3. Order By 最佳化工作原理(續)

  • 提升Order By之執行效率原則

    • 考慮的是使用索引表而非額外的排序

    • 假如無法搭配索引表時,你可以試著調整下列系統變數設定值:

      • 增加”sort_buffer_size”

      • 增加”read_rnd_buffer_size”

      • 將”tmpdir”指向一至多個空目錄

主從式資料庫系統 - CH12


12 5 5

12-5-5 避免掃描資料表之基本法則

  • 資料表掃描(Table Scan)發生原因

    • 料表只有10筆以下的紀錄,且每筆紀錄不長

    • On或Where子句運算式未包含索引表欄位

    • 比對索引鍵值後,值組範圍甚大,改採資料表掃描速度較快

    • 索引表跨越數個欄位,改採資料表掃描速度較快

主從式資料庫系統 - CH12


12 5 51

12-5-5 避免掃描資料表之基本法則(續)

  • 避免發生掃描資料表原則

    • 定期執行【Analyze Table】指令,重建索引表

    • 使用 ”Force Index” 子句,強制使用索引表

    • 啟動Mysqld伺服器時,搭配 ”--max-seeks-for-key=1000”,通知最佳化運算器,搜尋索引表時,不可能超過1000個

主從式資料庫系統 - CH12


12 mysql

12-6 資料表上鎖應用與問題探討

  • 12-6-1 上鎖的方法

    • MySQL使用下述方法將資料表上鎖

      • 鎖定資料表:ISAM,MyISAM及 Memory(Heap) 資料表

      • 鎖定資料頁:BDB資料表

      • 鎖定資料錄:InnoDB資料表

主從式資料庫系統 - CH12


12 6 1

12-6-1 上鎖的方法

  • MySQL在鎖定資料表時,可以完全排除互鎖(dead-lock)的死結問題

  • 對於寫入資料之上鎖方式,MySQL是以下列演算法來處理

    • 若該資料表未被鎖定,則立即上鎖

    • 否則,將上鎖請求置入 ”write lock” 佇列中

  • 對於讀取資料之上鎖方式,MySQL是以下列演算法來處理:

    • 若該資料表未以寫入鎖定,則立即上鎖

    • 否則,將上鎖請求置入 ”read lock” 佇列中

主從式資料庫系統 - CH12


12 6 11

12-6-1 上鎖的方法(續)

  • 無法同步處理多個Insert和Select 程序時,可以將新增資料存入暫存資料表,俟後,再將暫存表資料轉存至原資料表,處理程序如下

    Lock Tables 原資料表 Write, 暫存資料表 Write;

    Insert Into原資料表 Select * From暫存資料表;

    Truncate Table暫存資料表;

    Unlock Tables;

主從式資料庫系統 - CH12


12 6 12

12-6-1 上鎖的方法(續)

  • 紀錄鎖定模式之優缺點說明如下:

    • 優點

      • 在多執行緒下,處理不同的紀錄時,不太可能發生上鎖衝突問題

      • 異動資料時,不太可能發生需要捲回的情形

      • 一筆紀錄上鎖時間可較長

    • 缺點(與另外兩種模式比較)

      • 需要較大的記憶體

      • 速度較為緩慢(處理大型資料表時,需要較多的鎖定)

      • 處理大型資料之Group By程序時,非常不理想

主從式資料庫系統 - CH12


12 6 13

12-6-1 上鎖的方法(續)

  • 下列情況時,宜採資料表鎖定模式

    • 處理指令多數屬讀取資料

    • 更新或刪除資料可用單一索引鍵比對資料

    • Select伴隨同步式Insert指令,偶而有少許的Update和Delete

    • 需要掃描資料表時,例如處理Group By程序

主從式資料庫系統 - CH12


12 mysql

12-7 資料表開啟與關閉

  • MySQL允許開啟的資料表總數是受到限制的,開啟總數由下列三個系統變數來設定:

    • table_cache

    • max_conncetions

    • max_tmp_tables

主從式資料庫系統 - CH12


12 mysql

12-7 資料表開啟與關閉(續)

  • 不再使用的資料表,將依下列原則予以關閉,同時會從快取移除:

    • 快取已滿,執行緒需開起一個不在快取之新資料表

    • 快取資料表數已超過table_cache輸入屬性設定值,執行緒不再使用該資料表

    • 資料表已被【Flush Tables】指令處理過

主從式資料庫系統 - CH12


12 mysql

12-7 資料表開啟與關閉(續)

  • 快取已被填滿時,伺服器將依下列原則來挑選適當的快取空間,然後將新開起的資料表存入該快取:

    • 不再使用的資料表會依最久未被使用之時間,作為關閉優先順序

    • 快取已滿而無資料表可加以關閉時,此時將使用暫存快取區。置於暫存快取的資料表,當不再使用時,將立即被關閉及移除

主從式資料庫系統 - CH12


  • Login