1. 表空间概念

InnoDB存储引擎可将所有数据存放于ibdata的共享表空间，也可将每张表存放于独立的.ibd文件的独立表空间。共享表空间以及独立表空间都是针对数据的存储方式而言的。
MySQL表空间管理 - 图1
*MySQL从5.5版本以后出现共享表空间概念，在MySQL5.6版本中默认使用的是独立表空间。表空间的管理模式的出现是为了数据库的存储更容易扩展。

  • 共享表空间

某一个数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在data目录下。 默认的文件名为：“ibdata1” 初始化为10M。

  • 独立表空间

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

2. 共享表空间

1. 查看共享表空间

  • 物理文件查看

    1. cd /usr/local/mysql/data
    2. ll ibdata*

  • 在数据库命令行中查看

    1. SHOW VARIABLES LIKE "%file_path%";

    结果如下：

    1. +-----------------------+------------------------+
    2. | Variable_name         | Value                  |
    3. +-----------------------+------------------------+
    4. | innodb_data_file_path | ibdata1:12M:autoextend |
    5. +-----------------------+------------------------+

    2. 共享表中存储的内容

    InnoDB共享表空间文件“ibdata1”中存储了以下几部分信息：

内容 描述 备注
Data dictionary 数据字典 InnoDB 表的元数据
Double write buffer 双写缓冲区
Insert buffer 变更缓冲区
Rollback segments 回滚段 用于回滚
UNDO space 撤销日志
Foreign key constraint system tables 外键

因此，我们在初始化ibdata1时，最好设置大一些，这样就可以避免因为在高并发情景下导致ibdata1急剧增大，大大影响性能。

3. 设置共享表空间

5.7版本中默认会将undo和临时表独立出来，5.6版本也可以独立，只不过需要在初始化的时候进行配置。

  • 编辑配置文件

    1. vi /etc/my.cnf

    配置如下：

    1. [mysqld]
    2. innodb_data_file_path=ibdata1:12M;ibdata2:50M:autoextend

    修改完启动可能会报错，一般是由于ibdata1的大小设置与当前表空间大小不一致造成的。解决办法：把ibdata1的大小设置成与当前表空间一致，就可以正常启动。

    3. 独立表空间

    对于用户自主创建的表，会采用此种模式，每个表由一个独立的表空间进行管理。

    1. 查看独立表空间

  • 物理查看

    1. cd /usr/local/mysql/data/mysql
    2. ll servers.*

  • 命令行查看

    1. SHOW VARIABLES LIKE "%per_table%";

    结果如下：

    1. +-----------------------+-------+
    2. | Variable_name         | Value |
    3. +-----------------------+-------+
    4. | innodb_file_per_table | ON    |
    5. +-----------------------+-------+

    2. 设置独立表空间

  • 临时设置

    1. SET GLOBAL innodb_file_per_table=ON;

  • 永久设置

    1. vi /etc/my.cnf

    配置如下：

    1. [mysqld]
    2. innodb_file_per_table=ON

  • 验证

    1. select @@innodb_file_per_table;  -- 结果为1表示独立表空间生效（1：表示ON；0：表示OFF）

    3. 指定目录创建表

    创建表（独立表空间）时指定目录，之后表数据将存储于指定目录，而表的元信息还是存放于${datadir}（同时生成*.isl文件-连接文件）。

    1. CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/remote/directory';
    2. -- egg
    3. CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/tmp';

    4. 单表移动数据库

  • 思路1 - 复制表的.frm文件和.ibd文件到新库中。

分析：因缺少元数据，即“ibdata1”文件中记录的信息，而这个文件我们无法直接修改，顾此方法行不通。（提示：“Table ‘xx-database.xx-table’ doesn’t exist”）

  • 思路2 - 由MySQL提供的语句来操作。

步骤：

  1. 在新库中创建和目标表一样结构的表，使得ibdata1数据字典中有该表信息； ```sql use ${old-database}; show create table ${moveTableName};

— egg use eagle; show create table ke_users; CREATE TABLE ke_users ( id bigint(20) NOT NULL AUTO_INCREMENT, rtxno int(11) NOT NULL, username varchar(64) NOT NULL, password varchar(128) NOT NULL, email varchar(64) NOT NULL, realname varchar(128) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;

  2. 2. 删除新建表的*.ibd文件。
  3. ```sql
  4. alter table ${moveTableName} discard tablespace;
  5. -- egg
  6. alter table `ke_users` discard tablespace;

  1. 锁定源端数据表。

    1. lock tables ${moveTableName} read;
    2. -- egg
    3. lock tables `ke_users` read;

    注意：读写锁的区别，读锁：所有的会话只能进行SELECT语句查询，写锁：只有当前会话能增删改查，其他会话无法任何操作。

  2. 迁移.ibd文件，即：将待迁移表的.ibd文件拷贝到新建表对应表空间目录中。

    1. -- 刷新表（清除缓存）
    2. FLUSH TABLES ${moveTableName} FOR EXPORT;
    3. cp ${moveTableName}.ibd ${newDatabasePath}
    4. chown mysql:mysql ${moveTableName}.ibd
    5. # egg
    6. chown mysql:mysql ke_users.ibd

    注意：如果没有赋权将报错“Error Code:1812. Tablespace is missing for table ”。

  3. 重新加载*.ibd文件。

    1. alter table ${moveTableName} import tablespace;
    2. -- egg
    3. alter table `ke_users` import tablespace;

  4. 解锁源端数据表。

    1. unlock tables;

    5. 碎片整理

    该操作可以使表空间充分释放，但在操作过程中需要对表添加锁，需要在业务压力不大的时候操作。

    1. -- 查看文件大小
    2. # ls |grep ${tableName} |xargs -i du {} 
    3. -- 查看索引信息
    4. show index from ${tableName};
    5. -- 整理碎片
    6. optimize table ${tableName};

    4. 表空间对比

    | 表空间 | 优点 | 缺点 | | —- | —- | —- | | 共享表空间 |
    1. 可以将表空间分成多个文件存放到各个磁盘上，表空间文件大小不受表大小的限制（单表不超过64TB的限制），如：一个表可以分布在不同的文件上。
    1. 数据和文件放在一起方便管理。
    | 由于多表共用一个共享表空间，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，当某些表进行大量删除后，共享表空间存在大量碎片，但MySQL暂未提供对共享表空间收缩的方法。 | | 独立表空间 |
    1. 每个表有自己独立的表空间，数据和索引存放到其独立的表空间中。
    1. 可以实现单表在不同的数据库中移动。
    1. DROP TABLE后，表使用的空间将立即被释放。
    1. 当表中数据被大量删除后，可以使用“alter table TableName engine=innodb;”来进行收缩。
    1. 表空间存在碎片时不会影响其他表的使用，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。
    1. 对于使innodb-plugin的Innodb使用“turncate table”也会使空间收缩。
    1. 可以通过文件系统直接观察表的大小。
    1. 可以更有效的存储带BLOB，TEXT使用动态行模式的表。
    |
    1. 使用独立表空间后，无法再将表数据分散到不同的多个文件中，如果单个存储无法提供该表所需的全部空间时，无法通过增加新存储来解决。
    1. innodb_file_per_table开启后，不会影响已经使用共享表空间的表。
    1. 单表数据较大时，导致其对应的数据文件过大而不利于维护。
    1. MySQL-5.6.6或更高版本才能用，有向下兼容问题。
    1. 如果很多表都增长，会出现文件碎片问题。导致drop表和表扫描性能下降。
    1. 当drop表的时候会扫描buffer pool，如果太大会比较耗时。
    1. fsync操作必须在每个表上都运行一遍。
    |

对于单表增长过大的问题，如果使用共享表空间可以把文件分开，但有同样有一个问题，如果访问的范围过大同样会访问多个文件，一样会比较慢。对于独立表空间也有一个解决办法是：使用分区表，也可以把那个大的表空间移动到别的空间上然后做一个连接。其实从性能上出发，当一个表超过100个G有可能响应也是较慢了，对于独立表空间还容易发现问题早做处理。相比较之下，使用独占表空间的效率以及性能会更高一点。

5. 表空间管理

TODO

Mysql表空间管理的命令

MySQL表空间管理 - 图2关注赞赏支持

https://www.jianshu.com/p/d2add6609cb9

参考

博文：MySQL表空间管理
https://www.wanhebin.com/database/mysql/706.html
CSDN：MySQL独立表空间快速迁移数据
https://blog.csdn.net/weixin_45262858/article/details/105971294