Mysql索引與鎖

Mysql索引與鎖

本文以Mysql5.7為例測(cè)試。

1：mysql索引方法

Mysql的索引方法分為btree索引和hash索引。

hash索引：是通過(guò)hash計(jì)算后比較，所以只能用于等值過(guò)濾，不能用于范圍過(guò)濾。Hash索引會(huì)根據(jù)列數(shù)據(jù)維護(hù)一張hash表，所以任何時(shí)候都要進(jìn)行hash表掃描，當(dāng)hash表數(shù)據(jù)量龐大時(shí)，性能急劇下降。

Sql語(yǔ)句

是否使用索引

select * from t1 where c1=1 and c2=2

√

select * from testindex where c2>'1' and c1

√

select * from t1 where c1=1

√

select * from t1 where c2=2

×

結(jié)論：如果需要使用c2過(guò)濾時(shí)使用索引，必須同時(shí)用c1過(guò)濾

CREATE TABLE `testnoindex` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`c1` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

Id為主鍵索引，c1為普通字段，初始化一些數(shù)據(jù)方便查看

Session1：設(shè)置不自動(dòng)提交，并執(zhí)行update語(yǔ)句，根據(jù)條件可以看出，更新的是id為10的記錄，執(zhí)行完如果沒(méi)有顯式調(diào)用commit，則會(huì)鎖整個(gè)表。

SET AUTOCOMMIT=0;

update testnoindex set c1='2017-08-0209:52:21' where c1='2017-08-02 10:47:20';

注意：c1一定是無(wú)索引情況，如果有索引(非唯一索引)，都不會(huì)鎖整張表。

session2：更新id為1的記錄，獲取鎖超時(shí)。

以上實(shí)例可以證明mysql innodb存儲(chǔ)引擎默認(rèn)是表級(jí)鎖，當(dāng)然innodb是支持行級(jí)鎖的，比如通過(guò)唯一主鍵就可實(shí)現(xiàn)行級(jí)鎖。

如果where條件的索引數(shù)據(jù)是多行的話，多行數(shù)據(jù)會(huì)同時(shí)被鎖定，可理解為范圍鎖。

Mysql有三種鎖Record,Gap, and Next-KeyLocks。Recode為行鎖，Gap為間隙鎖，Next-KeyLocks為前兩種的結(jié)合。我們先看一個(gè)實(shí)例，加入mysql中記錄為如下所示：

Session1：執(zhí)行下面語(yǔ)句，因?yàn)闆](méi)有7的記錄，會(huì)向左掃描至id=2的記錄，向右掃描至id=8的記錄，鎖定區(qū)間為(2,8)。

set autocommit=0;

update testnoindex set c1='2017-08-0210:47:09' where id=7;

session2：執(zhí)行如下語(yǔ)句，可以看出id=4的記錄被鎖定。

INSERT into testnoindex (id,c1)values(16,NOW());

INSERT into testnoindex (id,c1)values(4,NOW());

[SQL]INSERT into testnoindex (id,c1)values(16,NOW());

受影響的行: 1

時(shí)間: 0.087s

[SQL]

INSERT into testnoindex (id,c1)values(4,NOW());

[Err] 1205 - Lock wait timeout exceeded;try restarting transaction

4：共享鎖與排他鎖

Mysql行級(jí)鎖又分為共享鎖(讀鎖或S鎖)和排他鎖(寫鎖或X鎖)，比如forupdate就是排它鎖。Mysql默認(rèn)delete,insert,update都是排它鎖，select默認(rèn)無(wú)鎖。

2? 如果行記錄被加了排它鎖，則其他事務(wù)無(wú)法再在其上加鎖，也就是加鎖狀態(tài)無(wú)法讀取/修改。換句話說(shuō)就是如果被加了排他鎖的行，不能再加排他或共享鎖，但是可以無(wú)鎖讀取(普通select,)。

2? 如果行記錄被加了共享鎖，則其他事務(wù)只能在共享鎖或無(wú)鎖狀態(tài)下讀取。

Mysql索引與鎖相關(guān)教程

總結(jié)

以上是生活随笔為你收集整理的mysql suoyin 和锁_Mysql索引与锁的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。