Shulou(Shulou.com)06/01 Report--

General tablespace

General tablespace is a shared innodb tablespace, somewhat similar to ibdata1. You can store multiple tables under one tablespace data file, even if they come from different schame.

Advantages of General tablespace

Similar to the system tablespace, you can store multiple tables under multiple schema.

Compared with file_per_table tablespace, General tablespace has potential memory advantages. In this way, the metadata of the tablespace is stored in memory, which reduces memory consumption.

General tablespace can store data files on other disks. The advantage of this is that, for example, if I need to put several hotspot tables on a faster storage disk, I can create a general tablespace on the faster storage disk and put the hotspot table there.

General supports all line formats, as well as related features.

The data table can be moved back and forth between general tablespace and per_file_table tablespace. Similar to the move table of Oracle database.

Syntax for creating general tablespace

CREATE TABLESPACE tablespace_name

ADD DATAFILE 'file_name'

[FILE_BLOCK_SIZE = value]

[ENGINE [=] engine_name]

Note that to avoid data directory conflicts, it is not supported for tablespaces to be established under the data directory subdirectory. Because the datadir subdirectory will be considered database by mysql. But general tablespace can be built under datadir. After General tablespace is established in a non-datadir directory, an .isl file with a detailed path is generated under data directory.

For example, create a general tablespace under datadir. The page_size is 8k. The default engine is innodb:

Mysql > create tablespace tbs01 add datafile 'tbs01.ibd' file_block_size=8192 engine=innodb

Query OK, 0 rows affected (0.02 sec)

Mysql > create tablespace tbs02 add datafile'/ u01ql engine=innodb 3306 Uniql Universe Tbs02.ibd'

Query OK, 0 rows affected (0.01 sec)

Isl is actually a path:

[root@oradb-2062 data] # more tbs02.isl

/ u01/mysql/3306/general_tbs/tbs02.ibd

The INNODB_SYS_TABLESPACES table under Information_schema has metadata information for these tablespaces:

Mysql > select space,name,FLAG,FILE_FORMAT,PAGE_SIZE,SPACE_TYPE,FILE_SIZE,ALLOCATED_SIZE from INNODB_SYS_TABLESPACES where name like 'tbs%'\ G

* * 1. Row *

Space: 61

Name: tbs01

FLAG: 2089

FILE_FORMAT: Barracuda

PAGE_SIZE: 16384

SPACE_TYPE: General

FILE_SIZE: 65536

ALLOCATED_SIZE: 16384

* 2. Row * *

Space: 63

Name: tbs02

FLAG: 2048

FILE_FORMAT: Any

PAGE_SIZE: 16384

SPACE_TYPE: General

FILE_SIZE: 65536

ALLOCATED_SIZE: 32768

2 rows in set (0.00 sec)

Use of General tablespaces

Tbs01 specifies FILE_BLOCK_SIZE=8k when building a tablespace

Tbs02 did not specify FILE_BLOCK_SIZE when building the tablespace

FILE_BLOCK_SIZE can only be used when building tablespaces when storing compressed tables. Otherwise, an error will be reported by building a non-compressed table. That is, compressed and uncompressed tables cannot be in the same tablespace.

Mysql > create table tt (an int) tablespace=tbs01

ERROR 1478 (HY000): InnoDB: Tablespace `tbs01` uses block size 8192 and cannot contain a table with physical page size 16384

It is possible to build a compression table.

Mysql > create table tt (an int) tablespace=tbs01 ROW_FORMAT=COMPRESSED

Query OK, 0 rows affected (0.02 sec)

Similarly, you cannot build a compression table in tbs02

Mysql > create table tt01 (an int) tablespace=tbs02 ROW_FORMAT=COMPRESSED

ERROR 1478 (HY000): InnoDB: Tablespace `tbs02` cannot contain a COMPRESSED table

Mysql > create table tt01 (an int) tablespace=tbs02

Query OK, 0 rows affected (0.01 sec)

Move the table in the tablespace to file_per_table tablespace.

Mysql > alter table tt tablespace=innodb_file_per_table

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > alter table tt01 tablespace=innodb_file_per_table

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0 Warnings: 0

Move a 2.5 million table from file_per_table tablespace to general tablespace

Mysql > alter table test02 tablespace=tbs02

Query OK, 0 rows affected (9.41 sec)

Records: 0 Duplicates: 0 Warnings: 0

It took more than 9 seconds, which shows that it takes a lot of time to move a large watch.

Attached, move the table to the system tablespace

ALTER TABLE test01 TABLESPACE innodb_system

Delete the tablespace and note that it cannot be deleted successfully when there are tables in the tablespace.

Mysql > drop tablespace tbs02

ERROR 1529 (HY000): Failed to drop TABLESPACE tbs02

Limitations of General tablespace

General tablespace does not support temporary tables.

Similar to system tablespaces, truncate or drop table simply marks the space as available, but does not return it to the operating system.

MySQL version 5.7.24 and later no longer supports putting partitions into general tablespace.

ALTER TABLE... DISCARD TABLESPACE and ALTER TABLE... IMPORT TABLESPACE does not support general tablespace.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.