https://futures.yunsonbai.top/?hmsr=yunsonbai.top
贵金属行情

MySQL复习笔记-数据类型及使用优化

发表于 | 分类于
本文记录了整数类型、实数类型、varchar、char、BLOB、TEXT、枚举类型、DATETIME、TIMESTAMP等，并记录了一些包括存储效率、查询效率、使用注意事项等等一些问题和实例

转载请标注出处: https://yunsonbai.top, 并发送转载通知到yunsonbai@163.com

https://futures.yunsonbai.top/?hmsr=yunsonbai.top

引言

原文链接：https://yunsonbai.top/2020/09/24/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
公众号:技术and生活