关于这段SQL语句如何优化?

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

【概述】 场景 + 问题概述

【应用框架及开发适配业务逻辑】

【背景】 做过哪些操作

【现象】 业务和数据库现象

【问题】 当前遇到的问题

【业务影响】 无

【TiDB 版本】v5.3.0

您好,我们在做业务项目中,碰到一个SQL语句,执行结果出现随机值,MYSQL里面运行正常。
请问这段SQL语句在TiDB数据库上如何优化?谢谢

SELECT COUNT(1) FROM (
SELECT m.* FROM (
SELECT id, send_user_id, receive_user_id, send_time, receive_time, content, customer_id, system_id, receive_user_name, send_user_name
FROM tbl_message WHERE send_time >= 1651334400 AND send_time < 1653840000 ORDER BY send_time DESC, id DESC
) AS m GROUP BY m.system_id, m.customer_id ORDER BY m.send_time DESC, m.id DESC
) AS t
WHERE 1=1
AND EXISTS(SELECT 1 FROM tbl_user AS u WHERE u.type = 2 AND u.id = t.receive_user_id)

附件是建表语句sql20220527.sql (4.0 KB)


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

1赞

出现随机值是什么意思

1赞

SELECT
COUNT( 1 )
FROM
(
SELECT
m.*
FROM
(
SELECT
id,
send_user_id,
receive_user_id,
send_time,
receive_time,
content,
customer_id,
system_id,
receive_user_name,
send_user_name
FROM
tbl_message
WHERE
send_time >= 1651334400
AND send_time < 1653840000

	) AS m 
GROUP BY
	m.system_id,
	m.customer_id 

) AS t 

) AS t inner join tbl_user u on t.receive_user_id =u.id where u.type=2

2赞

出现随机值可能是因为 u. id = t. receive_user_id 中的 receive_user_id 不在分组列中(分布式取到的 receive_user_id 可能不一样吧)

count 计算的值随机变化,比如结果是450,460,442等类似的变化

我用你上面发的SQL试试,谢谢啦

SELECT COUNT(1) FROM (
SELECT m.* FROM (
SELECT id, send_user_id, receive_user_id, send_time, receive_time, content, customer_id, system_id, receive_user_name, send_user_name
FROM tbl_message WHERE send_time >= 1651334400 AND send_time < 1653840000 ORDER BY send_time DESC, id DESC
) AS m ORDER BY m.send_time DESC, m.id DESC
) AS t
inner join 
tbl_user AS u on u.id = t.receive_user_id and u.type = 2 

这个你试试看,试了边城元元的,执行后报错

谢谢指正啊!

数据是固定的,为什么会取得不一样,这点我不太能理解:joy:

– 1、准备测试表
– 保证有多个region 使用 SHARD_ROW_ID_BITS
drop table employees;
CREATE TABLE employees (
id bigint not null,
fname VARCHAR(30),
store_id INT NOT NULL,
type int not null default 0
)SHARD_ROW_ID_BITS=5 PRE_SPLIT_REGIONS=4;

– 2、插入基础数据
insert into employees(id,fname,store_id,type) values
(1,‘z1’,1,0),
(2,‘z2’,1,0),
(3,‘z3’,1,1);

– 3、执行 多次,让数据在10万左右
insert into employees(id,fname,store_id,type)
select floor(rand()*10000000),concat(‘z’,floor(rand()*10)),floor(rand()*100),floor(rand()*10) from employees;

– 4、确认有多个region
SHOW TABLE TPCH_001.employees REGIONS;

– 5、验证数据

SET sql_mode=(SELECT REPLACE(@@sql_mode,‘ONLY_FULL_GROUP_BY’,’’));
select type,fname from employees where type=2 GROUP BY type;

– 上面的sql和下面的sql 原理一样
– 此sql每次执行的 fname都不一样
select type,fname from employees where type=2 GROUP BY type ;

image