精心為你準備的最全的20道Mysql面試題

1.什么Mysql的事務？事務的四大特性？事務帶來的什么問題？

Mysql中事務的隔離級別分為四大等級：讀未提交（READ UNCOMMITTED）、讀提交 （READ COMMITTED）、可重復讀 （REPEATABLE READ）、串行化 （SERIALIZABLE）。

在Mysql中事務的四大特性主要包含：原子性（Atomicity）、一致性（Consistent）、隔離性（Isalotion）、持久性(Durable)，簡稱為ACID。

1. 原子性：是指事務的原子性操作， 對數(shù)據(jù)的修改要么全部執(zhí)行成功，要么全部失敗，實現(xiàn)事務的原子性，是基于日志的Redo/Undo機制。
2. 一致性：是指 執(zhí)行事務前后的狀態(tài)要一致，可以理解為數(shù)據(jù)一致性。
3. 隔離性：側重指 事務之間相互隔離，不受影響，這個與事務設置的隔離級別有密切的關系。
4. 持久性：則是指在 一個事務提交后，這個事務的狀態(tài)會被持久化到數(shù)據(jù)庫中，也就是事務提交，對數(shù)據(jù)的新增、更新將會持久化到數(shù)據(jù)庫中。

在我的理解中：原子性、隔離性、持久性都是為了保障一致性而存在的，一致性也是最終的目的。

沒有那種隔離級別是完美的，只能根據(jù)自己的項目業(yè)務場景去評估選擇最適合的隔離級別，大部分的公司一般選擇Mysql默認的隔離級別：可重復讀。

隔離級別從：讀未提交-讀提交-可重復讀-串行化，級別越來越高，隔離也就越來越嚴實，到最后的串行化，當出現(xiàn)讀寫鎖沖突的時候，后面的事務只能等前面的事務完成后才能繼續(xù)訪問。

1. 讀未提交：讀取到別的事務還沒有提交的數(shù)據(jù)，從而產(chǎn)生了臟讀。
2. 讀提交：讀取別的事務已經(jīng)提交的數(shù)據(jù)，從而產(chǎn)生不可重復讀。
3. 可重復讀：事務開啟過程中看到的數(shù)據(jù)和事務剛開始看到的數(shù)據(jù)是一樣的，從而產(chǎn)生幻讀，在Mysql的中通過MVCC多版本控制的一致性視圖解決了不可重復讀問題以及通過間隙鎖解決了幻讀問題。
4. 串行化：對于同一行記錄，若是讀寫鎖發(fā)生沖突，后面訪問的事務只能等前面的事務執(zhí)行完才能繼續(xù)訪問。

舉個例子，假如有一個user表，里面有兩個字段id和age，里面有一條測試數(shù)據(jù)：（1,24），現(xiàn)在要執(zhí)行age+1，同時有兩個事務執(zhí)行：

經(jīng)過上面的執(zhí)行，在四種隔離級別下a1,a2,a3,a4,a5的值分別是多少？我們來認真的分析一波：

1. 讀未提交：a1和a2因為讀的是初始值所以為24，隔離級別為讀未提交，事務2執(zhí)行了age=age+1，不管事務2是否提交，那么a3、a4和a5的值都是25。
2. 讀提交：a1和a2因為讀的是初始值所以為24，隔離級別為讀提交所以a3還是24，a4和a5因為事務2已經(jīng)提交所以得到的值是25。
3. 可重復讀：a1和a2因為讀的是初始值所以為24，可重復讀的隔離級別下，a3和a4讀取的值和事務開始的結果一樣，所以還是24，a5前一步因為已經(jīng)提交事務，所以a5的值是25。
4. 串行化：a1和a2因為讀的是初始值所以為24，串行化隔離級別下，當事務2修改數(shù)據(jù)的時候，獲取了寫鎖，事務1讀取age的值會被鎖住，所以在事務1的角度下a3和a4讀取的值為24，a5的值為25。

當你能夠分析得出這個例子下，在不同隔離級別下分析的出a1-a5的值，說明你對事務的隔離級別已經(jīng)有比較深入的理解了。

2.你詳細了解過MVCC嗎？它是怎么工作的？

MVCC叫做多版本控制，實現(xiàn)MVCC時用到了一致性視圖，用于支持讀提交和可重復讀的實現(xiàn)。

對于一行數(shù)據(jù)若是想實現(xiàn)可重復讀取或者能夠讀取數(shù)據(jù)的另一個事務未提交前的原始值，那么必須對原始數(shù)據(jù)進行保存或者對更新操作進行保存，這樣才能夠查詢到原始值。

在Mysql的MVCC中規(guī)定每一行數(shù)據(jù)都有多個不同的版本，一個事務更新操作完后就生成一個新的版本，并不是對全部數(shù)據(jù)的全量備份，因為全量備份的代價太大了：

如圖中所示，假如三個事務更新了同一行數(shù)據(jù)，那么就會有對應的v1、v2、v3三個數(shù)據(jù)版本，每一個事務在開始的時候都獲得一個唯一的事務id（transaction id），并且是順序遞增的，并且這個事務id最后會賦值給row trx_id，這樣就形成了一個唯一的一行數(shù)據(jù)版本。

實際上版本1、版本2并非實際物理存在的，而圖中的U1和U2實際就是undo log日志（回滾日志），這v1和v2版本是根據(jù)當前v3和undo log計算出來的。

InnoDB引擎就是利用每行數(shù)據(jù)有多個版本的特性，實現(xiàn)了秒級創(chuàng)建“快照”，并不需要花費大量的是時間。

3.Mysql的InnoDB和MyISAM有什么區(qū)別？

（1）InnoDB和MyISAM都是Mysql的存儲引擎，現(xiàn)在MyISAM也逐漸被InnoDB給替代，主要因為InnoDB支持事務和行級鎖，MyISAM不支持事務和行級鎖，MyISAM最小鎖單位是表級。因為MyISAM不支持行級鎖，所以在并發(fā)處理能力上InnoDB會比MyISAM好。

（2） 數(shù)據(jù)的存儲上：MyISAM的索引也是由B+樹構成，但是樹的葉子結點存的是行數(shù)據(jù)的地址，查找時需要找到葉子結點的地址，再根據(jù)葉子結點地址查找數(shù)據(jù)。

InnoDB的主鍵索引的葉子結點直接就是存儲行數(shù)據(jù)，查找主鍵索引樹就能獲得數(shù)據(jù)：

若是根據(jù)非主鍵索引查找，非主鍵索引的葉子結點存儲的就是，當前索引值以及對應的主鍵的值，若是聯(lián)合索引存儲的就是聯(lián)合索引值和對應的主鍵值。

（3）數(shù)據(jù)文件構成：MyISAM有三種存儲文件分別是擴展名為：.frm（文件存儲表定義）、.MYD (MYData數(shù)據(jù)文件)、.MYI (MYIndex索引文件)。而InnoDB的表只受限于操作系統(tǒng)文件的大小，一般是2GB

（4）查詢區(qū)別：對于讀多寫少的業(yè)務場景，MyISAM會更加適合，而對于update和insert比較多的場景InnoDB會比較適合。

（5）coun(*)區(qū)別：select count(*) from table，MyISAM引擎會查詢已經(jīng)保存好的行數(shù)，這是不加where的條件下，而InnoDB需要全表掃描一遍，InnoDB并沒有保存表的具體行數(shù)。

（6）其它的區(qū)別：InnoDB支持外鍵，但是不支持全文索引，而MyISAM不支持外鍵，支持全文索引，InnoDB的主鍵的范圍比MyISAM的大。

4.你知道執(zhí)行一條查詢語句的流程嗎？

當Mysql執(zhí)行一條查詢的SQl的時候大概發(fā)生了以下的步驟：

1. 客戶端發(fā)送查詢語句給服務器。
2. 服務器首先進行用戶名和密碼的驗證以及權限的校驗。
3. 然后會檢查緩存中是否存在該查詢，若存在，返回緩存中存在的結果。若是不存在就進行下一步。注意：Mysql 8就把緩存這塊給砍掉了。
4. 接著進行語法和詞法的分析，對SQl的解析、語法檢測和預處理，再由優(yōu)化器生成對應的執(zhí)行計劃。
5. Mysql的執(zhí)行器根據(jù)優(yōu)化器生成的執(zhí)行計劃執(zhí)行，調(diào)用存儲引擎的接口進行查詢。服務器將查詢的結果返回客戶端。

Mysql中語句的執(zhí)行都是都是分層執(zhí)行，每一層執(zhí)行的任務都不同，直到最后拿到結果返回，主要分為Service層和引擎層。

在Service層中包含：連接器、分析器、優(yōu)化器、執(zhí)行器。引擎層以插件的形式可以兼容各種不同的存儲引擎，主要包含的有InnoDB和MyISAM兩種存儲引擎。具體的執(zhí)行流程圖如下所示：

5.redo log和binlog了解過嗎？

redo log日志也叫做WAL技術（Write- Ahead Logging），他是一種先寫日志，并更新內(nèi)存，最后再更新磁盤的技術，為了就是減少sql執(zhí)行期間的數(shù)據(jù)庫io操作，并且更新磁盤往往是在Mysql比較閑的時候，這樣就大大減輕了Mysql的壓力。

redo log是固定大小，是物理日志，屬于InnoDB引擎的，并且寫redo log是環(huán)狀寫日志的形式：

如上圖所示：若是四組的redo log文件，一組為1G的大小，那么四組就是4G的大小，其中write pos是記錄當前的位置，有數(shù)據(jù)寫入當前位置，那么write pos就會邊寫入邊往后移。

check point記錄擦除的位置，因為redo log是固定大小，所以當redo log滿的時候，也就是write pos追上check point的時候，需要清除redo log的部分數(shù)據(jù)，清除的數(shù)據(jù)會被持久化到磁盤中，然后將check point向前移動。

redo log日志實現(xiàn)了即使在數(shù)據(jù)庫出現(xiàn)異常宕機的時候，重啟后之前的記錄也不會丟失，這就是crash-safe能力。

binlog稱為歸檔日志，是邏輯上的日志，它屬于Mysql的Server層面的日志，記錄著sql的原始邏輯，主要有兩種模式：一個是statement格式記錄的是原始的sql，而row格式則是記錄行內(nèi)容。

redo log和binlog記錄的形式、內(nèi)容不同，這兩者日志都能通過自己記錄的內(nèi)容恢復數(shù)據(jù)。

之所以這兩個日志同時存在，是因為剛開始Mysql自帶的引擎MyISAM就沒有crash-safe功能的，并且在此之前Mysql還沒有InnoDB引擎，Mysql自帶的binlog日志只是用來歸檔日志的，所以InnoDB引擎也就通過自己redo log日志來實現(xiàn)crash-safe功能。

6.線上要給熱點數(shù)據(jù)表添加字段該怎么操作？

首先給表加一個字段，會導致掃描全表數(shù)據(jù)，并且會加MDL寫鎖，所以在線上操作一定要謹慎再謹慎，有可能還沒操作完就導致數(shù)據(jù)庫給搞崩了。

對于這種情況有限考慮線上的穩(wěn)定的運行，加字段是其次，可以通過在alter table后設定等待的時間，若是獲取不到鎖后面在進行嘗試，并且可以選擇訪問量比較上的時間段進行獲取。

若是能獲取到鎖那是最好了，當然即使獲取到鎖也不要阻塞后面的業(yè)務語句，一切都是以業(yè)務優(yōu)先為原則。

7.Msyql的索引的底層實現(xiàn)嗎？為什么不用有序數(shù)組、hash或者二叉樹實現(xiàn)索引？

Mysql的索引是一種加快查詢速度的數(shù)據(jù)結構，索引就好比書的目錄一樣能夠快速的定位你要查找的位置。

Mysql的索引底層是使用B+樹的數(shù)據(jù)結構進行實現(xiàn)，結構如下圖所示：

索引的一個數(shù)據(jù)頁的大小是16kb，從磁盤加載到內(nèi)存中是以數(shù)據(jù)頁的大小為單位進行加載，然后供查詢操作進行查詢，若是查詢的數(shù)據(jù)不在內(nèi)存中，才會從磁盤中再次加載到內(nèi)存中。

索引的實現(xiàn)有很多，比如hash。hash是以key-value的形式進行存儲，適合于等值查詢的場景，查詢的時間復雜度為O(1)，因為hash儲存并不是有序的，所以對于范圍查詢就可能要遍歷所有數(shù)據(jù)進行查詢，而且不同值的計算還會出現(xiàn)hash沖突，所以hash并不適合于做Mysql的索引。

有序數(shù)組在等值查詢和范圍查詢性能都是非常好的，那為什么又不用有序數(shù)組作為索引呢？因為對于數(shù)組而言作為索引更新的成本太高，新增數(shù)據(jù)要把后面的數(shù)據(jù)都往后移一位，所以也不采用有序數(shù)組作為索引的底層實現(xiàn)。

最后二叉樹，主要是因為二叉樹只有二叉，一個節(jié)點存儲的數(shù)據(jù)量非常有限，需要頻繁的隨機IO讀寫磁盤，若是數(shù)據(jù)量大的情況下二叉的樹高太高，嚴重影響性能，所以也不采用二叉樹進行實現(xiàn)。

而B+樹是多叉樹，一個數(shù)據(jù)頁的大小是16kb，在1-3的樹高就能存儲10億級以上的數(shù)據(jù)，也就是只要訪問磁盤1-3次就足夠了，并且B+樹的葉子結點上一個葉子結點有指針指向下一個葉子結點，便于范圍查詢：

8.怎么查看索引是否生效？什么情況下索引會失效呢？

查看索引是否起作用可以使用explain關鍵字，查詢后的語句中的key字段，若是使用了索引，該字段會展示索引的名字。

（1）where條件查詢中使用了or關鍵字，有可能使用了索引進行查詢也會導致索引失效，若是想使用or關鍵字，又不想索引失效，只能在or的所有列上都建立索引。

（2）條件查詢中使用like關鍵字，并且不符合最左前綴原則，會導致索引失效。

（3）條件查詢的字段是字符串，而錯誤的使用where column = 123 數(shù)字類型也會導致索引失效。

（4）對于聯(lián)合索引查詢不符合最左前綴原則，也會導致索引失效，如下所示：

alter?table?user?add?index?union_index(name,?age)???//?name左邊的列，?age?右邊的列??????????????????????????????????????????????????????????????
select?*?from?user?where?name?=?'lidu'?????//?會用到索引
select?*?from?user?where?age?=?18??????????//??不會使用索引

（5）在where條件查詢的后面對字段進行null值判斷，會導致索引失效，解決的辦法就是可以把null改為0或者-1這些特殊的值代替：

SELECT?id?FROM?table?WHERE?num?is?null

（6）在where子句中使用!= ,< >這樣的符號，也會導致索引失效。

SELECT?id?FROM?table?WHERE?num?!=?0

（7）where條件子句中=的左邊使用表達式操作或者函數(shù)操作，也會導致索引失效。

SELECT?id?FROM?user?WHERE?age?/?2?=?1
SELECT?id?FROM?user?WHERE?SUBSTRING(name,1,2)?=?'lidu'

9.你知道有哪些種類的索引？

索引從數(shù)據(jù)結構進行劃分的分為：B+樹索引、hash索引、R-Tree索引、FULLTEXT索引。

索引從物理存儲的角度劃分為：聚族索引和非聚族索引。

從邏輯的角度分為：主鍵索引、普通索引、唯一索引、聯(lián)合索引以及空間索引。

10.你平時是怎么進行SQL優(yōu)化的？

SQL的優(yōu)化主要是對字段添加索引，主要包含有這四種索引(主鍵索引/唯一索引/全文索引/普通索引)，以及結合具體的業(yè)務場景分析具體是使用什么索引最合理。

explain 可以幫助我們在不真正執(zhí)行某個sql語句時，就執(zhí)行mysql怎樣執(zhí)行，這樣利用我們?nèi)シ治鰏ql指令：

1. id：查詢的序列號。
2. select_type：查詢類型。
3. table：查詢表名。
4. type：掃描方式，all表示全表掃描。
5. possible_keys：可是使用到的索引。
6. key：實際使用到的索引。
7. rows：該sql掃面了多少行。
8. Extra：sql語句額外的信息，比如排序方式

SQL優(yōu)化方法

（1）對于條件查詢，首先考慮在條件where和order by后的字段建立索引。（2）避免索引失效，避免where條件后進行null值的判斷。（3）避免where后使用!=或<>操作符。（4）避免在where后面進行使用函數(shù)。（5）避免where條件后使用or關鍵字來連接。

上面的這一些都是要注意的，當然還有很多的小技巧，都有可能會導致索引的實效。

索引的種類

另一方面就是考慮到底是建立哪種索引比較合適，這里以普通索引和唯一索引進行舉例說明。

假如我們的業(yè)務場景是讀多寫少的場景，那么SQL查詢請求過來，假如數(shù)據(jù)已經(jīng)在內(nèi)存中，獲取到數(shù)據(jù)后就直接返回，假如數(shù)據(jù)不在內(nèi)存的數(shù)據(jù)頁中，就會加載磁盤到內(nèi)存中再返回，對于這種場景可能對于普通索引和唯一索引的選擇性能上并沒有明顯的區(qū)別。

但是，一般建議選擇普通索引，在寫多讀少的場景下，這兩者索引的選擇對性能的影響就比較大了，對于普通索引的的寫，不管數(shù)據(jù)是否存在于內(nèi)存中，都會先寫入內(nèi)存中的一小塊叫做chang buffer內(nèi)存中，然后在通過后臺刷盤，一般會選擇Mysql比較閑的時候進行刷盤。

而唯一索引就不同了，因為他要確保索引的唯一性，索引寫數(shù)據(jù)的時候，假如數(shù)據(jù)不在內(nèi)存中，要先從磁盤中加載數(shù)據(jù)到內(nèi)存中，然后比較是否唯一，所以唯一索引就不能使用chang buffer的優(yōu)化機制，會頻繁的進行隨機的磁盤IO。

11.什么是聚簇索引和非聚簇索引？

聚族索引和非聚族索引的主要區(qū)別是：聚族索引的葉子結點就是數(shù)據(jù)節(jié)點，而非聚族索引的葉子結點存儲仍然是索引節(jié)點，只不過有指向?qū)獢?shù)據(jù)塊的指針。

區(qū)別這兩者的區(qū)別就是來對比InnoDB和MYISAM的數(shù)據(jù)結構了。假如我們有一個表原始數(shù)據(jù)如下所示：

那么在MyISAM的索引中數(shù)據(jù)的儲存結構如下所示：

MyISAM以葉子結點存儲的Row number來找到對應的行數(shù)據(jù)，也就是葉子結點存儲的是行指針，這也可以發(fā)現(xiàn)MyISAM引擎中數(shù)據(jù)文件（.MYI）和索引文件（.MYD）是分開的，索引MyISAM的查找索引樹后，需要根據(jù)行指針二次的進行定位。

而在InnoDB的主鍵索引存儲的結構形式如下所示：

InnoDB的主鍵索引中葉子結點并不是存儲行指針，而是存儲行數(shù)據(jù)，二級索引中MyISAM也是一樣的存儲方式，InnoDB的二級索引的葉子結點則是存儲當前索引值以及對應的主鍵索引值。

InnoDB的二級索引帶來的好處就是減少了由于數(shù)據(jù)移動或者數(shù)據(jù)頁分列導致行數(shù)據(jù)的地址變了而帶來的維護二級索引的性能開銷，因為InnoDB的二級索引不需要更新行指針：

12.什么是回表？回表是怎么產(chǎn)生的呢？

上面說過InnoDB引擎的主鍵索引存儲的是行數(shù)據(jù)，二級索引的葉子結點存儲的是索引數(shù)據(jù)以及對應的主鍵，所以回表就是根據(jù)索引進行條件查詢，回到主鍵索引樹進行搜索的過程：

因為查詢還要回表一次，再次查詢主鍵索引樹，所以實際中應該盡量避免回表的產(chǎn)生。

13.怎么解決回表的問題？

解決回表問題可以建立聯(lián)合索引進行索引覆蓋，如圖所示根據(jù)name字段查詢用戶的name和sex屬性出現(xiàn)了回表問題：

那么我們可以建立下面這個聯(lián)合索引來解決：

create?table?user?(
?id?int?primary?key,
?name?varchar(20),
?sex?varchar(5),
?index(name,?sex)
)?engine?=?innodb;

建立了如上所示的index(name,sex)聯(lián)合索引，在二級索引的葉子結點的位置就會同時也出現(xiàn)sex字段的值，因為能夠獲取到要查詢的所有字段，因為就不用再回表查詢一次。

14.什么是最左前綴原則？

最左前綴原則可以是聯(lián)合索引的的最左N個字段，也可以是字符串索引的最左的M個字符。舉個例子，假如現(xiàn)在有一個表的原始數(shù)據(jù)如下所示：

并根據(jù)col3 ，col2的順序建立聯(lián)合索引，此時聯(lián)合索引樹結構如圖下所示：

葉子結點中首先會根據(jù)col3的字符進行排序，若是col3相等，在col3相等的值里面再對col2進行排序，假如我們要查詢where col3 like 'Eri%'，就可以快速的定位查詢到Eric。

若是查詢條件為where col3 like '%se'，前面的字符不確定，表示任意字符都可以，這樣就可以導致全表掃描進行字符的比較，就會使索引失效。

15.什么是索引下推？

Mysql5.6之前是沒有索引下推這個功能，后面為了提高性能，避免不必要的回表5.6之后就有了索引下推優(yōu)化的功能。

假如我們有一個用戶表，并且使用用戶的name，age兩個字段建立聯(lián)合索引，name在沒有索引下推的功能，執(zhí)行下面的sql，執(zhí)行的流程如下圖所示：

select?*?from?tuser?where?name?like?'張%'?and?age=10?and?ismale=1;

當比較第一個索引字段name like '張%' 就會篩選出四行數(shù)據(jù)，后面它不會再比較age值是否符合要求，直接獲取到主鍵值，然后在回表查詢，回表后再對比age、ismale是否符合條件。

從上面的數(shù)據(jù)看來其實name，age兩個字段建立的聯(lián)合索引，兩個字段的值會存儲在聯(lián)合索引樹中，可以直接對比age字段是否符合查詢的條件age=10，那么索引下推就是做了這些事：

索引下推會再次根據(jù)你的age進行比較，發(fā)現(xiàn)有兩條記錄不符合條件直接過濾掉，符合條件的才會進行回表查詢，這樣就減少了不必要的回表查詢。

16.主鍵使用自增ID還是UUID?能說說原因嗎？

自增ID和UUID作為主鍵的考慮主要有兩方面，一個是性能另一個就是存儲的空間大小，一般沒有特定的業(yè)務要求都不推薦使用UUID作為主鍵。

因為使用UUID作為主鍵插入并不能保證插入是有序的，有可能會涉及數(shù)據(jù)的挪動，也有可能觸發(fā)數(shù)據(jù)頁的分裂，因為一個數(shù)據(jù)頁的大小就是16KB，這樣插入數(shù)據(jù)的成本就會比較高。

而自增ID作為主鍵的話插入數(shù)據(jù)都是追加操作，不會有數(shù)據(jù)的移動以及數(shù)據(jù)頁的分裂，性能會比較好。

另一方面就是存儲空間，自增主鍵一般整形只要4個字節(jié)，長整形才占8字節(jié)的大小空間，而使用UUID作為主鍵存儲空間需要16字節(jié)的大小，會占用更多的磁盤，在二級索引中也會存出一份主鍵索引，這樣多占用消耗的空間就是兩倍，性能低，所以不推薦使用。

17.Mysql是怎么控制并發(fā)的訪問資源？

Mysql內(nèi)部通過鎖機制實現(xiàn)對資源的并發(fā)訪問控制，保證數(shù)據(jù)的一致性，鎖機制的類型和引擎的種類有關，MyISAM中默認支持的表級鎖有兩種：共享讀鎖和獨占寫鎖。表級鎖在MyISAM和InnoDB的存儲引擎中都支持，但是InnoDB默認支持的是行鎖。

MyISAM鎖機制

Mysql中可以通過以下sql來顯示的在事務中顯式的進行加鎖和解鎖操作：

//?顯式的添加表級讀鎖
LOCK?TABLE?表名?READ
//?顯示的添加表級寫鎖
LOCK?TABLE?表名?WRITE
//?顯式的解鎖（當一個事務commit的時候也會自動解鎖）
unlock?tables;

（1）MyISAM表級寫鎖：當一個線程獲取到表級寫鎖后，只能由該線程對表進行讀寫操作，別的線程必須等待該線程釋放鎖以后才能操作。

（2）MyISAM表級共享讀鎖：當一個線程獲取到表級讀鎖后，該線程只能讀取數(shù)據(jù)不能修改數(shù)據(jù)，其它線程也只能加讀鎖，不能加寫鎖。

InnoDB鎖機制

InnoDB和MyISAM不同的是，InnoDB支持行鎖和事務，InnoDB中除了有表鎖和行級鎖的概念，還有Gap Lock（間隙鎖）、Next-key Lock鎖，間隙鎖主要用于范圍查詢的時候，鎖住查詢的范圍，并且間隙鎖也是解決幻讀的方案。

InnoDB中的行級鎖是對索引加的鎖，在不通過索引查詢數(shù)據(jù)的時候，InnoDB就會使用表鎖。

但是通過索引查詢的時候是否使用索引，還要看Mysql的執(zhí)行計劃，Mysql的優(yōu)化器會判斷是一條sql執(zhí)行的最佳策略。

若是Mysql覺得執(zhí)行索引查詢還不如全表掃描速度快，那么Mysql就會使用全表掃描來查詢，這是即使sql語句中使用了索引，最后還是執(zhí)行為全表掃描，加的是表鎖。

18.Mysql的死鎖是怎么發(fā)生的？怎么解決死鎖問題？

死鎖在InnoDB中才會出現(xiàn)死鎖，MyISAM是不會出現(xiàn)死鎖，因為MyISAM支持的是表鎖，一次性獲取了所有的鎖，其它的線程只能排隊等候。

而InnoDB默認支持行鎖，獲取鎖是分步的，并不是一次性獲取所有的鎖，因此在鎖競爭的時候就會出現(xiàn)死鎖的情況。

雖然InnoDB會出現(xiàn)死鎖，但是并不影響InnoDB成為最受歡迎的存儲引擎，MyISAM可以理解為串行化操作，讀寫有序，因此支持的并發(fā)性能低下。

（1）死鎖案例一：

舉一個例子，現(xiàn)在數(shù)據(jù)庫表employee中六條數(shù)據(jù)，如下所示：

其中name=ldc的有兩條數(shù)據(jù)，并且name字段為普通索引，分別是id=2和id=3的數(shù)據(jù)行，現(xiàn)在假設有兩個事務分別執(zhí)行下面的兩條sql語句：

//?session1執(zhí)行
update?employee?set?num?=?2?where?name?='ldc';
//?session2執(zhí)行
select?*?from?employee?where?id?=?2?or?id?=3;

其中session1執(zhí)行的sql獲取的數(shù)據(jù)行是兩條數(shù)據(jù)，假設先獲取到第一個id=2的數(shù)據(jù)行，然后cpu的時間分配給了另一個事務，另一個事務執(zhí)行查詢操作獲取了第二行數(shù)據(jù)也就是id=3的數(shù)據(jù)行。

當事務2繼續(xù)執(zhí)行的時候獲取到id=3的數(shù)據(jù)行，鎖定了id=3的數(shù)據(jù)行，此時cpu又將時間分配給了第一個事務，第一個事務執(zhí)行準備獲取第二行數(shù)據(jù)的鎖，發(fā)現(xiàn)已經(jīng)被其他事務獲取了，它就處于等待的狀態(tài)。

當cpu把時間有分配給了第二個事務，第二個事務準備獲取第一行數(shù)據(jù)的鎖發(fā)現(xiàn)已經(jīng)被第一個事務獲取了鎖，這樣就行了死鎖，兩個事務彼此之間相互等待。

（2）死鎖案例二

第二種死鎖情況就是當一個事務開始并且update一條id=1的數(shù)據(jù)行時，成功獲取到寫鎖，此時另一個事務執(zhí)行也update另一條id=2的數(shù)據(jù)行時，也成功獲取到寫鎖（id為主鍵）。

此時cpu將時間分配給了事務一，事務一接著也是update id=2的數(shù)據(jù)行，因為事務二已經(jīng)獲取到id=2數(shù)據(jù)行的鎖，所以事務已處于等待狀態(tài)。

事務二有獲取到了時間，像執(zhí)行update id=1的數(shù)據(jù)行，但是此時id=1的鎖被事務一獲取到了，事務二也處于等待的狀態(tài)，因此形成了死鎖。

死鎖的解決方案

首先要解決死鎖問題，在程序的設計上，當發(fā)現(xiàn)程序有高并發(fā)的訪問某一個表時，盡量對該表的執(zhí)行操作串行化，或者鎖升級，一次性獲取所有的鎖資源。

然后也可以設置參數(shù)innodb_lock_wait_timeout，超時時間，并且將參數(shù)innodb_deadlock_detect 打開，當發(fā)現(xiàn)死鎖的時候，自動回滾其中的某一個事務。

19.能說一說Mysql的主從復制嗎？

讀寫分離

實現(xiàn)MySQL讀寫分離的前提是我們已經(jīng)將MySQL主從復制配置完畢，讀寫分離實現(xiàn)方式：（1）配置多數(shù)據(jù)源。（2）使用mysql的proxy中間件代理工具。

主從復制的原理

MySQL的主從復制和讀寫分離兩者有著緊密的聯(lián)系，首先要部署主從復制，只有主從復制完成了才能在此基礎上進行數(shù)據(jù)的讀寫分離。

讀寫分離的原理

讀寫分離就是只在主服務器上寫，只在從服務器上讀?；驹硎亲屩鲾?shù)據(jù)庫處理事務性查詢，而從服務器處理select查詢。數(shù)據(jù)庫復制被用來把事務性查詢導致的變更同步到從數(shù)據(jù)庫中。

20.能說一說分庫分表嗎？怎么分？

首先為什么要分表？（1） 如果一個表的每條記錄的內(nèi)容很大，那么就需要更多的IO操作，如果字段值比較大，而使用頻率相對比較低，可以將大字段移到另一張表中，當查詢不查大字段的時候，這樣就減少了I/O操作 （2）如果表的數(shù)據(jù)量非常非常大，那么查詢就變的比較慢；也就是表的數(shù)據(jù)量影響查詢的性能。（3）表中的數(shù)據(jù)本來就有獨立性，例如分別記錄各個地區(qū)的數(shù)據(jù)或者不同時期的數(shù)據(jù)，特別是有些數(shù)據(jù)常用，而另外一些數(shù)據(jù)不常用。（4） 分表技術有(水平分割和垂直分割)

垂直分割

垂直分割是指數(shù)據(jù)表列的拆分，把一張列比較多的表拆分為多張表。垂直分割一般用于拆分大字段和訪問頻率低的字段，分離冷熱數(shù)據(jù)。

垂直分割比較常見：例如博客系統(tǒng)中的文章表，比如文章tbl_articles (id, titile, summary, content, user_id, create_time)，因為文章中的內(nèi)容content會比較長，放在tbl_articles中會嚴重影響表的查詢速度，所以將內(nèi)容放到tbl_articles_detail(article_id, content)，像文章列表只需要查詢tbl_articles中的字段即可。

垂直拆分的優(yōu)點：可以使得行數(shù)據(jù)變小，在查詢時減少讀取的Block數(shù)，減少I/O次數(shù)。此外，垂直分區(qū)可以簡化表的結構，易于維護。

垂直拆分的缺點：主鍵會出現(xiàn)冗余，需要管理冗余列，并會引起Join操作，可以通過在應用層進行Join來解決。此外，垂直分區(qū)會讓事務變得更加復雜。

水平分割

水平拆分是指數(shù)據(jù)表行數(shù)據(jù)的拆分，表的行數(shù)超過500萬行或者單表容量超過10GB時，查詢就會變慢，這時可以把一張的表的數(shù)據(jù)拆成多張表來存放。水平分表盡可能使每張表的數(shù)據(jù)量相當，比較均勻。

水平拆分會給應用增加復雜度，它通常在查詢時需要多個表名，查詢所有數(shù)據(jù)需要union操作。在許多數(shù)據(jù)庫應用中，這種復雜性會超過它帶來的優(yōu)點。

因為只要索引關鍵字不大，則在索引用于查詢時，表中增加2-3倍數(shù)據(jù)量，查詢時也就增加讀一個索引層的磁盤次數(shù)，所以水平拆分要考慮數(shù)據(jù)量的增長速度，根據(jù)實際情況決定是否需要對表進行水平拆分。

水平分割最重要的是找到分割的標準，不同的表應根據(jù)業(yè)務找出不同的標準

1. 用戶表可以根據(jù)用戶的手機號段進行分割如user183、user150、user153、user189等，每個號段就是一張表。
2. 用戶表也可以根據(jù)用戶的id進行分割，加入分3張表user0,user1,user2，如果用戶的id%3=0就查詢user0表，如果用戶的id%3=1就查詢user1表。
3. 對于訂單表可以按照訂單的時間進行分表。

分庫分表技術

現(xiàn)在市面上主要的分庫分表技術有mycat和sharding-jdbc，具體的分庫分表的技術講解我們留在下一次進行詳細的講解。

參考

《Mysql45講》

《數(shù)據(jù)庫原理》