TIDB分区查询问题

leojiang · 2020 年8 月 4 日 02:15

前提：

1、版本tidb4.0.0，CentOS Linux release 7.6.1810 (Core)
2、集群使用的 tiup 部署
3、生产环境

疑问：

1、分区表dc_sale根据时间进行分区，字段类型为date
2、tidb查看分区，where以data条件按yy-mm-dd查询走分区，按yy-mm-dd hh:mi:ss查询全表扫不走分区,
以下是验证
3、想问下这种情况后续tidb版本是否会做兼容改进。按date类型分区按yy-mm-dd hh:mi:ss查询分区也生效
– tidb查看分区，where以data条件按yy-mm-dd查询走分区

EXPLAIN SELECT
	* 
FROM
	deliver_sale 
WHERE
	seller_date BETWEEN '2020-08-03' 
	AND '2020-08-04';

– 按yy-mm-dd hh:mi:ss查询全表扫不走分区

explain SELECT 
    *
FROM
    deliver_sale
WHERE
    seller_date BETWEEN '2018-08-03 00:00:00' AND '2020-08-03 00:00:00';

这道题我不会 · 2020 年8 月 4 日 02:34

麻烦提供下表结构信息，我们这边内部复现下，谢谢！

leojiang · 2020 年8 月 4 日 02:35

CREATE TABLE `deliver_sale` (
  `code` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '客户编码',
  `name` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '客户名称',
  `file_id` varchar(500) COLLATE utf8_bin DEFAULT NULL COMMENT '原始文件ID',
  `seller_date` date NOT NULL COMMENT '销售日期',
  PRIMARY KEY (`seller_date`),
  KEY `index_source_file_id` (`file_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='销售数据'
PARTITION BY RANGE ( TO_DAYS(`seller_date`) ) (
  PARTITION `p200001` VALUES LESS THAN (730485),
  PARTITION `p201701` VALUES LESS THAN (736726),
  PARTITION `p201702` VALUES LESS THAN (736754),
  PARTITION `p201703` VALUES LESS THAN (736785),
  PARTITION `p201704` VALUES LESS THAN (736815),
  PARTITION `p201705` VALUES LESS THAN (736846),
  PARTITION `p201706` VALUES LESS THAN (736876),
  PARTITION `p201707` VALUES LESS THAN (736907),
  PARTITION `p201708` VALUES LESS THAN (736938),
  PARTITION `p201709` VALUES LESS THAN (736968),
  PARTITION `p201710` VALUES LESS THAN (736999),
  PARTITION `p201711` VALUES LESS THAN (737029),
  PARTITION `p201712` VALUES LESS THAN (737060),
  PARTITION `p201801` VALUES LESS THAN (737091),
  PARTITION `p201802` VALUES LESS THAN (737119),
  PARTITION `p201803` VALUES LESS THAN (737150),
  PARTITION `p201804` VALUES LESS THAN (737180),
  PARTITION `p201805` VALUES LESS THAN (737211),
  PARTITION `p201806` VALUES LESS THAN (737241),
  PARTITION `p201807` VALUES LESS THAN (737272),
  PARTITION `p201808` VALUES LESS THAN (737303),
  PARTITION `p201809` VALUES LESS THAN (737333),
  PARTITION `p201810` VALUES LESS THAN (737364),
  PARTITION `p201811` VALUES LESS THAN (737394),
  PARTITION `p201812` VALUES LESS THAN (737425),
  PARTITION `p201901` VALUES LESS THAN (737456),
  PARTITION `p201902` VALUES LESS THAN (737484),
  PARTITION `p201903` VALUES LESS THAN (737515),
  PARTITION `p201904` VALUES LESS THAN (737545),
  PARTITION `p201905` VALUES LESS THAN (737576),
  PARTITION `p201906` VALUES LESS THAN (737606),
  PARTITION `p201907` VALUES LESS THAN (737637),
  PARTITION `p201908` VALUES LESS THAN (737668),
  PARTITION `p201909` VALUES LESS THAN (737698),
  PARTITION `p201910` VALUES LESS THAN (737729),
  PARTITION `p201911` VALUES LESS THAN (737759),
  PARTITION `p201912` VALUES LESS THAN (737790),
  PARTITION `p202001` VALUES LESS THAN (737821),
  PARTITION `p202002` VALUES LESS THAN (737850),
  PARTITION `p202003` VALUES LESS THAN (737881),
  PARTITION `p202004` VALUES LESS THAN (737911),
  PARTITION `p202005` VALUES LESS THAN (737942),
  PARTITION `p202006` VALUES LESS THAN (737972),
  PARTITION `p202007` VALUES LESS THAN (738003),
  PARTITION `p202008` VALUES LESS THAN (738034),
  PARTITION `p202009` VALUES LESS THAN (738064),
  PARTITION `p202010` VALUES LESS THAN (738095),
  PARTITION `p202011` VALUES LESS THAN (738125),
  PARTITION `p202012` VALUES LESS THAN (738156),
  PARTITION `p202101` VALUES LESS THAN (738187),
  PARTITION `p202102` VALUES LESS THAN (738215),
  PARTITION `p202103` VALUES LESS THAN (738246),
  PARTITION `p202104` VALUES LESS THAN (738276),
  PARTITION `p202105` VALUES LESS THAN (738307),
  PARTITION `p202106` VALUES LESS THAN (738337),
  PARTITION `p202107` VALUES LESS THAN (738368),
  PARTITION `p202108` VALUES LESS THAN (738399),
  PARTITION `p202109` VALUES LESS THAN (738429),
  PARTITION `p202110` VALUES LESS THAN (738460),
  PARTITION `p202111` VALUES LESS THAN (738490),
  PARTITION `p202112` VALUES LESS THAN (738521),
  PARTITION `p202201` VALUES LESS THAN (738552),
  PARTITION `p202202` VALUES LESS THAN (738580),
  PARTITION `p202203` VALUES LESS THAN (738611),
  PARTITION `p202204` VALUES LESS THAN (738641),
  PARTITION `p202205` VALUES LESS THAN (738672),
  PARTITION `p202206` VALUES LESS THAN (738702),
  PARTITION `p202207` VALUES LESS THAN (738733),
  PARTITION `p202208` VALUES LESS THAN (738764),
  PARTITION `p202209` VALUES LESS THAN (738794),
  PARTITION `p202210` VALUES LESS THAN (738825),
  PARTITION `p202211` VALUES LESS THAN (738855),
  PARTITION `p202212` VALUES LESS THAN (738886),
  PARTITION `p202301` VALUES LESS THAN (738917),
  PARTITION `p202302` VALUES LESS THAN (738945),
  PARTITION `p202303` VALUES LESS THAN (738976),
  PARTITION `p202304` VALUES LESS THAN (739006),
  PARTITION `p202305` VALUES LESS THAN (739037),
  PARTITION `p202306` VALUES LESS THAN (739067),
  PARTITION `p202307` VALUES LESS THAN (739098),
  PARTITION `p202308` VALUES LESS THAN (739129),
  PARTITION `p202309` VALUES LESS THAN (739159),
  PARTITION `p202310` VALUES LESS THAN (739190),
  PARTITION `p202311` VALUES LESS THAN (739220),
  PARTITION `p202312` VALUES LESS THAN (739251),
  PARTITION `p202401` VALUES LESS THAN (739282),
  PARTITION `p202402` VALUES LESS THAN (739311),
  PARTITION `p202403` VALUES LESS THAN (739342),
  PARTITION `p202404` VALUES LESS THAN (739372),
  PARTITION `p202405` VALUES LESS THAN (739403),
  PARTITION `p202406` VALUES LESS THAN (739433),
  PARTITION `p202407` VALUES LESS THAN (739464),
  PARTITION `p202408` VALUES LESS THAN (739495),
  PARTITION `p202409` VALUES LESS THAN (739525),
  PARTITION `p202410` VALUES LESS THAN (739556),
  PARTITION `p202411` VALUES LESS THAN (739586),
  PARTITION `p202412` VALUES LESS THAN (739617),
  PARTITION `p202501` VALUES LESS THAN (739648),
  PARTITION `p202502` VALUES LESS THAN (739676),
  PARTITION `p202503` VALUES LESS THAN (739707),
  PARTITION `p202504` VALUES LESS THAN (739737),
  PARTITION `p202505` VALUES LESS THAN (739768),
  PARTITION `p202506` VALUES LESS THAN (739798),
  PARTITION `p202507` VALUES LESS THAN (739829),
  PARTITION `p202508` VALUES LESS THAN (739860),
  PARTITION `p202509` VALUES LESS THAN (739890),
  PARTITION `p202510` VALUES LESS THAN (739921),
  PARTITION `p202511` VALUES LESS THAN (739951),
  PARTITION `p202512` VALUES LESS THAN (739982),
  PARTITION `p202601` VALUES LESS THAN (740013),
  PARTITION `p202602` VALUES LESS THAN (740041),
  PARTITION `p202603` VALUES LESS THAN (740072),
  PARTITION `p202604` VALUES LESS THAN (740102),
  PARTITION `p202605` VALUES LESS THAN (740133),
  PARTITION `p202606` VALUES LESS THAN (740163),
  PARTITION `p202607` VALUES LESS THAN (740194),
  PARTITION `p202608` VALUES LESS THAN (740225),
  PARTITION `p202609` VALUES LESS THAN (740255),
  PARTITION `p202610` VALUES LESS THAN (740286),
  PARTITION `p202611` VALUES LESS THAN (740316),
  PARTITION `p202612` VALUES LESS THAN (740347),
  PARTITION `p202701` VALUES LESS THAN (740378),
  PARTITION `p202702` VALUES LESS THAN (740406),
  PARTITION `p202703` VALUES LESS THAN (740437),
  PARTITION `p202704` VALUES LESS THAN (740467),
  PARTITION `p202705` VALUES LESS THAN (740498),
  PARTITION `p202706` VALUES LESS THAN (740528),
  PARTITION `p202707` VALUES LESS THAN (740559),
  PARTITION `p202708` VALUES LESS THAN (740590),
  PARTITION `p202709` VALUES LESS THAN (740620),
  PARTITION `p202710` VALUES LESS THAN (740651),
  PARTITION `p202711` VALUES LESS THAN (740681)
);

这道题我不会 · 2020 年8 月 4 日 02:50

内部测试了下，发现在 MySQL 8.0.21 中按yy-mm-dd hh:mi:ss查询也是全表扫不走分区，这个建议我这边先收集下哈，看下后面的版本中能否兼容这个需求。

leojiang · 2020 年8 月 4 日 02:57

好的多谢，因为我们生产环境，大批量使用的yy-mm-dd hh:mi:ss这种格式导致不走分区，新版4.0.4支持么？

这道题我不会 · 2020 年8 月 4 日 03:20

4.0.4 版本已经发布了，目前这一块应该还没有做调整，只能看下后面版本能否兼容这个需求了。

leojiang · 2020 年8 月 4 日 03:22

好嘞多谢

来了老弟 · 2020 年8 月 4 日 13:25

leojiang · 2020 年8 月 19 日 10:05

今天测试的时候发现，按yy-mm-dd hh:mi:ss查询是走分区的，是时间段选错了，跨了两年

explain SELECT 
    *
FROM
    deliver_sale
WHERE
    seller_date BETWEEN '2018-08-03 00:00:00' AND '2020-08-03 00:00:00';

来了老弟 · 2020 年8 月 19 日 10:58

收到，这边会评估这个需求，