请教下关于百万数据表group by和分页oom问题,谢谢!

【 TiDB 使用环境】

【概述】
1、 tidb和mysql数据库对比,同样的SQL,在百万级表数据查询时,如果group+分页的话,mysql可以正常返回,但tidb则会出来oom情况。
2、如果是只是count统计表数据,则tidb效率会比mysql要高。

【背景】 mysql和tidb在count、以及分组分页场景性能测试。

【现象】 tidb在count场景效率较高,mysql在分组和分页占优。

【问题】 tidb在与mysql数据库对比时,count效率较高,但是分组和分页则会有oom情况,请问这个正常么

【业务影响】 无

【TiDB 版本】 5.3

【应用软件及版本】

count语句:
select count(*) from ( select id, xzxdrlb, xzxdrmc, xzxdrdm_1, xzxdrdm_2 , xzxdrdm_3, xzxdrdm_4, xzxdrdm_5, xzxdrdm_6, fddbrxm , fddbrzjhm, zjlx, zjhm, xzcfjdswh, wfxwlx , cfsy, cfyj, cflb, cfnr, fkje , ms, zkdx, cfjdrq, cfycq, gsjzrq , cfjg, cfjgtystydm, sjlydw, sjlydwtyshxydm, bz , rowcheck, reportstate, sourcetype, reportusercode, reportdeptcode , updateusercode, updatedeptcode, dissenttype, isvalid, isdelete , reporttime, updatetime, etablename, fddbrzjlx, historyid , repairstate, confirmremark, reportingtime, update_dept_id, remark , create_user, update_user, create_time, update_time, del_flag , dept_id, version from pprs_punish where del_flag = ‘0’ and reportstate = ‘1’ and isvalid = ‘1’ and isdelete = ‘0’ order by reportingtime desc ) temp_data_scope inner join creditx.sys_dept_relation t1 on temp_data_scope.dept_id = t1.descendant and t1.ancestor = 1

group by
select * from ( select id, xzxdrlb, xzxdrmc, xzxdrdm_1, xzxdrdm_2 , xzxdrdm_3, xzxdrdm_4, xzxdrdm_5, xzxdrdm_6, fddbrxm , fddbrzjhm, zjlx, zjhm, xzcfjdswh, wfxwlx , cfsy, cfyj, cflb, cfnr, fkje , ms, zkdx, cfjdrq, cfycq, gsjzrq , cfjg, cfjgtystydm, sjlydw, sjlydwtyshxydm, bz , rowcheck, reportstate, sourcetype, reportusercode, reportdeptcode , updateusercode, updatedeptcode, dissenttype, isvalid, isdelete , reporttime, updatetime, etablename, fddbrzjlx, historyid , repairstate, confirmremark, reportingtime, update_dept_id, remark , create_user, update_user, create_time, update_time, del_flag , dept_id, version from pprs_punish where del_flag = ‘0’ and reportstate = ‘1’ and isvalid = ‘1’ and isdelete = ‘0’ order by reportingtime desc ) temp_data_scope inner join creditx.sys_dept_relation t1 on temp_data_scope.dept_id = t1.descendant and t1.ancestor = 1 limit 20

1 个赞

OOM的话可以试着把tidb_mem_quota_query这个参数调大看看:
https://docs.pingcap.com/zh/tidb/dev/system-variables/#tidb_mem_quota_query

能不能把执行计划补充一下

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。