简单的MySQL子查询性能 [英] Simple MySQL Subquery Performance

问题描述

考虑2个MySQL查询:

Consider the 2 MySQL queries:

SELECT ue.userid,e.courseid 
FROM (SELECT id,courseid FROM mdl_enrol WHERE status = 0 AND courseid IN (46)) e 
INNER JOIN (SELECT enrolid,userid FROM mdl_user_enrolments ) ue ON ue.enrolid = e.id 
INNER JOIN (SELECT userid FROM mdl_userdata) ud ON ue.userid = ud.userid

-

SELECT ue.userid,e.courseid 
FROM mdl_enrol e 
INNER JOIN mdl_user_enrolments ue ON ue.enrolid = e.id 
INNER JOIN mdl_userdata ud ON ue.userid = ud.userid
WHERE e.status = 0 AND e.courseid IN (46)

底部查询比顶部查询快得多，但是为什么呢?我读过要提高性能，您应该只选择所需的列.另外，对我来说，最上面的查询应该表现得更好，因为在每个JOIN中，您正在减少要加入的数据量.显然，我对数据库工作原理的理解是错误的，但是如果有人可以解决这个问题，将不胜感激. EXPLAIN还可以确认底部查询的速度要快得多.

The bottom query is much much faster than the top query, but why? I've read that to increase performance you should only select the columns you need. Also, to me the top query should perform better because in each JOIN, you're reducing the amount of data you're joining. Obviously my understanding of how databases work is wrong, but if anyone could clear this up it would be much appreciated. An EXPLAIN also confirms that the bottom query is much faster.

非常感谢.

推荐答案

在第一个查询中，mysql应该从mdl_enrol表中选择一个子集并将 complete mdl_user_enrolments和mdl_userdata放入内存.因此，您在内存中选择了很多数据.完成之后-加入数据.如果没有足够的内存来放置所有数据，直到它们被加入并发送回客户端为止，则会在硬盘驱动器上创建临时表.最有可能的mysql优化器不够强大，无法解决您的错误并尝试改善执行计划.这就是为什么它很慢.

In the first query mysql should select a subset from mdl_enrol table and complete mdl_user_enrolments and mdl_userdata into the memory. So you select a lot of data in memory. After you've done that - you join the data. If there is not enough memory to put all the data until it's joined and sent back to the client - then temporary table on the hard drive is created. Most likely mysql optimizer isn't cool enough to fix your mistake and try to improve execution plan. That's why it's slow.

对于第二个查询，mysql知道它到底需要选择什么，并且仅选择少量的所需数据.在这种情况下，可以使用索引(假设已经创建了所有必要的索引).这样很快.

Whereas for the second query mysql knows what exactly it needs to select and only selects the small amount of required data. In this scenario it's possible to use indexes (assuming all the necessary indexes have been created). So it's fast.

这篇关于简单的MySQL子查询性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！