Mysql

如何盡可能快地更新 MySQL 單表中超過 1000 萬行？

  • January 23, 2019

大多數表使用 MySQL 5.6 和 InnoDB 儲存引擎。InnoDB 緩衝池大小為 15 GB，InnoDB DB + 索引約為 10 GB。伺服器有 32GB RAM，執行 Cent OS 7 x64。

我有一張大表，其中包含大約 1000 萬條記錄。

我每 24 小時從遠端伺服器獲取更新的轉儲文件。該文件為 csv 格式。我無法控制這種格式。該文件約為 750 MB。我嘗試將數據逐行插入到 MyISAM 表中，花了 35 分鐘。

我只需要從文件中的 10-12 中的每行取 3 個值並在數據庫中更新它。

實現這樣的目標的最佳方法是什麼？

我需要每天這樣做。

目前Flow是這樣的：

  1. mysqli_begin_transaction
  2. 逐行讀取轉儲文件
  3. 逐行更新每條記錄。
  4. mysqli_commit

上述操作大約需要30-40 分鐘才能完成，在執行此操作時，還有其他更新正在進行，這給了我

超過鎖定等待超時；嘗試重啟事務

更新 1

使用 .將數據載入到新表中LOAD DATA LOCAL INFILE。在 MyISAM38.93 sec中，在 InnoDB 中需要 7 分 5.21 秒。然後我做了：

UPDATE table1 t1, table2 t2
SET 
t1.field1 = t2.field1,
t1.field2 = t2.field2,
t1.field3 = t2.field3
WHERE t1.field10 = t2.field10

Query OK, 434914 rows affected (22 hours 14 min 47.55 sec)

更新 2

與連接查詢相同的更新

UPDATE table1 a JOIN table2 b 
ON a.field1 = b.field1 
SET 
a.field2 = b.field2,
a.field3 = b.field3,
a.field4 = b.field4

(14 hours 56 min 46.85 sec)

評論中問題的澄清：

  • 表中大約 6% 的行將由文件更新，但有時可能高達 25%。
  • 正在更新的欄位上有索引。表上有12個索引，8個索引包含更新欄位。
  • 不必在一個事務中進行更新*。*這可能需要一些時間，但不超過 24 小時。我希望在不鎖定整個表的情況下在 1 小時內完成它，因為稍後我必須更新依賴於該表的 sphinx 索引。只要數據庫可用於其他任務，這些步驟是否需要更長的持續時間都沒有關係。
  • 我可以在預處理步驟中修改 csv 格式。唯一重要的是快速更新並且沒有鎖定。
  • 表 2 是 MyISAM。它是使用 load data infile 從 csv 文件中新創建的表。MYI 文件大小為 452 MB。表 2 在 field1 列上被索引。
  • MyISAM 表的 MYD 為 663MB。

更新 3：

這是有關這兩個表的更多詳細資訊。

CREATE TABLE `content` (
 `hash` char(40) CHARACTER SET ascii NOT NULL DEFAULT '',
 `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 `og_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 `keywords` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 `files_count` smallint(5) unsigned NOT NULL DEFAULT '0',
 `more_files` smallint(5) unsigned NOT NULL DEFAULT '0',
 `files` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
 `category` smallint(3) unsigned NOT NULL DEFAULT '600',
 `size` bigint(19) unsigned NOT NULL DEFAULT '0',
 `downloaders` int(11) NOT NULL DEFAULT '0',
 `completed` int(11) NOT NULL DEFAULT '0',
 `uploaders` int(11) NOT NULL DEFAULT '0',
 `creation_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `upload_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `last_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `vote_up` int(11) unsigned NOT NULL DEFAULT '0',
 `vote_down` int(11) unsigned NOT NULL DEFAULT '0',
 `comments_count` int(11) NOT NULL DEFAULT '0',
 `imdb` int(8) unsigned NOT NULL DEFAULT '0',
 `video_sample` tinyint(1) NOT NULL DEFAULT '0',
 `video_quality` tinyint(2) NOT NULL DEFAULT '0',
 `audio_lang` varchar(127) CHARACTER SET ascii NOT NULL DEFAULT '',
 `subtitle_lang` varchar(127) CHARACTER SET ascii NOT NULL DEFAULT '',
 `verified` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `uploader` int(11) unsigned NOT NULL DEFAULT '0',
 `anonymous` tinyint(1) NOT NULL DEFAULT '0',
 `enabled` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `tfile_size` int(11) unsigned NOT NULL DEFAULT '0',
 `scrape_source` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `record_num` int(11) unsigned NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`record_num`),
 UNIQUE KEY `hash` (`hash`),
 KEY `uploaders` (`uploaders`),
 KEY `tfile_size` (`tfile_size`),
 KEY `enabled_category_upload_date_verified_` (`enabled`,`category`,`upload_date`,`verified`),
 KEY `enabled_upload_date_verified_` (`enabled`,`upload_date`,`verified`),
 KEY `enabled_category_verified_` (`enabled`,`category`,`verified`),
 KEY `enabled_verified_` (`enabled`,`verified`),
 KEY `enabled_uploader_` (`enabled`,`uploader`),
 KEY `anonymous_uploader_` (`anonymous`,`uploader`),
 KEY `enabled_uploaders_upload_date_` (`enabled`,`uploaders`,`upload_date`),
 KEY `enabled_verified_category` (`enabled`,`verified`,`category`),
 KEY `verified_enabled_category` (`verified`,`enabled`,`category`)
) ENGINE=InnoDB AUTO_INCREMENT=7551163 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED


CREATE TABLE `content_csv_dump_temp` (
 `hash` char(40) CHARACTER SET ascii NOT NULL DEFAULT '',
 `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `category_id` int(11) unsigned NOT NULL DEFAULT '0',
 `uploaders` int(11) unsigned NOT NULL DEFAULT '0',
 `downloaders` int(11) unsigned NOT NULL DEFAULT '0',
 `verified` tinyint(1) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`hash`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

這是更新查詢，它content使用來自的數據更新表content_csv_dump_temp

UPDATE content a JOIN content_csv_dump_temp b 
ON a.hash = b.hash 
SET 
a.uploaders = b.uploaders,
a.downloaders = b.downloaders,
a.verified = b.verified

更新4：

以上測試都是在測試機上完成的，但現在我在生產機上做了同樣的測試，查詢速度非常快。

mysql> UPDATE content_test a JOIN content_csv_dump_temp b
   -> ON a.hash = b.hash
   -> SET
   -> a.uploaders = b.uploaders,
   -> a.downloaders = b.downloaders,
   -> a.verified = b.verified;
Query OK, 2673528 rows affected (7 min 50.42 sec)
Rows matched: 7044818  Changed: 2673528  Warnings: 0

我為我的錯誤道歉。最好使用 join 而不是每個記錄更新。現在我正在嘗試使用 rick_james 建議的索引來改進 mpre，一旦完成基準測試，就會更新。

根據我的經驗，我會使用LOAD DATA INFILE來導入您的 CSV 文件。

LOAD DATA INFILE 語句以非常高的速度將文本文件中的行讀取到表中。

我在 Internet Load Data example上找到的範例。我在我的盒子上測試了這個例子並且工作得很好

範例表

CREATE TABLE example (
 `Id` int(11) NOT NULL AUTO_INCREMENT,
 `Column2` varchar(14) NOT NULL,
 `Column3` varchar(14) NOT NULL,
 `Column4` varchar(14) NOT NULL,
 `Column5` DATE NOT NULL,
 PRIMARY KEY (`Id`)
) ENGINE=InnoDB

範例 CSV 文件

# more /tmp/example.csv
Column1,Column2,Column3,Column4,Column5
1,A,Foo,sdsdsd,4/13/2013
2,B,Bar,sdsa,4/12/2013
3,C,Foo,wewqe,3/12/2013
4,D,Bar,asdsad,2/1/2013
5,E,FOObar,wewqe,5/1/2013

從 MySQL 控制台執行的導入語句

LOAD DATA LOCAL INFILE '/tmp/example.csv'
   -> INTO TABLE example
   -> FIELDS TERMINATED BY ','
   -> LINES TERMINATED BY '\n'
   -> IGNORE 1 LINES
   -> (id, Column3,Column4, @Column5)
   -> set
   -> Column5 = str_to_date(@Column5, '%m/%d/%Y');

結果

MySQL [testcsv]> select * from example;
+----+---------+---------+---------+------------+
| Id | Column2 | Column3 | Column4 | Column5    |
+----+---------+---------+---------+------------+
|  1 |         | Column2 | Column3 | 0000-00-00 |
|  2 |         | B       | Bar     | 0000-00-00 |
|  3 |         | C       | Foo     | 0000-00-00 |
|  4 |         | D       | Bar     | 0000-00-00 |
|  5 |         | E       | FOObar  | 0000-00-00 |
+----+---------+---------+---------+------------+

IGNORE 只是簡單地忽略作為列標題的第一行。

在 IGNORE 之後，我們指定要導入的列（跳過 column2），它與您問題中的條件之一匹配。

這是直接來自 Oracle 的另一個範例：LOAD DATA INFILE 範例

這應該足以讓你開始。

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

相關問答

Mysql

有沒有辦法使用連接或任何其他方法來加速這個更新查詢

  • April 18, 2019
檢視問答
Mysql

Mysql：插入性能 INNODB 與 MISAM

  • February 6, 2022
檢視問答
Mysql

如何為使用過多 CPU 的 Mysql 5.1 配置 my.cnf？

  • January 6, 2022
檢視問答
Mysql

為什麼 InnoDB 上的簡單 SELECT 比 MyISAM 慢 100 倍？

  • December 31, 2021
檢視問答
Mysql

MyISAM 表的性能設置 - 將所有內容保存在記憶體中

  • December 21, 2021
檢視問答
Mysql

我可以從 MyISAM ALTER 中的進度報告推斷嗎？

  • December 10, 2021
檢視問答