面试，MySQL 搞透这 20 道就稳了
1.什么Mysql的事務(wù)？事務(wù)的四大特性？事務(wù)帶來的什么問題？
Mysql中事務(wù)的隔離級(jí)別分為四大等級(jí)：讀未提交（READ UNCOMMITTED）、讀提交 （READ COMMITTED）、可重復(fù)讀 （REPEATABLE READ）、串行化 （SERIALIZABLE）。
在Mysql中事務(wù)的四大特性主要包含：原子性（Atomicity）、一致性（Consistent）、隔離性（Isalotion）、持久性(Durable)，簡稱為ACID。
原子性：是指事務(wù)的原子性操作，對(duì)數(shù)據(jù)的修改要么全部執(zhí)行成功，要么全部失敗，實(shí)現(xiàn)事務(wù)的原子性，是基于日志的Redo/Undo機(jī)制。
一致性：是指執(zhí)行事務(wù)前后的狀態(tài)要一致，可以理解為數(shù)據(jù)一致性。
隔離性：側(cè)重指事務(wù)之間相互隔離，不受影響，這個(gè)與事務(wù)設(shè)置的隔離級(jí)別有密切的關(guān)系。
持久性：則是指在一個(gè)事務(wù)提交后，這個(gè)事務(wù)的狀態(tài)會(huì)被持久化到數(shù)據(jù)庫中，也就是事務(wù)提交，對(duì)數(shù)據(jù)的新增、更新將會(huì)持久化到數(shù)據(jù)庫中。
在我的理解中：原子性、隔離性、持久性都是為了保障一致性而存在的，一致性也是最終的目的。
沒有那種隔離級(jí)別是完美的，只能根據(jù)自己的項(xiàng)目業(yè)務(wù)場景去評(píng)估選擇最適合的隔離級(jí)別，大部分的公司一般選擇Mysql默認(rèn)的隔離級(jí)別：可重復(fù)讀。
隔離級(jí)別從：讀未提交-讀提交-可重復(fù)讀-串行化，級(jí)別越來越高，隔離也就越來越嚴(yán)實(shí)，到最后的串行化，當(dāng)出現(xiàn)讀寫鎖沖突的時(shí)候，后面的事務(wù)只能等前面的事務(wù)完成后才能繼續(xù)訪問。
讀未提交：讀取到別的事務(wù)還沒有提交的數(shù)據(jù)，從而產(chǎn)生了臟讀。
讀提交：讀取別的事務(wù)已經(jīng)提交的數(shù)據(jù)，從而產(chǎn)生不可重復(fù)讀。
可重復(fù)讀：事務(wù)開啟過程中看到的數(shù)據(jù)和事務(wù)剛開始看到的數(shù)據(jù)是一樣的，從而產(chǎn)生幻讀，在Mysql的中通過MVCC多版本控制的一致性視圖解決了不可重復(fù)讀問題以及通過間隙鎖解決了幻讀問題。
串行化：對(duì)于同一行記錄，若是讀寫鎖發(fā)生沖突，后面訪問的事務(wù)只能等前面的事務(wù)執(zhí)行完才能繼續(xù)訪問。
舉個(gè)例子，假如有一個(gè)user表，里面有兩個(gè)字段id和age，里面有一條測(cè)試數(shù)據(jù)：（1,24），現(xiàn)在要執(zhí)行age+1，同時(shí)有兩個(gè)事務(wù)執(zhí)行：
經(jīng)過上面的執(zhí)行，在四種隔離級(jí)別下a1,a2,a3,a4,a5的值分別是多少？我們來認(rèn)真的分析一波：
讀未提交：a1和a2因?yàn)樽x的是初始值所以為24，隔離級(jí)別為讀未提交，事務(wù)2執(zhí)行了age=age+1，不管事務(wù)2是否提交，那么a3、a4和a5的值都是25。
讀提交：a1和a2因?yàn)樽x的是初始值所以為24，隔離級(jí)別為讀提交所以a3還是24，a4和a5因?yàn)槭聞?wù)2已經(jīng)提交所以得到的值是25。
可重復(fù)讀：a1和a2因?yàn)樽x的是初始值所以為24，可重復(fù)讀的隔離級(jí)別下，a3和a4讀取的值和事務(wù)開始的結(jié)果一樣，所以還是24，a5前一步因?yàn)橐呀?jīng)提交事務(wù)，所以a5的值是25。
串行化：a1和a2因?yàn)樽x的是初始值所以為24，串行化隔離級(jí)別下，當(dāng)事務(wù)2修改數(shù)據(jù)的時(shí)候，獲取了寫鎖，事務(wù)1讀取age的值會(huì)被鎖住，所以在事務(wù)1的角度下a3和a4讀取的值為24，a5的值為25。
當(dāng)你能夠分析得出這個(gè)例子下，在不同隔離級(jí)別下分析的出a1-a5的值，說明你對(duì)事務(wù)的隔離級(jí)別已經(jīng)有比較深入的理解了。
2.你詳細(xì)了解過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)行保存，這樣才能夠查詢到原始值。
在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ù)在開始的時(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ì)算出來的。
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+樹構(gòu)成，但是樹的葉子結(jié)點(diǎn)存的是行數(shù)據(jù)的地址，查找時(shí)需要找到葉子結(jié)點(diǎn)的地址，再根據(jù)葉子結(jié)點(diǎn)地址查找數(shù)據(jù)。
InnoDB的主鍵索引的葉子結(jié)點(diǎn)直接就是存儲(chǔ)行數(shù)據(jù)，查找主鍵索引樹就能獲得數(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）查詢區(qū)別：對(duì)于讀多寫少的業(yè)務(wù)場景，MyISAM會(huì)更加適合，而對(duì)于update和insert比較多的場景InnoDB會(huì)比較適合。
（5）coun()區(qū)別：select count() from table，MyISAM引擎會(huì)查詢已經(jīng)保存好的行數(shù)，這是不加where的條件下，而InnoDB需要全表掃描一遍，InnoDB并沒有保存表的具體行數(shù)。
（6）其它的區(qū)別：InnoDB支持外鍵，但是不支持全文索引，而MyISAM不支持外鍵，支持全文索引，InnoDB的主鍵的范圍比MyISAM的大。
4.你知道執(zhí)行一條查詢語句的流程嗎？
當(dāng)Mysql執(zhí)行一條查詢的SQl的時(shí)候大概發(fā)生了以下的步驟：
客戶端發(fā)送查詢語句給服務(wù)器。
服務(wù)器首先進(jìn)行用戶名和密碼的驗(yàn)證以及權(quán)限的校驗(yàn)。
然后會(huì)檢查緩存中是否存在該查詢，若存在，返回緩存中存在的結(jié)果。若是不存在就進(jìn)行下一步。注意：Mysql 8就把緩存這塊給砍掉了。
接著進(jìn)行語法和詞法的分析，對(duì)SQl的解析、語法檢測(cè)和預(yù)處理，再由優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃。
Mysql的執(zhí)行器根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃執(zhí)行，調(diào)用存儲(chǔ)引擎的接口進(jìn)行查詢。服務(wù)器將查詢的結(jié)果返回客戶端。
Mysql中語句的執(zhí)行都是都是分層執(zhí)行，每一層執(zhí)行的任務(wù)都不同，直到最后拿到結(jié)果返回，主要分為Service層和引擎層。
在Service層中包含：連接器、分析器、優(yōu)化器、執(zhí)行器。引擎層以插件的形式可以兼容各種不同的存儲(chǔ)引擎，主要包含的有InnoDB和MyISAM兩種存儲(chǔ)引擎。具體的執(zhí)行流程圖如下所示：
5.redo log和binlog了解過嗎？
redo log日志也叫做WAL技術(shù)（Write- Ahead Logging），他是一種先寫日志，并更新內(nèi)存，最后再更新磁盤的技術(shù)，為了就是減少sql執(zhí)行期間的數(shù)據(jù)庫io操作，并且更新磁盤往往是在Mysql比較閑的時(shí)候，這樣就大大減輕了Mysql的壓力。
redo log是固定大小，是物理日志，屬于InnoDB引擎的，并且寫redo log是環(huán)狀寫日志的形式：
如上圖所示：若是四組的redo log文件，一組為1G的大小，那么四組就是4G的大小，其中write pos是記錄當(dāng)前的位置，有數(shù)據(jù)寫入當(dāng)前位置，那么write pos就會(huì)邊寫入邊往后移。
check point記錄擦除的位置，因?yàn)閞edo log是固定大小，所以當(dāng)redo log滿的時(shí)候，也就是write pos追上check point的時(shí)候，需要清除redo log的部分?jǐn)?shù)據(jù)，清除的數(shù)據(jù)會(huì)被持久化到磁盤中，然后將check point向前移動(dòng)。
redo log日志實(shí)現(xiàn)了即使在數(shù)據(jù)庫出現(xiàn)異常宕機(jī)的時(shí)候，重啟后之前的記錄也不會(huì)丟失，這就是crash-safe能力。
binlog稱為歸檔日志，是邏輯上的日志，它屬于Mysql的Server層面的日志，記錄著sql的原始邏輯，主要有兩種模式：一個(gè)是statement格式記錄的是原始的sql，而row格式則是記錄行內(nèi)容。
redo log和binlog記錄的形式、內(nèi)容不同，這兩者日志都能通過自己記錄的內(nèi)容恢復(fù)數(shù)據(jù)。
之所以這兩個(gè)日志同時(shí)存在，是因?yàn)閯傞_始Mysql自帶的引擎MyISAM就沒有crash-safe功能的，并且在此之前Mysql還沒有InnoDB引擎，Mysql自帶的binlog日志只是用來歸檔日志的，所以InnoDB引擎也就通過自己redo log日志來實(shí)現(xiàn)crash-safe功能。
6.線上要給熱點(diǎn)數(shù)據(jù)表添加字段該怎么操作？
首先給表加一個(gè)字段，會(huì)導(dǎo)致掃描全表數(shù)據(jù)，并且會(huì)加MDL寫鎖，所以在線上操作一定要謹(jǐn)慎再謹(jǐn)慎，有可能還沒操作完就導(dǎo)致數(shù)據(jù)庫給搞崩了。
對(duì)于這種情況有限考慮線上的穩(wěn)定的運(yùn)行，加字段是其次，可以通過在alter table后設(shè)定等待的時(shí)間，若是獲取不到鎖后面在進(jìn)行嘗試，并且可以選擇訪問量比較上的時(shí)間段進(jìn)行獲取。
若是能獲取到鎖那是最好了，當(dāng)然即使獲取到鎖也不要阻塞后面的業(yè)務(wù)語句，一切都是以業(yè)務(wù)優(yōu)先為原則。
7.Msyql的索引的底層實(shí)現(xiàn)嗎？為什么不用有序數(shù)組、hash或者二叉樹實(shí)現(xiàn)索引？
Mysql的索引是一種加快查詢速度的數(shù)據(jù)結(jié)構(gòu)，索引就好比書的目錄一樣能夠快速的定位你要查找的位置。
Mysql的索引底層是使用B+樹的數(shù)據(jù)結(jié)構(gòu)進(jìn)行實(shí)現(xiàn)，結(jié)構(gòu)如下圖所示：
索引的一個(gè)數(shù)據(jù)頁的大小是16kb，從磁盤加載到內(nèi)存中是以數(shù)據(jù)頁的大小為單位進(jìn)行加載，然后供查詢操作進(jìn)行查詢，若是查詢的數(shù)據(jù)不在內(nèi)存中，才會(huì)從磁盤中再次加載到內(nèi)存中。
索引的實(shí)現(xiàn)有很多，比如hash。hash是以key-value的形式進(jìn)行存儲(chǔ)，適合于等值查詢的場景，查詢的時(shí)間復(fù)雜度為O(1)，因?yàn)閔ash儲(chǔ)存并不是有序的，所以對(duì)于范圍查詢就可能要遍歷所有數(shù)據(jù)進(jìn)行查詢，而且不同值的計(jì)算還會(huì)出現(xiàn)hash沖突，所以hash并不適合于做Mysql的索引。
有序數(shù)組在等值查詢和范圍查詢性能都是非常好的，那為什么又不用有序數(shù)組作為索引呢？因?yàn)閷?duì)于數(shù)組而言作為索引更新的成本太高，新增數(shù)據(jù)要把后面的數(shù)據(jù)都往后移一位，所以也不采用有序數(shù)組作為索引的底層實(shí)現(xiàn)。
最后二叉樹，主要是因?yàn)槎鏄渲挥卸?#xff0c;一個(gè)節(jié)點(diǎn)存儲(chǔ)的數(shù)據(jù)量非常有限，需要頻繁的隨機(jī)IO讀寫磁盤，若是數(shù)據(jù)量大的情況下二叉的樹高太高，嚴(yán)重影響性能，所以也不采用二叉樹進(jìn)行實(shí)現(xiàn)。
而B+樹是多叉樹，一個(gè)數(shù)據(jù)頁的大小是16kb，在1-3的樹高就能存儲(chǔ)10億級(jí)以上的數(shù)據(jù)，也就是只要訪問磁盤1-3次就足夠了，并且B+樹的葉子結(jié)點(diǎn)上一個(gè)葉子結(jié)點(diǎn)有指針指向下一個(gè)葉子結(jié)點(diǎn)，便于范圍查詢：
8.怎么查看索引是否生效？什么情況下索引會(huì)失效呢？
查看索引是否起作用可以使用explain關(guān)鍵字，查詢后的語句中的key字段，若是使用了索引，該字段會(huì)展示索引的名字。
（1）where條件查詢中使用了or關(guān)鍵字，有可能使用了索引進(jìn)行查詢也會(huì)導(dǎo)致索引失效，若是想使用or關(guān)鍵字，又不想索引失效，只能在or的所有列上都建立索引。
（2）條件查詢中使用like關(guān)鍵字，并且不符合最左前綴原則，會(huì)導(dǎo)致索引失效。
（3）條件查詢的字段是字符串，而錯(cuò)誤的使用where column = 123 數(shù)字類型也會(huì)導(dǎo)致索引失效。
（4）對(duì)于聯(liá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條件查詢的后面對(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.你知道有哪些種類的索引？
索引從數(shù)據(jù)結(jié)構(gòu)進(jìn)行劃分的分為：B+樹索引、hash索引、R-Tree索引、FULLTEXT索引。
索引從物理存儲(chǔ)的角度劃分為：聚族索引和非聚族索引。
從邏輯的角度分為：主鍵索引、普通索引、唯一索引、聯(lián)合索引以及空間索引。
10.你平時(shí)是怎么進(jìn)行SQL優(yōu)化的？
SQL的優(yōu)化主要是對(duì)字段添加索引，主要包含有這四種索引(主鍵索引/唯一索引/全文索引/普通索引)，以及結(jié)合具體的業(yè)務(wù)場景分析具體是使用什么索引最合理。
explain 可以幫助我們?cè)诓徽嬲龍?zhí)行某個(gè)sql語句時(shí)，就執(zhí)行mysql怎樣執(zhí)行，這樣利用我們?nèi)シ治鰏ql指令：
id：查詢的序列號(hào)。
select_type：查詢類型。
table：查詢表名。
type：掃描方式，all表示全表掃描。
possible_keys：可是使用到的索引。
key：實(shí)際使用到的索引。
rows：該sql掃面了多少行。
Extra：sql語句額外的信息，比如排序方式
SQL優(yōu)化方法
（1）對(duì)于條件查詢，首先考慮在條件where和order by后的字段建立索引。（2）避免索引失效，避免where條件后進(jìn)行null值的判斷。（3）避免where后使用!=或<>操作符。（4）避免在where后面進(jìn)行使用函數(shù)。（5）避免where條件后使用or關(guān)鍵字來連接。
上面的這一些都是要注意的，當(dāng)然還有很多的小技巧，都有可能會(huì)導(dǎo)致索引的實(shí)效。
索引的種類
另一方面就是考慮到底是建立哪種索引比較合適，這里以普通索引和唯一索引進(jìn)行舉例說明。
假如我們的業(yè)務(wù)場景是讀多寫少的場景，那么SQL查詢請(qǐng)求過來，假如數(shù)據(jù)已經(jīng)在內(nèi)存中，獲取到數(shù)據(jù)后就直接返回，假如數(shù)據(jù)不在內(nèi)存的數(shù)據(jù)頁中，就會(huì)加載磁盤到內(nèi)存中再返回，對(duì)于這種場景可能對(duì)于普通索引和唯一索引的選擇性能上并沒有明顯的區(qū)別。
但是，一般建議選擇普通索引，在寫多讀少的場景下，這兩者索引的選擇對(duì)性能的影響就比較大了，對(duì)于普通索引的的寫，不管數(shù)據(jù)是否存在于內(nèi)存中，都會(huì)先寫入內(nèi)存中的一小塊叫做chang buffer內(nèi)存中，然后在通過后臺(tái)刷盤，一般會(huì)選擇Mysql比較閑的時(shí)候進(jìn)行刷盤。
而唯一索引就不同了，因?yàn)樗_保索引的唯一性，索引寫數(shù)據(jù)的時(shí)候，假如數(shù)據(jù)不在內(nèi)存中，要先從磁盤中加載數(shù)據(jù)到內(nèi)存中，然后比較是否唯一，所以唯一索引就不能使用chang buffer的優(yōu)化機(jī)制，會(huì)頻繁的進(jìn)行隨機(jī)的磁盤IO。
11.什么是聚簇索引和非聚簇索引？
聚族索引和非聚族索引的主要區(qū)別是：聚族索引的葉子結(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)，而非聚族索引的葉子結(jié)點(diǎn)存儲(chǔ)仍然是索引節(jié)點(diǎn)，只不過有指向?qū)?yīng)數(shù)據(jù)塊的指針。
區(qū)別這兩者的區(qū)別就是來對(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來找到對(duì)應(yīng)的行數(shù)據(jù)，也就是葉子結(jié)點(diǎn)存儲(chǔ)的是行指針，這也可以發(fā)現(xiàn)MyISAM引擎中數(shù)據(jù)文件（.MYI）和索引文件（.MYD）是分開的，索引MyISAM的查找索引樹后，需要根據(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í)索引帶來的好處就是減少了由于數(shù)據(jù)移動(dòng)或者數(shù)據(jù)頁分列導(dǎo)致行數(shù)據(jù)的地址變了而帶來的維護(hù)二級(jí)索引的性能開銷，因?yàn)镮nnoDB的二級(jí)索引不需要更新行指針：
12.什么是回表？回表是怎么產(chǎn)生的呢？
上面說過InnoDB引擎的主鍵索引存儲(chǔ)的是行數(shù)據(jù)，二級(jí)索引的葉子結(jié)點(diǎn)存儲(chǔ)的是索引數(shù)據(jù)以及對(duì)應(yīng)的主鍵，所以回表就是根據(jù)索引進(jìn)行條件查詢，回到主鍵索引樹進(jìn)行搜索的過程：
因?yàn)椴樵冞€要回表一次，再次查詢主鍵索引樹，所以實(shí)際中應(yīng)該盡量避免回表的產(chǎn)生。
13.怎么解決回表的問題？
解決回表問題可以建立聯(lián)合索引進(jìn)行索引覆蓋，如圖所示根據(jù)name字段查詢用戶的name和sex屬性出現(xiàn)了回表問題：
那么我們可以建立下面這個(gè)聯(lián)合索引來解決：
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)槟軌颢@取到要查詢的所有字段，因?yàn)榫筒挥迷倩乇聿樵円淮巍?/p>
14.什么是最左前綴原則？
最左前綴原則可以是聯(lián)合索引的的最左N個(gè)字段，也可以是字符串索引的最左的M個(gè)字符。舉個(gè)例子，假如現(xiàn)在有一個(gè)表的原始數(shù)據(jù)如下所示：
并根據(jù)col3 ，col2的順序建立聯(lián)合索引，此時(shí)聯(lián)合索引樹結(jié)構(gòu)如圖下所示：
葉子結(jié)點(diǎn)中首先會(huì)根據(jù)col3的字符進(jìn)行排序，若是col3相等，在col3相等的值里面再對(duì)col2進(jìn)行排序，假如我們要查詢where col3 like 'Eri%'，就可以快速的定位查詢到Eric。
若是查詢條件為where col3 like '%se'，前面的字符不確定，表示任意字符都可以，這樣就可以導(dǎo)致全表掃描進(jìn)行字符的比較，就會(huì)使索引失效。
15.什么是索引下推？
Mysql5.6之前是沒有索引下推這個(gè)功能，后面為了提高性能，避免不必要的回表5.6之后就有了索引下推優(yōu)化的功能。
假如我們有一個(gè)用戶表，并且使用用戶的name，age兩個(gè)字段建立聯(lián)合索引，name在沒有索引下推的功能，執(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值是否符合要求，直接獲取到主鍵值，然后在回表查詢，回表后再對(duì)比age、ismale是否符合條件。
從上面的數(shù)據(jù)看來其實(shí)name，age兩個(gè)字段建立的聯(lián)合索引，兩個(gè)字段的值會(huì)存儲(chǔ)在聯(lián)合索引樹中，可以直接對(duì)比age字段是否符合查詢的條件age=10，那么索引下推就是做了這些事：
索引下推會(huì)再次根據(jù)你的age進(jìn)行比較，發(fā)現(xiàn)有兩條記錄不符合條件直接過濾掉，符合條件的才會(huì)進(jìn)行回表查詢，這樣就減少了不必要的回表查詢。
16.主鍵使用自增ID還是UUID?能說說原因嗎？
自增ID和UUID作為主鍵的考慮主要有兩方面，一個(gè)是性能另一個(gè)就是存儲(chǔ)的空間大小，一般沒有特定的業(yè)務(wù)要求都不推薦使用UUID作為主鍵。
因?yàn)槭褂肬UID作為主鍵插入并不能保證插入是有序的，有可能會(huì)涉及數(shù)據(jù)的挪動(dòng)，也有可能觸發(fā)數(shù)據(jù)頁的分裂，因?yàn)橐粋€(gè)數(shù)據(jù)頁的大小就是16KB，這樣插入數(shù)據(jù)的成本就會(huì)比較高。
而自增ID作為主鍵的話插入數(shù)據(jù)都是追加操作，不會(huì)有數(shù)據(jù)的移動(dòng)以及數(shù)據(jù)頁的分裂，性能會(huì)比較好。
另一方面就是存儲(chǔ)空間，自增主鍵一般整形只要4個(gè)字節(jié)，長整形才占8字節(jié)的大小空間，而使用UUID作為主鍵存儲(chǔ)空間需要16字節(jié)的大小，會(huì)占用更多的磁盤，在二級(jí)索引中也會(huì)存出一份主鍵索引，這樣多占用消耗的空間就是兩倍，性能低，所以不推薦使用。
17.Mysql是怎么控制并發(fā)的訪問資源？
Mysql內(nèi)部通過鎖機(jī)制實(shí)現(xiàn)對(duì)資源的并發(fā)訪問控制，保證數(shù)據(jù)的一致性，鎖機(jī)制的類型和引擎的種類有關(guān)，MyISAM中默認(rèn)支持的表級(jí)鎖有兩種：共享讀鎖和獨(dú)占寫鎖。表級(jí)鎖在MyISAM和InnoDB的存儲(chǔ)引擎中都支持，但是InnoDB默認(rèn)支持的是行鎖。
MyISAM鎖機(jī)制
Mysql中可以通過以下sql來顯示的在事務(wù)中顯式的進(jìn)行加鎖和解鎖操作：
// 顯式的添加表級(jí)讀鎖 LOCK TABLE 表名 READ // 顯示的添加表級(jí)寫鎖 LOCK TABLE 表名 WRITE // 顯式的解鎖（當(dāng)一個(gè)事務(wù)commit的時(shí)候也會(huì)自動(dòng)解鎖） unlock tables;
（1）MyISAM表級(jí)寫鎖：當(dāng)一個(gè)線程獲取到表級(jí)寫鎖后，只能由該線程對(duì)表進(jìn)行讀寫操作，別的線程必須等待該線程釋放鎖以后才能操作。
（2）MyISAM表級(jí)共享讀鎖：當(dāng)一個(gè)線程獲取到表級(jí)讀鎖后，該線程只能讀取數(shù)據(jù)不能修改數(shù)據(jù)，其它線程也只能加讀鎖，不能加寫鎖。
InnoDB鎖機(jī)制
InnoDB和MyISAM不同的是，InnoDB支持行鎖和事務(wù)，InnoDB中除了有表鎖和行級(jí)鎖的概念，還有Gap Lock（間隙鎖）、Next-key Lock鎖，間隙鎖主要用于范圍查詢的時(shí)候，鎖住查詢的范圍，并且間隙鎖也是解決幻讀的方案。
InnoDB中的行級(jí)鎖是對(duì)索引加的鎖，在不通過索引查詢數(shù)據(jù)的時(shí)候，InnoDB就會(huì)使用表鎖。
但是通過索引查詢的時(shí)候是否使用索引，還要看Mysql的執(zhí)行計(jì)劃，Mysql的優(yōu)化器會(huì)判斷是一條sql執(zhí)行的最佳策略。
若是Mysql覺得執(zhí)行索引查詢還不如全表掃描速度快，那么Mysql就會(huì)使用全表掃描來查詢，這是即使sql語句中使用了索引，最后還是執(zhí)行為全表掃描，加的是表鎖。
18.Mysql的死鎖是怎么發(fā)生的？怎么解決死鎖問題？
死鎖在InnoDB中才會(huì)出現(xiàn)死鎖，MyISAM是不會(huì)出現(xiàn)死鎖，因?yàn)镸yISAM支持的是表鎖，一次性獲取了所有的鎖，其它的線程只能排隊(duì)等候。
而InnoDB默認(rèn)支持行鎖，獲取鎖是分步的，并不是一次性獲取所有的鎖，因此在鎖競爭的時(shí)候就會(huì)出現(xiàn)死鎖的情況。
雖然InnoDB會(huì)出現(xiàn)死鎖，但是并不影響InnoDB成為最受歡迎的存儲(chǔ)引擎，MyISAM可以理解為串行化操作，讀寫有序，因此支持的并發(fā)性能低下。
（1）死鎖案例一：
舉一個(gè)例子，現(xiàn)在數(shù)據(jù)庫表employee中六條數(shù)據(jù)，如下所示：
其中name=ldc的有兩條數(shù)據(jù)，并且name字段為普通索引，分別是id=2和id=3的數(shù)據(jù)行，現(xiàn)在假設(shè)有兩個(gè)事務(wù)分別執(zhí)行下面的兩條sql語句：
// 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í)行查詢操作獲取了第二行數(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ù)開始并且update一條id=1的數(shù)據(jù)行時(shí)，成功獲取到寫鎖，此時(shí)另一個(gè)事務(wù)執(zhí)行也update另一條id=2的數(shù)據(jù)行時(shí)，也成功獲取到寫鎖（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)，因此形成了死鎖。
死鎖的解決方案
首先要解決死鎖問題，在程序的設(shè)計(jì)上，當(dāng)發(fā)現(xiàn)程序有高并發(fā)的訪問某一個(gè)表時(shí)，盡量對(duì)該表的執(zhí)行操作串行化，或者鎖升級(jí)，一次性獲取所有的鎖資源。
然后也可以設(shè)置參數(shù)innodb_lock_wait_timeout，超時(shí)時(shí)間，并且將參數(shù)innodb_deadlock_detect 打開，當(dāng)發(fā)現(xiàn)死鎖的時(shí)候，自動(dòng)回滾其中的某一個(gè)事務(wù)。
19.能說一說Mysql的主從復(fù)制嗎？
讀寫分離
實(shí)現(xiàn)MySQL讀寫分離的前提是我們已經(jīng)將MySQL主從復(fù)制配置完畢，讀寫分離實(shí)現(xiàn)方式：（1）配置多數(shù)據(jù)源。（2）使用mysql的proxy中間件代理工具。
主從復(fù)制的原理
MySQL的主從復(fù)制和讀寫分離兩者有著緊密的聯(lián)系，首先要部署主從復(fù)制，只有主從復(fù)制完成了才能在此基礎(chǔ)上進(jìn)行數(shù)據(jù)的讀寫分離。
讀寫分離的原理
讀寫分離就是只在主服務(wù)器上寫，只在從服務(wù)器上讀。基本原理是讓主數(shù)據(jù)庫處理事務(wù)性查詢，而從服務(wù)器處理select查詢。數(shù)據(jù)庫復(fù)制被用來把事務(wù)性查詢導(dǎo)致的變更同步到從數(shù)據(jù)庫中。
20.能說一說分庫分表嗎？怎么分？
首先為什么要分表？（1） 如果一個(gè)表的每條記錄的內(nèi)容很大，那么就需要更多的IO操作，如果字段值比較大，而使用頻率相對(duì)比較低，可以將大字段移到另一張表中，當(dāng)查詢不查大字段的時(shí)候，這樣就減少了I/O操作（2）如果表的數(shù)據(jù)量非常非常大，那么查詢就變的比較慢；也就是表的數(shù)據(jù)量影響查詢的性能。（3）表中的數(shù)據(jù)本來就有獨(dú)立性，例如分別記錄各個(gè)地區(qū)的數(shù)據(jù)或者不同時(shí)期的數(shù)據(jù)，特別是有些數(shù)據(jù)常用，而另外一些數(shù)據(jù)不常用。（4） 分表技術(shù)有(水平分割和垂直分割)
垂直分割
垂直分割是指數(shù)據(jù)表列的拆分，把一張列比較多的表拆分為多張表。垂直分割一般用于拆分大字段和訪問頻率低的字段，分離冷熱數(shù)據(jù)。
垂直分割比較常見：例如博客系統(tǒng)中的文章表，比如文章tbl_articles(id, titile, summary, content, user_id, create_time)，因?yàn)槲恼轮械膬?nèi)容content會(huì)比較長，放在tbl_articles中會(huì)嚴(yán)重影響表的查詢速度，所以將內(nèi)容放到tbl_articles_detail(article_id, content)，像文章列表只需要查詢tbl_articles中的字段即可。
垂直拆分的優(yōu)點(diǎn)：可以使得行數(shù)據(jù)變小，在查詢時(shí)減少讀取的Block數(shù)，減少I/O次數(shù)。此外，垂直分區(qū)可以簡化表的結(jié)構(gòu)，易于維護(hù)。
垂直拆分的缺點(diǎn)：主鍵會(huì)出現(xiàn)冗余，需要管理冗余列，并會(huì)引起Join操作，可以通過在應(yīng)用層進(jìn)行Join來解決。此外，垂直分區(qū)會(huì)讓事務(wù)變得更加復(fù)雜。
水平分割
水平拆分是指數(shù)據(jù)表行數(shù)據(jù)的拆分，表的行數(shù)超過500萬行或者單表容量超過10GB時(shí)，查詢就會(huì)變慢，這時(shí)可以把一張的表的數(shù)據(jù)拆成多張表來存放。水平分表盡可能使每張表的數(shù)據(jù)量相當(dāng)，比較均勻。
水平拆分會(huì)給應(yīng)用增加復(fù)雜度，它通常在查詢時(shí)需要多個(gè)表名，查詢所有數(shù)據(jù)需要union操作。在許多數(shù)據(jù)庫應(yīng)用中，這種復(fù)雜性會(huì)超過它帶來的優(yōu)點(diǎn)。
因?yàn)橹灰饕P(guān)鍵字不大，則在索引用于查詢時(shí)，表中增加2-3倍數(shù)據(jù)量，查詢時(shí)也就增加讀一個(gè)索引層的磁盤次數(shù)，所以水平拆分要考慮數(shù)據(jù)量的增長速度，根據(jù)實(shí)際情況決定是否需要對(duì)表進(jìn)行水平拆分。
水平分割最重要的是找到分割的標(biāo)準(zhǔn)，不同的表應(yīng)根據(jù)業(yè)務(wù)找出不同的標(biāo)準(zhǔn)
用戶表可以根據(jù)用戶的手機(jī)號(hào)段進(jìn)行分割如user183、user150、user153、user189等，每個(gè)號(hào)段就是一張表。
用戶表也可以根據(jù)用戶的id進(jìn)行分割，加入分3張表user0,user1,user2，如果用戶的id%3=0就查詢user0表，如果用戶的id%3=1就查詢user1表。
對(duì)于訂單表可以按照訂單的時(shí)間進(jìn)行分表。
分庫分表技術(shù)
現(xiàn)在市面上主要的分庫分表技術(shù)有mycat和sharding-jdbc。
總結(jié)
以上是生活随笔為你收集整理的面试，MySQL 搞透这 20 道就稳了的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 【教程】Linux DNS 服务器安装、
- 下一篇： 超简单的Oracle 数据库安装教程