MySQL 17.6m行(1.2 GB)全表更新太慢 [英] MySQL 17.6m rows (1.2 gb) full-table update is too slow

问题描述

我有一张有1760万行的表

'CREATE TABLE `tmp_hist` (
`ti` int(11) DEFAULT NULL,
`cip6` varchar(15) DEFAULT NULL,
`date` varchar(20) DEFAULT NULL,
`fact` int(11) DEFAULT NULL,
`se` char(1) DEFAULT NULL,
`oper` int(11) DEFAULT NULL,
`qte` int(11) DEFAULT NULL,
`prix` double DEFAULT NULL,
`cip` int(11) DEFAULT NULL,
`fl` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1'

运行一个非常简单的更新大约需要10分钟

update tmp_hist set cip=100

有时候:

• 39sec-修复表tmp_hist(这一点特别有趣，因为在myisam中，修复表会将其复制到其他文件并替换原始文件，这表明磁盘速度)
• 531sec-更新tmp_hist设置cip = 100
• 400sec-创建表tmp_inno SELECT * FROM tmp_hist(在这里我尝试将表转换为InnoDB)
• 317sec-更新tmp_inno设置cip = 999(InnoDb中的该密码)

根据所有假设，我希望更新时间与修复时间非常可比，即40秒.但这需要10分钟！可以采取什么措施来加快速度?有问题的代码将某些数据从格式A转换为格式B.保证可以在单个线程中运行，而没有其他人访问相同的数据，并且如果出现任何错误，则无需进行恢复，只需重新启动即可.

PS:UPDATE语句已简化测试(并且所有时间均用于此简化更新).实际代码为每一行设置了不同的值，但是我发现实际更新的执行时间与简化更新的执行时间几乎相同，因此我将问题缩小为简化的时间.

当前进度

利用答案中的知识，我应用了ROW_FORMAT = FIXED(这似乎等同于将所有列类型都更改为Fixed)，并将更新时间缩短至145sec，这几乎快了4倍.

不过，它的修复时间比修复时间慢了约2.5倍，我认为这是可以达到的时间.

解决方案

由于表中有varchar，因此更新必须读取该行，查找正确的偏移量，然后更新cip字段.此外，由于行的大小是可变的，因此引擎无法轻松确定单个记录的偏移量.因此，您可以尝试将varchar字段更改为固定的char并进行测试，如果这样做有所不同.

有关此主题的一个有趣的答案也已在dba SE上结束 https://dba.stackexchange.com/a/2643

I have a table with 17.6m rows

'CREATE TABLE `tmp_hist` (
`ti` int(11) DEFAULT NULL,
`cip6` varchar(15) DEFAULT NULL,
`date` varchar(20) DEFAULT NULL,
`fact` int(11) DEFAULT NULL,
`se` char(1) DEFAULT NULL,
`oper` int(11) DEFAULT NULL,
`qte` int(11) DEFAULT NULL,
`prix` double DEFAULT NULL,
`cip` int(11) DEFAULT NULL,
`fl` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1'

And it takes around 10 minutes to run a very simple update

update tmp_hist set cip=100

There're the times:

• 39sec -- repair table tmp_hist (this one is particularly interesting because in myisam, repairing a table copies it to a different file and replaces original, this indicates disk speed)
• 531sec -- update tmp_hist set cip=100
• 400sec -- CREATE TABLE tmp_inno SELECT * FROM tmp_hist (here I try to convert the table to InnoDB)
• 317sec -- update tmp_inno set cip=999 (this one in InnoDb)

By all assumptions I expect the update time to be very comparable to repair time, that is, 40 seconds. But it takes 10 minutes! What can be done to speed it up? The code in question converts some data from format A to format B. It is guaranteed to run in a single thread without anyone else ever accessing same data and no recovery will be needed should anything go wrong, it can be just started over.

PS: The UPDATE statement is simplified for testing (and all times are for this simplified update). The real code sets different values for each row, but I found that the execution time for real update is almost the same as for simplified update, so i narrowed the problem to simplified one.

Current progress

Using the knowledge from the answer, I applied ROW_FORMAT=FIXED (which appears to be equivalent to changing all column types to fixed) and it reduced the update time to 145sec, which is almost 4 times faster.

Still, it's around 2.5x slower then repair time, which I deem to be the time that can be reached.

解决方案

Since you have varchars in your table, the update must read the row, look for the correct offset and then update the cip field. Furthermore, since the rows are variable sized, the engine cannot easily determine offsets of a single record. So, you might try changing your varchar fields to fixed char and test, if this makes a difference.

An interesting answer, covering this topic too, is over at dba SE https://dba.stackexchange.com/a/2643

这篇关于MySQL 17.6m行(1.2 GB)全表更新太慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！