MySQL VARCHAR 最佳长度评估实践

2024-05-07 323

你的 VARCHAR 长度合适么？

作者：官永强，爱可生 DBA 团队成员，擅长 MySQL 运维方面的技能。热爱学习新知识，亦是个爱打游戏的宅男。

作者：李富强，爱可生 DBA 团队成员，熟悉 MySQL，TiDB，OceanBase 等数据库。相信持续把对的事情做好一点，会有不一样的收获。

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

本文约 2200 字，预计阅读需要 8 分钟。

背景描述

有客户反馈，他们对一个 VARCHAR 类型的字段进行长度扩容。第一次可以很快就可以修改好，但是第二次却需要执行很久。比较疑惑明明表中的数据量是差不多的，为什么从 VARCHAR(20) 调整为 VARCHAR(50) 就比较快，但是从 VARCHAR(50) 调整为 VARCHAR(100) 就需要执行很久呢？ 于是我们对该情况进行场景复现并进行问题分析。

环境信息

本次验证涉及到的产品及版本信息如下：

产品	版本
MySQL	5.7.25-log MySQL Community Server (GPL)
Sysbench	sysbench 1.0.17

场景复现

3.1 数据准备

mysql> show create table test.sbtest1;
+---------+----------------------------------------+
| Table   | Create Table                           |
+---------+----------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+----------------------------------------+
1 row in set (0.00 sec)
mysql> select count(*) from test.sbtest1;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.10 sec)

3.2 问题验证

模拟客户的描述，我们对字段 c 进行修改，将 VARCHAR(20) 修改为 VARCHAR(50) 后再修改为 VARCHAR(100)，并观察其执行所需时间，以下是相关的操作命令以及执行结果：

mysql> ALTER TABLE test.sbtest1 MODIFY c VARCHAR(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test.sbtest1;
+---------+-------------------------------+
| Table   | Create Table                  |
+---------+-------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  `pad` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE test.sbtest1 MODIFY c VARCHAR(100);
Query OK, 1000000 rows affected (4.80 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> show create table test.sbtest1;
+---------+---------------------------+
| Table   | Create Table              |
+---------+---------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
  `pad` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+------------------------------------------------------------------------+
1 row in set (0.00 sec)

通过验证发现，该问题会稳定复现，故继续尝试去修改，最终发现在修改 VARCHAR(63) 为 VARCHAR(64) 时需要执行很久，但在 64 之后继续进行长度扩容发现可以很快完成。

mysql> ALTER TABLE test.sbtest1 MODIFY c VARCHAR(63);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test.sbtest1 MODIFY c VARCHAR(64);
Query OK, 1000000 rows affected (4.87 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> show create table test.sbtest1;
+---------+---------------+
| Table   | Create Table  |
+---------+---------------+
| sbtest1 | CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `pad` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE test.sbtest1 MODIFY c VARCHAR(65);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test.sbtest1 MODIFY c VARCHAR(66);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

3.3 问题分析

对于 VARCHAR(63) 修改为 VARCHAR(64) 需要执行很久的这个情况进行分析。通过查阅官方文档发现，由于 VARCHAR 字符类型在字节长度为 1 时可存储的字符为 0~255。当前字符集类型为 UTF8MB4，由于 UTF8MB4 为四字节编码字符集，即一个字节长度可存储 63.75（255/4）个字符，所以当我们将 VARCHAR(63) 修改为 VARCHAR(64) 时，需要增加一个字节去进行数据的存储，就要通过建立临时表的方式去完成本次长度扩容，故需要花费大量时间。

拓展验证

4.1 数据准备

mysql> show create table test_utf8.sbtest1;
+---------+----------------------------------------+
| Table   | Create Table                           |
+---------+----------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` varchar(20) NOT NULL DEFAULT '',
  `pad` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 |
+---------+------------------+
1 row in set (0.00 sec)

mysql> select count(*) from test_utf8.sbtest1;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.10 sec)

4.2 UTF8 场景验证

由于 UTF8 为三字节编码字符集，即一个字节可存储 85（255/3=85）个字符。

本次修改顺序：VARCHAR(20)→VARCHAR(50)→VARCHAR(85)，并观察其执行所需时间，以下是相关的操作命令以及执行结果：

mysql>  ALTER TABLE test_utf8.sbtest1 MODIFY c VARCHAR(50) ,algorithm=inplace,lock=none;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test_utf8.sbtest1 MODIFY c VARCHAR(85) ,algorithm=inplace,lock=none;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test_utf8.sbtest1;
+---------+-------------------------------+
| Table   | Create Table                  |
+---------+-------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` varchar(85) DEFAULT NULL,
  `pad` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 |
+---------+--------------------------------------------------+
1 row in set (0.00 sec)

修改顺序：VARCHAR(85)→VARCHAR(86)→VARCHAR(100)，此时我们会观察到执行的 SQL 语句直接返回报错。于是我们删除 algorithm=inplace ,lock=none 这两个参数，即允许本次 SQL 创建临时表以及给目标表上锁，然后重新执行 SQL，以下是相关的操作命令以及执行结果：

mysql> ALTER TABLE test_utf8.sbtest1 MODIFY c VARCHAR(86) ,algorithm=inplace,lock=none;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

mysql> ALTER TABLE test_utf8.sbtest1 MODIFY c VARCHAR(86);
Query OK, 1000000 rows affected (4.94 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> show create table test_utf8.sbtest1;
+---------+-------------------------------+
| Table   | Create Table                  |
+---------+-------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` varchar(86) DEFAULT NULL,
  `pad` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 |
+---------+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE test_utf8.sbtest1 MODIFY c VARCHAR(100) ,algorithm=inplace,lock=none;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.3 UTF8MB4 场景验证

由于 UTF8MB4 为四字节编码字符集，即一个字节长度可存储 63（255/4=63.75）个字符。

本次修改顺序：VARCHAR(20)→VARCHAR(50)→VARCHAR(63)，并观察其执行所需时间，以下是相关的操作命令以及执行结果：

mysql>  ALTER TABLE test.sbtest1 MODIFY c VARCHAR(50) ,algorithm=inplace,lock=none;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test.sbtest1 MODIFY c VARCHAR(63) ,algorithm=inplace,lock=none;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test.sbtest1;
+---------+-------------------------+
| Table   | Create Table           |
+---------+-------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` varchar(63) COLLATE utf8mb4_bin DEFAULT NULL,
  `pad` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

本次修改顺序：VARCHAR(63)→VARCHAR(64)→VARCHAR(100)，此时我们会观察到执行的 SQL 语句直接返回报错。于是我们删除 algorithm=inplace, lock=none 这两个参数，即允许本次 SQL 创建临时表以及给目标表上锁，然后重新执行 SQL，以下是相关的操作命令以及执行结果：

mysql> ALTER TABLE test.sbtest1 MODIFY c VARCHAR(64) ,algorithm=inplace,lock=none;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

mysql> ALTER TABLE test.sbtest1 MODIFY c VARCHAR(64) ;
Query OK, 1000000 rows affected (4.93 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> show create table test.sbtest1;
+---------+--------------------------+
| Table   | Create Table             |
+---------+--------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `pad` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE test.sbtest1 MODIFY c VARCHAR(100) ,algorithm=inplace,lock=none;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.4 对比分析

字符长度修改	UTF8(MB3)	UTF8MB4
20->50	online ddl (inplace)	online ddl (inplace)
50->100	online ddl (copy)	online ddl (copy)
X->Y	当Y3<256 时，inplace <br> 当X3>=256，inplace	当 Y4<256 时，inplace <br> 当 X4>=256，inplace
备注	一个字符最大占用 3 个字节	一个字符最大占用 4 个字节

结论

当一个字段的最大字节长度 >=256 字符时，需要 2 个字节来表示字段长度。

使用 UTF8MB4 举例：

对于字段的最大字节长度在 256 字符内变化 (即 x*4<256 且 Y*4<256)，online ddl 走 inplace 模式，效率高。
对于字段的最大字节长度在 256 字符外变化 (即 x*4>=256 且 Y*4>=256) ，online ddl 走 inplace 模式，效率高。
否则，online ddl 走 copy 模式，效率低.
UTF8(MB3) 同理。

建议

为避免由于后期字段长度扩容，online ddl 走效率低的 copy 模式，建议：

对于 UTF8(MB3) 字符类型：
- 字符个数小于 50 个，建议设置为 VARCHAR(50) 或更小的字符长度。
- 字符个数接近 84（256/3=83.33）个，建议设置为varchar(84)或更大的字符长度。
对于 UTF8MB4 字符类型：
- 字符个数小于 50 个，建议设置为 VARCHAR(50)，或更小的字符长度。
- 字符个数接近 64（256/4=64）个，建议设置为 VARCHAR(64) 或更大的字符长度。

本次验证结果仅供参考，若您需要在生产环境中进行操作，请结合实际情况合理定义 VARCHAR 的长度，避免造成经济损失。

更多技术文章，请访问：https://opensource.actionsky.com/

关于 SQLE

SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。

SQLE 获取

类型	地址
版本库	https://github.com/actiontech/sqle
文档	https://actiontech.github.io/sqle-docs/
发布信息	https://github.com/actiontech/sqle/releases
数据审核插件开发文档	https://actiontech.github.io/sqle-docs/docs/dev-manual/plugins/howtouse

微信关注我们

原文链接：https://my.oschina.net/actiontechoss/blog/11094958

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

开源日报 | RustDesk暂停国内服务；高中生自创开源编程语言；国产开源MoE指标炸裂；大模型开闭源之争的实质；iPad要为AI“练兵”？

欢迎阅读 OSCHINA 编辑部出品的开源日报，每天更新一期。 # 2024.5.7 今日要点 RustDesk 由于诈骗猖獗，暂停国内服务 RustDesk 是一款开源远程桌面软件，然而也被诈骗分子频繁使用。为了进一步应对诈骗，RustDesk 暂时决定停止中国地区的服务，如果用户现在通过公共服务器访问国内主机，将会收到被禁止的消息。针对海外，RustDesk 也只允许同城访问，未来也会屏蔽掉所有 VPN（等找到合适的数据源）。高中生自创开源编程语言作为成人礼一名高中生独立设计和开发了名为「Key Lang」的编程语言。他在 B 站上传视频分享了这门自创的编程语言，表示这是送给自己的成人礼，项目也已在 GitHub 开源。 https://www.bilibili.com/video/BV1St421P72d/ https://github.com/Bylx666/key-lang 国产开源MoE指标炸裂：GPT-4级别能力，API价格仅百分之一最新国产开源MoE大模型，刚刚亮相就火了。DeepSeek-V2性能达GPT-4级别，但开源、可免费商用、API价格仅为GPT-4-...

2024-05-07

380

数据库索引是优化任何数据库系统性能的关键组成部分。如果没有有效的索引，您的数据库查询可能会变得缓慢且低效，从而导致用户体验不佳并降低生产力。在这篇文章中，我们将探讨创建和使用数据库索引的一些最佳实践。作者：The Java Trail 本文和封面来源：https://medium.com/，爱可生开源社区翻译。本文约 2700 字，预计阅读需要 9 分钟。数据库中使用多种索引算法来提高查询性能。以下是一些最常用的索引算法： B 树（B-Tree）索引 B-Tree 索引是一种自平衡树数据结构，可保持数据排序并允许在对数时间内搜索、顺序访问、插入和删除。 B-Tree 索引结构广泛应用于数据库和文件系统中。 B-Tree 索引广泛应用于 MySQL、PostgreSQL 等关系数据库中。 B-Tree 索引针对范围查询进行了优化，因为它们可以有效地查找某个值范围内的所有记录。这是因为记录在索引中按排序顺序存储。利用在使用 =、>、>=、<、<= 或 BETWEEN 运算符的表达式中使用列比较。例如，假设我们有一个具有以下表结构的产品表： CREATE TA...

2024-05-07

373

资源下载

更多资源

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。