（6）MySQL進階篇SQL優化（MyISAM表鎖）

• 2021 年 4 月 13 日
• 筆記
• MySQL進階篇

1.MySQL鎖概述

鎖是電腦協調多個進程或執行緒並發訪問某一資源的機制。在資料庫中，除傳統的計算資源 （如 CPU、RAM、I/O 等）的搶佔以外，數據也是一種供許多用戶共享的資源。如何保證數 據並發訪問的一致性、有效性是所有資料庫必須解決的一個問題，鎖衝突也是影響資料庫並發訪問性能的一個重要因素。

2.MySQL鎖特性

相對其他資料庫而言，MySQL的鎖機制比較簡單，其最顯著的特點是不同的存儲引擎支援不同的鎖機制，歸納表格如下：

註：InnoDB存儲引擎但默認情況下是採用行級鎖。
MySQL這3種鎖的特性可大致歸納如下：
●表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高，並發度最低。
●行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，並發度最高。
●頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，並發度一般。
從上述特點可見，很難籠統地說哪種鎖更好，只能就具體應用程式的特點來說哪種鎖更合適！僅從鎖的角度來說：表級鎖更適合於以查詢為主，只有少量按索引條件更新數據的應用程式，如Web應用程式；而行級鎖則更適合於有大量按索引條件並發更新少量不同數據，同時又有並發查詢的應用程式，如一些在線事務處理（OLTP）系統。接下來鎖內容將重點介紹MySQL表鎖和InnoDB行鎖的問題，由於BDB已經被InnoDB取代，已經成為歷史，在此就不做進一步的討論了。

3.MyISAM表鎖

MyISAM存儲引擎只支援表鎖，這也是MySQL開始幾個版本中唯一支援的鎖類型。隨著應用程式對事務完整性和並發性要求的不斷提高，MySQL才開始開發基於事務的存儲引擎，後來慢慢出現了支援頁鎖的BDB存儲引擎和支援行鎖的InnoDB存儲引擎（InnoDB實際是單獨的一個公司，後面被Oracle公司收購了）。但是MyISAM的表鎖依然是使用最為廣泛的鎖類型。

3.1查詢表級鎖爭用情況

可以通過檢查table_locks_waited和table_locks_immediate狀態變數來分析系統上的表鎖定爭奪：

SHOW STATUS LIKE '%Table_locks%';

●Table_locks_immediate：表示立即釋放表鎖數。
●Table_locks_waited：表示需要等待的表鎖數。
如果Table_locks_waited的值比較高，則說明存在著較嚴重的表級鎖爭用情況。如果Table_locks_immediate / Table_locks_waited > 5000，最好採用InnoDB引擎，因為InnoDB是行鎖而MyISAM是表鎖，對於高並發寫入的應用程式InnoDB效果會好些。

3.2MySQL表級鎖的鎖模式

MySQL的表級鎖有兩種模式：表共享讀鎖（Table Read Lock）和表獨佔寫鎖（Table Write Lock）。
因為8.0版本以上MySQL創建表類型（儲存引擎）默認是InnoDB類型（通過以下命令可以看到）：

SHOW ENGINES;

所以下面例子中我們需要把創建的表類型轉換為MyISAM類型方便測試，首先創建兩個結構一樣的goods_test、goods_test_tmp測試表，方便測試：

CREATE TABLE `goods_test`  (
  `ID` int NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `Name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '名稱',
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
..............

然後把goods_test、goods_test_tmp表從InnoDB轉換為MyISAM表類型：

ALTER TABLE goods_test ENGINE=MyISAM;
ALTER TABLE goods_test_tmp ENGINE=MyISAM;

再查看下goods_test、goods_test_tmp表類型：

SHOW TABLE STATUS LIKE 'goods_test';

SHOW TABLE STATUS LIKE 'goods_test_tmp';

MyISAM在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖，這個過程並不需要用戶干預，因此，用戶一般不需要直接用LOCK TABLE命令給MyISAM表顯式加鎖。在以下的示例中，顯式加鎖基本上都是為了方便演示而已，並非必須如此的。

3.2.1MyISAM存儲引擎的寫阻塞讀例子

從上述例子可見，對MyISAM表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作；MyISAM表的讀操作與寫操作之間，以及寫操作之間是串列的！當一個執行緒獲得對一個表的寫鎖後，只有持有鎖的執行緒可以對錶進行更新操作。其他執行緒的讀、寫操作都會等待，直到鎖被釋放為止，再來看看以下語句：

LOCK TABLE goods_stock READ LOCAL, goods_stock_price READ LOCAL; 

註：LOCK TABLES語句後面加了「LOCAL」選項，其作用就是在滿足MyISAM表並發插入條件的情況下，允許其他用戶在表尾並發插入記錄。

3.2.2MyISAM存儲引擎的讀阻塞寫例子

從上述例子可見，對MyISAM表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求。其實，在自動加鎖的情況下也基本如此，MyISAM總是一次獲得SQL語句所需要的全部鎖。這也正是MyISAM表不會出現死鎖（Deadlock Free）的原因。
註：還有一點，當使用LOCK TABLES時，不僅需要一次鎖定用到的所有表，而且，同一個表在SQL語句中出現多少次，就要通過與SQL語句中相同的別名鎖定多少次，否則也會出錯！舉例如下：
●對goods_test表獲得讀鎖：

LOCK TABLE goods_test READ;

●但是通過別名（AS [tableName]）訪問會提示錯誤：

SELECT a.ID,a.`Name` FROM goods_test AS a JOIN goods_test AS b ON a.ID=b.ID
> 1100 - Table 'a' was not locked with LOCK TABLES
> 時間: 0.017s

●需要對別名分別鎖定：

LOCK TABLE goods_test AS a READ,goods_test AS b READ
> OK
> 時間: 0.006s

●按照別名的查詢可以正確執行：

SELECT a.ID,a.`Name` FROM goods_test AS a JOIN goods_test AS b ON a.ID=b.ID
> OK
> 時間: 0.004s

3.2.3 MyISAM存儲引擎的讀寫（並發插入（Concurrent Inserts））並發例子

上小節提到過MyISAM表的讀和寫是串列的，但這是就總體而言的。在一定條件下，MyISAM表也支援查詢和插入操作的並發進行。 MyISAM存儲引擎有一個系統變數concurrent_insert，專門用以控制其並發插入的行為，其值分別可以為0、1或2。
●當concurrent_insert設置為0時，不允許並發插入。
●當concurrent_insert設置為1時，如果MyISAM表中沒有空洞（即表的中間沒有被刪除的行），MyISAM允許在一個進程讀表的同時，另一個進程從表尾插入記錄。這也是MySQL的默認設置。
●當concurrent_insert設置為2時，無論MyISAM表中有沒有空洞，都允許在表尾並發插入記錄。
請看以下例子：

可以利用MyISAM存儲引擎的並發插入特性，來解決應用程式中對同一表查詢和插入的鎖爭用。例如，將concurrent_insert系統變數設為2，總是允許並發插入；同時，通過定期在系統空閑時段執行OPTIMIZE TABLE語句來整理空間碎片，收回因刪除記錄而產生的中間空洞。有關OPTIMIZE TABLE語句的詳細介紹，可以參見第三章「MySQL進階篇SQL優化（索引）」裡面「定期優化表」5.2小節的內容。

3.3 MyISAM的鎖調度

前面講過，MyISAM存儲引擎的讀鎖和寫鎖是互斥的，讀寫操作是串列的。那麼，一個進程請求某個MyISAM表的讀鎖，同時另一個進程也請求同一表的寫鎖，MySQL如何處理呢？
答案是寫進程先獲得鎖。不僅如此，即使讀請求先到鎖等待隊列，寫請求後到，寫鎖也會插到讀鎖請求之前！這是因為MySQL認為寫請求一般比讀請求要重要。這也正是MyISAM表不太適合於有大量更新操作和查詢操作應用的原因，因為，大量的更新操作會造成查詢操作很難獲得讀鎖，從而可能永遠阻塞。這種情況有時可能會變得非常糟糕！幸好我們可以通過一些設置來調節MyISAM的調度行為：
●通過指定啟動參數low-priority-updates，使MyISAM引擎默認給予讀請求以優先的權利。
●通過執行命令SET LOW_PRIORITY_UPDATES=1，使該連接發出的更新請求優先順序降低。
●通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優先順序。
雖然上面三種方法都是要麼更新優先，要麼查詢優先的方法，但還是可以用其來解決查詢相對重要的應用程式（如用戶登錄系統）中讀鎖等待嚴重的問題。
另外，MySQL也提供了一種折中的辦法來調節讀寫衝突，即給系統參數max_write_lock_count設置一個合適的值，當一個表的讀鎖達到這個值後，MySQL就暫時將寫請求的優先順序降低，給讀進程一定獲得鎖的機會。
上面已經討論了寫優先調度機制帶來的問題和解決辦法。這裡還要強調一點：一些需要長時間運行的查詢操作，也會使寫進程「餓死」！因此，應用程式中應盡量避免出現長時間運行的查詢操作，不要總想用一條SELECT語句來解決問題，因為這種看似巧妙的SQL語句，往往比較複雜，執行時間較長，在可能的情況下可以通過使用中間表等措施對SQL語句做一定的「分解」，使每一步查詢都能在較短時間完成，從而減少鎖衝突。如果複雜查詢不可避免，應盡量安排在資料庫空閑時段執行，比如一些定期統計可以安排在夜間執行。

參考文獻：
深入淺出MySQL大全