为什么 MySQL 数据库的字符串列宽是191?
微wx笑 2021-06-06【数据库】 2 0关键字: MySQL 数据库 字符串
MySQL 数据库的 varchar 格式的列,长度常常是191个字符,这是为什么?
原文:https://www.grouparoo.com/blog/varchar-191
有时候,当你查看一个数据库的模式时,你会看到有这样定义的文本字段:
email_address varchar(191) NOT NULL
这意味着列支持最大长度为191个字符的字符串,并且不能为空。191是个奇数,从哪儿来的?在这篇文章中,我们将探讨大多数关系数据库将191个字符限制作为默认值的历史原因。
为什么是 varchar 而不是text?
您可能会问的第一个问题是,为什么要限制可以存储在数据库中的字符串的长度呢?所有现代流行的关系数据库都支持(几乎)无限大小的字符串和文本或 blob 类型的列,那么为什么不使用它呢?原因在于索引。
如果你想通过一个栏目来搜索,比如 email 地址,你可能需要添加一个索引来加快搜索速度,当你这样做的时候:
select id from users where email = 'foo@example.com';
随着表变得越来越大,搜索变得越来越慢,因为数据库必须检查每一行才能找到匹配项。但是,如果您添加了一个搜索索引,那么您就是在告诉数据库使用一棵树来“预先计算”流行的搜索模式,这样下一次搜索就会快得多。本质上,索引花费了几秒计算时间(和一点点磁盘空间)使写入到数据库的速度变慢,以加快后面的读取速度。对于大多数应用程序来说,这是一个很大的折衷,因为它们是“读重”和“写轻”的。
那么,为什么要使用 varchar 呢?如果可以对索引存储的数据类型进行假设,则可以使索引执行得更好。知道索引中的字符串有多长是加快速度的最好方法之一。对于某些数据库,不允许向文本类型的列添加搜索索引,因为这种优化无法完成,而在其他数据库中,索引的性能就不那么好了。事实上,从历史上看,数据库的构建限制了索引的大小,以优化搜索,以及数据在磁盘上的存储方式。
这是 MySQL 的错
好的,索引是好的。但是,一般来说,似乎任何大小的索引都可以工作,虽然这在今天是正确的,但并不总是可能的。我们的下一步是查看过去默认的列大小是多少,即255个字符,例如:
email_address varchar(255) NOT NULL
2000年代早期最流行的开源数据库 MySQL 在索引字段中限制了255个字符。关于 MySQL 为什么选择255个字符的限制历史是模糊的(见下面链接的文章) ,但是最流行的理论包括:
256 is the largest number you can represent with an 8-bit integer. MySQL being very concerned with speed and memory usage, wanted to store things with the smallest possible data types.
MySQL was itself trying to be compatible with even older databases (sybase/SAP), and they had a 255 character limit.
MySQL wanted to ensure that its index files could fit within a single page block on older file systems.
考虑到256个字符的限制,MySQL 开发人员对于针对255个字符的限制(稍后将详细介绍)进一步优化数据库的许多部分感到放心。由于许多流行的开源应用程序框架都是在那个时期发布的(比如 Wordpress、 Django 和 Rails) ,它们都遵循了 MySQL 的默认设置,即使它们可以在多种数据库类型上运行,比如 postgres。这形成了大多数 orm (对象-关系映射-维基百科)使用 varchar (255)的普遍缺省值,而不管使用的数据库是什么。
这是🐟的错
255比191合理多了。我们是怎么到191的?我要怪表情符号。说真的。嗯,utf8mb4至少,字符集,允许“国际”1个字符,并包括第一个表情符号。在2000年代早期,MySQL 很乐意在 varchar 列中支持255个字符并对它们进行索引。然而,在最流行的 MySQL 数据库引擎(innodb)上,最流行的文本编码(Latin1或 utf8)假定3个字节就足以存储每个字符2,一旦 utf8mb4带有3和4个字节来存储每个字符。这里有更多的字符可供选择,所以引用他们需要更多的字节。
Innodb MySQL 数据库的工作方式是,索引只能有767个字节——足以存储255个3字节的字符(767/3 = 255)。这是一个索引优化的极端例子,基于知道索引数据的大小!因此,如果字符需要更多的空间来存储,那么您可以索引的字符数必须变得更少。具体来说,767/4 = 191个字符!随着越来越多的软件支持国际受众,varchar (191)取代 varchar (255)成为默认受众。对于那些不需要支持国际用户的软件应用程序,一旦用户在2010年初期开始期待表情符号支持(通常与智能手机的崛起有关) ,他们也需要升级。
今天
如今,在现代数据库中,默认的字符编码是 utf8mb4等可以支持“所有”字符的字符编码,而固定长度的索引已经成为过去。然而,在许多应用程序中,我们仍然有这些191个字符的默认值,以确保兼容性。无论如何,当索引知道所比较的字符串的大小时,它们仍然工作得最好,所以出于速度方面的原因,我们仍然希望对列长度进行一些限制,而且由于历史和惯性,191限制仍然存在。
鸣谢
感谢我在整理这些历史时查阅的所有参考文章,特别是:.
utf 8 - Mysql four byte chinese characters support - Stack Overflow
mysql - Why are InnoDB’s index keys limited to 767 bytes? - Database Administrators Stack Exchange
本文由 微wx笑 创作,采用 署名-非商业性使用-相同方式共享 4.0 许可协议,转载请附上原文出处链接及本声明。
原文链接:https://www.ivu4e.cn/blog/database/2021-06-06/675.html