Mysql---partition table

Concept: A table consisting of one or more physical partitions

Type: RANGE HASH LIST KEY

The following main implementation range and hash

A. Range type partition Table 1. By primary KEY partitioning

DROP TABLE IF EXISTSP_user;CREATE TABLEP_user (IDINTAuto_incrementPRIMARY KEY, nameVARCHAR(225), Login_dateDATETIME) ENGINE=INNODB PARTITION byRANGE (ID) (PARTITION p0VALUESLess THAN (Ten), PARTITION p1VALUESLess THAN ( -), PARTITION p2VALUESLess THAN ( -), PARTITION P3VALUESLess THAN MAXVALUE)

Partition table P_user, the ID of less than 10 is stored in the P0 partition, the ID between 10 and 20 is stored in P1, and so on, the ID greater than 30 is stored in P3.

If you remove PARTITION p3 VALUES less THAN MAXVALUE , data with an insertion ID of more than 30 will be error:Table has no PARTITION for value

2. Partitioning by date

DROP TABLE IF EXISTSP_user;CREATE TABLEP_user (NameVARCHAR(225), Login_dateDATETIME) ENGINE=INNODB PARTITION byRANGE ( Year(login_date)) (PARTITION p_2014VALUESLess THAN ( -), PARTITION p_2015VALUESLess THAN ( .), PARTITION p_2016VALUESLess THAN ( .), PARTITION P_maxVALUESLess THAN MAXVALUE)

To view a partition query:

Select *  from where >= ' 2017-1-1 '

Visible is the data read from the P_max partition

Two. Hash type partition Table 1. partition by ID and distribute data evenly

DROP TABLE IF EXISTSP_user;CREATE TABLEP_user (IDINTAuto_incrementPRIMARY KEY, nameVARCHAR(225), Login_dateDATETIME) ENGINE=INNODB PARTITION byHASH (ID) Partitions4

SELECT *  from WHERE = 1015

The selection partition rule is: The computed value after the hash divided by the number of partitions the remainder is where the partition resides. (1015/4 of the 3 so in P3). So the hash type partition table can guarantee the data distribution evenly.

2. By ID partition, and with full 1000000 data for partition conditions

Note: The first partition is full of 1000000 data, then replace a partition, when four partitions are full 1000000 data, back to the first partition to start again, when the first partition again full of 1000000 data, continue to replace a partition, this cycle

DROP TABLE IF EXISTSP_user;CREATE TABLEP_user (IDINTAuto_incrementPRIMARY KEY, nameVARCHAR(225), Login_dateDATETIME) ENGINE=INNODB PARTITION byHASH (ID DIV1000000) Partitions4

Three. Efficiency test

Prepare two sheets

--Table with PartitionsDROP TABLE IF EXISTSP_user;CREATE TABLEP_user (IDINTAuto_incrementPRIMARY KEY, nameVARCHAR(225), Login_dateDATETIME) ENGINE=INNODB PARTITION byHASH (ID) Partitions4--table with no partitionsDROP TABLE IF EXISTS User;CREATE TABLE User(IDINTAuto_incrementPRIMARY KEY, nameVARCHAR(225), Login_dateDATETIME) ENGINE=INNODB

Two tables are populated with about 16 million of the data, respectively.

1. Test (1)

Select *  from where = 15000000 ; Select *  from User where = 15000000;

The query is time consuming because the ID is the primary key index, so the benefits of partitioned tables cannot be reflected

2. Test (2)

Remove the primary key and the self-increment attribute of the P_user and User table ID fields, and change the name of the ID 15000000 line to ' HTTT '

Select *  from where = 15000000 ; Select *  from User where = 15000000;

Reason: The first query will find the data in the corresponding partition, so faster

Select *  from where = 15000000;

3. Test (3)

And the same scenario as the test (2)

Select *  fromP_userwhereName= 'HTTT'  andId= 15000000;Select *  from User whereName= 'HTTT'  andId= 15000000;

Reason: The first query will find the data in the corresponding partition, so faster

4. Test (4)

And the same scenario as the test (2)

Select *  from where = ' HTTT ' ; Select *  from User where = ' HTTT ';

It takes more than two seconds for the partitioned table to be visible and executes multiple times, and the result is that the partitioned tables take a long time. Cause: The criteria field is not a partition field, so querying the data for each partition

Select *  from where = ' HTTT ';

5. Test Summary

---is partitioned by indexed columns, it has little effect on query optimization

---by non-indexed column partitioning, the query condition uses the partition column, can improve the query speed, conversely, will reduce the query speed

Mysql---partition table