如何加快 2 個 mysql 表之間的更新連接查詢？
好吧長話短說，我有強大的專用伺服器。
Intel I7-6700K - 64GB DDR4 2400 MHz 1x480GB SSD與 nginx、php 一起執行 mysql 伺服器
innodb-ft-min-token-size = 1 innodb-ft-enable-stopword = 0 innodb_buffer_pool_size = 40G max_connections = 2000 [deploy@ns540545 ~]$ free -h total used free shared buff/cache available Mem: 62G 45G 11G 107M 6.4G 16G Swap: 2.0G 1.4G 640M它很貴，所以我買了另一台專用伺服器來削減成本讓我們稱之為 不那麼強大的專用伺服器
Intel i3-2130 8GB DDR3 1333 MHz 2TB與 nginx、php 一起執行 mysql 伺服器
innodb-ft-min-token-size = 1 innodb-ft-enable-stopword = 0 innodb_buffer_pool_size = 4G max_connections = 2000 [root@privateserver deploy]# free -h total used free shared buff/cache available Mem: 7.7G 7.5G 73M 24M 150M 79M Swap: 39G 7.8G 32G我將數據庫從強大的伺服器移到了不太強大的伺服器。
在執行簡單查詢時我會感到性能略有下降，這很好，但這個查詢過去在強大的伺服器上需要 2 分鐘，現在它需要大約26.6525 小時，並且指望不那麼強大的伺服器。
UPDATE content a JOIN peers_data b ON a.hash = b.hash SET a.seeders = b.seeders, a.leechers = b.leechers, a.is_updated = b.is_updated有關兩個專用伺服器上完全相同的表的更多資訊
CREATE TABLE `peers_data` ( `id` int(11) NOT NULL AUTO_INCREMENT, `hash` char(40) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `seeders` int(11) NOT NULL DEFAULT '0', `leechers` int(11) NOT NULL DEFAULT '0', `is_updated` int(1) NOT NULL DEFAULT '1', PRIMARY KEY (`hash`), UNIQUE KEY `id` (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE `content` ( `id` int(11) NOT NULL AUTO_INCREMENT, `content_id` int(11) unsigned NOT NULL DEFAULT '0', `hash` char(40) CHARACTER SET ascii NOT NULL DEFAULT '', `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `tags` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `category` smallint(3) unsigned NOT NULL DEFAULT '0', `category_name` varchar(50) CHARACTER SET ascii COLLATE ascii_bin DEFAULT '', `sub_category` smallint(3) unsigned NOT NULL DEFAULT '0', `sub_category_name` varchar(50) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, `size` bigint(20) unsigned NOT NULL DEFAULT '0', `seeders` int(11) unsigned NOT NULL DEFAULT '0', `leechers` int(11) unsigned NOT NULL DEFAULT '0', `upload_date` datetime DEFAULT NULL, `uploader` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0', `uploader_level` varchar(10) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '', `comments_count` int(11) unsigned NOT NULL DEFAULT '0', `is_updated` tinyint(1) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`content_id`), UNIQUE KEY `unique` (`id`) USING BTREE, KEY `hash` (`hash`), KEY `uploader` (`uploader`), KEY `sub_category` (`sub_category`), KEY `category` (`category`), KEY `title_index` (`title`), KEY `category_sub_category` (`category`,`sub_category`), KEY `seeders` (`seeders`), KEY `uploader_sub_category` (`uploader`,`sub_category`), KEY `upload_date` (`upload_date`), KEY `uploader_upload_date` (`uploader`,`upload_date`), KEY `leechers` (`leechers`), KEY `size` (`size`), KEY `uploader_seeders` (`uploader`,`seeders`), KEY `uploader_size` (`uploader`,`size`), FULLTEXT KEY `title` (`title`), FULLTEXT KEY `tags` (`tags`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; mysql> explain UPDATE content a JOIN peers_data b ON a.hash = b.hash SET a.seeders = b.seeders, a.leechers = b.leechers, a.is_updated = b.is_updated ; +----+-------------+-------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+ | 1 | UPDATE | a | NULL | ALL | NULL | NULL | NULL | NULL | 4236260 | 100.00 | NULL | | 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 160 | func | 1 | 100.00 | Using where | +----+-------------+-------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+ 2 rows in set (0.00 sec)peers_data 中的
記錄 6,367,417 內容中的記錄 4,236,268
如何加快上述更新連接查詢？我期待在不太強大的伺服器上執行大約 1 小時，但 26 小時+ 太多了。
我做錯了什麼？還是在這裡失踪？
我試圖通過設置 32 GB + 交換空間來補償不太強大的伺服器上的 RAM。innodb 緩衝池 4 GB 太多了嗎？
到目前為止，我已經嘗試過了。
- 修復兩個字元集類型相同
- 將 innodb 緩衝池大小設置為預設 128M
現在任務在19.93 小時內完成。
根據RolandoMySQLDBA 的建議，
我正在嘗試SUGGESTION #2，因為這些索引是站點正常執行所必需的。
我已經設定
innodb_buffer_pool_size = 5G並嘗試
設置全域 innodb_change_buffer_max_size = 50; 更新內容 a JOIN peers_data b ON a.hash = b.hash SET a.seeders = b.seeders, a.leechers = b.leechers, a.is_updated = b.is_updated; 設置全域 innodb_change_buffer_max_size = 25;
已經25 多個小時了，查詢仍在執行。將在查詢完成時更新。
另一件事是數據不是非常重要，它的數據隨時可用，我每天都進行備份。所以稍後我也會嘗試建議 3。並將使用更快的那個。
更新 2：
建議 2 花了 38.36667 小時完成。
現在嘗試建議 3。
您的查詢
您的查詢應該需要很長時間。為什麼 ？？？查看您的查詢：
UPDATE content a JOIN peers_data b ON a.hash = b.hash SET a.seeders = b.seeders, a.leechers = b.leechers, a.is_updated = b.is_updated;請注意，列開始由
content表中的查詢更新：
seedersleechersis_updated這些列中的哪些列被索引？
seedersleechers
content您在涉及這些列的表中有哪些索引？KEY `seeders` (`seeders`), <<<<-------------------------------- THIS ONE !!! KEY `uploader_sub_category` (`uploader`,`sub_category`), KEY `upload_date` (`upload_date`), KEY `uploader_upload_date` (`uploader`,`upload_date`), KEY `leechers` (`leechers`), <<<<------------------------------ THIS ONE !!! KEY `size` (`size`), KEY `uploader_seeders` (`uploader`,`seeders`), <<<<------------ THIS ONE !!!引擎蓋下發生了什麼？？？
如果您要更改 和 的值
seeders，則leeches這三 (3) 個索引的葉節點將被重新洗牌。即使大多數值沒有被更改，行也會被鎖定，並且數據的副本會被儲存在撤消日誌中（為了 MVCC）。這個結果是額外的磁碟 I/O（ibdata1 應該會增長）
INNODB
InnoDB 緩衝池將經歷“完美風暴”。為什麼 ？？？
請注意 InnoDB 架構（圖片來自 Percona CTO Vadim Tkachenko）
請注意
Insert Buffer. 這是 MySQL 文件中關於它的內容：更改緩衝區是一種特殊的資料結構，當受影響的頁面不在緩衝池中時，它會記憶體對二級索引頁面的更改。可能由 INSERT、UPDATE 或 DELETE 操作 (DML) 導致的緩衝更改稍後會在其他讀取操作將頁面載入到緩衝池中時合併。
與聚集索引不同，二級索引通常是非唯一的，並且插入二級索引的順序相對隨機。類似地，刪除和更新可能會影響索引樹中不相鄰的二級索引頁面。稍後合併記憶體的更改，當受影響的頁面被其他操作讀入緩衝池時，避免了從磁碟讀入二級索引頁面所需的大量隨機訪問 I/O。
在系統大部分空閒或緩慢關閉期間執行的清除操作會定期將更新的索引頁寫入磁碟。與將每個值立即寫入磁碟相比，清除操作可以更有效地為一系列索引值寫入磁碟塊。
當有許多二級索引要更新並且許多受影響的行時，更改緩衝區合併可能需要幾個小時。在此期間，磁碟 I/O 會增加，這可能會導致磁碟綁定查詢顯著變慢。送出事務後，更改緩衝區合併也可能繼續發生。事實上，在伺服器關閉和重新啟動後，更改緩衝區合併可能會繼續發生（有關更多資訊，請參閱第 14.21.2 節，“強制 InnoDB 恢復”）。
在記憶體中，更改緩衝區佔據了 InnoDB 緩衝池的一部分。在磁碟上，更改緩衝區是系統表空間的一部分，因此索引更改在數據庫重新啟動時保持緩衝。
這就是“完美風暴”的用武之地：預設情況下，InnoDB 儲存引擎保留高達 25% 的 Buffer Pool 用於更改緩衝。對三 (3) 個二級索引的所有更改都必須堆積在 InnoDB 緩衝池的插入緩衝區中。當實際的索引頁面進入緩衝池時，合併過程隨後將被推送到磁碟（注意 ibdata1 中的插入緩衝區），從而產生更多的磁碟 I/O。
建議
建議 #1
擺脫那些索引。為什麼 ？？？這將消除在大規模更新期間管理這些索引所需的更改緩衝。
執行以下查詢：
SELECT COUNT(1) rowcount, COUNT(DISTINCT seeders) seeders_count, COUNT(DISTINCT leechers) leechers_count FROM content;如果
seeders_count小於 的 5%rowcount，則 的基數seeders_count會失去任何用途的資格seeders。Smae 與leechers.對於該索引
uploader_seeders，執行以下查詢：SELECT COUNT(1) uploader_seeders_count FROM (SELECT DISTINCT uploader,seeders FROM content) A;如果
uploader_seeders_count小於rowcount（來自上一個查詢）的 5%，則去掉uploader_seeders索引。要擺脫這些索引，請執行以下命令：
ALTER TABLE content DROP INDEX uploader_seeders,DROP INDEX seeders,DROP INDEX leechers;建議 #2
將插入緩衝區大小更改為最大值，執行查詢：
SET GLOBAL innodb_change_buffer_max_size = 50; UPDATE content a JOIN peers_data b ON a.hash = b.hash SET a.seeders = b.seeders, a.leechers = b.leechers, a.is_updated = b.is_updated; SET GLOBAL innodb_change_buffer_max_size = 25;此外，將緩衝池大小 ( innodb_buffer_pool_size ) 設置為
20G. 在 中更改該值my.cnf。如果你有 MySQL 5.7，只需執行
mysql> SET GLOBAL innodb_buffer_pool_size = 1024 * 1024 * 1024 * 20;如果您有 MySQL 5.6 及之前的版本，則必須重新啟動 mysqld。
建議#3（風險）
如果您不想刪除索引，您可以在批量 UPDATE 期間禁用更改緩衝，然後再啟用它：
執行以下命令：
SET GLOBAL innodb_change_buffering = 'none'; UPDATE content a JOIN peers_data b ON a.hash = b.hash SET a.seeders = b.seeders, a.leechers = b.leechers, a.is_updated = b.is_updated; FLUSH TABLES; SET GLOBAL innodb_change_buffering = 'all';這是有風險的，因為這加快了對索引的更改，有利於在崩潰或重新啟動時沒有緩衝來恢復。
建議 #4（風險）
另一種大膽的方法是禁用 Double Write Buffer。由於需要重新啟動，請執行以下操作：
service mysql restart --skip-innodb_doublewrite- 做你的質量
UPDATEservice mysql restart不建議將其用於生產。請僅開發和登台！！！
hash char(40) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''雜湊是十六進制的，對嗎？不要讓它成為 utf8mb4；使用ASCII。
將雜湊打包到
BINARY(20)using 中UNHEX()。現在它只有 20 個字節。任何類型的散列都是非常“隨機的” 可能發生的事情——散列不能被記憶體，所以你在磁碟上打了很多，也許是大多數，行。這意味著磁碟 I/O 而不是簡單的 CPU。
當表變得太大（相對於 buffer_pool_size）時，即使你更快的機器最終也會大幅減速。
雜湊、UUID 等有這個缺點。盡量避免它們。
如果
seeders只是一個標誌，請使用TINYINT（1 個字節），而不是INT（4 個字節）。為什麼你有這兩個？
PRIMARY KEY (`hash`), UNIQUE KEY `id` (`id`)做其他事情來縮小表的磁碟佔用空間。更小 -> 更多可記憶體 -> 更少 I/O -> 更快。