求助一个 SQL 优化

SELECT a.* FROM `tblog` as a 
INNER JOIN `structroot` as b ON a.`ModID`=b.`ModuleID` AND a.`SrcGuid`=b.`DataGUID` 
INNER JOIN (SELECT * FROM `tblog` WHERE `ModID`=357 AND `ID`<2147483647 ORDER BY `ID` desc limit 100) as f ON f.`ModID`=b.`RootModuleID` AND f.`SrcGuid` = b.`RootDataGUID`
WHERE a.`ID`<2147483647
UNION ALL
(SELECT * FROM `tblog` WHERE `ModID`=357 AND `ID`<2147483647 ORDER BY `ID` desc limit 100)

如图，有括号的俩句实际都是一个查询。所以我想优化成一句这是我想象的语法，但是就是跑不通，所以让大佬看看呢

SELECT a.* FROM `tblog` as a 
INNER JOIN `structroot` as b ON a.`ModID`=b.`ModuleID` AND a.`SrcGuid`=b.`DataGUID` 
INNER JOIN (SELECT * FROM `tblog` WHERE `ModID`=357 AND `ID`<2147483647 ORDER BY `ID` desc limit 100) as f ON f.`ModID`=b.`RootModuleID` AND f.`SrcGuid` = b.`RootDataGUID`
WHERE a.`ID`<2147483647
UNION ALL
f

Supplement 1 · Feb 2, 2024

用 with 完成了，基本和 @alwaysdazz 的一样。
多谢大家

9 replies • 2024-02-02 17:43:43 +08:00

lianyue

Feb 2, 2024

手写 sql ？
这不影响性能优化他干嘛。

lianyue

Feb 2, 2024

https://www.google.com/search?q=sql+%E5%A4%8D%E7%94%A8+with

xuecat

Feb 2, 2024

影响啊，因为有俩句一模一样的查询，我时间就 double 了。所以我想看看能不能只查一次

Duolingo

Feb 2, 2024

mysql8 以上可以用 with 语句抽出公共部分，低版本就不知道了。

mightybruce

Feb 2, 2024

mysql 将这个查询语句创建为一个视图，
这两句改写为在视图上操作。

dog82

Feb 2, 2024

用 with 子句

declandragon

Feb 2, 2024

CREATE TEMPORARY TABLE TempTable AS
(SELECT * FROM `tblog` WHERE `ModID`=357 AND `ID`<2147483647 ORDER BY `ID` desc limit 100);

SELECT a.* FROM `tblog` as a
INNER JOIN `structroot` as b ON a.`ModID`=b.`ModuleID` AND a.`SrcGuid`=b.`DataGUID`
INNER JOIN TempTable as f ON f.`ModID`=b.`RootModuleID` AND f.`SrcGuid` = b.`RootDataGUID`
WHERE a.`ID`<2147483647
UNION
SELECT * FROM TempTable;

yidinghe

PRO

Feb 2, 2024

@xuecat 耗时 double 的原因在于 UNION ALL ，所以即便用 WITH 提取出来，恐怕也无济于事。

alwaysdazz

Feb 2, 2024 via Android

WITH FilteredTBlog AS (
SELECT * FROM `tblog`
WHERE `ModID`=357 AND `ID`<2147483647
ORDER BY `ID` DESC
LIMIT 100
)
SELECT a.*
FROM `tblog` AS a
INNER JOIN `structroot` AS b ON a.`ModID` = b.`ModuleID` AND a.`SrcGuid` = b.`DataGUID`
INNER JOIN FilteredTBlog AS f ON f.`ModID` = b.`RootModuleID` AND f.`SrcGuid` = b.`RootDataGUID`
WHERE a.`ID` < 2147483647
UNION ALL
SELECT * FROM FilteredTBlog