join、group查询优化

[Dwana (Wangcheng)]

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

• 【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服务器上
  求解该如何优化

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

[GangShen]

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

[Dwana (Wangcheng)]

[qizheng]

1、可以先确认下统计信息是否准确，关于查看和收集统计信息详见
https://pingcap.com/docs-cn/stable/reference/performance/statistics/

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

[Dwana (Wangcheng)]

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

[qizheng]

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

[Dwana (Wangcheng)]

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

[qizheng]

调整 concurrency 参数有效果吗，a 表和 log 表的关联还可以使用 hint 走 hash join
https://pingcap.com/docs-cn/stable/reference/performance/optimizer-hints/#tidb_hjt1-t2

[Dwana (Wangcheng)]

@qizheng-PingCAP

tidb_index_lookup_join_concurrency

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

[飞与非-PingCAP (飞与非)]

@Dwana

tidb_index_lookup_join_concurrency

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

[Dwana (Wangcheng)]

不算DM单机空余内存8G

[飞与非-PingCAP (飞与非)]

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

[Dwana (Wangcheng)]

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

[飞与非-PingCAP (飞与非)]

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

[飞与非-PingCAP (飞与非)]

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

[Dwana (Wangcheng)]

@飞与非-PingCAP

select last_insert_id();

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

[飞与非-PingCAP (飞与非)]

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