mysql独立表空间与共享表空间的优劣

1.设置
innodb_file_per_table=on/off (5.6.5及之前版本默认为off,5.6.6及之后版本默认设置on);
启用该参数后,数据和索引都存储在单个独立的.ibd文件,不是在共享的系统表空间;
2.优势
在drop或者truncate table时,存储空间被回收(共享表空间只可以被重用),支持压缩（减少磁盘空间，减少io开销，缓存使用较小的内存，innodb表的数据和索引都可以被压缩，压缩数据可以被加载到buffer pool，但是不会apply到undo buffer）
3.benchmark
(1)innodb_file_per_table = off
[root@master ~]# time $(for db in {1..16};
> do mysql -S /tmp/mysqld7.sock -e "create database bench$db";
> $(for tb in {1..500}; do $(mysql -S /tmp/mysqld7.sock bench$db -e "create table tab${tb} (i int) engine=innodb"); done) & done)
real 12m0.503s
user 0m0.066s
sys 0m0.092s

[root@master ~]# time $(for db in {1..16};do mysql -S /tmp/mysqld7.sock -e "drop database bench${db}" & done)
real 10m13.608s
user 0m0.004s
sys 0m0.023s

[root@master ~]# cat /var/lib/mysql/3312/my.cnf 

[mysqld]
user = mysql
pid-file = /var/lib/mysql/mysqld7.pid
socket = /tmp/mysqld7.sock
port = 3312
datadir = /var/lib/mysql/3312
log-bin         = mysql-bin
log-error       = error.log
innodb_file_per_table = off

(2)innodb_file_per_table = on

[root@master mysql]# time $(for db in {1..16};
> do mysql -S /tmp/mysqld6.sock -e "create database bench$db";
> $(for tb in {1..500}; do $(mysql -S /tmp/mysqld6.sock bench$db -e "create table tab${tb} (i int) engine=innodb"); done) & done)
real 20m56.215s
user 0m0.027s
sys 0m0.183s

[root@master mysql]# time $(for db in {1..16};do mysql -S /tmp/mysqld6.sock -e "drop database bench${db}" & done)
real 15m25.782s
user 0m0.000s
sys 0m0.025s

[root@master mysql]# cat /var/lib/mysql/3311/my.cnf 
[mysqld]
user = mysql
pid-file = /var/lib/mysql/mysqld6.pid
socket = /tmp/mysqld6.sock
port = 3311
datadir = /var/lib/mysql/3311
log-bin         = mysql-bin
log-error       = error.log
innodb_file_per_table = on

4.结论
drop database约减少34%时间，create database及create table减少约43%。这是创建和删除空表的，如果说带有数据的话，可能情况会更为乐观。

参考资料:https://www.percona.com/blog/2015/02/24/mysqls-innodb_file_per_table-slowing/