默认情况下,innodb表使用共享表空间存储数据和索引。mysql同时提供了另一种选择,将每个表的数据和索引放在单独的文件中,这就是“独占表空间”或者说“多表空间”,设置了之后,每个表有单独的表空间。
一、独占表空间的优点:
①、可以在不影响其他innodb表的情况下对单个表进行备份和恢复。
②、可以将特定的表放在特定的磁盘上,利于优化IO和备份。
③、使用compressed_row_format行记录模式压缩数据。
④、truncate table会回收表空间,这是对比共享表空间很大的优势。
⑤、监控表的大小,不用进入mysql查看table status,只需要在数据目录中查看文件大小就可以了。
二、开启/关闭独占表空间
在my.cnf中设置
- [mysqld]
- innodb_file_per_table
如果关闭innodb_file_per_table,INNODB又会将新表的数据和索引放在共享的文件中。
可以将数据从共享表空间转移到独占表空间或者反过来,而不用管my.cnf中的配置,用下面的方法实现:
- -- Move table from system tablespace to its own tablespace.
- SET GLOBAL innodb_file_per_table=1;
- ALTER TABLE table_name ENGINE=InnoDB;
- -- Move table from its own tablespace to system tablespace.
- SET GLOBAL innodb_file_per_table=0;
- ALTER TABLE table_name ENGINE=InnoDB;
你可以像移动MYSIAM文件一样将.ibd文件在数据库目录之间随意移动,因为表定义记录在共享表空间中,包括数据库的名字。不同的数据库之间,保存在各自表空间中的事物ID和日志序列号也都是不一样的。使用RENAME更改表定义:
- RENAME TABLE db1.tbl_name TO db2.tbl_name;
表空间回收:
独占表空间在删除数据的时候也是不会回收磁盘空间的。使用optimize table 或者alter table table_name engine=innodb可以回收磁盘空间,因为会建立新文件,删除旧文件,数据就紧凑了。