`
lixuanbin
  • 浏览: 137713 次
  • 性别: 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分区分表方案实践手册

    9. **分区裁剪**：优化器会自动剔除不相关的分区，但某些复杂查询可能无法充分利用分区裁剪，需要通过调整SQL语句来优化。 总之，《MySQL分区分表方案实践手册》将引导读者全面了解MySQL分区的理论与实践，通过实例...

    hive数据分区时分区字段不可为中文。.doc

    - **提高查询性能**：利用分区裁剪技术，可以在查询执行阶段排除不相关的分区，显著提升查询速度。 - **简化数据管理**：对于大量数据的管理变得更加简单，如删除旧数据或导入新数据等操作仅涉及特定分区。 #### ...

    mysql-installer-community-5.6.msi安装版

    - **分区优化**：MySQL 5.6 对分区表的支持更加完善，包括对分区表的索引优化、分区裁剪等，这些改进使得分区表的查询速度更快。 - **InnoDB 存储引擎增强**：InnoDB 是 MySQL 最常用的事务安全存储引擎之一，在 ...

    MySQL精简版合集

    MySQL是一种广泛使用的开源关系型数据库管理系统（RDBMS），它以其高效、稳定和灵活的特点，在全球范围内被众多企业和开发者所采用。本合集包含了不同版本的MySQL，包括MariaDB精简版以及MySQL5.1和5.5的精简版。...

    自己制作的MySql5精简版，只有4.5M

    7. **管理工具**：虽然精简版可能不包含图形化的数据库管理工具，如MySQL Workbench，但依然可以通过命令行工具（如`mysql.exe`）进行数据库的创建、查询、更新等操作。对于熟悉SQL的用户来说，这种方式足够高效。 ...

    Prometheus监控MySQL(ECS版)

    6. **可扩展性**：支持多实例联邦集群，通过功能分区和联邦集群，能够在需求增加时平滑扩展。 7. **易于集成**：Prometheus的广泛社区支持和丰富的生态系统使得与其他系统集成变得简单。 **监控MySQL** 对于ECS版...

    纯真ip库2017年1月20最新，已经转化成mysql数据表格式

    同时，了解如何在MySQL中处理大数据和性能优化也是很重要的，例如使用索引、分区等技术。 总的来说，纯真IP库2017年1月20日的版本提供了一种方便的方式来管理和查询IP地址信息，特别是在MySQL环境中。通过学习和...

    hive-0.8.1.tar.gz.zip下载

    用户可以通过自定义分区、分区裁剪或者使用动态分区重写等方法来缓解数据倾斜。 7. **并行执行**：Hive-0.8.1支持并行执行优化，能够同时运行多个MapReduce任务，从而加快查询速度。然而，由于依赖于Hadoop的调度...

    ClickHouse入门学习笔记

    - 单表查询的优化技巧，如使用`PREWHERE`、数据采样、列裁剪和分区裁剪。 - 多表关联优化，包括`IN`替代`JOIN`，小表在右原则，谓词下推等。 - 使用分布式表和全局关键字`GLOBAL`。 12. **数据一致性**： - ...

    hive 操作相关的测试数据集

    6. **查询优化**：Hive支持多种查询优化策略，如谓词下推、Join重写、分区裁剪等。在处理大量数据时，合理使用这些策略可以显著提高查询效率。 7. **分区与桶**：分区有助于减少查询时的数据扫描量，而桶则是基于...

    内容介绍了SparkSql概述，参数调优，逻辑优化及数栈问题案例。

    解决小文件问题通常涉及合并文件、调整分区策略或使用工具如Hadoop的`merge`命令。 总之，SparkSQL是大数据处理领域的一个强大工具，它提供了高效的数据处理能力、灵活的数据源支持和丰富的优化手段。通过深入理解...

    hive常用优化方法大全共2页.pdf.zip

    3. **倾斜表处理**：数据倾斜是指某些分区或键值拥有远超平均的数据量，导致查询不均衡。解决方法包括重新分布数据、使用动态分区、或调整join操作。 4. **数据压缩**：启用数据压缩可以减少HDFS的存储需求，同时在...

    Hive学习资料

    - **分区裁剪**：在查询时通过指定条件自动排除不相关的分区，从而减少数据扫描量。 - **动态分区**：在执行INSERT语句时动态确定目标分区，提高数据加载的灵活性。 7. **最佳实践**： - **小文件问题**：避免...

    ClickHouse--day02--企业最佳实战.pdf

    数据排序使得某些类型的查询（如范围查询）更快，而数据分区分片和分布式查询则支持大规模数据处理和扩展性。预聚合和向量化引擎利用了CPU特性，进一步加速计算。ClickHouse 提供了多种索引类型，包括主键索引、二级...

    Hive 编程指南

    - **分区裁剪**：通过WHERE子句限制只读取特定分区，减少IO。 - **桶表扫描**：利用桶的排序性质优化JOIN操作。 - **数据倾斜**：处理数据分布不均的问题，可能需要重新分区或桶化。 - **MapReduce优化**：调整...

    HiveSQL使用考核题.zip

    - **分区裁剪**：利用WHERE子句中的分区信息，减少扫描的数据量。 - **桶表JOIN优化**：当JOIN操作的两个表都进行了桶化，可以实现快速JOIN。 - **减少MR任务数**：通过`SET hive.exec.reducers.bytes.per....

    apache-hive-0.14.0-bin.tar.gz

    - **优化的分区策略**：改进了动态分区和分区裁剪，减少了不必要的数据扫描。 - **Hive-on-MR3**：支持使用YARN（Yet Another Resource Negotiator）作为资源调度器，提高了资源利用率和集群管理能力。 通过这些...

    Hive数据仓库全套电子课件完整版ppt整本书电子教案最全教学教程.pptx

    Hive 提供了优化器来改进查询性能，例如通过分区裁剪、谓词下推等策略。 4. **Hive 与数据库比较**：Hive 不是实时的数据库，它适合批处理分析，而不适合频繁的更新和插入操作。此外，Hive 依赖于 HDFS，因此它的...

    presto-hive-cdh4-0.69.zip

    此外，Presto支持并行查询和分区裁剪，能够有效利用硬件资源，提高查询速度。 总的来说，Presto-Hive-CDH4是大数据查询的一个强大工具，它的开源性质使得用户可以自由地进行扩展和定制。通过深入理解Presto的架构和...

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