學 MySQL，這篇萬字總結，真的夠用了

這篇文章將從查詢緩存，索引，優化器，explain，redo 日誌，undo 日誌，事務隔離級別，鎖等方面來講解 MySQL。

開局一張圖

這張圖是重點！！！咱要先對 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 的二叉樹，如下圖：

他與聚簇索引的不同：

爲什麼不再是完整信息？MySQL 只讓聚簇索引的葉子節點存放完整的記錄信息，因爲如果有好幾個非聚簇索引，他們的葉子節點也存放完整的記錄績效，那就不浪費空間啦。

如果我搜索條件是基於 name，需要查詢所有字段的信息，那查詢過程是啥？

聯合索引

圖就不畫了，簡單來說，如果 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 是如何選擇時間最短，佔用內存最小的執行方法呢？

什麼是成本？

單表查詢的成本

先來建一個用戶表 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：

③計算使用不同索引執行查詢的代價

因爲要查詢出滿足條件的所有字段信息，所以要考慮回表成本：

④對比各種執行方案的代價，找出成本最低的那個

上面兩個數字一對比，成本是採用 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：

**table：**顯示這一行是關於哪張表的。

type 訪問方法：

**possible_keys：**對某表進行單表查詢時可能用到的索引。

**key：**經過查詢優化器計算不同索引的成本，最終選擇成本最低的索引。

rows：

filtered：

redo 日誌（物理日誌）

InnoDB 存儲引擎是以頁爲單位來管理存儲空間的，我們進行的增刪改查操作都是將頁的數據加載到內存中，然後進行操作，再將數據刷回到硬盤上。

那麼問題就來了，如果我要給張三轉賬 100 塊錢，事務已經提交了，這個時候 InnoDB 把數據加載到內存中，這個時候還沒來得及刷入硬盤，突然停電了，數據庫崩了。

重啓之後，發現我的錢沒有轉成功，這不是尷尬了嗎？

解決方法很明顯，我們在硬盤加載到內存之後，進行一系列操作，一頓操作猛如虎，還未刷新到硬盤之前，先記錄下，在 XXX 位置我的記錄中金額減 100，在 XXX 位置張三的記錄中金額加 100。

然後再進行增刪改查操作，最後刷入硬盤。如果未刷入硬盤，在重啓之後，先加載之前的記錄，那麼數據就回來了。

這個記錄就叫做重做日誌，即 redo 日誌。他的目的是想讓已經提交的事務對數據的修改是永久的，就算他重啓，數據也能恢復出來。

log buffer（日誌緩衝區）

爲了解決磁盤速度過慢的問題，redo 日誌不能直接寫入磁盤，咱先整一大片連續的內存空間給他放數據。

這一大片內存就叫做日誌緩衝區，即 log buffer。到了合適的時候，再刷入硬盤。至於什麼時候是合適的，這個下一章節說。

我們可以通過 show VARIABLES like 'innodb_log_buffer_size' 命令來查看當前的日誌緩存大小。

下圖爲線上的大小：

redo 日誌刷盤時機

由於 redo 日誌一直都是增長的，且內存空間有限，數據也不能一直待在緩存中， 我們需要將其刷新至硬盤上。 

那什麼時候刷新到硬盤呢？

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 分爲兩步：

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) 這句話就沒有意義了。

現在看下正確的恢復：

**存儲方式：**是存在段之中。

事務

事務中有一個隔離性特徵，理論上在某個事務對某個數據進行訪問時，其他事務應該排序，當該事務提交之後，其他事務才能繼續訪問這個數據。

但是這樣子對性能影響太大，我們既想保持事務的隔離性，又想讓服務器在出來多個事務時性能儘量高些，所以只能捨棄一部分隔離性而去性能。

事務併發執行的問題：

**①髒寫：**這個太嚴重了，任何隔離級別都不允許發生）。

對於 sessionB 來說，明明數據更新了也提交了事務，不能說自己啥都沒幹。

**②髒讀：**一個事務讀到另一個未提交事務修改的數據。

對於 session A 來說，讀到了 session 回滾之前的髒數據。

**③不可重複讀：**前後多次讀取，同一個數據內容不一樣。

**④幻讀：**前後多次讀取，數據總量不一致。

數據庫都有的四種隔離級別，MySQL 事務默認的隔離級別是可重複讀，而且 MySQL 可以解決了幻讀的問題：

但凡事沒有百分百，emmmm，其實 MySQL 並沒有百分之百解決幻讀的問題。

舉個例子：

MVCC

**版本鏈：**對於該記錄的每次更新，都會將值放在一條 undo 日誌中，算是該記錄的一箇舊版本，隨着更新次數的增多，所有版本都會被 roll_pointer 屬性連接成一個鏈表，即爲版本鏈。

readview：

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/lU8AfCHKNwK-Yjv0uDXNnA

猜你喜歡