mysql 表分区按年分区，季度子分区_mysql partition 按照季度分区-优快云博客

本文链接：https://blog.youkuaiyun.com/default7/article/details/74783337

本文介绍了MySQL中如何实现表按年和季度进行分区，详细解析了表结构设计，并通过运行`EXPLAIN PARTITIONS`来分析分区对查询性能的影响，旨在提升大数据量下的查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

alter table zz_info 
partition by range(year(date2))
subpartition by hash(quarter(date2 ))
subpartitions 4 (
    partition p0 values less than (2015),
    partition p2015 values less than (2016),
    partition p2016 values less than (2017),
    partition p2017 values less than (2018),
    partition p2018 values less than (2019),
    partition p1 values less than maxvalue
);

表结构

... 
/*!50100 PARTITION BY RANGE (year(date2))
SUBPARTITION BY HASH (quarter(date2 ))
SUBPARTITIONS 4
(PARTITION p0 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p2015 VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION p2016 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p2017 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p2018 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.24 sec)

运行explain partitions

mysql> explain partitions select * from zz_info where tid=@tid\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: zz_info
   partitions: p0_p0sp0,p0_p0sp1,p0_p0sp2,p0_p0sp3,p2015_p2015sp0,p2015_p2015sp1,p2015_p2015sp2,p2015_p2015sp3,p2016_p2016sp0,p2016_p2016sp1,p2016_p2016sp2,p2016_p2016sp3,p2017_p2017sp0,p2017_p2017sp1,p2017_p2017sp2,p2017_p2017sp3,p2018_p2018sp0,p2018_p2018sp1,p2018_p2018sp2,p2018_p2018sp3,p1_p1sp0,p1_p1sp1,p1_p1sp2,p1_p1sp3
         type: ref
possible_keys: idx-uniq-info-part
          key: idx-uniq-info-part
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 2 warnings (0.00 sec)