（9）MySQL進階篇SQL優化（InnoDB鎖-記錄鎖）

2021 年 5 月 12 日
筆記
MySQL進階篇

1.概述

InnoDB行鎖是通過給索引上的索引項加鎖來實現的，這一點MySQL與Oracle不同，後者是通過在數據塊中對相應數據行加鎖來實現的。InnoDB這種行鎖實現特點意味著：只有通過索引條件檢索數據，InnoDB才使用行級鎖，否則InnoDB將使用表鎖！在實際應用程式中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導致大量的鎖衝突，從而影響並發性能。

2. InnoDB行鎖實現方式

2.1InnoDB存儲引擎的表在不使用索引時使用表鎖例子

創建一個臨時表：

MySQL [(none)]> CREATE TABLE goods.tab_no_index (ID INT,Name VARCHAR(50));
Query OK, 0 rows affected (0.02 sec)

插入三條測試數據：

MySQL [(none)]> INSERT INTO goods.tab_no_index (ID,`Name`) VALUES (1,'1'),(2,'2'),(3,'3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

session_1	session_2
（1）先設置事務T1提交類型為事務非自動提交。	（1）先設置事務T2提交類型為事務非自動提交。
MySQL [(none)]> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec)	MySQL [(none)]> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec)
（2）在當前事務T1中查詢tab_no_index表的數據行ID=1數據。	（2）在當前事務T2中查詢tab_no_index表的數據行ID=2數據。
MySQL [(none)]> SELECT * FROM goods.tab_no_index WHERE ID=1; +------+------+ \| ID \| Name \| +------+------+ \| 1 \| 1 \| +------+------+ 1 row in set (0.00 sec)	MySQL [(none)]> SELECT * FROM goods.tab_no_index WHERE ID=2; +------+------+ \| ID \| Name \| +------+------+ \| 2 \| 2 \| +------+------+ 1 row in set (0.00 sec)
（3）在當前事務T1中為tab_no_index表的數據行ID=1加上排他鎖。
MySQL [(none)]> SELECT * FROM goods.tab_no_index WHERE ID=1 FOR UPDATE; +------+------+ \| ID \| Name \| +------+------+ \| 1 \| 1 \| +------+------+ 1 row in set (0.00 sec)
	（3）在當前事務T2中為tab_no_index表的數據行ID=2加上排他鎖，會發生阻塞超時。
	MySQL [(none)]> SELECT * FROM goods.tab_no_index WHERE ID=2 FOR UPDATE; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

從上述表格的例子來看，session_1隻給ID=1數據行加了排他鎖，但session_2在請求ID=2的數據行排他鎖時，卻出現了鎖等待！

2.2InnoDB存儲引擎的表在使用索引時使用行鎖例子

創建一個臨時表：

MySQL [(none)]> CREATE TABLE goods.tab_with_index (ID INT,Name VARCHAR(50));
Query OK, 0 rows affected (0.02 sec)

建立臨時表ID列索引：

MySQL [(none)]> ALTER TABLE goods.tab_with_index ADD INDEX Index_ID(ID);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

插入三條測試數據：

MySQL [(none)]> INSERT INTO goods.tab_with_index (ID,`Name`) VALUES (1,'1'),(2,'2'),(3,'3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

session_1	session_2
（1）先設置事務T1提交類型為事務非自動提交。	（1）先設置事務T2提交類型為事務非自動提交。
MySQL [(none)]> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec)	MySQL [(none)]> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec)
（2）在當前事務T1中查詢tab_with_index表的數據行ID=1數據。	（2）在當前事務T2中查詢tab_with_index表的數據行ID=2數據。
MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE ID=1; +------+------+ \| ID \| Name \| +------+------+ \| 1 \| 1 \| +------+------+ 1 row in set (0.00 sec)	MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE ID=2; +------+------+ \| ID \| Name \| +------+------+ \| 2 \| 2 \| +------+------+ 1 row in set (0.00 sec)
（3）在當前事務T1中為tab_with_index表的數據行ID=1加上排他鎖。
MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE ID=1 FOR UPDATE; +------+------+ \| ID \| Name \| +------+------+ \| 1 \| 1 \| +------+------+ 1 row in set (0.00 sec)
	（3）在當前事務T2中為tab_with_index表的數據行ID=2加上排他鎖，卻並沒有發生阻塞超時。
	MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE ID=2 FOR UPDATE; +------+------+ \| ID \| Name \| +------+------+ \| 2 \| 2 \| +------+------+ 1 row in set (0.00 sec)

該示例同樣跟2小節示例一樣，只是ID列加了索引，而session_2在請求ID=2的數據行卻沒有阻塞！

2.3小結

通過以上兩個示例可以了解到：
●在ID列沒有建立索引的情況下，InnoDB沒有使用到行鎖，而是使用到表鎖。
●在ID列建立索引的情況下，InnoDB使用到行鎖，而是沒有使用到表鎖。
也就是說，InnoDB存儲引擎的表列如果在沒有加索引情況下查詢，使用到是表鎖而不是行鎖，會產生阻塞情況，這在並發情況下是災難的。

4.記錄鎖

由於MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同記錄行，但是如果是使用相同的索引鍵，是會出現鎖衝突的。下面我們通過兩個示例來了解下。

4.1InnoDB存儲引擎使用相同索引鍵的阻塞例子

這個示例還是沿用tab_with_index表做演示，ID是非聚集索引列，Name列沒有索引，有以下數據：

MySQL [(none)]> SELECT * FROM goods.tab_with_index;
+------+------+
| ID   | Name |
+------+------+
|    1 | 1    |
|    1 | 2    |
|    3 | 3    |
+------+------+
3 rows in set (0.00 sec)

session_1	session_2
（1）先設置事務T1提交類型為事務非自動提交。	（1）先設置事務T2提交類型為事務非自動提交。
MySQL [(none)]> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec)	MySQL [(none)]> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec)
（2）在當前事務T1中為tab_with_index表的數據行ID=1 AND `Name`=’1′ 加上排他鎖。
MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE ID=1 AND `Name`='1' FOR UPDATE; +------+------+ \| ID \| Name \| +------+------+ \| 1 \| 1 \| +------+------+ 1 row in set (0.00 sec)
	（2）在當前事務T2中為tab_with_index表的數據行ID=1 AND `Name`=’2‘ 加上排他鎖，發生阻塞超時。
	MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE ID=1 AND `Name`='2' FOR UPDATE; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
	（3）在當前事務T2中為tab_with_index表插入以下一行數據，並沒有發生阻塞超時。
	MySQL [(none)]> INSERT INTO goods.tab_with_index (ID,`Name`) VALUES (4,'4'); Query OK, 1 row affected (0.00 sec)

從上面示例可以看到，當session_1鎖定事務中ID=1記錄行時，會阻止session_2事務獲取該記錄行，而當插入一條ID=4數據時，卻沒有發生阻塞，成功插入！也就是說當索引數據加上記錄鎖時，會阻止其他事務對該表該行數據（例如ID=1記錄行）進行插入，更新和刪除操作。

4.2InnoDB存儲引擎的表使用不同索引的阻塞例子

這個示例還是沿用tab_with_index表做演示，ID為主鍵索引列，Name為非聚集索引列，有以下數據：

MySQL [(none)]> SELECT * FROM goods.tab_with_index;
+----+------+
| ID | Name |
+----+------+
|  1 | 1    |
|  2 | 2    |
+----+------+
2 rows in set (0.00 sec)

session_1	session_2
（1）先設置事務T1提交類型為事務非自動提交。	（1）先設置事務T2提交類型為事務非自動提交。
MySQL [(none)]> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec)	MySQL [(none)]> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec)
（2）在當前事務T1中為tab_with_index表的數據行ID=1 加上排他鎖。
MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE ID=1 FOR UPDATE; +----+------+ \| ID \| Name \| +----+------+ \| 1 \| 1 \| +----+------+ 1 row in set (0.00 sec)
	（2）由於tab_with_index表ID=1的記錄行在session_1事務中被鎖定，當在session_2事務查詢Name=』2』記錄行時，因為該記錄行並不屬於ID=1範圍記錄行之中，所以可以獲得tab_with_index表的鎖。
	MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE `Name`='2' FOR UPDATE; +----+------+ \| ID \| Name \| +----+------+ \| 2 \| 2 \| +----+------+ 1 row in set (0.00 sec)
	（3）同理，由於訪問的Name=』1』記錄已經被 session_1事務中被鎖定，所以只能等待獲得tab_with_index表的鎖。
	MySQL [(none)]> SELECT * FROM goods.tab_with_index WHERE `Name`='1' FOR UPDATE; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會使用行鎖來對數據加鎖。

5.總結

其實在現實生產環境，即便在條件中使用了索引欄位，但是決定是否使用索引來檢索數據記錄行是由MySQL執行計劃來決定的，所以如果MySQL認為全表掃描效率更高，那麼它就會優先執行全表掃描操作。比如一些很小的表，哪怕您在條件中使用了索引列，它也不會使用索引，這種情況下InnoDB將會使用表鎖，而不是使用行鎖。因此，在分析鎖衝突時，別忘了檢查SQL的執行計劃，以確認是否真正使用了索引。還有一種情況在之前索引章節也有說過，如果檢索值的數據類型與索引欄位值不同，雖然MySQL能夠進行數據類型轉換，但是卻不會使用索引，從而導致InnoDB使用表鎖。例如tab_with_index表的name欄位有索引，但是name欄位是varchar類型的，如果where條件中值是int等值類型，那麼就不是和varchar類型進行比較，而會對name進行類型轉換，從而要不全表或遍歷索引樹掃描獲取記錄行，如下面語句：

-- 全表或遍歷索引樹掃描
EXPLAIN SELECT * FROM goods.tab_with_index WHERE `Name`=1;
-- 走索引掃描
EXPLAIN SELECT * FROM goods.tab_with_index WHERE `Name`='1';

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

Tags: MySQL進階篇