MySQL独立表空间管理_vlambda技术博客

vlambda
2020-05-31

MySQL独立表空间管理

MySQL的Innodb存储引擎包含两种表空间文件模式，默认的共享表空间和每个表分离的独立表空间，每个表都有自已独立的表空间，每个表的数据和索引都会存在自已的表空间中，可以实现单表在不同的数据库中移动。

每个表都有自已独立的表空间，每个表的数据和索引都会存在自已的表空间中，可以实现单表在不同的数据库中移动。

空间可以回收（除drop table操作处，表空不能自已回收），Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。

对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

普通建表语句

mysql> create database test;Query OK, 1 row affected (0.00 sec)
mysql> use testReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table table1(a int);Query OK, 0 rows affected (0.01 sec)
mysql> show create table table1;+--------+-------------------------------------------------------------------------------------------+| Table | Create Table |+--------+-------------------------------------------------------------------------------------------+| table1 | CREATE TABLE `table1` ( `a` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 |+--------+-------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

查看数据目录内容

•auto.cnf文件是MySQL第一次启动时生成的uuid值•ib_buffer_pool文件是MySQL启动时加载缓存数据到内存区域•ibdata1文件时存放系统表空间数据，在5.7版本及之前undo log也存放与该文件•ib_logfile0 ib_logfile1文件是MySQL的重做日志存放文件，默认两个，循环使用•ibtmp1是存放临时表数据的，比如join操作结束后就会删除临时表•mysql为MySQL用户信息与权限信息等、performance_schema为MySQL性能库、sys为MySQL系统信息库•test库为用户自行创建的库

[root@db03 ~]# ls /usr/local/mysql/dataauto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql mysql_upgrade_info performance_schema sys test

指定表空间创建表

mysql> create tablespace general add datafile 'general.ibd';Query OK, 0 rows affected (0.01 sec)
mysql> show tables;+----------------+| Tables_in_test |+----------------+| table1 |+----------------+1 row in set (0.00 sec)
mysql> create table table2(b int) tablespace=general;Query OK, 0 rows affected (0.01 sec)
mysql> show create table table2;+--------+----------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+--------+----------------------------------------------------------------------------------------------------------------------------+| table2 | CREATE TABLE `table2` ( `b` int(11) DEFAULT NULL) /*!50100 TABLESPACE `general` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 |+--------+----------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)

查看数据目录内容

•InnoDB存储引擎：frm文件是表结构元数据文件•InnoDB存储引擎：ibd文件是存储数据的文件（独立表空间模式），如果是共享表空间模式将存储在ibdata文件

[root@db03 ~]# ls /usr/local/mysql/dataauto.cnf general.ibd ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql mysql_upgrade_info performance_schema sys test
[root@db03 ~]# ls /usr/local/mysql/data/test/db.opt table1.frm table1.ibd table2.frm

指定外部表空间创建表

[root@db03 ~]# mkdir /tablespace[root@db03 ~]# chown -R mysql.mysql /tablespace

mysql> create tablespace external add datafile '/tablespace/external.ibd';Query OK, 0 rows affected (0.01 sec)
mysql> create table table3(c int) tablespace=external;Query OK, 0 rows affected (0.00 sec)
mysql> show create table table3;+--------+-----------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+--------+-----------------------------------------------------------------------------------------------------------------------------+| table3 | CREATE TABLE `table3` ( `c` int(11) DEFAULT NULL) /*!50100 TABLESPACE `external` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 |+--------+-----------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

查看数据目录内容

•isl文件是一个链接文件，里面记录表空间ibd文件存放的路径

[root@db03 ~]# ls /usr/local/mysql/dataauto.cnf external.isl general.ibd ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql mysql_upgrade_info performance_schema sys test
[root@db03 ~]# ls /usr/local/mysql/data/test/db.opt table1.frm table1.ibd table2.frm table3.frm
[root@db03 ~]# ls /tablespace/external.ibd
[root@db03 ~]# cat /usr/local/mysql/data/external.isl/tablespace/external.ibd

指定表空间目录创建表

mysql> create table table4(d int) data directory='/tablespace';Query OK, 0 rows affected (0.01 sec)
mysql> show create table table4;+--------+-------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+--------+-------------------------------------------------------------------------------------------------------------------------+| table4 | CREATE TABLE `table4` ( `d` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='/tablespace/' |+--------+-------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

查看数据目录内容

[root@db03 ~]# ls /usr/local/mysql/data/test/db.opt table1.frm table1.ibd table2.frm table3.frm table4.frm table4.isl
[root@db03 ~]# ls /tablespace/test/table4.ibd
[root@db03 ~]# cat /usr/local/mysql/data/test/table4.isl/tablespace/test/table4.ibd

使用系统表空间创建一张表

mysql> create table table5(e int) tablespace=innodb_system;Query OK, 0 rows affected (0.00 sec)
mysql> show create table table5;+--------+----------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+--------+----------------------------------------------------------------------------------------------------------------------------------+| table5 | CREATE TABLE `table5` ( `e` int(11) DEFAULT NULL) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 |+--------+----------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

查看数据目录内容

[root@db03 ~]# ls /usr/local/mysql/data/testdb.opt table1.frm table1.ibd table2.frm table3.frm table4.frm table4.isl table5.frm
[root@db03 ~]# ls /tablespace/testtable4.ibd

分别删除库和表空间并且查看原始数据

•最终效果：表空间存放路径可以自由设置，某一张大数据量的表也可以指定存放某个表空间或者某一个挂载到系统目录中的大磁盘存储。

mysql> drop database test;Query OK, 5 rows affected (0.01 sec)

[root@db03 ~]# ls /usr/local/mysql/dataauto.cnf external.isl general.ibd ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql mysql_upgrade_info performance_schema sys
[root@db03 ~]# ls /tablespace/test/
[root@db03 ~]# ls /tablespaceexternal.ibd test

mysql> drop tablespace general;Query OK, 0 rows affected (0.00 sec)
mysql> drop tablespace external;Query OK, 0 rows affected (0.00 sec)

[root@db03 ~]# ls /tablespacetest
[root@db03 ~]# ls /usr/local/mysql/dataauto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql mysql_upgrade_info performance_schema sys

[root@db03 ~]# rm -rf /tablespace

文章来源：https://dbawsp.com/1853.html

vlambda博客
学习文章列表