RANGE & amp; HASH of MySQL partitions

Since Mysql5.1, the partition function has emerged. Table Partitioning is like dividing a large table into several small tables. You do not need to perform full table scanning when performing a query, you only need to query the table partitions that meet the requirements, which greatly improves the query speed. In addition, the implementation of table partitions also facilitates data management, for example, if the product needs to delete all data from the previous year, you only need to delete the table partition where the data from the previous year is located.

Mysql table partition has a lot of, details click: http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html
Here we will discuss the partitioning operations of RANGE, HASH, and RANGE in combination with HASH.
Note: The primary key or unique index Column exists in the source table must be used for the columns used by all table partitions. Otherwise, the creation fails. If the source table does not have any primary key or unique index column, you can select any column as needed during partitioning.
RANGE: As the name implies, partitions are performed by determining the value RANGE of the selected column.

Recommended reading:

MySQL Partition Table practice

Failed to write Rsyslog data because the MySQL partition table is not created

The statement for creating a common table is as follows:
For the convenience of the experiment, the date field uses the time type DATETIME instead of TIMESTAMP, because TIMESTAMP does not support YEAR (), MONTH (), TO_DAYS () and other operations can only use the UNIX_TIMSTAMP () function, so you need to consider this when designing the table:
Create table t1 (id INT, date datetime default CURRENT_TIMESTAMP) ENGINE = Innodb;

Insert some test data:
Mysql> SELECT * FROM t1;
+ ------ + --------------------- +
| Id | date |
+ ------ + --------------------- +
| 1 | 12:59:39 |
| 2 | 12:59:43 |
| 3 | 12:59:44 |
| 4 | 19:35:45 |
| 5 | 19:35:45 |
| 6 | 19:35:45 |
| 7 | 19:35:45 |
| 8 | 19:35:45 |
| 9 | 19:35:45 |
| 10 | 19:35:45 |
+ ------ + --------------------- +
10 rows in set (0.00 sec)

View query statement execution plan: a full table scan is performed.

Mysql> explain select * FROM t1;
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------- +
| 1 | SIMPLE | t1 | ALL | NULL | 10 | NULL |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------- +
1 row in set (0.00 sec)
Mysql> explain select * FROM t1 WHERE date> = '2017-03-05 19:00:12'
-> AND date <= '2014-03-05 18:45:12 ';
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------------- +
| 1 | SIMPLE | t1 | ALL | NULL | 10 | Using where |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------------- +
1 row in set (0.00 sec)

Create a new table t2 and partition the table by year
Mysql> create table t2 (id INT, date datetime default CURRENT_TIMESTAMP) ENGINE = Innodb
-> Partition by range (YEAR (date ))(
-> PARTITION p2013 values less than (2014 ),
-> PARTITION p2014 values less than (2015 ),
-> PARTITION p2015 values less than (2016 ),
-> PARTITION p2016 values less than (2017 ),
-> PARTITION p2017 values less than (2018 ),
-> PARTITION p2099 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (2.47 sec)

View the data distribution and import the data of table t1:
Mysql> SELECT table_name, partition_name, table_rows FROM information_schema.PARTITIONS WHERE table_schema = database () AND table_name = 't2 ';
+ ------------ + ---------------- + ------------ +
| Table_name | partition_name | table_rows |
+ ------------ + ---------------- + ------------ +
| T2 | p2013 | 0 |
| T2 | p2014 | 0 |
| T2 | p2015 | 0 |
| T2 | p2016 | 0 |
| T2 | p2017 | 0 |
| T2 | p2099 | 0 |
+ ------------ + ---------------- + ------------ +
6 rows in set (0.00 sec)
Mysql> SELECT * FROM t2;
Empty set (0.00 sec)
Mysql> explain select * FROM t2;
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------- +
| 1 | SIMPLE | t2 | ALL | NULL | 6 | NULL |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------- +
1 row in set (0.00 sec)
Mysql> insert into t2 SELECT * FROM t1;
Query OK, 10 rows affected (0.36 sec)
Records: 10 Duplicates: 0 Warnings: 0

View the data segment status again:
Mysql> SELECT * FROM t2;
+ ------ + --------------------- +
| Id | date |
+ ------ + --------------------- +
| 1 | 12:59:39 |
| 2 | 12:59:43 |
| 3 | 12:59:44 |
| 4 | 19:35:45 |
| 5 | 19:35:45 |
| 6 | 19:35:45 |
| 7 | 19:35:45 |
| 8 | 19:35:45 |
| 9 | 19:35:45 |
| 10 | 19:35:45 |
+ ------ + --------------------- +
10 rows in set (0.00 sec)
Mysql> SELECT table_name, partition_name, table_rows FROM information_schema.PARTITIONS WHERE table_schema = database () AND table_name = 't2 ';
+ ------------ + ---------------- + ------------ +
| Table_name | partition_name | table_rows |
+ ------------ + ---------------- + ------------ +
| T2 | p2013 | 4 |
| T2 | p2014 | 2 |
| T2 | p2015 | 2 |
| T2 | p2016 | 0 |
| T2 | p2017 | 1 |
| T2 | p2099 | 1 |
+ ------------ + ---------------- + ------------ +
6 rows in set (0.00 sec)

View the number of rows scanned for the entire table:

Mysql> explain select * FROM t2 \ G
* *************************** 1. row ***************************
Id: 1
Select_type: SIMPLE
Table: t2
Type: ALL
Possible_keys: NULL
Key: NULL
Key_len: NULL
Ref: NULL
Rows: 11
Extra: NULL
1 row in set (0.00 sec)

After filtering the where clause:
1234567 mysql> explain partitions select * FROM t2 WHERE date> = '2014-03-05 19:00:12 'AND date <= '2014-03-05 18:45:12 ';
+ ---- + ------------- + ------- + ------------------- + ------ + ------------- + ------ + --------- + ------ + ------------- +
| Id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------------------- + ------ + ------------- + ------ + --------- + ------ + ------------- +
| 1 | SIMPLE | t2 | p2014, p2015, p2016 | ALL | NULL | 5 | Using where |
+ ---- + ------------- + ------- + ------------------- + ------ + ------------- + ------ + --------- + ------ + ------------- +
1 row in set (0.00 sec)

It can be found that no full table scan is performed after the partition, And the scan partition is only within the time range.

1 2 Next Page