本文共 5939 字，大约阅读时间需要 19 分钟。
###################################################
MySQL8.0 Tablespace
###################################################
----------------------------------
Common
----------------------------------
mysql CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd'
Engine=InnoDB;
Query OK, 0 rows affected (0.31 sec)
mysql CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1
ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.46 sec)
mysql
mysql
mysql
mysql CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd'
FILE_BLOCK_SIZE = 8192 Eng
ine=InnoDB;
Query OK, 0 rows affected (0.36 sec)
mysql
mysql
mysql CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (0.35 sec)
mysql
mysql
mysql drop tablespace ts1;
ERROR 3120 (HY000): Tablespace `ts1` is not empty.
mysql drop tablespace ts2;
ERROR 3120 (HY000): Tablespace `ts2` is not empty.
mysql
mysql DROP TABLESPACE TS2;
ERROR 3510 (HY000): Tablespace TS2 doesn't exist.
mysql
mysql
mysql drop table t4;
Query OK, 0 rows affected (0.32 sec)
mysql drop table t3;
ERROR 1051 (42S02): Unknown table 'mysql.t3'
mysql drop table t2;
ERROR 1051 (42S02): Unknown table 'mysql.t2'
mysql
mysql drop table t1;
Query OK, 0 rows affected (0.32 sec)
mysql
mysql
mysql
mysql drop tablespace ts1;
Query OK, 0 rows affected (0.35 sec)
mysql drop tablespace ts2;
Query OK, 0 rows affected (0.18 sec)
mysql
mysql
----------------------------------
General Tablespace Table Partition Support
----------------------------------
mysql
mysql CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd'
Engine=InnoDB;
Query OK, 0 rows affected (0.35 sec)
mysql CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd'
Engine=InnoDB;
Query OK, 0 rows affected (0.30 sec)
mysql
mysql
mysql 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`
);
Query OK, 0 rows affected (1.28 sec)
mysql
mysql
mysql
mysql 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`));
Query OK, 0 rows affected (1.93 sec)
mysql
mysql SELECT NAME, SPACE, SPACE_TYPE FROM
INFORMATION_SCHEMA.INNODB_TABLES
-
WHERE NAME
LIKE '%t1%';
+------------------------+-------+------------+
| NAME
| SPACE | SPACE_TYPE |
+------------------------+-------+------------+
| mysql/t1#p#p1#sp#p1sp0 |
17 | General
|
| mysql/t1#p#p2#sp#p2sp0 |
18 | General
|
| mysql/t1#p#p3#sp#p3sp0 |
19 | Single
|
| mysql/t1#p#p4#sp#p4sp0 |
0 | System
|
+------------------------+-------+------------+
5 rows in set (0.21 sec)
mysql SELECT NAME, SPACE, SPACE_TYPE FROM
INFORMATION_SCHEMA.INNODB_TABLES
-
WHERE NAME
LIKE '%t2%';
+----------------------+-------+------------+
| NAME
| SPACE |
SPACE_TYPE |
+----------------------+-------+------------+
| mysql/t2#p#p1#sp#sp1 |
17 | General |
| mysql/t2#p#p1#sp#sp2 |
17 | General |
| mysql/t2#p#p2#sp#sp3 |
20 | Single
|
| mysql/t2#p#p2#sp#sp4 |
18 | General |
| mysql/t2#p#p3#sp#sp5 |
0 | System
|
| mysql/t2#p#p3#sp#sp6 |
21 | Single
|
+----------------------+-------+------------+
6 rows in set (0.00 sec)
mysql
----------------------------------
Moving Table Partitions Between Tablespaces Using ALTER
TABLE
----------------------------------
mysql CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
mysql CREATE TABLESPACE ts2 ADD DATAFILE 'ts2.ibd';
mysql CREATE TABLE t1 ( a INT NOT NULL, PRIMARY KEY
(a))
ENGINE=InnoDB TABLESPACE ts1
PARTITION BY RANGE (a) PARTITIONS 3 (
PARTITION P1 VALUES LESS THAN (2),
PARTITION P2 VALUES LESS THAN (4) TABLESPACE
`innodb_file_per_table`,
PARTITION P3 VALUES LESS THAN (6) TABLESPACE
`innodb_system`);
mysql SELECT A.NAME as partition_name, A.SPACE_TYPE as
space_type, B.NAME as space_name
FROM INFORMATION_SCHEMA.INNODB_TABLES A
LEFT JOIN INFORMATION_SCHEMA.INNODB_TABLESPACES
B
ON A.SPACE = B.SPACE WHERE A.NAME LIKE '%t1%'
ORDER BY A.NAME;
+----------------+------------+--------------+
| partition_name | space_type | space_name
|
+----------------+------------+--------------+
| test/t1#P#P1 |
General |
ts1
|
| test/t1#P#P2 |
Single |
test/t1#P#P2 |
| test/t1#P#P3 |
System |
NULL
|
+----------------+------------+--------------+
mysql ALTER TABLE t1 REORGANIZE PARTITION P1
INTO (PARTITION P1 VALUES LESS THAN (2)
TABLESPACE = `ts2`);
mysql ALTER TABLE t1 REORGANIZE PARTITION P2
INTO (PARTITION P2 VALUES LESS THAN (4)
TABLESPACE = `ts2`);
mysql ALTER TABLE t1 REORGANIZE PARTITION P3
INTO (PARTITION P3 VALUES LESS THAN (6));
mysql SELECT A.NAME AS partition_name, A.SPACE_TYPE AS
space_type, B.NAME AS space_name
FROM INFORMATION_SCHEMA.INNODB_TABLES A
LEFT JOIN INFORMATION_SCHEMA.INNODB_TABLESPACES
B
ON A.SPACE = B.SPACE WHERE A.NAME LIKE '%t1%'
ORDER BY A.NAME;
+----------------+------------+------------+
| partition_name | space_type | space_name |
+----------------+------------+------------+
| test/t1#P#P1 |
General |
ts2
|
| test/t1#P#P2 |
General |
ts2
|
| test/t1#P#P3 |
General |
ts1
|
+----------------+------------+------------+
----------------------------------
Renaming a General Tablespace
----------------------------------
ALTER TABLESPACE s1 RENAME TO s2;
----------------------------------
Dropping a General Tablespace
----------------------------------
mysql CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd'
Engine=InnoDB;
mysql CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts10
Engine=InnoDB;
mysql DROP TABLE t1;
mysql DROP TABLESPACE ts1;
转载地址：http://wvdoo.baihongyu.com/