mysql 表分区按年分区，季度子分区_mysql按年分区的含义

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
);
1
2
3
4
5
6
7
8
9
10
11

表结构

... 
/*!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)
1
2
3
4
5
6
7
8
9
10
11

运行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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15