Mysql 索引覆蓋及回表查詢

在innoDB中，有兩大索引類，分別是

讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來自于我們對這個(gè)行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡單的方式提供給客戶，將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長期合作伙伴，公司提供的服務(wù)項(xiàng)目有：域名注冊、雅安服務(wù)器托管、營銷軟件、網(wǎng)站建設(shè)、匯川網(wǎng)站維護(hù)、網(wǎng)站推廣。

執(zhí)行上述語句，執(zhí)行過程如下圖

從圖中，我們可以看出，掃了兩個(gè)索引樹

（1）先從普通索引name找到lisi

（2）再根據(jù)主鍵值9，再在聚集索引中找到行記錄。

這就是回表查詢，先在普通索引中找到主鍵值，再在聚集索引中找到行記錄。

很顯然，在一棵索引樹上就能獲取SQL所需的所有列數(shù)據(jù)的，就是索引覆蓋。

如下語句

很顯然，我們可以直接在name索引上直接找到id,name，不用再去回表。

而且我們通過explain的extra屬性也能觀察到

像我們開頭的SQL語句

我們只需要在name索引中再加個(gè)sex，name(name,sex)，這樣變成了聯(lián)合索引，也是索引覆蓋。

mysql函數(shù)可以返回多個(gè)值嗎

用存儲(chǔ)過程算了，函數(shù)不能返回一個(gè)值，如果要返回多值的話，那就返回一個(gè)table,用存儲(chǔ)過程同樣能達(dá)到效果

CREATE PROCEDURE `goodcheck`(

in xxx int

in xxx

...

out xxx ..

out xxx char

)

begin

select sum(productstockinfo.num) into production from productstockinfo where pro_id=productId;

end;

在Mysql中，把多個(gè)值以字符串組合的方式保存到一列和把各個(gè)值單獨(dú)保存到一條記錄的綜合效率分析。

索引的目的在于提高查詢效率，可以類比字典，如果要查“mysql”這個(gè)單詞，我們肯定需要定位到m字母，然后從下往下找到y(tǒng)字母，再找到剩下的sql。如果沒有索引，那么你可能需要把所有單詞看一遍才能找到你想要的。

1.索引的優(yōu)點(diǎn)

假設(shè)你擁有三個(gè)未索引的表t1、t2和t3，每個(gè)表都分別包含數(shù)據(jù)列i1、i2和i3，并且每個(gè)表都包含了1000條數(shù)據(jù)行，其序號從1到1000。查找某些值匹配的數(shù)據(jù)行組合的查詢可能如下所示：

SELECT t1.i1, t2.i2, t3.i3 FROM t1, t2, t3 WHERE t1.i1 = t2.i2 AND t2.i1 = t3.i3;

這個(gè)查詢的結(jié)果應(yīng)該是1000行，每個(gè)數(shù)據(jù)行包含三個(gè)相等的值。如果在沒有索引的情況下處理這個(gè)查詢，那么如果我們不對這些表進(jìn)行全部地掃描，我們是沒有辦法知道哪些數(shù)據(jù)行含有哪些值的。因此你必須嘗試所有的組合來查找符合WHERE條件的記錄?？赡艿慕M合的數(shù)量是1000 x 1000 x 1000（10億！），它是匹配記錄的數(shù)量的一百萬倍。這就浪費(fèi)了大量的工作。這個(gè)例子顯示，如果沒有使用索引，隨著表的記錄不斷增長，處理這些表的聯(lián)結(jié)所花費(fèi)的時(shí)間增長得更快，導(dǎo)致性能很差。我們可以通過索引這些數(shù)據(jù)表來顯著地提高速度，因?yàn)樗饕尣樵儾捎萌缦滤镜姆绞絹硖幚恚?/p>

1．選擇表t1中的第一行并查看該數(shù)據(jù)行的值。

2．使用表t2上的索引，直接定位到與t1的值匹配的數(shù)據(jù)行。類似地，使用表t3上的索引，直接定位到與表t2的值匹配的數(shù)據(jù)行。

3．處理表t1的下一行并重復(fù)前面的過程。執(zhí)行這樣的操作直到t1中的所有數(shù)據(jù)行都被檢查過。

在這種情況下，我們?nèi)匀粚Ρ韙1執(zhí)行了完整的掃描，但是我們可以在t2和t3上執(zhí)行索引查找，從這些表中直接地獲取數(shù)據(jù)行。理論上采用這種方式運(yùn)行上面的查詢會(huì)快一百萬倍。當(dāng)然這個(gè)例子是為了得出結(jié)論來人為建立的。然而，它解決的問題卻是現(xiàn)實(shí)的，給沒有索引的表添加索引通常會(huì)獲得驚人的性能提高。

-

2.索引的代價(jià)

首先，索引加快了檢索的速度，但是減慢了插入和刪除的速度，同時(shí)還減慢了更新被索引的數(shù)據(jù)列中的值的速度。也就是說，索引減慢了大多數(shù)涉及寫操作的速度。發(fā)生這種現(xiàn)象的原因在于寫入一條記錄的時(shí)候不但需要寫入數(shù)據(jù)行，還需要改變所有的索引。數(shù)據(jù)表帶有的索引越多，需要做出的修改就越多，平均性能的降低程度也就越大。在本文的”高效率載入數(shù)據(jù)”部分中，我們將更細(xì)致地了解這些現(xiàn)象并找出處理方法。

其次，索引會(huì)花費(fèi)磁盤空間，多個(gè)索引相應(yīng)地花費(fèi)更多的磁盤空間。這可能導(dǎo)致更快地到達(dá)數(shù)據(jù)表的大小限制：

· 對于MyISAM表，頻繁地索引可能引起索引文件比數(shù)據(jù)文件更快地達(dá)到最大限制。

· 對于BDB表，它把數(shù)據(jù)和索引值一起存儲(chǔ)在同一個(gè)文件中，添加索引引起這種表更快地達(dá)到最大文件限制。

· 在InnoDB的共享表空間中分配的所有表都競爭使用相同的公共空間池，因此添加索引會(huì)更快地耗盡表空間中的存儲(chǔ)。但是，與MyISAM和BDB表使用的文件不同，InnoDB共享表空間并不受操作系統(tǒng)的文件大小限制，因?yàn)槲覀兛梢园阉渲贸墒褂枚鄠€(gè)文件。只要有額外的磁盤空間，你就可以通過添加新組件來擴(kuò)展表空間。

使用單獨(dú)表空間的InnoDB表與BDB表受到的約束是一樣的，因?yàn)樗臄?shù)據(jù)和索引值都存儲(chǔ)在單個(gè)文件中。

這些要素的實(shí)際含義是：如果你不需要使用特殊的索引幫助查詢執(zhí)行得更快，就不要建立索引。

3.選擇索引

假設(shè)你已經(jīng)知道了建立索引的語法，但是語法不會(huì)告訴你數(shù)據(jù)表應(yīng)該如何索引。這要求我們考慮數(shù)據(jù)表的使用方式。這一部分指導(dǎo)你如何識別出用于索引的備選數(shù)據(jù)列，以及如何最好地建立索引：

用于搜索、排序和分組的索引數(shù)據(jù)列并不僅僅是用于輸出顯示的。換句話說，用于索引的最好的備選數(shù)據(jù)列是那些出現(xiàn)在WHERE子句、join子句、ORDER BY或GROUP BY子句中的列。僅僅出現(xiàn)在SELECT關(guān)鍵字后面的輸出數(shù)據(jù)列列表中的數(shù)據(jù)列不是很好的備選列：

SELECT col_a - 不是備選列 FROM tbl1 LEFT JOIN tbl2 ON tbl1.col_b = tbl2.col_c - 備選列 WHERE col_d = expr; - 備選列

當(dāng)然，顯示的數(shù)據(jù)列與WHERE子句中使用的數(shù)據(jù)列也可能相同。我們的觀點(diǎn)是輸出列表中的數(shù)據(jù)列本質(zhì)上不是用于索引的很好的備選列。

Join子句或WHERE子句中類似col1 = col2形式的表達(dá)式中的數(shù)據(jù)列都是特別好的索引備選列。前面顯示的查詢中的col_b和col_c就是這樣的例子。如果MySQL能夠利用聯(lián)結(jié)列來優(yōu)化查詢，它一定會(huì)通過減少整表掃描來大幅度減少潛在的表－行組合。

考慮數(shù)據(jù)列的基數(shù)（cardinality）?；鶖?shù)是數(shù)據(jù)列所包含的不同值的數(shù)量。例如，某個(gè)數(shù)據(jù)列包含值1、3、7、4、7、3，那么它的基數(shù)就是4。索引的基數(shù)相對于數(shù)據(jù)表行數(shù)較高（也就是說，列中包含很多不同的值，重復(fù)的值很少）的時(shí)候，它的工作效果最好。如果某數(shù)據(jù)列含有很多不同的年齡，索引會(huì)很快地分辨數(shù)據(jù)行。如果某個(gè)數(shù)據(jù)列用于記錄性別（只有”M”和”F”兩種值），那么索引的用處就不大。如果值出現(xiàn)的幾率幾乎相等，那么無論搜索哪個(gè)值都可能得到一半的數(shù)據(jù)行。在這些情況下，最好根本不要使用索引，因?yàn)椴樵儍?yōu)化器發(fā)現(xiàn)某個(gè)值出現(xiàn)在表的數(shù)據(jù)行中的百分比很高的時(shí)候，它一般會(huì)忽略索引，進(jìn)行全表掃描。慣用的百分比界線是”30%”?，F(xiàn)在查詢優(yōu)化器更加復(fù)雜，把其它一些因素也考慮進(jìn)去了，因此這個(gè)百分比并不是MySQL決定選擇使用掃描還是索引的唯一因素。

索引較短的值。盡可能地使用較小的數(shù)據(jù)類型。例如，如果MEDIUMINT足夠保存你需要存儲(chǔ)的值，就不要使用BIGINT數(shù)據(jù)列。如果你的值不會(huì)長于25個(gè)字符，就不要使用CHAR(100)。較小的值通過幾個(gè)方面改善了索引的處理速度：

· 較短的值可以更快地進(jìn)行比較，因此索引的查找速度更快了。

· 較小的值導(dǎo)致較小的索引，需要更少的磁盤I/O。

· 使用較短的鍵值的時(shí)候，鍵緩存中的索引塊（block）可以保存更多的鍵值。MySQL可以在內(nèi)存中一次保持更多的鍵，在不需要從磁盤讀取額外的索引塊的情況下，提高鍵值定位的可能性。

對于InnoDB和BDB等使用聚簇索引（clustered index）的存儲(chǔ)引擎來說，保持主鍵（primary key）短小的優(yōu)勢更突出。聚簇索引中數(shù)據(jù)行和主鍵值存儲(chǔ)在一起（聚簇在一起）。其它的索引都是次級索引；它們存儲(chǔ)主鍵值和次級索引值。次級索引屈從主鍵值，它們被用于定位數(shù)據(jù)行。這暗示主鍵值都被復(fù)制到每個(gè)次級索引中，因此如果主鍵值很長，每個(gè)次級索引就需要更多的額外空間。

索引字符串值的前綴（prefixe）。如果你需要索引一個(gè)字符串?dāng)?shù)據(jù)列，那么最好在任何適當(dāng)?shù)那闆r下都應(yīng)該指定前綴長度。例如，如果有CHAR(200)數(shù)據(jù)列，如果前面10個(gè)或20個(gè)字符都不同，就不要索引整個(gè)數(shù)據(jù)列。索引前面10個(gè)或20個(gè)字符會(huì)節(jié)省大量的空間，并且可能使你的查詢速度更快。通過索引較短的值，你可以獲得那些與比較速度和磁盤I/O節(jié)省相關(guān)的好處。當(dāng)然你也需要利用常識。僅僅索引某個(gè)數(shù)據(jù)列的第一個(gè)字符串可能用處不大，因?yàn)槿绻@樣操作，那么在索引中不會(huì)有太多的唯一值。

你可以索引CHAR、VARCHAR、BINARY、VARBINARY、BLOB和TEXT數(shù)據(jù)列的前綴。

使用最左（leftmost）前綴。建立多列復(fù)合索引的時(shí)候，你實(shí)際上建立了MySQL可以使用的多個(gè)索引。復(fù)合索引可以作為多個(gè)索引使用，因?yàn)樗饕凶钭筮叺牧屑隙伎梢杂糜谄ヅ鋽?shù)據(jù)行。這種列集合被稱為”最左前綴”（它與索引某個(gè)列的前綴不同，那種索引把某個(gè)列的前面幾個(gè)字符作為索引值）。

假設(shè)你在表的state、city和zip數(shù)據(jù)列上建立了復(fù)合索引。索引中的數(shù)據(jù)行按照state/city/zip次序排列，因此它們也會(huì)自動(dòng)地按照state/city和state次序排列。這意味著，即使你在查詢中只指定了state值，或者指定state和city值，MySQL也可以使用這個(gè)索引。因此，這個(gè)索引可以被用于搜索如下所示的數(shù)據(jù)列組合：

state, city, zip state, city state

MySQL不能利用這個(gè)索引來搜索沒有包含在最左前綴的內(nèi)容。例如，如果你按照city或zip來搜索，就不會(huì)使用到這個(gè)索引。如果你搜索給定的state和具體的ZIP代碼（索引的1和3列），該索引也是不能用于這種組合值的，盡管MySQL可以利用索引來查找匹配的state從而縮小搜索的范圍。

不要過多地索引。不要認(rèn)為”索引越多，性能越高”，不要對每個(gè)數(shù)據(jù)列都進(jìn)行索引。我們在前面提到過，每個(gè)額外的索引都會(huì)花費(fèi)更多的磁盤空間，并降低寫操作的性能。當(dāng)你修改表的內(nèi)容的時(shí)候，索引就必須被更新，甚至可能重新整理。如果你的索引很少使用或永不使用，你就沒有必要減小表的修改操作的速度。此外，為檢索操作生成執(zhí)行計(jì)劃的時(shí)候，MySQL會(huì)考慮索引。建立額外的索引會(huì)給查詢優(yōu)化器增加更多的工作量。如果索引太多，有可能（未必）出現(xiàn)MySQL選擇最優(yōu)索引失敗的情況。維護(hù)自己必須的索引可以幫助查詢優(yōu)化器來避免這類錯(cuò)誤。

如果你考慮給已經(jīng)索引過的表添加索引，那么就要考慮你將增加的索引是否是已有的多列索引的最左前綴。如果是這樣的，不用增加索引，因?yàn)橐呀?jīng)有了（例如，如果你在state、city和zip上建立了索引，那么沒有必要再增加state的索引）。

讓索引類型與你所執(zhí)行的比較的類型相匹配。在你建立索引的時(shí)候，大多數(shù)存儲(chǔ)引擎會(huì)選擇它們將使用的索引實(shí)現(xiàn)。例如，InnoDB通常使用B樹索引。MySQL也使用B樹索引，它只在三維數(shù)據(jù)類型上使用R樹索引。但是，MEMORY存儲(chǔ)引擎支持散列索引和B樹索引，并允許你選擇使用哪種索引。為了選擇索引類型，需要考慮在索引數(shù)據(jù)列上將執(zhí)行的比較操作類型：

· 對于散列（hash）索引，會(huì)在每個(gè)數(shù)據(jù)列值上應(yīng)用散列函數(shù)。生成的結(jié)果散列值存儲(chǔ)在索引中，并用于執(zhí)行查詢。散列函數(shù)實(shí)現(xiàn)的算法類似于為不同的輸入值生成不同的散列值。使用散列值的好處是散列值比原始值的比較效率更高。散列索引用于執(zhí)行=或=操作等精確匹配的時(shí)候速度非?？臁５菍τ诓樵円粋€(gè)值的范圍效果就非常差了：

id 30 weight BETWEEN 100 AND 150

· B樹索引可以用于高效率地執(zhí)行精確的或者基于范圍（使用操作、=、=、=、、、!=和BETWEEN）的比較。B樹索引也可以用于LIKE模式匹配，前提是該模式以文字串而不是通配符開頭。

如果你使用的MEMORY數(shù)據(jù)表只進(jìn)行精確值查詢，散列索引是很好的選擇。這是MEMORY表使用的默認(rèn)的索引類型，因此你不需要特意指定。如果你希望在MEMORY表上執(zhí)行基于范圍的比較，應(yīng)該使用B樹索引。為了指定這種索引類型，需要給索引定義添加USING BTREE。例如：

CREATE TABLE lookup ( id INT NOT NULL, name CHAR(20), PRIMARY KEY USING BTREE (id) ) ENGINE = MEMORY;

如果你希望執(zhí)行的語句的類型允許，單個(gè)MEMORY表可以同時(shí)擁有散列索引和B樹索引，即使在同一個(gè)數(shù)據(jù)列上。

有些類型的比較不能使用索引。如果你只是通過把值傳遞到函數(shù)（例如STRCMP()）中來執(zhí)行比較操作，那么對它進(jìn)行索引就沒有價(jià)值。服務(wù)器必須計(jì)算出每個(gè)數(shù)據(jù)行的函數(shù)值，它會(huì)排除數(shù)據(jù)列上索引的使用。

使用慢查詢（slow-query）日志來識別執(zhí)行情況較差的查詢。這個(gè)日志可以幫助你找出從索引中受益的查詢。你可以直接查看日志（它是文本文件），或者使用mysqldumpslow工具來統(tǒng)計(jì)它的內(nèi)容。如果某個(gè)給定的查詢多次出現(xiàn)在”慢查詢”日志中，這就是一個(gè)線索，某個(gè)查詢可能沒有優(yōu)化編寫。你可以重新編寫它，使它運(yùn)行得更快。你要記住，在評估”慢查詢”日志的時(shí)候，”慢”是根據(jù)實(shí)際時(shí)間測定的，在負(fù)載較大的服務(wù)器上”慢查詢”日志中出現(xiàn)的查詢會(huì)多一些。

*4.建索引的幾大原則*

4.1.最左前綴匹配原則，非常重要的原則，mysql會(huì)一直向右匹配直到遇到范圍查詢(、、between、like)就停止匹配，比如a = 1 and b = 2 and c 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。

4.2.=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優(yōu)化器會(huì)幫你優(yōu)化成索引可以識別的形式

4.3.盡量選擇區(qū)分度高的列作為索引,區(qū)分度的公式是count(distinct col)/count(*)，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少，唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是0，那可能有人會(huì)問，這個(gè)比例有什么經(jīng)驗(yàn)值嗎？使用場景不同，這個(gè)值也很難確定，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄

4.4.索引列不能參與計(jì)算，保持列“干凈”，比如from_unixtime(create_time) = '2014-05-29'就不能使用到索引，原因很簡單，b+樹中存的都是數(shù)據(jù)表中的字段值，但進(jìn)行檢索時(shí)，需要把所有元素都應(yīng)用函數(shù)才能比較，顯然成本太大。所以語句應(yīng)該寫成create_time = unix_timestamp('2014-05-29');

4.5.盡量的擴(kuò)展索引，不要新建索引。比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可。

數(shù)據(jù)庫回表

一張用于用戶登錄的user表：id、userName、Password三個(gè)字段

再根據(jù)查出來的user信息去對比密碼是否正確

這時(shí)你發(fā)現(xiàn)username字段是唯一的又經(jīng)常作為where條件所以可以給username字段建一個(gè)索引，于是就給username建了一個(gè)普通的B+Tree索引。這時(shí)候就出問題的，因?yàn)镸ySQL的InnoDB使用聚簇索引，具體的數(shù)據(jù)只和主鍵索引放在一起，其他的索引只存儲(chǔ)了數(shù)據(jù)的地址（主鍵id）。比如上面的例子中，我根據(jù)username索引找到的只是一個(gè)username為admin這條數(shù)據(jù)的id而不是這條數(shù)據(jù)信息，所以要找到整條數(shù)據(jù)信息要根據(jù)得到的id再去找?？赐晟厦娴牧鞒蹋銘?yīng)該已經(jīng)發(fā)現(xiàn)問題了，我要通過username找到id，再根據(jù)id找整條數(shù)據(jù)，這里有兩個(gè)查找過程，這是影響效率的。就像上面的兩個(gè)查找過程就是回表了。

解決辦法

使用覆蓋索引可以解決上面所說的回表的問題。還是拿上面上面登錄的例子來說，其實(shí)登錄只需要判斷用戶名和密碼，如果user表中有其他用戶信息也是不需要的，那我們能不能只查詢一次就找到這個(gè)用戶名對應(yīng)的密碼呢。這個(gè)是可以的，上面所說的分兩步查找，第一步根據(jù)username查找是肯定不能少的，那我們只要把password和索引username放到一起就可以了。我們可以建立一個(gè)（username、password）的組合索引，這里username一定要放在前面，然后我們把sql語句改一下

這樣建立組合索引后根據(jù)username查找password，只要一步查找就可以查找到，因?yàn)閜assword已經(jīng)是username索引的一部分了，直接可以查出來，不再需要通過id找對應(yīng)的整條數(shù)據(jù)。覆蓋索引就是覆蓋了多個(gè)列（字段）的索引。

那我們應(yīng)該如何避免回表，因?yàn)槿绻玫搅嘶乇?，就需要二次查詢的過程，效率肯定更慢，很簡單，回表是因?yàn)橐樵兊淖侄卧诜蔷奂饕餂]有，所以在滿足需求的情況下，我們盡量使非聚集索引里有要查詢的索引字段。

在查詢時(shí)，可以盡量用聚集索引來查（也就是用主鍵來查詢），或者根據(jù)業(yè)務(wù)需求，建好的索引，滿足索引查詢字段。但是實(shí)際業(yè)務(wù)中，很難建立一個(gè)索引就能滿足所有查詢要求，所以，正常情況，回表也沒事，只要能用到索引也能大大加快查詢速度。

mysql 核心內(nèi)容-上

1、SQL語句執(zhí)行流程

MySQL大體上可分為Server層和存儲(chǔ)引擎層兩部分。

Server層：

連接器：TCP握手后服務(wù)器來驗(yàn)證登陸用戶身份，A用戶創(chuàng)建連接后，管理員對A用戶權(quán)限修改了也不會(huì)影響到已經(jīng)創(chuàng)建的鏈接權(quán)限，必須重新登陸。

查詢緩存：查詢后的結(jié)果存儲(chǔ)位置，MySQL8.0版本以后已經(jīng)取消，因?yàn)椴樵兙彺媸l繁，得不償失。

分析器：根據(jù)語法規(guī)則，判斷你輸入的這個(gè)SQL語句是否滿足MySQL語法。

優(yōu)化器：多種執(zhí)行策略可實(shí)現(xiàn)目標(biāo)，系統(tǒng)自動(dòng)選擇最優(yōu)進(jìn)行執(zhí)行。

執(zhí)行器：判斷是否有權(quán)限，將最終任務(wù)提交到存儲(chǔ)引擎。

存儲(chǔ)引擎層

負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取。其架構(gòu)模式是插件式的，支持InnoDB、MyISAM、Memory等多個(gè)存儲(chǔ)引擎?，F(xiàn)在最常用的存儲(chǔ)引擎是InnoDB，它從MySQL 5.5.5版本開始成為了默認(rèn)存儲(chǔ)引擎(經(jīng)常用的也是這個(gè))。

SQL執(zhí)行順序

2、BinLog、RedoLog、UndoLog

BinLog

BinLog是記錄所有數(shù)據(jù)庫表結(jié)構(gòu)變更（例如create、alter table）以及表數(shù)據(jù)修改(insert、update、delete)的二進(jìn)制日志，主從數(shù)據(jù)庫同步用到的都是BinLog文件。BinLog日志文件有三種模式。

STATEMENT 模式

內(nèi)容：binlog 記錄可能引起數(shù)據(jù)變更的 sql 語句

優(yōu)勢：該模式下，因?yàn)闆]有記錄實(shí)際的數(shù)據(jù)，所以日志量很少 IO 都消耗很低，性能是最優(yōu)的

劣勢：但有些操作并不是確定的，比如 uuid() 函數(shù)會(huì)隨機(jī)產(chǎn)生唯一標(biāo)識，當(dāng)依賴 binlog 回放時(shí)，該操作生成的數(shù)據(jù)與原數(shù)據(jù)必然是不同的，此時(shí)可能造成無法預(yù)料的后果。

ROW 模式

內(nèi)容：在該模式下，binlog 會(huì)記錄每次操作的源數(shù)據(jù)與修改后的目標(biāo)數(shù)據(jù)，StreamSets就要求該模式。

優(yōu)勢：可以絕對精準(zhǔn)的還原，從而保證了數(shù)據(jù)的安全與可靠，并且復(fù)制和數(shù)據(jù)恢復(fù)過程可以是并發(fā)進(jìn)行的

劣勢：缺點(diǎn)在于 binlog 體積會(huì)非常大，同時(shí)，對于修改記錄多、字段長度大的操作來說，記錄時(shí)性能消耗會(huì)很嚴(yán)重。閱讀的時(shí)候也需要特殊指令來進(jìn)行讀取數(shù)據(jù)。

MIXED 模式

內(nèi)容：是對上述STATEMENT 跟 ROW 兩種模式的混合使用。

細(xì)節(jié)：對于絕大部分操作，都是使用 STATEMENT 來進(jìn)行 binlog 沒有記錄，只有以下操作使用 ROW 來實(shí)現(xiàn)：表的存儲(chǔ)引擎為 NDB，使用了uuid() 等不確定函數(shù)，使用了 insert delay 語句，使用了臨時(shí)表

主從同步流程：

1、主節(jié)點(diǎn)必須啟用二進(jìn)制日志，記錄任何修改了數(shù)據(jù)庫數(shù)據(jù)的事件。

2、從節(jié)點(diǎn)開啟一個(gè)線程（I/O Thread)把自己扮演成 mysql 的客戶端，通過 mysql 協(xié)議，請求主節(jié)點(diǎn)的二進(jìn)制日志文件中的事件 。

3、主節(jié)點(diǎn)啟動(dòng)一個(gè)線程（dump Thread），檢查自己二進(jìn)制日志中的事件，跟對方請求的位置對比，如果不帶請求位置參數(shù)，則主節(jié)點(diǎn)就會(huì)從第一個(gè)日志文件中的第一個(gè)事件一個(gè)一個(gè)發(fā)送給從節(jié)點(diǎn)。

4、從節(jié)點(diǎn)接收到主節(jié)點(diǎn)發(fā)送過來的數(shù)據(jù)把它放置到中繼日志（Relay log）文件中。并記錄該次請求到主節(jié)點(diǎn)的具體哪一個(gè)二進(jìn)制日志文件內(nèi)部的哪一個(gè)位置（主節(jié)點(diǎn)中的二進(jìn)制文件會(huì)有多個(gè)）。

5、從節(jié)點(diǎn)啟動(dòng)另外一個(gè)線程（sql Thread ），把 Relay log 中的事件讀取出來，并在本地再執(zhí)行一次。

mysql默認(rèn)的復(fù)制方式是異步的，并且復(fù)制的時(shí)候是有并行復(fù)制能力的。主庫把日志發(fā)送給從庫后不管了，這樣會(huì)產(chǎn)生一個(gè)問題就是假設(shè)主庫掛了，從庫處理失敗了，這時(shí)候從庫升為主庫后，日志就丟失了。由此產(chǎn)生兩個(gè)概念。

全同步復(fù)制

主庫寫入binlog后強(qiáng)制同步日志到從庫，所有的從庫都執(zhí)行完成后才返回給客戶端，但是很顯然這個(gè)方式的話性能會(huì)受到嚴(yán)重影響。

半同步復(fù)制

半同步復(fù)制的邏輯是這樣，從庫寫入日志成功后返回ACK確認(rèn)給主庫，主庫收到至少一個(gè)從庫的確認(rèn)就認(rèn)為寫操作完成。

還可以延伸到由于主從配置不一樣、主庫大事務(wù)、從庫壓力過大、網(wǎng)絡(luò)震蕩等造成主備延遲，如何避免這個(gè)問題？主備切換的時(shí)候用可靠性優(yōu)先原則還是可用性優(yōu)先原則？如何判斷主庫Crash了？互為主備的情況下如何避免主備循環(huán)復(fù)制？被刪庫跑路了如何正確恢復(fù)？( o )… 感覺越來越扯到DBA的活兒上去了。

RedoLog

可以先通過下面demo理解：

飯點(diǎn)記賬可以把賬單寫在賬本上也可以寫在粉板上。有人賒賬或者還賬的話，一般有兩種做法：

1、直接把賬本翻出來，把這次賒的賬加上去或者扣除掉。

2、先在粉板上記下這次的賬，等打烊以后再把賬本翻出來核算。

生意忙時(shí)選后者，因?yàn)榍罢咛闊┝?。得在密密麻麻的記錄中找到這個(gè)人的賒賬總額信息，找到之后再拿出算盤計(jì)算，最后再將結(jié)果寫回到賬本上。

同樣在MySQL中如果每一次的更新操作都需要寫進(jìn)磁盤，然后磁盤也要找到對應(yīng)的那條記錄，然后再更新，整個(gè)過程IO成本、查找成本都很高。而粉板和賬本配合的整個(gè)過程就是MySQL用到的是Write-Ahead Logging 技術(shù)，它的關(guān)鍵點(diǎn)就是先寫日志，再寫磁盤。此時(shí)賬本 = BinLog，粉板 = RedoLog。

1、 記錄更新時(shí)，InnoDB引擎就會(huì)先把記錄寫到RedoLog（粉板）里面，并更新內(nèi)存。同時(shí)，InnoDB引擎會(huì)在空閑時(shí)將這個(gè)操作記錄更新到磁盤里面。

2、 如果更新太多RedoLog處理不了的時(shí)候，需先將RedoLog部分?jǐn)?shù)據(jù)寫到磁盤，然后擦除RedoLog部分?jǐn)?shù)據(jù)。RedoLog類似轉(zhuǎn)盤。

RedoLog有write pos 跟checkpoint

write pos ：是當(dāng)前記錄的位置，一邊寫一邊后移，寫到第3號文件末尾后就回到0號文件開頭。

check point：是當(dāng)前要擦除的位置，也是往后推移并且循環(huán)的，擦除記錄前要把記錄更新到數(shù)據(jù)文件。

write pos和check point之間的是粉板上還空著的部分，可以用來記錄新的操作。如果write pos追上checkpoint，表示粉板滿了，這時(shí)候不能再執(zhí)行新的更新，得停下來先擦掉一些記錄，把checkpoint推進(jìn)一下。

有了redo log，InnoDB就可以保證即使數(shù)據(jù)庫發(fā)生異常重啟，之前提交的記錄都不會(huì)丟失，這個(gè)能力稱為crash-safe。 redolog兩階段提交：為了讓binlog跟redolog兩份日志之間的邏輯一致。提交流程大致如下：

1 prepare階段 -- 2 寫binlog -- 3 commit

當(dāng)在2之前崩潰時(shí)，重啟恢復(fù)后發(fā)現(xiàn)沒有commit，回滾。備份恢復(fù)：沒有binlog 。一致

當(dāng)在3之前崩潰時(shí)，重啟恢復(fù)發(fā)現(xiàn)雖沒有commit，但滿足prepare和binlog完整，所以重啟后會(huì)自動(dòng)commit。備份：有binlog. 一致

binlog跟redolog區(qū)別：

redo log是InnoDB引擎特有的；binlog是MySQL的Server層實(shí)現(xiàn)的，所有引擎都可以使用。

redo log是物理日志，記錄的是在某個(gè)數(shù)據(jù)頁上做了什么修改；binlog是邏輯日志，記錄的是這個(gè)語句的原始邏輯，比如給ID=2這一行的c字段加1。

redo log是循環(huán)寫的，空間固定會(huì)用完；binlog是可以追加寫入的。追加寫是指binlog文件寫到一定大小后會(huì)切換到下一個(gè)，并不會(huì)覆蓋以前的日志。

UndoLog

UndoLog 一般是邏輯日志，主要分為兩種：

insert undo log

代表事務(wù)在insert新記錄時(shí)產(chǎn)生的undo log, 只在事務(wù)回滾時(shí)需要，并且在事務(wù)提交后可以被立即丟棄

update undo log

事務(wù)在進(jìn)行update或delete時(shí)產(chǎn)生的undo log; 不僅在事務(wù)回滾時(shí)需要，在快照讀時(shí)也需要；所以不能隨便刪除，只有在快速讀或事務(wù)回滾不涉及該日志時(shí)，對應(yīng)的日志才會(huì)被purge線程統(tǒng)一清除

3、MySQL中的索引

索引的常見模型有哈希表、有序數(shù)組和搜索樹。

哈希表：一種以KV存儲(chǔ)數(shù)據(jù)的結(jié)構(gòu)，只適合等值查詢，不適合范圍查詢。

有序數(shù)組：只適用于靜態(tài)存儲(chǔ)引擎，涉及到插入的時(shí)候比較麻煩。可以參考Java中的ArrayList。

搜索樹：按照數(shù)據(jù)結(jié)構(gòu)中的二叉樹來存儲(chǔ)數(shù)據(jù)，不過此時(shí)是N叉樹(B+樹)。廣泛應(yīng)用在存儲(chǔ)引擎層中。

B+樹比B樹優(yōu)勢在于：

B+ 樹非葉子節(jié)點(diǎn)存儲(chǔ)的只是索引，可以存儲(chǔ)的更多。B+樹比B樹更加矮胖，IO次數(shù)更少。

B+ 樹葉子節(jié)點(diǎn)前后管理，更加方便范圍查詢。同時(shí)結(jié)果都在葉子節(jié)點(diǎn)，查詢效率穩(wěn)定。

B+樹中更有利于對數(shù)據(jù)掃描，可以避免B樹的回溯掃描。

索引的優(yōu)點(diǎn)：

1、唯一索引可以保證每一行數(shù)據(jù)的唯一性

2、提高查詢速度

3、加速表與表的連接

4、顯著的減少查詢中分組和排序的時(shí)間

5、通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。

索引的缺點(diǎn)：

1、創(chuàng)建跟維護(hù)都需要耗時(shí)

2、創(chuàng)建索引時(shí)，需要對表加鎖，在鎖表的同時(shí)，可能會(huì)影響到其他的數(shù)據(jù)操作

3、 索引需要磁盤的空間進(jìn)行存儲(chǔ)，磁盤占用也很快。

4、當(dāng)對表中的數(shù)據(jù)進(jìn)行CRUD的時(shí)，也會(huì)觸發(fā)索引的維護(hù)，而維護(hù)索引需要時(shí)間，可能會(huì)降低數(shù)據(jù)操作性能

索引設(shè)計(jì)的原則不應(yīng)該：

1、索引不是越多越好。索引太多，維護(hù)索引需要時(shí)間跟空間。

2、 頻繁更新的數(shù)據(jù)，不宜建索引。

3、數(shù)據(jù)量小的表沒必要建立索引。

應(yīng)該：

1、重復(fù)率小的列建議生成索引。因?yàn)橹貜?fù)數(shù)據(jù)少，索引樹查詢更有效率，等價(jià)基數(shù)越大越好。

2、數(shù)據(jù)具有唯一性，建議生成唯一性索引。在數(shù)據(jù)庫的層面，保證數(shù)據(jù)正確性

3、頻繁group by、order by的列建議生成索引。可以大幅提高分組和排序效率

4、經(jīng)常用于查詢條件的字段建議生成索引。通過索引查詢，速度更快

索引失效的場景

1、模糊搜索：左模糊或全模糊都會(huì)導(dǎo)致索引失效，比如'%a'和'%a%'。但是右模糊是可以利用索引的，比如'a%' 。

2、隱式類型轉(zhuǎn)換：比如select * from t where name = xxx , name是字符串類型，但是沒有加引號，所以是由MySQL隱式轉(zhuǎn)換的，所以會(huì)讓索引失效 3、當(dāng)語句中帶有or的時(shí)候：比如select * from t where name=‘sw’ or age=14

4、不符合聯(lián)合索引的最左前綴匹配：(A,B,C)的聯(lián)合索引，你只where了C或B或只有B,C

關(guān)于索引的知識點(diǎn)：

主鍵索引：主鍵索引的葉子節(jié)點(diǎn)存的是整行數(shù)據(jù)信息。在InnoDB里，主鍵索引也被稱為聚簇索引（clustered index）。主鍵自增是無法保證完全自增的哦，遇到唯一鍵沖突、事務(wù)回滾等都可能導(dǎo)致不連續(xù)。

唯一索引：以唯一列生成的索引，該列不允許有重復(fù)值，但允許有空值(NULL)

普通索引跟唯一索引查詢性能：InnoDB的數(shù)據(jù)是按數(shù)據(jù)頁為單位來讀寫的，默認(rèn)每頁16KB，因此這兩種索引查詢數(shù)據(jù)性能差別微乎其微。

change buffer：普通索引用在更新過程的加速，更新的字段如果在緩存中，如果是普通索引則直接更新即可。如果是唯一索引需要將所有數(shù)據(jù)讀入內(nèi)存來確保不違背唯一性，所以盡量用普通索引。

非主鍵索引：非主鍵索引的葉子節(jié)點(diǎn)內(nèi)容是主鍵的值。在InnoDB里，非主鍵索引也被稱為二級索引（secondary index）

回表：先通過數(shù)據(jù)庫索引掃描出數(shù)據(jù)所在的行，再通過行主鍵id取出索引中未提供的數(shù)據(jù)，即基于非主鍵索引的查詢需要多掃描一棵索引樹。

覆蓋索引：如果一個(gè)索引包含（或者說覆蓋）所有需要查詢的字段的值，我們就稱之為覆蓋索引。

聯(lián)合索引：相對單列索引，組合索引是用多個(gè)列組合構(gòu)建的索引，一次性最多聯(lián)合16個(gè)。

最左前綴原則：對多個(gè)字段同時(shí)建立的組合索引(有順序，ABC，ACB是完全不同的兩種聯(lián)合索引) 以聯(lián)合索引(a,b,c)為例，建立這樣的索引相當(dāng)于建立了索引a、ab、abc三個(gè)索引。另外組合索引實(shí)際還是一個(gè)索引，并非真的創(chuàng)建了多個(gè)索引，只是產(chǎn)生的效果等價(jià)于產(chǎn)生多個(gè)索引。

索引下推：MySQL 5.6引入了索引下推優(yōu)化，可以在索引遍歷過程中，對索引中包含的字段先做判斷，過濾掉不符合條件的記錄，減少回表字?jǐn)?shù)。

索引維護(hù)：B+樹為了維護(hù)索引有序性涉及到頁分裂跟頁合并。增刪數(shù)據(jù)時(shí)需考慮頁空間利用率。

自增主鍵：一般會(huì)建立與業(yè)務(wù)無關(guān)的自增主鍵，不會(huì)觸發(fā)葉子節(jié)點(diǎn)分裂。

延遲關(guān)聯(lián)：通過使用覆蓋索引查詢返回需要的主鍵，再根據(jù)主鍵關(guān)聯(lián)原表獲得需要的數(shù)據(jù)。

InnoDB存儲(chǔ): * .frm文件是一份定義文件，也就是定義數(shù)據(jù)庫表是一張?jiān)趺礃拥谋怼?.ibd文件則是該表的索引，數(shù)據(jù)存儲(chǔ)文件，既該表的所有索引樹，所有行記錄數(shù)據(jù)都存儲(chǔ)在該文件中。

MyISAM存儲(chǔ)：* .frm文件是一份定義文件，也就是定義數(shù)據(jù)庫表是一張?jiān)趺礃拥谋怼? .MYD文件是MyISAM存儲(chǔ)引擎表的所有行數(shù)據(jù)的文件。* .MYI文件存放的是MyISAM存儲(chǔ)引擎表的索引相關(guān)數(shù)據(jù)的文件。MyISAM引擎下，表數(shù)據(jù)和表索引數(shù)據(jù)是分開存儲(chǔ)的。

MyISAM查詢：在MyISAM下，主鍵索引和輔助鍵索引都屬于非聚簇索引。查詢不管是走主鍵索引，還是非主鍵索引，在葉子結(jié)點(diǎn)得到的都是目的數(shù)據(jù)的地址，還需要通過該地址，才能在數(shù)據(jù)文件中找到目的數(shù)據(jù)。

PS：InnoDB支持聚簇索引，MyISAM不支持聚簇索引

4、SQL事務(wù)隔離級別

ACID的四個(gè)特性

原子性（Atomicity）：把多個(gè)操作放到一個(gè)事務(wù)中，保證這些操作要么都成功，要么都不成功

一致性（Consistency）：理解成一串對數(shù)據(jù)進(jìn)行操作的程序執(zhí)行下來，不會(huì)對數(shù)據(jù)產(chǎn)生不好的影響，比如憑空產(chǎn)生，或消失

隔離性（Isolation，又稱獨(dú)立性）：隔離性的意思就是多個(gè)事務(wù)之間互相不干擾，即使是并發(fā)事務(wù)的情況下，他們只是兩個(gè)并發(fā)執(zhí)行沒有交集，互不影響的東西；當(dāng)然實(shí)現(xiàn)中，也不一定需要這么完整隔離性，即不一定需要這么的互不干擾，有時(shí)候還是允許有部分干擾的。所以MySQL可以支持4種事務(wù)隔離性

持久性（Durability）：當(dāng)某個(gè)操作操作完畢了，那么結(jié)果就是這樣了，并且這個(gè)操作會(huì)持久化到日志記錄中

PS：ACID中C與CAP定理中C的區(qū)別

ACID的C著重強(qiáng)調(diào)單數(shù)據(jù)庫事務(wù)操作時(shí)，要保證數(shù)據(jù)的完整和正確性，數(shù)據(jù)不會(huì)憑空消失跟增加。CAP 理論中的C指的是對一個(gè)數(shù)據(jù)多個(gè)備份的讀寫一致性

事務(wù)操作可能會(huì)出現(xiàn)的數(shù)據(jù)問題

1、臟讀(dirty read)：B事務(wù)更改數(shù)據(jù)還未提交，A事務(wù)已經(jīng)看到并且用了。B事務(wù)如果回滾，則A事務(wù)做錯(cuò)了

2、 不可重復(fù)讀(non-repeatable read)：不可重復(fù)讀的重點(diǎn)是修改: 同樣的條件, 你讀取過的數(shù)據(jù), 再次讀取出來發(fā)現(xiàn)值不一樣了，只需要鎖住滿足條件的記錄

3、 幻讀(phantom read)：事務(wù)A先修改了某個(gè)表的所有紀(jì)錄的狀態(tài)字段為已處理，未提交；事務(wù)B也在此時(shí)新增了一條未處理的記錄，并提交了；事務(wù)A隨后查詢記錄，卻發(fā)現(xiàn)有一條記錄是未處理的造成幻讀現(xiàn)象，幻讀僅專指新插入的行?；米x會(huì)造成語義上的問題跟數(shù)據(jù)一致性問題。

4、 在可重復(fù)讀RR隔離級別下，普通查詢是快照讀，是不會(huì)看到別的事務(wù)插入的數(shù)據(jù)的。因此，幻讀在當(dāng)前讀下才會(huì)出現(xiàn)。要用間隙鎖解決此問題。

在說隔離級別之前，你首先要知道，你隔離得越嚴(yán)實(shí)，效率就會(huì)越低。因此很多時(shí)候，我們都要在二者之間尋找一個(gè)平衡點(diǎn)。SQL標(biāo)準(zhǔn)的事務(wù)隔離級別由低到高如下： 上圖從上到下的模式會(huì)導(dǎo)致系統(tǒng)的并行性能依次降低，安全性依次提高。

讀未提交：別人改數(shù)據(jù)的事務(wù)尚未提交，我在我的事務(wù)中也能讀到。

讀已提交(Oracle默認(rèn))：別人改數(shù)據(jù)的事務(wù)已經(jīng)提交，我在我的事務(wù)中才能讀到。

可重復(fù)讀(MySQL默認(rèn))：別人改數(shù)據(jù)的事務(wù)已經(jīng)提交，我在我的事務(wù)中也不去讀，以此保證重復(fù)讀一致性。

串行：我的事務(wù)尚未提交，別人就別想改數(shù)據(jù)。

標(biāo)準(zhǔn)跟實(shí)現(xiàn)：上面都是關(guān)于事務(wù)的標(biāo)準(zhǔn)，但是每一種數(shù)據(jù)庫都有不同的實(shí)現(xiàn)，比如MySQL InnDB 默認(rèn)為RR級別，但是不會(huì)出現(xiàn)幻讀。因?yàn)楫?dāng)事務(wù)A更新了所有記錄的某個(gè)字段，此時(shí)事務(wù)A會(huì)獲得對這個(gè)表的表鎖，因?yàn)槭聞?wù)A還沒有提交，所以事務(wù)A獲得的鎖沒有釋放，此時(shí)事務(wù)B在該表插入新記錄，會(huì)因?yàn)闊o法獲得該表的鎖，則導(dǎo)致插入操作被阻塞。只有事務(wù)A提交了事務(wù)后，釋放了鎖，事務(wù)B才能進(jìn)行接下去的操作。所以可以說 MySQL的RR級別的隔離是已經(jīng)實(shí)現(xiàn)解決了臟讀，不可重復(fù)讀和幻讀的。

5、MySQL中的鎖

無論是Java的并發(fā)編程還是數(shù)據(jù)庫的并發(fā)操作都會(huì)涉及到鎖，研發(fā)人員引入了悲觀鎖跟樂觀鎖這樣一種鎖的設(shè)計(jì)思想。

悲觀鎖：

優(yōu)點(diǎn)：適合在寫多讀少的并發(fā)環(huán)境中使用，雖然無法維持非常高的性能，但是在樂觀鎖無法提更好的性能前提下，可以做到數(shù)據(jù)的安全性

缺點(diǎn)：加鎖會(huì)增加系統(tǒng)開銷，雖然能保證數(shù)據(jù)的安全，但數(shù)據(jù)處理吞吐量低，不適合在讀書寫少的場合下使用

樂觀鎖：

優(yōu)點(diǎn)：在讀多寫少的并發(fā)場景下，可以避免數(shù)據(jù)庫加鎖的開銷，提高DAO層的響應(yīng)性能，很多情況下ORM工具都有帶有樂觀鎖的實(shí)現(xiàn)，所以這些方法不一定需要我們?nèi)藶榈娜?shí)現(xiàn)。

缺點(diǎn)：在寫多讀少的并發(fā)場景下，即在寫操作競爭激烈的情況下，會(huì)導(dǎo)致CAS多次重試，沖突頻率過高，導(dǎo)致開銷比悲觀鎖更高。

實(shí)現(xiàn)：數(shù)據(jù)庫層面的樂觀鎖其實(shí)跟CAS思想類似， 通數(shù)據(jù)版本號或者時(shí)間戳也可以實(shí)現(xiàn)。

數(shù)據(jù)庫并發(fā)場景主要有三種：

讀-讀：不存在任何問題，也不需要并發(fā)控制

讀-寫：有隔離性問題，可能遇到臟讀，幻讀，不可重復(fù)讀

寫-寫：可能存更新丟失問題，比如第一類更新丟失，第二類更新丟失

兩類更新丟失問題：

第一類更新丟失：事務(wù)A的事務(wù)回滾覆蓋了事務(wù)B已提交的結(jié)果 第二類更新丟失：事務(wù)A的提交覆蓋了事務(wù)B已提交的結(jié)果

為了合理貫徹落實(shí)鎖的思想，MySQL中引入了雜七雜八的各種鎖：

鎖分類

MySQL支持三種層級的鎖定，分別為

表級鎖定

MySQL中鎖定粒度最大的一種鎖，最常使用的MYISAM與INNODB都支持表級鎖定。

頁級鎖定

是MySQL中鎖定粒度介于行級鎖和表級鎖中間的一種鎖，表級鎖速度快，但沖突多，行級沖突少，但速度慢。所以取了折衷的頁級，一次鎖定相鄰的一組記錄。

行級鎖定

Mysql中鎖定粒度最細(xì)的一種鎖，表示只針對當(dāng)前操作的行進(jìn)行加鎖。行級鎖能大大減少數(shù)據(jù)庫操作的沖突。其加鎖粒度最小，但加鎖的開銷也最大行級鎖不一定比表級鎖要好：鎖的粒度越細(xì)，代價(jià)越高，相比表級鎖在表的頭部直接加鎖，行級鎖還要掃描找到對應(yīng)的行對其上鎖，這樣的代價(jià)其實(shí)是比較高的，所以表鎖和行鎖各有所長。

MyISAM中的鎖

雖然MySQL支持表，頁，行三級鎖定，但MyISAM存儲(chǔ)引擎只支持表鎖。所以MyISAM的加鎖相對比較開銷低，但數(shù)據(jù)操作的并發(fā)性能相對就不高。但如果寫操作都是尾插入，那還是可以支持一定程度的讀寫并發(fā)

從MyISAM所支持的鎖中也可以看出，MyISAM是一個(gè)支持讀讀并發(fā)，但不支持通用讀寫并發(fā)，寫寫并發(fā)的數(shù)據(jù)庫引擎，所以它更適合用于讀多寫少的應(yīng)用場合，一般工程中也用的較少。

InnoDB中的鎖

該模式下支持的鎖實(shí)在是太多了，具體如下：

共享鎖和排他鎖 (Shared and Exclusive Locks)

意向鎖（Intention Locks）

記錄鎖（Record Locks）

間隙鎖（Gap Locks）

臨鍵鎖 （Next-Key Locks）

插入意向鎖（Insert Intention Locks）

主鍵自增鎖 (AUTO-INC Locks)

空間索引斷言鎖（Predicate Locks for Spatial Indexes）

舉個(gè)栗子，比如行鎖里的共享鎖跟排它鎖：lock in share modle 共享讀鎖：

為了確保自己查到的數(shù)據(jù)沒有被其他的事務(wù)正在修改，也就是說確保查到的數(shù)據(jù)是最新的數(shù)據(jù)，并且不允許其他人來修改數(shù)據(jù)。但是自己不一定能夠修改數(shù)據(jù)，因?yàn)橛锌赡芷渌氖聞?wù)也對這些數(shù)據(jù)使用了 in share mode 的方式上了S 鎖。如果不及時(shí)的commit 或者rollback 也可能會(huì)造成大量的事務(wù)等待。

for update排它寫鎖:

為了讓自己查到的數(shù)據(jù)確保是最新數(shù)據(jù)，并且查到后的數(shù)據(jù)只允許自己來修改的時(shí)候，需要用到for update。相當(dāng)于一個(gè) update 語句。在業(yè)務(wù)繁忙的情況下，如果事務(wù)沒有及時(shí)的commit或者rollback 可能會(huì)造成其他事務(wù)長時(shí)間的等待，從而影響數(shù)據(jù)庫的并發(fā)使用效率。

Gap Lock間隙鎖：

1、行鎖只能鎖住行，如果在記錄之間的間隙插入數(shù)據(jù)就無法解決了，因此MySQL引入了間隙鎖(Gap Lock)。間隙鎖是左右開區(qū)間。間隙鎖之間不會(huì)沖突。

2、間隙鎖和行鎖合稱NextKeyLock，每個(gè)NextKeyLock是前開后閉區(qū)間。

間隙鎖加鎖原則(學(xué)完忘那種)：

1、加鎖的基本單位是 NextKeyLock，是前開后閉區(qū)間。

2、查找過程中訪問到的對象才會(huì)加鎖。

3、索引上的等值查詢，給唯一索引加鎖的時(shí)候，NextKeyLock退化為行鎖。

4、索引上的等值查詢，向右遍歷時(shí)且最后一個(gè)值不滿足等值條件的時(shí)候，NextKeyLock退化為間隙鎖。

5、唯一索引上的范圍查詢會(huì)訪問到不滿足條件的第一個(gè)值為止。

【Mysql】查詢優(yōu)化——減少回表操作

??聚集索引：數(shù)據(jù)行的物理順序與列值（一般是主鍵的那一列）的邏輯順序相同，一個(gè)表中只能擁有一個(gè)聚集索引。 葉子結(jié)點(diǎn)存儲(chǔ)索引和行記錄，聚簇索引查詢會(huì)很快，因?yàn)榭梢灾苯佣ㄎ坏叫杏涗洝?/p>

??非聚集索引：該索引中索引的邏輯順序與磁盤上行的物理存儲(chǔ)順序不同，一個(gè)表中可以擁有多個(gè)非聚集索引。 葉子節(jié)點(diǎn)存儲(chǔ)聚簇索引值（主鍵id），需要掃碼兩遍索引樹，先通過普通索引定位到主鍵值id，再通過聚集索引定位到行記錄。

??回表查詢可以理解為普通索引的查詢，先定位主鍵值，再定位行記錄，它的性能較掃一遍索引樹更低。

??索引覆蓋，即將查詢sql中的字段添加到聯(lián)合索引里面，只要保證查詢語句里面的字段都在索引文件中，就無需進(jìn)行回表查詢；

??實(shí)際開發(fā)中，不可能把所有字段建立到聯(lián)合索引，可根據(jù)實(shí)際業(yè)務(wù)場景，把經(jīng)常需要查詢的字段建立到聯(lián)合索引中。

?? 在Mysql5.6的版本上推出，用于優(yōu)化查詢。 在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數(shù)。

?? 優(yōu)化超多分頁場景。 查詢條件放到子查詢中，子查詢只查主鍵id，然后使用子查詢中確定的主鍵關(guān)聯(lián)查詢其他的屬性字段。

當(dāng)前名稱：mysql多個(gè)值怎么回表 mysql怎么進(jìn)行多表查詢
標(biāo)題路徑：http://jinyejixie.com/article0/hpcdoo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供標(biāo)簽優(yōu)化、網(wǎng)站建設(shè)、電子商務(wù)、ChatGPT、微信公眾號、營銷型網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)