如何尽快更新million+单表中的10行MySQL行？

Question

对大多数表使用MySQL 5.6和InnoDB存储引擎。InnoDB缓冲池大小为15 GB，Innodb DB +索引约为10 GB。服务器拥有32 is内存，并且正在运行分时OS 7 x64。

我有一张大桌子，里面有大约一千万张唱片。

我每24小时从远程服务器获得一个更新的转储文件。该文件采用csv格式。我无法控制那种形式。文件大约为750 MB。我试着逐行向MyISAM表插入数据，花费了35分钟。

我只需要从文件中取出10-12中的每一行3个值并在数据库中更新它。

，实现这样的目标最好的方法是什么?

我每天都要这么做。

目前的流程如下：

1. mysqli_begin_transaction
2. 逐行读取转储文件
3. 逐行更新每条记录。
4. 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小时内完成它，因为以后我必须更新依赖于此表的sphinx索引。只要数据库可用于其他任务，这些步骤是否需要较长的持续时间并不重要。
• 我可以在预处理步骤中修改csv格式。唯一重要的是快速更新和不锁定。
• 表2是MyISAM。它是使用load data infile从csv文件中新建的表。MYI文件大小为452 MB。表2是在field1列上索引的。
• MyISAM表的内存是663 is。

更新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

我为我的错误道歉。最好是使用联接，而不是每次更新记录。现在，我正在努力改进mpre使用rick_james建议的索引，将在平台标记完成后进行更新。

Answer 1

根据我的经验，我将使用负载数据信息导入您的CSV文件。

LOAD DATA INFILE语句以非常快的速度将文本文件中的行读入表中。

我在互联网负载数据示例上找到了一个例子。我在我的盒子上测试了这个例子并且工作得很好。

示例表

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 console

运行

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 |
+----+---------+---------+---------+------------+

忽略只是忽略了第一行，即列标题。

忽略之后，我们将指定要导入的列(跳过column2)，这与问题中的条件之一相匹配。

下面是直接来自甲骨文的另一个例子：加载数据示例

这应该足够让你开始工作了。

Answer 2

从所有提到的事情来看，瓶颈似乎是连接本身。

方面#1 :连接缓冲区大小

很可能，您的加入_缓冲器_大小可能太低了。

根据MySQL关于MySQL如何使用连接缓冲区缓存的文档

我们只将使用过的列存储在联接缓冲区中，而不是整个行。

在这种情况下，使连接缓冲区的键留在RAM中。

每个键有1000万行乘以4个字节。大约4000万。

试着把它提高到4200万(略大于40米)。

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;

方面#2 :加入

操作

您可以通过推特优化器来操作联接操作的样式。

根据MySQL在块嵌套-循环和批处理密钥访问连接上的文档

当使用BKA时，join_buffer_size的值定义了对存储引擎的每个请求中批密钥的大小。缓冲区越大，对join操作的右表的顺序访问就越多，这可以显著提高性能。要使用BKA，必须将batched_key_access系统变量的optimizer_switch标志设置为on。BKA使用MRR，因此mrr标志也必须打开。目前，MRR的成本估算过于悲观。因此，mrr_cost_based也必须关闭才能使用BKA。

同一页建议这样做：

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

方面#3 :将更新写入磁盘(可选)

大多数人忘记增加诺姆b_写_io_线程以更快地从缓冲池中写入脏页。

[mysqld]
innodb_write_io_threads = 16

为此，您必须重新启动MySQL。

试试看！

Answer 3

1. 与CSV匹配的CREATE TABLE
2. 把LOAD DATA放到那张桌子上
3. UPDATE real_table JOIN csv_table ON ... SET ..., ..., ...;
4. DROP TABLE csv_table;

步骤3将比逐行快得多，但它仍然会在相当长的时间内锁定表中的所有行。如果这个锁定时间比整个过程需要多长时间更重要，.

如果没有其他东西写在桌子上那么..。

1. 与CSV匹配的CREATE TABLE；除了UPDATE中的JOIN中需要什么之外，没有索引。如果是唯一的，就让它成为PRIMARY KEY。
2. 把LOAD DATA放到那张桌子上
3. 将real_table复制到new_table (CREATE ... SELECT)
4. UPDATE new_table JOIN csv_table ON ... SET ..., ..., ...;
5. RENAME TABLE real_table TO old, new_table TO real_table;
6. DROP TABLE csv_table, old;

步骤3比更新更快，特别是当不必要的索引被关闭时。

第五步是“瞬间”。