0、功能介绍
1
2
3
4
5
6
| 1、类似于系统表空间，一般表空间也是共享表空间，可以存储多个表的数据
2、一般表空间相比于单表文件表空间有潜在的内存优势。在表空间的生命周期里，服务端可以在内存中保持表空间的元数据。多个表在较少的一般表空间内消耗的内存相比于相同数量的表在单表文件表空间所占用的内存要少的多。
3、一般表空间的存放目录可以是放在mysql的数据目录，也可以单独存放于其他目录。将数据文件放在mysql数据目录以外，这样就可以自己管理单独管理表的性能，对特定的表设置raid、drbd，或者使用特殊的磁盘。
4、一般表空间支持antelope和barracuda文件格式，因此支持所有的表行格式和相关特性。因为支持上面两种文件格式，因此一般表空间不依赖与innodb_file_format和innodb_file_per_table的设置，这些参数的设置对于一般表空间没有影响。
5、create tables语句时可以使用tablespace参数指定放到一遍表空间、单表文件表空间还是系统表空间
6、使用alter table移动表时，可以添加tablespace参数用于将表在一般表空间、单表文件表空间、系统表空间之间移动。
|
1、创建一般表空间
1.1、创建表空间语法
1
| CREATE TABLESPACE tablespace_name ADD DATAFILE 'file_name' [FILE_BLOCK_SIZE = value] [ENGINE [=] engine_name]
|
1.2、创建在mysql数据目录下的一般表空间
1
2
| root@db 22:19: [information_schema]> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
Query OK, 0 rows affected (0.28 sec)
|
查看创建成功后的表空间信息：
1
2
3
4
5
6
7
| root@db 22:19: [information_schema]> select * from innodb_sys_tablespaces where name='ts1';
+-------+------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| 678 | ts1 | 2048 | Any | Any | 16384 | 0 | General | 4096 | 65536 | 32768 |
+-------+------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
1 row in set (0.00 sec)
|
删除创建的表空间：
1
2
| root@db 22:19: [information_schema]> drop tablespace ts1;
Query OK, 0 rows affected (0.20 sec)
|
1.3、创建在数据目录以外的一般表空间(切记如果是主从或者是mgr集群模式，其他节点也要执行创建目录操作，否则会导致事务异常，集群崩溃)
1
2
3
4
5
6
7
8
9
10
| shell> mkdir -p /tmp/test
shell> chown mysql.mysql /tmp/test
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '/tmp/test/ts1.ibd' Engine=InnoDB;
mysql> select * from innodb_sys_tablespaces where name='ts1';
+-------+------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| 680 | ts1 | 2048 | Any | Any | 16384 | 0 | General | 4096 | 65536 | 32768 |
+-------+------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
1 row in set (0.01 sec)
|
创建完成之后会在mysql数据目录下生成一个.isl的文件
1
2
| [root@dax-mysql-master data]# ll /data/mysql/data/ts1*
-rw-r----- 1 mysql mysql 17 2月 10 22:32 /data/mysql/data/ts1.isl
|
在创建的外部数据目录下生成了创建的ibd文件
1
2
| [root@dax-mysql-master data]# ll /tmp/test/ts1*
-rw-r----- 1 mysql mysql 65536 2月 10 22:32 /tmp/test/ts1.ibd
|
1.4、使用create tablespace创建一般表空间时，如果默认参数(default_storage_engine=InnoDB)没有被指定，一定要手动在语句末尾加上ENGINE = InnoDB 子句。
2、增加表到一般表空间
2.1、使用create table
1
| CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;
|
查看表所属的表空间
1
2
3
4
5
6
7
8
9
| root@db 23:08: [test]> select a.table_id,a.name,a.space,b.name from information_schema.innodb_sys_tables a,information_schema.innodb_sys_tablespaces b where a.space=b.space and a.name='test/t1';
+----------+---------+-------+------+
| table_id | name | space | name |
+----------+---------+-------+------+
| 970 | test/t1 | 680 | ts1 |
+----------+---------+-------+------+
1 row in set (0.01 sec)
root@db 23:06: [test]> CREATE TABLE test.t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;
Query OK, 0 rows affected (0.55 sec)
|
2.2、使用alter table移动表到一般表空间
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| root@db 23:11: [test]> select a.table_id,a.name,a.space,b.name from information_schema.innodb_sys_tables a,information_schema.innodb_sys_tablespaces b where a.space=b.space and a.name='aaaa/test';
+----------+-----------+-------+-----------+
| table_id | name | space | name |
+----------+-----------+-------+-----------+
| 761 | aaaa/test | 476 | aaaa/test |
+----------+-----------+-------+-----------+
1 row in set (0.00 sec)
root@db 23:11: [test]> alter table aaaa.test tablespace ts1;
Query OK, 0 rows affected (0.92 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@db 23:12: [test]> select a.table_id,a.name,a.space,b.name from information_schema.innodb_sys_tables a,information_schema.innodb_sys_tablespaces b where a.space=b.space and a.name='aaaa/test';
+----------+-----------+-------+------+
| table_id | name | space | name |
+----------+-----------+-------+------+
| 971 | aaaa/test | 680 | ts1 |
+----------+-----------+-------+------+
1 row in set (0.01 sec)
|
修改完成之后，在aaaa的数据目录下的test.ibd文件就不存在了，只剩下了test.frm文件
3、关于压缩表和general tablespace的限制
对于包含压缩表的general tablespace，在创建的时候必须指定FILE_BLOCK_SIZE参数，该值必须是合理的压缩页大小与innodb_page_size的值相关联，同时创建压缩表是的key_block_size必须等于FILE_BLOCK_SIZ/1024。例如，如果innodb_page_size=16k、file_block_size=8k，key_block_size的值必须是8.
关于innodb_page_size、file_block_size、key_block_size的大小的设置，官方文档有详解介绍，点击此处查看详情
下面举例创建压缩表
1
2
3
4
5
6
7
8
| mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;(在这里执行的时候，没有注意主从，在从库执行的时候也成功了，并且同步到了主库上)
mysql> CREATE TABLE test.t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
root@db 00:01: [test]> select a.table_id,a.name,a.row_format,a.space,b.name from information_schema.innodb_sys_tables a,information_schema.innodb_sys_tablespaces b where a.space=b.space and a.name='test/t4';
+----------+---------+------------+-------+------+
| table_id | name | row_format | space | name |
+----------+---------+------------+-------+------+
| 973 | test/t4 | Compressed | 681 | ts2 |
+----------+---------+------------+-------+------+
|
如果创建的general tablespace没有指定file_block_size，会使用默认的innodb_page_size，但是当这两个值相等时，该表空间将仅支持（COMPACT, REDUNDANT, and DYNAMIC row formats），不支持uncompressed 格式。
4、移动表
4.1、从单表文件表空间、系统表空间移动到general tablespace
1
| ALTER TABLE tbl_name TABLESPACE [=] tablespace_name;
|
参考2.2章节
4.2、从单表文件表空间、一般表空间移动到系统表空间
ALTER TABLE tbl_name TABLESPACE [=] innodb_system;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| root@db 01:09: [test]> select a.table_id,a.name,a.space,b.name from information_schema.innodb_sys_tables a,information_schema.innodb_sys_tablespaces b where a.space=b.space and a.name='aaaa/test';
+----------+-----------+-------+------+
| table_id | name | space | name |
+----------+-----------+-------+------+
| 971 | aaaa/test | 680 | ts1 |
+----------+-----------+-------+------+
1 row in set (0.01 sec)
root@db 01:09: [test]> alter table aaaa.test tablespace = innodb_system;
Query OK, 0 rows affected (0.78 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@db 01:34: [aaaa]> select * from information_schema.innodb_sys_tables where name='aaaa/test';
+----------+-----------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+-----------+------+--------+-------+-------------+------------+---------------+------------+
| 976 | aaaa/test | 161 | 4 | 0 | Barracuda | Dynamic | 0 | System |
+----------+-----------+------+--------+-------+-------------+------------+---------------+------------+
1 row in set (0.00 sec)
|
4.3、从系统表空间或者general table移动到单表文件表空间
1
| ALTER TABLE tbl_name TABLESPACE [=] innodb_file_per_table;
|
1
2
3
4
5
6
7
8
9
10
| root@db 01:36: [aaaa]> ALTER TABLE aaaa.test TABLESPACE = innodb_file_per_table;
Query OK, 0 rows affected (0.80 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@db 01:36: [aaaa]> select a.table_id,a.name,a.space,b.name from information_schema.innodb_sys_tables a,information_schema.innodb_sys_tablespaces b where a.space=b.space and a.name='aaaa/test';
+----------+-----------+-------+-----------+
| table_id | name | space | name |
+----------+-----------+-------+-----------+
| 977 | aaaa/test | 682 | aaaa/test |
+----------+-----------+-------+-----------+
1 row in set (0.01 sec)
|
4.4、alter table tablespace操作会重建整个表，并且不支持从临时表移动到永久表，
5、删除general tablespace
在删除general tablespace之前要删除这个表空间下的所有表，如果强行删除非空表空间，会返回报错。
查看要删除的表空间下面有没有其他库的表
1
2
3
4
5
6
7
8
| root@db 17:18: [aaaa]> select a.table_id,a.name,a.space,b.name from information_schema.innodb_sys_tables a,information_schema.innodb_sys_tablespaces b where a.space=b.space and b.name='ts1';
+----------+------------+-------+------+
| table_id | name | space | name |
+----------+------------+-------+------+
| 972 | aaaa/test1 | 680 | ts1 |
| 970 | test/t1 | 680 | ts1 |
+----------+------------+-------+------+
2 rows in set (0.00 sec)
|
强行删除ts1表空间
1
2
| root@db 17:18: [aaaa]> drop tablespace ts1;
ERROR 1529 (HY000): Failed to drop TABLESPACE ts1
|
6、注意事项
1
2
3
4
5
| 1、不建议使用表分区在共享表空间，这个特性在5.7.24版本是支持的，但是会在未来被移除。
2、对于不同的physical page size的压缩表和非压缩表不能共存于同一个general tablespace
3、不能创建临时general tablespace
4、删除在general tablespace下的表，空间不会释放，只能用于新的innodb数据
5、alter table ... discard tablespace和alter table ... import tablespace对于general tablespace下的表时不支持的
|