提高 30 億行表的 sql 查詢執行時間

我有一個超過 2 億行的 mysql 表。我也每天寫 100 萬行。該表包含一些大列，例如varchar(255)（保存長網址）

為了對該表執行分析，我創建了 5 個特定索引，它們真正加快了執行時間。（對於某些查詢，從 25+ 分鐘到 2 分鐘）。

問題仍然存在，即 2 分鐘對於一個查詢來說是很多時間。我想為分析和報告執行多個查詢。

此外，該表每天都在迅速增加，我很確定索引已盡可能優化。

**這是集群可以解決我的問題的地方嗎？**即這種大小的表仍然在單個 sql 節點上執行是否不尋常？

還是仍然可以在幾毫秒內在如此大的表中執行查詢？

我的一個範例查詢是：

SELECT name, url, SUM(visits), AVG(price), AVG(loc) FROM mytable
   WHERE sname IN ('white') AND usage IN ('three') AND date BETWEEN '2001-01-01' AND '2003-03-10'
   GROUP BY name, url ORDER BY SUM(visits);

我是集群和 HPC 的新手，一般來說，任何關於我應該在這裡做什麼的建議都值得讚賞。

您的問題中沒有包含很多資訊，這使得提供完整的答案相當困難。但是，僅使用您共享的內容：

這是集群可以解決我的問題的地方嗎？

不見得。集群提供了很多優勢，但它似乎不是您嘗試做的正確解決方案。隨著每天添加一百萬行，您的主系統需要針對寫入進行優化。在談論報告時，您可能會使用針對讀取進行優化的系統。

這種大小的表仍然在單個 SQL 節點上執行是不尋常的嗎？

這裡的異常程度很大程度上取決於業務的需求（和期望）。如果主伺服器出現故障，我希望某個地方有一個熱備用或複制實例可以隨時跳入。每秒插入 11.5 條記錄，停機時間不多。

是否仍然可以在幾毫秒內在如此大的表中執行查詢？

給定足夠的硬體，我不明白為什麼不這樣做。然而，很少有人可以訪問整個數據中心的全部計算能力。

---

一般來說，當我不得不處理這樣的情況時，我會盡量保持簡單，並就人們試圖從系統收集的報告類型提出具體問題。如果有共同的模式，那麼扁平化的歷史表為大家節省了大量的時間。畢竟，既然您可以以一種使長視圖報告更快且同樣準確的方式對其進行匯總，為什麼還要每週查詢 2003 年的相同數據一千次呢？

然而，我解決這類問題的主要方法之一——通常對於每天從遍布全國和太平洋的地震儀和氣象站收集數百萬條記錄的大學來說——是“作弊”並複制實例每天只更新一次或兩次源表。這允許系統可以針對具有無數索引的讀取進行優化，從而使主伺服器針對具有較少索引（如果有的話）的寫入進行優化。

對於普通報告，數據中的模式會被發現並按每小時/每晚的時間表放入匯總表中，從而可以快速生成普通報告。也可以針對複制的實例執行臨時或更具體的查詢，而不必擔心影響主系統的性能。只要生成的報告不需要是“實時的”，這種方法通常是有效的，並且可以在合理的預算內完成，這是管理類型傾向於欣賞的。

請注意，不要將此答案視為超出思考範圍的任何事情。如開頭所述，原始問題中沒有包含很多資訊，例如報告的目標受眾，數據庫正在用於哪些其他任務，查詢歷史數據的頻率與. 目前數據等。當我被要求解決類似的問題時，這只是基於過去經驗的一個選項。

每晚總結一天的數據。然後針對該表執行“報告” 。

請注意，SUM 是總和的總和；AVG 是（總和的總和/計數的總和）。

匯總表可能有（按此順序）

PRIMARY KEY(sname, usage, url, date)

加上像這樣的列

sum_visits,
sum_price,
count_price,
sum_loc

更多討論：http: //mysql.rjweb.org/doc.php/summarytables

我希望這種方法將“分鐘”變成“秒”，而不是“毫秒”。如果你想要毫秒，我們需要討論更多細節；有可能。

（初始化需要很長時間才能遍歷現有的 3B 行，但每晚匯總會快得多。）

建立匯總表後，您可以並且應該刪除不必要的索引；他們陷入了那個大事實表的困境。

如果您計劃最終刪除“舊”數據（例如 90 天之後），您應該在表變得更大之前添加 Partitioning *now 。*請參閱http://mysql.rjweb.org/doc.php/partitionmaint （關鍵是DELETE一百萬行非常昂貴，而DROP PARTITION實際上是即時的。）

請注意使用正確的數據類型以節省空間。（更小 –> 快一點）。

可能單個節點很好，甚至還有一些增長空間。

即使在 HDD 上，12 次插入/秒也不是很快。SSD上沒問題。

雖然我建議每晚進行一次匯總，但也可以在插入每一行時進行匯總——使用 IODKU。這會將 HDD 推到極限，但仍會在 SSD 上留下喘息的空間。它將允許“實時”報告。

你的數據是突發的嗎？它是通過一個客戶來的嗎？可以批量處理（一次多行）嗎？某種形式的批處理可以將 12/sec 擴展到更高的吞吐量。

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