《MySQL 效能優化》之理解 MySQL 體系結構

作為專欄文章《MySQL 效能優化》的第一篇，本文介紹 MySQL 的伺服器體系結構，包括物理結構、邏輯結構以及外掛式儲存引擎。

例項和資料庫

我們通常所說的 MySQL 資料庫伺服器由一個例項（instance）以及一個資料庫（database）組成。例項包括一組後臺程式/執行緒和許多記憶體結構，用於管理資料庫；資料庫由一組磁碟檔案組成，用於儲存資料和日誌等資訊。MySQL 使用典型的客戶端/伺服器（Client/Server）結構，下圖顯示了一個簡單的 MySQL 體系結構：

客戶端通過通過例項中的後臺程式訪問 MySQL 資料庫。MySQL 採用單程式多執行緒架構，也就是說一個 MySQL 例項在作業系統中就是一個程式（mysqld）。在 Linux 系統中使用ps命令進行檢視：

[root@sqlhost ~]# ps -ef|grep mysql|grep -v grep
mysql     7898     1  1 Feb03 ?        00:12:12 /usr/sbin/mysqld

嚴格來說，一個 MySQL 例項管理的是多個資料庫（也叫模式，Schema）包括系統資料庫 mysql、information_schema、performance_schema、sys 以及使用者建立的資料庫等。使用SHOW DATABASES或者SHOW SCHEMAS命令檢視當前例項中的資料庫：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.14 sec)

資料庫就像是一個容器，儲存了各種物件。例如，資料表（Table）、檢視（View）、儲存過程（Stored Procedure）以及觸發器（Trigger）等。其中，表是儲存資料的主要物件。它們之間的關係如下圖所示：

MySQL 物理結構

MySQL 的物理結構主要包括兩個目錄：軟體的安裝目錄和資料目錄，以及配置檔案和日誌檔案等。

?不同平臺、不同安裝方式（原始碼安裝、二進位制解壓）的目錄結構有所不同，具體可以參考 MySQL 官方文件。

安裝目錄

安裝目錄（Base Directory）是 MySQL 伺服器的安裝路徑，Linux 上使用 RPM 包安裝的預設位置為 /usr/。安裝目錄中主要包含以下內容：

檔案或目錄	描述
bin/	mysql 客戶端和實用程式目錄
sbin/	mysqld 伺服器程式目錄
share/man/	Unix 幫助手冊頁目錄
include/mysql/	標頭檔案目錄
lib/mysql/	庫檔案目錄
share/mysql/	各種字符集、語言相關的錯誤資訊目錄

我們可以使用下面的命令檢視安裝目錄：

mysql> show global variables like "%basedir%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| basedir       | /usr/ |
+---------------+-------+
1 row in set (0.00 sec)

資料目錄

資料目錄（Data Directory）是 MySQL 儲存資料庫檔案的位置，Linux 上使用 RPM 包安裝的預設位置為 /var/lib/mysql/。資料目錄中主要包含以下內容：

檔案或目錄	描述
#innodb_temp/	InnoDB 會話臨時表空間目錄
mysql/	系統資料庫 mysql 檔案目錄
performance_schema/	效能資料庫 performance_schema 檔案目錄
sys/	sys 資料庫檔案目錄
其他子目錄	每個資料庫對應一個檔案目錄，儲存該資料庫中的檔案
auto.cnf	當前伺服器例項的 UUID，用於主從複製
binlog.*	二進位制日誌 binary log 相關檔案
*.pem	SSL 連線相關的證照和金鑰檔案
ib_buffer_pool	緩衝區 buffer pool 中資料頁的頁號轉儲檔案
ibdata1	InnoDB 表空間檔案
ib_logfile0 ib_logfile1	InnoDB 事務日誌（REDO）檔案
ibtmp1	InnoDB 臨時表空間檔案
mysql.ibd	系統資料庫 mysql 資料檔案
mysql.sock	Unix 套接字檔案
undo_001 undo_002	InnoDB UNDO 表空間檔案

我們可以使用以下命令檢視資料目錄：

mysql> show global variables like "%datadir%";
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)

配置檔案

MySQL 伺服器程式和各種工具程式啟動時，需要通過配置檔案（my.cnf 或者 my.ini）讀取各種引數。Linux 上使用 RPM 包安裝的預設配置檔案為 /etc/my.cnf，可以使用mysqld --verbose --help檢視讀取配置檔案的順序：

[root@sqlhost ~]# mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

伺服器執行使用的所有配置選項都可以使用SHOW VARIABLES命令檢視，例如以下命令可以檢視 InnoDB 緩衝池相關的配置：

mysql> show variables like '%buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 8388608        |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_in_core_file     | ON             |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 8388608        |
+-------------------------------------+----------------+
11 rows in set (0.00 sec)

除此之外，MySQL 還使用一些其他的檔案：

• 錯誤日誌檔案，例如 /var/log/mysqld.log；
• 慢查詢日誌檔案，例如 /var/lib/mysql/HOSTNAME-slow.log；
• 伺服器程式 PID 檔案，例如 /var/run/mysqld/mysqld.pid；

MySQL 邏輯結構

MySQL 使用典型的客戶端/伺服器（Client/Server）結構，邏輯結構圖如下所示：

MySQL 體系結構大體可以分為三層：客戶端、伺服器層以及儲存引擎層。其中，伺服器層又包括了連線管理、查詢快取 、SQL 介面、解析器、優化器、緩衝與快取以及各種管理工具與服務等。

具體來說，每個元件的作用如下：

• 客戶端，連線 MySQL 伺服器的各種工具和應用程式。例如 mysql 命令列工具、mysqladmin 以及各種驅動程式等。
• 連線管理，負責監聽和管理客戶端的連線以及執行緒處理等。每一個連線到 MySQL 伺服器的請求都會被分配一個連線執行緒。連線執行緒負責與客戶端的通訊，接受客戶端傳送的命令並且返回伺服器處理的結果。
• 查詢快取 ，用於將執行過的 SELECT 語句和結果快取在記憶體中。每次執行查詢之前判斷是否命中快取，如果命中直接返回快取的結果。快取命中需要滿足許多條件，SQL 語句完全相同，上下文環境相同等。實際上除非是隻讀應用，查詢快取的失效頻率非常高，任何對錶的修改都會導致快取失效；因此，查詢快取在 MySQL 8.0 中已經被刪除。
• SQL 介面，接收客戶端傳送的各種 DML和 DDL 命令，並且返回使用者查詢的結果。另外還包括所有的內建函式（日期、時間、數學以及加密函式）和跨儲存引擎的功能，例如儲存過程、觸發器、檢視等。
• 解析器，對 SQL 語句進行解析，例如語義和語法的分析和檢查，以及物件訪問許可權檢查等。
• 優化器，利用資料庫的統計資訊決定 SQL 語句的最佳執行方式。使用索引還是全表掃描的方式訪問單個表，多表連線的實現方式等。優化器是決定查詢效能的關鍵元件，而資料庫的統計資訊是優化器判斷的基礎。
• 快取與緩衝，由一系列快取組成的，例如資料快取、索引快取以及物件許可權快取等。對於已經訪問過的磁碟資料，在緩衝區中進行快取；下次訪問時可以直接讀取記憶體中的資料，從而減少磁碟 IO。
• 儲存引擎，儲存引擎是對底層物理資料執行實際操作的元件，為伺服器層提供各種運算元據的 API。MySQL 支援外掛式的儲存引擎，包括 InnoDB、MyISAM、Memory 等。
• 管理工具，MySQL 提供的系統管理和控制工具，例如備份與恢復、複製、叢集等。

我們使用 mysql 客戶端工具連線到 MySQL 伺服器：

[root@sqlhost ~]# mysql -h 192.168.56.104 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

以上方式是通過 TCP/IP 網路協議連線到 MySQL 伺服器，需要提供伺服器的 IP、埠以及使用者名稱和密碼等資訊。如果提供的資訊不正確，將會返回錯誤訊息。另外，我們也可以通過命名管道或者 UNIX 套接字進行連線。

連線成功之後就可以執行各種語句和命令，我們以一個查詢語句為例：

SEELCT * FROM employees;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SEELCT * FROM employees' at line 1

以上語句通過 SQL 查詢介面提交給伺服器的處理執行緒，解析器進行語法檢查時發現 SELECT 寫成了 SEELCT，提示的是語法錯誤。示例表來自《SQL 入門教程》示例資料庫。

如果語句沒有問題，通過解析器之後生成解析樹，然後傳送給優化器；優化器判斷是否進行查詢重寫，並且根據統計資訊決定訪問表的方式和順序等。我們可以通過 EXPLAIN 命名瞭解優化器選擇的執行計劃：

mysql> EXPLAIN SELECT * FROM employees;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  107 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

這裡我們查詢的是整個表的資料，因此 MySQL 採用全表掃描的方式（type = ALL）獲取資料。關於執行計劃的解釋，可以參考這篇文章。

最後，由儲存引擎獲取表中的資料；如果資料已經被快取，可以直接從緩衝區獲取。

MySQL 儲存引擎

外掛式儲存引擎是 MySQL 的一大特點體系結構，每個儲存引擎都提供了各自的功能，使用者可以根據業務或者應用場景為資料表選擇不同的儲存引擎。也就是說，儲存引擎的設定是在表級別的；因此也被稱為表型別（table type）。

?從 MySQL 5.5 之後，預設的儲存引擎是 InnoDB。InnoDB 是一個通用的儲存引擎，除非有特殊需求，推薦使用 InnoDB。

MySQL 外掛式儲存引擎結構允許在 MySQL 伺服器執行時裝載和解除安裝一個儲存引擎，使用SHOW ENGINES語句可以檢視當前伺服器支援的儲存引擎：

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

其中，Support 列顯示了是否支援該儲存引擎以及預設的儲存引擎。MySQL 8.0 支援的儲存引擎如下：

• InnoDB：預設的儲存引擎。InnoDB 是一個事務安全（ACID 相容）的儲存引擎，支援事務的提交、回滾以及故障恢復功能。InnoDB 行級鎖（無需升級到更高粒度的鎖）以及類似 Oracle 的一致性非鎖定讀功能能夠增加多使用者併發性和效能。InnoDB 使用聚集索引儲存使用者資料，減少了常見的基於主鍵查詢時的 I/O 操作。為了維護資料完整性，InnoDB 還支援 FOREIGN KEY 參照完整性約束。
• MyISAM：這種型別的表佔用很少的磁碟空間。表級鎖限制了它的併發讀/寫效能，因此通常用於只讀或者以讀為主的 Web 應用和資料倉儲。
• Memory：將所有資料儲存在 RAM 中，用於需要快速查詢的非關鍵資料。Memory 之前被稱為 HEAP 儲存引擎。它的使用場景越來越少；InnoDB 中的 buffer pool 記憶體區提供了將大部分或全部資料保留在記憶體中的一個通用永續性方法，NDBCLUSTER 為海量分散式資料集提供了快速的鍵-值查詢。
• CSV：這種型別的表實際上就是逗號分隔符文字檔案。CSV 表可以匯入或匯出 CSV 格式的資料，用於指令碼和應用程式之間的資料交換。由於 CSV 表不支援索引，通常可以在日常操作中將資料儲存在 InnoDB 表中，只在匯入或匯出階段使用 CSV 表。
• Archive：這種壓縮格式的無索引表主要用於儲存和檢索大量的很少使用的歷史、歸檔或者安全審計資料。
• Blackhole：Blackhole 儲存引擎不會實際儲存資料，類似於 Unix 中的 /dev/null 裝置。查詢永遠不會返回資料。這種表可以用於特定的複製環境，DML 語句需要傳送到從伺服器，但是主伺服器自身不儲存資料。
• NDB （也就是 NDBCLUSTER）：這種叢集資料庫引擎主要用於對服務可用性要求極高的場景。
• Merge：支援 MySQL DBA 或者開發人員將許多結構一致的 MyISAM 表作為一個邏輯物件使用。可以用於 VLDB 環境，例如資料倉儲。
• Federated：提供訪問遠端資料庫的功能，可以將多個 MySQL 物理伺服器組合成一個邏輯資料庫。非常適合分散式環境或者資料集市。
• Example：這是 MySQL 原始碼中的一個示例儲存引擎，用於演示如何編寫新的儲存引擎。它主要面向開發者，這種型別的表無法儲存資料，也不會返回任何結果。

儲存引擎的設定不在伺服器級別，也不在資料庫（模式）級別；使用者可以為不同的表指定不同的儲存引擎。例如，一個應用程式大多數的表使用 InnoDB 儲存引擎；同時建立一個 CSV 表，用於將資料匯出到電子表格檔案中；另外建立一些 MEMORY 表作為臨時儲存區。

下表描述了 MySQL 中常見儲存引擎支援的功能特性。

功能特性	MyISAM	Memory	InnoDB	Archive	NDB
B-樹索引	✔️	✔️	✔️	❌	❌
備份/時間點恢復(1)	✔️	✔️	✔️	✔️	✔️
叢集資料庫	❌	❌	❌	❌	✔️
聚集索引	❌	❌	✔️	❌	❌
壓縮資料	✔️ (2)	❌	✔️	✔️	❌
資料快取	❌	N/A	✔️	❌	✔️
加密資料	✔️ (3)	✔️ (3)	✔️ (4)	✔️ (3)	✔️ (3)
外來鍵	❌	❌	✔️	❌	✔️ (5)
全文搜尋索引	✔️	❌	✔️ (6)	❌	❌
空間資料型別	✔️	❌	✔️	✔️	✔️
空間資料索引	✔️	❌	✔️ (7)	❌	❌
雜湊索引	❌	✔️	❌ (8)	❌	✔️
索引快取	✔️	N/A	✔️	❌	✔️
鎖的粒度	表級	表級	行級	行級	行級
MVCC	❌	❌	✔️	❌	❌
複製 (1)	✔️	有限(9)	✔️	✔️	✔️
儲存限制	256TB	RAM	64TB	無	384EB
T-樹索引	❌	❌	❌	❌	✔️
資料庫事務	❌	❌	✔️	❌	✔️
更新資料字典統計信息	✔️	✔️	✔️	✔️	✔️

註釋：

1. 在伺服器層實現，而不是儲存引擎層。
2. 只有使用行壓縮格式才支援 MyISAM 壓縮表。使用行壓縮格式的 MyISAM 表屬於只讀表。
3. 在伺服器層通過加密函式實現。
4. 在伺服器層通過加密函式實現；MySQL 5.7 開始支援表空間靜態資料（data-at-rest）加密。
5. MySQL Cluster NDB 7.3 開始支援外來鍵約束。
6. MySQL 5.6 開始支援 InnoDB 的 FULLTEXT 索引。
7. MySQL 5.7 開始支援 InnoDB 的空間資料索引。
8. InnoDB 內部使用雜湊索引實現自適應雜湊索引（Adaptive Hash Index）特性。
9. 記憶體表的複製。

下一篇我們來具體談談 InnoDB 儲存引擎的體系結構。歡迎關注❤️、點贊?、轉發?！