我有一个MySQL DB,它有一个包含500万条记录的MyISAM表,还有几个字段。其中一个字段是一个大INT,据我了解,它是8个字节,另一个是char 13,我认为它是每个字符的一个字节。
我注意到,当向每个字段添加索引时,索引文件在CHAR 13上增加了51 13,在BIGINT上增加了81 13。
根据每个字段的字节位置,我预计大INT会显着地缩小,但实际上两者之间的差异是相反的,而且比我预期的要大。
为什么会这样呢?
CREATE TABLE `msisdns_key` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`key` mediumint(6) unsigned NOT NULL DEFAULT '0',
`key2` smallint(5) unsigned NOT NULL DEFAULT '0',
`msisdn_big` bigint(20) unsigned NOT NULL,
`msisdn_bit` binary(20) NOT NULL,
`msisdn` char(13) NOT NULL,
PRIMARY KEY (`id`),
KEY `key` (`key`),
KEY `key2` (`key2`),
KEY `msisdn_bit` (`msisdn_bit`(3)),
KEY `msisdn` (`msisdn`),
KEY `msisdn_big` (`msisdn_big`)
) ENGINE=MyISAM AUTO_INCREMENT=6745305 DEFAULT CHARSET=latin1发布于 2018-01-22 11:58:34
基准往往有缺陷。以下是一些影响测试结果的问题:
ALTER TABLE .. ADD INDEX ..?DELETEs或UPDATEs吗?这些都会扰乱BTrees。CHAR值中是否有尾随空格?(在某些情况下,CHAR会删除它们--我不知道这种情况。)CHAR值都一样吗?还是连续的值具有相同的前几个字符?(可能存在优化。)所有这些问题都与MyISAM索引的大小有关。( InnoDB的列表类似。)
回答这个问题..。
MyISAM索引以1KB块的形式构造为BTree。每一项“记录”都包含:
BIGINT为8字节,CHAR(13) latin1为13字节).MYD中行的字节指针(默认情况下)另外,BTree也有一些开销。
UPDATEs并不重要,因为记录似乎是FIXED。)现在是数学题。
BIGINT:500万行* (8+5)字节/行=65 5M。这可以很容易地增长到81 69与开销,69%等。
CHAR:5M * (13+5) =90 5M。所以这肯定是不正确的。
CHAR:5M * (2+5) =35 5M。这可能会膨胀到51 to。假设您插入了'x',MyISAM将其转换为VARCHAR(13)。
CHAR,向后工作,假设随机插入:51 69* 69% =35 69。除以5米行。这使得每个CHAR只剩下两个字节。如果所有的都是相同的,或者块中的所有都有相同的前缀,那么它就合适了。
CHAR,向后工作,假设有序插入:51 5MB/5MB=每行10字节。指针为5,CHAR为5。仍然有可能。
关于使用CHAR(13)进行基准测试的评论:
CHAR比VARCHAR更受欢迎的情况。utf8mb4,而不是latin1。x放在所有行中是不现实的。LEFT(MD5(id), 13)还有一点需要注意: MyISAM可能有两种方法可以回复构建索引:
一个快速的实验:ALTER TABLE msisdns_key ENGINE=MyISAM;手表SHOW PROCESSLIST;,看看使用了哪种修复方法。看看其中一些索引是否缩水。
MyISAM要走了,别花太多时间在上面。
ALTER TABLE .. ADD INDEX ..以下列方式之一发生:
ls -l时,它可能还没有被更新。修正几乎从来没有比DYNAMIC更好,即使是使用MyISAM。可能有帮助的地方:
如果你有很多这样的尺寸,而“固定的”尺寸并不比“动态”尺寸大多少,那么也许固定的更好。
当平均行大小较小时,动态通常更好。这是因为更小->更可缓存->更快。
关于MyISAM的唯一情况是磁盘占用空间比InnoDB小。实际上,在任何其他比赛中,InnoDB都会遇到或超过MyISAM。
我10%的经验法则。当我试图优化一些东西(通常是速度或空间)时,我猜想一个提议的改变会有多大的帮助。如果不到10%,我就继续寻找“大鱼”。
它大吗?5米记录;150 5M数据+索引这个不算太大。随着MySQL表的发展,我会把它排在80%左右。它应该很容易地缓存在内存中,即使是在InnoDB中实现。如果是几个GB,那么让我们看看具体的模式,而不是“哦,顺便说一下,BIGINT是用于电话号码的”。
最小的手机。一个12位数字可以被塞进BINARY(5)中(没有长度信息)。但是,我建议代码中的混乱使得它不值得付出努力。(也许我的10%的规则生效了?)
https://dba.stackexchange.com/questions/195902
复制相似问题