程式設計師的MySQL水平如何體現出來？

關於MySQL的索引,曾經進行過一次總結,文章連結在這裡 Mysql索引原理及其優化.

1. 什麼是索引?

索引是一種資料結構,可以幫助我們快速地進行資料的查詢.

2. 索引是個什麼樣的資料結構呢?

索引的資料結構和具體儲存引擎的實現有關, 在MySQL中使用較多的索引有Hash索引,B+樹索引等,而我們經常使用的InnoDB儲存引擎的預設索引實現為:B+樹索引.

背景介紹

我的工作是python伺服器開發工程師，最近準備看看其他工作機會，也面試了一些公司。MySQL基本是一定會被問到的知識點，所以最近又開始惡補相關的知識點。面試過程中越來越覺得面試官真的很重要，他是面試者瞭解公司最直接的一個途徑，也是公司技術的把關者。所以我也在思考一個問題，假如我是面試官我會怎麼考察面試者？

面試的宗旨

首先面試最本質的目的是招收合理的人來工作，所以應該要避免的問題是「面試內容是造火箭，結果工作內容卻是搬磚」。所以要問的問題最好是貼近生產環境的。其次面試通常會涉及很多概念，工作比較久的同學通常容易忘記這些東西。所以我覺得沒有必要考察概念，而是考察這些概念的本質，這些本質的東西是不容易忘記的。

這裡說明一下，通常業務中用得最多的是Innodb，所以以下問題都是基於Innodb的。

問題一：考察資料隔離和事務

第一階段：

MySQL的隔離級別有如下四種，預設的級別是什麼？

READ-UNCOMMITTED

READ-COMMITTED

REPEATABLE-READ

SERIALIZABLE

REPEATABLE-READ。這個應該是常識，大家都知道的。這裡需要讓面試者解釋一下隔離級別的意義，因為後面的問題是基於這個概念的。如果面試者忘了概念建議提示一下，繼續下一個問題。

READ-UNCOMMITTED：表示可以讀取到未提交的資料

READ-COMMITTED：可以讀取到已經提交的資料

REPEATABLE-READ：可重複讀，每次讀取的結果都是一樣的。

SERIALIZABLE 完全序列化的讀，每次讀都需要獲得共享鎖，讀寫相互都會阻塞

這裡很多面試官會問一個REPEATABLE-READ怎麼解決幻讀的問題。當時有點蒙，「幻讀」是什麼東西。其實本質上很簡單，幻讀其實就是MySQL怎麼確保「可重複讀」這個功能的。具體的名詞叫資料多版本併發控制（MVCC）。不過這個應該是比較底層的知識，屬於Nice to have了。

第二階段

有一張表user_count，這個表記錄的是不同國家使用者的個數。現在如下程式碼會在以上四個級別的隔離中分別執行一次，請問結果分別是什麼？注意，以下程式碼是在5000個執行緒中執行的，所以是併發執行的。

資料樣例：

+--------+--------+

| region | number |

| us | 0 |

| cn | 0 |

程式碼：

@dec_multi_thread_run(5000)

def do():

try:

# get old num from table

num = "select number from user_count where region = 'cn'"

# update to new value

"update user_count set number = %s where region = 'cn' " % (num +1)

except:

rollback()

else:

commit()

首先問這個問題的話，面試者肯定知道答案不是5000，而是小於5000。大部分人會根據隔離級別越來越高判斷結果的值會越來越小，而真實結果如下。

read uncommitted，3271

read committed，2638

repeatable read，2551

serializable，3052

對於前三個結果遞減比較容易解釋，因為隨著事務隔離級別變高越不可能讀到髒資料。但對於serializable，直觀的結果應該是返回5000，因為序列化級別是不會讀到髒資料的。但實際上這裡考察的是一個會出現死鎖的問題。serializable級別會對讀取加上一個共享鎖，也就是說在多執行緒下會出現很多事務同時獲取到了共享鎖。然後當要執行update操作去獲取排它鎖時就會出現死鎖了。也就是說某些更新會失敗造成結果小於5000。這裡考察到了serializable具體的底層執行方式。

如果問的深入一點，可以問下MySQL檢查死鎖的方式以及怎麼自動解除死鎖的。當然，這個也是Nice to have的。在看參考資料1的時候，有提到對高併發業務的優化。具體的方式就是關閉死鎖檢測，然後用一個較短的timout時間替代。不過我沒有親自驗證，有興趣的同學可以自行驗證一下。

第三階段

怎麼優化上面的程式碼，使結果為5000

這個比較簡單，主要是考察一下面試者知不知道鎖的概念。 select 語句後面加一個for update就解決問題了。

如果把語句改成update user_count set number = number + 1 where region = 'cn' 在四個事務級別下結果分別是多少？

結果都是5000，因為MySQL會預設在insert,update,delete時增加一個排他鎖，Nice to have。

問題二：索引

有一個user表，id是自增主鍵，記錄的是使用者的資訊。資料量是1000萬行，請建立索引並優化用到的查詢。

+--------+--------------+----------+----------+--------------+

| id | country user_ name sex create_time |

| 1 | cn | alice | female | 1559476160 |

| 2 | us | bob | male | 1559476170 |

用到的查詢語句

-- 查詢叫Alice的人

select * from user where user_name = "alice";

-- 查詢國家是us叫Alice的人

select * from user where country = "us" and user_name = "alice";

-- 查詢所有叫Alice的女性

select * from user where sex = "female" and user_name = "alice";

-- 展示最新註冊的10個使用者

select * from user order by create_time desc limit 10;

最優解是隻用一個聯合索引(user_name, country)並調整一下語句2和語句3的查詢順序，username放在前面。

針對第一個語句，只查詢user_name也能利用到聯合索引。針對語句2完美利用了聯合索引。針對語句三不用對sex建立索引，因為區分度太低沒有意義。針對語句4自增id預設就是按照時間遞增的，所以沒有必要再增加索引了。

第二階段：

通常在設計資料庫的時候都會用到自增id，具體的原因是什麼？

答案是增加資料插入的速度。知道這裡就夠了。下一個階段是Nice to Have

第三階段：

MySQL索引的資料結構是什麼？為什麼要用這個結構？

因為MySQL儲存的資料結構用的是B+樹。B+樹和B樹（多路平衡查詢樹）的主要區別是B+樹所有的資料都是儲存在葉子節點的。通常B+樹的層級都是很少的，單個非根節點會儲存很多的資料，這樣可以減少讀取索引的次數。因為機械磁碟擅長的是大塊地順序讀寫，一次性讀到越多的資料越有利於快速查詢，而且B+樹有個好處，所有的葉子節點都是指針相連的，可以快速地進行全表便利。所以是個特別適合資料庫的資料結構。

以上是我能想到的兩大問題（其實我就準備了這些 ），日常開發中我覺得知道MySQL的這兩個問題應該就夠了，如果有其他好的問題還請各位不吝賜教