史上最全MySQL參數調優及資料庫鎖實踐

    • 1. 應用優化
      • 1.2 減少對MySQL的訪問
        • 1.2.1 避免對數據進行重複檢索
        • 1.2.2 增加cache層
      • 1.3 負載均衡
        • 1.3.1 利用MySQL複製分流查詢
        • 1.3.2 採用分散式數據架構
    • 2. MySQL中查詢緩存優化
      • 2.1 概述
      • 2.2 操作流程
      • 2.3 查詢緩存配置
      • 2.4 開啟查詢緩存
      • 2.5 查詢緩存SELECT選項
      • 2.6 查詢緩存失效的情況
    • 3. MySQl內存管理及優化
      • 3.1 內存優化原則
      • 3.2 MyISAM內存優化
      • 3.3 InnoDB內存優化
    • 4. MySQL並發參數調整
      • 4.1 max_connections
      • 4.2 back_log
      • 4.3 table_open_cache
      • 4.4 thread_cache_size
      • 4.5 innodb_lock_wait_timeout
    • 5. MySQl鎖問題
      • 5.1 鎖概述
      • 5.2 鎖分類
      • 5.3 MySQL鎖
      • 5.2 MyISAM表鎖
        • 5.2.1 如何加表鎖
        • 5.2.4 結論
        • 5.2.5 查看鎖的爭用情況
      • 5.3 InnoDB行鎖
        • 5.3.1 行鎖介紹
        • 5.3.2 背景知識
        • 5.3.3 InnoDB的行鎖模式
        • 5.3.6 無索引行鎖升級為表鎖
        • 5.3.7 間隙鎖危害
        • 5.3.8 InnoDB行鎖爭用情況
        • 5.3.9 總結
    • 6. 常用SQL技巧
      • 6.1 SQL執行順序
      • 6.2 正則表達式
      • 6.3 MySQL常用函數

1. 應用優化

對於訪問資料庫來說，建立連接的代價是比較昂貴的，因為我們頻繁的創建關閉連接，是比較耗費資源的，我們有必要建立資料庫連接池，以提高訪問的性能。

1.2 減少對MySQL的訪問

1.2.1 避免對數據進行重複檢索

在編寫應用代碼時，需要能夠理清對資料庫的訪問邏輯。能夠一次連接就獲取到結果的，就不用兩次連接，這樣可以大大減少對數據無用的重複請求。

比如，需要獲取書籍的id和name欄位，則查詢如下 ：select id，name from tb_book;之後，在業務邏輯中有需要獲取到書籍狀態信息，則查詢如下 ：select id,status from tb_book;

1.2.2 增加cache層

在應用中，我們可以在應用中增加緩存層來達到減輕資料庫負擔的目的。緩存層有很多種，也很有很多實現方式，只要能達到資料庫的負擔又能滿足應用需求就可以。

因此可以部分數據從資料庫中抽取出來放到應用端以文本方式存儲，或者使用框架（Mybatis，Hibernate）提供的一級緩存/二級緩存，或者使用redis資料庫來緩存數據。

1.3 負載均衡

負載均衡是應用中使用非常普遍的一種優化方法，它的機制就是利用某種均衡演算法，將固定的負載量分佈到不同的伺服器上，以此來降低單臺伺服器的負載，達到優化的效果。

1.3.1 利用MySQL複製分流查詢

通過MySQL的主從複製，實現讀寫分離，使增刪改操作走主節點，查詢操作走從節點，從而可以降低單臺伺服器的讀寫壓力。

1.3.2 採用分散式數據架構

分散式資料庫架構適合大數據量、負載高的情況，它有良好的拓展性和高可用性。通過在多臺伺服器之間分佈數據，可以實現在多臺伺服器之間的負載均衡，提高訪問效率。

2. MySQL中查詢緩存優化

2.1 概述

開啟MySQL的查詢緩存，當執行完全相同的SQL語句的時候，伺服器就會直接從緩存中讀取結果，當數據被修改，之前的緩存會失效，修改比較頻繁的表不適合做查詢緩存。

2.2 操作流程

  1. 客戶端發送一條查詢給伺服器；
  2. 伺服器先回檢查查詢緩存，如果命中了緩存，則立即返回存儲在緩存中的結果。否則進入下一階段；
  3. 伺服器端進行SQL解析、預處理，再由優化器生成對應的執行計劃；
  4. 執行計劃查詢執行引擎調用存儲引擎介面獲取數據並返回結果，以及更新緩存。

2.3 查詢緩存配置

1.查看當前的MySQL資料庫是否支持查詢緩存 ：

SHOW VARIABLES LIKE 『have_query_cache』;

2.查看當前MySQL是否開啟了查詢緩存 ：SHOW VARIABLES LIKE 『query_cache_type』;

3.查看查詢緩存的佔用大小 ：SHOW VARIABLES LIKE 『query_cache_size』;

4.查看查詢緩存的狀態變數 ：

SHOW STATUS LIKE 『Qcache%』;

各個變數的含義如下 :

2.4 開啟查詢緩存

MySQL的查詢緩存默認是關閉的，需要手動配置參數query cache type，來開啟查詢緩存。query cache type該參數的可取值有三個 ：

1）OFF或0 ：查詢緩存功能關閉；2）ON或1 ：查詢緩存功能打開，SELECT的結果符合緩存條件即會緩存，否則，不予緩存，顯示指定SQL_NO_CACHE，不予緩存

3）DEMAND或2 ：查詢緩存功能按需進行，顯示指定SQL_CACHE的SELECT語句才會緩存；其他均不予緩存。

在/usr/my.cnf配置中，增加以下配置 ：開啟mysql的查詢緩存query_cache_type=1配置完畢之後，重啟服務即可生效；然後就可以在命令行執行SQL語句進行驗證，執行一條比較耗時的SQL語句，然後再多執行幾次，查看後面幾次的執行時間；獲取通過查看查詢緩存的緩存命中數，來判定是否走查詢緩存。

2.5 查詢緩存SELECT選項

可以在SELECT語句中指定兩個與查詢緩存相關的選項 ：

SQL_CACHE : 如果查詢結果是可緩存的，並且query_cache_type系統變數的值為ON或DEMAND，則緩存查詢結果。SQL_NO_CACHE : 伺服器不使用查詢緩存。它既不查詢緩存，也不檢查是否已緩存，也不緩存查詢結果。

例子 ：

2.6 查詢緩存失效的情況

1）SQL語句不一致的情況，要想命中查詢緩存，查詢的SQL語句必須一致。

2）當查詢語句中有一些不確定的時，則不會緩存。如 ：now(),current_date(),curdate(),curtime(),rand(),uuid(),database().

3）不使用任何錶查詢語句。

select 『A』;4）查詢MySQL，information_schema 或 performance_schema資料庫中的表時，不會走查詢緩存。select * from information_schema.engines;5）在存儲的函數，觸發器或事件的主體內執行的查詢。6）如果表更改，則使用該表的所有高速緩存查詢都將變為無效並從高速緩存中刪除。這包括使用MERGE映射到已更改表的表的查詢。一個表可以被許多類型的語句，如被改變INSERT，UPDATE，TRUNCATE TABLE，ALTER TABLE，DROP TABLE，或 DROP DATABASE。

3. MySQl內存管理及優化

3.1 內存優化原則

1）將盡量多的內存分配給MySQL做緩存，但要給操作系統和其他程序預留足夠內存。

2）MyISAM存儲引擎的數據文件讀取依賴於操作系統自身的IO緩存，因此，如果有MySQL表，就要預留更多的內存給操作系統做IO緩存。

3）排序區、連接區等緩存是分配給每個資料庫會後（session）專用的，其默認值的設置要根據最大連接數合理分配，如果設置太大，不但浪費資源，而且在並發連接較高時會導致物理內存耗盡。

3.2 MyISAM內存優化

myisam存儲引擎使用key_buffer緩存索引塊，加速myisam索引的讀寫速度。對於myisam表的數據塊，mysql沒有特別的緩存機制，完全依賴於操作系統的IO緩存。

key_buffer_size

key_buffer_size決定MyISAM索引塊緩存區的大小，直接影響到MyISAM表的存取效率。可以在MySQL參數文件中設置key_buffer_size的值，對於一般MyISAM數據塊，建議至少將1/4可用內存分配給key_buffer_size。在/usr/my.cnf中做如下配置 ：key_buffer_size=512M

read _buffer_size

如果需要經常順序掃描myisam表，可以通過增大read_buffer_size的值來改善性能。但需要注意的是read_buffer_size是每個session獨佔的，如果默認值設置太大，就會造成內存浪費。

read_rnd_buffer_size

對於需要做排序的myisam表的查詢，如帶有order by子句的sql，適當增加read_buffer_size的值，可以改善此類的sql性能。但需要注意的是read_rnd_buffer_size是每個session獨佔的，如果默認值設置太大，就會造成內存浪費。

3.3 InnoDB內存優化

innoDB用一塊內存區做IO緩存池，該緩存池不僅用來緩存innodb的索引塊，而且也用來緩存innodb的數據塊。

innodb_buffer_pool_size

該變數決定了innodb存儲引擎表數據和索引數據的最大緩存區大小。在保證操作系統及其他程序有足夠內存可用的情況下，innodb_buffer_pool_size的值越大，緩存命中率越高，訪問InnoDB表需要的磁碟I/O就越少，性能也就越高。innodb_buffer_pool_size=512M

innodb_log_buffer_size

決定innodb重做日誌緩存的大小，對於可能產生大量更新記錄的大事務，增加innodb_log_buffer_size的大小，可用避免innodb在事物提交前就執行不必要的日誌寫入磁碟操作。innodb_log_buffer_size=10M

4. MySQL並發參數調整

從實現上來說，MySQL Server是多線程結構，包括後臺線程和客戶服務線程。多線程可用有效利用伺服器資源，提供資料庫的並發性能。

在MySQL中，控制並發連接和線程的主要參數包括max_connections、back_log、thread_cache_siez、table_open_cache。

4.1 max_connections

採用max_connections控制允許連接到MySQL資料庫的最大數量，默認值時151.如果狀態變數connection_errors_max_connections不為零，並且一直增長，則說明不斷有連接請求因資料庫連接數已達到允許最大值而失敗，這是可以考慮增大max_connections的值。

MySQL最大可支持的連接數，取決於很多因素，包括給定操作系統平臺的線程庫的質量、內存大小、每個連接的負荷、CPU的處理速度，期望的響應時間等。在Linux平臺下，性能好的伺服器，支持500-1000個連接不是難事，需要根據伺服器性能進行評估設定。

4.2 back_log

back_log參數控制MySQL監聽TCP埠時設置的積壓請求棧大小。如果MySQL的連接數達到max_connections時，新來的請求將會被存在堆棧中，以等待某一連接釋放資源，該堆棧的數量即back_log，如果等待連接的數量超過back_log，將不被授予連接資源，將會報錯。5.6.6版本之前默認值為50，之後的版本默認為50+（max_connections / 5），最大不超過900。

如果需要資料庫在較短的時間內處理大量連接請求，可以考慮適當增大back_log的值。

4.3 table_open_cache

該參數用來控制所有SQL語句執行線程可打開表緩存的數量，而在執行SQL語句時，每一個SQL執行線程至少要打開1個表緩存。該參數的值應該根據設置的最大連接數mac_connections以及每個連接執行關聯查詢中涉及的表的最大數量來設定 ：

max_connections x N ;

4.4 thread_cache_size

為了加快連接資料庫的速度，MySQL會緩存一定數量的客戶服務線程以備重用，通過參數thread_cache_size可控制MySQL緩存客戶服務線程的數量。

4.5 innodb_lock_wait_timeout

該參數是用來設置InnoDB事務等待行鎖的時間，默認值是50ms，可以根據需要進行動態設置。對於需要快速反饋的業務系統來說，可以將行鎖的等待時間調小，以避免事務長時間掛起；對於後臺運行的批量處理程序來說，可以將行鎖的等待時間調大，以避免發生大的回滾操作。

5. MySQl鎖問題

5.1 鎖概述

鎖是計算機協調多個進程或線程並發訪問某一資源的機制。

在資料庫中，除傳統的計算資源（如CPU、RAM、I/O等）的爭用之外，數據也是一種供許多用戶共享的資源。如何保證數據並發訪問的一致性、有效性是所有資料庫必須解決的一個問題，鎖衝突也是影響資料庫並發訪問性能的一個重要因素。從這個角度來說，鎖對資料庫而言顯得尤其重要，也更加複雜。

5.2 鎖分類

從對數據操作的粒度分：

1）表鎖 ：操作時，會鎖定整個表。2）行鎖 ：操作時，會鎖定當前操作行。從對數據操作的類型分 ：1）讀鎖（共享鎖） ：針對同一份數據，多個讀操作可以同時進行而不會互相影響（只能讀不能寫，寫必須先釋放鎖）。2）寫鎖（排它鎖） ：當前操作沒有完成之前，它會阻斷其他寫鎖和讀鎖（只能在當前操作釋放寫鎖，其他操作纔可以進行）。

5.3 MySQL鎖

相對其他資料庫而言，MySQL的鎖機制比較簡單，其最顯著的特點是不同的存儲引擎不同的鎖機制。下表中羅列出了各存儲引擎對鎖的支持情況 ：

MySQL這三種鎖的特性可大致歸納如下 ：表級鎖 ：偏向MyISAM存儲引擎，開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高，並發度最低。行級鎖 ：偏向InnoDB存儲引擎，開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，並發度也最高。頁面鎖 ：開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，並發度一般。從上述特點可見，很難籠統地說那種鎖更好，只能就具體應用的特點來說那種鎖更合適！僅從鎖的角度來說 ：表級鎖更適合於以查詢為主，只有少量按索引條件更新數據的應用，如web應用；而行級鎖則更適合於大量按索引條件並發更新少量不同數據，同時又並查詢的應用，如一些在線事物處理（OLTP）系統。

5.2 MyISAM表鎖

MyISAM存儲引擎只支持表鎖，這也是MySQL開始幾個版本中唯一支持的鎖類型。

5.2.1 如何加表鎖

MyISAM在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖，這個過程並不需要用戶幹預，因此，用戶一般不需要直接用LOCK TABLE命令給MyISAM表顯示加鎖。

顯示加表鎖語法：加讀鎖 ：lock table table_name read;加寫鎖 ：lock table table_name write;unlock tables : 解鎖

5.2.4 結論

鎖模式的相互兼容性如表中所示 ：

由上表可見 ：1）對MyISAM表的讀操作，不會阻塞其他用戶對同一表的讀請求，但是會阻塞對同一表的寫請求；2）對MyISAM表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作；簡而言之，就是讀鎖會阻塞寫，但是不會阻塞讀。而寫鎖，則既會阻塞讀，又會阻塞寫。此外，MyISAM的讀寫鎖調度是寫優先，這也是MyISAM不適合做寫為主的表的存儲引擎的原因。因為寫鎖後，其他線程不能做任何操作，大量的更新會使查詢很難得到鎖，從而造成用於阻塞。

5.2.5 查看鎖的爭用情況

show open tables;

In_user : 表當前被查詢使用的次數。如果該數為零，則表是打開的，但是當前沒有被使用。Name_locked : 表名稱是否被鎖定。名稱鎖定用於取消表或對錶進行重命名等操作。show status like 『Table_locks%』;

Table_locks_immediate : 指的是能夠立即獲得表級鎖的次數，每立即獲取鎖，值加1。Table_locks_waited : 指的是不能立即獲取表級鎖而需要等待的次數，每等待一次，該值加1，此值高說明存在著較為嚴重的表級鎖爭用情況。

5.3 InnoDB行鎖

5.3.1 行鎖介紹

行鎖特點 ：偏向InnoDB存儲引擎，開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，並發度也最高。

InnoDB與MyISAM的最大不同有兩點 ：一是支持事務；二是採用了行級鎖。

5.3.2 背景知識

事務及其ACID屬性

事務是由一組SQL語句組成的邏輯處理單元。事務具有以下四個特性，簡稱為事務ACID屬性。

並發事務處理帶來的問題

事務隔離級別為瞭解決上述提到的事務並發問題，資料庫提供一定的事務隔離機制來解決這個問題。資料庫的事務隔離越嚴格，並發副作用越小，但付出的代價也就越大，因為事務隔離實質上就是使用事務在一定程度上「串列化」進行，這顯然與「並發」是矛盾的。資料庫的隔離級別有四個，由低到高依次為Read uncommitted、Read committed、Repeatable read、Serializable，這四個級別可以逐個解決臟寫、臟讀、不可重複讀、幻讀這幾類問題。

MySQL的資料庫的默認隔離級別Repeatable read ,查看方式 ：

5.3.3 InnoDB的行鎖模式

InnoDB實現了以下兩種類型的行鎖。

共享鎖（S）：又稱為讀鎖，簡稱S鎖，共享鎖就是多個事務對於同一數據可以共享一把鎖，都能訪問到數據，但是隻能讀不能修改。排他鎖（X）：又稱為寫鎖，簡稱X鎖，排他鎖就是不能與其他鎖並存，入一個事務獲取了一個數據行的排他鎖，其他事務就不能再獲取該行的其他鎖，包括共享鎖和排他鎖，但是獲取排他鎖的事務是可以對數據進行讀取和修改。對於update、delete和insert語句，InnoDB會自動給涉及數據集加排他鎖（X）；對於普通SELECT語句，InnoDB不會加任何鎖；可以通過以下語句顯示給記錄集加共享鎖或排他鎖。共享鎖（S）：select * from table_name where LOCK IN SHARE MODE排他鎖（X）：select * from table_name where … FOR UPDATE

5.3.6 無索引行鎖升級為表鎖

如果不通過索引條件檢索數據，那麼InnoDB將對表中的所有記錄加鎖，實際效果更表鎖一樣。

查看當前表的索引 : show index from test_innodb_lock;

由於執行更新時，name欄位本來為varchar類型，我們是作為數組類型使用，存在類型轉換，索引失效，最終行鎖變為表鎖。

5.3.7 間隙鎖危害

當我們用範圍條件，而不是使用相等條件檢索數據，並請求共享或排他鎖時，InnoDB會給符合條件的已有數據進行加鎖；對於鍵值在條件返回內但並不存在的記錄，叫做「間隙（GAP）」，InnoDB也會對這個「間隙」加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）。

5.3.8 InnoDB行鎖爭用情況

show status like 『innodb_row_lock%』;

Innodb_row_lock_current_waits : 當前正在等待鎖定的數量Innodb_row_lock_time : 從系統啟動到現在鎖定總時間長度Innodb_row_lock_time_avg : 每次等待所花平均時長Innodb_row_lock_time_max : 從系統啟動到現在等待最長的一次所花的時間Innodb_row_lock_waits : 系統啟動後到現在總共等待的次數當等待的次數很高，而且每次等待的時長也不小的時候，我們就需要分析系統中為什麼會有如此多的等待，然後根據分析結果訂製優化計劃。

5.3.9 總結

InnoDB存儲引擎由於實現了行級鎖定，雖然在鎖定機制的實現方面帶來了性能損耗可能比表鎖會更高一些，但是在整體並發處理能力方面要遠遠優於MyISAM的表鎖的。當系統並發量較高時，InnoDB的整體性能和MyISAM相比就會有比較明顯的優勢。

但是，InnoDB的行級鎖同樣也有其脆弱的一面，當我們使用不當的時候，可能會讓InnoDB的整體性能表現不僅不能比MyISAM高，甚至可能會更差。優化建議 ：儘可能讓所有數據檢索都能通過索引來完成，避免無索引行鎖升級為表鎖。合理設計索引，盡量縮小鎖的範圍。儘可能減少索引條件，及索引範圍，避免間隙鎖。盡量控制事務大小，減少鎖定資源量和時間長度。盡可使用低級別事務隔離（但是需要業務層面滿足需求）。

6. 常用SQL技巧

6.1 SQL執行順序

編寫順序

執行順序

6.2 正則表達式

正則表達式（Regular Expression）是指一個用來描述或者匹配一系列符合某個句法規則的字元串的單個字元串。

6.3 MySQL常用函數

數字函數

字元串函數

日期函數

聚合函數

推薦閱讀：
相關文章