MySQL Partition Scheme Summary based on the time field, mysql Field

MySQL Partition Scheme Summary based on the time field, mysql Field

MySQL supports four partition types: RANGE, LIST, HASH, and KEY. RANGE can be divided into Native RANGE and range columns. LIST is divided into Native LIST and list columns. HASH is divided into Native HASH and linear hash, and KEY includes native KEY and linear hash. The differences between these partitions can be elaborated in another article.

Recently, I encountered a requirement that the time field (type: datetime) of the table should be partitioned Based on days. Traverse the partition section of the MySQL official document and summarize it as follows:

 

Implementation Method

It mainly includes the following types:

1. Based on RANGE

2. Based on RANGE COLUMNS

3. Based on HASH

 

Test Data

To test the above three solutions, we constructed 1 million of the test data and put it in the test table. The test table has only two columns: id and hiredate. hiredate only contains 10 days of data, from to 2015-12-10. The details are as follows:

mysql> show create table test\G*************************** 1. row ***************************       Table: testCreate Table: CREATE TABLE `test` (  `id` int(11) DEFAULT NULL,  `hiredate` datetime DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)mysql> select min(hiredate),max(hiredate) from test;+---------------------+---------------------+| min(hiredate)       | max(hiredate)       |+---------------------+---------------------+| 2015-12-01 00:00:00 | 2015-12-10 23:59:56 |+---------------------+---------------------+1 row in set (0.44 sec)mysql> select date(hiredate),count(*) from test group by date(hiredate);+----------------+----------+| date(hiredate) | count(*) |+----------------+----------+| 2015-12-01     |    99963 || 2015-12-02     |   100032 || 2015-12-03     |   100150 || 2015-12-04     |    99989 || 2015-12-05     |    99908 || 2015-12-06     |    99897 || 2015-12-07     |   100137 || 2015-12-08     |   100171 || 2015-12-09     |    99851 || 2015-12-10     |    99902 |+----------------+----------+10 rows in set (0.98 sec)

 

Test dimension

The test dimension mainly involves two aspects,

I. Partition pruning

Whether partition pruning can be performed for specific queries (that is, only related partitions are queried, not all partitions)

Ii. query time

Given that the batch of test data is static (no insert, update, or delete operations are performed concurrently), the data volume is not too large. It does not seem very significant to consider this dimension.

Therefore, the first dimension is tested.

 

RANGE-based partitioning scheme

Here, the TO_DAYS function is used.

CREATE TABLE range_datetime(    id INT,    hiredate DATETIME)PARTITION BY RANGE (TO_DAYS(hiredate) ) (    PARTITION p1 VALUES LESS THAN ( TO_DAYS('20151202') ),    PARTITION p2 VALUES LESS THAN ( TO_DAYS('20151203') ),    PARTITION p3 VALUES LESS THAN ( TO_DAYS('20151204') ),    PARTITION p4 VALUES LESS THAN ( TO_DAYS('20151205') ),    PARTITION p5 VALUES LESS THAN ( TO_DAYS('20151206') ),    PARTITION p6 VALUES LESS THAN ( TO_DAYS('20151207') ),    PARTITION p7 VALUES LESS THAN ( TO_DAYS('20151208') ),    PARTITION p8 VALUES LESS THAN ( TO_DAYS('20151209') ),    PARTITION p9 VALUES LESS THAN ( TO_DAYS('20151210') ),    PARTITION p10 VALUES LESS THAN ( TO_DAYS('20151211') ));

Insert data and view the execution plan of a specific query

mysql> insert into range_datetime select * from test;                                                                    Query OK, 1000000 rows affected (8.15 sec)Records: 1000000  Duplicates: 0  Warnings: 0mysql> explain partitions select * from range_datetime where hiredate >= '20151207124503' and hiredate<='20151210111230'; +----+-------------+----------------+--------------+------+---------------+------+---------+------+--------+-------------+| id | select_type | table          | partitions   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |+----+-------------+----------------+--------------+------+---------------+------+---------+------+--------+-------------+|  1 | SIMPLE      | range_datetime | p7,p8,p9,p10 | ALL  | NULL          | NULL | NULL    | NULL | 400061 | Using where |+----+-------------+----------------+--------------+------+---------------+------+---------+------+--------+-------------+1 row in set (0.03 sec)

Note that in the execution plan, only the p7, p8, p9, and p10 partitions are queried. Therefore, the to_days function can be used to crop partitions.

 

Range columns-based partitioning scheme

Range columns can be directly based on COLUMNS, and the partition object can only be an integer without the need for the preceding RANGE.

The statement for creating a table is as follows:

CREATE TABLE range_columns (     id INT,    hiredate DATETIME)PARTITION BY RANGE COLUMNS(hiredate) (    PARTITION p1 VALUES LESS THAN ( '20151202' ),    PARTITION p2 VALUES LESS THAN ( '20151203' ),    PARTITION p3 VALUES LESS THAN ( '20151204' ),    PARTITION p4 VALUES LESS THAN ( '20151205' ),    PARTITION p5 VALUES LESS THAN ( '20151206' ),    PARTITION p6 VALUES LESS THAN ( '20151207' ),    PARTITION p7 VALUES LESS THAN ( '20151208' ),    PARTITION p8 VALUES LESS THAN ( '20151209' ),    PARTITION p9 VALUES LESS THAN ( '20151210' ),    PARTITION p10 VALUES LESS THAN ('20151211' ));

Insert data and view the execution plan of the preceding Query

mysql> insert into range_columns select * from test;                                                                    Query OK, 1000000 rows affected (9.20 sec)Records: 1000000  Duplicates: 0  Warnings: 0mysql> explain partitions select * from range_columns where hiredate >= '20151207124503' and hiredate<='20151210111230'; +----+-------------+---------------+--------------+------+---------------+------+---------+------+--------+-------------+| id | select_type | table         | partitions   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |+----+-------------+---------------+--------------+------+---------------+------+---------+------+--------+-------------+|  1 | SIMPLE      | range_columns | p7,p8,p9,p10 | ALL  | NULL          | NULL | NULL    | NULL | 400210 | Using where |+----+-------------+---------------+--------------+------+---------------+------+---------+------+--------+-------------+1 row in set (0.11 sec)

Similarly, the partition scheme is used to achieve partition pruning.

 

HASH-based Partition Scheme

Because the HASH partition object can only be an integer, we cannot directly reference COLUMNS like the preceding range columns. Here, we also use the TO_DAYS function for conversion.

The statement for creating a table is as follows:

CREATE TABLE hash_datetime (   id INT,   hiredate DATETIME)PARTITION BY HASH( TO_DAYS(hiredate) )PARTITIONS 10;

Insert data and view the execution plan of the preceding Query

mysql> insert into hash_datetime select * from test;Query OK, 1000000 rows affected (9.43 sec)Records: 1000000  Duplicates: 0  Warnings: 0mysql> explain partitions select * from hash_datetime where hiredate >= '20151207124503' and hiredate<='20151210111230';+----+-------------+---------------+-------------------------------+------+---------------+------+---------+------+---------+-------------+| id | select_type | table         | partitions                    | type | possible_keys | key  | key_len | ref  | rows    | Extra       |+----+-------------+---------------+-------------------------------+------+---------------+------+---------+------+---------+-------------+|  1 | SIMPLE      | hash_datetime | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9 | ALL  | NULL          | NULL | NULL    | NULL | 1000500 | Using where |+----+-------------+---------------+-------------------------------+------+---------------+------+---------+------+---------+-------------+1 row in set (0.00 sec)

It is not difficult to see that using hash partitions does not effectively achieve partition pruning, at least in this example, based on the needs of the day.

 

The above three solutions are based on datetime. So how should we choose the timestamp type?

In fact, MySQL provides a RANGE partitioning scheme based on the UNIX_TIMESTAMP function, and can only use the UNIX_TIMESTAMP function. If other functions, such as to_days, the following ERROR is reported: "ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub) partitioning function are not allowed ".

The official document also mentions "Any other expressions involving TIMESTAMP values are not permitted. (See Bug #42849 .)".

Next we will test the RANGE Partition Scheme Based on the UNIX_TIMESTAMP function to see if it can achieve partition pruning.

 

Partition Scheme for TIMESTAMP

The statement for creating a table is as follows:

CREATE TABLE range_timestamp (    id INT,    hiredate TIMESTAMP)PARTITION BY RANGE ( UNIX_TIMESTAMP(hiredate) ) (    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-02 00:00:00') ),    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-03 00:00:00') ),    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-04 00:00:00') ),    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-05 00:00:00') ),    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-06 00:00:00') ),    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-07 00:00:00') ),    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-08 00:00:00') ),    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-09 00:00:00') ),    PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-10 00:00:00') ),    PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2015-12-11 00:00:00') ));

Insert data and view the execution plan of the preceding Query

mysql> insert into range_timestamp select * from test;Query OK, 1000000 rows affected (13.25 sec)Records: 1000000  Duplicates: 0  Warnings: 0mysql> explain partitions select * from range_timestamp where hiredate >= '20151207124503' and hiredate<='20151210111230';+----+-------------+-----------------+--------------+------+---------------+------+---------+------+--------+-------------+| id | select_type | table           | partitions   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |+----+-------------+-----------------+--------------+------+---------------+------+---------+------+--------+-------------+|  1 | SIMPLE      | range_timestamp | p7,p8,p9,p10 | ALL  | NULL          | NULL | NULL    | NULL | 400448 | Using where |+----+-------------+-----------------+--------------+------+---------------+------+---------+------+--------+-------------+1 row in set (0.00 sec)

Partition pruning is also possible.

 

Summary:

1. After comparison, I personally prefer the second solution, that is, implement partitions Based on RANGE COLUMNS.

2. before Version 5.7, for columns of the DATA and DATETIME types, to achieve partition pruning, you can only use the YEAR () and TO_DAYS () functions. In version 5.7, the TO_SECONDS () function is added.

3. In fact, the LIST can also implement the day-based partition scheme, but in this requirement, it is quite bad compared with RANGE.

4. columns of the TIMESTAMP type can only be partitioned Based on the UNIX_TIMESTAMP function. Remember!

 

Refer:

Http://dev.mysql.com/doc/refman/5.7/en/partitioning.html