MySQL 17.6m rows (1.2 gb) 全表更新太慢

Question

我有一个 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 -- CREATE TABLE tmp_inno SELECT * FROM tmp_hist(这里我尝试将表转换为 InnoDB)
• 317sec -- 更新 tmp_inno set cip=999(InnoDb 中的这个)

根据所有假设，我预计更新时间与修复时间相当，即 40 秒。但是需要10分钟!可以做些什么来加快速度？有问题的代码将一些数据从格式 A 转换为格式 B。它保证在单个线程中运行，而其他任何人都不会访问相同的数据，并且如果出现任何问题也不需要恢复，它可以重新开始。

PS:UPDATE语句为了测试进行了简化(所有时间都是为了这个简化的更新)。实际代码为每一行设置了不同的值，但我发现实际更新的执行时间与简化更新几乎相同，所以我将问题缩小到简化问题。

当前进度

根据答案中的知识，我应用了 ROW_FORMAT=FIXED(这似乎等同于将所有列类型更改为固定的)并将更新时间减少到 145 秒，几乎快了 4 倍。

尽管如此，它仍然比修复时间慢 2.5 倍左右，我认为这是可以达到的时间。

Answer 1

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

dba SE 上有一个有趣的答案，也涵盖了这个主题 https://dba.stackexchange.com/a/2643