欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

MySQL日期范围分区查询时分区裁剪不正确的问题

程序员文章站 2022-05-25 09:40:50
...

---

layout:     post

title:      "MySQL日期范围分区查询时分区裁剪不正确的问题"

subtitle:   ""

date:       2016-07-07

author:     "lixuanbin"

header-img: "img/bg15.jpg"

tags:

    - MySQL

---

 

获得更加浏览体验，请点击：

http://lixuanbin.github.io/2016/07/07/MySQL-partition-by-datetime-range-not-pruning-when-select/ 

 

   最近优化几条业务报表语句的时候，偶然间发现有个按日期分区的表在指定了日期范围后仍然扫描了所有分区，刚开始怀疑是建表语句中分区的写法有问题，以下是原来的分区写法：

 

```sql

CREATE TABLE `tblogin_2007` (

   `logid` int(11) NOT NULL AUTO_INCREMENT,

   `appId` varchar(20) NOT NULL,

   `other_fields_blah_blah` varchar(200) NOT NULL,

   `reportTime` datetime NOT NULL,

   `dtUpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

   PRIMARY KEY (`logid`,`reportTime`)

 ) ENGINE=InnoDB AUTO_INCREMENT=97662 DEFAULT CHARSET=utf8

 PARTITION BY RANGE (YEAR (reportTime) * 100 + MONTH (reportTime))

 (PARTITION p201407 VALUES LESS THAN (201407) ENGINE = InnoDB,

  PARTITION p201408 VALUES LESS THAN (201408) ENGINE = InnoDB,

  PARTITION p201409 VALUES LESS THAN (201409) ENGINE = InnoDB,

  PARTITION p201410 VALUES LESS THAN (201410) ENGINE = InnoDB,

  PARTITION p201411 VALUES LESS THAN (201411) ENGINE = InnoDB,

  PARTITION p201412 VALUES LESS THAN (201412) ENGINE = InnoDB,

  PARTITION p201501 VALUES LESS THAN (201501) ENGINE = InnoDB,

  PARTITION p201502 VALUES LESS THAN (201502) ENGINE = InnoDB,

  PARTITION p201503 VALUES LESS THAN (201503) ENGINE = InnoDB,

  PARTITION p201504 VALUES LESS THAN (201504) ENGINE = InnoDB,

  PARTITION p201505 VALUES LESS THAN (201505) ENGINE = InnoDB,

  PARTITION p201506 VALUES LESS THAN (201506) ENGINE = InnoDB,

  PARTITION p201507 VALUES LESS THAN (201507) ENGINE = InnoDB,

  PARTITION p201508 VALUES LESS THAN (201508) ENGINE = InnoDB,

  PARTITION p201509 VALUES LESS THAN (201509) ENGINE = InnoDB,

  PARTITION p201510 VALUES LESS THAN (201510) ENGINE = InnoDB,

  PARTITION p201511 VALUES LESS THAN (201511) ENGINE = InnoDB,

  PARTITION p201512 VALUES LESS THAN (201512) ENGINE = InnoDB,

  PARTITION p201601 VALUES LESS THAN (201601) ENGINE = InnoDB,

  PARTITION p201602 VALUES LESS THAN (201602) ENGINE = InnoDB,

  PARTITION p201603 VALUES LESS THAN (201603) ENGINE = InnoDB,

  PARTITION p201604 VALUES LESS THAN (201604) ENGINE = InnoDB,

  PARTITION p201605 VALUES LESS THAN (201605) ENGINE = InnoDB,

  PARTITION p201606 VALUES LESS THAN (201606) ENGINE = InnoDB,

  PARTITION p201607 VALUES LESS THAN (201607) ENGINE = InnoDB,

  PARTITION p201608 VALUES LESS THAN (201608) ENGINE = InnoDB,

  PARTITION p201609 VALUES LESS THAN (201609) ENGINE = InnoDB,

  PARTITION p201610 VALUES LESS THAN (201610) ENGINE = InnoDB,

  PARTITION p201611 VALUES LESS THAN (201611) ENGINE = InnoDB,

  PARTITION p201612 VALUES LESS THAN (201612) ENGINE = InnoDB,

  PARTITION p201701 VALUES LESS THAN (201701) ENGINE = InnoDB,

  PARTITION p201702 VALUES LESS THAN (201702) ENGINE = InnoDB,

  PARTITION p201703 VALUES LESS THAN (201703) ENGINE = InnoDB,

  PARTITION p201704 VALUES LESS THAN (201704) ENGINE = InnoDB,

  PARTITION p201705 VALUES LESS THAN (201705) ENGINE = InnoDB,

  PARTITION p201706 VALUES LESS THAN (201706) ENGINE = InnoDB,

  PARTITION p201707 VALUES LESS THAN (201707) ENGINE = InnoDB,

  PARTITION p201708 VALUES LESS THAN (201708) ENGINE = InnoDB,

  PARTITION p201709 VALUES LESS THAN (201709) ENGINE = InnoDB,

  PARTITION p201710 VALUES LESS THAN (201710) ENGINE = InnoDB,

  PARTITION p201711 VALUES LESS THAN (201711) ENGINE = InnoDB,

  PARTITION p201712 VALUES LESS THAN (201712) ENGINE = InnoDB,

  PARTITION p201801 VALUES LESS THAN (201801) ENGINE = InnoDB,

  PARTITION p201802 VALUES LESS THAN (201802) ENGINE = InnoDB,

  PARTITION p201803 VALUES LESS THAN (201803) ENGINE = InnoDB,

  PARTITION p201804 VALUES LESS THAN (201804) ENGINE = InnoDB,

  PARTITION p201805 VALUES LESS THAN (201805) ENGINE = InnoDB,

  PARTITION p201806 VALUES LESS THAN (201806) ENGINE = InnoDB,

  PARTITION p201807 VALUES LESS THAN (201807) ENGINE = InnoDB,

  PARTITION p201808 VALUES LESS THAN (201808) ENGINE = InnoDB,

  PARTITION p201809 VALUES LESS THAN (201809) ENGINE = InnoDB,

  PARTITION p201810 VALUES LESS THAN (201810) ENGINE = InnoDB,

  PARTITION p201811 VALUES LESS THAN (201811) ENGINE = InnoDB,

  PARTITION p201812 VALUES LESS THAN (201812) ENGINE = InnoDB,

  PARTITION p201901 VALUES LESS THAN (201901) ENGINE = InnoDB,

  PARTITION p201902 VALUES LESS THAN (201902) ENGINE = InnoDB,

  PARTITION p201903 VALUES LESS THAN (201903) ENGINE = InnoDB,

  PARTITION p201904 VALUES LESS THAN (201904) ENGINE = InnoDB,

  PARTITION p201905 VALUES LESS THAN (201905) ENGINE = InnoDB,

  PARTITION p201906 VALUES LESS THAN (201906) ENGINE = InnoDB,

  PARTITION p201907 VALUES LESS THAN (201907) ENGINE = InnoDB,

  PARTITION p201908 VALUES LESS THAN (201908) ENGINE = InnoDB,

  PARTITION p201909 VALUES LESS THAN (201909) ENGINE = InnoDB,

  PARTITION p201910 VALUES LESS THAN (201910) ENGINE = InnoDB,

  PARTITION p201911 VALUES LESS THAN (201911) ENGINE = InnoDB,

  PARTITION p201912 VALUES LESS THAN (201912) ENGINE = InnoDB,

  PARTITION p202001 VALUES LESS THAN (202001) ENGINE = InnoDB,

  PARTITION p202002 VALUES LESS THAN (202002) ENGINE = InnoDB,

  PARTITION p202003 VALUES LESS THAN (202003) ENGINE = InnoDB,

  PARTITION p202004 VALUES LESS THAN (202004) ENGINE = InnoDB,

  PARTITION p202005 VALUES LESS THAN (202005) ENGINE = InnoDB,

  PARTITION p202006 VALUES LESS THAN (202006) ENGINE = InnoDB,

  PARTITION p202007 VALUES LESS THAN (202007) ENGINE = InnoDB,

  PARTITION p202008 VALUES LESS THAN (202008) ENGINE = InnoDB,

  PARTITION p202009 VALUES LESS THAN (202009) ENGINE = InnoDB,

  PARTITION p202010 VALUES LESS THAN (202010) ENGINE = InnoDB,

  PARTITION p202011 VALUES LESS THAN (202011) ENGINE = InnoDB,

  PARTITION p202012 VALUES LESS THAN (202012) ENGINE = InnoDB,

  PARTITION p202101 VALUES LESS THAN (202101) ENGINE = InnoDB,

  PARTITION p202102 VALUES LESS THAN (202102) ENGINE = InnoDB,

  PARTITION p202103 VALUES LESS THAN (202103) ENGINE = InnoDB,

  PARTITION p202104 VALUES LESS THAN (202104) ENGINE = InnoDB,

  PARTITION p202105 VALUES LESS THAN (202105) ENGINE = InnoDB,

  PARTITION p202106 VALUES LESS THAN (202106) ENGINE = InnoDB,

  PARTITION p202107 VALUES LESS THAN (202107) ENGINE = InnoDB,

  PARTITION p202108 VALUES LESS THAN (202108) ENGINE = InnoDB,

  PARTITION p202109 VALUES LESS THAN (202109) ENGINE = InnoDB,

  PARTITION p202110 VALUES LESS THAN (202110) ENGINE = InnoDB,

  PARTITION p202111 VALUES LESS THAN (202111) ENGINE = InnoDB,

  PARTITION p202112 VALUES LESS THAN (202112) ENGINE = InnoDB,

  PARTITION pcatchall VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

```

   explain partitions的结果：

 

![](http://lixuanbin.github.io/img/ref-img/20160707-1.jpg)

   Google一番后改成以下写法：

 

```sql

CREATE TABLE dataservice.`tblogin_20007` (

   `logid` int(11) NOT NULL AUTO_INCREMENT,

   `appId` varchar(20) NOT NULL,

   `other_fields_blah_blah` varchar(200) NOT NULL,

   `reportTime` datetime NOT NULL,

   `dtUpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

   PRIMARY KEY (`logid`,`reportTime`)

 ) ENGINE=InnoDB AUTO_INCREMENT=97662 DEFAULT CHARSET=utf8

 PARTITION BY RANGE (TO_DAYS(reportTime))

 (PARTITION p201407 VALUES LESS THAN (TO_DAYS('2014-07-01')) ENGINE = InnoDB,

  PARTITION p201408 VALUES LESS THAN (TO_DAYS('2014-08-01')) ENGINE = InnoDB,

  PARTITION p201409 VALUES LESS THAN (TO_DAYS('2014-09-01')) ENGINE = InnoDB,

  PARTITION p201410 VALUES LESS THAN (TO_DAYS('2014-10-01')) ENGINE = InnoDB,

  PARTITION p201411 VALUES LESS THAN (TO_DAYS('2014-11-01')) ENGINE = InnoDB,

  PARTITION p201412 VALUES LESS THAN (TO_DAYS('2014-12-01')) ENGINE = InnoDB,

  PARTITION p201501 VALUES LESS THAN (TO_DAYS('2015-01-01')) ENGINE = InnoDB,

  PARTITION p201502 VALUES LESS THAN (TO_DAYS('2015-02-01')) ENGINE = InnoDB,

  PARTITION p201503 VALUES LESS THAN (TO_DAYS('2015-03-01')) ENGINE = InnoDB,

  PARTITION p201504 VALUES LESS THAN (TO_DAYS('2015-04-01')) ENGINE = InnoDB,

  PARTITION p201505 VALUES LESS THAN (TO_DAYS('2015-05-01')) ENGINE = InnoDB,

  PARTITION p201506 VALUES LESS THAN (TO_DAYS('2015-06-01')) ENGINE = InnoDB,

  PARTITION p201507 VALUES LESS THAN (TO_DAYS('2015-07-01')) ENGINE = InnoDB,

  PARTITION p201508 VALUES LESS THAN (TO_DAYS('2015-08-01')) ENGINE = InnoDB,

  PARTITION p201509 VALUES LESS THAN (TO_DAYS('2015-09-01')) ENGINE = InnoDB,

  PARTITION p201510 VALUES LESS THAN (TO_DAYS('2015-10-01')) ENGINE = InnoDB,

  PARTITION p201511 VALUES LESS THAN (TO_DAYS('2015-11-01')) ENGINE = InnoDB,

  PARTITION p201512 VALUES LESS THAN (TO_DAYS('2015-12-01')) ENGINE = InnoDB,

  PARTITION p201601 VALUES LESS THAN (TO_DAYS('2016-01-01')) ENGINE = InnoDB,

  PARTITION p201602 VALUES LESS THAN (TO_DAYS('2016-02-01')) ENGINE = InnoDB,

  PARTITION p201603 VALUES LESS THAN (TO_DAYS('2016-03-01')) ENGINE = InnoDB,

  PARTITION p201604 VALUES LESS THAN (TO_DAYS('2016-04-01')) ENGINE = InnoDB,

  PARTITION p201605 VALUES LESS THAN (TO_DAYS('2016-05-01')) ENGINE = InnoDB,

  PARTITION p201606 VALUES LESS THAN (TO_DAYS('2016-06-01')) ENGINE = InnoDB,

  PARTITION p201607 VALUES LESS THAN (TO_DAYS('2016-07-01')) ENGINE = InnoDB,

  PARTITION p201608 VALUES LESS THAN (TO_DAYS('2016-08-01')) ENGINE = InnoDB,

  PARTITION p201609 VALUES LESS THAN (TO_DAYS('2016-09-01')) ENGINE = InnoDB,

  PARTITION p201610 VALUES LESS THAN (TO_DAYS('2016-10-01')) ENGINE = InnoDB,

  PARTITION p201611 VALUES LESS THAN (TO_DAYS('2016-11-01')) ENGINE = InnoDB,

  PARTITION p201612 VALUES LESS THAN (TO_DAYS('2016-12-01')) ENGINE = InnoDB,

  PARTITION p201701 VALUES LESS THAN (TO_DAYS('2017-01-01')) ENGINE = InnoDB,

  PARTITION p201702 VALUES LESS THAN (TO_DAYS('2017-02-01')) ENGINE = InnoDB,

  PARTITION p201703 VALUES LESS THAN (TO_DAYS('2017-03-01')) ENGINE = InnoDB,

  PARTITION p201704 VALUES LESS THAN (TO_DAYS('2017-04-01')) ENGINE = InnoDB,

  PARTITION p201705 VALUES LESS THAN (TO_DAYS('2017-05-01')) ENGINE = InnoDB,

  PARTITION p201706 VALUES LESS THAN (TO_DAYS('2017-06-01')) ENGINE = InnoDB,

  PARTITION p201707 VALUES LESS THAN (TO_DAYS('2017-07-01')) ENGINE = InnoDB,

  PARTITION p201708 VALUES LESS THAN (TO_DAYS('2017-08-01')) ENGINE = InnoDB,

  PARTITION p201709 VALUES LESS THAN (TO_DAYS('2017-09-01')) ENGINE = InnoDB,

  PARTITION p201710 VALUES LESS THAN (TO_DAYS('2017-10-01')) ENGINE = InnoDB,

  PARTITION p201711 VALUES LESS THAN (TO_DAYS('2017-11-01')) ENGINE = InnoDB,

  PARTITION p201712 VALUES LESS THAN (TO_DAYS('2017-12-01')) ENGINE = InnoDB,

  PARTITION p201801 VALUES LESS THAN (TO_DAYS('2018-01-01')) ENGINE = InnoDB,

  PARTITION p201802 VALUES LESS THAN (TO_DAYS('2018-02-01')) ENGINE = InnoDB,

  PARTITION p201803 VALUES LESS THAN (TO_DAYS('2018-03-01')) ENGINE = InnoDB,

  PARTITION p201804 VALUES LESS THAN (TO_DAYS('2018-04-01')) ENGINE = InnoDB,

  PARTITION p201805 VALUES LESS THAN (TO_DAYS('2018-05-01')) ENGINE = InnoDB,

  PARTITION p201806 VALUES LESS THAN (TO_DAYS('2018-06-01')) ENGINE = InnoDB,

  PARTITION p201807 VALUES LESS THAN (TO_DAYS('2018-07-01')) ENGINE = InnoDB,

  PARTITION p201808 VALUES LESS THAN (TO_DAYS('2018-08-01')) ENGINE = InnoDB,

  PARTITION p201809 VALUES LESS THAN (TO_DAYS('2018-09-01')) ENGINE = InnoDB,

  PARTITION p201810 VALUES LESS THAN (TO_DAYS('2018-10-01')) ENGINE = InnoDB,

  PARTITION p201811 VALUES LESS THAN (TO_DAYS('2018-11-01')) ENGINE = InnoDB,

  PARTITION p201812 VALUES LESS THAN (TO_DAYS('2018-12-01')) ENGINE = InnoDB,

  PARTITION p201901 VALUES LESS THAN (TO_DAYS('2019-01-01')) ENGINE = InnoDB,

  PARTITION p201902 VALUES LESS THAN (TO_DAYS('2019-02-01')) ENGINE = InnoDB,

  PARTITION p201903 VALUES LESS THAN (TO_DAYS('2019-03-01')) ENGINE = InnoDB,

  PARTITION p201904 VALUES LESS THAN (TO_DAYS('2019-04-01')) ENGINE = InnoDB,

  PARTITION p201905 VALUES LESS THAN (TO_DAYS('2019-05-01')) ENGINE = InnoDB,

  PARTITION p201906 VALUES LESS THAN (TO_DAYS('2019-06-01')) ENGINE = InnoDB,

  PARTITION p201907 VALUES LESS THAN (TO_DAYS('2019-07-01')) ENGINE = InnoDB,

  PARTITION p201908 VALUES LESS THAN (TO_DAYS('2019-08-01')) ENGINE = InnoDB,

  PARTITION p201909 VALUES LESS THAN (TO_DAYS('2019-09-01')) ENGINE = InnoDB,

  PARTITION p201910 VALUES LESS THAN (TO_DAYS('2019-10-01')) ENGINE = InnoDB,

  PARTITION p201911 VALUES LESS THAN (TO_DAYS('2019-11-01')) ENGINE = InnoDB,

  PARTITION p201912 VALUES LESS THAN (TO_DAYS('2019-12-01')) ENGINE = InnoDB,

  PARTITION p202001 VALUES LESS THAN (TO_DAYS('2020-01-01')) ENGINE = InnoDB,

  PARTITION p202002 VALUES LESS THAN (TO_DAYS('2020-02-01')) ENGINE = InnoDB,

  PARTITION p202003 VALUES LESS THAN (TO_DAYS('2020-03-01')) ENGINE = InnoDB,

  PARTITION p202004 VALUES LESS THAN (TO_DAYS('2020-04-01')) ENGINE = InnoDB,

  PARTITION p202005 VALUES LESS THAN (TO_DAYS('2020-05-01')) ENGINE = InnoDB,

  PARTITION p202006 VALUES LESS THAN (TO_DAYS('2020-06-01')) ENGINE = InnoDB,

  PARTITION p202007 VALUES LESS THAN (TO_DAYS('2020-07-01')) ENGINE = InnoDB,

  PARTITION p202008 VALUES LESS THAN (TO_DAYS('2020-08-01')) ENGINE = InnoDB,

  PARTITION p202009 VALUES LESS THAN (TO_DAYS('2020-09-01')) ENGINE = InnoDB,

  PARTITION p202010 VALUES LESS THAN (TO_DAYS('2020-10-01')) ENGINE = InnoDB,

  PARTITION p202011 VALUES LESS THAN (TO_DAYS('2020-11-01')) ENGINE = InnoDB,

  PARTITION p202012 VALUES LESS THAN (TO_DAYS('2020-12-01')) ENGINE = InnoDB,

  PARTITION p202101 VALUES LESS THAN (TO_DAYS('2021-01-01')) ENGINE = InnoDB,

  PARTITION p202102 VALUES LESS THAN (TO_DAYS('2021-02-01')) ENGINE = InnoDB,

  PARTITION p202103 VALUES LESS THAN (TO_DAYS('2021-03-01')) ENGINE = InnoDB,

  PARTITION p202104 VALUES LESS THAN (TO_DAYS('2021-04-01')) ENGINE = InnoDB,

  PARTITION p202105 VALUES LESS THAN (TO_DAYS('2021-05-01')) ENGINE = InnoDB,

  PARTITION p202106 VALUES LESS THAN (TO_DAYS('2021-06-01')) ENGINE = InnoDB,

  PARTITION p202107 VALUES LESS THAN (TO_DAYS('2021-07-01')) ENGINE = InnoDB,

  PARTITION p202108 VALUES LESS THAN (TO_DAYS('2021-08-01')) ENGINE = InnoDB,

  PARTITION p202109 VALUES LESS THAN (TO_DAYS('2021-09-01')) ENGINE = InnoDB,

  PARTITION p202110 VALUES LESS THAN (TO_DAYS('2021-10-01')) ENGINE = InnoDB,

  PARTITION p202111 VALUES LESS THAN (TO_DAYS('2021-11-01')) ENGINE = InnoDB,

  PARTITION p202112 VALUES LESS THAN (TO_DAYS('2021-12-01')) ENGINE = InnoDB,

  PARTITION pcatchall VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

```

   更改后的查询执行计划：

 

![](http://lixuanbin.github.io/img/ref-img/20160707-2.jpg)

   咋一看好像是对了，裁剪了不少分区呢！但是仔细一瞧，尼玛，查询日期范围和分区块没对上号！

再Google一番，发现如果是MySQL 5.6.2以上的版本，可以在查询时候指定分区块进行手动裁剪：

![](http://lixuanbin.github.io/img/ref-img/20160707-3.jpg)

   再看看生产上的MySQL版本：

![](http://lixuanbin.github.io/img/ref-img/20160707-4.jpg)

   我顿时就呵呵了。。。

![](http://lixuanbin.github.io/img/ref-img/20160707-5.jpg)

   跟DBA交流了一下，他也没说出个所以然。再向电商那边的专家老张和老咸请教了下，发现他们以前也遇到过类似的坑，他们的做法是使用LIST分区替代RANGE分区，在原表新增个整型字段用于标识分区位置：

 

```sql

CREATE TABLE dataservice.webgame_channel_new_user_new2 (

  `passport` varchar(255) NOT NULL,

  `dtStatDate` date DEFAULT NULL,

  `partMonth` INT NOT NULL COMMENT 'yyyyMM',

  `channel` varchar(255) DEFAULT NULL,

  KEY `pdc` (`passport`,`dtStatDate`,`channel`, `partMonth`),

  KEY `date_idx` (`dtStatDate`),

  KEY `date_channel_idx` (`channel`,`dtStatDate`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY LIST(partMonth) PARTITIONS 91

(PARTITION p201407 VALUES IN (201407) ,

 PARTITION p201408 VALUES IN (201408) ,

 PARTITION p201409 VALUES IN (201409) ,

 PARTITION p201410 VALUES IN (201410) ,

 PARTITION p201411 VALUES IN (201411) ,

 PARTITION p201412 VALUES IN (201412) ,

 PARTITION p201501 VALUES IN (201501) ,

 PARTITION p201502 VALUES IN (201502) ,

 PARTITION p201503 VALUES IN (201503) ,

 PARTITION p201504 VALUES IN (201504) ,

 PARTITION p201505 VALUES IN (201505) ,

 PARTITION p201506 VALUES IN (201506) ,

 PARTITION p201507 VALUES IN (201507) ,

 PARTITION p201508 VALUES IN (201508) ,

 PARTITION p201509 VALUES IN (201509) ,

 PARTITION p201510 VALUES IN (201510) ,

 PARTITION p201511 VALUES IN (201511) ,

 PARTITION p201512 VALUES IN (201512) ,

 PARTITION p201601 VALUES IN (201601) ,

 PARTITION p201602 VALUES IN (201602) ,

 PARTITION p201603 VALUES IN (201603) ,

 PARTITION p201604 VALUES IN (201604) ,

 PARTITION p201605 VALUES IN (201605) ,

 PARTITION p201606 VALUES IN (201606) ,

 PARTITION p201607 VALUES IN (201607) ,

 PARTITION p201608 VALUES IN (201608) ,

 PARTITION p201609 VALUES IN (201609) ,

 PARTITION p201610 VALUES IN (201610) ,

 PARTITION p201611 VALUES IN (201611) ,

 PARTITION p201612 VALUES IN (201612) ,

 PARTITION p201701 VALUES IN (201701) ,

 PARTITION p201702 VALUES IN (201702) ,

 PARTITION p201703 VALUES IN (201703) ,

 PARTITION p201704 VALUES IN (201704) ,

 PARTITION p201705 VALUES IN (201705) ,

 PARTITION p201706 VALUES IN (201706) ,

 PARTITION p201707 VALUES IN (201707) ,

 PARTITION p201708 VALUES IN (201708) ,

 PARTITION p201709 VALUES IN (201709) ,

 PARTITION p201710 VALUES IN (201710) ,

 PARTITION p201711 VALUES IN (201711) ,

 PARTITION p201712 VALUES IN (201712) ,

 PARTITION p201801 VALUES IN (201801) ,

 PARTITION p201802 VALUES IN (201802) ,

 PARTITION p201803 VALUES IN (201803) ,

 PARTITION p201804 VALUES IN (201804) ,

 PARTITION p201805 VALUES IN (201805) ,

 PARTITION p201806 VALUES IN (201806) ,

 PARTITION p201807 VALUES IN (201807) ,

 PARTITION p201808 VALUES IN (201808) ,

 PARTITION p201809 VALUES IN (201809) ,

 PARTITION p201810 VALUES IN (201810) ,

 PARTITION p201811 VALUES IN (201811) ,

 PARTITION p201812 VALUES IN (201812) ,

 PARTITION p201901 VALUES IN (201901) ,

 PARTITION p201902 VALUES IN (201902) ,

 PARTITION p201903 VALUES IN (201903) ,

 PARTITION p201904 VALUES IN (201904) ,

 PARTITION p201905 VALUES IN (201905) ,

 PARTITION p201906 VALUES IN (201906) ,

 PARTITION p201907 VALUES IN (201907) ,

 PARTITION p201908 VALUES IN (201908) ,

 PARTITION p201909 VALUES IN (201909) ,

 PARTITION p201910 VALUES IN (201910) ,

 PARTITION p201911 VALUES IN (201911) ,

 PARTITION p201912 VALUES IN (201912) ,

 PARTITION p202001 VALUES IN (202001) ,

 PARTITION p202002 VALUES IN (202002) ,

 PARTITION p202003 VALUES IN (202003) ,

 PARTITION p202004 VALUES IN (202004) ,

 PARTITION p202005 VALUES IN (202005) ,

 PARTITION p202006 VALUES IN (202006) ,

 PARTITION p202007 VALUES IN (202007) ,

 PARTITION p202008 VALUES IN (202008) ,

 PARTITION p202009 VALUES IN (202009) ,

 PARTITION p202010 VALUES IN (202010) ,

 PARTITION p202011 VALUES IN (202011) ,

 PARTITION p202012 VALUES IN (202012) ,

 PARTITION p202101 VALUES IN (202101) ,

 PARTITION p202102 VALUES IN (202102) ,

 PARTITION p202103 VALUES IN (202103) ,

 PARTITION p202104 VALUES IN (202104) ,

 PARTITION p202105 VALUES IN (202105) ,

 PARTITION p202106 VALUES IN (202106) ,

 PARTITION p202107 VALUES IN (202107) ,

 PARTITION p202108 VALUES IN (202108) ,

 PARTITION p202109 VALUES IN (202109) ,

 PARTITION p202110 VALUES IN (202110) ,

 PARTITION p202111 VALUES IN (202111) ,

 PARTITION p202112 VALUES IN (202112) ,

 PARTITION pcatchall VALUES IN (202201) );

```

   查询写法以及执行计划：

![](http://lixuanbin.github.io/img/ref-img/20160707-6.jpg)

 

小结一下：

   如果条件许可就采用升级数据库版本的方法，只需改动原有的查询语句手动指定对应分区；实在不行就采用后一种法子，但是多了个冗余字段，插入和查询时候都要做多点工作。

相关标签： MySQL partition range 分区

上一篇： oracle报错 ora-14300:partition key maps to a partition outside maximum pemitted nu

下一篇： Mysql 分区详解

推荐阅读