- 推薦閱讀：
01 開局一張圖
這張圖是重點！！！咱要先對MySQL有一個宏觀的瞭解，知道他的執行流程。
一條SQL語句過來的流程是什麼樣的？那就follow me。哈哈哈哈，皮一下很開心。
- 1.當客戶端連接到MySQL服務器時，服務器對其進行認證。可以通過用戶名與密碼認證，也可以通過SSL證書進行認證。登錄認證後，服務器還會驗證客戶端是否有執行某個查詢的操作權限。
- 2.在正式查詢之前，服務器會檢查查詢緩存，如果能找到對應的查詢，則不必進行查詢解析，優化，執行等過程，直接返回緩存中的結果集。
- 3.MySQL的解析器會根據查詢語句，構造出一個解析樹，主要用於根據語法規則來驗證語句是否正確，比如SQL的關鍵字是否正確，關鍵字的順序是否正確。
而預處理器主要是進一步校驗，比如表名，字段名是否正確等
- 4.查詢優化器將解析樹轉化為查詢計劃，一般情況下，一條查詢可以有很多種執行方式，最終返回相同的結果，優化器就是根據成本找到這其中最優的執行計劃
- 5.執行計劃調用查詢執行引擎，而查詢引擎通過一系列API接口查詢到數據
- 6.得到數據之後，在返回給客戶端的同時，會將數據存在查詢緩存中
02 查詢緩存
我們先通過show variables like '%query_cache%'來看一下默認的數據庫配置，此為本地數據庫的配置。
2.1 概念
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:如果該表被鎖住，是否返回緩存中的數據，默認是關閉的。
2.2 原理
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哈希出來的值是不一樣的，大小寫和空格影響了他們，所以並不能命中緩存，但其實他們搜索結果是完全一樣的。
2.3 生產如何設置MySQL Query Cache
先來看線上參數：
我們發現將query_cache_type設置為OFF，其實網上資料和各大雲廠商提供的雲服務器都是將這個功能關閉的，從上面的原理來看，在一般情況下，他的弊端大於優點。
03 索引
3.1 例子
創建一個名為user的表，其包括id，name，age，sex等字段信息。此外，id為主鍵聚簇索引，idx_name為非聚簇索引。
<code>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;
/<code>
我們將其設置10條數據，便於下面的索引的理解。
<code>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', '男');
/<code>3.2 聚簇索引（主鍵索引）
先來一張圖鎮樓，接下來就是看圖說話。
他包含兩個特點：
1.使用記錄主鍵值的大小來進行記錄和頁的排序。
頁內的記錄是按照主鍵的大小順序排成一個單項鍊表。
各個存放用戶記錄的頁也是根據頁中用戶記錄的主鍵大小順序排成一個雙向鏈表。
2.葉子節點存儲的是完整的用戶記錄。
<code>注：聚簇索引不需要我們顯示的創建，他是由InnoDB存儲引擎自動為我們創建的。如果沒有主鍵，其也會默認創建一個。複製代碼
/<code>
3.3 非聚簇索引（二級索引）
上面的聚簇索引只能在搜索條件是主鍵時才能發揮作用，因為聚簇索引可以根據主鍵進行排序的。如果搜索條件是name，在剛才的聚簇索引上，我們可能遍歷，挨個找到符合條件的記錄，但是，這樣真的是太蠢了，MySQL不會這樣做的。
如果我們想讓搜索條件是name的時候，也能使用索引，那可以多創建一個基於name的二叉樹。如下圖。
他與聚簇索引的不同：
1.葉子節點內部使用name字段排序，葉子節點之間也是使用name字段排序。
2.葉子節點不再是完整的數據記錄，而是name和主鍵值。
為什麼不再是完整信息？
MySQL只讓聚簇索引的葉子節點存放完整的記錄信息，因為如果有好幾個非聚簇索引，他們的葉子節點也存放完整的記錄績效，那就不浪費空間啦。
如果我搜索條件是基於name，需要查詢所有字段的信息，那查詢過程是啥？
1.根據查詢條件，採用name的非聚簇索引，先定位到該非聚簇索引某些記錄行。
2.根據記錄行找到相應的id，再根據id到聚簇索引中找到相關記錄。這個過程叫做回``表。
3.4 聯合索引
圖就不畫了，簡單來說，如果name和age組成一個聯合索引，那麼先按name排序，如果name一樣，就按age排序。
3.5 一些原則
1.最左前綴原則。一個聯合索引（a,b,c）,如果有一個查詢條件有a，有b，那麼他則走索引，如果有一個查詢條件沒有a，那麼他則不走索引。
2.使用唯一索引。具有多個重複值的列，其索引效果最差。例如，存放姓名的列具有不同值，很容易區分每行。而用來記錄性別的列，只含有“男”，“女”，不管搜索哪個值，都會得出大約一半的行，這樣的索引對性能的提升不夠高。
3.不要過度索引。每個額外的索引都要佔用額外的磁盤空間，並降低寫操作的性能。在修改表的內容時，索引必須進行更新，有時可能需要重構，因此，索引越多，所花的時間越長。
4、索引列不能參與計算，保持列“乾淨”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是數據表中的字段值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。所以語句應該寫成create_time = unix_timestamp(’2014-05-29’);
5.一定要設置一個主鍵。前面聚簇索引說到如果不指定主鍵，InnoDB會自動為其指定主鍵，這個我們是看不見的。反正都要生成一個主鍵的，還不如我們設置，以後在某些搜索條件時還能用到主鍵的聚簇索引。
6.主鍵推薦用自增id，而不是uuid。上面的聚簇索引說到每頁數據都是排序的，並且頁之間也是排序的，如果是uuid，那麼其肯定是隨機的，其可能從中間插入，導致頁的分裂，產生很多表碎片。如果是自增的，那麼其有從小到大自增的，有順序，那麼在插入的時候就添加到當前索引的後續位置。當一頁寫滿，就會自動開闢一個新的頁。
<code>注：如果自增id用完了，那將字段類型改為bigint，就算每秒1萬條數據，跑100年，也沒達到bigint的最大值。複製代碼
/<code>
3.6 萬年面試題（為什麼索引用B+樹）
1、 B+樹的磁盤讀寫代價更低：B+樹的內部節點並沒有指向關鍵字具體信息的指針，因此其內部節點相對B樹更小，如果把所有同一內部節點的關鍵字存放在同一盤塊中，那麼盤塊所能容納的關鍵字數量也越多，一次性讀入內存的需要查找的關鍵字也就越多，相對IO讀寫次數就降低了。
2、由於B+樹的數據都存儲在葉子結點中，分支結點均為索引，方便掃庫，只需要掃一遍葉子結點即可，但是B樹因為其分支結點同樣存儲著數據，我們要找到具體的數據，需要進行一次中序遍歷按序來掃，所以B+樹更加適合在區間查詢的情況，所以通常B+樹用於數據庫索引。
04 優化器
在開篇的圖裡面，我們知道了SQL語句從客戶端經由網絡協議到查詢緩存，如果沒有命中緩存，再經過解析工作，得到準確的SQL，現在就來到了我們這模塊說的優化器。
首先，我們知道每一條SQL都有不同的執行方法，要不通過索引，要不通過全表掃描的方式。
那麼問題就來了，MySQL是如何選擇時間最短，佔用內存最小的執行方法呢？
4.1 什麼是成本？
1.I/O成本。數據存儲在硬盤上，我們想要進行某個操作需要將其加載到內存中，這個過程的時間被稱為I/O成本。默認是1。
2.CPU成本。在內存對結果集進行排序的時間被稱為CPU成本。默認是0.2。
4.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是如何選擇相關索引呢？
1.使用所有可能用到的索引
我們可以看到搜索條件username，所以可能走uname_unique索引。也可以做聚簇索引，也就是全表掃描。
2.計算全表掃描代價
我們通過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
3.計算使用不同索引執行查詢的代價
因為要查詢出滿足條件的所有字段信息，所以要考慮回表成本。
I/O成本=1+1X1=2(範圍區間的數量+預計二級記錄索引條數)
CPU成本=1X0.2+1X0.2=0.4(讀取二級索引的成本+回表聚簇索引的成本)
總成本=I/O成本+CPU成本=2.4
4.對比各種執行方案的代價，找出成本最低的那個
上面兩個數字一對比，成本是採用uname_unique索引成本最低。
4.3 多表查詢的成本
對於兩表連接查詢來說，他的查詢成本由下面兩個部分構成：
- 單次查詢驅動表的成本
- 多次查詢被驅動表的成本（具體查詢多次取決於對驅動表查詢的結果集有多少個記錄）
4.4 index dive
如果前面的搜索條件不是等值，而是區間，如select * from dev_user where username>'admin' and username
步驟1：先根據username>'admin'這個條件找到第一條記錄，稱為區間最左記錄。
步驟2：再根據username
步驟3：如果區間最左記錄和區間最右記錄相差不是很遠，可以準確統計出需要回表的數量。如果相差很遠，就先計算10頁有多少條記錄，再乘以頁面數量，最終模糊統計出來。
05 Explain
5.1 產品來索命
產品：為什麼這個頁面出來這麼慢？
開發：因為你查的數據多唄，他就是這麼慢
產品：我不管，我要這個頁面快點，你這樣，客戶怎麼用啊
開發：。。。。。。。你行你來
哈哈哈哈，不瞎BB啦，如果有些SQL賊慢，我們需要知道他有沒有走索引，走了哪個索引，這個時候我就需要通過explain關鍵字來深入瞭解MySQL內部是如何執行的。
5.2 id
一般來說一個select一個唯一id，如果是子查詢，就有兩個select，id是不一樣的，但是凡事有例外，有些子查詢的，他們id是一樣的。
這是為什麼呢？
那是因為MySQL在進行優化的時候已經將子查詢改成了連接查詢，而連接查詢的id是一樣的。
5.3 select_type
- simple：不包括union和子查詢的查詢都算simple類型。
- primary：包括union，union all，其中最左邊的查詢即為primary。
- union：包括union，union all，除了最左邊的查詢，其他的查詢類型都為union。
5.4 table
顯示這一行是關於哪張表的。
5.5 type：訪問方法
- ref：普通二級索引與常量進行等值匹配
- ref_or_null：普通二級索引與常量進行等值匹配，該索引可能是null
- const：主鍵或唯一二級索引列與常量進行等值匹配
- range：範圍區間的查詢
- all：全表掃描
5.6 possible_keys
對某表進行單表查詢時可能用到的索引
5.7 key
經過查詢優化器計算不同索引的成本，最終選擇成本最低的索引
5.8 rows
- 如果使用全表掃描，那麼rows就代表需要掃描的行數
- 如果使用索引，那麼rows就代表預計掃描的行數
5.9 filtered
- 如果全表掃描，那麼filtered就代表滿足搜索條件的記錄的滿分比
- 如果是索引，那麼filtered就代表除去索引對應的搜索，其他搜索條件的百分比
06 redo日誌（物理日誌）
InnoDB存儲引擎是以頁為單位來管理存儲空間的，我們進行的增刪改查操作都是將頁的數據加載到內存中，然後進行操作，再將數據刷回到硬盤上。
那麼問題就來了，如果我要給張三轉賬100塊錢，事務已經提交了，這個時候InnoDB把數據加載到內存中，這個時候還沒來得及刷入硬盤，突然停電了，數據庫崩了。重啟之後，發現我的錢沒有轉成功，這不是尷尬了嗎？
解決方法很明顯，我們在硬盤加載到內存之後，進行一系列操作，一頓操作猛如虎，還未刷新到硬盤之前，先記錄下，在XXX位置我的記錄中金額減100，在XXX位置張三的記錄中金額加100，然後再進行增刪改查操作，最後刷入硬盤。如果未刷入硬盤，在重啟之後，先加載之前的記錄，那麼數據就回來了。
這個記錄就叫做重做日誌，即redo日誌。他的目的是想讓已經提交的事務對數據的修改是永久的，就算他重啟，數據也能恢復出來。
6.1 log buffer（日誌緩衝區）
為了解決磁盤速度過慢的問題，redo日誌不能直接寫入磁盤，咱先整一大片連續的內存空間給他放數據。這一大片內存就叫做日誌緩衝區，即log buffer。到了合適的時候，再刷入硬盤。至於什麼時候是合適的，這個下一章節說。
我們可以通過show VARIABLES like 'innodb_log_buffer_size'命令來查看當前的日誌緩存大小，下圖為線上的大小。
6.2 redo日誌刷盤時機
由於redo日誌一直都是增長的，且內存空間有限，數據也不能一直待在緩存中， 我們需要將其刷新至硬盤上。
那什麼時候刷新到硬盤呢？
- log buffer空間不足。上面有指定緩衝區的內存大小，MySQL認為日誌量已經佔了 總容量的一半左右，就需要將這些日誌刷新到磁盤上。
- 事務提交時。我們使用redo日誌的目的就是將他未刷新到磁盤的記錄保存起來，防止 丟失，如果數據提交了，我們是可以不把數據提交到磁盤的，但為了保證持久性，必須 把修改這些頁面的redo日誌刷新到磁盤。
- 後臺線程不同的刷新 後臺有一個線程，大概每秒都會將log buffer裡面的redo日誌刷新到硬盤上。
- checkpoint 下下小節講
6.3 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的工作啦。
6.4 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過程中的一些數據統計。
07 undo日誌（這部分不是很明白，所以大概說了）
7.1 基本概念
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)
系統就回到了原先的狀態，沒有這條記錄了
7.2 存儲方式
是存在段之中。
08 事務
8.1 引言
事務中有一個隔離性特徵，理論上在某個事務對某個數據進行訪問時，其他事務應該排序，當該事務提交之後，其他事務才能繼續訪問這個數據。
但是這樣子對性能影響太大，我們既想保持事務的隔離性，又想讓服務器在出來多個事務時性能儘量高些，所以只能捨棄一部分隔離性而去性能。
8.2 事務併發執行的問題
- 髒寫（這個太嚴重了，任何隔離級別都不允許發生）
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 : 再次查詢該條記錄，發現前後查詢不一致
8.3 四種隔離級別
數據庫都有的四種隔離級別，MySQL事務默認的隔離級別是可重複讀，而且MySQL可以解決了幻讀的問題。
- 未提交讀：髒讀，不可重複讀，幻讀都有可能發生
- 已提交讀：不可重複讀，幻讀可能發生
- 可重複讀：幻讀可能發生
- 可串行化：都不可能發生
但凡事沒有百分百，emmmm，其實MySQL並沒有百分之百解決幻讀的問題。
舉個例子：
session A：查詢某條不存在的記錄。
session B：新增該條不存在的記錄，並提交事務。
session A：再次查詢該條不存在的記錄，是查詢不出來的，但是如果我嘗試修改該條記錄，並提交，其實他是可以修改成功的。
8.4 MVCC
版本鏈：對於該記錄的每次更新，都會將值放在一條undo日誌中，算是該記錄的一箇舊版本，隨著更新次數的增多，所有版本都會被roll_pointer屬性連接成一個鏈表，即為版本鏈。
readview：
- 未提交讀：因為可以讀到未提交事務修改的記錄，所以可以直接讀取記錄的最新版本就行
- 已提交讀：每次讀取之前都生成一個readview
- 可重複讀：只有在第一次讀取的時候才生成readview
- 可串行化：InnoDB涉及了加鎖的方式來訪問記錄
作者：學習Java的小姐姐
原文鏈接：https://juejin.im/post/5dfc846051882512327a63b6
閱讀更多 追逐仰望星空 的文章
