Attempted operations:
- Drop stats and rerun the execution plan, health is 99
- Re-add and delete TiFlash replicas
- Global tidb_allow_mpp=on is enabled
Take this SQL as an example:
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 health
Number of TiFlash replicas
Table structure
CREATE TABLE synads_gdt_adgroup
(
adgroup_id
bigint(60) NOT NULL COMMENT ‘Ad group id’,
adgroup_name
varchar(255) DEFAULT NULL COMMENT ‘Ad group name’,
account_id
bigint(60) NOT NULL COMMENT ‘Advertiser account id’,
campaign_id
bigint(60) DEFAULT NULL COMMENT ‘Campaign id’,
site_set
varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Placement set’,
automatic_site_enabled
tinyint(1) DEFAULT NULL COMMENT ‘Is automatic placement enabled’,
optimization_goal
varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Ad optimization goal type’,
billing_event
varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Billing type’,
bid_amount
int(11) DEFAULT NULL COMMENT ‘Bid amount, in cents’,
daily_budget
bigint(20) DEFAULT NULL COMMENT ‘Daily budget of the ad group, in cents, set to 0 means no budget’,
promoted_object_type
varchar(500) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Promotion target type’,
promoted_object_id
varchar(512) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Promotion target id’,
app_android_channel_package_id
varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Android app channel package id’,
mini_game_program_id
varchar(500) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘WeChat mini-game id’,
targeting_id
bigint(100) DEFAULT NULL COMMENT ‘Targeting id, only valid for non-circle of friends ads’,
targeting
varchar(16384) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Detailed targeting settings, storing all targeting conditions’,
targeting_translation
text CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Description of selected targeting conditions’,
is_include_unsupported_targeting
tinyint(1) DEFAULT NULL COMMENT ‘Whether to include unsupported targeting’,
scene_spec
varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Scene targeting’,
begin_date
varchar(25) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Start date of delivery, date format: YYYY-MM-DD, and the date is less than or equal to end_date’,
first_day_begin_time
varchar(25) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Start time of delivery on the first day: HH:ii:ss’,
end_date
varchar(25) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘End date of delivery, date format: YYYY-MM-DD, greater than or equal to today, and greater than or equal to begin_date; if you want to deliver for a long time, pass an empty string’,
time_series
varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Delivery time period’,
configured_status
int(5) NOT NULL COMMENT ‘Customer set status 1 represents AD_STATUS_NORMAL 0 represents AD_STATUS_SUSPEND’,
customized_category
varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Custom category’,
created_time
bigint(20) NOT NULL DEFAULT ‘1585670400’ COMMENT ‘Creation time (timestamp)’,
last_modified_time
bigint(20) DEFAULT NULL COMMENT ‘Last modification time (timestamp)’,
ad_count
bigint(20) DEFAULT NULL COMMENT ‘Number of ads, number of creatives query’,
dynamic_ad_spec
varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Dynamic product ad attributes’,
user_action_sets
varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘User behavior data source’,
additional_user_action_sets
varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Supplementary app user behavior data source, field to be deprecated’,
is_deleted
tinyint(1) NOT NULL COMMENT ‘Is deleted, true: yes, false: no’,
dynamic_creative_id
bigint(20) DEFAULT NULL COMMENT ‘DC dynamic creative id’,
is_rewarded_video_ad
tinyint(1) DEFAULT NULL COMMENT ‘Is it a rewarded video ad’,
cost_guarantee_message
varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Cost guarantee status information description’,
cost_guarantee_status
varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Cost guarantee status, cost guarantee status definition’,
cold_start_audience
varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Cold start seed audience’,
auto_audience
tinyint(1) DEFAULT NULL COMMENT ‘Is system optimization used’,
expand_enabled
tinyint(1) DEFAULT NULL COMMENT ‘Is automatic expansion used’,
expand_targeting
varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT ‘Expansion cannot break through targeting’,
deep_conversion_spec
varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘oCPC/oCPM deep optimization content’,
deep_optimization_action_type
varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘CPC/oCPM deep optimization method configuration’,
poi_list
varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Store id list’,
conversion_id
bigint(20) DEFAULT NULL COMMENT ‘Conversion id’,
deep_conversion_behavior_bid
bigint(20) DEFAULT NULL COMMENT ‘Bid for deep optimization behavior’,
deep_conversion_worth_rate
float DEFAULT NULL COMMENT ‘Expected ROI of deep optimization value’,
android_channel_package_audit_message
varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Ad package channel package audit message’,
system_status
varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Ad group status in the system’,
bid_mode
varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Bidding method’,
status
varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Ad status’,
bid_adjustment
text CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Bid coefficient setting content’,
auto_acquisition_enabled
tinyint(1) DEFAULT NULL COMMENT ‘Is one-click volume enabled’,
auto_acquisition_budget
int(11) DEFAULT NULL COMMENT ‘One-click volume exploration budget, in cents, once set, cannot be modified’,
creative_display_type
varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Creative display type’,
auto_derived_creative_enabled
tinyint(1) DEFAULT NULL COMMENT ‘Is automatic derived video creative enabled’,
smart_bid_type
varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Bid type’,
smart_cost_cap
int(11) DEFAULT NULL COMMENT ‘Under automatic bidding’,
marketing_scene
varchar(256) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT ‘Marketing scene’,
synch_time
varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT ‘Format: yyyy-mm-dd HH:MM:SS, time when synchronization is called’,
synch_create_date
varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT ‘Format: yyyy-mm-dd’,
synch_create_hour
int(11) NOT NULL COMMENT ‘Hour value range: 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=‘Sync Guangdiantong to get ad group data’
PARTITION BY LIST (configured_status
)
(PARTITION p0
VALUES IN (0),
PARTITION p1
VALUES IN (1))
Execution plan