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

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大全

標籤：MySQL

上一篇：java連接資料庫（jdbc）的標準規范

下一篇：SQLserver資料庫安裝教程