排序和limit无法下推的SQL优化

Lawrence · 2021 年8 月 17 日 08:46

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：
【 TiDB 使用环境】
v4.0.13

【概述】场景 + 问题概述

SELECT doc_id,pub_code,MAX(pub_time) AS pub_time FROM fact_3860
WHERE pub_code = “bg_m_sinacn”
GROUP BY doc_id, pub_code
ORDER BY MAX(IF(like_cnt IS NULL, 0, like_cnt) + IF(collect_cnt IS NULL, 0, collect_cnt) + IF(forward_cnt IS NULL, 0, forward_cnt) + IF(reply_cnt IS NULL, 0, reply_cnt)) DESC
LIMIT 100;

这个sql就是一个根据某字段排序取前100条记录，查看执行计划发现 limit 无法下推tiflash，导致会把结果全部推到tidb server 去做统一排序，导致tidb server oom

这个可以优化吗，4.0.13，无法升级5版本的

songxuecheng · 2021 年8 月 17 日 09:48

explain analyze 看下结果

Lawrence · 2021 年8 月 17 日 09:51

这个不太能行，我们测试环境执行了这个sql，直接把测试环境tidb server 弄挂了，所以就看了下这个执行计划，发现order by 和limit 并没有下推下去，理论上来说如果能下推tikv/tiflash，每个region各自 order by 再limit100条推送给tidb ，数据量不会太大的，tidb是不会崩的

xfworld · 2021 年8 月 17 日 09:57

你想先limit 就应该做条件筛选，如果做了group by ，必然会从多个tikv 聚合到 tidb 做分组了，肯定不可能下推的；
看看能不能优化一下SQL

Lawrence · 2021 年8 月 17 日 10:07

这个逻辑就是根据一个字段排序取前100，难道不能在tiflash计算完后，每个region各自排序只取前100 推送给tidb，tidb在做最后的聚合？这个sql 分组后还有百万数据呢，全推到tidb server不合适吧。。。

这个逻辑我觉得在很多场景都会用到吧，分组后排序取前100，如果下推不了，千万、亿级数据都要推送到tidb server 去排序取100？

Lawrence · 2021 年8 月 17 日 11:32

如果无法下推，那这种分组后取top 100的 sql该怎么优化呢

yilong · 2021 年8 月 19 日 08:25

表结构方便发一下吗？

Lawrence · 2021 年8 月 19 日 08:59

大佬，你终于来了

CREATE TABLE fact_3860 (
id varchar(40) NOT NULL,
doc_id varchar(40) DEFAULT NULL,
account_id varchar(40) DEFAULT NULL,
pub_code varchar(40) DEFAULT NULL,
pub_type varchar(2) DEFAULT NULL,
pub_time datetime DEFAULT NULL,
subject1_id varchar(50) DEFAULT NULL,
subject2_id varchar(50) DEFAULT NULL,
subject3_id varchar(50) DEFAULT NULL,
subject4_id varchar(50) DEFAULT NULL,
subject5_id varchar(50) DEFAULT NULL,
subject6_id varchar(50) DEFAULT NULL,
subject_keyword text DEFAULT NULL,
aspect1_id varchar(50) DEFAULT NULL,
aspect2_id varchar(50) DEFAULT NULL,
aspect3_id varchar(50) DEFAULT NULL,
aspect4_id varchar(50) DEFAULT NULL,
aspect5_id varchar(50) DEFAULT NULL,
aspect6_id varchar(50) DEFAULT NULL,
aspect_keyword text DEFAULT NULL,
subject_sentiment_type_id int(11) DEFAULT NULL,
subject_sentiment_keyword text DEFAULT NULL,
sentiment_type_id int(11) DEFAULT NULL,
unit_content text DEFAULT NULL,
bi_status_id varchar(20) DEFAULT NULL,
bi_attr_id int(11) DEFAULT NULL,
process_start_time datetime DEFAULT NULL,
process_end_time datetime DEFAULT NULL,
reply_cnt int(11) DEFAULT NULL,
view_cnt int(11) DEFAULT NULL,
like_cnt int(11) DEFAULT NULL,
forward_cnt int(11) DEFAULT NULL,
dislike_cnt int(11) DEFAULT NULL,
collect_cnt int(11) DEFAULT NULL,
play_cnt int(11) DEFAULT NULL,
haha_cnt int(11) DEFAULT NULL,
love_cnt int(11) DEFAULT NULL,
wow_cnt int(11) DEFAULT NULL,
angry_cnt int(11) DEFAULT NULL,
sad_cnt int(11) DEFAULT NULL,
care_cnt int(11) DEFAULT NULL,
coin_cnt int(11) DEFAULT NULL,
danmaku_cnt int(11) DEFAULT NULL,
pv int(11) DEFAULT NULL,
keyword varchar(1000) DEFAULT NULL,
keyword_frequency varchar(500) DEFAULT NULL,
media_id int(11) DEFAULT NULL,
media_type_id int(11) DEFAULT NULL,
dedup_label varchar(5) DEFAULT NULL,
doc_type varchar(5) DEFAULT NULL,
author_tag varchar(5) DEFAULT NULL,
flag_qc tinyint(1) DEFAULT NULL,
flag_reject tinyint(1) DEFAULT NULL,
PRIMARY KEY (id),
KEY doc_id (doc_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin