一、鎖概述

鎖是計算機(jī)協(xié)調(diào)多個進(jìn)程或線程并發(fā)訪問某一資源的機(jī)制（避免爭搶）。

在數(shù)據(jù)庫中，除傳統(tǒng)的計算資源（如 CPU、RAM、I/O 等）的爭用以外，數(shù)據(jù)也是一種供許多用戶共享的資源。如何保證數(shù)據(jù)并發(fā)訪問的一致性、有效性是所有數(shù)據(jù)庫必須解決的一個問題，鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個重要因素。從這個角度來說，鎖對數(shù)據(jù)庫而言顯得尤其重要，也更加復(fù)雜。

二、鎖分類

從對數(shù)據(jù)操作的粒度分：

• 1、表鎖：操作時，會鎖定整個表。
• 2、行鎖：操作時，會鎖定當(dāng)前操作行。

從對數(shù)據(jù)操作的類型分：

• 1、讀鎖（共享鎖）：針對同一份數(shù)據(jù)，多個讀操作可以同時進(jìn)行而不會互相影響。
• 2、寫鎖（排它鎖）：當(dāng)前操作沒有完成之前，它會阻斷其他寫鎖和讀鎖。

三、Mysql 鎖

相對其他數(shù)據(jù)庫而言，MySQL的鎖機(jī)制比較簡單，其最顯著的特點是不同的存儲引擎支持不同的鎖機(jī)制。下表中羅列出了各存儲引擎對鎖的支持情況：

MySQL這3種鎖的特性可大致歸納如下 ：

從上述特點可見，很難籠統(tǒng)地說哪種鎖更好，只能就具體應(yīng)用的特點來說哪種鎖更合適！僅從鎖的角度來說：表級鎖更適合于以查詢?yōu)橹?，只有少量按索引條件更新數(shù)據(jù)的應(yīng)用，如Web 應(yīng)用；而行級鎖則更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)，同時又有并查詢的應(yīng)用，如一些在線事務(wù)處理（OLTP）系統(tǒng)。

四、MyISAM 表鎖

MyISAM 存儲引擎只支持表鎖，這也是MySQL開始幾個版本中唯一支持的鎖類型。

4.1、如何加表鎖

MyISAM 在執(zhí)行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執(zhí)行更新操作（UPDATE、DELETE、INSERT 等）前，會自動給涉及的表加寫鎖，這個過程并不需要用戶干預(yù)，因此，用戶一般不需要直接用 LOCK TABLE 命令給 MyISAM 表顯式加鎖。

顯示加表鎖語法：

加讀鎖 ： lock table table_name read;

加寫鎖 ： lock table table_name write；

4.2、讀鎖案例

準(zhǔn)備環(huán)境

create database demo_03 default charset=utf8mb4;

use demo_03;

CREATE TABLE `tb_book` (
    `id` INT(11) auto_increment,
    `name` VARCHAR(50) DEFAULT NULL,
    `publish_time` DATE DEFAULT NULL,
    `status` CHAR(1) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'java編程思想','2088-08-01','1');
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'solr編程思想','2088-08-08','0');

CREATE TABLE `tb_user` (
    `id` INT(11) auto_increment,
    `name` VARCHAR(50) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

INSERT INTO tb_user (id, name) VALUES(NULL,'令狐沖');
INSERT INTO tb_user (id, name) VALUES(NULL,'田伯光');

客戶端 一：

• 1、獲得tb_book 表的讀鎖

lock table tb_book read;

• 2、執(zhí)行查詢操作

select * from tb_book;

可以正常執(zhí)行 ， 查詢出數(shù)據(jù)。

客戶端 二 ：

• 3、執(zhí)行查詢操作

select * from tb_book;

客戶端 一 ：

• 4、查詢未鎖定的表

select name from tb_seller;

客戶端 二 ：

• 5、查詢未鎖定的表

select name from tb_seller;

可以正常查詢出未鎖定的表；

客戶端 一 ：

• 6、執(zhí)行插入操作

insert into tb_book values(null,'Mysql高級','2088-01-01','1');

執(zhí)行插入， 直接報錯 ， 由于當(dāng)前tb_book 獲得的是 讀鎖， 不能執(zhí)行更新操作。

客戶端 二 ：

• 7、執(zhí)行插入操作

insert into tb_book values(null,'Mysql高級','2088-01-01','1');

當(dāng)在客戶端一中釋放鎖指令 unlock tables 后 ， 客戶端二中的 inesrt 語句 ， 立即執(zhí)行 ；

4.3、寫鎖案例

客戶端 一 :

• 1、獲得tb_book 表的寫鎖

lock table tb_book write ;

• 2、執(zhí)行查詢操作

select * from tb_book ;

查詢操作執(zhí)行成功；

• 3、執(zhí)行更新操作

update tb_book set name = 'java編程思想（第二版）' where id = 1;

更新操作執(zhí)行成功 ；

客戶端 二 :

• 4、執(zhí)行查詢操作

select * from tb_book ;

當(dāng)在客戶端一中釋放鎖指令 unlock tables 后，客戶端二中的 select 語句， 立即執(zhí)行 ；

4.4、結(jié)論

鎖模式的相互兼容性如表中所示：

由上表可見：

• 1、對MyISAM 表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求；

• 2） 對MyISAM 表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作；

簡而言之，就是讀鎖會阻塞寫，但是不會阻塞讀。而寫鎖，則既會阻塞讀，又會阻塞寫。

此外，MyISAM 的讀寫鎖調(diào)度是寫優(yōu)先，這也是MyISAM不適合做寫為主的表的存儲引擎的原因。因為寫鎖后，其他線程不能做任何操作，大量的更新會使查詢很難得到鎖，從而造成永遠(yuǎn)阻塞。

4.5、查看鎖的爭用情況

show open tables；

In_user：表當(dāng)前被查詢使用的次數(shù)。如果該數(shù)為零，則表是打開的，但是當(dāng)前沒有被使用。
Name_locked：表名稱是否被鎖定。名稱鎖定用于取消表或?qū)Ρ磉M(jìn)行重命名等操作。

show status like 'Table_locks%'

Table_locks_immediate：指的是能夠立即獲得表級鎖的次數(shù)，每立即獲取鎖，值加1。
Table_locks_waited：指的是不能立即獲取表級鎖而需要等待的次數(shù)，每等待一次，該值加1，此值高說明存在著較為嚴(yán)重的表級鎖爭用情況。

五、InnoDB 行鎖

5.1、 行鎖介紹

行鎖特點 ：偏向InnoDB 存儲引擎，開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。

InnoDB 與 MyISAM 的最大不同有兩點：一是支持事務(wù)；二是采用了行級鎖。

InnoDB 存儲結(jié)構(gòu)

InnoDB 是聚簇索引，也就是 B+樹的葉節(jié)點既存儲了主鍵索引也存儲了數(shù)據(jù)行。而 InnoDB 的二級索引的葉節(jié)點存儲的則是主鍵值，所以通過二級索引查詢數(shù)據(jù)時，還需要拿對應(yīng)的主鍵去聚簇索引中再次進(jìn)行查詢。

5.2、背景知識

事務(wù)及其ACID屬性

事務(wù)是由一組SQL語句組成的邏輯處理單元。
事務(wù)具有以下4個特性，簡稱為事務(wù)ACID屬性。

并發(fā)事務(wù)處理帶來的問題

事務(wù)隔離級別

為了解決上述提到的事務(wù)并發(fā)問題，數(shù)據(jù)庫提供一定的事務(wù)隔離機(jī)制來解決這個問題。

數(shù)據(jù)庫的事務(wù)隔離越嚴(yán)格，并發(fā)副作用越小，但付出的代價也就越大，因為事務(wù)隔離實質(zhì)上就是使用事務(wù)在一定程度上“串行化” 進(jìn)行，這顯然與“并發(fā)” 是矛盾的。

數(shù)據(jù)庫的隔離級別有4個，由低到高依次為Read uncommitted、Read committed、Repeatable read、Serializable，這四個級別可以逐個解決臟寫、臟讀、不可重復(fù)讀、幻讀這幾類問題。

備注 ： √ 代表可能出現(xiàn) ， × 代表不會出現(xiàn) 。

Mysql 的數(shù)據(jù)庫的默認(rèn)隔離級別為 Repeatable read ， 查看方式：

show variables like 'tx_isolation';

5.3、InnoDB 的行鎖模式

InnoDB 實現(xiàn)了以下兩種類型的行鎖。

• 共享鎖（S）：又稱為讀鎖，簡稱S鎖，共享鎖就是多個事務(wù)對于同一數(shù)據(jù)可以共享一把鎖，都能訪問到數(shù)據(jù)，但是只能讀不能修改。

• 排他鎖（X）：又稱為寫鎖，簡稱X鎖，排他鎖就是不能與其他鎖并存，如一個事務(wù)獲取了一個數(shù)據(jù)行的排他鎖，其他事務(wù)就不能再獲取該行的其他鎖，包括共享鎖和排他鎖，但是獲取排他鎖的事務(wù)是可以對數(shù)據(jù)就行讀取和修改。

對于UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及數(shù)據(jù)集加排他鎖（X)；
對于普通SELECT語句，InnoDB不會加任何鎖；

可以通過以下語句顯示給記錄集加共享鎖或排他鎖 。

共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

排他鎖（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE

5.4、 案例準(zhǔn)備工作

create table test_innodb_lock(
    id int(11),
    name varchar(16),
    sex varchar(1)
)engine = innodb default charset=utf8;

insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');

create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);

5.5、行鎖基本演示

5.6、無索引行鎖升級為表鎖

如果不通過索引條件檢索數(shù)據(jù)，那么InnoDB將對表中的所有記錄加鎖，實際效果跟表鎖一樣。

查看當(dāng)前表的索引：

show index from test_innodb_lock ;

由于 執(zhí)行更新時 ， name字段本來為varchar類型， 我們是作為數(shù)組類型使用，存在類型轉(zhuǎn)換，索引失效，最終行鎖變?yōu)楸礞i；

5.7、間隙鎖危害

當(dāng)我們用范圍條件，而不是使用相等條件檢索數(shù)據(jù)，并請求共享或排他鎖時，InnoDB會給符合條件的已有數(shù)據(jù)進(jìn)行加鎖； 對于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做 "間隙（GAP）" ， InnoDB也會對這個 "間隙" 加鎖，這種鎖機(jī)制就是所謂的 間隙鎖（Next-Key鎖） 。

示例 ：

5.8、Gap lock

還有一種間隙鎖的現(xiàn)象：
比如，更新用戶年齡的例子，如果 id = 49 這條記錄不存在，這個 SQL 語句還會加鎖嗎？答案是有可能，這取決于數(shù)據(jù)庫的隔離級別。這種情況下，在 RC 隔離級別不會加任何鎖，在 RR 隔離級別會在 id = 49 前后兩個索引之間加上間隙鎖。

間隙鎖是一種加在兩個索引之間的鎖，或者加在第一個索引之前，或最后一個索引之后的間隙。這個間隙可以跨一個索引記錄，多個索引記錄，甚至是空的。使用間隙鎖可以防止其他事務(wù)在這個范圍內(nèi)插入或修改記錄，保證兩次讀取這個范圍內(nèi)的記錄不會變，從而不會出現(xiàn)幻讀現(xiàn)象。

值得注意的是，間隙鎖和間隙鎖之間是互不沖突的，間隙鎖唯一的作用就是為了防止其他事務(wù)的插入，所以加間隙 S 鎖和加間隙 X 鎖沒有任何區(qū)別。

5.9、InnoDB 行鎖爭用情況

show status like 'innodb_row_lock%';

• Innodb_row_lock_current_waits：當(dāng)前正在等待鎖定的數(shù)量。
• Innodb_row_lock_time：從系統(tǒng)啟動到現(xiàn)在鎖定總時間長度。
• Innodb_row_lock_time_avg：每次等待所花平均時長。
• Innodb_row_lock_time_max：從系統(tǒng)啟動到現(xiàn)在等待最長的一次所花的時間。
• Innodb_row_lock_waits：系統(tǒng)啟動后到現(xiàn)在總共等待的次數(shù)。

當(dāng)?shù)却拇螖?shù)很高，而且每次等待的時長也不小的時候，我們就需要分析系統(tǒng)中為什么會有如此多的等待，然后根據(jù)分析結(jié)果著手制定優(yōu)化計劃。

5.10、InnoDB 行鎖總結(jié)：

InnoDB存儲引擎由于實現(xiàn)了行級鎖定，雖然在鎖定機(jī)制的實現(xiàn)方面帶來了性能損耗可能比表鎖會更高一些，但是在整體并發(fā)處理能力方面要遠(yuǎn)遠(yuǎn)優(yōu)于MyISAM的表鎖的。當(dāng)系統(tǒng)并發(fā)量較高的時候，InnoDB的整體性能和MyISAM相比就會有比較明顯的優(yōu)勢。

但是，InnoDB的行級鎖同樣也有其脆弱的一面，當(dāng)我們使用不當(dāng)?shù)臅r候，可能會讓InnoDB的整體性能表現(xiàn)不僅不能比MyISAM高，甚至可能會更差。

優(yōu)化建議：

• 1、盡可能讓所有數(shù)據(jù)檢索都能通過索引來完成，避免無索引行鎖升級為表鎖。
• 2、合理設(shè)計索引，盡量縮小鎖的范圍。
• 3、盡可能減少索引條件，及索引范圍，避免間隙鎖。
• 4、盡量控制事務(wù)大小，減少鎖定資源量和時間長度。
• 5、盡可使用低級別事務(wù)隔離（但是需要業(yè)務(wù)層面滿足需求）。

六、常用SQL技巧

6.1、SQL執(zhí)行順序

編寫順序

SELECT DISTINCT
    <select list>
FROM
    <left_table> <join_type>
JOIN
    <right_table> 
ON 
    <join_condition>
WHERE
    <where_condition>
GROUP BY
    <group_by_list>
HAVING
    <having_condition>
ORDER BY
    <order_by_condition>
LIMIT
    <limit_params>

執(zhí)行順序

FROM <left_table>

ON <join_condition>

<join_type> JOIN <right_table>

WHERE <where_condition>

GROUP BY <group_by_list>

HAVING <having_condition>

SELECT DISTINCT <select list>

ORDER BY <order_by_condition>

LIMIT <limit_params>

事實上，sql并不是按照我們的書寫順序來從前往后、左往右依次執(zhí)行的，它是按照固定的順序解析的，主要的作用就是從上一個階段的執(zhí)行返回結(jié)果來提供給下一階段使用，sql在執(zhí)行的過程中會有不同的臨時中間表，一般是按照如下順序：

6.2、正則表達(dá)式使用

正則表達(dá)式（Regular Expression）是指一個用來描述或者匹配一系列符合某個句法規(guī)則的字符串的單個字符串。

select * from emp where name regexp '^T';

select * from emp where name regexp '2$';

select * from emp where name regexp '[uvw]';

6.3、MySQL 常用函數(shù)

數(shù)字函數(shù)

字符串函數(shù)

日期函數(shù)

聚合函數(shù)

參考：
https://www.imooc.com/article/295953

https://blog.csdn.net/org_hjh/article/details/108901385

http://blog.itpub.net/31556440/viewspace-2642668/

https://www.cnblogs.com/leedaily/p/8378779.html

https://www.mscto.com/data/459802.html

https://www.cnblogs.com/axing-articles/p/11415763.html

MVCC--多版本并發(fā)控制機(jī)制

https://www.cnblogs.com/wyq178/p/11576065.html