MySQL高頻面試題，硬gang面試官
MySQL 索引使用什么數(shù)據(jù)結(jié)構(gòu)？為什么用 B+做索引？
使用B+樹(shù)。
這個(gè)問(wèn)題，可以在腦子里面先思考一下，如果讓你來(lái)設(shè)計(jì)數(shù)據(jù)庫(kù)的索引，你會(huì)怎么設(shè)計(jì)？
我們還是用Why？What？How？三步法來(lái)看這個(gè)問(wèn)題。
為什么會(huì)需要索引？索引是什么？索引怎么用的？
再思考為什么需要B+樹(shù)？B+樹(shù)是什么？B+樹(shù)怎么用？
答：大部分程序主要的功能都是對(duì)數(shù)據(jù)的處理，寫(xiě)入、查詢(xún)、轉(zhuǎn)化、輸出。最形象的比喻就是樹(shù)和內(nèi)容和目錄的關(guān)系，目錄就是索引，我們根據(jù)目錄能快速拿到想要內(nèi)容的頁(yè)碼。
為什么是B+樹(shù)，有這個(gè)幾個(gè)理由：
如果是用AVL平衡二叉樹(shù)，樹(shù)高度太高，索引查詢(xún)需要訪問(wèn)磁盤(pán)，每次訪問(wèn)以節(jié)點(diǎn)為單位進(jìn)行磁盤(pán)I/O ，需要盡量減少數(shù)據(jù)讀取的I/O操作，所以樹(shù)高度一定不能太高，存儲(chǔ)千萬(wàn)級(jí)別的數(shù)據(jù)，實(shí)踐中 B+ 樹(shù)的高度也就 4或者5。 B+樹(shù)經(jīng)常用來(lái)比較的是B樹(shù)，B+樹(shù)相比B樹(shù)有個(gè)很大的特點(diǎn)是B+樹(shù)所有關(guān)鍵字都出現(xiàn)在葉子結(jié)點(diǎn)的鏈表中（稠密索引），且鏈表中的關(guān)鍵字恰好是有序的，對(duì)于范圍查找，比如15～50，B樹(shù)需要中序遍歷二叉樹(shù)，但是B+樹(shù)直接在葉子節(jié)點(diǎn)順序訪問(wèn)就可以了。
什么是最左匹配原則？
首先說(shuō)明一點(diǎn)：
最左前綴匹配原則：在MySQL建立聯(lián)合索引時(shí)會(huì)遵守最左前綴匹配原則，即最左優(yōu)先，在檢索數(shù)據(jù)時(shí)從聯(lián)合索引的最左邊開(kāi)始匹配。
打個(gè)比方，我們有張student 表，我們根據(jù)學(xué)院編號(hào)+班級(jí)建立了一個(gè)聯(lián)合索引 index_magor_class(magor,class), 這個(gè)索引由二個(gè)字段組成。
索引的底層是一顆B+樹(shù)，那么聯(lián)合索引的底層也就是一顆B+樹(shù)，只不過(guò)聯(lián)合索引的B+樹(shù)節(jié)點(diǎn)中存儲(chǔ)的是逗號(hào)分隔的多個(gè)值。
舉例：創(chuàng)建一個(gè) index_magor_class(magor,class) 的聯(lián)合索引，那么它的索引樹(shù)就是下圖的樣子。
它是先根據(jù)magor排序，再根據(jù)class排序，如果索引后面還有字段，繼續(xù)以此類(lèi)推。
我們查詢(xún)的where 條件如果只傳入了班級(jí)，是走不到聯(lián)合索引的，但是如果只傳了學(xué)院編號(hào)，是可能會(huì)走到聯(lián)合索引的。（為什么說(shuō)可能，MYSQL的執(zhí)行計(jì)劃和查詢(xún)的實(shí)際執(zhí)行過(guò)程并不完全吻合，比如你數(shù)據(jù)庫(kù)數(shù)據(jù)量很少，可能直接全量遍歷速度更快，就不走索引了）
在建表的時(shí)候如何設(shè)計(jì)索引的？有沒(méi)有做過(guò)索引優(yōu)化 ？
1、利用覆蓋索引來(lái)進(jìn)行查詢(xún)操作，來(lái)避免回表操作。
說(shuō)明：如果一本書(shū)需要知道第11章是什么標(biāo)題，會(huì)翻開(kāi)第11章對(duì)應(yīng)的那一頁(yè)嗎？目錄瀏覽一下就好，這個(gè)目錄就是起到覆蓋索引的作用。
什么意思，比如你主鍵索引是學(xué)號(hào)，你寫(xiě)select 語(yǔ)句的時(shí)候，直接select 學(xué)號(hào) from table 就可以了，不用select 其他字段，一般除非非常有必要，盡量按需select 字段，少用或不用?select， 不然還需要回表。
這里我解釋一下回表，比如我們表主鍵索引是學(xué)號(hào)，另外我們還根據(jù)手機(jī)號(hào)也建了索引，如果我們where 條件是手機(jī)號(hào)，分二種情況：
正例：IDB能夠建立索引的種類(lèi)分為【主鍵索引、唯一索引、普通索引】，而覆蓋索引是一種查詢(xún)的一種效果，用explain的結(jié)果，extra列會(huì)出現(xiàn)：using index.
如果我們select 獲取的字段是學(xué)號(hào)，直接在手機(jī)號(hào)的索引表就能獲取到數(shù)據(jù)，不需要回表； 如果我們select 的時(shí)候還有其他字段，我們查詢(xún)的時(shí)候流程是這樣的，先根據(jù)手機(jī)號(hào)查到學(xué)號(hào)，再根據(jù)學(xué)號(hào)去主鍵索引表查詢(xún)數(shù)據(jù)，這個(gè)過(guò)程叫回表。
2、業(yè)務(wù)上具有唯一特性的字段，即使是組合字段，也建議建成唯一索引。說(shuō)明：不要以為唯一索引影響了insert速度，這個(gè)速度損耗可以忽略，但提高查找速度是明顯的；另外，即使在應(yīng)用層做了非常完善的校驗(yàn)和控制，只要沒(méi)有唯一索引，根據(jù)墨菲定律，必然有臟數(shù)據(jù)產(chǎn)生。
3、超過(guò)三個(gè)表禁止join。需要join的字段，數(shù)據(jù)類(lèi)型保持絕對(duì)一致；多表關(guān)聯(lián)查詢(xún)時(shí)，保證被關(guān)聯(lián)的字段需要有索引。說(shuō)明：即使雙表join也要注意表索引、SQL性能。
4、在varchar字段上建立索引時(shí)，必須指定索引長(zhǎng)度，沒(méi)必要對(duì)全字段建立索引，根據(jù)實(shí)際文本區(qū)分度決定索引長(zhǎng)度。說(shuō)明：索引的長(zhǎng)度與區(qū)分度是一對(duì)矛盾體，一般對(duì)字符串類(lèi)型數(shù)據(jù)，長(zhǎng)度為20的索引，區(qū)分度會(huì)高達(dá)90%以上，可以使用count(distinct left(列名, 索引長(zhǎng)度))/count(*)的區(qū)分度來(lái)確定。
5、頁(yè)面搜索嚴(yán)禁左模糊或者全模糊，如果需要請(qǐng)走搜索引擎來(lái)解決。說(shuō)明：索引文件具有B-Tree的最左前綴匹配特性，如果左邊的值未確定，那么無(wú)法使用此索引。
6、SQL性能優(yōu)化的目標(biāo)：至少要達(dá)到 range 級(jí)別，要求是ref級(jí)別，如果可以是const最好。說(shuō)明：
1）const 單表中最多只有一個(gè)匹配行（主鍵或者唯一索引），在優(yōu)化階段即可讀取到數(shù)據(jù)。 2）ref 指的是使用普通的索引。（normal index） 3）range 對(duì)索引進(jìn)行范圍檢索。反例：explain表的結(jié)果，type=index，索引物理文件全掃描，速度非常慢，這個(gè)index級(jí)別比較range還低，與全表掃描是小巫見(jiàn)大巫。
7、建組合索引的時(shí)候，區(qū)分度最高的在最左邊。正例：如果where a=? and b=? ，a列的幾乎接近于唯一值，那么只需要單建idx_a索引即可。說(shuō)明：存在非等號(hào)和等號(hào)混合判斷條件時(shí)，在建索引時(shí)，請(qǐng)把等號(hào)條件的列前置。如：where c>? and d=? 那么即使c的區(qū)分度更高，也必須把d放在索引的最前列，即建立組合索引idx_d_c。
8、防止因字段類(lèi)型不同造成的隱式轉(zhuǎn)換，導(dǎo)致索引失效。
MyBatis用過(guò)嗎? 一二級(jí)緩存清楚嗎？
一級(jí)緩存?
Mybatis的一級(jí)緩存是指SQLSession，一級(jí)緩存的作用域是SQlSession, Mabits默認(rèn)開(kāi)啟一級(jí)緩存。在同一個(gè)SqlSession中，執(zhí)行相同的SQL查詢(xún)時(shí)；第一次會(huì)去查詢(xún)數(shù)據(jù)庫(kù)，并寫(xiě)在緩存中，第二次會(huì)直接從緩存中取。當(dāng)執(zhí)行SQL時(shí)候兩次查詢(xún)中間發(fā)生了增刪改的操作，則SQLSession的緩存會(huì)被清空。每次查詢(xún)會(huì)先去緩存中找，如果找不到，再去數(shù)據(jù)庫(kù)查詢(xún)，然后把結(jié)果寫(xiě)到緩存中。Mybatis的內(nèi)部緩存使用一個(gè)HashMap，key為hashcode+statementId+sql語(yǔ)句。Value為查詢(xún)出來(lái)的結(jié)果集映射成的java對(duì)象。SqlSession執(zhí)行insert、update、delete等操作commit后會(huì)清空該SQLSession緩存。二級(jí)緩存
二級(jí)緩存是 mapper級(jí)別的，Mybatis默認(rèn)是沒(méi)有開(kāi)啟二級(jí)緩存的。第一次調(diào)用mapper下的SQL去查詢(xún)用戶(hù)的信息，查詢(xún)到的信息會(huì)存放在該mapper對(duì)應(yīng)的二級(jí)緩存區(qū)域。第二次調(diào)用namespace下的mapper映射文件中，相同的sql去查詢(xún)用戶(hù)信息，會(huì)去對(duì)應(yīng)的二級(jí)緩存內(nèi)取結(jié)果。
MySQL 主從同步怎么做的？binlog清楚嗎？
Master 數(shù)據(jù)庫(kù)只要發(fā)生變化，立馬記錄到Binary log 日志文件中
Slave數(shù)據(jù)庫(kù)啟動(dòng)一個(gè)I/O thread連接Master數(shù)據(jù)庫(kù)，請(qǐng)求Master變化的二進(jìn)制日志
Slave I/O獲取到的二進(jìn)制日志，保存到自己的Relay log 日志文件中。
Slave 有一個(gè) SQL thread定時(shí)檢查Realy log是否變化，變化那么就更新數(shù)據(jù)
MySQL 有沒(méi)有做分庫(kù)分表？怎么設(shè)計(jì)的？
Why?:
當(dāng)一張表的數(shù)據(jù)達(dá)到幾千萬(wàn)時(shí)，你查詢(xún)一次所花的時(shí)間會(huì)變多，如果有聯(lián)合查詢(xún)的話(huà)，我想有可能會(huì)死在那兒了。分表的目的就在于此，減小數(shù)據(jù)庫(kù)的負(fù)擔(dān)，縮短查詢(xún)時(shí)間。
mysql中有一種機(jī)制是表鎖定和行鎖定，是為了保證數(shù)據(jù)的完整性。表鎖定表示你們都不能對(duì)這張表進(jìn)行操作，必須等我對(duì)表操作完才行。行鎖定也一樣，別的sql必須等我對(duì)這條數(shù)據(jù)操作完了，才能對(duì)這條數(shù)據(jù)進(jìn)行操作。
When？（什么時(shí)候需要分表？）：
單表行數(shù)超過(guò)500萬(wàn)行或者單表容量超過(guò)2GB，才推薦進(jìn)行分庫(kù)分表。說(shuō)明：如果預(yù)計(jì)三年后的數(shù)據(jù)量根本達(dá)不到這個(gè)級(jí)別，請(qǐng)不要在創(chuàng)建表時(shí)就分庫(kù)分表。
反例：某業(yè)務(wù)三年總數(shù)據(jù)量才2萬(wàn)行，卻分成1024張表，問(wèn)：你為什么這么設(shè)計(jì)？答：分1024張表，不是標(biāo)配嗎？
How？（分庫(kù)分表有幾種策略）：
垂直拆分 or 水平拆分
拆分中間件,詳細(xì)可以參考:
Sharding-sphere，前身是sharding-jdbc；當(dāng)當(dāng)?shù)姆謳?kù)分表中間件 TDDL：jar，Taobao Distribute Data Layer； Mycat：中間件。
注：工具的利弊，請(qǐng)自行調(diào)研，官網(wǎng)和社區(qū)優(yōu)先。
按照userId緯度拆分，安琪拉見(jiàn)過(guò)的常見(jiàn)的有，根據(jù) userId % 64 取模拆0～63編號(hào)的64張表， 固定位拆，取userId 指定二位，例如倒數(shù)2，3位組成00～99 一共100張表的，百庫(kù)表表。 hash: userId hash一下，然后 % 表數(shù)； Range: 另外還有按照userId 指定范圍拆的，0-1千萬(wàn)一張表，這種用的比較少，容易產(chǎn)生熱點(diǎn)。 把不同業(yè)務(wù)域的表拆成不同庫(kù)，例如訂單相關(guān)表、用戶(hù)信息相關(guān)表、營(yíng)銷(xiāo)相關(guān)表分開(kāi)在不同庫(kù)； 把大字段獨(dú)立存儲(chǔ)到一張表中 把不常用的字段單獨(dú)拿出來(lái)存儲(chǔ)到一張表
用userId做的分庫(kù)分表，現(xiàn)在需要用電話(huà)號(hào)碼查詢(xún)?cè)趺崔k？
和回表邏輯一樣，單獨(dú)建一個(gè)電話(huà)號(hào)碼索引表，存放電話(huà)號(hào)碼和userId，查詢(xún)時(shí)先根據(jù)電話(huà)號(hào)碼查詢(xún)userId，然后再根據(jù)userId查詢(xún)數(shù)據(jù)。
如有文章對(duì)你有幫助，
“在看”和轉(zhuǎn)發(fā)是對(duì)我最大的支持！
推薦：
點(diǎn)擊領(lǐng)取：151個(gè)大廠面試講解！（圖片可上下滑動(dòng)！）??
