作者丨呼延十
排版丨Java團長
juejin.im/post/5d351303f265da1bd30596f9
前言
本文主要受眾為開發人員,所以不涉及到MySQL的服務部署等操作,且內容較多,大家準備好耐心和瓜子礦泉水.
前一陣系統的學習了一下MySQL,也有一些實際操作經驗,偶然看到一篇和MySQL相關的面試文章,發現其中的一些問題自己也回答不好,雖然知識點大部分都知道,但是無法將知識串聯起來.
因此決定搞一個MySQL靈魂100問,試著用回答問題的方式,讓自己對知識點的理解更加深入一點.
此文不會事無巨細的從select的用法開始講解mysql,主要針對的是開發人員需要知道的一些MySQL的知識點,主要包括索引,事務,優化等方面,以在面試中高頻的問句形式給出答案.
索引相關
關于MySQL的索引,曾經進行過一次總結,文章鏈接在這里?Mysql索引原理及其優化.
1. 什么是索引?
索引是一種數據結構,可以幫助我們快速的進行數據的查找.
2. 索引是個什么樣的數據結構呢?
索引的數據結構和具體存儲引擎的實現有關, 在MySQL中使用較多的索引有Hash索引,B+樹索引等,而我們經常使用的InnoDB存儲引擎的默認索引實現為:B+樹索引.
3. Hash索引和B+樹所有有什么區別或者說優劣呢?
首先要知道Hash索引和B+樹索引的底層實現原理:
hash索引底層就是hash表,進行查找時,調用一次hash函數就可以獲取到相應的鍵值,之后進行回表查詢獲得實際數據.B+樹底層實現是多路平衡查找樹.對于每一次的查詢都是從根節點出發,查找到葉子節點方可以獲得所查鍵值,然后根據查詢判斷是否需要回表查詢數據.
那么可以看出他們有以下的不同:
因為在hash索引中經過hash函數建立索引之后,索引的順序與原順序無法保持一致,不能支持范圍查詢.而B+樹的的所有節點皆遵循(左節點小于父節點,右節點大于父節點,多叉樹也類似),天然支持范圍.
-
hash索引不支持使用索引進行排序,原理同上.
-
hash索引不支持模糊查詢以及多列索引的最左前綴匹配.原理也是因為hash函數的不可預測.AAAA和AAAAB的索引沒有相關性.
-
hash索引任何時候都避免不了回表查詢數據,而B+樹在符合某些條件(聚簇索引,覆蓋索引等)的時候可以只通過索引完成查詢.
-
hash索引雖然在等值查詢上較快,但是不穩定.性能不可預測,當某個鍵值存在大量重復的時候,發生hash碰撞,此時效率可能極差.而B+樹的查詢效率比較穩定,對于所有的查詢都是從根節點到葉子節點,且樹的高度較低.
因此,在大多數情況下,直接選擇B+樹索引可以獲得穩定且較好的查詢速度.而不需要使用hash索引.
4. 上面提到了B+樹在滿足聚簇索引和覆蓋索引的時候不需要回表查詢數據,什么是聚簇索引?
在B+樹的索引中,葉子節點可能存儲了當前的key值,也可能存儲了當前的key值以及整行的數據,這就是聚簇索引和非聚簇索引. 在InnoDB中,只有主鍵索引是聚簇索引,如果沒有主鍵,則挑選一個唯一鍵建立聚簇索引.如果沒有唯一鍵,則隱式的生成一個鍵來建立聚簇索引.
當查詢使用聚簇索引時,在對應的葉子節點,可以獲取到整行數據,因此不用再次進行回表查詢.
5. 非聚簇索引一定會回表查詢嗎?
不一定,這涉及到查詢語句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再進行回表查詢.
舉個簡單的例子,假設我們在員工表的年齡上建立了索引,那么當進行 select age from employee where age < 20 的查詢時,在索引的葉子節點上,已經包含了age信息,不會再次進行回表查詢.
6. 在建立索引的時候,都有哪些需要考慮的因素呢?
建立索引的時候一般要考慮到字段的使用頻率,經常作為條件進行查詢的字段比較適合.如果需要建立聯合索引的話,還需要考慮聯合索引中的順序.此外也要考慮其他方面,比如防止過多的所有對表造成太大的壓力.這些都和實際的表結構以及查詢方式有關.
7. 聯合索引是什么?為什么需要注意聯合索引中的順序?
MySQL可以使用多個字段同時建立一個索引,叫做聯合索引.在聯合索引中,如果想要命中索引,需要按照建立索引時的字段順序挨個使用,否則無法命中索引.
具體原因為:
MySQL使用索引時需要索引有序,假設現在建立了"name,age,school"的聯合索引,那么索引的排序為: 先按照name排序,如果name相同,則按照age排序,如果age的值也相等,則按照school進行排序.
當進行查詢時,此時索引僅僅按照name嚴格有序,因此必須首先使用name字段進行等值查詢,之后對于匹配到的列而言,其按照age字段嚴格有序,此時可以使用age字段用做索引查找,,,以此類推.因此在建立聯合索引的時候應該注意索引列的順序,一般情況下,將查詢需求頻繁或者字段選擇性高的列放在前面.此外可以根據特例的查詢或者表結構進行多帶帶的調整.
8. 創建的索引有沒有被使用到?或者說怎么才可以知道這條語句運行很慢的原因?
MySQL提供了explain命令來查看語句的執行計劃,MySQL在執行某個語句之前,會將該語句過一遍查詢優化器,之后會拿到對語句的分析,也就是執行計劃,其中包含了許多信息. 可以通過其中和索引有關的信息來分析是否命中了索引,例如possilbe_key,key,key_len等字段,分別說明了此語句可能會使用的索引,實際使用的索引以及使用的索引長度.
9. 那么在哪些情況下會發生針對該列創建了索引但是在查詢的時候并沒有使用呢?
-
使用不等于查詢,
-
列參與了數學運算或者函數
-
在字符串like時左邊是通配符.類似于"%aaa".
-
當mysql分析全表掃描比使用索引快的時候不使用索引.
-
當使用聯合索引,前面一個條件為范圍查詢,后面的即使符合最左前綴原則,也無法使用索引.
以上情況,MySQL無法使用索引.
事務相關
1. 什么是事務?
理解什么是事務最經典的就是轉賬的栗子,相信大家也都了解,這里就不再說一邊了.
事務是一系列的操作,他們要符合ACID特性.最常見的理解就是:事務中的操作要么全部成功,要么全部失敗.但是只是這樣還不夠的.
2. ACID是什么?可以詳細說一下嗎?
A=Atomicity
原子性,就是上面說的,要么全部成功,要么全部失敗.不可能只執行一部分操作.
C=Consistency
系統(數據庫)總是從一個一致性的狀態轉移到另一個一致性的狀態,不會存在中間狀態.
I=Isolation
隔離性: 通常來說:一個事務在完全提交之前,對其他事務是不可見的.注意前面的通常來說加了紅色,意味著有例外情況.
D=Durability
持久性,一旦事務提交,那么就永遠是這樣子了,哪怕系統崩潰也不會影響到這個事務的結果.
3. 同時有多個事務在進行會怎么樣呢?
多事務的并發進行一般會造成以下幾個問題:
-
臟讀: A事務讀取到了B事務未提交的內容,而B事務后面進行了回滾.
-
不可重復讀: 當設置A事務只能讀取B事務已經提交的部分,會造成在A事務內的兩次查詢,結果竟然不一樣,因為在此期間B事務進行了提交操作.
-
幻讀: A事務讀取了一個范圍的內容,而同時B事務在此期間插入了一條數據.造成"幻覺".
4. 怎么解決這些問題呢?MySQL的事務隔離級別了解嗎?
MySQL的四種隔離級別如下:
這就是上面所說的例外情況了,這個隔離級別下,其他事務可以看到本事務沒有提交的部分修改.因此會造成臟讀的問題(讀取到了其他事務未提交的部分,而之后該事務進行了回滾).
這個級別的性能沒有足夠大的優勢,但是又有很多的問題,因此很少使用.
其他事務只能讀取到本事務已經提交的部分.這個隔離級別有 不可重復讀的問題,在同一個事務內的兩次讀取,拿到的結果竟然不一樣,因為另外一個事務對數據進行了修改.
可重復讀隔離級別解決了上面不可重復讀的問題(看名字也知道),但是仍然有一個新問題,就是 幻讀,當你讀取id> 10 的數據行時,對涉及到的所有行加上了讀鎖,此時例外一個事務新插入了一條id=11的數據,因為是新插入的,所以不會觸發上面的鎖的排斥,那么進行本事務進行下一次的查詢時會發現有一條id=11的數據,而上次的查詢操作并沒有獲取到,再進行插入就會有主鍵沖突的問題.
這是最高的隔離級別,可以解決上面提到的所有問題,因為他強制將所以的操作串行執行,這會導致并發性能極速下降,因此也不是很常用.
5. Innodb使用的是哪種隔離級別呢?
InnoDB默認使用的是可重復讀隔離級別.
6. 對MySQL的鎖了解嗎?
當數據庫有并發事務的時候,可能會產生數據的不一致,這時候需要一些機制來保證訪問的次序,鎖機制就是這樣的一個機制.
就像酒店的房間,如果大家隨意進出,就會出現多人搶奪同一個房間的情況,而在房間上裝上鎖,申請到鑰匙的人才可以入住并且將房間鎖起來,其他人只有等他使用完畢才可以再次使用.
7. MySQL都有哪些鎖呢?像上面那樣子進行鎖定豈不是有點阻礙并發效率了?
從鎖的類別上來講,有共享鎖和排他鎖.
共享鎖: 又叫做讀鎖. 當用戶要進行數據的讀取時,對數據加上共享鎖.共享鎖可以同時加上多個.
排他鎖: 又叫做寫鎖. 當用戶要進行數據的寫入時,對數據加上排他鎖.排他鎖只可以加一個,他和其他的排他鎖,共享鎖都相斥.
用上面的例子來說就是用戶的行為有兩種,一種是來看房,多個用戶一起看房是可以接受的. 一種是真正的入住一晚,在這期間,無論是想入住的還是想看房的都不可以.
鎖的粒度取決于具體的存儲引擎,InnoDB實現了行級鎖,頁級鎖,表級鎖.
他們的加鎖開銷從大大小,并發能力也是從大到小.
表結構設計
1. 為什么要盡量設定一個主鍵?
主鍵是數據庫確保數據行在整張表唯一性的保障,即使業務上本張表沒有主鍵,也建議添加一個自增長的ID列作為主鍵.設定了主鍵之后,在后續的刪改查的時候可能更加快速以及確保操作數據范圍安全.
2. 主鍵使用自增ID還是UUID?
推薦使用自增ID,不要使用UUID.
因為在InnoDB存儲引擎中,主鍵索引是作為聚簇索引存在的,也就是說,主鍵索引的B+樹葉子節點上存儲了主鍵索引以及全部的數據(按照順序),如果主鍵索引是自增ID,那么只需要不斷向后排列即可,如果是UUID,由于到來的ID與原來的大小不確定,會造成非常多的數據插入,數據移動,然后導致產生很多的內存碎片,進而造成插入性能的下降.
總之,在數據量大一些的情況下,用自增主鍵性能會好一些.
圖片來源于《高性能MySQL》: 其中默認后綴為使用自增ID,_uuid為使用UUID為主鍵的測試,測試了插入100w行和300w行的性能.
關于主鍵是聚簇索引,如果沒有主鍵,InnoDB會選擇一個唯一鍵來作為聚簇索引,如果沒有唯一鍵,會生成一個隱式的主鍵.
If you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.
If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index.
3. 字段為什么要求定義為not null?
MySQL官網這樣介紹:
NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.
null值會占用更多的字節,且會在程序中造成很多與預期不符的情況.
4. 如果要存儲用戶的密碼散列,應該使用什么字段進行存儲?
密碼散列,鹽,用戶身份證號等固定長度的字符串應該使用char而不是varchar來存儲,這樣可以節省空間且提高檢索效率.
存儲引擎相關
1. MySQL支持哪些存儲引擎?
MySQL支持多種存儲引擎,比如InnoDB,MyISAM,Memory,Archive等等.在大多數的情況下,直接選擇使用InnoDB引擎都是最合適的,InnoDB也是MySQL的默認存儲引擎.
-
InnoDB和MyISAM有什么區別?
-
InnoDB支持事物，而MyISAM不支持事物
-
InnoDB支持行級鎖，而MyISAM支持表級鎖
-
InnoDB支持MVCC, 而MyISAM不支持
-
InnoDB支持外鍵，而MyISAM不支持
-
InnoDB不支持全文索引，而MyISAM支持。
零散問題
1. MySQL中的varchar和char有什么區別.
char是一個定長字段,假如申請了 char(10) 的空間,那么無論實際存儲多少內容.該字段都占用10個字符,而varchar是變長的,也就是說申請的只是最大長度,占用的空間為實際字符長度+1,最后一個字符存儲使用了多長的空間.
在檢索效率上來講,char > varchar,因此在使用中,如果確定某個字段的值的長度,可以使用char,否則應該盡量使用varchar.例如存儲用戶MD5加密后的密碼,則應該使用char.
2. varchar(10)和int(10)代表什么含義?
varchar的10代表了申請的空間長度,也是可以存儲的數據的最大長度,而int的10只是代表了展示的長度,不足10位以0填充.也就是說,int(1)和int(10)所能存儲的數字大小以及占用的空間都是相同的,只是在展示時按照長度展示.
3. MySQL的binlog有有幾種錄入格式?分別有什么區別?
有三種格式,statement,row和mixed.
-
statement模式下,記錄單元為語句.即每一個sql造成的影響會記錄.由于sql的執行是有上下文的,因此在保存的時候需要保存相關的信息,同時還有一些使用了函數之類的語句無法被記錄復制.
-
row級別下,記錄單元為每一行的改動,基本是可以全部記下來但是由于很多操作,會導致大量行的改動(比如alter table),因此這種模式的文件保存的信息太多,日志量太大.
-
mixed. 一種折中的方案,普通操作使用statement記錄,當無法使用statement的時候使用row.
此外,新版的MySQL中對row級別也做了一些優化,當表結構發生變化的時候,會記錄語句而不是逐行記錄.
4. 超大分頁怎么處理?
超大的分頁一般從兩個方向上來解決.
-
數據庫層面,這也是我們主要集中關注的(雖然收效沒那么大),類似于 select * from table where age > 20 limit 1000000,10 這種查詢其實也是有可以優化的余地的. 這條語句需要load1000000數據然后基本上全部丟棄,只取10條當然比較慢. 當時我們可以修改為 select * from table where id in (select id from table where age > 20 limit 1000000,10) .這樣雖然也load了一百萬的數據,但是由于索引覆蓋,要查詢的所有字段都在索引中,所以速度會很快. 同時如果ID連續的好,我們還可以 select * from table where id > 1000000 limit 10 ,效率也是不錯的,優化的可能性有許多種,但是核心思想都一樣,就是減少load的數據.
-
從需求的角度減少這種請求….主要是不做類似的需求(直接跳轉到幾百萬頁之后的具體某一頁.只允許逐頁查看或者按照給定的路線走,這樣可預測,可緩存)以及防止ID泄漏且連續被人惡意攻擊.
解決超大分頁,其實主要是靠緩存,可預測性的提前查到內容,緩存至redis等k-V數據庫中,直接返回即可.
在阿里巴巴《Java開發手冊》中,對超大分頁的解決辦法是類似于上面提到的第一種.
5. 關心過業務系統里面的sql耗時嗎?統計過慢查詢嗎?對慢查詢都怎么優化過?
在業務系統中,除了使用主鍵進行的查詢,其他的我都會在測試庫上測試其耗時,慢查詢的統計主要由運維在做,會定期將業務中的慢查詢反饋給我們.
慢查詢的優化首先要搞明白慢的原因是什么? 是查詢條件沒有命中索引?是load了不需要的數據列?還是數據量太大?
所以優化也是針對這三個方向來的,
-
首先分析語句,看看是否load了額外的數據,可能是查詢了多余的行并且拋棄掉了,可能是加載了許多結果中并不需要的列,對語句進行分析以及重寫.
-
分析語句的執行計劃,然后獲得其使用索引的情況,之后修改語句或者修改索引,使得語句可以盡可能的命中索引.
-
如果對語句的優化已經無法進行,可以考慮表中的數據量是否太大,如果是的話可以進行橫向或者縱向的分表.
6. 上面提到橫向分表和縱向分表,可以分別舉一個適合他們的例子嗎?
橫向分表是按行分表.假設我們有一張用戶表,主鍵是自增ID且同時是用戶的ID.數據量較大,有1億多條,那么此時放在一張表里的查詢效果就不太理想.我們可以根據主鍵ID進行分表,無論是按尾號分,或者按ID的區間分都是可以的. 假設按照尾號0-99分為100個表,那么每張表中的數據就僅有100w.這時的查詢效率無疑是可以滿足要求的.
縱向分表是按列分表.假設我們現在有一張文章表.包含字段 id-摘要-內容 .而系統中的展示形式是刷新出一個列表,列表中僅包含標題和摘要,當用戶點擊某篇文章進入詳情時才需要正文內容.此時,如果數據量大,將內容這個很大且不經常使用的列放在一起會拖慢原表的查詢速度.我們可以將上面的表分為兩張. id-摘要 , id-內容 .當用戶點擊詳情,那主鍵再來取一次內容即可.而增加的存儲量只是很小的主鍵字段.代價很小.
當然,分表其實和業務的關聯度很高,在分表之前一定要做好調研以及benchmark.不要按照自己的猜想盲目操作.
7. 什么是存儲過程？有哪些優缺點？
存儲過程是一些預編譯的SQL語句。1、更加直白的理解：存儲過程可以說是一個記錄集，它是由一些T-SQL語句組成的代碼塊，這些T-SQL語句代碼像一個方法一樣實現一些功能（對單表或多表的增刪改查），然后再給這個代碼塊取一個名字，在用到這個功能的時候調用他就行了。2、存儲過程是一個預編譯的代碼塊，執行效率比較高,一個存儲過程替代大量T_SQL語句 ，可以降低網絡通信量，提高通信速率,可以一定程度上確保數據安全
但是,在互聯網項目中,其實是不太推薦存儲過程的,比較出名的就是阿里的《Java開發手冊》中禁止使用存儲過程,我個人的理解是,在互聯網項目中,迭代太快,項目的生命周期也比較短,人員流動相比于傳統的項目也更加頻繁,在這樣的情況下,存儲過程的管理確實是沒有那么方便,同時,復用性也沒有寫在服務層那么好.
8. 說一說三個范式
第一范式: 每個列都不可以再拆分. 第二范式: 非主鍵列完全依賴于主鍵,而不能是依賴于主鍵的一部分. 第三范式: 非主鍵列只依賴于主鍵,不依賴于其他非主鍵.
在設計數據庫結構的時候,要盡量遵守三范式,如果不遵守,必須有足夠的理由.比如性能. 事實上我們經常會為了性能而妥協數據庫的設計.
9. MyBatis中的#
亂入了一個奇怪的問題…..我只是想多帶帶記錄一下這個問題,因為出現頻率太高了.
# 會將傳入的內容當做字符串,而有什么區別???亂入了一個奇怪的問題.....我只是想多帶帶記錄一下這個問題,因為出現頻率太高了.#會將傳入的內容當做字符串,而會直接將傳入值拼接在sql語句中.
所以#可以在一定程度上預防sql注入攻擊.
?推薦↓↓↓?
涵蓋：程序員大咖、源碼共讀、程序員共讀、數據結構與算法、黑客技術和網絡安全、大數據科技、編程前端、Java、Python、Web編程開發、Android、iOS開發、Linux、數據庫研發、幽默程序員等。
