在 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.

請詳細說明valuevs id_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)由於正在為聚合創建臨時表，因此該索引應該允許以幾乎沒有成本的方式進行流水線化。

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