我在PHP中将MySQL与PDO结合使用,并且我有一个SQL查询,该查询可以正常工作。但是,我关心性能,想知道是否可以改善查询。我也在问,因为我想获得更多有关SQL的背景知识。
假设我有两个表,它们有几个相等的字段(以及一些附加信息,每个表中都有不同):
table `blog_comments`: id, userid (int) | timestamp (int) | content (varchar) | other
table `projects_comments`: id, userid (int) | timestamp (int) | content (varchar) | other
该字段id
是主键,在两个表中userid + timestamp
都有索引,而timestamp只是unixtime,长度为10(整数)。
作为一种简单的垃圾邮件防护措施,我禁止用户提交新评论(无论是博客,项目还是其他任何方式),直到自上次评论以来已过60秒。为此,我从所有评论表中获取了该用户的最新时间戳。
这是我的工作查询:
SELECT MAX(`last_timestamp`) AS `last_timestamp`
FROM
(
SELECT `userid`, max(`timestamp`) AS `last_timestamp`
FROM `blog_comments`
GROUP BY `userid`
UNION ALL
SELECT `userid`, max(`timestamp`) as `last_timestamp`
FROM `projects_comments`
GROUP BY `userid`
) AS `subquery`
WHERE `userid` = 1
LIMIT 0, 1;
如您所见,我在子查询中使用GROUP BY,在主查询中,我仅过滤了用户ID(在本例中为1)。其优点是:我只需要通过该用户ID曾经作为参数。
现在,我对SQL到底如何工作很感兴趣。我认为这将是这样的:首先SQL执行的子查询,集团所有的现有行通过用户名和返回一整套的主查询,然后应用where子句中找到所需要的用户ID。对我来说,这似乎是很大的性能泄漏。
所以我想稍微改变查询:
SELECT max(`last_timestamp`) AS `last_timestamp`
FROM
(
SELECT max(`timestamp`) AS `last_timestamp`
FROM `blog_comments`
WHERE `userid` = 1
UNION ALL
SELECT max(`timestamp`) as `last_timestamp`
FROM `projects_comments`
WHERE `userid` = 1
) AS `subquery`
LIMIT 0, 1
现在,我必须传递两次userid,仍然会针对给定的userid查找整个行集。我不确定这是否真的可以提高性能。
我还没有大量数据可以真正测试,也许以后我会做一些测试。我真的很想知道这些表中何时会有许多数据集,是否会有区别?
在此先感谢您的任何想法,信息和提示。
编辑:
MySQL对第一个查询的解释:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 Using where
2 DERIVED blog_comments range NULL userid 8 NULL 10 Using index for group-by
3 UNION projects_comments index NULL userid 12 NULL 6 Using index
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
MySQL解释第二个查询:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
2 DERIVED NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
3 UNION NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
问题的答案是,由于您给出的原因,第二个在MySQL中应比第一个表现更好。MySQL将对group by
所有数据运行全部,然后选择一组。
通过explain
在查询前面放置一个,您可以看到不同的执行路径。这将使您对查询的实际作用有所了解。
如果您在上具有索引user_id, timestamp
,则第二个查询将仅使用索引即可运行得非常快。即使没有索引,第二个查询也会对两个表进行全表扫描-就是这样。第一个将执行全表扫描和文件排序以进行聚合。第二个需要更长的时间。
如果您只想传递userid
一次,则可以执行以下操作:
select coalesce(greatest(bc_last_timestamp, pc_last_timestamp),
bc_last_timestamp, pc_last_timestamp
)
from (select (SELECT max(`timestamp`) FROM `blog_comments` bc where bc.userid = const.userid
) bc_last_timestamp,
(SELECT max(`timestamp`) FROM `projects_comments` pc where pc.userid = const.userid
) pc_last_timestamp
from (select 1 as userid) const
) t;
该查询看起来很神秘,但它的优化应类似于第二个查询。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句