MySQL Table partitioning case

0. Defragment the table space
Optimize table TableName

1, table partition by year partition, quarter sub-partition

Alter TableKey_part Partition byRange Year(create_time)) Subpartition byHash (quarter (create_time)) Subpartitions4(Partition P0ValuesLess Than ( -), Partition p2015ValuesLess Than ( .), Partition p2016ValuesLess Than ( .), Partition p2017ValuesLess Than (2018), Partition p2018ValuesLess Than (2019), partition P1ValuesLess than MaxValue);

A total of 24 partitions were generated, 1 years and 4 quarters, and 6 years and 24 quarters.

2, according to the Day Partition monthly table
Create_time supports the following date formats:
%y-%c-%d
%y-%c-%d%h:%i:%s

Alter TableAA Partition byRange (To_days (create_time)) (Partition P01ValuesLess Than (To_days ('2018-04-01')) engine=InnoDB, partition P02ValuesLess Than (To_days ('2018-04-02')) engine=InnoDB, Partition p03ValuesLess Than (To_days ('2018-04-03')) engine=InnoDB, Partition p04ValuesLess Than (To_days ('2018-04-04')) engine=InnoDB, Partition p05ValuesLess Than (To_days ('2018-04-05')) engine=InnoDB, Partition p06ValuesLess Than (To_days ('2018-04-06')) engine=InnoDB, Partition p07ValuesLess Than (To_days ('2018-04-07')) engine=InnoDB, Partition P08ValuesLess Than (To_days ('2018-04-08')) engine=InnoDB, Partition p09ValuesLess Than (To_days ('2018-04-09')) engine=InnoDB, Partition P10ValuesLess Than (To_days ('2018-04-10')) engine=InnoDB, partition P11ValuesLess Than (To_days ('2018-04-11')) engine=InnoDB, Partition P12ValuesLess Than (To_days ('2018-04-12')) engine=InnoDB, partition P13ValuesLess Than (To_days ('2018-04-13')) engine=InnoDB, Partition P14ValuesLess Than (To_days ('2018-04-14')) engine=InnoDB, Partition P15ValuesLess Than (To_days ('2018-04-15')) engine=InnoDB, partition p16ValuesLess Than (To_days ('2018-04-16')) engine=InnoDB, Partition p17ValuesLess Than (To_days ('2018-04-17')) engine=InnoDB, partition p18ValuesLess Than (To_days ('2018-04-18')) engine=InnoDB, Partition p19ValuesLess Than (To_days ('2018-04-19')) engine=InnoDB, Partition P20ValuesLess Than (To_days ('2018-04-20')) engine=InnoDB, Partition P21ValuesLess Than (To_days ('2018-04-21')) engine=InnoDB, partition P22ValuesLess Than (To_days ('2018-04-22')) engine=InnoDB, Partition p23ValuesLess Than (To_days ('2018-04-23')) engine=InnoDB, Partition P24ValuesLess Than (To_days ('2018-04-24')) engine=InnoDB, Partition P25ValuesLess Than (To_days ('2018-04-25')) engine=InnoDB, Partition P26ValuesLess Than (To_days ('2018-04-26')) engine=InnoDB, partition p27ValuesLess Than (To_days ('2018-04-27')) engine=InnoDB, partition p28ValuesLess Than (To_days ('2018-04-28')) engine=InnoDB, Partition P29ValuesLess Than (To_days ('2018-04-29')) engine=InnoDB, Partition P30ValuesLess Than (To_days ('2018-04-30')) engine=InnoDB, partition p31ValuesLess than MAXVALUE engine=InnoDB);

Explain partitions select * from Key_part where create_time>= ' 2018-04-12 ' and create_time<= ' 2018-04-15 '

You can see that only 4 partitioned tables were traversed, and only 4 rows were scanned, not all rows.

Explain partitions select * from Key_part where create_time>= ' 2018-04-27 '

You can see that only 5 partitioned tables were traversed, and only 5 rows were scanned, not all rows.

MySQL Table partitioning case