文章目錄

• 生猛干貨
• 官方文檔
• 利用索引優化鎖
• • 為什么索引能優化鎖
  • 演示
  • • 無索引的情況 （獲取不同的數據 發生了阻塞）
    • 有索引的情況 （獲取不同的數據 未阻塞）
• 搞定MySQL

生猛干貨

帶你搞定MySQL實戰，輕松對應海量業務處理及高并發需求，從容應對大場面試

---

官方文檔

https://dev.mysql.com/doc/

如果英文不好的話，可以參考 searchdoc 翻譯的中文版本

http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/index.com.coder114.cn.html

---

利用索引優化鎖

為什么索引能優化鎖

Innodb采用的行級鎖，只有在修改行時才會對需要修改的行加鎖。 但是這種情況只有在Innodb層過濾掉不需要的行是才有效。

如果存儲引擎層不能過濾掉不需要的行，則需要在內存中鎖定所有的行，在內存中進行過濾。

所以利用索引可以過濾掉不需要的數據， 使用索引的話，僅需要鎖定被索引檢索出來的數據，而不是鎖定全部數據，從而達到優化鎖的目的。

• 索引可以減少鎖定的行數
• 索引可以加快處理速度，同時也加快了鎖的釋放

---

演示

舉個例子 （演示鎖， 肯定需要兩個會話了）

無索引的情況 （獲取不同的數據 發生了阻塞）

session 1 :

mysql> show create table actor \G *************************** 1. row ***************************Table: actor Create Table: CREATE TABLE `actor` (`actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,`first_name` varchar(45) NOT NULL,`last_name` varchar(45) NOT NULL,`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`actor_id`),KEY `idx_actor_last_name` (`last_name`) ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec)mysql> drop index idx_actor_last_name on actor ; #Step1 先拿掉 last_name的索引 Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0mysql> explain select * from actor where last_name = 'WOOD' \G; #Step2 執行下執行計劃 *************************** 1. row ***************************id: 1select_type: SIMPLEtable: actorpartitions: NULLtype: ALL --------------------> 全表掃描 possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 200filtered: 10.00Extra: Using where --------------------> 全表掃描后，通過where來過濾 1 row in set, 1 warning (0.00 sec)ERROR: No query specifiedmysql> mysql> begin ; # Step3 開啟事務 Query OK, 0 rows affected (0.00 sec)mysql> select * from actor where last_name = 'WOOD' for update ; -----> Step4 通過for update ,加入一個排它鎖 +----------+------------+-----------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+-----------+---------------------+ | 13 | UMA | WOOD | 2006-02-15 04:34:33 | | 156 | FAY | WOOD | 2006-02-15 04:34:33 | +----------+------------+-----------+---------------------+ 2 rows in set (0.00 sec)mysql> mysql>

新開一個會話 session 2 ：

行級鎖，咋把willis 的查詢也阻塞了呢？ 就是因為沒有索引 ，走了全表掃描，然后通過where 過濾。 把全表的數據都鎖定了， 你查willis 自然也被阻塞了。 如果有索引的話，其實就僅僅會鎖定 WOOD 對應的數據行。其他數據的操作都是可以的，我們接下來通過實驗來驗證下

---

有索引的情況 （獲取不同的數據 未阻塞）

我們把會話一和會話二 ，rollback 掉。 然后把last_name的索引加上去，然后重新做下試驗 。

會話一：

mysql> mysql> rollback; Query OK, 0 rows affected (0.00 sec)mysql> create index idx_actor_last_name on actor(last_name); Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0mysql> explain select * from actor where last_name = 'WOOD' \G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: actorpartitions: NULLtype: ref possible_keys: idx_actor_last_namekey: idx_actor_last_namekey_len: 182ref: constrows: 2filtered: 100.00Extra: NULL 1 row in set, 1 warning (0.00 sec)mysql> begin ; Query OK, 0 rows affected (0.00 sec)mysql> select * from actor where last_name = 'WOOD' for update ; +----------+------------+-----------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+-----------+---------------------+ | 13 | UMA | WOOD | 2006-02-15 04:34:33 | | 156 | FAY | WOOD | 2006-02-15 04:34:33 | +----------+------------+-----------+---------------------+ 2 rows in set (0.00 sec)mysql>

會話二：

mysql> rollback; Query OK, 0 rows affected (0.00 sec)mysql> begin ; Query OK, 0 rows affected (0.00 sec)mysql> select * from actor where last_name = 'willis' for update; +----------+------------+-----------+---------------------+ | actor_id | first_name | last_name | last_update | +----------+------------+-----------+---------------------+ | 83 | BEN | WILLIS | 2006-02-15 04:34:33 | | 96 | GENE | WILLIS | 2006-02-15 04:34:33 | | 164 | HUMPHREY | WILLIS | 2006-02-15 04:34:33 | +----------+------------+-----------+---------------------+ 3 rows in set (0.00 sec)mysql>

可以看到，建立完索引后，會話二查詢 willis 數據， 可以獲取到結果， 因為會話一的事務中通過索引僅在內中鎖定了WOOD對應的兩條數據，其他數據是沒有被鎖定的。 ------》可以看到索引對鎖的優化后， 增加了并發，提高DB的性能

當然了，你要是會話二和會話一查詢的都是統一批數據，比如都是WOOD，因為會話一未提交， 會話二肯定會被阻塞的。 這里簡單提一下。

---

搞定MySQL

總結

以上是生活随笔為你收集整理的MySQL-索引优化篇(3)_利用索引优化锁的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。