關(guān)于Mysql的20道問題詳解

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

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

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

原子性：是指事務(wù)的原子性操作，對(duì)數(shù)據(jù)的修改要么全部執(zhí)行成功，要么全部失敗，實(shí)現(xiàn)事務(wù)的原子性，是基于日志的Redo/Undo機(jī)制。

一致性：是指執(zhí)行事務(wù)前后的狀態(tài)要一致，可以理解為數(shù)據(jù)一致性。

隔離性：側(cè)重指事務(wù)之間相互隔離，不受影響，這個(gè)與事務(wù)設(shè)置的隔離級(jí)別有密切的關(guān)系。

持久性：則是指在一個(gè)事務(wù)提交后，這個(gè)事務(wù)的狀態(tài)會(huì)被持久化到數(shù)據(jù)庫中，也就是事務(wù)提交，對(duì)數(shù)據(jù)的新增、更新將會(huì)持久化到數(shù)據(jù)庫中。

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

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

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

讀未提交：讀取到別的事務(wù)還沒有提交的數(shù)據(jù)，從而產(chǎn)生了臟讀。

讀提交：讀取別的事務(wù)已經(jīng)提交的數(shù)據(jù)，從而產(chǎn)生不可重復(fù)讀。

可重復(fù)讀：事務(wù)開啟過程中看到的數(shù)據(jù)和事務(wù)剛開始看到的數(shù)據(jù)是一樣的，從而產(chǎn)生幻讀，在Mysql的中通過MVCC多版本控制的一致性視圖解決了不可重復(fù)讀問題以及通過間隙鎖解決了幻讀問題。

串行化：對(duì)于同一行記錄，若是讀寫鎖發(fā)生沖突，后面訪問的事務(wù)只能等前面的事務(wù)執(zhí)行完才能繼續(xù)訪問。

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

事務(wù)1事務(wù)2

啟動(dòng)事務(wù)，接著查詢age（a1）

啟動(dòng)事務(wù)

查詢age（a2）

執(zhí)行age=age+1

查詢age（a3）

提交事務(wù)

查詢age（a4）

提交事務(wù)

查詢age（a5）

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

讀未提交：a1和a2因?yàn)樽x的是初始值所以為24，隔離級(jí)別為讀未提交，事務(wù)2執(zhí)行了age=age+1，不管事務(wù)2是否提交，那么a3、a4和a5的值都是25。

讀提交：a1和a2因?yàn)樽x的是初始值所以為24，隔離級(jí)別為讀提交所以a3還是24，a4和a5因?yàn)槭聞?wù)2已經(jīng)提交所以得到的值是25。

可重復(fù)讀：a1和a2因?yàn)樽x的是初始值所以為24，可重復(fù)讀的隔離級(jí)別下，a3和a4讀取的值和事務(wù)開始的結(jié)果一樣，所以還是24，a5前一步因?yàn)橐呀?jīng)提交事務(wù)，所以a5的值是25。

串行化：a1和a2因?yàn)樽x的是初始值所以為24，串行化隔離級(jí)別下，當(dāng)事務(wù)2修改數(shù)據(jù)的時(shí)候，獲取了寫鎖，事務(wù)1讀取age的值會(huì)被鎖住，所以在事務(wù)1的角度下a3和a4讀取的值為24，a5的值為25。

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

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

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

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

在Mysql的MVCC中規(guī)定每一行數(shù)據(jù)都有多個(gè)不同的版本，一個(gè)事務(wù)更新操作完后就生成一個(gè)新的版本，并不是對(duì)全部數(shù)據(jù)的全量備份，因?yàn)槿總浞莸拇鷥r(jià)太大了：

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

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

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

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

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

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

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

若是根據(jù)非主鍵索引查找，非主鍵索引的葉子結(jié)點(diǎn)存儲(chǔ)的就是，當(dāng)前索引值以及對(duì)應(yīng)的主鍵的值，若是聯(lián)合索引存儲(chǔ)的就是聯(lián)合索引值和對(duì)應(yīng)的主鍵值。

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

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

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

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

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

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

客戶端發(fā)送查詢語句給服務(wù)器。

服務(wù)器首先進(jìn)行用戶名和密碼的驗(yàn)證以及權(quán)限的校驗(yàn)。

然后會(huì)檢查緩存中是否存在該查詢，若存在，返回緩存中存在的結(jié)果。若是不存在就進(jìn)行下一步。注意：Mysql 8就把緩存這塊給砍掉了。

接著進(jìn)行語法和詞法的分析，對(duì)SQl的解析、語法檢測和預(yù)處理，再由優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃。

Mysql的執(zhí)行器根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃執(zhí)行，調(diào)用存儲(chǔ)引擎的接口進(jìn)行查詢。服務(wù)器將查詢的結(jié)果返回客戶端。

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

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

5.redo log和binlog了解過嗎？

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

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

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

check point記錄擦除的位置，因?yàn)閞edo log是固定大小，所以當(dāng)redo log滿的時(shí)候，也就是write pos追上check point的時(shí)候，需要清除redo log的部分?jǐn)?shù)據(jù)，清除的數(shù)據(jù)會(huì)被持久化到磁盤中，然后將check point向前移動(dòng)。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

最后二叉樹，主要是因?yàn)槎鏄渲挥卸?，一個(gè)節(jié)點(diǎn)存儲(chǔ)的數(shù)據(jù)量非常有限，需要頻繁的隨機(jī)IO讀寫磁盤，若是數(shù)據(jù)量大的情況下二叉的樹高太高，嚴(yán)重影響性能，所以也不采用二叉樹進(jìn)行實(shí)現(xiàn)。

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

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

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

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

（2）條件查詢中使用like關(guān)鍵字，并且不符合最左前綴原則，會(huì)導(dǎo)致索引失效。

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

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

alter table user add index union_index（name， age） // name左邊的列， age 右邊的列

select * from user where name = ‘lidu’ // 會(huì)用到索引

select * from user where age = 18 // 不會(huì)使用索引

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

SELECT id FROM table WHERE num is null

（6）在where子句中使用！= ，《 》這樣的符號(hào)，也會(huì)導(dǎo)致索引失效。

SELECT id FROM table WHERE num ！= 0

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

SELECT id FROM user WHERE age / 2 = 1

SELECT id FROM user WHERE SUBSTRING（name，1，2） = ‘lidu’

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

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

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

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

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

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

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

id：查詢的序列號(hào)。

select_type：查詢類型。

table：查詢表名。

type：掃描方式，all表示全表掃描。

possible_keys：可是使用到的索引。

key：實(shí)際使用到的索引。

rows：該sql掃面了多少行。

Extra：sql語句額外的信息，比如排序方式

SQL優(yōu)化方法

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

上面的這一些都是要注意的，當(dāng)然還有很多的小技巧，都有可能會(huì)導(dǎo)致索引的實(shí)效。

索引的種類

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

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

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

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

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

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

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

row numbercol1col2

0998

11256

2300062

。..。..。..

9997188

9998470013

9999393

那么在MyISAM的索引中數(shù)據(jù)的儲(chǔ)存結(jié)構(gòu)如下所示：

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

而在InnoDB的主鍵索引存儲(chǔ)的結(jié)構(gòu)形式如下所示：

InnoDB的主鍵索引中葉子結(jié)點(diǎn)并不是存儲(chǔ)行指針，而是存儲(chǔ)行數(shù)據(jù)，二級(jí)索引中MyISAM也是一樣的存儲(chǔ)方式，InnoDB的二級(jí)索引的葉子結(jié)點(diǎn)則是存儲(chǔ)當(dāng)前索引值以及對(duì)應(yīng)的主鍵索引值。

InnoDB的二級(jí)索引帶來的好處就是減少了由于數(shù)據(jù)移動(dòng)或者數(shù)據(jù)頁分列導(dǎo)致行數(shù)據(jù)的地址變了而帶來的維護(hù)二級(jí)索引的性能開銷，因?yàn)镮nnoDB的二級(jí)索引不需要更新行指針：

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

上面說過InnoDB引擎的主鍵索引存儲(chǔ)的是行數(shù)據(jù)，二級(jí)索引的葉子結(jié)點(diǎn)存儲(chǔ)的是索引數(shù)據(jù)以及對(duì)應(yīng)的主鍵，所以回表就是根據(jù)索引進(jìn)行條件查詢，回到主鍵索引樹進(jìn)行搜索的過程：

因?yàn)椴樵冞€要回表一次，再次查詢主鍵索引樹，所以實(shí)際中應(yīng)該盡量避免回表的產(chǎn)生。

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

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

那么我們可以建立下面這個(gè)聯(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)合索引，在二級(jí)索引的葉子結(jié)點(diǎn)的位置就會(huì)同時(shí)也出現(xiàn)sex字段的值，因?yàn)槟軌颢@取到要查詢的所有字段，因?yàn)榫筒挥迷倩乇聿樵円淮巍?/p>

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

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

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

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

若是查詢條件為where col3 like ‘%se’，前面的字符不確定，表示任意字符都可以，這樣就可以導(dǎo)致全表掃描進(jìn)行字符的比較，就會(huì)使索引失效。

15.什么是索引下推？

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

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

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

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

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

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

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

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

因?yàn)槭褂肬UID作為主鍵插入并不能保證插入是有序的，有可能會(huì)涉及數(shù)據(jù)的挪動(dòng)，也有可能觸發(fā)數(shù)據(jù)頁的分裂，因?yàn)橐粋€(gè)數(shù)據(jù)頁的大小就是16KB，這樣插入數(shù)據(jù)的成本就會(huì)比較高。

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

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

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

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

MyISAM鎖機(jī)制

Mysql中可以通過以下sql來顯示的在事務(wù)中顯式的進(jìn)行加鎖和解鎖操作：

// 顯式的添加表級(jí)讀鎖

LOCK TABLE 表名 READ

// 顯示的添加表級(jí)寫鎖

LOCK TABLE 表名 WRITE

// 顯式的解鎖（當(dāng)一個(gè)事務(wù)commit的時(shí)候也會(huì)自動(dòng)解鎖）

unlock tables;

（1）MyISAM表級(jí)寫鎖：當(dāng)一個(gè)線程獲取到表級(jí)寫鎖后，只能由該線程對(duì)表進(jìn)行讀寫操作，別的線程必須等待該線程釋放鎖以后才能操作。

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

InnoDB鎖機(jī)制

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

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

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

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

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

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

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

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

（1）死鎖案例一：

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

其中name=ldc的有兩條數(shù)據(jù)，并且name字段為普通索引，分別是id=2和id=3的數(shù)據(jù)行，現(xiàn)在假設(shè)有兩個(gè)事務(wù)分別執(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ù)，假設(shè)先獲取到第一個(gè)id=2的數(shù)據(jù)行，然后cpu的時(shí)間分配給了另一個(gè)事務(wù)，另一個(gè)事務(wù)執(zhí)行查詢操作獲取了第二行數(shù)據(jù)也就是id=3的數(shù)據(jù)行。

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

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

（2）死鎖案例二

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

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

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

session1session2

begin;update t set name=‘測試’ where id=1;begin

update t set name=‘測試’ where id=2;

update t set name=‘測試’ where id=2;

等待…update t set name=‘測試’ where id=1;

等待…等待…

死鎖的解決方案

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

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

19.能說一說Mysql的主從復(fù)制嗎？

讀寫分離

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

主從復(fù)制的原理

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

讀寫分離的原理

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

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

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

垂直分割

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

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

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

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

水平分割

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

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

因?yàn)橹灰饕P(guān)鍵字不大，則在索引用于查詢時(shí)，表中增加2-3倍數(shù)據(jù)量，查詢時(shí)也就增加讀一個(gè)索引層的磁盤次數(shù)，所以水平拆分要考慮數(shù)據(jù)量的增長速度，根據(jù)實(shí)際情況決定是否需要對(duì)表進(jìn)行水平拆分。

水平分割最重要的是找到分割的標(biāo)準(zhǔn)，不同的表應(yīng)根據(jù)業(yè)務(wù)找出不同的標(biāo)準(zhǔn)

用戶表可以根據(jù)用戶的手機(jī)號(hào)段進(jìn)行分割如user183、user150、user153、user189等，每個(gè)號(hào)段就是一張表。

用戶表也可以根據(jù)用戶的id進(jìn)行分割，加入分3張表user0，user1，user2，如果用戶的id%3=0就查詢user0表，如果用戶的id%3=1就查詢user1表。

對(duì)于訂單表可以按照訂單的時(shí)間進(jìn)行分表。

責(zé)任編輯：haq