一、MySQL 架構
和其它資料庫相比，MySQL 有點與眾不同，它的架構可以在多種不同場景中應用併發揮良好作用。主要體現在儲存引擎的架構上，外掛式的儲存引擎架構將查詢處理和其它的系統任務以及資料的儲存提取相分離。這種架構可以根據業務的需求和實際需要選擇合適的儲存引擎。
連線層：最上層是一些客戶端和連線服務。主要完成一些類似於連線處理、授權認證、及相關的安全方案。在該層上引入了執行緒池的概念，為通過認證安全接入的客戶端提供執行緒。同樣在該層上可以實現基於 SSL 的安全連結。伺服器也會為安全接入的每個客戶端驗證它所具有的操作許可權。
服務層：第二層服務層，主要完成大部分的核心服務功能， 包括查詢解析、分析、優化、快取、以及所有的內建函式，所有跨儲存引擎的功能也都在這一層實現，包括觸發器、儲存過程、檢視等
引擎層：第三層儲存引擎層，儲存引擎真正的負責了 MySQL 中資料的儲存和提取，伺服器通過 API 與儲存引擎進行通訊。不同的儲存引擎具有的功能不同，這樣我們可以根據自己的實際需要進行選取
儲存層：第四層為資料儲存層，主要是將資料儲存在執行於該裝置的檔案系統之上，並完成與儲存引擎的互動
❝
畫出 MySQL 架構圖，這種變態問題都能問的出來
MySQL 的查詢流程具體是？or 一條 SQL 語句在 MySQL 中如何執行的？
客戶端請求 —> 聯結器（驗證使用者身份，給予許可權） —> 查詢快取（存在快取則直接返回，不存在則執行後續操作） —> 分析器（對 SQL 進行詞法分析和語法分析操作） —> 優化器（主要對執行的 sql 優化選擇最優的執行方案方法） —> 執行器（執行時會先看使用者是否有執行許可權，有才去使用這個引擎提供的介面） —> 去引擎層獲取資料返回（如果開啟查詢快取則會快取查詢結果）圖：極客時間
❝
說說 MySQL 有哪些儲存引擎？都有哪些區別？
二、儲存引擎
儲存引擎是 MySQL 的元件，用於處理不同表型別的 SQL 操作。不同的儲存引擎提供不同的儲存機制、索引技巧、鎖定水平等功能，使用不同的儲存引擎，還可以獲得特定的功能。
使用哪一種引擎可以靈活選擇，一個資料庫中多個表可以使用不同引擎以滿足各種效能和實際需求，使用合適的儲存引擎，將會提高整個資料庫的效能 。
MySQL 伺服器使用可插拔的儲存引擎體系結構，可以從執行中的 MySQL 伺服器載入或解除安裝儲存引擎 。
檢視儲存引擎
-- 檢視支援的儲存引擎
SHOW ENGINES
-- 檢視預設儲存引擎
SHOW VARIABLES LIKE 'storage_engine'
--檢視具體某一個表所使用的儲存引擎，這個預設儲存引擎被修改了！
show create table tablename
--準確檢視某個資料庫中的某一表所使用的儲存引擎
show table status like 'tablename'
show table status from database where
設定儲存引擎
-- 建表時指定儲存引擎。預設的就是INNODB，不需要設定
CREATE TABLE t1 (i INT) ENGINE = INNODB;
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;
-- 修改儲存引擎
ALTER TABLE t ENGINE = InnoDB;
-- 修改預設儲存引擎，也可以在配置檔案my.cnf中修改預設引擎
SET default_storage_engine=NDBCLUSTER;
預設情況下，每當 CREATE TABLE 或 ALTER TABLE 不能使用預設儲存引擎時，都會生成一個警告。為了防止在所需的引擎不可用時出現令人困惑的意外行為，可以啟用 NO_ENGINE_SUBSTITUTION SQL 模式。如果所需的引擎不可用，則此設定將產生錯誤而不是警告，並且不會建立或更改表
儲存引擎對比
常見的儲存引擎就 InnoDB、MyISAM、Memory、NDB。
InnoDB 現在是 MySQL 預設的儲存引擎，支援事務、行級鎖定和外來鍵
檔案儲存結構對比
在 MySQL 中建立任何一張資料表，在其資料目錄對應的資料庫目錄下都有對應表的 .frm 檔案，.frm 檔案是用來儲存每個資料表的後設資料 (meta) 資訊，包括表結構的定義等，與資料庫儲存引擎無關，也就是任何儲存引擎的資料表都必須有.frm檔案，命名方式為 資料表名. frm，如 user.frm。
檢視 MySQL 資料儲存在哪裡：show variables like 'data%'
MyISAM 物理檔案結構為：
.frm檔案：與表相關的後設資料資訊都存放在 frm 檔案，包括表結構的定義資訊等.MYD(MYData) 檔案：MyISAM 儲存引擎專用，用於儲存 MyISAM 表的資料.MYI(MYIndex) 檔案：MyISAM 儲存引擎專用，用於儲存 MyISAM 表的索引相關資訊
InnoDB 物理檔案結構為：
.frm檔案：與表相關的後設資料資訊都存放在 frm 檔案，包括表結構的定義資訊等.ibd檔案或.ibdata檔案：這兩種檔案都是存放 InnoDB 資料的檔案，之所以有兩種檔案形式存放 InnoDB 的資料，是因為 InnoDB 的資料儲存方式能夠通過配置來決定是使用共享表空間存放儲存資料，還是用獨享表空間存放儲存資料。獨享表空間儲存方式使用
.ibd檔案，並且每個表一個.ibd檔案 共享表空間儲存方式使用.ibdata檔案，所有表共同使用一個.ibdata檔案（或多個，可自己配置）
❝
ps：正經公司，這些都有專業運維去做，資料備份、恢復啥的，讓我一個 Javaer 搞這的話，加錢不？
面試這麼回答
InnoDB 支援事務，MyISAM 不支援事務。這是 MySQL 將預設儲存引擎從 MyISAM 變成 InnoDB 的重要原因之一；
InnoDB 支援外來鍵，而 MyISAM 不支援。對一個包含外來鍵的 InnoDB 錶轉為 MYISAM 會失敗；
InnoDB 是聚簇索引，MyISAM 是非聚簇索引。聚簇索引的檔案存放在主鍵索引的葉子節點上，因此 InnoDB 必須要有主鍵，通過主鍵索引效率很高。但是輔助索引需要兩次查詢，先查詢到主鍵，然後再通過主鍵查詢到資料。因此，主鍵不應該過大，因為主鍵太大，其他索引也都會很大。而 MyISAM 是非聚集索引，資料檔案是分離的，索引儲存的是資料檔案的指標。主鍵索引和輔助索引是獨立的。
InnoDB 不儲存表的具體行數，執行
select count(*) from table時需要全表掃描。而 MyISAM 用一個變數儲存了整個表的行數，執行上述語句時只需要讀出該變數即可，速度很快；InnoDB 最小的鎖粒度是行鎖，MyISAM 最小的鎖粒度是表鎖。一個更新語句會鎖住整張表，導致其他查詢和更新都會被阻塞，因此併發訪問受限。這也是 MySQL 將預設儲存引擎從 MyISAM 變成 InnoDB 的重要原因之一；
| 對比項 | MyISAM | InnoDB |
|---|---|---|
| 主外來鍵 | 不支援 | 支援 |
| 事務 | 不支援 | 支援 |
| 行表鎖 | 表鎖，即使操作一條記錄也會鎖住整個表，不適合高併發的操作 | 行鎖, 操作時只鎖某一行，不對其它行有影響，適合高併發的操作 |
| 快取 | 只快取索引，不快取真實資料 | 不僅快取索引還要快取真實資料，對記憶體要求較高，而且記憶體大小對效能有決定性的影響 |
| 表空間 | 小 | 大 |
| 關注點 | 效能 | 事務 |
| 預設安裝 | 是 | 是 |
❝
一張表，裡面有 ID 自增主鍵，當 insert 了 17 條記錄之後，刪除了第 15,16,17 條記錄，再把 Mysql 重啟，再 insert 一條記錄，這條記錄的 ID 是 18 還是 15 ？
如果表的型別是 MyISAM，那麼是 18。因為 MyISAM 表會把自增主鍵的最大 ID 記錄到資料檔案中，重啟 MySQL 自增主鍵的最大 ID 也不會丟失；
如果表的型別是 InnoDB，那麼是 15。因為 InnoDB 表只是把自增主鍵的最大 ID 記錄到記憶體中，所以重啟資料庫或對錶進行 OPTION 操作，都會導致最大 ID 丟失。
❝
哪個儲存引擎執行 select count(*) 更快，為什麼?
MyISAM 更快，因為 MyISAM 內部維護了一個計數器，可以直接調取。
在 MyISAM 儲存引擎中，把表的總行數儲存在磁碟上，當執行 select count(*) from t 時，直接返回總資料。
在 InnoDB 儲存引擎中，跟 MyISAM 不一樣，沒有將總行數儲存在磁碟上，當執行 select count(*) from t 時，會先把資料讀出來，一行一行的累加，最後返回總數量。
InnoDB 中 count(*) 語句是在執行的時候，全表掃描統計總數量，所以當資料越來越大時，語句就越來越耗時了，為什麼 InnoDB 引擎不像 MyISAM 引擎一樣，將總行數儲存到磁碟上？這跟 InnoDB 的事務特性有關，由於多版本併發控制（MVCC）的原因，InnoDB 表 “應該返回多少行” 也是不確定的。
三、資料型別
主要包括以下五大類：
整數型別：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
浮點數型別：FLOAT、DOUBLE、DECIMAL
字串型別：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
日期型別：Date、DateTime、TimeStamp、Time、Year
其他資料型別：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection 等
❝
CHAR 和 VARCHAR 的區別？
char 是固定長度，varchar 長度可變：
char(n) 和 varchar(n) 中括號中 n 代表字元的個數，並不代表位元組個數，比如 CHAR(30) 就可以儲存 30 個字元。
儲存時，前者不管實際儲存資料的長度，直接按 char 規定的長度分配儲存空間；而後者會根據實際儲存的資料分配最終的儲存空間
相同點：
char(n)，varchar(n) 中的 n 都代表字元的個數
超過 char，varchar 最大長度 n 的限制後，字串會被截斷。
不同點：
char 不論實際儲存的字元數都會佔用 n 個字元的空間，而 varchar 只會佔用實際字元應該佔用的位元組空間加 1（實際長度 length，0<=length<255）或加 2（length>255）。因為 varchar 儲存資料時除了要儲存字串之外還會加一個位元組來記錄長度（如果列宣告長度大於 255 則使用兩個位元組來儲存長度）。
能儲存的最大空間限制不一樣：char 的儲存上限為 255 位元組。
char 在儲存時會截斷尾部的空格，而 varchar 不會。
char 是適合儲存很短的、一般固定長度的字串。例如，char 非常適合儲存密碼的 MD5 值，因為這是一個定長的值。對於非常短的列，char 比 varchar 在儲存空間上也更有效率。
❝
列的字串型別可以是什麼？
字串型別是：SET、BLOB、ENUM、CHAR、CHAR、TEXT、VARCHAR
❝
BLOB 和 TEXT 有什麼區別？
BLOB 是一個二進位制物件，可以容納可變數量的資料。有四種型別的 BLOB：TINYBLOB、BLOB、MEDIUMBLO 和 LONGBLOB
TEXT 是一個不區分大小寫的 BLOB。四種 TEXT 型別：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。
BLOB 儲存二進位制資料，TEXT 儲存字元資料。
四、索引
❝
說說你對 MySQL 索引的理解？
資料庫索引的原理，為什麼要用 B + 樹，為什麼不用二叉樹？
聚集索引與非聚集索引的區別？
InnoDB 引擎中的索引策略，瞭解過嗎？
建立索引的方式有哪些？
聚簇索引 / 非聚簇索引，mysql 索引底層實現，為什麼不用 B-tree，為什麼不用 hash，葉子結點存放的是資料還是指向資料的記憶體地址，使用索引需要注意的幾個地方？
- MYSQL 官方對索引的定義為：索引（Index）是幫助 MySQL 高效獲取資料的資料結構，所以說索引的本質是：資料結構
- 索引的目的在於提高查詢效率，可以類比字典、 火車站的車次表、圖書的目錄等 。
- 可以簡單的理解為 “排好序的快速查詢資料結構”，資料本身之外，資料庫還維護者一個滿足特定查詢演算法的資料結構，這些資料結構以某種方式引用（指向）資料，這樣就可以在這些資料結構上實現高階查詢演算法。這種資料結構，就是索引。下圖是一種可能的索引方式示例。
- 左邊的資料表，一共有兩列七條記錄，最左邊的是資料記錄的實體地址。為了加快 Col2 的查詢，可以維護一個右邊所示的二叉查詢樹，每個節點分別包含索引鍵值，和一個指向對應資料記錄實體地址的指標，這樣就可以運用二叉查詢在一定的複雜度內獲取到對應的資料，從而快速檢索出符合條件的記錄。
- 索引本身也很大，不可能全部儲存在記憶體中，一般以索引檔案的形式儲存在磁碟上
- 平常說的索引，沒有特別指明的話，就是 B + 樹（多路搜尋樹，不一定是二叉樹）結構組織的索引。其中聚集索引，次要索引，覆蓋索引，符合索引，字首索引，唯一索引預設都是使用 B + 樹索引，統稱索引。此外還有雜湊索引等。
基本語法：
建立：
建立索引：
CREATE [UNIQUE] INDEX indexName ON mytable(username(length));如果是 CHAR，VARCHAR 型別，length 可以小於欄位實際長度；如果是 BLOB 和 TEXT 型別，必須指定 length。
修改表結構 (新增索引)：
ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName)刪除：
DROP INDEX [indexName] ON mytable;檢視：
SHOW INDEX FROM table_name\G– 可以通過新增 \G 來格式化輸出資訊。使用 ALERT 命令
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):該語句新增一個主鍵，這意味著索引值必須是唯一的，且不能為 NULL。ALTER TABLE tbl_name ADD UNIQUE index_name (column_list這條語句建立索引的值必須是唯一的（除了 NULL 外，NULL 可能會出現多次）。ALTER TABLE tbl_name ADD INDEX index_name (column_list)新增普通索引，索引值可出現多次。ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)該語句指定了索引為 FULLTEXT ，用於全文索引。
優勢
提高資料檢索效率，降低資料庫 IO 成本
降低資料排序的成本，降低 CPU 的消耗
劣勢
索引也是一張表，儲存了主鍵和索引欄位，並指向實體表的記錄，所以也需要佔用記憶體
雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行 INSERT、UPDATE 和 DELETE。因為更新表時，MySQL 不僅要儲存資料，還要儲存一下索引檔案每次更新新增了索引列的欄位， 都會調整因為更新所帶來的鍵值變化後的索引資訊
MySQL 索引分類
資料結構角度
B + 樹索引
Hash 索引
Full-Text 全文索引
R-Tree 索引
從物理儲存角度
聚集索引（clustered index）
非聚集索引（non-clustered index），也叫輔助索引（secondary index）
聚集索引和非聚集索引都是 B + 樹結構
從邏輯角度
主鍵索引：主鍵索引是一種特殊的唯一索引，不允許有空值
普通索引或者單列索引：每個索引只包含單個列，一個表可以有多個單列索引
多列索引（複合索引、聯合索引）：複合索引指多個欄位上建立的索引，只有在查詢條件中使用了建立索引時的第一個欄位，索引才會被使用。使用複合索引時遵循最左字首集合
唯一索引或者非唯一索引
空間索引：空間索引是對空間資料型別的欄位建立的索引，MYSQL 中的空間資料型別有 4 種，分別是 GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL 使用 SPATIAL 關鍵字進行擴充套件，使得能夠用於建立正規索引型別的語法建立空間索引。建立空間索引的列，必須將其宣告為 NOT NULL，空間索引只能在儲存引擎為 MYISAM 的表中建立
❝
為什麼 MySQL 索引中用 B+tree，不用 B-tree 或者其他樹，為什麼不用 Hash 索引
聚簇索引 / 非聚簇索引，MySQL 索引底層實現，葉子結點存放的是資料還是指向資料的記憶體地址，使用索引需要注意的幾個地方？
使用索引查詢一定能提高查詢的效能嗎？為什麼?
MySQL 索引結構
首先要明白索引（index）是在儲存引擎（storage engine）層面實現的，而不是 server 層面。不是所有的儲存引擎都支援所有的索引型別。即使多個儲存引擎支援某一索引型別，它們的實現和行為也可能有所差別。
B+Tree 索引
MyISAM 和 InnoDB 儲存引擎，都使用 B+Tree 的資料結構，它相對與 B-Tree 結構，所有的資料都存放在葉子節點上，且把葉子節點通過指標連線到一起，形成了一條資料連結串列，以加快相鄰資料的檢索效率。
先了解下 B-Tree 和 B+Tree 的區別
B-Tree
B-Tree 是為磁碟等外儲存裝置設計的一種平衡查詢樹。
系統從磁碟讀取資料到記憶體時是以磁碟塊（block）為基本單位的，位於同一個磁碟塊中的資料會被一次性讀取出來，而不是需要什麼取什麼。
InnoDB 儲存引擎中有頁（Page）的概念，頁是其磁碟管理的最小單位。InnoDB 儲存引擎中預設每個頁的大小為 16KB，可通過引數 innodb_page_size 將頁的大小設定為 4K、8K、16K，在 MySQL 中可通過如下命令檢視頁的大小：show variables like 'innodb_page_size';
而系統一個磁碟塊的儲存空間往往沒有這麼大，因此 InnoDB 每次申請磁碟空間時都會是若干地址連續磁碟塊來達到頁的大小 16KB。InnoDB 在把磁碟資料讀入到磁碟時會以頁為基本單位，在查詢資料時如果一個頁中的每條資料都能有助於定位資料記錄的位置，這將會減少磁碟 I/O 次數，提高查詢效率。
B-Tree 結構的資料可以讓系統高效的找到資料所在的磁碟塊。為了描述 B-Tree，首先定義一條記錄為一個二元組 [key, data] ，key 為記錄的鍵值，對應表中的主鍵值，data 為一行記錄中除主鍵外的資料。對於不同的記錄，key 值互不相同。
一棵 m 階的 B-Tree 有如下特性：
每個節點最多有 m 個孩子
除了根節點和葉子節點外，其它每個節點至少有 Ceil(m/2) 個孩子。
若根節點不是葉子節點，則至少有 2 個孩子
所有葉子節點都在同一層，且不包含其它關鍵字資訊
每個非終端節點包含 n 個關鍵字資訊（P0,P1,…Pn, k1,…kn）
關鍵字的個數 n 滿足：ceil(m/2)-1 <= n <= m-1
ki(i=1,…n) 為關鍵字，且關鍵字升序排序
Pi(i=1,…n) 為指向子樹根節點的指標。P(i-1) 指向的子樹的所有節點關鍵字均小於 ki，但都大於 k(i-1)
B-Tree 中的每個節點根據實際情況可以包含大量的關鍵字資訊和分支，如下圖所示為一個 3 階的 B-Tree：
每個節點佔用一個盤塊的磁碟空間，一個節點上有兩個升序排序的關鍵字和三個指向子樹根節點的指標，指標儲存的是子節點所在磁碟塊的地址。兩個關鍵詞劃分成的三個範圍域對應三個指標指向的子樹的資料的範圍域。以根節點為例，關鍵字為 17 和 35，P1 指標指向的子樹的資料範圍為小於 17，P2 指標指向的子樹的資料範圍為 17~35，P3 指標指向的子樹的資料範圍為大於 35。
模擬查詢關鍵字 29 的過程：
根據根節點找到磁碟塊 1，讀入記憶體。【磁碟 I/O 操作第 1 次】
比較關鍵字 29 在區間（17,35），找到磁碟塊 1 的指標 P2。
根據 P2 指標找到磁碟塊 3，讀入記憶體。【磁碟 I/O 操作第 2 次】
比較關鍵字 29 在區間（26,30），找到磁碟塊 3 的指標 P2。
根據 P2 指標找到磁碟塊 8，讀入記憶體。【磁碟 I/O 操作第 3 次】
在磁碟塊 8 中的關鍵字列表中找到關鍵字 29。
分析上面過程，發現需要 3 次磁碟 I/O 操作，和 3 次記憶體查詢操作。由於記憶體中的關鍵字是一個有序表結構，可以利用二分法查詢提高效率。而 3 次磁碟 I/O 操作是影響整個 B-Tree 查詢效率的決定因素。B-Tree 相對於 AVLTree 縮減了節點個數，使每次磁碟 I/O 取到記憶體的資料都發揮了作用，從而提高了查詢效率。
B+Tree
B+Tree 是在 B-Tree 基礎上的一種優化，使其更適合實現外儲存索引結構，InnoDB 儲存引擎就是用 B+Tree 實現其索引結構。
從上一節中的 B-Tree 結構圖中可以看到每個節點中不僅包含資料的 key 值，還有 data 值。而每一個頁的儲存空間是有限的，如果 data 資料較大時將會導致每個節點（即一個頁）能儲存的 key 的數量很小，當儲存的資料量很大時同樣會導致 B-Tree 的深度較大，增大查詢時的磁碟 I/O 次數，進而影響查詢效率。在 B+Tree 中，所有資料記錄節點都是按照鍵值大小順序存放在同一層的葉子節點上，而非葉子節點上只儲存 key 值資訊，這樣可以大大加大每個節點儲存的 key 值數量，降低 B+Tree 的高度。
B+Tree 相對於 B-Tree 有幾點不同：
非葉子節點只儲存鍵值資訊；
所有葉子節點之間都有一個鏈指標；
資料記錄都存放在葉子節點中
將上一節中的 B-Tree 優化，由於 B+Tree 的非葉子節點只儲存鍵值資訊，假設每個磁碟塊能儲存 4 個鍵值及指標資訊，則變成 B+Tree 後其結構如下圖所示：
通常在 B+Tree 上有兩個頭指標，一個指向根節點，另一個指向關鍵字最小的葉子節點，而且所有葉子節點（即資料節點）之間是一種鏈式環結構。因此可以對 B+Tree 進行兩種查詢運算：一種是對於主鍵的範圍查詢和分頁查詢，另一種是從根節點開始，進行隨機查詢。
可能上面例子中只有 22 條資料記錄，看不出 B+Tree 的優點，下面做一個推算：
InnoDB 儲存引擎中頁的大小為 16KB，一般表的主鍵型別為 INT（佔用 4 個位元組）或 BIGINT（佔用 8 個位元組），指標型別也一般為 4 或 8 個位元組，也就是說一個頁（B+Tree 中的一個節點）中大概儲存 16KB/(8B+8B)=1K 個鍵值（因為是估值，為方便計算，這裡的 K 取值為 10^3）。也就是說一個深度為 3 的 B+Tree 索引可以維護 10^3 * 10^3 * 10^3 = 10 億 條記錄。
實際情況中每個節點可能不能填充滿，因此在資料庫中，B+Tree 的高度一般都在 2-4 層。MySQL 的 InnoDB 儲存引擎在設計時是將根節點常駐記憶體的，也就是說查詢某一鍵值的行記錄時最多隻需要 1~3 次磁碟 I/O 操作。
B+Tree 性質
通過上面的分析，我們知道 IO 次數取決於 b + 數的高度 h，假設當前資料表的資料為 N，每個磁碟塊的資料項的數量是 m，則有 h=㏒(m+1)N，當資料量 N 一定的情況下，m 越大，h 越小；而 m = 磁碟塊的大小 / 資料項的大小，磁碟塊的大小也就是一個資料頁的大小，是固定的，如果資料項佔的空間越小，資料項的數量越多，樹的高度越低。這就是為什麼每個資料項，即索引欄位要儘量的小，比如 int 佔 4 位元組，要比 bigint8 位元組少一半。這也是為什麼 b + 樹要求把真實的資料放到葉子節點而不是內層節點，一旦放到內層節點，磁碟塊的資料項會大幅度下降，導致樹增高。當資料項等於 1 時將會退化成線性表。
當 b + 樹的資料項是複合的資料結構，比如 (name,age,sex) 的時候，b + 數是按照從左到右的順序來建立搜尋樹的，比如當 (張三, 20,F) 這樣的資料來檢索的時候，b + 樹會優先比較 name 來確定下一步的所搜方向，如果 name 相同再依次比較 age 和 sex，最後得到檢索的資料；但當 (20,F) 這樣的沒有 name 的資料來的時候，b + 樹就不知道下一步該查哪個節點，因為建立搜尋樹的時候 name 就是第一個比較因子，必須要先根據 name 來搜尋才能知道下一步去哪裡查詢。比如當 (張三, F) 這樣的資料來檢索時，b + 樹可以用 name 來指定搜尋方向，但下一個欄位 age 的缺失，所以只能把名字等於張三的資料都找到，然後再匹配性別是 F 的資料了， 這個是非常重要的性質，即索引的最左匹配特性。
MyISAM 主鍵索引與輔助索引的結構
MyISAM 引擎的索引檔案和資料檔案是分離的。MyISAM 引擎索引結構的葉子節點的資料域，存放的並不是實際的資料記錄，而是資料記錄的地址。索引檔案與資料檔案分離，這樣的索引稱為 “ 非聚簇索引 “。MyISAM 的主索引與輔助索引區別並不大，只是主鍵索引不能有重複的關鍵字。
在 MyISAM 中，索引（含葉子節點）存放在單獨的. myi 檔案中，葉子節點存放的是資料的實體地址偏移量（通過偏移量訪問就是隨機訪問，速度很快）。
主索引是指主鍵索引，鍵值不可能重複；輔助索引則是普通索引，鍵值可能重複。
通過索引查詢資料的流程：先從索引檔案中查詢到索引節點，從中拿到資料的檔案指標，再到資料檔案中通過檔案指標定位了具體的資料。輔助索引類似。
InnoDB 主鍵索引與輔助索引的結構
InnoDB 引擎索引結構的葉子節點的資料域，存放的就是實際的資料記錄（對於主索引，此處會存放表中所有的資料記錄；對於輔助索引此處會引用主鍵，檢索的時候通過主鍵到主鍵索引中找到對應資料行），或者說，InnoDB 的資料檔案本身就是主鍵索引檔案，這樣的索引被稱為 “聚簇索引”，一個表只能有一個聚簇索引。
主鍵索引：
我們知道 InnoDB 索引是聚集索引，它的索引和資料是存入同一個. idb 檔案中的，因此它的索引結構是在同一個樹節點中同時存放索引和資料，如下圖中最底層的葉子節點有三行資料，對應於資料表中的 id、stu_id、name 資料項。
在 Innodb 中，索引分葉子節點和非葉子節點，非葉子節點就像新華字典的目錄，單獨存放在索引段中，葉子節點則是順序排列的，在資料段中。Innodb 的資料檔案可以按照表來切分（只需要開啟innodb_file_per_table)，切分後存放在xxx.ibd中，預設不切分，存放在xxx.ibdata中。
輔助（非主鍵）索引：
這次我們以示例中學生表中的 name 列建立輔助索引，它的索引結構跟主鍵索引的結構有很大差別，在最底層的葉子結點有兩行資料，第一行的字串是輔助索引，按照 ASCII 碼進行排序，第二行的整數是主鍵的值。
這就意味著，對 name 列進行條件搜尋，需要兩個步驟：
① 在輔助索引上檢索 name，到達其葉子節點獲取對應的主鍵；
② 使用主鍵在主索引上再進行對應的檢索操作
這也就是所謂的 “回表查詢”
InnoDB 索引結構需要注意的點
資料檔案本身就是索引檔案
表資料檔案本身就是按 B+Tree 組織的一個索引結構檔案
聚集索引中葉節點包含了完整的資料記錄
InnoDB 表必須要有主鍵，並且推薦使用整型自增主鍵
正如我們上面介紹 InnoDB 儲存結構，索引與資料是共同儲存的，不管是主鍵索引還是輔助索引，在查詢時都是通過先查詢到索引節點才能拿到相對應的資料，如果我們在設計表結構時沒有顯式指定索引列的話，MySQL 會從表中選擇資料不重複的列建立索引，如果沒有符合的列，則 MySQL 自動為 InnoDB 表生成一個隱含欄位作為主鍵，並且這個欄位長度為 6 個位元組，型別為整型。
❝
那為什麼推薦使用整型自增主鍵而不是選擇 UUID？
UUID 是字串，比整型消耗更多的儲存空間；
在 B + 樹中進行查詢時需要跟經過的節點值比較大小，整型資料的比較運算比字串更快速；
自增的整型索引在磁碟中會連續儲存，在讀取一頁資料時也是連續；UUID 是隨機產生的，讀取的上下兩行資料儲存是分散的，不適合執行 where id > 5 && id < 20 的條件查詢語句。
在插入或刪除資料時，整型自增主鍵會在葉子結點的末尾建立新的葉子節點，不會破壞左側子樹的結構；UUID 主鍵很容易出現這樣的情況，B + 樹為了維持自身的特性，有可能會進行結構的重構，消耗更多的時間。
❝
為什麼非主鍵索引結構葉子節點儲存的是主鍵值？
保證資料一致性和節省儲存空間，可以這麼理解：商城系統訂單表會儲存一個使用者 ID 作為關聯外來鍵，而不推薦儲存完整的使用者資訊，因為當我們使用者表中的資訊（真實名稱、手機號、收貨地址 ···）修改後，不需要再次維護訂單表的使用者資料，同時也節省了儲存空間。
Hash 索引
主要就是通過 Hash 演算法（常見的 Hash 演算法有直接定址法、平方取中法、摺疊法、除數取餘法、隨機數法），將資料庫欄位資料轉換成定長的 Hash 值，與這條資料的行指標一併存入 Hash 表的對應位置；如果發生 Hash 碰撞（兩個不同關鍵字的 Hash 值相同），則在對應 Hash 鍵下以連結串列形式儲存。
檢索演算法：在檢索查詢時，就再次對待查關鍵字再次執行相同的 Hash 演算法，得到 Hash 值，到對應 Hash 表對應位置取出資料即可，如果發生 Hash 碰撞，則需要在取值時進行篩選。目前使用 Hash 索引的資料庫並不多，主要有 Memory 等。
MySQL 目前有 Memory 引擎和 NDB 引擎支援 Hash 索引。
full-text 全文索引
全文索引也是 MyISAM 的一種特殊索引型別，主要用於全文索引，InnoDB 從 MYSQL5.6 版本提供對全文索引的支援。
它用於替代效率較低的 LIKE 模糊匹配操作，而且可以通過多欄位組合的全文索引一次性全模糊匹配多個欄位。
同樣使用 B-Tree 存放索引資料，但使用的是特定的演算法，將欄位資料分割後再進行索引（一般每 4 個位元組一次分割），索引檔案儲存的是分割前的索引字串集合，與分割後的索引資訊，對應 Btree 結構的節點儲存的是分割後的詞資訊以及它在分割前的索引字串集合中的位置。
R-Tree 空間索引
空間索引是 MyISAM 的一種特殊索引型別，主要用於地理空間資料型別
❝
為什麼 Mysql 索引要用 B + 樹不是 B 樹？
用 B + 樹不用 B 樹考慮的是 IO 對效能的影響，B 樹的每個節點都儲存資料，而 B + 樹只有葉子節點才儲存資料，所以查詢相同資料量的情況下，B 樹的高度更高，IO 更頻繁。資料庫索引是儲存在磁碟上的，當資料量大時，就不能把整個索引全部載入到記憶體了，只能逐一載入每一個磁碟頁（對應索引樹的節點）。其中在 MySQL 底層對 B + 樹進行進一步優化：在葉子節點中是雙向連結串列，且在連結串列的頭結點和尾節點也是迴圈指向的。
❝
面試官：為何不採用 Hash 方式？
因為 Hash 索引底層是雜湊表，雜湊表是一種以 key-value 儲存資料的結構，所以多個資料在儲存關係上是完全沒有任何順序關係的，所以，對於區間查詢是無法直接通過索引查詢的，就需要全表掃描。所以，雜湊索引只適用於等值查詢的場景。而 B+ Tree 是一種多路平衡查詢樹，所以他的節點是天然有序的（左子節點小於父節點、父節點小於右子節點），所以對於範圍查詢的時候不需要做全表掃描。
雜湊索引不支援多列聯合索引的最左匹配規則，如果有大量重複鍵值得情況下，雜湊索引的效率會很低，因為存在雜湊碰撞問題。
哪些情況需要建立索引
主鍵自動建立唯一索引
頻繁作為查詢條件的欄位
查詢中與其他表關聯的欄位，外來鍵關係建立索引
單鍵 / 組合索引的選擇問題，高併發下傾向建立組合索引
查詢中排序的欄位，排序欄位通過索引訪問大幅提高排序速度
查詢中統計或分組欄位
哪些情況不要建立索引
表記錄太少
經常增刪改的表
資料重複且分佈均勻的表欄位，只應該為最經常查詢和最經常排序的資料列建立索引（如果某個資料類包含太多的重複資料，建立索引沒有太大意義）
頻繁更新的欄位不適合建立索引（會加重 IO 負擔）
where 條件裡用不到的欄位不建立索引
MySQL 高效索引
覆蓋索引（Covering Index）, 或者叫索引覆蓋， 也就是平時所說的不需要回表操作
就是 select 的資料列只用從索引中就能夠取得，不必讀取資料行，MySQL 可以利用索引返回 select 列表中的欄位，而不必根據索引再次讀取資料檔案，換句話說查詢列要被所建的索引覆蓋。
索引是高效找到行的一個方法，但是一般資料庫也能使用索引找到一個列的資料，因此它不必讀取整個行。畢竟索引葉子節點儲存了它們索引的資料，當能通過讀取索引就可以得到想要的資料，那就不需要讀取行了。一個索引包含（覆蓋）滿足查詢結果的資料就叫做覆蓋索引。
判斷標準
使用 explain，可以通過輸出的 extra 列來判斷，對於一個索引覆蓋查詢，顯示為 using index，MySQL 查詢優化器在執行查詢前會決定是否有索引覆蓋查詢
五、MySQL 查詢
❝
count(*) 和 count(1) 和 count(列名) 區別 ps：這道題說法有點多
執行效果上：
count(*) 包括了所有的列，相當於行數，在統計結果的時候，不會忽略列值為 NULL
count(1) 包括了所有列，用 1 代表程式碼行，在統計結果的時候，不會忽略列值為 NULL
count(列名) 只包括列名那一列，在統計結果的時候，會忽略列值為空（這裡的空不是隻空字串或者 0，而是表示 null）的計數，即某個欄位值為 NULL 時，不統計。
執行效率上：
列名為主鍵，count(列名) 會比 count(1) 快
列名不為主鍵，count(1) 會比 count(列名) 快
如果表多個列並且沒有主鍵，則 count(1) 的執行效率優於 count(*)
如果有主鍵，則 select count（主鍵）的執行效率是最優的
如果表只有一個欄位，則 select count(*) 最優。
❝
MySQL 中 in 和 exists 的區別？
exists：exists 對外表用 loop 逐條查詢，每次查詢都會檢視 exists 的條件語句，當 exists 裡的條件語句能夠返回記錄行時（無論記錄行是的多少，只要能返回），條件就為真，返回當前 loop 到的這條記錄；反之，如果 exists 裡的條件語句不能返回記錄行，則當前 loop 到的這條記錄被丟棄，exists 的條件就像一個 bool 條件，當能返回結果集則為 true，不能返回結果集則為 false
in：in 查詢相當於多個 or 條件的疊加
SELECT * FROM A WHERE A.id IN (SELECT id FROM B);
SELECT * FROM A WHERE EXISTS (SELECT * from B WHERE B.id = A.id);
如果查詢的兩個表大小相當，那麼用 in 和 exists 差別不大。
如果兩個表中一個較小，一個是大表，則子查詢表大的用 exists，子查詢表小的用 in：
❝
UNION 和 UNION ALL 的區別?
UNION 和 UNION ALL 都是將兩個結果集合併為一個，兩個要聯合的 SQL 語句 欄位個數必須一樣，而且欄位型別要 “相容”（一致）；
UNION 在進行表連線後會篩選掉重複的資料記錄（效率較低），而 UNION ALL 則不會去掉重複的資料記錄；
UNION 會按照欄位的順序進行排序，而 UNION ALL 只是簡單的將兩個結果合併就返回；
SQL 執行順序
手寫
SELECT DISTINCT <select_list> FROM <left_table> <join_type> JOIN <right_table> ON <join_condition> WHERE <where_condition> GROUP BY <group_by_list> HAVING <having_condition> ORDER BY <order_by_condition> LIMIT <limit_number>
* 機讀
FROM
ON
JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT
* 總結
> ❝
>
> mysql 的內連線、左連線、右連線有什麼區別？
>
> 什麼是內連線、外連線、交叉連線、笛卡爾積呢？
### Join 圖
* * *
六、MySQL 事務
----------
> ❝
>
> 事務的隔離級別有哪些？MySQL 的預設隔離級別是什麼？
>
> 什麼是幻讀，髒讀，不可重複讀呢？
>
> MySQL 事務的四大特性以及實現原理
>
> MVCC 熟悉嗎，它的底層原理？
MySQL 事務主要用於處理操作量大，複雜度高的資料。比如說，在人員管理系統中，你刪除一個人員，你即需要刪除人員的基本資料，也要刪除和該人員相關的資訊，如信箱，文章等等，這樣，這些資料庫操作語句就構成一個事務！
### ACID — 事務基本要素
事務是由一組 SQL 語句組成的邏輯處理單元，具有 4 個屬性，通常簡稱為事務的 ACID 屬性。
* **A (Atomicity) 原子性**：整個事務中的所有操作，要麼全部完成，要麼全部不完成，不可能停滯在中間某個環節。事務在執行過程中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣
* **C (Consistency) 一致性**：在事務開始之前和事務結束以後，資料庫的完整性約束沒有被破壞
* **I (Isolation) 隔離性**：一個事務的執行不能其它事務干擾。即一個事務內部的操作及使用的資料對其它併發事務是隔離的，併發執行的各個事務之間不能互相干擾
* **D (Durability) 永續性**：在事務完成以後，該事務所對資料庫所作的更改便持久的儲存在資料庫之中，並不會被回滾
**併發事務處理帶來的問題**
* 更新丟失（Lost Update)：事務 A 和事務 B 選擇同一行，然後基於最初選定的值更新該行時，由於兩個事務都不知道彼此的存在，就會發生丟失更新問題
* 髒讀 (Dirty Reads)：事務 A 讀取了事務 B 更新的資料，然後 B 回滾操作，那麼 A 讀取到的資料是髒資料
* 不可重複讀（Non-Repeatable Reads)：事務 A 多次讀取同一資料，事務 B 在事務 A 多次讀取的過程中，對資料作了更新並提交，導致事務 A 多次讀取同一資料時，結果不一致。
* 幻讀（Phantom Reads)：幻讀與不可重複讀類似。它發生在一個事務 A 讀取了幾行資料，接著另一個併發事務 B 插入了一些資料時。在隨後的查詢中，事務 A 就會發現多了一些原本不存在的記錄，就好像發生了幻覺一樣，所以稱為幻讀。
**幻讀和不可重複讀的區別：**
* **不可重複讀的重點是修改**：在同一事務中，同樣的條件，第一次讀的資料和第二次讀的資料不一樣。（因為中間有其他事務提交了修改）
* **幻讀的重點在於新增或者刪除**：在同一事務中，同樣的條件,，第一次和第二次讀出來的記錄數不一樣。（因為中間有其他事務提交了插入 / 刪除）
**併發事務處理帶來的問題的解決辦法：**
* “更新丟失” 通常是應該完全避免的。但防止更新丟失，並不能單靠資料庫事務控制器來解決，需要應用程式對要更新的資料加必要的鎖來解決，因此，防止更新丟失應該是應用的責任。
* “髒讀” 、 “不可重複讀” 和 “幻讀” ，其實都是資料庫讀一致性問題，必須由資料庫提供一定的事務隔離機制來解決：
* 一種是加鎖：在讀取資料前，對其加鎖，阻止其他事務對資料進行修改。
* 另一種是資料多版本併發控制（MultiVersion Concurrency Control，簡稱 **MVCC** 或 MCC），也稱為多版本資料庫：不用加任何鎖， 通過一定機制生成一個資料請求時間點的一致性資料快照 （Snapshot)， 並用這個快照來提供一定級別 （語句級或事務級） 的一致性讀取。從使用者的角度來看，好象是資料庫可以提供同一資料的多個版本。
### 事務隔離級別
資料庫事務的隔離級別有 4 種，由低到高分別為
* **READ-UNCOMMITTED(讀未提交)：** 最低的隔離級別，允許讀取尚未提交的資料變更，**可能會導致髒讀、幻讀或不可重複讀**。
* **READ-COMMITTED(讀已提交)：** 允許讀取併發事務已經提交的資料，**可以阻止髒讀，但是幻讀或不可重複讀仍有可能發生**。
* **REPEATABLE-READ(可重複讀)：** 對同一欄位的多次讀取結果都是一致的，除非資料是被本身事務自己所修改，**可以阻止髒讀和不可重複讀，但幻讀仍有可能發生**。
* **SERIALIZABLE(可序列化)：** 最高的隔離級別，完全服從 ACID 的隔離級別。所有的事務依次逐個執行，這樣事務之間就完全不可能產生干擾，也就是說，**該級別可以防止髒讀、不可重複讀以及幻讀**。
檢視當前資料庫的事務隔離級別：
show variables like ‘tx_isolation’
下面通過事例一一闡述在事務的併發操作中可能會出現髒讀，不可重複讀，幻讀和事務隔離級別的聯絡。
資料庫的事務隔離越嚴格，併發副作用越小，但付出的代價就越大，因為事務隔離實質上就是使事務在一定程度上 “序列化” 進行，這顯然與 “併發” 是矛盾的。同時，不同的應用對讀一致性和事務隔離程度的要求也是不同的，比如許多應用對 “不可重複讀” 和“幻讀”並不敏感，可能更關心資料併發訪問的能力。
#### Read uncommitted
讀未提交，就是一個事務可以讀取另一個未提交事務的資料。
事例：老闆要給程式設計師發工資，程式設計師的工資是 3.6 萬 / 月。但是發工資時老闆不小心按錯了數字，按成 3.9 萬 / 月，該錢已經打到程式設計師的戶口，但是事務還沒有提交，就在這時，程式設計師去檢視自己這個月的工資，發現比往常多了 3 千元，以為漲工資了非常高興。但是老闆及時發現了不對，馬上回滾差點就提交了的事務，將數字改成 3.6 萬再提交。
分析：實際程式設計師這個月的工資還是 3.6 萬，但是程式設計師看到的是 3.9 萬。他看到的是老闆還沒提交事務時的資料。這就是髒讀。
那怎麼解決髒讀呢？Read committed！讀提交，能解決髒讀問題。
#### Read committed
讀提交，顧名思義，就是一個事務要等另一個事務提交後才能讀取資料。
事例：程式設計師拿著信用卡去享受生活（卡里當然是只有 3.6 萬），當他埋單時（程式設計師事務開啟），收費系統事先檢測到他的卡里有 3.6 萬，就在這個時候！！程式設計師的妻子要把錢全部轉出充當家用，並提交。當收費系統準備扣款時，再檢測卡里的金額，發現已經沒錢了（第二次檢測金額當然要等待妻子轉出金額事務提交完）。程式設計師就會很鬱悶，明明卡里是有錢的…
分析：這就是讀提交，若有事務對資料進行更新（UPDATE）操作時，讀操作事務要等待這個更新操作事務提交後才能讀取資料，可以解決髒讀問題。但在這個事例中，出現了一個事務範圍內兩個相同的查詢卻返回了不同資料，這就是**不可重複讀**。
那怎麼解決可能的不可重複讀問題？Repeatable read ！
#### Repeatable read
重複讀，就是在開始讀取資料（事務開啟）時，不再允許修改操作。**MySQL 的預設事務隔離級別**
事例：程式設計師拿著信用卡去享受生活（卡里當然是只有 3.6 萬），當他埋單時（事務開啟，不允許其他事務的 UPDATE 修改操作），收費系統事先檢測到他的卡里有 3.6 萬。這個時候他的妻子不能轉出金額了。接下來收費系統就可以扣款了。
分析：重複讀可以解決不可重複讀問題。寫到這裡，應該明白的一點就是，**不可重複讀對應的是修改，即 UPDATE 操作。但是可能還會有幻讀問題。因為幻讀問題對應的是插入 INSERT 操作，而不是 UPDATE 操作**。
**什麼時候會出現幻讀？**
事例：程式設計師某一天去消費，花了 2 千元，然後他的妻子去檢視他今天的消費記錄（全表掃描 FTS，妻子事務開啟），看到確實是花了 2 千元，就在這個時候，程式設計師花了 1 萬買了一部電腦，即新增 INSERT 了一條消費記錄，並提交。當妻子列印程式設計師的消費記錄清單時（妻子事務提交），發現花了 1.2 萬元，似乎出現了幻覺，這就是幻讀。
那怎麼解決幻讀問題？Serializable！
#### Serializable 序列化
Serializable 是最高的事務隔離級別，在該級別下，事務序列化順序執行，可以避免髒讀、不可重複讀與幻讀。簡單來說，Serializable 會在讀取的每一行資料上都加鎖，所以可能導致大量的超時和鎖爭用問題。這種事務隔離級別效率低下，比較耗資料庫效能，一般不使用。
#### 比較
| 事務隔離級別 | 讀資料一致性 | 髒讀 | 不可重複讀 | 幻讀 |
| ---------------------------- | ---------------------------------------- | ---- | ---------- | ---- |
| 讀未提交（read-uncommitted） | 最低階被，只能保證不讀取物理上損壞的資料 | 是 | 是 | 是 |
| 讀已提交（read-committed） | 語句級 | 否 | 是 | 是 |
| 可重複讀（repeatable-read） | 事務級 | 否 | 否 | 是 |
| 序列化（serializable） | 最高階別，事務級 | 否 | 否 | 否 |
需要說明的是，事務隔離級別和資料訪問的併發性是對立的，事務隔離級別越高併發性就越差。所以要根據具體的應用來確定合適的事務隔離級別，這個地方沒有萬能的原則。
MySQL InnoDB 儲存引擎的預設支援的隔離級別是 **REPEATABLE-READ（可重讀）**。我們可以通過`SELECT @@tx_isolation;`命令來檢視，MySQL 8.0 該命令改為`SELECT @@transaction_isolation;`
這裡需要注意的是：與 SQL 標準不同的地方在於 InnoDB 儲存引擎在 **REPEATABLE-READ**（可重讀）事務隔離級別下使用的是 Next-Key Lock 演算法，因此可以避免幻讀的產生，這與其他資料庫系統 (如 SQL Server) 是不同的。所以說 InnoDB 儲存引擎的預設支援的隔離級別是 REPEATABLE-READ（可重讀）已經可以完全保證事務的隔離性要求，即達到了 SQL 標準的 **SERIALIZABLE**(可序列化) 隔離級別，而且保留了比較好的併發效能。
因為隔離級別越低，事務請求的鎖越少，所以大部分資料庫系統的隔離級別都是 **READ-COMMITTED**(讀已提交):，但是你要知道的是 InnoDB 儲存引擎預設使用 **REPEATABLE-READ**（可重讀）並不會有任何效能損失。
### MVCC 多版本併發控制
MySQL 的大多數事務型儲存引擎實現都不是簡單的行級鎖。基於提升併發性考慮，一般都同時實現了多版本併發控制（MVCC），包括 Oracle、PostgreSQL。只是實現機制各不相同。
可以認為 MVCC 是行級鎖的一個變種，但它在很多情況下避免了加鎖操作，因此開銷更低。雖然實現機制有所不同，但大都實現了非阻塞的讀操作，寫操作也只是鎖定必要的行。
MVCC 的實現是通過儲存資料在某個時間點的快照來實現的。也就是說不管需要執行多長時間，每個事物看到的資料都是一致的。
典型的 MVCC 實現方式，分為**樂觀（optimistic）併發控制和悲觀（pressimistic）併發控制**。下邊通過 InnoDB 的簡化版行為來說明 MVCC 是如何工作的。
InnoDB 的 MVCC，是通過在每行記錄後面儲存兩個隱藏的列來實現。這兩個列，一個儲存了行的建立時間，一個儲存行的過期時間（刪除時間）。當然儲存的並不是真實的時間，而是系統版本號（system version number）。每開始一個新的事務，系統版本號都會自動遞增。事務開始時刻的系統版本號會作為事務的版本號，用來和查詢到的每行記錄的版本號進行比較。
**REPEATABLE READ（可重讀）隔離級別下 MVCC 如何工作：**
* SELECT
InnoDB 會根據以下兩個條件檢查每行記錄：
只有符合上述兩個條件的才會被查詢出來
* InnoDB 只查詢版本早於當前事務版本的資料行，這樣可以確保事務讀取的行，要麼是在開始事務之前已經存在要麼是事務自身插入或者修改過的
* 行的刪除版本號要麼未定義，要麼大於當前事務版本號，這樣可以確保事務讀取到的行在事務開始之前未被刪除
* INSERT：InnoDB 為新插入的每一行儲存當前系統版本號作為行版本號
* DELETE：InnoDB 為刪除的每一行儲存當前系統版本號作為行刪除標識
* UPDATE：InnoDB 為插入的一行新紀錄儲存當前系統版本號作為行版本號，同時儲存當前系統版本號到原來的行作為刪除標識
儲存這兩個額外系統版本號，使大多數操作都不用加鎖。使資料操作簡單，效能很好，並且也能保證只會讀取到符合要求的行。不足之處是每行記錄都需要額外的儲存空間，需要做更多的行檢查工作和一些額外的維護工作。
MVCC 只在 COMMITTED READ（讀提交）和 REPEATABLE READ（可重複讀）兩種隔離級別下工作。
### 事務日誌
InnoDB 使用日誌來減少提交事務時的開銷。因為日誌中已經記錄了事務，就無須在每個事務提交時把緩衝池的髒塊重新整理 (flush) 到磁碟中。
事務修改的資料和索引通常會對映到表空間的隨機位置，所以重新整理這些變更到磁碟需要很多隨機 IO。
InnoDB 假設使用常規磁碟，隨機 IO 比順序 IO 昂貴得多，因為一個 IO 請求需要時間把磁頭移到正確的位置，然後等待磁碟上讀出需要的部分，再轉到開始位置。
InnoDB 用日誌把隨機 IO 變成順序 IO。一旦日誌安全寫到磁碟，事務就持久化了，即使斷電了，InnoDB 可以重放日誌並且恢復已經提交的事務。
InnoDB 使用一個後臺執行緒智慧地重新整理這些變更到資料檔案。這個執行緒可以批量組合寫入，使得資料寫入更順序，以提高效率。
事務日誌可以幫助提高事務效率：
* 使用事務日誌，儲存引擎在修改表的資料時只需要修改其記憶體拷貝，再把該修改行為記錄到持久在硬碟上的事務日誌中，而不用每次都將修改的資料本身持久到磁碟。
* 事務日誌採用的是追加的方式，因此寫日誌的操作是磁碟上一小塊區域內的順序 I/O，而不像隨機 I/O 需要在磁碟的多個地方移動磁頭，所以採用事務日誌的方式相對來說要快得多。
* 事務日誌持久以後，記憶體中被修改的資料在後臺可以慢慢刷回到磁碟。
* 如果資料的修改已經記錄到事務日誌並持久化，但資料本身沒有寫回到磁碟，此時系統崩潰，儲存引擎在重啟時能夠自動恢復這一部分修改的資料。
目前來說，大多數儲存引擎都是這樣實現的，我們通常稱之為**預寫式日誌**（Write-Ahead Logging），修改資料需要寫兩次磁碟。
### 事務的實現
事務的實現是基於資料庫的儲存引擎。不同的儲存引擎對事務的支援程度不一樣。MySQL 中支援事務的儲存引擎有 InnoDB 和 NDB。
事務的實現就是如何實現 ACID 特性。
事務的隔離性是通過鎖實現，而事務的原子性、一致性和永續性則是通過事務日誌實現 。
> ❝
>
> 事務是如何通過日誌來實現的，說得越深入越好。
事務日誌包括：**重做日誌 redo** 和**回滾日誌 undo**
* **redo log（重做日誌**） 實現持久化和原子性
在 innoDB 的儲存引擎中，事務日誌通過重做 (redo) 日誌和 innoDB 儲存引擎的日誌緩衝 (InnoDB Log Buffer) 實現。事務開啟時，事務中的操作，都會先寫入儲存引擎的日誌緩衝中，在事務提交之前，這些緩衝的日誌都需要提前重新整理到磁碟上持久化，這就是 DBA 們口中常說的“日誌先行”(Write-Ahead Logging)。當事務提交之後，在 Buffer Pool 中對映的資料檔案才會慢慢重新整理到磁碟。此時如果資料庫崩潰或者當機，那麼當系統重啟進行恢復時，就可以根據 redo log 中記錄的日誌，把資料庫恢復到崩潰前的一個狀態。未完成的事務，可以繼續提交，也可以選擇回滾，這基於恢復的策略而定。
在系統啟動的時候，就已經為 redo log 分配了一塊連續的儲存空間，以順序追加的方式記錄 Redo Log，通過順序 IO 來改善效能。所有的事務共享 redo log 的儲存空間，它們的 Redo Log 按語句的執行順序，依次交替的記錄在一起。
* **undo log（回滾日誌）** 實現一致性
undo log 主要為事務的回滾服務。在事務執行的過程中，除了記錄 redo log，還會記錄一定量的 undo log。undo log 記錄了資料在每個操作前的狀態，如果事務執行過程中需要回滾，就可以根據 undo log 進行回滾操作。單個事務的回滾，只會回滾當前事務做的操作，並不會影響到其他的事務做的操作。
Undo 記錄的是已部分完成並且寫入硬碟的未完成的事務，預設情況下回滾日誌是記錄下表空間中的（共享表空間或者獨享表空間）
二種日誌均可以視為一種恢復操作，redo_log 是恢復提交事務修改的頁操作，而 undo_log 是回滾行記錄到特定版本。二者記錄的內容也不同，redo_log 是物理日誌，記錄頁的物理修改操作，而 undo_log 是邏輯日誌，根據每行記錄進行記錄。
> ❝
>
> 又引出個問題：你知道 MySQL 有多少種日誌嗎？
* **錯誤日誌**：記錄出錯資訊，也記錄一些警告資訊或者正確的資訊。
* **查詢日誌**：記錄所有對資料庫請求的資訊，不論這些請求是否得到了正確的執行。
* **慢查詢日誌**：設定一個閾值，將執行時間超過該值的所有 SQL 語句都記錄到慢查詢的日誌檔案中。
* **二進位制日誌**：記錄對資料庫執行更改的所有操作。
* **中繼日誌**：中繼日誌也是二進位制日誌，用來給 slave 庫恢復
* **事務日誌**：重做日誌 redo 和回滾日誌 undo
> ❝
>
> 分散式事務相關問題，可能還會問到 2PC、3PC，，，
### MySQL 對分散式事務的支援
分散式事務的實現方式有很多，既可以採用 InnoDB 提供的原生的事務支援，也可以採用訊息佇列來實現分散式事務的最終一致性。這裡我們主要聊一下 InnoDB 對分散式事務的支援。
MySQL 從 5.0.3 InnoDB 儲存引擎開始支援 XA 協議的分散式事務。一個分散式事務會涉及多個行動，這些行動本身是事務性的。所有行動都必須一起成功完成，或者一起被回滾。
在 MySQL 中，使用分散式事務涉及一個或多個資源管理器和一個事務管理器。
如圖，MySQL 的分散式事務模型。模型中分三塊：應用程式（AP）、資源管理器（RM）、事務管理器（TM）:
* 應用程式：定義了事務的邊界，指定需要做哪些事務；
* 資源管理器：提供了訪問事務的方法，通常一個資料庫就是一個資源管理器；
* 事務管理器：協調參與了全域性事務中的各個事務。
分散式事務採用兩段式提交（two-phase commit）的方式：
* 第一階段所有的事務節點開始準備，告訴事務管理器 ready。
* 第二階段事務管理器告訴每個節點是 commit 還是 rollback。如果有一個節點失敗，就需要全域性的節點全部 rollback，以此保障事務的原子性。
* * *
七、MySQL 鎖機制
-----------
> ❝
>
> 資料庫的樂觀鎖和悲觀鎖？
>
> MySQL 中有哪幾種鎖，列舉一下？
>
> MySQL 中 InnoDB 引擎的行鎖是怎麼實現的？
>
> MySQL 間隙鎖有沒有了解，死鎖有沒有了解，寫一段會造成死鎖的 sql 語句，死鎖發生瞭如何解決，MySQL 有沒有提供什麼機制去解決死鎖
鎖是計算機協調多個程式或執行緒併發訪問某一資源的機制。
在資料庫中，除傳統的計算資源（如 CPU、RAM、I/O 等）的爭用以外，資料也是一種供許多使用者共享的資源。資料庫鎖定機制簡單來說，就是資料庫為了保證資料的一致性，而使各種共享資源在被併發訪問變得有序所設計的一種規則。
打個比方，我們到淘寶上買一件商品，商品只有一件庫存，這個時候如果還有另一個人買，那麼如何解決是你買到還是另一個人買到的問題？這裡肯定要用到事物，我們先從庫存表中取出物品數量，然後插入訂單，付款後插入付款表資訊，然後更新商品數量。在這個過程中，使用鎖可以對有限的資源進行保護，解決隔離和併發的矛盾。
### 鎖的分類
**從對資料操作的型別分類**：
* **讀鎖**（共享鎖）：針對同一份資料，多個讀操作可以同時進行，不會互相影響
* **寫鎖**（排他鎖）：當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖
**從對資料操作的粒度分類**：
為了儘可能提高資料庫的併發度，每次鎖定的資料範圍越小越好，理論上每次只鎖定當前操作的資料的方案會得到最大的併發度，但是管理鎖是很耗資源的事情（涉及獲取，檢查，釋放鎖等動作），因此資料庫系統需要在高併發響應和系統效能兩方面進行平衡，這樣就產生了 “鎖粒度（Lock granularity）” 的概念。
* **表級鎖**：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高，併發度最低（MyISAM 和 MEMORY 儲存引擎採用的是表級鎖）；
* **行級鎖**：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，併發度也最高（InnoDB 儲存引擎既支援行級鎖也支援表級鎖，但預設情況下是採用行級鎖）；
* **頁面鎖**：開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，併發度一般。
適用：從鎖的角度來說，表級鎖更適合於以查詢為主，只有少量按索引條件更新資料的應用，如 Web 應用；而行級鎖則更適合於有大量按索引條件併發更新少量不同資料，同時又有併發查詢的應用，如一些線上事務處理（OLTP）系統。
|
| 行鎖 | 表鎖 | 頁鎖 |
| ------ | ---- | ---- |
| MyISAM | | |
| √ | | |
|
| BDB |
| √ | √ |
| InnoDB | √ | √ |
|
| Memory |
| √ |
|
### MyISAM 表鎖
MyISAM 的表鎖有兩種模式：
* 表共享讀鎖 （Table Read Lock）：不會阻塞其他使用者對同一表的讀請求，但會阻塞對同一表的寫請求；
* 表獨佔寫鎖 （Table Write Lock）：會阻塞其他使用者對同一表的讀和寫操作；
MyISAM 表的讀操作與寫操作之間，以及寫操作之間是序列的。當一個執行緒獲得對一個表的寫鎖後， 只有持有鎖的執行緒可以對錶進行更新操作。其他執行緒的讀、 寫操作都會等待，直到鎖被釋放為止。
預設情況下，寫鎖比讀鎖具有更高的優先順序：當一個鎖釋放時，這個鎖會優先給寫鎖佇列中等候的獲取鎖請求，然後再給讀鎖佇列中等候的獲取鎖請求。
### InnoDB 行鎖
InnoDB 實現了以下兩種型別的**行鎖**：
* 共享鎖（S）：允許一個事務去讀一行，阻止其他事務獲得相同資料集的排他鎖。
* 排他鎖（X）：允許獲得排他鎖的事務更新資料，阻止其他事務取得相同資料集的共享讀鎖和排他寫鎖。
為了允許行鎖和表鎖共存，實現多粒度鎖機制，InnoDB 還有兩種內部使用的意向鎖（Intention Locks），這兩種意向鎖都是**表鎖**：
* 意向共享鎖（IS）：事務打算給資料行加行共享鎖，事務在給一個資料行加共享鎖前必須先取得該表的 IS 鎖。
* 意向排他鎖（IX）：事務打算給資料行加行排他鎖，事務在給一個資料行加排他鎖前必須先取得該表的 IX 鎖。
**索引失效會導致行鎖變表鎖**。比如 vchar 查詢不寫單引號的情況。
#### 加鎖機制
**樂觀鎖與悲觀鎖是兩種併發控制的思想，可用於解決丟失更新問題**
樂觀鎖會 “樂觀地” 假定大概率不會發生併發更新衝突，訪問、處理資料過程中不加鎖，只在更新資料時再根據版本號或時間戳判斷是否有衝突，有則處理，無則提交事務。用資料版本（Version）記錄機制實現，這是樂觀鎖最常用的一種實現方式
悲觀鎖會 “悲觀地” 假定大概率會發生併發更新衝突，訪問、處理資料前就加排他鎖，在整個資料處理過程中鎖定資料，事務提交或回滾後才釋放鎖。另外與樂觀鎖相對應的，**悲觀鎖是由資料庫自己實現了的，要用的時候，我們直接呼叫資料庫的相關語句就可以了。**
#### 鎖模式 (InnoDB 有三種行鎖的演算法)
* **記錄鎖 (Record Locks)**：單個行記錄上的鎖。對索引項加鎖，鎖定符合條件的行。其他事務不能修改和刪除加鎖項；
SELECT * FROM table WHERE id = 1 FOR UPDATE;
它會在 id=1 的記錄上加上記錄鎖，以阻止其他事務插入，更新，刪除 id=1 這一行
在通過 主鍵索引 與 唯一索引 對資料行進行 UPDATE 操作時，也會對該行資料加記錄鎖：
– id 列為主鍵列或唯一索引列
UPDATE SET age = 50 WHERE id = 1;
* **間隙鎖（Gap Locks）**：當我們使用範圍條件而不是相等條件檢索資料，並請求共享或排他鎖時，InnoDB 會給符合條件的已有資料記錄的索引項加鎖。對於鍵值在條件範圍內但並不存在的記錄，叫做 “間隙”。
InnoDB 也會對這個 “間隙” 加鎖，這種鎖機制就是所謂的間隙鎖。
對索引項之間的 “間隙” 加鎖，鎖定記錄的範圍（對第一條記錄前的間隙或最後一條將記錄後的間隙加鎖），不包含索引項本身。其他事務不能在鎖範圍內插入資料，這樣就防止了別的事務新增幻影行。
間隙鎖基於非唯一索引，它鎖定一段範圍內的索引記錄。間隙鎖基於下面將會提到的`Next-Key Locking` 演算法，請務必牢記：**使用間隙鎖鎖住的是一個區間，而不僅僅是這個區間中的每一條資料**。
SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;
即所有在`（1，10）`區間內的記錄行都會被鎖住，所有 id 為 2、3、4、5、6、7、8、9 的資料行的插入會被阻塞，但是 1 和 10 兩條記錄行並不會被鎖住。
GAP 鎖的目的，是為了防止同一事務的兩次當前讀，出現幻讀的情況
* **臨鍵鎖 (Next-key Locks)**：**臨鍵鎖**，是**記錄鎖與間隙鎖的組合**，它的封鎖範圍，既包含索引記錄，又包含索引區間。(臨鍵鎖的主要目的，也是為了避免**幻讀** (Phantom Read)。如果把事務的隔離級別降級為 RC，臨鍵鎖則也會失效。)
Next-Key 可以理解為一種特殊的**間隙鎖**，也可以理解為一種特殊的**演算法**。通過**臨建鎖**可以解決幻讀的問題。每個資料行上的非唯一索引列上都會存在一把臨鍵鎖，當某個事務持有該資料行的臨鍵鎖時，會鎖住一段左開右閉區間的資料。需要強調的一點是，`InnoDB` 中行級鎖是基於索引實現的，臨鍵鎖只與非唯一索引列有關，在唯一索引列（包括主鍵列）上不存在臨鍵鎖。
對於行的查詢，都是採用該方法，主要目的是解決幻讀的問題。
> ❝
>
> select for update 有什麼含義，會鎖表還是鎖行還是其他
for update 僅適用於 InnoDB，且必須在事務塊 (BEGIN/COMMIT) 中才能生效。在進行事務操作時，通過 “for update” 語句，MySQL 會對查詢結果集中每行資料都新增排他鎖，其他執行緒對該記錄的更新與刪除操作都會阻塞。排他鎖包含行鎖、表鎖。
InnoDB 這種行鎖實現特點意味著：只有通過索引條件檢索資料，InnoDB 才使用行級鎖，否則，InnoDB 將使用表鎖！假設有個表單 products ，裡面有 id 跟 name 二個欄位，id 是主鍵。
* 明確指定主鍵，並且有此筆資料，row lock
SELECT * FROM products WHERE id=’3’ FOR UPDATE;
SELECT * FROM products WHERE id=’3’ and type=1 FOR UPDATE;
* 明確指定主鍵，若查無此筆資料，無 lock
SELECT * FROM products WHERE id=’-1’ FOR UPDATE;
* 無主鍵，table lock
SELECT * FROM products WHERE name=’Mouse’ FOR UPDATE;
* 主鍵不明確，table lock
SELECT * FROM products WHERE id<>’3’ FOR UPDATE;
* 主鍵不明確，table lock
SELECT * FROM products WHERE id LIKE ‘3’ FOR UPDATE;
**注 1**: FOR UPDATE 僅適用於 InnoDB，且必須在交易區塊 (BEGIN/COMMIT) 中才能生效。**注 2**: 要測試鎖定的狀況，可以利用 MySQL 的 Command Mode ，開二個視窗來做測試。
> ❝
>
> MySQL 遇到過死鎖問題嗎，你是如何解決的？
### 死鎖
**死鎖產生**：
* 死鎖是指兩個或多個事務在同一資源上相互佔用，並請求鎖定對方佔用的資源，從而導致惡性迴圈
* 當事務試圖以不同的順序鎖定資源時，就可能產生死鎖。多個事務同時鎖定同一個資源時也可能會產生死鎖
* 鎖的行為和順序和儲存引擎相關。以同樣的順序執行語句，有些儲存引擎會產生死鎖有些不會——死鎖有雙重原因：真正的資料衝突；儲存引擎的實現方式。
**檢測死鎖**：資料庫系統實現了各種死鎖檢測和死鎖超時的機制。InnoDB 儲存引擎能檢測到死鎖的迴圈依賴並立即返回一個錯誤。
**死鎖恢復**：死鎖發生以後，只有部分或完全回滾其中一個事務，才能打破死鎖，InnoDB 目前處理死鎖的方法是，將持有最少行級排他鎖的事務進行回滾。所以事務型應用程式在設計時必須考慮如何處理死鎖，多數情況下只需要重新執行因死鎖回滾的事務即可。
**外部鎖的死鎖檢測**：發生死鎖後，InnoDB 一般都能自動檢測到，並使一個事務釋放鎖並回退，另一個事務獲得鎖，繼續完成事務。但在涉及外部鎖，或涉及表鎖的情況下，InnoDB 並不能完全自動檢測到死鎖， 這需要通過設定鎖等待超時引數 innodb_lock_wait_timeout 來解決
**死鎖影響效能**：死鎖會影響效能而不是會產生嚴重錯誤，因為 InnoDB 會自動檢測死鎖狀況並回滾其中一個受影響的事務。在高併發系統上，當許多執行緒等待同一個鎖時，死鎖檢測可能導致速度變慢。有時當發生死鎖時，禁用死鎖檢測（使用 innodb_deadlock_detect 配置選項）可能會更有效，這時可以依賴`innodb_lock_wait_timeout`設定進行事務回滾。
**MyISAM 避免死鎖**：
* 在自動加鎖的情況下，MyISAM 總是一次獲得 SQL 語句所需要的全部鎖，所以 MyISAM 表不會出現死鎖。
**InnoDB 避免死鎖**：
* 為了在單個 InnoDB 表上執行多個併發寫入操作時避免死鎖，可以在事務開始時通過為預期要修改的每個元祖（行）使用`SELECT ... FOR UPDATE`語句來獲取必要的鎖，即使這些行的更改語句是在之後才執行的。
* 在事務中，如果要更新記錄，應該直接申請足夠級別的鎖，即排他鎖，而不應先申請共享鎖、更新時再申請排他鎖，因為這時候當使用者再申請排他鎖時，其他事務可能又已經獲得了相同記錄的共享鎖，從而造成鎖衝突，甚至死鎖
* 如果事務需要修改或鎖定多個表，則應在每個事務中以相同的順序使用加鎖語句。在應用中，如果不同的程式會併發存取多個表，應儘量約定以相同的順序來訪問表，這樣可以大大降低產生死鎖的機會
* 通過`SELECT ... LOCK IN SHARE MODE`獲取行的讀鎖後，如果當前事務再需要對該記錄進行更新操作，則很有可能造成死鎖。
* 改變事務隔離級別
如果出現死鎖，可以用 `show engine innodb status;`命令來確定最後一個死鎖產生的原因。返回結果中包括死鎖相關事務的詳細資訊，如引發死鎖的 SQL 語句，事務已經獲得的鎖，正在等待什麼鎖，以及被回滾的事務等。據此可以分析死鎖產生的原因和改進措施。
* * *
八、MySQL 調優
----------
> ❝
>
> 日常工作中你是怎麼優化 SQL 的？
>
> SQL 優化的一般步驟是什麼，怎麼看執行計劃（explain），如何理解其中各個欄位的含義？
>
> 如何寫 sql 能夠有效的使用到複合索引？
>
> 一條 sql 執行過長的時間，你如何優化，從哪些方面入手？
>
> 什麼是最左字首原則？什麼是最左匹配原則？
### 影響 mysql 的效能因素
* 業務需求對 MySQL 的影響 (合適合度)
* 儲存定位對 MySQL 的影響
* 系統各種配置及規則資料
* 活躍使用者的基本資訊資料
* 活躍使用者的個性化定製資訊資料
* 準實時的統計資訊資料
* 其他一些訪問頻繁但變更較少的資料
* 二進位制多媒體資料
* 流水佇列資料
* 超大文字資料
* 不適合放進 MySQL 的資料
* 需要放進快取的資料
* Schema 設計對系統的效能影響
* 儘量減少對資料庫訪問的請求
* 儘量減少無用資料的查詢請求
* 硬體環境對系統效能的影響
### 效能分析
#### MySQL Query Optimizer
1. MySQL 中有專門負責優化 SELECT 語句的優化器模組，主要功能：通過計算分析系統中收集到的統計資訊，為客戶端請求的 Query 提供他認為最優的執行計劃（他認為最優的資料檢索方式，但不見得是 DBA 認為是最優的，這部分最耗費時間）
2. 當客戶端向 MySQL 請求一條 Query，命令解析器模組完成請求分類，區別出是 SELECT 並轉發給 MySQL Query Optimize r 時，MySQL Query Optimizer 首先會對整條 Query 進行優化，處理掉一些常量表示式的預算，直接換算成常量值。並對 Query 中的查詢條件進行簡化和轉換，如去掉一些無用或顯而易見的條件、結構調整等。然後分析 Query 中的 Hint 資訊（如果有），看顯示 Hint 資訊是否可以完全確定該 Query 的執行計劃。如果沒有 Hint 或 Hint 資訊還不足以完全確定執行計劃，則會讀取所涉及物件的統計資訊，根據 Query 進行寫相應的計算分析，然後再得出最後的執行計劃。
#### MySQL 常見瓶頸
* CPU：CPU 在飽和的時候一般發生在資料裝入記憶體或從磁碟上讀取資料時候
* IO：磁碟 I/O 瓶頸發生在裝入資料遠大於記憶體容量的時候
* 伺服器硬體的效能瓶頸：top，free，iostat 和 vmstat 來檢視系統的效能狀態
#### 效能下降 SQL 慢 執行時間長 等待時間長 原因分析
* 查詢語句寫的爛
* 索引失效（單值、複合）
* 關聯查詢太多 join（設計缺陷或不得已的需求）
* 伺服器調優及各個引數設定（緩衝、執行緒數等）
#### MySQL 常見效能分析手段
在優化 MySQL 時，通常需要對資料庫進行分析，常見的分析手段有**慢查詢日誌**，**EXPLAIN 分析查詢**，**profiling 分析**以及 **show 命令查詢系統狀態及系統變數**，通過定位分析效能的瓶頸，才能更好的優化資料庫系統的效能。
##### 效能瓶頸定位
我們可以通過 show 命令檢視 MySQL 狀態及變數，找到系統的瓶頸：
Mysql> show status ——顯示狀態資訊（擴充套件show status like ‘XXX’）
Mysql> show variables ——顯示系統變數（擴充套件show variables like ‘XXX’）
Mysql> show innodb status ——顯示InnoDB儲存引擎的狀態
Mysql> show processlist ——檢視當前SQL執行，包括執行狀態、是否鎖表等
Shell> mysqladmin variables -u username -p password——顯示系統變數
Shell> mysqladmin extended-status -u username -p password——顯示狀態資訊
##### Explain(執行計劃)
是什麼：使用 **Explain** 關鍵字可以模擬優化器執行 SQL 查詢語句，從而知道 MySQL 是如何處理你的 SQL 語句的。分析你的查詢語句或是表結構的效能瓶頸
能幹嗎：
* 表的讀取順序
* 資料讀取操作的操作型別
* 哪些索引可以使用
* 哪些索引被實際使用
* 表之間的引用
* 每張表有多少行被優化器查詢
怎麼玩：
* Explain + SQL 語句
* 執行計劃包含的資訊（如果有分割槽表的話還會有 **partitions**）
expalin
各欄位解釋
* **id**（select 查詢的序列號，包含一組數字，表示查詢中執行 select 子句或操作表的順序）
* id 相同，執行順序從上往下
* id 全不同，如果是子查詢，id 的序號會遞增，id 值越大優先順序越高，越先被執行
* id 部分相同，執行順序是先按照數字大的先執行，然後數字相同的按照從上往下的順序執行
* **select_type**（查詢型別，用於區別普通查詢、聯合查詢、子查詢等複雜查詢）
* **SIMPLE** ：簡單的 select 查詢，查詢中不包含子查詢或 UNION
* **PRIMARY**：查詢中若包含任何複雜的子部分，最外層查詢被標記為 PRIMARY
* **SUBQUERY**：在 select 或 where 列表中包含了子查詢
* **DERIVED**：在 from 列表中包含的子查詢被標記為 DERIVED，MySQL 會遞迴執行這些子查詢，把結果放在臨時表裡
* **UNION**：若第二個 select 出現在 UNION 之後，則被標記為 UNION，若 UNION 包含在 from 子句的子查詢中，外層 select 將被標記為 DERIVED
* **UNION RESULT**：從 UNION 表獲取結果的 select
* **table**（顯示這一行的資料是關於哪張表的）
* **type**（顯示查詢使用了那種型別，從最好到最差依次排列 **system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL** ）
tip: 一般來說，得保證查詢至少達到 range 級別，最好到達 ref
* system：表只有一行記錄（等於系統表），是 const 型別的特例，平時不會出現
* const：表示通過索引一次就找到了，const 用於比較 primary key 或 unique 索引，因為只要匹配一行資料，所以很快，如將主鍵置於 where 列表中，mysql 就能將該查詢轉換為一個常量
* eq_ref：唯一性索引掃描，對於每個索引鍵，表中只有一條記錄與之匹配，常見於主鍵或唯一索引掃描
* ref：非唯一性索引掃描，範圍匹配某個單獨值得所有行。本質上也是一種索引訪問，他返回所有匹配某個單獨值的行，然而，它可能也會找到多個符合條件的行，多以他應該屬於查詢和掃描的混合體
* range：只檢索給定範圍的行，使用一個索引來選擇行。key 列顯示使用了哪個索引，一般就是在你的 where 語句中出現了 between、<、>、in 等的查詢，這種範圍掃描索引比全表掃描要好，因為它只需開始於索引的某一點，而結束於另一點，不用掃描全部索引
* index：Full Index Scan，index 於 ALL 區別為 index 型別只遍歷索引樹。通常比 ALL 快，因為索引檔案通常比資料檔案小。（**也就是說雖然 all 和 index 都是讀全表，但 index 是從索引中讀取的，而 all 是從硬碟中讀的**）
* ALL：Full Table Scan，將遍歷全表找到匹配的行
* **possible_keys**（顯示可能應用在這張表中的索引，一個或多個，查詢涉及到的欄位若存在索引，則該索引將被列出，但不一定被查詢實際使用）
* **key**
* 實際使用的索引，如果為 NULL，則沒有使用索引
* **查詢中若使用了覆蓋索引，則該索引和查詢的 select 欄位重疊，僅出現在 key 列表中**
 explain-key
* **key_len**
* 表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度。在不損失精確性的情況下，長度越短越好
* key_len 顯示的值為索引欄位的最大可能長度，並非實際使用長度，即 key_len 是根據表定義計算而得，不是通過表內檢索出的
* **ref**（顯示索引的哪一列被使用了，如果可能的話，是一個常數。哪些列或常量被用於查詢索引列上的值）
* **rows**（根據表統計資訊及索引選用情況，大致估算找到所需的記錄所需要讀取的行數）
* **Extra**（包含不適合在其他列中顯示但十分重要的額外資訊）
1. using filesort: 說明 mysql 會對資料使用一個外部的索引排序，不是按照表內的索引順序進行讀取。mysql 中無法利用索引完成的排序操作稱為 “檔案排序”。常見於 order by 和 group by 語句中
2. Using temporary：使用了臨時表儲存中間結果，mysql 在對查詢結果排序時使用臨時表。常見於排序 order by 和分組查詢 group by。
3. using index：表示相應的 select 操作中使用了覆蓋索引，避免訪問了表的資料行，效率不錯，如果同時出現 using where，表明索引被用來執行索引鍵值的查詢；否則索引被用來讀取資料而非執行查詢操作
4. using where：使用了 where 過濾
5. using join buffer：使用了連線快取
6. impossible where：where 子句的值總是 false，不能用來獲取任何元祖
7. select tables optimized away：在沒有 group by 子句的情況下，基於索引優化操作或對於 MyISAM 儲存引擎優化 COUNT(*) 操作，不必等到執行階段再進行計算，查詢執行計劃生成的階段即完成優化
8. distinct：優化 distinct 操作，在找到第一匹配的元祖後即停止找同樣值的動作
**case**:
explain-demo
1. 第一行（執行順序 4）：id 列為 1，表示是 union 裡的第一個 select，select_type 列的 primary 表示該查詢為外層查詢，table 列被標記為，表示查詢結果來自一個衍生表，其中 derived3 中 3 代表該查詢衍生自第三個 select 查詢，即 id 為 3 的 select。【select d1.name......】
2. 第二行（執行順序 2）：id 為 3，是整個查詢中第三個 select 的一部分。因查詢包含在 from 中，所以為 derived。【select id,name from t1 where other_column=''】
3. 第三行（執行順序 3）：select 列表中的子查詢 select_type 為 subquery，為整個查詢中的第二個 select。【select id from t3】
4. 第四行（執行順序 1）：select_type 為 union，說明第四個 select 是 union 裡的第二個 select，最先執行【select name,id from t2】
5. 第五行（執行順序 5）：代表從 union 的臨時表中讀取行的階段，table 列的 <union1,4> 表示用第一個和第四個 select 的結果進行 union 操作。【兩個結果 union 操作】
##### 慢查詢日誌
MySQL 的慢查詢日誌是 MySQL 提供的一種日誌記錄，它用來記錄在 MySQL 中響應時間超過閾值的語句，具體指執行時間超過 `long_query_time` 值的 SQL，則會被記錄到慢查詢日誌中。
* `long_query_time` 的預設值為 10，意思是執行 10 秒以上的語句
* 預設情況下，MySQL 資料庫沒有開啟慢查詢日誌，需要手動設定引數開啟
**檢視開啟狀態**
SHOW VARIABLES LIKE ‘%slow_query_log%’
**開啟慢查詢日誌**
* 臨時配置：
mysql> set global slow_query_log=’ON’;
mysql> set global slow_query_log_file=’/var/lib/mysql/hostname-slow.log’;
mysql> set global long_query_time=2;
也可 set 檔案位置，系統會預設給一個預設檔案 host_name-slow.log
使用 set 操作開啟慢查詢日誌只對當前資料庫生效，如果 MySQL 重啟則會失效。
* 永久配置
修改配置檔案 my.cnf 或 my.ini，在 [mysqld] 一行下面加入兩個配置引數
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/hostname-slow.log
long_query_time = 3
注：log-slow-queries 引數為慢查詢日誌存放的位置，一般這個目錄要有 MySQL 的執行帳號的可寫許可權，一般都將這個目錄設定為 MySQL 的資料存放目錄；long_query_time=2 中的 2 表示查詢超過兩秒才記錄；在 my.cnf 或者 my.ini 中新增 log-queries-not-using-indexes 引數，表示記錄下沒有使用索引的查詢。
可以用 `select sleep(4)` 驗證是否成功開啟。
在生產環境中，如果手工分析日誌，查詢、分析 SQL，還是比較費勁的，所以 MySQL 提供了日誌分析工具 **mysqldumpslow**。
通過 mysqldumpslow --help 檢視操作幫助資訊
* 得到返回記錄集最多的 10 個 SQL
`mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log`
* 得到訪問次數最多的 10 個 SQL
`mysqldumpslow -s c -t 10 /var/lib/mysql/hostname-slow.log`
* 得到按照時間排序的前 10 條裡面含有左連線的查詢語句
`mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hostname-slow.log`
* 也可以和管道配合使用
`mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log | more`
**也可使用 pt-query-digest 分析 RDS MySQL 慢查詢日誌**
##### Show Profile 分析查詢
通過慢日誌查詢可以知道哪些 SQL 語句執行效率低下，通過 explain 我們可以得知 SQL 語句的具體執行情況，索引使用等，還可以結合`Show Profile`命令檢視執行狀態。
* Show Profile 是 MySQL 提供可以用來分析當前會話中語句執行的資源消耗情況。可以用於 SQL 的調優的測量
* 預設情況下，引數處於關閉狀態，並儲存最近 15 次的執行結果
* 分析步驟
mysql> show profiles; +----------+------------+---------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------+ | 1 | 0.00385450 | show variables like "profiling" | | 2 | 0.00170050 | show variables like "profiling" | | 3 | 0.00038025 | select * from t_base_user | +----------+------------+---------------------------------+
* converting HEAP to MyISAM 查詢結果太大，記憶體都不夠用了往磁碟上搬了。
* create tmp table 建立臨時表，這個要注意
* Copying to tmp table on disk 把記憶體臨時表複製到磁碟
* locked
5. 診斷 SQL，show profile cpu,block io for query id(上一步前面的問題 SQL 數字號碼)
6. 日常開發需要注意的結論
7. 是否支援，看看當前的 mysql 版本是否支援
mysql>Show variables like ‘profiling’; –預設是關閉，使用前需要開啟
8. 開啟功能，預設是關閉，使用前需要開啟
mysql>set profiling=1;
9. 執行 SQL
10. 檢視結果
> ❝
>
> 查詢中哪些情況不會使用索引？
### 效能優化
#### 索引優化
1. 全值匹配我最愛
2. 最佳左字首法則，比如建立了一個聯合索引 (a,b,c)，那麼其實我們可利用的索引就有 (a), (a,b), (a,b,c)
3. 不在索引列上做任何操作（計算、函式、(自動 or 手動) 型別轉換），會導致索引失效而轉向全表掃描
4. 儲存引擎不能使用索引中範圍條件右邊的列
5. 儘量使用覆蓋索引 (只訪問索引的查詢 (索引列和查詢列一致))，減少 select
6. is null ,is not null 也無法使用索引
7. like "xxxx%" 是可以用到索引的，like "%xxxx" 則不行 (like "%xxx%" 同理)。like 以萬用字元開頭('%abc...') 索引失效會變成全表掃描的操作，
8. 字串不加單引號索引失效
9. 少用 or，用它來連線時會索引失效
10. <，<=，=，>，>=，BETWEEN，IN 可用到索引，<>，not in ，!= 則不行，會導致全表掃描
**一般性建議**
* 對於單鍵索引，儘量選擇針對當前 query 過濾性更好的索引
* 在選擇組合索引的時候，當前 Query 中過濾性最好的欄位在索引欄位順序中，位置越靠前越好。
* 在選擇組合索引的時候，儘量選擇可以能夠包含當前 query 中的 where 字句中更多欄位的索引
* 儘可能通過分析統計資訊和調整 query 的寫法來達到選擇合適索引的目的
* 少用 Hint 強制索引
#### 查詢優化
**永遠小標驅動大表（小的資料集驅動大的資料集）**
slect * from A where id in (select id from B)`等價於
#等價於
select id from B
select * from A where A.id=B.id
當 B 表的資料集必須小於 A 表的資料集時，用 in 優於 exists
select * from A where exists (select 1 from B where B.id=A.id)
#等價於
select * from A
select * from B where B.id = A.id`
當 A 表的資料集小於 B 表的資料集時，用 exists 優於用 in
注意：A 表與 B 表的 ID 欄位應建立索引。
**order by 關鍵字優化**
* order by 子句，儘量使用 Index 方式排序，避免使用 FileSort 方式排序
* MySQL 支援兩種方式的排序，FileSort 和 Index，Index 效率高，它指 MySQL 掃描索引本身完成排序，FileSort 效率較低；
* ORDER BY 滿足兩種情況，會使用 Index 方式排序；①ORDER BY 語句使用索引最左前列 ②使用 where 子句與 ORDER BY 子句條件列組合滿足索引最左前列
* 儘可能在索引列上完成排序操作，遵照索引建的最佳最字首
* 如果不在索引列上，filesort 有兩種演算法，mysql 就要啟動雙路排序和單路排序
* 雙路排序：MySQL 4.1 之前是使用雙路排序, 字面意思就是兩次掃描磁碟，最終得到資料
* 單路排序：從磁碟讀取查詢需要的所有列，按照 order by 列在 buffer 對它們進行排序，然後掃描排序後的列表進行輸出，效率高於雙路排序
* 優化策略
* 增大 sort_buffer_size 引數的設定
* 增大 max_lencth_for_sort_data 引數的設定
**GROUP BY 關鍵字優化**
* group by 實質是先排序後進行分組，遵照索引建的最佳左字首
* 當無法使用索引列，增大 `max_length_for_sort_data` 引數的設定，增大`sort_buffer_size`引數的設定
* where 高於 having，能寫在 where 限定的條件就不要去 having 限定了
#### 資料型別優化
MySQL 支援的資料型別非常多，選擇正確的資料型別對於獲取高效能至關重要。不管儲存哪種型別的資料，下面幾個簡單的原則都有助於做出更好的選擇。
* 更小的通常更好：一般情況下，應該儘量使用可以正確儲存資料的最小資料型別。
簡單就好：簡單的資料型別通常需要更少的 CPU 週期。例如，整數比字元操作代價更低，因為字符集和校對規則（排序規則）使字元比較比整型比較複雜。
* 儘量避免 NULL：通常情況下最好指定列為 NOT NULL
* * *
九、分割槽、分表、分庫
----------
### MySQL 分割槽
一般情況下我們建立的表對應一組儲存檔案，使用`MyISAM`儲存引擎時是一個`.MYI`和`.MYD`檔案，使用`Innodb`儲存引擎時是一個`.ibd`和`.frm`（表結構）檔案。
當資料量較大時（一般千萬條記錄級別以上），MySQL 的效能就會開始下降，這時我們就需要將資料分散到多組儲存檔案，保證其單個檔案的執行效率
**能幹嘛**
* 邏輯資料分割
* 提高單一的寫和讀應用速度
* 提高分割槽範圍讀查詢的速度
* 分割資料能夠有多個不同的物理檔案路徑
* 高效的儲存歷史資料
**怎麼玩**
首先檢視當前資料庫是否支援分割槽
* MySQL5.6 以及之前版本：
SHOW VARIABLES LIKE ‘%partition%’;
* MySQL5.6：
show plugins;
```
分割槽型別及操作
RANGE 分割槽：基於屬於一個給定連續區間的列值，把多行分配給分割槽。mysql 將會根據指定的拆分策略，, 把資料放在不同的表檔案上。相當於在檔案上, 被拆成了小塊. 但是, 對外給客戶的感覺還是一張表，透明的。
按照 range 來分，就是每個庫一段連續的資料，這個一般是按比如時間範圍來的，比如交易表啊，銷售表啊等，可以根據年月來存放資料。可能會產生熱點問題，大量的流量都打在最新的資料上了。
range 來分，好處在於說，擴容的時候很簡單。
LIST 分割槽：類似於按 RANGE 分割槽，每個分割槽必須明確定義。它們的主要區別在於，LIST 分割槽中每個分割槽的定義和選擇是基於某列的值從屬於一個值列表集中的一個值，而 RANGE 分割槽是從屬於一個連續區間值的集合。
HASH 分割槽：基於使用者定義的表示式的返回值來進行選擇的分割槽，該表示式使用將要插入到表中的這些行的列值進行計算。這個函式可以包含 MySQL 中有效的、產生非負整數值的任何表示式。
hash 分發，好處在於說，可以平均分配每個庫的資料量和請求壓力；壞處在於說擴容起來比較麻煩，會有一個資料遷移的過程，之前的資料需要重新計算 hash 值重新分配到不同的庫或表
KEY 分割槽：類似於按 HASH 分割槽，區別在於 KEY 分割槽只支援計算一列或多列，且 MySQL 伺服器提供其自身的雜湊函式。必須有一列或多列包含整數值。
看上去分割槽表很帥氣，為什麼大部分網際網路還是更多的選擇自己分庫分表來水平擴充套件咧？
分割槽表，分割槽鍵設計不太靈活，如果不走分割槽鍵，很容易出現全表鎖
一旦資料併發量上來，如果在分割槽表實施關聯，就是一個災難
自己分庫分表，自己掌控業務場景與訪問模式，可控。分割槽表，研發寫了一個 sql，都不確定 mysql 是怎麼玩的，不太可控
❝
隨著業務的發展，業務越來越複雜，應用的模組越來越多，總的資料量很大，高併發讀寫操作均超過單個資料庫伺服器的處理能力怎麼辦？
這個時候就出現了資料分片，資料分片指按照某個維度將存放在單一資料庫中的資料分散地存放至多個資料庫或表中。資料分片的有效手段就是對關係型資料庫進行分庫和分表。
區別於分割槽的是，分割槽一般都是放在單機裡的，用的比較多的是時間範圍分割槽，方便歸檔。只不過分庫分表需要程式碼實現，分割槽則是 mysql 內部實現。分庫分表和分割槽並不衝突，可以結合使用。
❝
說說分庫與分表的設計
MySQL 分表
分表有兩種分割方式，一種垂直拆分，另一種水平拆分。
垂直拆分
垂直分表，通常是按照業務功能的使用頻次，把主要的、熱門的欄位放在一起做為主要表。然後把不常用的，按照各自的業務屬性進行聚集，拆分到不同的次要表中；主要表和次要表的關係一般都是一對一的。
水平拆分 (資料分片)
單表的容量不超過 500W，否則建議水平拆分。是把一個表複製成同樣表結構的不同表，然後把資料按照一定的規則劃分，分別儲存到這些表中，從而保證單表的容量不會太大，提升效能；當然這些結構一樣的表，可以放在一個或多個資料庫中。
水平分割的幾種方法：
使用 MD5 雜湊，做法是對 UID 進行 md5 加密，然後取前幾位（我們這裡取前兩位），然後就可以將不同的 UID 雜湊到不同的使用者表（user_xx）中了。
還可根據時間放入不同的表，比如：article_201601，article_201602。
按熱度拆分，高點選率的詞條生成各自的一張表，低熱度的詞條都放在一張大表裡，待低熱度的詞條達到一定的貼數後，再把低熱度的表單獨拆分成一張表。
根據 ID 的值放入對應的表，第一個表 user_0000，第二個 100 萬的使用者資料放在第二 個表 user_0001 中，隨使用者增加，直接新增使用者表就行了。
MySQL 分庫
❝
為什麼要分庫?
資料庫叢集環境後都是多臺 slave，基本滿足了讀取操作; 但是寫入或者說大資料、頻繁的寫入操作對 master 效能影響就比較大，這個時候，單庫並不能解決大規模併發寫入的問題，所以就會考慮分庫。
❝
分庫是什麼？
一個庫裡表太多了，導致了海量資料，系統效能下降，把原本儲存於一個庫的表拆分儲存到多個庫上， 通常是將表按照功能模組、關係密切程度劃分出來，部署到不同庫上。
優點：
減少增量資料寫入時的鎖對查詢的影響
由於單表數量下降，常見的查詢操作由於減少了需要掃描的記錄，使得單表單次查詢所需的檢索行數變少，減少了磁碟 IO，時延變短
但是它無法解決單表資料量太大的問題
分庫分表後的難題
分散式事務的問題，資料的完整性和一致性問題。
資料操作維度問題：使用者、交易、訂單各個不同的維度，使用者查詢維度、產品資料分析維度的不同對比分析角度。跨庫聯合查詢的問題，可能需要兩次查詢 跨節點的 count、order by、group by 以及聚合函式問題，可能需要分別在各個節點上得到結果後在應用程式端進行合併 額外的資料管理負擔，如：訪問資料表的導航定位 額外的資料運算壓力，如：需要在多個節點執行，然後再合併計算程式編碼開發難度提升，沒有太好的框架解決，更多依賴業務看如何分，如何合，是個難題。
❝
配主從，正經公司的話，也不會讓 Javaer 去搞的，但還是要知道
十、主從複製
複製的基本原理
slave 會從 master 讀取 binlog 來進行資料同步
三個步驟
img
master 將改變記錄到二進位制日誌（binary log）。這些記錄過程叫做二進位制日誌事件，binary log events；
salve 將 master 的 binary log events 拷貝到它的中繼日誌（relay log）;
slave 重做中繼日誌中的事件，將改變應用到自己的資料庫中。MySQL 複製是非同步且是序列化的。
複製的基本原則
每個 slave 只有一個 master
每個 salve 只能有一個唯一的伺服器 ID
每個 master 可以有多個 salve
複製的最大問題
- 延時
十一、其他問題
說一說三個正規化
第一正規化（1NF）：資料庫表中的欄位都是單一屬性的，不可再分。這個單一屬性由基本型別構成，包括整型、實數、字元型、邏輯型、日期型等。
第二正規化（2NF）：資料庫表中不存在非關鍵欄位對任一候選關鍵欄位的部分函式依賴（部分函式依賴指的是存在組合關鍵字中的某些欄位決定非關鍵欄位的情況），也即所有非關鍵欄位都完全依賴於任意一組候選關鍵字。
第三正規化（3NF）：在第二正規化的基礎上，資料表中如果不存在非關鍵欄位對任一候選關鍵欄位的傳遞函式依賴則符合第三正規化。所謂傳遞函式依賴，指的是如 果存在 “A → B → C” 的決定關係，則 C 傳遞函式依賴於 A。因此，滿足第三正規化的資料庫表應該不存在如下依賴關係：關鍵欄位 → 非關鍵欄位 x → 非關鍵欄位 y
百萬級別或以上的資料如何刪除
關於索引：由於索引需要額外的維護成本，因為索引檔案是單獨存在的檔案, 所以當我們對資料的增加, 修改, 刪除, 都會產生額外的對索引檔案的操作, 這些操作需要消耗額外的 IO, 會降低增 / 改 / 刪的執行效率。所以，在我們刪除資料庫百萬級別資料的時候，查詢 MySQL 官方手冊得知刪除資料的速度和建立的索引數量是成正比的。
所以我們想要刪除百萬資料的時候可以先刪除索引（此時大概耗時三分多鐘）
然後刪除其中無用資料（此過程需要不到兩分鐘）
刪除完成後重新建立索引 (此時資料較少了) 建立索引也非常快，約十分鐘左右。
與之前的直接刪除絕對是要快速很多，更別說萬一刪除中斷, 一切刪除會回滾。那更是坑了。
本文原文地址 mp.weixin.qq.com/s/MCFHNOQnTtJ6MGV...
本作品採用《CC 協議》，轉載必須註明作者和本文連結