tiflash跑2个结构相同条件不同的sql，执行效率相差20倍

foxchan · 2025 年8 月 4 日 09:38

【 TiDB 使用环境】生产环境、

表结构

CREATE TABLE `leader_relate_link` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '物理ID',
  `tenant_id` int(11) NOT NULL COMMENT ' 团长id',
  `person_user_id` int(11) NOT NULL DEFAULT '0' COMMENT '负责人ID',
  `link_url` varchar(1000) NOT NULL COMMENT '第三方链接地址',
  `platform` int(2) NOT NULL COMMENT '第三方店铺类型 1抖店,3快手',
  `relate_shop_id` varchar(50) DEFAULT NULL COMMENT '第三方店铺ID',
  `relate_shop_name` varchar(50) DEFAULT NULL COMMENT '第三方店铺名称',
  `relate_shop_phone` varchar(20) DEFAULT NULL COMMENT '第三方店铺电话',
  `relate_shop_logo` varchar(255) DEFAULT NULL COMMENT '第三方店铺LOGO',
  `relate_product_id` varchar(50) NOT NULL COMMENT '第三方商品ID',
  `relate_product_name` varchar(100) NOT NULL COMMENT '第三方商品名称',
  `relate_product_img` varchar(500) NOT NULL COMMENT '第三方商品主图',
  `relate_product_img_list` text DEFAULT NULL COMMENT '第三方商品轮播图集合',
  `relate_product_img_detail_list` text DEFAULT NULL COMMENT '第三方商品详情图集合',
  `relate_first_cid` varchar(50) NOT NULL DEFAULT '0' COMMENT '第三方：一级类目ID',
  `relate_first_cname` varchar(50) DEFAULT NULL COMMENT '第三方：一级类目名称',
  `relate_second_cid` varchar(50) DEFAULT NULL COMMENT '第三方：二级类目ID',
  `relate_second_cname` varchar(50) DEFAULT NULL COMMENT '第三方：二级类目名称',
  `relate_third_cid` varchar(50) DEFAULT NULL COMMENT '第三方：三级类目ID',
  `relate_third_cname` varchar(50) DEFAULT NULL COMMENT '第三方：三级类目ID',
  `relate_fourth_cid` varchar(50) DEFAULT NULL COMMENT '第三方：四级类目ID',
  `relate_fourth_cname` varchar(50) DEFAULT NULL COMMENT '第三方：四级类目ID',
  `relate_brand_id` varchar(50) DEFAULT NULL COMMENT '第三方品牌ID',
  `relate_brand_name` varchar(50) DEFAULT NULL COMMENT '第三方品牌名称',
  `relate_spec_live_price` bigint(15) DEFAULT NULL COMMENT '第三方直播价/售卖价',
  `relate_goods_stock` bigint(20) DEFAULT NULL COMMENT '第三方商品库存',
  `relate_spec_sell_num` int(11) DEFAULT NULL COMMENT '第三方销量',
  `relate_product_score` decimal(8,4) DEFAULT NULL COMMENT '第三方商品体验',
  `relate_shop_score` decimal(8,4) DEFAULT NULL COMMENT '第三方商家服务',
  `relate_logistics_score` decimal(8,4) DEFAULT NULL COMMENT '第三方物流体验',
  `relate_activity_id` varchar(50) DEFAULT NULL COMMENT '第三方活动id',
  `relate_activity_name` varchar(100) DEFAULT NULL COMMENT '第三方活动名称，托管商品存托管活动',
  `relate_promises` varchar(100) DEFAULT NULL COMMENT '第三方服务承诺',
  `relate_product_status` int(11) NOT NULL COMMENT '第三方商品状态，1上架，2下架。',
  `relate_activity_product_status` int(4) DEFAULT '1' COMMENT '活动商品状态 0：待审核；1：推广中；2：申请未通过；3：合作已终止；6：合作已到期',
  `relate_apply_id` bigint(20) DEFAULT '0' COMMENT '第三方-申请ID（活动审核使用）',
  `relate_logistics_info` varchar(100) DEFAULT NULL COMMENT '第三方-商品物流说明',
  `relate_view_num` bigint(20) DEFAULT '0' COMMENT '第三方-近30天浏览量',
  `relate_kol_num` bigint(20) DEFAULT '0' COMMENT '第三方-近30天推广总达人数',
  `relate_yesterday_order_num` bigint(20) DEFAULT '0' COMMENT '第三方-昨天订单数',
  `relate_order_num` bigint(20) DEFAULT NULL COMMENT '第三方-近30天订单数',
  `relate_has_sxt` int(2) DEFAULT '0' COMMENT '第三方-是否具有短视频随心推资质:0否1是',
  `goods_sample_flag` int(2) NOT NULL DEFAULT '0' COMMENT '是否支持寄样，0不支持，1支持',
  `service_margin` decimal(6,4) NOT NULL COMMENT '第三方平台团长服务费率',
  `commission_margin` decimal(6,4) DEFAULT '0.0000' COMMENT '第三方平台达人公开佣金率--普通佣金',
  `activity_commission_margin` decimal(6,4) DEFAULT '0.0000' COMMENT '第三方平台活动佣金率----活动佣金',
  `has_leader_sec` int(2) NOT NULL DEFAULT '0' COMMENT '是否支持二级团申报 0都不支持，1都支持，2不支持采集，支持提报',
  `divide_service_margin_sec` decimal(6,4) DEFAULT NULL COMMENT '给二级团服务费的分成比例',
  `link_start_time` datetime NOT NULL COMMENT '链接起始时间',
  `link_end_time` datetime NOT NULL COMMENT '链接终止时间',
  `sample_fans` int(11) DEFAULT '0' COMMENT '寄样最低粉丝数',
  `sample_sales` int(11) DEFAULT '0' COMMENT '寄样最低30天销量',
  `sample_sales_money` int(11) DEFAULT '0' COMMENT '免费寄样要求：近30天销售额，0无限制',
  `sample_level` int(4) DEFAULT '0' COMMENT '寄样要求达人等级',
  `sample_threshold_flag` int(2) NOT NULL DEFAULT '0' COMMENT '是否有寄样门槛，0没有，1有',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '系统字段,创建时间,不再变化',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '系统字段,修改时间,每次变更自动更新',
  `operator` varchar(30) DEFAULT 'sys' COMMENT '操作人,用户名',
  `review_status` int(2) DEFAULT '0' COMMENT '本地状态，0未上架，1上架，2下架',
  `last_reviewer` varchar(30) DEFAULT NULL COMMENT '最后审核人',
  `review_time` timestamp NULL DEFAULT NULL COMMENT '审核时间',
  `institution_id` varchar(20) NOT NULL COMMENT '团长的机构id',
  `origin_buyin_id` varchar(20) COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '原始团长百应ID',
  `origin_institution_id` varchar(20) DEFAULT '0' COMMENT '原始团长的机构id',
  `origin_institution_name` varchar(50) DEFAULT NULL COMMENT '原始团长名称',
  `origin_institution_phone` varchar(15) DEFAULT NULL COMMENT '原始团长联系方式',
  `origin_activity_id` bigint(20) DEFAULT NULL COMMENT '原始第三方活动id。',
  `partner_type` int(2) NOT NULL COMMENT '合作类型：1商家(一级团商品，商家自建活动商品)，2团长（二级团商品，一级团长报名二级团商品），3托管',
  `partner_shop_id` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '合作的店铺ID（商家合作存店铺ID，团长合作存原始团长机构ID）',
  `partner_shop_name` varchar(50) DEFAULT NULL COMMENT '合作方店铺名称',
  `partner_shop_phone` varchar(13) DEFAULT NULL COMMENT '合作方联系方式',
  `local_first_cid` int(11) DEFAULT NULL COMMENT '映射表的一级类目id',
  `local_first_cname` varchar(50) DEFAULT NULL COMMENT '映射表一级类目名称',
  `goods_sell_point` varchar(1000) DEFAULT NULL COMMENT '商品卖点',
  `goods_slogan` varchar(1000) DEFAULT NULL COMMENT '推广话术',
  `goods_card_img` varchar(255) DEFAULT NULL COMMENT '手卡',
  `relate_sharable` int(2) DEFAULT '0' COMMENT '第三方是否支持分销：0否1是',
  `promote_flg` int(2) DEFAULT '0' COMMENT '是否支持投流：0否1是',
  `promote_start_time` datetime DEFAULT NULL COMMENT '投流开始时间',
  `promote_end_time` datetime DEFAULT NULL COMMENT '投流结束时间',
  `promote_ask` varchar(300) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '投流要求',
  `promote_reward` varchar(600) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '投流奖励',
  `sort_weight` int(11) DEFAULT '0' COMMENT '排序权重',
  `material_flag` int(4) DEFAULT '0' COMMENT '是否支持素材库 0否1是',
  `open_show_flag` int(4) DEFAULT '0' COMMENT '开放显示标志位：0都显示，1只媒介，2只达人',
  `salesman_goods_price` bigint(11) NOT NULL DEFAULT '-1' COMMENT '达人专属价（-1未设置。单位分）',
  `salesman_goods_price_info` varchar(100) DEFAULT NULL COMMENT '达人专属价说明',
  `sample_custom_info` varchar(255) DEFAULT NULL COMMENT '寄样自定义信息',
  `sort_weight_sec` int(11) DEFAULT '0' COMMENT '选品池排序权重：使用二级团分成服务费计算',
  `to_top` int(2) DEFAULT '0' COMMENT '是否置顶：0否1是',
  `to_top_time` datetime DEFAULT NULL COMMENT '置顶时间',
  `relate_category_id` varchar(50) DEFAULT NULL COMMENT '第三方：行业类目ID',
  `relate_category_name` varchar(50) DEFAULT NULL COMMENT '第三方：行业类目name',
  `link_status` int(11) DEFAULT '0' COMMENT '链接状态：0正常。1已提报活动达到最大数',
  PRIMARY KEY (`id`,`tenant_id`) /*T![clustered_index] NONCLUSTERED */,
  UNIQUE KEY `relate_product_id_racid_pl_tid` (`relate_product_id`,`relate_activity_id`,`tenant_id`,`platform`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1455487798 /*T! SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=2 */ COMMENT='推广链接表或商品表'
PARTITION BY RANGE (`tenant_id`)
(PARTITION `p3` VALUES LESS THAN (4),
 PARTITION `p16` VALUES LESS THAN (24),
 PARTITION `p50` VALUES LESS THAN (57),
 PARTITION `p58` VALUES LESS THAN (73),
 PARTITION `p101` VALUES LESS THAN (104),
 PARTITION `p105` VALUES LESS THAN (106),
 PARTITION `p106` VALUES LESS THAN (110),
 PARTITION `p113` VALUES LESS THAN (115),
 PARTITION `p118` VALUES LESS THAN (123),
 PARTITION `p123` VALUES LESS THAN (131),
 PARTITION `p136` VALUES LESS THAN (140),
 PARTITION `p140` VALUES LESS THAN (147),
 PARTITION `p152` VALUES LESS THAN (153),
 PARTITION `p153` VALUES LESS THAN (154),
 PARTITION `p156` VALUES LESS THAN (158),
 PARTITION `p158` VALUES LESS THAN (160),
 PARTITION `p170` VALUES LESS THAN (175),
 PARTITION `p175` VALUES LESS THAN (179),
 PARTITION `p180` VALUES LESS THAN (184),
 PARTITION `p184` VALUES LESS THAN (187),
 PARTITION `p187` VALUES LESS THAN (190),
 PARTITION `p190` VALUES LESS THAN (191),
 PARTITION `p191` VALUES LESS THAN (193),
 PARTITION `p193` VALUES LESS THAN (194),
 PARTITION `p194` VALUES LESS THAN (197),
 PARTITION `p204` VALUES LESS THAN (207),
 PARTITION `p207` VALUES LESS THAN (210),
 PARTITION `p224` VALUES LESS THAN (227),
 PARTITION `p232` VALUES LESS THAN (234),
 PARTITION `p234` VALUES LESS THAN (237),
 PARTITION `p237` VALUES LESS THAN (240),
 PARTITION `p244` VALUES LESS THAN (247),
 PARTITION `p257` VALUES LESS THAN (260),
 PARTITION `p260` VALUES LESS THAN (261),
 PARTITION `p261` VALUES LESS THAN (263),
 PARTITION `p263` VALUES LESS THAN (264),
 PARTITION `p267` VALUES LESS THAN (270),
 PARTITION `p270` VALUES LESS THAN (273),
 PARTITION `p273` VALUES LESS THAN (276),
 PARTITION `p276` VALUES LESS THAN (279),
 PARTITION `p284` VALUES LESS THAN (287),
 PARTITION `p287` VALUES LESS THAN (290),
 PARTITION `p300` VALUES LESS THAN (304),
 PARTITION `p304` VALUES LESS THAN (307),
 PARTITION `p314` VALUES LESS THAN (317),
 PARTITION `p317` VALUES LESS THAN (320),
 PARTITION `p320` VALUES LESS THAN (324),
 PARTITION `p324` VALUES LESS THAN (327),
 PARTITION `p327` VALUES LESS THAN (330),
 PARTITION `p330` VALUES LESS THAN (334),
 PARTITION `p334` VALUES LESS THAN (337),
 PARTITION `p337` VALUES LESS THAN (340),
 PARTITION `p357` VALUES LESS THAN (360),
 PARTITION `p360` VALUES LESS THAN (364),
 PARTITION `p364` VALUES LESS THAN (367),
 PARTITION `p370` VALUES LESS THAN (374),
 PARTITION `p374` VALUES LESS THAN (377),
 PARTITION `p377` VALUES LESS THAN (380),
 PARTITION `p384` VALUES LESS THAN (387),
 PARTITION `p387` VALUES LESS THAN (390),
 PARTITION `p390` VALUES LESS THAN (394),
 PARTITION `p394` VALUES LESS THAN (397),
 PARTITION `p397` VALUES LESS THAN (400),
 PARTITION `p400` VALUES LESS THAN (404),
 PARTITION `p404` VALUES LESS THAN (407),
 PARTITION `p407` VALUES LESS THAN (410),
 PARTITION `p410` VALUES LESS THAN (414),
 PARTITION `p414` VALUES LESS THAN (417),
 PARTITION `p417` VALUES LESS THAN (420),
 PARTITION `p420` VALUES LESS THAN (424),
 PARTITION `p424` VALUES LESS THAN (427),
 PARTITION `p430` VALUES LESS THAN (434),
 PARTITION `p434` VALUES LESS THAN (437),
 PARTITION `p440` VALUES LESS THAN (444),
 PARTITION `p444` VALUES LESS THAN (447),
 PARTITION `p447` VALUES LESS THAN (450),
 PARTITION `p450` VALUES LESS THAN (454),
 PARTITION `p457` VALUES LESS THAN (460),
 PARTITION `p467` VALUES LESS THAN (470),
 PARTITION `p470` VALUES LESS THAN (474),
 PARTITION `p477` VALUES LESS THAN (480),
 PARTITION `p484` VALUES LESS THAN (487),
 PARTITION `p487` VALUES LESS THAN (490),
 PARTITION `p490` VALUES LESS THAN (494),
 PARTITION `p494` VALUES LESS THAN (497),
 PARTITION `p497` VALUES LESS THAN (500),
 PARTITION `p500` VALUES LESS THAN (504),
 PARTITION `p510` VALUES LESS THAN (514),
 PARTITION `p514` VALUES LESS THAN (517),
 PARTITION `p524` VALUES LESS THAN (527),
 PARTITION `p527` VALUES LESS THAN (530),
 PARTITION `p530` VALUES LESS THAN (534),
 PARTITION `p534` VALUES LESS THAN (537),
 PARTITION `p537` VALUES LESS THAN (540),
 PARTITION `p540` VALUES LESS THAN (544),
 PARTITION `p544` VALUES LESS THAN (547),
 PARTITION `p560` VALUES LESS THAN (564),
 PARTITION `p564` VALUES LESS THAN (567),
 PARTITION `p570` VALUES LESS THAN (574),
 PARTITION `p577` VALUES LESS THAN (580),
 PARTITION `p580` VALUES LESS THAN (584),
 PARTITION `p587` VALUES LESS THAN (590),
 PARTITION `p594` VALUES LESS THAN (597),
 PARTITION `p607` VALUES LESS THAN (610),
 PARTITION `p610` VALUES LESS THAN (614),
 PARTITION `p617` VALUES LESS THAN (620),
 PARTITION `p630` VALUES LESS THAN (634),
 PARTITION `p634` VALUES LESS THAN (637),
 PARTITION `p637` VALUES LESS THAN (640),
 PARTITION `p640` VALUES LESS THAN (644),
 PARTITION `p644` VALUES LESS THAN (647),
 PARTITION `p647` VALUES LESS THAN (650),
 PARTITION `p650` VALUES LESS THAN (654),
 PARTITION `p654` VALUES LESS THAN (657),
 PARTITION `p657` VALUES LESS THAN (660),
 PARTITION `p660` VALUES LESS THAN (664),
 PARTITION `p664` VALUES LESS THAN (667),
 PARTITION `p667` VALUES LESS THAN (670),
 PARTITION `p680` VALUES LESS THAN (684),
 PARTITION `p684` VALUES LESS THAN (687),
 PARTITION `p687` VALUES LESS THAN (690),
 PARTITION `p694` VALUES LESS THAN (697),
 PARTITION `p697` VALUES LESS THAN (700),
 PARTITION `p704` VALUES LESS THAN (707),
 PARTITION `p707` VALUES LESS THAN (710),
 PARTITION `p710` VALUES LESS THAN (714),
 PARTITION `p714` VALUES LESS THAN (717),
 PARTITION `p720` VALUES LESS THAN (724),
 PARTITION `p724` VALUES LESS THAN (727),
 PARTITION `p727` VALUES LESS THAN (730),
 PARTITION `p730` VALUES LESS THAN (734),
 PARTITION `p734` VALUES LESS THAN (737),
 PARTITION `p737` VALUES LESS THAN (740),
 PARTITION `p740` VALUES LESS THAN (744),
 PARTITION `p744` VALUES LESS THAN (747),
 PARTITION `p750` VALUES LESS THAN (754),
 PARTITION `p754` VALUES LESS THAN (757),
 PARTITION `p757` VALUES LESS THAN (760),
 PARTITION `p760` VALUES LESS THAN (764),
 PARTITION `p764` VALUES LESS THAN (767),
 PARTITION `p767` VALUES LESS THAN (770),
 PARTITION `p770` VALUES LESS THAN (774),
 PARTITION `p774` VALUES LESS THAN (777),
 PARTITION `p777` VALUES LESS THAN (780),
 PARTITION `p780` VALUES LESS THAN (784),
 PARTITION `p784` VALUES LESS THAN (787),
 PARTITION `p787` VALUES LESS THAN (790),
 PARTITION `p790` VALUES LESS THAN (794),
 PARTITION `p794` VALUES LESS THAN (797),
 PARTITION `p797` VALUES LESS THAN (800),
 PARTITION `p800` VALUES LESS THAN (804),
 PARTITION `p804` VALUES LESS THAN (807),
 PARTITION `p807` VALUES LESS THAN (810),
 PARTITION `p810` VALUES LESS THAN (814),
 PARTITION `p814` VALUES LESS THAN (817),
 PARTITION `p817` VALUES LESS THAN (820),
 PARTITION `p820` VALUES LESS THAN (824),
 PARTITION `p824` VALUES LESS THAN (827),
 PARTITION `p827` VALUES LESS THAN (830),
 PARTITION `p830` VALUES LESS THAN (834),
 PARTITION `p834` VALUES LESS THAN (837),
 PARTITION `p837` VALUES LESS THAN (840),
 PARTITION `p840` VALUES LESS THAN (844),
 PARTITION `p844` VALUES LESS THAN (847),
 PARTITION `p847` VALUES LESS THAN (850),
 PARTITION `p850` VALUES LESS THAN (854),
 PARTITION `p854` VALUES LESS THAN (857),
 PARTITION `p857` VALUES LESS THAN (860))

sql1

EXPLAIN ANALYZE
SELECT
  /*+read_from_storage(tiflash[link])*/
  id,
  tenant_id,
  relate_product_id,
  service_margin,
  commission_margin,
  activity_commission_margin,
  has_leader_sec,
  divide_service_margin_sec,
  local_first_cid,
  platform,
  review_status,
  review_time,
  create_time,
  relate_category_id,
  relate_activity_id,
  institution_id,
  relate_activity_name,
  relate_product_name,
  relate_product_img,
  relate_spec_live_price,
  link_start_time,
  link_end_time
FROM
  leader_relate_link link
WHERE
  link.tenant_id = 859
  AND id IN (
    SELECT
      link.id
    FROM
      (
        SELECT
          id,
          ROW_NUMBER() OVER (
            PARTITION BY relate_product_id
            ORDER BY
              sort_weight DESC,
              id DESC
          ) AS rankIndex
        FROM
          leader_relate_link_store link
        WHERE
          1 = 1
          AND link.relate_product_status = 1
          AND link.institution_id = '7356927793174888719'
          AND link.relate_activity_product_status = 1
          AND link.link_start_time <= NOW()
          AND link.link_end_time > NOW()
          AND link.tenant_id = 859
          AND link.partner_type IN (1, 3)
          AND link.has_leader_sec IN (1, 2)
          AND link.platform = 1
          AND link.link_status = 0
      ) link
    WHERE
      link.rankIndex = 1
  )
ORDER BY
  service_margin DESC;

走的tiflash很快，并且用到了runtimefilter
sql2

EXPLAIN ANALYZE
SELECT
  /*+ read_from_storage(tiflash[link1])*/
  link1.*
FROM
  leader_relate_link link1
WHERE
  tenant_id = 259
  AND id IN (
    SELECT
      link.id
    FROM
      (
        SELECT
          /*+ read_from_storage(tiflash[link])*/
          id,
          ROW_NUMBER() OVER (
            PARTITION BY relate_product_id
            ORDER BY
              id DESC
          ) AS rankIndex
        FROM
          leader_relate_link link
        WHERE
          1 = 1
          AND link.relate_product_status = 1
          AND link.relate_activity_product_status = 1
          AND link.review_status = 1
          AND link.link_start_time <= NOW()
          AND link.link_end_time > NOW()
          AND link.tenant_id IN ('259')
          AND link.platform = 1
          AND link.open_show_flag IN (0, 2)
      ) link
    WHERE
      link.rankIndex = 1
    LIMIT
      0, 10
  )
ORDER BY
  relate_spec_sell_num DESC;

这个却很慢，要20多s

问题：
同样结构的sql，走tiflash，一个花费1s，一个花费28s,里面的子查询单独跑也很快。
如何让sql2实现sql1的执行计划呢
sql1执行计划.txt (34.1 KB)
sql2执行计划.txt (23.1 KB)

Billmay表妹 · 2025 年8 月 4 日 10:49

tiflash 混布了吗？两个跑 sql 的时候集群有没有同步
在跑什么其他的 sql

何明_亿玛 · 2025 年8 月 4 日 11:57

集群没有压力，第二个sql 把子查询的结果10个id 直接写进去走tiflash 就超级快，
子查询本身也超级快，就是在in 子句里的时候最外层的tiflash 就需要28秒