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

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

什么是通用表空間？

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

主要特征

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

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

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

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

使用通用表空間的好處

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

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

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

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

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

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

錯誤 3121 (HY000)：數(shù)據(jù)文件位置必須位于已知目錄中。提示 MySQL 無法在指定目錄中創(chuàng)建表空間，因為該目錄未配置為數(shù)據(jù)文件的有效位置。 要解決此錯誤，請按照下列步驟操作：使用SHOW VARIABLES LIKE 'innodb_directories'檢查配置的目錄；如果/var/lib/mysql_user_define未列出，請繼續(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)建過程中或通過更改現(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未加密，允許我們在其中創(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)有表并希望將它們移動到通用表空間，則可以使用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>

要將表從通用表空間轉(zhuǎn)移到獨立表空間，請指定“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 表空間的信息，包括表空間名稱、文件名、存儲引擎、狀態(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 信息，請使用以下查詢。

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 的通用表空間中，以優(yōu)化存儲成本。

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

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

結(jié)論

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

審核編輯：湯梓紅