1.MySQL的邏輯架構(gòu)圖

MySQL可以分為Server層和存儲引擎層兩部分：

1.連接器

1.）連接器負(fù)責(zé)跟客戶端建立連接、獲取權(quán)限、維持和管理連接。連接命令一般是這么寫的：

mysql -h$ip -P$port -u$user -p

2.）連接完成后，如果你沒有后續(xù)的動作，這個連接就處于空閑狀態(tài)，你可以在show processlist命令中看到它

3.）客戶端如果太長時間沒動靜，連接器就會自動將它斷開。這個時間是由參數(shù)wait_timeout控制的，默認(rèn)值是8小時

2.查詢緩存

1.）連接建立完成后，你就可以執(zhí)行select語句了。執(zhí)行邏輯就會來到第二步：查詢緩存

2.）之前執(zhí)行過的語句及其結(jié)果可能會以key-value對的形式，被直接緩存在內(nèi)存中。key是查詢的語句，value是查詢的結(jié)果。如果你的查詢能夠直接在這個緩存中找到key，那么這個value就會被直接返回給客戶端。如果語句不在查詢緩存中，就會繼續(xù)后面的執(zhí)行階段。執(zhí)行完成后，執(zhí)行結(jié)果會被存入查詢緩存中。你可以看到，如果查詢命中緩存，MySQL不需要執(zhí)行后面的復(fù)雜操作，就可以直接返回結(jié)果，這個效率會很高

3.）但是大多數(shù)情況下我會建議你不要使用查詢緩存，為什么呢？因為查詢緩存往往弊大于利。

查詢緩存的失效非常頻繁，只要有對一個表的更新，這個表上所有的查詢緩存都會被清空。因此很可能你費勁地把結(jié)果存起來，還沒使用呢，就被一個更新全清空了。對于更新壓力大的數(shù)據(jù)庫來說，查詢緩存的命中率會非常低

除非你的業(yè)務(wù)就是有一張靜態(tài)表，很長時間才會更新一次。比如，一個系統(tǒng)配置表，那這張表上的查詢才適合使用查詢緩存

使用查詢緩存參數(shù):query_cache_type設(shè)置DEMAND，對于默認(rèn)的SQL語句都不使用查詢緩存

對于你確定要使用查詢緩存的語句，可以用SQL_CACHE顯式指定

需要注意的是，MySQL 8.0版本直接將查詢緩存的整塊功能刪掉了，也就是說8.0開始徹底沒有這個功能了

3.分析器

1.) 如果沒有命中查詢緩存，就要開始真正執(zhí)行語句了。首先，MySQL需要知道你要做什么，因此需要對SQL語句做解析

4.優(yōu)化器

經(jīng)過了分析器，MySQL就知道你要做什么了。在開始執(zhí)行之前，還要先經(jīng)過優(yōu)化器的處理。優(yōu)化器是在表里面有多個索引的時候，決定使用哪個索引；或者在一個語句有多表關(guān)聯(lián)（join）的時候，決定各個表的連接順序。比如你執(zhí)行下面這樣的語句，這個語句是執(zhí)行兩個表的join

5.執(zhí)行器

MySQL通過分析器知道了你要做什么，通過優(yōu)化器知道了該怎么做，于是就進入了執(zhí)行器階段，開始執(zhí)行語句。

開始執(zhí)行的時候，要先判斷一下你對這個表T有沒有執(zhí)行查詢的權(quán)限，如果沒有，就會返回沒有權(quán)限的錯誤，如下所示(在工程實現(xiàn)上，如果命中查詢緩存，會在查詢緩存返回結(jié)果的時候，做權(quán)限驗證。查詢也會在優(yōu)化器之前調(diào)用precheck驗證權(quán)限)

2.日志系統(tǒng)

1.redo log（重做日志）和 binlog（歸檔日志）

重要的日志模塊：redo log

write pos是當(dāng)前記錄的位置，一邊寫一邊后移，寫到第3號文件末尾后就回到0號文件開頭。checkpoint是當(dāng)前要擦除的位置，也是往后推移并且循環(huán)的，擦除記錄前要把記錄更新到數(shù)據(jù)文件.有了redo log，InnoDB就可以保證即使數(shù)據(jù)庫發(fā)生異常重啟，之前提交的記錄都不會丟失，這個能力稱為crash-safe。

重要的日志模塊：binlog

1.) MySQL整體來看，其實就有兩塊：一塊是Server層，它主要做的是MySQL功能層面的事情；還有一塊是引擎層，負(fù)責(zé)存儲相關(guān)的具體事宜。上面我們聊到的redo log是InnoDB引擎特有的日志，而Server層也有自己的日志，稱為binlog（歸檔日志）。

2.)?binlog日志只能用于歸檔

這兩種日志有以下三點不同:

1.redo log是InnoDB引擎特有的；binlog是MySQL的Server層實現(xiàn)的，所有引擎都可以使用。

2.redo log是物理日志，記錄的是“在某個數(shù)據(jù)頁上做了什么修改”；binlog是邏輯日志，記錄的是這個語句的原始邏輯，比如“給ID=2這一行的c字段加1 ”。

3.redo log是循環(huán)寫的，空間固定會用完；binlog是可以追加寫入的。“追加寫”是指binlog文件寫到一定大小后會切換到下一個，并不會覆蓋以前的日志

下面是updata跟新流程：

1.執(zhí)行器先找引擎取ID=2這一行。ID是主鍵，引擎直接用樹搜索找到這一行。如果ID=2這一行所在的數(shù)據(jù)頁本來就在內(nèi)存中，就直接返回給執(zhí)行器；否則，需要先從磁盤讀入內(nèi)存，然后再返回。

2.執(zhí)行器拿到引擎給的行數(shù)據(jù)，把這個值加上1，比如原來是N，現(xiàn)在就是N+1，得到新的一行數(shù)據(jù)，再調(diào)用引擎接口寫入這行新數(shù)據(jù)。

3.引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時將這個更新操作記錄到redo log里面，此時redo log處于prepare狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了，隨時可以提交事務(wù)。

4.執(zhí)行器生成這個操作的binlog，并把binlog寫入磁盤。

5.執(zhí)行器調(diào)用引擎的提交事務(wù)接口，引擎把剛剛寫入的redo log改成提交（commit）狀態(tài)，更新完成。

最后三步看上去有點“繞”，將redo log的寫入拆成了兩個步驟：prepare和commit，這就是"兩階段提交"。

redo log用于保證crash-safe能力。innodb_flush_log_at_trx_commit這個參數(shù)設(shè)置成1的時候，表示每次事務(wù)的redo log都直接持久化到磁盤。這個參數(shù)我建議你設(shè)置成1，這樣可以保證MySQL異常重啟之后數(shù)據(jù)不丟失

sync_binlog這個參數(shù)設(shè)置成1的時候，表示每次事務(wù)的binlog都持久化到磁盤

3.事務(wù)隔離

1.事務(wù)定義

1.若隔離級別是“讀未提交”， 則V1的值就是2。這時候事務(wù)B雖然還沒有提交，但是結(jié)果已經(jīng)被A看到了。因此，V2、V3也都是2。

2.若隔離級別是“讀提交”，則V1是1，V2的值是2。事務(wù)B的更新在提交后才能被A看到。所以， V3的值也是2。

3.若隔離級別是“可重復(fù)讀”，則V1、V2是1，V3是2。之所以V2還是1，遵循的就是這個要求：事務(wù)在執(zhí)行期間看到的數(shù)據(jù)前后必須是一致的。

4.若隔離級別是“串行化”，則在事務(wù)B執(zhí)行“將1改成2”的時候，會被鎖住。直到事務(wù)A提交后，事務(wù)B才可以繼續(xù)執(zhí)行。所以從A的角度看， V1、V2值是1，V3的值是2。

參數(shù)transaction-isolation的值設(shè)置成READ-COMMITTED。你可以用show variables來查看當(dāng)前的值。

mysql> show variables like 'transaction_isolation';

事務(wù)隔離的實現(xiàn)

理解了事務(wù)的隔離級別，我們再來看看事務(wù)隔離具體是怎么實現(xiàn)的。這里我們展開說明“可重復(fù)讀”。

在MySQL中，實際上每條記錄在更新的時候都會同時記錄一條回滾操作。記錄上的最新值，通過回滾操作，都可以得到前一個狀態(tài)的值。

假設(shè)一個值從1被按順序改成了2、3、4，在回滾日志里面就會有類似下面的記錄。當(dāng)前值是4，但是在查詢這條記錄的時候，不同時刻啟動的事務(wù)會有不同的read-view,同時你會發(fā)現(xiàn)，即使現(xiàn)在有另外一個事務(wù)正在將4改成5，這個事務(wù)跟read-view A、B、C對應(yīng)的事務(wù)是不會沖突的.系統(tǒng)會判斷，當(dāng)沒有事務(wù)再需要用到這些回滾日志時，回滾日志會被刪除。什么時候才不需要了呢？就是當(dāng)系統(tǒng)里沒有比這個回滾日志更早的read-view的時候

長事務(wù)意味著系統(tǒng)里面會存在很老的事務(wù)視圖。由于這些事務(wù)隨時可能訪問數(shù)據(jù)庫里面的任何數(shù)據(jù)，所以這個事務(wù)提交之前，數(shù)據(jù)庫里面它可能用到的回滾記錄都必須保留，這就會導(dǎo)致大量占用存儲空間,對回滾段的影響，長事務(wù)還占用鎖資源，也可能拖垮整個庫

2.事務(wù)的啟動方式

1.顯式啟動事務(wù)語句， begin 或 start transaction。配套的提交語句是commit，回滾語句是rollback

2.set autocommit=0，這個命令會將這個線程的自動提交關(guān)掉。意味著如果你只執(zhí)行一個select語句，這個事務(wù)就啟動了，而且并不會自動提交。這個事務(wù)持續(xù)存在直到你主動執(zhí)行commit 或 rollback 語句，或者斷開連接，因此，我會建議你總是使用set autocommit=1, 通過顯式語句的方式來啟動事務(wù)。

3.索引

nnoDB表T，如果你要重建索引 k，你的兩個SQL語句可以這么寫：

alter table T drop index k;

alter table T add index(k);

如果你要重建主鍵索引，也可以這么寫：

alter table T drop primary key;

alter table T add primary key(id);

總結(jié)：1.監(jiān)控 information_schema.Innodb_trx表，設(shè)置長事務(wù)閾值，超過就報警/或者kill；

2.Percona的pt-kill這個工具不錯，推薦使用；

3.在業(yè)務(wù)功能測試階段要求輸出所有的general_log，分析日志行為提前發(fā)現(xiàn)問題；

4.如果使用的是MySQL 5.6或者更新版本，把innodb_undo_tablespaces設(shè)置成2（或更大的值）。如果真的出現(xiàn)大事務(wù)導(dǎo)致回滾段過大，這樣設(shè)置后清理起來更方便。

深入淺出索引

mysql> create table T (

ID int primary key,

k int NOT NULL DEFAULT 0,

s varchar(16) NOT NULL DEFAULT '',

index k(k))

engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

查詢語句：

執(zhí)行 select * from T where k between 3 and 5，需要執(zhí)行幾次樹的搜索操作，會掃描多少行

流程：

1.在k索引樹上找到k=3的記錄，取得 ID = 300；

2.再到ID索引樹查到ID=300對應(yīng)的R3；

3.在k索引樹取下一個值k=5，取得ID=500；

4.再回到ID索引樹查到ID=500對應(yīng)的R4；

5在k索引樹取下一個值k=6，不滿足條件，循環(huán)結(jié)束。

在這個過程中，回到主鍵索引樹搜索的過程，我們稱為回表?？梢钥吹剑@個查詢過程讀了k索引樹的3條記錄（步驟1、3和5），回表了兩次（步驟2和4）。

覆蓋索引

如果執(zhí)行的語句是select ID from T where k between 3 and 5，這時只需要查ID的值，而ID的值已經(jīng)在k索引樹上了，因此可以直接提供查詢結(jié)果，不需要回表。也就是說，在這個查詢里面，索引k已經(jīng)“覆蓋了”我們的查詢需求，我們稱為覆蓋索引。

由于覆蓋索引可以減少樹的搜索次數(shù)，顯著提升查詢性能，所以使用覆蓋索引是一個常用的性能優(yōu)化手段

最左前綴原則

索引項是按照索引定義里面出現(xiàn)的字段順序排序的。

當(dāng)你的邏輯需求是查到所有名字是“張三”的人時，可以快速定位到ID4，然后向后遍歷得到所有需要的結(jié)果。

如果你要查的是所有名字第一個字是“張”的人，你的SQL語句的條件是"where name like ‘張%’"。這時，你也能夠用上這個索引，查找到第一個符合條件的記錄是ID3，然后向后遍歷，直到不滿足條件為止