TiDB开窗函数,如lead,lag函数,执行缓慢,在分钟级,如何优化?

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。

  • 【TiDB 版本】:4.0.0
  • 【问题描述】:
    TiDB开窗函数,如lead,lag函数,执行缓慢。我是用三台阿里云服务器测试的,每台节点8核32G。数据源表的数据量在500w左右,SQL的执行时间达到了1分钟,不满足我们的实时需求所要求的时效性了。我希望能降调秒级。正常如果不开窗,仅仅是做聚合,排序的话,时间在几秒钟左右。
    以及,是否TiDB已经对lag,lead,row_number等开窗函数作了优化?或者以后会做优化吗?或者我可以调整什么参数来解决这个问题?
    sql:
    select
    lead(a.is_circle, 1, 0) over (partition by a.hp_date, a.guid order by a.createtime, a.opentime) as next_status,
    lag(a.is_circle, 1, 0) over (partition by a.hp_date, a.guid order by a.createtime, a.opentime) as last_status
    from (
    select hp_stat_date,
    guid,
    opentime,
    createtime,
    id
    from db_test.web_log – 500w 数据量
    where hp_stat_date = to_date(now())
    ) a
    ) a
  1. 一直是有优化的, TIDB SQL 优化要看执行时间延迟是在哪一层出现的,需要看一下 explain analyze 操作来检查 SQL 是否因为开窗函数慢 ? 还是聚合下推慢。可以根据官方文档排查一下 SQL 的执行计划 和 统计信息是否准确。

https://docs.pingcap.com/zh/tidb/stable/sql-tuning-overview

  1. 如果是 SQL 在开窗函数慢,可以帮忙反馈一下具体的 explain analyze 的执行时间和执行计划。

https://asktug.com/t/topic/348

这个是我的SQL:


这个是explain执行计划:
这个是explain analyze执行计划,我看是Sort操作,还有window操作占据了大部分时间,大约在一分钟左右。

但是我不太清楚如何优化,能否请您帮我看下,给一下建议?

从执行计划看,主要慢在 order by 排序部分,可以试试将 order by 去掉是不是是否比较快。另外可以尝试该写一下 SQL,尝试将order by 逻辑放在 TiKV 层处理。

https://docs.pingcap.com/zh/tidb/stable/expressions-pushed-down

order by 去掉的话,目前看就不太符合业务逻辑了。我试试改写下SQL。
请问能不能通过水平扩展节点,以及加资源的方式来加速执行呢?我目前只是测试,是在3台阿里云服务器上,8核32G,而且tidb,tikv,tispark,tiflash都部署在这三台节点的同一目录下。
如果生产集群优化的话,我可能会部署5台左右的节点。请问是tidb,tikv分界点部署呢,还是可以部署同一个节点,但是不同的磁盘?

目前延迟时间长的地方是在单个 tidb server 的 order by 排序的计算上面,因为数据量比较大,所以 order by 时间会很长。但通过增加水平扩展是不能加速的。如果可以尝试 tidb server 升配, 8 core 32 GB 调整为 16 core 32 GB,看看是否可以增加处理速度。

好的,还想请教下:
1: 只增加tidb server的CPU和内存就可以是吧, 需不需要配置文件里面修改些东西? 我们生产集群是32核128G内存的,我可以部署5~6台生产节点, 到时候我需要提高配置文件里面的什么参数吗?还是说tidb会自动用上这么多的资源?
2: 如果部署5~6台节点的话,我是把tidb,tikv分别部署在不同的3台节点上好,还是把tidb和tikv同时部署在6台节点上好? 我们只能用一块磁盘, 都在/data下,不能分盘.

也可能会部署9台节点, 那我就3台tikv,3台tidb,3台tiflash? 还是9台节点同时部署tikv,tidb,tiflash? 我们不能分盘,所有数据都在同一块磁盘上

理论上是可以的,参数也可以调整,主要参数就是

https://docs.pingcap.com/zh/tidb/stable/system-variables#tidb_window_concurrency-从-v40-版本开始引入

https://docs.pingcap.com/zh/tidb/stable/system-variables#tidb_distsql_scan_concurrency

最好 tidb 不要和存储节点部署 tikv 或者 tiflash在一起,容易出现在 CPU 竞争。存储节点最好也不要部署在一起容易出现 I/O 争抢。最佳建议都单独部署。

好的,还想请教下。
1、我之前测的SQL,做group by 和order by都挺快的,为什么开窗函数里面的order by 和partition by这么慢呢?同样都是几百万的数据量。
2、order by 排序是把数据全部汇总到tidb节点来排序的吗?还是会在tikv做一下预排序? 开窗的排序,是不是因为窗口特别多,所以要做很多次排序?
3、如果使用tispark来完成lead和lag这些开创操作的话,会不会比tidb要快?

  1. 从执行计划看,现在这个 SQL 应该的 group by 和 order by 应该是在 TiDB Server 做的,因为数据量比较大,所以聚合计算很慢。其他时候的 SQL 的 group by 和 order by 都是在 TiKV 完成的下推计算的;
  2. 是的,order by 会在 TiKV 做排序,但是开窗函数是将数据先抓取到 TiDB Server 再进行的排序,所以可以在 SQL 上尝试做一下调整,将排序下推到 TiKV ;
    3.Tispark 应该不支持开窗,可以再确认一下。

好的,谢谢您的回答
请问第二点:在 SQL 上尝试做一下调整,将排序下推到 TiKV. 这个大致怎么操作,可以麻烦您提供下思路吗?
第三点:TiSpark我测试目前可以支持开窗,但是执行时间和tidb开窗差不多,某些场景下可以快两倍左右.
我还有一点疑问想请教下: 请问有没有办法让tidb,或者tispark能用得上更多的资源去进行计算? 感觉这样会快一点, 请问有相关的参数,能够让tidb和tispark可以用上更多的资源吗?还是说目前他们已经随心所欲的尽可能多的用资源了

SQL 建议你根据业务情况做一些调整,比如将 order by 逻辑调整到 selection 算子层,在 where 后面进行排序。

这个帖子中描述的场景就典型 spark 的聚合计算场景,建议使用 tispark 来处理,tidb server 可能不太合适。另外可以尝试 TiDB 的一些参数,可以看一下上面的两个参数 ,在机器配置允许情况,必须将 TiDB Server 提升到 16 core 32 GB

SET SESSION tidb_distsql_scan_concurrency = 10;
SET SESSION  tidb_window_concurrency = 8;

楼上已经提供了相关参数