為什麼99%的程式員都做不好SQL優化？

1. 連接層

最上層是一些客戶端和鏈接服務，包含本地sock 通信和大多數基於客戶端/服務端工具實現的類似於 TCP/IP的通信。主要完成一些類似於連接處理、授權認證、及相關的安全方案。在該層上引入了線程 池的概念，為通過認證安全接入的客戶端提供線程。同樣在該層上可以實現基於SSL的安全鏈接。服務 器也會為安全接入的每個客戶端驗證它所具有的操作許可權。

2. 服務層

第二層架構主要完成大多數的核心服務功能，如SQL介面，並完成緩存的查詢，SQL的分析和優化，部 分內置函數的執行。所有跨存儲引擎的功能也在這一層實現，如 過程、函數等。在該層，伺服器會解 析查詢並創建相應的內部解析樹，並對其完成相應的優化如確定表的查詢的順序，是否利用索引等， 最後生成相應的執行操作。如果是select語句，伺服器還會查詢內部的緩存，如果緩存空間足夠大， 這樣在解決大量讀操作的環境中能夠很好的提升系統的性能。

3. 引擎層

存儲引擎層， 存儲引擎真正的負責了MySQL中數據的存儲和提取，伺服器通過API和存儲引擎進行通 信。不同的存儲引擎具有不同的功能，這樣我們可以根據自己的需要，來選取合適的存儲引擎。資料庫 中的索引是在存儲引擎層實現的。

4. 存儲層

數據存儲層， 主要是將數據(如: redolog、undolog、數據、索引、二進位日誌、錯誤日誌、查詢 日誌、慢查詢日誌等)存儲在文件系統之上，並完成與存儲引擎的交互。

和其他資料庫相比，MySQL有點與眾不同，它的架構可以在多種不同場景中應用併發揮良好作用。主要 體現在存儲引擎上，插件式的存儲引擎架構，將查詢處理和其他的系統任務以及數據的存儲提取分離。 這種架構可以根據業務的需求和實際需要選擇合適的存儲引擎。

存儲引擎介紹

大家可能沒有聽說過存儲引擎，但是一定聽過引擎這個詞，引擎就是發動機，是一個機器的核心組件。 比如，對於艦載機、直升機、火箭來說，他們都有各自的引擎，是他們最為核心的組件。而我們在選擇 引擎的時候，需要在合適的場景，選擇合適的存儲引擎，就像在直升機上，我們不能選擇艦載機的引擎 一樣。 而對於存儲引擎，也是一樣，他是mysql資料庫的核心，我們也需要在合適的場景選擇合適的存儲引 擎。接下來就來介紹一下存儲引擎。 存儲引擎就是存儲數據、建立索引、更新/查詢數據等技術的實現方式 。存儲引擎是基於表的，而不是 基於庫的，所以存儲引擎也可被稱為表類型。我們可以在創建表的時候，來指定選擇的存儲引擎，如果 沒有指定將自動選擇預設的存儲引擎。

1. 建表時指定存儲引擎

CREATE TABLE 表名(
    欄位1 欄位1類型 [ COMMENT 欄位1註釋 ] ,
    ......
    欄位n 欄位n類型 [COMMENT 欄位n註釋 ]
) ENGINE = INNODB [ COMMENT 表註釋 ] ;

1. 查詢當前資料庫支持的存儲引擎

SHOW ENGINES;

• 創建表 my_myisam , 並指定MyISAM存儲引擎

CREATE TABLE my_myisam(
	`id` INT,
	`name` VARCHAR(10)
	)ENGINE = MYISAM;

• 創建表 my_memory , 指定Memory存儲引擎

CREATE TABLE my_memory(
	`id` INT,
	`name` VARCHAR(10)
	)ENGINE = MEMORY;

存儲引擎特點

上面我們介紹了什麼是存儲引擎，以及如何在建表時如何指定存儲引擎，接下來我們就來介紹下來上面 重點提到的三種存儲引擎 InnoDB、MyISAM、Memory的特點。

InnoDB

1. 介紹

InnoDB是一種兼顧高可靠性和高性能的通用存儲引擎，在 MySQL 5.5 之後，InnoDB是預設的 MySQL 存儲引擎。

2. 特點

• DML操作遵循ACID模型，支持事務；
• 行級鎖，提高併發訪問性能；
• 支持外鍵FOREIGN KEY約束，保證數據的完整性和正確性；

3. 文件

xxx.ibd：xxx代表的是表名，innoDB引擎的每張表都會對應這樣一個表空間文件，存儲該表的表結 構（frm-早期的 、sdi-新版的）、數據和索引。

參數：innodb_file_per_table

show variables like 'innodb_file_per_table';

如果該參數開啟，代表對於InnoDB引擎的表，每一張表都對應一個ibd文件。 我們直接打開MySQL的 數據存放目錄： D:\DevelopTools\mysql-5.7.19-winx64\data ， 這個目錄下有很多文件 夾，不同的文件夾代表不同的資料庫，我們直接打開frx_db02文件夾。

可以看到裡面有很多的ibd文件，每一個ibd文件就對應一張表，比如：我們有一張表 account，就有這樣的一個account.ibd文件，而在這個ibd文件中不僅存放表結構、數據，還會存放該表對應的索引信息。 而該文件是基於二進位存儲的，不能直接基於記事本打開，我們可以使用mysql提供的一個指令 ibd2sdi ，通過該指令就可以從ibd文件中提取sdi信息，而sdi數據字典信息中就包含該表的表結構。

ibd2sdi account.ibd

針對MySQL8有效

4. 邏輯存儲結構

• 表空間 : InnoDB存儲引擎邏輯結構的最高層，ibd文件其實就是表空間文件，在表空間中可以包含多個Segment段。
• 段 : 表空間是由各個段組成的， 常見的段有數據段、索引段、回滾段等。InnoDB中對於段的管理，都是引擎自身完成，不需要人為對其控制，一個段中包含多個區。
• 區 : 區是表空間的單元結構，每個區的大小為1M。 預設情況下， InnoDB存儲引擎頁大小為16K， 即一個區中一共有64個連續的頁。
• 頁 : 頁是組成區的最小單元，頁也是InnoDB 存儲引擎磁碟管理的最小單元，每個頁的大小預設為 16KB。為了保證頁的連續性，InnoDB 存儲引擎每次從磁碟申請 4-5 個區。
• 行 : InnoDB 存儲引擎是面向行的，也就是說數據是按行進行存放的，在每一行中除了定義表時所指定的欄位以外，還包含兩個隱藏欄位(後面會詳細介紹)。

MyISAM

1. 介紹

MyISAM是MySQL早期的預設存儲引擎。

2. 特點

不支持事務，不支持外鍵

支持表鎖，不支持行鎖

訪問速度快

3. 文件

xxx.sdi：存儲表結構信息

xxx.MYD: 存儲數據

xxx.MYI: 存儲索引

Memory

1. 介紹

Memory引擎的表數據時存儲在記憶體中的，由於受到硬體問題、或斷電問題的影響，只能將這些表作為 臨時表或緩存使用。

2. 特點

記憶體存放

hash索引（預設）

3. 文件

xxx.sdi：存儲表結構信息

區別及特點

存儲引擎選擇

在選擇存儲引擎時，應該根據應用系統的特點選擇合適的存儲引擎。對於複雜的應用系統，還可以根據 實際情況選擇多種存儲引擎進行組合。

• InnoDB: 是Mysql的預設存儲引擎，支持事務、外鍵。如果應用對事務的完整性有比較高的要 求，在併發條件下要求數據的一致性，數據操作除了插入和查詢之外，還包含很多的更新、刪除操 作，那麼InnoDB存儲引擎是比較合適的選擇。
• MyISAM ： 如果應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，並且對事務的完 整性、併發性要求不是很高，那麼選擇這個存儲引擎是非常合適的。
• MEMORY：將所有數據保存在記憶體中，訪問速度快，通常用於臨時表及緩存。MEMORY的缺陷就是 對錶的大小有限制，太大的表無法緩存在記憶體中，而且無法保障數據的安全性。

MySQL InnoDB引擎

邏輯存儲引擎

InnoDB的邏輯存儲結構如下圖所示:

1. 表空間

表空間是InnoDB存儲引擎邏輯結構的最高層， 如果用戶啟用了參數 innodb_file_per_table(在8.0版本中預設開啟) ，則每張表都會有一個表空間（xxx.ibd），一個mysql實例可以對應多個表空間，用於存儲記錄、索引等數據。

2. 段

段，分為數據段（Leaf node segment）、索引段（Non-leaf node segment）、回滾段（Rollback segment），InnoDB是索引組織表，數據段就是B+樹的葉子節點， 索引段即為B+樹的非葉子節點。段用來管理多個Extent（區）。

3. 區

區，表空間的單元結構，每個區的大小為1M。 預設情況下， InnoDB存儲引擎頁大小為16K， 即一個區中一共有64個連續的頁。

4. 頁

頁，是InnoDB 存儲引擎磁碟管理的最小單元，每個頁的大小預設為 16KB。為了保證頁的連續性，InnoDB 存儲引擎每次從磁碟申請 4-5 個區。

5. 行

行，InnoDB 存儲引擎數據是按行進行存放的。

在行中，預設有兩個隱藏欄位：

• Trx_id：每次對某條記錄進行改動時，都會把對應的事務id賦值給trx_id隱藏列。
• Roll_pointer：每次對某條引記錄進行改動時，都會把舊的版本寫入到undo日誌中，然後這個隱藏列就相當於一個指針，可以通過它來找到該記錄修改前的信息。

架構

概述

MySQL5.5 版本開始，預設使用InnoDB存儲引擎，它擅長事務處理，具有崩潰恢復特性，在日常開發中使用非常廣泛。下麵是InnoDB架構圖，左側為記憶體結構，右側為磁碟結構。

記憶體架構

在左側的記憶體結構中，主要分為這麼四大塊兒： Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer。 下來介紹一下這四個部分。

1. Buffer Pool

InnoDB存儲引擎基於磁碟文件存儲，訪問物理硬碟和在記憶體中進行訪問，速度相差很大，為了儘可能彌補這兩者之間的I/O效率的差值，就需要把經常使用的數據載入到緩衝池中，避免每次訪問都進行磁碟I/O。

在InnoDB的緩衝池中不僅緩存了索引頁和數據頁，還包含了undo頁、插入緩存、自適應哈希索引以及InnoDB的鎖信息等等。

緩衝池 Buffer Pool，是主記憶體中的一個區域，裡面可以緩存磁碟上經常操作的真實數據，在執行增 刪改查操作時，先操作緩衝池中的數據（若緩衝池沒有數據，則從磁碟載入並緩存），然後再以一定頻 率刷新到磁碟，從而減少磁碟IO，加快處理速度。

緩衝池以Page頁為單位，底層採用鏈表數據結構管理Page。根據狀態，將Page分為三種類型：

• free page：空閑page，未被使用。
• clean page：被使用page，數據沒有被修改過。
• dirty page：臟頁，被使用page，數據被修改過，也中數據與磁碟的數據產生了不一致。

在專用伺服器上，通常將多達80％的物理記憶體分配給緩衝池 。參數設置： show variables like 'innodb_buffer_pool_size';

mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

2. Change Buffer

Change Buffer，更改緩衝區（針對於非唯一二級索引頁），在執行DML語句時，如果這些數據Page沒有在Buffer Pool中，不會直接操作磁碟，而會將數據變更存在更改緩衝區 Change Buffer中，在未來數據被讀取時，再將數據合併恢復到Buffer Pool中，再將合併後的數據刷新到磁碟中。

Change Buffer的意義是什麼呢?

先來看一幅圖，這個是二級索引的結構圖：

與聚集索引不同，二級索引通常是非唯一的，並且以相對隨機的順序插入二級索引。同樣，刪除和更新可能會影響索引樹中不相鄰的二級索引頁，如果每一次都操作磁碟，會造成大量的磁碟IO。有了ChangeBuffer之後，我們可以在緩衝池中進行合併處理，減少磁碟IO。

3. Adaptive Hash Index

自適應hash索引，用於優化對Buffer Pool數據的查詢。MySQL的innoDB引擎中雖然沒有直接支持hash索引，但是給我們提供了一個功能就是這個自適應hash索引。因為前面我們講到過，hash索引在進行等值匹配時，一般性能是要高於B+樹的，因為hash索引一般只需要一次IO即可，而B+樹，可能需要幾次匹配，所以hash索引的效率要高，但是hash索引又不適合做範圍查詢、模糊匹配等。

InnoDB存儲引擎會監控對錶上各索引頁的查詢，如果觀察到在特定的條件下hash索引可以提升速度，則建立hash索引，稱之為自適應hash索引。

自適應哈希索引，無需人工干預，是系統根據情況自動完成。

參數： adaptive_hash_index

4. Log Buffer

Log Buffer：日誌緩衝區，用來保存要寫入到磁碟中的log日誌數據（redo log 、undo log），預設大小為 16MB，日誌緩衝區的日誌會定期刷新到磁碟中。如果需要更新、插入或刪除許多行的事務，增加日誌緩衝區的大小可以節省磁碟 I/O。

參數:

innodb_log_buffer_size：緩衝區大小

innodb_flush_log_at_trx_commit：日誌刷新到磁碟時機，取值主要包含以下三個：

1:日誌在每次事務提交時寫入並刷新到磁碟，預設值。

0:每秒將日誌寫入並刷新到磁碟一次。

2:日誌在每次事務提交後寫入，並每秒刷新到磁碟一次。

mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

磁碟結構

接下來，再來看看InnoDB體繫結構的右邊部分，也就是磁碟結構：

1. System Tablespace

系統表空間是更改緩衝區的存儲區域。如果表是在系統表空間而不是每個表文件或通用表空間中創建的，它也可能包含表和索引數據。(在MySQL5.x版本中還包含InnoDB數據字典、undolog等)

參數：innodb_data_file_path

mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)

系統表空間，預設的文件名叫 ibdata1。

2. File-Per-Table Tablespaces

如果開啟了innodb_file_per_table開關 ，則每個表的文件表空間包含單個InnoDB表的數據和索引 ，並存儲在文件系統上的單個數據文件中。

開關參數：innodb_file_per_table，該參數預設開啟。

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

那也就是說，我們每創建一個表，都會產生一個表空間文件，如圖：

3. General Tablespaces

通用表空間，需要通過 CREATE TABLESPACE 語法創建通用表空間，在創建表時，可以指定該表空間。

A. 創建表空間

CREATE TABLESPACE ts_name ADD DATAFILE 'file_name' ENGINE = engine_name;

mysql> CREATE TABLESPACE ts_itheima ADD DATAFILE 'myitheima.ibd' ENGINE = innodb;
Query OK, 0 rows affected (0.00 sec)

B. 創建表時指定表空間

CREATE TABLE xxx ... TABLESPACE ts_name;

mysql> create table a(id int primary key auto_increment,name varchar(10)) engine=innodb tablespace ts_itheima;
Query OK, 0 rows affected (0.01 sec)

4. Undo Tablespaces

撤銷表空間，MySQL實例在初始化時會自動創建兩個預設的undo表空間（初始大小16M），用於存儲 undo log日誌。

5. Temporary Tablespaces

InnoDB 使用會話臨時表空間和全局臨時表空間。存儲用戶創建的臨時表等數據。

6. Doublewrite Buffer Files

雙寫緩衝區，innoDB引擎將數據頁從Buffer Pool刷新到磁碟前，先將數據頁寫入雙寫緩衝區文件中，便於系統異常時恢複數據。

7. Redo Log

重做日誌，是用來實現事務的持久性。該日誌文件由兩部分組成：重做日誌緩衝（redo log buffer）以及重做日誌文件（redo log）,前者是在記憶體中，後者在磁碟中。當事務提交之後會把所有修改信息都會存到該日誌中, 用於在刷新臟頁到磁碟時,發生錯誤時, 進行數據恢復使用。

以迴圈方式寫入重做日誌文件，涉及兩個文件：

-rw-r-----. 1 mysql mysql  50331648 10月  2 22:52 ib_logfile0
-rw-r-----. 1 mysql mysql  50331648 10月  2 22:52 ib_logfile1

前面我們介紹了InnoDB的記憶體結構，以及磁碟結構，那麼記憶體中我們所更新的數據，又是如何到磁碟中的呢？ 此時，就涉及到一組後臺線程，接下來，就來介紹一些InnoDB中涉及到的後臺線程。

後臺線程

在InnoDB的後臺線程中，分為4類，分別是：Master Thread 、IO Thread、Purge Thread、Page Cleaner Thread。

1. Master Thread

核心後臺線程，負責調度其他線程，還負責將緩衝池中的數據非同步刷新到磁碟中, 保持數據的一致性，還包括臟頁的刷新、合併插入緩存、undo頁的回收 。

2. IO Thread

在InnoDB存儲引擎中大量使用了AIO來處理IO請求, 這樣可以極大地提高資料庫的性能，而IOThread主要負責這些IO請求的回調。

我們可以通過以下的這條指令，查看到InnoDB的狀態信息，其中就包含IO Thread信息。

show engine innodb status;

3. Purge Thread

主要用於回收事務已經提交了的undo log，在事務提交之後，undo log可能不用了，就用它來回收。

4. Page Cleaner Thread

協助 Master Thread 刷新臟頁到磁碟的線程，它可以減輕 Master Thread 的工作壓力，減少阻塞

本文由傳智教育博學谷狂野架構師教研團隊發佈。

如果本文對您有幫助，歡迎關註和點贊；如果您有任何建議也可留言評論或私信，您的支持是我堅持創作的動力。

轉載請註明出處！