解讀mysql的索引和事務(wù)的正確姿勢

一舟陆、索引是做什么的?

很多時(shí)候，當(dāng)你的應(yīng)用程序進(jìn)行SQL查詢速度很慢時(shí)兼蕊，應(yīng)該想想是否可以建索引。

大多數(shù)MySQL索引(PRIMARY KEY件蚕、UNIQUE孙技、INDEX和FULLTEXT)在B樹中存儲(chǔ)。只是空間列類型的索引使用R-樹排作，并且MEMORY表還支持hash索引牵啦。

索引是一個(gè)排序的列表，在這個(gè)列表中存儲(chǔ)著索引的值和包含這個(gè)值的數(shù)據(jù)所在行的物理地址妄痪，在數(shù)據(jù)十分龐大的時(shí)候哈雏，索引可以大大加快查詢的速度，這是因?yàn)槭褂盟饕罂梢圆挥脪呙枞韥矶ㄎ荒承械臄?shù)據(jù)，而是先通過索引表找到該行數(shù)據(jù)對應(yīng)的物理地址然后訪問相應(yīng)的數(shù)據(jù)裳瘪。

二履因、索引的優(yōu)缺點(diǎn)

優(yōu)勢：可以快速檢索，減少I/O次數(shù)盹愚，加快檢索速度；根據(jù)索引分組和排序站故，可以加快分組和排序皆怕；

劣勢：索引本身也是表，因此會(huì)占用存儲(chǔ)空間西篓，一般來說愈腾，索引表占用的空間的數(shù)據(jù)表的1.5倍；索引表的維護(hù)和創(chuàng)建需要時(shí)間成本岂津，這個(gè)成本隨著數(shù)據(jù)量增大而增大虱黄；構(gòu)建索引會(huì)降低數(shù)據(jù)表的修改操作（刪除，添加吮成，修改）的效率橱乱，因?yàn)樵谛薷臄?shù)據(jù)表的同時(shí)還需要修改索引表；

三粱甫、索引的分類

常見的索引類型有：主鍵索引泳叠、唯一索引、普通索引茶宵、全文索引危纫、組合索引

1、主鍵索引：即主索引乌庶，根據(jù)主鍵pk_clolum（length）建立索引种蝶，不允許重復(fù)，不允許空值瞒大；

ALTER TABLE 'table_name' ADD PRIMARY KEY('id')螃征；

2、唯一索引：用來建立索引的列的值必須是唯一的糠赦，允許空值

ALTER TABLE 'table_name' ADD UNIQUE('email')会傲；

3、普通索引：用表中的普通列構(gòu)建的索引拙泽，沒有任何限制

ALTER TABLE 'table_name' ADD INDEX index_name('description')淌山；

4、全文索引：用大文本對象的列構(gòu)建的索引（下一部分會(huì)講解）

ALTER TABLE 'table_name' ADD FULLTEXT('content')顾瞻；

5泼疑、組合索引：用多個(gè)列組合構(gòu)建的索引，這多個(gè)列中的值不允許有空值

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')荷荤；

遵循“最左前綴”原則退渗，把最常用作為檢索或排序的列放在最左移稳，依次遞減，組合索引相當(dāng)于建立了col1,col1col2,col1col2col3三個(gè)索引会油，而col2或者col3是不能使用索引的个粱。

在使用組合索引的時(shí)候可能因?yàn)榱忻L度過長而導(dǎo)致索引的key太大，導(dǎo)致效率降低翻翩，在允許的情況下都许，可以只取col1和col2的前幾個(gè)字符作為索引

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；

表示使用col1的前4個(gè)字符和col2的前3個(gè)字符作為索引

四嫂冻、索引的實(shí)現(xiàn)原理

MySQL支持諸多存儲(chǔ)引擎胶征，而各種存儲(chǔ)引擎對索引的支持也各不相同，因此MySQL數(shù)據(jù)庫支持多種索引類型桨仿，如BTree索引睛低，B+Tree索引，哈希索引服傍，全文索引等等钱雷，

1、哈希索引：

只有memory（內(nèi)存）存儲(chǔ)引擎支持哈希索引吹零，哈希索引用索引列的值計(jì)算該值的hashCode急波，然后在hashCode相應(yīng)的位置存執(zhí)該值所在行數(shù)據(jù)的物理位置，因?yàn)槭褂蒙⒘兴惴ū裥＃虼嗽L問速度非吵文海快，但是一個(gè)值只能對應(yīng)一個(gè)hashCode阱扬，而且是散列的分布方式泣懊，因此哈希索引不支持范圍查找和排序的功能。

2麻惶、全文索引：

FULLTEXT（全文）索引馍刮，僅可用于MyISAM和InnoDB，針對較大的數(shù)據(jù)窃蹋，生成全文索引非常的消耗時(shí)間和空間卡啰。對于文本的大對象，或者較大的CHAR類型的數(shù)據(jù)警没，如果使用普通索引匈辱，那么匹配文本前幾個(gè)字符還是可行的，但是想要匹配文本中間的幾個(gè)單詞杀迹，那么就要使用LIKE %word%來匹配亡脸，這樣需要很長的時(shí)間來處理，響應(yīng)時(shí)間會(huì)大大增加，這種情況浅碾，就可使用時(shí)FULLTEXT索引了大州，在生成FULLTEXT索引時(shí)，會(huì)為文本生成一份單詞的清單垂谢，在索引時(shí)及根據(jù)這個(gè)單詞的清單來索引厦画。FULLTEXT可以在創(chuàng)建表的時(shí)候創(chuàng)建，也可以在需要的時(shí)候用ALTER或者CREATE INDEX來添加：

//創(chuàng)建表的時(shí)候添加FULLTEXT索引
CTREATE TABLE my_table(
id INT(10) PRIMARY KEY,
name VARCHAR(10) NOT NULL,
my_text text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
FULLTEXT(my_text));

//創(chuàng)建表以后滥朱，在需要的時(shí)候添加FULLTEXT索引
ALTER my_table ADD FULLTEXT ft_index(my_text);
CREATE INDEX ft_index ON my_table(my_text);

對于較大的數(shù)據(jù)集苛白，把數(shù)據(jù)添加到一個(gè)沒有FULLTEXT索引的表，然后添加FULLTEXT索引的速度比把數(shù)據(jù)添加到一個(gè)已經(jīng)有FULLTEXT索引的表快焚虱。

MySQL自帶的全文索引只能用于MyISAM存儲(chǔ)引擎，如果是其它數(shù)據(jù)引擎懂版，那么全文索引不會(huì)生效鹃栽。

在MySQL中，全文索引支隊(duì)英文有用躯畴，目前對中文還不支持民鼓。

在MySQL中，如果檢索的字符串太短則無法檢索得到預(yù)期的結(jié)果蓬抄，檢索的字符串長度至少為4字節(jié)丰嘉，此外，如果檢索的字符包括停止詞嚷缭，那么停止詞會(huì)被忽略饮亏。

3、BTree索引和B+Tree索引

BTree索引

BTree是平衡搜索多叉樹阅爽，設(shè)樹的度為d（d>1）路幸，高度為h，那么BTree要滿足以一下條件：

每個(gè)葉子結(jié)點(diǎn)的高度一樣付翁，等于h简肴；

每個(gè)非葉子結(jié)點(diǎn)由n-1個(gè)key和n個(gè)指針point組成，其中d<=n<=2d,key和point相互間隔百侧，結(jié)點(diǎn)兩端一定是key砰识；

葉子結(jié)點(diǎn)指針都為null；

非葉子結(jié)點(diǎn)的key都是[key,data]二元組佣渴，其中key表示作為索引的鍵辫狼，data為鍵值所在行的數(shù)據(jù)；

BTree的結(jié)構(gòu)如下：

image

在BTree的機(jī)構(gòu)下辛润，就可以使用二分查找的查找方式予借，查找復(fù)雜度為h*log(n)，一般來說樹的高度是很小的，一般為3左右灵迫，因此BTree是一個(gè)非常高效的查找結(jié)構(gòu)秦叛。

B+Tree索引

B+Tree是BTree的一個(gè)變種，設(shè)d為樹的度數(shù)瀑粥，h為樹的高度挣跋，B+Tree和BTree的不同主要在于：

B+Tree中的非葉子結(jié)點(diǎn)不存儲(chǔ)數(shù)據(jù)，只存儲(chǔ)鍵值狞换；

B+Tree的葉子結(jié)點(diǎn)沒有指針避咆，所有鍵值都會(huì)出現(xiàn)在葉子結(jié)點(diǎn)上，且key存儲(chǔ)的鍵值對應(yīng)的數(shù)據(jù)的物理地址修噪；

B+Tree的結(jié)構(gòu)如下：

image

一般來說B+Tree比BTree更適合實(shí)現(xiàn)外存的索引結(jié)構(gòu)查库，因?yàn)榇鎯?chǔ)引擎的設(shè)計(jì)專家巧妙的利用了外存（磁盤）的存儲(chǔ)結(jié)構(gòu)，即磁盤的一個(gè)扇區(qū)是整數(shù)倍的page（頁）黄琼，頁是存儲(chǔ)中的一個(gè)單位樊销，通常默認(rèn)為4K，因此索引結(jié)構(gòu)的節(jié)點(diǎn)被設(shè)計(jì)為一個(gè)頁的大小脏款，然后利用外存的“預(yù)讀取”原則围苫，每次讀取的時(shí)候，把整個(gè)節(jié)點(diǎn)的數(shù)據(jù)讀取到內(nèi)存中撤师，然后在內(nèi)存中查找剂府，已知內(nèi)存的讀取速度是外存讀取I/O速度的幾百倍，那么提升查找速度的關(guān)鍵就在于盡可能少的磁盤I/O剃盾，那么可以知道腺占，每個(gè)節(jié)點(diǎn)中的key個(gè)數(shù)越多，那么樹的高度越小痒谴，需要I/O的次數(shù)越少湾笛，因此一般來說B+Tree比BTree更快，因?yàn)锽+Tree的非葉節(jié)點(diǎn)中不存儲(chǔ)data闰歪，就可以存儲(chǔ)更多的key嚎研。

帶順序索引的B+TREE

很多存儲(chǔ)引擎在B+Tree的基礎(chǔ)上進(jìn)行了優(yōu)化，添加了指向相鄰葉節(jié)點(diǎn)的指針库倘，形成了帶有順序訪問指針的B+Tree临扮，這樣做是為了提高區(qū)間查找的效率，只要找到第一個(gè)值那么就可以順序的查找后面的值教翩。

B+Tree的結(jié)構(gòu)如下：

image

分析了MySQL的索引結(jié)構(gòu)的實(shí)現(xiàn)原理杆勇，然后我們來看看具體的存儲(chǔ)引擎怎么實(shí)現(xiàn)索引結(jié)構(gòu)的，MySQL中最常見的兩種存儲(chǔ)引擎分別是MyISAM和InnoDB饱亿，分別實(shí)現(xiàn)了非聚簇索引和聚簇索引蚜退。

首先要介紹幾個(gè)概念闰靴，在索引的分類中，我們可以按照索引的鍵是否為主鍵來分為“主索引”和“輔助索引”钻注，使用主鍵鍵值建立的索引稱為“主索引”蚂且，其它的稱為“輔助索引”。因此主索引只能有一個(gè)幅恋，輔助索引可以有很多個(gè)杏死。

MyISAM——非聚簇索引

MyISAM存儲(chǔ)引擎采用的是非聚簇索引，非聚簇索引的主索引和輔助索引幾乎是一樣的捆交，只是主索引不允許重復(fù)淑翼，不允許空值，他們的葉子結(jié)點(diǎn)的key都存儲(chǔ)指向鍵值對應(yīng)的數(shù)據(jù)的物理地址品追。

非聚簇索引的數(shù)據(jù)表和索引表是分開存儲(chǔ)的玄括。

非聚簇索引中的數(shù)據(jù)是根據(jù)數(shù)據(jù)的插入順序保存。因此非聚簇索引更適合單個(gè)數(shù)據(jù)的查詢肉瓦。插入順序不受鍵值影響遭京。

只有在MyISAM中才能使用FULLTEXT索引。

最開始我一直不懂既然非聚簇索引的主索引和輔助索引指向相同的內(nèi)容风宁，為什么還要輔助索引這個(gè)東西呢，后來才明白索引不就是用來查詢的嗎蛹疯，用在那些地方呢戒财，不就是WHERE和ORDER BY 語句后面嗎，那么如果查詢的條件不是主鍵怎么辦呢捺弦，這個(gè)時(shí)候就需要輔助索引了饮寞。

InnoDB——聚簇索引

聚簇索引的主索引的葉子結(jié)點(diǎn)存儲(chǔ)的是鍵值對應(yīng)的數(shù)據(jù)本身，輔助索引的葉子結(jié)點(diǎn)存儲(chǔ)的是鍵值對應(yīng)的數(shù)據(jù)的主鍵鍵值列吼。因此主鍵的值長度越小越好幽崩，類型越簡單越好。

聚簇索引的數(shù)據(jù)和主鍵索引存儲(chǔ)在一起寞钥。

聚簇索引的數(shù)據(jù)是根據(jù)主鍵的順序保存慌申。因此適合按主鍵索引的區(qū)間查找，可以有更少的磁盤I/O理郑，加快查詢速度蹄溉。但是也是因?yàn)檫@個(gè)原因，聚簇索引的插入順序最好按照主鍵單調(diào)的順序插入您炉，否則會(huì)頻繁的引起頁分裂柒爵，嚴(yán)重影響性能。

在InnoDB中赚爵，如果只需要查找索引的列棉胀，就盡量不要加入其它的列法瑟，這樣會(huì)提高查詢效率。

使用主索引的時(shí)候唁奢，更適合使用聚簇索引霎挟，因?yàn)榫鄞厮饕恍枰檎乙淮危蔷鄞厮饕诓榈綌?shù)據(jù)的地址后驮瞧，還要進(jìn)行一次I/O查找數(shù)據(jù)氓扛。

因?yàn)榫鄞剌o助索引存儲(chǔ)的是主鍵的鍵值，因此可以在數(shù)據(jù)行移動(dòng)或者頁分裂的時(shí)候降低委會(huì)成本论笔，因?yàn)檫@時(shí)不用維護(hù)輔助索引采郎。但是輔助索引會(huì)占用更多的空間。

聚簇索引在插入新數(shù)據(jù)的時(shí)候比非聚簇索引慢很多狂魔，因?yàn)椴迦胄聰?shù)據(jù)時(shí)需要減壓主鍵是否重復(fù)蒜埋，這需要遍歷主索引的所有葉節(jié)點(diǎn)，而非聚簇索引的葉節(jié)點(diǎn)保存的是數(shù)據(jù)地址最楷，占用空間少整份，因此分布集中，查詢的時(shí)候I/O更少籽孙，但聚簇索引的主索引中存儲(chǔ)的是數(shù)據(jù)本身烈评，數(shù)據(jù)占用空間大，分布范圍更大犯建，可能占用好多的扇區(qū)讲冠，因此需要更多次I/O才能遍歷完畢。

下圖可以形象的說明聚簇索引和非聚簇索引的區(qū)別

image

五适瓦、索引的使用策略

什么時(shí)候要使用索引竿开？

主鍵自動(dòng)建立唯一索引；

經(jīng)常作為查詢條件在WHERE或者ORDER BY 語句中出現(xiàn)的列要建立索引玻熙；

作為排序的列要建立索引否彩；

查詢中與其他表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引

高并發(fā)條件下傾向組合索引嗦随；

什么時(shí)候不要使用索引列荔？

經(jīng)常增刪改的列不要建立索引；

有大量重復(fù)的列不建立索引枚尼；

表記錄太少不要建立索引肌毅；

在組合索引中不能有列的值為NULL，如果有姑原，那么這一列對組合索引就是無效的悬而；

在一個(gè)SELECT語句中，索引只能使用一次锭汛，如果在WHERE中使用了笨奠，那么在ORDER BY中就不要用了袭蝗；

LIKE操作中，'%aaa%'不會(huì)使用索引般婆，也就是索引會(huì)失效到腥，但是‘a(chǎn)aa%’可以使用索引；

在索引的列上使用表達(dá)式或者函數(shù)會(huì)使索引失效蔚袍，例如：select * from users where YEAR(adddate)<2018乡范，將在每個(gè)行上進(jìn)行運(yùn)算，這將導(dǎo)致索引失效而進(jìn)行全表掃描啤咽，因此我們可以改成：select * from users where adddate<’2018-12-24′晋辆。

在查詢條件中使用正則表達(dá)式時(shí)，只有在搜索模板的第一個(gè)字符不是通配符的情況下才能使用索引宇整。

在查詢條件中使用<>會(huì)導(dǎo)致索引失效瓶佳。

在查詢條件中使用IS NULL會(huì)導(dǎo)致索引失效。

在查詢條件中使用OR連接多個(gè)條件會(huì)導(dǎo)致索引失效鳞青，這時(shí)應(yīng)該改為兩次查詢霸饲，然后用UNION ALL連接起來。

盡量不要包括多列排序臂拓，如果一定要厚脉，最好為這隊(duì)列構(gòu)建組合索引；

只有當(dāng)數(shù)據(jù)庫里已經(jīng)有了足夠多的測試數(shù)據(jù)時(shí)胶惰，它的性能測試結(jié)果才有實(shí)際參考價(jià)值傻工。如果在測試數(shù)據(jù)庫里只有幾百條數(shù)據(jù)記錄，它們往往在執(zhí)行完第一條查詢命令之后就被全部加載到內(nèi)存里童番，這將使后續(xù)的查詢命令都執(zhí)行得非尘ィ快--不管有沒有使用索引威鹿。只有當(dāng)數(shù)據(jù)庫里的記錄超過了1000條剃斧、數(shù)據(jù)總量也超過了MySQL服務(wù)器上的內(nèi)存總量時(shí)，數(shù)據(jù)庫的性能測試結(jié)果才有意義忽你。

六幼东、索引的優(yōu)化

1、最左前綴

索引的最左前綴和和B+Tree中的“最左前綴原理”有關(guān)科雳，舉例來說就是如果設(shè)置了組合索引<col1,col2,col3>那么以下3中情況可以使用索引：col1根蟹，<col1,col2>，<col1,col2,col3>糟秘，其它的列简逮，比如<col2,col3>，<col1,col3>尿赚，col2散庶，col3等等都是不能使用索引的蕉堰。

根據(jù)最左前綴原則，我們一般把排序分組頻率最高的列放在最左邊悲龟，以此類推屋讶。

2、帶索引的模糊查詢優(yōu)化

在上面已經(jīng)提到须教，使用LIKE進(jìn)行模糊查詢的時(shí)候皿渗，'%aaa%'不會(huì)使用索引，也就是索引會(huì)失效轻腺。如果是這種情況乐疆，只能使用全文索引來進(jìn)行優(yōu)化（上文有講到）。

為檢索的條件構(gòu)建全文索引约计，然后使用

SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);

事務(wù)介紹

首先诀拭，什么是事務(wù)？事務(wù)就是一段sql 語句的批處理煤蚌，但是這個(gè)批處理是一個(gè)atom（原子）耕挨，不可分割，要么都執(zhí)行尉桩，要么回滾（rollback）都不執(zhí)行筒占。

MySQL 事務(wù)主要用于處理操作量大，復(fù)雜度高的數(shù)據(jù)蜘犁。比如說翰苫，在人員管理系統(tǒng)中，你刪除一個(gè)人員这橙，你即需要?jiǎng)h除人員的基本資料奏窑，也要?jiǎng)h除和該人員相關(guān)的信息，如信箱屈扎，文章等等埃唯，這樣，這些數(shù)據(jù)庫操作語句就構(gòu)成一個(gè)事務(wù)鹰晨！

  • 在 MySQL 中只有使用了 Innodb 數(shù)據(jù)庫引擎的數(shù)據(jù)庫或表才支持事務(wù)墨叛。

  • 事務(wù)處理可以用來維護(hù)數(shù)據(jù)庫的完整性，保證成批的 SQL 語句要么全部執(zhí)行模蜡，要么全部不執(zhí)行漠趁。

  • 事務(wù)用來管理 insert,update,delete 語句

一般來說，事務(wù)是必須滿足4個(gè)條件（ACID）： Atomicity（原子性）忍疾、Consistency（穩(wěn)定性）闯传、Isolation（隔離性）、Durability（可靠性）

  • 1卤妒、事務(wù)的原子性：一組事務(wù)甥绿，要么成功叠必；要么撤回。

  • 2妹窖、穩(wěn)定性 ：有非法數(shù)據(jù)（外鍵約束之類）纬朝，事務(wù)撤回。

  • 3骄呼、隔離性：事務(wù)獨(dú)立運(yùn)行共苛。一個(gè)事務(wù)處理后的結(jié)果，影響了其他事務(wù)蜓萄，那么其他事務(wù)會(huì)撤回隅茎。事務(wù)的100%隔離，需要犧牲速度嫉沽。

  • 4辟犀、可靠性：軟、硬件崩潰后绸硕，InnoDB數(shù)據(jù)表驅(qū)動(dòng)會(huì)利用日志文件重構(gòu)修改堂竟。可靠性和高速度不可兼得玻佩， innodb_flush_log_at_trx_commit 選項(xiàng) 決定什么時(shí)候吧事務(wù)保存到日志里出嘹。

image

事務(wù)并發(fā)并不進(jìn)行事務(wù)隔離造成的臟讀、幻讀咬崔、不可重復(fù)讀

  • 臟讀：事務(wù)A讀到未提交事務(wù)B修改的數(shù)據(jù)税稼，如果此時(shí)事務(wù)B中途執(zhí)行失敗回滾，那么此時(shí)事務(wù)A讀取到的就是臟數(shù)據(jù)垮斯。比如事務(wù)A對money進(jìn)行修改郎仆，此時(shí)事務(wù)B讀取到事務(wù)A的更新結(jié)果，但是如果后面事務(wù)A回滾兜蠕，那么事務(wù)B讀取到的就是臟數(shù)據(jù)了扰肌。

  • 不可重復(fù)讀：同一個(gè)事務(wù)中，對同一份數(shù)據(jù)讀取的結(jié)果不一致牺氨。事務(wù)A在事務(wù)B對數(shù)據(jù)更新前進(jìn)行讀取狡耻，然后事務(wù)B更新提交墩剖，事務(wù)A再次讀取猴凹，這時(shí)候兩次讀取的數(shù)據(jù)不同。

  • 幻讀：（同一個(gè)事務(wù)中岭皂，同一個(gè)查詢多次返回的結(jié)果不一樣郊霎。事務(wù)B查詢表的記錄數(shù)，然后事務(wù)A對表插入一條記錄爷绘，接著事務(wù)B再次查詢發(fā)現(xiàn)記錄數(shù)不同书劝。注意這個(gè)解釋是不正確进倍，網(wǎng)絡(luò)上有很多這樣的解釋，包括我認(rèn)為比較權(quán)威的專家购对，但是經(jīng)過實(shí)驗(yàn)發(fā)現(xiàn)并不正確猾昆。所以這是需要注意的)÷獍可以做這樣一個(gè)實(shí)驗(yàn)垂蜗，事務(wù)A查詢記錄數(shù)，事務(wù)B插入一條記錄（主鍵值為6）解幽，提交贴见，然后事務(wù)A查詢記錄數(shù)，發(fā)現(xiàn)記錄數(shù)沒有改變躲株，但是此時(shí)插入一條主鍵值為6的記錄發(fā)現(xiàn)沖突了片部，感覺像出現(xiàn)了幻覺。

區(qū)別

1霜定、臟讀和不可重復(fù)讀：臟讀是事務(wù)讀取了還未提交事務(wù)的更新數(shù)據(jù)档悠。不可重復(fù)讀是同一個(gè)事務(wù)中，幾次讀取的數(shù)據(jù)不同望浩。

2站粟、不可重復(fù)讀和幻讀的區(qū)別：都是在同一個(gè)事務(wù)中，前者是幾次讀取數(shù)據(jù)不同曾雕，后者是幾次讀取數(shù)據(jù)整體不同奴烙。

隔離級(jí)別

image
image

  • 隔離級(jí)別改變影響鎖的周期

  • mysql支持上面4種隔離級(jí)別，默認(rèn)為可重復(fù)讀

image
image

MySQL有三種鎖的級(jí)別：頁級(jí)剖张、表級(jí)切诀、行級(jí)。

MyISAM和MEMORY存儲(chǔ)引擎采用的是表級(jí)鎖（table-level locking）搔弄；

BDB存儲(chǔ)引擎采用的是頁面鎖（page-level locking）幅虑，但也支持表級(jí)鎖；

InnoDB存儲(chǔ)引擎既支持行級(jí)鎖（row-level locking）顾犹，也支持表級(jí)鎖倒庵，但默認(rèn)情況下是

采用行級(jí)鎖。

MySQL這3種鎖的特性可大致歸納如下： 1炫刷、表級(jí)鎖：開銷小擎宝，加鎖快；不會(huì)出現(xiàn)死鎖浑玛；鎖定粒度大绍申，發(fā)生鎖沖突的概率最高,并發(fā)度最低。表級(jí)鎖讓多線程可以同時(shí)從數(shù)據(jù)表中讀取數(shù)據(jù)，但是如果另一個(gè)線程想要寫數(shù)據(jù)的話极阅，就必須要先取得排他訪問（默認(rèn)加排他表鎖）胃碾；（共享讀鎖（Table Read Lock）更新數(shù)據(jù)時(shí)，必須要等到更新完成了筋搏，其他線程才能訪問（讀）這個(gè)表仆百。（獨(dú)占寫鎖（Table Write Lock））

2、行級(jí)鎖：開銷大奔脐，加鎖慢儒旬；會(huì)出現(xiàn)死鎖；鎖定粒度最小帖族，發(fā)生鎖沖突的概率最低,并發(fā)度也最高栈源。

3、頁面鎖：開銷和加鎖時(shí)間界于表鎖和行鎖之間竖般；會(huì)出現(xiàn)死鎖甚垦；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般涣雕。

原則上數(shù)據(jù)表有一個(gè)讀鎖時(shí)艰亮，其它進(jìn)程無法對此表進(jìn)行更新操作，但在一定條件下挣郭，MyISAM表也支持查詢和插入操作的并發(fā)進(jìn)行迄埃。

一般MyISAM引擎的表也支持查詢和插入操作的并發(fā)進(jìn)行（原則上數(shù)據(jù)表有一個(gè)讀鎖時(shí)，其它進(jìn)程無法對此表進(jìn)行更新操作）

MyISAM引擎有一個(gè)系統(tǒng)變量concurrent_insert兑障，專門用以控制其并發(fā)插入的行為侄非，其值分別可以為0、1或2：

a流译、concurrent_insert為0逞怨，不允許并發(fā)插入。 　　　　
b福澡、concurrent_insert為1叠赦，如果MyISAM表中沒有空洞（即表的中間沒有被刪除的行），MyISAM允許在一個(gè)進(jìn)程讀表的同時(shí)革砸，另一個(gè)進(jìn)程從表尾插入記錄除秀。這也是MySQL的默認(rèn)設(shè)置。 　　　　
c算利、concurrent_insert為2册踩，無論MyISAM表中有沒有空洞，都允許在表尾并發(fā)插入記錄笔时。

如果有讀寫請求同時(shí)進(jìn)行的話棍好，MYSQL將會(huì)優(yōu)先執(zhí)行寫操作仗岸。這樣MyISAM表在進(jìn)行大量的更新操作時(shí)（特別是更新的字段中存在索引的情況下）允耿，會(huì)造成查詢操作很難獲得讀鎖借笙，從而導(dǎo)致查詢阻塞。

我們還可以調(diào)整MyISAM讀寫的優(yōu)先級(jí)別：

　　a较锡、通過指定啟動(dòng)參數(shù)low-priority-updates业稼，使MyISAM引擎默認(rèn)給予讀請求以優(yōu)先的權(quán)利。
　　b、通過執(zhí)行命令SET LOW_PRIORITY_UPDATES=1，使該連接發(fā)出的更新請求優(yōu)先級(jí)降低梆惯。
　　c邮弹、通過指定INSERT、UPDATE秕磷、DELETE語句的LOW_PRIORITY屬性，降低該語句的優(yōu)先級(jí)。

MyISAM使用的是 flock 類的函數(shù)引镊，直接就是對整個(gè)文件進(jìn)行鎖定（叫做文件鎖定），MyISAM的數(shù)據(jù)表是按照單個(gè)文件存儲(chǔ)的篮条，可以針對單個(gè)表文件進(jìn)行鎖定弟头；

InnoDB使用的是 fcntl 類的函數(shù)，可以對文件中局部數(shù)據(jù)進(jìn)行鎖定（叫做行鎖定）涉茧，InnoDB是一整個(gè)文件赴恨，把索引、數(shù)據(jù)伴栓、結(jié)構(gòu)全部保存在 ibdata 文件里伦连，所以必須用行鎖定。

事物控制語句：

BEGIN或START TRANSACTION钳垮；顯式地開啟一個(gè)事務(wù)除师；     
COMMIT；也可以使用COMMIT WORK扔枫，不過二者是等價(jià)的汛聚。
COMMIT會(huì)提交事務(wù)，并使已對數(shù)據(jù)庫進(jìn)行的所有修改稱為永久性的短荐；      
ROLLBACK倚舀；有可以使用ROLLBACK WORK，不過二者是等價(jià)的忍宋『勖玻回滾會(huì)結(jié)束用戶的事務(wù)，并撤銷正在進(jìn)行的所有未提交的修改糠排；      
SAVEPOINT identifier舵稠；SAVEPOINT允許在事務(wù)中創(chuàng)建一個(gè)保存點(diǎn)，一個(gè)事務(wù)中可以有多個(gè)SAVEPOINT；     
RELEASE SAVEPOINT identifier哺徊；刪除一個(gè)事務(wù)的保存點(diǎn)室琢，當(dāng)沒有指定的保存點(diǎn)時(shí)，執(zhí)行該語句會(huì)拋出一個(gè)異常落追；     
ROLLBACK TO identifier盈滴；把事務(wù)回滾到標(biāo)記點(diǎn)；     
SET TRANSACTION轿钠；用來設(shè)置事務(wù)的隔離級(jí)別巢钓。
InnoDB存儲(chǔ)引擎提供事務(wù)的隔離級(jí)別有READ UNCOMMITTED、READ COMMITTED疗垛、REPEATABLE READ和SERIALIZABLE症汹。

MYSQL 事務(wù)處理主要有兩種方法：

1、用 BEGIN, ROLLBACK, COMMIT來實(shí)現(xiàn)

BEGIN 開始一個(gè)事務(wù)     
ROLLBACK 事務(wù)回滾    
COMMIT 事務(wù)確認(rèn)

2贷腕、直接用 SET 來改變 My

SQL 的自動(dòng)提交模式:

SET AUTOCOMMIT=0 禁止自動(dòng)提交     
SET AUTOCOMMIT=1 開啟自動(dòng)提交

注意點(diǎn)

1烈菌、如果事務(wù)中sql正確運(yùn)行，后面沒有commit花履，結(jié)果是不會(huì)更新到數(shù)據(jù)庫的芽世，所以需要手動(dòng)添加commit。

2诡壁、如果事務(wù)中部分sql語句出現(xiàn)錯(cuò)誤济瓢，那么錯(cuò)誤語句后面不會(huì)執(zhí)行。而我們可能會(huì)認(rèn)為正確操作會(huì)回滾撤銷妹卿，但是實(shí)際上并沒有撤銷正確的操作旺矾，此時(shí)如果再無錯(cuò)情況下進(jìn)行一次commit，之前的正確操作會(huì)生效夺克，數(shù)據(jù)庫會(huì)進(jìn)行更新箕宙。

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末，一起剝皮案震驚了整個(gè)濱河市铺纽，隨后出現(xiàn)的幾起案子柬帕，更是在濱河造成了極大的恐慌，老刑警劉巖狡门，帶你破解...
    沈念sama閱讀 211,290評(píng)論 6贊 491
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件陷寝，死亡現(xiàn)場離奇詭異，居然都是意外死亡其馏，警方通過查閱死者的電腦和手機(jī)凤跑，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,107評(píng)論 2贊 385
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來叛复，“玉大人仔引，你說我怎么就攤上這事扔仓。” “怎么了咖耘？”我有些...
    開封第一講書人閱讀 156,872評(píng)論 0贊 347
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵翘簇，是天一觀的道長。 經(jīng)常有香客問我鲤看，道長缘揪，這世上最難降的妖魔是什么耍群？ 我笑而不...
    開封第一講書人閱讀 56,415評(píng)論 1贊 283
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任义桂，我火速辦了婚禮，結(jié)果婚禮上蹈垢，老公的妹妹穿的比我還像新娘慷吊。我一直安慰自己，他們只是感情好曹抬，可當(dāng)我...
    茶點(diǎn)故事閱讀 65,453評(píng)論 6贊 385
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布溉瓶。 她就那樣靜靜地躺著，像睡著了一般谤民。 火紅的嫁衣襯著肌膚如雪堰酿。 梳的紋絲不亂的頭發(fā)上，一...
    開封第一講書人閱讀 49,784評(píng)論 1贊 290
  • 城市分裂傳說
    那天张足，我揣著相機(jī)與錄音触创，去河邊找鬼。 笑死为牍，一個(gè)胖子當(dāng)著我的面吹牛哼绑，可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播碉咆，決...
    沈念sama閱讀 38,927評(píng)論 3贊 406
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼抖韩，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了疫铜？” 一聲冷哼從身側(cè)響起茂浮，我...
    開封第一講書人閱讀 37,691評(píng)論 0贊 266
  • 萬榮殺人案實(shí)錄
    序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎壳咕，沒想到半個(gè)月后励稳，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
    沈念sama閱讀 44,137評(píng)論 1贊 303
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡囱井，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,472評(píng)論 2贊 326
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年驹尼，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片庞呕。...
    茶點(diǎn)故事閱讀 38,622評(píng)論 1贊 340
  • 活死人
    序言：一個(gè)原本活蹦亂跳的男人離奇死亡新翎，死狀恐怖程帕，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情地啰，我是刑警寧澤愁拭，帶...
    沈念sama閱讀 34,289評(píng)論 4贊 329
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布，位于F島的核電站亏吝，受9級(jí)特大地震影響岭埠，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜蔚鸥，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,887評(píng)論 3贊 312
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一惜论、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧止喷，春花似錦馆类、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,741評(píng)論 0贊 21
  • 一樁弒父案乾巧，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至预愤，卻和暖如春沟于，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背植康。 一陣腳步聲響...
    開封第一講書人閱讀 31,977評(píng)論 1贊 265
  • 情欲美人皮
    我被黑心中介騙來泰國打工旷太， 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人向图。 一個(gè)月前我還...
    沈念sama閱讀 46,316評(píng)論 2贊 360
  • 代替公主和親
    正文 我出身青樓泳秀，卻偏偏與公主長得像，于是被迫代替她去往敵國和親榄攀。 傳聞我的和親對象是個(gè)殘疾皇子嗜傅，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,490評(píng)論 2贊 348