关于数据库可变长字符串类型长度设计问题:慷慨是不明智的

2021年9月9日 51点热度 0条评论 来源: haihui_yang

转载请注明原创出处,谢谢!

HappyFeet的博客

varchar 类型用于存储可变长字符串,是最常见的字符串数据类型。它比定长类型更节省空间,因为它仅使用必要的空间(例如:越短的字符串使用越少的空间)。

本文基于 MySQL 数据库进行探讨。

MySQL 版本为:Server version: 5.7.16 MySQL Community Server (GPL)。

1、varchar 的长度分配问题

一直对数据库的可变长字符串的长度分配问题存在疑问:不知道应该分配多长。

今天我们就来看看 varchar 的长度是依据什么来判断的?

  • 有的人说:“属性有多长,就分配多长”。 ---- 即只分配真正需要的空间

  • 也有人说:“属性的长度应为 4 的倍数,因为这样可以做到内存对齐”。 ---- 即分配为 4 的倍数的长度,可以内存对齐

  • 另一些极端的人觉得:“既然对于 varchar 来说,越短的字符串使用越少的空间,那么这个长度定大一些也不会有什么问题,都分配 4000 的长度,这样可以避免出现超过长度的数据存不进去的问题”,即不管属性长度如何,都分配一个很大的长度,例如:4000.

那到底哪一种说法是对的呢?

我们来看个例子,假设我们要建一张表来存 CSDN 个人资料中的三条属性:昵称、地区和简介。下面有三个版本的建表语句,分别针对不同的说法所创建的。

-- 版本 1:只分配真正需要的空间
create table user_info (
	user_id 		long primary key,
	nickname 		varchar(20),
	region   		varchar(100),
	introduction 	varchar(500)
);
-- 版本 2:在版本 1 的基础上调整为 4 的倍数
create table user_info (
	user_id 		long primary key,
	nickname 		varchar(32),
	region   		varchar(128),
	introduction 	varchar(512)
);
-- 版本 3:长度都取 4000
create table user_info (
	user_id 		long primary key,
	nickname 		varchar(4000),
	region   		varchar(4000),
	introduction 	varchar(4000)
);

经过查阅众多资料之后,得出结论:版本 1 是最好的,即只分配真正需要的空间

2、结论分析

下面就来说一下为什么:

(1)varchar 需要 1 或 2 个额外字节记录字符串的长度

如果列的最大长度小于或等于 255 字节,则只使用 1 个字节表示,否则使用 2 个字节。假设采用 latin1 字符集,一个 varchar(10) 的列需要 11 个字节的存储空间。varchar(1000) 的列则需要 1002 个字节,因为需要 2 个字节存储长度信息。

(2)内存对齐 确实有一定的性能提升,但是通过将字段长度定义为 4 的倍数做不到内存对齐

对于 varchar 类型来说,字符串是变长的,它的实际存储长度为:1 或 2 个额外字节 + 字符串实际长度。举几个例子:

a、往 varchar(8) 中存入 "abcdefgh",实际存储长度应该为 1 + 8 = 9 个字节,第一个字节记录这个字符串的长度;

b、往 varchar(8) 中存入 "abc",实际存储长度为 1 + 3 = 4 个字节。

c、往 varchar(1000) 中存入 "abc",实际存储长度为 2 + 3 = 5 个字节。

说到内存对齐就简单提一下 MySQL 存储引擎 InnoDB 的数据页结构:page 是 MySQL 管理存储空间的基本单位,它由一条或多条 row 组成,row 就对应于 table 的一条记录,数据读取也是基于 page 来的,而不是一行一行的读。更多详情见:InnoDB数据页结构

(3)MySQL 在解决某些类型的查询时需要创建隐式临时表(又分为内存临时表和磁盘临时表)

对于临时表中的字段,MySQL 都会分配一个足够长的定长空间来存放。这个定长空间必须足够长以容纳其中最长的字符串,例如,如果是 varchar 列则需要分配其完整长度,如果使用 UTF-8 字符集,那么 MySQL 将会为每个字符预留三个字节。

假设我们在查询 user_info 的时候触发了隐式临时表的创建,对于 nickname 这一列,如果我们使用版本 1的建表语句,MySQL 按照完整长度,每条记录仅需要分配 20 的内存,而如果是版本 3的话,每条记录却需要分配 4000 的长度!!!很明显,这是一个非常糟糕的设计。

MySQL 在以下几种情况会创建临时表(不过这不是本文的重点!)

  • UNION 查询;

  • 用到 TEMPTABLE 算法或者是 UNION 查询中的视图;

  • ORDER BY 和 GROUP BY 的子句不一样时;

  • 表连接中,ORDER BY 的列不是驱动表中的;

  • DISTINCT 查询并且加上 ORDER BY 时;

  • SQL 中用到 SQL_SMALL_RESULT 选项时;

  • FROM 中的子查询;

  • 子查询或者 semi-join 时创建的表;

(4)预分配内存空间的问题

注意,这一点存疑!这个说法我目前还没有找到具体的源码或文档说明,不过如果有预分配内存的话,那应该是说得通的,待找到依据再对这一点进行更新

这个和第三点有点类似,只是第三点讲的是在数据库内部引擎中,而这里讲的是客户端从数据库引擎获取数据。简单来说就是,对于一个 varchar(1000),客户端的 executor 在没拿到存储引擎存储的数据之前,并不会知道我这一行拿出来的数据到底有多长,可能长度只有 1,可能长度是 800,那怎么办呢,那就只能先把最大空间分配好了,避免放不下的问题发生,这样实际上对于真实数据较短的 varchar 确实会造成空间的浪费。举例:如果我有 1000 个 varchar(1000),但是每个只存一个字符,那么真实数据量其实只有 1K,但是我却需要 1M 的内存去适应它。

(5)字段长度能够起到一定的约束作用

比如一个字段长度最大不超过 10 个字节,那我们就可以将其设置为 varchar(10),如果超过了 10 个字节,就说明这是个脏数据,这对实际数据能起到一定的约束。

(6)数据库对于索引的字段长度有限制,在长的数据列上建索引可能会报错,例如

mysql> create table long_length_table(
    -> long_column1 varchar(2000),
    -> long_column2 varchar(2000),
    -> long_column3 varchar(2000),
    -> long_column4 varchar(2000),
    -> long_column5 varchar(2000)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> create table short_length_table(
    -> short_column1 varchar(10),
    -> short_column2 varchar(10),
    -> short_column3 varchar(10),
    -> short_column4 varchar(10),
    -> short_column5 varchar(10)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> create index short_length_table_idx on short_length_table(short_column1, short_column2, short_column3, short_column4);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index long_length_table_idx on long_length_table(long_column1, long_column2, long_column3, long_column4);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

3、总结

在 varchar(30) 和 varchar(1000) 存入 “ab”,使用的存储空间区别不大,无非就是一个使用 1 个字节记录字符串的长度,另一个使用 2 个字节记录。但是对于在某些 case 下,varchar(1000) 的性能会比 varchar(30) 差很多,内存消耗也会成倍增加。

另外,将长度调整为 4 的倍数(内存对齐)会提高性能这种说法也是不对的。

所以得出的结论是: 只分配真正需要的空间

PS:以后终于可以理直气壮的跟别人讲:varchar 的长度不宜过长,满足长度要求即可;也不必强行将长度设置成 4 的倍数,这对性能提升是没有效果的。_

参考资料:

(1)《高性能 MySQL》第三版.

(2)MySQL - varchar length and performance

(3)What are the current best practices concerning varchar sizing in SQL Server?

(4)Best practices for SQL varchar column length

(5)Would using varchar(5000) be bad compared to varchar(255)?

(6)What are the optimum varchar sizes for MySQL?

(7)MySQL: Disk Space Exhaustion for Implicit Temporary Tables

(8)File Space Management

(9)InnoDB数据页结构

    原文作者:haihui_yang
    原文地址: https://blog.csdn.net/haihui_yang/article/details/103229425
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。