MySQL Table partitioning case

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.