MySQL通用表空間的幾個選項使用指南

更新時間：2024年01月12日 09:51:42 作者：愛可生開源社區(qū)

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

什么是通用表空間？

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

主要特征

多表存儲： 與將每個表存儲在單獨的文件中的獨立表空間不同，通用表空間可以容納大量的表，從而提高存儲效率。
靈活的位置： 數據文件可以駐留在 MySQL 的 data 目錄或獨立位置，從而可以更好地控制存儲管理和性能調整。
支持所有表格式： 通用表空間可容納所有 InnoDB 表格式，包括冗余、緊湊、動態(tài)和壓縮行格式，為特定需求提供靈活性。
內存優(yōu)化： 與每個表多個文件的表空間相比，共享表空間元數據減少了內存消耗。

使用通用表空間的好處

提高性能： 有策略地將數據文件放置在更快的磁盤上或將表分布在多個磁盤上可以顯著提高性能。
RAID 和 DRBD 集成： 數據文件可以放置在 RAID 或 DRBD 卷上，以增強數據冗余和災難恢復。
加密支持： MySQL 支持通用表空間加密，增強數據的安全性。
方便的表管理： 通用表空間允許您將多個表分組在一起，從而更輕松地管理和組織數據庫對象。

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

可以使用 CREATE TABLESPACE 語句創(chuàng)建通用表空間，并指定數據文件位置和引擎選項。

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

讓我們創(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>

現在，讓我們看看如何在數據目錄之外創(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)：數據文件位置必須位于已知目錄中。提示 MySQL 無法在指定目錄中創(chuàng)建表空間，因為該目錄未配置為數據文件的有效位置。

要解決此錯誤，請按照下列步驟操作：使用 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)建過程中或通過更改現有表為其分配表。以下是在 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)

將表遷移到通用表空間

如果您有現有表并希望將它們移動到通用表空間，則可以使用 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

監(jiān)控

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

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 的通用表空間中，以優(yōu)化存儲成本。
平衡 I/O 負載：將表分布在位于不同磁盤上的多個通用表空間中，以避免I/O瓶頸并提高查詢執(zhí)行速度。
關鍵數據的專用存儲：為關鍵表創(chuàng)建具有 RAID 或 DRBD 配置的獨立通用表空間，確保最大程度的冗余并防止硬件故障。

結論

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

以上就是MySQL通用表空間的幾個選項使用指南的詳細內容，更多關于MySQL通用表空間選項的資料請關注腳本之家其它相關文章！

您可能感興趣的文章:

SQL中笛卡爾積的實際應用
笛卡爾積算法,又稱為笛卡爾積枚舉法,是一種枚舉算法,用于在兩個或多個集合之間枚舉所有可能的組合,這篇文章主要給大家介紹了關于SQL中笛卡爾積的相關資料,需要的朋友可以參考下
2023-03-03
mysql的innodb和myisam的區(qū)別及說明
這篇文章主要介紹了mysql的innodb和myisam的區(qū)別及說明,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
2024-03-03
MySQL 分表優(yōu)化試驗代碼
我們的項目中有好多不等于的情況。今天寫這篇文章簡單的分析一下怎么個優(yōu)化法。
2010-04-04
MYSQL配置參數優(yōu)化詳解
MySQL是優(yōu)化難度最大的一個部分，不但需要理解一些MySQL專業(yè)知識，同時還需要長時間的觀察統(tǒng)計并且根據經驗進行判斷，然后設置合理的參數。下面我們了解一下MySQL優(yōu)化的一些基礎
2018-07-07
淺談MYSQL主鍵約束和唯一約束的區(qū)別
本文主要介紹了MYSQL主鍵約束和唯一約束的區(qū)別，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下
2021-09-09
mysql一次將多條不同sql查詢結果并封裝到一個結果集的實現方法
這篇文章主要給大家介紹了關于mysql一次將多條不同sql查詢結果并封裝到一個結果集的實現方法,文中通過實例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下
2023-03-03
MSSQL產生死鎖的根本原因及解決方法
死鎖是指兩個或兩個以上的進程在執(zhí)行過程中,因爭奪資源而造成的一種互相等待的現象,若無外力作用,它們都將無法推進下去.此時稱系統(tǒng)處于死鎖狀態(tài)或系統(tǒng)產生了死鎖,這些永遠在互相等的進程稱為死鎖進程
2016-04-04
mysql實現向某個字段前或后添加字符
這篇文章主要介紹了mysql實現向某個字段前或后添加字符，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
2022-09-09
MySQL查詢優(yōu)化必備知識點總結
這篇文章主要給大家介紹了關于MySQL查詢優(yōu)化必備知識點的相關資料，文中介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2021-03-03
JDBC數據庫的使用操作總結
本篇文章介紹了，JDBC數據庫的使用操作方法的一些總結。需要的朋友可以參考一下
2013-05-05