优化器里能优先处理一下 时间函数吗？ DATE_ADD(NOW(), INTERVAL 2 DAY);

🪐 TiDB 技术问题 性能调优
,
1

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：
【 TiDB 使用环境】
V5.0.3

【概述】 场景 + 问题概述
EXPLAIN SELECT * FROM synrpt_tt_creative rtc WHERE rtc.date >= DATE_ADD(NOW(), INTERVAL -2 DAY)

遍历了所有分区 ，而不是我where条件希望走的当前月份分区 。 遍历所有分区 导致性能低下 。

image
image994×359 24.4 KB

如果优化器 能 先解析一下 DATE_ADD(NOW(), INTERVAL 2 DAY); 的具体值 只走相关的分区 性能就会好很多
image
image995×191 15.4 KB

【背景】 做过哪些操作

【现象】 业务和数据库现象
mysql分区表也一样 ：
这种对时间做函数的分区 或索引 好像不能指定具体分区 ，只有具体值才能用到分区
(user:mysqldba time: 15:50)[db: test]EXPLAIN SELECT * FROM synrpt_tt_creative rtc WHERE rtc.date >= ‘2021-08-10’ and date <‘2021-08-12’;
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| 1 | SIMPLE | rtc | p202108 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
1 row in set, 1 warning (0.00 sec)

(user:mysqldba time: 16:00)[db: test]EXPLAIN SELECT * FROM synrpt_tt_creative rtc WHERE rtc.date >= DATE_ADD(NOW(), INTERVAL -2 DAY) and rtc.date <DATE_ADD(NOW(), INTERVAL 2 DAY);
±—±------------±------±--------------------------------±-----±--------------±-----±--------±-----±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±--------------------------------±-----±--------------±-----±--------±-----±-----±---------±------------+
| 1 | SIMPLE | rtc | p202107,p202108,p202109,p202110 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
±—±------------±------±--------------------------------±-----±--------------±-----±--------±-----±-----±---------±------------+
1 row in set, 1 warning (0.00 sec)
【问题】 当前遇到的问题

【业务影响】
遍历所有分区 导致性能低下 。
【TiDB 版本】
5.0.3
【应用软件及版本】

【附件】 相关日志及配置信息

  • TiUP Cluster Display 信息
  • TiUP CLuster Edit config 信息

监控（https://metricstool.pingcap.com/)

  • TiDB-Overview Grafana监控
  • TiDB Grafana 监控
  • TiKV Grafana 监控
  • PD Grafana 监控
  • 对应模块日志（包含问题前后 1 小时日志）

若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出的打印结果，请务必全选并复制粘贴上传。

2 个赞
2

tidb应该暂时不支持函数索引

1 个赞
3

我记得mysql8.0支持 你可以参考一下

1 个赞
4

目前分区裁剪的优化还不支持 date 等日期类型 https://github.com/pingcap/tidb/issues/19941 ，后面版本会考虑优化

5

看上去是一个优化器对于有固定计算值的函数没有提前计算的问题。

如果可以的话，尝试一下指定分区？

SELECT * FROM table_name PARTITION (p0, p2)
    WHERE
1 个赞
6

是的 优化器对于有固定计算值的函数没有提前计算的问题 。 需求是看看优化器能不能提前做计算 ，之后选择适当的分区 ，避免扫描大量无关的分区

7

tibd 目前是支持 expression index 的。https://docs.pingcap.com/zh/tidb/v5.0/sql-statement-create-index#表达式索引, 这个功能会在近期发布的版本中 GA

8

应该不是这个问题吧， 不是函数索引 ， 是 分区过滤值使用了函数 导致不能有效定位分区。

9

请问下你这里是怎么分区的，我本地复现下。
我目前试了下 range columns(date) 是能支持你这里描述的情况的。

10

#用concat转换成同类型之后可以有效过滤分区了
EXPLAIN SELECT * FROM synrpt_tt_creative rtc WHERE rtc.date >= CONCAT(‘’,DATE_ADD(NOW(), INTERVAL -2 DAY))

image
image1037×300 15 KB

CREATE TABLE synrpt_tt_creative (
Synch_Report_Id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘主键ID’,
Synch_Time varchar(25) NOT NULL COMMENT ‘格式：yyyy-mm-dd HH:MM:SS’,
Report_State int(11) NOT NULL COMMENT ‘0:无效;1:有效未处理;2:有效已处理’,
advertiser_id varchar(100) NOT NULL COMMENT ‘广告主ID’,
campaign_id varchar(100) DEFAULT NULL COMMENT ‘当获取广告主ID’,
creative_id VARCHAR(100) NOT NULL COMMENT ‘创意ID’,
date VARCHAR(25) NOT NULL COMMENT ‘数据日期，格式：yyyy-mm-dd’,
PRIMARY KEY (Synch_Report_Id,date) /*T![clustered_index] NONCLUSTERED /
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=394048321/!90000 SHARD_ROW_ID_BITS=4 */ COMMENT=‘同步头条创意数据’
PARTITION BY RANGE COLUMNS(date) (
PARTITION p201905 VALUES LESS THAN (“2019-06-01”),
PARTITION p201906 VALUES LESS THAN (“2019-07-01”),
PARTITION p201907 VALUES LESS THAN (“2019-08-01”),
PARTITION p201908 VALUES LESS THAN (“2019-09-01”),
PARTITION p201909 VALUES LESS THAN (“2019-10-01”),
PARTITION p201910 VALUES LESS THAN (“2019-11-01”),
PARTITION p201911 VALUES LESS THAN (“2019-12-01”),
PARTITION p201912 VALUES LESS THAN (“2020-01-01”),
PARTITION p202001 VALUES LESS THAN (“2020-02-01”),
PARTITION p202002 VALUES LESS THAN (“2020-03-01”),
PARTITION p202003 VALUES LESS THAN (“2020-04-01”),
PARTITION p202004 VALUES LESS THAN (“2020-05-01”),
PARTITION p202005 VALUES LESS THAN (“2020-06-01”),
PARTITION p202006 VALUES LESS THAN (“2020-07-01”),
PARTITION p202007 VALUES LESS THAN (“2020-08-01”),
PARTITION p202008 VALUES LESS THAN (“2020-09-01”),
PARTITION p202009 VALUES LESS THAN (“2020-10-01”),
PARTITION p202010 VALUES LESS THAN (“2020-11-01”),
PARTITION p202011 VALUES LESS THAN (“2020-12-01”),
PARTITION p202012 VALUES LESS THAN (“2021-01-01”),
PARTITION p202101 VALUES LESS THAN (“2021-02-01”),
PARTITION p202102 VALUES LESS THAN (“2021-03-01”),
PARTITION p202103 VALUES LESS THAN (“2021-04-01”),
PARTITION p202104 VALUES LESS THAN (“2021-05-01”),
PARTITION p202105 VALUES LESS THAN (“2021-06-01”),
PARTITION p202106 VALUES LESS THAN (“2021-07-01”),
PARTITION p202107 VALUES LESS THAN (“2021-08-01”),
PARTITION p202108 VALUES LESS THAN (“2021-09-01”),
PARTITION p202109 VALUES LESS THAN (“2021-10-01”),
PARTITION p202110 VALUES LESS THAN (“2021-11-01”),
PARTITION p202111 VALUES LESS THAN (“2021-12-01”)
)

同样mysql也是 需要类型一致 就可以实现分区过滤
mysql> EXPLAIN SELECT * FROM synrpt_tt_creative rtc WHERE rtc.date >= CONCAT(‘’,DATE_ADD(NOW(), INTERVAL -2 DAY)) and rtc.date <=concat(‘’,NOW());
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| 1 | SIMPLE | rtc | p202109 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |

11

如果分区字段是标准日期格式 是可以的 ，如果是 varchar的日期 就不行 这块能兼容一下吗？

image
image985×349 25 KB

EXPLAIN SELECT * FROM tbl_new_viewshow_202109 v
WHERE v.createTime > DATE_ADD(NOW(), INTERVAL -2 DAY)
AND v.createTime < NOW()

CREATE TABLE tbl_new_viewshow_202109 (
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
asId VARCHAR(64) DEFAULT NULL,
createTime DATETIME NOT NULL,
PRIMARY KEY (id,createTime) /*T![clustered_index] NONCLUSTERED */,
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=160290001
PARTITION BY RANGE COLUMNS(createtime) (
PARTITION p20210901 VALUES LESS THAN (“20210902”),
PARTITION p20210902 VALUES LESS THAN (“20210903”),
PARTITION p20210903 VALUES LESS THAN (“20210904”),
PARTITION p20210904 VALUES LESS THAN (“20210905”),
PARTITION p20210905 VALUES LESS THAN (“20210906”),
PARTITION p20210906 VALUES LESS THAN (“20210907”),
PARTITION p20210907 VALUES LESS THAN (“20210908”),
PARTITION p20210908 VALUES LESS THAN (“20210909”),
PARTITION p20210909 VALUES LESS THAN (“20210910”),
PARTITION p20210910 VALUES LESS THAN (“20210911”),
PARTITION p20210911 VALUES LESS THAN (“20210912”),
PARTITION p20210912 VALUES LESS THAN (“20210913”),
PARTITION p20210913 VALUES LESS THAN (“20210914”),
PARTITION p20210914 VALUES LESS THAN (“20210915”),
PARTITION p20210915 VALUES LESS THAN (“20210916”),
PARTITION p20210916 VALUES LESS THAN (“20210917”),
PARTITION p20210917 VALUES LESS THAN (“20210918”),
PARTITION p20210918 VALUES LESS THAN (“20210919”),
PARTITION p20210919 VALUES LESS THAN (“20210920”),
PARTITION p20210920 VALUES LESS THAN (“20210921”),
PARTITION p20210921 VALUES LESS THAN (“20210922”),
PARTITION p20210922 VALUES LESS THAN (“20210923”),
PARTITION p20210923 VALUES LESS THAN (“20210924”),
PARTITION p20210924 VALUES LESS THAN (“20210925”),
PARTITION p20210925 VALUES LESS THAN (“20210926”),
PARTITION p20210926 VALUES LESS THAN (“20210927”),
PARTITION p20210927 VALUES LESS THAN (“20210928”),
PARTITION p20210928 VALUES LESS THAN (“20210929”),
PARTITION p20210929 VALUES LESS THAN (“20210930”),
PARTITION p20210930 VALUES LESS THAN (“20211001”)
)

12

确实还不行，我先记录下来了,近期会支持 https://github.com/pingcap/tidb/issues/28291

13

会支持跨类型的兼容是吗？ 同类型是没问题的

14

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。