MySQL uses range partitioning to improve query efficiency

Brief introduction:

The range partition is based on a given range of contiguous intervals, and earlier versions of range are primarily integer-based partitions. The range partition can also be used for date, datetime columns in version 5.7, and non-shaped range column partitions are available on more than 5.5 versions. The range partition must be contiguous and cannot overlap. Use

"Values less THAN ()" To define the partition interval, non-shaping range values need to use single quotes, and MaxValue can be used as the highest value for the partition.

This article will give you the MySQL 5.6.24 range to increase the deletion process of the relevant content, share to everyone for reference to study, the following to see the detailed introduction:

There are about 400,000 data in the Box_enter_record_archive table, which is queried without any changes: time consuming 0.545s

Add time to the primary key, Note: The field that created the partition in MySQL must be in the main key , and then the query takes time:0.547s

Use range Partitioning

ALTER TABLE' box_enter_record_archive ' partition byRange (To_days (entertime)) (Partition P0ValuesLess Than (To_days ('2018-01-01')), Partition P1ValuesLess Than (To_days ('2018-02-01')), Partition P2ValuesLess Than (To_days ('2018-03-01')), Partition P3ValuesLess Than (To_days ('2018-04-01')), Partition P4ValuesLess Than (To_days ('2018-05-01')), Partition P5ValuesLess Than (To_days ('2018-06-01')), Partition P6ValuesLess Than (To_days ('2018-07-01')), Partition P7ValuesLess Than (To_days ('2018-08-01')), Partition P8ValuesLess Than (To_days ('2018-09-01')), Partition P9ValuesLess Than (To_days ('2018-10-01')), Partition P10ValuesLess Than (To_days ('2018-11-01')), Partition P11ValuesLess Than (To_days ('2018-12-01')))

View partition status, how many records each partition has

SELECT partition_name,table_rows,a.*from    information_schema.partitions A  WHERE = Schema()  and table_name    ='box_enter_record_archive'

Query time after partition creation:0.169s

After you add an index to Entertime, the query takes time:0.004s

Related commands:

 alter  table  ' Box_enter_record_ Archive ' drop  partition P0; --  alter  table  ' Box_enter_record_ Archive ' REMOVE partitioning; --  alter  table  ' Box_enter_record_ Archive ' add  partition (partition P12 values  less Than (To_days ( " 2019-01-01  Span style= "COLOR: #ff0000" > ")"); --  new range partition  

Every day, progress a little

MySQL uses range partitioning to improve query efficiency