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