运维首页 > 数据运维 > 技术译文 | MySQL 通用表空间的这几个选项你会用吗？

技术译文 | MySQL 通用表空间的这几个选项你会用吗？

2024年 1月 10日 63.3k 0

作者：Arunjith Aravindan

本文来源：https://www.percona.com/blog/，爱可生开源社区翻译。

本文约 1800 字，预计阅读需要 5 分钟。

本图由DELL-3 生成

在 MySQL 数据库中有效管理存储和性能至关重要，通用表空间为实现这一目标提供了灵活性。本文讨论通用表空间并探讨其功能、优点和实际用法，并附有说明性示例。

1什么是通用表空间？

与默认保存系统表的单个系统表空间不同，通用表空间是用户定义的多个 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)

2将表分配给通用表空间

创建 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)

3将表迁移到通用表空间

如果您有现有表并希望将它们移动到通用表空间，则可以使用 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

4监控

该查询检索指定 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 配置的独立通用表空间，确保最大程度的冗余并防止硬件故障。

5结论

MySQL 通用表空间提供了强大而灵活的存储解决方案，用于优化数据组织和性能，了解其功能并有效部署它们可以显着改善您的数据库管理工作。为了最大限度地发挥其优势，请记住在实施通用表空间之前仔细考虑您的特定需求和工作负载特征。

本文原文：https://www.percona.com/blog/mysql-general-tablespaces-a-powerful-storage-option-for-your-data/

本文关键字：#MySQL# #表空间# #InnoDB#

阅读推荐

技术译文 | MySQL 8.1 和 8.2 中 EXPLAIN 的新玩法

技术译文 | 年底了，可以信任 ChatGPT 来解决数据库问题了么？

技术译文 | 一份全是“错误”答案的 MySQL 面试题

技术译文 | MySQL 与主流分支版本上执行 ANALYZE TABLE 安全么？

技术译文 | MySQL 8.2 支持读写分离！

技术译文 | 一文了解 MySQL 全新版本模型

技术译文 | MySQL 和 MariaDB 版本管理的历史背景及差异

关于 SQLE

SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。

SQLE 获取

🔗 Github https://github.com/actiontech/sqle

📚 文档 https://actiontech.github.io/sqle-docs/

💻 官网 https://opensource.actionsky.com/sqle/

👥 微信群 添加管理员微信 ActionOpenSource

📊 点击 原文链接 查看社区版和企业版功能对比：https://actiontech.github.io/sqle-docs/docs/support/compare

穿过生命散发芬芳
数据运维
0 0

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF，云和恩墨技术通讯（2024年7月刊）
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论

穿过生命散发芬芳

这个人很懒，什么都没有留下～

文章8392 评论0