mysql - 优化mysql巨表

Question

我有一个巨大的 mysql 表，它一直在增长。它正在记录聊天数据。

这就是我的 table 的样子

CREATE TABLE `log` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `channel` VARCHAR(26) NOT NULL,
    `timestamp` DATETIME NOT NULL,
    `username` VARCHAR(25) NOT NULL,
    `message` TEXT NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `username` (`username`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2582573
;

对用户名建立索引非常重要，因为否则查询用户名可能需要大约 5 秒的时间。

有没有办法进一步优化该表，以便为大量数据做好准备。因此，即使 100m 行也不会成为问题。

Answer 1

`id` BIGINT(20) NOT NULL AUTO_INCREMENT,

您的行数会超过 40 亿行吗？如果没有，请使用INT UNSIGNED，每行节省 4 个字节。为二级索引中的每行加上另外 4 个字节。

`channel` VARCHAR(26) NOT NULL,
`username` VARCHAR(25) NOT NULL,

标准化每个 - 也就是说，将其替换为 SMALLINT UNSIGNED 并在它们之间建立映射。节省:很多。

INDEX `username` (`username`)

这将成为 user_id，节省更多。

更小 --> 更可缓存 --> 更快。​​

您还有什么其他疑问？

“内存使用情况”——对于 InnoDB，将 innodb_buffer_pool_size 设置为可用 RAM 的大约 70%。然后，让它担心什么在内存中，什么不在内存中。一旦表太大而无法缓存，您应该缩小数据(正如我上面提到的)并提供“好的”索引(如其他评论中提到的)，并且可能为“引用位置”构建表(不知道所有查询，我无法解决这个问题)。

您对使用 ID 而不是字符串提示不已...让我们仔细看看这一点。有多少个不同的用户名？ channel ？数据是如何进来的——是一次获取一行，还是批量获取？是直接执行插入操作还是向某些执行插入操作的代码提供数据？是否可以有一个存储过程来进行规范化和插入？如果您需要每秒插入数百行，那么我可以讨论如何同时做到这两点，并高效地完成它们。

您没有询问PARTITIONs。我不推荐它用于简单的用户名查询。

250 万行大约是第 85 个百分位。 1 亿行更令人兴奋——第 98 个百分点。