MySQL 避免行鎖升級為表鎖——使用高效的索引

zhoupq發表於2017-04-14
MySql索引

　　眾所周知，MySQL 的 InnoDB 儲存引擎支援事務，預設是行鎖。得益於這些特性，資料庫支援高併發。如果 InnoDB 更新資料使用的不是行鎖，而是表鎖呢？是的，InnoDB 其實很容易就升級為表鎖，屆時併發性將大打折扣了。

　　經過我操作驗證，得出行鎖升級為表鎖的原因之一是： SQL 語句中未使用到索引，或者說使用的索引未被資料庫認可（相當於沒有使用索引）。

　　我相信，MySQL InnoDB 儲存引擎引發表鎖的原因肯定不止一個因素，針對其解決方法也不是隻有一種。

　　【掘金】上另一位作者【Blink-前端】，提出行鎖升級為表鎖與 事務的隔離級別 有關，並給出了事例。當然，我同意這個說法，因為事務的隔離性是靠加鎖來實現的，而加鎖勢必會影響併發。本篇只針對 索引影響併發 作出說明，並特別希望有朋友能提出質疑並給出獨特見解，萬分感謝。

普通索引

　　既然談及索引是影響併發的決定因素之一，那我們就來了解一下索引這位主角。

　　常用的索引有三類：主鍵、唯一索引、普通索引。主鍵 不由分說，自帶最高效的索引屬性；唯一索引 指的是該屬性值重複率為0，一般可作為業務主鍵，例如學號；普通索引 與前者不同的是，屬性值的重複率大於0，不能作為唯一指定條件，例如學生姓名。接下來我要說明是 “普通索引對併發的影響”。

　　為什麼我會想到 “普通索引對併發有影響”？這源自【掘金】微信群丟擲的一個問題：

mysql 5.6 在 update 和 delete 的時候，where 條件如果不存在索引欄位，那麼這個事務是否會導致表鎖？

　　有人回答：

只有主鍵和唯一索引才是行鎖，普通索引是表鎖。

　　我針對 “普通索引是表鎖” 進行了驗證，結果發現普通索引並不一定會引發表鎖，在普通索引中，是否引發表鎖取決於普通索引的高效程度。

　　上文提及的“高效”是相對主鍵和唯一索引而言，也許“高效”並不是一個很好的解釋，明白在一般i情況下，“普通索引”效率低於其他兩者即可。

屬性值重複率高

　　為了突出效果，我將“普通索引”建立在一個“值重複率”高的屬性下。以相對極端的方式，擴大對結果的影響。

　　我會建立一張“分數等級表”，屬性有“id”、“score（分數）”、“level（等級）”，模擬一個半自動的業務——“分數”已被自動匯入，而“等級”需要手工更新。

　　操作步驟如下：

  1. 取消 MySQL 的 事務自動提交
  2. 建表，id自增，並給“score（分數）”建立普通索引
  3. 插入分數值，等級為 null
  4. 開啟兩個事務 session_1、session_2，兩個事務以“score”為條件指定不同值，鎖定資料
  5. session_1 和 session_2 先後更新各自事務鎖定內容的“level”
  6. 觀察資料庫對兩個事務的響應

　　取消 事務自動提交：

mysql> set autocommit = off;
Query OK, 0 rows affected (0.02 sec)

mysql> show variables like "autocommit";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| autocommit               | OFF   |
+--------------------------+-------+
1 rows in set (0.01 sec)複製程式碼

　　建表、建立索引、插入資料：

DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (
`ID`  int(5) NOT NULL AUTO_INCREMENT ,
`SCORE`  int(3) NOT NULL ,
`LEVEL`  int(2) NULL DEFAULT NULL ,
PRIMARY KEY (`ID`)
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

ALTER TABLE `test2` ADD INDEX index_name ( `SCORE` );

INSERT INTO `test1`(`SCORE`) VALUE (100);
……
INSERT INTO `test1`(`SCORE`) VALUE (0);
……複製程式碼

　　"SCORE" 屬性的“值重複率”奇高，達到了 50%，劍走偏鋒：

mysql> select * from test1;
+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  1 |   100 | NULL  |
|  2 |     0 | NULL  |
|  5 |   100 | NULL  |
|  6 |   100 | NULL  |
|  7 |   100 | NULL  |
|  8 |   100 | NULL  |
|  9 |   100 | NULL  |
| 10 |   100 | NULL  |
| 11 |   100 | NULL  |
| 12 |   100 | NULL  |
| 13 |   100 | NULL  |
| 14 |     0 | NULL  |
| 15 |     0 | NULL  |
| 16 |     0 | NULL  |
| 17 |     0 | NULL  |
| 18 |     0 | NULL  |
| 19 |     0 | NULL  |
| 20 |     0 | NULL  |
| 21 |     0 | NULL  |
| 22 |     0 | NULL  |
| 23 |     0 | NULL  |
| 24 |   100 | NULL  |
| 25 |     0 | NULL  |
| 26 |   100 | NULL  |
| 27 |     0 | NULL  |
+----+-------+-------+
25 rows in set複製程式碼

　　開啟兩個事務（一個視窗對應一個事務），並選定資料：

-- SESSION_1，選定 SCORE = 100 的資料
mysql> BEGIN;
SELECT t.* FROM `test1` t WHERE t.`SCORE` = 100 FOR UPDATE;
Query OK, 0 rows affected

+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  1 |   100 | NULL  |
|  5 |   100 | NULL  |
|  6 |   100 | NULL  |
|  7 |   100 | NULL  |
|  8 |   100 | NULL  |
|  9 |   100 | NULL  |
| 10 |   100 | NULL  |
| 11 |   100 | NULL  |
| 12 |   100 | NULL  |
| 13 |   100 | NULL  |
| 24 |   100 | NULL  |
| 26 |   100 | NULL  |
+----+-------+-------+
12 rows in set複製程式碼

　　再開啟一個視窗：

-- SESSION_2，選定 SCORE = 0 的資料
mysql> BEGIN;
SELECT t.* FROM `test1` t WHERE t.`SCORE` = 0 FOR UPDATE;
Query OK, 0 rows affected

+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  2 |     0 | NULL  |
| 14 |     0 | NULL  |
| 15 |     0 | NULL  |
| 16 |     0 | NULL  |
| 17 |     0 | NULL  |
| 18 |     0 | NULL  |
| 19 |     0 | NULL  |
| 20 |     0 | NULL  |
| 21 |     0 | NULL  |
| 22 |     0 | NULL  |
| 23 |     0 | NULL  |
| 25 |     0 | NULL  |
| 27 |     0 | NULL  |
+----+-------+-------+
13 rows in set複製程式碼

　　session_1 視窗，更新“LEVEL”失敗：

mysql> UPDATE `test1` SET `LEVEL` = 1 WHERE `SCORE` = 100;
1205 - Lock wait timeout exceeded; try restarting transaction複製程式碼

　　在之前的操作中，session_1 選擇了 `SCORE` = 100 的資料，session_2 選擇了 `SCORE` = 0 的資料，看似兩個事務井水不犯河水，但是在 session_1 事務中更新自己鎖定的資料失敗，只能說明在此時引發了表鎖。彆著急，剛剛走向了一個極端——索引屬性值重複性奇高，接下來走向另一個極端。 　　

屬性值重複率低

　　還是同一張表，將資料刪除只剩下兩條，“SCORE” 的 “值重複率” 為 0：

mysql> delete from test1 where id > 2;
Query OK, 23 rows affected

mysql> select * from test1;
+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  1 |   100 | NULL  |
|  2 |     0 | NULL  |
+----+-------+-------+
2 rows in set複製程式碼

　　關閉兩個事務操作視窗，重新開啟 session_1 和 session_2，並選擇各自需要的資料：

-- SESSION_1，選定 SCORE = 100 的資料
mysql> BEGIN;
SELECT t.* FROM `test1` t WHERE t.`SCORE` = 100 FOR UPDATE;
Query OK, 0 rows affected

+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  1 |   100 | NULL  |
+----+-------+-------+
1 row in set

-- -----------------新視窗----------------- --

-- SESSION_2，選定 SCORE = 0 的資料
mysql> BEGIN;
SELECT t.* FROM `test1` t WHERE t.`SCORE` = 0 FOR UPDATE;
Query OK, 0 rows affected

+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  2 |     0 | NULL  |
+----+-------+-------+
1 row in set複製程式碼

　　session_1 更新資料成功：

mysql> UPDATE `test1` SET `LEVEL` = 1 WHERE `SCORE` = 100;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0複製程式碼

　　相同的表結構，相同的操作，兩個不同的結果讓人出乎意料。第一個結果讓人覺得“普通索引”引發表鎖，第二個結果推翻了前者，兩個操作中，唯一不同的是索引屬性的“值重複率”。根據 單一變數 證明法，可以得出結論：當“值重複率”低時，甚至接近主鍵或者唯一索引的效果，“普通索引”依然是行鎖；當“值重複率”高時，MySQL 不會把這個“普通索引”當做索引，即造成了一個沒有索引的 SQL，此時引發表鎖。

小結

　　索引不是越多越好，索引存在一個和這個表相關的檔案裡，佔用硬碟空間，寧缺勿濫，每個表都有主鍵（id），操作能使用主鍵儘量使用主鍵。

　　同 JVM 自動優化 java 程式碼一樣，MySQL 也具有自動優化 SQL 的功能。低效的索引將被忽略，這也就倒逼開發者使用正確且高效的索引。

　　轉載請註明出處：zhoupq.com/MySQL-%E9%8…

相關文章