尝试操作： 1、drop stats 重跑执行计划，健康度都是99
2、重新添加删除tiflash副本
3、已经开启 global tidb_allow_mpp=on
以这个SQL举例
EXPLAIN
SELECT
a.account_id,
a.campaign_id,
a.ad_id,
IFNULL(b.amount, a.amount) AS amount
FROM
(
SELECT
account_id,
campaign_id,
adgroup_id AS ad_id,
0 AS amount
FROM
Synads_Gdt_adgroup
WHERE
account_id IN (
SELECT
DISTINCT platform_account_id
FROM
mbg_core.emarbox_project ep
INNER JOIN mbg_core.yxt_finance_customer_entity yfce ON ep.pig_advertiser_id = yfce.id
WHERE
ep.media_id = 2
AND yfce.customer_id = 2250
AND (
(
ep.begin_date IS NULL
AND ep.end_date IS NULL
)
OR (
ep.begin_date IS NULL
AND ep.end_date >= CURDATE()
)
OR (
ep.begin_date <= CURDATE()
AND ep.end_date IS NULL
)
OR (
ep.begin_date <= CURDATE()
AND ep.end_date >= CURDATE()
)
)
)
AND is_deleted = 0
) a
LEFT JOIN (
SELECT
account_id,
campaign_id,
ad_id,
ROUND(IFNULL(SUM(amount), 0), 2) AS amount
FROM
(
SELECT
account_id,
campaign_id,
adgroup_id AS ad_id,
IFNULL(cost, 0) / 100 AS amount
FROM
synrpt_gdt_adgroup_daily_reporting_part
WHERE
DATE >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 0 DAY), ‘%Y-%m-%d’)
AND DATE < DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 DAY), ‘%Y-%m-%d’)
AND account_id IN (
SELECT
DISTINCT platform_account_id
FROM
mbg_core.emarbox_project ep
INNER JOIN mbg_core.yxt_finance_customer_entity yfce ON ep.pig_advertiser_id = yfce.id
WHERE
ep.media_id = 2
AND yfce.customer_id = 2250
AND (
(
ep.begin_date IS NULL
AND ep.end_date IS NULL
)
OR (
ep.begin_date IS NULL
AND ep.end_date >= CURDATE()
)
OR (
ep.begin_date <= CURDATE()
AND ep.end_date IS NULL
)
OR (
ep.begin_date <= CURDATE()
AND ep.end_date >= CURDATE()
)
)
)
) d
GROUP BY
ad_id,
account_id
) b ON a.account_id = b.account_id
AND a.campaign_id = b.campaign_id
AND a.ad_id = b.ad_id
WHERE
IFNULL(b.amount, a.amount) > 1000;
synads_gdt_adgroup 健康度
tiflash副本数
表结构
CREATE TABLE synads_gdt_adgroup (
adgroup_id bigint(60) NOT NULL COMMENT ‘广告组 id’,
adgroup_name varchar(255) DEFAULT NULL COMMENT ‘广告组名称’,
account_id bigint(60) NOT NULL COMMENT ‘广告主帐号 id’,
campaign_id bigint(60) DEFAULT NULL COMMENT ‘推广计划 id’,
site_set varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘投放版位集合’,
automatic_site_enabled tinyint(1) DEFAULT NULL COMMENT ‘是否开启自动版位功能’,
optimization_goal varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘广告优化目标类型’,
billing_event varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘计费类型’,
bid_amount int(11) DEFAULT NULL COMMENT ‘告出价，单位为分’,
daily_budget bigint(20) DEFAULT NULL COMMENT ‘广告组日预算，单位为分，设置为 0 表示不设预算’,
promoted_object_type varchar(500) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘推广目标类型’,
promoted_object_id varchar(512) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘推广目标 id’,
app_android_channel_package_id varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘安卓应用渠道包 id’,
mini_game_program_id varchar(500) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘微信小游戏 id’,
targeting_id bigint(100) DEFAULT NULL COMMENT ‘定向 id，仅非朋友圈广告有效’,
targeting varchar(16384) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘定向详细设置，存放所有定向条件’,
targeting_translation text CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘已选择定向条件的描述’,
is_include_unsupported_targeting tinyint(1) DEFAULT NULL COMMENT ‘是否包含不支持定向’,
scene_spec varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘场景定向’,
begin_date varchar(25) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘开始投放日期，日期格式：YYYY-MM-DD，且日期小于等于 end_date’,
first_day_begin_time varchar(25) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘首日开始投放时间，首日开始投放时间：HH:ii:ss’,
end_date varchar(25) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘结束投放日期，日期格式：YYYY-MM-DD，大于等于今天，且大于等于 begin_date ；如果希望长期投放，传空字符串’,
time_series varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘投放时间段’,
configured_status int(5) NOT NULL COMMENT ‘客户设置的状态 1 代表 AD_STATUS_NORMAL 0 代表 AD_STATUS_SUSPEND’,
customized_category varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘自定义分类’,
created_time bigint(20) NOT NULL DEFAULT ‘1585670400’ COMMENT ‘创建时间（时间戳）’,
last_modified_time bigint(20) DEFAULT NULL COMMENT ‘最后修改时间（时间戳）’,
ad_count bigint(20) DEFAULT NULL COMMENT ‘广告数量，创意数量查询’,
dynamic_ad_spec varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘动态商品广告属性’,
user_action_sets varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘用户行为数据源’,
additional_user_action_sets varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘补充的 app 用户行为数据源，字段待废弃’,
is_deleted tinyint(1) NOT NULL COMMENT ‘是否已删除，true：是，false：否’,
dynamic_creative_id bigint(20) DEFAULT NULL COMMENT ‘DC 动态创意 id’,
is_rewarded_video_ad tinyint(1) DEFAULT NULL COMMENT ’ 是否是激励视频广告’,
cost_guarantee_message varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ’ 成本保障状态信息说明’,
cost_guarantee_status varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘成本保障状态，成本保障状态定义’,
cold_start_audience varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘扩量种子人群’,
auto_audience tinyint(1) DEFAULT NULL COMMENT ’ 是否使用系统优选’,
expand_enabled tinyint(1) DEFAULT NULL COMMENT ‘是否使用自动扩量’,
expand_targeting varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT ‘扩量不可突破定向’,
deep_conversion_spec varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘oCPC/oCPM 深度优化内容’,
deep_optimization_action_type varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘CPC/oCPM 深度优化方式配置’,
poi_list varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘门店 id 列表’,
conversion_id bigint(20) DEFAULT NULL COMMENT ‘转化 id’,
deep_conversion_behavior_bid bigint(20) DEFAULT NULL COMMENT ‘深度优化行为的出价’,
deep_conversion_worth_rate float DEFAULT NULL COMMENT ‘深度优化价值的期望 ROI’,
android_channel_package_audit_message varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘广告包渠道包审核消息’,
system_status varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘广告组在系统中的状态’,
bid_mode varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘出价方式’,
status varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ’ 广告状态’,
bid_adjustment text CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ’ 出价系数设置内容’,
auto_acquisition_enabled tinyint(1) DEFAULT NULL COMMENT ‘是否开启一键起量’,
auto_acquisition_budget int(11) DEFAULT NULL COMMENT ‘一键起量探索预算，单位为分，一键起量预算设置后不可修改’,
creative_display_type varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘创意展示类型’,
auto_derived_creative_enabled tinyint(1) DEFAULT NULL COMMENT ‘是否开启自动衍生视频创意’,
smart_bid_type varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘出价类型’,
smart_cost_cap int(11) DEFAULT NULL COMMENT ‘自动出价下’,
marketing_scene varchar(256) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘营销场景’,
synch_time varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT ‘格式：yyyy-mm-dd HH:MM:SS，同步调用时候时间’,
synch_create_date varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT ‘格式：yyyy-mm-dd’,
synch_create_hour int(11) NOT NULL COMMENT ‘小时值范围：0~23’,
PRIMARY KEY (adgroup_id,configured_status) /*T![clustered_index] NONCLUSTERED */,
KEY idx_created_time (created_time),
KEY account_id_status (account_id,status),
KEY unq_agid (adgroup_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T! SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=2 */ COMMENT=‘同步广点通获取广告组数据’
PARTITION BY LIST (configured_status)
(PARTITION p0 VALUES IN (0),
PARTITION p1 VALUES IN (1))
执行计划
