主页 / dba / 问题 / 195902
Accepted
GemmaB89
Asked: 2018-01-23 00:42:46 +0800 CST

为什么 BIGINT 索引大于 CHAR 13 索引？

  • 772

我有一个 MySQL 数据库，其中有一个包含 500 万条记录和多个字段的 MyISAM 表。其中 1 个字段是 BIG INT，我理解为 8 个字节，另一个是 CHAR 13，我认为每个字符一个字节。

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

基于每个字段的字节位置，我预计 BIG 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
mysql myisam

1 个回答

  1. Best Answer

    基准往往是有缺陷的。以下是一些会影响测试结果的问题：

    • 你是如何测量尺寸的？

    • 列中的值是什么？特别是，它们的顺序是否与插入顺序相同？

    • 在填充表之前你有索引吗？还是你ALTER TABLE .. ADD INDEX ..后来做了？

    • 你有没有做过DELETEs或UPDATEs？这些往往会与 BTrees 混淆。

    • 您的CHAR值中是否有尾随空格？（在某些情况下CHAR会删除它们——我不知道这种情况。）

    • 所有的CHAR值都一样吗？或者连续的值有相同的前几个字符？（可能会有优化。）

    所有这些问题都与 MyISAM 索引的大小有关。（InnoDB 的列表类似。）

    要回答这个问题...

    MyISAM 索引的结构为 1KB 块中的 BTree。每个“记录”包含：

    • 数据（8 字节BIGINT或 13 字节CHAR(13) latin1）
    • 5（默认）字节指针指向行.MYD
    • 一些开销？

    此外，BTree 有一些开销。

    • 如果按顺序插入 BTree 的行，则每个 BTree 块（最后一个块除外）将是“完整的”。
    • 在另一个极端，由于在错误的时间插入行而导致块分裂，行将是半满的。
    • 平均而言，在随机插入和/或删除之后，BTree 稳定到大约 69% 满。（UPDATEs你的情况无关紧要，因为记录似乎是FIXED。）

    现在进行一些数学运算。

    BIGINT：5M 行 * (8+5) 字节/行 = 65MB。这很容易增长到 81MB，开销为 69%，等等。

    CHAR: 5M * (13+5) = 90MB。所以这一定是不正确的。

    CHAR: 5M * (2+5) = 35MB。这可能会增加到 51MB。假设您插入'x'并且 MyISAM 将其转换为VARCHAR(13).

    CHAR，向后计算，假设随机插入：51MB * 69% = 35MB。除以 5M 行。每个 2 个字节CHAR。如果全部相同，或者一个块中的所有前缀都相同，那么它就适合。

    CHAR，向后工作，假设有序插入：每行 51MB/5MB = 10 字节。5 for pointer 留下 5 for CHAR. 还是有可能的。

    对基准测试的评论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你看的时候可能还没有更新。

    FIXED几乎永远不会比 更好DYNAMIC，即使使用 MyISAM。它可能有帮助的地方：

    • 删除一条较短的记录，然后插入一条较长的记录。
    • 更新记录，增加某些列的大小。

    如果你有很多这样的东西，而且“固定”尺寸并不比“动态”尺寸大多少，那么固定尺寸可能更好。

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

    MyISAM的唯一情况是磁盘占用空间小于 InnoDB。在几乎任何其他竞赛中，InnoDB 都达到或超过了 MyISAM。

    我的 10% 经验法则。当尝试优化某些东西（通常是速度或空间）时，我会估计提议的更改会有多大帮助。如果低于 10%，我会继续寻找“更大的鱼”。

    大么？. 5M记录；150MB 数据 + 索引。这不是很大。随着 MySQL 表的发展，我会将其排在大约第 80 个百分位。即使在 InnoDB 中实现，它也应该很容易缓存在 RAM 中。如果它将是几 GB，让我们看看具体的模式，而不是“哦，顺便说一句，BIGINT 是用于电话号码的”。

    最小的手机。可以将一个 12 位数字（没有长度信息）塞入BINARY(5). 但是，我认为代码中的混乱不值得付出努力。（也许我的 10% 规则生效了？）

    • 1

相关问题