高頻 MySQL 面試題,值得地鐵上看一波

語言: CN / TW / HK

MySQL 索引使用什麼資料結構?為什麼用 B+做索引?
使用B+樹。

這個問題,可以在腦子裡面先思考一下,如果讓你來設計資料庫的索引,你會怎麼設計?

我們還是用Why?What?How?三步法來看這個問題。

為什麼會需要索引?索引是什麼?索引怎麼用的?

再思考為什麼需要B+樹?B+樹是什麼?B+樹怎麼用?

答:大部分程式主要的功能都是對資料的處理,寫入、查詢、轉化、輸出。最形象的比喻就是樹和內容和目錄的關係,目錄就是索引,我們根據目錄能快速拿到想要內容的頁碼。

為什麼是B+樹,有這個幾個理由:

如果是用AVL平衡二叉樹,樹高度太高,索引查詢需要訪問磁碟,每次訪問以節點為單位進行磁碟I/O ,需要儘量減少資料讀取的I/O操作,所以樹高度一定不能太高,儲存千萬級別的資料,實踐中 B+ 樹的高度也就 4或者5。
B+樹經常用來比較的是B樹,B+樹相比B樹有個很大的特點是B+樹所有關鍵字都出現在葉子結點的連結串列中(稠密索引),且連結串列中的關鍵字恰好是有序的,對於範圍查詢,比如15~50,B樹需要中序遍歷二叉樹,但是B+樹直接在葉子節點順序訪問就可以了。
什麼是最左匹配原則?
首先說明一點:

最左字首匹配原則:在MySQL建立聯合索引時會遵守最左字首匹配原則,即最左優先,在檢索資料時從聯合索引的最左邊開始匹配。

打個比方,我們有張student 表,我們根據學院編號+班級建立了一個聯合索引 index_magor_class(magor,class), 這個索引由二個欄位組成。

索引的底層是一顆B+樹,那麼聯合索引的底層也就是一顆B+樹,只不過聯合索引的B+樹節點中儲存的是逗號分隔的多個值。

舉例:建立一個 index_magor_class(magor,class) 的聯合索引,那麼它的索引樹就是下圖的樣子。

它是先根據magor排序,再根據class排序,如果索引後面還有欄位,繼續以此類推。

我們查詢的where 條件如果只傳入了班級,是走不到聯合索引的,但是如果只傳了學院編號,是可能會走到聯合索引的。(為什麼說可能,MYSQL的執行計劃和查詢的實際執行過程並不完全吻合,比如你資料庫資料量很少,可能直接全量遍歷速度更快,就不走索引了)

在建表的時候如何設計索引的?有沒有做過索引優化 ?
1、利用覆蓋索引來進行查詢操作,來避免回表操作。

說明:如果一本書需要知道第11章是什麼標題,會翻開第11章對應的那一頁嗎?目錄瀏覽一下就好,這個目錄就是起到覆蓋索引的作用。

什麼意思,比如你主鍵索引是學號,你寫select 語句的時候,直接select 學號 from table 就可以了,不用select 其他欄位,一般除非非常有必要,儘量按需select 欄位,少用或不用 select, 不然還需要回表。

這裡我解釋一下回表,比如我們表主鍵索引是學號,另外我們還根據手機號也建了索引,如果我們where 條件是手機號,分二種情況:

正例:IDB能夠建立索引的種類分為【主鍵索引、唯一索引、普通索引】,而覆蓋索引是一種查詢的一種效果,用explain的結果,extra列會出現:using index.

如果我們select 獲取的欄位是學號,直接在手機號的索引表就能獲取到資料,不需要回表;
如果我們select 的時候還有其他欄位,我們查詢的時候流程是這樣的,先根據手機號查到學號,再根據學號去主鍵索引表查詢資料,這個過程叫回表。
2、業務上具有唯一特性的欄位,即使是組合欄位,也建議建成唯一索引。說明:不要以為唯一索引影響了insert速度,這個速度損耗可以忽略,但提高查詢速度是明顯的;另外,即使在應用層做了非常完善的校驗和控制,只要沒有唯一索引,根據墨菲定律,必然有髒資料產生。

3、超過三個表禁止join。需要join的欄位,資料型別保持絕對一致;多表關聯查詢時,保證被關聯的欄位需要有索引。說明:即使雙表join也要注意表索引、SQL效能。

4、在varchar欄位上建立索引時,必須指定索引長度,沒必要對全欄位建立索引,根據實際文字區分度決定索引長度。說明:索引的長度與區分度是一對矛盾體,一般對字串型別資料,長度為20的索引,區分度會高達90%以上,可以使用count(distinct left(列名, 索引長度))/count(*)的區分度來確定。

5、頁面搜尋嚴禁左模糊或者全模糊,如果需要請走搜尋引擎來解決。說明:索引檔案具有B-Tree的最左字首匹配特性,如果左邊的值未確定,那麼無法使用此索引。

6、SQL效能優化的目標:至少要達到 range 級別,要求是ref級別,如果可以是const最好。說明:

1)const 單表中最多隻有一個匹配行(主鍵或者唯一索引),在優化階段即可讀取到資料。
2)ref 指的是使用普通的索引。(normal index)
3)range 對索引進行範圍檢索。反例:explain表的結果,type=index,索引物理檔案全掃描,速度非常慢,這個index級別比較range還低,與全表掃描是小巫見大巫。
7、建組合索引的時候,區分度最高的在最左邊。正例:如果where a=? and b=? ,a列的幾乎接近於唯一值,那麼只需要單建idx_a索引即可。說明:存在非等號和等號混合判斷條件時,在建索引時,請把等號條件的列前置。如:where c>? and d=? 那麼即使c的區分度更高,也必須把d放在索引的最前列,即建立組合索引idx_d_c。

8、防止因欄位型別不同造成的隱式轉換,導致索引失效。

MyBatis用過嗎? 一二級快取清楚嗎?
一級快取 Mybatis的一級快取是指SQLSession,一級快取的作用域是SQlSession, Mabits預設開啟一級快取。在同一個SqlSession中,執行相同的SQL查詢時;第一次會去查詢資料庫,並寫在快取中,第二次會直接從快取中取。當執行SQL時候兩次查詢中間發生了增刪改的操作,則SQLSession的快取會被清空。每次查詢會先去快取中找,如果找不到,再去資料庫查詢,然後把結果寫到快取中。Mybatis的內部快取使用一個HashMap,key為hashcode+statementId+sql語句。Value為查詢出來的結果集對映成的java物件。SqlSession執行insert、update、delete等操作commit後會清空該SQLSession快取。
二級快取 二級快取是 mapper級別的,Mybatis預設是沒有開啟二級快取的。第一次呼叫mapper下的SQL去查詢使用者的資訊,查詢到的資訊會存放在該mapper對應的二級快取區域。第二次呼叫namespace下的mapper對映檔案中,相同的sql去查詢使用者資訊,會去對應的二級快取內取結果。

MySQL 主從同步怎麼做的?binlog清楚嗎?
Master 資料庫只要發生變化,立馬記錄到Binary log 日誌檔案中
Slave資料庫啟動一個I/O thread連線Master資料庫,請求Master變化的二進位制日誌
Slave I/O獲取到的二進位制日誌,儲存到自己的Relay log 日誌檔案中。
Slave 有一個 SQL thread定時檢查Realy log是否變化,變化那麼就更新資料

MySQL 有沒有做分庫分表?怎麼設計的?
Why?:

當一張表的資料達到幾千萬時,你查詢一次所花的時間會變多,如果有聯合查詢的話,我想有可能會死在那兒了。分表的目的就在於此,減小資料庫的負擔,縮短查詢時間。

mysql中有一種機制是表鎖定和行鎖定,是為了保證資料的完整性。表鎖定表示你們都不能對這張表進行操作,必須等我對錶操作完才行。行鎖定也一樣,別的sql必須等我對這條資料操作完了,才能對這條資料進行操作。

When?(什麼時候需要分表?):

單錶行數超過500萬行或者單表容量超過2GB,才推薦進行分庫分表。說明:如果預計三年後的資料量根本達不到這個級別,請不要在建立表時就分庫分表。

反例:某業務三年總資料量才2萬行,卻分成1024張表,問:你為什麼這麼設計?答:分1024張表,不是標配嗎?

How?(分庫分表有幾種策略):

垂直拆分 or 水平拆分

拆分中介軟體,詳細可以參考:

Sharding-sphere,前身是sharding-jdbc;噹噹的分庫分表中介軟體
TDDL:jar,Taobao Distribute Data Layer;
Mycat:中介軟體。
注:工具的利弊,請自行調研,官網和社群優先。

按照userId緯度拆分,安琪拉見過的常見的有,根據 userId % 64 取模拆0~63編號的64張表,
固定位拆,取userId 指定二位,例如倒數2,3位組成00~99 一共100張表的,百庫表表。
hash: userId hash一下,然後 % 表數;
Range: 另外還有按照userId 指定範圍拆的,0-1千萬一張表,這種用的比較少,容易產生熱點。
把不同業務域的表拆成不同庫,例如訂單相關表、使用者資訊相關表、營銷相關表分開在不同庫;
把大欄位獨立儲存到一張表中
把不常用的欄位單獨拿出來儲存到一張表
用userId做的分庫分表,現在需要用電話號碼查詢怎麼辦?
和回表邏輯一樣,單獨建一個電話號碼索引表,存放電話號碼和userId,查詢時先根據電話號碼查詢userId,然後再根據userId查詢資料。