前言：学习mysql的时候总是习惯性的和oracle数据库进行比较。在学习mysql InnoDB的存储结构的时候也免不了跟oracle进行比较。Oracle的数据存储有表空间、段、区、块、数据文件；mysql InnoDB的存储管理也类似，但是mysql增加了一个共享表空间和独立表空间的概念；

一、概念

共享表空间： Innodb的所有数据保存在一个单独的表空间里面，而这个表空间可以由很多个文件组成，一个表可以跨多个文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。从Innodb的官方文档中可以看到，其表空间的最大限制为64TB，也就是说，Innodb的单表限制基本上也在64TB左右了，当然这个大小是包括这个表的所有索引等其他相关数据。

独立表空间：

二、查看数据库的表空间

mysql> show variables like 'innodb_data%';

l 表空间有四个文件组成：ibdata1、ibdata2、ibdata3、ibdata4，每个文件的大小为10M，当每个文件都满了的时候，ibdata4会自动扩展；

l 当前的存储空间满的时候，可以在其他的磁盘添加数据文件，语法如下：语法如下所示：

pathtodatafile:sizespecification;pathtodatafile:sizespec;.;pathtodatafile:sizespec[:autoextend[:max:sizespecification]]

如果用 autoextend 选项描述最后一个数据文件，当 InnoDB 用尽所有表自由空间后将会自动扩充最后一个数据文件，每次增量为 8 MB。示例：

不管是共享表空间和独立表空间，都会存在innodb_data_file文件，因为这些文件不仅仅要存放数据，而且还要充当着类似于ORACLE的UNDO表空间等一些角色。

三、共享表空间优缺点

既然Innodb有共享表空间和独立表空间两种类型，那么这两种表空间存在肯定都有时候自己的应用的场景，存在即合理。以下是摘自mysql官方的一些介绍：

3.1 共享表空间的优点

表空间可以分成多个文件存放到各个磁盘，所以表也就可以分成多个文件存放在磁盘上，表的大小不受磁盘大小的限制（很多文档描述有点问题）。

数据和文件放在一起方便管理。

3.2 共享表空间的缺点

所有的数据和索引存放到一个文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，当数据量非常大的时候，表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，对于经常删除操作的这类应用最不适合用共享表空间。

共享表空间分配后不能回缩：当出现临时建索引或是创建一个临时表的操作表空间扩大后，就是删除相关的表也没办法回缩那部分空间了（可以理解为oracle的表空间10G，但是才使用10M，但是操作系统显示mysql的表空间为10G），进行数据库的冷备很慢；

四、独立表空间的优缺点

4.1 独立表空间的优点

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

空间可以回收（除drop table操作处，表空不能自已回收）

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

对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。

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

4.2 独立表空间的缺点

单表增加过大，当单表占用空间过大时，存储空间不足，只能从操作系统层面思考解决方法；

五、共享表空间和独立表空间之间的转换

5.1 查看当前数据库的表空间管理类型

脚本：show variables like "innodb_file_per_table";

mysql> show variables like "innodb_file_per_table";

ON代表独立表空间管理，OFF代表共享表空间管理；（查看单表的表空间管理方式，需要查看每个表是否有单独的数据文件）

5.2 修改数据库的表空间管理方式

修改innodb_file_per_table的参数值即可，但是修改不能影响之前已经使用过的共享表空间和独立表空间；

innodb_file_per_table=1 为使用独占表空间

innodb_file_per_table=0 为使用共享表空间

5.3共享表空间转化为独立表空间的方法（参数innodb_file_per_table=1需要设置）
单个表的转换操作，脚本：alter table table_name engine=innodb;

当有大量的表需要操作的时候，先把数据库导出，然后删除数据再进行导入操作，该操作可以用mysqldump进行操作（http://blog.itpub.net/12679300/viewspace-1259451/）

总结：经过以上操作便完成数据库的存储空间的转换，了解技术是为了更好的利用技术，当数据量很小的时候建议使用共享表空间的管理方式。数据量很大的时候建议使用独立表空间的管理方式。

---

---

Advantages of File-Per-Table Mode

1 当truncate或者drop一个表时可以释放磁盘空间。如果不是独立表空间，truncate或drop一个表只是在ibdata文件内部释放，实际ibdata文件并不会缩小，释放出来的空间也只能让其他innodb引擎的表使用（有时这会造成磁盘空间容量监控的一个痛点，无法判断它到底有多满）

2 独立表空间下，truncate table操作会更快

3 独立表空间下，可以自定义表的存储位置，通过CREATE TABLE ... DATA DIRECTORY =absolute_path_to_directory命令实现（有时将部分热表放在不同的磁盘可有效地提升IO性能）

4 独立表空间下，可以回收表空间碎片（比如一个非常大的delete操作之后释放的空间），alter table xxx engine=innodb

5 可以移动单独的innodb表，而不是整个database

6 可以copy单独的innodb表从一个实例到另外一个实例（也就是transportable tablespace特色）

7 独立表空间模式下，可以使用Barracuda的文件格式，这个文件格式有压缩和动态行模式的特色。这个当表中有blob或者text字段的话，动态行模式（dynamic row format）可以发挥出更高效的存储。

8 独立表空间模式下，可以更好的改善故障恢复，比如更加节约时间或者增加崩溃后正常恢复的机率。

9 单独备份和恢复某张表的话会更快

10 可以使得从一个备份中单独分离出表，比如一个lvm的快照备份

11 可以在不访问MySQL的情况下方便地得知一个表的大小，即在文件系统的角度上查看。

12 在大部分的linux文件系统中，如果innodb_flush_method为O_DIRECT，通常是不允许针对同一个文件做并发写操作的。这时如果为独立表空间模式的话，应该会有较大的性能提升。

13 如果没有独立表空间模式，那么所有的东西都在共享表空间，而共享表空间是有64T的容量上限的（这点比较扯淡）

Potential Disadvantages of File-Per-Table Mode

1 独立表空间模式下，每个表或许会有很多没用到的磁盘空间，因为初始表空间大小是固定的吧，有些表根本没使用都这么多。如果没做好管理，可能会造成较大的空间浪费。

2 fsync操作必须运行在每一个单一的文件上，独立表空间模式下，多个表的写操作就无法合并为一个单一的IO，这样就添加许多额外的fsync操作

3 mysqld必须保证每个表都有一个open file，独立表空间模式下，这样就需要很多打开文件数，可能会影响性能

4 当drop一个表空间时，buffer pool会被扫描，如果buffer pool有几十G那么大，或许要花费几秒钟时间。这个扫描操作还会产生一个内部锁，可能会延迟其他操作，共享表空间模式下不会有这个问题。

5 如果许多表都增长迅速，那么可能会产生更多的分裂操作（应该指的是表空间大小的扩充），这个操作会损害drop table和table scan的性能。

6 innodb_autoextend_increment参数对独立表空间无效，这个参数指的是当系统表空间满了以后，它再次预先申请的磁盘空间大小，单位为M

 

Copying Tablespaces to Another Server(Transportable Tablespaces)

Transportable Tablespaces特性可以处理以下几个痛点

1 跑报表，单独拎出需要的几个表到新的实例上跑报表，可以保证报表产生巨大服务器开销不影响到现网服务

2 通过这个特性快速地建立一个新的slave

3 在当前库发生一个故障后备份一个该表的临时版本

4 一种比mysqldump更快的moving data的方式

5 可以将部分表移交到一个更合适的硬件环境下，比如小而操作频繁的放在SSD下，大而操作不频繁的放在HDD

Tablespace Copying Limitations and Usage Notes(Transportable Tablespaces)

1 必须是在独立表空间模式下

2 在迁移过程中，涉及到的表只读不可写

3 两边的page size必须保持一致（innodb_page_size）

4 DISCARD TABLESPACE操作不一致分区表，否则报错ERROR 1031 (HY000): Table storage engine for 'part' doesn'thave this option.

5 DISCARD TABLESPACE时，如果foreign_key_checks为1，那么不支持存在外键约束的表

6 ALTER TABLE ... IMPORT TABLESPACE操作不会去检查外键约束

7 ALTER TABLE ... IMPORT TABLESPACE并不需要.cfg的元数据文件来导入一个表空间。但是，这样的话就没有元数据检查了，同时会给出一个waring

Message: InnoDB: IO Read error: (2, No suchfile or directory) Error opening '.\
test\t.cfg', will attempt to import without schema verification
1 row in set (0.00 sec)

如果确认schema没有配错，那么不用.cfg文件可能更方便简洁，并且有利于元数据无法在.ibd文件里收集的故障恢复。

8 主从环境下，master和slave都必须设置为独立表空间模式

Example Procedure: Copying a Tablespace From One Server ToAnother (Transportable Tablespaces)

1 在源server上，创建一个测试表

mysql> use test;
mysql> CREATE TABLE t(c1 INT) engine=InnoDB;

2 在目标server上，同样创建出相同表结构的表

mysql> use test;
mysql> CREATE TABLE t(c1 INT) engine=InnoDB;

3 在目标server上，discard掉该表的表空间

mysql> ALTER TABLE t DISCARD TABLESPACE;

4 在源server上，运行FLUSH TABLES ... FOR EXPORT命令来静默这张表，并创建出.cfg元数据文件（.cfg文件创建在innodb的data目录）

mysql> use test;
mysql> FLUSH TABLES t FOR EXPORT;

5 复制源server上的.ibd和.cfg文件到目标server上

shell> scp /path/to/datadir/test/t.{ibd,cfg}destination-server:/path/to/datadir/test

6 在源server上，使用unlock tables命令来释放flush tables..for export命令上的锁

mysql> use test;
mysql> UNLOCK TABLES;

7 在目标server上，导入表空间

mysql> use test;
mysql> ALTER TABLE t IMPORT TABLESPACE;

Tablespace Copying Internals (Transportable Tablespaces)

ALTER TABLE ... DISCARD TABLESPACE做了下面这两件事

1 给table加X锁

2 分离出表空间

FLUSH TABLES ... FOR EXPORT做了以下几件事

1 给table加S锁

2 停止purge线程

3 脏页刷新到磁盘

4 表的元数据写到.cfg文件

Unlock tables做了以下几件事

1 删除.cfg文件

2 释放之前的共享锁，并重启purge线程

ALTER TABLE ... IMPORT TABLESPACE做了以下几件事

1 检查表空间内的每个页是否有损坏

2 更新每页上的space ID和LSNs

3 更新开头页的有效标志位和LSN

4 更新btree页

5 将页状态改为“脏页”，刷新到磁盘

Storing InnoDB Undo Logs in SeparateTablespaces

  5.6.3以前，innodb undo log存放在系统表空间，5.6.3以后可将undo log从系统表空间中分离出来。这样带来的好处是，可将undo放到SSD盘提升性能。这个特性主要由这几个参数实现

innodb_undo_tablespaces：undo logs创建的表空间文件数量，默认为0，表示存放在系统表空间，最大值为126

innodb_undo_directory：undo logs存放的路径，默认为.表示和其他日志文件一致

innodb_undo_logs：系统表空间中在一个事务里最大使用到的回滚段数量，默认为最大值128

分离步骤如下

1 决定undo logs的存放路径，修改innodb_undo_directory参数

2 决定一个非0的innodb_undo_logs值，可以通过压测决定到底取啥值，起始值设置小一些

3 决定一个非0的innodb_undo_tablespaces值

4 利用上述配置创建出新的mysql实例

5 压测IO负载情况

6 压测期间慢慢增大innodb_undo_logs，找到最合适的值

7 上生产环境下再测试观察

Changing the Number or Size of InnoDB LogFiles and Resizing the InnoDB Tablespace

Changing the Number or Size of InnoDB Log Files

1 SET GLOBAL innodb_fast_shutdown = 1;

2 正常关闭mysqld进程，确保正常关闭

3 备份旧的log files

4 删除旧的log files

5 修改my.cnf配置

6 重启mysqld，确保启动后log files的数量和新配置一致

5.6.8以后的版本，不再需要innodb_fast_shutdown，也不用删除旧的log files。

Increasing the Size of the InnoDB Tablespace

由innodb_data_file_path参数的autoextend参数控制就好，表空间会自动增长。默认每次增长为8M，系统表空间每次增长量可由参数innodb_autoextend_increment控制。如果要两个系统表空间文件，一个固定大小，一个自增，可以这么弄（建议一个就好，不要这么搞，有点乱，不好管理）

innodb_data_file_path =/ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

Decreasing the Size of the InnoDB Tablespace

独立表空间回收磁盘空间，只要运行alter table xx engine=innodb就行，系统表空间回收步骤如下

1 使用mysqldump dump出所有的表

2 关闭mysql

3 删除整个data目录，包括ibdata文件和log file

4 启动mysql

5 导入dump文件

---

---

Mysql Innodb的两种表空间方式