ljluestc
commented
1 year ago SELECT `datestr` AS dimension_19700,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_GUANJIANZI' THEN pv ELSE 0 END)) AS measure_35041,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_GUANJIANZI' THEN uv ELSE 0 END)) AS measure_35042,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_CHEXI' THEN pv ELSE 0 END)) AS measure_35043,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_CHEXI' THEN uv ELSE 0 END)) AS measure_35044,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_DIQU' THEN uv ELSE 0 END)) AS measure_35045,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_CHELING' THEN pv ELSE 0 END)) AS measure_35046,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_CHELING' THEN uv ELSE 0 END)) AS measure_35047,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_JIAGE' THEN pv ELSE 0 END)) AS measure_35048,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_JIAGE' THEN uv ELSE 0 END)) AS measure_35049,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_LICHENG' THEN pv ELSE 0 END)) AS measure_35050,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_LICHENG' THEN uv ELSE 0 END)) AS measure_35051,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_YANSE' THEN pv ELSE 0 END)) AS measure_35052,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_YANSE' THEN uv ELSE 0 END)) AS measure_35053,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_PAIFANG' THEN pv ELSE 0 END)) AS measure_35054,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_PAIFANG' THEN uv ELSE 0 END)) AS measure_35055,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_CHANDI' THEN pv ELSE 0 END)) AS measure_35056,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_CHANDI' THEN uv ELSE 0 END)) AS measure_35057,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_BIANSUXIANG' THEN pv ELSE 0 END)) AS measure_35058,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_LEIXING' THEN pv ELSE 0 END)) AS measure_35059,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_LEIXING' THEN uv ELSE 0 END)) AS measure_35060,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_PAIXU' THEN pv ELSE 0 END)) AS measure_35061,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_PAIXU' THEN uv ELSE 0 END)) AS measure_35062,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_TWO' THEN uv ELSE 0 END)) AS measure_35063,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_THREE' THEN pv ELSE 0 END)) AS measure_35064,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_THREE' THEN uv ELSE 0 END)) AS measure_35065,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_FOUR' THEN pv ELSE 0 END)) AS measure_35066,
SUM((CASE WHEN typeid='CHENIU_SOUSUO_FOUR' THEN uv ELSE 0 END)) AS measure_35067
FROM rpt_dm_biz_trackmodel_count_dd
WHERE (`datestr` BETWEEN '2019-03-24' AND '2019-06-21')
GROUP BY dimension_19700
ORDER BY `datestr` DESC
LIMIT 1000;
Regarding the EXPLAIN output, it indicates that the query is performing an IndexScan and a StreamAgg operation. To encourage the use of Hash Aggregation, you can follow the suggestions mentioned in the previous response. Adjust the tidb_hash_join_concurrency and tidb_index_lookup_join_concurrency variables, optimize table indexes, rewrite the query if necessary, and analyze table statistics.
jebter
TiDB version
TiDB Cluster topology
TiDB 8CPU 16G 100G PD 5CPU 8G 200G TiKV 8CPU 32G 200G(SDD)
Question
I use this cluster test a single table aggregation on a 2kw+ table，view the execution plan, the aggregation method is StreamAgg，TiDB documentation have write "hash aggregation will push down tikv to improve the concurrency and reduce the network load."，but I don't understand "If Hash Aggregation is close to the read operator of Table or Index" what does it mean and I don't know how to make my sql to use has agg. my sql execution is slow (3min+), performance bottlenecks are aggregated on the tidb node. Can you help me solve it? I have already set concurrency like set @@session.tidb_distsql_scan_concurrency=40; set @@session.tidb_index_lookup_concurrency=40;
table count
single aggregation scan table total count
SQL
EXPLAIN
Table DDL
System Variables