為什麼在我的查詢中添加 LIMIT 會使其爬網？

簡單查詢：

select sum(score) total,name,gender,dob,country 
from users join scores on users.id = scores.user_id
where date between '2012-01-01' and '2012-01-31 23:59:59'
group by scores.user_id having sum(score)>=1000 order by sum(score) desc limit 50

因此，嘗試獲取 2012 年 1 月的累積分數列表，按分數降序排列並分頁。

無限制：緩慢但正常：搜尋 69348 行。（很高興弄清楚如何避免臨時表，但我不能）。解釋說：

1, 'SIMPLE', 'scores', 'range', 'user,date,user+date', 'date', '8', '', 69348, 'Using where; Using temporary; Using filesort'
1, 'SIMPLE', 'users', 'eq_ref', 'PRIMARY', 'PRIMARY', '8', 'scores.user_id', 1, 'Using where'

有限制：它是相同的，但行搜尋現在是 1806794 並且它需要永遠。

如果這有什麼不同的話，它就是一個分區的 InnoDB，所有數據都在一個分區上。

要嘗試的事情：

在上添加索引(user_id, date, score)

僅在scores表上分組，然後加入users：

SELECT s.total, u.name, u.gender, u.dob, u.country
FROM users AS u
 JOIN 
 ( SELECT user_id, SUM(score) AS total
   FROM scores
   WHERE date >= '2012-01-01' AND date < '2012-02-01'
   GROUP BY user_id
   HAVING SUM(score) >= 1000
   ORDER BY total DESC LIMIT 50
 ) AS s
     ON u.id = s.user_id
ORDER BY total DESC ;

使用正確的 ANSI group by（不是 MySQL可憎擴展），看看會發生什麼

select sum(score) total,name,gender,dob,country  
from users join scores on users.id = scores.user_id
where date between '2012-01-01' and '2012-01-31 23:59:59'
group by name,gender,dob,country
having sum(score)>=1000
order by sum(score) desc limit 50

為什麼？

MySQL 中的 GROUP BY 意味著 ORDER BY 導致此處的文件排序

將查詢更改為更標準可能有助於優化器

更多關於 MySQL abominations擴展：

• https://stackoverflow.com/a/6642253/27535
• https://stackoverflow.com/a/10894876/27535
• https://stackoverflow.com/a/6457144/27535

來自 MySQL Docs on ORDER BY Optimization

在某些情況下，MySQL 不能使用索引來解析 ORDER BY，儘管它仍然使用索引來查找與 WHERE 子句匹配的行。這些案例包括：

…

您有不同的 ORDER BY 和 GROUP BY 表達式。

引用自：https://dba.stackexchange.com/questions/41828