一、基礎(chǔ)

1、mysql可以使用多少列創(chuàng)建索引？

16

2、mysql常用的存儲引擎有哪些

存儲引擎Storage engine：MySQL中的數(shù)據(jù)、索引以及其他對象是如何存儲的，是一套文件系統(tǒng)的實現(xiàn)。常用的存儲引擎有以下：

• Innodb引擎：Innodb引擎提供了對數(shù)據(jù)庫ACID事務(wù)的支持。并且還提供了行級鎖和外鍵的約束。它的設(shè)計的目標(biāo)就是處理大數(shù)據(jù)容量的數(shù)據(jù)庫系統(tǒng)。

• MyISAM引擎(原本Mysql的默認(rèn)引擎)：不提供事務(wù)的支持，也不支持行級鎖和外鍵。

• MEMORY引擎：所有的數(shù)據(jù)都在內(nèi)存中，數(shù)據(jù)的處理速度快，但是安全性不高

3、MySQL 存儲引擎，兩者區(qū)別

MySQL 5.5.5 之前，MyISAM 是 MySQL 的默認(rèn)存儲引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默認(rèn)存儲引擎。

MyISAM和InnoDB 區(qū)別：

• 事務(wù)支持 MyISAM 不提供事務(wù)支持。InnoDB 提供事務(wù)支持

• 表鎖差異 MyISAM 只有表級鎖(table-level locking)，而 InnoDB 支持行級鎖(row-level locking)和表級鎖,默認(rèn)為行級鎖。

• 索引結(jié)構(gòu) 二者數(shù)據(jù)結(jié)構(gòu)存儲內(nèi)容不同:主索引的區(qū)別，InnoDB的數(shù)據(jù)文件本身就是索引文件。而MyISAM的索引和數(shù)據(jù)是分開的。

• 外鍵支持 MyISAM 不支持外鍵，而 InnoDB 支持外鍵。

• 容災(zāi)恢復(fù) 是否支持?jǐn)?shù)據(jù)庫異常崩潰后的安全恢復(fù)：MyISAM 不支持，而 InnoDB 支持。

4、mysql默認(rèn)的隔離級別

MySQL InnoDB 存儲引擎的默認(rèn)支持的隔離級別是 REPEATABLE-READ（可重復(fù)讀）

5、數(shù)據(jù)庫三范式

1NF：每個列不可再拆分

2NF：在第一范式的基礎(chǔ)上，非主鍵列完全依賴于主鍵，而不能是依賴于主鍵的一部分

3NF：在第二范式的基礎(chǔ)上，非主鍵列只依賴于主鍵，不依賴于其他非主鍵

另一個版本：

第一范式：列的原子性，即每一列（每一個屬性，字段）都不可分割。 　　舉例：銷售成本=成本的單價*銷售的數(shù)量，所以這里就不可以以銷售成本作為字段。 第二范式：非主屬性必須完全依賴于主屬性，不能存在只依賴于主屬性的一部分屬性。 　　主鍵是唯一的，用來確定每一行數(shù)據(jù)的。學(xué)生信息表由，學(xué)號，姓名，性別，年齡組成，這里不能以姓名作為主鍵，因為可能存在同名，不是唯一的，學(xué)號是唯一的，所以選擇作為主鍵。 　　“張三”的學(xué)號對應(yīng)了自己的姓名，年齡，性別，這里年齡性別，不能存儲別人的信息，不能只有姓名，年齡依賴于學(xué)號，必須所有的信息都依賴于學(xué)號。 第三范式：消除依賴的傳遞。 　　這里可以理解為消除冗余，這里以學(xué)生信息表（同上），院系表（主鍵系編號，屬性：系名字，系主任），如果把院系表的系編號，系名字，系主任都存儲到“張三”的信息中，這里就會發(fā)現(xiàn)數(shù)據(jù)冗余，可能許多學(xué)生的院系信息都是相同的， 我們只需要將系編號這個字段存儲到學(xué)生信息表即可。通過系編號即可查詢院系表對應(yīng)信息，第三范式消除了依賴的傳遞。

6、drop、delete 與 truncate 區(qū)別？

• drop(丟棄數(shù)據(jù)): drop table 表名 ，直接將表都刪除掉，在刪除表的時候使用。

• truncate (清空數(shù)據(jù)) : truncate table 表名 ，只刪除表中的數(shù)據(jù)，再插入數(shù)據(jù)的時候自增長 id 又從 1 開始，在清空表中數(shù)據(jù)的時候使用。

• delete（刪除數(shù)據(jù)） : delete from 表名 where 列名=值，刪除某一行的數(shù)據(jù)，如果不加 where 子句和truncate table 表名作用類似。

7、IN與EXISTS的區(qū)別

exists和in都用于兩個表的連接查詢中，最好遵循小表驅(qū)動大表的原則。

exists適合B表比A表數(shù)據(jù)大的情況，in適合A表比B表數(shù)據(jù)大的情況

（SELECT * FROM A WHERE id IN (SELECT id FROM B)）

二、索引

1、索引及索引底層數(shù)據(jù)結(jié)構(gòu)

索引是一種用于快速查詢和檢索數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)，其本質(zhì)可以看成是一種排序好的數(shù)據(jù)結(jié)構(gòu)。

1. Hash 哈希表是鍵值對的集合，通過鍵(key)即可快速取出對應(yīng)的值(value)，因此哈希表可以快速檢索數(shù)據(jù)（接近 O（1））。mysql不采用這種數(shù)據(jù)結(jié)構(gòu)，主要是因為 Hash 索引不支持順序和范圍查詢。

2. B 樹& B+樹

B 樹也稱 B-樹,全稱為 多路平衡查找樹 ，B+ 樹是 B 樹的一種變體。

• B 樹的所有節(jié)點(diǎn)既存放鍵(key) 也存放 數(shù)據(jù)(data)，而 B+樹只有葉子節(jié)點(diǎn)存放 key 和 data，其他內(nèi)節(jié)點(diǎn)只存放 key。

• B 樹的葉子節(jié)點(diǎn)都是獨(dú)立的;B+樹的葉子節(jié)點(diǎn)有一條引用鏈指向與它相鄰的葉子節(jié)點(diǎn)。

• B 樹的檢索的過程相當(dāng)于對范圍內(nèi)的每個節(jié)點(diǎn)的關(guān)鍵字做二分查找，可能還沒有到達(dá)葉子節(jié)點(diǎn)，檢索就結(jié)束了。而 B+樹的檢索效率就很穩(wěn)定了，任何查找都是從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)的過程，葉子節(jié)點(diǎn)的順序檢索很明顯。

2、二叉樹、B樹和B+樹有什么區(qū)別？

認(rèn)識各種樹

二叉樹，每個節(jié)點(diǎn)支持兩個分支的樹結(jié)構(gòu)，相比于單向鏈表，多了一個分支。 二叉查找樹，在二叉樹的基礎(chǔ)上增加了一個規(guī)則，左子樹的所有節(jié)點(diǎn)的值都小于它的根節(jié)點(diǎn)，右子樹的所有子節(jié)點(diǎn)都大于它的根節(jié)點(diǎn)。

二叉查找樹會出現(xiàn)斜樹問題，導(dǎo)致時間復(fù)雜度增加，因此又引入了一種平衡二叉樹，它具有二叉查找樹的所有特點(diǎn)，同時增加了一個規(guī)則：”它的左右兩個子樹的高度差的絕對值不超過 1“。平衡二叉樹會采用左旋、右旋的方式來實現(xiàn)平衡

而 B 樹是一種多路平衡查找樹，它滿足平衡二叉樹的規(guī)則，但是它可以有多個子樹，子樹的數(shù)量取決于關(guān)鍵字的數(shù)量，比如這個圖中根節(jié)點(diǎn)有兩個關(guān)鍵字 3 和 5，那么它能夠擁有的子路數(shù)量=關(guān)鍵字?jǐn)?shù)+1。 因此從這個特征來看，在存儲同樣數(shù)據(jù)量的情況下，平衡二叉樹的高度要大于 B 樹

B+樹，其實是在 B 樹的基礎(chǔ)上做的增強(qiáng)，最大的區(qū)別有兩個：

• B 樹的數(shù)據(jù)存儲在每個節(jié)點(diǎn)上，而 B+樹中的數(shù)據(jù)是存儲在葉子節(jié)點(diǎn)，并且通過鏈表的方式把葉子節(jié)點(diǎn)中的數(shù)據(jù)進(jìn)行連接。

• B+樹的子樹數(shù)量等于關(guān)鍵字?jǐn)?shù)

這個是 B 樹的存儲結(jié)構(gòu)，從 B 樹上可以看到每個節(jié)點(diǎn)會存儲數(shù)據(jù)。

這個是 B+樹，B+樹的所有數(shù)據(jù)是存儲在葉子節(jié)點(diǎn)，并且葉子節(jié)點(diǎn)的數(shù)據(jù)是用雙向鏈表關(guān)聯(lián)的。

B樹和B+樹對比

B 樹和 B+樹，一般都是應(yīng)用在文件系統(tǒng)和數(shù)據(jù)庫系統(tǒng)中，用來減少磁盤 IO 帶來的性能損耗。以 Mysql 中的 InnoDB 為例，當(dāng)我們通過 select 語句去查詢一條數(shù)據(jù)時，InnoDB 需要從磁盤上去讀取數(shù)據(jù)，這個過程會涉及到磁盤 IO 以及磁盤的隨機(jī) IO（如圖所示）我們知道磁盤 IO 的性能是特別低的，特別是隨機(jī)磁盤 IO。因為，磁盤 IO 的工作原理是，首先系統(tǒng)會把數(shù)據(jù)邏輯地址傳給磁盤，磁盤控制電路按照尋址邏輯把邏輯地址翻譯成物理地址，也就是確定要讀取的數(shù)據(jù)在哪個磁道，哪個扇區(qū)。為了讀取這個扇區(qū)的數(shù)據(jù)，需要把磁頭放在這個扇區(qū)的上面，為了實現(xiàn)這一個點(diǎn)，磁盤會不斷旋轉(zhuǎn)，把目標(biāo)扇區(qū)旋轉(zhuǎn)到磁頭下面，使得磁頭找到對應(yīng)的磁道，這里涉及到尋道事件以及旋轉(zhuǎn)時間。

很明顯，磁盤 IO 這個過程的性能開銷是非常大的，特別是查詢的數(shù)據(jù)量比較多的情況下。所以在 InnoDB 中，干脆對存儲在磁盤塊上的數(shù)據(jù)建立一個索引，然后把索引數(shù)據(jù)以及索引列對應(yīng)的磁盤地址，以 B+樹的方式來存儲

為什么用 B 樹或者 B+樹來做索引結(jié)構(gòu)？

為什么用 B 樹或者 B+樹來做索引結(jié)構(gòu)？原因是 AVL 樹的高度要比 B 樹的高度要高，而高度就意味著磁盤 IO 的數(shù)量。所以為了減少磁盤 IO 的次數(shù)，文件系統(tǒng)或者數(shù)據(jù)庫才會采用 B 樹或者 B+樹。

B+樹的非葉子結(jié)點(diǎn)只包含導(dǎo)航信息，不包含實際的值，所有的葉子結(jié)點(diǎn)和相連的節(jié)點(diǎn)使用鏈表相連，便于區(qū)間查找和遍歷。B+ 樹的優(yōu)點(diǎn)在于：

• IO次數(shù)更少：由于B+樹在內(nèi)部節(jié)點(diǎn)上不包含數(shù)據(jù)信息，因此在內(nèi)存頁中能夠存放更多的key。 數(shù)據(jù)存放的更加緊密，具有更好的空間局部性。因此訪問葉子節(jié)點(diǎn)上關(guān)聯(lián)的數(shù)據(jù)也具有更好的緩存命中率。

• 遍歷更加方便：B+樹的葉子結(jié)點(diǎn)都是相鏈的，因此對整棵樹的遍歷只需要一次線性遍歷葉子結(jié)點(diǎn)即可。而且由于數(shù)據(jù)順序排列并且相連，所以便于區(qū)間查找和搜索。而B樹則需要進(jìn)行每一層的遞歸遍歷。相鄰的元素可能在內(nèi)存中不相鄰，所以緩存命中性沒有B+樹好。

但是B樹也有優(yōu)點(diǎn)，其優(yōu)點(diǎn)在于，由于B樹的每一個節(jié)點(diǎn)都包含key和value，因此經(jīng)常訪問的元素可能離根節(jié)點(diǎn)更近，因此訪問也更迅速。

3、關(guān)于索引的底層實現(xiàn),為什么選擇 B+Tree 而不是紅黑樹？

我主要從兩個點(diǎn)來回答

1. 第一點(diǎn)，對于一個數(shù)據(jù)庫來說，存儲的數(shù)據(jù)量會比較多，導(dǎo)致索引也很大 因此需要將索引存儲 在磁盤，但是磁盤的 IO 操作又非常耗時，所以提高索引效率的關(guān)鍵在于減少磁盤 IO 的次 數(shù)。 舉個例子 對于 31 個節(jié)點(diǎn)的樹來說 ，一個 5 階 B+Tree 的高度是 3 一個紅黑樹的 最小高度是 5，樹的高度基本決定了磁盤的 IO 次數(shù) ，所以使用 B+Tree 性能要高 很多

2. 第二點(diǎn)，B+Tree 有個特點(diǎn)是相鄰的數(shù)據(jù)在物理上也是相鄰的，因為 B+Tree 的 node 的大小設(shè)為一個頁，而一個節(jié)點(diǎn)上存有多個相鄰的關(guān)鍵字和分支信息，每個節(jié)點(diǎn)只需要一次 IO就能完全載入，相當(dāng)于一次 IO 載入了多個相鄰的關(guān)鍵字和分支，而紅黑樹不具有這個特性，紅黑樹中大小相鄰的數(shù)據(jù)，在物理結(jié)構(gòu)上可能距離相差很大。由于程序的局部性原理，如果我們在索引中采用了預(yù)加載的技術(shù)，每次磁盤訪問的時候除了將訪問到的頁加載到磁盤，我們還可以基于局部性原理加載，幾頁相鄰的數(shù)據(jù)到內(nèi)存中，而這個加載是不需要消耗多余磁盤 IO 時間的。因此 基于局部性原理，以及 B+Tree 存儲結(jié)構(gòu)物理上的特性，所以 B+Tree 的索引性能比紅黑樹要好很多

簡潔答案：

• 相比與二叉樹，層級更少，搜索效率高

• 對于B樹，葉子節(jié)點(diǎn)和非葉子節(jié)點(diǎn)都會存儲數(shù)據(jù)，這樣就導(dǎo)致一頁中存儲的鍵值、指針跟著減少，同樣保存大量數(shù)據(jù)，就需要增加樹的高度，導(dǎo)致性能降低

• 相比hash索引，B+樹支持范圍匹配及排序操作

4、索引算法有哪些

• BTree算法

BTree是最常用的mysql數(shù)據(jù)庫索引算法，也是mysql默認(rèn)的算法。因為它不僅可以被用在=,>,>=,<,<=和between這些比較操作符上，而且還可以用于like操作符，只要它的查詢條件是一個不以通配符開頭的常量， 例如：

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

• Hash算法

Hash索引只能用于對等比較，例如=,<=>（相當(dāng)于=）操作符。由于是一次定位數(shù)據(jù)，不像BTree索引需要從根節(jié)點(diǎn)到枝節(jié)點(diǎn)，最后才能訪問到頁節(jié)點(diǎn)這樣多次IO訪問，所以檢索效率遠(yuǎn)高于BTree索引

5、InnoDB一棵B+樹的查找流程

1. 在B+樹中葉子節(jié)點(diǎn)存放數(shù)據(jù)，非葉子節(jié)點(diǎn)存放鍵值+指針。InnoDB存儲引擎的最小存儲單元是頁，頁可以用于存放數(shù)據(jù)也可以用于存放鍵值+指針

2. 頁內(nèi)的記錄是有序的，所以可以使用二分查找在頁內(nèi)到下一層的目標(biāo)頁面的指針。從根頁開始，首先通過非葉子節(jié)點(diǎn)的二分查找法，確定數(shù)據(jù)在下一層哪個頁之后，一層一層往下找，一直到葉子節(jié)點(diǎn)，進(jìn)而在葉子節(jié)點(diǎn)（數(shù)據(jù)頁）中查找到需要的數(shù)據(jù)；

6、高為2的B+樹可以存放多少行數(shù)據(jù)

首先，需要計算出非葉子節(jié)點(diǎn)能存放多少指針。B+ 樹能夠存放多少行數(shù)據(jù)，其實問的就是這棵 B+ 樹的非葉子節(jié)點(diǎn)中存放的數(shù)據(jù)量。

簡單按照一行記錄的數(shù)據(jù)大小為 1k 來算的話（實際上現(xiàn)在很多互聯(lián)網(wǎng)業(yè)務(wù)數(shù)據(jù)記錄大小通常就是 1K左右），一頁（16K）或者說一個葉子節(jié)點(diǎn)可以存放 16 行這樣的數(shù)據(jù)。那么 ，這顆B+ 樹 的非葉子節(jié)點(diǎn)（ 唯一的）能夠存儲多少數(shù)據(jù)呢？

非葉子節(jié)點(diǎn)里面存的是主鍵值 + 指針，為了方便分析，這里我們把一個主鍵值 + 一個指針稱為一個單元，

• 我們假設(shè)主鍵的類型是 bigint，長度為 8 字節(jié)，

• 而指針大小在 InnoDB 中設(shè)置為 6 字節(jié)

這樣一個單元，一共 14 字節(jié)。

這樣的話，一頁或者說一個非葉子節(jié)點(diǎn)能夠存放 16384 / 14=1170 個這樣的單元。

也就是說一個非葉子節(jié)點(diǎn)中能夠存放 1170 個指針，即對應(yīng) 1170 個葉子節(jié)點(diǎn)，所以對于這樣一棵高度為 2 的 B+ 樹，能存放 1170（一個非葉子節(jié)點(diǎn)中的指針數(shù)） * 16（一個葉子節(jié)點(diǎn)中的行數(shù)）= 18720 行數(shù)據(jù)。

7、高為3的B+樹可以存放多少行數(shù)據(jù)

分析完高度為 2 的 B+ 樹，同樣的道理，我們來看高度為 3 的：

根頁（page10）可以存放 1170 個指針，然后第二層的每個頁（page:11,12,13）也都分別可以存放1170個指針。

這樣一共可以存放 1170 * 1170 個指針，即對應(yīng)的有 1170 * 1170 個非葉子節(jié)點(diǎn)，

所以，高為3的B+樹一共可以存放 1170 * 1170 * 16 = 21902400 行記錄。

8、創(chuàng)建索引的原則

1. 較頻繁作為查詢條件的字段才去創(chuàng)建索引 ，更新頻繁字段不適合創(chuàng)建索引 。

2. 不要過度索引。索引需要額外的磁盤空間，并降低寫操作的性能。在修改表內(nèi)容的時候，索引會進(jìn)行更新甚至重構(gòu)，索引列越多，這個時間就會越長。

3. 若是不能有效區(qū)分?jǐn)?shù)據(jù)的列不適合做索引列(如性別，男女未知，最多也就三種，區(qū)分度實在太低)

4. 最左前綴匹配原則，組合索引非常重要的原則，mysql會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 andc > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。

5. 盡量的擴(kuò)展索引，不要新建索引。比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可。

6. 對于定義為text、image和bit的數(shù)據(jù)類型的列不要建立索引

9、索引使用場景

1. where

2. order by

3. join

10、索引類型

1. 主鍵索引(Primary Key)：主鍵不能為 null，不能重復(fù)。

2. 唯一索引(Unique Key) ：唯一索引也是一種約束。唯一索引的屬性列不能出現(xiàn)重復(fù)的數(shù)據(jù)，但是允許數(shù)據(jù)為 NULL，一張表允許創(chuàng)建多個唯一索引。 建立唯一索引的目的大部分時候都是為了該屬性列的數(shù)據(jù)的唯一性，而不是為了查詢效率。

3. 普通索引(Index) ：普通索引的唯一作用就是為了快速查詢數(shù)據(jù)，一張表允許創(chuàng)建多個普通索引，并允許數(shù)據(jù)重復(fù)和 NULL。

4. 前綴索引(Prefix) ：前綴索引只適用于字符串類型的數(shù)據(jù)。前綴索引是對文本的前幾個字符創(chuàng)建索引，相比普通索引建立的數(shù)據(jù)更小， 因為只取前幾個字符。

5. 全文索引(Full Text) ：全文索引主要是為了檢索大文本數(shù)據(jù)中的關(guān)鍵字的信息，是目前搜索引擎數(shù)據(jù)庫使用的一種技術(shù)。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

11、聚簇索引與非聚簇索引

• 聚簇索引（聚集索引）：即索引結(jié)構(gòu)和數(shù)據(jù)一起存放的索引，并不是一種單獨(dú)的索引類型。InnoDB 中的主鍵索引就屬于聚簇索引。

• 非聚簇索引：即索引結(jié)構(gòu)和數(shù)據(jù)分開存放的索引，并不是一種單獨(dú)的索引類型。二級索引(輔助索引)就屬于非聚簇索引。MySQL 的 MyISAM 引擎，不管主鍵還是非主鍵，使用的都是非聚簇索引

12、最左前綴匹配的內(nèi)部原理

索引的底層是一顆 B+ 樹，那么聯(lián)合索引當(dāng)然還是一顆 B+ 樹，只不過聯(lián)合索引的鍵值數(shù)量不是一個，而是多個。構(gòu)建一顆 B+ 樹只能根據(jù)一個值來構(gòu)建，因此數(shù)據(jù)庫依據(jù)聯(lián)合索引最左的字段來構(gòu)建 B+ 樹。因為聯(lián)合索引的B+Tree是按照第一個關(guān)鍵字進(jìn)行索引排列的。在 InnoDB 中聯(lián)合索引只有先確定了前一個（左側(cè)的值）后，才能確定下一個值。如果有范圍查詢的話，那么聯(lián)合索引中使用范圍查詢的字段后的索引在該條 SQL 中都不會起作用。

• 顧名思義，就是最左優(yōu)先，在創(chuàng)建多列索引時，要根據(jù)業(yè)務(wù)需求，where子句中使用最頻繁的一列放在最左邊。

• 最左前綴匹配原則，非常重要的原則，mysql會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4，如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。

• =和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優(yōu)化器會幫你優(yōu)化成索引可以識別的形式

13、索引失效的情況

• like 以%開頭索引無效，當(dāng) like 以%結(jié)尾，索引有效。

• or 語句前后沒有同時使用索引，當(dāng)且僅當(dāng) or 語句查詢條件的前后列均為索引時，索引 生效。

• 組合索引，使用的不是第一列索引時候，索引失效，即最左匹配規(guī)則。

• 數(shù)據(jù)類型出現(xiàn)隱式轉(zhuǎn)換，如 varchar 不加單引號的時候可能會自動轉(zhuǎn)換為 int 類型，這 個時候索引失效。

• 在索引列上使用 IS NULL 或者 IS NOT NULL 時候，索引可能失效，因為索引是不索引空值。（索引不一定失效。索引是否失效取決于表中數(shù)據(jù)分布情況，mysql會評估索引快，還是全表快，去選擇是否使用索引）

• 在索引字段上使用，NOT、 <>、！= 、時候是不會使用索引的，對于這樣的處理只會進(jìn) 行全表掃描。

• 對索引字段進(jìn)行計算操作，函數(shù)操作時不會使用索引。

• 當(dāng)全表掃描速度比索引速度快的時候不會使用索引。

14、索引條件下推

MySQL5.6新添加的特性，用于優(yōu)化數(shù)據(jù)查詢的。

5.6 之前通 過非主鍵索引查詢時，存儲引擎通過索引查詢數(shù)據(jù)，然后將結(jié)果返回給MySQL server層，在server層判斷是否符合條件，在以后的版本可以使用索引下推，當(dāng)存在索引列作為判斷條件時，Mysql server 將這一部分判斷條件傳遞給存儲引擎，然后存儲引擎會篩選出符合傳遞傳遞條件的索引項，即在存儲引擎層根據(jù)索引條件過濾掉不符合條件的索引項，然后回表查詢得到結(jié)果，將結(jié)果再返回給Mysql server，有了索引下推的優(yōu)化，在滿足一定條件下，存儲 引擎層會在回表查詢之前對數(shù)據(jù)進(jìn)行過濾，可以減少存儲引擎回表查詢的次數(shù)。

回表查詢：先根據(jù)二級索引（非聚集索引）在樹中查詢主鍵，然后在去聚集索引樹中根據(jù)主鍵查詢對應(yīng)的行記錄。

15、什么是自適應(yīng)哈希索引？

自適應(yīng)哈希索引是Innodb引擎的一個特殊功能，當(dāng)它注意到某些索引值被使用的非常頻繁時，會在內(nèi)存中基于B-Tree所有之上再創(chuàng)建一個哈希索引，這就讓B-Tree索引也具有哈希索引的一些優(yōu)點(diǎn)，比如快速哈希查找。這是一個完全自動的內(nèi)部行為，用戶?法控制或配置

使用命令

SHOW ENGINE INNODB STATUS ;

查看INSERT BUFFER AND ADAPTIVE HASH INDEX

16、為什么官方建議使用自增長主鍵作為索引？（說一下自增主鍵和字符串類型主鍵的區(qū)別和影響）

1. 自增主鍵能夠維持底層數(shù)據(jù)順序?qū)?

2. 讀取可以由b+樹的二分查找定位

3. ?持范圍查找，范圍數(shù)據(jù)自帶順序

17、MySQL explain執(zhí)行計劃詳解

1. 作用：explain顯示了mysql如何使用索引來處理select語句以及連接表

2. explain執(zhí)行計劃包含的信息：

• id（table是id對應(yīng)的表）：表的讀取順序，id相同順序執(zhí)行，id不同，數(shù)字越大優(yōu)先級越高

• type：查詢類型。性能由最好到最差依次是：system > const > eq_ref > ref > range > index > ALL

  • system、const：MySQL對查詢的某部分進(jìn)行優(yōu)化并把其轉(zhuǎn)化成一個常量（可以通過show warnings命令查看結(jié)果）。system是const的一個特例，表示表里只有一條元組匹配時為system

  • eq_ref：主鍵或唯一鍵索引被連接使用，最多只會返回一條符合條件的記錄。簡單的select查詢不會出現(xiàn)這種type。

  • ref：相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一索引的部分前綴，索引和某個值比較，會找到多個符合條件的行。

  • range：通常出現(xiàn)在范圍查詢中，比如in、between、大于、小于等。使用索引來檢索給定范圍的行。

  • index：掃描全索引拿到結(jié)果，一般是掃描某個二級索引，二級索引一般比較少，所以通常比ALL快一

  • ALL：全表掃描，掃描聚簇索引的所有葉子節(jié)點(diǎn)。

• key：實際用到的索引鍵

• rows：索引查詢時，大致估算出查詢到所需記錄讀取的行數(shù)，rows越小越好

• Extra：額外信息

  ??using index : 覆蓋索引，避免回表

  ??using where: 全表掃描 + where 條件過濾數(shù)據(jù)

  ??using filesort: 無法使用索引排序，只能使用排序算法進(jìn)行排序，會產(chǎn)生額外的消耗

  ??using temporary: 使用了臨時表

  ??no matching row in const table: 在唯一性索引上無法匹配到數(shù)據(jù)

  ??using join buffer: 連接字段上沒有索引，使用了連接緩存

三、事務(wù)

1、事務(wù)的四大特性(ACID)介紹一下

1. 原子性： 事務(wù)是最小的執(zhí)行單位，不允許分割。事務(wù)的原子性確保動作要么全部完成，要么完全不起作用；

2. 一致性： 執(zhí)行事務(wù)前后，數(shù)據(jù)保持一致，多個事務(wù)對同一個數(shù)據(jù)讀取的結(jié)果是相同的；

3. 隔離性： 并發(fā)訪問數(shù)據(jù)庫時，一個用戶的事務(wù)不被其他事務(wù)所干擾，各并發(fā)事務(wù)之間數(shù)據(jù)庫是獨(dú)立的；

4. 持久性： 一個事務(wù)被提交之后。它對數(shù)據(jù)庫中數(shù)據(jù)的改變是持久的，即使數(shù)據(jù)庫發(fā)生故障也不應(yīng)該對其有任何影響。

2、MySQL的事務(wù)ACID是如何實現(xiàn)的？

以MySQL 的InnoDB （InnoDB 是 MySQL 的一個存儲引擎）為例。InnoDB 是通過 日志和鎖 來保證的事務(wù)的 ACID特性，具體如下：

1. 通過數(shù)據(jù)庫鎖機(jī)制，保障事務(wù)的隔離性；

2. 通過 Redo Log（重做日志）來，保障事務(wù)的持久性；

3. 通過 Undo Log （撤銷日志）來，保障事務(wù)的原子性；

4. 通過 Undo Log （撤銷日志）來，保障事務(wù)的一致性；

3、什么是臟讀？幻讀？不可重復(fù)讀？

• 臟讀(Drity Read)：某個事務(wù)已更新一份數(shù)據(jù)，另一個事務(wù)在此時讀取了同一份數(shù)據(jù)，由于某些原因，前一個RollBack了操作，則后一個事務(wù)所讀取的數(shù)據(jù)就會是不正確的

• 不可重復(fù)讀(Non-repeatable read):在一個事務(wù)的兩次查詢之中數(shù)據(jù)不一致，這可能是兩次查詢過程中間插入了一個事務(wù)更新的原有的數(shù)據(jù)。

• 幻讀(Phantom Read):在一個事務(wù)的兩次查詢中數(shù)據(jù)筆數(shù)不一致，例如有一個事務(wù)查詢了幾列(Row)數(shù)據(jù)，而另一個事務(wù)卻在此時插入了新的幾列數(shù)據(jù)，先前的事務(wù)在接下來的查詢中，就會發(fā)現(xiàn)有幾列數(shù)據(jù)是它先前所沒有的。

4、什么是事務(wù)的隔離級別？MySQL的默認(rèn)隔離級別是什么？

為了達(dá)到事務(wù)的四大特性，數(shù)據(jù)庫定義了4種不同的事務(wù)隔離級別，由低到高依次為Read uncommitted、Read committed、Repeatable read、Serializable，這四個級別可以逐個解決臟讀、不可重復(fù)讀、幻讀這幾類問題。

• READ-UNCOMMITTED(讀取未提交)： 最低的隔離級別，允許讀取尚未提交的數(shù)據(jù)變更，可能會導(dǎo)致臟讀、幻讀或不可重復(fù)讀。

• READ-COMMITTED(讀取已提交)： 允許讀取并發(fā)事務(wù)已經(jīng)提交的數(shù)據(jù)，可以阻止臟讀，但是幻讀或不可重復(fù)讀仍有可能發(fā)生。

• REPEATABLE-READ(可重復(fù)讀)： 對同一字段的多次讀取結(jié)果都是一致的，除非數(shù)據(jù)是被本身事務(wù)自己所修改，可以阻止臟讀和不可重復(fù)讀，但幻讀仍有可能發(fā)生。

• SERIALIZABLE(可串行化)： 最高的隔離級別，完全服從ACID的隔離級別。所有的事務(wù)依次逐個執(zhí)行，這樣事務(wù)之間就完全不可能產(chǎn)生干擾，也就是說，該級別可以防止臟讀、不可重復(fù)讀以及幻讀

Mysql 默認(rèn)采用的 REPEATABLE_READ隔離級別 Oracle默認(rèn)采用的 READ_COMMITTED隔離級別

5、事務(wù)的實現(xiàn)原理

Innodb通過Buffer Pool，LogBuffer，Redo Log，Undo Log來實現(xiàn)事務(wù)，以?個update語句為例：

1. Innodb在收到?個update語句后，會先根據(jù)條件找到數(shù)據(jù)所在的?，并將該?緩存在Buffer Pool中

2. 執(zhí)?update語句，修改Buffer Pool中的數(shù)據(jù)，也就是內(nèi)存中的數(shù)據(jù)

3. 針對update語句?成?個RedoLog對象，并存?LogBuffer中

4. 針對update語句?成undolog?志，?于事務(wù)回滾

5. 如果事務(wù)提交，那么則把RedoLog對象進(jìn)?持久化，后續(xù)還有其他機(jī)制將Buffer Pool中所修改的數(shù)據(jù)?持久化到磁盤中

6. 如果事務(wù)回滾，則利?undolog?志進(jìn)?回滾

6、MySQL事務(wù)日志介紹下？

innodb 事務(wù)日志包括 redo log 和 undo log。

undo log 指事務(wù)開始之前，在操作任何數(shù)據(jù)之前，首先將需操作的數(shù)據(jù)備份到一個地方。

redo log 指事務(wù)中操作的任何數(shù)據(jù)，將最新的數(shù)據(jù)備份到一個地方。

（1）redo log

redo log 不是隨著事務(wù)的提交才寫入的，而是在事務(wù)的執(zhí)行過程中，便開始寫入 redo 中。具體的落盤策略可以進(jìn)行配置 。防止在發(fā)生故障的時間點(diǎn)，尚有臟頁未寫入磁盤，在重啟 MySQL 服務(wù)的時候，根據(jù) redo log 進(jìn)行重做，從而達(dá)到事務(wù)的未入磁盤數(shù)據(jù)進(jìn)行持久化這一特性。RedoLog 是為了實現(xiàn)事務(wù)的持久性而出現(xiàn)的產(chǎn)物。

（2）undo log

undo log 用來回滾行記錄到某個版本。事務(wù)未提交之前，Undo 保存了未提交之前的版本數(shù)據(jù)，Undo 中的數(shù)據(jù)可作為數(shù)據(jù)舊版本快照供其他并發(fā)事務(wù)進(jìn)行快照讀。是為了實現(xiàn)事務(wù)的原子性而出現(xiàn)的產(chǎn)物,在 MySQL innodb 存儲引擎中用來實現(xiàn)多版本并發(fā)控制。

7、什么是MySQL的 binlog？

MySQL的 binlog 是記錄所有數(shù)據(jù)庫表結(jié)構(gòu)變更（例如 CREATE、ALTER TABLE）以及表數(shù)據(jù)修改（INSERT、UPDATE、DELETE）的二進(jìn)制日志。binlog 不會記錄 SELECT 和 SHOW 這類操作，因為這類操作對數(shù)據(jù)本身并沒有修改，但你可以通過查詢通用日志來查看 MySQL 執(zhí)行過的所有語句。

MySQL binlog 以事件形式記錄，還包含語句所執(zhí)行的消耗的時間，MySQL 的二進(jìn)制日志是事務(wù)安全型的。binlog 的主要目的是復(fù)制和恢復(fù)。

binlog 有三種格式，各有優(yōu)缺點(diǎn)：

• statement： 基于 SQL 語句的模式，某些語句和函數(shù)如 UUID, LOAD DATA INFILE 等在復(fù)制過程可能導(dǎo)致數(shù)據(jù)不一致甚至出錯。

• row： 基于行的模式，記錄的是行的變化，很安全。但是 binlog 會比其他兩種模式大很多，在一些大表中清除大量數(shù)據(jù)時在 binlog 中會生成很多條語句，可能導(dǎo)致從庫延遲變大。

• mixed： 混合模式，根據(jù)語句來選用是 statement 還是 row 模式。

四、鎖

1、Mysql 中鎖機(jī)制

事務(wù)的隔離性（隔離級別）是由鎖來保證的。

并發(fā)訪問數(shù)據(jù)的情況分為：

讀-讀

即并發(fā)事務(wù)相繼讀取相同的記錄，因為沒涉及到數(shù)據(jù)的更改，所以不會有并發(fā)安全問題，允許這種情況發(fā)生。

寫-寫

即并發(fā)事務(wù)對相同記錄進(jìn)行修改，會出現(xiàn)臟寫問題，因為任何一種隔離級別都不允許發(fā)生臟寫，所以多個未提交的事務(wù)對同一個記錄修改時需要加鎖，保證它們是順序執(zhí)行的。

鎖內(nèi)存中的結(jié)構(gòu)，當(dāng)事務(wù)想對某條數(shù)據(jù)進(jìn)行更改時，首先會查看該記錄有沒有與之關(guān)聯(lián)的鎖結(jié)構(gòu)，有的話則等待它的事務(wù)被提交，鎖被釋放；反之沒有鎖則生成鎖結(jié)構(gòu)與該記錄繼續(xù)關(guān)聯(lián)。

寫-讀或讀-寫

即一個事務(wù)進(jìn)行讀取操作，另一個進(jìn)行改動操作。這種情況下可能發(fā)生臟讀 、不可重復(fù)讀 、幻讀的問題?？梢允褂脙煞N方式解決(都離不開鎖)：

• 讀寫都采用加鎖的方式，讀寫也需要排隊執(zhí)行，性能較差

• 寫操作加鎖，讀操作利用MVVC多版本并發(fā)控制，讀取歷史記錄，性能更高

涉及到寫操作時，必須有鎖

https://blog.csdn.net/weixin_45902285/article/details/126186759

（1）從數(shù)據(jù)操作的類型分類

• 共享鎖（S鎖）

也稱讀鎖，允許事務(wù)對某些數(shù)據(jù)進(jìn)行讀取。多個事務(wù)的讀操作不會相互影響，也不會相互阻塞。

• 排他鎖（X鎖）

也稱寫鎖，允許事務(wù)對某些數(shù)據(jù)進(jìn)行刪除或更新。如果當(dāng)前操作還沒完成，其他事務(wù)的S和X鎖是會被阻塞的，確保在多個事務(wù)中，對同一資源，只有一個事務(wù)能寫入，并防止其他用戶讀取正在寫入的資源。

（2）從鎖的粒度分類

從鎖的粒度劃分可分為：

• 全局鎖

全局鎖就是對整個數(shù)據(jù)庫實例加鎖，主要是做全庫邏輯備份

• 表鎖

• 表級別的S鎖和X鎖

• 意向鎖: 意向鎖的作用就是加快表鎖的檢查過程。

意向共享鎖（IS）：事務(wù)有意向?qū)Ρ碇械哪承┬屑庸蚕礞i（S鎖），會自動加上意向共享鎖 意向排他鎖（IX）：事務(wù)有意向?qū)Ρ碇械哪承┬屑优潘i（X鎖），會自動加上意向排它鎖

• 自增鎖: 表中有自增列時，插入記錄會使用到自增鎖

• 元數(shù)據(jù)鎖: 在對某個表執(zhí)行一些諸如ALTER TABLE 、DROP TABLE這類的DDL語句時，其他事務(wù)對這個表并發(fā)執(zhí)行諸如SELECT、INSERT、DELETE、UPDATE的語句會發(fā)生阻塞

• 行鎖

記錄鎖(Record Lockss)

記錄鎖就是行級別的X鎖和S鎖，僅僅鎖住一行記錄。當(dāng)我們針對主鍵或者唯一索引加鎖的時候，

Mysql 默認(rèn)會對查詢的這一行數(shù)據(jù)加行鎖，避免其他事務(wù)對這一行數(shù)據(jù)進(jìn)行修改。例如：select * from user

where id='1' for UPDATE;

間隙鎖（Gap Locks）

就是某個事務(wù)對索引列加鎖的時候，默認(rèn)鎖定對應(yīng)索引的左右開區(qū)間范圍。間隙鎖是針對事務(wù)隔離級別為可重復(fù)讀或以上級別而言的。例如：

select * from user where id BETWEEN '5' and '8' for UPDATE;

圖中id值為8的記錄加了gap鎖，意味著不允許別的事務(wù)在(5,8)之間插入新記錄。比如，有另外一個事務(wù)再想插入一條id值為6的新記錄，它定位到該條新記錄的下一條記錄的id值為8，而這條記錄上又有一個gap鎖，所以就會阻塞插入操作，直到擁有這個gap鎖的事務(wù)提交了之后，id列的值在區(qū)間(5, 8)中的新記錄才可以被插入。

臨鍵鎖（Next-Key Locks）

記錄鎖 + 間隙鎖，它指的是加在某條記錄以及這條記錄前面間隙上的鎖。例如：我們使用非唯一索引列進(jìn)行查詢的時候，默認(rèn)會加一個臨鍵鎖，鎖定一個左開右閉區(qū)間的范圍，select * from user where age= '11' for UPDATE,鎖定區(qū)間（10,11];

插入意向鎖（Insert Intention Locks）

表明想在某個間隙中插入一條數(shù)據(jù)

（3）從鎖的態(tài)度分類

樂觀鎖

• 樂觀鎖：總是假設(shè)最好的情況，每次去拿數(shù)據(jù)的時候都認(rèn)為別人不會修改，所以不會上鎖，但是在更新的時候會判斷一下在此期間別人有沒有去更新這個數(shù)據(jù)，可以使用版本號機(jī)制和CAS算法實現(xiàn)

• 簡單實現(xiàn)：

• CAS的實現(xiàn)原理是比較并交換，簡單點(diǎn)來說就是，更新數(shù)據(jù)之前，會先檢查數(shù)據(jù)是否有被修改過：如果沒有修改，則直接更新；如果有被修改過，則重試；

• 版本號機(jī)制：在表中加個version或updatetime字段，在每次更新操作時對此一下該字段，如果一致則更新數(shù)據(jù)，數(shù)據(jù)不等則放棄本次修改，根據(jù)實際業(yè)務(wù)需求做相應(yīng)的處理。

• 樂觀鎖的適用場景：讀多寫少

悲觀鎖

• 悲觀鎖：共享資源每次只給一個線程使用，其它線程阻塞，用完后再把資源轉(zhuǎn)讓給其它線程。比如行鎖，表鎖等，讀鎖，寫鎖等，都是在做操作之前先上鎖。實現(xiàn)：Java中synchronized和ReentrantLock

• 悲觀鎖的適用場景：寫多讀少

2、隔離級別與鎖的關(guān)系

數(shù)據(jù)庫事務(wù)有不同的隔離級別，不同的隔離級別對鎖的使用是不同的，鎖的應(yīng)用最終導(dǎo)致不同事務(wù)的隔離級別。

1. Read Uncommitted：讀或?qū)懲瓿蓵r釋放鎖，然后提交事務(wù)，會造成臟讀。

2. Read Committed：大多數(shù)數(shù)據(jù)庫默認(rèn)的隔離級別，將釋放鎖的位置調(diào)整到事務(wù)提交后。會造成不可重復(fù)讀。（在同一個事務(wù)中如果兩次讀取相同的數(shù)據(jù)時，最后的結(jié)果卻不一致）

3. Repeatable-Read：mysql數(shù)據(jù)庫所默認(rèn)的級別，使用MVCC并發(fā)版本控制來實現(xiàn)讀寫一致性。

4. serializable：表鎖

*注意：幻讀和不可重復(fù)讀的區(qū)別：

幻讀是針對的一批數(shù)據(jù)記錄整體

不可重復(fù)讀針對的是同一數(shù)據(jù)項的記錄

3、Mysql如何解決幻讀問題

MySQL默認(rèn)采用的隔離級別是可重復(fù)讀，在這種隔離級別下不同的讀模式，針對幻讀問題采用了不同解決方案：

針對快照讀（普通 select 語句），是通過 MVCC 方式解決了幻讀。

針對當(dāng)前讀（select ... for update 等語句），是通過 next-key lock（記錄鎖+間隙鎖）方式解決了幻讀。

但是，強(qiáng)調(diào)一點(diǎn)的是，MySQL在可重復(fù)讀級別下，并沒有完完全全的解決幻讀問題，特別是在一個事務(wù)的快照讀和當(dāng)前讀穿插使用的場景下，還是會出現(xiàn)幻讀的情況，比如下圖所示。

4、MVCC 多版本并發(fā)控制

MVCC， 即多版本并發(fā)控制。MVCC 的實現(xiàn)，是通過保存數(shù)據(jù)在某個時間點(diǎn)的快照來實現(xiàn)的。根據(jù)事務(wù)開始的時間不同，每個事務(wù)對同一張表，同一時刻看到的數(shù)據(jù)可能是不一樣的。

• undo Log

當(dāng)我們讀取一條被其他事務(wù)變更的數(shù)據(jù)時,會在undo Log中產(chǎn)生一條變更前的日志.這個日志可以專門用于回滾。

我們大概來看一下這個日志的大概結(jié)構(gòu):

前面三個字段屬于變更前的，另外：

trx_id : 代表是哪個事務(wù)編號修改的。

roll_pointer : 相當(dāng)于一個鏈表，往下查找就是上一次更改前的。當(dāng)一條數(shù)據(jù)被更改了多次之后，由該字段構(gòu)建成一個鏈表俗稱版本鏈。

• Read View 讀視圖

基于當(dāng)前活躍事務(wù)列表構(gòu)成的ReadView，當(dāng)某個事務(wù)創(chuàng)建ReadView時，會將當(dāng)前活躍的事務(wù)也加入其中。

readview中四個比較重要的概念：

m_ids：表示在生成readview時，當(dāng)前系統(tǒng)中活躍的讀寫事務(wù)id列表；

min_trx_id：表示在生成readview時，當(dāng)前系統(tǒng)中活躍的讀寫事務(wù)中最小的事務(wù)id，也就是m_ids中最小的值；

max_trx_id：表示生成readview時，系統(tǒng)中應(yīng)該分配給下一個事務(wù)的id值；

creator_trx_id：表示生成該readview的事務(wù)的事務(wù)id；

有了readview，在訪問某條記錄時，按照以下步驟判斷記錄的某個版本是否可見:

基于可重復(fù)讀

ReadView(簡稱RV)一旦創(chuàng)建是不可變的，即便其中某個線程事務(wù)提交了，也不會影響當(dāng)前線程創(chuàng)建的ReadView，你可以理解為一個副本快照。

總的來說判斷就三個條件:

1. undo log的數(shù)據(jù)中包含的trx_id是介于min_trx_id和max_trx_id之間

   1. 如果小于min_trx_id說明創(chuàng)建RV 之前 的時候這個trx_id就已經(jīng)事務(wù)提交了,不活躍了，說明可以讀。

   2. 如果大于max_trx_id說明這個版本是在創(chuàng)建RV 之后 產(chǎn)生的，不可讀。因為創(chuàng)建RV時你這個版本還不存在

   3. 如果是在這之間的再看步驟2

2. 查看trx_id是否包含m_id之中：

   1. 包含說明創(chuàng)建RV的時候，還是活躍（沒提交）事務(wù)。那么是不可見的，臟讀；繼續(xù)看步驟3

   2. 不包含說明創(chuàng)建RV之前這個事務(wù)已經(jīng)被提交了，那么是可見的。

3. 到了這里說明這條數(shù)據(jù)的變更版本在RV之內(nèi)，則要查看creator_trx_id與trx_id是否一致:

   1. 一致說明就是當(dāng)前事務(wù)創(chuàng)建的；允許使用；

   2. 否則說明是當(dāng)前RV的其他事務(wù)操作的不能使用;

Read Committed：某個事務(wù)對某個數(shù)據(jù)每進(jìn)行一次快照讀，都會新生成一個新的ReadView。會造成不可重復(fù)讀。

Repeatable-Read：當(dāng)某個事務(wù)對某條記錄進(jìn)行快照讀(即不加鎖的select)時，會創(chuàng)建一個ReadView，之后再進(jìn)行快照讀時，還是繼續(xù)使用同一個ReadView

五、架構(gòu)

1、為什么要分庫分表

在業(yè)務(wù)場景中，mysql的單表數(shù)據(jù)量出現(xiàn)在千萬級左右查詢數(shù)據(jù)就會出現(xiàn)瓶頸。在各種優(yōu)化后，可以考慮對數(shù)據(jù)庫進(jìn)行拆分。

（1）水平拆分：

就是把一張表的數(shù)據(jù)拆成兩張表

（2）垂直拆分：

將表中的某些字段，單獨(dú)拆分出來，并通過某個字段和原表建立聯(lián)系分庫分表常用思路

2、分庫分表常用思路

（1）hash取模方案

hash的方案就是對指定的路由key（通常為主鍵id）對分表總數(shù)進(jìn)行取模。

優(yōu)點(diǎn)：某個表不會出現(xiàn)熱點(diǎn)問題（某個表被頻繁訪問，其他表訪問較少的情況）

缺點(diǎn)：對于以后要擴(kuò)容會比較麻煩，增加表的時候要對所有數(shù)據(jù)重新hash

（2）range范圍方案

簡單來說就是1-1000萬的數(shù)據(jù)放在1數(shù)據(jù)庫并以此類推。

優(yōu)點(diǎn)：有利于將來擴(kuò)容，有新數(shù)據(jù)就直接加入就好了

缺點(diǎn)：缺點(diǎn)也很明顯，有可能出現(xiàn)熱點(diǎn)問題。比如剛加進(jìn)來的數(shù)據(jù)經(jīng)常使用之類的問題

（3）兩者結(jié)合的方案

一個Group01可以存放4000w個數(shù)據(jù)，Group01有三個DB，共10table。那么可以通過id范圍確定在哪

個group，然后id%10哪個db。再根據(jù)范圍確定哪個table。簡單來說就是整體有序，局部用hash打

亂，當(dāng)擴(kuò)容的時候只要加group就好了。

3、分庫分表帶來的問題有哪些？

（1）分布式事務(wù)問題

使用分布式事務(wù)中間件解決，具體是通過最終一致性還是強(qiáng)一致性分布式事務(wù)，看業(yè)務(wù)需求。

（2）跨節(jié)點(diǎn)關(guān)聯(lián)查詢 Join 問題

切分之前，我們可以通過Join來完成。而切分之后，數(shù)據(jù)可能分布在不同的節(jié)點(diǎn)上，此時Join帶來的問題就比較麻煩了，考慮到性能，盡量避免使用Join查詢。解決這個問題的一些方法：

• 全局表

全局表，也可看做是 "數(shù)據(jù)字典表"，就是系統(tǒng)中所有模塊都可能依賴的一些表，為了避免跨庫Join查詢，可以將這類表在每個數(shù)據(jù)庫中都保存一份。這些數(shù)據(jù)通常 很少會進(jìn)行修改，所以也不擔(dān)心一致性的問題。

• 字段冗余

利用空間換時間，為了性能而避免join查詢。例：訂單表保存userId時候,也將userName冗余保存一份，這樣查詢訂單詳情時就不需要再去查詢"買家user表"了。

• 數(shù)據(jù)組裝

在系統(tǒng)層面，分兩次查詢。第一次查詢的結(jié)果集中找出關(guān)聯(lián)數(shù)據(jù)id，然后根據(jù)id發(fā)起第二次請求得到關(guān)聯(lián)數(shù)據(jù)。最后將獲得到的數(shù)據(jù)進(jìn)行字段拼裝。

（3）跨節(jié)點(diǎn)分頁、排序、函數(shù)問題

跨節(jié)點(diǎn)多庫進(jìn)行查詢時，會出現(xiàn)Limit分頁、Order by排序等問題。分頁需要按照指定字段進(jìn)行排序，當(dāng)排序字段就是分片字段時，通過分片規(guī)則就比較容易定位到指定的分片；當(dāng)排序字段非分片字段時，就變得比較復(fù)雜了。需要先在不同的分片節(jié)點(diǎn)中將數(shù)據(jù)進(jìn)行排序并返回，然后將不同分片返回的結(jié)果集進(jìn)行匯總和再次排序，最終返回給用戶。

（4）全局主鍵避重問題

如果都用主鍵自增肯定不合理，如果用UUID那么無法做到根據(jù)主鍵排序，所以我們可以考慮通過雪花ID來作為數(shù)據(jù)庫的主鍵

4、超大分頁怎么處理?

（1）用id優(yōu)化

先找到上次分頁的最大ID,然后利用id上的索引來查詢,類似于

select * from user where id>1000000 limit 100

這樣的效率非?？?因為主鍵上是有索引的,但是這樣有個缺點(diǎn),就是ID必須是連續(xù)的,并且查詢不能有where語句,因為where語句會造成過濾數(shù)據(jù)。

（2）用覆蓋索引優(yōu)化

Mysql的查詢完全命中索引的時候,稱為覆蓋索引,是非常快的,因為查詢只需要在索引上進(jìn)行查找,之后可以直接返回,而不用再回表拿數(shù)據(jù).因此我們可以先查出索引的ID,然后根據(jù)Id拿數(shù)據(jù)

select * from table where id in (select id from table where age > 20 limit 1000000,10)

（3）在業(yè)務(wù)允許的情況下限制頁數(shù)

建議跟業(yè)務(wù)討論，有沒有必要查這么靠后的分頁啦。因為絕大多數(shù)用戶都不會往后翻太多頁

5、order by 調(diào)優(yōu)10倍，思路是啥？

（1）減少select 后?的查詢的字段。 禁?使用select *

（2）聯(lián)合索引優(yōu)化

如果數(shù)據(jù)本身是有序的，那就不需要排序，而索引數(shù)據(jù)本身是有序的，所以，我們可以通過建立聯(lián)

合索引，跳過排序步驟。

（3）參數(shù)優(yōu)化

通過調(diào)整參數(shù)，也可以優(yōu)化 order by 的執(zhí)行。

1. 調(diào)整 sort_buffer_size 參數(shù)的值。如果 sort_buffer 值太小而數(shù)據(jù)量大的話，MySQL 會采用磁盤臨時文件輔助排序。MySQL 服務(wù)器配置高的情況下，可以將參數(shù)調(diào)大些。

2. 調(diào)整 max_length_for_sort_data 的值，值太小的話 MySQL 會采用 rowid 排序，會多一次回表操作導(dǎo)致查詢性能降低。同樣可以適當(dāng)調(diào)大些。

6、聊聊：什么是雙路排序和單路排序

1. 單路排序：一次取出所有字段進(jìn)行排序，內(nèi)存不夠用的時候會使用磁盤

2. 雙路排序：取出排序字段進(jìn)行排序，排序完成后再次回表查詢所需要的其他字段

7、什么是MySQL 的 MRR 優(yōu)化？

MRR 是 Multi-Range Read Optimization，通過把隨機(jī)磁盤讀轉(zhuǎn)化為順序磁盤讀，提高索引查詢的性能。

在不使用 MRR 時，優(yōu)化器需要根據(jù)二級索引返回的記錄來進(jìn)行“回表”，這個過程一般會有較多的隨機(jī)IO,使用 MRR 時，SQL 語句的執(zhí)行過程是這樣的：

（1）先把通過二級索引取出的值緩存在緩沖區(qū)中

這個緩沖區(qū)叫做 read_rnd_buffer ，簡稱 rowid buffer。

（2）再把這部分緩沖區(qū)中的數(shù)據(jù)按照ID進(jìn)行排序。

如果二級索引掃描到索引文件的末尾或者緩沖區(qū)已滿，則使用快速排序?qū)彌_區(qū)中的內(nèi)容按照主鍵進(jìn)行排序；

（3）然后再依次根據(jù)ID去聚集索引中獲取整個數(shù)據(jù)行。

線程調(diào)用 MRR 接口取 rowId，然后根據(jù)rowId 取行數(shù)據(jù)；當(dāng)根據(jù)緩沖區(qū)中的 rowId 取完數(shù)據(jù)，則繼續(xù)調(diào)用過程 2) 3)，直至掃描結(jié)束；

MRR 的本質(zhì)：

是在回表的過程中， 把分散的無序回表， 變成排序后有序的回表， 從而實現(xiàn) 隨機(jī)磁盤讀 盡可能變成順序讀。

MRR 使用限制

MRR 適用于range、ref、eq_ref的查詢

8、一條MySQL更新語句的執(zhí)行過程是什么樣的？

（1）連接驗證及解析

客戶端與MySQL Server建立連接，發(fā)送語句給MySQL Server，接收到后如果是查詢語句會先去查詢緩存中看，有的話就直接返回了，（新版本的MySQL已經(jīng)廢除了查詢緩存，命中率太低了），如果是緩存沒有或者是非查詢語句，會創(chuàng)建一個解析樹，然后進(jìn)行優(yōu)化，（解析器知道語句是要執(zhí)行什么，會評估使用各種索引的代價，然后去使用索引，以及調(diào)節(jié)表的連接順序）然后調(diào)用innodb引擎的接口來執(zhí)行語句。

（2）寫undo log

innodb 引擎首先開啟事務(wù)，獲得一個事務(wù)ID(是一直遞增的)，根據(jù)執(zhí)行的語句生成一個反向的語句，(如果是INSERT會生成一條DELETE語句，如果UPDATE語句就會生成一個UPDATE成舊數(shù)據(jù)的語句)，用于提交失敗后回滾，將這條反向語句寫入undo log，得到回滾指針，并且更新這個數(shù)據(jù)行的回滾指針和事務(wù)id。（事務(wù)提交后，Undo log并不能立馬被刪除，而是放入待清理的鏈表，由purge 線程判斷是否有其他事務(wù)在使用undo 段中表的上一個事務(wù)之前的版本信息，決定是否可以清理undo log的日志空間，簡單的說就是看之前的事務(wù)是否提交成功，這個事務(wù)及之前的事務(wù)都提交成功了，這部分undo log才能刪除。）

（3）從索引中查找數(shù)據(jù)

根據(jù)索引去B+樹中找到這一行數(shù)據(jù)（如果是普通索引，查到不符合條件的索引，會把所有數(shù)據(jù)查找出來，唯一性索引查到第一個數(shù)據(jù)就可以了）

（4）更新數(shù)據(jù)

看圖。。。

（5）寫redo log（prepare狀態(tài)）

將對數(shù)據(jù)頁的更改寫入到redo log，此時redo log中這條事務(wù)的狀態(tài)為prepare狀態(tài)。

（6）寫bin log（同時將redo log設(shè)置為commit狀態(tài)）

通知MySQL server已經(jīng)更新操作寫入到redo log 了，隨時可以提交，將執(zhí)行的SQL寫入到bin log日志，將redo log 中這條事務(wù)的狀態(tài)改成commit狀態(tài)，事務(wù)提交成功。

9、大表查詢優(yōu)化

1. 選擇合適的數(shù)據(jù)類型

2. 建立合適的索引

3. sql語句優(yōu)化

4. 分表分庫

5. 讀寫分離

6. 冷熱數(shù)據(jù)分離

7. Elastic search

8. 大數(shù)據(jù)

10、百萬級別或以上的數(shù)據(jù)，你是如何刪除的？

1. 我們想要刪除百萬數(shù)據(jù)的時候可以先刪除索引

2. 然后批量刪除其中無用數(shù)據(jù)

3. 刪除完成后重新創(chuàng)建索引。

11、Mysql 主從復(fù)制的原理及流程

就是將主節(jié)點(diǎn)的數(shù)據(jù)復(fù)制到從節(jié)點(diǎn)。

• 用途：

1. 可以讀寫分離，主庫用來寫數(shù)據(jù)，從庫用來讀數(shù)據(jù)。在更新數(shù)據(jù)時，會對整個表加鎖，如果是讀寫分離的，可以去從表讀取數(shù)據(jù)，這樣就不會有問題。

2. 做備份，主庫出故障后，進(jìn)行故障轉(zhuǎn)移，讓從庫代替主庫，提供服務(wù)。

• 實現(xiàn)原理

主節(jié)點(diǎn)日志發(fā)送線程

當(dāng)主節(jié)點(diǎn)和從節(jié)點(diǎn)建立連接后，主服務(wù)器上會起一個bin log dump線程，用于給從節(jié)點(diǎn)發(fā)送bin log日志(日志所包含的信息之外，還包括本次返回的信息的bin-log file 的以及bin-log position)，在讀取bin log日志時，會對日志文件加鎖，讀取完成后會解鎖。

從節(jié)點(diǎn)I/O線程

從節(jié)點(diǎn)與主節(jié)點(diǎn)建立連接后會起一個I/O線程來接受主節(jié)點(diǎn)發(fā)送過來的bin log日志內(nèi)容，并且保存在從節(jié)點(diǎn)的relay log文件中，保存成功后就會給主節(jié)點(diǎn)回復(fù)ACK消息，表明接收成功。

從節(jié)點(diǎn)SQL線程

從節(jié)點(diǎn)同時會起一個SQL線程，來讀取 relay log 中的內(nèi)容，解析成SQL，并且在從節(jié)點(diǎn)上執(zhí)行，保證和主節(jié)點(diǎn)的數(shù)據(jù)一致性。

• 復(fù)制模式

異步模式（默認(rèn)的模式）

主節(jié)點(diǎn)不會主動push bin log給從節(jié)點(diǎn)，也不會管從節(jié)點(diǎn)的同步情況，默認(rèn)就是這種模式。

半同步模式（MySQL 5.5之后提供）

主節(jié)點(diǎn)給從節(jié)點(diǎn)發(fā)送bin log 之后，會一直等待回應(yīng)，只要一個從節(jié)點(diǎn)接受bin log，并且寫入relay log 成功，給主節(jié)點(diǎn)返回接受成功的ACK信息，主節(jié)點(diǎn)就認(rèn)為成功，提交事務(wù)。

全同步模式

就是需要所有的從節(jié)點(diǎn)接受日志，并且寫入relay log 成功，給主節(jié)點(diǎn)返回接受成功的ACK信息，主節(jié)點(diǎn)才認(rèn)為成功，提交事務(wù)。

總結(jié)：

二個日志：二進(jìn)制日志和中繼日志 三個線程：master的dump和slave的I/O、SQL 主要原理：master將數(shù)據(jù)保存在二進(jìn)制日志中，I/O向dump發(fā)出同步請求，dump把數(shù)據(jù)發(fā)送給I/O線程，I/O寫入本地的中繼日志，SOL線程讀取本地中繼日志數(shù)據(jù)，同步到自己數(shù)據(jù)庫中，完成同步

12、Mysql 主從集群同步延遲問題怎么解決

復(fù)制過程分為幾個步驟：

1. 主庫的更新事件(update、insert、delete)被寫到 binlog

2. 從庫發(fā)起連接，連接到主庫

3. 此時主庫創(chuàng)建一個 binlog dump thread，把 binlog 的內(nèi)容發(fā)送到從庫

4. 從庫啟動之后，創(chuàng)建一個 I/O 線程，讀取主庫傳過來的 binlog 內(nèi)容并寫入到relay log

5. 從庫還會創(chuàng)建一個 SQL 線程，從 relay log 里面讀取內(nèi)容，從Exec_Master_Log_Pos 位置開始執(zhí)行讀取到的更新事件，將更新內(nèi)容寫入到 slave的 db

主從數(shù)據(jù)同步涉及到網(wǎng)絡(luò)數(shù)據(jù)傳輸，由于網(wǎng)絡(luò)通信的延遲以及從庫數(shù)據(jù)處理的效率問題，就會導(dǎo)致主從數(shù)據(jù)同步延遲的情況

一般可以通過以下幾個方法來解決

• 設(shè)計一主多從來分擔(dān)從庫壓力，減少主從同步延遲問題

• 如果對數(shù)據(jù)一致性要求高，在從庫存在延遲的情況下，可以強(qiáng)制走主庫查詢數(shù)據(jù)

• 可以在從庫上執(zhí)行 show slave status 命令，獲取 seconds_behind_master 字段的延遲時間，然后通過 sleep 阻塞等待固定時間后再次查詢

• 通過并行復(fù)制解決從庫復(fù)制延遲的問題

13、執(zhí)行 SQL 響應(yīng)比較慢，你有哪些排查思路及解決方案？

MySQL 的性能優(yōu)化我認(rèn)為可以分為 4 大部分

（1）硬件及操作系統(tǒng)層面優(yōu)化

從硬件層面來說，影響 Mysql 性能的因素有，CPU、可用內(nèi)存大小、磁盤讀寫速度、網(wǎng)絡(luò)帶寬。從操作系層面來說，應(yīng)用文件句柄數(shù)、操作系統(tǒng)網(wǎng)絡(luò)的配置都會影響到 Mysql 性能

（2）架構(gòu)設(shè)計層面的優(yōu)化

MySQL 是一個磁盤 IO 訪問量非常頻繁的關(guān)系型數(shù)據(jù)庫，在高并發(fā)和高性能的場景中.MySQL 數(shù)據(jù)庫必然會承受巨大的并發(fā)壓力，而此時，我們的優(yōu)化方式可以分為幾個部分

• 搭建 Mysql 主從集群，單個 Mysql 服務(wù)容易單點(diǎn)故障，一旦服務(wù)器宕機(jī)，將會導(dǎo)致依賴 Mysql 數(shù)據(jù)庫的應(yīng)用全部無法響應(yīng)。 主從集群或者主主集群可以保證服務(wù)的高可用性。

• 讀寫分離設(shè)計，在讀多寫少的場景中，通過讀寫分離的方案，可以避免讀寫沖突導(dǎo)致的性能影響。

• 引入分庫分表機(jī)制，通過分庫可以降低單個服務(wù)器節(jié)點(diǎn)的 IO 壓力，通過分表的方式可以降低單表數(shù)據(jù)量，從而提升 sql 查詢的效率。

• 針對熱點(diǎn)數(shù)據(jù)，可以引入更為高效的分布式數(shù)據(jù)庫，比如 Redis、MongoDB 等，他們可以很好的緩解 Mysql 的訪問壓力，同時還能提升數(shù)據(jù)檢索性能

（3）MySQL 程序配置優(yōu)化

MySQL 是一個經(jīng)過互聯(lián)網(wǎng)大廠驗證過的生產(chǎn)級別的成熟數(shù)據(jù)庫，對于 Mysql 數(shù)據(jù)庫本身的優(yōu)化，一般是通過 Mysql 中的配置文件 my.cnf 來完成的，比如

• Mysql5.7 版本默認(rèn)的最大連接數(shù)是 151 個，這個值可以在 my.cnf 中修改。 binlog 日志，默認(rèn)是不開啟

• 緩存池 bufferpoll 的默認(rèn)大小配置等。

由于這些配置一般都和用戶安裝的硬件環(huán)境以及使用場景有關(guān)系，因此這些配置官方只會提供一個默認(rèn)值，具體情況還得由使用者來修改。關(guān)于配置項的修改，需要關(guān)注兩個方面

• 配置的作用域，分為會話級別和全局

• 是否支持熱加載

因此，針對這兩個點(diǎn)，我們需要注意的是：

• 全局參數(shù)的設(shè)定對于已經(jīng)存在的會話無法生效

• 會話參數(shù)的設(shè)定隨著會話的銷毀而失效

• 全局類的統(tǒng)一配置建議配置在默認(rèn)配置文件中，否則重啟服務(wù)會導(dǎo)致配置失效

（4）SQL 優(yōu)化

SQL 優(yōu)化又能分為三步曲

• 第一、慢 SQL 的定位和排查。我們可以通過慢查詢?nèi)罩竞吐樵內(nèi)罩痉治龉ぞ叩玫接袉栴}的 SQL 列表????????

• 慢查詢?nèi)罩纠?#xff1a;

• 第二、執(zhí)行計劃分析。針對慢 SQL，我們可以使用關(guān)鍵字 explain 來查看當(dāng)前 sql 的執(zhí)行計劃.可以重點(diǎn)關(guān)注 type key rows filterd 等字段 ，從而定位該 SQL 執(zhí)行慢的根本原因。再有的放矢的進(jìn)行優(yōu)化

• 第三、使用 show profile 工具 。Show Profile 是 MySQL 提供的可以用來分析當(dāng)前會話中，SQL 語句資源消耗情況的工具，可用于 SQL 調(diào)優(yōu)的測量。在當(dāng)前會話中.默認(rèn)情況下處于 show profile 是關(guān)閉狀態(tài)，打開之后保存最近 15 次的運(yùn)行結(jié)果。針對運(yùn)行慢的 SQL，通過 profile 工具進(jìn)行詳細(xì)分析，可以得到 SQL 執(zhí)行過程中所有的 資源開銷情況，如 IO 開銷,CPU 開銷,內(nèi)存開銷等

常見的 SQL 優(yōu)化規(guī)則：

• SQL 的查詢一定要基于索引來進(jìn)行數(shù)據(jù)掃描

• 避免索引列上使用函數(shù)或者運(yùn)算,這樣會導(dǎo)致索引失效

• where 字句中 like %號,盡量放置在右邊

• 使用索引掃描,聯(lián)合索引中的列從左往右,命中越多越好.

• 盡可能使用 SQL 語句用到的索引完成排序,避免使用文件排序的方式

• 查詢有效的列信息即可，少用 * 代替列信息

• 永遠(yuǎn)用小結(jié)果集驅(qū)動大結(jié)果集

14、MySQL 數(shù)據(jù)庫 cpu 飆升的話，要怎么處理呢？

（1）第一步，排查問題

• 使用 top 命令，找到 cpu 占用過高的進(jìn)程是否是 mysqld

• 如果是mysqld導(dǎo)致的，可以在 mysql 中通過 show processlist 查看當(dāng)前的會話情況，確定是否有消耗資源的 SQL 正在運(yùn)行

• 找到消耗過高的 SQL，通過執(zhí)行計劃進(jìn)行具體的分析

（2）第二步，處理方式

• 如果確定是 SQL 問題，可以通過 SQL 的優(yōu)化手段進(jìn)行調(diào)整

• 重新執(zhí)行 SQL 分析確認(rèn)是否有達(dá)到優(yōu)化的目的

（3）第三步，其他情況

如果不是 SQL 的問題導(dǎo)致，那就需要分析 CPU 飆高的這個時間段，Mysql 的整體并發(fā)連接數(shù)。如果有大量的請求連接進(jìn)來，那我們就需要分析這個時間段業(yè)務(wù)的情況，再做出相應(yīng)的調(diào)整。最后，如果是 Mysql 本身的參數(shù)并不是最優(yōu)狀態(tài)，那我們可以對 Mysql 服務(wù)節(jié)點(diǎn)的配置進(jìn)行調(diào)整，比如緩存大小、線程池大小等

15、知道MySQL的WAL、LSN、Checkpoint 嗎？

（1）WAL （預(yù)寫式日志）技術(shù)

WAL的全稱是 Write-Ahead Logging。修改的數(shù)據(jù)要持久化到磁盤，會先寫入磁盤的文件系統(tǒng)緩存，然后可以由后臺線程異步慢慢地刷回到磁盤。所以WAL技術(shù)修改數(shù)據(jù)需要寫兩次寫入。

兩次寫入

• 內(nèi)存寫入：第一次，修改在緩沖池中的頁， 隨機(jī)IO

• 磁盤寫入：第二次，再以一定的頻率刷新到磁盤上，順序IO

WAL的好處

節(jié)省了隨機(jī)寫磁盤的 IO 消耗（轉(zhuǎn)成順序?qū)?#xff09;。而且順序IO性能比較高

（2）LSN（日志序列號）

LSN是Log Sequence Number的縮寫，即日志序列號，表示Redo Log 的序號。

（3）Checkpoint(檢查點(diǎn))

緩沖池的容量和重做日志（redo log）容量是有限的，Checkpoint所做的事就是把臟頁給刷新回磁盤。

定義

一個時間點(diǎn)，由一個LSN值（Checkpoint LSN）表示的整型值，在checkpoint LSN之前的每個數(shù)據(jù)(buffer pool中的臟頁)的更改都已經(jīng)落盤(刷新到數(shù)據(jù)文件中)，checkpoint 完成后，在checkpoint LSN之前的Redo Log就不再需要了

所以：checkpoint是通過LSN實現(xiàn)的。

分類

• Sharp Checkpont

該機(jī)制下，在數(shù)據(jù)庫發(fā)生關(guān)閉時將所有的臟頁都刷新回磁盤。

• Fuzzy Checkpoint

在該機(jī)制下，只刷新一部分臟頁，而不是刷新所有臟頁回磁盤。

數(shù)據(jù)庫關(guān)閉時，使用 Sharp Checkpont 機(jī)制刷新臟頁。 數(shù)據(jù)庫運(yùn)行時，使用 Fuzzy Checkpoint 機(jī)制刷新臟頁。

檢查點(diǎn)觸發(fā)時機(jī)

• Master Thread Checkpoint

后臺異步線程以每秒或每十秒的速度從緩沖池的臟頁列表中刷新一定比例的頁回磁盤。

• FLUSH_LRU_LIST Checkpoint

為了保證LRU列表中可用頁的數(shù)量（通過參數(shù)innodb_lru_scan_depth控制，默認(rèn)值1024），后臺線程定期檢測LRU列表中空閑列表的數(shù)量，若不滿足，就會將移除LRU列表尾端的頁，若移除的頁為臟頁，則需要進(jìn)行Checkpoint。

show VARIABLES like 'innodb_lru_scan_depth'

• Async/sync Flush Checkpoint 當(dāng)重做日志不可用（即redo log寫滿）時，需要強(qiáng)制將一些頁刷新回磁盤，此時臟頁從臟頁列表中獲取。

• Dirty Page too much Checkpoint

即臟頁數(shù)量太多，會強(qiáng)制推進(jìn)CheckPoint。目的是保證緩沖區(qū)有足夠的空閑頁。 innodb_max_dirty_pages_pct的默認(rèn)值為75，表示當(dāng)緩沖池臟頁比例達(dá)到該值時，就會強(qiáng)制進(jìn)行 Checkpoint，刷新一部分臟頁到磁盤。

show VARIABLES like 'innodb_max_dirty_pages_pct'

解決的問題

• 縮短數(shù)據(jù)庫的恢復(fù)時間。

• 緩沖池不夠用時，刷新臟頁到磁盤。

• 重做日志滿時，刷新臟頁。

LSN與checkpoint的聯(lián)系

LSN號串聯(lián)起一個事務(wù)開始到恢復(fù)的過程。

重啟 innodb 時，Redo log 完不完整，采用 Redo log 相關(guān)知識。用 Redo log 恢復(fù)，啟動數(shù)據(jù)庫時，InnoDB 會掃描數(shù)據(jù)磁盤的數(shù)據(jù)頁 data disk lsn 和日志磁盤中的 checkpoint lsn。

兩者相等則從 checkpoint lsn 點(diǎn)開始恢復(fù)，恢復(fù)過程是利用 redo log 到 buffer pool，直到checkpoint lsn 等于 redo log file lsn，則恢復(fù)完成。如果 checkpoint lsn 小于 data disk lsn，說明在檢查點(diǎn)觸發(fā)后還沒結(jié)束刷盤時數(shù)據(jù)庫宕機(jī)了。

因為 checkpoint lsn 最新值是在數(shù)據(jù)刷盤結(jié)束后才記錄的，檢查點(diǎn)之后有一部分?jǐn)?shù)據(jù)已經(jīng)刷入數(shù)據(jù)磁盤，這個時候數(shù)據(jù)磁盤已經(jīng)寫入部分的部分恢復(fù)將不會重做，直接跳到?jīng)]有恢復(fù)的 lsn 值開始恢復(fù)。

總結(jié)

日志空間中的每條日志對應(yīng)一個LSN值，而在數(shù)據(jù)頁的頭部也記錄了當(dāng)前頁最后一次修改的LSN號，每次當(dāng)數(shù)據(jù)頁刷新到磁盤后，會去更新日志文件中checkpoint，以減少需要恢復(fù)執(zhí)行的日志記錄。

極端情況下，數(shù)據(jù)頁刷新到磁盤成功后，去更新checkpoint時如果宕機(jī)，則在恢復(fù)過程中，由于checkpoint還未更新，則數(shù)據(jù)頁中的記錄相當(dāng)于被重復(fù)執(zhí)行，不過由于在日志文件中的操作記錄具有冪等性，所以同一條redo log執(zhí)行多次，不影響數(shù)據(jù)的恢復(fù)

16、聊聊：現(xiàn)在有一個未分庫分表的系統(tǒng)，未來要分庫分表，如何設(shè)計才可以讓系統(tǒng)從未分庫分表動態(tài)切換到分庫分表上？

（1）停機(jī)遷移方案

我先給你說一個最 low 的方案，就是很簡單，大家伙兒凌晨 12 點(diǎn)開始運(yùn)維，網(wǎng)站或者 app 掛個公告，

說 0 點(diǎn)到早上 6 點(diǎn)進(jìn)行運(yùn)維，無法訪問。

接著到 0 點(diǎn)停機(jī)，系統(tǒng)停掉，沒有流量寫入了，此時老的單庫單表數(shù)據(jù)庫靜止了。然后你之前得寫好一個導(dǎo)數(shù)的一次性工具，此時直接跑起來，然后將單庫單表的數(shù)據(jù)嘩嘩嘩讀出來，寫到分庫分表里面去。

導(dǎo)數(shù)完了之后，就 ok 了，修改系統(tǒng)的數(shù)據(jù)庫連接配置啥的，包括可能代碼和 SQL 也許有修改，那你就用最新的代碼，然后直接啟動連到新的分庫分表上去。

驗證一下，ok了，完美，大家伸個懶腰，看看看凌晨 4 點(diǎn)鐘的北京夜景，打個滴滴回家吧。

但是這個方案比較 low，誰都能干，我們來看看高大上一點(diǎn)的方案。

（2）雙寫遷移方案

這個是我們常用的一種遷移方案，比較靠譜一些，不用停機(jī)，不用看北京凌晨 4 點(diǎn)的風(fēng)景。

簡單來說，就是在線上系統(tǒng)里面，之前所有寫庫的地方，增刪改操作，除了對老庫增刪改，都加上對新庫的增刪改，這就是所謂的雙寫，同時寫倆庫，老庫和新庫。

然后系統(tǒng)部署之后，新庫數(shù)據(jù)差太遠(yuǎn)，用之前說的導(dǎo)數(shù)工具，跑起來讀老庫數(shù)據(jù)寫新庫，寫的時候要根據(jù) gmt_modified 這類字段判斷這條數(shù)據(jù)最后修改的時間，除非是讀出來的數(shù)據(jù)在新庫里沒有，或者是比新庫的數(shù)據(jù)新才會寫。簡單來說，就是不允許用老數(shù)據(jù)覆蓋新數(shù)據(jù)。導(dǎo)完一輪之后，有可能數(shù)據(jù)還是存在不一致，那么就程序自動做一輪校驗，比對新老庫每個表的每條數(shù)據(jù)，接著如果有不一樣的，就針對那些不一樣的，從老庫讀數(shù)據(jù)再次寫。反復(fù)循環(huán)，直到兩個庫每個表的數(shù)據(jù)都完全一致為止。

接著當(dāng)數(shù)據(jù)完全一致了，就 ok 了，基于僅僅使用分庫分表的最新代碼，重新部署一次，不就僅僅基于分庫分表在操作了么，還沒有幾個小時的停機(jī)時間，很穩(wěn)。所以現(xiàn)在基本玩兒數(shù)據(jù)遷移之類的，都是這么干的。

17、聊聊：如何設(shè)計可以動態(tài)擴(kuò)容縮容的分庫分表方案？

（1）停機(jī)擴(kuò)容（不推薦）

這個方案就跟停機(jī)遷移一樣，步驟幾乎一致，唯一的一點(diǎn)就是那個導(dǎo)數(shù)的工具，是把現(xiàn)有庫表的數(shù)據(jù)抽出來慢慢倒入到新的庫和表里去。但是最好別這么玩兒，有點(diǎn)不太靠譜，因為既然分庫分表就說明數(shù)據(jù)量實在是太大了，可能多達(dá)幾億條，甚至幾十億，你這么玩兒，可能會出問題。

從單庫單表遷移到分庫分表的時候，數(shù)據(jù)量并不是很大，單表最大也就兩三千萬。那么你寫個工具，多弄幾臺機(jī)器并行跑，1小時數(shù)據(jù)就導(dǎo)完了。這沒有問題。

如果 3 個庫 + 12 個表，跑了一段時間了，數(shù)據(jù)量都 1~2 億了。光是導(dǎo) 2 億數(shù)據(jù)，都要導(dǎo)個幾個小時，6 點(diǎn)，剛剛導(dǎo)完數(shù)據(jù)，還要搞后續(xù)的修改配置，重啟系統(tǒng)，測試驗證，10 點(diǎn)才可以搞完。所以不能這么搞

（2）32庫*32表

一開始上來就是 32 個庫，每個庫 32 個表，那么總共是 1024 張表。

我可以告訴各位同學(xué)，這個分法，第一，基本上國內(nèi)的互聯(lián)網(wǎng)肯定都是夠用了，第二，無論是并發(fā)支撐還是數(shù)據(jù)量支撐都沒問題。

每個庫正常承載的寫入并發(fā)量是 1000，那么 32 個庫就可以承載32 * 1000 = 32000 的寫并發(fā)，如果每個庫承載 1500 的寫并發(fā)，32 * 1500 = 48000 的寫并發(fā)，接近 5萬/s 的寫入并發(fā)，前面再加一個MQ，削峰，每秒寫入 MQ 8 萬條數(shù)據(jù)，每秒消費(fèi) 5 萬條數(shù)據(jù)。

有些除非是國內(nèi)排名非?？壳暗倪@些公司，他們的最核心的系統(tǒng)的數(shù)據(jù)庫，可能會出現(xiàn)幾百臺數(shù)據(jù)庫的這么一個規(guī)模，128個庫，256個庫，512個庫。1024 張表，假設(shè)每個表放 500 萬數(shù)據(jù)，在 Mysql 里可以放 50 億條數(shù)據(jù)。

每秒的 5 萬寫并發(fā)，總共 50 億條數(shù)據(jù)，對于國內(nèi)大部分的互聯(lián)網(wǎng)公司來說，其實一般來說都夠了。談分庫分表的擴(kuò)容，第一次分庫分表，就一次性給他分個夠，32 個庫，1024 張表，可能對大部分的中小型互聯(lián)網(wǎng)公司來說，已經(jīng)可以支撐好幾年了。一個實踐是利用 32 * 32 來分庫分表，即分為 32 個庫，每個庫里一個表分為 32 張表。一共就是 1024張表。根據(jù)某個 id 先根據(jù) 32 取模路由到庫，再根據(jù) 32 取模路由到庫里的表。

18、七種日志的詳細(xì)分析

進(jìn)入正題前，可以先簡單介紹一下，MySQL的邏輯架構(gòu)

MySQL的邏輯架構(gòu)大致可以分為三層：

• 第一層：處理客戶端連接、授權(quán)認(rèn)證，安全校驗等。

• 第二層：服務(wù)器 server 層，負(fù)責(zé)對SQL解釋、分析、優(yōu)化、執(zhí)行操作引擎等。

• 第三層：存儲引擎，負(fù)責(zé)MySQL中數(shù)據(jù)的存儲和提取。

我們要知道MySQL的服務(wù)器層是不管理事務(wù)的，事務(wù)是由存儲引擎實現(xiàn)的，

而MySQL中支持事務(wù)的存儲引擎又屬 InnoDB 使用的最為廣泛，所以后續(xù)文中提到的存儲引擎都以 InnoDB 為主。

而且，可以再簡單介紹一下，MySQL數(shù)據(jù)更新流程，作為鋪墊，具體如下圖：

（1）redo log（重做日志）

redo log 屬于MySQL存儲引擎InnoDB的事務(wù)日志。

MySQL的數(shù)據(jù)是存放在磁盤中的，每次讀寫數(shù)據(jù)都需做磁盤IO操作，如果并發(fā)場景下性能就會很差。為此MySQL提供了一個優(yōu)化手段，引入緩存Buffer Pool。 這個緩存中包含了磁盤中部分?jǐn)?shù)據(jù)頁（page）的映射，以此來緩解數(shù)據(jù)庫的磁盤壓力。當(dāng)從數(shù)據(jù)庫讀數(shù)據(jù)時，首先從緩存中讀取，如果緩存中沒有，則從磁盤讀取后放入緩存；

當(dāng)向數(shù)據(jù)庫寫入數(shù)據(jù)時，先向緩存寫入，此時緩存中的數(shù)據(jù)頁數(shù)據(jù)變更，這個數(shù)據(jù)頁稱為臟頁，BufferPool中修改完數(shù)據(jù)后會按照設(shè)定的更新策略，定期刷到磁盤中，這個過程稱為刷臟頁。

如何保證數(shù)據(jù)不丟失 ，實現(xiàn)高可靠，實現(xiàn)事務(wù)持久性 ？ 如果刷臟頁還未完成，可MySQL由于某些原因宕機(jī)重啟，此時Buffer Pool中修改的數(shù)據(jù)還沒有及時的刷到磁盤中，就會導(dǎo)致數(shù)據(jù)丟失，無法保證事務(wù)的持久性。為了解決這個問題引入了redo log，redo Log如其名側(cè)重于重做！ 它記錄的是數(shù)據(jù)庫中每個頁的修改，而不是某一行或某幾行修改成怎樣，可以用來恢復(fù)提交后的物理數(shù)據(jù)頁，且只能恢復(fù)到最后一次提交的位置。redo log用到了WAL（Write-Ahead Logging）技術(shù)，這個技術(shù)的核心就在于修改記錄前，一定要先寫日志，并保證日志先落盤，才能算事務(wù)提交完成。 有了redo log再修改數(shù)據(jù)時，InnoDB引擎會把更新記錄先寫在redo log中，再修改Buffer Pool中的數(shù)據(jù)，當(dāng)提交事務(wù)時，調(diào)用fsync把redo log刷入磁盤。至于緩存中更新的數(shù)據(jù)文件何時刷入磁盤，則由后臺線程異步處理。 注意：此時redo log的事務(wù)狀態(tài)是prepare，還未真正提交成功，要等bin log日志寫入磁盤完成才會變更為commit，事務(wù)才算真正提交完成。 這樣一來即使刷臟頁之前MySQL意外宕機(jī)也沒關(guān)系，只要在重啟時解析redo log中的更改記錄進(jìn)行重放，重新刷盤即可。

redo log 大小固定 redo log采用固定大小，循環(huán)寫入的格式，當(dāng)redo log寫滿之后，重新從頭開始如此循環(huán)寫，形成一個環(huán)狀。 那為什么要如此設(shè)計呢？ 因為redo log記錄的是數(shù)據(jù)頁上的修改，如果Buffer Pool中數(shù)據(jù)頁已經(jīng)刷磁盤后，那這些記錄就失效了，新日志會將這些失效的記錄進(jìn)行覆蓋擦除。

上圖中的write pos表示redo log當(dāng)前記錄的日志序列號LSN(log sequence number)，寫入還未刷盤，循環(huán)往后遞增； check point表示redo log中的修改記錄已刷入磁盤后的LSN，循環(huán)往后遞增，這個LSN之前的數(shù)據(jù)已經(jīng)全落盤。 write pos到check point之間的部分是redo log空余的部分（綠色），用來記錄新的日志； check point到write pos之間是redo log已經(jīng)記錄的數(shù)據(jù)頁修改數(shù)據(jù)，此時數(shù)據(jù)頁還未刷回磁盤的部分。 當(dāng)write pos追上check point時，會先推動check point向前移動，空出位置（刷盤）再記錄新的日志。

注意：redo log日志滿了，在擦除之前，需要確保這些要被擦除記錄對應(yīng)在內(nèi)存中的數(shù)據(jù)頁都已經(jīng)刷到磁盤中了。擦除舊記錄騰出新空間這段期間，是不能再接收新的更新請求的，此刻MySQL的性能會下降。所以在并發(fā)量大的情況下，合理調(diào)整redo log的文件大小非常重要。

crash-safe 因為redo log的存在使得Innodb引擎具有了crash-safe的能力，即MySQL宕機(jī)重啟，系統(tǒng)會自動去檢查redo log，將修改還未寫入磁盤的數(shù)據(jù)從redo log恢復(fù)到MySQL中。MySQL啟動時，不管上次是正常關(guān)閉還是異常關(guān)閉，總是會進(jìn)行恢復(fù)操作。會先檢查數(shù)據(jù)頁中的LSN，如果這個 LSN 小于 redo log 中的LSN，即write pos位置，說明在redo log上記錄著數(shù)據(jù)頁上尚未完成的操作，接著就會從最近的一個check point出發(fā)，開始同步數(shù)據(jù)。

簡單理解，比如：redo log的LSN是500，數(shù)據(jù)頁的 LSN 是300，表明重啟前有部分?jǐn)?shù)據(jù)未完全刷入到磁盤中，那么系統(tǒng)則將redo log中LSN序號300到500的記錄進(jìn)行重放刷盤。

（2）undo log（回滾日志）

undo log也是屬于MySQL存儲引擎InnoDB的事務(wù)日志。

undo log屬于邏輯日志，如其名主要起到回滾的作用，它是保證事務(wù)原子性的關(guān)鍵。

記錄的是數(shù)據(jù)修改前的狀態(tài)，在數(shù)據(jù)修改的流程中，同時會記錄一條與當(dāng)前操作相反的邏輯日志到undo log中。

（3）bin log（歸檔日志）

bin log是一種數(shù)據(jù)庫Server層（和什么引擎無關(guān)），以二進(jìn)制形式存儲在磁盤中的邏輯日志。

bin log記錄了數(shù)據(jù)庫所有 DDL 和 DML 操作（不包含 SELECT 和 SHOW 等命令，因為這類操作對數(shù)據(jù)本身并沒有修改）

默認(rèn)情況下，二進(jìn)制日志功能是關(guān)閉的。

可以通過以下命令查看二進(jìn)制日志是否開啟：

mysql> SHOW VARIABLES LIKE 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | OFF | +---------------+-------+

bin log主要應(yīng)用于MySQL主從模式（master-slave）中，主從節(jié)點(diǎn)間的數(shù)據(jù)同步；以及基于時間點(diǎn)的數(shù)據(jù)還原。

? ? ? ? ?

通過下圖MySQL的主從復(fù)制過程，來了解下bin log在主從模式下的應(yīng)用。

• 用戶在主庫master執(zhí)行 DDL 和 DML 操作，修改記錄順序?qū)懭隻in log;

• 從庫slave的I/O線程連接上Master，并請求讀取指定位置position的日志內(nèi)容;

• Master收到從庫slave請求后，將指定位置position之后的日志內(nèi)容，和主庫bin log文件的名稱以及在日志中的位置推送給從庫;

• slave的I/O線程接收到數(shù)據(jù)后，將接收到的日志內(nèi)容依次寫入到relay log文件最末端，并將讀取到的主庫bin log文件名和位置position記錄到master-info文件中，以便在下一次讀取用;

• slave的SQL線程檢測到relay log中內(nèi)容更新后，讀取日志并解析成可執(zhí)行的SQL語句，這樣就實現(xiàn)了主從庫的數(shù)據(jù)一致;

基于時間點(diǎn)還原

我們看到bin log也可以做數(shù)據(jù)的恢復(fù)，而redo log也可以，那它們有什么區(qū)別？

• 層次不同：redo log 是InnoDB存儲引擎實現(xiàn)的，bin log 是MySQL的服務(wù)器層實現(xiàn)的，但MySQL數(shù)據(jù)庫中的任何存儲引擎對于數(shù)據(jù)庫的更改都會產(chǎn)生bin log。

• 作用不同：redo log 用于故障恢復(fù)（crash recovery），保證MySQL宕機(jī)也不會影響持久性；bin log 用于時間點(diǎn)恢復(fù)（point-in-time recovery），保證服務(wù)器可以基于時間點(diǎn)恢復(fù)數(shù)據(jù)和主從復(fù)制。

• 內(nèi)容不同：redo log 是物理日志，內(nèi)容基于磁盤的頁P(yáng)age；bin log的內(nèi)容是二進(jìn)制，可以根據(jù)binlog_format參數(shù)自行設(shè)置。

• 寫入方式不同：redo log 采用循環(huán)寫的方式記錄；binlog 通過追加的方式記錄，當(dāng)文件大小大于給定值后，后續(xù)的日志會記錄到新的文件上。

• 刷盤時機(jī)不同：bin log在事務(wù)提交時寫入；redo log 在事務(wù)開始時即開始寫入

bin log 與 redo log 功能并不沖突而是起到相輔相成的作用，需要二者同時記錄，才能保證當(dāng)數(shù)據(jù)庫發(fā)生宕機(jī)重啟時，數(shù)據(jù)不會丟失。

（4）relay log（中繼日志）

relay log日志文件具有與bin log日志文件相同的格式，從上邊MySQL主從復(fù)制的流程可以看出，relay log起到一個中轉(zhuǎn)的作用，slave先從主庫master讀取二進(jìn)制日志數(shù)據(jù)，寫入從庫本地，后續(xù)再異步由SQL線程讀取解析relay log為對應(yīng)的SQL命令執(zhí)行。

（5）slow query log

慢查詢?nèi)罩?#xff08;slow query log）: 用來記錄在 MySQL 中執(zhí)行時間超過指定時間的查詢語句，在 SQL 優(yōu)化過程中會經(jīng)常使用到。通過慢查詢?nèi)罩?#xff0c;我們可以查找出哪些查詢語句的執(zhí)行效率低，耗時嚴(yán)重。

出于性能方面的考慮，一般只有在排查慢SQL、調(diào)試參數(shù)時才會開啟，默認(rèn)情況下，慢查詢?nèi)罩竟δ苁顷P(guān)閉的?？梢酝ㄟ^以下命令查看是否開啟慢查詢?nèi)罩?#xff1a;

mysql> SHOW VARIABLES LIKE 'slow_query%'; +---------------------+--------------------------------------------------------+ | Variable_name | Value | +---------------------+--------------------------------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /usr/local/mysql/data/iZ2zebfzaequ90bdlz820sZ-slow.log | +---------------------+--------------------------------------------------------+

（6）general query log

一般查詢?nèi)罩?#xff08; general query log ）：用來記錄用戶的所有操作，包括客戶端何時連接了服務(wù)器、客戶端發(fā)送的所有 SQL 以及其他事件，比如 MySQL 服務(wù)啟動和關(guān)閉等等。

MySQL 服務(wù)器會按照它接收到語句的先后順序?qū)懭肴罩疚募?/p>

由于一般查詢?nèi)罩居涗浀膬?nèi)容過于詳細(xì)，開啟后 Log 文件的體量會非常龐大，所以出于對性能的考慮，默認(rèn)情況下，該日志功能是關(guān)閉的，通常會在排查故障需獲得詳細(xì)日志的時候才會臨時開啟。

我們可以通過以下命令查看一般查詢?nèi)罩臼欠耖_啟，命令如下：

mysql> show variables like 'general_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | general_log | OFF | +---------------+-------+

（7）error log

錯誤日志（error log）: 應(yīng)該是 MySQL 中最好理解的一種日志，主要記錄 MySQL 服務(wù)器每次啟動和停止的時間以及診斷和出錯信息。默認(rèn)情況下，該日志功能是開啟的，通過如下命令查找錯誤日志文件的存放路徑。

mysql> SHOW VARIABLES LIKE 'log_error'; +---------------+--------------------------------------------------------------- -+ | Variable_name | Value | + ---------------+--------------------------------------------------------------- -+ | log_error | /usr/local/mysql/data/LAPTOP-UHQ6V8KP.err | +---------------+--------------------------------------------------------------- -+

錯誤日志中記錄的可并非全是錯誤信息，像 MySQL 如何啟動 InnoDB 的表空間文件、如何初始化自己的存儲引擎，初始化 buffer pool 等等，這些也記錄在錯誤日志文件中。

六、Innodb引擎

1、邏輯存儲結(jié)構(gòu)

1. 表空間：一個mysql實例對應(yīng)多個表空間，用于存儲索引和數(shù)據(jù)

2. 段：分為數(shù)據(jù)段、索引段、回滾段。一個段包含多個區(qū)

3. 區(qū)：表空間的單元結(jié)構(gòu)，每個區(qū)的大小為1M，默認(rèn)情況下，頁大小為16K，即一個區(qū)中有64個連續(xù)的頁

4. 頁：innodb存儲引擎中磁盤的最小存儲單元

5. 行：存儲事務(wù)id,回滾指針等

2、內(nèi)存結(jié)構(gòu)

1. Buffer pool：緩存池，增刪改查首先操作的是緩存池中的數(shù)據(jù)，之后再以一定頻率刷新磁盤

2. Change buffer：更改緩沖區(qū)（針對非唯一的二級索引頁），執(zhí)行DML語句時，如果Buffer pool不存在對應(yīng)數(shù)據(jù)，會先將數(shù)據(jù)變更存儲在Change buffer，在未來，數(shù)據(jù)被讀取時，在合并到Buffer pool中，一起刷新到磁盤

3. Log buffer：日志緩沖區(qū)，用于保存redolog,undolog,定期刷新到磁盤中，默認(rèn)大小16M

4. 自適應(yīng)hash索引：用于優(yōu)化對Buffer pool數(shù)據(jù)查詢