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

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

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

什么是通用表空間？

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

主要特征

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

使用通用表空間的好處

提高性能： 有策略地將數(shù)據(jù)文件放置在更快的磁盤上或將表分布在多個磁盤上可以顯著提高性能。
RAID 和 DRBD 集成： 數(shù)據(jù)文件可以放置在 RAID 或 DRBD 卷上，以增強數(shù)據(jù)冗余和災難恢復。
加密支持： 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 選項設置文件塊大小。

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

要將表從通用表空間轉移到獨立表空間，請指定 “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)和可用的空閑數(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)

以下查詢有助于查找有關屬于指定表空間的 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í)行速度。
關鍵數(shù)據(jù)的專用存儲：為關鍵表創(chuàng)建具有 RAID 或 DRBD 配置的獨立通用表空間，確保最大程度的冗余并防止硬件故障。

結論

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

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

您可能感興趣的文章:

MySQL內存及虛擬內存優(yōu)化設置參數(shù)
這篇文章主要介紹了MySQL內存及虛擬內存優(yōu)化設置參數(shù),需要的朋友可以參考下
2016-05-05
在centos7下安裝和部署java8和mysql
一般學習java和部署項目都是在本地部署，但是生產環(huán)境一般都是在linux環(huán)境下，部署和安裝環(huán)境都是在控制臺下進行操作的，沒有windows的可視化的操作界面，對與linux的命令掌握和操作對小白來說都是一個個挑戰(zhàn)，記錄下自己的安裝配置過程
2017-04-04
Mysql數(shù)據(jù)庫清理binlog日志命令詳解
這篇文章主要給大家介紹了Mysql數(shù)據(jù)庫清理binlog日志命令的相關資料，文中通過示例代碼介紹的非常詳細，對大家學習或者使用Mysql具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧
2019-09-09
詳解mysql 組合查詢
這篇文章主要介紹了詳解mysql 組合查詢的的相關資料，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下
2020-12-12
Mysql和SQLServer驅動連接的實現(xiàn)步驟
本文主要介紹了Mysql和SQL?Server的驅動連接，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2023-06-06
解析Mysql Profiling的使用
本篇文章是對Mysql Profiling的使用進行了詳細的分析介紹，需要的朋友參考下
2013-06-06
window環(huán)境配置Mysql 5.7.21 windowx64.zip免安裝版教程詳解
這篇文章主要介紹了window環(huán)境配置Mysql 5.7.21 windowx64.zip免安裝版教程詳解,需要的朋友可以參考下
2018-02-02
MySQL Semaphore wait has lasted使用詳解
MySQL 5.7.19 Semaphore wait >600秒錯誤源于InnoDB線程等待信號量超時,常見于死鎖、資源競爭或IO瓶頸,排查需檢查長事務、高并發(fā)寫入、磁盤性能及數(shù)據(jù)頁損壞,建議升級至5.7或8.0版本以修復問題
2025-07-07
mysql查詢樹形,id與pid關聯(lián)實踐
在MySQL中查詢基于id和pid關聯(lián)的樹形結構數(shù)據(jù),推薦使用遞歸公用表表達式（WITH RECURSIVE）以實現(xiàn)高效遞歸查詢,適用于MySQL8.0及以上版本,對于舊版本MySQL,可使用自定義函數(shù)收集所有節(jié)點或父節(jié)點ID,或采用應用層組裝策略,一次性加載數(shù)據(jù)并內存組裝樹形結構
2026-06-06
SQL?Server實現(xiàn)全文搜索查詢詳解
這篇文章介紹了SQL?Server的全文搜索功能，文中通過示例代碼介紹的非常詳細。對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下
2023-04-04