浅谈共享表空间与独立表空间

共享表空间与独立表空间

共享表空间,又称系统表空间，在数据目录中，存储多张表的索引和数据文件，以ibdata1,2，3的形式，可以跨多个数据库使用

独立表空间：既可以在数据目录，也可以独立于数据目录之外，存储单张表的索引和数据文件，以ibd形式，不可以跨库

区别

空间回收：共享表空间内的表数据进行删除，由于碎片化，是无法进行回收的，即数据文件无法自动收缩；独立表空间，删除表数据后可以回收

并发：共享表空间内由于多个表可能存储在同一个数据文件中，在并发比较大的场景下，磁盘对该文件的io会有瓶颈； 独立表空间的优势较明显。

迁移：共享表空间无法进行单表迁移，独立表空间可以复制到另一实例中

如何调整表空间大小

通常默认表空间为12M，可以通过innodb_data_file_path来调整

show variables like "innodb_data_file_path";

在/etc/my.cnf 中设置innodb_data_file_path来调整系统表空间大小，在多个数据文件中autoextend仅能使用一次

systemctl stop mysqld

mkdir mysql_tablespace

chown -R mysql:mysql mysql_tablespace

chmod 750 mysql_tablespace

systemctl start mysqld

再次查询，

如何创建独立表空间

独立表空间可以通过innodb_file_per_table设置，通常是默认打开的，如果未设置路径，那.ibd存放于数据目录中，如果磁盘满了可以考虑挂载到新路径下

1.

create table test( tt_id int unsigned auto_increment primary key, tt_name

varchar(10), tt_age int unsigned not null );

可以在数据目录下所属数据库查找到数据文件

2.

create table test_02( tt_id int unsigned auto_increment primary key, tt_name varchar(10), tt_age int unsigned not null ) tablespace = innodb_file_per_table data directory = '/var/lib/mysql_tablespace';

可以在新目录下查找到数据文件

如何将独立表空间迁移到新实例

1. 在目标库上，创建相同结构的表，并discard表空间（依次按顺序操作）

CREATE TABLE `test` ( `tt_id` int unsigned NOT NULL AUTO_INCREMENT, `tt_name` varchar(10) DEFAULT NULL, `tt_age` int unsigned NOT NULL, PRIMARY KEY (`tt_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

alter table test discard tablespace;

2.在源库上锁表导出,复制ibd文件到目标实例路径，最后unlock tables

flush tables test for export;

scp test.ibd root@192.168.1.129:/var/lib/mysql/p01

unlock tables;

3.在目标库上对ibd文件进行授权

chown -R mysql:mysql test.ibd

4.导入表空间并验证

alter table test import tablespace;

select * from test;