眾所周知，MySQL 的 InnoDB 存儲引擎支持事務督弓，默認是行鎖营曼。得益于這些特性，數(shù)據(jù)庫支持高并發(fā)愚隧。如果 InnoDB 更新數(shù)據(jù)使用的不是行鎖蒂阱，而是表鎖呢？是的狂塘，InnoDB 其實很容易就升級為表鎖录煤，屆時并發(fā)性將大打折扣了。

經(jīng)過我操作驗證荞胡，得出行鎖升級為表鎖的原因之一是： SQL 語句中未使用到索引妈踊，或者說使用的索引未被數(shù)據(jù)庫認可（相當于沒有使用索引）。

常用的索引有三類：主鍵泪漂、唯一索引廊营、普通索引歪泳。主鍵 不由分說，自帶最高效的索引屬性露筒；唯一索引 指的是該屬性值重復率為0呐伞，一般可作為業(yè)務主鍵，例如學號慎式；普通索引 與前者不同的是伶氢，屬性值的重復率大于0，不能作為唯一指定條件瞬捕，例如學生姓名鞍历。接下來我要說明是 “普通索引對并發(fā)的影響”。

為什么我會想到 “普通索引對并發(fā)有影響”肪虎？這源于微信群拋出的一個問題：

mysql 5.6 在 update 和 delete 的時候劣砍，where 條件如果不存在索引字段，那么這個事務是否會導致表鎖扇救？

有人回答：

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

我針對 “普通索引是表鎖” 進行了驗證迅腔，結(jié)果發(fā)現(xiàn)普通索引并不一定會引發(fā)表鎖装畅，在普通索引中，是否引發(fā)表鎖取決于普通索引的高效程度沧烈。

屬性值重復率高

為了突出效果掠兄，我將“普通索引”建立在一個“值重復率”高的屬性下。以相對極端的方式锌雀，擴大對結(jié)果的影響蚂夕。

我會創(chuàng)建一張“分數(shù)等級表”，屬性有“id”腋逆、“score（分數(shù)）”婿牍、“l(fā)evel（等級）”，模擬一個半自動的業(yè)務——“分數(shù)”已被自動導入惩歉，而“等級”需要手工更新等脂。

操作步驟如下：

1. 取消 MySQL 的 事務自動提交
2. 建表，id自增撑蚌，并給“score（分數(shù)）”創(chuàng)建普通索引
3. 插入分數(shù)值上遥，等級為 null
4. 開啟兩個事務 session_1、session_2争涌，兩個事務以“score”為條件指定不同值露该，鎖定數(shù)據(jù)
5. session_1 和 session_2 先后更新各自事務鎖定內(nèi)容的“l(fā)evel”
6. 觀察數(shù)據(jù)庫對兩個事務的響應

取消 事務自動提交：

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)

建表、創(chuàng)建索引第煮、插入數(shù)據(jù)：

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

開啟兩個事務（一個窗口對應一個事務），并選定數(shù)據(jù)：

-- SESSION_1撵摆，選定 SCORE = 100 的數(shù)據(jù)
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 的數(shù)據(jù)
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 的數(shù)據(jù)，session_2 選擇了 SCORE = 0 的數(shù)據(jù)鲫剿，看似兩個事務井水不犯河水鳄逾，但是在 session_1 事務中更新自己鎖定的數(shù)據(jù)失敗，只能說明在此時引發(fā)了表鎖灵莲。別著急雕凹，剛剛走向了一個極端——索引屬性值重復性奇高，接下來走向另一個極端政冻。

屬性值重復率低

還是同一張表枚抵，將數(shù)據(jù)刪除只剩下兩條，“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

關(guān)閉兩個事務操作窗口明场，重新開啟 session_1 和 session_2汽摹，并選擇各自需要的數(shù)據(jù)：

-- SESSION_1，選定 SCORE = 100 的數(shù)據(jù)
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 的數(shù)據(jù)
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

相同的表結(jié)構(gòu)逼泣，相同的操作，兩個不同的結(jié)果讓人出乎意料舟舒。第一個結(jié)果讓人覺得“普通索引”引發(fā)表鎖拉庶，第二個結(jié)果推翻了前者，兩個操作中魏蔗，唯一不同的是索引屬性的“值重復率”。根據(jù) 單一變量 證明法痹筛，可以得出結(jié)論：當“值重復率”低時莺治，甚至接近主鍵或者唯一索引的效果，“普通索引”依然是行鎖帚稠；當“值重復率”高時谣旁，MySQL 不會把這個“普通索引”當做索引，即造成了一個沒有索引的 SQL滋早，此時引發(fā)表鎖榄审。

小結(jié)

索引不是越多越好，索引存在一個和這個表相關(guān)的文件里杆麸，占用硬盤空間搁进，寧缺勿濫浪感，每個表都有主鍵（id），操作能使用主鍵盡量使用主鍵饼问。

同 JVM 自動優(yōu)化 java 代碼一樣影兽，MySQL 也具有自動優(yōu)化 SQL 的功能。低效的索引將被忽略莱革，這也就倒逼開發(fā)者使用正確且高效的索引峻堰。

MySQL 表鎖和行鎖機制

MySQL的存儲引擎是從MyISAM到InnoDB，鎖從表鎖到行鎖盅视。后者的出現(xiàn)從某種程度上是彌補前者的不足捐名。比如：MyISAM不支持事務，InnoDB支持事務闹击。表鎖雖然開銷小镶蹋，鎖表快，但高并發(fā)下性能低拇砰。行鎖雖然開銷大梅忌，鎖表慢，但高并發(fā)下相比之下性能更高除破。事務和行鎖都是在確保數(shù)據(jù)準確的基礎(chǔ)上提高并發(fā)的處理能力牧氮。

案例分析

目前，MySQL常用的存儲引擎是InnoDB瑰枫，相對于MyISAM而言踱葛。InnoDB更適合高并發(fā)場景，同時也支持事務處理光坝。我們通過下面這個案例(坑)尸诽，來了解行鎖和表鎖。

業(yè)務：因為訂單重復導入盯另，需要用腳本將訂單狀態(tài)為"待客服確認"且平臺是"xxx"的數(shù)據(jù)批量修改為"已關(guān)閉"性含。
說明：避免直接修改訂單表造成數(shù)據(jù)異常。這里用innodb_lock 表演示InnoDB的行鎖鸳惯。表中有三個字段：id商蕴，k(key值)，v(value值)芝发。表為：

CREATE TABLE `itdragon_order_list` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵id绪商，默認自增長',
  `transaction_id` varchar(150) DEFAULT NULL COMMENT '交易號',
  `gross` double DEFAULT NULL COMMENT '毛收入(RMB)',
  `net` double DEFAULT NULL COMMENT '凈收入(RMB)',
  `stock_id` int(11) DEFAULT NULL COMMENT '發(fā)貨倉庫',
  `order_status` int(11) DEFAULT NULL COMMENT '訂單狀態(tài)',
  `descript` varchar(255) DEFAULT NULL COMMENT '客服備注',
  `finance_descript` varchar(255) DEFAULT NULL COMMENT '財務備注',
  `create_type` varchar(100) DEFAULT NULL COMMENT '創(chuàng)建類型',
  `order_level` int(11) DEFAULT NULL COMMENT '訂單級別',
  `input_user` varchar(20) DEFAULT NULL COMMENT '錄入人',
  `input_date` varchar(20) DEFAULT NULL COMMENT '錄入時間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=utf8;

步驟：

1. 第一步：連接數(shù)據(jù)庫，這里為了方便區(qū)分命名為Transaction-A辅鲸，設(shè)置autocommit為零格郁，表示需手動提交事務。
2. 第二步：Transaction-A，執(zhí)行update修改id為1的命令例书。
3. 第三步：新增一個連接锣尉，命名為Transaction-B，能正常修改id為2的數(shù)據(jù)雾叭。再執(zhí)行修改id為1的數(shù)據(jù)命令時悟耘，卻發(fā)現(xiàn)該命令一直處理阻塞等待中。
4. 第四步：Transaction-A织狐，執(zhí)行commit命令暂幼。Transaction-B，修改id為1的命令自動執(zhí)行移迫，等待37.51秒旺嬉。

# Transaction-A
mysql> set autocommit = 0;
mysql> update innodb_lock set v='1001' where id=1;
mysql> commit;

# Transaction-B
mysql> update innodb_lock set v='2001' where id=2;
Query OK, 1 row affected (0.37 sec)
mysql> update innodb_lock set v='1002' where id=1;
Query OK, 1 row affected (37.51 sec)

總結(jié)：多個事務操作同一行數(shù)據(jù)時，后來的事務處于阻塞等待狀態(tài)厨埋。這樣可以避免了臟讀等數(shù)據(jù)一致性的問題邪媳。后來的事務可以操作其他行數(shù)據(jù)，解決了表鎖高并發(fā)性能低的問題荡陷。

有了上面的模擬操作雨效，結(jié)果和理論又驚奇的一致，似乎可以放心大膽的實戰(zhàn)废赞。徽龟。。唉地。据悔。。但現(xiàn)實真的很殘酷耘沼。

現(xiàn)實：當執(zhí)行批量修改數(shù)據(jù)腳本的時候极颓，行鎖升級為表鎖。其他對訂單的操作都處于等待中群嗤，菠隆，，
原因：InnoDB只有在通過索引條件檢索數(shù)據(jù)時使用行級鎖狂秘，否則使用表鎖骇径！而模擬操作正是通過id去作為檢索條件，而id又是MySQL自動創(chuàng)建的唯一索引赃绊，所以才忽略了行鎖變表鎖的情況既峡。

步驟：
第一步：還原問題羡榴，Transaction-A碧查，通過k=1更新v。Transaction-B，通過k=2更新v忠售，命令處于阻塞等待狀態(tài)传惠。
第二步：處理問題，給需要作為查詢條件的字段添加索引稻扬。用完后可以刪掉卦方。

總結(jié)：InnoDB的行鎖是針對索引加的鎖，不是針對記錄加的鎖泰佳。并且該索引不能失效盼砍，否則都會從行鎖升級為表鎖。索引失效的原因后面文章會介紹

Transaction-A
mysql> update innodb_lock set v='1002' where k=1;
mysql> commit;
mysql> create index idx_k on innodb_lock(k);

Transaction-B
mysql> update innodb_lock set v='2002' where k=2;
Query OK, 1 row affected (19.82 sec)

從上面的案例看出逝她，行鎖變表鎖似乎是一個坑浇坐，可MySQL沒有這么無聊給你挖坑。這是因為MySQL有自己的執(zhí)行計劃黔宛。

當你需要更新一張較大表的大部分甚至全表的數(shù)據(jù)時近刘。而你又傻乎乎地用索引作為檢索條件。一不小心開啟了行鎖(沒毛病巴位巍觉渴！保證數(shù)據(jù)的一致性！)徽惋“噶埽可MySQL卻認為大量對一張表使用行鎖，會導致事務執(zhí)行效率低寂曹，從而可能造成其他事務長時間鎖等待和更多的鎖沖突問題哎迄，性能嚴重下降。所以MySQL會將行鎖升級為表鎖隆圆，即實際上并沒有使用索引漱挚。
我們仔細想想也能理解，既然整張表的大部分數(shù)據(jù)都要更新數(shù)據(jù)渺氧，一行一行地加鎖效率則更低旨涝。其實我們可以通過explain命令查看MySQL的執(zhí)行計劃，你會發(fā)現(xiàn)key為null侣背。表明MySQL實際上并沒有使用索引白华，行鎖升級為表鎖也和上面的結(jié)論一致。

行鎖

• 行鎖的劣勢：開銷大贩耐；加鎖慢弧腥；會出現(xiàn)死鎖
• 行鎖的優(yōu)勢：鎖的粒度小，發(fā)生鎖沖突的概率低潮太；處理并發(fā)的能力強
• 加鎖的方式：自動加鎖管搪。對于UPDATE虾攻、DELETE和INSERT語句，InnoDB會自動給涉及數(shù)據(jù)集加排他鎖更鲁；對于普通SELECT語句霎箍，InnoDB不會加任何鎖；當然我們也可以顯示的加鎖：
• 共享鎖：select * from tableName where ... + lock in share more
• 排他鎖：select * from tableName where ... + for update

InnoDB和MyISAM的最大不同點有兩個：一澡为，InnoDB支持事務(transaction)漂坏；二，默認采用行級鎖媒至。加鎖可以保證事務的一致性

InnoDB默認采用行鎖顶别，在未使用索引字段查詢時升級為表鎖。MySQL這樣設(shè)計并不是給你挖坑拒啰。它有自己的設(shè)計目的筋夏。
即便你在條件中使用了索引字段，MySQL會根據(jù)自身的執(zhí)行計劃图呢，考慮是否使用索引(所以explain命令中會有possible_key 和 key)条篷。如果MySQL認為全表掃描效率更高，它就不會使用索引蛤织，這種情況下InnoDB將使用表鎖赴叹，而不是行鎖。因此指蚜，在分析鎖沖突時乞巧，別忘了檢查SQL的執(zhí)行計劃，以確認是否真正使用了索引摊鸡。

第一種情況：全表更新绽媒。事務需要更新大部分或全部數(shù)據(jù)，且表又比較大免猾。若使用行鎖是辕，會導致事務執(zhí)行效率低，從而可能造成其他事務長時間鎖等待和更多的鎖沖突猎提。

第二種情況：多表級聯(lián)获三。事務涉及多個表，比較復雜的關(guān)聯(lián)查詢锨苏，很可能引起死鎖疙教，造成大量事務回滾。這種情況若能一次性鎖定事務涉及的表伞租，從而可以避免死鎖贞谓、減少數(shù)據(jù)庫因事務回滾帶來的開銷。