贵金属行情
微信公众号

MySQL复习笔记-数据类型

引言

原文链接:http://yunsonbai.top/2020/09/07/mysql-datatype/
最近在复习MySQL的一些知识,结合自己的实践,记录了一些知识点,本文记录了整数类型、实数类型、varchar、char、BLOB、TEXT、枚举类型、DATETIME、TIMESTAMP等,并记录了一些包括存储效率、查询效率、使用注意事项等等一些问题和实例,欢迎大家阅读和指正。

整数类型

  • TINYINT: 8位
  • SMALLINT: 16位
  • MEDIUMINT: 24位
  • INT: 32位
  • BIGINT: 64位

整数类型有一个UNSIGNED属性,标识无符或者有符。比如对于TINYINT,存储范围上,无符0-255,有符则是-128-127。无符号和有符号类型使用相同的存储空间,具有相同的性能。

问题: INT(11)表示的是11位存储么?
这个对于大多数应用是没有用的,它只能限制客户端用来显示字符的个数,实际的存储还是32位。

实数类型

MySQL支持精确类型和不精确类型。

FLOAT和DOUBLE支持使用标准的浮点运算进行近似计算。
DECIMAL用于存储精确的小数,允许小说点两边最多65个数字。

CPU不支持对DECIMAL的直接计算但是迟迟浮点计算,MySQL则自己实现了DECIMAL的计算,换句话说,浮点计算要比精确计算快。

另外浮点类型在存储同样范围的值时,要比DECIMAL使用更少的空间。因为需要额外的空间和计算开销,应尽量避免使用精确的DECIMAL,除非不可避免。可以考虑使用int来代替DECIMAL,只需要扩大响应的倍数即可。

字符串类型

varchar 和 char

varchar

用于存储可变长度字符串,比长类型更节省空间,仅使用必要的空间。varchar需要使用1或者2个字节记录字符串的长度。
由于varchar是变长的,所以在update的时,存储引擎会有不同的处理方式。InnoDB需要分裂页来使行可以存放进页内。MyISAM将行拆成不同的片段存放。

当字符串长度不定:
1、最大长度比平均长度大的多;
2、列更新少,碎片不是问题
3、存储utf-8等复杂字符集时
推荐使用varchar。

char

存储定长字符串,这里的定长不是说存储的内容一定长度,而是占用的存储空间定长。
需要注意的是,char存储时会删除末尾的空格,而varchar则不会,可自行使用mysql(5.5+)测试。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE `yuntest` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`str1` char(10) NOT NULL,
`str2` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
insert into yuntest(str1, str2) VALUES (" test", " test"), ("test2 ", "test2 ");

select id, CONCAT("'",str1, "'"), CONCAT("'", str2, "'") from yuntest;
+----+-----------------------+------------------------+
| id | CONCAT("'",str1, "'") | CONCAT("'", str2, "'") |
+----+-----------------------+------------------------+
| 1 | ' test' | ' test' |
| 2 | 'test2' | 'test2 ' |
+----+-----------------------+------------------------+

对于很短的字符串,或者所有的值长度近似一样,使用char存储比较理想。
如果数据经常变动,由于char类型使用了固定长度的存储空间,不容易产生碎片,所以要比varchar性能更好。

想想一下下边的例子,那种存储更有效率?
char(1) 和varchar(1)。 显然char(1)存储效率要高,因为varchar(1)实际存储是两个字节,多余的那个用来记录长度。

varchar和char的延伸问题

1、当我们知道字符串最长不会超过10时
使用varchar(10) 和 varchar(255)存储,哪个更好呢?
存储开销上,没有区别。但对于查询时,则更短的更好,MySQL实际会为varchar分配固定且最大的内存来保证存储下内容,这样一来,varchar(255)将会使用更大的内存来,如果在遇到操作很多临时表,更短的varchar则能表现出更好的性能。

另外根据实际情况,在设计varchar长度时,在尽可能短的情况下可以适当的考虑一下扩展。如果有特殊值真的超过了设计的长度,如果适当的考虑了扩展,则业务不会受影响,反之可能需要改动的就比较大。

2、存在varchar(2000)
表有2000万行,占用GB级存储空间,其中一列是varchar(2000),且用的utf-8, 每个字符需要用3个字节存储空间,最坏的情况下用6000字节空间,如果在order by中用到了这个列,且扫描整表,则可能需要超过120GB的临时表,这是灾难性的。怎么判断是不是使用了临时表呢?
使用explain显示执行计划,其中的Extra列,如果包含 “Using temporary”,说明用到了临时表。

BLOB 和 TEXT

用于存储较大的数据,BLOB存储二进制数据,TEXT存储字符串。
分为 TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB。
TINYTEXT(256 bytes)、SMALLTEXT、TEXT(64kb)、MEDIUMTEXT(~16MB)、LONGTEXT(~4GB)

在InnoDB中,BLOB和TEXT一般会存在行外,即在行内存储目标数据的存储指针,真实数据在外部存储。

需要注意:
MySQL只对每个列的最前面的max_sort_length字节进行排序,不是整个都做排序。

枚举类型ENUM

当要存储的字符串选值固定时,建议存储成整型(TINYINT),然后由应用维护对应关系,要知道MySQL在整型的存储和查询效率上是高于字符串类型的。

也可以选用枚举来存储,MySQL中的枚举类型其实存的也是整数,由MySQL来维护“数字-字符串”映射关系,存在.frm文件中。

依然推荐使用应用自己维护这个映射关系,因为这样更加的灵活,方便扩展,而且MySQL不用维护映射关系使得查询效率更高。

问题,如果存成了整数,那么想按照字符串排序怎么办?
1、如果是固定的几个字符串,一般排序的需求不会太多
2、即便真的要排序,开发者提前在外部排好序,然后在对应整型数字不就解决了么?

DATETIME和TIMESTAMP类型

  • DATETIME存储日期,精度为秒,从1001到9999
  • TIMESTAMP存储从1970年1月1日(格林时间)午夜以来的秒数。和UNIX时间戳相同。注意其显示依赖时区。

TIMESTAMP要比DATETIME空间效率更高。

一个思考题

当存储Ipv4的时候,使用varchar还是int?
如果考虑存储效率和查询效率,建议使用int,因为实际Ipv4就是个32位的无符号整型值,只是为了方便人们观看,用.分割。 那该怎么存和取呢?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 存:
def ip_to_int(ip):
"""
:type ip: str
:rtype: int
"""
int_ = 0
for i in ip.split('.'):
int_ = int_ << 8 | int(i)

return int_

# 取:
def int_to_ip(int_):
"""
:type int_: int
:rtype: str
"""
ip = []
for _ in range(4):
ip.append(str(int_ & 255))
int_ >>= 8

return '.'.join(ip[::-1])

如果不考虑存储、查询,为了查看数据方便,其实存成varchar,也不是不行。

yunsonbai wechat
微信公众号