sql 优化以及子查询Hint怎么使用。

tidb版本 4.0.11
执行比较慢, 索引加了。 想试试大表走tiflash。但是发现说明子查询Hint的文档比较难懂。

sql:

explain ANALYZE 
	SELECT
	/*+ read_from_storage(tiflash[@sel_2 dy_video_stats_daily@sel_2]) */
  DISTINCT d.video_id AS id,
  v.title,
  v.cover,
  v.author_id AS authorId,
  v.duration,
  v.aid,
  v.item_id AS itemId,
  v.share_url AS shareUrl,
  v.is_reviewed AS isReviewed,
  a.nickname AS authorName,
  a.avatar,
  v.create_time AS createTime,
  v.update_time AS updateTime,
  v.comment_count AS commentSumCount,
  v.digg_count AS diggSumCount,
  v.share_count AS shareSumCount,
  d.dyShengCount,
  d.commentCount,
  d.diggCount,
  d.playCount,
  d.shareCount,
  d.downloadCount
FROM
  (
    SELECT
      video_id,
      COALESCE (sum(dy_sheng), 0) AS dyShengCount,
      COALESCE (sum(new_comment_count), 0) AS commentCount,
      COALESCE (sum(new_digg_count), 0) AS diggCount,
      COALESCE (sum(new_play_count), 0) AS playCount,
      COALESCE (sum(new_share_count), 0) AS shareCount,
      COALESCE (sum(new_download_count), 0) AS downloadCount
    FROM
      dy_video_stats_daily
    WHERE
      video_id IN (
        SELECT
          DISTINCT b.video_id
        FROM
          dy_video_auto b
        WHERE
          b.brand_id = 345
          AND b.is_del = 0
      )
      AND date >= '2020-10-01'
      AND date <= '2021-04-01'
    GROUP BY
      video_id
  ) d
  LEFT JOIN dy_video v ON v.id = d.video_id
  LEFT JOIN dy_author a ON v.author_id = a.id
ORDER BY
  d.dyShengCount DESC,
  d.diggCount DESC
LIMIT
  50;

explain:

Projection_25	50.00	50	root		time:233.5ms, loops:2, Concurrency:OFF	svda.dy_video_stats_daily.video_id, svda.dy_video.title, svda.dy_video.cover, svda.dy_video.author_id, svda.dy_video.duration, svda.dy_video.aid, svda.dy_video.item_id, svda.dy_video.share_url, svda.dy_video.is_reviewed, svda.dy_author.nickname, svda.dy_author.avatar, svda.dy_video.create_time, svda.dy_video.update_time, svda.dy_video.comment_count, svda.dy_video.digg_count, svda.dy_video.share_count, Column#32, Column#33, Column#34, Column#35, Column#36, Column#37	60.8 KB	N/A
└─Limit_31	50.00	50	root		time:233.5ms, loops:2	offset:0, count:50	N/A	N/A
  └─IndexJoin_127	50.00	50	root		time:233.5ms, loops:1, inner:{total:1.42ms, concurrency:4, task:1, construct:49µs, fetch:1.35ms, build:16.9µs}, probe:180.1µs	left outer join, inner:TableReader_126, outer key:svda.dy_video.author_id, inner key:svda.dy_author.id, equal cond:eq(svda.dy_video.author_id, svda.dy_author.id)	414.7 KB	N/A
    ├─TopN_132(Build)	50.00	50	root		time:232.1ms, loops:3	Column#32:desc, Column#34:desc, offset:0, count:50	48.3 KB	N/A
    │ └─IndexJoin_47	50.00	50	root		time:232ms, loops:2, inner:{total:1.59ms, concurrency:4, task:1, construct:74.6µs, fetch:1.5ms, build:17.8µs}, probe:94.6µs	left outer join, inner:TableReader_46, outer key:svda.dy_video_stats_daily.video_id, inner key:svda.dy_video.id, equal cond:eq(svda.dy_video_stats_daily.video_id, svda.dy_video.id)	382.7 KB	N/A
    │   ├─Projection_52(Build)	50.00	50	root		time:230.5ms, loops:3, Concurrency:OFF	svda.dy_video_stats_daily.video_id, coalesce(Column#26, 0)->Column#32, coalesce(Column#27, 0)->Column#33, coalesce(Column#28, 0)->Column#34, coalesce(Column#29, 0)->Column#35, coalesce(Column#30, 0)->Column#36, coalesce(Column#31, 0)->Column#37	15.0 KB	N/A
    │   │ └─Projection_137	50.00	50	root		time:230.5ms, loops:3, Concurrency:OFF	Column#26, Column#27, Column#28, Column#29, Column#30, Column#31, svda.dy_video_stats_daily.video_id	19.8 KB	N/A
    │   │   └─TopN_55	50.00	50	root		time:230.5ms, loops:3	Column#115:desc, Column#116:desc, offset:0, count:50	87.8 KB	N/A
    │   │     └─Projection_138	45259.42	884	root		time:230ms, loops:5, Concurrency:4	Column#26, Column#27, Column#28, Column#29, Column#30, Column#31, svda.dy_video_stats_daily.video_id, coalesce(Column#26, 0)->Column#115, coalesce(Column#28, 0)->Column#116	220.3 KB	N/A
    │   │       └─HashAgg_56	45259.42	884	root		time:230.1ms, loops:5, PartialConcurrency:4, FinalConcurrency:4	group by:Column#114, funcs:sum(Column#107)->Column#26, funcs:sum(Column#108)->Column#27, funcs:sum(Column#109)->Column#28, funcs:sum(Column#110)->Column#29, funcs:sum(Column#111)->Column#30, funcs:sum(Column#112)->Column#31, funcs:firstrow(Column#113)->svda.dy_video_stats_daily.video_id	2.04 MB	N/A
    │   │         └─Projection_136	45259.42	91508	root		time:228.3ms, loops:92, Concurrency:4	cast(svda.dy_video_stats_daily.dy_sheng, decimal(41,0) BINARY)->Column#107, cast(svda.dy_video_stats_daily.new_comment_count, decimal(32,0) BINARY)->Column#108, cast(svda.dy_video_stats_daily.new_digg_count, decimal(32,0) BINARY)->Column#109, cast(svda.dy_video_stats_daily.new_play_count, decimal(32,0) BINARY)->Column#110, cast(svda.dy_video_stats_daily.new_share_count, decimal(32,0) BINARY)->Column#111, cast(svda.dy_video_stats_daily.new_download_count, decimal(32,0) BINARY)->Column#112, svda.dy_video_stats_daily.video_id, svda.dy_video_stats_daily.video_id	1.28 MB	N/A
    │   │           └─IndexHashJoin_65	45259.42	91508	root		time:227ms, loops:92, inner:{total:832.6ms, concurrency:4, task:4, construct:954.6µs, fetch:727.7ms, build:316.8µs, join:104ms}	inner join, inner:IndexLookUp_62, outer key:svda.dy_video_auto.video_id, inner key:svda.dy_video_stats_daily.video_id, equal cond:eq(svda.dy_video_auto.video_id, svda.dy_video_stats_daily.video_id)	6.61 MB	N/A
    │   │             ├─HashAgg_96(Build)	342.60	886	root		time:9.53ms, loops:6, PartialConcurrency:4, FinalConcurrency:4	group by:svda.dy_video_auto.video_id, funcs:firstrow(svda.dy_video_auto.video_id)->svda.dy_video_auto.video_id	12.0 KB	N/A
    │   │             │ └─IndexLookUp_97	342.60	886	root		time:8.36ms, loops:2, index_task: {total_time: 1.07ms, fetch_handle: 1.06ms, build: 601ns, wait: 5.86µs}, table_task: {total_time: 11.4ms, num: 1, concurrency: 4}		29.3 KB	N/A
    │   │             │   ├─IndexRangeScan_94(Build)	706.94	904	cop[tikv]	table:b, index:IDX_brand_id_is_del(brand_id, is_del)	time:1.05ms, loops:3, cop_task: {num: 1, max: 1.03ms, proc_keys: 904, tot_proc: 1ms, rpc_num: 1, rpc_time: 1.02ms, copr_cache: disabled}, tikv_task:{time:1ms, loops:5}	range:[345 0,345 0], keep order:false	N/A	N/A
    │   │             │   └─HashAgg_86(Probe)	342.60	886	cop[tikv]		time:6.81ms, loops:2, cop_task: {num: 1, max: 6.69ms, proc_keys: 904, tot_proc: 6ms, rpc_num: 1, rpc_time: 6.68ms, copr_cache: disabled}, tikv_task:{time:6ms, loops:1}	group by:svda.dy_video_auto.video_id, 	N/A	N/A
    │   │             │     └─TableRowIDScan_95	706.94	904	cop[tikv]	table:b	tikv_task:{time:6ms, loops:1}	keep order:false	N/A	N/A
    │   │             └─IndexLookUp_62(Probe)	132.10	91508	root		time:725.6ms, loops:95, index_task: {total_time: 156ms, fetch_handle: 155.9ms, build: 21.1µs, wait: 54.2µs}, table_task: {total_time: 2.19s, num: 20, concurrency: 16}		1.64 MB	N/A
    │   │               ├─IndexRangeScan_60(Build)	132.10	91508	cop[tikv]	table:dy_video_stats_daily, index:IDX_video_id_date(video_id, date)	time:151.1ms, loops:102, cop_task: {num: 155, max: 36ms, min: 574.4µs, avg: 8.41ms, p95: 21ms, max_proc_keys: 3996, p95_proc_keys: 1480, tot_proc: 132ms, tot_wait: 143ms, rpc_num: 155, rpc_time: 1.29s, copr_cache: disabled}, tikv_task:{proc max:9ms, min:0s, p80:1ms, p95:2ms, iters:670, tasks:155}	range: decided by [eq(svda.dy_video_stats_daily.video_id, svda.dy_video_auto.video_id) ge(svda.dy_video_stats_daily.date, 2020-10-01 00:00:00.000000) le(svda.dy_video_stats_daily.date, 2021-04-01 00:00:00.000000)], keep order:false	N/A	N/A
    │   │               └─TableRowIDScan_61(Probe)	132.10	91508	cop[tikv]	table:dy_video_stats_daily	time:1.93s, loops:113, cop_task: {num: 1269, max: 109.4ms, min: 434.7µs, avg: 16.3ms, p95: 42.5ms, max_proc_keys: 1969, p95_proc_keys: 170, tot_proc: 4.5s, tot_wait: 12s, rpc_num: 1275, rpc_time: 20.6s, copr_cache: disabled}, tikv_task:{proc max:45ms, min:0s, p80:1ms, p95:6ms, iters:2219, tasks:1269}	keep order:false	N/A	N/A
    │   └─TableReader_46(Probe)	1.00	50	root		time:1.37ms, loops:2, cop_task: {num: 4, max: 1.25ms, min: 662.6µs, avg: 995.4µs, p95: 1.25ms, max_proc_keys: 24, p95_proc_keys: 24, tot_proc: 1ms, rpc_num: 4, rpc_time: 3.95ms, copr_cache: disabled}	data:TableRangeScan_45	N/A	N/A
    │     └─TableRangeScan_45	1.00	50	cop[tikv]	table:v	tikv_task:{proc max:1ms, min:0s, p80:1ms, p95:1ms, iters:4, tasks:4}	range: decided by [svda.dy_video_stats_daily.video_id], keep order:false	N/A	N/A
    └─TableReader_126(Probe)	1.00	27	root		time:1.27ms, loops:2, cop_task: {num: 1, max: 1.2ms, proc_keys: 27, rpc_num: 1, rpc_time: 1.19ms, copr_cache: disabled}	data:TableRangeScan_125	N/A	N/A
      └─TableRangeScan_125	1.00	27	cop[tikv]	table:a	tikv_task:{time:0s, loops:1}	range: decided by [svda.dy_video.author_id], keep order:false	N/A	N/A

https://docs.pingcap.com/zh/tidb/stable/optimizer-hints#read_from_storagetiflasht1_name--tl_name--tikvt2_name--tl_name-

难懂的点是什么?具体怎么不理解?


这个位置, 复杂子查询这里没理解, 表都是t1 t2 t3跟子查询内表明也重复 比如下面这块半知半解。

@sel_1 t1@sel_1


这个文档中有关于查询块的定义解释

我看过文档了。 我上面截图不就是你发的Hint的说明连接么。 感觉你那里没有加载出来我发的截图。
我想问的是下面这句怎么解释,文档里没有说 。

@sel_1 t1@sel_1

比如说 子查询里还套用了子查询应该怎么写。

@sel_1 t1@sel_1@t1@sel_1?

比如这段,就是我主题里发的那段sql


这里应该怎么写。 有点迷糊。
read_from_storage(tiflash[@sel_2 dy_video_stats_daily@sel_2])


如果是多层嵌套子查询的复杂语句时,可以通过 QB_NAME 自定义查询块的名字。

也试过。 可以麻烦给个示例么。就是我主题里的sql。想指定下面两个表用tiflash。
@sel_1 t1@shl_1
前面的shl_1是啥意思。 t1@sel_1 不已经表达 sel_1中的t1表了么。

explain ANALYZE 
	SELECT
	/*+ read_from_storage(tiflash[dy_video_stats_daily@qb1,dy_video_auto@qb2]) */
  DISTINCT d.video_id AS id,
  v.title,
  v.cover,
  v.author_id AS authorId,
  v.duration,
  v.aid,
  v.item_id AS itemId,
  v.share_url AS shareUrl,
  v.is_reviewed AS isReviewed,
  a.nickname AS authorName,
  a.avatar,
  v.create_time AS createTime,
  v.update_time AS updateTime,
  v.comment_count AS commentSumCount,
  v.digg_count AS diggSumCount,
  v.share_count AS shareSumCount,
  d.dyShengCount,
  d.commentCount,
  d.diggCount,
  d.playCount,
  d.shareCount,
  d.downloadCount
FROM
  (
    SELECT /*+ QB_NAME(qb1) */
      video_id,
      COALESCE (sum(dy_sheng), 0) AS dyShengCount,
      COALESCE (sum(new_comment_count), 0) AS commentCount,
      COALESCE (sum(new_digg_count), 0) AS diggCount,
      COALESCE (sum(new_play_count), 0) AS playCount,
      COALESCE (sum(new_share_count), 0) AS shareCount,
      COALESCE (sum(new_download_count), 0) AS downloadCount
    FROM
      dy_video_stats_daily
    WHERE
      video_id IN (
        SELECT
          /*+ QB_NAME(qb2) */
          DISTINCT b.video_id
        FROM
          dy_video_auto b
        WHERE
          b.brand_id = 345
          AND b.is_del = 0
      )
      AND date >= '2020-10-01'
      AND date <= '2021-04-01'
    GROUP BY
      video_id
  ) d
  LEFT JOIN dy_video v ON v.id = d.video_id
  LEFT JOIN dy_author a ON v.author_id = a.id
ORDER BY
  d.dyShengCount DESC,
  d.diggCount DESC
LIMIT
  50;

试下这个行不行。

例子中的 /*+ HASH_JOIN(@sel_1 t1@sel_1, t3) */ 是将两种查询块名字使用方式都用上了:
一种是使用查询块作为 Hint 的第一个参数,与其他参数用空格隔开
另一种是在表名后面加@查询块,用于明确指定哪个查询块中的表

/*+ HASH_JOIN(@sel_1 t1@sel_1, t3) / 等于 /+ HASH_JOIN(t1@sel_1, t3) */

如果上述的 hint 没有用的话,可以先将子查询单独拿出来使用 hint 查询一下看,先确认子查询的确是能走到 TiFlash 的,因为并不是所有的查询都可以无条件指定走到 TiFlash 的。