MySQL 最佳化（五）

文章目錄

• 7.3 鎖
• 7.3.1 鎖機制
• 7.3.2 鎖表

7.3 鎖7.3.1 鎖機制

當前MySQL已經支援 ISAM, MyISAM, MEMORY (HEAP) 類型表的表級鎖了，BDB 表支援頁級鎖，InnoDB 表支援行級鎖。
很多時候，可以通過經驗來猜測什麼樣的鎖對應用程式更合適，不過通常很難說一個鎖比別的更好，這全都要依據應用程式來決定，不同的地方可能需要不同的鎖。
想要決定是否需要採用一個支援行級鎖的儲存引擎，就要看看應用程式都要做什麼，其中的查詢、更新語句是怎麼用的。例如，很多的web應用程式大量的做查詢，很少刪除，主要是基於索引的更新，只往特定的表中插入記錄。採用基本的MySQL MyISAM 表就很合適了。
MySQL中對錶級鎖的儲存引擎來說是釋放死結的。避免死結可以這樣做到：在任何查詢之前先請求鎖，並且按照請求的順序鎖表。
MySQL中用於 WRITE（寫） 的表鎖的實現機制如下：

• 如果表沒有加鎖，那麼就加一個寫鎖。
• 否則的話，將請求放到寫鎖隊列中。

MySQL中用於 READ（讀） 的表鎖的實現機制如下：

• 如果表沒有加寫鎖，那麼就加一個讀鎖。
• 否則的話，將請求放到讀鎖隊列中。

當鎖釋放後，寫鎖隊列中的線程可以用這個鎖資源，然後才輪到讀鎖隊列中的線程。
這就是說，如果表裡有很多更新操作的話，那麼 Select 必須等到所有的更新都完成了之後才能開始。
從 MySQL 3.23.33 開始，可以通過狀態變數 Table_locks_waited 和 Table_locks_immediate 來分析系統中的鎖表爭奪情況：

mysql> SHOW STATUS LIKE 'Table%';+-----------------------+---------+| Variable_name         | Value   |+-----------------------+---------+| Table_locks_immediate | 1151552 || Table_locks_waited    | 15324   |+-----------------------+---------+

在 MySQL 3.23.7（在Windows上是3.23.25）以後，在 MyISAM 表中只要沒有衝突的 Insert 操作，就可以無需使用鎖表自由地並存執行 Insert 和 Select 語句。也就是說，可以在其它用戶端正在讀取 MyISAM 表記錄的同時時插入新記錄。如果資料檔案的中間沒有空餘的磁碟塊的話，就不會發生衝突了，因為這種情況下所有的新記錄都會寫在資料檔案的末尾（當在表的中間做刪除或者更新操作時，就可能導致空洞）。當空洞被新資料填充後，並行插入特性就會自動重新被啟用了。
如果想要在一個表上做大量的 Insert 和 Select 操作，但是並行的插入卻不可能時，可以將記錄插入到暫存資料表中，然後定期將暫存資料表中的資料更新到實際的表裡。可以用以下命令實現：

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;mysql> Insert INTO real_table Select * FROM insert_table;mysql> TRUNCATE TABLE insert_table;mysql> UNLOCK TABLES;

InnoDB 使用行級鎖，BDB 使用頁級鎖。對於 InnoDB 和 BDB 儲存引擎來說，是可能產生死結的。這是因為 InnoDB 會自動捕獲行鎖，BDB 會在執行 SQL 陳述式時捕獲頁鎖的，而不是在事務的開始就這麼做。
行級鎖的優點有：

• 在很多線程請求不同記錄時減少衝突鎖。
• 交易回復時減少改變資料。
• 使長時間對單獨的一行記錄加鎖成為可能。

行級鎖的缺點有：

• 比頁級鎖和表級鎖消耗更多的記憶體。
• 當在大量表中使用時，比頁級鎖和表級鎖更慢，因為他需要請求更多的所資源。
• 當需要頻繁對大部分資料做 GROUP BY 操作或者需要頻繁掃描整個表時，就明顯的比其它鎖更糟糕。
• 使用更高層的鎖的話，就能更方便的支援各種不同的類型應用程式，因為這種鎖的開銷比行級鎖小多了。

表級鎖在下列幾種情況下比頁級鎖和行級鎖更優越：

• 很多操作都是讀表。
• 在嚴格條件的索引上讀取和更新，當更新或者刪除可以用單獨的索引來讀取得到時：

  Update tbl_name SET column=value Where unique_key_col=key_value;    Delete FROM tbl_name Where unique_key_col=key_value;    

• Select 和 Insert 語句並發的執行，但是只有很少的 Update 和 Delete 語句。
• 很多的掃描表和對全表的 GROUP BY 操作，但是沒有任何寫表。

表級鎖和行級鎖或頁級鎖之間的不同之處還在於：
將同時有一個寫和多個讀的地方做版本（例如在MySQL中的並發插入）。也就是說，資料庫/表支援根據開始訪問資料時間點的不同支援各種不同的試圖。其它名有：時間行程，寫複製，或者是按需複製。
原文： Versioning (such as we use in MySQL for concurrent inserts) where you can have one writer at the same time as many readers. This means that the database/table supports different views for the data depending on when you started to access it. Other names for this are time travel, copy on write, or copy on demand.
按需複製在很多情況下比頁級鎖或行級鎖好多了。儘管如此，最壞情況時還是比其它正常鎖使用了更多的記憶體。
可以用應用程式級鎖來代替行級鎖，例如MySQL中的 GET_LOCK() 和 RELEASE_LOCK()。但它們是勸告鎖（原文：These are advisory locks），因此只能用於安全可信的應用程式中。

7.3.2 鎖表

為了能有快速的鎖，MySQL除了 InnoDB 和 BDB 這兩種儲存引擎外，所有的都是用表級鎖（而非頁、行、列級鎖）。
對於 InnoDB 和 BDB 表，MySQL只有在指定用 LOCK TABLES 鎖表時才使用表級鎖。在這兩種表中，建議最好不要使用 LOCK TABLES，因為 InnoDB 自動採用行級鎖，BDB 用頁級鎖來保證事務的隔離。
如果資料表很大，那麼在大多數應用中表級鎖會比行級鎖好多了，不過這有一些陷阱。
表級鎖讓很多線程可以同時從資料表中讀取資料，但是如果另一個線程想要寫資料的話，就必須要先取得排他訪問。正在更新資料時，必須要等到更新完成了，其他線程才能訪問這個表。
更新操作通常認為比讀取更重要，因此它的優先順序更高。不過最好要先確認，資料表是否有很高的 Select 操作，而更新操作並非很‘急需’。
表鎖鎖在一個線程在等待，因為磁碟空間滿了，但是卻需要有空餘的磁碟空間，這個線程才能繼續處理時就有問題了。這種情況下，所有要訪問這個出問題的表的線程都會被置為等待狀態，直到有剩餘磁碟空間了。
表鎖在以下設想情況中就不利了：

• 一個用戶端提交了一個需要長時間啟動並執行 Select 操作。
• 其他用戶端對同一個表提交了 Update 操作，這個用戶端就要等到 Select 完成了才能開始執行。
• 其他用戶端也對同一個表提交了 Select 請求。由於 Update 的優先順序高於 Select，所以 Select 就會先等到 Update 完成了之後才開始執行，它也在等待第一個 Select 操作。

下列所述可以減少表鎖帶來的資源爭奪：

• 讓 Select 速度盡量快，這可能需要建立一些摘要表。
• 啟動 mysqld 時使用參數 --low-priority-updates。這就會讓更新操作的優先順序低於 Select。這種情況下，在上面的假設中，第二個 Select 就會在 Insert 之前執行了，而且也無需等待第一個Select 了。
• 可以執行 SET LOW_PRIORITY_UpdateS=1 命令，指定所有的更新操作都放到一個指定的連結中去完成。詳情請看“14.5.3.1 SET Syntax”。
• 用 LOW_PRIORITY 屬性來降低 Insert，Update，Delete 的優先順序。
• 用 HIGH_PRIORITY 來提高 Select 語句的優先順序。詳情請看“14.1.7 Select Syntax”。
• 從MySQL 3.23.7 開始，可以在啟動 mysqld 時指定系統變數 max_write_lock_count 為一個比較低的值，它能強制臨時地提高表的插入數達到一個特定值後的所有 Select 操作的優先順序。它允許在 WRITE 鎖達到一定數量後有 READ 鎖。
• 當 Insert 和 Select 一起使用出現問題時，可以轉而採用 MyISAM 表，它支援並發的Select 和 Insert 操作。
• 當在同一個表上同時有插入和刪除操作時，Insert DELAYED 可能會很有用。詳情請看“14.1.4.2 Insert DELAYED Syntax”。
• 當 Select 和 Delete 一起使用出現問題時，Delete 的 LIMIT 參數可能會很有用。詳情請看“14.1.1 Delete Syntax”
• 執行 Select 時使用 SQL_BUFFER_RESULT 有助於減短鎖表的期間.詳情請看“14.1.7 Select Syntax”。
• 可以修改原始碼 `mysys/thr_lock.c'，只用一個所隊列。這種情況下，寫鎖和讀鎖的優先順序就一樣了，這對一些應用可能有協助。

以下是MySQL鎖的一些建議：

• 只要對同一個表沒有大量的更新和查詢操作混在一起，目前的使用者並不是問題。
• 執行 LOCK TABLES 來提高速度（很多更新操作放在一個鎖之中比沒有鎖的很多更新快多了）。將資料拆分開到多個表中可能也有協助。
• 當MySQL碰到由於鎖表引起的速度問題時，將表類型轉換成 InnoDB 或 BDB 可能有助於提高效能。詳情請看“16 The InnoDB Storage Engine”和“15.4 The BDB (BerkeleyDB) Storage Engine”。