概述

今天主要介绍一下mysql的表空间，不过MySQL没有真正意义上的表空间管理。

MySQL的Innodb包含两种表空间文件模式，默认的共享表空间和每个表分离的独立表空间。

，当数据量很小的时候建议使用共享表空间的管理方式。数据量很大的时候建议使用独立表空间的管理方式。

01

查看表空间模式

mysql show variables like '%innodb_file_per_table%' ;

ON表示当前是独立表空间，若为OFF，则表示为共享表空间。

同样1表示ON、0表示OFF

02

查看表空间信息

select from information_schema.global_variables where variable_name in ("datadir","innodb_data_file_path","innodb_data_home_dir","innodb_file_per_table","innodb_open_files");

• datadir表示数据文件的基本路径。
• innodb_data_file_path表示共享文件的名称。
• innodb_file_per_table表示表空间的类型。
• innodb_open_files最多可打开的文件个数。
• innodb_data_file_path文件格式
• file_name:file_size:auto_extend:max_size
• file_name表示数据文件名称、file_size表示数据文件大小、auto_extend表示自动扩展、max_size表示最大大小。
• innodb_data_file_path=ibdata1:10M:autoextend:max:500M
• ibdata1初始大小为10M、自动扩展且最大为500M。

多个标示符之间使用分号隔开，，只有一个文件能够使用autoextend属性。

03

共享表空间

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

1、共享表空间的优点

表空间可以分成多个文件存放到各个磁盘，所以表也就可以分成多个文件存放在磁盘上，表的大小不受磁盘大小的限制。

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

2、共享表空间的缺点

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

2）共享表空间分配后不能回缩当出现临时建索引或是创建一个临时表的操作表空间扩大后，就是删除相关的表也没办法回缩那部分空间了，进行数据库的冷备很慢；

3、设置数据文件

有两种方式设置数据文件，一种是所有的数据文件均在一个目录下，称为同目录数据文件。另一种是所有的数据文件在不同的目录下，称为异目录数据文件。

1）设置同目录数据文件

innodb_file_per_table=0 innodb_data_home_dir=/msdata/datas innodb_data_file_path=ibdata1:50M;ibdata2:20M;ibdata3:50M:autoextend

通过innodb_data_home_dir设置一个共同的数据文件目录。

2）设置异目录数据文件

innodb_file_per_table=0 innodb_data_home_dir= innodb_data_file_path=/msdata/datas/ibdata1:76M;/msdata/datas/ibdata2:10M;/msdata/datas/ibdata3:10M;/msdata/datas01/ibdata4:10M:autoextend

注意“innodb_data_home_dir=”参数必须存在。，autoextend属性只能用于一个文件。

04

独立表空间

独立表空间是每个表都有独立的多个数据文件，而且做到了索引和数据的分离。

参数

1、独立表空间的优点

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

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

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

2、独立表空间的缺点

1）当数据库中的表数量达到一定级别时，每次操作所涉及的文件过多，如果按照默认Centos的ulimit -n = 1024的话，仅仅只能保证打开256个表以内，这在习惯上“拆库拆表”的MySQL数据结构上很难达到要求。

2）这种数据文件的利用率不算很高，当大量“不高”的文件集中起来，浪费的空间也很惊人，更何况可能出现的状况不是“一堆K级别的小文件”而是“一堆G级别的大文件”，有点适得其反的意思。

3）独立表空间数据存储规则

表结构定义文件$datadir/$dbname/$tabname.frm

数据文件$datadir/$dbname/$tabname.ibd

附独立表空间空间爆满的问题

当独立表空间的所处空间爆满的时候，只能采用数据迁移的方式，将表空间移走。具体办法是，将独立表空间更改为共享表空间。

05

释放表空间

方法一optimize table table_name

这是我们经常见到的方法，这里就不在过多介绍。

方法二alter table table_name engine=engine_name

该方法通常用于切换表的引擎，例如MyISAM转为InnoDB，同样适用于释放表空间，只不过切换后的引擎和原来的engine相同罢了。

注意

• 1.这两种方式都适用于我们常用的表，myisam和innodb。
• 2.优化表的时候会锁表，数据表越大，耗时越长，不要在网站忙时进行表优化。

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~