v7.5.4 分区表在analyze之后不支持tiflash查询，加hint报错Can't find a proper physical plan for this query

foxchan · 2025 年4 月 8 日 08:12

【 TiDB 使用环境】生产环境
【 TiDB 版本】 v7.5.4
【复现路径】建表后直接加tiflash副本，执行计划可以走tiflash，但是analyze table board_material_rebate_daily_fox之后就不能走了，加hint报错Can’t find a proper physical plan for this query

表结构如下

CREATE TABLE board_material_rebate_daily_fox (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘主键ID’,
date varchar(10) NOT NULL COMMENT ‘日期’,
stat_month varchar(20) NOT NULL COMMENT ‘统计月份：形如 2024-12’,
advertiser_id bigint(20) NOT NULL COMMENT ‘账号ID’,
advertiser_name varchar(120) DEFAULT NULL COMMENT ‘账号名称’,
material_id bigint(32) NOT NULL COMMENT ‘素材ID’,
platform_id int(3) NOT NULL COMMENT ‘平台ID，2广点通、3微信、4头条、5快手’,
customer_id int(11) NOT NULL COMMENT ‘客户ID’,
operate_type varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT ‘运营方式’,
operator varchar(50) NOT NULL COMMENT ‘运营人员ID’,
operator_dept_id varchar(30) NOT NULL COMMENT ‘运营人员所属部门’,
product_id bigint(20) NOT NULL COMMENT ‘产品ID’,
platform_name varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘平台名称’,
customer_short_name varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘客户简称’,
product_name varchar(100) DEFAULT NULL COMMENT ‘产品名称’,
operator_name varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘运营人员姓名’,
operator_dept_name varchar(30) DEFAULT NULL COMMENT ‘运营人员部门名称’,
operator_parent_dept_id varchar(30) DEFAULT NULL COMMENT ‘运营父部门ID’,
operator_parent_dept_name varchar(30) DEFAULT NULL COMMENT ‘运营人员父部门名称’,
industry_id bigint(20) DEFAULT NULL COMMENT ‘客户所属行业id’,
industry_name varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘客户所属行业名称’,
sales varchar(50) DEFAULT NULL COMMENT ‘销售人员ID’,
sales_name varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘销售姓名’,
sales_dept_id varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘销售部门ID’,
sales_dept_name varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘销售部门名称’,
sales_parent_dept_id varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘销售父部门ID’,
sales_parent_dept_name varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘销售父部门’,
operator_role varchar(255) DEFAULT NULL COMMENT ‘运营人员 - 角色’,
sales_role varchar(255) DEFAULT NULL COMMENT ‘销售人员 - 角色’,
sales_employee_status int(5) DEFAULT NULL COMMENT ‘销售 -人员在职状态 4、离职 5、待离职’,
operator_employee_status int(5) DEFAULT NULL COMMENT ‘运营 -人员在职状态 4、离职 5、待离职’,
is_high_quality_material int(1) DEFAULT NULL COMMENT ‘是否有效优质 0 、否 1、是’,
high_quality_performance_cost double DEFAULT NULL COMMENT '有效优质消耗单位：元 ',
is_first_effective_material int(1) DEFAULT NULL COMMENT ‘是否有效首发 0 、否 1、是’,
first_effective_performance_cost double DEFAULT NULL COMMENT '有效优质消耗单位：元 ',
rebate_all_good_performance_cost double DEFAULT NULL COMMENT '双优素材消耗单位：元 ',
rebate_all_bad_performance_cost double DEFAULT NULL COMMENT '双非素材消耗单位：元 ',
create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
update_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘修改时间’,
first_return_money double DEFAULT ‘0’ COMMENT ‘首发素材返点金额（单位：元）：first_effective_cost_rebate_amount’,
high_quality_return_money double DEFAULT ‘0’ COMMENT ‘优质素材返点金额（单位：元）： high_cost_rebate_amount’,
live_return_money double DEFAULT ‘0’ COMMENT ‘直播返点金额（单位：元）： live_cost_rebate_amount’,
bidding_type varchar(50) DEFAULT NULL COMMENT ‘竞价分类：rebate_calc_external_industry_category’,
is_all_bad int(1) DEFAULT NULL COMMENT ‘是否双非 0 、否 1、是：is_cal_high_material_cost_rebate = NO\r\nAND is_cal_first_effective_material_cost_rebate =NO ，如果是1，则说明是双非，在算素材数量的时候去重下’,
performance_cost double DEFAULT NULL COMMENT ‘业绩消耗’,
PRIMARY KEY (id,date) /*T![clustered_index] NONCLUSTERED */,
KEY adve_date (date,advertiser_id),
KEY material_date (date,material_id),
KEY operdept_date (date,operator_dept_id),
KEY customer_date (date,customer_id),
KEY platform_id_date (date,platform_id),
KEY idx_date (date),
KEY idx_customer_id (customer_id),
KEY idx_product_id (product_id),
KEY idx_operator_dept_id (operator_dept_id),
KEY idx_operator (operator),
KEY idx_sales (sales),
KEY idx_sales_dept_id (sales_dept_id),
KEY product_id_date (date,product_id),
KEY operator_date (date,operator),
KEY sales_date (date,sales),
KEY sales_dept_id_date (date,sales_dept_id),
KEY idx_advertiser_id (advertiser_id),
KEY idx_platform_id (platform_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=96000001 COMMENT=‘明点-有效优质、有效首发素材 -日表：根据子远统计的 synrpt_tt_material_rebate_common_daily明点天表数据统计而来 -于子怡需求’
PARTITION BY RANGE COLUMNS(date)
(PARTITION p20250101 VALUES LESS THAN (‘2025-01-02’),
PARTITION p20250102 VALUES LESS THAN (‘2025-01-03’),
PARTITION p20250103 VALUES LESS THAN (‘2025-01-04’),
PARTITION p20250104 VALUES LESS THAN (‘2025-01-05’),
PARTITION p20250105 VALUES LESS THAN (‘2025-01-06’),
PARTITION p20250106 VALUES LESS THAN (‘2025-01-07’),
PARTITION p20250107 VALUES LESS THAN (‘2025-01-08’),
PARTITION p20250108 VALUES LESS THAN (‘2025-01-09’),
PARTITION p20250109 VALUES LESS THAN (‘2025-01-10’),
PARTITION p20250110 VALUES LESS THAN (‘2025-01-11’),
PARTITION p20250111 VALUES LESS THAN (‘2025-01-12’),
PARTITION p20250112 VALUES LESS THAN (‘2025-01-13’),
PARTITION p20250113 VALUES LESS THAN (‘2025-01-14’),
PARTITION p20250114 VALUES LESS THAN (‘2025-01-15’),
PARTITION p20250115 VALUES LESS THAN (‘2025-01-16’),
PARTITION p20250116 VALUES LESS THAN (‘2025-01-17’),
PARTITION p20250117 VALUES LESS THAN (‘2025-01-18’),
PARTITION p20250118 VALUES LESS THAN (‘2025-01-19’),
PARTITION p20250119 VALUES LESS THAN (‘2025-01-20’),
PARTITION p20250120 VALUES LESS THAN (‘2025-01-21’),
PARTITION p20250121 VALUES LESS THAN (‘2025-01-22’),
PARTITION p20250122 VALUES LESS THAN (‘2025-01-23’),
PARTITION p20250123 VALUES LESS THAN (‘2025-01-24’),
PARTITION p20250124 VALUES LESS THAN (‘2025-01-25’),
PARTITION p20250125 VALUES LESS THAN (‘2025-01-26’),
PARTITION p20250126 VALUES LESS THAN (‘2025-01-27’),
PARTITION p20250127 VALUES LESS THAN (‘2025-01-28’),
PARTITION p20250128 VALUES LESS THAN (‘2025-01-29’),
PARTITION p20250129 VALUES LESS THAN (‘2025-01-30’),
PARTITION p20250130 VALUES LESS THAN (‘2025-01-31’),
PARTITION p20250131 VALUES LESS THAN (‘2025-02-01’),
PARTITION p20250201 VALUES LESS THAN (‘2025-02-02’),
PARTITION p20250202 VALUES LESS THAN (‘2025-02-03’),
PARTITION p20250203 VALUES LESS THAN (‘2025-02-04’),
PARTITION p20250204 VALUES LESS THAN (‘2025-02-05’),
PARTITION p20250205 VALUES LESS THAN (‘2025-02-06’),
PARTITION p20250206 VALUES LESS THAN (‘2025-02-07’),
PARTITION p20250207 VALUES LESS THAN (‘2025-02-08’),
PARTITION p20250208 VALUES LESS THAN (‘2025-02-09’),
PARTITION p20250209 VALUES LESS THAN (‘2025-02-10’),
PARTITION p20250210 VALUES LESS THAN (‘2025-02-11’),
PARTITION p20250211 VALUES LESS THAN (‘2025-02-12’),
PARTITION p20250212 VALUES LESS THAN (‘2025-02-13’),
PARTITION p20250213 VALUES LESS THAN (‘2025-02-14’),
PARTITION p20250214 VALUES LESS THAN (‘2025-02-15’),
PARTITION p20250215 VALUES LESS THAN (‘2025-02-16’),
PARTITION p20250216 VALUES LESS THAN (‘2025-02-17’),
PARTITION p20250217 VALUES LESS THAN (‘2025-02-18’),
PARTITION p20250218 VALUES LESS THAN (‘2025-02-19’),
PARTITION p20250219 VALUES LESS THAN (‘2025-02-20’),
PARTITION p20250220 VALUES LESS THAN (‘2025-02-21’),
PARTITION p20250221 VALUES LESS THAN (‘2025-02-22’),
PARTITION p20250222 VALUES LESS THAN (‘2025-02-23’),
PARTITION p20250223 VALUES LESS THAN (‘2025-02-24’),
PARTITION p20250224 VALUES LESS THAN (‘2025-02-25’),
PARTITION p20250225 VALUES LESS THAN (‘2025-02-26’),
PARTITION p20250226 VALUES LESS THAN (‘2025-02-27’),
PARTITION p20250227 VALUES LESS THAN (‘2025-02-28’),
PARTITION p20250228 VALUES LESS THAN (‘2025-02-29’),
PARTITION p20250229 VALUES LESS THAN (‘2025-03-01’),
PARTITION p20250301 VALUES LESS THAN (‘2025-03-02’),
PARTITION p20250302 VALUES LESS THAN (‘2025-03-03’),
PARTITION p20250303 VALUES LESS THAN (‘2025-03-04’),
PARTITION p20250304 VALUES LESS THAN (‘2025-03-05’),
PARTITION p20250305 VALUES LESS THAN (‘2025-03-06’),
PARTITION p20250306 VALUES LESS THAN (‘2025-03-07’),
PARTITION p20250307 VALUES LESS THAN (‘2025-03-08’),
PARTITION p20250308 VALUES LESS THAN (‘2025-03-09’),
PARTITION p20250309 VALUES LESS THAN (‘2025-03-10’),
PARTITION p20250310 VALUES LESS THAN (‘2025-03-11’),
PARTITION p20250311 VALUES LESS THAN (‘2025-03-12’),
PARTITION p20250312 VALUES LESS THAN (‘2025-03-13’),
PARTITION p20250313 VALUES LESS THAN (‘2025-03-14’),
PARTITION p20250314 VALUES LESS THAN (‘2025-03-15’),
PARTITION p20250315 VALUES LESS THAN (‘2025-03-16’),
PARTITION p20250316 VALUES LESS THAN (‘2025-03-17’),
PARTITION p20250317 VALUES LESS THAN (‘2025-03-18’),
PARTITION p20250318 VALUES LESS THAN (‘2025-03-19’),
PARTITION p20250319 VALUES LESS THAN (‘2025-03-20’),
PARTITION p20250320 VALUES LESS THAN (‘2025-03-21’),
PARTITION p20250321 VALUES LESS THAN (‘2025-03-22’),
PARTITION p20250322 VALUES LESS THAN (‘2025-03-23’),
PARTITION p20250323 VALUES LESS THAN (‘2025-03-24’),
PARTITION p20250324 VALUES LESS THAN (‘2025-03-25’),
PARTITION p20250325 VALUES LESS THAN (‘2025-03-26’),
PARTITION p20250326 VALUES LESS THAN (‘2025-03-27’),
PARTITION p20250327 VALUES LESS THAN (‘2025-03-28’),
PARTITION p20250328 VALUES LESS THAN (‘2025-03-29’),
PARTITION p20250329 VALUES LESS THAN (‘2025-03-30’),
PARTITION p20250330 VALUES LESS THAN (‘2025-03-31’),
PARTITION p20250331 VALUES LESS THAN (‘2025-04-01’),
PARTITION p20250401 VALUES LESS THAN (‘2025-04-02’),
PARTITION p20250402 VALUES LESS THAN (‘2025-04-03’),
PARTITION p20250403 VALUES LESS THAN (‘2025-04-04’),
PARTITION p20250404 VALUES LESS THAN (‘2025-04-05’),
PARTITION p20250405 VALUES LESS THAN (‘2025-04-06’),
PARTITION p20250406 VALUES LESS THAN (‘2025-04-07’),
PARTITION p20250407 VALUES LESS THAN (‘2025-04-08’),
PARTITION p20250408 VALUES LESS THAN (‘2025-04-09’),
PARTITION p20250409 VALUES LESS THAN (‘2025-04-10’),
PARTITION p20250410 VALUES LESS THAN (‘2025-04-11’),
PARTITION p20250411 VALUES LESS THAN (‘2025-04-12’),
PARTITION p20250412 VALUES LESS THAN (‘2025-04-13’),
PARTITION p20250413 VALUES LESS THAN (‘2025-04-14’),
PARTITION p20250414 VALUES LESS THAN (‘2025-04-15’),
PARTITION p20250415 VALUES LESS THAN (‘2025-04-16’),
PARTITION p20250416 VALUES LESS THAN (‘2025-04-17’),
PARTITION p20250417 VALUES LESS THAN (‘2025-04-18’),
PARTITION p20250418 VALUES LESS THAN (‘2025-04-19’),
PARTITION p20250419 VALUES LESS THAN (‘2025-04-20’),
PARTITION p20250420 VALUES LESS THAN (‘2025-04-21’),
PARTITION p20250421 VALUES LESS THAN (‘2025-04-22’),
PARTITION p20250422 VALUES LESS THAN (‘2025-04-23’),
PARTITION p20250423 VALUES LESS THAN (‘2025-04-24’),
PARTITION p20250424 VALUES LESS THAN (‘2025-04-25’),
PARTITION p20250425 VALUES LESS THAN (‘2025-04-26’),
PARTITION p20250426 VALUES LESS THAN (‘2025-04-27’),
PARTITION p20250427 VALUES LESS THAN (‘2025-04-28’),
PARTITION p20250428 VALUES LESS THAN (‘2025-04-29’),
PARTITION p20250429 VALUES LESS THAN (‘2025-04-30’),
PARTITION p20250430 VALUES LESS THAN (‘2025-05-01’),
PARTITION p20250501 VALUES LESS THAN (‘2025-05-02’),
PARTITION p20250502 VALUES LESS THAN (‘2025-05-03’),
PARTITION p20250503 VALUES LESS THAN (‘2025-05-04’),
PARTITION p20250504 VALUES LESS THAN (‘2025-05-05’),
PARTITION p20250505 VALUES LESS THAN (‘2025-05-06’),
PARTITION p20250506 VALUES LESS THAN (‘2025-05-07’),
PARTITION p20250507 VALUES LESS THAN (‘2025-05-08’),
PARTITION p20250508 VALUES LESS THAN (‘2025-05-09’),
PARTITION p20250509 VALUES LESS THAN (‘2025-05-10’),
PARTITION p20250510 VALUES LESS THAN (‘2025-05-11’),
PARTITION p20250511 VALUES LESS THAN (‘2025-05-12’),
PARTITION p20250512 VALUES LESS THAN (‘2025-05-13’),
PARTITION p20250513 VALUES LESS THAN (‘2025-05-14’),
PARTITION p20250514 VALUES LESS THAN (‘2025-05-15’),
PARTITION p20250515 VALUES LESS THAN (‘2025-05-16’),
PARTITION p20250516 VALUES LESS THAN (‘2025-05-17’),
PARTITION p20250517 VALUES LESS THAN (‘2025-05-18’),
PARTITION p20250518 VALUES LESS THAN (‘2025-05-19’),
PARTITION p20250519 VALUES LESS THAN (‘2025-05-20’),
PARTITION p20250520 VALUES LESS THAN (‘2025-05-21’),
PARTITION p20250521 VALUES LESS THAN (‘2025-05-22’),
PARTITION p20250522 VALUES LESS THAN (‘2025-05-23’),
PARTITION p20250523 VALUES LESS THAN (‘2025-05-24’),
PARTITION p20250524 VALUES LESS THAN (‘2025-05-25’),
PARTITION p20250525 VALUES LESS THAN (‘2025-05-26’),
PARTITION p20250526 VALUES LESS THAN (‘2025-05-27’),
PARTITION p20250527 VALUES LESS THAN (‘2025-05-28’),
PARTITION p20250528 VALUES LESS THAN (‘2025-05-29’),
PARTITION p20250529 VALUES LESS THAN (‘2025-05-30’),
PARTITION p20250530 VALUES LESS THAN (‘2025-05-31’),
PARTITION p20250531 VALUES LESS THAN (‘2025-06-01’),
PARTITION p20250601 VALUES LESS THAN (‘2025-06-02’),
PARTITION p20250602 VALUES LESS THAN (‘2025-06-03’),
PARTITION p20250603 VALUES LESS THAN (‘2025-06-04’),
PARTITION p20250604 VALUES LESS THAN (‘2025-06-05’),
PARTITION p20250605 VALUES LESS THAN (‘2025-06-06’),
PARTITION p20250606 VALUES LESS THAN (‘2025-06-07’),
PARTITION p20250607 VALUES LESS THAN (‘2025-06-08’),
PARTITION p20250608 VALUES LESS THAN (‘2025-06-09’),
PARTITION p20250609 VALUES LESS THAN (‘2025-06-10’),
PARTITION p20250610 VALUES LESS THAN (‘2025-06-11’),
PARTITION p20250611 VALUES LESS THAN (‘2025-06-12’),
PARTITION p20250612 VALUES LESS THAN (‘2025-06-13’),
PARTITION p20250613 VALUES LESS THAN (‘2025-06-14’),
PARTITION p20250614 VALUES LESS THAN (‘2025-06-15’),
PARTITION p20250615 VALUES LESS THAN (‘2025-06-16’),
PARTITION p20250616 VALUES LESS THAN (‘2025-06-17’),
PARTITION p20250617 VALUES LESS THAN (‘2025-06-18’),
PARTITION p20250618 VALUES LESS THAN (‘2025-06-19’),
PARTITION p20250619 VALUES LESS THAN (‘2025-06-20’),
PARTITION p20250620 VALUES LESS THAN (‘2025-06-21’),
PARTITION p20250621 VALUES LESS THAN (‘2025-06-22’),
PARTITION p20250622 VALUES LESS THAN (‘2025-06-23’),
PARTITION p20250623 VALUES LESS THAN (‘2025-06-24’),
PARTITION p20250624 VALUES LESS THAN (‘2025-06-25’),
PARTITION p20250625 VALUES LESS THAN (‘2025-06-26’),
PARTITION p20250626 VALUES LESS THAN (‘2025-06-27’),
PARTITION p20250627 VALUES LESS THAN (‘2025-06-28’),
PARTITION p20250628 VALUES LESS THAN (‘2025-06-29’),
PARTITION p20250629 VALUES LESS THAN (‘2025-06-30’),
PARTITION p20250630 VALUES LESS THAN (‘2025-07-01’))

另一个表

CREATE TABLE dept_level_fox (
dept_id bigint(20) NOT NULL COMMENT ‘部门ID：pig_dd_department_info.id’,
dept_name varchar(500) DEFAULT NULL COMMENT ‘部门名称：pig_dd_department_info.name’,
id1 bigint(20) DEFAULT NULL COMMENT ‘dept_id的层级信息：层级1’,
name1 varchar(500) DEFAULT NULL COMMENT ‘dept_id的层级信息： -层级1名称’,
id2 bigint(20) DEFAULT NULL COMMENT ‘dept_id的层级信息：层级2’,
name2 varchar(500) DEFAULT NULL COMMENT ‘dept_id的层级信息：层级2名称’,
id3 bigint(20) DEFAULT NULL COMMENT ‘dept_id的层级信息：层级3’,
name3 varchar(500) DEFAULT NULL COMMENT ‘dept_id的层级信息：层级3名称’,
id4 bigint(20) DEFAULT NULL COMMENT ‘dept_id的层级信息：层级4’,
name4 varchar(500) DEFAULT NULL COMMENT ‘dept_id的层级信息：层级4名称’,
create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
update_time timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘修改时间’,
is_delete int(1) NOT NULL DEFAULT ‘0’ COMMENT ‘是否删除：0、否 1、是 - 这个表主要是当前存在的部门，如果后续数据量累计，可将历史的不存在的部门此字段设置为1，使用方根据需要进行过滤’,
remark varchar(500) DEFAULT NULL COMMENT ‘备注’,
parent_dept_id bigint(20) DEFAULT NULL COMMENT ‘dept_id的父部门ID’,
orders bigint(20) DEFAULT NULL COMMENT ‘部门顺序：取自pig_dd_department_info.orders’,
sub_deptid_list varchar(500) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘子部门列表：取自pig_dd_department_info.sub_deptid_list’,
parent_dept_name varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘dept_id的父部门名称’,
slogan varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘部门标语，用于个人看板，可编辑’,
PRIMARY KEY (dept_id) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT=‘统计pig_dd_department_info中每个部门的一~四级的 ID名称信息后续有五级以上再追加字段重新进行统计，相关功能进行改造，如：钉钉运营平台-消耗趋势-部门消耗趋势需要再追加新层级的取部门逻辑’

查询SQL如下：

EXPLAIN
SELECT
COUNT(DISTINCT a.material_id) mingdianTotalCountHigh,
SUM(a.performance_cost) mingdianTotalCostHigh,
COUNT(DISTINCT a.material_id) mingdianTotalCountFirst,
SUM(a.performance_cost) mingdianTotalCostFirst,
COUNT(
DISTINCT
IF (
a.is_high_quality_material = 1
AND a.is_first_effective_material = 1,
a.material_id,
NULL
)
) mingdianAllGoodCount,
SUM(
a.rebate_all_good_performance_cost
) mingdianAllGoodCost,
COUNT(
DISTINCT
IF (
a.is_all_bad = 1,
a.material_id,
NULL
)
) mingdianAllBadCount,
SUM(
a.rebate_all_bad_performance_cost
) mingdianAllBadCost,
COUNT(
DISTINCT
IF (
a.is_high_quality_material = 1,
a.material_id,
NULL
)
) highQualityMaterialCount,
SUM(
a.high_quality_performance_cost
) highQualityMaterialCost,
COUNT(
DISTINCT
IF (
a.is_first_effective_material = 1,
a.material_id,
NULL
)
) firstEffectiveMaterialCount,
SUM(
a.first_effective_performance_cost
) firstEffectiveMaterialCost,
WEEK (a.date) idx
FROM
board_material_rebate_daily_fox a
– JOIN dept_level deptLevel ON a.operator_dept_id = deptLevel.dept_id
WHERE
1 = 1
AND a.platform_name IN (‘头条’,‘广点通’,‘本地推’)
– AND a.platform_id IN (2,4,6,11)
AND a.operate_type IN (‘代运营’, ‘服务标’, ‘代运营(客供素材)’)
AND a.date >=‘2025-02-07’
AND a.date <=‘2025-04-10’ /权限相关条件/
GROUP BY
idx

沧海一声笑 · 2025 年4 月 8 日 13:11

可以通过以下命令查看统计信息：
SHOW STATS_META WHERE table_name = ‘board_material_rebate_daily_fox’;
SHOW STATS_HISTOGRAMS WHERE table_name = ‘board_material_rebate_daily_fox’;

统计信息异常，可以尝试重新收集统计信息，或者调整采样率：
ANALYZE TABLE board_material_rebate_daily_fox WITH NUM BUCKETS 256;

有猫万事足 · 2025 年4 月 8 日 13:35

加了什么hint？

正常来说设置强制mpp就应该可以走tiflash。而且设置强制mpp之后，可以通过

show warnings；

看到不能mpp的原因

乡在人间 · 2025 年4 月 9 日 01:47

检查下统计信息和执行计划，另外，hint报错，应该是没有正确使用吧

foxchan · 2025 年4 月 9 日 01:54

可能我没描述清楚，这个bug可以复现。
1、建立表board_material_rebate_daily_fox和dept_level_fox
2、添加board_material_rebate_daily_fox表 tiflash副本数为2
3、tiflash副本数正常后，用样例SQL查询，可以看到board_material_rebate_daily_fox表走的tiflash
4、对表board_material_rebate_daily_fox 进行analyze 之后，再用SQL查询，执行计划就走了tikv
5、样例SQL添加 hint，/*+ read_from_storage(tiflash[a]) */，执行SQL就报错Can’t find a proper physical plan for this query

问题1：为什么表的健康度低就可以走tiflash，analyze 之后反而走tikv了。
问题2：为什么强制走tiflash会报错

WalterWj · 2025 年4 月 14 日 02:33

1：
因为走 tiflash 还是 tikv 默认是根据 cbo 选择的，也就是表健康度低，统计信息不准，代价模型认为 tiflash 的代价更低。你可以 explain verbose 看下代价
2：
你要不是事别名改为表名。或者 session 级别 set 下 engine 为 tidb,tiflash 看看能不能干扰走。报错感觉不应该。

foxchan · 2025 年4 月 14 日 06:01

设置engine 为tidb，tiflash，就会走tiflash

如果用hint的话就是报错

走tikv 看到warnings

WalterWj · 2025 年4 月 15 日 07:36

感觉比较奇怪，我找个研发老师帮忙看看。

WalterWj · 2025 年4 月 18 日 02:06

有时间试试 plan replayer 或者裸导统计信息到一个 8.5 上，加个 tiflash replica 看看会不会有同样的错。
可能是一个已经修复过的问题。

foxchan · 2025 年4 月 18 日 06:57

8.5 没问题，看来是已知问题。7.5的小版本修复了吗

WalterWj · 2025 年4 月 27 日 09:35

看起来是已知问题。新版本没问题感觉可以考虑升级了。

system · 2025 年5 月 15 日 06:30

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