Mysql刨根问底之二(高效数据类型)

最新推荐文章于 2024-05-01 17:27:34 发布

置顶 Let's start coding

最新推荐文章于 2024-05-01 17:27:34 发布

阅读量233

点赞数

分类专栏： mysql刨根问底文章标签： mysql java 数据库

本文链接：https://blog.csdn.net/qq_24872115/article/details/129268687

版权

mysql刨根问底专栏收录该内容

6 篇文章 0 订阅

订阅专栏

使用原则

更小通常更好
简单为好
尽量避免存储NULL

整数类型

(8bit存储空间)
SMALLINT （16bit存储空间）
MEDIUMINT (24bit存储空间)
INT （32位存储空间）
BINGINT （64bit存储空间）

类型	占用空间	取值范围
tinyint	8bit (1 Byte)	-128~127
smallint	16bit (2 Byte)	32768~32767
mediumint	24 bit (3 Byte)	-223~ 223 - 1
int	32 bit (4 Byte)	-231~ 231- 1
bigint	64bit (8 Byte)	-263~ 263- 1

注意事项：

当插入的值，超出取值范围的时候，MySQL并不会报错，而是自动变成成在取值范围内最接近该值的边界值。例如字段为 tinyint ，有符号型时取值范围 -128至127 ，当你输入-222时，不会报错，会自动存入最接近-222的-128，当你输入222时，会自动存入127。

另外整数类型有可选的UNSIGNED属性，可以使取值范围大一倍，TINYINT UNSIGNED可以存储的值的范围是0～255。

MySQL可以为整数类型指定宽度，例如，INT（11），这对大多数应用毫无
意义：它不会限制值的合法范围，只是规定了MySQL的一些交互工具（例
如，MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，
INT（1）和INT（20）是相同的。

如果设置fillzreo(填充0)选项，也会根据设置的长度来填充。

实数类型

实数是带有小数部分的数字。然而，它们不仅适用于带小数的数字，也可
以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型。
浮点数通过比decimal使用更少的空间来储存相同范围的之。float列使
用4字节的存储空间。DOUBLE占用8字节，比FLOAT具有更高的精度和更大的值范围。
由于额外的空间需求和计算成本，应该尽量只在对小数进行精确计算时才
使用DECIMAL——例如，存储财务数据。但在一些大容量的场景，可以考虑
使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

实数类型主要有：

类型	占用空间	取值范围
float	4个字节	-3.402823466E+38～-1.175494351E-38
double	8个字节	-1.7976931348623157E+308～-2.2250738585072014E-308
decimal	M+2个字节	-1.7976931348623157E+308～-2.2250738585072014E-308

注意：提示：不论是定点还是浮点类型，如果用户指定的精度超出精度范围，则会四舍五入进行处理。 FLOAT 和 DOUBLE
在不指定精度时，默认会按照实际的精度（由计算机硬件和操作系统决定），DECIMAL 如果不指定精度，默认为（10，0）。

字符串类型

VARCHAR：

VARCHAR用于存储可变长度的字符串，是最常见的字符串数据类型。它比固定长度的类型更节省空间，因为它仅使用必要的空间（即，更少
的空间用于存储更短的值） VARCHAR需要额外使用1或2字节记录字符串的长度：如果列的最大长度
小于或等于255字节，则只使用1字节表示，否则使用2字节。 VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是可变
长度的，在更新时可能会增长，这会导致额外的性能损耗。下面这些情况使用VARCHAR是合适的：字符串列的最大长度远大于平均
长度；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
InnoDB更为复杂，它可以将过长的VARCHAR值存储为BLOB

CHAR类型

CHAR是固定长度的：MySQL总是为定义的字符串长度分配足够的空间。
当存储CHAR值时，MySQL删除所有尾随空格。如果需要进行比较，值会
用空格填充。
CHAR适合存储非常短的字符串，或者适用于所有值的长度都几乎相同
的情况。例如，对于用户密码的MD5值，CHAR是一个很好的选择，它们
的长度总是相同的。对于经常修改的数据，CHAR也比VARCHAR更好，因
为固定长度的行不容易出现碎片。
设计为只保存Y和N的值的CHAR（1）在单字节字符集 [1] 中只使
用1字节，但VARCHAR（1）需要2字节，因为还有一个记录长度的额外
字节。

BLOB和TEXT类型

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。
：字符类型是TINYTEXT、
SMALLTEXT、TEXT、MEDIUMTEXT和LONGTEXT；二进制类型是TINYBLOB、
SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB。BLOB是SMALLBLOB的同义词，
TEXT是SMALLTEXT的同义词。

日期和时间类型

DATETIME

这种类型可以保存大范围的数值，从1000年到9999年，精度为1微秒。
它以YYYYMMDDHHMMSS格式存储压缩成整数的日期和时间，且与时区无
关。这需要8字节的存储空间。
默认情况下，MySQL以可排序、无歧义的格式显示DATETIME值，例如，
2008-01-16 22：37：08。这是ANSI表示日期和时间的标准方式。

TIMESTAMP

顾名思义，TIMESTAMP类型存储自1970年1月1日格林尼治标准时间
（GMT）午夜以来经过的秒数——与UNIX时间戳相同。TIMESTAMP只使
用4字节的存储空间，所以它的范围比DATETIME小得多：只能表示从
1970年到2038年1月19日。MySQL提供FROM_UNIXTIME()函数来将UNIX时
间戳转换为日期，并提供了UNIX_TIMESTAMP()函数将日期转换为UNIX
时间戳。
时间戳显示的值依赖于时区。MySQL服务器、操作系统和客户端连接都
有时区设置。

使用无符号的32位INT，可以表达直到2106年的时间。INT查询效率更高

性能优化建议：

小心使用完全随机的字符串，比如MD5()、SHA1()、UUID()生成的字符串，这些函数生成的新值会任意分布在很大的空间内，这会减慢insert和某些select查询的速度
- 因为插入值会写到索引的随机位置，所以会使得insert查询变慢，这会导致页分裂、磁盘随机访问，以及对于聚簇储存引擎产生聚簇索引碎片。
- SELECT查询也会变慢，因为逻辑上相邻的行会广泛分布在磁盘和内
存中。
- 对于所有类型的查询，随机值都会导致缓存的性能低下，因为它们
会破坏引用的局部性，而这正是缓存的工作原理。
如果存储通用唯一标识符（UUID）值，则应该删除破折号，或者更好的做法是，使用UNHEX()函数将UUID值转换为16字节的数字，并将其存储在一个BINARY（16）列中。可以使用HEX()函数以十六进制格式检索值。
使用32位无符号整数存储IP地址
也许可以使用0、特殊值或空字符串作为代替NULL。