Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to create a table space in MySQL 5.7. the article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

The files in MySQL are very interesting. There are generally two parameters to do basic control before. One is that innodb_data_file_path is a shared tablespace, and the data is put into this file, that is, ibdata1. This file actually has duplicate roles, undo, and the data will be put together. Ibdata1 will continue to grow and cannot contract. Another parameter is innodb_file_per_table, in this way, it becomes an independent tablespace, more popular is that each table has independent files .frm and .ibd, and in practice, the use of independent tablespaces is quite common, the impact of delete operations on MySQL and Oracle is very different.

After that, we came to MySQL 5.7.5, adding the function of online phase undo log, which allows undo to be separated from the original ibdata1. For general application scenarios of independent tablespaces, MySQL also provides another management method, that is, General tablespace. In fact, this feature is already very common in Oracle, it is easy to understand from another point of view, it does not have the concept of library, you can create tables belonging to the same tablespace in multiple libraries.

In order to support this feature, two main changes have been made: the support of the Innodb layer and the changes to the MDL sub-module of the Server layer.

The statement to create a tablespace is simple, with the following syntax:

CREATE TABLESPACE tablespace_name ADD DATAFILE 'file_name' [FILE_BLOCK_SIZE = value] [ENGINE [=] engine_name]

The general format is create tablespace xxx add datafile 'xxxx' engine=innodb;, and the default storage unit is 16k.

Create tablespace general_ts1 add datafile 'general_ts1_01.dbf' engine=innodb

ERROR 3121 (HY000): Incorrect File Name 'general_ts1_01.dbf'.

One thing to note here is that the file path can be absolute or relative. But the file name has to be in .ibd format.

Create tablespace general_ts1 add datafile 'general_ts1_01.ibd' engine=innodb

Query OK, 0 rows affected (0.06 sec)

Of course, we can use create table xxx to specify tablespace, or alter table to specify tablespace.

The following method is not supported under GTID and is worth explaining.

Create table test_ts tablespace general_ts1 as select * from test

ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE. SELECT.

Let's change the posture and create a table to specify the tablespace.

Create table test_ts (id int,name varchar (30)) tablespace general_ts1

Query OK, 0 rows affected (0. 04 sec) look at the table statement and you can see it clearly.

> show create table test_ts

| | test_ts | CREATE TABLE `test_ ts` (

`id`int (11) DEFAULT NULL

`name` varchar (30) DEFAULT NULL

) / *! 50100 TABLESPACE `general_ ts1` * / ENGINE=InnoDB DEFAULT CHARSET=utf8 |

Let's compare and test and reassign a table users, which has more than 800,000 data.

> select count (*) from users

+-+

| | count (*) |

+-+

| | 817975 |

+-+

You can see that there are two separate files in the table usres before modification.

-rw-r- 1 mysql mysql 8606 Dec 4 22:48 users.frm

-rw-r- 1 mysql mysql 41943040 Dec 4 22:48 users.ibd

Use the alter statement to modify, the whole process is very fast

> alter table users tablespace general_ts1

Query OK, 0 rows affected (1.87 sec)

Records: 0 Duplicates: 0 Warnings: 0

At this point, there is only one definition file under the directory, and the data is placed in the newly created tablespace.

-rw-r- 1 mysql mysql 8606 Jan 4 22:46 users.frm

Let's briefly parse the definition file to see how the content is different from the original, and we can see that there is a new identification of the table space.

# strings users.frm

PRIMARY

InnoDB

General_ts1

)

User_id

User_name

User_id

User_name

The original blank data file will have the data immediately.

-rw-r- 1 mysql mysql 41943040 Jan 4 22:46 general_ts1_01.ibd

It is also possible if we change the way the table space is changed to a separate table space.

> ALTER TABLE users TABLESPACE=innodb_file_per_table

Query OK, 0 rows affected (2.17 sec)

Records: 0 Duplicates: 0 Warnings: 0

One difference is that the table DDL is not the same as the original format.

> show create table users

| | users | CREATE TABLE `users` (

`user_ id` int (11) unsigned NOT NULL

`user_ name` varchar (64) DEFAULT NULL

PRIMARY KEY (`user_ id`)

) / *! 50100 TABLESPACE `innodb_file_per_ Table` * / ENGINE=InnoDB DEFAULT CHARSET=utf8 |

The .ibd file is regenerated after the modification is completed.

If you want to view information about tablespaces, there are some differences in viewing data dictionaries with general tablespace. For example, there is a table users under the database test. In the view INNODB_SYS_TABLESPACES, you can only see the basic definition information of the table space, general_ts1, but can not find the word users.

This is how to create a table space in MySQL 5.7. if you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.