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

語言: CN / TW / HK

時間 2023-03-09 12:01:21 博學谷狂野架構師

主題:

file

連接層

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

服務層

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

引擎層

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

存儲層

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

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

存儲引擎介紹

file

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

建表時指定存儲引擎

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

查詢當前數據庫支持的存儲引擎

SHOW ENGINES;

file

創建表 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

介紹

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

特點

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

文件

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

參數：innodb_file_per_table

show variables like 'innodb_file_per_table';

Variable_name	Value
innodb_file_per_table	ON

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

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

ibd2sdi account.ibd

針對MySQL8有效

邏輯存儲結構

file

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

MyISAM

介紹

MyISAM是MySQL早期的默認存儲引擎。

特點

訪問速度快

不支持事務，不支持外鍵

支持表鎖，不支持行鎖

文件

xxx.sdi：存儲表結構信息

xxx.MYD: 存儲數據

xxx.MYI: 存儲索引

Memory

介紹

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

特點

內存存放

hash索引（默認）

文件

xxx.sdi：存儲表結構信息

區別及特點

特點	InnoDB	MyISAM	Memory
存儲限制	64TB	有	有
事務安全	支持	-	-
鎖機制	行鎖	表鎖	表鎖
B+tree索引	支持	支持	支持
Hash索引	-	-	支持
全文索引	支持(5.6版本之後)	支持	-
空間使用	高	底	N/A
內存使用	高	底	中等
批量插入速度	低	高	高
支持外鍵	支持	-	-

存儲引擎選擇

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

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

MySQL InnoDB引擎

邏輯存儲引擎

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

表空間

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

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

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

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

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

在行中，默認有兩個隱藏字段：

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

架構

概述

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

file

內存架構

file

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

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)

Change Buffer

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

Change Buffer的意義是什麼呢?

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

file

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

Adaptive Hash Index

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

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

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

參數： adaptive_hash_index

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體系結構的右邊部分，也就是磁盤結構：

file

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。

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)

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

file

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)

Undo Tablespaces

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

Temporary Tablespaces

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

Doublewrite Buffer Files

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

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中涉及到的後台線程。

file

後台線程

file

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

Master Thread

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

IO Thread

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

線程類型	默認個數	職責
Read thread	4	負責讀操作
Write thread	4	負責寫操作
Log thread	1	負責將日誌緩衝區刷新到磁盤
Insert buffer thread	1	負責將寫緩衝區內容刷新到磁盤

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

show engine innodb status;

file

Purge Thread

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

Page Cleaner Thread

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

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

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

轉載請註明出處！

「其他文章」