網際網路公司面試必問的MySQL題目

介紹：MySQL是一個關係型資料庫管理系統，目前屬於 Oracle 旗下產品。雖然單機效能比不上oracle，但免費開源，單機成本低且藉助於分散式叢集所以受到網際網路公司的青睞，是網際網路公司的主流資料庫。

01

什麼是資料庫事務？如果沒有事物會有什麼後果？事務的特性是什麼？

事務是指作為單個邏輯工作單元執行的一系列操作，可以被看作一個單元的一系列SQL陳述句的集合。要麼完全地執行，要麼完全地不執行。

如果不對資料庫進行併發控制，可能會產生 臟讀、非重覆讀、幻像讀、丟失修改的異常情況。

事務的特性（ACID）

A, atomacity 原子性 事務必須是原子工作單元；對於其資料修改，要麼全都執行，要麼全都不執行。通常，與某個事務關聯的操作具有共同的標的，並且是相互依賴的。如果系統只執行這些操作的一個子集，則可能會破壞事務的總體標的。原子性消除了系統處理操作子集的可能性。

C, consistency 一致性

事務將資料庫從一種一致狀態轉變為下一種一致狀態。也就是說，事務在完成時，必須使所有的資料都保持一致狀態（各種 constraint 不被破壞）。

I, isolation 隔離性 由併發事務所作的修改必須與任何其它併發事務所作的修改隔離。事務檢視資料時資料所處的狀態，要麼是另一併發事務修改它之前的狀態，要麼是另一事務修改它之後的狀態，事務不會檢視中間狀態的資料。換句話說，一個事務的影響在該事務提交前對其他事務都不可見。

D, durability 永續性

事務完成之後，它對於系統的影響是永久性的。該修改即使出現致命的系統故障也將一直保持。

“A向B匯錢100”

1. 讀出A賬號餘額（500）。

2. A賬號扣錢操作（500-100）。

3. 結果寫回A賬號（400）。

4. 讀出B賬號餘額（500）。

5. B賬號做加法操作（500+100）。

6. 結果寫回B賬號（600）。

原子性：

保證1-6所有過程要麼都執行，要麼都不執行。如果異常了那麼回滾。

一致性

轉賬前，A和B的賬戶中共有500+500=1000元錢。轉賬後，A和B的賬戶中共有400+600=1000元。

隔離性

在A向B轉賬的整個過程中，只要事務還沒有提交（commit），查詢A賬戶和B賬戶的時候，兩個賬戶裡面的錢的數量都不會有變化。

永續性

一旦轉賬成功（事務提交），兩個賬戶的裡面的錢就會真的發生變化

02

什麼是臟讀？幻讀？不可重覆讀？什麼是事務的隔離級別？Mysql的預設隔離級別是？

• 臟讀：事務A讀取了事務B更新的資料，然後B回滾操作，那麼A讀取到的資料是臟資料

• 不可重覆讀：事務 A 多次讀取同一資料，事務 B 在事務A多次讀取的過程中，對資料作了更新並提交，導致事務A多次讀取同一資料時，結果 不一致。

• 幻讀：系統管理員A將資料庫中所有學生的成績從具體分數改為ABCDE等級，但是系統管理員B就在這個時候插入了一條具體分數的記錄，當系統管理員A改結束後發現還有一條記錄沒有改過來，就好像發生了幻覺一樣，這就叫幻讀。

Read uncommitted

讀未提交，顧名思義，就是一個事務可以讀取另一個未提交事務的資料。

Read committed

讀提交，顧名思義，就是一個事務要等另一個事務提交後才能讀取資料。

小A去買東西（卡裡有1萬元），當他買單時（事務開啟），系統事先檢測到他的卡裡有1萬，就在這個時候！！小A的妻子要把錢全部轉出充當家用，並提交。當系統準備扣款時，再檢測卡裡的金額，發現已經沒錢了（第二次檢測金額當然要等待妻子轉出金額事務提交完）。A就會很鬱悶

分析：這就是讀提交，若有事務對資料進行更新（UPDATE）操作時，讀操作事務要等待這個更新操作事務提交後才能讀取資料，可以解決臟讀問題。但在這個事例中，出現了一個事務範圍內兩個相同的查詢卻傳回了不同資料，這就是不可重覆讀。

Repeatable read

重覆讀，就是在開始讀取資料（事務開啟）時，不再允許修改操作

事例：小A去買東西（卡裡有1萬元），當他買單時（事務開啟，不允許其他事務的UPDATE修改操作），收費系統事先檢測到他的卡裡有1萬。這時候他的妻子不能轉出金額了。接下來收費系統就可以扣款了。

分析：重覆讀可以解決不可重覆讀問題。寫到這裡，應該明白的一點就是，不可重覆讀對應的是修改，即UPDATE操作。但是可能還會有幻讀問題。因為幻讀問題對應的是插入INSERT操作，而不是UPDATE操作。

什麼時候會出現幻讀？

事例：小A去買東西，花了2千元，然後他的妻子去檢視他的消費記錄（全表掃描FTS，妻事務開啟），看到確實是花了2千元，就在這個時候，小A花了1萬買了一部電腦，INSERT了一條消費記錄，並提交。當妻子列印小A的消費記錄清單時（妻子事務提交），發現花了1.2萬元，似乎出現了幻覺，這就是幻讀。

Serializable 序列化

Serializable 是最高的事務隔離級別，在該級別下，事務序列化順序執行，可以避免臟讀、不可重覆讀與幻讀。但是這種事務隔離級別效率低下，比較耗資料庫效能，一般不使用。

Mysql的預設隔離級別是Repeatable read。

03

事物隔離是怎麼實現的？

是基於鎖實現的.

有哪些鎖？分別介紹下

在DBMS中，可以按照鎖的粒度把資料庫鎖分為行級鎖(INNODB引擎)、表級鎖(MYISAM引擎)和頁級鎖(BDB引擎 )。

行級鎖

行級鎖是Mysql中鎖定粒度最細的一種鎖，表示只針對當前操作的行進行加鎖。行級鎖能大大減少資料庫操作的衝突。其加鎖粒度最小，但加鎖的開銷也最大。行級鎖分為共享鎖 和 排他鎖。

特點

開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的機率最低，併發度也最高。

表級鎖

表級鎖是MySQL中鎖定粒度最大的一種鎖，表示對當前操作的整張表加鎖，它實現簡單，資源消耗較少，被大部分MySQL引擎支援。最常使用的MYISAM與INNODB都支援表級鎖定。表級鎖定分為表共享讀鎖（共享鎖）與表獨佔寫鎖（排他鎖）。

特點

開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發出鎖衝突的機率最高，併發度最低。

頁級鎖

頁級鎖是MySQL中鎖定粒度介於行級鎖和表級鎖中間的一種鎖。表級鎖速度快，但衝突多，行級衝突少，但速度慢。所以取了折衷的頁級，一次鎖定相鄰的一組記錄。

特點

開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，併發度一般

04

什麼是死鎖？怎麼解決？（前幾問題是我個人最喜歡的連環炮，基本可以看出面試者的基礎功）

死鎖是指兩個或多個事務在同一資源上相互佔用，並請求鎖定對付的資源，從而導致惡性迴圈的現象。

常見的解決死鎖的方法

1、如果不同程式會併發存取多個表，儘量約定以相同的順序訪問表，可以大大降低死鎖機會。

2、在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產生機率；

3、對於非常容易產生死鎖的業務部分，可以嘗試使用升級鎖定顆粒度，透過表級鎖定來減少死鎖產生的機率；

如果業務處理不好可以用分散式事務鎖或者使用樂觀鎖

05

SQL的生命週期？關鍵字的先後順序？

1. 應用伺服器與資料庫伺服器建立一個連線

2. 資料庫行程拿到請求sql

3. 解析並生成執行計劃，執行

4. 讀取資料到記憶體併進行邏輯處理

5. 透過步驟一的連線，傳送結果到客戶端

6. 關掉連線，釋放資源

1、 FROM：對 FROM 子句中的前兩個表執行笛卡爾積(交叉聯接)，生成虛擬表 VT1。

2、 ON：對 VT1 應用 ON 篩選器，只有那些使為真才被插入到 TV2。
3、 OUTER (JOIN):如果指定了 OUTER JOIN(相對於 CROSS JOIN 或 INNER JOIN)，保留表中未找到
匹配的行將作為外部行新增到 VT2，生成 TV3。如果 FROM 子句包含兩個以上的表，則對上一個聯接生成的
結果表和下一個表重覆執行步驟 1 到步驟 3，直到處理完所有的表位置。
4、 WHERE：對 TV3 應用 WHERE 篩選器，只有使為 true 的行才插入 TV4。
5、 GROUP BY：按 GROUP BY 子句中的列串列對 TV4 中的行進行分組，生成 TV5。
6、 CUTE|ROLLUP：把超組插入 VT5，生成 VT6。
7、 HAVING：對 VT6 應用 HAVING 篩選器，只有使為 true 的組插入到 VT7。
8、 SELECT：處理 SELECT 串列，產生 VT8。
9、 DISTINCT：將重覆的行從 VT8 中刪除，產品 VT9。

10、 ORDER BY：將 VT9 中的行按 ORDER BY 子句中的列串列順序，生成一個遊標(VC10)。
11、 TOP：從 VC10 的開始處選擇指定數量或比例的行，生成表 TV11，並傳回給呼叫者。

06

什麼是樂觀鎖？悲觀鎖？實現方式？

悲觀鎖：

悲觀鎖指對資料被意外修改持保守態度，依賴資料庫原生支援的鎖機制來保證當前事務處理的安全性，防止其他併發事務對標的資料的破壞或破壞其他併發事務資料，將在事務開始執行前或執行中申請鎖定，執行完後再釋放鎖定。這對於長事務來講，可能會嚴重影響系統的併發處理能力。 自帶的資料庫事務就是典型的悲觀鎖。

樂觀鎖：

樂觀鎖（Optimistic Lock），顧名思義，就是很樂觀，每次去拿資料的時候都認為別人不會修改，所以不會上鎖，但是在提交更新的時候會判斷一下在此期間別人有沒有去更新這個資料。樂觀鎖適用於讀多寫少的應用場景，這樣可以提高吞吐量。

一般是加一個版本號欄位 每次更新時候比較版本號

07

大資料情況下如何做分頁？

可以參考阿裡巴巴java開發手冊上的答案

08

什麼是資料庫連線池？

從上一個sql生命週期題目，可以看到其中的連線在裡面發揮著重大作用，但頻繁的建立和銷毀，非常浪費系統資源。由於資料庫更適合長連線，也就有個連線池，能對連線復用，維護連線物件、分配、管理、釋放，也可以避免建立大量的連線對DB引發的各種問題；另外透過請求排隊，也緩解對DB的衝擊。

網際網路公司面試必問的MySQL題目(下)

索引是對資料庫表中一列或多列的值進行排序的一種結構。一個非常恰當的比喻就是書的目錄頁與書的正文內容之間的關係，為了方便查詢書中的內容，透過對內容建立索引形成目錄。索引是一個檔案，它是要佔據物理空間的。

主鍵索引:
資料列不允許重覆，不允許為NULL.一個表只能有一個主鍵。

唯一索引:
資料列不允許重覆，允許為NULL值，一個表允許多個列建立唯一索引。

可以透過
ALTER TABLE table_name ADD UNIQUE (column); 
建立唯一索引

可以透過
ALTER TABLE table_name ADD UNIQUE (column1,column2);
建立唯一組合索引

普通索引:
基本的索引型別，沒有唯一性的限制，允許為NULL值。

可以透過ALTER TABLE table_name ADD INDEX index_name (column);建立普通索引

可以透過ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);建立組合索引

全文索引：
是目前搜尋引擎使用的一種關鍵技術。

可以透過ALTER TABLE table_name ADD FULLTEXT (column);建立全文索引

最左字首

• 顧名思義，就是最左優先，在建立多列索引時，要根據業務需求，where子句中使用最頻繁的一列放在最左邊。

• 還有一個就是生效原則 比如

index(a,b,c)
where a=3    只使用了a
where a=3 and b=5    使用了a,b
where a=3 and b=5 and c=4    使用了a,b,c
where b=3 or where c=4    沒有使用索引
where a=3 and c=4    僅使用了a
where a=3 and b>10 and c=7    使用了a,b
where a=3 and b like ' xx%' and c=7    使用了a,b

索引演演算法有 BTree Hash

BTree是最常用的mysql資料庫索引演演算法，也是mysql預設的演演算法。因為它不僅可以被用在=,>,>=,例如：

select * from user where name like ‘jack%’; 
如果一萬用字元開頭，或者沒有使用常量，則不會使用索引，例如： 
select * from user where name like ‘%jack’;

Hash
Hash索引只能用於對等比較，例如=,<=>（相當於=）運運算元。由於是一次定位資料，不像BTree索引需要從根節點到枝節點，最後才能訪問到頁節點這樣多次IO訪問，所以檢索效率遠高於BTree索引。

BTree索引是最常用的mysql資料庫索引演演算法，也是mysql預設的演演算法。因為它不僅可以被用在=,>,>=,例如：

只要它的查詢條件是一個不以萬用字元開頭的常量
select * from user where name like 'jack%'; 
如果一萬用字元開頭，或者沒有使用常量，則不會使用索引，例如： 
select * from user where name like '%jack'; 

Hash
Hash索引只能用於對等比較，例如=,<=>（相當於=）運運算元。由於是一次定位資料，不像BTree索引需要從根節點到枝節點，最後才能訪問到頁節點這樣多次IO訪問，所以檢索效率遠高於BTree索引。

索引設計的原則？

1、適合索引的列是出現在where子句中的列，或者連線子句中指定的列

2、基數較小的類，索引效果較差，沒有必要在此列建立索引

3、使用短索引，如果對長字串列進行索引，應該指定一個字首長度，這樣能夠節省大量索引空間

4、不要過度索引。索引需要額外的磁碟空間，並降低寫操作的效能。在修改表內容的時候，索引會進行更新甚至重構，索引列越多，這個時間就會越長。所以只保持需要的索引有利於查詢即可。

如何定位及最佳化SQL陳述句的效能問題？

對於低效能的SQL陳述句的定位，最重要也是最有效的方法就是使用執行計劃。

我們知道，不管是哪種資料庫，或者是哪種資料庫引擎，在對一條SQL陳述句進行執行的過程中都會做很多相關的最佳化，對於查詢陳述句，最重要的最佳化方式就是使用索引。

而執行計劃，就是顯示資料庫引擎對於SQL陳述句的執行的詳細情況，其中包含了是否使用索引，使用什麼索引，使用的索引的相關資訊等。

執行計劃包含的資訊

id

有一組數字組成。表示一個查詢中各個子查詢的執行順序;

• id相同執行順序由上至下。

• id不同，id值越大優先順序越高，越先被執行。

• id為null時表示一個結果集，不需要使用它查詢，常出現在包含union等查詢陳述句中。

select_type

每個子查詢的查詢型別，一些常見的查詢型別。

table
查詢的資料表，當從衍生表中查資料時會顯示 x 表示對應的執行計劃id

partitions
表分割槽、表建立的時候可以指定透過那個列進行表分割槽。 舉個例子：

create table tmp (
    id int unsigned not null AUTO_INCREMENT,
    name varchar(255),
    PRIMARY KEY (id)
) engine = innodb
partition by key (id) partitions 5;

type(非常重要，可以看到有沒有走索引)
訪問型別

• ALL 掃描全表資料

• index 遍歷索引

• range 索引範圍查詢

• index_subquery 在子查詢中使用 ref

• unique_subquery 在子查詢中使用 eq_ref

• ref_or_null 對Null進行索引的最佳化的 ref

• fulltext 使用全文索引

• ref 使用非唯一索引查詢資料

• eq_ref 在join查詢中使用PRIMARY KEYorUNIQUE NOT NULL索引關聯。

possible_keys
可能使用的索引，註意不一定會使用。查詢涉及到的欄位上若存在索引，則該索引將被列出來。當該列為 NULL時就要考慮當前的SQL是否需要優化了。

key
顯示MySQL在查詢中實際使用的索引，若沒有使用索引，顯示為NULL。

TIPS:查詢中若使用了改寫索引(改寫索引：索引的資料改寫了需要查詢的所有資料)，則該索引僅出現在key串列中

key_length
索引長度 

ref
表示上述表的連線匹配條件，即哪些列或常量被用於查詢索引列上的值

rows
傳回估算的結果集數目，並不是一個準確的值。

extra
extra的資訊非常豐富，常見的有：
1、Using index 使用改寫索引
2、Using where 使用了用where子句來過濾結果集
3、Using filesort 使用檔案排序，使用非索引列進行排序時出現，非常消耗效能，儘量最佳化。
4、Using temporary 使用了臨時表

sql最佳化的標的可以參考阿裡開發手冊

某個表有近千萬資料，CRUD比較慢，如何最佳化？分庫分表了是怎麼做的？分表分庫了有什麼問題？有用到中介軟體麼?他們的原理知道麼？

資料千萬級別之多，佔用的儲存空間也比較大，可想而知它不會儲存在一塊連續的物理空間上，而是鏈式儲存在多個碎片的物理空間上。可能對於長字串的比較，就用更多的時間查詢與比較，這就導致用更多的時間。

• 可以做表拆分，減少單表欄位數量，最佳化表結構。

• 在保證主鍵有效的情況下，檢查主鍵索引的欄位順序，使得查詢陳述句中條件的欄位順序和主鍵索引的欄位順序保持一致。

  主要兩種拆分 垂直拆分，水平拆分。

  

垂直分表

也就是“大表拆小表”，基於列欄位進行的。一般是表中的欄位較多，將不常用的， 資料較大，長度較長（比如text型別欄位）的拆分到“擴充套件表“。 一般是針對那種幾百列的大表，也避免查詢時，資料量太大造成的“跨頁”問題。

垂直分庫針對的是一個系統中的不同業務進行拆分，比如使用者User一個庫，商品Producet一個庫，訂單Order一個庫。 切分後，要放在多個伺服器上，而不是一個伺服器上。為什麼？ 我們想象一下，一個購物網站對外提供服務，會有使用者，商品，訂單等的CRUD。沒拆分之前， 全部都是落到單一的庫上的，這會讓資料庫的單庫處理能力成為瓶頸。按垂直分庫後，如果還是放在一個資料庫伺服器上， 隨著使用者量增大，這會讓單個資料庫的處理能力成為瓶頸，還有單個伺服器的磁碟空間，記憶體，tps等非常吃緊。 所以我們要拆分到多個伺服器上，這樣上面的問題都解決了，以後也不會面對單機資源問題。

資料庫業務層面的拆分，和服務的“治理”，“降級”機制類似，也能對不同業務的資料分別的進行管理，維護，監控，擴充套件等。 資料庫往往最容易成為應用系統的瓶頸，而資料庫本身屬於“有狀態”的，相對於Web和應用伺服器來講，是比較難實現“橫向擴充套件”的。 資料庫的連線資源比較寶貴且單機處理能力也有限，在高併發場景下，垂直分庫一定程度上能夠突破IO、連線數及單機硬體資源的瓶頸。

水平分表

針對資料量巨大的單張表（比如訂單表），按照某種規則（RANGE,HASH取模等），切分到多張表裡面去。 但是這些表還是在同一個庫中，所以庫級別的資料庫操作還是有IO瓶頸。不建議採用。

水平分庫分表

將單張表的資料切分到多個伺服器上去，每個伺服器具有相應的庫與表，只是表中資料集合不同。 水平分庫分表能夠有效的緩解單機和單庫的效能瓶頸和壓力，突破IO、連線數、硬體資源等的瓶頸。

水平分庫分表切分規則

1. RANGE從
   0到10000一個表，10001到20000一個表；

2. HASH取模
   一個商場系統，一般都是將使用者，訂單作為主表，然後將和它們相關的作為附表，這樣不會造成跨庫事務之類的問題。 取使用者id，然後hash取模，分配到不同的資料庫上。

3. 地理區域
   比如按照華東，華南，華北這樣來區分業務，七牛雲應該就是如此。

4. 時間
   按照時間切分，就是將6個月前，甚至一年前的資料切出去放到另外的一張表，因為隨著時間流逝，這些表的資料 被查詢的機率變小，所以沒必要和“熱資料”放在一起，這個也是“冷熱資料分離”。

分庫分表後面臨的問題

• 事務支援
  分庫分表後，就成了分散式事務了。如果依賴資料庫本身的分散式事務管理功能去執行事務，將付出高昂的效能代價； 如果由應用程式去協助控制，形成程式邏輯上的事務，又會造成程式設計方面的負擔。

• 跨庫join

  只要是進行切分，跨節點Join的問題是不可避免的。但是良好的設計和切分卻可以減少此類情況的發生。解決這一問題的普遍做法是分兩次查詢實現。在第一次查詢的結果集中找出關聯資料的id,根據這些id發起第二次請求得到關聯資料。
  分庫分表方案產品

• 跨節點的count,order by,group by以及聚合函式問題
  這些是一類問題，因為它們都需要基於全部資料集合進行計算。多數的代理都不會自動處理合併工作。解決方案：與解決跨節點join問題的類似，分別在各個節點上得到結果後在應用程式端進行合併。和join不同的是每個結點的查詢可以並行執行，因此很多時候它的速度要比單一大表快很多。但如果結果集很大，對應用程式記憶體的消耗是一個問題。

• 資料遷移，容量規劃，擴容等問題
  來自淘寶綜合業務平臺團隊，它利用對2的倍數取餘具有向前相容的特性（如對4取餘得1的數對2取餘也是1）來分配資料，避免了行級別的資料遷移，但是依然需要進行表級別的遷移，同時對擴容規模和分表數量都有限制。總得來說，這些方案都不是十分的理想，多多少少都存在一些缺點，這也從一個側面反映出了Sharding擴容的難度。

• ID問題

  一旦資料庫被切分到多個物理結點上，我們將不能再依賴資料庫自身的主鍵生成機制。一方面，某個分割槽資料庫自生成的ID無法保證在全域性上是唯一的；另一方面，應用程式在插入資料之前需要先獲得ID,以便進行SQL路由.
  一些常見的主鍵生成策略

UUID
使用UUID作主鍵是最簡單的方案，但是缺點也是非常明顯的。由於UUID非常的長，除佔用大量儲存空間外，最主要的問題是在索引上，在建立索引和基於索引進行查詢時都存在效能問題。
Twitter的分散式自增ID演演算法Snowflake
在分散式系統中，需要生成全域性UID的場合還是比較多的，twitter的snowflake解決了這種需求，實現也還是很簡單的，除去配置資訊，核心程式碼就是毫秒級時間41位 機器ID 10位 毫秒內序列12位。

跨分片的排序分頁
般來講，分頁時需要按照指定欄位進行排序。當排序欄位就是分片欄位的時候，我們透過分片規則可以比較容易定位到指定的分片，而當排序欄位非分片欄位的時候，情況就會變得比較複雜了。為了最終結果的準確性，我們需要在不同的分片節點中將資料進行排序並傳回，並將不同分片傳回的結果集進行彙總和再次排序，最後再傳回給使用者。如下圖所示：

中介軟體推薦

mysql中in 和exists 區別

mysql中的in陳述句是把外表和內表作hash 連線，而exists陳述句是對外表作loop迴圈，每次loop迴圈再對內表進行查詢。一直大家都認為exists比in陳述句的效率要高，這種說法其實是不準確的。這個是要區分環境的。

1、如果查詢的兩個表大小相當，那麼用in和exists差別不大。

2、如果兩個表中一個較小，一個是大表，則子查詢表大的用exists，子查詢表小的用in。

3、not in 和not exists如果查詢陳述句使用了not in 那麼內外表都進行全表掃描，沒有用到索引；而not extsts的子查詢依然能用到表上的索引。所以無論那個表大，用not exists都比not in要快。