作者：愛可生開源社區(qū)

在 MySQL 數(shù)據(jù)庫(kù)中有效管理存儲(chǔ)和性能至關(guān)重要，通用表空間為實(shí)現(xiàn)這一目標(biāo)提供了靈活性。本文討論通用表空間并探討其功能、優(yōu)點(diǎn)和實(shí)際用法，并附有說(shuō)明性示例。

什么是通用表空間？

與默認(rèn)保存系統(tǒng)表的單個(gè)系統(tǒng)表空間不同，通用表空間是用戶定義的多個(gè) InnoDB 表的存儲(chǔ)容器。與默認(rèn)設(shè)置相比，它們?cè)跀?shù)據(jù)組織和性能優(yōu)化方面提供了靈活性。

主要特征

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

靈活的位置：數(shù)據(jù)文件可以駐留在 MySQL 的 data 目錄或獨(dú)立位置，從而可以更好地控制存儲(chǔ)管理和性能調(diào)整。

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

內(nèi)存優(yōu)化：與每個(gè)表多個(gè)文件的表空間相比，共享表空間元數(shù)據(jù)減少了內(nèi)存消耗。

使用通用表空間的好處

提高性能：有策略地將數(shù)據(jù)文件放置在更快的磁盤上或?qū)⒈矸植荚诙鄠€(gè)磁盤上可以顯著提高性能。

RAID 和 DRBD 集成：數(shù)據(jù)文件可以放置在 RAID 或 DRBD 卷上，以增強(qiáng)數(shù)據(jù)冗余和災(zāi)難恢復(fù)。

加密支持：MySQL 支持通用表空間加密，增強(qiáng)數(shù)據(jù)的安全性。

方便的表管理：通用表空間允許您將多個(gè)表分組在一起，從而更輕松地管理和組織數(shù)據(jù)庫(kù)對(duì)象。

創(chuàng)建和管理通用表空間

可以使用CREATE TABLESPACE語(yǔ)句創(chuàng)建通用表空間，并指定數(shù)據(jù)文件位置和引擎選項(xiàng)。 創(chuàng)建通用表空間涉及幾個(gè)簡(jiǎn)單的步驟。下面的CREATE TABLESPACE語(yǔ)句使用指定的數(shù)據(jù)文件general_tablespace.ibd創(chuàng)建一個(gè)名為my_general_tablespace的新表空間。此外，它還使用選項(xiàng)ENCRYPTION='Y'啟用表空間加密，并使用FILE_BLOCK_SIZE = 16384選項(xiàng)設(shè)置文件塊大小。 讓我們創(chuàng)建一個(gè)名為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>

現(xiàn)在，讓我們看看如何在數(shù)據(jù)目錄之外創(chuàng)建通用表空間。

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.

錯(cuò)誤 3121 (HY000)：數(shù)據(jù)文件位置必須位于已知目錄中。提示 MySQL 無(wú)法在指定目錄中創(chuàng)建表空間，因?yàn)樵撃夸浳磁渲脼閿?shù)據(jù)文件的有效位置。 要解決此錯(cuò)誤，請(qǐng)按照下列步驟操作：使用SHOW VARIABLES LIKE 'innodb_directories'檢查配置的目錄；如果/var/lib/mysql_user_define未列出，請(qǐng)繼續(xù)添加該目錄。

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)

將表分配給通用表空間

創(chuàng)建 MySQL 通用表空間后，您可以在表創(chuàng)建過(guò)程中或通過(guò)更改現(xiàn)有表為其分配表。以下是在my_general_tablespace中創(chuàng)建表的示例：

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)

我們創(chuàng)建的user_define_general_tablespace未加密，允許我們?cè)谄渲袆?chuàng)建未加密的表。

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)

將表遷移到通用表空間

如果您有現(xiàn)有表并希望將它們移動(dòng)到通用表空間，則可以使用ALTER TABLE語(yǔ)句。例如：

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>

要將表從通用表空間轉(zhuǎn)移到獨(dú)立表空間，請(qǐng)指定“innodb_file_per_table”作為目標(biāo)表空間名稱。

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

監(jiān)控

該查詢檢索指定 MySQL 表空間的信息，包括表空間名稱、文件名、存儲(chǔ)引擎、狀態(tài)和可用的空閑數(shù)據(jù)空間。

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)

以下查詢有助于查找有關(guān)屬于指定表空間的 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 信息，請(qǐng)使用以下查詢。

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)

實(shí)際使用示例：

將頻繁訪問(wèn)和很少使用的表進(jìn)行分離：將頻繁訪問(wèn)的表放置在 SSD 上的通用表空間中，以獲得卓越的性能，同時(shí)將很少使用的表放置在基于 HDD 的通用表空間中，以優(yōu)化存儲(chǔ)成本。

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

關(guān)鍵數(shù)據(jù)的專用存儲(chǔ)：為關(guān)鍵表創(chuàng)建具有 RAID 或 DRBD 配置的獨(dú)立通用表空間，確保最大程度的冗余并防止硬件故障。

結(jié)論

MySQL 通用表空間提供了強(qiáng)大而靈活的存儲(chǔ)解決方案，用于優(yōu)化數(shù)據(jù)組織和性能，了解其功能并有效部署它們可以顯著改善您的數(shù)據(jù)庫(kù)管理工作。為了最大限度地發(fā)揮其優(yōu)勢(shì)，請(qǐng)記住在實(shí)施通用表空間之前仔細(xì)考慮您的特定需求和工作負(fù)載特征。

審核編輯：湯梓紅