mysql管理数据表（一文看懂mysql数据库表空间管理）

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

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

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

mysql> show variables like '%innodb_file_per_table%' ;

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

同样：1表示ON、0表示OFF

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属性。

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

1、共享表空间的优点：

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

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

2、共享表空间的缺点：

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

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

3、设置数据文件：

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

1）设置同目录数据文件：

[mysqld] 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）设置异目录数据文件：

[mysqld] 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属性只能用于最后一个文件。

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

参数

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

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

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

方法一：optimize table table_name

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

方法二：alter table table_name engine=engine_name

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

注意：

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

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