MySQL面試專題

1.什么是BufferPool？

Buffer Pool基本概念

Buffer Pool：緩沖池，簡稱BP。其作用是用來緩存表數據與索引數據，減少磁盤IO操作，提升效率。

Buffer Pool由緩存數據頁(Page) 和對緩存數據頁進行描述的控制塊 組成, 控制塊中存儲著對應緩存頁的所屬的表空間、數據頁的編號、以及對應緩存頁在Buffer Pool中的地址等信息.

Buffer Pool默認大小是128M, 以Page頁為單位，Page頁默認大小16K，而控制塊的大小約為數據頁的5%，大概是800字節。

注: Buffer Pool大小為128M指的就是緩存頁的大小，控制塊則一般占5%，所以每次會多申請6M的內存空間用于存放控制塊

如何判斷一個頁是否在BufferPool中緩存 ?

MySQl中有一個哈希表數據結構，它使用表空間號+數據頁號，作為一個key，然后緩沖頁對應的控制塊作為value。

當需要訪問某個頁的數據時，先從哈希表中根據表空間號+頁號看看是否存在對應的緩沖頁。
如果有，則直接使用；如果沒有，就從free鏈表中選出一個空閑的緩沖頁，然后把磁盤中對應的頁加載到該緩沖頁的位置

2.InnoDB如何管理Page頁？

Page頁分類

BP的底層采用鏈表數據結構管理Page。在InnoDB訪問表記錄和索引時會在Page頁中緩存，以后使用可以減少磁盤IO操作，提升效率。

Page根據狀態可以分為三種類型：

free page ：空閑page，未被使用
clean page：被使用page，數據沒有被修改過
dirty page：臟頁，被使用page，數據被修改過，Page頁中數據和磁盤的數據產生了不一致

Page頁如何管理

針對上面所說的三種page類型，InnoDB通過三種鏈表結構來維護和管理

free list：表示空閑緩沖區，管理free page

free鏈表是把所有空閑的緩沖頁對應的控制塊作為一個個的節點放到一個鏈表中，這個鏈表便稱之為free鏈表
基節點: free鏈表中只有一個基節點是不記錄緩存頁信息(單獨申請空間)，它里面就存放了free鏈表的頭節點的地址，尾節點的地址，還有free鏈表里當前有多少個節點。

2.flush list：表示需要刷新到磁盤的緩沖區，管理dirty page，內部page按修改時間排序。

InnoDB引擎為了提高處理效率，在每次修改緩沖頁后，并不是立刻把修改刷新到磁盤上，而是在未來的某個時間點進行刷新操作. 所以需要使用到flush鏈表存儲臟頁，凡是被修改過的緩沖頁對應的控制塊都會作為節點加入到flush鏈表.
flush鏈表的結構與free鏈表的結構相似

3.lru list：表示正在使用的緩沖區，管理clean page和dirty page，緩沖區以midpoint為基點，前面鏈表稱為new列表區，存放經常訪問的數據，占63%；后面的鏈表稱為old列表區，存放使用較少數據，占37%

3.為什么寫緩沖區，僅適用于非唯一普通索引頁？

change Buffer基本概念

Change Buffer：寫緩沖區,是針對二級索引(輔助索引) 頁的更新優化措施。

作用: 在進行DML操作時，如果請求的輔助索引（二級索引）沒有在緩沖池中時，并不會立刻將磁盤頁加載到緩沖池，而是在CB記錄緩沖變更，等未來數據被讀取時，再將數據合并恢復到BP中。

ChangeBuffer用于存儲SQL變更操作，比如Insert/Update/Delete等SQL語句
ChangeBuffer中的每個變更操作都有其對應的數據頁，并且該數據頁未加載到緩存中；
當ChangeBuffer中變更操作對應的數據頁加載到緩存中后，InnoDB會把變更操作Merge到數據頁上；
InnoDB會定期加載ChangeBuffer中操作對應的數據頁到緩存中，并Merge變更操作；

change buffer更新流程

寫緩沖區，僅適用于非唯一普通索引頁，為什么？

如果在索引設置唯一性，在進行修改時，InnoDB必須要做唯一性校驗，因此必須查詢磁盤，做一次IO操作。會直接將記錄查詢到BufferPool中，然后在緩沖池修改，不會在ChangeBuffer操作。

4.MySQL為什么改進LRU算法？

普通LRU算法

LRU = Least Recently Used（最近最少使用）: 就是末尾淘汰法，新數據從鏈表頭部加入，釋放空間時從末尾淘汰.

當要訪問某個頁時，如果不在Buffer Pool，需要把該頁加載到緩沖池,并且把該緩沖頁對應的控制塊作為節點添加到LRU鏈表的頭部。
當要訪問某個頁時，如果在Buffer Pool中，則直接把該頁對應的控制塊移動到LRU鏈表的頭部
當需要釋放空間時,從最末尾淘汰

普通LRU鏈表的優缺點

優點

所有最近使用的數據都在鏈表表頭，最近未使用的數據都在鏈表表尾,保證熱數據能最快被獲取到。

缺點

如果發生全表掃描（比如：沒有建立合適的索引 or 查詢時使用select * 等），則有很大可能將真正的熱數據淘汰掉.
由于MySQL中存在預讀機制，很多預讀的頁都會被放到LRU鏈表的表頭。如果這些預讀的頁都沒有用到的話，這樣，會導致很多尾部的緩沖頁很快就會被淘汰。

改進型LRU算法

改進型LRU：將鏈表分為new和old兩個部分，加入元素時并不是從表頭插入，而是從中間midpoint位置插入(就是說從磁盤中新讀出的數據會放在冷數據區的頭部)，如果數據很快被訪問，那么page就會向new列表頭部移動，如果數據沒有被訪問，會逐步向old尾部移動，等待淘汰。

在這里插入圖片描述
冷數據區的數據頁什么時候會被轉到到熱數據區呢 ?

如果該數據頁在LRU鏈表中存在時間超過1s，就將其移動到鏈表頭部 ( 鏈表指的是整個LRU鏈表)
如果該數據頁在LRU鏈表中存在的時間短于1s，其位置不變(由于全表掃描有一個特點，就是它對某個頁的頻繁訪問總耗時會很短)
1s這個時間是由參數 innodb_old_blocks_time 控制的

5.使用索引一定可以提升效率嗎？

索引就是排好序的,幫助我們進行快速查找的數據結構.

簡單來講，索引就是一種將數據庫中的記錄按照特殊形式存儲的數據結構。通過索引，能夠顯著地提高數據查詢的效率，從而提升服務器的性能.

索引的優勢與劣勢

優點
- 提高數據檢索的效率,降低數據庫的IO成本
- 通過索引列對數據進行排序,降低數據排序的成本,降低了CPU的消耗
缺點
- 創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加
- 索引需要占物理空間，除了數據表占用數據空間之外，每一個索引還要占用一定的物理空間
- 當對表中的數據進行增加、刪除和修改的時候，索引也要動態的維護，降低了數據的維護速度
創建索引的原則
- 在經常需要搜索的列上創建索引，可以加快搜索的速度；
- 在作為主鍵的列上創建索引，強制該列的唯一性和組織表中數據的排列結構；
- 在經常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；
- 在經常需要根據范圍進行搜索的列上創建索引，因為索引已經排序，其指定的范圍是連續的；
- 在經常需要排序的列上創建索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；
- 在經常使用在WHERE子句中的列上面創建索引，加快條件的判斷速度。

6.介紹一下Page頁的結構？

Page是整個InnoDB存儲的最基本構件，也是InnoDB磁盤管理的最小單位，與數據庫相關的所有內容都存儲在這種Page結構里。

Page分為幾種類型，常見的頁類型有數據頁（B+tree Node）Undo頁（Undo Log Page）系統頁（System Page）事務數據頁（Transaction System Page）等

Page 各部分說明

名稱	占用大小	說明
File Header	38字節	文件頭, 描述頁信息
Page Header	56字節	頁頭,頁的狀態
Infimum + Supremum	26字節	最大和最小記錄,這是兩個虛擬的行記錄
User Records	不確定	用戶記錄,存儲數據行記錄
Free Space	不確定	空閑空間,頁中還沒有被使用的空間
Page Directory	不確定	頁目錄,存儲用戶記錄的相對位置
File Trailer	8字節	文件尾,校驗頁是否完整

File Header 字段用于記錄 Page 的頭信息，其中比較重要的是 FIL_PAGE_PREV 和 FIL_PAGE_NEXT 字段，通過這兩個字段，我們可以找到該頁的上一頁和下一頁，實際上所有頁通過兩個字段可以形成一條雙向鏈表
Page Header 字段用于記錄 Page 的狀態信息。
Infimum 和 Supremum 是兩個偽行記錄，Infimum（下確界）記錄比該頁中任何主鍵值都要小的值，Supremum （上確界）記錄比該頁中任何主鍵值都要大的值，這個偽記錄分別構成了頁中記錄的邊界。
User Records 中存放的是實際的數據行記錄
Free Space 中存放的是空閑空間，被刪除的行記錄會被記錄成空閑空間
Page Directory 記錄著與二叉查找相關的信息
File Trailer 存儲用于檢測數據完整性的校驗和等數據。

頁結構整體上可以分為三大部分，分別為通用部分(文件頭、文件尾)、存儲記錄空間、索引部分。

通用部分 (File Header&File Trailer )

通用部分 : 主要指文件頭和文件尾，將頁的內容進行封裝，通過文件頭和文件尾校驗的CheckSum方式來確保頁的傳輸是完整的。

其中比較重要的是在文件頭中的 FIL_PAGE_PREV 和 FIL_PAGE_NEXT 字段，通過這兩個字段，我們可以找到該頁的上一頁和下一頁，實際上所有頁通過兩個字段可以形成一條雙向鏈表

記錄部分(User Records&Free Space)

頁的主要作用是存儲記錄，所以“最小和最大記錄”和“用戶記錄”部分占了頁結構的主要空間。另外空閑空間是個靈活的部分，當有新的記錄插入時，會從空閑空間中進行分配用于存儲新記錄

3)數據目錄部分 (Page Directory)

數據頁中行記錄按照主鍵值由小到大順序串聯成一個單鏈表(頁中記錄是以單向鏈表的形式進行存儲的)，且單鏈表的鏈表頭為最小記錄，鏈表尾為最大記錄。并且為了更快速地定位到指定的行記錄，通過 Page Directory實現目錄的功能，借助 Page Directory使用二分法快速找到需要查找的行記錄。

7.說一下聚簇索引與非聚簇索引？

聚集索引與非聚集索引的區別是：葉節點是否存放一整行記錄

聚簇索引: 將數據存儲與索引放到了一塊,索引結構的葉子節點保存了行數據.
非聚簇索引：將數據與索引分開存儲，索引結構的葉子節點指向了數據對應的位置.

InnoDB 主鍵使用的是聚簇索引，MyISAM 不管是主鍵索引，還是二級索引使用的都是非聚簇索引。

在InnoDB引擎中，主鍵索引采用的就是聚簇索引結構存儲。

聚簇索引（聚集索引）

聚簇索引是一種數據存儲方式，InnoDB的聚簇索引就是按照主鍵順序構建 B+Tree結構。B+Tree 的葉子節點就是行記錄，行記錄和主鍵值緊湊地存儲在一起。這也意味著 InnoDB 的主鍵索引就是數據表本身，它按主鍵順序存放了整張表的數據，占用的空間就是整個表數據量的大小。通常說的主鍵索引就是聚集索引。
InnoDB的表要求必須要有聚簇索引：
- 如果表定義了主鍵，則主鍵索引就是聚簇索引
- 如果表沒有定義主鍵，則第一個非空unique列作為聚簇索引
- 否則InnoDB會從建一個隱藏的row-id作為聚簇索引
輔助索引
InnoDB輔助索引，也叫作二級索引，是根據索引列構建 B+Tree結構。但在 B+Tree 的葉子節點中只存了索引列和主鍵的信息。二級索引占用的空間會比聚簇索引小很多，通常創建輔助索引就是為了提升查詢效率。一個表InnoDB只能創建一個聚簇索引，但可以創建多個輔助索引。

非聚簇索引

與InnoDB表存儲不同，MyISM使用的是非聚簇索引，非聚簇索引的兩棵B+樹看上去沒什么不同，節點的結構完全一致只是存儲的內容不同而已，主鍵索引B+樹的節點存儲了主鍵，輔助鍵索引B+樹存儲了輔助鍵。

表數據存儲在獨立的地方，這兩顆B+樹的葉子節點都使用一個地址指向真正的表數據，對于表數據來說，這兩個鍵沒有任何差別。由于索引樹是獨立的，通過輔助鍵檢索無需訪問主鍵的索引樹。

聚簇索引的優點

當你需要取出一定范圍內的數據時，用聚簇索引也比用非聚簇索引好。
當通過聚簇索引查找目標數據時理論上比非聚簇索引要快，因為非聚簇索引定位到對應主鍵時還要多一次目標記錄尋址,即多一次I/O。
使用覆蓋索引掃描的查詢可以直接使用頁節點中的主鍵值。

聚簇索引的缺點

插入速度嚴重依賴于插入順序。
更新主鍵的代價很高，因為將會導致被更新的行移動。
二級索引訪問需要兩次索引查找，第一次找到主鍵值，第二次根據主鍵值找到行數據。

8.索引有哪幾種類型？

1）普通索引

這是最基本的索引類型，基于普通字段建立的索引，沒有任何限制。

CREATE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );

2）唯一索引

與"普通索引"類似，不同的就是：索引字段的值必須唯一，但允許有空值。

CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;

3）主鍵索引

它是一種特殊的唯一索引，不允許有空值。在創建或修改表時追加主鍵約束即可，每個表只能有一個主鍵。

CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );
ALTER TABLE tablename ADD PRIMARY KEY (字段名);

4）復合索引

用戶可以在多個列上建立索引，這種索引叫做組復合索引（組合索引）。復合索引可以代替多個單一索引，相比多個單一索引復合索引所需的開銷更小。

CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );

復合索引使用注意事項：
- 何時使用復合索引，要根據where條件建索引，注意不要過多使用索引，過多使用會對更新操作效率有很大影響。
- 如果表已經建立了(col1，col2)，就沒有必要再單獨建立（col1）；如果現在有(col1)索引，如果查詢需要col1和col2條件，可以建立(col1,col2)復合索引，對于查詢有一定提高。

5) 全文索引

查詢操作在數據量比較少時，可以使用like模糊查詢，但是對于大量的文本數據檢索，效率很低。如果使用全文索引，查詢速度會比like快很多倍。

在MySQL 5.6 以前的版本，只有MyISAM存儲引擎支持全文索引，從MySQL 5.6開始MyISAM和InnoDB存儲引擎均支持。

CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;

全文索引方式有自然語言檢索 IN NATURAL LANGUAGE MODE和布爾檢索 IN BOOLEAN MODE兩種

和常用的like模糊查詢不同，全文索引有自己的語法格式，使用 match 和 against 關鍵字，比如

SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aabb');-- * 表示通配符,只能在詞的后面
SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aa*'  IN BOOLEAN MODE);

全文索引使用注意事項：

全文索引必須在字符串、文本字段上建立。
全文索引字段值必須在最小字符和最大字符之間的才會有效。（innodb：3-84；myisam：4-84）

9.介紹一下最佳左前綴法則？

1)最佳左前綴法則

最佳左前綴法則: 如果創建的是聯合索引,就要遵循該法則. 使用索引時，where后面的條件需要從索引的最左前列開始使用,并且不能跳過索引中的列使用。

場景1: 按照索引字段順序使用，三個字段都使用了索引,沒有問題。

EXPLAIN SELECT * FROM users WHERE user_name = 'tom' 
AND user_age = 17 AND user_level = 'A';

![外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳](https://img-home.csdnimg.cn/images/20230724024159.png?origin_url=file%3A%2F%2F%2FUsers%2Fcongqing%2FDownloads%2F%252002_%25E6%258E%2588%25E8%25AF%25BE%25E8%25B5%2584%25E6%2596%2599%2FMySQL%25E4%25BC%2598%25E5%258C%2596%25E5%25AE%259E%25E6%2588%2598_%25E6%259C%2580%25E7%25BB%2588%25E8%25AE%25B2%25E4%25B9%2589%2F02_%25E5%259B%25BE%25E7%2589%2587%2F96.jpg%3FlastModify%3D1672123139&pos_id=img-oXt79swg-1742805466

場景2: 直接跳過user_name使用索引字段，索引無效，未使用到索引。

EXPLAIN SELECT * FROM users WHERE user_age = 17 AND user_level = 'A';

場景3: 不按照創建聯合索引的順序,使用索引

EXPLAIN SELECT * FROM users WHERE 
user_age = 17 AND user_name = 'tom' AND user_level = 'A';

where后面查詢條件順序是 user_age、user_level、user_name與我們創建的索引順序 user_name、user_age、user_level不一致，為什么還是使用了索引，原因是因為MySql底層優化器對其進行了優化。

最佳左前綴底層原理
MySQL創建聯合索引的規則是: 首先會對聯合索引最左邊的字段進行排序( 例子中是 user_name ), 在第一個字段的基礎之上再對第二個字段進行排序 ( 例子中是 user_age ) .
最佳左前綴原則其實是和B+樹的結構有關系, 最左字段肯定是有序的, 第二個字段則是無序的(聯合索引的排序方式是: 先按照第一個字段進行排序,如果第一個字段相等再根據第二個字段排序). 所以如果直接使用第二個字段 user_age 通常是使用不到索引的.

10.什么是索引下推？

索引下推（index condition pushdown ）簡稱ICP，在Mysql5.6的版本上推出，用于優化查詢。

需求: 查詢users表中 “名字第一個字是張，年齡為10歲的所有記錄”。

SELECT * FROM users WHERE user_name LIKE '張%' AND user_age = 10;

根據最左前綴法則，該語句在搜索索引樹的時候，只能匹配到名字第一個字是‘張’的記錄，接下來是怎么處理的呢？當然就是從該記錄開始，逐個回表，到主鍵索引上找出相應的記錄，再比對 age 這個字段的值是否符合。

圖1: 在 (name,age) 索引里面特意去掉了 age 的值，這個過程 InnoDB 并不會去看 age 的值，只是按順序把“name 第一個字是’張’”的記錄一條條取出來回表。因此，需要回表 4 次

MySQL 5.6引入了索引下推優化，可以在索引遍歷過程中，對索引中包含的字段先做判斷，過濾掉不符合條件的記錄，減少回表次數。

圖2: InnoDB 在 (name,age) 索引內部就判斷了 age 是否等于 10，對于不等于 10 的記錄，直接判斷并跳過,減少回表次數.

總結

如果沒有索引下推優化（或稱ICP優化），當進行索引查詢時，首先根據索引來查找記錄，然后再根據where條件來過濾記錄；

在支持ICP優化后，MySQL會在取出索引的同時，判斷是否可以進行where條件過濾再進行索引查詢，也就是說提前執行where的部分過濾操作，在某些場景下，可以大大減少回表次數，從而提升整體性能。

11.什么是自適應哈希索引？

自適應Hash索引（Adatptive Hash Index，內部簡稱AHI）是InnoDB的三大特性之一，還有兩個是 Buffer Pool簡稱BP、雙寫緩沖區（Doublewrite Buffer）。

1、自適應即我們不需要自己處理，當InnoDB引擎根據查詢統計發現某一查詢滿足hash索引的數據結構特點，就會給其建立一個hash索引；

2、hash索引底層的數據結構是散列表（Hash表），其數據特點就是比較適合在內存中使用，自適應Hash索引存在于InnoDB架構中的緩存中（不存在于磁盤架構中），見下面的InnoDB架構圖。

3、自適應hash索引只適合搜索等值的查詢，如select * from table where index_col=‘xxx’，而對于其他查找類型，如范圍查找，是不能使用的；

Adaptive Hash Index是針對B+樹Search Path的優化，因此所有會涉及到Search Path的操作，均可使用此Hash索引進行優化.

根據索引鍵值(前綴)快速定位到葉子節點滿足條件記錄的Offset，減少了B+樹Search Path的代價，將B+樹從Root節點至Leaf節點的路徑定位，優化為Hash Index的快速查詢。

InnoDB的自適應Hash索引是默認開啟的，可以通過配置下面的參數設置進行關閉。

innodb_adaptive_hash_index = off

自適應Hash索引使用分片進行實現的，分片數可以使用配置參數設置：

innodb_adaptive_hash_index_parts = 8

12.為什么LIKE以%開頭索引會失效？

like查詢為范圍查詢，%出現在左邊，則索引失效。%出現在右邊索引未失效.

場景1: 兩邊都有% 或者字段左邊有%,索引都會失效。

EXPLAIN SELECT * FROM users WHERE user_name LIKE '%tom%';EXPLAIN SELECT * FROM users WHERE user_name LIKE '%tom';

場景2: 字段右邊有%,索引生效

EXPLAIN SELECT * FROM users WHERE user_name LIKE 'tom%';

解決%出現在左邊索引失效的方法，使用覆蓋索引

EXPLAIN SELECT user_name FROM users WHERE user_name LIKE '%jack%';EXPLAIN SELECT user_name,user_age,user_level FROM users WHERE user_name LIKE '%jack%';

對比場景1可以知道, 通過使用覆蓋索引 type = index,并且 extra = Using index,從全表掃描變成了全索引掃描.

like 失效的原因

%號在右: 由于B+樹的索引順序，是按照首字母的大小進行排序，%號在右的匹配又是匹配首字母。所以可以在B+樹上進行有序的查找，查找首字母符合要求的數據。所以有些時候可以用到索引.
%號在左: 是匹配字符串尾部的數據，我們上面說了排序規則，尾部的字母是沒有順序的，所以不能按照索引順序查詢，就用不到索引.
兩個%%號: 這個是查詢任意位置的字母滿足條件即可，只有首字母是進行索引排序的，其他位置的字母都是相對無序的，所以查找任意位置的字母是用不上索引的.

13.自增還是UUID？數據庫主鍵的類型該如何選擇？

auto_increment的優點：

字段長度較uuid小很多，可以是bigint甚至是int類型，這對檢索的性能會有所影響。
在寫的方面，因為是自增的，所以主鍵是趨勢自增的，也就是說新增的數據永遠在后面，這點對于性能有很大的提升。
數據庫自動編號，速度快，而且是增量增長，按順序存放，對于檢索非常有利。
數字型，占用空間小，易排序，在程序中傳遞也方便。

auto_increment的缺點：

由于是自增，很容易通過網絡爬蟲知曉當前系統的業務量。
高并發的情況下，競爭自增鎖會降低數據庫的吞吐能力。
數據遷移或分庫分表場景下，自增方式不再適用。

UUID的優點：

不會沖突。進行數據拆分、合并存儲的時候，能保證主鍵全局的唯一性
可以在應用層生成，提高數據庫吞吐能力

UUID的缺點：

影響插入速度，并且造成硬盤使用率低。與自增相比，最大的缺陷就是隨機io，下面我們會去具體解釋
字符串類型相比整數類型肯定更消耗空間，而且會比整數類型操作慢。

uuid 和自增 id 的索引結構對比

1、使用自增 id 的內部結構

自增的主鍵的值是順序的，所以 InnoDB 把每一條記錄都存儲在一條記錄的后面。

當達到頁面的最大填充因子時候（InnoDB 默認的最大填充因子是頁大小的 15/16，會留出 1/16 的空間留作以后的修改）。
下一條記錄就會寫入新的頁中，一旦數據按照這種順序的方式加載，主鍵頁就會近乎于順序的記錄填滿，提升了頁面的最大填充率，不會有頁的浪費。
新插入的行一定會在原有的最大數據行下一行，MySQL 定位和尋址很快，不會為計算新行的位置而做出額外的消耗。減少了頁分裂和碎片的產生。

2、使用 uuid 的索引內部結構

插入UUID：新的記錄可能會插入之前記錄的中間，因此需要移動之前的記錄

被寫滿已經刷新到磁盤上的頁可能會被重新讀取

因為 uuid 相對順序的自增 id 來說是毫無規律可言的，新行的值不一定要比之前的主鍵的值要大，所以 innodb 無法做到總是把新行插入到索引的最后，而是需要為新行尋找新的合適的位置從而來分配新的空間。

這個過程需要做很多額外的操作，數據的毫無順序會導致數據分布散亂，將會導致以下的問題：

寫入的目標頁很可能已經刷新到磁盤上并且從緩存上移除，或者還沒有被加載到緩存中，innodb 在插入之前不得不先找到并從磁盤讀取目標頁到內存中，這將導致大量的隨機 IO。
因為寫入是亂序的，innodb 不得不頻繁的做頁分裂操作，以便為新的行分配空間，頁分裂導致移動大量的數據，一次插入最少需要修改三個頁以上。
由于頻繁的頁分裂，頁會變得稀疏并被不規則的填充，最終會導致數據會有碎片。
在把隨機值（uuid 和雪花 id）載入到聚簇索引（InnoDB 默認的索引類型）以后，有時候會需要做一次 OPTIMEIZE TABLE 來重建表并優化頁的填充，這將又需要一定的時間消耗。

結論：使用 InnoDB 應該盡可能的按主鍵的自增順序插入，并且盡可能使用單調的增加的聚簇鍵的值來插入新行。如果是分庫分表場景下，分布式主鍵ID的生成方案優先選擇雪花算法生成全局唯一主鍵（雪花算法生成的主鍵在一定程度上是有序的）。

14.InnoDB與MyISAM的區別？

InnoDB和MyISAM是使用MySQL時最常用的兩種引擎類型，我們重點來看下兩者區別。

事務和外鍵
InnoDB支持事務和外鍵，具有安全性和完整性，適合大量insert或update操作
MyISAM不支持事務和外鍵，它提供高速存儲和檢索，適合大量的select查詢操作
鎖機制
InnoDB支持行級鎖，鎖定指定記錄。基于索引來加鎖實現。
MyISAM支持表級鎖，鎖定整張表。
索引結構
InnoDB使用聚集索引（聚簇索引），索引和記錄在一起存儲，既緩存索引，也緩存記錄。
MyISAM使用非聚集索引（非聚簇索引），索引和記錄分開。
并發處理能力
MyISAM使用表鎖，會導致寫操作并發率低，讀之間并不阻塞，讀寫阻塞。
InnoDB讀寫阻塞可以與隔離級別有關，可以采用多版本并發控制（MVCC）來支持高并發
存儲文件
InnoDB表對應兩個文件，一個.frm表結構文件，一個.ibd數據文件。InnoDB表最大支持64TB；
MyISAM表對應三個文件，一個.frm表結構文件，一個MYD表數據文件，一個.MYI索引文件。從MySQL5.0開始默認限制是256TB。

MyISAM 適用場景

不需要事務支持（不支持）
并發相對較低（鎖定機制問題）
數據修改相對較少，以讀為主
數據一致性要求不高

InnoDB 適用場景

需要事務支持（具有較好的事務特性）
行級鎖定對高并發有很好的適應能力
數據更新較為頻繁的場景
數據一致性要求較高
硬件設備內存較大，可以利用InnoDB較好的緩存能力來提高內存利用率，減少磁盤IO

兩種引擎該如何選擇？

是否需要事務？有，InnoDB
是否存在并發修改？有，InnoDB
是否追求快速查詢，且數據修改少？是，MyISAM
在絕大多數情況下，推薦使用InnoDB

擴展資料：各個存儲引擎特性對比

15.B樹和B+樹的區別是什么？

1）B-Tree介紹

B-Tree是一種平衡的多路查找樹,B樹允許一個節點存放多個數據. 這樣可以在盡可能減少樹的深度的同時,存放更多的數據(把瘦高的樹變的矮胖).

B-Tree中所有節點的子樹個數的最大值稱為B-Tree的階,用m表示.一顆m階的B樹,如果不為空,就必須滿足以下條件.

m階的B-Tree滿足以下條件:

每個節點最多擁有m-1個關鍵字(根節點除外),也就是m個子樹
根節點至少有兩個子樹(可以沒有子樹,有就必須是兩個)
分支節點至少有(m/2)顆子樹 (除去根節點和葉子節點其他都是分支節點)
所有葉子節點都在同一層,并且以升序排序

什么是B-Tree的階 ?
所有節點中，節點【60,70,90】擁有的子節點數目最多，四個子節點（灰色節點），所以上面的B-Tree為4階B樹。

B-Tree結構存儲索引的特點

為了描述B-Tree首先定義一條記錄為一個鍵值對[key, data] ，key為記錄的鍵值，對應表中的主鍵值(聚簇索引)，data為一行記錄中除主鍵外的數據。對于不同的記錄，key值互不相同

索引值和data數據分布在整棵樹結構中
白色塊部分是指針,存儲著子節點的地址信息。
每個節點可以存放多個索引值及對應的data數據
樹節點中的多個索引值從左到右升序排列

B-Tree的查找操作

B-Tree的每個節點的元素可以視為一次I/O讀取，樹的高度表示最多的I/O次數，在相同數量的總元素個數下，每個節點的元素個數越多，高度越低，查詢所需的I/O次數越少.

B-Tree總結

優點: B樹可以在內部節點存儲鍵值和相關記錄數據，因此把頻繁訪問的數據放在靠近根節點的位置將大大提高熱點數據的查詢效率。
缺點: B樹中每個節點不僅包含數據的key值,還有data數據. 所以當data數據較大時,會導致每個節點存儲的key值減少,并且導致B樹的層數變高.增加查詢時的IO次數.
使用場景: B樹主要應用于文件系統以及部分數據庫索引，如MongoDB，大部分關系型數據庫索引則是使用B+樹實現

2）B+Tree

B+Tree是在B-Tree基礎上的一種優化，使其更適合實現存儲索引結構，InnoDB存儲引擎就是用B+Tree實現其索引結構。

B+Tree的特征

非葉子節點只存儲鍵值信息.
所有葉子節點之間都有一個鏈指針.
數據記錄都存放在葉子節點中.

B+Tree的優勢

B+Tree是B Tree的變種，B Tree能解決的問題，B+Tree也能夠解決（降低樹的高度，增大節點存儲數據量）
B+Tree掃庫和掃表能力更強，如果我們要根據索引去進行數據表的掃描，對B Tree進行掃描，需要把整棵樹遍歷一遍，而B+Tree只需要遍歷他的所有葉子節點即可（葉子節點之間有引用）。
B+Tree磁盤讀寫能力更強，他的根節點和支節點不保存數據區，所有根節點和支節點同樣大小的情況下，保存的關鍵字要比B Tree要多。而葉子節點不保存子節點引用。所以，B+Tree讀寫一次磁盤加載的關鍵字比B Tree更多。
B+Tree排序能力更強，如上面的圖中可以看出，B+Tree天然具有排序功能。
B+Tree查詢效率更加穩定，每次查詢數據，查詢IO次數一定是穩定的。當然這個每個人的理解都不同，因為在B Tree如果根節點命中直接返回，確實效率更高。

16.一個B+樹中大概能存放多少條索引記錄？

MySQL設計者將一個B+Tree的節點的大小設置為等于一個頁. (這樣做的目的是每個節點只需要一次I/O就可以完全載入), InnoDB的一個頁的大小是16KB,所以每個節點的大小也是16KB, 并且B+Tree的根節點是保存在內存中的,子節點才是存儲在磁盤上.

假設一個B+樹高為2，即存在一個根節點和若干個葉子節點，那么這棵B+樹的存放總記錄數為：

根節點指針數*單個葉子節點記錄行數.

計算根節點指針數: 假設表的主鍵為INT類型,占用的就是4個字節,或者是BIGINT占用8個字節, 指針大小為6個字節,那么一個頁(就是B+Tree中的一個節點) ,大概可以存儲: 16384B / (4B+6B) = 1638 ,一個節點最多可以存儲1638個索引指針.
計算每個葉子節點的記錄數:我們假設一行記錄的數據大小為1k,那么一頁就可以存儲16行數據,16KB / 1KB = 16.
一顆高度為2的B+Tree可以存放的記錄數為: 1638 * 16=26208 條數據記錄, 同樣的原理可以推算出一個高度3的B+Tree可以存放: 1638 * 1638 * 16 = 42928704條這樣的記錄.

所以InnoDB中的B+Tree高度一般為1-3層,就可以滿足千萬級別的數據存儲,在查找數據時一次頁的查找代表一次 IO，所以通過主鍵索引查詢通常只需要 1-3 次 IO 操作即可查找到數據。

17.explain 用過嗎，有哪些主要字段？

使用 EXPLAIN 關鍵字可以模擬優化器來執行SQL查詢語句，從而知道MySQL是如何處理我們的SQL語句的。分析出查詢語句或是表結構的性能瓶頸。

MySQL查詢過程

通過explain我們可以獲得以下信息：

表的讀取順序
數據讀取操作的操作類型
哪些索引可以被使用
哪些索引真正被使用
表的直接引用
每張表的有多少行被優化器查詢了

Explain使用方式: explain+sql語句, 通過執行explain可以獲得sql語句執行的相關信息

explain select * from users;

18.type字段中有哪些常見的值？

type字段在 MySQL 官網文檔描述如下：

The join type. For descriptions of the different types.

type字段顯示的是連接類型 ( join type表示的是用什么樣的方式來獲取數據)，它描述了找到所需數據所使用的掃描方式, 是較為重要的一個指標。

下面給出各種連接類型,按照從最佳類型到最壞類型進行排序:

-- 完整的連接類型比較多
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL-- 簡化之后,我們可以只關注一下幾種
system > const > eq_ref > ref > range > index > ALL

一般來說,需要保證查詢至少達到 range級別,最好能到ref,否則就要就行SQL的優化調整

下面介紹type字段不同值表示的含義:

type類型	解釋
system	不進行磁盤IO,查詢系統表,僅僅返回一條數據
const	查找主鍵索引,最多返回1條或0條數據. 屬于精確查找
eq_ref	查找唯一性索引,返回數據最多一條, 屬于精確查找
ref	查找非唯一性索引,返回匹配某一條件的多條數據,屬于精確查找,數據返回可能是多條.
range	查找某個索引的部分索引,只檢索給定范圍的行,屬于范圍查找. 比如: > 、 < 、in 、between
index	查找所有索引樹,比ALL快一些,因為索引文件要比數據文件小.
ALL	不使用任何索引,直接進行全表掃描

19.Extra有哪些主要指標，各自的含義是什么？

Extra 是 EXPLAIN 輸出中另外一個很重要的列，該列顯示MySQL在查詢過程中的一些詳細信息

extra類型	解釋
Using filesort	MySQL中無法利用索引完成的排序操作稱為 “文件排序”
Using index	表示直接訪問索引就能夠獲取到所需要的數據（覆蓋索引），不需要通過索引回表
Using index condition	搜索條件中雖然出現了索引列，但是有部分條件無法使用索引，會根據能用索引的條件先搜索一遍再匹配無法使用索引的條件。
Using join buffer	使用了連接緩存, 會顯示join連接查詢時,MySQL選擇的查詢算法
Using temporary	表示MySQL需要使用臨時表來存儲結果集，常見于排序和分組查詢
Using where	意味著全表掃描或者在查找使用索引的情況下，但是還有查詢條件不在索引字段當中

20.如何進行分頁查詢優化？

一般性分頁一般的分頁查詢使用簡單的 limit 子句就可以實現。limit格式如下：

SELECT * FROM 表名 LIMIT [offset,] rows

思考1：如果偏移量固定，返回記錄量對執行時間有什么影響？

select * from user limit 10000,1;
select * from user limit 10000,10;
select * from user limit 10000,100;
select * from user limit 10000,1000;
select * from user limit 10000,10000;

結果：在查詢記錄時，返回記錄量低于100條，查詢時間基本沒有變化，差距不大。隨著查詢記錄量越大，所花費的時間也會越來越多。

思考2：如果查詢偏移量變化，返回記錄數固定對執行時間有什么影響？

select * from user limit 1,100;
select * from user limit 10,100;
select * from user limit 100,100;
select * from user limit 1000,100;
select * from user limit 10000,100;

結果：在查詢記錄時，如果查詢記錄量相同，偏移量超過100后就開始隨著偏移量增大，查詢時間急劇的增加。（這種分頁查詢機制，每次都會從數據庫第一條記錄開始掃描，越往后查詢越慢，而且查詢的數據越多，也會拖慢總查詢速度。）

- 第一個參數指定第一個返回記錄行的偏移量，注意從0開始；
- 第二個參數指定返回記錄行的最大數目；
- 如果只給定一個參數，它表示返回最大的記錄行數目；

分頁優化方案優化1: 通過索引進行分頁直接進行limit操作會產生全表掃描,速度很慢. Limit限制的是從結果集的M位置處取出N條輸出,其余拋棄.假設ID是連續遞增的,我們根據查詢的頁數和查詢的記錄數可以算出查詢的id的范圍，然后配合 limit使用

EXPLAIN SELECT * FROM user WHERE id  >= 100001 LIMIT 100;

優化2：利用子查詢優化

-- 首先定位偏移位置的id
SELECT id FROM user_contacts LIMIT 100000,1;-- 根據獲取到的id值向后查詢.
EXPLAIN SELECT * FROM user_contacts WHERE id >=
(SELECT id FROM user_contacts LIMIT 100000,1) LIMIT 100;

原因：使用了id做主鍵比較(id>=)，并且子查詢使用了覆蓋索引進行優化。

21.如何做慢查詢優化？

MySQL 慢查詢的相關參數解釋：

slow_query_log：是否開啟慢查詢日志，ON(1)表示開啟,
OFF(0) 表示關閉。
slow-query-log-file：新版（5.6及以上版本）MySQL數據庫慢查詢日志存儲路徑。
long_query_time：慢查詢閾值，當查詢時間多于設定的閾值時，記錄日志。

慢查詢配置方式

默認情況下slow_query_log的值為OFF，表示慢查詢日志是禁用的

mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------+
| Variable_name       | Value                        |
+---------------------+------------------------------+
| slow_query_log      | ON                           |
| slow_query_log_file | /var/lib/mysql/test-slow.log |
+---------------------+------------------------------+

可以通過設置slow_query_log的值來開啟

mysql> set global slow_query_log=1;

使用 set global slow_query_log=1 開啟了慢查詢日志只對當前數據庫生效，MySQL重啟后則會失效。如果要永久生效，就必須修改配置文件my.cnf（其它系統變量也是如此）

-- 編輯配置
vim /etc/my.cnf-- 添加如下內容
slow_query_log =1
slow_query_log_file=/var/lib/mysql/ruyuan-slow.log-- 重啟MySQL
service mysqld restartmysql> show variables like '%slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /var/lib/mysql/ruyuan-slow.log |
+---------------------+--------------------------------+

那么開啟了慢查詢日志后，什么樣的SQL才會記錄到慢查詢日志里面呢？這個是由參數 long_query_time控制，默認情況下long_query_time的值為10秒.

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)mysql>  show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

修改了變量long_query_time，但是查詢變量long_query_time的值還是10，難道沒有修改到呢？注意：使用命令 set global long_query_time=1 修改后，需要重新連接或新開一個會話才能看到修改值。

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

log_output 參數是指定日志的存儲方式。log_output='FILE' 表示將日志存入文件，默認值是’FILE’。log_output='TABLE' 表示將日志存入數據庫，這樣日志信息就會被寫入到 mysql.slow_log 表中。

mysql> SHOW VARIABLES LIKE '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

MySQL數據庫支持同時兩種日志存儲方式，配置的時候以逗號隔開即可，如：log_output=‘FILE,TABLE’。日志記錄到系統的專用日志表中，要比記錄到文件耗費更多的系統資源，因此對于需要啟用慢查詢日志，又需要能夠獲得更高的系統性能，那么建議優先記錄到文件.

系統變量 log-queries-not-using-indexes：未使用索引的查詢也被記錄到慢查詢日志中（可選項）。如果調優的話，建議開啟這個選項。

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+mysql> set global log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+

3) 慢查詢測試

執行 test_index.sql 腳本,監控慢查詢日志內容

[root@localhost mysql]# tail -f /var/lib/mysql/ruyuan-slow.log 
/usr/sbin/mysqld, Version: 5.7.30-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument

執行下面的SQL,執行超時 (超過1秒) 我們去查看慢查詢日志

SELECT * FROM test_index WHERE  
hobby = '20009951' OR hobby = '10009931' OR hobby = '30009931' 
OR dname = 'name4000' OR dname = 'name6600' ;

日志內容

我們得到慢查詢日志后，最重要的一步就是去分析這個日志。我們先來看下慢日志里到底記錄了哪些內容。

如下圖是慢日志里其中一條SQL的記錄內容，可以看到有時間戳，用戶，查詢時長及具體的SQL等信息.

# Time: 2022-02-23T13:50:45.005959Z
# User@Host: root[root] @ localhost []  Id:     3
# Query_time: 3.724273  Lock_time: 0.000371 Rows_sent: 5  Rows_examined: 5000000
SET timestamp=1645624245;
select * from test_index where hobby = '20009951' or hobby = '10009931' or hobby = '30009931' or dname = 'name4000' or dname = 'name6600';

Time: 執行時間
User: 用戶信息 ,Id信息
Query_time: 查詢時長
Lock_time: 等待鎖的時長
Rows_sent:查詢結果的行數
Rows_examined: 查詢掃描的行數
SET timestamp: 時間戳
SQL的具體信息

慢查詢SQL優化思路

1) SQL性能下降的原因

在日常的運維過程中，經常會遇到DBA將一些執行效率較低的SQL發過來找開發人員分析，當我們拿到這個SQL語句之后，在對這些SQL進行分析之前，需要明確可能導致SQL執行性能下降的原因進行分析，執行性能下降可以體現在以下兩個方面：

等待時間長

鎖表導致查詢一直處于等待狀態，后續我們從MySQL鎖的機制去分析SQL執行的原理

執行時間長

1.查詢語句寫的爛
2.索引失效 
3.關聯查詢太多join 
4.服務器調優及各個參數的設置

2) 慢查詢優化思路

優先選擇優化高并發執行的SQL,因為高并發的SQL發生問題帶來后果更嚴重.

比如下面兩種情況:SQL1: 每小時執行10000次, 每次20個IO 優化后每次18個IO,每小時節省2萬次IOSQL2: 每小時10次,每次20000個IO,每次優化減少2000個IO,每小時節省2萬次IOSQL2更難優化,SQL1更好優化.但是第一種屬于高并發SQL,更急需優化 成本更低

定位優化對象的性能瓶頸(在優化之前了解性能瓶頸在哪)

在去優化SQL時,選擇優化分方向有三個: 1.IO(數據訪問消耗的了太多的時間,查看是否正確使用了索引) , 2.CPU(數據運算花費了太多時間, 數據的運算分組 排序是不是有問題) 3.網絡帶寬(加大網絡帶寬)

明確優化目標

需要根據數據庫當前的狀態
數據庫中與該條SQL的關系
當前SQL的具體功能
最好的情況消耗的資源,最差情況下消耗的資源,優化的結果只有一個給用戶一個好的體驗

從explain執行計劃入手

只有explain能告訴你當前SQL的執行狀態

永遠用小的結果集驅動大的結果集

小的數據集驅動大的數據集,減少內層表讀取的次數類似于嵌套循環
for(int i = 0; i < 5; i++){for(int i = 0; i < 1000; i++){}
}
如果小的循環在外層,對于數據庫連接來說就只連接5次,進行5000次操作,如果1000在外,則需要進行1000次數據庫連接,從而浪費資源，增加消耗.這就是為什么要小表驅動大表。

盡可能在索引中完成排序

排序操作用的比較多,order by 后面的字段如果在索引中,索引本來就是排好序的,所以速度很快,沒有索引的話,就需要從表中拿數據,在內存中進行排序,如果內存空間不夠還會發生落盤操作

只獲取自己需要的列

不要使用select  * ,select * 很可能不走索引,而且數據量過大

只使用最有效的過濾條件

誤區 where后面的條件越多越好,但實際上是應該用最短的路徑訪問到數據

盡可能避免復雜的join和子查詢

每條SQL的JOIN操作 建議不要超過三張表
將復雜的SQL, 拆分成多個小的SQL 單個表執行,獲取的結果 在程序中進行封裝
如果join占用的資源比較多,會導致其他進程等待時間變長

合理設計并利用索引

如何判定是否需要創建索引?1.較為頻繁的作為查詢條件的字段應該創建索引.2.唯一性太差的字段不適合單獨創建索引，即使頻繁作為查詢條件.（唯一性太差的字段主要是指哪些呢？如狀態字段，類型字段等等這些字段中的數據可能總共就是那么幾個幾十個數值重復使用）（當一條Query所返回的數據超過了全表的15%的時候，就不應該再使用索引掃描來完成這個Query了）.3.更新非常頻繁的字段不適合創建索引.（因為索引中的字段被更新的時候，不僅僅需要更新表中的數據，同時還要更新索引數據，以確保索引信息是準確的）.4.不會出現在WHERE子句中的字段不該創建索引.如何選擇合適索引?1.對于單鍵索引，盡量選擇針對當前Query過濾性更好的索引.2.選擇聯合索引時,當前Query中過濾性最好的字段在索引字段順序中排列要靠前.3.選擇聯合索引時,盡量索引字段出現在w中比較多的索引.

22.Hash索引有哪些優缺點？

MySQL中索引的常用數據結構有兩種: 一種是B+Tree,另一種則是Hash.

Hash底層實現是由Hash表來實現的，是根據鍵值 <key,value> 存儲數據的結構。非常適合根據key查找value值，也就是單個key查詢，或者說等值查詢。

對于每一行數據，存儲引擎都會對所有的索引列計算一個哈希碼，哈希碼是一個較小的值,如果出現哈希碼值相同的情況會拉出一條鏈表.

Hsah索引的優點

因為索引自身只需要存儲對應的Hash值,所以索引結構非常緊湊, 只需要做等值比較查詢，而不包含排序或范圍查詢的需求，都適合使用哈希索引 .
沒有哈希沖突的情況下,等值查詢訪問哈希索引的數據非常快.(如果發生Hash沖突,存儲引擎必須遍歷鏈表中的所有行指針,逐行進行比較,直到找到所有符合條件的行).

Hash索引的缺點

哈希索引只包含哈希值和行指針，而不存儲字段值，所以不能使用索引中的值來避免讀取行。
哈希索引只支持等值比較查詢。不支持任何范圍查詢和部分索引列匹配查找。
哈希索引數據并不是按照索引值順序存儲的，所以也就無法用于排序。

23.說一下InnoDB內存相關的參數優化？

Buffer Pool參數優化

1.1 緩沖池內存大小配置

一個大的日志緩沖區允許大量的事務在提交之前不寫日志到磁盤。因此，如果你有很多事務的更新，插入或刪除操作，通過設置這個參數會大量的減少磁盤I/O的次數數。
建議: 在專用數據庫服務器上，可以將緩沖池大小設置為服務器物理內存的60% - 80%.

查看緩沖池大小

mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+mysql> select 134217728 / 1024 / 1024;
+-------------------------+
| 134217728 / 1024 / 1024 |
+-------------------------+
|            128.00000000 |
+-------------------------+

在線調整InnoDB緩沖池大小
innodb_buffer_pool_size可以動態設置，允許在不重新啟動服務器的情況下調整緩沖池的大小.

mysql> SET GLOBAL innodb_buffer_pool_size = 268435456; -- 512
Query OK, 0 rows affected (0.10 sec)mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+-------------------------+-----------+

監控在線調整緩沖池的進度

mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+----------------------------------------------------------------------+
| Variable_name                    | Value                                                        |
+----------------------------------+----------------------------------------------------------------------+
| Innodb_buffer_pool_resize_status | Size did not change (old size = new size = 268435456. Nothing to do. |
+----------------------------------+----------------------------------------------------------------------+

1.3 InnoDB 緩存性能評估

當前配置的innodb_buffer_pool_size是否合適，可以通過分析InnoDB緩沖池的緩存命中率來驗證。

以下公式計算InnoDB buffer pool 命中率:

命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests+innodb_buffer_pool_reads)* 100參數1: innodb_buffer_pool_reads：表示InnoDB緩沖池無法滿足的請求數。需要從磁盤中讀取。
參數2: innodb_buffer_pool_read_requests：表示從內存中讀取頁的請求數。

mysql> show status like 'innodb_buffer_pool_read%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd     | 0     |
| Innodb_buffer_pool_read_ahead         | 0     |
| Innodb_buffer_pool_read_ahead_evicted | 0     |
| Innodb_buffer_pool_read_requests      | 12701 |
| Innodb_buffer_pool_reads              | 455   |
+---------------------------------------+-------+-- 此值低于90%，則可以考慮增加innodb_buffer_pool_size。
mysql> select 12701 / (455 + 12701) * 100 ;
+-----------------------------+
| 12701 / (455 + 12701) * 100 |
+-----------------------------+
|                     96.5415 |
+-----------------------------+

1.4 Page管理相關參數

查看Page頁的大小(默認16KB),innodb_page_size只能在初始化MySQL實例之前配置，不能在之后修改。如果沒有指定值，則使用默認頁面大小初始化實例。

mysql> show variables like '%innodb_page_size%'; 
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+

Page頁管理狀態相關參數

mysql> show global status like '%innodb_buffer_pool_pages%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Innodb_buffer_pool_pages_data    | 515   |
| Innodb_buffer_pool_pages_dirty   | 0     |
| Innodb_buffer_pool_pages_flushed | 334   |
| Innodb_buffer_pool_pages_free    | 15868 |
| Innodb_buffer_pool_pages_misc    | 0     |
| Innodb_buffer_pool_pages_total   | 16383 |
+----------------------------------+-------+

pages_data: InnoDB緩沖池中包含數據的頁數。該數字包括臟頁面和干凈頁面。

pages_dirty: 顯示在內存中修改但尚未寫入數據文件的InnoDB緩沖池數據頁的數量（臟頁刷新）。

pages_flushed: 表示從InnoDB緩沖池中刷新臟頁的請求數。

pages_free: 顯示InnoDB緩沖池中的空閑頁面

pages_misc: 緩存池中當前已經被用作管理用途或hash index而不能用作為普通數據頁的數目

pages_total: 緩存池的頁總數目。單位是page。

24.InnoDB日志相關的參數優化了解過嗎？

1.日志緩沖區相關參數配置

日志緩沖區的大小。一般默認值16MB是夠用的，但如果事務之中含有blog/text等大字段，這個緩沖區會被很快填滿會引起額外的IO負載。配置更大的日志緩沖區,可以有效的提高MySQL的效率.

innodb_log_buffer_size 緩沖區大小

mysql> show variables like 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+

innodb_log_files_in_group 日志組文件個數
日志組根據需要來創建。而日志組的成員則需要至少2個，實現循環寫入并作為冗余策略。

mysql> show variables like 'innodb_log_files_in_group';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 2     |
+---------------------------+-------+

innodb_log_file_size 日志文件大小
參數innodb_log_file_size用于設定MySQL日志組中每個日志文件的大小(默認48M)。此參數是一個全局的靜態參數，不能動態修改。
參數innodb_log_file_size的最大值，二進制日志文件大小（innodb_log_file_size * innodb_log_files_in_group）不能超過512GB.所以單個日志文件的大小不能超過256G.

mysql> show variables like 'innodb_log_file_size';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+

2.日志文件參數優化

首先我們先來看一下日志文件大小設置對性能的影響

設置過小
1. 參數 innodb_log_file_size設置太小，就會導致MySQL的日志文件( redo log）頻繁切換，頻繁的觸發數據庫的檢查點（Checkpoint），導致刷新臟頁到磁盤的次數增加。從而影響IO性能。
2. 處理大事務時，將所有的日志文件寫滿了，事務內容還沒有寫完，這樣就會導致日志不能切換.
設置過大
參數 innodb_log_file_size如果設置太大，雖然可以提升IO性能，但是當MySQL由于意外宕機時，二進制日志很大，那么恢復的時間必然很長。而且這個恢復時間往往不可控，受多方面因素影響。

優化建議:

如何設置合適的日志文件大小 ?

根據實際生產場景的優化經驗,一般是計算一段時間內生成的事務日志（redo log）的大小，而MySQL的日志文件的大小最少應該承載一個小時的業務日志量(官網文檔中有說明)。

想要估計一下InnoDB redo log的大小，需要抓取一段時間內Log SequenceNumber（日志順序號）的數據,來計算一小時內產生的日志大小.

Log sequence number

自系統修改開始，就不斷的生成redo日志。為了記錄一共生成了多少日志，于是mysql設計了全局變量log sequence number，簡稱lsn，但不是從0開始，是從8704字節開始。

-- pager分頁工具, 只獲取 sequence的信息
mysql> pager grep sequence;
PAGER set to 'grep sequence'-- 查詢狀態,并倒計時一分鐘
mysql> show engine innodb status\G select sleep(60);
Log sequence number 5399154
1 row in set (0.00 sec)1 row in set (1 min 0.00 sec)-- 一分時間內所生成的數據量 5406150
mysql> show engine innodb status\G;
Log sequence number 5406150-- 關閉pager
mysql> nopager;
PAGER set to stdout

有了一分鐘的日志量,據此推算一小時內的日志量

mysql> select (5406150 - 5399154) / 1024 as kb_per_min;
+------------+
| kb_per_min |
+------------+
|     6.8320 |
+------------+mysql> select (5406150 - 5399154) / 1024 * 60 as kb_per_min;
+------------+
| kb_per_min |
+------------+
|   409.9219 |
+------------+

太大的緩沖池或非常不正常的業務負載可能會計算出非常大(或非常小)的日志大小。這也是公式不足之處，需要根據判斷和經驗。但這個計算方法是一個很好的參考標準。

25.InnoDB IO線程相關參數優化了解過嗎？

數據庫屬于 IO 密集型的應用程序，其主要職責就是數據的管理及存儲工作。從內存中讀取一個數據庫數據的時間是微秒級別，而從一塊普通硬盤上讀取一個IO是在毫秒級別。要優化數據庫，IO操作是必須要優化的，盡可能將磁盤IO轉化為內存IO。

1) 參數: query_cache_size&have_query_cache
MySQL查詢緩存會保存查詢返回的完整結果。當查詢命中該緩存，會立刻返回結果，跳過了解析，優化和執行階段。
查詢緩存會跟蹤查詢中涉及的每個表，如果這些表發生變化，那么和這個表相關的所有緩存都將失效。

查看查詢緩存是否開啟

-- 查詢是否支持查詢緩存
mysql> show variables like 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+-- 查詢是否開啟查詢緩存 默認關閉
mysql> show variables like '%query_cache_type%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+

開啟緩存,在my.ini中添加下面一行參數

query_cache_size=128M
query_cache_type=1query_cache_type:
設置為0，OFF,緩存禁用
設置為1，ON,緩存所有的結果
設置為2，DENAND,只緩存在select語句中通過SQL_CACHE指定需要緩存的查詢

測試能否緩存查詢

  mysql> show status like '%Qcache%';+-------------------------+---------+| Variable_name           | Value   |+-------------------------+---------+| Qcache_free_blocks      | 1       || Qcache_free_memory      | 1031832 || Qcache_hits             | 0       || Qcache_inserts          | 0       || Qcache_lowmem_prunes    | 0       || Qcache_not_cached       | 1       || Qcache_queries_in_cache | 0       || Qcache_total_blocks     | 1       |+-------------------------+---------+

Qcache_free_blocks:緩存中目前剩余的blocks數量（如果值較大，則查詢緩存中的內存碎片過多）
Qcache_free_memory:空閑緩存的內存大小
Qcache_hits:命中緩存次數
Qcache_inserts: 未命中然后進行正常查詢
Qcache_lowmem_prunes:查詢因為內存不足而被移除出查詢緩存記錄
Qcache_not_cached: 沒有被緩存的查詢數量
Qcache_queries_in_cache:當前緩存中緩存的查詢數量
Qcache_total_blocks:當前緩存的block數量

優化建議: Query Cache的使用需要多個參數配合，其中最為關鍵的是 query_cache_size 和 query_cache_type ，前者設置用于緩存 ResultSet 的內存大小，后者設置在何場景下使用 Query Cache。

MySQL數據庫數據變化相對不多，query_cache_size 一般設置為256MB比較合適 ,也可以通過計算Query Cache的命中率來進行調整

( Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100) )

參數: innodb_max_dirty_pages_pct 該參數是InnoDB 存儲引擎用來控制buffer pool中臟頁的百分比，當臟頁數量占比超過這個參數設置的值時，InnoDB會啟動刷臟頁的操作。

-- innodb_max_dirty_pages_pct 參數可以動態調整，最小值為0， 最大值為99.99，默認值為 75。
mysql> show variables like 'innodb_max_dirty_pages_pct';
+----------------------------+-----------+
| Variable_name              | Value     |
+----------------------------+-----------+
| innodb_max_dirty_pages_pct | 75.000000 |
+----------------------------+-----------+

優化建議: 該參數比例值越大，從內存到磁盤的寫入操作就會相對減少，所以能夠一定程度下減少寫入操作的磁盤IO。但是，如果這個比例值過大，當數據庫 Crash 之后重啟的時間可能就會很長，因為會有大量的事務數據需要從日志文件恢復出來寫入數據文件中.最大不建議超過90,一般重啟恢復的數據在超過1GB的話,啟動速度就會變慢.

3) 參數: innodb_old_blocks_pct&innodb_old_blocks_time
innodb_old_blocks_pct 用來確定LRU鏈表中old sublist所占比例,默認占用37%

mysql> show variables like '%innodb_old_blocks_pct%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37    |
+-----------------------+-------+

innodb_old_blocks_time 用來控制old sublist中page的轉移策略，新的page頁在進入LRU鏈表中時，會先插入到old sublist的頭部，然后page需要在old sublist中停留innodb_old_blocks_time這么久后，下一次對該page的訪問才會使其移動到new sublist的頭部，默認值1秒.

mysql> show variables like '%innodb_old_blocks_time%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_old_blocks_time | 1000  |
+------------------------+-------+

優化建議: 在沒有大表掃描的情況下，并且數據多為頻繁使用的數據時，我們可以增加innodb_old_blocks_pct的值，并且減小innodb_old_blocks_time的值。讓數據頁能夠更快和更多的進入的熱點數據區。

26.什么是寫失效？

InnoDB的頁和操作系統的頁大小不一致，InnoDB頁大小一般為16K，操作系統頁大小為4K，InnoDB的頁寫入到磁盤時，一個頁需要分4次寫。

如果存儲引擎正在寫入頁的數據到磁盤時發生了宕機，可能出現頁只寫了一部分的情況，比如只寫了4K，就宕機了，這種情況叫做部分寫失效（partial page write），可能會導致數據丟失。

雙寫緩沖區 Doublewrite Buffer

為了解決寫失效問題，InnoDB實現了double write buffer Files, 它位于系統表空間，是一個存儲區域。

在BufferPool的page頁刷新到磁盤真正的位置前，會先將數據存在Doublewrite 緩沖區。這樣在宕機重啟時，如果出現數據頁損壞，那么在應用redo log之前，需要通過該頁的副本來還原該頁，然后再進行redo log重做，double write實現了InnoDB引擎數據頁的可靠性.

默認情況下啟用雙寫緩沖區，如果要禁用Doublewrite 緩沖區，可以將 innodb_doublewrite設置為0。

mysql> show variables like '%innodb_doublewrite%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_doublewrite | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

數據雙寫流程

step1：當進行緩沖池中的臟頁刷新到磁盤的操作時,并不會直接寫磁盤,每次臟頁刷新必須要先寫double write .
step2：通過memcpy函數將臟頁復制到內存中的double write buffer .
step3: double write buffer再分兩次、每次1MB, 順序寫入共享表空間的物理磁盤上, 第一次寫.
step4: 在完成double write頁的寫入后，再將double wirite buffer中的頁寫入各個表的獨立表空間文件中(數據文件 .ibd), 第二次寫。

為什么寫兩次 ?

可能有的同學會有疑問，為啥寫兩次，刷一次數據文件保存數據不就可以了，為什么還要寫共享表空間 ?其實是因為共享表空間是在ibdbata文件中劃出2M連續的空間，專門給double write刷臟頁用的, 由于在這個過程中，double write頁的存儲是連續的，因此寫入磁盤為順序寫，性能很高；完成double write后，再將臟頁寫入實際的各個表空間文件，這時寫入就是離散的了.

27.什么是行溢出？

行記錄格式

1) 行格式分類

表的行格式決定了它的行是如何物理存儲的，這反過來又會影響查詢和DML操作的性能。如果在單個page頁中容納更多行，查詢和索引查找可以更快地工作，緩沖池中所需的內存更少，寫入更新時所需的I/O更少。

InnoDB存儲引擎支持四種行格式：Redundant、Compact、Dynamic 和 Compressed .

查詢MySQL使用的行格式,默認為: dynamic

mysql> show variables like 'innodb_default_row_format';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+

指定行格式語法

CREATE TABLE <table_name(column_name)> ROW_FORMAT=行格式名稱
ALTER TABLE <table_name> ROW_FORMAT=行格式名稱

2) COMPACT 行記錄格式

Compact 設計目標是高效地存儲數據，一個頁中存放的行數據越多，其性能就越高。

Compact行記錄由兩部分組成: 記錄放入額外信息和記錄的真實數據.

記錄額外信息部分

服務器為了描述一條記錄而添加了一些額外信息(元數據信息)，這些額外信息分為3類，分別是: 變長字段長度列表、NULL值列表和記錄頭信息.

變長字段長度列表MySQL支持一些變長的數據類型，比如VARCHAR(M)、VARBINARY(M)、各種TEXT類型，各種BLOB類型，這些變長的數據類型占用的存儲空間分為兩部分：

變長字段的長度是不固定的，所以在存儲數據的時候要把這些數據占用的字節數也存起來，讀取數據的時候才能根據這個長度列表去讀取對應長度的數據。

在 Compact行格式中，把所有變長類型的列的長度都存放在記錄的開頭部位形成一個列表，按照列的順序逆序存放,這個列表就是 變長字段長度列表。

1. 真正的數據內容
2. 占用的字節數

NULL值列表表中的某些列可能會存儲NULL值，如果把這些NULL值都放到記錄的真實數據中會比較浪費空間，所以Compact行格式把這些值為NULL的列存儲到NULL值列表中。( 如果表中所有列都不允許為 NULL，就不存在NULL值列表 )
記錄頭信息記錄頭信息是由固定的5個字節組成，5個字節也就是40個二進制位，不同的位代表不同的意思，這些頭信息會在后面的一些功能中看到。

名稱	大小(單位:bit)	描述
預留位1	1	沒有使用
預留位2	1	沒有使用
delete_mask	1	標記該記錄是否被刪除
min_rec_mask	1	標記該記錄是否是本層B+樹的非葉子節點中的最小記錄
n_owned	4	表示當前分組中管理的記錄數
heap_no	13	表示當前記錄在記錄堆中的位置信息
record_type	3	表示當前記錄的類型: 0 表示普通記錄, 1 表示B+樹非葉子節點記錄, 2 表示最小記錄,3表示最大記錄
next_record	16	表示下一條記錄的相對位置

1. delete_mask這個屬性標記著當前記錄是否被刪除，占用1個二進制位，值為0 的時候代表記錄并沒有被刪除，為1 的時候代表記錄被刪除掉了
2. min_rec_maskB+樹的每層非葉子節點中的最小記錄都會添加該標記。
3. n_owned代表每個分組里，所擁有的記錄的數量，一般是分組里主鍵最大值才有的。
4. heap_no在數據頁的User Records中插入的記錄是一條一條緊湊的排列的，這種緊湊排列的結構又被稱為堆。為了便于管理這個堆，把記錄在堆中的相對位置給定一個編號——heap_no。所以heap_no這個屬性表示當前記錄在本頁中的位置。
5. record_type這個屬性表示當前記錄的類型，一共有4種類型的記錄， 0 表示普通用戶記錄， 1 表示B+樹非葉節點記錄， 2 表示最小記錄， 3 表示最大記錄。
6. next_record表示從當前記錄的真實數據到下一條記錄的真實數據的地址偏移量，可以理解為指向下一條記錄地址的指針。值為正數說明下一條記錄在當前記錄后面，為負數說明下一條記錄在當前記錄的前面。

記錄真實數據部分記錄的真實數據除了插入的那些列的數據，MySQL會為每個記錄默認的添加一些列（也稱為隱藏列），具體的列如下：

列名	是否必須	占用空間	描述
row_id	否	6字節	行ID,唯一標識一條記錄
transaction_id	是	6字節	事務ID
roll_pointer	是	7字節	回滾指針

生成隱藏主鍵列的方式有:

3) Compact中的行溢出機制

什么是行溢出 ?

MySQL中是以頁為基本單位,進行磁盤與內存之間的數據交互的,我們知道一個頁的大小是16KB,16KB = 16384字節.而一個varchar(m) 類型列最多可以存儲65532個字節,一些大的數據類型比如TEXT可以存儲更多.

如果一個表中存在這樣的大字段,那么一個頁就無法存儲一條完整的記錄.這時就會發生行溢出,多出的數據就會存儲在另外的溢出頁中.

總結: 如果某些字段信息過長，無法存儲在B樹節點中，這時候會被單獨分配空間，此時被稱為溢出頁，該字段被稱為頁外列。

Compact中的行溢出機制

InnoDB 規定一頁至少存儲兩條記錄(B+樹特點)，如果頁中只能存放下一條記錄，InnoDB存儲引擎會自動將行數據存放到溢出頁中.
當發生行溢出時，數據頁只保存了前768字節的前綴數據，接著是20個字節的偏移量，指向行溢出頁.

1. 服務器會在內存中維護一個全局變量，每當向某個包含隱藏的row_id列的表中插入一條記錄時，就會把該變量的值當作新記錄的row_id列的值，并且把該變量自增1。
2. 每當這個變量的值為256的倍數時，就會將該變量的值刷新到系統表空間的頁號為7的頁面中一個Max Row ID的屬性處。
3. 當系統啟動時，會將頁中的Max Row ID屬性加載到內存中，并將該值加上256之后賦值給全局變量，因為在上次關機時該全局變量的值可能大于頁中Max Row ID屬性值。4.

28.如何進行JOIN優化？

JOIN 是 MySQL 用來進行聯表操作的，用來匹配兩個表的數據，篩選并合并出符合我們要求的結果集。

JOIN 操作有多種方式，取決于最終數據的合并效果。常用連接方式的有以下幾種:

什么是驅動表 ?

多表關聯查詢時,第一個被處理的表就是驅動表,使用驅動表去關聯其他表.
驅動表的確定非常的關鍵,會直接影響多表關聯的順序,也決定后續關聯查詢的性能

驅動表的選擇要遵循一個規則:

在對最終的結果集沒有影響的前提下,優先選擇結果集最小的那張表作為驅動表

3) 三種JOIN算法

1.Simple Nested-Loop Join（簡單的嵌套循環連接 )

簡單來說嵌套循環連接算法就是一個雙層for 循環，通過循環外層表的行數據，逐個與內層表的所有行數據進行比較來獲取結果.
這種算法是最簡單的方案，性能也一般。對內循環沒優化。
例如有這樣一條SQL:

-- 連接用戶表與訂單表 連接條件是 u.id = o.user_id
select * from user t1 left join order t2 on t1.id = t2.user_id;
-- user表為驅動表,order表為被驅動表

轉換成代碼執行時的思路是這樣的:

for(user表行 uRow : user表){for(Order表的行 oRow : order表){if(uRow.id = oRow.user_id){return uRow;}}
}

匹配過程如下圖
SNL 的特點
- 簡單粗暴容易理解，就是通過雙層循環比較數據來獲得結果
- 查詢效率會非常慢,假設 A 表有 N 行，B 表有 M 行。SNL 的開銷如下：
  - A 表掃描 1 次。
  - B 表掃描 M 次。
  - 一共有 N 個內循環，每個內循環要 M 次，一共有內循環 N * M 次

2) Index Nested-Loop Join（索引嵌套循環連接）

Index Nested-Loop Join 其優化的思路: 主要是為了減少內層表數據的匹配次數 , 最大的區別在于，用來進行 join 的字段已經在被驅動表中建立了索引。
從原來的 匹配次數 = 外層表行數 * 內層表行數 , 變成了 匹配次數 = 外層表的行數 * 內層表索引的高度 ，極大的提升了 join的性能。
當 order 表的 user_id 為索引的時候執行過程會如下圖：

注意：使用Index Nested-Loop Join 算法的前提是匹配的字段必須建立了索引。

3) Block Nested-Loop Join( 塊嵌套循環連接 )

如果 join 的字段有索引，MySQL 會使用 INL 算法。如果沒有的話，MySQL 會如何處理？

因為不存在索引了，所以被驅動表需要進行掃描。這里 MySQL 并不會簡單粗暴的應用 SNL 算法，而是加入了 buffer 緩沖區，降低了內循環的個數，也就是被驅動表的掃描次數。
外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳

在外層循環掃描 user表中的所有記錄。掃描的時候，會把需要進行 join 用到的列都緩存到 buffer 中。buffer 中的數據有一個特點，里面的記錄不需要一條一條地取出來和 order 表進行比較，而是整個 buffer 和 order表進行批量比較。
如果我們把 buffer 的空間開得很大，可以容納下 user 表的所有記錄，那么 order 表也只需要訪問一次。
MySQL 默認 buffer 大小 256K，如果有 n 個 join 操作，會生成 n-1 個 join buffer。

mysql> show variables like '%join_buffer%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+mysql> set session join_buffer_size=262144;
Query OK, 0 rows affected (0.00 sec)

4) JOIN優化總結

永遠用小結果集驅動大結果集(其本質就是減少外層循環的數據數量)
為匹配的條件增加索引(減少內層表的循環匹配次數)
增大join buffer size的大小（一次緩存的數據越多，那么內層包的掃表次數就越少）
減少不必要的字段查詢（字段越少，join buffer 所緩存的數據就越多

29.索引哪些情況下會失效？

查詢條件包含 or，會導致索引失效。
隱式類型轉換，會導致索引失效，例如 age 字段類型是 int，我們 where age = “1”，這樣就會觸發隱式類型轉換
like 通配符會導致索引失效，注意:”ABC%” 不會失效，會走 range 索引，”% ABC” 索引會失效
聯合索引，查詢時的條件列不是聯合索引中的第一個列，索引失效。
對索引字段進行函數運算。
對索引列運算（如，+、-、*、/），索引失效。
索引字段上使用（!= 或者 < >，not in）時，會導致索引失效。
索引字段上使用 is null， is not null，可能導致索引失效。
相 join 的兩個表的字符編碼不同，不能命中索引，會導致笛卡爾積的循環計算
mysql 估計使用全表掃描要比使用索引快，則不使用索引。

30.什么是覆蓋索引？

覆蓋索引是一種避免回表查詢的優化策略: 只需要在一棵索引樹上就能獲取SQL所需的所有列數據，無需回表，速度更快。

具體的實現方式:

將被查詢的字段建立普通索引或者聯合索引，這樣的話就可以直接返回索引中的的數據，不需要再通過聚集索引去定位行記錄，避免了回表的情況發生。

EXPLAIN SELECT user_name,user_age,user_level FROM users 
WHERE user_name = 'tom' AND user_age = 17;

覆蓋索引的定義與注意事項:

如果一個索引包含了所有需要查詢的字段的值 (不需要回表)，這個索引就是覆蓋索引。
MySQL只能使用B+Tree索引做覆蓋索引 (因為只有B+樹能存儲索引列值)
在explain的Extra列, 如果出現 **Using index 表示使用到了覆蓋索引 , 所取的數據完全在索引中就能拿到

31.介紹一下MySQL中事務的特性？

在關系型數據庫管理系統中，一個邏輯工作單元要成為事務，必須滿足這 4 個特性，即所謂的 ACID：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）和持久性（Durability）。

1）原子性

原子性：事務作為一個整體被執行，包含在其中的對數據庫的操作要么全部被執行，要么都不執行。

InnoDB存儲引擎提供了兩種事務日志：redo log(重做日志)和undo log(回滾日志)。其中redo log用于保證事務持久性；undo log則是事務原子性和隔離性實現的基礎。

每寫一個事務,都會修改Buffer Pool,從而產生相應的Redo/Undo日志:

如果要回滾事務，那么就基于undo log來回滾就可以了，把之前對緩存頁做的修改都給回滾了就可以了。
如果事務提交之后，redo log刷入磁盤，結果MySQL宕機了，是可以根據redo log恢復事務修改過的緩存數據的。

實現原子性的關鍵，是當事務回滾時能夠撤銷所有已經成功執行的sql語句。

InnoDB 實現回滾，靠的是undo log ：當事務對數據庫進行修改時，InnoDB 會生成對應的undo log ；如果事務執行失敗或調用了rollback ，導致事務需要回滾，便可以利用undo log中的信息將數據回滾到修改之前的樣子。

2）一致性

一致性：事務應確保數據庫的狀態從一個一致狀態轉變為另一個一致狀態。_一致狀態_的含義是數據庫中的數據應滿足完整性約束。

約束一致性：創建表結構時所指定的外鍵、唯一索引等約束。
數據一致性：是一個綜合性的規定，因為它是由原子性、持久性、隔離性共同保證的結果，而不是單單依賴于某一種技術。

3）隔離性

隔離性：指的是一個事務的執行不能被其他事務干擾，即一個事務內部的操作及使用的數據對其他的并發事務是隔離的。

不考慮隔離性會引發的問題:

臟讀 : 一個事務讀取到了另一個事務修改但未提交的數據。
不可重復讀: 一個事務中多次讀取同一行記錄的結果不一致，后面讀取的跟前面讀取的結果不一致。
幻讀 : 一個事務中多次按相同條件查詢，結果不一致。后續查詢的結果和面前查詢結果不同，多了或少了幾行記錄。

數據庫事務的隔離級別有4個，由低到高依次為Read uncommitted 、Read committed、Repeatable read 、Serializable ，這四個級別可以逐個解決臟讀、不可重復讀、幻讀這幾類問題。

4）持久性

持久性：指的是一個事務一旦提交，它對數據庫中數據的改變就應該是永久性的，后續的操作或故障不應該對其有任何影響，不會丟失。

MySQL 事務的持久性保證依賴的日志文件: redo log

redo log 也包括兩部分：一是內存中的日志緩沖(redo log buffer)，該部分日志是易失性的；二是磁盤上的重做日志文件(redo log file)，該部分日志是持久的。redo log是物理日志，記錄的是數據庫中物理頁的情況。
當數據發生修改時，InnoDB不僅會修改Buffer Pool中的數據，也會在redo log buffer記錄這次操作；當事務提交時，會對redo log buffer進行刷盤，記錄到redo log file中。如果MySQL宕機，重啟時可以讀取redo log file中的數據，對數據庫進行恢復。這樣就不需要每次提交事務都實時進行刷臟了。

5）ACID總結

事務的持久化是為了應對系統崩潰造成的數據丟失.
只有保證了事務的一致性，才能保證執行結果的正確性
在非并發狀態下，事務間天然保證隔離性，因此只需要保證事務的原子性即可保證一致性.
在并發狀態下，需要嚴格保證事務的原子性、隔離性。

32.MySQL 的可重復讀怎么實現的？

可重復讀（repeatable read）定義：一個事務執行過程中看到的數據，總是跟這個事務在啟動時看到的數據是一致的。

MVCC

MVCC，多版本并發控制, 用于實現讀已提交和可重復讀隔離級別。
MVCC的核心就是 Undo log多版本鏈 + Read view，“MV”就是通過 Undo log來保存數據的歷史版本，實現多版本的管理，“CC”是通過 Read-view來實現管理，通過 Read-view原則來決定數據是否顯示。同時針對不同的隔離級別， Read view的生成策略不同，也就實現了不同的隔離級別。

Undo log 多版本鏈

每條數據都有兩個隱藏字段:

trx_id: 事務id,記錄最近一次更新這條數據的事務id.
roll_pointer: 回滾指針,指向之前生成的undo log

每一條數據都有多個版本,版本之間通過undo log鏈條進行連接通過這樣的設計方式,可以保證每個事務提交的時候,一旦需要回滾操作,可以保證同一個事務只能讀取到比當前版本更早提交的值,不能看到更晚提交的值。

ReadView

Read View是 InnoDB 在實現 MVCC 時用到的一致性讀視圖，即 consistent read view，用于支持 RC（Read Committed，讀提交）和 RR（Repeatable Read，可重復讀）隔離級別的實現.

Read View簡單理解就是對數據在某個時刻的狀態拍成照片記錄下來。那么之后獲取某時刻的數據時就還是原來的照片上的數據，是不會變的.

Read View中比較重要的字段有4個:

m_ids : 用來表示MySQL中哪些事務正在執行,但是沒有提交.
min_trx_id: 就是m_ids里最小的值.
max_trx_id : 下一個要生成的事務id值,也就是最大事務id
creator_trx_id: 就是你這個事務的id

當一個事務第一次執行查詢sql時，會生成一致性視圖 read-view（快照），查詢時從 undo log 中最新的一條記錄開始跟 read-view 做對比，如果不符合比較規則，就根據回滾指針回滾到上一條記錄繼續比較，直到得到符合比較條件的查詢結果。

Read View判斷記錄某個版本是否可見的規則如下

1.如果當前記錄的事務id落在綠色部分（trx_id < min_id），表示這個版本是已提交的事務生成的，可讀。
2.如果當前記錄的事務id落在紅色部分（trx_id > max_id），表示這個版本是由將來啟動的事務生成的，不可讀。

如果當前記錄的事務id落在黃色部分（min_id <= trx_id <= max_id），則分為兩種情況：
若當前記錄的事務id在未提交事務的數組中，則此條記錄不可讀；
若當前記錄的事務id不在未提交事務的數組中，則此條記錄可讀。

RC 和 RR 隔離級別都是由 MVCC 實現，區別在于：

RC 隔離級別時，read-view 是每次執行 select 語句時都生成一個；
RR 隔離級別時，read-view 是在第一次執行 select 語句時生成一個，同一事務中后面的所有 select 語句都復用這個 read-view 。

33.Repeatable Read 解決了幻讀問題嗎？

可重復讀（repeatable read）定義：一個事務執行過程中看到的數據，總是跟這個事務在啟動時看到的數據是一致的。

不過理論上會出現幻讀，簡單的說幻讀指的的當用戶讀取某一范圍的數據行時，另一個事務又在該范圍插入了新行，當用戶在讀取該范圍的數據時會發現有新的幻影行。

注意在可重復讀隔離級別下，普通的查詢是快照讀，是不會看到別的事務插入的數據的。因此，幻讀在“當前讀”下才會出現（查詢語句添加for update，表示當前讀）；

在 MVCC 并發控制中，讀操作可以分為兩類: 快照讀（Snapshot Read）與當前讀（Current Read）。

快照讀
快照讀是指讀取數據時不是讀取最新版本的數據，而是基于歷史版本讀取的一個快照信息（mysql讀取undo log歷史版本) ，快照讀可以使普通的SELECT 讀取數據時不用對表數據進行加鎖，從而解決了因為對數據庫表的加鎖而導致的兩個如下問題
1. 解決了因加鎖導致的修改數據時無法對數據讀取問題.
2. 解決了因加鎖導致讀取數據時無法對數據進行修改的問題.
當前讀
當前讀是讀取的數據庫最新的數據，當前讀和快照讀不同，因為要讀取最新的數據而且要保證事務的隔離性，所以當前讀是需要對數據進行加鎖的（插入/更新/刪除操作，屬于當前讀，需要加鎖 , select for update 為當前讀）

表結構

id	key	value
0	0	0
1	1	1

假設 select * from where value=1 for update，只在這一行加鎖（注意這只是假設），其它行不加鎖，那么就會出現如下場景：

Session A的三次查詢Q1-Q3都是select * from where value=1 for update，查詢的value=1的所有row。

T1：Q1只返回一行(1,1,1)；
T2：session B更新id=0的value為1，此時表t中value=1的數據有兩行
T3：Q2返回兩行(0,0,1),(1,1,1)
T4：session C插入一行(6,6,1)，此時表t中value=1的數據有三行
T5：Q3返回三行(0,0,1),(1,1,1),(6,6,1)
T6：session A事物commit。

其中Q3讀到value=1這一樣的現象，就稱之為幻讀，幻讀指的是一個事務在前后兩次查詢同一個范圍的時候，后一次查詢看到了前一次查詢沒有看到的行。

先對“幻讀”做出如下解釋：

要討論「可重復讀」隔離級別的幻讀現象，是要建立在「當前讀」的情況下，而不是快照讀,因為在可重復讀隔離級別下，普通的查詢是快照讀，是不會看到別的事務插入的數據的。

Next-key Lock 鎖

產生幻讀的原因是，行鎖只能鎖住行，但是新插入記錄這個動作，要更新的是記錄之間的“間隙”。因此，Innodb 引擎為了解決「可重復讀」隔離級別使用「當前讀」而造成的幻讀問題，就引出了 next-key 鎖，就是記錄鎖和間隙鎖的組合。

RecordLock鎖：鎖定單個行記錄的鎖。（記錄鎖，RC、RR隔離級別都支持）
GapLock鎖：間隙鎖，鎖定索引記錄間隙(不包括記錄本身)，確保索引記錄的間隙不變。（范圍鎖，RR隔離級別支持）
Next-key Lock 鎖：記錄鎖和間隙鎖組合，同時鎖住數據，并且鎖住數據前后范圍。（記錄鎖+范圍鎖，RR隔離級別支持）

總結

RR隔離級別下間隙鎖才有效，RC隔離級別下沒有間隙鎖；
RR隔離級別下為了解決“幻讀”問題：“快照讀”依靠MVCC控制，“當前讀”通過間隙鎖解決；
間隙鎖和行鎖合稱next-key lock，每個next-key lock是前開后閉區間；
間隙鎖的引入，可能會導致同樣語句鎖住更大的范圍，影響并發度。

34.請說一下數據庫鎖的種類？

MySQL數據庫由于其自身架構的特點,存在多種數據存儲引擎, MySQL中不同的存儲引擎支持不同的鎖機制。

MyISAM和MEMORY存儲引擎采用的表級鎖，
InnoDB存儲引擎既支持行級鎖，也支持表級鎖，默認情況下采用行級鎖。
BDB采用的是頁面鎖，也支持表級鎖

按照數據操作的類型分

讀鎖（共享鎖）：針對同一份數據，多個讀操作可以同時進行而不會互相影響。
寫鎖（排他鎖）：當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。

按照數據操作的粒度分

表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的概率最高，并發度最低。
行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度也最高。
頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般

按照操作性能可分為樂觀鎖和悲觀鎖

樂觀鎖：一般的實現方式是對記錄數據版本進行比對，在數據更新提交的時候才會進行沖突檢測，如果發現沖突了，則提示錯誤信息。
悲觀鎖：在對一條數據修改的時候，為了避免同時被其他人修改，在修改數據之前先鎖定，再修改的控制方式。共享鎖和排他鎖是悲觀鎖的不同實現，但都屬于悲觀鎖范疇。

35.請說一下共享鎖和排他鎖？

行級鎖分為共享鎖和排他鎖兩種。

行鎖的是mysql鎖中粒度最小的一種鎖，因為鎖的粒度很小，所以發生資源爭搶的概率也最小，并發性能最大，但是也會造成死鎖，每次加鎖和釋放鎖的開銷也會變大。

使用MySQL行級鎖的兩個前提

使用 innoDB 引擎
開啟事務 (隔離級別為 Repeatable Read)

InnoDB行鎖的類型

共享鎖（S）：當事務對數據加上共享鎖后, 其他用戶可以并發讀取數據，但任何事務都不能對數據進行修改（獲取數據上的排他鎖），直到已釋放所有共享鎖。
排他鎖（X）：如果事務T對數據A加上排他鎖后，則其他事務不能再對數據A加任任何類型的封鎖。獲準排他鎖的事務既能讀數據，又能修改數據。

加鎖的方式

InnoDB引擎默認更新語句，update,delete,insert 都會自動給涉及到的數據加上排他鎖，select語句默認不會加任何鎖類型，如果要加可以使用下面的方式:
加共享鎖（S）：select * from table_name where … lock in share mode;
加排他鎖（x）：select * from table_name where … for update;

鎖兼容

共享鎖只能兼容共享鎖, 不兼容排它鎖
排它鎖互斥共享鎖和其它排它鎖

36.InnoDB 的行鎖是怎么實現的？

InnoDB行鎖是通過對索引數據頁上的記錄加鎖實現的，主要實現算法有 3 種：Record Lock、Gap Lock 和 Next-key Lock。

RecordLock鎖：鎖定單個行記錄的鎖。（記錄鎖，RC、RR隔離級別都支持）
GapLock鎖：間隙鎖，鎖定索引記錄間隙，確保索引記錄的間隙不變。（范圍鎖，RR隔離級別支持）
Next-key Lock 鎖：記錄鎖和間隙鎖組合，同時鎖住數據，并且鎖住數據前后范圍。（記錄鎖+范圍鎖，RR隔離級別支持）

注意： InnoDB這種行鎖實現特點意味著：只有通過索引條件檢索數據，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖

在RR隔離級別，InnoDB對于記錄加鎖行為都是先采用Next-Key Lock，但是當SQL操作含有唯一索引時，Innodb會對Next-Key Lock進行優化，降級為RecordLock，僅鎖住索引本身而非范圍。

各種操作加鎖的特點

1）select … from 語句：InnoDB引擎采用MVCC機制實現非阻塞讀，所以對于普通的select語句，InnoDB不加鎖

2）select … from lock in share mode語句：追加了共享鎖，InnoDB會使用Next-Key Lock鎖進行處理，如果掃描發現唯一索引，可以降級為RecordLock鎖。

3）select … from for update語句：追加了排他鎖，InnoDB會使用Next-Key Lock鎖進行處理，如果掃描發現唯一索引，可以降級為RecordLock鎖。

4）update … where 語句：InnoDB會使用Next-Key Lock鎖進行處理，如果掃描發現唯一索引，可以降級為RecordLock鎖。

5）delete … where 語句：InnoDB會使用Next-Key Lock鎖進行處理，如果掃描發現唯一索引，可以降級為RecordLock鎖。

6）insert語句：InnoDB會在將要插入的那一行設置一個排他的RecordLock鎖。

下面以“update t1 set name=‘lisi’ where id=10”操作為例，舉例子分析下 InnoDB 對不同索引的加鎖行為，以RR隔離級別為例。

主鍵加鎖加鎖行為：僅在id=10的主鍵索引記錄上加X鎖。
唯一鍵加鎖加鎖行為：現在唯一索引id上加X鎖，然后在id=10的主鍵索引記錄上加X鎖。
非唯一鍵加鎖加鎖行為：對滿足id=10條件的記錄和主鍵分別加X鎖，然后在(6,c)-(10,b)、(10,b)-(10,d)、(10,d)-(11,f)范圍分別加Gap Lock。

無索引加鎖加鎖行為：表里所有行和間隙都會加X鎖。（當沒有索引時，會導致全表鎖定，因為InnoDB引擎鎖機制是基于索引實現的記錄鎖定）。

37.并發事務會產生哪些問題

事務并發處理可能會帶來一些問題，如下：

更新丟失
當兩個或多個事務更新同一行記錄，會產生更新丟失現象。可以分為回滾覆蓋和提交覆蓋。
- 回滾覆蓋：一個事務回滾操作，把其他事務已提交的數據給覆蓋了。
- 提交覆蓋：一個事務提交操作，把其他事務已提交的數據給覆蓋了。
臟讀
一個事務讀取到了另一個事務修改但未提交的數據。
不可重復讀
一個事務中多次讀取同一行記錄不一致，后面讀取的跟前面讀取的不一致。
幻讀
一個事務中多次按相同條件查詢，結果不一致。后續查詢的結果和面前查詢結果不同，多了或少了幾行記錄。

“更新丟失”、”臟讀”、“不可重復讀”和“幻讀”等并發事務問題，其實都是數據庫一致性問題，為了解決這些問題，MySQL數據庫是通過事務隔離級別來解決的，數據庫系統提供了以下 4 種事務隔離級別供用戶選擇。

讀未提交
Read Uncommitted 讀未提交：解決了回滾覆蓋類型的更新丟失，但可能發生臟讀現象，也就是可能讀取到其他會話中未提交事務修改的數據。
已提交讀
Read Committed 讀已提交：只能讀取到其他會話中已經提交的數據，解決了臟讀。但可能發生不可重復讀現象，也就是可能在一個事務中兩次查詢結果不一致。
可重復度
Repeatable Read 可重復讀：解決了不可重復讀，它確保同一事務的多個實例在并發讀取數據時，會看到同樣的數據行。不過理論上會出現幻讀，簡單的說幻讀指的的當用戶讀取某一范圍的數據行時，另一個事務又在該范圍插入了新行，當用戶在讀取該范圍的數據時會發現有新的幻影行。
可串行化
所有的增刪改查串行執行。它通過強制事務排序，解決相互沖突，從而解決幻度的問題。這個級別可能導致大量的超時現象的和鎖競爭，效率低下。

數據庫的事務隔離級別越高，并發問題就越小，但是并發處理能力越差（代價）。讀未提交隔離級別最低，并發問題多，但是并發處理能力好。以后使用時，可以根據系統特點來選擇一個合適的隔離級別，比如對不可重復讀和幻讀并不敏感，更多關心數據庫并發處理能力，此時可以使用Read Commited隔離級別。

事務隔離級別，針對Innodb引擎，支持事務的功能。像MyISAM引擎沒有關系。

事務隔離級別和鎖的關系

1）事務隔離級別是SQL92定制的標準，相當于事務并發控制的整體解決方案，本質上是對鎖和MVCC使用的封裝，隱藏了底層細節。

2）鎖是數據庫實現并發控制的基礎，事務隔離性是采用鎖來實現，對相應操作加不同的鎖，就可以防止其他事務同時對數據進行讀寫操作。

3）對用戶來講，首先選擇使用隔離級別，當選用的隔離級別不能解決并發問題或需求時，才有必要在開發中手動的設置鎖。

MySQL默認隔離級別：可重復讀

Oracle、SQLServer默認隔離級別：讀已提交

一般使用時，建議采用默認隔離級別，然后存在的一些并發問題，可以通過悲觀鎖、樂觀鎖等實現處理。

38.說一下MVCC內部細節

MVCC概念

MVCC（Multi Version Concurrency Control）被稱為多版本并發控制，是指在數據庫中為了實現高并發的數據訪問，對數據進行多版本處理，并通過事務的可見性來保證事務能看到自己應該看到的數據版本。

MVCC最大的好處是讀不加鎖，讀寫不沖突。在讀多寫少的系統應用中，讀寫不沖突是非常重要的，極大的提升系統的并發性能，這也是為什么現階段幾乎所有的關系型數據庫都支持 MVCC 的原因，不過目前MVCC只在 Read Commited 和 Repeatable Read 兩種隔離級別下工作。

回答這個面試題時，主要介紹以下的幾個關鍵內容：

1）行記錄的三個隱藏字段

DB_ROW_ID : 如果沒有為表顯式的定義主鍵，并且表中也沒有定義唯一索引，那么InnoDB會自動為表添加一個row_id的隱藏列作為主鍵。
DB_TRX_ID : 事務中對某條記錄做增刪改時,就會將這個事務的事務ID寫入到trx_id中.
DB_ROLL_PTR: 回滾指針,指向undo log的指針

2）Undo log 多版本鏈

舉例：事務 T-100 和 T-120 對表中 id = 1 的數據行做 update 操作，事務 T-130 進行 select 操作，即使 T-100 已經提交修改，三次 select 語句的結果都是“lisi”。

每一條數據都有多個版本,版本之間通過undo log鏈條進行連接

3）ReadView

Read View簡單理解就是對數據在每個時刻的狀態拍成照片記錄下來。那么之后獲取某時刻的數據時就還是原來的照片上的數據，是不會變的.

Read View中比較重要的字段有4個:

m_ids : 用來表示MySQL中哪些事務正在執行,但是沒有提交.
min_trx_id: 就是m_ids里最小的值.
max_trx_id : 下一個要生成的事務id值,也就是最大事務id
creator_trx_id: 就是你這個事務的id

通過Read View判斷記錄的某個版本是否可見的方式總結:

trx_id = creator_trx_id
如果被訪問版本的trx_id,與readview中的creator_trx_id值相同,表明當前事務在訪問自己修改過的記錄,該版本可以被當前事務訪問.
trx_id < min_trx_id
如果被訪問版本的trx_id,小于readview中的min_trx_id值,表明生成該版本的事務在當前事務生成readview前已經提交,該版本可以被當前事務訪問.
trx_id >= max_trx_id
如果被訪問版本的trx_id,大于或等于readview中的max_trx_id值,表明生成該版本的事務在當前事務生成readview后才開啟,該版本不可以被當前事務訪問.
trx_id > min_trx_id && trx_id < max_trx_id
如果被訪問版本的trx_id,值在readview的min_trx_id和max_trx_id之間，就需要判斷trx_id屬性值是不是在m_ids列表中？
- 在：說明創建readview時生成該版本的事務還是活躍的,該版本不可以被訪問
- 不在：說明創建readview時生成該版本的事務已經被提交,該版本可以被訪問

何時生成ReadView快照

在 讀已提交（Read Committed，簡稱RC） 隔離級別下，每一次讀取數據前都生成一個ReadVIew。
在 可重復讀 （Repeatable Read，簡稱RR）隔離級別下，在一個事務中，只在 第一次讀取數據前生成一個ReadVIew。

4）快照讀（Snapshot Read）與當前讀（Current Read）

在 MVCC 并發控制中，讀操作可以分為兩類: 快照讀（Snapshot Read）與當前讀（Current Read）。

快照讀
快照讀是指讀取數據時不是讀取最新版本的數據，而是基于歷史版本讀取的一個快照信息（mysql讀取undo log歷史版本) ，快照讀可以使普通的SELECT 讀取數據時不用對表數據進行加鎖，從而解決了因為對數據庫表的加鎖而導致的兩個如下問題
1. 解決了因加鎖導致的修改數據時無法對數據讀取問題.
2. 解決了因加鎖導致讀取數據時無法對數據進行修改的問題.
當前讀
當前讀是讀取的數據庫最新的數據，當前讀和快照讀不同，因為要讀取最新的數據而且要保證事務的隔離性，所以當前讀是需要對數據進行加鎖的（Update delete insert select ....lock in share mode , select for update 為當前讀）

總結一下

并發環境下，寫-寫操作有加鎖解決方案，但為了提高性能，InnoDB存儲引擎提供MVCC，目的是為了解決讀-寫，寫-讀操作下不加鎖仍能安全進行。
MVCC的過程，本質就是訪問版本鏈，并判斷哪個版本可見的過程。該判斷算法是通過版本上的trx_id與快照ReadView的若干個信息進行對比。
快照生成的時機因隔離級別不同，讀已提交隔離級別下，每一次讀取前都會生成一個快照ReadView；而可重復讀則僅在一個事務中，第一次讀取前生成一個快照。

39.說一下MySQL死鎖的原因和處理方法

1) 表的死鎖

產生原因:

用戶A訪問表A（鎖住了表A），然后又訪問表B；另一個用戶B訪問表B（鎖住了表B），然后企圖訪問表A；這時用戶A由于用戶B已經鎖住表B，它必須等待用戶B釋放表B才能繼續，同樣用戶B要等用戶A釋放表A才能繼續，這就死鎖就產生了。

用戶A–》A表（表鎖）–》B表（表鎖）

用戶B–》B表（表鎖）–》A表（表鎖）

解決方案：

這種死鎖比較常見，是由于程序的BUG產生的，除了調整的程序的邏輯沒有其它的辦法。

仔細分析程序的邏輯，對于數據庫的多表操作時，盡量按照相同的順序進行處理，盡量避免同時鎖定兩個資源，如操作A和B兩張表時，總是按先A后B的順序處理，必須同時鎖定兩個資源時，要保證在任何時刻都應該按照相同的順序來鎖定資源。

2) 行級鎖死鎖

產生原因1：

如果在事務中執行了一條沒有索引條件的查詢，引發全表掃描，把行級鎖上升為全表記錄鎖定（等價于表級鎖），多個這樣的事務執行后，就很容易產生死鎖和阻塞，最終應用系統會越來越慢，發生阻塞或死鎖。

解決方案1：

SQL語句中不要使用太復雜的關聯多表的查詢；使用explain“執行計劃"對SQL語句進行分析，對于有全表掃描和全表鎖定的SQL語句，建立相應的索引進行優化。

產生原因2：

兩個事務分別想拿到對方持有的鎖，互相等待，于是產生死鎖

產生原因3：每個事務只有一個SQL,但是有些情況還是會發生死鎖.

事務1,從name索引出發 , 讀到的[hdc, 1], [hdc, 6]均滿足條件, 不僅會加name索引上的記錄X鎖, 而且會加聚簇索引上的記錄X鎖, 加鎖順序為先[1,hdc,100], 后[6,hdc,10]
事務2，從pubtime索引出發，[10,6],[100,1]均滿足過濾條件，同樣也會加聚簇索引上的記錄X鎖，加鎖順序為[6,hdc,10]，后[1,hdc,100]。
但是加鎖時發現跟事務1的加鎖順序正好相反，兩個Session恰好都持有了第一把鎖，請求加第二把鎖，死鎖就發生了。

解決方案: 如上面的原因2和原因3, 對索引加鎖順序的不一致很可能會導致死鎖，所以如果可以，盡量以相同的順序來訪問索引記錄和表。在程序以批量方式處理數據的時候，如果事先對數據排序，保證每個線程按固定的順序來處理記錄，也可以大大降低出現死鎖的可能；

40.介紹一下MySQL的體系架構？

MySQL Server架構自頂向下大致可以分網絡連接層、服務層、存儲引擎層和系統文件層。

一、網絡連接層

客戶端連接器（Client Connectors）：提供與MySQL服務器建立的支持。目前幾乎支持所有主流的服務端編程技術，例如常見的 Java、C、Python、.NET等，它們通過各自API技術與MySQL建立連接。

二、服務層（MySQL Server）

服務層是MySQL Server的核心，主要包含系統管理和控制工具、連接池、SQL接口、解析器、查詢優化器和緩存六個部分。

連接池（Connection Pool）：負責存儲和管理客戶端與數據庫的連接，一個線程負責管理一個連接。
系統管理和控制工具（Management Services & Utilities）：例如備份恢復、安全管理、集群管理等
SQL接口（SQL Interface）：用于接受客戶端發送的各種SQL命令，并且返回用戶需要查詢的結果。比如DML、DDL、存儲過程、視圖、觸發器等。
解析器（Parser）：負責將請求的SQL解析生成一個"解析樹"。然后根據一些MySQL規則進一步檢查解析樹是否合法。
查詢優化器（Optimizer）：當“解析樹”通過解析器語法檢查后，將交由優化器將其轉化成執行計劃，然后與存儲引擎交互。

select uid,name from user where gender=1;

選取–》投影–》聯接策略

1）select先根據where語句進行選取，并不是查詢出全部數據再過濾

2）select查詢根據uid和name進行屬性投影，并不是取出所有字段

3）將前面選取和投影聯接起來最終生成查詢結果

緩存（Cache&Buffer）：緩存機制是由一系列小緩存組成的。比如表緩存，記錄緩存，權限緩存，引擎緩存等。如果查詢緩存有命中的查詢結果，查詢語句就可以直接去查詢緩存中取數據。

三、存儲引擎層（Pluggable Storage Engines）

存儲引擎負責MySQL中數據的存儲與提取，與底層系統文件進行交互。MySQL存儲引擎是插件式的，服務器中的查詢執行引擎通過接口與存儲引擎進行通信，接口屏蔽了不同存儲引擎之間的差異。現在有很多種存儲引擎，各有各的特點，最常見的是MyISAM和InnoDB。

四、系統文件層（File System）

該層負責將數據庫的數據和日志存儲在文件系統之上，并完成與存儲引擎的交互，是文件的物理存儲層。主要包含日志文件，數據文件，配置文件，pid 文件，socket 文件等。

日志文件
- 錯誤日志（Error log）
  默認開啟，show variables like ‘%log_error%’
- 通用查詢日志（General query log）
  記錄一般查詢語句，show variables like ‘%general%’;
- 二進制日志（binary log）
  記錄了對MySQL數據庫執行的更改操作，并且記錄了語句的發生時間、執行時長；但是它不記錄select、show等不修改數據庫的SQL。主要用于數據庫恢復和主從復制。
  show variables like ‘%log_bin%’; //是否開啟
  show variables like ‘%binlog%’; //參數查看
  show binary logs;//查看日志文件
- 慢查詢日志（Slow query log）
  記錄所有執行時間超時的查詢SQL，默認是10秒。
  show variables like ‘%slow_query%’; //是否開啟
  show variables like ‘%long_query_time%’; //時長
配置文件
用于存放MySQL所有的配置信息文件，比如my.cnf、my.ini等。
數據文件
- db.opt 文件：記錄這個庫的默認使用的字符集和校驗規則。
- frm 文件：存儲與表相關的元數據（meta）信息，包括表結構的定義信息等，每一張表都會有一個frm 文件。
- MYD 文件：MyISAM 存儲引擎專用，存放 MyISAM 表的數據（data)，每一張表都會有一個 .MYD 文件。
- MYI 文件：MyISAM 存儲引擎專用，存放 MyISAM 表的索引相關信息，每一張 MyISAM 表對應一個 .MYI 文件。
- ibd文件和 IBDATA 文件：存放 InnoDB 的數據文件（包括索引）。InnoDB 存儲引擎有兩種表空間方式：獨享表空間和共享表空間。獨享表空間使用 .ibd 文件來存放數據，且每一張 InnoDB 表對應一個 .ibd 文件。共享表空間使用 .ibdata 文件，所有表共同使用一個（或多個，自行配置）.ibdata 文件。
- ibdata1 文件：系統表空間數據文件，存儲表元數據、Undo日志等。
- ib_logfile0、ib_logfile1 文件：Redo log 日志文件。
pid 文件
pid 文件是 mysqld 應用程序在 Unix/Linux 環境下的一個進程文件，和許多其他 Unix/Linux 服務端程序一樣，它存放著自己的進程 id。
socket 文件
socket 文件也是在 Unix/Linux 環境下才有的，用戶在 Unix/Linux 環境下客戶端連接可以不通過 TCP/IP 網絡而直接使用 Unix Socket 來連接 MySQL。

41.undo log、redo log、 bin log的作用是什么？

undo log 基本概念

undo log是一種用于撤銷回退的日志，在數據庫事務開始之前，MySQL會先記錄更新前的數據到 undo log日志文件里面，當事務回滾時或者數據庫崩潰時，可以利用 undo log來進行回退。
Undo Log產生和銷毀：Undo Log在事務開始前產生；事務在提交時，并不會立刻刪除undo log，innodb會將該事務對應的undo log放入到刪除列表中，后面會通過后臺線程purge thread進行回收處理。

注意: undo log也會產生redo log，因為undo log也要實現持久性保護。

undo log的作用

提供回滾操作【undo log實現事務的原子性】
在數據修改的時候，不僅記錄了redo log，還記錄了相對應的undo log，如果因為某些原因導致事務執行失敗了，可以借助undo log進行回滾。
_undo log 和 redo log 記錄物理日志不一樣，它是_邏輯日志。可以認為當delete一條記錄時，undo log中會記錄一條對應的insert記錄，反之亦然，當update一條記錄時，它記錄一條對應相反的update記錄。
提供多版本控制(MVCC)【undo log實現多版本并發控制（MVCC）】
MVCC，即多版本控制。在MySQL數據庫InnoDB存儲引擎中，用undo Log來實現多版本并發控制(MVCC)。當讀取的某一行被其他事務鎖定時，它可以從undo log中分析出該行記錄以前的數據版本是怎樣的，從而讓用戶能夠讀取到當前事務操作之前的數據【快照讀】。

redo log 基本概念

InnoDB引擎對數據的更新，是先將更新記錄寫入redo log日志，然后會在系統空閑的時候或者是按照設定的更新策略再將日志中的內容更新到磁盤之中。這就是所謂的預寫式技術（Write Ahead logging）。這種技術可以大大減少IO操作的頻率，提升數據刷新的效率。
redo log：被稱作重做日志, 包括兩部分：一個是內存中的日志緩沖： redo log buffer，另一個是磁盤上的日志文件： redo log file 。

redo log的作用

mysql 每執行一條 DML 語句，先將記錄寫入 redo log buffer 。后續某個時間點再一次性將多個操作記錄寫到 redo log file 。當故障發生致使內存數據丟失后，InnoDB會在重啟時，經過重放 redo，將Page恢復到崩潰之前的狀態 通過Redo log可以實現事務的持久性。

bin log基本概念

binlog是一個二進制格式的文件，用于記錄用戶對數據庫更新的SQL語句信息，例如更改數據庫表和更改內容的SQL語句都會記錄到binlog里，但是不會記錄SELECT和SHOW這類操作。
binlog在MySQL的Server層實現(引擎共用)
binlog為邏輯日志,記錄的是一條SQL語句的原始邏輯
- binlog不限制大小,追加寫入,不會覆蓋以前的日志.
- 默認情況下，binlog日志是二進制格式的，不能使用查看文本工具的命令（比如，cat，vi等）查看，而使用mysqlbinlog解析查看。

bin log的作用

主從復制：在主庫中開啟Binlog功能，這樣主庫就可以把Binlog傳遞給從庫，從庫拿到Binlog后實現數據恢復達到主從數據一致性。
數據恢復：通過mysqlbinlog工具來恢復數據。

42.redo log與undo log的持久化策略？

redo log持久化

緩沖區數據一般情況下是無法直接寫入磁盤的，中間必須經過操作系統緩沖區( OS Buffer )。因此， redo log buffer 寫入 redo logfile 實際上是先寫入 OS Buffer，然后再通過系統調用 fsync() 將其刷到 redo log file.

Redo Buffer 持久化到 redo log 的策略，可通過 Innodb_flush_log_at_trx_commit 設置：

參數值	含義
0 (延遲寫)	事務提交時不會將 `redo log buffer`中日志寫入到 `os buffer`，而是每秒寫入 `os buffer`并調用 `fsync()`寫入到 `redo log file`中。也就是說設置為0時是(大約)每秒刷新寫入到磁盤中的，當系統崩潰，會丟失1秒鐘的數據。
1 (實時寫,實時刷)	事務每次提交都會將 `redo log buffer`中的日志寫入 `os buffer`并調用 `fsync()`刷到 `redo log file`中。這種方式即使系統崩潰也不會丟失任何數據，但是因為每次提交都寫入磁盤，IO的性能較差。
2 (實時寫, 延時刷)	每次提交都僅寫入到 `os buffer`，然后是每秒調用 `fsync()`將 `os buffer`中的日志寫入到 `redo log file`。

一般建議選擇取值2，因為 MySQL 掛了數據沒有損失，整個服務器掛了才會損失1秒的事務提交數據

undo log持久化

MySQL中的Undo Log嚴格的講不是Log，而是數據，因此他的管理和落盤都跟數據是一樣的：

Undo的磁盤結構并不是順序的，而是像數據一樣按Page管理
Undo寫入時，也像數據一樣產生對應的Redo Log (因為undo也是對頁面的修改，記錄undo這個操作本身也會有對應的redo)。
Undo的Page也像數據一樣緩存在Buffer Pool中，跟數據Page一起做LRU換入換出，以及刷臟。Undo Page的刷臟也像數據一樣要等到對應的Redo Log 落盤之后

當事務提交的時候，innodb不會立即刪除undo log，因為后續還可能會用到undo log，如隔離級別為repeatable read時，事務讀取的都是開啟事務時的最新提交行版本，只要該事務不結束，該行版本就不能刪除，即undo log不能刪除。

但是在事務提交的時候，會將該事務對應的undo log放入到刪除列表中，未來通過purge來刪除。并且提交事務時，還會判斷undo log分配的頁是否可以重用，如果可以重用，則會分配給后面來的事務，避免為每個獨立的事務分配獨立的undo log頁而浪費存儲空間和性能。

43.bin log與undo log的區別？

1）redo log是InnoDB引擎特有的；binlog是MySQL的Server層實現的，所有引擎都可以使用。

2）redo log是物理日志，記錄的是“在XXX數據頁上做了XXX修改”；binlog是邏輯日志，記錄的是原始邏輯，其記錄是對應的SQL語句。

物理日志: 記錄的是每一個page頁中具體存儲的值是多少，在這個數據頁上做了什么修改. 比如: 某個事物將系統表空間中的第100個頁面中偏移量為1000處的那個字節的值1改為2.
邏輯日志: 記錄的是每一個page頁面中具體數據是怎么變動的，它會記錄一個變動的過程或SQL語句的邏輯, 比如: 把一個page頁中的一個數據從1改為2，再從2改為3,邏輯日志就會記錄1->2,2->3這個數據變化的過程.

3）redo log是循環寫的，空間一定會用完，需要write pos和check point搭配；binlog是追加寫，寫到一定大小會切換到下一個，并不會覆蓋以前的日志

Redo Log 文件內容是以順序循環的方式寫入文件，寫滿時則回溯到第一個文件，進行覆蓋寫。

write pos: 表示日志當前記錄的位置，當ib_logfile_4寫滿后，會從ib_logfile_1從頭開始記錄；
check point: 表示將日志記錄的修改寫進磁盤，完成數據落盤，數據落盤后checkpoint會將日志上的相關記錄擦除掉，即 write pos -> checkpoint 之間的部分是redo log空著的部分，用于記錄新的記錄，checkpoint -> write pos 之間是redo log 待落盤的數據修改記錄
如果 write pos 追上 checkpoint，表示寫滿，這時候不能再執行新的更新，得停下來先擦掉一些記錄，把 checkpoint 推進一下。

3）Redo Log作為服務器異常宕機后事務數據自動恢復使用，Binlog可以作為主從復制和數據恢復使用。Binlog沒有自動crash-safe能力

CrashSafe指MySQL服務器宕機重啟后，能夠保證：

所有已經提交的事務的數據仍然存在。
所有沒有提交的事務的數據自動回滾。

44.MySQL的binlog有幾種日志格式？分別有什么區別？

binlog日志有三種模式

1）ROW（row-based replication, RBR）：日志中會記錄每一行數據被修改的情況，然后在slave端對相同的數據進行修改。

優點：能清楚記錄每一個行數據的修改細節，能完全實現主從數據同步和數據的恢復。而且不會出現某些特定情況下存儲過程或function無法被正確復制的問題。
缺點：批量操作，會產生大量的日志，尤其是alter table會讓日志量暴漲。

2）STATMENT（statement-based replication, SBR）：記錄每一條修改數據的SQL語句（批量修改時，記錄的不是單條SQL語句，而是批量修改的SQL語句事件）, slave在復制的時候SQL進程會解析成和原來master端執行過的相同的SQL再次執行。簡稱SQL語句復制。

優點：日志量小，減少磁盤IO，提升存儲和恢復速度
缺點：在某些情況下會導致主從數據不一致，比如last_insert_id()、now()等函數。

3）MIXED（mixed-based replication, MBR）：以上兩種模式的混合使用，一般會使用STATEMENT模式保存binlog，對于STATEMENT模式無法復制的操作使用ROW模式保存binlog，MySQL會根據執行的SQL語句選擇寫入模式。

企業場景如何選擇binlog的模式

如果生產中使用MySQL的特殊功能相對少（存儲過程、觸發器、函數）。選擇默認的語句模式，Statement。
如果生產中使用MySQL的特殊功能較多的，可以選擇Mixed模式。
如果生產中使用MySQL的特殊功能較多，又希望數據最大化一致，此時最好Row 模式；但是要注意，該模式的binlog日志量增長非常快.

45.mysql 線上修改大表結構有哪些風險？

在線修改大表的可能影響

在線修改大表的表結構執行時間往往不可預估，一般時間較長。
由于修改表結構是表級鎖，因此在修改表結構時，影響表寫入操作。
如果長時間的修改表結構，中途修改失敗，由于修改表結構是一個事務，因此失敗后會還原表結構，在這個過程中表都是鎖著不可寫入。
修改大表結構容易導致數據庫CPU、IO等性能消耗，使MySQL服務器性能降低。
在線修改大表結構容易導致主從延時，從而影響業務讀取。

修改方式：

對表加鎖(表此時只讀)
復制原表物理結構
修改表的物理結構
把原表數據導入中間表中，數據同步完后，**鎖定中間表，并刪除原表
rename中間表為原表
刷新數據字典，并釋放鎖

使用工具： online-schema-change ，是percona推出的一個針對mysql在線ddl的工具。percona是一個mysql分支維護公司，專門提供mysql技術服務的。

46.count(列名)、count(1)和 count(*)有什么區別?

進行統計操作時,count中的統計條件可以三種選擇:

EXPLAIN  SELECT COUNT(*) FROM user;EXPLAIN  SELECT COUNT(列名) FROM user;EXPLAIN  SELECT COUNT(1) FROM user;

執行效果上：

count(*) 包括了所有的列,在統計時不會忽略列值為null的數據。
count(1) 用1表示代碼行,在統計時,不會忽略列值為null的數據。
count(列名)在統計時,會忽略列值為空的數據,就是說某個字段的值為null時不統計。

執行效率上：

InnoDB引擎：count（字段) < count(1) = count(*)
- InnoDB通過遍歷最小的可用二級索引來處理select count(*) 語句，除非索引或優化器提示指示優化器使用不同的索引。如果二級索引不存在，則通過掃描聚集索引來處理。
- InnoDB已同樣的方式處理count(1)和count(*)
MyISAM引擎：count（字段) < count(1) <= count(*)
- MyISAM存儲了數據的準確行數，使用 count(*)會直接讀取該行數，只有當第一列定義為NOT NULL時，count（1），才會執行該操作，所以優先選擇 count(*)
count(列名) 會遍歷整個表，但不同的是，它會先獲取列，然后判斷是否為空，然后累加，因此count(列名)性能不如前兩者。

注意：count(*)，這是SQL92 定義的標準統計行數的語法，跟數據庫無關，與NULL也無關。而count(列名) 是統計列值數量，不計NULL，相同列值算一個。

47.什么是分庫分表？什么時候進行分庫分表？

什么是分庫分表

簡單來說，就是指通過某種特定的條件，將我們存放在同一個數據庫中的數據分散存放到多個數據庫（主機）上面，以達到分散單臺設備負載的效果。

分庫分表解決的問題分庫分表的目的是為了解決由于數據量過大而導致數據庫性能降低的問題，將原來單體服務的數據庫進行拆分.將數據大表拆分成若干數據表組成，使得單一數據庫、單一數據表的數據量變小，從而達到提升數據庫性能的目的。
什么情況下需要分庫分表
- 單機存儲容量遇到瓶頸.
- 連接數,處理能力達到上限.

注意:

分庫分表之前,要根據項目的實際情況確定我們的數據量是不是夠大,并發量是不是夠大,來決定是否分庫分表.

數據量不夠就不要分表,單表數據量超過1000萬或100G的時候, 速度就會變慢(官方測試),

分庫分表包括：垂直分庫、垂直分表、水平分庫、水平分表四種方式。

垂直分庫

數據庫中不同的表對應著不同的業務，垂直切分是指按照業務的不同將表進行分類,分布到不同的數據庫上面

- 將數據庫部署在不同服務器上，從而達到多個服務器共同分攤壓力的效果

垂直分表

表中字段太多且包含大字段的時候，在查詢時對數據庫的IO、內存會受到影響，同時更新數據時，產生的binlog文件會很大，MySQL在主從同步時也會有延遲的風險

將一個表按照字段分成多表，每個表存儲其中一部分字段。
對職位表進行垂直拆分, 將職位基本信息放在一張表, 將職位描述信息存放在另一張表

垂直拆分帶來的一些提升
- 解決業務層面的耦合，業務清晰
- 能對不同業務的數據進行分級管理、維護、監控、擴展等
- 高并發場景下，垂直分庫一定程度的提高訪問性能
垂直拆分沒有徹底解決單表數據量過大的問題

水平分庫

將單張表的數據切分到多個服務器上去，每個服務器具有相應的庫與表，只是表中數據集合不同。水平分庫分表能夠有效的緩解單機和單庫的性能瓶頸和壓力，突破IO、連接數、硬件資源等的瓶頸.
簡單講就是根據表中的數據的邏輯關系，將同一個表中的數據按照某種條件拆分到多臺數據庫（主機）上面, 例如將訂單表按照id是奇數還是偶數, 分別存儲在不同的庫中。

水平分表

針對數據量巨大的單張表（比如訂單表），按照規則把一張表的數據切分到多張表里面去。但是這些表還是在同一個庫中，所以庫級別的數據庫操作還是有IO瓶頸。
總結
- 垂直分表: 將一個表按照字段分成多表，每個表存儲其中一部分字段。
- 垂直分庫: 根據表的業務不同,分別存放在不同的庫中,這些庫分別部署在不同的服務器.
- 水平分庫: 把一張表的數據按照一定規則,分配到不同的數據庫,每一個庫只有這張表的部分數據.
- 水平分表: 把一張表的數據按照一定規則,分配到同一個數據庫的多張表中,每個表只有這個表的部分數據.

48.說說 MySQL 的主從復制？

主從復制的用途

實時災備，用于故障切換
讀寫分離，提供查詢服務
備份，避免影響業務

主從部署必要條件

主庫開啟binlog日志（設置log-bin參數）
主從server-id不同
從庫服務器能連通主庫

主從復制的原理

Mysql 中有一種日志叫做 bin 日志（二進制日志）。這個日志會記錄下所有修改了數據庫的SQL 語句（insert,update,delete,create/alter/drop table, grant 等等）。
主從復制的原理其實就是把主服務器上的 bin 日志復制到從服務器上執行一遍，這樣從服務器上的數據就和主服務器上的數據相同了。

主庫db的更新事件(update、insert、delete)被寫到binlog
主庫創建一個binlog dump thread，把binlog的內容發送到從庫
從庫啟動并發起連接，連接到主庫
從庫啟動之后，創建一個I/O線程，讀取主庫傳過來的binlog內容并寫入到relay log
從庫啟動之后，創建一個SQL線程，從relay log里面讀取內容，執行讀取到的更新事件，將更新內容寫入到slave的db

49. 說一下 MySQL 執行一條查詢語句的內部執行過程？

①建立連接（Connectors&Connection Pool），通過客戶端/服務器通信協議與MySQL建立連接。MySQL 客戶端與服務端的通信方式是 “ 半雙工 ”。對于每一個 MySQL 的連接，時刻都有一個線程狀態來標識這個連接正在做什么。
通訊機制：

線程狀態：
show processlist; //查看用戶正在運行的線程信息，root用戶能查看所有線程，其他用戶只能看自己的

- 全雙工：能同時發送和接收數據，例如平時打電話。
- 半雙工：指的某一時刻，要么發送數據，要么接收數據，不能同時。例如早期對講機
- 單工：只能發送數據或只能接收數據。例如單行道
- id：線程ID，可以使用kill xx；
- user：啟動這個線程的用戶
- Host：發送請求的客戶端的IP和端口號
- db：當前命令在哪個庫執行
- Command：該線程正在執行的操作命令* Create DB：正在創建庫操作* Drop DB：正在刪除庫操作* Execute：正在執行一個PreparedStatement* Close Stmt：正在關閉一個PreparedStatement* Query：正在執行一個語句* Sleep：正在等待客戶端發送語句* Quit：正在退出* Shutdown：正在關閉服務器
- Time：表示該線程處于當前狀態的時間，單位是秒
- State：線程狀態* Updating：正在搜索匹配記錄，進行修改* Sleeping：正在等待客戶端發送新請求* Starting：正在執行請求處理* Checking table：正在檢查數據表* Closing table : 正在將表中數據刷新到磁盤中* Locked：被其他查詢鎖住了記錄* Sending Data：正在處理Select查詢，同時將結果發送給客戶端
- Info：一般記錄線程執行的語句，默認顯示前100個字符。想查看完整的使用show full processlist;

②查詢緩存（Cache&Buffer），這是MySQL的一個可優化查詢的地方，如果開啟了查詢緩存且在查詢緩存過程中查詢到完全相同的SQL語句，則將查詢結果直接返回給客戶端；如果沒有開啟查詢緩存或者沒有查詢到完全相同的 SQL 語句則會由解析器進行語法語義解析，并生成“解析樹”。
- 緩存Select查詢的結果和SQL語句
- 執行Select查詢時，先查詢緩存，判斷是否存在可用的記錄集，要求是否完全相同（包括參數值），這樣才會匹配緩存數據命中。
- 即使開啟查詢緩存，以下SQL也不能緩存
  - 查詢語句使用SQL_NO_CACHE
  - 查詢的結果大于query_cache_limit設置
  - 查詢中有一些不確定的參數，比如now()
- show variables like ‘%query_cache%’; //查看查詢緩存是否啟用，空間大小，限制等
- show status like ‘Qcache%’; //查看更詳細的緩存參數，可用緩存空間，緩存塊，緩存多少等
③解析器（Parser）將客戶端發送的SQL進行語法解析，生成"解析樹"。預處理器根據一些MySQL規則進一步檢查“解析樹”是否合法，例如這里將檢查數據表和數據列是否存在，還會解析名字和別名，看看它們是否有歧義，最后生成新的“解析樹”。
④查詢優化器（Optimizer）根據“解析樹”生成最優的執行計劃。MySQL使用很多優化策略生成最優的執行計劃，可以分為兩類：靜態優化（編譯時優化）、動態優化（運行時優化）。
- 等價變換策略
  - 5=5 and a>5 改成 a > 5
  - a < b and a=5 改成b>5 and a=5
  - 基于聯合索引，調整條件位置等
- 優化count、min、max等函數
  - InnoDB引擎min函數只需要找索引最左邊
  - InnoDB引擎max函數只需要找索引最右邊
  - MyISAM引擎count(*)，不需要計算，直接返回
- 提前終止查詢
  - 使用了limit查詢，獲取limit所需的數據，就不在繼續遍歷后面數據
- in的優化
  - MySQL對in查詢，會先進行排序，再采用二分法查找數據。比如where id in (2,1,3)，變成 in (1,2,3)
⑤查詢執行引擎負責執行 SQL 語句，此時查詢執行引擎會根據 SQL 語句中表的存儲引擎類型，以及對應的API接口與底層存儲引擎緩存或者物理文件的交互，得到查詢結果并返回給客戶端。若開啟用查詢緩存，這時會將SQL 語句和結果完整地保存到查詢緩存（Cache&Buffer）中，以后若有相同的 SQL 語句執行則直接返回結果。
- 如果開啟了查詢緩存，先將查詢結果做緩存操作
- 返回結果過多，采用增量模式返回