为什么将Mysql数据库列的字符长度设为191?

JerryXia 发表于 , 阅读 (933)

有时候,当你查看一个数据库的schema时,你会看到有这样定义的文本字段:

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是用8位整数表示的最大数。MySQL 非常关注速度和内存使用,希望用尽可能小的数据类型存储东西
  • MySQL 本身试图与更老的数据库(sybase/SAP)兼容,它们有255个字符的限制
  • MySQL 希望确保它的索引文件可以在旧文件系统的单个页面块中存放

考虑到256个字符的限制,MySQL 开发人员对于针对255个字符的限制(稍后将详细介绍)进一步优化数据库的许多部分感到放心。由于许多流行的开源应用程序框架都是在那个时期发布的(比如 Wordpress、 Django 和 Rails) ,它们都遵循了 MySQL 的默认设置,即使它们可以在多种数据库类型上运行,比如 postgres。这形成了大多数 orm (对象-关系映射-维基百科)使用varchar (255)的普遍缺省值,而不管使用的数据库是什么。

这是emoji的错

255比191合理多了。我们是怎么到191的?我要怪emoji表情符号。说真的。嗯,utf8mb4至少,字符集,允许“international”1个字符,并包括第一个表情符号。在2000年代早期,MySQL 很乐意在 varchar 列中支持255个字符并对它们进行索引。然而,在最流行的 MySQL 数据库引擎(innodb)上,最流行的文本编码(Latin1utf8)假定3个字节就足以存储每个字符2,一旦utf8mb4`带有3和4个字节来存储每个字符。这里有更多的字符可供选择,所以引用他们需要更多的字节。

Innodb MySQL数据库的工作方式是,索引只能有767个字节——足以存储255个3字节的字符(767/3 = 255)。这是一个索引优化的极端例子,基于知道索引数据的大小!因此,如果字符需要更多的空间来存储,那么您可以索引的字符数必须变得更少。具体来说,767/4 = 191个字符!随着越来越多的软件支持国际受众,varchar (191)取代 varchar (255)成为默认受众。对于那些不需要支持国际用户的软件应用程序,一旦用户在2010年初期开始期待表情符号支持(通常与智能手机的崛起有关) ,他们也需要升级。

今天

如今,在现代数据库中,默认的字符编码是 utf8mb4等可以支持“所有”字符的字符编码,而固定长度的索引已经成为过去。然而,在许多应用程序中,我们仍然有这些191个字符的默认值,以确保兼容性。无论如何,当索引知道所比较的字符串的大小时,它们仍然工作得最好,所以出于速度方面的原因,我们仍然希望对列长度进行一些限制,而且由于历史和惯性,191限制仍然存在。

添加新评论