主页 / dba / 问题 / 160251
Accepted
the_nuts
Asked: 2017-01-08 02:32:21 +0800 CST

在生产中更改表列

  • 772

我想将表列从更改CHAR(2) utf8mb4为CHAR(2) latin1（因此它将使用 2 个字节而不是 8 个字节，不需要 utf8，因为它们是国家代码，所以只有拉丁字符）。

该表每秒接收大约 50 个新行。

大约有 1.25 亿行，总表大小为 5GB，因此更改需要花费一些时间。

如何在不锁定写入的情况下更改表？

mysql innodb

2 个回答

  1. Best Answer

    问题 1 - 如何在ALTER不严重阻塞一切的情况下做到这一点。答：使用pt-online-schema-change。

    问题 2 - 类似 char 的数据类型的存储。

    过去，CHAR(n)总是保留 n*c 个字节，其中 c 是基于CHARACTER SETlatin1 的最大大小：1 代表 latin1，3 代表 utf8，4 代表 utf8mb4。对于具有固定大小行的 Engine=MyISAM，这有时很方便。

    在某些ROW_FORMATs足够新的 InnoDB 版本中（抱歉，我没有具体细节），CHAR(n)将占用 n 到 n*c 个字节，具体取决于实际需要的字符。使用 InnoDB，没有固定大小行的概念。

    一个SELECT需要临时表（例如 for ORDER BY）的综合体将尝试为此使用一个MEMORY表。CHAR(n) 在这种情况下， and 的长度VARCHAR(n)将始终为 n*c。这有时会导致效率低下。或者它可能使用 MyISAM 作为 tmp 表。8.0 版将转向使用 InnoDB 来处理 tmp 表，因此这一段最终将没有实际意义。

    转换和/或将列从一个列转换CHAR为另一个列需要重型（不是）。请参阅https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html 该链接适用于 5.6；发生了变化；为您的版本选择页面。VARCHARCHARACTER SETALTERINPLACE

    VARCHAR有 1 或 2 个字节的长度，加上实际字符所需的字节数。所以它是 1+n*c 简称 max len。

    • 3

  2. UTF8MB 中的 CHAR(2) 在 latin1 中存储为 VARCHAR(2*4)。所以你不会节省太多——长度字段上只有一个字节。

    更新：

    一张桌子t1：

    CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `country` char(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

    有两条记录：

    mysql> SELECT * FROM t1;
+----+---------+
| id | country |
+----+---------+
|  1 | ua      |
|  2 | us      |
+----+---------+
2 rows in set (0.00 sec)

    在磁盘上看起来像这样：

    0000c060  02 00 1c 69 6e 66 69 6d  75 6d 00 03 00 0b 00 00  |...infimum......|
0000c070  73 75 70 72 65 6d 75 6d  02 00 00 00 10 00 1a 80  |supremum........|
0000c080  00 00 01 00 00 00 00 31  09 a9 00 00 01 1d 01 10  |.......1........|
0000c090  75 61 02 00 00 00 18 ff  d7 80 00 00 02 00 00 00  |ua..............|
0000c0a0  00 31 0a aa 00 00 01 1e  01 10 75 73 00 00 00 00  |.1........us....|

    让我们分开吧。

    记录1：

    • 0x80000001那将是一个签名INT的主键
    • 0x000000003109- 六字节交易ID
    • 0xa90000011d0110- 七字节回滚指针
    • 0x7561- 那将是十六进制的“ua”值。两个字节，而不是四个。

    以下字节属于第二条记录：

    • 0x02- 它是一个值的长度country- 两个字节。长度本身使用一个字节，因为 CHAR(2) 最多可以使用 2*4 个字节，小于 256。
    • 00-NULL价值观地图。country可以为 NULL，因此至少用一个字节来编码一个NULL值。
    • 0x000018ffd7- 五个字节的所谓“额外字节”字段。
    • 0x80000002- 第二条记录的主键 - 二。
    • 0x00000000310a-trx_id
    • 0xaa0000011e0110-roll_ptr
    • 0x7573- “我们”

    所以，这是一种COMPACT格式DYNAMIC。

    让我们对格式做同样的练习REDUNDANT——自版本 4.0.*+ 以来最古老的 InnoDB 格式

    0000c060  00 00 03 00 87 69 6e 66  69 6d 75 6d 00 09 03 00  |.....infimum....|
0000c070  08 03 00 00 73 75 70 72  65 6d 75 6d 00 19 11 0a  |....supremum....|
0000c080  04 00 00 10 09 00 aa 80  00 00 01 00 00 00 00 31  |...............1|
0000c090  14 b2 00 00 01 26 01 10  75 61 20 20 20 20 20 20  |.....&..ua      |
0000c0a0  19 11 0a 04 00 00 18 09  00 74 80 00 00 02 00 00  |.........t......|
0000c0b0  00 00 31 15 b3 00 00 01  27 01 10 75 73 20 20 20  |..1.....'..us   |
0000c0c0  20 20 20 19 11 0a 04 00  00 20 09 00 74 80 00 00  |   ...... ..t...|

    区别：

    • “额外字节”是六个字节长。
    • 每个字段都有长度，包括固定长度。
    • CHAR(2) 与尾随空格一起存储以填充到八个字节 (2*4)。
    • 2

相关问题