tidb简单关联查询都无法查

[fzy97212]

【TiDB 使用环境】生产环境
【TiDB 版本】v6.5.6
【操作系统】
SELECT
UR.ROLE_ID,
UR.USER_ID,
UR.USER_ROLE_ID,
UR.IS_USED,
R.ROLE_NAME,
R.ORG_RANGE_ID,
R.IS_USED
FROM
UPMS_USER_ROLE UR,
UPMS_ROLE R
WHERE
UR.DEL_FLAG = ‘0’
AND R.DEL_FLAG = ‘0’
AND UR.ROLE_ID = R.ROLE_ID
AND R.IS_USED = ‘1’
AND UR.IS_USED = ‘1’
ORDER BY
R.ORDER_NUM ASC

1105 - Your query has been cancelled due to exceeding the allowed memory limit for the tidb-server instance and this query is currently using the most memory. Please try narrowing your query scope or increase the tidb_server_memory_limit and try again.[conn=8819281722441574333]
时间: 9.41s

一个表只有5条记录，一个表4条记录，tidb_mem_quota_query 设了20G，还是不行，服务器性能很好，内存500G，只用几十G，cpu也正常，遇到这种问题该如何查，大家帮我提点建议

[db_user]

表多大，满足条件的数据量多大，查询的字段有没有大字段，关联字段有没有索引，如果需要扫描和join的行数很多，确实会超过你设置的内存，你说内存只用了几十G，是因为你限制了20G，所以内存也不会更大，如果确定没有影响，且查询没有优化（必须加索引，换join方式）那只能加大参数了

[TiDBer_HErMeXDz]

没有遇到过。。tidb日志也发出来看看。

[zhanggame1 (Ti D Ber G I13ecx U)]

是不是原始数据有问题，你考虑下重建表 原来表drop了建新的再查

[Tfzxc]

这也不是个多复杂的SQL，随便一个数据库都能满足吧，关健是参数的设置和满足条件的数据

[麻辣机师 (Ti D Ber N Ec Hp7n S)]

COUNT 试一下

[fzy97212]

time:2.09s, loops:2, build_hash_table:{total:2.09s, fetch:2.09s, build:27.4µs}, probe:{concurrency:5, total:10.5s, max:2.09s, probe:30.6µs, fetch:10.5s}
time:208µs, loops:2, cop_task: {num: 1, max: 1.39ms, proc_keys: 5, rpc_num: 1, rpc_time: 1.31ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}
tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 5, total_process_keys_size: 1422, total_keys: 8, get_snapshot_time: 33.1µs, rocksdb: {key_skipped_count: 7, block: {cache_hit_count: 4}}}
tikv_task:{time:0s, loops:1}
time:1.33ms, loops:2, cop_task: {num: 1, max: 1.35ms, proc_keys: 5, rpc_num: 1, rpc_time: 1.26ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}
tikv_task:{time:1ms, loops:1}, scan_detail: {total_process_keys: 5, total_process_keys_size: 1422, total_keys: 8, get_snapshot_time: 35µs, rocksdb: {key_skipped_count: 7, block: {cache_hit_count: 4}}}
tikv_task:{time:1ms, loops:1}

[wzf0072]

@fzy97212

Your query has been cancelled

tidb_server_memory_limit 设置的多少？报错提示要增大tidb_server_memory_limit 值。这个值限制了TiDB Server内存的使用（内存500G，只用几十G）
SET GLOBAL tidb_server_memory_limit = “256GB”;

还有一种方式是 set global tidb_mem_oom_action=‘CANCEL’ ；
该变量控制当单个查询使用的内存超过限制 (tidb_mem_quota_query ) 且不能再利用临时磁盘时，TiDB 所采取的操作。#
https://docs.pingcap.com/zh/tidb/stable/system-variables/#tidb_mem_oom_action-从-v610-版本开始引入

tidb_mem_oom_action 从 v6.1.0 版本开始引入

• 作用域：GLOBAL
• 是否持久化到集群：是
• 是否受 Hint SET_VAR 控制：否
• 类型：枚举型
• 默认值：CANCEL
• 可选值：CANCEL，LOG
• 该变量控制当单个查询使用的内存超过限制 (tidb_mem_quota_query) 且不能再利用临时磁盘时，TiDB 所采取的操作。详情见 TiDB 内存控制。
• 该变量默认值为 CANCEL，但在 TiDB v4.0.2 及之前的版本中，默认值为 LOG。
• 在 v6.1.0 之前这个开关通过 TiDB 配置文件 (oom-action) 进行配置，升级到 v6.1.0 时会自动继承原有设置。

[fzy97212]

单表查询可以，关联表查询就很慢

[lllzd (时光旅行者)]

看你报错日志是因为内存超限被取消， 建议优先检查并调大 tidb_server_memory_limit 配置（如设为 400GB）。

[wzf0072]

@fzy97212

UR.DEL_FLAG = ‘0’
AND R.DEL_FLAG = ‘0’
AND UR.ROLE_ID = R.ROLE_ID
AND R.IS_USED = ‘1’
AND UR.IS_USED = ‘1’
ORDER BY
R.ORDER_NUM ASC

explain analyze 查看实际执行计划

[TiDBer_sheldon (宣恩裕)]

explain看看执行计划的估计值是多少？

[TiDBer_sheldon (宣恩裕)]

@fzy97212

SELECT
UR.ROLE_ID,
UR.USER_ID,
UR.USER_ROLE_ID,
UR.IS_USED,
R.ROLE_NAME,
R.ORG_RANGE_ID,
R.IS_USED
FROM
UPMS_USER_ROLE UR,
UPMS_ROLE R
WHERE
UR.DEL_FLAG = ‘0’
AND R.DEL_FLAG = ‘0’
AND UR.ROLE_ID = R.ROLE_ID
AND R.IS_USED = ‘1’
AND UR.IS_USED = ‘1’

SELECT
count(*)
FROM
UPMS_USER_ROLE UR
inner join UPMS_ROLE R UR.ROLE_ID = R.ROLE_ID
WHERE
UR.DEL_FLAG = ‘0’
AND R.DEL_FLAG = ‘0’
AND R.IS_USED = ‘1’
AND UR.IS_USED = ‘1’
这样能出来吗？每行的数据多大？

[Kongdom]

@fzy97212

一个表只有5条记录，一个表4条记录

这个是原表数据，还是执行筛选后的数据？按说就算是全表扫描，一共才9条数据的表也不至于占用这么大的内存啊。这个表是频繁新增删除么？做过admin check table么？会不会是索引和表数据不一致导致的？

[sd4544763 (Ti D Ber Oj Ng Muip)]

能不能用ANALYZE TABLE刷一下那两个表试试
再用EXPLAIN看看是不是走了啥奇怪的执行计划。

[独善其身 (Ti D Ber Bi Rqfz5 K)]

这个情况trace看看,然后用执行计划看看走索引了吗,同时看看系统内存使用情况