MySQL 通用表空间的这几个选项你会用么?
在 MySQL 数据库中有效管理存储和性能至关重要,通用表空间为实现这一目标提供了灵活性。本文讨论通用表空间并探讨其功能、优点和实际用法,并附有说明性示例。
什么是通用表空间?
与默认保存系统表的单个系统表空间不同,通用表空间是用户定义的多个 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)
将表分配给通用表空间
创建 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)
将表迁移到通用表空间
如果您有现有表并希望将它们移动到通用表空间,则可以使用 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
监控
该查询检索指定 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 配置的独立通用表空间,确保最大程度的冗余并防止硬件故障。
结论
MySQL 通用表空间提供了强大而灵活的存储解决方案,用于优化数据组织和性能,了解其功能并有效部署它们可以显着改善您的数据库管理工作。为了最大限度地发挥其优势,请记住在实施通用表空间之前仔细考虑您的特定需求和工作负载特征。
更多技术文章,请访问:https://opensource.actionsky.com/
关于 SQLE
SQLE 是一款全方位的 SQL 质量管理平台,覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库,为开发和运维提供流程自动化能力,提升上线效率,提高数据质量。
SQLE 获取

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
-
上一篇
权威整理!2023年度数据库回顾:Databases in 2023: A Year in Review
StoneDB开源地址 https://github.com/stoneatom/stonedb 作者:Andy Pavlo 编辑:宇亭 今年和去年一样,我以服用抗生素的方式开始新的一年,因为我的亲生女儿从卡内基梅隆大学的幼儿园带回来一个讨厌的鼻窦问题。这发生在我第一任妻子背叛我并给我带来COVID之后。尽管如此,去年数据库领域发生了很多重大事件,是时候写一篇年度评论了。 我的目标是保持我的尖锐观点公正,避免哗众取宠的标题。如果你对这个游戏不熟悉,或者对我的无拘无束的机智不够了解,你也可以查看我其他针对2022年和2021年的年终数据库评论。 RiseofVectorDatabases 向量数据库的崛起 这无疑是向量数据库的一年。虽然其中的一些系统已经存在了几年,但对LLM和建立在它们之上的服务(例如ChatGPT)的广泛兴趣在去年把它们推向了最前沿。向量数据库承诺基于数据的语义而不是仅基于其内容来提供对数据的更深入的搜索。也就是说,应用程序可以搜索与主题相关的文档(例如“有关摆摊的歌曲的嘻哈团体”),而不是搜索包含确切关键词的文档(例如“武当派”)。 实现这些类型搜索的“魔法”是转换...
-
下一篇
GaussDB(for MySQL)新特性TDE发布:支持透明数据加密
本文分享自华为云社区《GaussDB(for MySQL)新特性TDE发布:支持透明数据加密》,作者: GaussDB 数据库。 技术背景 为了保护数据的安全,我们可能通过防火墙、身份认证、安全权限控制、网络及端口访问控制、传输加密等多种方式对程序运行过程中的各个环节进行安全防控,但这些并不能保证数据静态存储在物理介质中的安全;恶意方如果窃取了硬盘等物理介质,就可能还原并获取其中存储的数据。 透明数据加密(Transparent Data Encryption,简称TDE),作为一种在数据“静止”时保护数据的机制,对数据文件执行实时I/O加密和解密;数据在写入磁盘之前进行加密,从磁盘读入内存时进行解密,这样数据在数据库共享内存中以明文形态存在,而在数据存储介质中则以密文形态存在,能有效保护数据静态存储的安全。 特性价值 GaussDB(for MySQL)新特性TDE核心价值在于数据的静态安全保护,同时有助于解决用户安全相关的法规遵从性挑战: (1)数据保护:支持AES256、SM4两种加密算法,对数据进行加密,保护数据安全,有助于解决用户满足法规遵从性的需求。 (2)透明访问:数据库...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- Docker快速安装Oracle11G,搭建oracle11g学习环境
- MySQL数据库在高并发下的优化方案
- CentOS7,CentOS8安装Elasticsearch6.8.6
- Linux系统CentOS6、CentOS7手动修改IP地址
- 2048小游戏-低调大师作品
- SpringBoot2整合MyBatis,连接MySql数据库做增删改查操作
- SpringBoot2编写第一个Controller,响应你的http请求并返回结果
- Dcoker安装(在线仓库),最新的服务器搭配容器使用
- Docker使用Oracle官方镜像安装(12C,18C,19C)
- MySQL8.0.19开启GTID主从同步CentOS8