优化一个巨大的mysql表

Question

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

这是我桌子的样子

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秒，否则。

是否有更多的优化这个表来为大量的数据做准备。所以即使是一亿行也不成问题。

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设置为可用内存的70%左右。那么，让它担心的是什么是在记忆中，什么不是。一旦表太大而无法缓存，您就应该收缩数据(正如我前面提到的)，并提供'good‘索引(如其他注释中提到的)，并可能为“引用的局部性”构造表(不知道所有的查询，我就无法解决这个问题)。

你抱怨用ID代替字符串..。让我们仔细看一看。有多少不同的用户名？频道？数据是如何进入的--你是一次得到一排还是一批？是做直接INSERTs的事情还是给做INSERTs的代码喂食呢？会有一个STORED PROCEDURE来做规范化和插入吗？如果您需要每秒插入数百行，那么我可以讨论如何做到这两种方法，并有效地完成它们。

你没有问PARTITIONs的事。对于简单的username查询，我不推荐使用它。

2.5M行约为85个百分位数。100米行更令人兴奋-第98百分位数。