MySQL連接兩個大表很慢

我有兩個表，其中一個包含下載 url 的歷史記錄，而另一個表包含有關每個 url 的詳細資訊。

以下查詢按過去一小時內的重複次數對 URL 進行分組。

SELECT COUNT(history.url) as total, history.url
FROM history
WHERE history.time > UNIX_TIMESTAMP()-3600
GROUP BY history.url
ORDER BY COUNT(history.url) DESC 
LIMIT 30

上面的查詢執行大約需要800ms，不夠快，但可以接受，

但是，將它與記憶體表連接時，新查詢大約需要 25 秒才能執行，這非常慢。

SELECT th.total, th.url, tc.url, tc.json 
FROM (SELECT COUNT(history.url) as total, history.url
     FROM history 
     WHERE history.time > UNIX_TIMESTAMP()-3600
     GROUP BY history.url
     ORDER BY COUNT(history.url) DESC 
     LIMIT 30
) th
INNER JOIN (SELECT cache.url, cache.json FROM cache) tc
   ON th.url = tc.url
GROUP BY th.url
ORDER BY th.total DESC
LIMIT 30

我認為這可能會發生，因為在“tc”中，正在載入整個記憶體表，並且它包含 100 萬多個條目。

當我使用第一個查詢，然後以程式方式迭代結果，然後從記憶體中為每個結果執行 SELECT 查詢時，速度要快得多。反正有沒有加快我的第二個查詢？

PS 我正在使用 InnoDB

UPDATE 使用 EXPLAIN 的第二個查詢的輸出

“歷史”表的結構

“記憶體”表的結構

索引參與 JOIN 謂詞或 WHERE 子句的列通常是一個好主意。一個常見的錯誤是創建多個單列索引而不是更少的多列索引。在這裡，我們可以從 url 和歷史時間中受益（查看針對這些表的所有查詢，您可能會發現可以向這些索引添加額外的列）：

CREATE INDEX x01_history_url ON HISTORY (URL, TIME);
CREATE INDEX x01_cache_url ON CACHE (URL);

其次，嘗試取消嵌套查詢。MySQL 對它能夠執行的查詢重寫類型有限制，因此嵌套可能會導致不必要的成本。

SELECT COUNT(th.url) as total, tc.url, tc.json 
FROM history th
JOIN cache tc
   ON th.url = tc.url
WHERE th.time > UNIX_TIMESTAMP()-3600
GROUP BY tc.url, tc.json
ORDER BY COUNT(th.url) DESC 
LIMIT 30

請注意，此查詢在語義上與您的查詢不同，因此您可能會得到不同的結果。如果這是一個問題，您可能希望像以前一樣將 LIMIT 30 構造保留在子查詢中。您還可以考慮是否可以為 CACHE 添加類似的限制，您必須調查多少 CACHE 行才能總共獲得 30 行是否有上限？

INNER JOIN (SELECT cache.url, cache.json 
           FROM cache
           ORDER BY ? LIMIT ?) tc

對於第一個查詢：

SELECT COUNT(*) as total,  -- * is the common pattern
      url
   FROM history
   WHERE time > NOW() - INTERVAL 1 HOUR
   GROUP BY url
   ORDER BY COUNT(*) DESC 
   LIMIT 30

INDEX(time, url)  -- in this order
INDEX(url, time)  -- maybe this order

使用兩個索引；不同的 MySQL 版本和不同的時間範圍可能會使用一個索引而不是另一個。

對於第二個查詢，不要不必要地使用派生表：

INNER JOIN (SELECT cache.url, cache.json FROM cache) tc
  ON th.url = tc.url

–>

INNER JOIN cache tc
   ON th.url = tc.url

和cache需要INDEX(url)。

這兩個表是 1:1 還是 1:many 或 many:1 或 many:many？它可能會對外部產生影響GROUP/ORDER/LIMIT。

當你在它的時候，請提供SHOW CREATE TABLE桌子。

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