mysql - 如何改进SQL查询？

Question

我有如下查询(旨在获得相同的结果)，并且我想优化查询以减少数据库服务器的 CPU 使用率。

我尝试了下面两个不同的查询，似乎两个查询具有相似的性能。 (使用SHOW PROFILES检查)

如有任何意见或建议，我们将不胜感激。

提前谢谢您。

信息:

Table A has 7333 rows    
Table B has 5656 rows    
Table C has 1108 rows    
Table D has ~150 rows (keep changing)    
Table E has 18576 rows

查询1:

SELECT A.ct, A.res, A.ctype, A.cweight, B.lat, B.lng, C.first, C.second, C.third
FROM A
JOIN B
ON A.ct= B.dct
JOIN C
ON A.ct = C.dct
LEFT JOIN 
( 
SELECT D.dct
FROM D
JOIN E
ON D.oct = E.oct
WHERE E.oct is null
OR (E.oct is not null 
AND E.wua <> '')
) as K
ON A.ct = K.dct
WHERE A.type = 1 AND B.lng != '' AND B.lat != '' AND A.black != 1 AND B.modi_time > (NOW() + INTERVAL 8 HOUR) 
ORDER BY A.ct DESC

查询2:

SELECT A.ct, A.res, A.ctype, A.cweight, B.lat, B.lng, C.first, C.second, C.third
FROM A 
JOIN B 
ON A.ct = B.ct
JOIN C 
ON A.ct = C.dct
WHERE A.type = 1 AND B.lng != '' AND B.lat != '' AND A.black != 1 AND B.modi_time > (NOW() + INTERVAL 8 HOUR) 
AND A.ct NOT IN ( 
SELECT D.dct
FROM D 
JOIN E
ON D.oct = E.oct
WHERE E.wua <> ''
)
ORDER BY A.ct DESC;

Answer 1

根据我的经验，尝试为值 ( NOW() + INTERVAL 8 HOUR ) 声明一个变量，然后在查询中使用该变量，如下所示:

AND B.modi_time>@variable

这可能会提高您的查询性能。