join、group查询优化

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

  • 【TiDB 版本】:3.0.9
  • 【问题描述】: batchtmp、dm_device为百万级、一对一的表。
    connect_result_log为亿级1对多的表
    关联分组查询时间比Mysql还慢1s,sql语句如下:
    SELECT a.serialNum,customer_id CustomerId from batchTmp a LEFT JOIN dm_device b on a.serialNum=b.serial_num LEFT JOIN connect_result_log log on a.serialNum=log.serialNum WHERE a.batchId = 70 AND connectTimeNum BETWEEN 20191006 AND 20200306 GROUP BY a.serialNum HAVING COUNT(log.serialNum)>=1
    查询统计如下:
    因本人是RD、非OP,无法运行脚本,请见谅
    部署环境为 tidb *3 ,pd *2,tikv *3,全部部署在3台 4C、16G、300G SSD服务器上
    求解该如何优化

若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出打印结果,请务必全选并复制粘贴上传。

麻烦补充提供一下对应表的表结构信息

image image

1、可以先确认下统计信息是否准确,关于查看和收集统计信息详见

2、如果执行计划未发生改变,explain analyze 确认下哪个算子耗时比较长, 尝试调大相关的并发参数,比如

  • Table/Index Scan 相关参数
    • tidb_index_lookup_size
    • tidb_index_lookup_concurrency
    • tidb_index_serial_scan_concurrency
    • tidb_distsql_scan_concurrency
  • 算子相关参数
    • tidb_index_join_batch_size
    • tidb_index_lookup_join_concurrency
    • tidb_hash_join_concurrency
    • tidb_projection_concurrency
    • tidb_hashagg_partial_concurrency
    • tidb_hashagg_final_concurrency

执行了set @@global.tidb_index_join_batch_size =1000000 查询时间依然很慢

batch size 没必要调这么大,调大 tidb_index_lookup_join_concurrency ,调整参数在 session 级别设置就好,另外问下这个 SQL 预期的执行时间是多少。

Mysql查询时间在6s,预期能达到3s最好

调整 concurrency 参数有效果吗,a 表和 log 表的关联还可以使用 hint 走 hash join

sorry,拖了这么长时间才回复,
修改了 tidb_index_lookup_join_concurrency 参数没有什么效果,但是我让OP停止了DM,空余出8G内存,查询速度就快了2s,所有考虑后续加一些内存

在没有下线 DM 之前,系统的内存使用情况如何?

不算DM单机空余内存8G

我的意思是没有下线这前,整个机器的内存使用情况如何?有没有 swap?有没有内存占用比较多的情况

没有,目前还在测试阶段,没有任何流量,只有我一个人在测试,
另问个问题,mysql中的SELECT @@IDENTITY 怎么在TIDB中实现呢?

请确认一下楼上提到的信息,因为将 DM 下线了性有就上去了,可能资源占用有关,特别是内存、CPU 相关

可以用 select last_insert_id(); 完成相同的功能

好的,我再持续观察一下,十分感谢

好的,若有问题请继续回贴或者新开贴子