在 MySQL 中處理數百萬行的數據庫設計
我們正在執行一個應用程序,它收集數據的速度比我們預期的要快得多。為了適應這一點,我們正在重新設計數據庫。在閱讀了這個、這個和這個之後,我不確定我們設計的最佳方法是什麼……考慮到我們的硬體非常簡陋。
導致問題的三個主要表:
- 掃描
- 域
- 文件
- 價值觀
目前我們只有一個表來儲存數據。它們之間的關係是:
- 1次掃描->(平均 4x)域->(平均 3000)許多文件->(平均 51000)許多值
- 1 SCAN 指向 DOMAINS 上平均 4 個條目。
- DOMAINS 上的 4 個條目指向 DOCUMENTS 上的平均 12.000 個條目
- DOCUMENTS 上的 12000 個條目指向 VALUES 上的平均 204000 個條目
我們目前每天進行大約 100 次掃描。也就是說,每天將大約 20.400.000 個項目插入到 VALUES 中。
我們正在考慮將 VALUES 表拆分為一個“VALUE_table_per_month”:
- VALUES_year_month旨在在它們之間分配負載。但是如果我們增加掃描器的數量,這種機制是不可擴展的。
- VALUES_year_month_day那麼我們最終會將這麼多表放入同一個數據庫中。
在這兩種情況下,如果我們增加每天的掃描次數,似乎沒有一個解決方案是可擴展的。
此時,出於可擴展性的原因,將所有數據保存到集中式數據庫中似乎不是最佳選擇……但同時,分佈式系統將顯著增載入入時間。
什麼是合理的方法?我相信我們不是第一個發現這個問題的團隊!:P
編輯
每個查詢我們讀取多少數據?
這取決於掃描。並非所有掃描都具有相同數量的數據。範圍在以下之間變化:
- 1 次掃描 –> 200 個值
- 1 次掃描 –> 200.000 個值
資訊在前端呈現給最終使用者。因此,我們拆分瞭如何向後端請求查詢以避免伺服器過載,但在某些情況下,由於 VALUES 的數量很大,這還不夠。
什麼時候讀取數據?
這完全取決於最終使用者。有些日子他們每天閱讀 10 次 SCANS,有些則沒有,有些則 100 次。
EDIT II ANALYZE DESCRIBE 結果來自兩個查詢。第一個快,第二個慢。
EXPLAIN ANALYZE SELECT value, url, filetype, severity, COUNT(id_value) AS data_count FROM VALUES WHERE (weigth = 150 OR weigth = 100) AND id_analysis = 23 AND is_hidden = 0 AND is_hidden_by_user = 0 GROUP BY value ORDER BY data_count DESC
結果1:
| -> Sort row IDs: data_count DESC (actual time=34.016..34.016 rows=0 loops=1) -> Table scan on <temporary> (actual time=34.006..34.006 rows=0 loops=1) -> Aggregate using temporary table (actual time=34.005..34.005 rows=0 loops=1) -> Filter: ((VALUES.is_hidden_by_user = 0) and (VALUES.is_hidden = 0) and ((VALUES.weigth = 150) or (VALUES.weigth = 100))) (cost=1.00 rows=0.05) (actual time=0.024..0.024 rows=0 loops=1) -> Index lookup on VALUES using id_analysis (id_analysis=23) (cost=1.00 rows=1) (actual time=0.024..0.024 rows=0 loops=1)
|
結果 2:
| -> Sort row IDs: data_count DESC (actual time=187172.159..187172.173 rows=136 loops=1) -> Table scan on <temporary> (actual time=187172.079..187172.111 rows=136 loops=1) -> Aggregate using temporary table (actual time=187172.077..187172.077 rows=136 loops=1) -> Filter: ((VALUES.is_hidden_by_user = 0) and (VALUES.is_hidden = 0) and ((VALUES.weigth = 150) or (VALUES.weigth = 100))) (cost=264956.35 rows=695) (actual time=249.030..186775.012 rows=52289 loops=1) -> Index lookup on VALUES using id_analysis (id_analysis=8950) (cost=264956.35 rows=265154) (actual time=248.979..186696.529 rows=134236 loops=1) |
編輯三
考慮
PARTITION
_這是一個很好的建議。贊!從我現在讀到的內容來看,這與我們打算做的拆分錶的本地等效。
(weigth = 150 OR weigth = 100)
是一個相當奇怪的測試。刪除
OR
分句可以改善時間:| -> Sort row IDs: data_count DESC (actual time=101261.260..101261.271 rows=113 loops=1) -> Table scan on <temporary> (actual time=101261.187..101261.216 rows=113 loops=1) -> Aggregate using temporary table (actual time=101261.185..101261.185 rows=113 loops=1) -> Filter: ((VALUES.is_hidden_by_user = 0) and (VALUES.is_hidden = 0) and (VALUES.id_analysis = 8950) and (VALUES.weigth = 150)) (cost=79965.29 rows=623) (actual time=83848.835..100942.179 rows=52259 loops=1) -> Intersect rows sorted by row ID (cost=79965.29 rows=62292) (actual time=83848.830..100908.758 rows=52259 loops=1) -> Index range scan on VALUES using id_analysis over (id_analysis = 8950) (cost=291.66 rows=265154) (actual time=0.100..443.145 rows=134236 loops=1) -> Index range scan on VALUES using weigth over (weigth = 150) (cost=13492.63 rows=12380386) (actual time=0.043..83511.686 rows=7822871 loops=1) |
請詳細說明
value
對id_value
我相信這可能只是一個“壞名字”。
+-------------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+-------------+------+-----+---------+----------------+ | id_value | int | NO | PRI | NULL | auto_increment | | id_document | int | NO | MUL | NULL | | | id_tag | int | YES | MUL | NULL | | | value | mediumtext | YES | | NULL | | | weigth | int | YES | MUL | NULL | | | id_analysis | int | YES | MUL | NULL | | | url | text | YES | | NULL | | | domain | varchar(64) | YES | | NULL | | | filetype | varchar(16) | YES | | NULL | | | severity_name | varchar(16) | YES | | NULL | | | id_domain | int | YES | MUL | NULL | | | id_city | int | YES | MUL | NULL | | | city_name | varchar(32) | YES | | NULL | | | is_hidden | tinyint | NO | | 0 | | | id_company | int | YES | | NULL | | | is_hidden_by_user | tinyint(1) | NO | | 0 | | +-------------------+-------------+------+-----+---------+----------------+
不要僅僅因為它很大就拆分錶。如果您需要刪除“舊”數據,
請考慮使用大表。 當寫入數量對於單台機器來說太大時,請考慮“分片”。
PARTITIONing
SSD 設備上每秒插入的 250 行本身不會觸發上述任何拆分原因。
如果您有 2 個月的保留期,那麼建議
PARTITION BY RANGE(TO_DAYS(...))
每月進行一次DROP PARTITION
+REORGANIZE PARTITION
。更多討論: 分區
(weigth = 150 OR weigth = 100)
是一個相當奇怪的測試。100 到 150 之間是否沒有值,或者您是否故意將它們過濾掉?我問是因為OR
優化複雜化。您提出的查詢需要
INDEX(id_analysis, is_idden, is_hidden_by_user, weight)
由於
ONLY_FULL_GROUP_BY
. 我懷疑是否url, filetype, and severity
“依賴”於value
.請詳細說明
value
vsid_value
。這聽起來像是查詢中的另一個錯誤。請詳細說明為什麼
Documents
和Values
是分開的。它聞起來像“過度標準化”。或者也許我真的被這個名字弄糊塗了,
VALUES
因為它包含 url、文件類型、嚴重性。請提供
SHOW CREATE TABLE
每張桌子。在數據倉庫的情況下,匯總表通常是性能的答案。你能總結每天的計數,然後總結這些小計嗎?
索引(id_analysis,重量)
大部分查詢時間都花在了這兩列上的索引掃描/查找上。將它們放在一起將阻止規劃
weight
器單獨進行索引掃描並讀取 7,822,871 行,values
以與從掃描中讀取的 134,236 行進行比較id_analysis
。
Filter: ((VALUES.is_hidden_by_user = 0) and (VALUES.is_hidden = 0)
由於正在為聚合創建臨時表,因此該索引應該允許以幾乎沒有成本的方式進行流水線化。