為什么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 的作業壓力，減少阻塞

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

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

轉載請注明出處！

標籤：Java

上一篇：Spring 國際化@Autowired MessageSource 組態檔讀取順序

下一篇：自從用了 Stream，代碼更簡潔優雅了！