网易首页 > 网易号 > 正文 申请入驻

MySQL通用表空间的这几个选项你会用么?

0
分享至

作者:爱可生开源社区

链接:https://my.oschina.net/actiontechoss/blog/10678832

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

特别声明:以上内容(如有图片或视频亦包括在内)为自媒体平台“网易号”用户上传并发布,本平台仅提供信息存储服务。

Notice: The content above (including the pictures and videos if any) is uploaded and posted by a user of NetEase Hao, which is a social media platform and only provides information storage services.

相关推荐
热点推荐
耗资20多亿的古城日均卖票不足20张!省委书记现场步行察看

耗资20多亿的古城日均卖票不足20张!省委书记现场步行察看

政知新媒体
2024-11-14 07:09:13
敢不敢想?国足若双杀印尼巴林+平沙特赢澳洲:直通2026年世界杯

敢不敢想?国足若双杀印尼巴林+平沙特赢澳洲:直通2026年世界杯

念洲
2024-11-15 00:25:01
天津最大奥迪4S店“爆雷”闭店,多位车主保养套餐、储值金还没用完

天津最大奥迪4S店“爆雷”闭店,多位车主保养套餐、储值金还没用完

红星新闻
2024-11-14 19:39:07
故事:明年是蛇年,双春闰月加青蛇,这3种人要穿红,千万不可忽视

故事:明年是蛇年,双春闰月加青蛇,这3种人要穿红,千万不可忽视

红豆讲堂
2024-11-14 10:46:35
随着国足1-0巴林,澳大利亚0-0沙特,世预赛最新积分榜出炉:乱套

随着国足1-0巴林,澳大利亚0-0沙特,世预赛最新积分榜出炉:乱套

侃球熊弟
2024-11-15 00:04:41
这是中国手机的悲哀,也是中国电子工业的悲哀,吃渣舔碗令人伤心

这是中国手机的悲哀,也是中国电子工业的悲哀,吃渣舔碗令人伤心

小啾咪侃侃史
2024-11-14 07:05:12
幸存者讲述细节,遇害人多为暴走团成员,当天6支团队三百多人

幸存者讲述细节,遇害人多为暴走团成员,当天6支团队三百多人

辣条小剧场
2024-11-13 22:52:53
马斯克到华盛顿,皮靴配西裤昂首阔步,被特朗普调侃“没法摆脱”

马斯克到华盛顿,皮靴配西裤昂首阔步,被特朗普调侃“没法摆脱”

译言
2024-11-14 07:26:47
11月14日俄乌最新:最大的意外

11月14日俄乌最新:最大的意外

西楼饮月
2024-11-14 21:44:17
因500万港元产生分歧,50岁“天王嫂”被迫复出,女人一定要事业

因500万港元产生分歧,50岁“天王嫂”被迫复出,女人一定要事业

七阿姨爱八卦
2024-11-14 15:50:26
特朗普新任命,马斯克踏入政途,第1把火烧向台湾,中方回了2句话

特朗普新任命,马斯克踏入政途,第1把火烧向台湾,中方回了2句话

说天说地说实事
2024-11-13 16:37:23
10月新能源车销量TOP10:特斯拉中国环比骤降超4成

10月新能源车销量TOP10:特斯拉中国环比骤降超4成

财联社
2024-11-12 13:40:12
输急眼!巴林主帅与记者互骂:我们不该输 难道我哭就能赢球吗?

输急眼!巴林主帅与记者互骂:我们不该输 难道我哭就能赢球吗?

风过乡
2024-11-15 00:57:52
炸裂!昆明女孩被男友殴打满脸是血,店员帮报警,女孩称自己摔伤

炸裂!昆明女孩被男友殴打满脸是血,店员帮报警,女孩称自己摔伤

小淇言说
2024-11-14 16:43:36
1-0,张玉宁炸裂:幽灵跑位,无敌推射绝杀,现场视角,太惊艳了

1-0,张玉宁炸裂:幽灵跑位,无敌推射绝杀,现场视角,太惊艳了

侧身凌空斩
2024-11-15 00:20:44
堪比降低“印花税”!11月15日,深夜的第一大利空消息出炉!

堪比降低“印花税”!11月15日,深夜的第一大利空消息出炉!

风口招财猪
2024-11-15 02:14:54
金融战打响!A股、港股大跳水,狂飙的美元可能带崩全球股市

金融战打响!A股、港股大跳水,狂飙的美元可能带崩全球股市

看财经show
2024-11-14 17:20:07
观网快评:特朗普对华鹰派“梦之队”?警惕有人祸水东引

观网快评:特朗普对华鹰派“梦之队”?警惕有人祸水东引

观察者网
2024-11-14 20:11:17
拜登和特朗普在白宫会面2小时,他们谈了什么?

拜登和特朗普在白宫会面2小时,他们谈了什么?

第一财经资讯
2024-11-14 17:02:22
活久见!网传长沙一年轻小伙在大学浴室偷拍男生洗澡,发给女友看

活久见!网传长沙一年轻小伙在大学浴室偷拍男生洗澡,发给女友看

火山诗话
2024-11-14 15:36:19
2024-11-15 05:18:44
开源中国
开源中国
每天为开发者推送最新技术资讯
6590文章数 34154关注度
往期回顾 全部

科技要闻

官宣!极氪领克合并,吉利走向大整合

头条要闻

世预赛:国足客场1-0巴林收获两连胜 张玉宁绝杀

头条要闻

世预赛:国足客场1-0巴林收获两连胜 张玉宁绝杀

体育要闻

本季英超最炸裂的瓜,由一名裁判制造

娱乐要闻

娜扎张云龙恋情曝光!甜蜜细节被扒

财经要闻

"机构举报游资"导致A股大跌?

汽车要闻

七块屏幕四座布局 仰望U7中式百万座舱

态度原创

手机
数码
本地
家居
艺术

手机要闻

vivo S20 Pro详细参数流出:旗舰同款影像方案,配置也少有短板!

数码要闻

红魔氘锋能量块 120W 三口氮化镓套装上架,首发 139 元

本地新闻

重庆记忆|别再CityWalk了 来云端之眼CityClimb

家居要闻

现代潮流空间 轻奢现代风并重

艺术要闻

故宫珍藏的墨迹《十七帖》,比拓本更精良,这才是地道的魏晋写法

无障碍浏览 进入关怀版