按月分組花費大量時間

我正在嘗試在一個大（> 10M）表上做一個簡單的每月值總和。但是，它面臨著重大的性能問題（4G innodb 池大小為 7 秒，在本地 db 中，1G innodb 池大小需要一分鐘以上）。腳本看起來相當簡單。

SELECT
 year(date_time),
 month(date_time),
 sum(value)
FROM measurements
WHERE
 source_id = 2
GROUP BY year(date_time), month(date_time)

我嘗試過使用 MySQL 5.7 和 MariaDB 10.2。在這兩種情況下使用 EXPLAIN 提供：

SIMPLE  measurements    ref source_id,source_date   source_id   4   const   4418476 Using where; Using temporary; Using filesort

看來我無法避免using temporary餐桌。該表有 2 個索引：date_time和source_id, date_time。

• INDEX(source_id, date_time, value)是最佳的，部分原因是它是“覆蓋”的。“覆蓋”意味著查詢可以完全由索引中的列處理。 我假設您沒有淡化查詢？
• 無論基數如何，測試的列=都必須排在第一位。否則，如果date_time是第一個，則必須讀取整個索引。
• EXPLAIN估計有44 source_id = 2% (4418476/10M) 的時間。讀取 4M 行比 10M 好很多。
• 請提供SHOW CREATE TABLE；沒有它，我在我的答案中做出猜測。
• 您嘗試的兩個索引都必須在索引 BTree 和數據 BTree 之間反彈。
• 可能需要將 1GB 到 4GB 的塊（16KB - 數據或索引）拉入記憶體（buffer_pool）以滿足您的查詢。 可能它在 7 秒後完全在 4GB 的 buffer_pool 中。在 1GB 中，沒有空間，所以它至少部分受 I/O 限制。
• 建議的索引很容易適合 1GB 的 buffer_pool。此查詢只需要其中的 44%。
• 當您添加我的複合查詢時，去掉作為它前綴的現有查詢；這將是多餘和不必要的。
• 隨著數據的增長，有一天索引會變得太大而無法容納 1GB。這是生活中的事實。
• 但是，通過建構和維護一個 [摘要表( http://mysql.rjweb.org/doc.php/summarytables )，您可以在任一伺服器上使等效查詢花費不到 1 秒的時間，即使隨著表的增長。（它可能有 3 列：source_id, date (no time), and sum(value)。）

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