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_definedroot@mysql8:/var/lib# chown -r mysql.mysql mysql_user_definedroot@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.cnfinnodb_directories=/var/lib/mysql_user_definedroot@mysql8:/etc/mysql/mysql.conf.d# service mysql restartroot@mysql8:/etc/mysql/mysql.conf.dmysql> 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: authorscreate 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_ci1 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: 0mysql> alter table authors -> tablespace = my_general_tablespace;query ok, 0 rows affected (0.03 sec)records: 0 duplicates: 0 warnings: 0mysql>要将表从通用表空间转移到独立表空间，请指定 “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: 02 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: generaltablespace_name: my_general_tablespace*************************** 2. row *************************** name: mytestdb/books space_type: generaltablespace_name: my_general_tablespace2 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: generaltablespace_name: my_general_tablespace1 row in set (0.00 sec)
实际使用示例：
将频繁访问和很少使用的表进行分离：将频繁访问的表放置在 ssd 上的通用表空间中，以获得卓越的性能，同时将很少使用的表放置在基于 hdd 的通用表空间中，以优化存储成本。
平衡 i/o 负载：将表分布在位于不同磁盘上的多个通用表空间中，以避免 i/o 瓶颈并提高查询执行速度。
关键数据的专用存储：为关键表创建具有 raid 或 drbd 配置的独立通用表空间，确保最大程度的冗余并防止硬件故障。
结论
mysql 通用表空间提供了强大而灵活的存储解决方案，用于优化数据组织和性能，了解其功能并有效部署它们可以显着改善您的数据库管理工作。为了最大限度地发挥其优势，请记住在实施通用表空间之前仔细考虑您的特定需求和工作负载特征。
东芝存储产品无处不在，跟随东芝创领未来
人工智能在企业应用的反思
达实亮相2023四川电气年会，分享物联网技术在智慧建筑的创新设计
三安光电营收35.68亿元，同比增长5.31%
AZ431AR-ATRE1 产品信息
MySQL数据库中通用表空间详解
5G网络与VR技术的结合将会开启一个全新时代
索尼手机什么时候才能在中国市场复兴
降压斩波电路和升压斩波电路的电容、电感、二极管各起什么作用？
隔离电源并非越贵越好，一文教你如何选择
如何生成超声图像-B模式 如何影响前端组件选择
传感器市场即将爆发 欧姆龙积极备战应对
如何设计一个精确的温控门？
三星新款Galaxy设备或采用定制108MP相机传感器
二保焊操作规程_二保焊操作注意事项
人工智能商业落地后才能长远发展
兰和校园智能锁管理系统平台解决方案
意法半导体面向嵌入式应用而推出了STM32L4 +微控制器
高通骁龙710手机那么多你知道骁龙710到底有多强
lwm2m协议与MQTT协议有什么区别?怎么选择？哪个更适合物联网？
在 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_definedroot@mysql8:/var/lib# chown -r mysql.mysql mysql_user_definedroot@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.cnfinnodb_directories=/var/lib/mysql_user_definedroot@mysql8:/etc/mysql/mysql.conf.d# service mysql restartroot@mysql8:/etc/mysql/mysql.conf.dmysql> 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: authorscreate 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_ci1 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: 0mysql> alter table authors -> tablespace = my_general_tablespace;query ok, 0 rows affected (0.03 sec)records: 0 duplicates: 0 warnings: 0mysql>要将表从通用表空间转移到独立表空间，请指定 “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: 02 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: generaltablespace_name: my_general_tablespace*************************** 2. row *************************** name: mytestdb/books space_type: generaltablespace_name: my_general_tablespace2 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: generaltablespace_name: my_general_tablespace1 row in set (0.00 sec)
实际使用示例：
将频繁访问和很少使用的表进行分离：将频繁访问的表放置在 ssd 上的通用表空间中，以获得卓越的性能，同时将很少使用的表放置在基于 hdd 的通用表空间中，以优化存储成本。
平衡 i/o 负载：将表分布在位于不同磁盘上的多个通用表空间中，以避免 i/o 瓶颈并提高查询执行速度。
关键数据的专用存储：为关键表创建具有 raid 或 drbd 配置的独立通用表空间，确保最大程度的冗余并防止硬件故障。
结论
mysql 通用表空间提供了强大而灵活的存储解决方案，用于优化数据组织和性能，了解其功能并有效部署它们可以显着改善您的数据库管理工作。为了最大限度地发挥其优势，请记住在实施通用表空间之前仔细考虑您的特定需求和工作负载特征。
东芝存储产品无处不在，跟随东芝创领未来
人工智能在企业应用的反思
达实亮相2023四川电气年会，分享物联网技术在智慧建筑的创新设计
三安光电营收35.68亿元，同比增长5.31%
AZ431AR-ATRE1 产品信息
MySQL数据库中通用表空间详解
5G网络与VR技术的结合将会开启一个全新时代
索尼手机什么时候才能在中国市场复兴
降压斩波电路和升压斩波电路的电容、电感、二极管各起什么作用？
隔离电源并非越贵越好，一文教你如何选择
如何生成超声图像-B模式 如何影响前端组件选择
传感器市场即将爆发 欧姆龙积极备战应对
如何设计一个精确的温控门？
三星新款Galaxy设备或采用定制108MP相机传感器
二保焊操作规程_二保焊操作注意事项
人工智能商业落地后才能长远发展
兰和校园智能锁管理系统平台解决方案
意法半导体面向嵌入式应用而推出了STM32L4 +微控制器
高通骁龙710手机那么多你知道骁龙710到底有多强
lwm2m协议与MQTT协议有什么区别?怎么选择？哪个更适合物联网？