作者：愛可生開源社區

在 MySQL 數據庫中有效管理存儲和性能至關重要，通用表空間為實現這一目標提供了靈活性。本文討論通用表空間并探討其功能、優點和實際用法，并附有說明性示例。

什么是通用表空間？

與默認保存系統表的單個系統表空間不同，通用表空間是用戶定義的多個 InnoDB 表的存儲容器。與默認設置相比，它們在數據組織和性能優化方面提供了靈活性。

主要特征

多表存儲：與將每個表存儲在單獨的文件中的獨立表空間不同，通用表空間可以容納大量的表，從而提高存儲效率。

靈活的位置：數據文件可以駐留在 MySQL 的 data 目錄或獨立位置，從而可以更好地控制存儲管理和性能調整。

支持所有表格式：通用表空間可容納所有 InnoDB 表格式，包括冗余、緊湊、動態和壓縮行格式，為特定需求提供靈活性。

內存優化：與每個表多個文件的表空間相比，共享表空間元數據減少了內存消耗。

使用通用表空間的好處

提高性能：有策略地將數據文件放置在更快的磁盤上或將表分布在多個磁盤上可以顯著提高性能。

RAID 和 DRBD 集成：數據文件可以放置在 RAID 或 DRBD 卷上，以增強數據冗余和災難恢復。

加密支持：MySQL 支持通用表空間加密，增強數據的安全性。

方便的表管理：通用表空間允許您將多個表分組在一起，從而更輕松地管理和組織數據庫對象。

創建和管理通用表空間

可以使用CREATE TABLESPACE語句創建通用表空間，并指定數據文件位置和引擎選項。 創建通用表空間涉及幾個簡單的步驟。下面的CREATE TABLESPACE語句使用指定的數據文件general_tablespace.ibd創建一個名為my_general_tablespace的新表空間。此外，它還使用選項ENCRYPTION='Y'啟用表空間加密，并使用FILE_BLOCK_SIZE = 16384選項設置文件塊大小。 讓我們創建一個名為my_general_tablespace的通用表空間：

mysql> CREATE TABLESPACE my_general_tablespace
   -> ADD DATAFILE 'general_tablespace.ibd'
   -> ENCRYPTION='Y'
   -> FILE_BLOCK_SIZE = 16384;
ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
mysql>


mysql> pager grep -i keyring_file;
PAGER set to 'grep -i keyring_file'

mysql> SHOW PLUGINS;
50 rows in set (0.00 sec)

mysql> INSTALL PLUGIN keyring_file SONAME 'keyring_file.so';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW PLUGINS;
| keyring_file                     | ACTIVE   | KEYRING            | keyring_file.so | GPL     |
50 rows in set (0.00 sec)

mysql> CREATE TABLESPACE my_general_tablespace
   -> ADD DATAFILE 'general_tablespace.ibd'
   -> ENCRYPTION='Y'
   -> FILE_BLOCK_SIZE = 16384;
Query OK, 0 rows affected (0.01 sec)

mysql>

現在，讓我們看看如何在數據目錄之外創建通用表空間。

root@mysql8:/var/lib# mkdir mysql_user_defined
root@mysql8:/var/lib# chown -R mysql.mysql mysql_user_defined
root@mysql8:/var/lib#

mysql> CREATE TABLESPACE user_defined_general_tablespace
    -> ADD DATAFILE '/var/lib/var/lib/mysql_user_defined/user_defined_general_tablespace.ibd'
    -> Engine=InnoDB;
ERROR 3121 (HY000): The DATAFILE location must be in a known directory.

錯誤 3121 (HY000)：數據文件位置必須位于已知目錄中。提示 MySQL 無法在指定目錄中創建表空間，因為該目錄未配置為數據文件的有效位置。 要解決此錯誤，請按照下列步驟操作：使用SHOW VARIABLES LIKE 'innodb_directories'檢查配置的目錄；如果/var/lib/mysql_user_define未列出，請繼續添加該目錄。

mysql> SHOW VARIABLES LIKE 'innodb_directories';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_directories |       |
+--------------------+-------+
1 row in set (0.00 sec)

root@mysql8:/etc/mysql/mysql.conf.d# grep -i innodb_directories mysqld.cnf
innodb_directories=/var/lib/mysql_user_defined
root@mysql8:/etc/mysql/mysql.conf.d# service mysql restart
root@mysql8:/etc/mysql/mysql.conf.d

mysql> CREATE TABLESPACE user_defined_general_tablespace
    -> ADD DATAFILE '/var/lib/mysql_user_defined/user_defined_general_tablespace.ibd'
    -> Engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

將表分配給通用表空間

創建 MySQL 通用表空間后，您可以在表創建過程中或通過更改現有表為其分配表。以下是在my_general_tablespace中創建表的示例：

mysql> CREATE TABLE my_table (
    ->     id INT PRIMARY KEY,
    ->     name VARCHAR(50)
    -> ) TABLESPACE = my_general_tablespace;
ERROR 3825 (HY000): Request to create 'unencrypted' table while using an 'encrypted' tablespace.
mysql>

mysql> CREATE TABLE my_table (
    ->     id INT PRIMARY KEY,
    ->     name VARCHAR(50)
    -> ) TABLESPACE = my_general_tablespace
    ->   ENCRYPTION='Y';
Query OK, 0 rows affected (0.02 sec)

我們創建的user_define_general_tablespace未加密，允許我們在其中創建未加密的表。

mysql> CREATE TABLE my_unencrypted_table(
    -> id INT PRIMARY KEY,
    -> name VARCHAR(50)
    -> ) TABLESPACE = user_defined_general_tablespace;
Query OK, 0 rows affected (0.01 sec)

將表遷移到通用表空間

如果您有現有表并希望將它們移動到通用表空間，則可以使用ALTER TABLE語句。例如：

mysql> show create table authorsG
*************************** 1. row ***************************
       Table: authors
Create Table: CREATE TABLE `authors` (
  `id` int DEFAULT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> ALTER TABLE authors
    -> TABLESPACE = my_general_tablespace;
ERROR 3825 (HY000): Request to create 'unencrypted' table while using an 'encrypted' tablespace.

mysql> ALTER TABLE authors ENCRYPTION='Y';
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE authors
    -> TABLESPACE = my_general_tablespace;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql>

要將表從通用表空間轉移到獨立表空間，請指定“innodb_file_per_table”作為目標表空間名稱。

mysql> ALTER TABLE authors
    -> TABLESPACE = innodb_file_per_table ENCRYPTION = 'Y';
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

監控

該查詢檢索指定 MySQL 表空間的信息，包括表空間名稱、文件名、存儲引擎、狀態和可用的空閑數據空間。

mysql> SELECT TABLESPACE_NAME, FILE_NAME, ENGINE, STATUS, DATA_FREE FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME IN ('my_general_tablespace',
'user_defined_general_tablespace')G
*************************** 1. row ***************************
TABLESPACE_NAME: my_general_tablespace
      FILE_NAME: ./general_tablespace.ibd
         ENGINE: InnoDB
         STATUS: NORMAL
      DATA_FREE: 0
*************************** 2. row ***************************
TABLESPACE_NAME: user_defined_general_tablespace
      FILE_NAME: /var/lib/mysql_user_defined/user_defined_general_tablespace.ibd
         ENGINE: InnoDB
         STATUS: NORMAL
      DATA_FREE: 0
2 rows in set (0.00 sec)

以下查詢有助于查找有關屬于指定表空間的 InnoDB 表的信息。

mysql> SELECT NAME, SPACE_TYPE, TABLESPACE_NAME from INFORMATION_SCHEMA.INNODB_TABLES JOIN INFORMATION_SCHEMA.FILES ON FILE_ID=SPACE WHERE TABLESPACE_NAME='my_general_tablespace'G
*************************** 1. row ***************************
           NAME: mytestdb/my_table
     SPACE_TYPE: General
TABLESPACE_NAME: my_general_tablespace
*************************** 2. row ***************************
           NAME: mytestdb/books
     SPACE_TYPE: General
TABLESPACE_NAME: my_general_tablespace
2 rows in set (0.01 sec)

要檢索特定 InnoDB 表的 TABLESPACE 信息，請使用以下查詢。

mysql> SELECT NAME, SPACE_TYPE, TABLESPACE_NAME from INFORMATION_SCHEMA.INNODB_TABLES JOIN INFORMATION_SCHEMA.FILES ON FILE_ID=SPACE WHERE NAME='mytestdb/my_table'G
*************************** 1. row ***************************
           NAME: mytestdb/my_table
     SPACE_TYPE: General
TABLESPACE_NAME: my_general_tablespace
1 row in set (0.00 sec)

實際使用示例：

將頻繁訪問和很少使用的表進行分離：將頻繁訪問的表放置在 SSD 上的通用表空間中，以獲得卓越的性能，同時將很少使用的表放置在基于 HDD 的通用表空間中，以優化存儲成本。

平衡 I/O 負載：將表分布在位于不同磁盤上的多個通用表空間中，以避免 I/O 瓶頸并提高查詢執行速度。

關鍵數據的專用存儲：為關鍵表創建具有 RAID 或 DRBD 配置的獨立通用表空間，確保最大程度的冗余并防止硬件故障。

結論

MySQL 通用表空間提供了強大而靈活的存儲解決方案，用于優化數據組織和性能，了解其功能并有效部署它們可以顯著改善您的數據庫管理工作。為了最大限度地發揮其優勢，請記住在實施通用表空間之前仔細考慮您的特定需求和工作負載特征。

審核編輯：湯梓紅