MySQL数据类型选择与设计（转载）_数据库中数据类型的设计-CSDN博客

1 MySQL的基本数据类型

首先了解一下MySQL的基本数据类型，要注意的是MySQL为了兼容性，所以支持很多别名,如INTEGER, BOOL以及NUMERIC，这些别名在底层还是用的MySQL的基本数据类型。

1.1 整数(whole number)类型

MySQL中数字类型有两个：整数和实数。整数的类型有：TINYINT（8位），SMALLINT（16位），MEDIUMINT（24位），INT（32位），BIGINT（64位）。储值范围为-2(N-1)到2(N-1)，N为位数。

整数类型有可选的UNSIGNED属性，标识不允许负值，因为去掉了符号位，这大致可以让上限提升一倍。但是无论有无符号，在性能上是一样的。

类型的选择仅仅影响存储。涉及到计算除了部分聚合函数大多数情况下都会以64位的BIGINT的形式计算（即使在32位的系统），这一点是为什么呢？为什么不用实际类型运算呢？

MySQL也可以设置整数类型宽度，如INT(11),然而这几乎没有意义，设置宽度并不会影响值的合法范围，存储和性能上也并没有区别。也就是说对于存储和计算而言，INT(1)和INT(20)都是一样的。这个宽度设置仅仅用来在一些交互工具上显示字符个数。

1.2 实数（real number）类型

实数是带有小数的数字。MySQL既支持精确类型如DECIMAL,也支持不精确类型，如FLOAT和DOUBLE这样的浮点类型。浮点和DECIMAL都可以指定精度。不同于浮点型的固定空间占用，不同的数值对于DECIMAL来说，空间消耗也不同，这是因为MySQL将DECIMAL打包成一个二进制字符串(每4个字节存9个数字)，如一个小数点前后各9个数字的DECIMAL，那么这个数字就占据9个字节，小数点前的9位用4个字节，小数点后面的9用4个字节，小数点用一个字节。

浮点类型存储同样范围的值时，通常比DECIMAL使用更少的空间，FLOAT使用4字节，DOUBLE使用8字节。和整数类型一样，浮点类型选择的仅仅是存储类型，内部浮点计算还是使用的DOUBLE。

浮点运算属于近似计算，因此如果需要对小数的精确运算时可以使用DECIMAL。但如果数值比较大时可以使用BIGINT代替，如精度是万分之一，那么可以把所有数值乘以一百万，然后将结果存入BIGINT中，这样可以同时避免浮点数计算不精确已经DECIMAL计算代价高，占据空间大的问题。

1.3 字符串类型

1.3.1 VARCHAR和CAHR类型

VARCHAR和CHAR是两种数据类型，但是其值在磁盘的存储方式与存储引擎相关，这里仅仅记录这两种数据类型在常用的InnoDB上磁盘的存储方式。值得注意的是内存和磁盘的存储方式可能不一样，MySQL服务器从存储引擎读取到数据后可能需要换一种存储格式。

VARCHAR
VARCHAR类型用来存储变长的字符串。对于长度不固定的字符串，他比定长类型CHAR更节省空间，因为他仅适用必要的空间。但是这并无意味着可以说肆无忌惮的定义长度。因为在内部值存储上MySQL依然会分配固定的值，更长的列需要消耗更多的内存。定义不切实际的长度会在使用临时表进行排序或操作时特别糟糕，利用磁盘临时表进行排序也同样糟糕，因此最佳的策略依然是指分配真正需要的长度。

VARCHAR需要1或2个字节记录长度。如果最大长度小于等于255字节，则长度用1个字节，否则使用2个字节记录。这样一个VARCHAR(10)就需要11个字节的空间，VARCAHR(1000)则需要1002个字节空间。

VARCHAR变长的存储方式对性能是有帮助的，但是因为行是变长的，所以可能出现UPDATE的新值比原来的更长，这就需要额外的工作。如果导致所在行占用的空间增长且恰好行所在页内没有更多的空间存储，InnoDB就会触发页分裂以存储这个行。

适合使用VARCHAR的场景如下：字符串列的最大长度比平常长度大的多；列的更新比较少，这样不会让空间过度碎片化，也不会频繁触发页分裂；使用了像UTF-8这样复杂的字符集，每个字符使用不同的字节数进行存储。

从5.0开始，MySQL在存储和检索时会保留字符串空的空格，而CHAR则继续保留自动剔除末尾空格的特性。这个行为和存储引擎无关，而是在服务器层面做的。

InnoDB在存储方面较为灵活，如果字符串长度较长，可以以BLOB的方式存储字符串。

CHAR
CHAR是定长的，MySQL根据组定义的长度分配空间，CHAR会自动剔除字符串末尾的所有空格。CHAR并不代表着存储的字符串一定等于指定长度，也可以小于，对于小于指定长度的字符串，CHAR值会根据需要填充空格以方便比较。

CHAR适合存储较短的长度相近的字符串。空间长度定长加上字符串长度相近，这使得即使更新频繁，也不会导致空间的过度碎片化。且对于极短的列，CHAR比VARCHAR有跟高的空间利用效率，比如CHAR(1)仅需一个字节，而VARCHAR(1)则需要两个字节，因为还有一个字节记录长度。这是二倍的差距。不过长度越长，这个差距越小。

与CAHR和VARCAHR对应的还有BINARY和VARBINARY,用来存储二进制字符串，存储的是字节码而不是字符，BINARY的填充用的是\0(零字节)，而不是空格。

1.3.2 BLOB和TEXT类型

BLOB和TEXT都是用来存储很大的数据，分别采用二进制和字符形式存储。二进制家族有：TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB，二进制家族没有排序规则或字符集。字符串家族有：TINYTEXT,SMALLTEXT,,TEXT,,MEDIUMTEXT,,LONGTEXT，字符串家族有字符集和排序规则。

MySQL将BLOB和TEXT当做独立的对象存储，如果其值过大，InnoDB会使用专门的“外部”存储区域存储，然后再行内用一个1~4字节指针指向。在索引上MySQL也不会将BLOB和TEXT的全部长度作为索引。关于索引的详细信息在下一篇。

提一嘴：MySQL的Memory不支持BLOB和TEXT，如果查询了BLOB和TEXT列且需要使用隐式临时表那么就不得不使用MyISAM引擎临时表，这有很严重的性能开销。因此Memory引擎应该避免使用BLOB和TEXT，如果必须要使用，那么可以使用SUBSTRING截断到足够小，这样可以使用MySQL内存临时表。EXPLAIN执行计划中的Extra列如果包含了“Using temporary”，说明使用了隐式临时表。

1.3.3 使用枚举（ENUM代替字符串类型）

对于一些不重复的字符串，可以以枚举的方式存储。MySQL在存储枚举上非常紧凑，然后行内的值也会存储为枚举值对应的整数（下标从1开始），这可以在一定程度上缩小表大小。

CREATE TABLE enum_test(
	e ENUM('fish', 'apple', 'dog') NOT NULL
)
复制代码

枚举字段的ORDER BY排序是按照枚举值对应的整数排序，而不是字符串排序。MySQL可以用索引消除排序。

枚举的方式也有缺点：值范围固定，要调整值范围必选使用ALTER TABLE。行内存储为整数，要经过查找才能转换为字符串，因此如果进行非自身的关联查询，需要额外的开销。

1.4 日期和时间类型

MySQL能存储的最小时间粒度是秒，如果要使用微妙，那么可以用BIGINT存储秒级别的时间戳，或者使用DOUBLE存储秒之后的小数部分。MySQL有很多类型来保存日期和时间，例如YEAR和DATE。但是说起最常用的还是DATETIME和TIMESTAMP。至于如何选择，那就需要根据实际的目的来选择了：

DATETIME
DATETIME能保存大范围的值，从1001年到9999年，精度为秒，他把时间日期封装成YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节存储。默认情况下，MySQL以一种可排序的，无歧义的方式显示DATETIME值。如“2021-10-18 22:05:28”，这是ANSI标准定义的日期时间表示方法。

TIMESTAMP
TIMESTAMP就像他的名字一样，保存的是时间戳，即1970年1月1日午夜(格林尼治时间)以来的秒数。TIMESTAMP仅需要4个字节，因此他仅能表示1970年到2038年的数据。MyMySQL4.1后TIMESTAMP会按照DATETIME的形式显示。值得注意的是，TIMESTAMP的存储方式与时区相关，不同时区的机器会获得不同的时间。TIMESTAMP默认为NOT NULL，这点不同于其他数据类型，且插入时如果不指定值，行中的TIMESTAMP会被默认为当前时间。

除了特殊需要，应该尽量使用TIMESTAMP，因为其拥有更高的空间利用率。另外如果精度在秒级，自己将秒数转为整型存储并不会有什么收益，且处理起来也不方便。

1.5 位数据类型

BIT
可以使用BIT列在一列存储一个或多个true/false值，定义为BIT(n),最大长度是64,。MyISAM是按位存储的，但是InnoDB不是，InnoDB是使用一个足够小的最小整数类型存放，这和你自己定义一个整数类型，然后存入bit对应的值所占的空间是一样的，所以并不节约空间，虽然存储上是数值，但是查询时MySQL把BIT当做字符串类型，而不是数字类型。也就是说如果你存储一个b'00111001',那么你查出来的值是ASCII码为57的字符‘9’,而不是数值9。这点通过通过对结果运算验证：select a， a +0 from table; (a+0)结果是57，而不是9。因此BIT的使用应该谨慎。如果想用一个bit的空间存储true/false，或许可以创建一个可以为空的CHAR(0)列，然后用空值NULL和空字符串区别。

SET
如果要保存多个true/false值，可以考虑SET数据类型。这个类型是以打包的为来存储的，有效的利用空间。而且还有FIND_IN_SET()和FIELD()函数在查询使用。缺点是要修改列的定义代价比较高，因为定义上像枚举ENUM一样，需要ALTER TABLE才行。

CREATE TABLE acl(
	prems set('CAN_READ','CAN_WRITE','CAN_DELETE') NOT NULL
	);
INSERT INTO acl(perms) VALUES('CAN_READ,CAN_WRITE');
SELECT perms FROM acl WHERE FIND_IN_SET('CAN_READ'， perms);
复制代码

除了SET，获取我们可以自定义一个整型，然后让维护映射工作放在应用代码完成，好处是更改只需改代码就行了，表不用动，缺点是相较于SET，更难于理解。怎么使用，仁者见仁智者见智吧。

2 如何选择与设计

使用小而简单的合适数据类型。小而简单的类型意味着占据更小的物理空间(内存和磁盘)，CPU处理所需的周期也更少。比如整型比字符串比较的更快，使用Mysql内的时间日期格式也比字符串更快，TIMESTAMP只使用DATETIME一半的空间。但是前提是要确保值的范围在类型容许内，因为变更数据类型很麻烦。
除非真实数据模型有确切需要，否则应该避免使用NULL值,因为NULL需要额外的特殊处理与标记。特别索引所在的列更应该尽量避免NULL。
对于标识列，如作为主键和外键，联合查询的这些列。整数通常是最好的选择。ENUM和SET则是相对糟糕的选择，因为要经过转换。字符串也尽量避免，因为占据空间较大，比较速度也比整数慢。
避免使用“随机”的字符串作为主键，比如MD5(),SHA1()。这些函数生产的值会任意分布在很大的空间，这会导致INSERT和一些SELECT语句都很慢。这是因为无规律的广泛分布会导致页分裂，磁盘随机访问；逻辑相邻的行会分布在磁盘和内存的不同地方，这会导致SELECT变的更加慢；随机值也使得缓存赖以工作的访问局部原理失效。也许这种方式唯一的好处就是可以消除热点。解决超大表的热点区域问题。 UUID()虽然分别也不均匀，但是还是有一定顺序的，尽管如此，还是不如整数好用，如果要使用UUID,应该消除‘-’符号，更好的做法是使用UNHEX()函数把UUID值转换成一个16字节的字符串(因为UUID其实就是一个十六进制值的字符串形式)，然后存储在BINARY(16)列中，然后查询出来时再用HEX()函数转变回去。
尽量使用相同的数据类型存储相似或者相关的值，特别是要关联查询的列
尽量避免使用ORM自动生成表，一般情况下这种不透明的方式不仅扩展困难，而且性能较差。
对于一些值可以灵活改变类型，如IP可以去掉 '.' 存储为整数。MySQL为此专门提供了INET_ATON()和INET_NTOA()两个函数。
一个表有太多的列并不是一个好方案。MySQL读取行是通过先通过缓冲行从存储引擎拷贝到服务器层，然后在服务器层转换成行数据结构。这个转换的代价非常高。如果一个很宽的表，却只有一小部分列用到，那么转换所付出的代价就会浪费很多，而且无效的数据也会影响IO。如果使用数千个字段，那必须意识到服务器的性能特征会有一些不同。
过多的表关联是一个糟糕的主意，不仅维护困难，而且解析和优化查询也是问题。如果需要联表，那么最好在12个表以下。
范式并不是标准，范式设计可以重复数据较少，占据更小的内存，修改也更快，缺点是通常都需要关联查询。而反范式可以减少或者避免关联查询。适当的冗余数据可以有效的提升效率。完全的按范式或者反范式设计者只存在于实验室。
应该尽量避免使用枚举ENUM和类枚举SET。这些列扩展困难，需要ALTER TABLE,而且维护起来也很凌乱。BIT格式因为存储方式和易歧义问题，也尽量不要使用。
必要时可以设计一些如缓存表和汇总表这样的冗余表来提升性能。对于大表的热点数据冗余到一个单独的表里面，这固然会使得写变的麻烦且慢，却能大大提升读的效率。如果建立滚动的汇总表或者缓存表时可以使用建立影子表的方式。新建一个表，然后切换表名而不是直接修改原表，这样在出问题是可以更容易的进行回滚。
对于热点行的高并发操作。如计数器：update table set cnt = cnt+1;如果仅有一行，对于更新这一行的事务来数，因为互斥锁的存在，只能串行执行。此时可以通过设置多行数据提高并发性能：insert建立多行，每次随机一行更新，做总计数时使用sum函数聚合查询结果。如果需求周期性的生成新的计数器，那么可以使用 ON DUPLICATE KEY UPDATE。还可以周期性的把结果合并到0号槽来减少表的行数，避免表变的太大。
ALTER TABLE对大表来说很是问题，因为MySQL执行大部分ALTER TABLE语句会锁表，然后建立一个新表，从旧表查出所有数据再插入新表。这个操作很花费时间，甚至需要数小时甚至数天。如果碰到这种大表，一个比较好的方式是在一个不提供服务的备机上完成ALTER再切换为主库。
注意可变长字符串，其在临时表或者排序时可能导致悲观的按照最大长度分配内存，所以不能因为变长字符串是按实际长度分配磁盘就肆无忌惮的设大长度。

作者：水月_hu
链接：https://juejin.cn/post/7081993740855803941
来源：稀土掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。