在 64GB 16 核機器上為 250M 行表優化 MySQL / MariaDB 的最佳方法
我在一台非常強大的台式電腦上有一個非常大的數據集（一個表中有 250M 行，磁碟上有 170G），我用它來清理數據並對其進行分析。
作為此分析的一部分：
- 我需要在整個數據庫上執行更新以查找非標準格式的電話號碼等內容，並將它們轉換為標準化格式。
- 我需要能夠對諸如提供與另一條記錄和類似查詢共享相同電話號碼的所有記錄之類的事情進行分析。
我將主要查詢的所有欄位都已設置為索引，但唯一的唯一索引是主鍵。
我擁有的功能強大的台式機是具有 4 個 CPUS 的 Ryzen 處理器，每個 CPU 有 4 個核心，它有 64GB 的 RAM。數據庫還有一個專用的 2TB SSD 驅動器（作業系統在不同的驅動器上執行）。
我目前在執行 MariaDB 10.4.17 的 Windows 10 Pro 上使用預設的 Xampp 配置進行操作。
您建議對 MariaDB 或電腦設置進行哪些更改，以便在這種情況下獲得最佳性能，並希望使事情進展得更快。我有一些更新查詢需要超過 16 小時。我知道我可能會進一步優化這些查詢；但我也想像預設 Xampp 配置中有很多設置可以進行重大調整。
更新
我對這些回應感到有些驚訝。我知道我可以更好地進行查詢等；但是沒有一個數據庫設置我可以調整以使事情執行得更順暢或更適合我擁有的數據的大小和類型？
Rick James 是絕對正確的，從過程中獲得的收益比從調整中獲得的收益要大得多。使用多個執行緒在不同的表上執行查詢，索引表以避免查詢中的表掃描，並刪除不必要的索引以進行大量更新，避免在 where 子句中呼叫函式等。例如，您可以做的最基本的事情之一，是將許多更新捆綁到一個事務中。送出事務的成本很高，因此如果您可以在每個事務中找到並刪除 1000 行，而不是讓自動送出為每個刪除語句發出一個送出，那麼事情會進展得更快。
但是，通過適當地調整數據庫肯定會有一些好處，雖然有很多東西可以通過迭代過程來很好地調整最後 10-20% 的收益，但從中可以獲得顯著的收益一些非常基本的調整：
- 您不需要超過預設的 151 個同時連接，因此我們將假設 max_users 設置為預設的 151，這可能會佔用多達 2.5G RAM。為作業系統留出另外 8G，然後您可以安全地將至少 50G 分配給
innodb_buffer_pool_size.- 此外，如果您能承受在發生崩潰時損失 1 秒的交易，這很好，因為您沒有擷取新數據，只需修復已經存在的數據：您將看到通過設置
innodb_flush_log_at_trx_commit=2.- 如果二進制日誌已啟用並且您沒有使用它（log_bin 參數），則禁用它，或者，如果您需要二進制日誌，請設置 1binlog_row_image=MINIMAL` 以節省大量 IO。
- 設置
innodb_flush_neighbors=0;預設值為 1，因為 SSD 基本上有 0 次尋軌時間，所以在固態儲存上執行時，此預設值會浪費 IO。調整 600 多個可用配置參數中的許多參數很容易發瘋。抵制擺弄你不完全理解的參數的衝動。在配備 SSD 儲存的 64GB RAM 機器上，這四個應該可以為您提供相當不錯的基準性能提升。