1.什么Mysql的事務(wù)？事務(wù)的四大特性？事務(wù)帶來(lái)的什么問(wèn)題？

Mysql中事務(wù)的隔離級(jí)別分為四大等級(jí)：讀未提交（READ UNCOMMITTED）、讀提交 （READ COMMITTED）、可重復(fù)讀 （REPEATABLE READ）、串行化 （SERIALIZABLE）。

在Mysql中事務(wù)的四大特性主要包含：原子性（Atomicity）、一致性（Consistent）、隔離性（Isalotion）、持久性(Durable)，簡(jiǎn)稱(chēng)為ACID。

1. 原子性：是指事務(wù)的原子性操作， 對(duì)數(shù)據(jù)的修改要么全部執(zhí)行成功，要么全部失敗，實(shí)現(xiàn)事務(wù)的原子性，是基于日志的Redo/Undo機(jī)制。
2. 一致性：是指 執(zhí)行事務(wù)前后的狀態(tài)要一致，可以理解為數(shù)據(jù)一致性。
3. 隔離性：側(cè)重指 事務(wù)之間相互隔離，不受影響，這個(gè)與事務(wù)設(shè)置的隔離級(jí)別有密切的關(guān)系。
4. 持久性：則是指在 一個(gè)事務(wù)提交后，這個(gè)事務(wù)的狀態(tài)會(huì)被持久化到數(shù)據(jù)庫(kù)中，也就是事務(wù)提交，對(duì)數(shù)據(jù)的新增、更新將會(huì)持久化到數(shù)據(jù)庫(kù)中。

在我的理解中：原子性、隔離性、持久性都是為了保障一致性而存在的，一致性也是最終的目的。

沒(méi)有那種隔離級(jí)別是完美的，只能根據(jù)自己的項(xiàng)目業(yè)務(wù)場(chǎng)景去評(píng)估選擇最適合的隔離級(jí)別，大部分的公司一般選擇Mysql默認(rèn)的隔離級(jí)別：可重復(fù)讀。

隔離級(jí)別從：讀未提交-讀提交-可重復(fù)讀-串行化，級(jí)別越來(lái)越高，隔離也就越來(lái)越嚴(yán)實(shí)，到最后的串行化，當(dāng)出現(xiàn)讀寫(xiě)鎖沖突的時(shí)候，后面的事務(wù)只能等前面的事務(wù)完成后才能繼續(xù)訪問(wèn)。

1. 讀未提交：讀取到別的事務(wù)還沒(méi)有提交的數(shù)據(jù)，從而產(chǎn)生了臟讀。
2. 讀提交：讀取別的事務(wù)已經(jīng)提交的數(shù)據(jù)，從而產(chǎn)生不可重復(fù)讀。
3. 可重復(fù)讀：事務(wù)開(kāi)啟過(guò)程中看到的數(shù)據(jù)和事務(wù)剛開(kāi)始看到的數(shù)據(jù)是一樣的，從而產(chǎn)生幻讀，在Mysql的中通過(guò)MVCC多版本控制的一致性視圖解決了不可重復(fù)讀問(wèn)題以及通過(guò)間隙鎖解決了幻讀問(wèn)題。
4. 串行化：對(duì)于同一行記錄，若是讀寫(xiě)鎖發(fā)生沖突，后面訪問(wèn)的事務(wù)只能等前面的事務(wù)執(zhí)行完才能繼續(xù)訪問(wèn)。

舉個(gè)例子，假如有一個(gè)user表，里面有兩個(gè)字段id和age，里面有一條測(cè)試數(shù)據(jù)：（1,24），現(xiàn)在要執(zhí)行age+1，同時(shí)有兩個(gè)事務(wù)執(zhí)行：

經(jīng)過(guò)上面的執(zhí)行，在四種隔離級(jí)別下a1,a2,a3,a4,a5的值分別是多少？我們來(lái)認(rèn)真的分析一波：

1. 讀未提交：a1和a2因?yàn)樽x的是初始值所以為24，隔離級(jí)別為讀未提交，事務(wù)2執(zhí)行了age=age+1，不管事務(wù)2是否提交，那么a3、a4和a5的值都是25。
2. 讀提交：a1和a2因?yàn)樽x的是初始值所以為24，隔離級(jí)別為讀提交所以a3還是24，a4和a5因?yàn)槭聞?wù)2已經(jīng)提交所以得到的值是25。
3. 可重復(fù)讀：a1和a2因?yàn)樽x的是初始值所以為24，可重復(fù)讀的隔離級(jí)別下，a3和a4讀取的值和事務(wù)開(kāi)始的結(jié)果一樣，所以還是24，a5前一步因?yàn)橐呀?jīng)提交事務(wù)，所以a5的值是25。
4. 串行化：a1和a2因?yàn)樽x的是初始值所以為24，串行化隔離級(jí)別下，當(dāng)事務(wù)2修改數(shù)據(jù)的時(shí)候，獲取了寫(xiě)鎖，事務(wù)1讀取age的值會(huì)被鎖住，所以在事務(wù)1的角度下a3和a4讀取的值為24，a5的值為25。

當(dāng)你能夠分析得出這個(gè)例子下，在不同隔離級(jí)別下分析的出a1-a5的值，說(shuō)明你對(duì)事務(wù)的隔離級(jí)別已經(jīng)有比較深入的理解了。

2.你詳細(xì)了解過(guò)MVCC嗎？它是怎么工作的？

MVCC叫做多版本控制，實(shí)現(xiàn)MVCC時(shí)用到了一致性視圖，用于支持讀提交和可重復(fù)讀的實(shí)現(xiàn)。

對(duì)于一行數(shù)據(jù)若是想實(shí)現(xiàn)可重復(fù)讀取或者能夠讀取數(shù)據(jù)的另一個(gè)事務(wù)未提交前的原始值，那么必須對(duì)原始數(shù)據(jù)進(jìn)行保存或者對(duì)更新操作進(jìn)行保存，這樣才能夠查詢(xún)到原始值。

在Mysql的MVCC中規(guī)定每一行數(shù)據(jù)都有多個(gè)不同的版本，一個(gè)事務(wù)更新操作完后就生成一個(gè)新的版本，并不是對(duì)全部數(shù)據(jù)的全量備份，因?yàn)槿總浞莸拇鷥r(jià)太大了：

如圖中所示，假如三個(gè)事務(wù)更新了同一行數(shù)據(jù)，那么就會(huì)有對(duì)應(yīng)的v1、v2、v3三個(gè)數(shù)據(jù)版本，每一個(gè)事務(wù)在開(kāi)始的時(shí)候都獲得一個(gè)唯一的事務(wù)id（transaction id），并且是順序遞增的，并且這個(gè)事務(wù)id最后會(huì)賦值給row trx_id，這樣就形成了一個(gè)唯一的一行數(shù)據(jù)版本。

實(shí)際上版本1、版本2并非實(shí)際物理存在的，而圖中的U1和U2實(shí)際就是undo log日志（回滾日志），這v1和v2版本是根據(jù)當(dāng)前v3和undo log計(jì)算出來(lái)的。

InnoDB引擎就是利用每行數(shù)據(jù)有多個(gè)版本的特性，實(shí)現(xiàn)了秒級(jí)創(chuàng)建“快照”，并不需要花費(fèi)大量的是時(shí)間。

3.Mysql的InnoDB和MyISAM有什么區(qū)別？

（1）InnoDB和MyISAM都是Mysql的存儲(chǔ)引擎，現(xiàn)在MyISAM也逐漸被InnoDB給替代，主要因?yàn)镮nnoDB支持事務(wù)和行級(jí)鎖，MyISAM不支持事務(wù)和行級(jí)鎖，MyISAM最小鎖單位是表級(jí)。因?yàn)镸yISAM不支持行級(jí)鎖，所以在并發(fā)處理能力上InnoDB會(huì)比MyISAM好。

（2） 數(shù)據(jù)的存儲(chǔ)上：MyISAM的索引也是由B+樹(shù)構(gòu)成，但是樹(shù)的葉子結(jié)點(diǎn)存的是行數(shù)據(jù)的地址，查找時(shí)需要找到葉子結(jié)點(diǎn)的地址，再根據(jù)葉子結(jié)點(diǎn)地址查找數(shù)據(jù)。

InnoDB的主鍵索引的葉子結(jié)點(diǎn)直接就是存儲(chǔ)行數(shù)據(jù)，查找主鍵索引樹(shù)就能獲得數(shù)據(jù)：

若是根據(jù)非主鍵索引查找，非主鍵索引的葉子結(jié)點(diǎn)存儲(chǔ)的就是，當(dāng)前索引值以及對(duì)應(yīng)的主鍵的值，若是聯(lián)合索引存儲(chǔ)的就是聯(lián)合索引值和對(duì)應(yīng)的主鍵值。

（3）數(shù)據(jù)文件構(gòu)成：MyISAM有三種存儲(chǔ)文件分別是擴(kuò)展名為：.frm（文件存儲(chǔ)表定義）、.MYD (MYData數(shù)據(jù)文件)、.MYI (MYIndex索引文件)。而InnoDB的表只受限于操作系統(tǒng)文件的大小，一般是2GB

（4）查詢(xún)區(qū)別：對(duì)于讀多寫(xiě)少的業(yè)務(wù)場(chǎng)景，MyISAM會(huì)更加適合，而對(duì)于update和insert比較多的場(chǎng)景InnoDB會(huì)比較適合。

（5）coun(*)區(qū)別：select count(*) from table，MyISAM引擎會(huì)查詢(xún)已經(jīng)保存好的行數(shù)，這是不加where的條件下，而InnoDB需要全表掃描一遍，InnoDB并沒(méi)有保存表的具體行數(shù)。

（6）其它的區(qū)別：InnoDB支持外鍵，但是不支持全文索引，而MyISAM不支持外鍵，支持全文索引，InnoDB的主鍵的范圍比MyISAM的大。

4.你知道執(zhí)行一條查詢(xún)語(yǔ)句的流程嗎？

當(dāng)Mysql執(zhí)行一條查詢(xún)的SQl的時(shí)候大概發(fā)生了以下的步驟：

1. 客戶(hù)端發(fā)送查詢(xún)語(yǔ)句給服務(wù)器。
2. 服務(wù)器首先進(jìn)行用戶(hù)名和密碼的驗(yàn)證以及權(quán)限的校驗(yàn)。
3. 然后會(huì)檢查緩存中是否存在該查詢(xún)，若存在，返回緩存中存在的結(jié)果。若是不存在就進(jìn)行下一步。注意：Mysql 8就把緩存這塊給砍掉了。
4. 接著進(jìn)行語(yǔ)法和詞法的分析，對(duì)SQl的解析、語(yǔ)法檢測(cè)和預(yù)處理，再由優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃。
5. Mysql的執(zhí)行器根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃執(zhí)行，調(diào)用存儲(chǔ)引擎的接口進(jìn)行查詢(xún)。服務(wù)器將查詢(xún)的結(jié)果返回客戶(hù)端。

Mysql中語(yǔ)句的執(zhí)行都是都是分層執(zhí)行，每一層執(zhí)行的任務(wù)都不同，直到最后拿到結(jié)果返回，主要分為Service層和引擎層。

在Service層中包含：連接器、分析器、優(yōu)化器、執(zhí)行器。引擎層以插件的形式可以兼容各種不同的存儲(chǔ)引擎，主要包含的有InnoDB和MyISAM兩種存儲(chǔ)引擎。具體的執(zhí)行流程圖如下所示：

5.redo log和binlog了解過(guò)嗎？

redo log日志也叫做WAL技術(shù)（Write- Ahead Logging），他是一種先寫(xiě)日志，并更新內(nèi)存，最后再更新磁盤(pán)的技術(shù)，為了就是減少sql執(zhí)行期間的數(shù)據(jù)庫(kù)io操作，并且更新磁盤(pán)往往是在Mysql比較閑的時(shí)候，這樣就大大減輕了Mysql的壓力。

redo log是固定大小，是物理日志，屬于InnoDB引擎的，并且寫(xiě)redo log是環(huán)狀寫(xiě)日志的形式：

如上圖所示：若是四組的redo log文件，一組為1G的大小，那么四組就是4G的大小，其中write pos是記錄當(dāng)前的位置，有數(shù)據(jù)寫(xiě)入當(dāng)前位置，那么write pos就會(huì)邊寫(xiě)入邊往后移。

check point記錄擦除的位置，因?yàn)閞edo log是固定大小，所以當(dāng)redo log滿(mǎn)的時(shí)候，也就是write pos追上check point的時(shí)候，需要清除redo log的部分?jǐn)?shù)據(jù)，清除的數(shù)據(jù)會(huì)被持久化到磁盤(pán)中，然后將check point向前移動(dòng)。

redo log日志實(shí)現(xiàn)了即使在數(shù)據(jù)庫(kù)出現(xiàn)異常宕機(jī)的時(shí)候，重啟后之前的記錄也不會(huì)丟失，這就是crash-safe能力。

binlog稱(chēng)為歸檔日志，是邏輯上的日志，它屬于Mysql的Server層面的日志，記錄著sql的原始邏輯，主要有兩種模式：一個(gè)是statement格式記錄的是原始的sql，而row格式則是記錄行內(nèi)容。

redo log和binlog記錄的形式、內(nèi)容不同，這兩者日志都能通過(guò)自己記錄的內(nèi)容恢復(fù)數(shù)據(jù)。

之所以這兩個(gè)日志同時(shí)存在，是因?yàn)閯傞_(kāi)始Mysql自帶的引擎MyISAM就沒(méi)有crash-safe功能的，并且在此之前Mysql還沒(méi)有InnoDB引擎，Mysql自帶的binlog日志只是用來(lái)歸檔日志的，所以InnoDB引擎也就通過(guò)自己redo log日志來(lái)實(shí)現(xiàn)crash-safe功能。

6.線上要給熱點(diǎn)數(shù)據(jù)表添加字段該怎么操作？

首先給表加一個(gè)字段，會(huì)導(dǎo)致掃描全表數(shù)據(jù)，并且會(huì)加MDL寫(xiě)鎖，所以在線上操作一定要謹(jǐn)慎再謹(jǐn)慎，有可能還沒(méi)操作完就導(dǎo)致數(shù)據(jù)庫(kù)給搞崩了。

對(duì)于這種情況有限考慮線上的穩(wěn)定的運(yùn)行，加字段是其次，可以通過(guò)在alter table后設(shè)定等待的時(shí)間，若是獲取不到鎖后面在進(jìn)行嘗試，并且可以選擇訪問(wèn)量比較上的時(shí)間段進(jìn)行獲取。

若是能獲取到鎖那是最好了，當(dāng)然即使獲取到鎖也不要阻塞后面的業(yè)務(wù)語(yǔ)句，一切都是以業(yè)務(wù)優(yōu)先為原則。

7.Msyql的索引的底層實(shí)現(xiàn)嗎？為什么不用有序數(shù)組、hash或者二叉樹(shù)實(shí)現(xiàn)索引？

Mysql的索引是一種加快查詢(xún)速度的數(shù)據(jù)結(jié)構(gòu)，索引就好比書(shū)的目錄一樣能夠快速的定位你要查找的位置。

Mysql的索引底層是使用B+樹(shù)的數(shù)據(jù)結(jié)構(gòu)進(jìn)行實(shí)現(xiàn)，結(jié)構(gòu)如下圖所示：

索引的一個(gè)數(shù)據(jù)頁(yè)的大小是16kb，從磁盤(pán)加載到內(nèi)存中是以數(shù)據(jù)頁(yè)的大小為單位進(jìn)行加載，然后供查詢(xún)操作進(jìn)行查詢(xún)，若是查詢(xún)的數(shù)據(jù)不在內(nèi)存中，才會(huì)從磁盤(pán)中再次加載到內(nèi)存中。

索引的實(shí)現(xiàn)有很多，比如hash。hash是以key-value的形式進(jìn)行存儲(chǔ)，適合于等值查詢(xún)的場(chǎng)景，查詢(xún)的時(shí)間復(fù)雜度為O(1)，因?yàn)?strong>hash儲(chǔ)存并不是有序的，所以對(duì)于范圍查詢(xún)就可能要遍歷所有數(shù)據(jù)進(jìn)行查詢(xún)，而且不同值的計(jì)算還會(huì)出現(xiàn)hash沖突，所以hash并不適合于做Mysql的索引。

有序數(shù)組在等值查詢(xún)和范圍查詢(xún)性能都是非常好的，那為什么又不用有序數(shù)組作為索引呢？因?yàn)?strong>對(duì)于數(shù)組而言作為索引更新的成本太高，新增數(shù)據(jù)要把后面的數(shù)據(jù)都往后移一位，所以也不采用有序數(shù)組作為索引的底層實(shí)現(xiàn)。

最后二叉樹(shù)，主要是因?yàn)?strong>二叉樹(shù)只有二叉，一個(gè)節(jié)點(diǎn)存儲(chǔ)的數(shù)據(jù)量非常有限，需要頻繁的隨機(jī)IO讀寫(xiě)磁盤(pán)，若是數(shù)據(jù)量大的情況下二叉的樹(shù)高太高，嚴(yán)重影響性能，所以也不采用二叉樹(shù)進(jìn)行實(shí)現(xiàn)。

而B(niǎo)+樹(shù)是多叉樹(shù)，一個(gè)數(shù)據(jù)頁(yè)的大小是16kb，在1-3的樹(shù)高就能存儲(chǔ)10億級(jí)以上的數(shù)據(jù)，也就是只要訪問(wèn)磁盤(pán)1-3次就足夠了，并且B+樹(shù)的葉子結(jié)點(diǎn)上一個(gè)葉子結(jié)點(diǎn)有指針指向下一個(gè)葉子結(jié)點(diǎn)，便于范圍查詢(xún)：

8.怎么查看索引是否生效？什么情況下索引會(huì)失效呢？

查看索引是否起作用可以使用explain關(guān)鍵字，查詢(xún)后的語(yǔ)句中的key字段，若是使用了索引，該字段會(huì)展示索引的名字。

（1）where條件查詢(xún)中使用了or關(guān)鍵字，有可能使用了索引進(jìn)行查詢(xún)也會(huì)導(dǎo)致索引失效，若是想使用or關(guān)鍵字，又不想索引失效，只能在or的所有列上都建立索引。

（2）條件查詢(xún)中使用like關(guān)鍵字，并且不符合最左前綴原則，會(huì)導(dǎo)致索引失效。

（3）條件查詢(xún)的字段是字符串，而錯(cuò)誤的使用where column = 123 數(shù)字類(lèi)型也會(huì)導(dǎo)致索引失效。

（4）對(duì)于聯(lián)合索引查詢(xún)不符合最左前綴原則，也會(huì)導(dǎo)致索引失效，如下所示：

alter?table?user?add?index?union_index(name,?age)???//?name左邊的列，?age?右邊的列??????????????????????????????????????????????????????????????
select?*?from?user?where?name?=?'lidu'?????//?會(huì)用到索引
select?*?from?user?where?age?=?18??????????//??不會(huì)使用索引

（5）在where條件查詢(xún)的后面對(duì)字段進(jìn)行null值判斷，會(huì)導(dǎo)致索引失效，解決的辦法就是可以把null改為0或者-1這些特殊的值代替：

SELECT?id?FROM?table?WHERE?num?is?null

（6）在where子句中使用!= ,< >這樣的符號(hào)，也會(huì)導(dǎo)致索引失效。

SELECT?id?FROM?table?WHERE?num?!=?0

（7）where條件子句中=的左邊使用表達(dá)式操作或者函數(shù)操作，也會(huì)導(dǎo)致索引失效。

SELECT?id?FROM?user?WHERE?age?/?2?=?1
SELECT?id?FROM?user?WHERE?SUBSTRING(name,1,2)?=?'lidu'

9.你知道有哪些種類(lèi)的索引？

索引從數(shù)據(jù)結(jié)構(gòu)進(jìn)行劃分的分為：B+樹(shù)索引、hash索引、R-Tree索引、FULLTEXT索引。

索引從物理存儲(chǔ)的角度劃分為：聚族索引和非聚族索引。

從邏輯的角度分為：主鍵索引、普通索引、唯一索引、聯(lián)合索引以及空間索引。

10.你平時(shí)是怎么進(jìn)行SQL優(yōu)化的？

SQL的優(yōu)化主要是對(duì)字段添加索引，主要包含有這四種索引(主鍵索引/唯一索引/全文索引/普通索引)，以及結(jié)合具體的業(yè)務(wù)場(chǎng)景分析具體是使用什么索引最合理。

explain 可以幫助我們?cè)诓徽嬲龍?zhí)行某個(gè)sql語(yǔ)句時(shí)，就執(zhí)行mysql怎樣執(zhí)行，這樣利用我們?nèi)シ治鰏ql指令：

1. id：查詢(xún)的序列號(hào)。
2. select_type：查詢(xún)類(lèi)型。
3. table：查詢(xún)表名。
4. type：掃描方式，all表示全表掃描。
5. possible_keys：可是使用到的索引。
6. key：實(shí)際使用到的索引。
7. rows：該sql掃面了多少行。
8. Extra：sql語(yǔ)句額外的信息，比如排序方式

SQL優(yōu)化方法

（1）對(duì)于條件查詢(xún)，首先考慮在條件where和order by后的字段建立索引。（2）避免索引失效，避免where條件后進(jìn)行null值的判斷。（3）避免where后使用!=或<>操作符。（4）避免在where后面進(jìn)行使用函數(shù)。（5）避免where條件后使用or關(guān)鍵字來(lái)連接。

上面的這一些都是要注意的，當(dāng)然還有很多的小技巧，都有可能會(huì)導(dǎo)致索引的實(shí)效。

索引的種類(lèi)

另一方面就是考慮到底是建立哪種索引比較合適，這里以普通索引和唯一索引進(jìn)行舉例說(shuō)明。

假如我們的業(yè)務(wù)場(chǎng)景是讀多寫(xiě)少的場(chǎng)景，那么SQL查詢(xún)請(qǐng)求過(guò)來(lái)，假如數(shù)據(jù)已經(jīng)在內(nèi)存中，獲取到數(shù)據(jù)后就直接返回，假如數(shù)據(jù)不在內(nèi)存的數(shù)據(jù)頁(yè)中，就會(huì)加載磁盤(pán)到內(nèi)存中再返回，對(duì)于這種場(chǎng)景可能對(duì)于普通索引和唯一索引的選擇性能上并沒(méi)有明顯的區(qū)別。

但是，一般建議選擇普通索引，在寫(xiě)多讀少的場(chǎng)景下，這兩者索引的選擇對(duì)性能的影響就比較大了，對(duì)于普通索引的的寫(xiě)，不管數(shù)據(jù)是否存在于內(nèi)存中，都會(huì)先寫(xiě)入內(nèi)存中的一小塊叫做chang buffer內(nèi)存中，然后在通過(guò)后臺(tái)刷盤(pán)，一般會(huì)選擇Mysql比較閑的時(shí)候進(jìn)行刷盤(pán)。

而唯一索引就不同了，因?yàn)樗_保索引的唯一性，索引寫(xiě)數(shù)據(jù)的時(shí)候，假如數(shù)據(jù)不在內(nèi)存中，要先從磁盤(pán)中加載數(shù)據(jù)到內(nèi)存中，然后比較是否唯一，所以唯一索引就不能使用chang buffer的優(yōu)化機(jī)制，會(huì)頻繁的進(jìn)行隨機(jī)的磁盤(pán)IO。

11.什么是聚簇索引和非聚簇索引？

聚族索引和非聚族索引的主要區(qū)別是：聚族索引的葉子結(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)，而非聚族索引的葉子結(jié)點(diǎn)存儲(chǔ)仍然是索引節(jié)點(diǎn)，只不過(guò)有指向?qū)?yīng)數(shù)據(jù)塊的指針。

區(qū)別這兩者的區(qū)別就是來(lái)對(duì)比InnoDB和MYISAM的數(shù)據(jù)結(jié)構(gòu)了。假如我們有一個(gè)表原始數(shù)據(jù)如下所示：

那么在MyISAM的索引中數(shù)據(jù)的儲(chǔ)存結(jié)構(gòu)如下所示：

MyISAM以葉子結(jié)點(diǎn)存儲(chǔ)的Row number來(lái)找到對(duì)應(yīng)的行數(shù)據(jù)，也就是葉子結(jié)點(diǎn)存儲(chǔ)的是行指針，這也可以發(fā)現(xiàn)MyISAM引擎中數(shù)據(jù)文件（.MYI）和索引文件（.MYD）是分開(kāi)的，索引MyISAM的查找索引樹(shù)后，需要根據(jù)行指針二次的進(jìn)行定位。

而在InnoDB的主鍵索引存儲(chǔ)的結(jié)構(gòu)形式如下所示：

InnoDB的主鍵索引中葉子結(jié)點(diǎn)并不是存儲(chǔ)行指針，而是存儲(chǔ)行數(shù)據(jù)，二級(jí)索引中MyISAM也是一樣的存儲(chǔ)方式，InnoDB的二級(jí)索引的葉子結(jié)點(diǎn)則是存儲(chǔ)當(dāng)前索引值以及對(duì)應(yīng)的主鍵索引值。

InnoDB的二級(jí)索引帶來(lái)的好處就是減少了由于數(shù)據(jù)移動(dòng)或者數(shù)據(jù)頁(yè)分列導(dǎo)致行數(shù)據(jù)的地址變了而帶來(lái)的維護(hù)二級(jí)索引的性能開(kāi)銷(xiāo)，因?yàn)镮nnoDB的二級(jí)索引不需要更新行指針：

12.什么是回表？回表是怎么產(chǎn)生的呢？

上面說(shuō)過(guò)InnoDB引擎的主鍵索引存儲(chǔ)的是行數(shù)據(jù)，二級(jí)索引的葉子結(jié)點(diǎn)存儲(chǔ)的是索引數(shù)據(jù)以及對(duì)應(yīng)的主鍵，所以回表就是根據(jù)索引進(jìn)行條件查詢(xún)，回到主鍵索引樹(shù)進(jìn)行搜索的過(guò)程：

因?yàn)椴樵?xún)還要回表一次，再次查詢(xún)主鍵索引樹(shù)，所以實(shí)際中應(yīng)該盡量避免回表的產(chǎn)生。

13.怎么解決回表的問(wèn)題？

解決回表問(wèn)題可以建立聯(lián)合索引進(jìn)行索引覆蓋，如圖所示根據(jù)name字段查詢(xún)用戶(hù)的name和sex屬性出現(xiàn)了回表問(wèn)題：

那么我們可以建立下面這個(gè)聯(lián)合索引來(lái)解決：

create?table?user?(
?id?int?primary?key,
?name?varchar(20),
?sex?varchar(5),
?index(name,?sex)
)?engine?=?innodb;

建立了如上所示的index(name,sex)聯(lián)合索引，在二級(jí)索引的葉子結(jié)點(diǎn)的位置就會(huì)同時(shí)也出現(xiàn)sex字段的值，因?yàn)槟軌颢@取到要查詢(xún)的所有字段，因?yàn)榫筒挥迷倩乇聿樵?xún)一次。

14.什么是最左前綴原則？

最左前綴原則可以是聯(lián)合索引的的最左N個(gè)字段，也可以是字符串索引的最左的M個(gè)字符。舉個(gè)例子，假如現(xiàn)在有一個(gè)表的原始數(shù)據(jù)如下所示：

并根據(jù)col3 ，col2的順序建立聯(lián)合索引，此時(shí)聯(lián)合索引樹(shù)結(jié)構(gòu)如圖下所示：

葉子結(jié)點(diǎn)中首先會(huì)根據(jù)col3的字符進(jìn)行排序，若是col3相等，在col3相等的值里面再對(duì)col2進(jìn)行排序，假如我們要查詢(xún)where col3 like 'Eri%'，就可以快速的定位查詢(xún)到Eric。

若是查詢(xún)條件為where col3 like '%se'，前面的字符不確定，表示任意字符都可以，這樣就可以導(dǎo)致全表掃描進(jìn)行字符的比較，就會(huì)使索引失效。

15.什么是索引下推？

Mysql5.6之前是沒(méi)有索引下推這個(gè)功能，后面為了提高性能，避免不必要的回表5.6之后就有了索引下推優(yōu)化的功能。

假如我們有一個(gè)用戶(hù)表，并且使用用戶(hù)的name，age兩個(gè)字段建立聯(lián)合索引，name在沒(méi)有索引下推的功能，執(zhí)行下面的sql，執(zhí)行的流程如下圖所示：

select?*?from?tuser?where?name?like?'張%'?and?age=10?and?ismale=1;

當(dāng)比較第一個(gè)索引字段name like '張%' 就會(huì)篩選出四行數(shù)據(jù)，后面它不會(huì)再比較age值是否符合要求，直接獲取到主鍵值，然后在回表查詢(xún)，回表后再對(duì)比age、ismale是否符合條件。

從上面的數(shù)據(jù)看來(lái)其實(shí)name，age兩個(gè)字段建立的聯(lián)合索引，兩個(gè)字段的值會(huì)存儲(chǔ)在聯(lián)合索引樹(shù)中，可以直接對(duì)比age字段是否符合查詢(xún)的條件age=10，那么索引下推就是做了這些事：

索引下推會(huì)再次根據(jù)你的age進(jìn)行比較，發(fā)現(xiàn)有兩條記錄不符合條件直接過(guò)濾掉，符合條件的才會(huì)進(jìn)行回表查詢(xún)，這樣就減少了不必要的回表查詢(xún)。

16.主鍵使用自增ID還是UUID?能說(shuō)說(shuō)原因嗎？

自增ID和UUID作為主鍵的考慮主要有兩方面，一個(gè)是性能另一個(gè)就是存儲(chǔ)的空間大小，一般沒(méi)有特定的業(yè)務(wù)要求都不推薦使用UUID作為主鍵。

因?yàn)槭褂?strong>UUID作為主鍵插入并不能保證插入是有序的，有可能會(huì)涉及數(shù)據(jù)的挪動(dòng)，也有可能觸發(fā)數(shù)據(jù)頁(yè)的分裂，因?yàn)橐粋€(gè)數(shù)據(jù)頁(yè)的大小就是16KB，這樣插入數(shù)據(jù)的成本就會(huì)比較高。

而自增ID作為主鍵的話插入數(shù)據(jù)都是追加操作，不會(huì)有數(shù)據(jù)的移動(dòng)以及數(shù)據(jù)頁(yè)的分裂，性能會(huì)比較好。

另一方面就是存儲(chǔ)空間，自增主鍵一般整形只要4個(gè)字節(jié)，長(zhǎng)整形才占8字節(jié)的大小空間，而使用UUID作為主鍵存儲(chǔ)空間需要16字節(jié)的大小，會(huì)占用更多的磁盤(pán)，在二級(jí)索引中也會(huì)存出一份主鍵索引，這樣多占用消耗的空間就是兩倍，性能低，所以不推薦使用。

17.Mysql是怎么控制并發(fā)的訪問(wèn)資源？

Mysql內(nèi)部通過(guò)鎖機(jī)制實(shí)現(xiàn)對(duì)資源的并發(fā)訪問(wèn)控制，保證數(shù)據(jù)的一致性，鎖機(jī)制的類(lèi)型和引擎的種類(lèi)有關(guān)，MyISAM中默認(rèn)支持的表級(jí)鎖有兩種：共享讀鎖和獨(dú)占寫(xiě)鎖。表級(jí)鎖在MyISAM和InnoDB的存儲(chǔ)引擎中都支持，但是InnoDB默認(rèn)支持的是行鎖。

MyISAM鎖機(jī)制

Mysql中可以通過(guò)以下sql來(lái)顯示的在事務(wù)中顯式的進(jìn)行加鎖和解鎖操作：

//?顯式的添加表級(jí)讀鎖
LOCK?TABLE?表名?READ
//?顯示的添加表級(jí)寫(xiě)鎖
LOCK?TABLE?表名?WRITE
//?顯式的解鎖（當(dāng)一個(gè)事務(wù)commit的時(shí)候也會(huì)自動(dòng)解鎖）
unlock?tables;

（1）MyISAM表級(jí)寫(xiě)鎖：當(dāng)一個(gè)線程獲取到表級(jí)寫(xiě)鎖后，只能由該線程對(duì)表進(jìn)行讀寫(xiě)操作，別的線程必須等待該線程釋放鎖以后才能操作。

（2）MyISAM表級(jí)共享讀鎖：當(dāng)一個(gè)線程獲取到表級(jí)讀鎖后，該線程只能讀取數(shù)據(jù)不能修改數(shù)據(jù)，其它線程也只能加讀鎖，不能加寫(xiě)鎖。

InnoDB鎖機(jī)制

InnoDB和MyISAM不同的是，InnoDB支持行鎖和事務(wù)，InnoDB中除了有表鎖和行級(jí)鎖的概念，還有Gap Lock（間隙鎖）、Next-key Lock鎖，間隙鎖主要用于范圍查詢(xún)的時(shí)候，鎖住查詢(xún)的范圍，并且間隙鎖也是解決幻讀的方案。

InnoDB中的行級(jí)鎖是對(duì)索引加的鎖，在不通過(guò)索引查詢(xún)數(shù)據(jù)的時(shí)候，InnoDB就會(huì)使用表鎖。

但是通過(guò)索引查詢(xún)的時(shí)候是否使用索引，還要看Mysql的執(zhí)行計(jì)劃，Mysql的優(yōu)化器會(huì)判斷是一條sql執(zhí)行的最佳策略。

若是Mysql覺(jué)得執(zhí)行索引查詢(xún)還不如全表掃描速度快，那么Mysql就會(huì)使用全表掃描來(lái)查詢(xún)，這是即使sql語(yǔ)句中使用了索引，最后還是執(zhí)行為全表掃描，加的是表鎖。

18.Mysql的死鎖是怎么發(fā)生的？怎么解決死鎖問(wèn)題？

死鎖在InnoDB中才會(huì)出現(xiàn)死鎖，MyISAM是不會(huì)出現(xiàn)死鎖，因?yàn)镸yISAM支持的是表鎖，一次性獲取了所有的鎖，其它的線程只能排隊(duì)等候。

而InnoDB默認(rèn)支持行鎖，獲取鎖是分步的，并不是一次性獲取所有的鎖，因此在鎖競(jìng)爭(zhēng)的時(shí)候就會(huì)出現(xiàn)死鎖的情況。

雖然InnoDB會(huì)出現(xiàn)死鎖，但是并不影響InnoDB成為最受歡迎的存儲(chǔ)引擎，MyISAM可以理解為串行化操作，讀寫(xiě)有序，因此支持的并發(fā)性能低下。

（1）死鎖案例一：

舉一個(gè)例子，現(xiàn)在數(shù)據(jù)庫(kù)表employee中六條數(shù)據(jù)，如下所示：

其中name=ldc的有兩條數(shù)據(jù)，并且name字段為普通索引，分別是id=2和id=3的數(shù)據(jù)行，現(xiàn)在假設(shè)有兩個(gè)事務(wù)分別執(zhí)行下面的兩條sql語(yǔ)句：

//?session1執(zhí)行
update?employee?set?num?=?2?where?name?='ldc';
//?session2執(zhí)行
select?*?from?employee?where?id?=?2?or?id?=3;

其中session1執(zhí)行的sql獲取的數(shù)據(jù)行是兩條數(shù)據(jù)，假設(shè)先獲取到第一個(gè)id=2的數(shù)據(jù)行，然后cpu的時(shí)間分配給了另一個(gè)事務(wù)，另一個(gè)事務(wù)執(zhí)行查詢(xún)操作獲取了第二行數(shù)據(jù)也就是id=3的數(shù)據(jù)行。

當(dāng)事務(wù)2繼續(xù)執(zhí)行的時(shí)候獲取到id=3的數(shù)據(jù)行，鎖定了id=3的數(shù)據(jù)行，此時(shí)cpu又將時(shí)間分配給了第一個(gè)事務(wù)，第一個(gè)事務(wù)執(zhí)行準(zhǔn)備獲取第二行數(shù)據(jù)的鎖，發(fā)現(xiàn)已經(jīng)被其他事務(wù)獲取了，它就處于等待的狀態(tài)。

當(dāng)cpu把時(shí)間有分配給了第二個(gè)事務(wù)，第二個(gè)事務(wù)準(zhǔn)備獲取第一行數(shù)據(jù)的鎖發(fā)現(xiàn)已經(jīng)被第一個(gè)事務(wù)獲取了鎖，這樣就行了死鎖，兩個(gè)事務(wù)彼此之間相互等待。

（2）死鎖案例二

第二種死鎖情況就是當(dāng)一個(gè)事務(wù)開(kāi)始并且update一條id=1的數(shù)據(jù)行時(shí)，成功獲取到寫(xiě)鎖，此時(shí)另一個(gè)事務(wù)執(zhí)行也update另一條id=2的數(shù)據(jù)行時(shí)，也成功獲取到寫(xiě)鎖（id為主鍵）。

此時(shí)cpu將時(shí)間分配給了事務(wù)一，事務(wù)一接著也是update id=2的數(shù)據(jù)行，因?yàn)槭聞?wù)二已經(jīng)獲取到id=2數(shù)據(jù)行的鎖，所以事務(wù)已處于等待狀態(tài)。

事務(wù)二有獲取到了時(shí)間，像執(zhí)行update id=1的數(shù)據(jù)行，但是此時(shí)id=1的鎖被事務(wù)一獲取到了，事務(wù)二也處于等待的狀態(tài)，因此形成了死鎖。

死鎖的解決方案

首先要解決死鎖問(wèn)題，在程序的設(shè)計(jì)上，當(dāng)發(fā)現(xiàn)程序有高并發(fā)的訪問(wèn)某一個(gè)表時(shí)，盡量對(duì)該表的執(zhí)行操作串行化，或者鎖升級(jí)，一次性獲取所有的鎖資源。

然后也可以設(shè)置參數(shù)innodb_lock_wait_timeout，超時(shí)時(shí)間，并且將參數(shù)innodb_deadlock_detect 打開(kāi)，當(dāng)發(fā)現(xiàn)死鎖的時(shí)候，自動(dòng)回滾其中的某一個(gè)事務(wù)。

19.能說(shuō)一說(shuō)Mysql的主從復(fù)制嗎？

讀寫(xiě)分離

實(shí)現(xiàn)MySQL讀寫(xiě)分離的前提是我們已經(jīng)將MySQL主從復(fù)制配置完畢，讀寫(xiě)分離實(shí)現(xiàn)方式：（1）配置多數(shù)據(jù)源。（2）使用mysql的proxy中間件代理工具。

主從復(fù)制的原理

MySQL的主從復(fù)制和讀寫(xiě)分離兩者有著緊密的聯(lián)系，首先要部署主從復(fù)制，只有主從復(fù)制完成了才能在此基礎(chǔ)上進(jìn)行數(shù)據(jù)的讀寫(xiě)分離。

讀寫(xiě)分離的原理

讀寫(xiě)分離就是只在主服務(wù)器上寫(xiě)，只在從服務(wù)器上讀?；驹硎亲屩鲾?shù)據(jù)庫(kù)處理事務(wù)性查詢(xún)，而從服務(wù)器處理select查詢(xún)。數(shù)據(jù)庫(kù)復(fù)制被用來(lái)把事務(wù)性查詢(xún)導(dǎo)致的變更同步到從數(shù)據(jù)庫(kù)中。

20.能說(shuō)一說(shuō)分庫(kù)分表嗎？怎么分？

首先為什么要分表？（1） 如果一個(gè)表的每條記錄的內(nèi)容很大，那么就需要更多的IO操作，如果字段值比較大，而使用頻率相對(duì)比較低，可以將大字段移到另一張表中，當(dāng)查詢(xún)不查大字段的時(shí)候，這樣就減少了I/O操作 （2）如果表的數(shù)據(jù)量非常非常大，那么查詢(xún)就變的比較慢；也就是表的數(shù)據(jù)量影響查詢(xún)的性能。（3）表中的數(shù)據(jù)本來(lái)就有獨(dú)立性，例如分別記錄各個(gè)地區(qū)的數(shù)據(jù)或者不同時(shí)期的數(shù)據(jù)，特別是有些數(shù)據(jù)常用，而另外一些數(shù)據(jù)不常用。（4） 分表技術(shù)有(水平分割和垂直分割)

垂直分割

垂直分割是指數(shù)據(jù)表列的拆分，把一張列比較多的表拆分為多張表。垂直分割一般用于拆分大字段和訪問(wèn)頻率低的字段，分離冷熱數(shù)據(jù)。

垂直分割比較常見(jiàn)：例如博客系統(tǒng)中的文章表，比如文章tbl_articles (id, titile, summary, content, user_id, create_time)，因?yàn)槲恼轮械膬?nèi)容content會(huì)比較長(zhǎng)，放在tbl_articles中會(huì)嚴(yán)重影響表的查詢(xún)速度，所以將內(nèi)容放到tbl_articles_detail(article_id, content)，像文章列表只需要查詢(xún)tbl_articles中的字段即可。

垂直拆分的優(yōu)點(diǎn)：可以使得行數(shù)據(jù)變小，在查詢(xún)時(shí)減少讀取的Block數(shù)，減少I(mǎi)/O次數(shù)。此外，垂直分區(qū)可以簡(jiǎn)化表的結(jié)構(gòu)，易于維護(hù)。

垂直拆分的缺點(diǎn)：主鍵會(huì)出現(xiàn)冗余，需要管理冗余列，并會(huì)引起Join操作，可以通過(guò)在應(yīng)用層進(jìn)行Join來(lái)解決。此外，垂直分區(qū)會(huì)讓事務(wù)變得更加復(fù)雜。

水平分割

水平拆分是指數(shù)據(jù)表行數(shù)據(jù)的拆分，表的行數(shù)超過(guò)500萬(wàn)行或者單表容量超過(guò)10GB時(shí)，查詢(xún)就會(huì)變慢，這時(shí)可以把一張的表的數(shù)據(jù)拆成多張表來(lái)存放。水平分表盡可能使每張表的數(shù)據(jù)量相當(dāng)，比較均勻。

水平拆分會(huì)給應(yīng)用增加復(fù)雜度，它通常在查詢(xún)時(shí)需要多個(gè)表名，查詢(xún)所有數(shù)據(jù)需要union操作。在許多數(shù)據(jù)庫(kù)應(yīng)用中，這種復(fù)雜性會(huì)超過(guò)它帶來(lái)的優(yōu)點(diǎn)。

因?yàn)橹灰饕P(guān)鍵字不大，則在索引用于查詢(xún)時(shí)，表中增加2-3倍數(shù)據(jù)量，查詢(xún)時(shí)也就增加讀一個(gè)索引層的磁盤(pán)次數(shù)，所以水平拆分要考慮數(shù)據(jù)量的增長(zhǎng)速度，根據(jù)實(shí)際情況決定是否需要對(duì)表進(jìn)行水平拆分。

水平分割最重要的是找到分割的標(biāo)準(zhǔn)，不同的表應(yīng)根據(jù)業(yè)務(wù)找出不同的標(biāo)準(zhǔn)

1. 用戶(hù)表可以根據(jù)用戶(hù)的手機(jī)號(hào)段進(jìn)行分割如user183、user150、user153、user189等，每個(gè)號(hào)段就是一張表。
2. 用戶(hù)表也可以根據(jù)用戶(hù)的id進(jìn)行分割，加入分3張表user0,user1,user2，如果用戶(hù)的id%3=0就查詢(xún)user0表，如果用戶(hù)的id%3=1就查詢(xún)user1表。
3. 對(duì)于訂單表可以按照訂單的時(shí)間進(jìn)行分表。

分庫(kù)分表技術(shù)

現(xiàn)在市面上主要的分庫(kù)分表技術(shù)有mycat和sharding-jdbc，具體的分庫(kù)分表的技術(shù)講解我們留在下一次進(jìn)行詳細(xì)的講解。

參考

《Mysql45講》

《數(shù)據(jù)庫(kù)原理》