主页 / dba / 问题 / 294222
Accepted
dierre
Asked: 2021-06-15 04:34:38 +0800 CST

将字符集从 utf8 更改为 ascii 是否会改善 mysql 上 CHAR 字段的消耗空间？

  • 772

我有下表：

CREATE TABLE `tokens` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `s_id` int(10) unsigned NOT NULL,
  `a_token` char(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `a_token_exp` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_a_token` (`a_token`) USING HASH,
  KEY `f_seid` (`s_id`),
  CONSTRAINT `f_seid` FOREIGN KEY (`s_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1

我想更改字符集，a_token因为我们使用此命令仅使用 ascii 字符：

ALTER TABLE tokens MODIFY a_token CHAR(40) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL;

和新SHOW CREATE TABLE节目：

CREATE TABLE `tokens` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id` int(10) unsigned NOT NULL,
  `a_token` char(40) CHARACTER SET ascii NOT NULL,
  `a_token_exp` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_a_token` (`a_token`) USING HASH,
  KEY `f_seid` (`s_id`),
  CONSTRAINT `f_seid` FOREIGN KEY (`s_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1

然后我跑OPTIMIZE TABLE oauth_session_access_tokens。

此查询应该（我不确定）我的架构的表大小：

SELECT table_name AS "Table",
      ((data_length + index_length)) AS "Size"
FROM information_schema.TABLES
WHERE table_schema = "test"
ORDER BY (data_length + index_length) DESC;

但特定表在.之前和之后tokens总是返回49152ALTER TABLE

我可以假设即使使用 CHAR 和 utf8，如果只有 ASCII 字符，那么该字段不会为 utf8 预先分配空间？

mysql aws-aurora

2 个回答

  1. UTF-8 是一种变长字符编码。对于 ASCII 范围内的字符，每个字符只占用 1 个字节。对于那些需要它的字符，它每个字符只使用 2、3 或 4 个字节。

    关于 UTF-8 的维基百科文章对多字节编码的工作方式有很好的解释和说明。https://en.wikipedia.org/wiki/UTF-8

    因此，即使您只有 ASCII 字符要存储，使用 UTF-8 也没有什么缺点。

    • 5
  2. Best Answer

    简短回答：是的，切换到 ASCII。

    Looooong 回答：这个问题有很多方面。

    • 如果字符串的长度实际上不同，请使用VARCHAR而不是CHAR.
    • 如果字符串的长度是固定的，请使用CHAR. （40 听起来像 SHA1，它是固定长度）。
    • 如果字符串始终为十六进制，则UNHEX()在存储和HEX()获取时使用，然后存储到一半大小的BINARY或VARBINARY列中（BINARY(20)对于 SHA1）。这为 Ascii 节省了每行 20 个字节。
    • 对于旧版本的 MySQL 和不同的长度，CHAR填充到给定的长度。
    • 对于旧版本的 MySQL，每列强制使用 3 个字节，将其中的两个字节浪费为十六进制CHAR。utf8这可能是您的主要问题。
    • 如果您将其保留为CHARor VARCHAR，请考虑COLLATION使用什么。对于十六进制，任何以结尾的排序规则_ci都可能是合适的。（对于 Base64 文本，_bin是合适的。）
    • 49152 字节 = 3*16KB。这意味着表中的行太少了，1 个块用于数据，1 个块用于两个二级索引中的每一个。即使该表正好有 1 行，该表也将占用 49152 个字节。或者 100 行可能是相同的大小。
    • 如今，49152 字节微不足道。即使有一百万行，表的大小也不太可能很大。对于十亿行，是的，在这里研究我的观点。（但是，对于 10 亿行，会出现另一个问题——INT UNSIGNED溢出大约 40 亿行。）
    • 当索引变得大于 RAM 时，SHA1（或任何其他“随机”字符串）将出现性能问题。
    • 在这个问题的上下文中，Ascii并latin1执行类似的操作。
    • （可能还有更多我可以指出的事情。大多数都是次要的。）
    • 2

相关问题