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

Mysql partition sub-table (2)

Test the performance of unpartitioned and partitioned tables

Re-create a new test database and unpartitioned table back1

Create a partition table back2, distinguished by year and month

Maxvalue puts all the values for 2005 in the p11 area

Create larger data (to make it easier to distinguish between obvious partitions and unpartitioned ones when testing)

The rand () function produces a random number between 0 and 1, which is used as a seed value if an integer parameter N is specified. Each seed produces a different sequence of random numbers.

Execute the stored procedure load_part_tab to insert data into the back2

Insert data into back1

Test the performance of unpartitioned and partitioned

The execution can be analyzed by explain statement

Create an index to test the effect

Restart the mysqld service, you can see that there is little difference in time, if the data conference is obvious

Mysql Partition Type

1.range partition: partition multiple rows based on column values that belong to a given contiguous interval. Continuous intervals cannot overlap each other. Use values less than definition

Create a new table named benet1

2.list partition: similar to range partition, except that the basic column values are discrete sets of values

List partitioning is implemented by using partition by list

It is easy to add or delete records in specified places from the table.

3.hash partitioning: allows dba to compute the hash key of one or more columns of a table and finally partition it through data regions corresponding to different values of the hash code

It is divided into four regions using hash algorithm.

Create the data and view the data in that partition

View the details of the partition

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

* * 1. Row *

TABLE_CATALOG: def

TABLE_SCHEMA: test1

TABLE_NAME: benet3

PARTITION_NAME: p0

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 1

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: HASH

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: year (b)

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: NULL

TABLE_ROWS: 0

AVG_ROW_LENGTH: 0

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: 2017-06-22 18:01:56

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

* 2. Row * *

TABLE_CATALOG: def

TABLE_SCHEMA: test1

TABLE_NAME: benet3

PARTITION_NAME: p1

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 2

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: HASH

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: year (b)

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: NULL

TABLE_ROWS: 0

AVG_ROW_LENGTH: 0

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: 2017-06-22 18:01:56

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

* 3. Row * *

TABLE_CATALOG: def

TABLE_SCHEMA: test1

TABLE_NAME: benet3

PARTITION_NAME: p2

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 3

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: HASH

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: year (b)

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: NULL

TABLE_ROWS: 1

AVG_ROW_LENGTH: 16384

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: 2017-06-22 18:01:56

UPDATE_TIME: 2017-06-22 18:02:57

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

* * 4. Row *

TABLE_CATALOG: def

TABLE_SCHEMA: test1

TABLE_NAME: benet3

PARTITION_NAME: p3

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 4

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: HASH

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: year (b)

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: NULL

TABLE_ROWS: 0

AVG_ROW_LENGTH: 0

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: 2017-06-22 18:01:56

UPDATE_TIME: 2017-06-22 18:02:57

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

4 rows in set (0.00 sec)

ERROR:

No query specified

You can see that there is a data record in the third partition p2

4.key partition: key partition uses functions provided by mysql database for partition, ndb cluster uses md5 function partition, for hash functions within mysql of other storage engines

Create key partitions, tables, data View data will be placed in that area

There are four kinds of partitions: range, list, hash and key. The partition condition must be × ×, not that it needs to be converted through a function.

5.columns partition: columns partition is supported from 5.5.5.It can be said to be the evolutionary partition of range and list. You can directly use non-× × data for the data supported by partition:

All × ×, such as INT SMALLINT TINYINT BIGINT. FLOAT and DECIMAL do not support it.

Date types, such as DATE and DATETIME. The remaining date types are not supported.

String types, such as CHAR, VARCHAR, BINARY, and VARBINARY. BLOB and TEXT types are not supported.

COLUMNS can use multiple columns for partitioning.

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.