MySQL 中的共享表空间与独立表空间如何选择

我们今天就开始学习 MySQL 中另外一个非常重要的存储引擎 InnoDB 了。

要说 InnoDB，我们就不得不先来说说另外一个比较重要的话题表空间和共享表空间，这也是我们本文的主题。

对于 InnoDB 存储引擎来说，它可以将每张表存放于独立的表空间，即 tablename.ibd 文件;也可以将数据存放于 ibdata 的共享表空间，一般命名是 ibdataX，后面的 X 是一个具体的数字。

我们先来体验看看这两种有什么区别。

首先我们执行如下代码可以查看是否开启了独立表空间：

show variables like 'innodb_file_per_table'; 

可以看到，默认情况下，独立表空间是开启的。

这个时候我们来创建一个使用了 InnoDB 引擎的表，如下(默认存储引擎就是 InnoDB)：

create table book(id int,name varchar(255)); 

创建完成后，我们来到存放数据文件的目录下，可以看到如下文件：

可以看到，当前表被存放在独立的 book.ibd 文件中。

存放于独立表空间的表都将会以独立文件的方式来进行存储，每一个表都有一个 .frm 表描述文件(这个和 MyISAM 引擎一致)，还有一个 .ibd 文件，这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中。

接下来我们执行如下代码，修改 innodb_file_per_table 的值为 OFF，即关闭独立表空间，如下：

关闭之后，再来创建新表：

create table book2(id int,name varchar(255));

新表创建成功之后，此时我们再去表目录下查看文件，发现并没有一个名为 book2.ibd 的文件，说明此时的表已经存放于共享表空间了。

那么共享表空间的 ibdata1 文件又在哪里呢?其实就在 MySQL 存放数据库的目录下：

我们可以通过如下命令查看 ibdata1 的默认大小：

show variables like 'innodb_data_file_path';

可以看到，默认大小是 12M。

需要注意的是，innodb_file_per_table 要在创建表之前修改，创建表之后再去修改，是不会影响已有的表结构的。

要搞明白要用哪一种表空间，我们得先明白两种不同表空间各自的特点。

2.1 独立表空间

优势

1. 每张表都有自己独立的表空间。
2. 每张表的数据和索引都会存储在自己的表空间中。
3. 可以实现单表在不同的数据库中移动(因为每张表都有独立的数据表文件)。
4. 空间可以回收(通过 optimize table 命令实现)。
5. 无论怎么删除，表空间的碎片不会太严重影响系统性能。

缺点

1. 单表增加过大。

2.2 共享表空间

优势

1. 可以将表空间分成多个文件存放到各个磁盘上(表空间文件大小不受表大小的限制，如一个表可以分布在不同的文件上)。
2. 数据和文件放在一起方便管理。

缺点

1. 所有的数据和索引存放到一个文件中，这将意味着有一个很大的文件存在(虽然可以把一个大文件分成多个小文件)，但是多个表及索引在表空间中混合存储，这样当一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析、日志系统这类应用而言，最不适合用共享表空间(例如，当系统空间不够用的时候，我们希望通过删除一些无效数据来腾出来一些表空间，这个时候我们会发现，如果使用了共享表空间，即使无效数据删除了，表空间还是还是不会缩小)。
2. 共享表空间管理会出现表空间分配后不能回缩的问题，当临时建立索引或者临时表导致表空间扩大后，就是删除相关的表也没办法回缩那部分空间了。
3. 对于第二点存在的问题，一般使用 mysqldump 导出数据，然后删除共享表空间数据文件后，再重新导入。
4. 由于一个文件中保存了多个表数据，所以并发操作时可能会产生 IO 瓶颈，所以需要频繁写入的场景并不适合共享表空间。

经过以上的分析，相信小伙伴们已经明白了，在实际项目中，还是首选独立表空间比较好，事实上，从 MySQL5.6 开始，独立表空间就已经成为默认选项了。

最后还有一个问题，如果一开始创建的是共享表空间，那么还能迁移到独立表空间吗?当然可以!参考如下四个步骤：

1. 使用 mysqldump 导出所有数据库表数据。
2. 停止 MySQL 服务，修改 innodb_file_per_table 参数，并删除 InnoDB 相关文件(如果是主从结构，则可以从 Slave 上入手完成这些操作)。
3. 重启 MySQL 服务，重建 InnoDB 共享表空间(此时里边就没有数据了)。
4. 重新导入数据。

好啦，今天就和小伙伴们聊一聊共享表空间和独立表空间，InnoDB 的其他玩法我们后面再继续介绍～