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

Mysql partition subtable

1. Sub-table is to decompose a large table into several physical tables with independent storage space according to certain rules, each table corresponds to three files, MYD data file, .MYI index file, .frm table structure file.

The mysql subtable is divided into vertical segmentation and horizontal segmentation.

Vertical splitting refers to the splitting of data table columns. We usually split a table with more columns into multiple tables according to the following principles: separate fields that are not commonly used in a single table; horizontal split refers to the split of data table rows. Split the data of a table into multiple tables to store.

Horizontal split principle: usually, we use hash, modularization, etc., to split the table.

By using the method of ID modeling, the data is distributed into four tables Id%4= [0meme 1meme 2p3]

Then query, update and delete are also queried by the method of taking the module.

There are several ways to divide the table:

1) mysql cluster

It is not a sub-table, but it plays the same role as a sub-table.

2) pre-estimate the tables with large amount of data and frequently accessed, and divide them into several tables

According to a certain algorithm (such as using hash, you can also use the method of remainder (modular)) to let users access different tables.

3) using merge storage engine to realize sub-table.

If you want to separate the existing big data scale is more painful, the most painful thing is to change the code, because the sql statement in the program has been written, using merge storage engine to achieve sub-table, this method is more suitable.

2. Zoning

Partitions are similar to subtables in that they are decomposed according to the rules. The difference is that the sub-table decomposes the large table into several independent physical tables, while the partition divides the data into segments and stores it in multiple locations. After the partition, the table is still a table, but the data is hashed to multiple locations.

There are two main forms of zoning:

Horizontal partitioning (Horizontal Partitioning) is a form of partitioning that partitions the rows of a table, and all the columns defined in the table can be found in every dataset, so the characteristics of the table are still maintained.

Vertical partitioning (Vertical Partitioning) generally reduces the width of the target table through vertical partitioning of the table, so that some specific columns are divided into specific partitions, and each partition contains rows corresponding to the columns in it.

Create a test database

Create a tabl

Insert data into the table, view the structure of the table, the data of the table

Execute the insert* statement to multiply the data and view the created data

Sub-table, divided into a master table for back1, slave table for back2,back3

INSERT_METHOD, this parameter INSERT_METHOD = NO indicates that the table cannot be written and used only as a query, and INSERT_METHOD = LAST indicates that it is inserted into the last table. INSERT_METHOD = first means to insert into the first table.

Divide the data into two slave tables and view the data in the child table

Check to see if there are created tables in the catalog data

Zoning:

Check if partitions are supported

If the mysql version is prior to 5.6, use the first one and select the second one after viewing 5.6.

Check whether partitioning is supported in the second query result

Active indicates support for zoning.

Create a new test database

Create data, create a range of partition tables maxvalue: from maximum into

Insert data

Check to see if there are partitions in the directory of the data

View partition table information from the system library

Mysql > select * from information_schema.partitions where table_schema='test2' and table_name='user'\ G

* * 1. Row *

TABLE_CATALOG: def

TABLE_SCHEMA: test2

TABLE_NAME: user

PARTITION_NAME: p0

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 1

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: id

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: 3

TABLE_ROWS: 2

AVG_ROW_LENGTH: 8192

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: 2017-06-2017: 44:03

UPDATE_TIME: 2017-06-2017: 45:04

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

* 2. Row * *

TABLE_CATALOG: def

TABLE_SCHEMA: test2

TABLE_NAME: user

PARTITION_NAME: p1

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 2

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: id

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: 6

TABLE_ROWS: 3

AVG_ROW_LENGTH: 5461

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: 2017-06-2017: 44:03

UPDATE_TIME: 2017-06-2017: 45:19

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

* 3. Row * *

TABLE_CATALOG: def

TABLE_SCHEMA: test2

TABLE_NAME: user

PARTITION_NAME: p2

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 3

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: id

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: 9

TABLE_ROWS: 3

AVG_ROW_LENGTH: 5461

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: 2017-06-2017: 44:03

UPDATE_TIME: 2017-06-2017: 45:36

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

* * 4. Row *

TABLE_CATALOG: def

TABLE_SCHEMA: test2

TABLE_NAME: user

PARTITION_NAME: p3

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 4

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: id

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: 12

TABLE_ROWS: 3

AVG_ROW_LENGTH: 5461

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: 2017-06-2017: 44:03

UPDATE_TIME: 2017-06-2017: 45:54

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

* * 5. Row * *

TABLE_CATALOG: def

TABLE_SCHEMA: test2

TABLE_NAME: user

PARTITION_NAME: p4

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 5

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: id

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: MAXVALUE

TABLE_ROWS: 1

AVG_ROW_LENGTH: 16384

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: 2017-06-2017: 44:03

UPDATE_TIME: 2017-06-2017: 45:57

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

5 rows in set (0.00 sec)

ERROR:

No query specified

View data from p0 partition

Delete p4 partition

Add New Partition

Delete all records in the area

Alter table table name drop partition area name

Division merging p0menp1rep2 into p01meme p02

Check whether the data is merged

Check to see if the data from the directory is merged into a new partition

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.