性能優化-理解 MySQL 體系結構（MySQL分庫分表）

實例和數據庫

我們通常所說的 MySQL 數據庫服務器由一個實例（instance）以及一個數據庫（database）組成。實例包括一組後臺進程/線程和許多內存結構，用於管理數據庫；數據庫由一組磁盤文件組成，用於存儲數據和日誌等信息。MySQL 使用典型的客戶端/服務器（Client/Server）結構，下圖顯示了一個簡單的 MySQL 體系結構：

客戶端通過通過實例中的後臺進程訪問 MySQL 數據庫。MySQL 採用單進程多線程架構，也就是說一個 MySQL 實例在操作系統中就是一個進程（mysqld）。在 Linux 系統中使用ps命令進行查看：

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

嚴格來說，一個 MySQL 實例管理的是多個數據庫（也叫模式，Schema）包括系統數據庫 mysql、information_schema、performance_schema、sys 以及用戶創建的數據庫等。使用SHOW DATABASES或者SHOW SCHEMAS命令查看當前實例中的數據庫：

<code>mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5
 rows 
in
 set (
0
.
14
 sec)/<code>

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

MySQL 物理結構

MySQL 的物理結構主要包括兩個目錄：軟件的安裝目錄和數據目錄，以及配置文件和日誌文件等。

安裝目錄

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

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

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

數據目錄

數據目錄（Data Directory）是 MySQL 存儲數據庫文件的位置，Linux 上使用 RPM 包安裝的默認位置為 /var/lib/mysql/。數據目錄中主要包含以下內容：

我們可以使用以下命令查看數據目錄：

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

配置文件

MySQL 服務器進程和各種工具程序啟動時，需要通過配置文件（my.cnf 或者 my.ini）讀取各種參數。Linux 上使用 RPM 包安裝的默認配置文件為 /etc/my.cnf，可以使用mysqld --verbose --help查看讀取配置文件的順序：

<code>[root@sqlhost ~] 
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
/<code>

服務器運行使用的所有配置選項都可以使用SHOW VARIABLES命令查看，例如以下命令可以查看 InnoDB 緩衝池相關的配置：

<code>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)
/<code>

除此之外，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 服務器：

<code>[root@sqlhost ~] 
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>/<code>

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

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

<code>
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
/<code>

以上語句通過 SQL 查詢接口提交給服務器的處理線程，解析器進行語法檢查時發現 SELECT 寫成了 SEELCT，提示的是語法錯誤。示例表來自《SQL 入門教程》示例數據庫。

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

<code>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)
/<code>

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

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

MySQL 存儲引擎

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

MySQL 插件式存儲引擎結構允許在 MySQL 服務器運行時裝載和卸載一個存儲引擎，使用SHOW ENGINES語句可以查看當前服務器支持的存儲引擎：

<code>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)
/<code>

其中，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 中常見存儲引擎支持的功能特性。

註釋：

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. 內存表的複製。

版權聲明：本文為CSDN博主「董旭陽TonyDong」的原創文章，遵循 CC 4.0 BY-SA 版權協議，轉載請附上原文出處鏈接及本聲明。

原文鏈接：https://blog.csdn.net/horses/article/details/103276723

MySQL分庫分表詳解 90帶你玩轉MySQL分庫分表