怎么面試程式員的MySQL水平?

背景介紹

我的作業是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的這兩個問題應該就夠了，如果有其他好的問題還請各位不吝賜教~