`
lixuanbin
  • 浏览: 136078 次
  • 性别: Icon_minigender_1
  • 来自: 广州
社区版块
存档分类
最新评论

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

MySQLpartitionrange分区 
阅读更多

---

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)

 

小结一下：

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

0
顶
2
踩
分享到：
| 利用反射获得数据源连接信息
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    mysql实现自动创建与删除分区

    实现mysql按时间分区方式自动创建与删除分区，包括创建/删除日志记录，通过存储过程与事件联合实现，自动创建数量与删除数量可动态配置

    mysql 实现定时给表追加分区

    实现mysql 每天定时自动给数据库表追加分区，包含存储计划和存储过程

    mysql 数据库表分区

    php for mysql的表分区类,为mysql的大数据查询提供分区支持. 该类包含追加 创建 删除分区方法.可以通过索引 分类等字段为条件对数据库物理文件分离. 数据表的查询不受任何影响

    MySQL分区表自动创建及删除存储过程

    用存储过程实现了MySQL数据库分区表的自动创建和自动删除功能。亲测有效。希望有用。

    创建mysql表分区的方法

    表分区是最近才知道的哦 ，以前自己做都是分表来实现上亿级别的数据了，下面我来给大家介绍一下mysql表分区创建与使用吧，希望对各位同学会有所帮助。表分区的测试使用，主要内容来自于其他博客文章以及mysql5.1的...

    MySQL分表和分区最佳攻略word

    MySQL分表和分区最佳攻略 word版本，

    varchar日期字段分区demo

    Range分区：最为常用，基于属于一个给定连续区间的列值，把多行分配给分区。最常见的是基于时间字段. 基于分区的列最好是整型，如果日期型的可以使用函数转换为整型。varchar日期字段分区sql demo

    mysql数据库表分区教程

    mysql数据库表分区教程，详细说明mysql表分区的每一个步骤

    MySQL数据库分区技术

    MySQL数据库分区技术，解决多并发问题，详细描述分区流程及操作步骤

    mysql表分区

    mysql表分区策略，包含range分区、list分区、hash分区等方法介绍及详解

    MySQL分区分表方案实践手册

    MySQL分区分表方案实践手册 MySQL分区分表方案实践手册 MySQL分区分表方案实践手册 MySQL分区分表方案实践手册 仅供个人学习, 禁止转载及其他商业用途.

    MYSQL分区表测试

    MYSQL分区表测试过程详细说明。MYSQL分区表测试过程详细说明。

    Mysql主从与分区技术.pdf

    mysql主从复制与分区技术： 主讲：李健； QQ：89267659； 1、mysql用户授权； 2、mysql bin-log日志； 3、mysql主从复制； 4、mysql分区技术；

    MySQL日期查询[定义].pdf

    MySQL日期查询[定义].pdf

    Mysql 时间模糊查询

    Mysql 时间模糊查询，不同场景的查询方式，根据条件查询

    MYSQL 日期函数大全

    MYSQL 日期函数大全，供大家一起共同分享学习。

    MySQL分表和分区最佳攻略

    这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。

    MySQL自动分区扩展

    最近需要把一个oralce数据库向mysql数据库移植。oracle库用到了job和存储过程，因为mysql中没有job，所有要用新功能event代替一下，这个是5.1以后才有的新功能，所以稍微研究了一下。

    互联网公司为啥不使用mysql分区表

    互联网公司为啥不使用mysql分区表

Magicbox
Global site tag (gtag.js) - Google Analytics