一篇文章讓你去懂MYSQL底層原理

mysql影片教學欄位介紹底層原理。

MYSQL

一條SQL執行過程

先看看一則查詢SQL

• （這裡提供一下官方對各儲存引擎的文件說明Mysql儲存引擎）

#一條update SQL執行

update的執行從客戶端=> ··· => 執行引擎 是一樣的流程，都要先查到這條數據，然後再去更新。要理解 UPDATE 流程我們先來看看，Innodb的架構模型。

Innodb 架構

上一張MYSQL 官方InnoDB架構圖：

內部模組

連接器(JDBC 、ODBC等) =>

[MYSQL 內部

[Connection Pool] (授权、线程复用、连接限制、内存检测等)
=>

[SQL Interface] (DML、DDL、Views等) [Parser] (Query Translation、Object privilege) [Optimizer] (Access Paths、 统计分析) [Caches & Buffers]
=>

[Pluggable Storage Engines]复制代码

]

=> [File]

記憶體結構

#這裡有個關鍵點，當我們去查詢資料時候會先拿著我們目前查詢的page 去buffer pool 中查詢目前page#是否在緩衝池中。如果在，則直接獲取。

而如果是update運算時，則會直接修改 Buffer中的值。這時候，buffer pool中的資料就和我們磁碟中實際儲存的資料不一致了，稱為髒頁。每隔一段時間，Innodb儲存引擎就會把髒頁資料刷入磁碟。一般來說當更新一條數據，我們需要將數據給讀取到buffer中修改，然後寫回磁碟，完成一次 落盤IO 操作。

為了提高update的操作效能，Mysql在記憶體中做了最佳化，可以看到，在架構圖的緩衝池中有一塊區域叫做：change buffer。顧名思義，給change後的數據，做buffer的，當更新一個沒有unique index 的數據時，直接將修改的數據放到change buffer#，然後透過merge 操作完成更新，從而減少了那一次落盤的IO 操作。

• 我們上面說的有個條件：沒有唯一索引的資料更新時，為什麼必須要沒有唯一索引的資料更新時才能直接放入change buffer呢？如果是有唯一約束的欄位，我們在更新資料後，可能更新的資料和已經存在的資料有重複，所以只能從磁碟中把所有資料讀出來比對才能確定唯一性。
• 所以當我們的資料是寫多讀少 的時候，就可以透過增加innodb_change_buffer_max_size 來調整change buffer在 buffer pool 中所佔的比例，預設25（即：25%）

#問題又來了，merge是如何運作的

#有四種情況：

1. 有其他訪問，訪問到了當前頁的數據，就會合併到磁碟
2. 後台線程定時merge
3. 系統正常shut down之前，merge一次
4. redo log寫滿的時候，merge到磁碟

#一、redo log是什麼

談到redo，就要談到innodb的crash safe，使用WAL 的方式實作（write Ahead Logging，在寫之前先記錄日誌）

這樣就可以在，當資料庫崩潰的後，直接從redo log中恢復數據，保證資料的正確性

redo log 預設儲存在兩個檔案中ib_logfile0 ib_logfile1，這兩個檔案都是固定大小的。為什麼需要固定大小？

這是因為redo log的順序讀取 的特性造成的，必須是連續的儲存空間

二、隨機讀寫與順序讀寫

看一張圖

一般我們的資料都是分散在磁碟上的：

機械硬碟的讀寫順序是：

1. 定位到磁軌
2. 等待旋轉到對應磁區
3. 開始讀取寫入

固態讀寫：

1. 直接定位到快閃記憶體晶片（這也是為啥固態比機械快）
2. 開始讀寫

其實不管機械還是固態，我們去儲存時，都是透過檔案系統與磁碟打交道的，而他們打交道的方式就有兩個。 隨機讀寫和順序讀寫

1. 隨機讀寫儲存的資料是分佈在不同的區塊（預設1block= 8扇區=4K）
2. 而順序存儲，顧名思義，資料是分佈在一串連續的區塊中，這樣讀取速度就大大提升了

三、回到我們架構圖

看到buffer pool中的Log Buffer，其就是用來寫redo log 之前存在的緩衝區

在這裡，redo log具體的執行策略有三種：

##不用寫
0. Log Buffer，只需要每秒寫redo log 磁碟資料一次，效能高，但會造成資料1s 內的一致性問題。適用於強實時性，弱一致性，例如評論區評論
寫
1. Log Buffer，同時寫入磁碟，效能最差，一致性最高。適用於弱實時性，強一致性,例如支付場景
寫
2. Log Buffer，同時寫到os buffer（其會每秒呼叫fsync 將資料刷入磁碟），效能好，安全性也高。這是即時性適中 一致性適中的，例如訂單類別。

我們透過

innodb_flush_log_at_trx_commit就可以設定執行策略。預設為1

記憶體結構小結

#Buffer Pool 用來加速讀取
1. Change Buffer用於沒有非唯一索引的加速寫入
2. Log Buffer 用於加速redo log寫
4. 自適應Hash索引主要用於加快查詢頁。在查詢時，Innodb透過監視索引搜尋的機制來判斷目前查詢是否能走Hash索引。例如LIKE運算子和% 通配符就不能走。

硬碟結構

一、System Tablespace

#儲存在一個叫

ibdata1的檔案中，其中包含：

#InnoDB Data Dictionary，儲存了元數據，例如表結構資訊、索引等
1. Doublewrite Buffer 當
2. Buffer Pool寫入資料頁時，不是直接寫入到文件，而是先寫入到這個區域。這樣做的好處的是，一但作業系統，檔案系統或是mysql掛掉，可以直接從這個Buffer中取得資料。
Change Buffer 當Mysql shut down的時候，修改就會被儲存在磁碟這裡
3. Undo Logs 記錄交易修改操作

二、File-Per- Table Tablespaces

每個表都有一張

.ibd 的文件，儲存資料和索引。

有了
1. 每表檔案表空間可以使得 ALTER TABLE與 TRUNCATE TABLE 效能得到很好的提升。例如ALTER TABLE，相較於對駐留在共享表空間中的表，在修改表時，會進行表複製操作，這可能會增加表空間佔用的磁碟空間量。此類操作可能需要與表中的資料以及索引一樣多的額外空間。該空間不會像每表檔案表空間那樣釋放回作業系統。
可以在單獨的儲存裝置上建立每個表格文件表空間資料文件，以進行I / O最佳化，空間管理或備份。這就意味著表資料與結構容易在不同資料庫中遷移。
2. 當發生資料損壞，備份或二進位日誌不可用或無法重新啟動MySQL伺服器執行個體時，儲存在單一表空間資料檔案中的表可以節省時間並提高成功復原的機會。

當然有優點就有缺陷：

1. 儲存空間的使用率低，會存在碎片，在Drop table的時候會影響效能（除非你自己管理了碎片）
2. 因為每個表分成各自的表文件，作業系統不能同時進行fsync一次刷入資料到文件中
3. mysqld會持續保持每個表文件的文件句柄， 以提供維持對檔案的持續存取

三、General Tablespaces

1. #通用表空間又叫共享表空間，他可以儲存多個表的資料
2. 如果儲存相同數量的表，消耗的儲存比每個表表空間 小
3. 在MySQL 5.7.24中棄用了將表分區放置在常規表空間中的支持，並且在將來的MySQL版本中將不再支持。

四、Temporary Tablespaces

儲存在一個叫 ibtmp1 的檔案中。正常情況下Mysql啟動的時候會建立臨時表空間，停止的時候會刪除臨時表空間。並且它能夠自動擴容。

五、Undo Tablespaces

1. 提供修改操作的 原子性，即當修改到一半，出現異常，可以透過Undo 日誌回滾。
2. 它儲存了，交易開始前的原始資料與這次的修改操作。
3. Undo log 存在於回滾段（rollback segment）中，回滾段又存在系統表空間``撤銷表空間``臨時表空間中，如架構圖所示。

Redo Log

前面已經介紹過

總結一下，我們執行一句update SQL 會發生什麼事

1. 查詢到我們要修改的那條數據，我們在這裡稱做origin，返給執行器
2. 在執行器中，修改數據，稱為modification
3. 將modification刷入內存，Buffer Pool的Change Buffer
4. 引擎層：記錄undo log （實作交易原子性）
5. 引擎層：記錄redo log （崩潰復原使用）
6. 服務層：記錄bin log（記錄DDL）
7. 傳回更新成功結果
8. 資料等待被工作執行緒刷入磁碟

Bin log

#說了Undo、Redo也順便說一下Bin log.

1. 這一個log和innodb引擎沒有多大關係，我們前面說的兩種日誌，都在是innodb引擎層的。而Bin log是處於服務層的。所以他能被各引擎通用
2. 他的主要角色是什麼呢？首先，Bin log 是以事件的形式，記錄了各個 DDL DML 語句，它是一種邏輯意義上的日誌。
3. 能夠實現主從複製，從伺服器拿到主伺服器的bin log日誌，然後執行。
4. 做資料復原，拿到某個時間段的日誌，重新執行一遍。

跟著一個SQL語句完成全域預覽後，我們來看看回過頭來讓SQL變得更豐富，我們來加入一個索引試試看

#華麗的分割線

---

索引篇

要想徹底弄清楚InnoDB中的索引是個什麼東西，就必須要了解它的在檔案儲存層級

Innodb中將檔案儲存分為了四個層級

Pages, Extents, Segments, and Tablespaces

它們的關係是：

• 預設的extent 大小為1M 即64個16KB的Page。平常我們檔案系統所說的頁大小是 4KB，包含 8 個 512Byte的磁區。

儲存結構B樹變體B 樹

所以有時候，我們被要求主鍵為什麼要有序的原因就是，如果我們在一個有序的欄位上，建立索引，然後插入資料。 在儲存的時候，innodb就會依照順序一個個儲存到 頁 上，存滿一個頁再去申請新的頁，然後接著存。

但如果我們的欄位是無序的，儲存的位置就會在不同的頁面上。當我們的資料儲存到一個已經被 存滿的頁上時，就會造成頁分裂，從而形成碎片。

幾種不同的索引組織形式

1. 叢集索引，如上圖B 樹圖所示，子節點上儲存行資料，而索引的排列的順序和索引鍵值順序一致的話就是叢集索引。主鍵索引就是叢集索引，除了主鍵索引，其他所以都是輔助索引
2. 輔助索引，如果我們建立了一個輔助索引，它的葉子節點上只儲存自己的值和主鍵索引的值。這就意味著，如果我們透過輔助索引查詢所有數據，就會先去尋找輔助索引中的主鍵鍵值，然後再去主鍵索引#裡面，查到相關資料。這個過程稱為回表
3. rowid 如果沒有主鍵索引怎麼辦呢？
   1. 沒有主鍵，但有一個 Unique key 而且都不是 null的，則會根據這個 key來建立叢集索引。
   2. 那上面兩種都沒有呢，別擔心，innodb自己維護了一個叫rowid 的東西，根據這個id來創建聚簇索引

索引如何運作

搞清楚什麼是索引，結構是什麼之後。 讓我們來看看，什麼時候我們要用到索引，理解了這些能更好的幫助我們創建正確高效的索引

1. 離散度低不建索引，也就是資料之間相差不大的就沒必要建立索引。 （因為建立索引，在查詢的時候，innodb大多資料都是相同的，我走索引 和全表沒什麼差別就會直接全表查詢）。如 性別欄位。這樣反而浪費了大量的儲存空間。

2. 聯合欄位索引，例如idx(name, class_name)

   1. 當執行select * from stu where class_name = xx and name = lzw 查詢時，也能走idx 這個索引的，因為優化器將SQL最佳化為了name = lzw and class_name = xx
   2. #當需要有select ··· where name = lzw 的時候，不需要建立一個單獨的name#索引，會直接走idx這個索引
   3. 覆蓋索引。如果我們這次查詢的所有資料全都包含在索引裡面了，就不需要再 回表去查詢了。例如：select class_name from stu where name =lzw

3. 索引條件下推(index_condition_pushdown)

   1. 有這樣一條SQL，select *  from stu where name = lzw and class_name like '%xx'
   2. 如果沒有索引條件下推，因為後面是like ' %xx'的查詢條件，所以這裡先根據name 走idx聯合索引 查詢到幾個資料後，再回表查詢到全量row資料，然後在server層進行like 過濾找到資料
   3. 如果有，則直接在引擎層對like也進行過濾了，相當於把server層這個過濾操作下推到引擎層了。如圖所示：

建立索引註意事項

1. 在where、order、join的on使用次數多的時候，加上索引
2. 離散度高的字段才能建立索引
3. 聯合索引把離散度高的放前面（因為首先根據第一個字段匹配，能迅速定位資料位置。）
4. 頻繁更新的欄位不能建索引（造成頁分裂，索引依序存儲，如果儲存頁滿了，再去插入就會造成頁分割）
5. 使用例如replace、sum、count等函數的時候不會使用索引，所以沒必要額外建
6. 出現隱含轉換的時候，例如字串轉int ，也用不到索引
7. 特別長的字段，可以截取前面幾位創建索引（可以透過select count(distinct left(name, 10))/count(*) 來看離散度，決定到底提取前幾位）

• tips: 執行一個SQL，不能確切地說他是否能不能用到索引，畢竟這一切都是優化器決定的。例如你使用了 Cost Base Optimizer 基於開銷的優化器，那種開銷小就用哪種優化。

弄清楚了索引，我們就有能力打開鎖篇的副本了

又一個華麗的分割線

---

鎖篇

四大特性

先回顧我們熟能詳的幾個基本概念：

1. 原子性（透過Undo log實作）
2. 一致性
3. 隔離性
4. 持久性（當機恢復，Redo log double write 實作）

讀取一致性問題應該由資料庫的交易隔離等級來解決（SQL92 標準）

#前提，在一個交易中：

1. 髒讀（讀到了別人還沒有commit的數據，然後別人又回滾掉了）
2. 不可重複讀（第一次讀取到了數據，然後別人修改commit了，再次去讀取就讀到了別人已經commit的資料）
3. 幻讀（在範圍查詢的時候，讀到別人新加入的資料）

SQL92 標準規定： (並發度從左到右，依序降低)

• tips: Innodb中，Repeatable Read的幻讀，也不可能存在，因為它自己解決了

Innodb中如何解決可重複讀(RR) 中產生幻讀的情況

鎖定模型

##LBCC (Lock Based Concurrency Control)讀之前加個鎖，但這樣可能會導致效能問題 => 讀的時候加鎖導致其他事務都不能讀寫了，性能低下
1. MVCC(Multi Version Concurrency Control) 讀的時候記錄當時快照，別人來讀取快照就行=> 效能消耗，儲存消耗

這兩個方案在Innodb中結合使用。這裡簡單說明一下

RR 的MVCC實作，圖中回溯id 初始值不應該是0而是NULL，這裡為了方便寫成0

##RC的MVCC實作是對同一個交易的多個讀取建立一個版本
• 而RR 是同一個交易任何一條都會建立一個版本
透過

MVCC

與LBCC的結合，InnoDB能解決對於不加鎖條件下的幻讀的情況。而不必像 Serializable 一樣，必須讓交易串行進行，無任何並發。 下面我們來深入研究一下

InnoDB鎖定

是如何實作RR 交易隔離層級的鎖定深入MVCC在Innodb的實作

一、Innodb 的鎖

Shared and Exclusive Locks 共享與排它鎖定=>（S、X）

1. Intention Locks 意圖鎖定=> 這裡指的是兩把鎖，其實就是
表格層級
2. 的共用和排它鎖=> (IS、IX)##上面這
四把鎖

是

最基本鎖定的類型Record Locks 記錄鎖定

#Gap Locks 間隙鎖定
3. Next-key Locks 臨片
5. #這三把鎖，理解成對於上面
四把鎖

實現的三種演算法方式，我們這裡暫且把它們稱為：

高階鎖定Insert Intention Locks 插入鎖定

AUTO-INC Locks 自增鍵鎖定
6. Predicate Locks for Spatial Indexes 專用於給Spatial Indexes用的
8. #上面三把是額外擴充的鎖

二、讀寫鎖深入解釋

要使用共享鎖，在語句後面加上

lock in share mode

。排它鎖預設
1. Insert、Update、Delete會使用。顯示使用在語句後面加上for update。 意向鎖都是由資料庫自己維護的。 （主要作用是給表打一個標記
，記錄這個表是否被鎖住了） => 如果沒有這個鎖，別的事務想鎖住這張表的時候，就要去全表掃描是否有鎖，效率太低。所以才會有意向鎖的存在。
2. 補充：Mysql中鎖，到底鎖的是什麼

鎖定的是索引，那麼這個時候可能有人要問了：那如果我不建立索引呢？

索引的存在，我們上面講過了，這裡再回顧一下，有下面幾個情況

你建了一個Primary key， 就是聚集索引（存儲的是

完整的資料

）
1. 沒有主鍵，但有一個Unique key 而是都不是null的，則會根據這個key來建立叢集索引
2. 那上面兩種都沒有呢，別擔心，innodb自己維護了一個叫rowid
的東西，根據這個id來創建
3. 聚簇索引所以一個表裡面，必然會存在一個索引，所以鎖當然總有索引拿來鎖住了。

當要給一張你沒有顯示創建

索引

的表，進行

加鎖查詢時，資料庫其實是不知道到底要查哪些資料的，整張表可能都會用到。所以索性就鎖整張表。

• 如果是給輔助索引加寫鎖，例如select * from where name = 'xxx' for update 最後要回表查主鍵上的信息，所以這個時候除了鎖輔助索引還要鎖主鍵索引

三、高階鎖定深入解釋

首先上三個概念，有這麼一組資料：主鍵是 1，3，6，9 在儲存時候有如下：x 1 x 3 x x 6 x x x 9 x···

記錄鎖，鎖的是每個記錄，也就是 1，3，6，9 間隙鎖，鎖的是記錄間隙，每個 x，(-∞,1), (1,3), (3,6), (6,9), (9, ∞) 臨鎖，鎖的是(-∞,1], (1,3], (3,6], (6,9], (9, ∞] 左開右閉的區間

首先這三種鎖都是排它鎖， 且臨鍵鎖= 記錄鎖間隙鎖

1. #當select * from xxx where id = 3 for update時，產生記錄鎖定
2. 當select * from xxx where id = 5 for update 時，產生間隙鎖=> 鎖住了(3,6)，這裡要格外注意一點：間隙鎖之間是不衝突的。
3. 當select * from xxx where id = 5 for update 時，產生臨鍵鎖=> 鎖住了(3,6] , mysql預設使用臨鍵鎖，如果不滿足1 ，2 情況則他的行鎖的都是臨鍵鎖

• 回到開始的問題，在這裡Record Lock 行鎖防止別的交易修改或刪除，Gap Lock 間隙鎖防止別的交易新增，Gap Lock 和Record Lock結合形成的Next- Key鎖定共同解決RR等級在寫資料時的幻讀問題。

#說到了鎖那麼必然逃不過要說一下死鎖

死鎖後的檢查

1. show status like 'innodb_row_lock_%'
   1. Innodb_row_lock_current_waits 目前正在有多少等待鎖定
   2. Innodb_row_lock_time 共等待了多少時間
   3. Innodb_row_lock_time_avg 平均等多少時間
   4. Innodb_row_lock_time_max 最大等多久
   5. Innodb_row_lock_waits 總共出現過多少次等待
2. #select * from information_schema.INNODB_TRX 可以查看到目前正在運作和被鎖定的交易
3. show full  processlist# = select  *  from    information_ = select  *  from   mainformation_listsche#.process#list ## 可以查詢出是哪個使用者 在哪台機器host的哪個連接埠上 連接哪個資料庫 執行什麼指令 的狀態與時間

死鎖預防

1. 保證存取資料的順序
2. 避免where的時候不用索引（這樣會鎖定表，不但死鎖更容易產生，而且效能更低）
3. 一個非常大的事務，拆成多個小的事務
4. 盡量使用等值查詢（就算用範圍查詢也要限定一個區間，而不要只開不閉，例如id > 1 就鎖住後面所有）

優化篇

分庫分錶

動態選擇資料來源

編碼層-- 實作AbstracRoutingDataSource => 框架層 -- 實作 Mybatis Plugin => 驅動層-- Sharding-JDBC(配置多個資料來源，根據自訂實現的策略對資料進行分庫分錶儲存)核心流程,SQL解析=>執行最佳化=>SQL資料庫路由=>SQL改變(例如分錶，改表名)=>SQL執行=>結果歸併） => 代理層 -- Mycat（將所有與資料庫的連接獨立出來。全部由Mycat連接，其他服務存取Mycat以取得資料） => 服務層-- 特殊的SQL版本

MYSQL如何做最佳化

說到底我們學習這麼多知識都是為了能更好使用MYSQL，那就讓我們來實作一下，建立一個完整的最佳化體系

要想獲得更好的查詢效能，可以從這張查詢執行過程

開始

一、客戶端連線池

新增連線池，避免每次都新建、銷毀連線

那我們的連線池是不是越多越好？ 有興趣的盆友可以看看這篇文章：About Pool Sizing

我大概總結一下：###

1. 我們並發的執行SQL，並不會因為連線數量增多而變快。為什麼呢？如果我有10000連接同時並發執行，難道不比你10個連接執行快得多嗎? 答案是否定的，不僅不快反而越來越慢。
   1. 在電腦中，我們都知道只有CPU才能真正去執行執行緒。而作業系統因為用時間分片的技術，讓我們以為一個CPU核心執行了多個執行緒。
   2. 但其實上一個CPU在某個時間段只能執行一個執行緒，所以無論我們怎麼增加並發， CPU還是只能在這個時段處理這麼多資料。
   3. 那就算CPU處理不了這麼多數據，又怎麼會變慢？因為時間分片，當多個執行緒看起來在"同時執行"，其實他們之間的上下文切換十分耗時
   4. #所以，一旦執行緒的數量超過了CPU核心的數量，再增加執行緒數係統就只會更慢，而不是更快。
2. 當然，這只是其中最核心的原因，磁碟同樣也會對速度有影響，同時也對我們連接數配置有影響。
   1. 例如我們使用的機械硬碟，我們要透過旋轉，尋址到某個位置，再進行I/O操作，這個時候，CPU就可以把時間，分片給其他線程，以提升處理效率和速度
   2. 所以，如果你用的是機械硬碟，我們通常可以多加一些連接數，保持高並發
   3. 但如果你用的是SSD 呢，因為I/O等待時間非常短，所以我們就無法加入太多連線數
3. 透過來說你需要遵循這麼一個公式：執行緒數= ((核心數* 2) 有效磁碟數)。例如一台i7 4core 1hard disk的機器，就是4 * 2 1 = 9
4. 看到這個公式不知道大家是不是很眼熟，這不僅適用於資料庫連接，也適用於任何很多CPU運算與I/O的場景 例如：設定最大執行緒數等

#二、資料庫整體設計方案

第三方快取

如果並發非常大，就不能讓他們全打到資料庫上，在客戶端連接資料庫查詢時，加入如Redis這種三方快取

叢集方式部署資料庫

既然我們一個資料庫承受不了巨大的並發，那為什麼不多再增加幾台機器呢？ 主從複製原理圖

從圖中我們不難看出、Mysql主從複製讀寫分離 異步複製的特性。

• tips: 在把Binary Log寫入relay log之後，slave都會把最新讀取到的 Binary Log Position記錄到master info上，下次就直接從這個位置去取。

不同方式的主從複製

上面這種異步的主從複製，很明顯的一個問題就是，更新不及時的問題。當寫入一個資料後，馬上有使用者讀取，讀取的還是之前的數據，也就是存在著延遲。 要解決延時的問題，就需要引入事務

1. 全同步複製，事務方式執行，主節點先寫入，然後讓所有slave寫，必須從節點把資料寫完，才回傳寫成功，這樣的話會大大影響寫入的效能
2. 半同步複製，只要有一個salve寫入數據，就算成功。 （如果需要半同步複製，主從節點都需要安裝semisync_mater.so和semisync_slave.so插件）
3. GTID（global transaction identities）複製，主庫並行複製的時候，從庫也並行複製，解決主從同步複製延遲，實現自動的failover動作，即主節點掛掉，選舉從節點後，能快速自動避免資料遺失。

叢集高可用方案

1. 主從HAPrxoy keeplive
2. NDB
3. Glaera Cluster for MySQL
4. #MHA （Master-Mater replication manager for MySQL），MMM（MySQL Master High Available）
5. MGR（MySQL Group Replication） => MySQL Cluster

#分錶

將資料分類劃分，分成不同表，減少對單一表造成過多鎖定操作影響效能

表格結構

1. 設計合理欄位類型
2. 設計合理欄位長度

三、最佳化器與執行引擎

#慢日誌

開啟show_query_log，執行時間超過變數long_query_time的SQL會被記錄下來。 可以使用mysqldumpslow /var/lib/mysql/mysql-slow.log，還有很多外掛可以提供比這個更優雅的分析，這裡就不詳細講了。

explain分析SQL

任何SQL在寫完之後都應該explain一下

1. 驅動表 -  例如濫用left/right join導致效能低

1. 使用left/right join會直接指定驅動表，在MYSQL中，預設使用Nest loop join進行表格關聯（即透過驅動表的結果集作為循環基礎數據，然後透過此集合中的每個數據篩選下一個關聯表的數據，最後合併結果，得出我們常說的臨時表）。
2. 如果驅動表的資料是 百萬千萬等級的，可想而知這聯表查詢得有多慢。但反過來，如果以小表作為驅動表，借助千萬級表的索引查詢就能變得很快。
3. 如果你不確定到底該用誰來作為驅動表，那麼請交給優化器來決定，例如：select xxx from table1, table2, table3 where ·· ·，優化器會將查詢記錄行數少的表當作驅動表。
4. 如果你就是想自己指定驅動表，那麼請拿好Explain武器，在Explain的結果中，第一個就是基礎驅動表
5. 排序。同樣的，對不同表排序也是有很大的效能差異，我們盡量對驅動表進行排序，而不要對臨時表，也就是合併後的結果集進行排序。即執行計劃中出現了 using temporary，就需要進行最佳化。

2. 執行計劃各參數意義

1. select_type（查詢的類型）：普通查詢和複雜查詢（聯合查詢、子查詢等）
   1. SIMPLE，查詢不包含子查詢或UNION
   2. PRIMARY，如果查詢包含複雜查詢的子結構，那麼就需要用到主鍵查詢
   3. SUBQUERY，在select或where中包含子查詢
   4. DERIVED，在from中包含子查詢
   5. UNION RESULT，從union 表格查詢子查詢
2. table 使用到的表格名稱
3. type（存取類型），找到所需行的方式，從上往下，查詢速度越來越快
   1. const或system 常數等級的掃描，查詢表最快的一種，system是const的特殊情況（表中只有一條資料）
   2. eq_ref 唯一性索引掃描
   3. ref 非唯一性索引掃描
   4. range 索引的範圍掃描，例如between、等範圍查詢
   5. #index（index full）掃描全部索引樹
   6. ##ALL 掃描全表
   7. NULL，不需要存取表或索引
possible_keys，給予使用哪個索引能找到表中的記錄。這裡被列出的索引
4. 不一定使用
key：到底
5. 哪一個索引被真正使用到了。如果沒有則為NULL
key_len：使用的索引所佔用的位元組數
6. #ref：哪個欄位或常數和
7. 索引（key）一起被使用
rows：一共掃描了多少行
8. filtered（百分比）：有多少資料在server層還進行了過濾
9. Extra：額外資訊
10. # #only index
    1. 資訊只需要從索引中查出，可能用到了覆蓋索引，查詢非常快
    using where
    2. 如果查詢沒有使用索引，這裡會在 server層過濾再使用where來過濾結果集
    impossible where
    3. 啥也沒查出來
    using filesort
    4. ，只要沒有透過索引來排序，而是使用了其他排序的方式就是filesort
    using temporary
    5. （需要透過臨時表來對結果集進行暫時存儲，然後再進行計算。）一般來說這種情況都是進行了DISTINCT、排序、分組
    #using index condition
    6. 索引下推，上文講過，就是把server層這個過濾操作下推到引擎層
#四、儲存引擎

2. ##當僅僅是
插入與查詢
3. 比較多的時候，可以使用MyISAM存儲引擎當只是使用臨時數據，可以使用
memory

當

插入、更新、查詢

等並發數很多時，可以使用

InnoDB
2. 總結
3. 從五個層次回答MYSQL最佳化，由上至下
5. SQL與索引

儲存引擎與表格結構

資料庫架構

MySQL設定# #####硬體與作業系統#########除此之外，查資料慢，要不僅僅拘留於一味的"優化" 資料庫，而是要從業務應用層面去分析。例如對資料進行緩存，對請求進行限流等。 ######我們下篇文章見###

相關免費學習推薦：mysql影片教學

#

以上是一篇文章讓你去懂MYSQL底層原理的詳細內容。更多資訊請關注PHP中文網其他相關文章！