mysql - 处理巨大的MyISAM表以进行优化

Question

我有一个巨大的（并且还在不断增长）MyISAM表（7亿行= 140Gb）。

CREATE TABLE `keypairs` (
  `ID` char(60) NOT NULL,
  `pair` char(60) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM

该表选项已更改为 ROW_FORMAT=FIXED，因为两列始终固定为最大长度（60）。是的， ID是一个可悲的字符串，而不是INT。

SELECT查询在速度效率上还可以。

数据库和mysql引擎都是 127.0.0.1/localhost。 （无远处）

可悲的是， INSERT慢得要命。我什至没有谈论尝试 LOAD DATA数百万新行...需要几天的时间。

不会有任何并发​​读取。所有的SELECT只能由我的本地服务器一个接一个地完成（不供客户使用）

（有关信息：文件大小.MYD = 88Gb，.MYI = 53Gb，.TMM = 400Mb）


我如何才能加快插入该表的速度？
那对PARTITION那张大桌子有帮助吗？ （那么如何？）
我听说MyISAM正在将“结构缓存”用作.frm文件。并且在配置文件中插入一行有助于mysql将所有.frm保留在内存中（如果已分区），是否也有帮助？实际上，我的.frm文件只有7亿行才9kb）
字符串缩短/压缩功能... ID字符串？ （与Rainbow表相同的想法）即使它降低了最大允许的唯一ID，我也将永远不会达到60个字符的最大值。所以也许是个主意？但是在创建新的唯一ID之前，我必须检查db ofc中是否不存在缩短的字符串
与缩短ID字符串的想法相同，如何在ID上使用md5（）？在这种情况下，缩短字符串是否意味着更快？

Answer 1

在执行LOAD之前，对输入的数据进行排序。这将提高PRIMARY KEY(id)的可缓存性。
除非PARTITIONing有一些有用的模式，否则ID不太可能有帮助。
PARTITIONing不能用于单行插入，也不能用于ID的单行读取。
如果字符串的宽度不是恒定的60，则您说CHAR而不是VARCHAR浪费了空间和速度。改变它。
MyISAM的FIXED仅在有很多“搅动”（删除+插入和/或更新）时才有用。
较小意味着可缓存性更高，意味着更少的I / O意味着更快。
.frm是CREATE TABLE的编码；与该讨论无关。
一个简单的compress / zip /无论如何，几乎都会压缩超过10个字符的文本字符串。而且它们可以无压缩地无损压缩。你的弦长什么样？ 60个字符的英文文本将缩小为20-25个字节。
MD5是“摘要”，而不是“压缩”。您无法从其MD5中恢复字符串。无论如何，转换为BINARY(16)后需要16个字节。
PRIMARY KEY是一个BTree。如果ID有点“随机”，则很可能不会缓存“下一个” ID（除非对输入进行了排序）。不，BTree不会一直保持平衡。
将PRIMARY KEY转换为辅助键（添加AUTO_INCREMENT后）将不会加快速度-它仍然必须使用其中的ID更新BTree！
你有多少RAM？根据您的情况以及此LOAD，将MyISAM的key_buffer_size设置为可用RAM的大约70％，但不要大于.MYI文件。我建议使用较大的key_buffer，因为这是发生随机访问的地方。 .MYD仅附加到（假设您从未删除任何行）。
我们确实需要查看您的SELECTs，以确保这些更改不会破坏其他地方的性能。
确保您使用的是CHARACTER SET latin1或ascii。 utf8使用CHAR会浪费更多的空间。


切换到InnoDB将使表的磁盘空间（数据+索引）增加一倍，甚至三倍。因此，它可能会显示出来。但是，缓解因素是PK与数据“聚集在一起”，因此您不必为插入的每一行更新两件事。请注意，key_buffer_size应该降低到10M，innodb_buffer_pool_size应该设置为可用RAM的70％。

（我的项目符号项目适用于InnoDB，除非指定了MyISAM。）

在使用InnoDB时，最好在每个事务中插入1000行。少于这将导致更多的交易开销；不仅如此，还会导致撤消日志超负荷运行，从而导致变慢的方式有所不同。

十六进制ID

由于ID始终为60个十六进制数字，因此将其声明为BINARY(30)并通过UNHEX(...)打包并通过HEX(ID)提取。通过WHERE ID = UNHEX(...)测试。这将使数据收缩约25％，而MyISAM的PK收缩约40％。 （对于InnoDB，总体为25％。）

要仅转换为BINARY(30)：

CREATE TABLE new (
    ID BINARY(30) NOT NULL,
    `pair` char(60) NOT NULL
    -- adding the PK later is faster for MyISAM
) ENGINE=MyISAM;
INSERT INTO new
    SELECT UNHEX(ID),
           pair
        FROM keypairs;
ALTER TABLE keypairs ADD
    PRIMARY KEY (`ID`);   -- For InnoDB, I would do differently
RENAME TABLE keypairs TO old,
             new TO keypairs;
DROP TABLE old;

微小的RAM

仅具有2GB的RAM，仅MyISAM的数据集应使用 key_buffer_size=300M和 innodb_buffer_pool_size=0之类的东西。对于仅InnoDB： key_buffer_size=10M和 innodb_buffer_pool_size=500M。由于 ID可能是某种摘要，因此它将是非常随机的。小缓存和随机密钥的结合意味着几乎每个插入都将涉及磁盘I / O。我的第一个估计将是大约30个小时来插入1000万行。您拥有哪种驱动器？如果您还没有SSD，SSD将会大为不同。

加快 INSERTs速度的另一件事是在启动 ID之前按 LOAD进行排序。但这对于 UNHEX来说很棘手。这是我的建议。


创建具有 tmp和 ID BINARY(30)但没有索引的MyISAM表 pair。 （不必担心 key_buffer_size；它不会被使用。）
LOAD将数据放入 tmp。
ALTER TABLE tmp ORDER BY ID;这将对表格进行排序。仍然没有索引。我认为，如果没有证据，这将是一个文件排序，在这种情况下，“通过密钥缓冲区修复”要快得多。
INSERT INTO keypairs SELECT * FROM tmp;通过以 keypairs顺序将行馈送到 ID，这将最大化缓存。


再次，我仔细说明了所有事情，以便无论哪个引擎 keypairs都可以正常工作。我预计步骤3或4将花费最长的时间，但我不知道哪一个。