MySQL 独立表空间与共享表空间总结

MySQL 独立表空间与共享表空间总结

本文是参考了这些文章做的梳理：
http://blog.itpub.net/69955379/viewspace-2783174/
https://www.ycbbs.vip/?p=35673

1. 独立表空间与共享表空间

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

• 如何查看是否开启了独立表空间？：

  mysql> show variables like 'innodb_file_per_table';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | innodb_file_per_table | ON    |
  +-----------------------+-------+
  1 row in set (0.01 sec)
  
  

  • ON 则代表已经开启了独立表空间,此时在 MySQL的 data-dir 目录下
    • 每个表将会以独立文件的方式来进行存储，每一个表都有一个 .frm 表描述文件（这个和 MyISAM 引擎一致），还有一个 .ibd 文件，这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中。
  • OFF 则表示关闭独立表空间，也就是使用共享表空间，此时在 MySQL的 data-dir 目录下只会存在一个 ibdata1 文件，所有表都集中存储在一个文件下。
  • 需要注意的是，innodb_file_per_table 要在创建表之前修改，创建表之后再去修改，是不会影响已有的表结构的。

1.1 独立表空间与共享表空间的优缺点

独立表空间

• 优势

  1. 每张表都有自己独立的表空间。
  2. 每张表的数据和索引都会存储在自己的表空间中。
  3. 可以实现单表在不同的数据库中移动（因为每张表都有独立的数据表文件）。
  4. 空间可以回收（通过 optimize table 等命令实现）。
  5. 无论怎么删除，表空间的碎片不会太严重影响系统性能。
  6. 不启用innodb_file_per_table，数据都会放在系统表空间中，最大64TB，如果使用innodb_file_per_table每个表可以64TB。

• 缺点

  1. 表空间中的空间只能被这个表使用
  2. fsync操作必须在每个表上都运行一遍
  3. mysqld必须保持一个打开的文件句柄，表太多会影响性能。(消耗很多 fd)

共享表空间

• 优势

  1. 可以将表空间分成多个文件存放到各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同的文件上）。

• 缺点

  1. 所有的数据和索引存放到一个文件中，这将意味着有一个很大的文件存在（虽然可以把一个大文件分成多个小文件），但是多个表及索引在表空间中混合存储，这样当一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析、日志系统这类应用而言，最不适合用共享表空间（例如，当系统空间不够用的时候，我们希望通过删除一些无效数据来腾出来一些表空间，这个时候我们会发现，如果使用了共享表空间，即使无效数据删除了，表空间还是还是不会缩小）。

  2. 共享表空间管理会出现表空间分配后不能回缩的问题，当临时建立索引或者临时表导致表空间扩大后，就是删除相关的表也没办法回缩那部分空间了。

  3. 对于第二点存在的问题，一般使用 mysqldump 导出数据，然后删除共享表空间数据文件后，再重新导入。

  4. 由于一个文件中保存了多个表数据，所以并发操作时可能会产生 IO 瓶颈，所以需要频繁写入的场景并不适合共享表空间。

在实际项目中，还是首选独立表空间比较好，事实上，从 MySQL5.6 开始，独立表空间就已经成为默认选项了。

1.2 如何转换共享表空间的数据到独立表空间

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

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