mysql表空间

表空间是一种逻辑结构，它位于表和数据文件之间

1.  System   Tablespace

SYSTEM表空间包含的内容： InnoDB数据字典，Change Buffer，DoublewriteBuffer， Undo Log，如果用户在该表空间中创建表和索引，那么数据也位于表空间中，表空间对应一个或多个数据文件，位于datadir中，默认情况下，其中一个文件为ibdata1

参数选项一： innodb_data_home_dir 

指定表空间存放位置，默认值为空，默认建立在数据库初始化目录。                                                                                                                              自定义表空间可以在[mysqld]下设置：innodb_data_home_dir = /your_path                  (=号两边需要有空格)                                                                  也可以指定绝对路径innodb_data_file_path                      innodb_data_file_path=/path/ibdata1:12M;ibdata2:/path/ibdata2:50M:autoextend[:max:maxfilesize]                                                                                自动扩展autoextend默认每次自动扩展64M,  想指定其扩展大小参数为innodb_autoextend_increment=128；

参数选项二：innodb_data_file_path

 控制表空间大小及文件个数。其默认值为 ibdata1:12M:autoextend                                                                                                                                  根据innodb_page_size按最大值指定的，为了确保其空间可以容纳doublewrite buffer pages，                                                                  innodb_page_size为16K时(默认值) innodb_data_file_path最小值为3M                                                                                                                   innodb_page_size为32K时                innodb_data_file_path最小值为6M                                                                                                                 innodb_page_size为64K时                innodb_data_file_path最小值为12M

*    当表空间文件空间满了之后，可以自动拓展或者指定新文件空间

方法一：最后一个数据文件（仅当最后一个）可以自动扩展，即innodb_data_file_path 设置   /ibdata/ibdata1:12M;/disk2/ibdata2:50M:autoextend:max:500M，动态变量innodb_autoextend_increment用于指定表空间扩展的基本单位，默认为64MB。

方法二：新建文件空间创建表空间

停止mysql客户端，vim打开并修改my.cnf的innodb_data_file_path参数，在:autoextend[:max:maxfilesize]前面添加新的文件系统的绝对路径，注意路径的读写权限，同时统计文件大小，旧数据文件50M修改为实际大小，大小为1M的整数倍，如统计大小不足100M，就写100M即可，写上新的文件系统的绝对路径，后面添加:autoextend[:max:maxfilesize]，注意新建路径及相应权限

[mysqld]                                                                                                                                                                                                                              innodb_data_home_dir =                                                                                                                                                                  innodb_data_file_path=/path of  your old file/ibdata1:100M;/path of your  new data file/ibdata2:50M:autoextend[:max:maxfilesize],最后重启mysql

2. File-Per-Table   Tablespace

默认为MySQL初始化路径下对应数据库名称文件夹下，在这种表空间中，表、表空间、数据文件之间存在意义对应关系，每个表空间对应一个.ibd文件，位于datadir目录下，与数据库名称相同的子目录中。如果不能使用这种表空间，表将位于SYSTEM表空间中。每个表对应一个表空间，每个表空间对应一个.ibd文件，表及索引中的数据存在这个.ibd文件中，当删除表或者truncate时，表所占用的空间将释放，可以把特定的放在特定的设备上，便于I/O优化，空间管理，备份等，方便的移动单个表，表可以方便地在不同服务器之间迁移。

参数选项一：

innodb_file_per_table    是否开启单表表空间，可动动态修改，默认为ON，如果设置为OFF，在没有显式指定表空间情况下，数据及索引默认存放到系统表空间ibdata下

表空间的使用：

动态变量innodb_file_per_table用于enable/disable这种表空间。例如：                                                                                                                              mysql>SET GLOBAL innodb_file_per_table=1;                                                                                                                                                              mysq> create table …                                                                                                                                                                                                                    如果这种表空间被disable，用户创建的表默认位于SYSTEM表空间

将表从SYSTEM表空间移动到File-Per-Table表空间：                                                                                                                                            mysql>SET GLOBAL innodb_file_per_table=1;                                                                                                                                                              mysql>ALTER  TABLE table_nameENGINE=InnoDB

把表从File-Per-Table表空间移动到SYSTEM表空间：                                                                                                                                                             mysql>alter table t1 tablespace innodb_system;

在datadir之外创建File-Per-Table表空间：                                                                                                                                                                                   mysql> create table t2(id int)engine=innodb data directory='/mysql';                                                                                                                                  或                                                                                                                                                                                                                                           mysql> create table t3(id int) tablespace=innodb_file_per_table data directory='/mysql';

以上确保操作系统用户mysql对目录具有读写权限

3.General    Tablespace

存放用户创建的表数据及索引的共享表空间，即多表数据存放在同一表空间内是一种共享的表空间，不同数据库中的各个表，都可以位于这种表空间中，数据文件可以位于datadir之外，位于手工创建路径下。                                                          

 表空间的创建：

create tablespace ts12  add datafile 'ts12.ibd' file_block_size=16K engine=innodb;                                                                                                        文件的扩展名必须是.ibd。文件默认位于datadir目录，需要指定engine=innodb，否则需要设置变量；                                                              mysql> set global default_storage_engine=innodb;

4.  redo log文件

 存放Innodb存储引擎的事务日志，在数据库进行数据恢复时保证数据完整性，默认2个，各48MB，位于datadir目录下

参数选项，编辑my.cnf文件：

innodb_log_group_home_dir 指定文件所在路径，相对或绝对。如果不指定，则为datadir路径

innodb_log_files_in_group 指定文件的数量，默认为2

innodb_log_file_size 指定每个文件的大小

如果希望修改redolog文件的存储路径、数量或者大小，修改上述变量的值，重新启动mysqld，文件便会在指定的目录下产生

5. undo log Tablespace

用来存放Innodb存储引擎的事务日志，与系统表空间存放于同一文件中数据库默认路径下ibdata1中。与事务有关的UNDO段位于SYSTEM空间中，可以单独创建一个UNDO表空间，将UNDO段放在UNDO表空间中，对于已经存在的数据库服务器来说，UNDO的管理方式不可改变。

参数选项，在对数据库服务器进行初始化时，指定以下变量：

–innodb_undo_directory：指定undo表空间的存储路径，该参数只能在mysql初始化时指定

–innodb_undo_log_truncate:支持动态修改，默认值OFF，使用innodb_undo_log_truncate时，至少需要保证 innodb_undo_tablespaces参数为2，保证truncate其中之一时，另一个可以继续工作。

–innodb_undo_tablespaces:指定UNDO表空间的数量，全局参数不支持动态修改，最大值95，默认值0

–innodb_max_undo_log_size:支持动态修改，默认值1G，配合innodb_undo_log_truncate参数使用，在undolog文件达到该限值时，会标记为截断，不会分配给其他事务，允许当前事务完成其操作后，回滚段不再被使用时，mysql将执行truncate，将文件大小恢复到初始化的大小(10MiB)

6. Temporary  Tablespace

•由临时表组成，用于存储临时数据,对应文件ibtmp1，位于datadir目录中,当数据服务器正常关闭时，该表空间被删除，下次重新产生

相关参数

•变量innodb_data_home_dir用于指定数据文件所在路径，如果为空，则表示datadir目录

•变量innodb_temp_data_file_path表示数据文件的名称，大小，是否自动扩展。相对路径或绝对历经。如：

innodb_temp_data_file_path=ibtmp1:12M:autoextend

#  使用示例

ile-per-table表空间的创建：

  1、设置变量innodb_file_per_table=1  或者on

    create table t10(...);

  2、   create table t2(id integer)    tablespace innodb_file_per_table;

  3、创建表，并且指定数据文件的存储路径：

  create table t3(id integer)   tablespace innodb_file_per_table                                        data directory='/var/lib/mysql-files';

把表移动到system表空间中：

alter table t2 tablespace innodb_system;

把表从SYSTEM表空间移出，转变为file_per_table：

alter table t2 tablespace innodb_file_per_table;

general表空间的用法：

创建： create tablespace ts1 add datafile 'ts1.ibd' engine=innodb;

在表空间上创建表：

create table t4(id integer)  tablespace ts1;