为提高效率,请提供以下信息,问题描述清晰能够更快得到解决:
【 TiDB 使用环境】
tidb v4.0.8
【概述】 场景 + 问题概述
查询range分区表,同一个SQL查询不同的日期范围,执行计划不一样,对应的两个日期范围数据条数差不多,但查询速度相差(慢)很多。请问这是什么原因导致的,又应该如何解决这类问题?
DDL(按月分区):
CREATE TABLE report_base_his
(
dt
date NOT NULL COMMENT ‘日期’,
cate_id
int(11) NOT NULL COMMENT ‘游戏组ID’,
channel
varchar(50) NOT NULL COMMENT ‘渠道标识’,
package_id
int(11) NOT NULL COMMENT ’ 融合拿包ID’,
monitor_id
int(11) NOT NULL COMMENT ‘监测ID’,
monitor_sn
int(11) NOT NULL COMMENT ‘投放序号’,
ad_id
varchar(50) NOT NULL DEFAULT ‘-1’ COMMENT ‘广告id’,
c_id
varchar(50) NOT NULL DEFAULT ‘-1’ COMMENT ‘创意id’,
game_id
int(11) NOT NULL COMMENT ‘融合游戏ID(子游戏ID)’,
his_income_money
bigint(20) DEFAULT 0 COMMENT ‘累计充值’,
his_income
bigint(20) DEFAULT 0 COMMENT ‘累计收入’,
his_cost
bigint(20) DEFAULT 0 COMMENT ‘累计支出’,
his_new_account
int(20) DEFAULT 0 COMMENT ‘累计新增账号’,
his_income_mobile
bigint(20) DEFAULT 0 COMMENT ‘累计付费设备数’,
his_new_mobile
bigint(20) DEFAULT 0 COMMENT ‘累计注册设备数’,
his_new_income_mobile
bigint(20) DEFAULT 0 COMMENT ‘累计新增付费设备数’,
PRIMARY KEY (dt
,monitor_sn
,cate_id
,channel
,package_id
,monitor_id
,ad_id
,c_id
,game_id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=‘数据历史汇总(每天)精确到创意cid’
PARTITION BY RANGE ( TO_DAYS(dt
) ) (
PARTITION pMIN
VALUES LESS THAN (735964),
PARTITION p201501
VALUES LESS THAN (735995),
PARTITION p201502
VALUES LESS THAN (736023),
……
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)
)
查询语句:
执行计划: