如何尽可能快地更新MySQL单个表中的1000万行以上？

对大多数表使用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就是这样的：

mysqli_begin_transaction
逐行读取转储文件
逐行更新每个记录。
mysqli_commit

上述操作大约需要30-40分钟完成，同时执行其他更新，这使我

超过了锁定等待超时；尝试重新启动事务

更新1

使用LOAD DATA LOCAL INFILE将数据加载到新表中。在MyISAM中，花了38.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小时内完成它而不锁定整个表，因为稍后我必须更新依赖于此表的狮身人面像索引。只要数据库可用于其他任务，步骤是否花费较长的时间都没关系。
我可以在预处理步骤中修改csv格式。唯一重要的是快速更新且没有锁定。
表2是MyISAM。它是使用加载数据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

我为我的错误表示歉意。最好使用连接而不是每个记录更新。现在，我正在尝试使用rick_james建议的索引来改进mpre，一旦完成基准测试，它将更新。

您是否有复合INDEX（field2，field3，field4）（以任何顺序）？请向我们显示SHOW CREATE TABLE。

12和8索引是您问题的重要组成部分。 MyISAM是另一个重要部分。 InnoDB或TokuDB在多个索引下的性能要好得多。

您有两个不同的更新。请准确告诉我们从csv数据更新表的简单语句是什么样的。这样我们也许可以帮助您设计出满足您要求的技术。

@RickJames只有一个更新，请检查更新的问题。，谢谢

#1 楼

根据我的经验，我将使用LOAD DATA INFILE导入您的CSV文件。

LOAD DATA INFILE语句以很高的速度将文本文件中的行读入
表中。

示例我在Internet上找到了“加载数据”示例。我在盒子上测试了此示例，并正常运行

示例表

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示例

这应该足以让您入门。

我可以使用加载数据将数据加载到临时表中，然后使用其他查询在主表中对其进行更新。

–user16108
2015年10月30日13:11

#2 楼

鉴于提到的所有内容，瓶颈似乎是联接本身。

方面＃1：联接缓冲区大小

很可能，您的join_buffer_size也可能也是低。

根据有关MySQL如何使用联接缓冲区高速缓存的MySQL文档

，我们仅将使用的列存储在联接缓冲区中，而不存储整个行在这种情况下，请将连接缓冲区的键保留在RAM中。

您有1000万行乘以每个键4个字节。大约是40M。

在会话中尝试将其增加到42M（比40M大一点）

SET join_buffer_size = 1024 * 1024 * 42;
UPDATE table1 a JOIN table2 b 
ON a.field1 = b.field1 
SET 
a.field2 = b.field2,
a.field3 = b.field3,
a.field4 = b.field4;

，请继续将其添加到my.cnf

[mysqld]
join_buffer_size = 42M

重新启动mysqld不需要新的连接。只需运行

mysql> SET GLOBAL join_buffer_size = 1024 * 1024 * 42;

ASPECT＃2：联接操作

您可以通过优化优化程序来操纵联接操作的样式

根据MySQL文档中有关块嵌套循环和批处理键访问联接的说明，

使用BKA时，join_buffer_size的值定义每个请求中的批处理键的大小到存储引擎。缓冲区越大，连接操作右侧表的访问顺序就越多，这可以显着提高性能。

若要使用BKA，optimizer_switch系统变量的batched_key_access标志必须设置为开。 BKA使用MRR，因此mrr标志也必须打开。当前，MRR的成本估算过于悲观。因此，也有必要关闭mrr_cost_based以便使用BKA。

同一页建议这样做：

mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

ASPECT＃3：将更新写入磁盘（可选）

最忘了增加innodb_write_io_threads以便将脏页更快地从缓冲池中写入。

[mysqld]
innodb_write_io_threads = 16

您必须重新启动MySQL才能进行此更改

尝试一下吧！

真好！可调连接缓冲区尖端的+1。如果您要加入，请加入内存。好提示！

– Peter Dixon-Moses
2015年12月29日20:26

#3 楼

CREATE TABLE与CSV匹配CSV

LOAD DATA到该表中
UPDATE real_table JOIN csv_table ON ... SET ..., ..., ...;
DROP TABLE csv_table;

步骤3将更快而不是逐行，但它仍将锁定表中的所有行一段很短的时间。如果此锁定时间比整个过程花费的时间更重要，那么...

如果没有其他东西在写表，那么...

CREATE TABLE与CSV匹配；除了在JOIN中的UPDATE中需要的索引之外，没有索引。如果是唯一的，则将其命名为PRIMARY KEY。

LOAD DATA放入该表中
将real_table复制到new_table（CREATE ... SELECT）
UPDATE new_table JOIN csv_table ON ... SET ..., ..., ...;
RENAME TABLE real_table TO old, new_table TO real_table;
DROP TABLE csv_table, old;

步骤3比更新快，尤其是在不必要的索引被遗忘的情况下。
步骤5是“即时”的。

以秒为例，在步骤3之后，我们正在执行步骤4，然后将新数据插入到real_table中，因此我们将在new_table中丢失该数据？解决方法是什么？谢谢

–user16108
2015年12月25日，下午3:14

看看什么pt-online-schema-digest;它通过TRIGGER处理此类问题。

–里克·詹姆斯（Rick James）
15/12/25在8:39

您可能不需要来自LOAD DATA的表上的任何索引。添加不必要的索引的成本很高（及时）。

–里克·詹姆斯（Rick James）
15年12月29日在6:40

根据最新信息，我倾向于将CSV文件仅通过AUTO_INCREMENT加载到MyISAM表中，然后根据PK一次将1K行分块。但是我需要先了解所有要求和表架构，然后再尝试阐明细节。

–里克·詹姆斯（Rick James）
2015年12月29日15:53

我已经将hash设置为PRIMARY索引，但是使用顺序查询在50k中分块会花费更多时间。如果创建自动增量会更好吗？并将其设置为主索引？

–user16108
2015年12月30日在3:11

#4 楼

您已经说过：

更新会影响您表的6-25％
您希望尽快执行此操作（<1小时）
而无需锁定
它并不一定要进行一次交易
（在评论Rick James的回答中），您对种族状况表示担忧。

这些陈述中有许多是矛盾的。例如，没有锁定表的大型更新。或通过一项巨额交易避免竞争条件。

此外，由于您的表已被大量索引，因此插入和更新都可能很慢。

避免比赛条件

如果您可以在表中添加更新的时间戳，则可以解决比赛条件，同时又避免了记录一半一笔交易中有-百万个更新。

这使您可以自由执行逐行更新（如您当前所做的那样），但是具有自动提交或更合理的事务批处理功能。

通过检查尚未发生的后续更新（UPDATE ... WHERE pk = [pk] AND updated < [batchfile date]），可以避免竞争条件（逐行更新时）

，重要的是，这使您运行并行更新。

以最快的速度运行-并行化

现在，使用此时间戳检查：

将批处理文件分成一些合理大小的块（例如50,000行/文件）
并行地，在每个文件中读取一个脚本，并输出包含50,000条UPDATE语句的文件。
同时（2）完成后，让mysql运行每个sql文件。

（例如，在bash中查看split和xargs -P，以轻松地以多种方式并行运行命令的方式。并行度取决于您愿意用于更新的线程数）

请记住，“逐行”的速度可能比至少100次批处理慢10倍。

–里克·詹姆斯（Rick James）
15年12月29日在6:37

为了确保这种情况，您必须对其进行基准测试。更新表的6-25％（更新的列涉及8个索引），我可能会感到索引维护成为瓶颈。

– Peter Dixon-Moses
15/12/29在11:37

我的意思是，在某些情况下，删除索引，批量更新并在之后重新创建它们可能会更快...但是OP不想停机。

– Peter Dixon-Moses
15年12月29日在11:58

#5 楼

为了使UPDATE快速运行，您需要

INDEX(uploaders, downloaders, verified)

它可以在任何一个表上。这三个字段可以是任意顺序。

这将有助于UPDATE能够快速匹配两个表之间的行。

并使数据类型相同这两个表（均为INT SIGNED或均为INT UNSIGNED）。

这实际上减慢了更新速度。

–user16108
16年1月1日，下午3:34

嗯...请提供解释更新...;。

–里克·詹姆斯（Rick James）
16年1月1日，下午4:59

#6 楼

大型更新受I / O约束。我建议：

创建一个不同的表，该表将存储您3个经常更新的字段。我们将其中一个表称为资产静态表，保存静态数据，将另一个资产动态表存储上载器，下载器和已验证的数据。
如果可以的话，对资产动态表使用MEMORY引擎。（每次更新后备份到磁盘）。
根据您的更新4更新轻巧灵活的asset_dynamic（即LOAD INFILE ... INTO temp；在a.id = b.id SET上更新asset_dynamic JOIN temp b [ [需要更新的内容]。这应该花费不到一分钟的时间（在我们的系统上，assets_dynamic拥有95M的行，而更新影响〜6M的行，仅需40多秒）。
运行Sphinx的索引器时，JOIN assets_static和assets_dynamic（假设您要将这些字段之一用作属性）。

编程黑洞网