首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >为什么BIGINT指数大于CHAR 13指数?

问为什么BIGINT指数大于CHAR 13指数?
EN

Database Administration用户
提问于 2018-01-22 08:42:46
回答 1查看 558关注 0票数 2

我有一个MySQL DB,它有一个包含500万条记录的MyISAM表,还有几个字段。其中一个字段是一个大INT,据我了解,它是8个字节,另一个是char 13,我认为它是每个字符的一个字节。

我注意到,当向每个字段添加索引时,索引文件在CHAR 13上增加了51 13,在BIGINT上增加了81 13。

根据每个字段的字节位置,我预计大INT会显着地缩小,但实际上两者之间的差异是相反的,而且比我预期的要大。

为什么会这样呢?

代码语言:javascript
复制
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
EN

回答 1

Database Administration用户

回答已采纳

发布于 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也有一些开销。

  • 如果按顺序插入BTree的行,则每个BTree块(最后一个除外)都将是“满的”。
  • 在另一个极端,由于在错误的时间插入行,由于块分割,行将是半满的。
  • 平均而言,在随机插入和/或删除后,BTree将稳定在69%左右。(在您的情况下,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放在所有行中是不现实的。
  • 如果要使用有效的随机、固定长度的latin1值进行测试:LEFT(MD5(id), 13)

还有一点需要注意: MyISAM可能有两种方法可以回复构建索引:

  • 通过key_buffer --这很可能会让指数达到69%的满分。
  • 通过分类--这可能会使它几乎100%满。

一个快速的实验:ALTER TABLE msisdns_key ENGINE=MyISAM;手表SHOW PROCESSLIST;,看看使用了哪种修复方法。看看其中一些索引是否缩水。

MyISAM要走了,别花太多时间在上面。

ALTER TABLE .. ADD INDEX ..以下列方式之一发生:

  • 复制表并重新生成所有索引,或
  • 添加新索引,不影响任何其他内容,直到完成时才返回,或
  • 在背景中添加新索引。这里的要点是,当您查看ls -l时,它可能还没有被更新。

修正几乎从来没有比DYNAMIC更好,即使是使用MyISAM。可能有帮助的地方:

  • 删除一个短记录,然后插入一个较长的记录。
  • 更新记录,增加某些列的大小(S)。

如果你有很多这样的尺寸,而“固定的”尺寸并不比“动态”尺寸大多少,那么也许固定的更好。

当平均行大小较小时,动态通常更好。这是因为更小->更可缓存->更快。

关于MyISAM的唯一情况是磁盘占用空间比InnoDB小。实际上,在任何其他比赛中,InnoDB都会遇到或超过MyISAM。

我10%的经验法则。当我试图优化一些东西(通常是速度或空间)时,我猜想一个提议的改变会有多大的帮助。如果不到10%,我就继续寻找“大鱼”。

它大吗?5米记录;150 5M数据+索引这个不算太大。随着MySQL表的发展,我会把它排在80%左右。它应该很容易地缓存在内存中,即使是在InnoDB中实现。如果是几个GB,那么让我们看看具体的模式,而不是“哦,顺便说一下,BIGINT是用于电话号码的”。

最小的手机。一个12位数字可以被塞进BINARY(5)中(没有长度信息)。但是,我建议代码中的混乱使得它不值得付出努力。(也许我的10%的规则生效了?)

票数 1
EN
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://dba.stackexchange.com/questions/195902

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档