青岛即墨网站建设设计,中英网站的设计,wordpress 图表插件,科技木是什么材料文章目录 MySQL 8.0 InnoDB Tablespaces之General Tablespaces#xff08;通用表空间/一般表空间#xff09;General tablespaces#xff08;通用表空间/一般表空间#xff09;通用表空间的功能通用表空间的限制 创建通用表空间#xff08;一般表空间#xff09;创建语法… 文章目录 MySQL 8.0 InnoDB Tablespaces之General Tablespaces通用表空间/一般表空间General tablespaces通用表空间/一般表空间通用表空间的功能通用表空间的限制 创建通用表空间一般表空间创建语法创建通用表空间例1不指定数据文件路径例2 省略add datafile语句例3创建datadir路径外的表空间innodb_directories变量参数例创建datadir路径外的表空间修改innodb_directories变量参数创建datadir路径外的表空间 例4创建InnoDB通用表空间的限制 通用表空间中的表例1创建表指定通用表空间例2修改表的表空间例2-1修改到不同的通用表空间中例2-2修改表从独立表空间或系统表空间到通用表空间例2-3修改表从通用表空间或系统表空间到独立表空间例2-4修改表从通用表空间或独立表空间到系统表空间 参考 【免责声明】文章仅供学习交流观点代表个人与任何公司无关。 编辑|SQL和数据库技术(ID:SQLplusDB) MySQL 8.0 OCP (1Z0-908) 考点概要
MySQL 8.0 OCP (1Z0-908) 考点精析-安装与配置考点1设置系统变量 【MySQL】控制MySQL优化器行为方法之optimizer_switch系统变量 【MySQL】MySQL系统变量system variables列表mysqld --verbose --help的结果例 【MySQL】MySQL系统变量system variables列表SHOW VARIABLES 的结果例 MySQL 8.0 OCP (1Z0-908) 考点精析-备份与恢复考点1MySQL Enterprise Backup概要 MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点1sys.statement_analysis视图 MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点2系统变量的确认 MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点3EXPLAIN ANALYZE MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点4慢速查询日志slow query log MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点5表连接算法join algorithm MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点6MySQL Enterprise Monitor之Query Analyzer MySQL 8.0 OCP (1Z0-908) 考点精析-架构考点1二进制日志文件Binary log MySQL 8.0 OCP (1Z0-908) 考点精析-架构考点5数据字典(Data Dictionary) MySQL 8.0 OCP (1Z0-908) 考点精析-架构考点6InnoDB Tablespaces之系统表空间System Tablespace MySQL 8.0 InnoDB Tablespaces之File-per-table tablespaces单独表空间
MySQL 8.0 InnoDB Tablespaces之General Tablespaces通用表空间/一般表空间
InnoDB表空间是MySQL中用于存储InnoDB存储引擎表数据和索引的物理文件。 InnoDB表空间根据用途可以分成多种类型
数据表空间: System tablespace系统表空间File-per-table tablespaces单独表空间General tablespaces通用表空间/一般表空间 Undo 表空间临时表空间Temporary table tablespaces
General tablespaces通用表空间/一般表空间
通用表空间具有如下功能和限制。
通用表空间的功能
通用表空间具有如下功能
使用CREATE TABLESPACE语法创建的共享InnoDB表空间与Oracle的表空间很相似。类似于系统表空间通用表空间是共享表空间可以存储多个表的数据。 与独立表空间相比通用表空间具有潜在的内存优势。通用表空间中多个表共享一个表空间所以消耗更少的内存用于表空间元数据独立表空间需要更多内存用于表空间元数据。数据文件可以放置在与MySQL数据目录 MySQL data directory相关或独立的目录中。通用表空间支持所有的表行格式和相关功能(REDUNDANT, COMPACT, DYNAMIC, COMPRESSED)。 CREATETABLE语句可以使用TABLESPACE选项在通用表空间、独立表空间或系统表空间中创建表。 ALTERTABLE语句可以使用TABLESPACE选项在通用表空间、独立表空间或系统表空间之间移动表。创建通用表空间时候会生成相应的.ibd数据文件。 通用表空间不属于特定的数据库。
通用表空间的限制
通用表空间具有如下限制
无法将现有的表空间更改为通用表空间。不支持创建临时通用表空间。通用表空间不支持临时表。与系统表空间类似truncate 或者drop通用表空间中的表仅释放系统表空间中的空间不会返回给操作系统。表复制的ALTER TABLE操作可能会增加表空间使用的空间量。不支持表分区table partitions放置在通用表空间中源和副本位于同一主机上的复制环境中不支持使用ADD DATAFILE子句。MySQL 8.0.21开始由datadir、innodb_data_home_dir和innodb_directories变量定义的目录以外无法在撤销表空间目录innodb_undo_directory中创建通用表空间。
创建通用表空间一般表空间
创建语法
通用表空间/一般表空间创建语法如下
CREATE [UNDO] TABLESPACE tablespace_nameInnoDB and NDB:[ADD DATAFILE file_name][AUTOEXTEND_SIZE [] value]InnoDB only:[FILE_BLOCK_SIZE value][ENCRYPTION [] {Y | N}]NDB only:USE LOGFILE GROUP logfile_group[EXTENT_SIZE [] extent_size][INITIAL_SIZE [] initial_size][MAX_SIZE [] max_size][NODEGROUP [] nodegroup_id][WAIT][COMMENT [] string]InnoDB and NDB:[ENGINE [] engine_name]Reserved for future use:[ENGINE_ATTRIBUTE [] string]创建通用表空间
例1不指定数据文件路径
可以不指定数据文件路径创建通用表空间这时候数据文件会默认创建在datadir路径下。
创建表空间
mysql create tablespace test_tbs1 add datafile test_tbs1.ibd;
Query OK, 0 rows affected (0.01 sec)mysql show variables like datadir;
--------------------------------
| Variable_name | Value |
--------------------------------
| datadir | /var/lib/mysql/ |
--------------------------------
1 row in set (0.01 sec)mysql select * from- information_schema.innodb_tablespaces t- join information_schema.innodb_datafiles d- on t.SPACEd.SPACE- where t.NAME test_tbs1\G
*************************** 1. row ***************************SPACE: 81NAME: test_tbs1FLAG: 18432ROW_FORMAT: AnyPAGE_SIZE: 16384ZIP_PAGE_SIZE: 0SPACE_TYPE: GeneralFS_BLOCK_SIZE: 4096FILE_SIZE: 114688ALLOCATED_SIZE: 114688
AUTOEXTEND_SIZE: 0SERVER_VERSION: 8.0.35SPACE_VERSION: 1ENCRYPTION: NSTATE: normalSPACE: 0x3831PATH: test_tbs1.ibd
1 row in set (0.00 sec)mysql查看数据文件
rootmysql-vm:/var/lib/mysql# ls -l test_tbs*
-rw-r----- 1 mysql mysql 114688 Dec 20 20:32 test_tbs1.ibd例2 省略add datafile语句
MySQL 8.0.14版本之后创建表空间时可以省略add datafile语句。这时MySQL会隐式创建具有唯一文件名的表空间数据文件。
文件名是一个128位的UUID格式为五组用破折号分隔的十六进制数字aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee。常规表空间数据文件包括一个.ibd文件扩展名。 在replication环境中在源上创建的数据文件名与在复制副本上创建的文件名不同。
例
mysql create tablespace test_tbs2;
Query OK, 0 rows affected (0.01 sec)
mysql select * from- information_schema.innodb_tablespaces t- join information_schema.innodb_datafiles d- on t.SPACEd.SPACE- where t.NAME test_tbs2\G
*************************** 1. row ***************************SPACE: 82NAME: test_tbs2FLAG: 18432ROW_FORMAT: AnyPAGE_SIZE: 16384ZIP_PAGE_SIZE: 0SPACE_TYPE: GeneralFS_BLOCK_SIZE: 4096FILE_SIZE: 114688ALLOCATED_SIZE: 114688
AUTOEXTEND_SIZE: 0SERVER_VERSION: 8.0.35SPACE_VERSION: 1ENCRYPTION: NSTATE: normalSPACE: 0x3832PATH: af5bb7e2-9f34-11ee-8d33-525400b409f6.ibd
1 row in set (0.00 sec)mysql查看数据文件
rootmysql-vm:/var/lib/mysql# ls -l af5bb7e2-9f34-11ee-8d33-525400b409f6.ibd
-rw-r----- 1 mysql mysql 114688 Dec 20 20:38 af5bb7e2-9f34-11ee-8d33-525400b409f6.ibd
rootmysql-vm:/var/lib/mysql#例3创建datadir路径外的表空间
通用表空间数据文件可以放置在数据目录之外的位置但是路径必须是innodb_directories的值或者附加到innodb_directories值的变量innodb_data_home_dir、innodb_undo_directory和datadir之一。
innodb_directories变量参数
innodb_directories变量参数内容如下
Command-Line Format–innodb-directoriesdir_nameSystem Variableinnodb_directoriesScopeGlobalDynamicNoSET_VAR Hint AppliesNoTypeDirectory nameDefault ValueNULL
innodb_directories参数的默认值为NULL。但是innodb_data_home_dir、innodb_undo_directory和datadir定义的目录会会附加到innodb_directories参数值上。
例
mysql show variables like innodb_directories;
---------------------------
| Variable_name | Value |
---------------------------
| innodb_directories | |
---------------------------
1 row in set (0.01 sec)mysql show variables like innodb_data_home_dir;
-----------------------------
| Variable_name | Value |
-----------------------------
| innodb_data_home_dir | |
-----------------------------
1 row in set (0.00 sec)mysql show variables like innodb_undo_directory;
------------------------------
| Variable_name | Value |
------------------------------
| innodb_undo_directory | ./ |
------------------------------
1 row in set (0.01 sec)mysql show variables like datadir;
--------------------------------
| Variable_name | Value |
--------------------------------
| datadir | /var/lib/mysql/ |
--------------------------------
1 row in set (0.00 sec)mysql/var/lib/mysql-files drwx------ 12 mysql mysql 4096 Dec 24 22:10 mysql
参考 https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_directories
修改innodb_directories变量参数需要修改MySQL配置文件并重新启动MySQL服务。
例创建datadir路径外的表空间
如果在未定义路径中创建通用表空间由于不是已知目录会发生如下ERROR 3121 (HY000)错误。
mysql show variables like innodb_directories;
---------------------------
| Variable_name | Value |
---------------------------
| innodb_directories | |
---------------------------
1 row in set (0.01 sec)mysql create tablespace sqlplusdb_tb1 add datafile /var/lib/mysql-files/sqlplusdb_tb1.ibd;
ERROR 3121 (HY000): The DATAFILE location must be in a known directory.
mysql可以通过修改innodb_directories变量参数添加已知目录。
修改innodb_directories变量参数
添加如下内容innodb_directories/var/lib/mysql-files到MySQL配置文件中。 例innodb_directories/var/lib/mysql-files
rootmysql-vm:/etc/mysql# vi /etc/mysql/mysql.conf.d/mysqld.cnf
rootmysql-vm:/etc/mysql# systemctl stop mysql
rootmysql-vm:/etc/mysql# systemctl start mysql创建datadir路径外的表空间
查看变量并创建datadir路径外的表空间的表空间
mysql show variables like innodb_directories;
------------------------------------------
| Variable_name | Value |
------------------------------------------
| innodb_directories | /var/lib/mysql-files |
------------------------------------------
1 row in set (0.00 sec)mysql create tablespace test_tb3 add datafile /var/lib/mysql-files/test_tb3.ibd;
Query OK, 0 rows affected (0.02 sec)
mysql
mysql select * from- information_schema.innodb_tablespaces t- join information_schema.innodb_datafiles d- on t.SPACEd.SPACE- where t.NAME test_tb3\G
*************************** 1. row ***************************SPACE: 92NAME: test_tb3FLAG: 18432ROW_FORMAT: AnyPAGE_SIZE: 16384ZIP_PAGE_SIZE: 0SPACE_TYPE: GeneralFS_BLOCK_SIZE: 4096FILE_SIZE: 114688ALLOCATED_SIZE: 114688
AUTOEXTEND_SIZE: 0SERVER_VERSION: 8.0.35SPACE_VERSION: 1ENCRYPTION: NSTATE: normalSPACE: 0x3932PATH: /var/lib/mysql-files/test_tb3.ibd
1 row in set (0.00 sec)mysql参考 MySQL 8.0 OCP (1Z0-908) 考点精析-安装与配置考点1设置系统变量
例4创建InnoDB通用表空间的限制
可以在数据目录datadir中创建通用表空间。
mysql create tablespace sqlplusdb_tb1 add datafile /var/lib/mysql/sqlplusdb_tb1.ibd;
Query OK, 0 rows affected (0.02 sec)但是为了避免与隐式创建的每个表一个文件的表空间发生冲突不支持在数据目录datadir的子目录中创建InnoDB通用表空间。 如果在数据目录datadir的子目录中创建InnoDB通用表空间会报ERROR 3121 (HY000)错误。
例
mysql show variables like datadir;
--------------------------------
| Variable_name | Value |
--------------------------------
| datadir | /var/lib/mysql/ |
--------------------------------
1 row in set (0.00 sec)mysql create tablespace sqlplusdb_tb2 add datafile /var/lib/mysql/testdb/sqlplusdb_tb1.ibd;
ERROR 3121 (HY000): The DATAFILE location cannot be under the datadir.
mysql通用表空间中的表
和Oracle一样MySQL在创建表时可以指定表空间也可以修改表的表空间。
例1创建表指定通用表空间
mysql use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql CREATE TABLE test_t1 (c1 INT) TABLESPACE test_tbs1 ;
Query OK, 0 rows affected (0.01 sec)mysql
mysql SHOW CREATE TABLE test_t1;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| test_t1 | CREATE TABLE test_t1 (c1 int DEFAULT NULL
) /*!50100 TABLESPACE test_tbs1 */ ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.02 sec)mysql
mysql select- name as table_name,- space_type- from- information_schema.innodb_tables- where- name like %test_t1% \g
----------------------------
| table_name | space_type |
----------------------------
| testdb/test_t1 | General |
----------------------------
1 row in set (0.08 sec)
例2修改表的表空间
通过ALTER TABLE语句的TABLESPACE选项可以修改表的表空间将表在通用表空间、独立表空间或系统表空间之间进行移动。
需要注意的是
- ALTER TABLE ... TABLESPACE操作将导致对表进行全表重建即使TABLESPACE属性未发生更改也会重建。
- ALTER TABLE ... TABLESPACE语法不支持将表从临时表空间移动到持久表空间。例2-1修改到不同的通用表空间中
通过ALTER TABLE tbl_name TABLESPACE [] tablespace_name修改表到不同的通用表空间中
例
mysql SHOW CREATE TABLE test_t1;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| test_t1 | CREATE TABLE test_t1 (c1 int DEFAULT NULL
) /*!50100 TABLESPACE test_tbs1 */ ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)mysql ALTER TABLE test_t1 tablespacesqlplusdb_tb1;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql SHOW CREATE TABLE test_t1;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| test_t1 | CREATE TABLE test_t1 (c1 int DEFAULT NULL
) /*!50100 TABLESPACE sqlplusdb_tb1 */ ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)mysql例2-2修改表从独立表空间或系统表空间到通用表空间
可以通过如下命令修改表从独立表空间或系统表空间到通用表空间。
ALTER TABLE tbl_name TABLESPACE [] tablespace_name例修改表从独立表空间到通用表空间
mysql select- name as table_name,- space_type- from- information_schema.innodb_tables- where- name testdb/test_file_pertable_tablespace \g
--------------------------------------------------
| table_name | space_type |
--------------------------------------------------
| testdb/test_file_pertable_tablespace | Single |
--------------------------------------------------
1 row in set (0.00 sec)mysql alter table test_file_pertable_tablespace tablespacetest_tbs1;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql select- name as table_name,- space_type- from- information_schema.innodb_tables- where- name testdb/test_file_pertable_tablespace \g
--------------------------------------------------
| table_name | space_type |
--------------------------------------------------
| testdb/test_file_pertable_tablespace | General |
--------------------------------------------------
1 row in set (0.00 sec)mysql例修改表从系统表空间到通用表空间
mysql select- name as table_name,- space_type- from- information_schema.innodb_tables- where- name testdb/test_file_pertable \g
---------------------------------------
| table_name | space_type |
---------------------------------------
| testdb/test_file_pertable | System |
---------------------------------------
1 row in set (0.01 sec)mysql alter table test_file_pertable tablespacetest_tbs1;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql select- name as table_name,- space_type- from- information_schema.innodb_tables- where- name testdb/test_file_pertable \g
---------------------------------------
| table_name | space_type |
---------------------------------------
| testdb/test_file_pertable | General |
---------------------------------------
1 row in set (0.00 sec)mysql例2-3修改表从通用表空间或系统表空间到独立表空间
可以通过如下命令修改表从通用表空间或系统表空间到独立表空间。
ALTER TABLE tbl_name TABLESPACE [] innodb_file_per_table;例修改表从通用表空间到独立表空间
mysql select- name as table_name,- space_type- from- information_schema.innodb_tables- where- name testdb/test_file_pertable \g
---------------------------------------
| table_name | space_type |
---------------------------------------
| testdb/test_file_pertable | General |
---------------------------------------
1 row in set (0.00 sec)mysql alter table test_file_pertable tablespaceinnodb_file_per_table;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql select- name as table_name,- space_type- from- information_schema.innodb_tables- where- name testdb/test_file_pertable \g
---------------------------------------
| table_name | space_type |
---------------------------------------
| testdb/test_file_pertable | Single |
---------------------------------------
1 row in set (0.01 sec)mysql例2-4修改表从通用表空间或独立表空间到系统表空间
可以通过如下命令修改表从通用表空间或独立表空间到系统表空间。
ALTER TABLE tbl_name TABLESPACE [] innodb_system;例修改表从独立表空间到系统表空间
mysql select- name as table_name,- space_type- from- information_schema.innodb_tables- where- name testdb/test_file_pertable \g
---------------------------------------
| table_name | space_type |
---------------------------------------
| testdb/test_file_pertable | Single |
---------------------------------------
1 row in set (0.00 sec)mysql
mysql alter table test_file_pertable tablespaceinnodb_system;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql select- name as table_name,- space_type- from- information_schema.innodb_tables- where- name testdb/test_file_pertable \g
---------------------------------------
| table_name | space_type |
---------------------------------------
| testdb/test_file_pertable | System |
---------------------------------------
1 row in set (0.00 sec)
参考
15.6.3.3 General Tablespaces https://dev.mysql.com/doc/refman/8.0/en/general-tablespaces.html
13.1.21 CREATE TABLESPACE Statement https://dev.mysql.com/doc/refman/8.0/en/create-tablespace.html
Chapter 2 Server Error Message Reference https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html Error number: 3121; Symbol: ER_WRONG_FILE_NAME; SQLSTATE: HY000 Message: Incorrect File Name ‘%s’.
