多线程查询并行导致整体速度慢

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

【概述】 场景 + 问题概述
从数据库中分页查询数据,需要执行2条SQL,获取整体条目数 select count 和 查询数据 select * limit pagestart, size.
在java 标准的mybatis 中先执行count ,再执行 数据查询,串行执行。
假设 执行count 4 s ,执行 数据查询 14 s ,那整体报表展示查询时间 4+14s ,总共18s .

采用多线程方式,主线程先启动 子线程去执行 count ,在自己执行 数据查询,理论上执行时间
14s 多一点点 应该能解决整体的数据查询。

但实际效果却变成 执行 count 超过 12s , 查询数据变成 19s 以上 。

【背景】 做过哪些操作

【现象】 业务和数据库现象
先执行 count ,再执行查询数据


并行执行,先子线程启动 count ,后主线程执行数据查询

【问题】 当前遇到的问题
并行查询导致查询时间 翻倍,是否有可优化的空间,从而实现 整体执行时间 = 数据查询的时间

【业务影响】
数据查询慢

【TiDB 版本】
TIDB 5.0.0
【应用软件及版本】

【附件】 相关日志及配置信息

  • TiUP Cluster Display 信息
  • TiUP CLuster Edit config 信息

监控(https://metricstool.pingcap.com/)

  • TiDB-Overview Grafana监控
  • TiDB Grafana 监控
  • TiKV Grafana 监控
  • PD Grafana 监控
  • 对应模块日志(包含问题前后 1 小时日志)

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

1赞

1、你的count的查询是固定的吗?还是带where条件的?
2、从程序的优化角度来看,如果count的次数很多,可以考虑缓存这个结果,定时更新。
3、从数据库角度来看,可以增加TiFlash来进行count的计算。
4、但是瓶颈是select * limit 分页,应该主要考虑提升这部分的性能?你的数据有多少呢?TiDB的部署环境提供一下。数据量也提供下。表结构提供下。执行计划提供下。

1赞

插个眼关注一下,说一下个人理解哈。
这种分页场景 并行执行时间应该为最久执行时间,串行执行时间为二者累加。从结果上看串行18秒,并行19秒以上,理论上是不应该的,条件一致的情况下应该为14秒。所以可能需要你提比对一下两次执行的完整sql看看sql是否一致。看了一下截图里的sql两次sql感觉有明显不同 一个是/* Memor_quota / 一次为/ agg——to_cop */

1赞

感觉是因为hint不同导致插叙速度不一样了

看描述你的并行指的是应用侧起了多个并行吧?还得看数据里的执行计划

开启预编译模式,会有效果的

使用 Prepare API

对于 OLTP 场景,程序发送给数据库的 SQL 语句在去除参数变化后都是可穷举的某几类,因此建议使用预处理语句 (Prepared Statements) 代替普通的文本执行,并复用预处理语句来直接执行,从而避免 TiDB 重复解析和生成 SQL 执行计划的开销。

目前多数上层框架都会调用 Prepare API 进行 SQL 执行,如果直接使用 JDBC API 进行开发,注意选择使用 Prepare API。

另外需要注意 MySQL Connector/J 实现中默认只会做客户端的语句预处理,会将 ? 在客户端替换后以文本形式发送到服务端,所以除了要使用 Prepare API,还需要在 JDBC 连接参数中配置 useServerPrepStmts = true ,才能在 TiDB 服务器端进行语句预处理(下面参数配置章节有详细介绍)。

https://docs.pingcap.com/zh/tidb/stable/java-app-best-practices

  1. 查询 count 非固定的, 带查询条件,查询条件动态变化 ,group by 动态变化 ,所以整个sql 是动态变化的
  2. 缓存 count 结果是不对的, 底层数据是不断插入更新的,都影响count 的记录数
  3. 数据情况 ,表 2.5亿行记录,上百个字段, 查询数据将会 按不同维度(动态) 统计 2.5亿的期初余额,期间发生额,期末余额。
  4. count 语句和 select limit 数据 是不同, count 只需统计 期末余额,减少2个union 的计算

麻烦提供下面的信息有助于分析:
1、TiDB的部署环境
2、表结构与数据量
3、以及SQL的执行计划
4、按照网页的方法导出TiKV、TiDB的监控:https://metricstool.pingcap.com/#backup-with-dev-tools

count 语句
select
count(1)
from (
SELECT
/*+ read_from_storage(tiflash[hgl_je_line]),agg_to_cop() */
count(je_line_id)
FROM
hgl_je_line
WHERE
tenant_id = 0
AND ledger_code = ‘0L’
AND period_num <= right(‘2021-09’, 2)
AND period_num >= 0
AND period_year = left(‘2021-06’, 4)
AND posted_status = ‘P’
AND company_code >= ‘1000’
AND company_code <= ‘1000’
AND account >= ‘10010101’
AND account <= ‘99999995’
GROUP BY
ledger_code,
account,
company_code,
lc_currency_code,
segment21,
segment20,
segment19,
segment18,
segment17,
segment16,
segment12,
segment11,
segment15
) a

count 执行计划

查询数据
SELECT
ledger_code,
company_code,
account,
lc_currency_code,
sum(lc_accounted_dr_start) AS lc_accounted_dr_start,
sum(lc_accounted_cr_start) AS lc_accounted_cr_start,
sum(lc_accounted_dr_period) AS lc_accounted_dr_period,
sum(lc_accounted_cr_period) AS lc_accounted_cr_period,
sum(lc_accounted_dr_end) AS lc_accounted_dr_end,
sum(lc_accounted_cr_end) AS lc_accounted_cr_end,
segment21,
segment20,
segment19,
segment18,
segment17,
segment16,
segment12,
segment11,
segment15
FROM
(
SELECT
/*+ read_from_storage(tiflash[hgl_je_line]),agg_to_cop() /
ledger_code,
account,
company_code,
lc_currency_code,
0 AS lc_accounted_dr_start,
0 AS lc_accounted_cr_start,
sum(lc_accounted_dr) AS lc_accounted_dr_period,
sum(lc_accounted_cr) AS lc_accounted_cr_period,
0 AS lc_accounted_dr_end,
0 AS lc_accounted_cr_end,
segment21,
segment20,
segment19,
segment18,
segment17,
segment16,
segment12,
segment11,
segment15
FROM
hgl_je_line
WHERE
tenant_id = 0
AND ledger_code = ‘0L’
AND period_num >= 6
AND period_num <= 9
AND period_year = 2021
AND posted_status = ‘P’
AND company_code >= ‘1000’
AND company_code <= ‘1000’
AND account >= ‘10010101’
AND account <= ‘99999995’
GROUP BY
ledger_code,
account,
company_code,
lc_currency_code,
segment21,
segment20,
segment19,
segment18,
segment17,
segment16,
segment12,
segment11,
segment15
UNION ALL
SELECT
/
+ read_from_storage(tiflash[hgl_je_line]),agg_to_cop() /
ledger_code,
account,
company_code,
lc_currency_code,
sum(lc_accounted_dr) AS lc_accounted_dr_start,
sum(lc_accounted_cr) AS lc_accounted_cr_start,
0 AS lc_accounted_dr_period,
0 AS lc_accounted_cr_period,
0 AS lc_accounted_dr_end,
0 AS lc_accounted_cr_end,
segment21,
segment20,
segment19,
segment18,
segment17,
segment16,
segment12,
segment11,
segment15
FROM
hgl_je_line
WHERE
tenant_id = 0
AND ledger_code = ‘0L’
AND period_num >= 0
AND period_num <= 5
AND period_year = 2021
AND posted_status = ‘P’
AND company_code >= ‘1000’
AND company_code <= ‘1000’
AND account >= ‘10010101’
AND account <= ‘99999995’
GROUP BY
ledger_code,
account,
company_code,
lc_currency_code,
segment21,
segment20,
segment19,
segment18,
segment17,
segment16,
segment12,
segment11,
segment15
UNION ALL
SELECT
/
+ read_from_storage(tiflash[hgl_je_line]),agg_to_cop() */
ledger_code,
account,
company_code,
lc_currency_code,
0 AS lc_accounted_dr_start,
0 AS lc_accounted_cr_start,
0 AS lc_accounted_dr_period,
0 AS lc_accounted_cr_period,
sum(lc_accounted_dr) AS lc_accounted_dr_end,
sum(lc_accounted_cr) AS lc_accounted_cr_end,
segment21,
segment20,
segment19,
segment18,
segment17,
segment16,
segment12,
segment11,
segment15
FROM
hgl_je_line
WHERE
tenant_id = 0
AND ledger_code = ‘0L’
AND period_num >= 0
AND period_num <= 9
AND period_year = 2021
AND posted_status = ‘P’
AND company_code >= ‘1000’
AND company_code <= ‘1000’
AND account >= ‘10010101’
AND account <= ‘99999995’
GROUP BY
ledger_code,
account,
company_code,
lc_currency_code,
segment21,
segment20,
segment19,
segment18,
segment17,
segment16,
segment12,
segment11,
segment15
) a
GROUP BY
ledger_code,
account,
company_code,
lc_currency_code,
segment21,
segment20,
segment19,
segment18,
segment17,
segment16,
segment12,
segment11,
segment15
ORDER BY
account ASC
查询数据 执行计划

应用 JAVA中 中 2 个线程,1个发起 count 查询,1个发起 数据查询

2亿条的全表扫描,tidb_distsql_scan_concurrency 加大这个变量参数看看能否加快速度