使用 MySQL 優化聚合和分組

我有一個應用程序，它每天從眾多 API 中提取數據，將其儲存，然後在前端以各種圖表、表格等形式呈現。

我在避免一些問題時遇到了一些問題Using temporary，並且Using filesort一些查詢正在數據庫上執行。

現在，我知道有時這是不可避免的。在這種情況下我是否應該費心改變它？

應用程序記憶體執行這些查詢的大部分請求，因此它們根本不會經常執行。但是，為了最佳實踐和學習，我想確保我以最佳方式執行。

這就是它的原因。有各種各樣的表格，其中包含各種類型的數據。假設一個表結構，其中大多數列是可以聚合的數據（SUM、AVG 等），而任何其他列要麼被忽略，要麼被分組。跨報告數據表一致的一列是一DATE列。由於每天都從 API 中提取數據，因此每個表中的數據都在所述日期儲存。

目前，大多數前端組件都以每月詳細級別向最終使用者顯示數據（儘管對於即將推出的功能，每天都需要 - 這種詳細級別不會受到影響）。

我目前正在使用 MySQL 對數據進行分組和聚合。

這是一個基本的說明：

SELECT
   DATE_FORMAT(c.date, '%Y-%m') as date,
   SUM(c.conversions) as conversions
FROM data_ga_conversions c
WHERE c.goal_id = 1 AND c.date BETWEEN '2014-10-01' AND '2015-02-28'
GROUP BY YEAR(c.date), MONTH(c.date)

請注意GROUP BY使用日期函式的子句。我相信你知道——這就是導致查詢執行器使用臨時和文件排序的原因。（注意：我知道我可以通過添加來擺脫ORDER BY NULL文件排序，但是我試圖將這些基本的排序和聚合計算保存在一個地方，應用程式碼或 SQL。）

我是以錯誤的方式接近這個嗎？這是我考慮過的幾種替代方法：

1. 將年、月和日儲存在不同的列中。
2. 聚合表（à la 物化視圖……切換到 postgresql 也是一個潛在的選擇）。
3. 讓應用程序來做

謝謝！

使用臨時和文件排序並不像每個人都認為的那樣邪惡。

不要將 a 拆分DATE為年、月和日列；它會造成比它值得的更多的痛苦。

我更喜歡（在您的特定情況下）這樣做GROUP BY LEFT(c.date, 7)，儘管它可能不會加快任何速度。

假設您有大表，“匯總表”是該類型查詢的方法。 更多討論在我的部落格中。您將每晚向匯總表添加一個（可能更多）行，然後針對該表的查詢可以匯總任何日期範圍，包括您的月份。

不要儲存AVG(...)在匯總表中；SUM(foo) AS sum_foo儲存and可能更“正確” COUNT(*) AS foo_ct，然後有報告

SUM(sum_foo) / SUM(foo_ct) AS 'Average foo'

可能最佳匯總表會有

PRIMARY KEY(goal_id, date)

併sum_conversions計算自SUM(c.conversions)，加上任何其他相關。通常，一個匯總表可以處理一小組標準“報告”。

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