本文详细介绍了MySQL InnoDB通用表空间的概念、创建、添加表、行格式、在表空间之间移动表以及删除通用表空间的方法。通用表空间提供了一种更灵活的方式来管理表数据，减少了内存使用，并允许数据文件放置在默认数据目录之外。
一. 通用表空间简介
通用表空间是innodb表空间新类型，5.7.6引入 通用表空间提供以下功能：
- 类似系统表空间，是共享表空间，能存储多个表的数据
- 相比独立表空间，通用表空间能减少内存使用(对表空间元数据)
- 通用表空间数据文件能够放到默认数据目录之外，提供相比独立表空间更多存储管理能力
- 通用表空间支持
Antelope和Barracuda文件格式，所以支持所有的表行格式和相关特性，不依赖于innodb_file_format或innodb_file_per_table设置 CREATE TABLE的TABLESPACE选项能够支持普通表空间、独立表空间或系统表空间ALTER TABLE的TABLESPACE选项支持能够在普通表空、独立表空间或系统表空间之间移动表
二. 创建通用表空间
Syntax:
CREATE TABLESPACE tablespace_name
ADD DATAFILE 'file_name'
[FILE_BLOCK_SIZE = value]
[ENGINE [=] engine_name]
- 能在默认数据目录或之外创建通用表空间，为了避免跟独立表空间冲突，不支持在数据目录的子目录创建通用表空间，在默认数据目录之外创建通用表空间，目录需先存在
- 5.7.8当创建默认数据目录之外的通用表空间，将在数据目录创建一个.isl文件(解决将数据目录移到另外一个目录后，通用表空间里面的表不能打开bug)
root@localhost*5.7.9-log[test] >create tablespace ts1 add datafile 'ts1.ibd' engine=innodb;
Query OK, 0 rows affected (0.00 sec)
root@localhost*5.7.9-log[test] >create tablespace ts2 add datafile '/data2/mysql57/ts2.ibd' engine=innodb;
Query OK, 0 rows affected (0.00 sec)
[root@mysql01 /root/tablespace]
$ll /data/mysql/ts*
-rw-r----- 1 mysql mysql 32768 Dec 30 14:38 /data/mysql/ts1.ibd
-rw-r----- 1 mysql mysql 22 Dec 30 14:40 /data/mysql/ts2.isl
[root@mysql01 /root/tablespace]
$ll /data2/mysql57/
total 32
drwxr-x--- 2 mysql mysql 6 Dec 30 10:33 test
-rw-r----- 1 mysql mysql 32768 Dec 30 14:40 ts2.ibd
- 可以指定相对数据目录的相对路径，
ENGINE=INNODB子句必须指定，或定义default_storage_engine=innodb
三. 添加表到通用表空间
- 创建通用表空间后，能使用
create table tbl_name ... tablespace=tablespace_name 或 alter table tbl_name tablespace=tablespace_name添加表到通用表空间
root@localhost*5.7.9-log[test] >create table t1(c1 int primary key) tablespace ts1 row_format=compact;
Query OK, 0 rows affected (0.00 sec)
root@localhost*5.7.9-log[test] >alter table test_rank tablespace ts1;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
$ll /data/mysql/test/test_ran*
-rw-r----- 1 mysql mysql 8588 Dec 14 12:40 /data/mysql/test/test_rank.frm
-rw-r----- 1 mysql mysql 49152 Dec 14 12:40 /data/mysql/test/test_rank.ibd
$ll /data/mysql/test/test_ran*
-rw-r----- 1 mysql mysql 8588 Dec 30 14:50 /data/mysql/test/test_rank.frm -- 表结构文件还是存在数据目录下
四. 通用表空间行格式
- 通用表空间支持所有表行格式(redundant,compact,dynamic,compressed)，压缩和非压缩表不能同时存在同一个表空间(物理页大小不同)
- 通用表空间包含压缩表(row_format=compressed)，必须指定
file_block_size(跟innodb_page_size有关)，页大小必须是有效压缩页大小，压缩表的物理页大小(KEY_BLOCK_SIZE)必须等于FILE_BLOCK_SIZE/1024 - 表压缩不支持32k和64k innodb页大小
- 当
FILE_BLOCK_SIZE跟innodb_page_size想等，通用表空间不能存放压缩表，只能存放非压缩表(compact,redundant,dynamic) - 如果创建表空间时，没有指定
key_block_size，默认为innodb_page_size
root@localhost*5.7.9-log[test] >show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_page_size | 8192 |
+------------------+-------+
1 row in set (0.00 sec)
root@localhost*5.7.9-log[test] >create tablespace ts3 add datafile 'ts3.ibd' file_block_size=4096 engine=innodb;
Query OK, 0 rows affected (0.02 sec)
root@localhost*5.7.9-log[test] >create table t4(c1 int primary key) tablespace ts3 row_format=compressed key_block_size=4;
Query OK, 0 rows affected (0.01 sec)
五. 在表空间之间移动表
- 使用
alter table ... tablespace移动一个innodb表到一个通用表空间、或独立表空间、或系统表空间 - 移动一个表从独立表空间或系统表空间到通用表空间，指定通用表空间的名称，通用表空间必须存在
- 从通用表空间或独立表空间到系统表空间，指定
innodb_system - 从系统表空间或通用表空间到独立表空间，指定
innodb_file_per_table alter table ... tablespace操作总是引起一个全表重建，甚至tablespace属性相对之前值没有发生改变，不支持移动一个表从临时表空间到持久表空间data directory语句跟create table ... tablespace=innodb_file_per_table作用相同，创建独立表空间，但是不能结合一起使用
表分区支持
tablespace选项能使用分配个别表分区或子分区到通用表空间(5.7.8支持)、独立表分区、系统表分区- 所有分区必须有相同的存储引擎
CREATE TABLE t1 (a INT, b INT) ENGINE = INNODB PARTITION BY RANGE (a) SUBPARTITION BY KEY (B)(
PARTITION p1
VALUES
LESS THAN (100) TABLESPACE = `ts1`,
PARTITION p2
VALUES
LESS THAN (1000) TABLESPACE = `ts2`,
PARTITION p3
VALUES
LESS THAN (10000) TABLESPACE `innodb_file_per_table`,
PARTITION p4
VALUES
LESS THAN (100000) TABLESPACE `innodb_system`
);
CREATE TABLE t2 (a INT, b INT) ENGINE = INNODB PARTITION BY RANGE (a) subpartition BY KEY (b)(
PARTITION p1
VALUES
less than (100) TABLESPACE = `ts1` (
subpartition sp1,
subpartition sp2
),
PARTITION p2
VALUES
less than (1000)(
subpartition sp3,
subpartition sp4 TABLESPACE = `ts2`
),
PARTITION p3
VALUES
less than (10000)(
subpartition sp5 TABLESPACE `innodb_system`,
subpartition sp6 TABLESPACE `innodb_file_per_table`
)
);
ALTER TABLE t1 ADD PARTITION (
PARTITION p5
VALUES
less than (1000000) TABLESPACE = `ts1`
);
-- 查看表所在的表空间
SELECT
t.name tablename,
t.file_format,
t.row_format,
s.name tablespace_name,
t.space,
s.page_size,
s.FS_BLOCK_SIZE
FROM
information_schema.innodb_sys_tables AS t,
information_schema.INNODB_SYS_TABLESPACES AS s
WHERE
t.space = s.space
AND (t.name LIKE '%t1%' OR t.name LIKE '%t2%');
六. 删除通用表空间
drop tablespace删除一个innodb通用表空间- 所有通用表空间内的表需先删除，才能删除通用表空间，如果通用表空间非空，
drop tablespace将返回错误 - 通用Innodb表空间当最后一个表删除后，通用表空间不会自动删除
- 通用表空间不属于任何特定的数据库，
drop database能删除表空间中的表，但不能删除表空间 - 类似系统表空间，truncate或drop table，通用表空间的自由空间不会释放给操作系统，仅仅能用于新的innodb数据，独立表空间当
drop table时会删除表空间
root@localhost*5.7.9-log[test] >create tablespace `ts3` add datafile 'ts3.ibd' engine=innodb;
Query OK, 0 rows affected (0.00 sec)
root@localhost*5.7.9-log[test] >create table t3(c1 int primary key) tablespace ts3 engine=innodb;
Query OK, 0 rows affected (0.00 sec)
root@localhost*5.7.9-log[test] >use test2
Database changed
root@localhost*5.7.9-log[test2] >create table t33(c1 int primary key) tablespace ts3 engine=innodb;
Query OK, 0 rows affected (0.00 sec)
root@localhost*5.7.9-log[test2] >drop tablespace ts3;
ERROR 1529 (HY000): Failed to drop TABLESPACE ts3 -- 表空间存在表，删除表空间报错
root@localhost*5.7.9-log[test2] >drop table test.t3;
Query OK, 0 rows affected (0.01 sec)
root@localhost*5.7.9-log[test2] >drop table test2.t33;
Query OK, 0 rows affected (0.00 sec)
root@localhost*5.7.9-log[test2] >drop tablespace ts3;
Query OK, 0 rows affected (0.00 sec)
查看对应表空间存在哪些表：
SELECT
t.name tablename,
t.file_format,
t.row_format,
s.name tablespace_name,
t.space,
s.page_size,
s.FS_BLOCK_SIZE
FROM
information_schema.innodb_sys_tables AS t,
information_schema.INNODB_SYS_TABLESPACES AS s
WHERE
t.space = s.space
AND s.name='ts3';
七. 通用表空间限制
- 生成或存在的表空间不能转换为通用表空间
- 不支持临时通用表空间
- 通用表空间不支持临时表
- 使用
tablespace=tablespace_name分配个别分区或子分区到同样表空间，仅仅5.7.8或高版本支持 - 5.7.6或高版本，表才能存储到通用表空间
- 类似系统表空间，
truncate或drop table存储在通用表空间的表，标记删除的空间仅仅能用于新innodb数据，不能释放给操作系统 alter table ... discard tablespace和alter table ... import tablespace不支持
扫一扫
2344
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
