[转]mysql中表设计如何更好的选择数据类型

原文地址:https://www.modb.pro/db/100714

日常工作中我们会接触到不同业务，同样也会设计不同的表，但是你有真正考虑的mysql支持的那些数据类型吗？知道如何根据不同的需求选择最合适或者正确的类型吗。

存储字符串类型只知道选择varchar , 是否知道字符串还有char, text, blob 。

存储数字类型只知道选择 int，float，double，是否知道还有tinyint，smallint 等。

 

看完这篇文章相信你在以后的表设计中不会再纠结究竟该使用什么字段了。

 

01

—

选择数据类型的简单原则

 

1. 更小的通常更好

更小的数据类型占用更少的磁盘，内存和CPU缓存，而且处理时需要的CPU周期也更少，所以会更快（但是要确保没有低估需要存储的值的范围）

2. 简单就好

简单的数据类型的操作通常需要更少的CPU周期。

例如：

  • 整型比字符串操作代价更低，因为字符集和校对规则使 字符串比较 比 整型比较 更复杂。

  • 使用mysql自建类型而不是字符串来存储日期和时间

  • 用int存储IP地址比字符串要节约空间(ip用select INET_ATON('192.1.1.101') 和 select INET_NTOA(3221291365))

3. 尽量避免null

如果查询中包含null的列使得索引，索引统计和值比较都更为复杂。所以如果在列上建索引，就应该尽量避免设计成可为null的列。

 

02

—

常用数据类型

 

1. 整数类型

尽可能使用满足需求的最小数据类型。

可以使用的整数类型存储空间(位)由小到大分别是tinyint（8）、smallint（16）、mediumint（24）、int（32）、bigint（64）。

那如何选择：

例如：性别只有男和女可能还有未知，如果用数字表示就是未知(1),男(0),女(2)，那么类型可以选择tinyint，没必要选择其他的更大的存储空间的类型。

扩展1:  整数类型中还有个可选项UNSIGNED属性，表示不允许有负值。这可以使得正整数的上限提高一倍。例如本来tinyint 可以存储的范围是-128～127，而tinyint UNSIGNED 可以存储的范围变成0～255(有无UNSIGNED 存储空间都是一样的，性能也不变)。

扩展2: mysql中可以设置类型的宽度，例如int(11),实际上是没有意义的，只是用来交互工具显示字符的个数，实际上对于存储和计算来说，int(1)和int(10) 是相同的。


2. 实数类型

实数是带有小数部分的数字，而且不只是为了存储小数部分，也可以使用DECIMAL存储比BIGINT还大的整数。

Decimal 类型用于存储精确的小数。(CPU支持原生浮点计算，不支持对decimal的直接计算，所以浮点运算更快)。

浮点类型在存储同样范围的值时，通常比decimal使用更少的空间，float用4字节存储，double用8个字节（mysql使用double作为内部浮点计算）。

只有需要对小数进行精确计算时才使用decimal(例如财务数据)，但是当数据量较大的时候，可以考虑使用bigint代替decimal，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

假如要存储财务数据精确到万分之一，可以把所有金额乘以一百万，然后将结果存储在bigint里，可以避免浮点存储计算不精确和decimal精确计算代价高的问题。

 

3. 字符串类型

字符串类型包含varchar ，char，text，blob。

varchar：存储的是可变长度字符串，比定长类型更节省空间(越短字符串使用越少的空间)。

  • 使用最小的符合需求的长度。

  • varchar(n) n<=255的时候使用一个字节来保存长度，当n>255的时候需要两个字节保存长度。

  • varchar(5)和varchar(200)保存同样的内容，硬盘的存储空间是一样的，不同的是内存的消耗。mysql通常会分配固定大小的内存块来保存内部值，尤其是使用内存临时表进行排序或者操作的时候会很糟糕。所以最好是只分配真正需要的空间。

        应用场景：

  •         存储波动长度较大的数据，如：文章，有的短有的长。

  •         字符串很少更新的场景，每次更新都会重新算并使用额外存储空间保存长度。

  •         适合保存多字节字符，如汉字，特殊字符等。

     

char：定长，最大长度255。

  • 存储char值时，mysql会删除所有数据的末尾空格，

  • 比较适合非常短的数据，例如char(1)来存储Y和N的值。

    应用场景：

  •          存储长度波动不大的数据，如：md5摘要（加密后的密码）

  •         存储短字符串，经常更新的字符串。

 

BLOB和TEXT类型

BLOB和text是存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。不建议使用，数据量较大的时候会严重影响效率。

 

4. 日期和时间

dateTime：8个字节的存储空间，可以保存大范围的值，保存1000-01-01到9999-12-31之间的日期，精度为秒，将日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。不要使用字符串存储日期类型，占用空间大，损失日期类型函数的便捷性。

timestamp：4个字节的存储空间，保存了从1970年1月1日午夜(格林尼治标准时间)以来的秒数，和unix时间戳相同。timestamp的范围只表示从1970年到2038年。timestamp的显示的值依赖于时区。

date：3个字节的存储空间，date类型还可以利用日期时间函数进行日期之间的计算。用于保存1000-01-01到9999-12-31之间的日期。

如果想要存储更小粒度的日期和时间值: 可以使用bigint 类型存储微秒级别的时间戳，或者使用double存储秒之后的小数部分。

 

5. 枚举类型

使用枚举替代常用字符串类型，mysql存储枚举类型会非常紧凑，会根据列表值的数据压缩到一个或者两个字节中，mysql会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存‘数字’，‘字符串’的映射关系的查找表。

举例说明：

在数据库存储枚举性别：男，女，还有性别未知，查询的时候可以直接看到男/女，不需要在业务进行转换。（实际保存的是整数1，2，3）

mysql> create table enum_test( sex enum('男','女','未知') not null ) charset=utf8;
Query OK, 0 rows affected (0.03 sec)


mysql> INSERT INTO enum_test(sex) values('男'),('女'),('未知'),('男');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0


mysql> select * from enum_test;
+--------+
| sex  |
+--------+
| 男   |
| 女   |
| 未知  |
| 男   |
+--------+
4 rows in set (0.00 sec)


mysql> select sex+0 from enum_test;
+-------+
| sex+0 |
+-------+
|     1 |
|     2 |
|     3 |
|     1 |
+-------+
4 rows in set (0.00 sec)

 

如果感觉类型的选择没那么重要，是因为遇到的数据体量比较小，但是如果表的数据量很大的话，就会凸显出数据类型的重要性，合适的类型节省的空间和内存就很重要了。

 

 

一句话：最好是只分配真正需要的空间。

 

参考书籍《高性能MySQL》

posted @ 2022-12-06 16:44  dirgo  阅读(111)  评论(0)  编辑  收藏  举报