一、索引
在之前,我對(duì)索引有以下的認(rèn)知:
- 索引可以加快數(shù)據(jù)庫的檢索速度;
- 表經(jīng)常進(jìn)行INSERT/UPDATE/DELETE操作就不要建立索引了,換言之:索引會(huì)降低插入、刪除、修改等維護(hù)任務(wù)的速度;
- 索引需要占物理和數(shù)據(jù)空間;
- 了解過索引的最左匹配原則;
- 知道索引的分類:聚集索引和非聚集索引;
- MySQL支持Hash索引和B+樹索引兩種;
看起來好像啥都知道,但面試讓你說的時(shí)候可能就GG了:
- 使用索引為什么可以加快數(shù)據(jù)庫的檢索速度???
- 為什么說索引會(huì)降低插入、刪除、修改等維護(hù)任務(wù)的速度;
- 索引的最左匹配原則指的是什么?
- Hash索引和B+樹索引有什么區(qū)別?主流的使用哪一個(gè)比較多?InnoDB存儲(chǔ)都支持嗎?
- 聚集索引和非聚集索引有什么區(qū)別?
- ........
1、聊聊索引的基礎(chǔ)知識(shí)
首先Mysql的基本存儲(chǔ)結(jié)構(gòu)是頁(記錄都存在頁里邊):
- 各個(gè)數(shù)據(jù)頁可以組成一個(gè)雙向鏈表;
- 而每個(gè)數(shù)據(jù)頁中的記錄又可以組成一個(gè)單向鏈表;
- 每個(gè)數(shù)據(jù)頁都會(huì)為存儲(chǔ)在它里邊兒的記錄生成一個(gè)頁目錄,在通過主鍵查找某條記錄的時(shí)候可以在頁目錄中使用二分法快速定位到對(duì)應(yīng)的槽,然后再遍歷該槽對(duì)應(yīng)分組中的記錄即可快速找到指定的記錄;
- 以其他列(非主鍵)作為搜索條件:只能從最小記錄開始依次遍歷單鏈表中的每條記錄。
所以說,如果我們寫select * from user where username = 'JAVA3y'這樣沒有進(jìn)行任何優(yōu)化的sql語句,默認(rèn)會(huì)這樣做:
- 定位到記錄所在的頁
- 需要遍歷雙向鏈表,找到所在的頁
- 從所在的頁內(nèi)中查找相應(yīng)的記錄
- 由于不是根據(jù)主鍵查詢,只能遍歷所在頁的單鏈表了
很明顯,在數(shù)據(jù)量很大的情況下這樣查找會(huì)很慢!
2、索引提高檢索速度
索引做了些什么可以讓我們查詢加快速度呢?
其實(shí)就是將無序的數(shù)據(jù)變成有序(相對(duì)):
要找到id為8的記錄簡(jiǎn)要步驟:
很明顯的是:沒有用索引我們是需要遍歷雙向鏈表來定位對(duì)應(yīng)的頁,現(xiàn)在通過"目錄"就可以很快地定位到對(duì)應(yīng)的頁上了!
其實(shí)底層結(jié)構(gòu)就是B+樹,B+樹作為樹的一種實(shí)現(xiàn),能夠讓我們很快地查找出對(duì)應(yīng)的記錄。
3、索引降低增刪改的速度
如果一棵普通的樹在極端的情況下,是能退化成鏈表的(樹的優(yōu)點(diǎn)就不復(fù)存在了)
B+樹是平衡樹的一種,是不會(huì)退化成鏈表的,樹的高度都是相對(duì)比較低的(基本符合矮矮胖胖(均衡)的結(jié)構(gòu))【這樣一來我們檢索的時(shí)間復(fù)雜度就是O(logn)】!從上一節(jié)的圖我們也可以看見,建立索引實(shí)際上就是建立一顆B+樹。
- B+樹是一顆平衡樹,如果我們對(duì)這顆樹增刪改的話,那肯定會(huì)破壞它的原有結(jié)構(gòu);
- 要維持平衡樹,就必須做額外的工作。正因?yàn)檫@些額外的工作開銷,導(dǎo)致索引會(huì)降低增刪改的速度;
4、哈希索引
除了B+樹之外,還有一種常見的是哈希索引。
哈希索引就是采用一定的哈希算法,把鍵值換算成新的哈希值,檢索時(shí)不需要類似B+樹那樣從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)逐級(jí)查找,只需一次哈希算法即可立刻定位到相應(yīng)的位置,速度非常快。
- 本質(zhì)上就是把鍵值換算成新的哈希值,根據(jù)這個(gè)哈希值來定位。
看起來哈希索引很牛逼啊,但其實(shí)哈希索引有好幾個(gè)局限(根據(jù)他本質(zhì)的原理可得):
- 哈希索引也沒辦法利用索引完成排序;
- 不支持最左匹配原則;
- 在有大量重復(fù)鍵值情況下,哈希索引的效率也是極低的---->哈希碰撞問題;
- 不支持范圍查詢;
5、InnoDB支持哈希索引嗎?
主流的還是使用B+樹索引比較多,對(duì)于哈希索引,InnoDB是自適應(yīng)哈希索引的(hash索引的創(chuàng)建由InnoDB存儲(chǔ)引擎引擎自動(dòng)優(yōu)化創(chuàng)建,我們干預(yù)不了)!
6、聚集和非聚集索引
簡(jiǎn)單概括:
- 聚集索引就是以主鍵創(chuàng)建的索引;
- 非聚集索引就是以非主鍵創(chuàng)建的索引;
區(qū)別:
- 聚集索引在葉子節(jié)點(diǎn)存儲(chǔ)的是表中的數(shù)據(jù);
- 非聚集索引在葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵和索引列;
- 使用非聚集索引查詢出數(shù)據(jù)時(shí),拿到葉子上的主鍵再去查到想要查找的數(shù)據(jù)。(拿到主鍵再查找這個(gè)過程叫做回表)
非聚集索引也叫做二級(jí)索引,不用糾結(jié)那么多名詞,將其等價(jià)就行了~
非聚集索引在建立的時(shí)候也未必是單列的,可以多個(gè)列來創(chuàng)建索引。
- 此時(shí)就涉及到了哪個(gè)列會(huì)走索引,哪個(gè)列不走索引的問題了(最左匹配原則-->后面有說)
- 創(chuàng)建多個(gè)單列(非聚集)索引的時(shí)候,會(huì)生成多個(gè)索引樹(所以過多創(chuàng)建索引會(huì)占用磁盤空間)
在創(chuàng)建多列索引中也涉及到了一種特殊的索引-->覆蓋索引
- 我們前面知道了,如果不是聚集索引,葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵+列值
- 最終還是要“回表”,也就是要通過主鍵再查找一次。這樣就會(huì)比較慢
- 覆蓋索引就是把要查詢出的列和索引是對(duì)應(yīng)的,不做回表操作!
比如說:
- 現(xiàn)在我創(chuàng)建了索引(username,age),在查詢數(shù)據(jù)的時(shí)候:select username , age from user where username = 'Java3y' and age = 20。
- 很明顯地知道,我們上邊的查詢是走索引的,并且,要查詢出的列在葉子節(jié)點(diǎn)都存在!所以,就不用回表了~
- 所以,能使用覆蓋索引就盡量使用吧~
7、索引最左匹配原則
最左匹配原則:
- 索引可以簡(jiǎn)單如一個(gè)列(a),也可以復(fù)雜如多個(gè)列(a, b, c, d),即聯(lián)合索引。
- 如果是聯(lián)合索引,那么key也由多個(gè)列組成,同時(shí),索引只能用于查找key是否存在(相等),遇到范圍查詢(>、<、between、like左匹配)等就不能進(jìn)一步匹配了,后續(xù)退化為線性查找。
- 因此,列的排列順序決定了可命中索引的列數(shù)。
例子:
- 如有索引(a, b, c, d),查詢條件a = 1 and b = 2 and c > 3 and d = 4,則會(huì)在每個(gè)節(jié)點(diǎn)依次命中a、b、c,無法命中d。(很簡(jiǎn)單:索引命中只能是相等的情況,不能是范圍匹配)
8、=、in自動(dòng)優(yōu)化順序
不需要考慮=、in等的順序,mysql會(huì)自動(dòng)優(yōu)化這些條件的順序,以匹配盡可能多的索引列。
例子:
- 如有索引(a, b, c, d),查詢條件c > 3 and b = 2 and a = 1 and d < 4與a = 1 and c > 3 and b = 2 and d < 4等順序都是可以的,MySQL會(huì)自動(dòng)優(yōu)化為a = 1 and b = 2 and c > 3 and d < 4,依次命中a、b、c。
9、索引總結(jié)
索引在數(shù)據(jù)庫中是一個(gè)非常重要的知識(shí)點(diǎn)!上面談的其實(shí)就是索引最基本的東西,要?jiǎng)?chuàng)建出好的索引要顧及到很多的方面:
- 1,最左前綴匹配原則。這是非常重要、非常重要、非常重要(重要的事情說三遍)的原則,MySQL會(huì)一直向右匹配直到遇到范圍查詢(>,<,BETWEEN,LIKE)就停止匹配。
- 3,盡量選擇區(qū)分度高的列作為索引,區(qū)分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重復(fù)的比率,比率越大我們掃描的記錄數(shù)就越少。
- 4,索引列不能參與計(jì)算,盡量保持列“干凈”。比如,F(xiàn)ROM_UNIXTIME(create_time) = '2016-06-06' 就不能使用索引,原因很簡(jiǎn)單,B+樹中存儲(chǔ)的都是數(shù)據(jù)表中的字段值,但是進(jìn)行檢索時(shí),需要把所有元素都應(yīng)用函數(shù)才能比較,顯然這樣的代價(jià)太大。所以語句要寫成 : create_time = UNIX_TIMESTAMP('2016-06-06')。
- 5,盡可能的擴(kuò)展索引,不要新建立索引。比如表中已經(jīng)有了a的索引,現(xiàn)在要加(a,b)的索引,那么只需要修改原來的索引即可。
- 6,單個(gè)多列組合索引和多個(gè)單列索引的檢索查詢效果不同,因?yàn)樵趫?zhí)行SQL時(shí),MySQL只能使用一個(gè)索引,會(huì)從多個(gè)單列索引中選擇一個(gè)限制最為嚴(yán)格的索引。
二、鎖
在mysql中的鎖看起來是很復(fù)雜的,因?yàn)橛幸淮蠖训臇|西和名詞:排它鎖,共享鎖,表鎖,頁鎖,間隙鎖,意向排它鎖,意向共享鎖,行鎖,讀鎖,寫鎖,樂觀鎖,悲觀鎖,死鎖。這些名詞有的博客又直接寫鎖的英文的簡(jiǎn)寫--->X鎖,S鎖,IS鎖,IX鎖,MMVC...
鎖的相關(guān)知識(shí)又跟存儲(chǔ)引擎,索引,事務(wù)的隔離級(jí)別都是關(guān)聯(lián)的....
這就給初學(xué)數(shù)據(jù)庫鎖的人帶來不少的麻煩~~~于是我下面就簡(jiǎn)單整理一下數(shù)據(jù)庫鎖的知識(shí)點(diǎn),希望大家看完會(huì)有所幫助。
1、為什么需要學(xué)習(xí)數(shù)據(jù)庫鎖知識(shí)
不少人在開發(fā)的時(shí)候,應(yīng)該很少會(huì)注意到這些鎖的問題,也很少會(huì)給程序加鎖(除了庫存這些對(duì)數(shù)量準(zhǔn)確性要求極高的情況下)
一般也就聽過常說的樂觀鎖和悲觀鎖,了解過基本的含義之后就沒了~~~
定心丸:即使我們不會(huì)這些鎖知識(shí),我們的程序在一般情況下還是可以跑得好好的。因?yàn)檫@些鎖數(shù)據(jù)庫隱式幫我們加了:
- 對(duì)于UPDATE、DELETE、INSERT語句,InnoDB會(huì)自動(dòng)給涉及數(shù)據(jù)集加排他鎖(X);
- MyISAM在執(zhí)行查詢語句SELECT前,會(huì)自動(dòng)給涉及的所有表加讀鎖,在執(zhí)行更新操作(UPDATE、DELETE、INSERT等)前,會(huì)自動(dòng)給涉及的表加寫鎖,這個(gè)過程并不需要用戶干預(yù);
只會(huì)在某些特定的場(chǎng)景下才需要手動(dòng)加鎖,學(xué)習(xí)數(shù)據(jù)庫鎖知識(shí)就是為了:
- 能讓我們?cè)谔囟ǖ膱?chǎng)景下派得上用場(chǎng)
- 更好把控自己寫的程序
- 在跟別人聊數(shù)據(jù)庫技術(shù)的時(shí)候可以搭上幾句話
- 構(gòu)建自己的知識(shí)庫體系!在面試的時(shí)候不虛
2、表鎖簡(jiǎn)單介紹
首先,從鎖的粒度,我們可以分成兩大類:
- 表鎖開銷小,加鎖快;不會(huì)出現(xiàn)死鎖;鎖定力度大,發(fā)生鎖沖突概率高,并發(fā)度最低;
- 行鎖開銷大,加鎖慢;會(huì)出現(xiàn)死鎖;鎖定粒度小,發(fā)生鎖沖突的概率低,并發(fā)度高;
不同的存儲(chǔ)引擎支持的鎖粒度是不一樣的:
- InnoDB行鎖和表鎖都支持!
- MyISAM只支持表鎖!
InnoDB只有通過索引條件檢索數(shù)據(jù)才使用行級(jí)鎖,否則,InnoDB將使用表鎖
- 也就是說,InnoDB的行鎖是基于索引的!
表鎖下又分為兩種模式:
- 表讀鎖(Table Read Lock)
- 表寫鎖(Table Write Lock)
- 從下圖可以清晰看到,在表讀鎖和表寫鎖的環(huán)境下:讀讀不阻塞,讀寫阻塞,寫寫阻塞!
- 讀讀不阻塞:當(dāng)前用戶在讀數(shù)據(jù),其他的用戶也在讀數(shù)據(jù),不會(huì)加鎖
- 讀寫阻塞:當(dāng)前用戶在讀數(shù)據(jù),其他的用戶不能修改當(dāng)前用戶讀的數(shù)據(jù),會(huì)加鎖!
- 寫寫阻塞:當(dāng)前用戶在修改數(shù)據(jù),其他的用戶不能修改當(dāng)前用戶正在修改的數(shù)據(jù),會(huì)加鎖!
從上面已經(jīng)看到了:讀鎖和寫鎖是互斥的,讀寫操作是串行。
- 如果某個(gè)進(jìn)程想要獲取讀鎖,同時(shí)另外一個(gè)進(jìn)程想要獲取寫鎖。在mysql里邊,寫鎖是優(yōu)先于讀鎖的!
- 寫鎖和讀鎖優(yōu)先級(jí)的問題是可以通過參數(shù)調(diào)節(jié)的:max_write_lock_count和low-priority-updates
值得注意的是:
- MyISAM可以支持查詢和插入操作的并發(fā)進(jìn)行??梢酝ㄟ^系統(tǒng)變量concurrent_insert來指定哪種模式,在MyISAM中它默認(rèn)是:如果MyISAM表中沒有空洞(即表的中間沒有被刪除的行),MyISAM允許在一個(gè)進(jìn)程讀表的同時(shí),另一個(gè)進(jìn)程從表尾插入記錄。
- 但是InnoDB存儲(chǔ)引擎是不支持的!
3、樂觀鎖和悲觀鎖
無論是Read committed還是Repeatable read隔離級(jí)別,都是為了解決讀寫沖突的問題。
單純?cè)赗epeatable read隔離級(jí)別下我們來考慮一個(gè)問題:
此時(shí),用戶李四的操作就丟失掉了:
- 丟失更新:一個(gè)事務(wù)的更新覆蓋了其它事務(wù)的更新結(jié)果。
(ps:暫時(shí)沒有想到比較好的例子來說明更新丟失的問題,雖然上面的例子也是更新丟失,但一定程度上是可接受的..不知道有沒有人能想到不可接受的更新丟失例子呢...)
解決的方法:
- 使用Serializable隔離級(jí)別,事務(wù)是串行執(zhí)行的!
- 樂觀鎖
- 悲觀鎖
- 樂觀鎖是一種思想,具體實(shí)現(xiàn)是,表中有一個(gè)版本字段,第一次讀的時(shí)候,獲取到這個(gè)字段。處理完業(yè)務(wù)邏輯開始更新的時(shí)候,需要再次查看該字段的值是否和第一次的一樣。如果一樣更新,反之拒絕。之所以叫樂觀,因?yàn)檫@個(gè)模式?jīng)]有從數(shù)據(jù)庫加鎖,等到更新的時(shí)候再判斷是否可以更新。
- 悲觀鎖是數(shù)據(jù)庫層面加鎖,都會(huì)阻塞去等待鎖。
3.1、悲觀鎖
所以,按照上面的例子。我們使用悲觀鎖的話其實(shí)很簡(jiǎn)單(手動(dòng)加行鎖就行了):
- select * from xxxx for update
在select 語句后邊加了 for update相當(dāng)于加了排它鎖(寫鎖),加了寫鎖以后,其他的事務(wù)就不能對(duì)它修改了!需要等待當(dāng)前事務(wù)修改完之后才可以修改.
- 也就是說,如果張三使用select ... for update,李四就無法對(duì)該條記錄修改了~
3.2、樂觀鎖
樂觀鎖不是數(shù)據(jù)庫層面上的鎖,是需要自己手動(dòng)去加的鎖。一般我們添加一個(gè)版本字段來實(shí)現(xiàn):
具體過程是這樣的:
張三select * from table --->會(huì)查詢出記錄出來,同時(shí)會(huì)有一個(gè)version字段
李四select * from table --->會(huì)查詢出記錄出來,同時(shí)會(huì)有一個(gè)version字段
李四對(duì)這條記錄做修改:update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version},判斷之前查詢到的version與現(xiàn)在的數(shù)據(jù)的version進(jìn)行比較,同時(shí)會(huì)更新version字段
此時(shí)數(shù)據(jù)庫記錄如下:
張三也對(duì)這條記錄修改:update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version},但失敗了!因?yàn)楫?dāng)前數(shù)據(jù)庫中的版本跟查詢出來的版本不一致!
4、間隙鎖GAP
當(dāng)我們用范圍條件檢索數(shù)據(jù)而不是相等條件檢索數(shù)據(jù),并請(qǐng)求共享或排他鎖時(shí),InnoDB會(huì)給符合范圍條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖;對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄,叫做“間隙(GAP)”。InnoDB也會(huì)對(duì)這個(gè)“間隙”加鎖,這種鎖機(jī)制就是所謂的間隙鎖。
值得注意的是:間隙鎖只會(huì)在Repeatable read隔離級(jí)別下使用~
例子:假如emp表中只有101條記錄,其empid的值分別是1,2,...,100,101
Select * from emp where empid > 100 for update;
上面是一個(gè)范圍查詢,InnoDB不僅會(huì)對(duì)符合條件的empid值為101的記錄加鎖,也會(huì)對(duì)empid大于101(這些記錄并不存在)的“間隙”加鎖。
InnoDB使用間隙鎖的目的有兩個(gè):
- 為了防止幻讀(上面也說了,Repeatable read隔離級(jí)別下再通過GAP鎖即可避免了幻讀)
- 滿足恢復(fù)和復(fù)制的需要MySQL的恢復(fù)機(jī)制要求:在一個(gè)事務(wù)未提交前,其他并發(fā)事務(wù)不能插入滿足其鎖定條件的任何記錄,也就是不允許出現(xiàn)幻讀
5、死鎖
并發(fā)的問題就少不了死鎖,在MySQL中同樣會(huì)存在死鎖的問題。
但一般來說MySQL通過回滾幫我們解決了不少死鎖的問題了,但死鎖是無法完全避免的,可以通過以下的經(jīng)驗(yàn)參考,來盡可能少遇到死鎖:
- 1)以固定的順序訪問表和行。比如對(duì)兩個(gè)job批量更新的情形,簡(jiǎn)單方法是對(duì)id列表先排序,后執(zhí)行,這樣就避免了交叉等待鎖的情形;將兩個(gè)事務(wù)的sql順序調(diào)整為一致,也能避免死鎖。
- 2)大事務(wù)拆小。大事務(wù)更傾向于死鎖,如果業(yè)務(wù)允許,將大事務(wù)拆小。
- 3)在同一個(gè)事務(wù)中,盡可能做到一次鎖定所需要的所有資源,減少死鎖概率。
- 4)降低隔離級(jí)別。如果業(yè)務(wù)允許,將隔離級(jí)別調(diào)低也是較好的選擇,比如將隔離級(jí)別從RR調(diào)整為RC,可以避免掉很多因?yàn)間ap鎖造成的死鎖。
- 5)為表添加合理的索引??梢钥吹饺绻蛔咚饕龑?huì)為表的每一行記錄添加上鎖,死鎖的概率大大增大。
6、鎖總結(jié)
上面說了一大堆關(guān)于MySQL數(shù)據(jù)庫鎖的東西,現(xiàn)在來簡(jiǎn)單總結(jié)一下。
表鎖其實(shí)我們程序員是很少關(guān)心它的:
- 在MyISAM存儲(chǔ)引擎中,當(dāng)執(zhí)行SQL語句的時(shí)候是自動(dòng)加的。
- 在InnoDB存儲(chǔ)引擎中,如果沒有使用索引,表鎖也是自動(dòng)加的。
現(xiàn)在我們大多數(shù)使用MySQL都是使用InnoDB,InnoDB支持行鎖:
- 共享鎖--讀鎖--S鎖
- 排它鎖--寫鎖--X鎖
在默認(rèn)的情況下,select是不加任何行鎖的~事務(wù)可以通過以下語句顯示給記錄集加共享鎖或排他鎖。
- 共享鎖(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
- 排他鎖(X):SELECT * FROM table_name WHERE ... FOR UPDATE。
InnoDB基于行鎖還實(shí)現(xiàn)了MVCC多版本并發(fā)控制,MVCC在隔離級(jí)別下的Read committed和Repeatable read下工作。MVCC能夠?qū)崿F(xiàn)讀寫不阻塞!
InnoDB實(shí)現(xiàn)的Repeatable read隔離級(jí)別配合GAP間隙鎖已經(jīng)避免了幻讀!
- 樂觀鎖其實(shí)是一種思想,正如其名:認(rèn)為不會(huì)鎖定的情況下去更新數(shù)據(jù),如果發(fā)現(xiàn)不對(duì)勁,才不更新(回滾)。在數(shù)據(jù)庫中往往添加一個(gè)version字段來實(shí)現(xiàn)。
- 悲觀鎖用的就是數(shù)據(jù)庫的行鎖,認(rèn)為數(shù)據(jù)庫會(huì)發(fā)生并發(fā)沖突,直接上來就把數(shù)據(jù)鎖住,其他事務(wù)不能修改,直至提交了當(dāng)前事務(wù)
三、總結(jié)
本文主要介紹了數(shù)據(jù)庫中的兩個(gè)比較重要的知識(shí)點(diǎn):索引和鎖。他倆可以說息息相關(guān)的,鎖會(huì)涉及到很多關(guān)于索引的知識(shí)~