前言
本文仅个人的学习所记随笔,如有不对之处,还有各位大佬斧正!谢谢!
1. 字段类型
1. 整形
tinyint,smallint,mediumint,int,bigint 分别使用 8, 16, 24, 32, 64 位存储空间,一般情况下越小的列越好
int(11) 中的数字只是规定了交互工具显示字符的个数,对于存储和计算来说是没有意义的
2. 浮点数
float 和 double 为浮点类型,decimal 为高精度小数类型
CPU 原生支持浮点运算,但是不支持 decimal 类型的计算,因此 decimal 的计算比浮点类型需要更高的代价
float 、double 和 decimal 都可以指定列宽;例如 decimal (18, 9) 表示总共 18 位,取 9 位存储小数部分,剩下 9 位存储整数部分。
3. 字符串
主要有 char 和 varchar 两种类型,一种是定长的,一种是变长的。
varchar 这种变长类型能够节省空间,因为只需要存储必要的内容。但是在执行 UPDATE 时可能会使行变得比原来长,当超出一个页所能容纳的大小时,就要执行额外的操作。MyISAM 会将行拆成不同的片段存储,而 InnoDB 则需要页分裂来使行放进页内
varchar 会保留字符串末尾的空格,而 char 会删除
4. 时间和日期
MySQL 提供了两种相似的日期时间类型: datetime 和 timestamp
1. datetime
datetime 能够保存从 1001 年到 9999 年的日期和时间,精度为秒,使用 8 字节的存储空间;它与时区无关
💡 默认情况下,MySQL 以一种可排序的、无歧义的格式显示 datetime 值
例如 “ 2023-07-25 16:49:32 ”,这是 ANSI 标准定义的日期和时间表示方法
2. timestamp
和 UNIX 时间戳相同,保存从 1970 年 1 月 1 日午夜(格林威治时间)以来的秒数,使用 4 个字节,只能表示从 1970 年 到 2038 年
它和时区有关,也就是说一个时间戳在不同的时区所代表的具体时间是不同的
MySQL 提供了 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期,并提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。
默认情况下,如果插入时没有指定 timestamp列的值,会将这个值设置为当前时间。
应该尽量使用 timestamp,因为它比 datetime 空间效率更高
但是个人更常用 datetime
2. 选择优化的数据类型
-
更小的通常更好;更小的数据类型通常更快,因为占用更少的磁盘、内存和 CPU 缓存,并且处理需要的 CPU 周期也更少
-
尽量避免 null ;如果查询中包含可为 null 的列,对 mysql 来说更难优化,因为可为 null 的列使得索引、索引统计和值比较都更复杂。但如果计划在列上创建索引,就应该尽量避免设计成可为 null 的列
1. varchar 和 char
varchar 类型用于存储可变长度字符串,它比固定长度类型更节省空间
varchar 这种变长类型能够节省空间,因为只需要存储必要的内容。但是在执行 UPDATE 时可能会使行变得比原来长,当超出一个页所能容纳的大小时,就要执行额外的操作。MyISAM 会将行拆成不同的片段存储,而 InnoDB 则需要页分裂来使行放进页内
char 类型用于存储固定长度字符串
当存储 char 值时,MySQL会删除所有的末尾空格,char 值会根据需要采用空格进行填充,以方便比较和检索。但正因为其长度固定,所以会占据多余的空间,也是一种空间换时间的策略
char的设计提高查询速度,varchar的设计节省空间(在表设计合理的情况下)
💡varchar(5) 和 varchar(200)
使用 varchar(5)和 varchar(200) 存储"hello"的空间开销是一样的。那么使用更短的列有什么优势吗
事实证明有很大的优势。更长的列会消耗更多的内存,因为MySQL通常会分配固定大小的内存块来保存内部值
在没拿到存储引擎存储的数据之前,并不会知道我这一行拿出来的数据到底有多长,可能长度只有5,也可能长度是200,那怎么办呢?那就只能先把最大空间分配好了,避免放不下的问题发生,这样实际上对于真实数据较短的 varchar 确实会造成空间的浪费
所以最好的策略是只分配真正需要的空间
varchar 需要使用 1 或 2 个额外字节记录字符串的长度:
如果列的最大长度小于或等于 255 字节,则只使用 1 个字节表示,否则使用 2 个字节
假设采用 latinl 字符集:
varchar (5) 的列需要 6 个字节的存储空间
varchar (500) 的列则需要 502 个字节,因为需要 2 个字节存储长度信息
2. blob 和 text
blob 和 text 都是为存储很大的数据而设计的数据类型,分别采用二进制和字符方式存储
与其他类型不同,MySQL把每个 blob 和 text 值当做一个独立的对象去处理。当 blob 和 text 值太大时,InnoDB会使用专门的”外部“存储区域来进行存储,此时每个值在行内需要1~4个字节存储一个指针,然后在外部存储区域存储实际的值。
MySQL对 blob 和 text 列进行排序与其他类型是不同的:它只对每个列的最前max_sort_length个字节而不是整个字符串做排序。同样的,MySQL也不能将 blob 或 text 列全部长度的字符串进行索引