MySQL優化高手筆記

語雀完整版：

https://www.yuque.com/g/mingrun/embiys/dv3btw/collaborator/join?token=zMBwPzSMfSGINLuv&source=doc_collaborator# 《MySQL優化高手筆記》

MySQL優化高手

一、MySQL架構

01 天天寫CRUD,你知道你的系統是如何跟MySQL打交道的嗎

通過驅動連接數據庫，而且會創建多個連接，可通過連接池進行管理，避免頻繁重復創建連接，浪費資源

02 為了執行SQL語句,你知道MySQL用了什么樣的架構設計嗎

除連接數據庫維護一個連接池外，數據庫本身也會維護一個連接池，其中有一個不變的原則，既然是網絡連接，那就一定會有一個線程去監聽處理，Mysql維護了一個sql接口用以接收各種語句，然后Sql接口將語句交給Sql解析器進行解析，然后找查詢優化器選擇一個最優的查詢路徑（好比到達終點有很多條路，選擇最優的那個方案，特別是復雜大Sql）。

優化器選擇好執行路徑后，就交給存儲引擎（去哪執行，去哪查，怎么執行）去真正的執行，

除此之外還有執行器，是他拿著優化器生成的執行方案去調用存儲引擎。

03 用一次數據更新流程，初步了解InnoDB存儲引擎的架構設計

04 借著更新語句在InnoDB存儲引擎中的執行流程，聊聊binlog是什么

binlog與redo log不同，它不屬于innodb這一個引擎，而是屬于整個MySql Server，其中前四步是執行sql，5、6步是提交事務，redo log和bin log寫入磁盤。
sync_binlog：是控制binglog刷磁盤的策略，默認是0，先進入Oscache，設置為1測試直接刷入磁盤。
當binlog 刷入磁盤后就算是事務提交完畢了，然后還需要在redo log中寫入一個commit標記，和binlog的文件名、路徑，寫入一個commit標記是為了確保binlog已經刷入了磁盤。

接著就是一個Io線程隨機的把臟數據緩存池中的數據刷回磁盤，如果此時宕機也不會造成問題，I/O線程依然可以根據redo把數據找回數據。

05 生產經驗:真實生產環境下的數據機器配置如何規劃

Java應用系統： 2c4g -> 4c8g ?>> 一兩百、每秒500左右

數據庫： 8c16g -> 16c32g ?>> 一兩千、三四千

對于Java系統I/O操作比較少，耗時的一般是數據庫，他會對磁盤文件進行大量的I/O操作，同時還有網絡傳輸上的耗時。

06 生產經驗:互聯網公司的生產環境數據庫是如何進行性能測試的

最好對數據庫先做壓測，因為java系統可能和數據所能承載的訪問量不一樣，嘗試每秒發送一千、兩千個請求。

QPS（Query Per Second）和TPS（Transaction Per Second）：前者是每秒處理多少個請求，或者說數據庫執行多少個SQL語句。 TPS則是每秒可以處理的提交或回滾事務。

然后再從I/O指標，CPU負載，網絡負載，內存負載，進行壓測。

07 生產經驗:如何對生產環境中的數據庫進行360度無死角玉測

使用 sysbench ?對數據庫進行360全方位壓測。

08 生產經驗:在數據庫的壓測過程中.如何360度無死角觀察機器性能

逐漸加大請求量，觀察機器的狀態

top命令觀察cpu負載，內存情況，dstat -d命令查看I/O吞吐量，dstat -n ?查看網絡流量情況。

09 生產經驗:如何為生產環境中的數據庫部署監控系統

Prometheus（數據采集）+Grafana（報表）：實現一套可視化監控系統

10 生產經驗:如何為數據庫的監控系統部署可視化報表系統

Prometheus（數據采集）+Grafana（報表）：實現一套可視化監控系統

二、Buffer Pool

11從數據的增刪改開始講起，回顧一下Buffer Pool在數據庫里的地位

buffer pool是一個非常重要的組件，一般大磁盤的隨機讀寫可能要幾百毫秒，如果直接查找磁盤效率很低

增刪改的操作首先操作的就是buffer pool，同時結合了 redo log，刷盤機制，

12 Buffer Pool這個內存數據結構到底長個什么樣子

128兆默認的有點小 --> 15c32g（推薦2g）

抽象的數據結構 -> 數據頁：每一頁中放了許多行

磁盤中的數據每一頁16kb,與此對應的緩存頁也是16kb

描述數據在緩存頁前面，包含表空間、數據頁的編號、此頁在buffer pool中的地址等信息，在buffer pool中描述數據大概相當于緩存頁的5%。

13從磁盤讀取數據頁到Buffer Pool的時候，free鏈表有什么用

初始化buffer pool：初始化內存空間，每頁16kb何其對應的800字節的描述數據，然后數據庫運行時，執行增刪改時再將數據加載進buffer pool里面

哪些數據頁是空閑的？
buffer pool擁有一個Free 鏈表(雙向)，他存放了空閑的緩存頁的描述數據塊的地址

將磁盤中的數據加載進buffer pool：從鏈表中獲取一個空閑的緩存頁，然后就將數據頁和對應的描述信息寫入，最后再將這個節點從緩存頁中刪除。

判斷數據頁是否已被緩存：

14當我們更新Buffer Pool中的數據時，flush鏈表有什么用

臟頁：指那些在緩存池中修改，尚未刷回磁盤的數據，而有的緩存頁是查詢產生的，不需要刷回磁盤，于是就有了flush鏈表，他存放的是被修改過的緩存頁的描述數據的地址（指針）。如果某個緩存頁被修改，那么他就會加入這個filush 鏈表。

15當Buffer Pool中的緩存頁不夠的時候，如何基于LRU算法淘汰部分緩存

把修改過數據的緩存頁刷新到磁盤中去，騰出來新的空閑緩存頁

緩存命中率： 只從緩存中操作的次數/總請求次數，可根據命中率決定淘汰哪個。

LRU鏈表：最近使用過的放在鏈表頭部，然后就淘汰鏈表尾部的。

16簡單的LRU鏈表在Buffer Pool實際運行中，可能遇到哪些問題

預讀機制

1. 預讀機制帶來的問題：從磁盤加載數據頁的時候，也會把它的左右鄰居帶上，并且會在LRU鏈表的前面，但實際上它的鄰居并沒有被訪問，而合理的情況下是要把這些沒人訪問的鄰居給清理掉。
2. 觸發預讀機制的情況：
  innodb_read_ahead_threshold ，最多連續訪問了相鄰的56（默認）數據頁，就會觸發。
  innodb_random_read_ahead ?，默認Off，存放了13個相鄰的，頻繁訪問，就觸發。

全表掃描（select * 并且沒有where條件）
會將這個表所有的數據頁加載進緩存頁里，占用lru鏈表的頭部。

17 MySQL是如何基于冷熱數據分離的方案，來優化LRU算法的

設計預讀就是感覺你連續訪問了很多相鄰頁，那么很有可能接下來還是，理想很豐滿，就是怕實際上沒人訪問。

而mysql中因為上述的問題會將 lru鏈表分為冷熱兩個部分，冷數據由nnodb_old_blocks_pc,控制，默認占37%。

數據頁第一次被放到緩存的時候，就會被放到冷數據區的頭部，此后innodb_old_blocks_time (默認1s) 后，如果還有訪問就放到熱區頭部，就是為了防止你只用了一次放到熱區

18 基于冷熱數據分離方案優化后的LRU鏈表，是如何解決之前的問題的

完美

19 MySQL是如何將LRU鏈表的使用性能優化到極致的

熱數據區節點移動優化：
為了防止移動過于頻繁，實際上只有后3/4 的數據被訪問了才會被移動到鏈表頭部

20 對于LRU鏈表中尾部的緩存頁，是如何淘汰他們刷入磁盤的

使用回顧：從磁盤中加載一個緩存頁，就會從free鏈表移除這個緩存頁，然后在lru冷數據頭部加入這個緩存頁。
如果修改了一個緩存頁，那么flush列表就會記錄這個臟頁，而且還可能會把這個臟頁從lru冷數據頭部移動到熱數據頭部。

在執行CRUD操作時，如果緩存頁已滿則會把一些緩存頁刷入磁盤，而刷入磁盤頁也會有幾個時機

將LRU尾部緩存頁刷入磁盤的幾個時機：

- 不等緩存頁用完，有個定時任務，將lru尾部的緩存頁刷入磁盤從flush鏈表中刪除，加入free鏈表。

- 定時將flush鏈表中的緩存頁刷入磁盤，從而將其從lru鏈表中移除。

21 生產經驗: 如何通過多個Buffer Pool來優化數據庫的并發性能

buffler pool本質上就是一大塊內存區域，里面存放了數據頁和描述數據塊，同時有free、flush、lru來輔助它的運行。

多個線程操作buffer pool是要排隊的，性能肯定會下降，所以可以調大內存設置多個buffer pool, 有多少個buffer pool ，每個buffer pool的大小。

22 生產經驗: 如何通過chunk來支持數據庫運行期間的Buffer Pool動態調整

不可以直接動態調整buffer pool的大小：比如從8G調整到 16G，會復制大量的緩存頁，描述數據等，不可接受。

chunk：buffer pool中包含了多個chunk

此時如果要增加內存那就增加chunk的數量就行了。

23 生產經驗:在生產環境中，如何基于機器配置來合理設置Buffer Pool

1. 要考慮系統內核運行所占用的內存，和其他人需要的內存，一般設置為總內存的 50% ~60%。

三、存儲結構

24 ?我們寫入數據庫的一行數據,在磁盤上是怎么存儲的

邏輯概念：表&行&字段 -> ?物理概念：表空間&數據區&數據頁

為什么不能直接操作磁盤？對磁盤的隨機讀寫很耗時，而內存中修改數據則會省時不少，高配置的機器上才可以看下每秒幾千的請求。

每次都加載一條數據到磁盤中再修改，效率不高，所以有了數據頁，每頁16kb，包含多條數據。要改一條數據就把這條數據的數據頁假如緩存。
而更新的時候，也是以頁為單位，刷新多條數據。

每一行數據在磁盤上如何存儲？
創建表時會指定一個行格式，比如COMPACT格式，實際每一行存儲時就像下面這樣

變長字段的長度列表，null值列表，數據頭，column01的值，column02的值，column0n的值......

25 對于VARCHAR這種變長字段，在磁盤上到底是如何存儲的

一行數據在磁盤上存儲的時候還包含其它描述數據。

表里面很多行數據，最終放到磁盤就是一大坨數據挨著放到一塊的。而變長數據如varchar，這時就變的難以讀取，因為無法確定一行的長度，所以就引入了變長字段的長度列表，他記錄這個變長數據的長度。

0x05 null值列表 數據頭 hello a a 0x02 null值列表 數據頭 hi a a

26 一行數據中的多個NULL字段值在磁盤上怎么存儲

實際上一個null 值，比如name字段的值為null，是不可能直接設置為null字符串的

null值是以二進制的bit位來存儲的，下面的案例中后四個都是為null的，且都是變長字段，

name  addreass  gender  job     school
jack    NULL      m     NULL   xx_school// 最終的行格式 其中0101是NULL值列表，它對應著上面四個可為null的字段，0不是null 1是null
0x09 0x04 0101 頭信息 column1=value1 column2=value2 ... columnN=valueN// 實際上null值列表的長度是 8的倍數，不足高位補零
0x09 0x04 00000101 頭信息 column1=value1 column2=value2 ... columnN=valueN

27 磁盤文件中，40個bit位的數據頭以及真實數據如何存儲

除上面所述外，每一行數據還有 40個bit的數據頭，用來描述這行數據

1~2位：預留位，無意義

3位：delete_mask該行是否已被刪除

4位：min_rec_mask每一層的非葉子節點都有最小值

5~8：n_owned ?記錄數

9~21：heap_no 當前這行數據在堆里面的位置

22~24：record_type 這行數據的類型,0代表的是普通類型，1代表的是B+樹非葉子節點，2代表的是最小值數據，3代表的是最大值數據

25~40：next_record，下一條數據的指針

28 我們每一行的實際數據在磁盤上是如何存儲的

每一行數據在磁盤中的存儲：

name  addreass  gender  job     school
jack    NULL      m     NULL   xx_school
// 變長字段的長度列表（字段倒敘） + NULL值列表（字段倒敘）+數據頭+真實數據
0x09 0x04 00000101 0000000000000000000010000000000000011001 jack m xx_school 
//而實際上這些字符串在磁盤上存儲的是指定編碼集編碼之后的
0x09 0x04 00000101 0000000000000000000010000000000000011001 616161 636320 6262626262

實際上在真實數據部分還有隱藏字段：
DB_ROW_ID ?每一行的一個標識
DB_TRX_ID ?事務Id
DB_ROLL_PTR ?回滾指針

// 至此真實數據應如下所示，而緩存池中加載的數據（每個數據頁/緩存頁裝載的數據頁） 實際上也如下所示
0x09 0x04 00000101 0000000000000000000010000000000000011001 00000000094C（DB_ROW_ID）
00000000032D（DB_TRX_ID） EA000010078E（DB_ROL_PTR） 616161 636320 6262626262

29 理解數據在磁盤上的物理存儲之后,聊聊行溢出是什么東西

每個數據頁/緩存頁只有16kb大小，如果存儲的真實數據過大，就會出現行溢出的這種情況，然后就會出現一行數據在多個數據頁/緩存頁存儲的情況。

30 于存放磁盤上的多行數據的數據頁到底長個什么樣子

如果此時數據庫一條數據都沒有，要插入一條數據就要先從磁盤中加載一個空的數據頁，然后向這個數據頁插入數據行。而實際上要明白磁盤中的數據行是和buffer pool中的緩存行是一一對應的，而后臺的IO線程也會定時的根據lru鏈表和flush鏈表將臟數據刷入磁盤

31 表空間以及劃分多個數據頁的數據區

表空間：有的表空間如系統表空間對應多個磁盤上的.idb文件，有的只對應一個，然后每個表空間會對應多個數據頁。

數據區（extent）:表空間包含多個數據頁，不便于管理，于是有了數據區，一個數據區對應連續64個數據頁，一個數據頁64kb大小，一個數據區就是1m大小，然后256個數據區劃分為一組。

第一個組的第一個數據區的前三個數據頁是固定的，用來存放描述性數據：
FSP_HDR：存放了表空間和這一組數據區的一些屬性。
IBUF_BITMA：這一組數據頁的所有insert buffer的一些信息。
INODE：也存放了一些特殊的信息

每個表空間，除第一組數據區外，其它組的第一個數據區的前兩個數據頁也是用來存放一些特殊信息的。

關系梳理: 表->對應的表空間->對應磁盤上的idb文件->多組數據區->數據頁

32 一文總結初步了解到的MySQL存儲模型以及數據讀寫機制

表是邏輯概念，對應物理層面就是表空間

插入一條數據：先找到表空間，Extent組，extent,然后找到數據頁，最后就可以加載到緩存池中了。

33 MySQl數據庫的日志順序讀寫以及數據文件隨機讀寫的原理

磁盤隨機讀：從磁盤中隨機讀一個數據頁放到緩存中，這個數據頁可能在磁盤中的任意一個位置，所以性能較差。

IOPS和響應延遲：這是隨機讀最需要關心的指標，代表磁盤每秒可以有多少次隨機讀操作

磁盤順序寫：緩存頁中數據更新后會寫一條redo log, 它是順序寫的，在末尾進行日志追加，而如果走OSchache的話效率會更高

34 生產經驗: Linux操作系統的存儲系統軟件層原理剖析以及IO調度優化原理

調度算法： ?CFQ公平算法就是挨個排隊，而deadline 算法，可能讓IO等待時間更少的操作先執行

35 生產經驗: 數據庫服務器使用的RAID存儲架構初步介紹

RAID架構：
很多數據庫部署時都采用他，它是一個磁盤冗余陣列，在實際生產中，如果服務器磁盤不夠用就會加磁盤，而RAID就是管理機器中的多塊磁盤的一種磁盤陣列技術，它可以告訴你該向那塊磁盤進行讀寫。

數據冗余機制：
將數據在另一塊磁盤中做冗余備份，如果一塊磁盤掛掉，則還可以使用另一塊磁盤，RAID技術則可以自動進行管理。而整體上RAID既有硬件層面也有軟件層面。

36 生產經驗:數據庫服務器上的RAID存儲架構的電池充放電原理

多塊磁盤組成RAID陣列時，會有一塊RAID卡，這塊卡是帶一個塊緩存的，緩存模式設置成write back后，數據就會先進入到緩存里，然后再慢慢寫入磁盤，這樣能大幅提高性能

但如果SDRAM突然斷電，那么數據就會丟失，所有有鋰電池來保證供電，直到數據刷回磁盤，但是鋰電池有性能衰減問題，要定時充放電，而在這個過程中，緩存模式就會設置成write through,直接寫硬盤，性能從0.1毫秒級退化成毫秒級。然后數據庫的性能也會因此出現幾十倍的抖動。

37 案例實戰: RAID鋰電池充放電導致的MySQL數據庫性能抖動的優化

一般廠商設置30天充放電一次，每次數據庫出現性能抖動，性能下降10倍以上。

可以使用RAID 設備提供的命令查看日志。

解決這個問題有三種方案：
換成電容，但不常用，更換麻煩，易老化。
手動充放電，使用腳本在夜深人靜時觸發
充放電時不關閉write back，它可以和上面的這個策略配合使用。

38 案例實戰:數據庫無法連接故障的定位，Too many connections

兩個Java系統設置最大連接數200個，那就有400個，而mysql也要建立400個網絡連接，對于高配置Mysql服務器是完全可以辦到的

先檢查 my.conf 中的max_connections ,再使用命令查看當前的連接數量show variables like 'max_connections' ?再檢查mysql的啟動日志，如下所示，mysql無法為我們設置我們的期望值

Could not increase number of max_open_files to more than mysqld (request: 65535)
Changed limits: max_connections: 214 (requested 2000)
Changed limits: table_open_cache: 400 (requested 4096)

上面的原因就是因為句柄的限制

39 案例實戰:如何解決經典的Too many connections故障? 背后原理是什么

解決：

ulimit -HSn 65535-- 然后就可以用如下命令檢查最大文件句柄數是否被修改了
cat /etc/security/limits.conf
cat /etc/rc.local

然后就重啟服務器就ok了

linux系統作此限制的目的就是為了防止某一線程占用過多的資源，一般如kafka之類的中間件都需要自行設置參數。

四、Redo Log

40 重新回顧redo日志對于事務提交后，數據絕對不會丟失的意義

redo log記錄著對數據庫的修改，如果提交事務后能保證刷回磁盤，出現故障后就能基于它進行重做

如果沒有rodo log 那么最大的缺陷就是，在IO線程還沒有把數據刷回磁盤就時突然宕機，而且也不可能提交一次事務就將數據刷新入磁盤，因為隨機讀寫的效率很低。

redo log的格式大致為：對表空間XX中的數據頁XX中的偏移量為XXXX的地方更新了數據XXX

直接將數據刷入磁盤和將日志寫入Redo log的區別：
二者都是寫入磁盤，但直接刷入磁盤要以數據頁為單位，一個數據頁16kb，僅僅修改了幾個字節就要刷入整個數據頁。
而一行redo log就可能只占用幾十個字節，就包含表空間號、數據頁號、磁盤文件偏移量、更新值，寫入磁盤速度很快

41 在Buffer Pool執行完增刪改之后，寫入日志文件的redo log長什么樣

根據修改的字節的個數不同 redo log劃分了幾種類型，MLOG_1BYTE （修改了一個字節），MLOG_2BYTE

//MOLOG_1BYTE
表空間ID，數據頁號，數據頁中的偏移量，具體修改的數據
//MLOG_WRITE_STRING 不知道具體修改了幾個字節的數據
表空間ID，數據頁號，數據頁中的偏移量，修改數據長度，具體修改的數據

42 redo log是直接一條條寫入文件的嗎? 非也，揭秘redo log block

redo log 不是把所有行的日志直接寫到一堆，而是用redo log block ?來存放多個單行日志，一個block是512字節

寫入流程：先在內存中湊夠一個數據塊，然后在寫入磁盤文件中

43 直接強行把redo log寫入磁盤?非也，揭秘redo log buffer

redo log buffer：申請出來的一塊連續的內存空間，劃分出了連續的多個redo log block，然后上面說的redo log都是先寫入這里的

寫時從第一個開始寫入，而且實際上再一次事務中可能涉及到多個redo log，他們湊成一組寫入到 redo log buffer中的 redo log block，最后再刷入磁盤中的redo log block。

44 redo log buffer中的緩沖日志，到底什么時候可以寫入磁盤?

redo log buffer 寫入磁盤的時機：

- 寫入redo log buffer中的日志已經占到了總容量的一半

- 提交事務時要提交redo log，就要把redo log對應的redo log block刷入到磁盤中

- 后臺線程每隔一秒將redo log buffer刷入磁盤

- mysql關閉的時候

磁盤中默認有兩個日志文件，每個48M，能容納百萬條redo log, 如果第二個寫滿了就寫到第一個如此往復。

五、事務與鎖機制

45 如果事務執行到一半要回滾怎么辦?再探undo log回滾日志原理!

undo log用來做回滾，如果一次任務失敗就要把buffer pool中的操作給回滾掉

比如是insert語句，那么undo中存的就是主鍵和 delete語句，能把你的操作回退掉，如果是update語句，就記錄原來的值，把舊值給更新回去。

46 一起來看看INSRET語句的undo log回滾日志長什么樣?

insert語句的undo log的類型是TRX_UNDO_INSERT_REC,它包含了：

這條日志的開始位置//主鍵中的每列的長度、值，如果沒有主鍵就設置row_id為主鍵
主鍵的各列長度和值 
表id
undo log日志編號//TRX_UNDO_INSERT_REC
undo log日志類型
這條日志的結束位置

有了該日志之后就知道了哪個表插入的數據，主鍵，注解定位到緩存頁，然后刪除掉之前插入的數據。

47 簡單回顧一下，MySQL 運行時多個事務同時執行是什么場景?

多線程操作buffer pool

48 多個事務并發更新以及查詢數據，為什么會有臟寫和臟讀的問題?

臟寫：A寫，B寫，A回滾，B讀，發現數據不是自己寫的。（B讀了A修改后但還未提交的數據，A回滾會覆蓋B的值）

臟讀：事務B用了事務A修改后的值，然后事務A回滾，導致事務B拿到的就是臟值。

他們倆的問題就是在別人還沒提交事務的時候就進行讀寫操作。

49 一個事務多次查詢一條數據讀到的都是不同的值，這就是不可重復讀?

不可重復讀：A事務讀取一個值->B事務修改一個值并提交->C事務修改一個值并提交，A事務再讀就發現和自己原來設置的值不同了。

可重復讀：和上面相反。

50 聽起來很恐怖的數據庫幻讀,到底是個什么奇葩問題 ?

幻讀：執行同一條sql語句比如 select *from table where id>10 , 多次執行中間有其它事務提交了數據導致數據增多，多次查詢的結果不一致

51 SQL標準中對事務的4個隔離級別，都是如何規定的呢?

根據并發可能出現的問題出現了幾個隔離級別

下圖中少了個臟寫，READ-UNCONMMITTEDA是可以防止臟寫的。

其中常用的是RC和RR

52 MySQL是如何支持4種事務隔離級別的? Spring事務注解是如何設置的?

MySQL的隔離級別和SQL標準中的略有不同，它默認的隔離級別是R-R，同時它的RR是可以防止幻讀的。

@Transactional(isolation=Isolation.DEFAULT) ?該注解就用mysql默認的讀寫機制

如果要自行改動mysql的隔離級別的話，就可能會用READ-COMMITTED，讓每次讀的數據都不一樣。

53 理解MVCC機制的前奏: undo log版本鏈是個什么東西?

Mysql之所以能做到多個事務平行執行，彼此之間的數據互不打擾是因為使用了MVCC(Multi-Version Concurrency Control) 多版本并發控制。

Undo log版本鏈：每行數據有兩個隱藏的字段 txr_id（最近一次更新這條數據的事務的Id）和roll_pointer (指向一個undo log)

事務A插入一條數據，記錄值A和新的事務Id，roll_pointer指向一個空的undo log

事務B 將值更新為值B，同時要生成一個undo log，記錄之前的值、事務id、roll_pointer
生成后就把這行數據的roll_pointer 指向這個生成的undo log，至此就會出現一條undo log鏈條

54 基于undo log多版本鏈條實現的ReadView機制，到底是什么?

ReadView：每次執行事務的時候生成一個，最關鍵的東西有四個

一個是m_ids，這個就是說此時有哪些事務在MySQL里執行還沒提交的；
一個是min_trx_id，就是m_ids里最小的值；
一個是max_trx_id，這是說mysql下一個要生成的事務id，就是最大事務id；
一個是creator_trx_id，就是你這個事務的id

ReadView讀視圖機制敘述：
首先他是基于undo log 鏈實現的一套讀視圖，事務執行時會生成一個readView，在這次事務id之前的就是已經提交過的事務，可以直接讀取。在這次事務id之后的表明又有其它事務修改了數據，不能直接讀到，需要沿著undo log鏈條找到自己的事務或者小于自己事務id的數據。

55 Read Comitted隔離級別是如何基于ReadView機制實現的?

56 MySQL最牛的RR隔離級別，是如何基于ReadView機制實現的?

RR隔離級別與上面樣式ReadView用畫了同一個流程圖

RR隔離級別與RC不同的就是 它的ReadView 是這個事務第一次查詢的時候只生成一次

57 停一停腳步:梳理一下數據庫的多事務并發運行的隔離機制

事務隔離級別

ReadView+undo log 鏈

58 多個事務更新同一行數據時，是如何加鎖避免臟寫的?

并發更新一條數據就會有臟寫問題，解決臟寫問題則需要鎖機制，然后在多個事務更新一條數據的時候就是串行化的

事務A看到當前行未加鎖就會創建一個鎖，這個鎖包含事務Id和等待狀態。

事務B過來時也要創建鎖，只不過因為事務A它的等待狀態要設置為true。

事務A執行完后要更改事務B的等待狀態，去喚醒他。

總結：其實就是加了獨占鎖才避免了臟寫問題

59 對MySQL鎖機制再深入一步，共享鎖和獨占鎖到底是什么?

上面多個數據更新同一行數據時加的是獨占鎖（Exclude）,也就是X鎖，一次只能有一個在寫數據，其他人排隊，但是其它事務要讀取數據的話是不用加鎖的，因為有Mvcc機制的存在。

如果在讀取數據的時候也非要加鎖，可以使用共享鎖，在查詢語句后面加上lock in share mode

共享鎖與獨占鎖互斥，加了共享鎖就不能加互斥鎖，反過來也一樣

查詢操作也可以加獨占鎖， select * from table for update,代表我查詢的時候別人都不能更新

60 在數據庫里，哪些操作會導致在表級別加鎖呢?

一般情況下不建議手動在SQL語句上加鎖，那樣加鎖邏輯就會隱藏在sql語句中

表級鎖：一般指存儲引擎的鎖，而不是DDL語句的鎖，雖然執行DDL語句的時候其它對數據的操作會阻塞。

61 表鎖和行鎖互相之間的關系以及互斥規則是什么呢?

加表鎖很少用，加表鎖的語法：

LOCK TABLES xxx READ：這是加表級共享鎖
LOCK TABLES xxx WRITE：這是加表級獨占鎖

一個事務對表進行增刪改操作的時候會加獨占鎖，讀操作的時候回加共享鎖，他們都是表級的意向鎖，實際上沒啥用

62 案例實戰:線上數據庫不確定性的性能抖動優化實踐(上)

執行一個sql語句，需要加載大量的緩存頁，然后可能導致原來大量的臟頁刷回磁盤中來騰出空間，這個過程可能會持續幾十毫秒到幾秒，然后sql語句才能執行，從而出現性能抖動。

磁盤中的redo log文件有兩個，第一個寫滿了寫第二個，第二寫滿了寫第一個，如此往復。
但是循環向第一個寫的時候要先看看對應的緩存頁是否都刷入了磁盤，沒有的話就要刷入，此時也會造成性能波動。

63 案例實戰:線上數據庫莫名其妙的隨機性能抖動優化(下)

解決上面這兩種問題有兩種思路，一種是減少緩存頁flush到磁盤的頻率，第二個是提升緩存頁flush到磁盤的速度，就是控制頻率和速度。

控制頻率只有加大機器內存，其它就很難控制了

控制速度，將緩存頁flush到磁盤的耗時控制到最小，解決方案如下：
使用SSD：它的隨機讀寫速度較高
innodb_io_capacity：該參數控制了使用多的I/O速率將緩存頁flush 到磁盤中
innodb_flush_neighbors：設置為0 代表不加載相鄰的緩存頁。

六、索引

64 深入研究索引之前，先來看看磁盤數據頁的存儲結構

每個數據頁直接看做一段在磁盤中連續的數據，他存儲了有上一個數據頁的地址，和下一個數據頁的地址，構成了一個雙向鏈表，然后每個數據頁中存儲的數據行，他們直接構成單鏈表。

65 假設沒有任何索引，數據庫是如何根據查詢語句搜索數據的?

數據行被分到不同的槽位中，每個數據頁包含頁目錄，存放了主鍵和槽位的對應關系

如果根據主鍵查數據，直接在數據頁中做二分查找，找到對應的槽位，再找到槽位中對應的行。

而如果是非主鍵·則無法使用二分查找，只能進到數據頁中對單向鏈表進行遍歷查找。

從第一個數據頁開始，而且要先加載成緩存頁，如果第一個沒找到就繼續加載后面的數據頁，而且都要先加載成緩存頁

以上過程就是全表掃描，效率很低

66 不斷在表中插入數據時，物理存儲是如何進行頁分裂的?

插入的數據在數據頁中會構成單列表，前面的數字都是類型，2代表最小的一行，3代表最大的一行

頁分裂：如果在一個數據頁的數據寫滿了，就要挪到另外一個數據頁，而索引運作的一個機制就是要求后面的數據頁的主鍵值要比前一個都大，然后如下圖，第一個數據也中比我大的都要挪到后面這個數據頁。
這個用來保證后面數據頁比前面數據頁主鍵大的，數據挪動的過程就是頁分裂。

67 基于主鍵的索引是如何設計的，以及如何根據主鍵索引查詢?

可以看成主鍵索引的主鍵目錄： 主鍵查找肯定不能走全表掃描，于是有了主鍵目錄，他有每頁的頁號和最小的主鍵值，這樣就可以通過二分查找來進行快速的查找了。

68 索引的頁存儲物理結構，是如何用B +樹來實現的?

主鍵目錄->索引頁：一個主鍵目錄不能面對大量數據，存儲大量的最小主鍵值。
于是就采用了將索引數據存放到數據也中的方式，然后就會形成很多的索引頁。

數據分了多個數據頁，而索引多的時候也要分成索引頁，而很多索引頁最后頁堆成了一個樹的結構

69 更新數據的時候，自動維護的聚簇索引到底是什么?

從索引樹的頂層開始查找，最后查到索引頁59號，然后找到對應的數據頁

每個數據頁對應了一個頁目錄，然后就是會在這個頁目錄里面找到對應的槽。

最下面的索引頁也引用了數據頁，同一級的索引頁之間有雙向鏈表。

整體來看，索引頁和數據頁構成了一整棵B+樹，數據頁作為葉子節點，這樣這顆B+樹索引就叫做聚簇索引。

頁分裂過程會保證后面的數據頁的最小主鍵值比前面數據頁里面的所有主鍵值都大

如果一個數據頁越來越多，索引頁放不下，就會拉出新的索引頁，同時在搞一個上層的索引頁。
而一般情況下億級別的大表索引的層級也就三四層。

從聚簇節點的根節點開始進行二分查找，找到對應的數據頁，再基于頁目錄定位到主鍵對應的數據

70 針對主鍵之外的字段建立的二級索引，又是如何運作的?

現有sql語句select * from table where name =‘張三’，對name建立索引，下面將對其進行分析。

name字段的索引B+樹：其它字段建立索引，比如name字段，插入數據的時候會新建一個B+樹，這個B樹的葉子節點也是數據頁，與聚簇索引B+樹不同的是，前者數據頁只存放了主鍵和Name字段，后者則存放了一整行數據。

搜索過程是通過排好序的 name值進行二分查找，但查到數據頁的時候只能獲得主鍵值，所以還要用主鍵值，到聚簇索引中獲得其它數據，這個過程叫回表。而name這種普通索引稱之為二級索引。一級索引就是聚簇索引。
的B +樹的?

新建一個表：就是一個數據頁，它是空的，并且屬于聚簇索引的一部分。

插入數據：并且維護了一個頁目錄，根據主鍵搜索沒問題，這個初始的數據頁就叫根頁。

插入更多數據：一個數據頁放不下，新建一個數據頁，把根頁數據都拷貝過去，再新建一個，根據主鍵值大小進行挪動

此時的根頁：成為了索引頁，里面存放了兩個數據頁的最小主鍵值和數據頁的頁號

繼續增多：數據頁繼續分裂，索引頁不斷增多，索引頁也會繼續分裂，成為一顆B+樹

Name索引的注意點：name索引的分裂過程和上面相似，需要注意的就是它的索引頁里面也存放了主鍵，因為同一級的name字段值可能一樣并且指向不同的頁號，這個時候就要根據主鍵判斷了。

72 一個表里是不是索引搞的越多越好?那你就大錯特錯了!

索引的順序性：索引頁內部的數據是按照從小到大的順序組成單向鏈表，數據頁之間還有索引頁之間構成雙向鏈表，也是有序的。
最終可以使用二分查找，效率很高。

使用索引的缺點：
一是占用磁盤空間，二是增刪改數據還要維護索引

73 通過一步一圖來深入理解聯合索引查詢原理以及全值匹配規則

對于聯合索引來說，就是依次按照各個字段來進行二分查找（比如name+age+gender，先根據name二分查，不是再找age）

全值匹配：就是搜索條件與索引一一對應，百分之百用上索引。

74 再來看看幾個最常見和最基本的索引使用規則

最左匹配原則：(class_name,student_name, subject_name)，從左邊按照順序匹配，如果查詢條件是
where class_name = ? and subject_name，那么后面的subject_name是沒法作為索引查的。

最左前綴匹配原則：百分號不能在前，在前的話沒法在索引中定位。

范圍查找規則：如where class_name>'1班' and class_name<'5班'，可以使用索引，但是student_name就不可以，因為范圍查找只能對聯合索引最左側的列才能生效

等值匹配+范圍匹配：where class_name='1班' and student_name>'' and subject_name<'', student_name走索引，但是subject_name不行。

75 當我們在SQL里進行排序的時候，如何才能使用索引 ?

直接在內存中排序肯定不行，數據量大的話，在磁盤中直接上排序算法也會要了老命。

對于一個聯合索引 INDEX(xx1,xx2,xx3),他本來就已經排好序了

規則： order by條件里不能有的升序有的降序，那樣不走索引，而且條件里面有不在索引里面的也完蛋，帶函數的也完蛋。

76 當我們在SQL里進行分組的時候，如何才能使用索引?

group by與order by一樣，也是從索引的最左側開始進行匹配，

如果能利用上索引 ?就不再需要針對雜亂無章的數據利用內存在進行重拍和分組了。

77 回表查詢對性能的損害以及覆蓋索引是什么 ?

對于select * 這種語句，他需要掃描聚簇索引和聯合索引，有時候mysql可能會認為還不如直接走全表掃描，
但是如果加上limit數量限制還是會走聯合索引的。

覆蓋索引的概念：指在索引樹中就可以直接查詢到要的數據

78 設計索引的時候，我們一般要考慮哪些因素呢? (上)

在系統開發好SQL語句都寫好后，就根據索引的設計原則去加索引。

設計的索引最好要包括 ?where、order by、group by里面的條件，而且要注意順序，符合最左原則。

79 設計索引的時候，我們一般要考慮哪些因素呢? (中)

基數比較大（就是取值要種類要多）的字段：那樣才能發揮B樹的潛力（關鍵）

考慮字段類型的大小：字段類型盡量小，比如tinyint，而如果對varchar這種類型簡歷索引，可以考慮只取前面一部分內容。建立出來的索引如：KEY my_index(name(20),age,course)，這個就是前綴索引，但是他沒辦法用到group by和order by中。

80 設計索引的時候，我們一般要考慮哪些因素呢? (下)

查詢條件中不要放函數：如where function(a) = xx，不走索引

索引不要太多：建立兩三個覆蓋索引，覆蓋所有查詢

不要使用UUID做主鍵：主鍵值都是有序的，這樣搞會頻繁導致頁分裂

81 案例實戰:陌生人社交APP的MySQL索引設計實戰(一)

實際場景中 where使用索引和order by使用索引不可兼得

82 案例實戰:陌生人社交APP的MySQL索引設計實戰(二)

一般優先滿足where條件使用索引的需求，篩選出來的數據再進行排序

基數較低也不一定就不用索引！
比如我要建立索引：（province, city, sex），雖然基數較低，但是如果不建立索引每次就要先用其它索引查一遍，加載到內存中，然后再根據這幾個條件過濾一遍。所以還不如直接放到聯合索引的最左側。

83 案例實戰:陌生人社交APP的MySQL索引設計實戰(三)

中間缺了個條件怎么辦？
對于索引 （province, city, sex）, 我要查where province=xx and city=xx and age between xx and xx，此時可以把索引建成（province, city, sex，age），查詢是還把sex帶上，只不過取所有的，where province=xx and city=xx and sex in ('female', 'male') and age >=xx and age<=xx。

對于一些其它的枚舉值，比如性格愛好，取值類型不多，索引可以設計成（province, city, sex, hobby, character, age）
查詢語句寫成：where province=xx and city=xx and sex in(xx, xx) and hobby in (xx, xx, xx, xx) and character=xx and age>=xx and age<=xx
就是說如果不需要根據某一個條件進行過濾那就直接in 里面放所有

范圍查詢的字段的索引，要放到最后，如果放到中間的話，后面的字段的索引也就用不上了。

84 案例實戰:陌生人社交APP的MySQL索引設計實戰(四)

如果要再加一個條件，最近7天內登陸過的，latest_login_time <= 7，這個地方還會用到函數，而且放在age后面也不會走索引，此時可以加一個枚舉值字段，用來直接表示最近天是否登陸過，does_login_in_latest_7_days,接下來建索引就可以把他放到age的前面了
（province, city, sex, hobby, character,does_login_in_latest_7_days, age ）

輔助索引在低基數條件、有排序分頁的情況下的運用：
對于SQL語句 select xx from user_info where sex='female' order by score limit xx,xx,
對此設計索引（sex, score），雖然

85 提綱挈領的告訴你，SQL 語句的執行計劃和性能優化有什么關系?

執行計劃：提交一個SQL給給MySQL，然后查詢優化器會生成一個執行計劃，執行計劃代表具體的怎么查詢

86 以MySQL單表查詢來舉例，看看執行計劃包含哪些內容(1) ?

const： const就是超高性能的查，就是直接從聚簇索引或者聚簇索引+二級索引（唯一類型的）查得的，速度極快

ref：普通的二級索引查詢，包括了從左查詢覆蓋的索引，而對于name IS NULL的這種查詢，因為判空函數，所以她不會是const，而是ref_or_null

87 以MySQL單表查詢來舉例，看看執行計劃包含哪些內容(2) ?

Range：利用了返回查找的普通索引就是range

Index：對于索引KEY(x1,x2,x3), 查詢語句select x1,x2,x3 from table where x2=xxx，索引樹中的葉子節點存儲的是這三個值+主鍵，這種慢于上面三種，需要遍歷二級索引，但強于遍歷聚簇索引（全表掃描）

All：最次的一種全表掃描

88 再次重溫寫出各種SQL語句的時候，會用什么執行計劃? (1)

對于：

select * from table where x1=xx or x2>=xx
--索引
(x1,x3)，(x2,x4)

查詢優化器會選擇行數比較少的，如 x1 == XX

對于x1=xx and c1=xx and c2>=xx and c3 IS NOT NULL 這種查詢，可能就只能走 x1一個索引，不肯能為每個條件都加行索引，所以就只能盡量保證 ?x1篩選出來的數據盡量少

89 再次重溫寫出各種SQL語句的時候，會用什么執行計劃? (2)

同時使用兩個索引的情況select * from table where x1=xx and x2=xx，先對x1進行索引查找，再對x2進行索引查找，最后取交集。

上面這種查詢比先查X1 然后回表，然后再根據X2過濾要好的多

出現這種情況的條件是：如果有聯合索引那么這個聯合索引需要時全值全匹配，或者是主鍵+其它二級索引等值全匹配，這樣才會出現多索引查詢做交集。

90 再次重溫寫出各種SQL語句的時候，會用什么執行計劃? (3)

總結

91 深入探索多表關聯的SQL語句到底是如何執行的? (1)

對于查詢select * from t1,t2 where t1.x1=xxx and t1.x2=t2.x2 and t2.x3=xxx
它是先從t1表過濾，然后t2表過濾，如果加了索引二者各干各的，然后讓t1表去關聯t2表中的數據

其中先查一波數據這個叫做驅動表，然后去關聯另一張表里面的數據他就是被驅動表

92 深入探索多表關聯的SQL語句到底是如何執行的? (2)

內外連接的基本語義

93 深入探索多表關聯的SQL語句到底是如何執行的? (3)

嵌套循環關聯（nested-loop join）：就是比如一個表里面查出來10條數據，然后就得讓這10條數據循環匹配表二中的數據，如果索引沒有加好效率就會很低下

t1Rows = queryFromt1() // 根據篩選條件對t1標進行查詢
for t1Row in t1Rows { // 對t1里每一條符合條件的數據進行循環t2Rows = queryFromt2(t1Row) //拿t1里的數據去t2表里查詢以及做關聯for t2Row in t2Rows { // 對t1和t2關聯后的數據進行循環t3Rows = queryFromt3(t2Row) // 拿t1和t2關聯后的數據去t3表里查間和關聯for t3Row in t3Rows { 1// 遍歷最終t1和t2和t3關聯好的數據}}
}

94 MySQL是如何根據成本優化選擇執行計劃的? ( 上)

I/O 成本 1.0 ：主要指從磁盤中讀取數據所需的成本，1.0是自定義的一個值

CPU成本 0.2 ：主要是指對數據的處理，0.2是自定義值。

計算成本方法：

-- 執行命令 返回的 rows是inndb的估計值，還有data_length
show table status like "表名"
-- 計算有多少數據頁
data_length / 16 = ()kb
-- I/O成本值計算
數據頁數量 * 1.0 + 微調值
-- CPU成本值計算
行記錄數 * 0.2 + 微調值
-- 然后總值相加即可
總成本 = I/O+Cpu

95 MySQL是如何根據成本優化選擇執行計劃的? (中)

根據二級索引先刷新到I/O中，一般的等值查詢和范圍查詢較少的可以直接認為是 1*1.0 或者 n**1.0

拿出的數據還要經過過濾處理，估算拿到100條數據，那么cpu成本就是 100**0.2+微調值

然后再回表去聚簇索引中查找完整數據（100 *1+微調值），然后再對數據進行過濾處理(100 * 0.2 + 微調值)

最后總的成本就是 1 + 20 + 100 + 20 = 141

執行計劃根據最小的成本值去執行

這種成本不是精確計算而是大致計算的。

96 MySQL是如何根據成本優化選擇執行計劃的? (下)

多個表的關聯查詢成本計算

97 MySQL如何基于各種規則去優化執行計劃的? (上)

常量替換
i = 5 and j > i這樣的SQL，就會改寫為i = 5 and j > 5

select * from t1 join t2 on t1.x1=t2.x1 and t1.id=1
改寫為
select t1表中id=1的那行數據的各個字段的常量值, t2.* from t1 join t2 on t1表里x1字段的常量值 =t2.x1

98 MySQL如何基于各種規則去優化執行計劃的? (中)

對于 select * from t1 where x1 = (select x1 from t2 where id=xxx)，它是先查詢括號內語句，然后就相當于普通的連表查了

另外一種效率則較低 select * from t1 where x1 = (select x1 from t2 where t1.x2=t2.x2)，子查詢用到了外表的字段，

99 MySQl是如何基于各種規則去優化執行計劃的? (下)

simi join 查詢：將select * from t1 where x1 in (select x2 from t2 where x3=xxx)
優化成select t1.* from t1 semi join t2 on t1.x1=t2.x2 and t2.x3=xxx，其中semi join是mysql內部的一種用法

100~108 透徹研究通過explain命令得到的SQL執行計劃

explain 開篇（100）

explain 命令：SQL前面加一個 explain explain select * from table

如果是簡單的 sql語句那么就會查出來一行，如果是復雜的sql語句就會是多行，因為執行計劃包含了多個步驟。

其中
id：每次查詢計劃的Id
select_type ?: 查詢類型
table：表名稱
partitions ：分區
type ?：當前對這個表的訪問方法，就好比之前的index all等
possible_keys ?：可能使用的索引
key_len：索引的長度
ref：使用某個字段的索引進行等值匹配搜索的時候，跟索引列進行等值匹配的那個目標值的一些信
息
rows：是預估通過索引或者別的方式訪問這個表的時候，大概可能會讀取多少條數據。
filtered：就是經過搜索條件過濾之后的剩余數據的百分比。extra是一些額外的信息，不是太重要。

簡單說明：

explain select * from t1

id：
select_type ?: simple 表示一個簡單的查詢
table：t1表
partitions ：分區
type ?：沒加任何條件就是all
rows：查出來大概 3457條數據
filtered：100%全查出來了

explain select * from t1 join t2

先是表1 all掃描
extra：Nested Loop ?代表嵌套執行
上面的這個用的是同一個查詢計劃，id都一樣，如果有子查詢id則會等于二

EXPLAIN SELECT * FROM t1 WHERE x1 IN (SELECT x1 FROM t2) OR x3 = 'xxxx';

因為有兩個select 所以id不同
第一條：select_type是PRIMARY ?代表主查詢
第二條：select_type是SUBQUERY ?代表子查詢

EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2

主要是第三個查詢計劃，他干的就是去重的活，<union 1,2> ?是一個臨時的表名，extra中有一個 using
temporary ?代表使用臨時表。

join buffer： ? EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.x2 = t2.x2
如果兩個表都沒有用索引，會使用此技術在內存中做一些優化減少t2表的全表掃描次數

如果排序條件沒有走索引那么extra就會是 filtered，性能比較差，直接在內存中進行的排序，如果是分組 distinct操作則會是temporary 效率同樣非常低

109~117 案例實戰

千萬級用戶場景下的運營系統SQL調優

需求描述：
用戶量是百萬級別的，現在要根據一些條件取一些用戶做一些消息推送這樣的業務需求

SQL語句：
SELECT id, name FROM users WHERE id IN (SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx)
sql中除掉基礎的用戶表之外還有用戶信息的擴展表，通常這種查詢要先看下數據量，前面可以改寫為SELECT count(id) 如果超過1000 那么是可以用limit 進行限制，每次取出來1000條數據，多分幾次推送消息。但是上面的這種sql語句在千萬級的表中還是會跑出來幾十秒的耗時，不可行的。

執行計劃分析：

+----+-------------+-------+------------+-------+---------------+----------+---------+------+
| id | select_type | table | type | key | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+
| 1 | SIMPLE | | ALL | NULL | NULL | 100.00 | NULL |
| 1 | SIMPLE | users | ALL | NULL | 49651 | 10.00 | Using where; Using join buffer(Block Nested Loop) |
| 2 | MATERIALIZED | users_extent_info | range | idx_login_time | 4561 | 100.00 | NULL |

先看第三行：針對latest_login_time 做 range類型的索引，MATERIALIZED表明是將結果集物化成一個臨時表，落到磁盤中速度較慢。
第一條執行計劃是物化表的，第二條是users表和第一個表做join的，所以會有using join buffer, 并且filter 是10%

為什么會這么慢：
第一點：將臨時表幾千條數據物化出來，而是users表需要和這個物化出來的表做全表掃描，所以很慢
第二點：使用命令show warnings，會看到 semi join這個關鍵字，他也是原因

怎么優化：
什么是semi join：只要users表里的一條數據，在物化臨時表里可以找到匹配的數據，那么users表里的數據就會返回，這就叫做semi join，他是用來篩選的。
執行 SET optimizer_switch='semijoin=off 速度就會提升但一般我們不能在生產環境這么做
改變sql語句來反正半連接：

-- 時間上后面的那個or條件 是沒用的
SELECT COUNT(id)
FROM users
WHERE (id IN (SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx) OR id IN (SELECT user_id FROM users_extent_info WHERE latest_login_time < -1)
)

都用上索引其實才是王道

億級數據量商品系統的SQL調優實戰

場景和需求：
晚高峰，商品系統非常繁忙，TRS每秒幾千，每分鐘的慢查詢超過了 10w+，數據庫連接阻塞，用戶無法查看商品相關的內容

SQL語句：
就是根據一些條件查詢商品信息，但他查詢需要幾十秒，數據庫連接被打滿

SELECT* 
FROMproducts 
WHEREcategory = 'xx' AND sub_category = 'xx' 
ORDER BYid DESC LIMIT xx,xx

原因分析：
如果是正常走索引，億級表也不過1s鐘，查看執行計劃發現possible_keys ?的確是category ?索引，但是實際上用的key卻不是，而且extra里面寫了 using where
救急使用force index：
select * from products force index(index_category) where category='xx' and sub_category='xx' order by id desc limit xx,xx
產生的問題由此產生了下面的三個問題

為什么在這個案例中MySQL默認會選擇對主鍵的聚簇索引進行掃描？
為什么沒使用index_category這個二級索引進行掃描？
mysql擔心從二級索引拿出來的數據會過多，而且還可能需要在內中排序，而且你是select * ，需要進行回表，那么我還不如直接走聚簇索引，using where
其實走聚簇索引也不慢，這里也是因為看到你 limit 10, 正常掃描聚簇索引也應該不會超過1s起碼

即使用了聚簇索引，為什么這個SQL以前沒有問題，現在突然就有問題了？
這個就因為有些商品分類并沒有對應的商品，就是說使用category進行搜索的時候有些數據找不到，這就導致sql會做全表掃描，而且掃來掃去也找不到數據

數十億數量級評論系統的SQL調優實戰

對于一件熱銷的商品，它的銷量有幾百萬，評論多達幾十萬，用戶可任意翻找評論，總結一下就是
針對一個商品幾十萬評論的深分頁問題。

查詢語句：SELECT * FROM comments WHERE product_id ='xx' and is_good_comment='1' ORDER BY id desc LIMIT 100000,20
此語句的問題：
如果只走了一個index_product_id 索引，里面沒有is_good_comment，那就要對查出來的幾十萬條數據每一條數據都要回表去比對 is_good_comment，總之就是他不適合用二級索引去查詢，因為回表次數過多。
進行改造：SELECT * from comments a,(SELECT id FROM comments WHERE product_id ='xx' and is_good_comment='1' ORDER BY id desc LIMIT 100000,20) b WHERE a.id=b.id
這個sql語句會先執行子查詢，他會走聚簇索引，按照主鍵倒序排列，然后找到符合條件的20條數據，然后這20條數據作為結果集，回表去聚簇中查一次就行了。

千萬級數據刪除導致的慢查詢優化實踐 115~117&120

情況描述：發現了大量慢查詢，而查看語句發現它不應該會導致慢查詢，可以推測是Mysql服務本身的問題，如磁盤的I/O負載比較高，或者網絡的負載超高，還有就是CPU負載，CPU過于繁忙，然后排查，方向也并不是這里的問題，這是就需要用profilling工具去排查

profiling(剖析) 工具：

-- 開始profiling
set profiling = 1
-- 從返回結果中可以看到 查詢的語句，對應的id和耗時
show profiles
-- 其中查詢出來的結果里面有一個send_data
show profile cpu, block io for query 16

分析結果
使用剖析工具查詢出來的 send_data 耗時很高，然后使用show engine innodb status發現 history list length 很高，到了上萬，它就是undo log版本鏈條的長度，事務提交后就應該被 purge清理掉，而這個list很長就代表有事務長時間沒有提交

原因和解決方法
原因就是那個時候在清理上千萬條數據，事務一直沒提交掉，而且對于刪除只是加了個標記，這樣查詢的時候就導致沒查到就走全表掃描
然后對于長事務而言，這么多數據，每次查詢的時候還會生成大量的readView，而且這刪除的幾千萬條數據還都會生成 undo log鏈條，這也就解釋了 history list為什么那么大，而且一直都不被purge清理掉。

118 ~119 我們為什么要搭建一套MySQL的主從復制架構

主從架構：主節點和從節點數據保持一致，一旦主節點掛掉那么從節點還可以提供服務
這就是它的第一個作用：保證高可用

主從架構的第二個作用：可以作為讀寫分離架構
比如讀寫各有3000 的qps（一般讀比寫高的多），一臺機器只能承載5000，此時就可以使用讀寫分離，而且可以添加多個從節點，其中還可以融入中間件來實現故障轉移等功能。

主從復制的基本原理

binlog中記錄了所有的增刪改操作。

主機點就負責生成binlog，然后用dump線程把日志傳輸給從庫，從庫就使用I/O線程把日志寫入到本地的relay中去，然后從庫會再基于它重新把sql語句執行一遍，來達到一個數據同步的效果，當然數據并不是完全一致的。

121 ~123 如何為MySQL搭建一套主從復制架構？

創建簡單的主從庫 (異步數據同步有延遲)

主庫創建一個用于主從復制的賬號

不能在主庫還在提供服務的時候 ?讓從庫從零開始導入數據，要選擇一個時間進行維護，導入和數據備份。

使用mysql自帶的 msqldump工具進行備份
/usr/local/mysql/bin/mysqldump --single-transaction -uroot -proot --master-data=2 -A > backup.sql
然后就把這個 backup.sql 復制到從庫里面，在從庫執行他，

CHANGE MASTER TO MASTER_HOST='192.168.31.229', MASTER_USER='backup_user',MASTER_PASSWORD='backup_123',MASTER_LOG_FILE='mysqlbin.000015',MASTER_LOG_POS=1689;

半同步的主從庫復制（一般采用這種方式）

半同步有兩種方式
一種是after_commit，非默認主庫將binlog 復制到從庫之后，提交事務，然后等待從庫的響應，然后再講主庫提交事務成功的消息給從庫
另一種是 mysql 5.7默認的方式：主庫將binlog 給從庫，然后等待從庫相應成功，然后再提交事務，并把提交成功的消息告訴從庫