學MySQL，這篇萬字總結，真的夠用了
這篇文章將從查詢緩存，索引，優化器，explain，redo 日志，undo 日志，事務隔離級別，鎖等方面來講解 MySQL。
圖片來自 Pexels
開局一張圖
這張圖是重點!!!咱要先對 MySQL 有一個宏觀的了解，知道他的執行流程。
一條 SQL 語句過來的流程是什么樣的?
①當客戶端連接到 MySQL 服務器時，服務器對其進行認證。可以通過用戶名與密碼認證，也可以通過 SSL 證書進行認證。登錄認證后，服務器還會驗證客戶端是否有執行某個查詢的操作權限。
②在正式查詢之前，服務器會檢查查詢緩存，如果能找到對應的查詢，則不必進行查詢解析，優化，執行等過程，直接返回緩存中的結果集。
③MySQL 的解析器會根據查詢語句，構造出一個解析樹，主要用于根據語法規則來驗證語句是否正確，比如 SQL 的關鍵字是否正確，關鍵字的順序是否正確。而預處理器主要是進一步校驗，比如表名，字段名是否正確等。
④查詢優化器將解析樹轉化為查詢計劃，一般情況下，一條查詢可以有很多種執行方式，最終返回相同的結果，優化器就是根據成本找到這其中最優的執行計劃。
⑤執行計劃調用查詢執行引擎，而查詢引擎通過一系列 API 接口查詢到數據。
⑥得到數據之后，在返回給客戶端的同時，會將數據存在查詢緩存中。
查詢緩存
我們先通過 show variables like '%query_cache%' 來看一下默認的數據庫配置，此為本地數據庫的配置。
概念
①have_query_cache：當前的 MySQL 版本是否支持“查詢緩存”功能。
②query_cache_limit：MySQL 能夠緩存的最大查詢結果，查詢結果大于該值時不會被緩存。默認值是 1048576(1MB)。
③query_cache_min_res_unit：查詢緩存分配的最小塊(字節)。默認值是 4096(4KB)。
當查詢進行時，MySQL 把查詢結果保存在 query cache，但是如果保存的結果比較大，超過了 query_cache_min_res_unit 的值，這時候 MySQL 將一邊檢索結果，一邊進行保存結果。
他保存結果也是按默認大小先分配一塊空間，如果不夠，又要申請新的空間給他。
如果查詢結果比較小，默認的 query_cache_min_res_unit 可能造成大量的內存碎片，如果查詢結果比較大，默認的 query_cache_min_res_unit 又不夠，導致一直分配塊空間。
所以可以根據實際需求，調節 query_cache_min_res_unit 的大小。
注：如果上面說的內容有點彎彎繞，那舉個現實生活中的例子，比如咱現在要給運動員送水，默認的是 500ml 的瓶子，如果過來的是少年運動員，可能 500ml 太大了，他們喝不完，造成了浪費。
那我們就可以選擇 300ml 的瓶子，如果過來的是成年運動員，可能 500ml 不夠，那他們一瓶喝完了，又開一瓶，直接不渴為止。那么那樣開瓶子也要時間，我們就可以選擇 1000ml 的瓶子。
④query_cache_size：為緩存查詢結果分配的總內存。
⑤query_cache_type：默認為 on，可以緩存除了以 select sql_no_cache 開頭的所有查詢結果。
⑥query_cache_wlock_invalidate：如果該表被鎖住，是否返回緩存中的數據，默認是關閉的。
原理
MySQL 的查詢緩存實質上是緩存 SQL 的 Hash 值和該 SQL 的查詢結果，如果運行相同的 SQL，服務器直接從緩存中去掉結果，而不再去解析，優化，尋找最低成本的執行計劃等一系列操作，大大提升了查詢速度。
但是萬事有利也有弊：
第一個弊端就是如果表的數據有一條發生變化，那么緩存好的結果將全部不再有效。這對于頻繁更新的表，查詢緩存是不適合的。
比如一張表里面只有兩個字段，分別是 id 和 name，數據有一條為 1，張三。
我使用 select * from 表名 where name=“張三”來進行查詢，MySQL 發現查詢緩存中沒有此數據，會進行一系列的解析，優化等操作進行數據的查詢。
查詢結束之后將該 SQL 的 Hash 和查詢結果緩存起來，并將查詢結果返回給客戶端。
但是這個時候我又新增了一條數據 2，張三。如果我還用相同的 SQL 來執行，他會根據該 SQL 的 Hash 值去查詢緩存中，那么結果就錯了。
所以 MySQL 對于數據有變化的表來說，會直接清空關于該表的所有緩存。這樣其實效率是很差的。
第二個弊端就是緩存機制是通過對 SQL 的 Hash，得出的值為 Key，查詢結果為 Value 來存放的，那么就意味著 SQL 必須完完全全一模一樣，否則就命不中緩存。
我們都知道 Hash 值的規則，就算很小的查詢，哈希出來的結果差距是很多的，所以 select * from 表名 where name=“張三”和SELECT * FROM 表名 WHERE NAME=“張三”和select * from 表名 where name = “張三”，三個SQL 哈希出來的值是不一樣的。
大小寫和空格影響了他們，所以并不能命中緩存，但其實他們搜索結果是完全一樣的。
生產如何設置 MySQL Query Cache
先來看線上參數：
我們發現將 query_cache_type 設置為 OFF，其實網上資料和各大云廠商提供的云服務器都是將這個功能關閉的，從上面的原理來看，在一般情況下，他的弊端大于優點。
索引
例子：創建一個名為 user 的表，其包括 id，name，age，sex 等字段信息。此外，id 為主鍵聚簇索引，idx_name 為非聚簇索引。
- CREATE TABLE `user` (
- `id` varchar(10) NOT NULL DEFAULT '',
- `name` varchar(10) DEFAULT NULL,
- `age` int(11) DEFAULT NULL,
- `sex` varchar(10) DEFAULT NULL,
- PRIMARY KEY (`id`),
- KEY `idx_name` (`name`) USING BTREE
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
我們將其設置 10 條數據，便于下面的索引的理解：
- INSERT INTO `user` VALUES ('1', 'andy', '20', '女');
- INSERT INTO `user` VALUES ('10', 'baby', '12', '女');
- INSERT INTO `user` VALUES ('2', 'kat', '12', '女');
- INSERT INTO `user` VALUES ('3', 'lili', '20', '男');
- INSERT INTO `user` VALUES ('4', 'lucy', '22', '女');
- INSERT INTO `user` VALUES ('5', 'bill', '20', '男');
- INSERT INTO `user` VALUES ('6', 'zoe', '20', '男');
- INSERT INTO `user` VALUES ('7', 'hay', '20', '女');
- INSERT INTO `user` VALUES ('8', 'tony', '20', '男');
- INSERT INTO `user` VALUES ('9', 'rose', '21', '男');
聚簇索引(主鍵索引)
先來一張圖鎮樓，接下來就是看圖說話：
他包含兩個特點：
- 使用記錄主鍵值的大小來進行記錄和頁的排序。頁內的記錄是按照主鍵的大小順序排成一個單項鏈表。各個存放用戶記錄的頁也是根據頁中用戶記錄的主鍵大小順序排成一個雙向鏈表。
- 葉子節點存儲的是完整的用戶記錄。
注：聚簇索引不需要我們顯示的創建，他是由 InnoDB 存儲引擎自動為我們創建的。如果沒有主鍵，其也會默認創建一個。
非聚簇索引(二級索引)
上面的聚簇索引只能在搜索條件是主鍵時才能發揮作用，因為聚簇索引可以根據主鍵進行排序的。
如果搜索條件是 name，在剛才的聚簇索引上，我們可能遍歷，挨個找到符合條件的記錄，但是，這樣真的是太蠢了，MySQL 不會這樣做的。
如果我們想讓搜索條件是 name 的時候，也能使用索引，那可以多創建一個基于 name 的二叉樹，如下圖：
他與聚簇索引的不同：
- 葉子節點內部使用 name 字段排序，葉子節點之間也是使用 name 字段排序。
- 葉子節點不再是完整的數據記錄，而是 name 和主鍵值。
為什么不再是完整信息?MySQL 只讓聚簇索引的葉子節點存放完整的記錄信息，因為如果有好幾個非聚簇索引，他們的葉子節點也存放完整的記錄績效，那就不浪費空間啦。
如果我搜索條件是基于 name，需要查詢所有字段的信息，那查詢過程是啥?
- 根據查詢條件，采用 name 的非聚簇索引，先定位到該非聚簇索引某些記錄行。
- 根據記錄行找到相應的 id，再根據 id 到聚簇索引中找到相關記錄。這個過程叫做回表。
聯合索引
圖就不畫了，簡單來說，如果 name 和 age 組成一個聯合索引，那么先按 name 排序，如果 name 一樣，就按 age 排序。
一些原則
①最左前綴原則。一個聯合索引(a,b,c),如果有一個查詢條件有 a，有 b，那么他則走索引，如果有一個查詢條件沒有 a，那么他則不走索引。
②使用唯一索引。具有多個重復值的列，其索引效果最差。例如，存放姓名的列具有不同值，很容易區分每行。
而用來記錄性別的列，只含有“男”，“女”，不管搜索哪個值，都會得出大約一半的行，這樣的索引對性能的提升不夠高。
③不要過度索引。每個額外的索引都要占用額外的磁盤空間，并降低寫操作的性能。
在修改表的內容時，索引必須進行更新，有時可能需要重構，因此，索引越多，所花的時間越長。
④索引列不能參與計算，保持列“干凈”，比如 from_unixtime(create_time) = ’2014-05-29’就不能使用到索引。
原因很簡單，B+ 樹中存的都是數據表中的字段值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。
所以語句應該寫成：
- create_time = unix_timestamp(’2014-05-29’);
⑤一定要設置一個主鍵。前面聚簇索引說到如果不指定主鍵，InnoDB 會自動為其指定主鍵，這個我們是看不見的。
反正都要生成一個主鍵的，還不如我們設置，以后在某些搜索條件時還能用到主鍵的聚簇索引。
⑥主鍵推薦用自增 id，而不是 uuid。上面的聚簇索引說到每頁數據都是排序的，并且頁之間也是排序的，如果是 uuid，那么其肯定是隨機的，其可能從中間插入，導致頁的分裂，產生很多表碎片。
如果是自增的，那么其有從小到大自增的，有順序，那么在插入的時候就添加到當前索引的后續位置。當一頁寫滿，就會自動開辟一個新的頁。
注：如果自增 id 用完了，那將字段類型改為 bigint，就算每秒 1 萬條數據，跑 100 年，也沒達到 bigint 的最大值。
萬年面試題(為什么索引用 B+ 樹)
①B+ 樹的磁盤讀寫代價更低：B+ 樹的內部節點并沒有指向關鍵字具體信息的指針，因此其內部節點相對 B 樹更小。
如果把所有同一內部節點的關鍵字存放在同一盤塊中，那么盤塊所能容納的關鍵字數量也越多，一次性讀入內存的需要查找的關鍵字也就越多，相對 IO 讀寫次數就降低了。
②由于 B+ 樹的數據都存儲在葉子結點中，分支結點均為索引，方便掃庫，只需要掃一遍葉子結點即可。
但是 B 樹因為其分支結點同樣存儲著數據，我們要找到具體的數據，需要進行一次中序遍歷按序來掃，所以 B+ 樹更加適合在區間查詢的情況，所以通常 B+ 樹用于數據庫索引。
優化器
在開篇的圖里面，我們知道了 SQL 語句從客戶端經由網絡協議到查詢緩存，如果沒有命中緩存，再經過解析工作，得到準確的 SQL，現在就來到了我們這模塊說的優化器。
首先，我們知道每一條 SQL 都有不同的執行方法，要不通過索引，要不通過全表掃描的方式。
那么問題就來了，MySQL 是如何選擇時間最短，占用內存最小的執行方法呢?
什么是成本?
- I/O 成本。數據存儲在硬盤上，我們想要進行某個操作需要將其加載到內存中，這個過程的時間被稱為 I/O 成本。默認是 1。
- CPU 成本。在內存對結果集進行排序的時間被稱為 CPU 成本。默認是 0.2。
單表查詢的成本
先來建一個用戶表 dev_user，里面包括主鍵 id，用戶名 username，密碼 password，外鍵 user_info_id，狀態 status，外鍵 main_station_id，是否外網訪問 visit，這七個字段。
索引有兩個，一個是主鍵的聚簇索引，另一個是顯式添加的以 username 為字段的唯一索引 uname_unique。
如果搜索條件是 select * from dev_user where username='XXX'，那么 MySQL 是如何選擇相關索引呢?
①使用所有可能用到的索引
我們可以看到搜索條件 username，所以可能走 uname_unique 索引。也可以做聚簇索引，也就是全表掃描。
②計算全表掃描代價
我們通過 show table status like ‘dev_user’命令知道 rows 和 data_length 字段，如下圖：
rows：表示表中的記錄條數，但是這個數據不準確，是個估計值。
data_length：表示表占用的存儲空間字節數。data_length=聚簇索引的頁面數量 X 每個頁面的大小。
反推出頁面數量=1589248÷16÷1024=97：
- I/O 成本：97X1=97
- CPU 成本：6141X0.2=1228
- 總成本：97+1228=1325
③計算使用不同索引執行查詢的代價
因為要查詢出滿足條件的所有字段信息，所以要考慮回表成本：
- I/O 成本=1+1X1=2(范圍區間的數量+預計二級記錄索引條數)
- CPU 成本=1X0.2+1X0.2=0.4(讀取二級索引的成本+回表聚簇索引的成本)
- 總成本= I/O 成本+CPU 成本=2.4
④對比各種執行方案的代價，找出成本最低的那個
上面兩個數字一對比，成本是采用 uname_unique 索引成本最低。
多表查詢的成本
對于兩表連接查詢來說，他的查詢成本由下面兩個部分構成：
- 單次查詢驅動表的成本
- 多次查詢被驅動表的成本(具體查詢多次取決于對驅動表查詢的結果集有多少個記錄)
index dive
如果前面的搜索條件不是等值，而是區間，如 select * from dev_user where username>'admin' and username<'test' 這個時候我們是無法看出需要回表的數量。
步驟 1：先根據 username>'admin' 這個條件找到第一條記錄，稱為區間最左記錄。
步驟 2：再根據 username<'test' 這個條件找到最后一條記錄，稱為區間最右記錄。
步驟 3：如果區間最左記錄和區間最右記錄相差不是很遠，可以準確統計出需要回表的數量。
如果相差很遠，就先計算 10 頁有多少條記錄，再乘以頁面數量，最終模糊統計出來。
Explain
產品來索命：
- 產品：為什么這個頁面出來這么慢?
- 開發：因為你查的數據多唄，他就是這么慢
- 產品：我不管，我要這個頁面快點，你這樣，客戶怎么用啊
- 開發：......你行你來
哈哈哈哈，不瞎 BB 啦，如果有些 SQL 賊慢，我們需要知道他有沒有走索引，走了哪個索引，這個時候我就需要通過 explain 關鍵字來深入了解 MySQL 內部是如何執行的。
id：一般來說一個 select 一個唯一 id，如果是子查詢，就有兩個 select，id 是不一樣的，但是凡事有例外，有些子查詢的，他們 id 是一樣的。
這是為什么呢?那是因為 MySQL 在進行優化的時候已經將子查詢改成了連接查詢，而連接查詢的 id 是一樣的。
select_type：
- simple：不包括 union 和子查詢的查詢都算 simple 類型。
- primary：包括 union，union all，其中最左邊的查詢即為 primary。
- union：包括 union，union all，除了最左邊的查詢，其他的查詢類型都為 union。
table：顯示這一行是關于哪張表的。
type 訪問方法：
- ref：普通二級索引與常量進行等值匹配
- ref_or_null：普通二級索引與常量進行等值匹配，該索引可能是 null
- const：主鍵或唯一二級索引列與常量進行等值匹配
- range：范圍區間的查詢
- all：全表掃描
possible_keys：對某表進行單表查詢時可能用到的索引。
key：經過查詢優化器計算不同索引的成本，最終選擇成本最低的索引。
rows：
- 如果使用全表掃描，那么 rows 就代表需要掃描的行數
- 如果使用索引，那么 rows 就代表預計掃描的行數
filtered：
- 如果全表掃描，那么 filtered 就代表滿足搜索條件的記錄的滿分比
- 如果是索引，那么 filtered 就代表除去索引對應的搜索，其他搜索條件的百分比
redo 日志(物理日志)
InnoDB 存儲引擎是以頁為單位來管理存儲空間的，我們進行的增刪改查操作都是將頁的數據加載到內存中，然后進行操作，再將數據刷回到硬盤上。
那么問題就來了，如果我要給張三轉賬 100 塊錢，事務已經提交了，這個時候 InnoDB 把數據加載到內存中，這個時候還沒來得及刷入硬盤，突然停電了，數據庫崩了。
重啟之后，發現我的錢沒有轉成功，這不是尷尬了嗎?
解決方法很明顯，我們在硬盤加載到內存之后，進行一系列操作，一頓操作猛如虎，還未刷新到硬盤之前，先記錄下，在 XXX 位置我的記錄中金額減 100，在 XXX 位置張三的記錄中金額加 100。
然后再進行增刪改查操作，最后刷入硬盤。如果未刷入硬盤，在重啟之后，先加載之前的記錄，那么數據就回來了。
這個記錄就叫做重做日志，即 redo 日志。他的目的是想讓已經提交的事務對數據的修改是永久的，就算他重啟，數據也能恢復出來。
log buffer(日志緩沖區)
為了解決磁盤速度過慢的問題，redo 日志不能直接寫入磁盤，咱先整一大片連續的內存空間給他放數據。
這一大片內存就叫做日志緩沖區，即 log buffer。到了合適的時候，再刷入硬盤。至于什么時候是合適的，這個下一章節說。
我們可以通過 show VARIABLES like 'innodb_log_buffer_size' 命令來查看當前的日志緩存大小。
下圖為線上的大小：
redo 日志刷盤時機
由于 redo 日志一直都是增長的，且內存空間有限，數據也不能一直待在緩存中， 我們需要將其刷新至硬盤上。
那什么時候刷新到硬盤呢?
- log buffer 空間不足。上面有指定緩沖區的內存大小，MySQL 認為日志量已經占了 總容量的一半左右，就需要將這些日志刷新到磁盤上。
- 事務提交時。我們使用 redo 日志的目的就是將他未刷新到磁盤的記錄保存起來，防止丟失，如果數據提交了，我們是可以不把數據提交到磁盤的，但為了保證持久性，必須 把修改這些頁面的 redo 日志刷新到磁盤。
- 后臺線程不同的刷新后臺有一個線程，大概每秒都會將 log buffer 里面的 redo 日志刷新到硬盤上。
- checkpoint 下下小節講。
redo 日志文件組
我們可以通過 show variables like 'datadir' 命令找到相關目錄，底下有兩個文件，分別是 ib_logfile0 和 ib_logfile1，如下圖所示：
我們將緩沖區 log buffer 里面的 redo 日志刷新到這個兩個文件里面，他們寫入的方式 是循環寫入的，先寫 ib_logfile0，再寫 ib_logfile1，等 ib_logfile1 寫滿了，再寫 ib_logfile0。
那這樣就會存在一個問題，如果 ib_logfile1 寫滿了，再寫 ib_logfile0，之前 ib_logfile0 的內容 不就被覆蓋而丟失了嗎?這就是 checkpoint 的工作啦。
checkpoint
redo 日志是為了系統崩潰后恢復臟頁用的，如果這個臟頁可以被刷新到磁盤上，那么 他就可以功成身退，被覆蓋也就沒事啦。
沖突補習：從系統運行開始，就不斷的修改頁面，會不斷的生成 redo 日志。
redo 日志是不斷遞增的，MySQL 為其取了一個名字日志序列號 Log Sequence Number，簡稱 lsn。他的初始化的值為 8704，用來記錄當前一共生成了多少 redo 日志。
redo 日志是先寫入 log buffer，之后才會被刷新到磁盤的 redo 日志文件。MySQL 為其取了一個名字 flush_to_disk_lsn。
用來說明緩存區中有多少的臟頁數據被刷新到磁盤上啦。他的初始值和 lsn 一樣，后面的差距就有了。
做一次 checkpoint 分為兩步：
- 計算當前系統可以被覆蓋的 redo 日志對應的 lsn 最大值是多少。redo 日志可以被覆蓋，意味著他對應的臟頁被刷新到磁盤上。
只要我們計算出當前系統中最早被修改的 oldest_modification，只要系統中 lsn 小于該節點的 oldest_modification 值，磁盤的 redo 日志都是可以被覆蓋的。
- 將 lsn 過程中的一些數據統計。
undo 日志
undo log 有兩個作用：提供回滾和多個行版本控制(MVCC)。
undo log 和 redo log 記錄物理日志不一樣，它是邏輯日志。
可以認為當 delete 一條記錄時，undo log 中會記錄一條對應的 insert 記錄，反之亦然，當 update 一條記錄時，它記錄一條對應相反的 update 記錄。
舉個例子：
- insert into a(id) values(1);(redo)
這條記錄是需要回滾的。回滾的語句是：
- delete from a where id = 1;(undo)
試想想看：如果沒有做 insert into a(id) values(1);(redo)，那么 delete from a where id = 1;(undo) 這句話就沒有意義了。
現在看下正確的恢復：
- 先 insert into a(id) values(1);(redo)
- 然后 delete from a where id = 1;(undo)
- 系統就回到了原先的狀態，沒有這條記錄了
存儲方式：是存在段之中。
事務
事務中有一個隔離性特征，理論上在某個事務對某個數據進行訪問時，其他事務應該排序，當該事務提交之后，其他事務才能繼續訪問這個數據。
但是這樣子對性能影響太大，我們既想保持事務的隔離性，又想讓服務器在出來多個事務時性能盡量高些，所以只能舍棄一部分隔離性而去性能。
事務并發執行的問題：
①臟寫：這個太嚴重了，任何隔離級別都不允許發生)。
- sessionA：修改了一條數據，回滾掉
- sessionB：修改了同一條數據，提交掉
對于 sessionB 來說，明明數據更新了也提交了事務，不能說自己啥都沒干。
②臟讀：一個事務讀到另一個未提交事務修改的數據。
- session A：查詢，得到某條數據
- session B：修改某條數據，但是最后回滾掉啦
- session A：在 sessionB 修改某條數據之后，在回滾之前，讀取了該條記錄
對于 session A 來說，讀到了 session 回滾之前的臟數據。
③不可重復讀：前后多次讀取，同一個數據內容不一樣。
- session A：查詢某條記錄
- session B : 修改該條記錄，并提交事務
- session A : 再次查詢該條記錄，發現前后查詢不一致
④幻讀：前后多次讀取，數據總量不一致。
- session A：查詢表內所有記錄
- session B：新增一條記錄，并查詢表內所有記錄
- session A：再次查詢該條記錄，發現前后查詢不一致
數據庫都有的四種隔離級別，MySQL 事務默認的隔離級別是可重復讀，而且 MySQL 可以解決了幻讀的問題：
- 未提交讀：臟讀，不可重復讀，幻讀都有可能發生
- 已提交讀：不可重復讀，幻讀可能發生
- 可重復讀：幻讀可能發生
- 可串行化：都不可能發生
但凡事沒有百分百，emmmm，其實 MySQL 并沒有百分之百解決幻讀的問題。
舉個例子：
- session A：查詢某條不存在的記錄。
- session B：新增該條不存在的記錄，并提交事務。
- session A：再次查詢該條不存在的記錄，是查詢不出來的，但是如果我嘗試修改該條記錄，并提交，其實他是可以修改成功的。
MVCC
版本鏈：對于該記錄的每次更新，都會將值放在一條 undo 日志中，算是該記錄的一個舊版本，隨著更新次數的增多，所有版本都會被 roll_pointer 屬性連接成一個鏈表，即為版本鏈。
readview：
- 未提交讀：因為可以讀到未提交事務修改的記錄，所以可以直接讀取記錄的最新版本就行
- 已提交讀：每次讀取之前都生成一個 readview
- 可重復讀：只有在第一次讀取的時候才生成 readview
- 可串行化：InnoDB 涉及了加鎖的方式來訪問記錄
參考文獻：
- 【原創】面試官：講講 MySQL 表設計要注意啥
- 【原創】雜談自增主鍵用完了怎么辦
- MySQL 是怎樣運行的：從根兒上理解 MySQL
- 詳細分析 MySQL 事務日志(redo log 和 undo log)
