数据类型优化

最新推荐文章于 2024-06-03 01:21:19 发布

Java Gosling

最新推荐文章于 2024-06-03 01:21:19 发布

阅读量194

点赞数

分类专栏：高性能MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/MAKEJAVAMAN/article/details/119654696

版权

高性能MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

文章目录

整数类型
实数类型
字符串类型
日期和时间类型
- DATETIME
- TIMESTAMP
位数据类型
- BIT
- SET
选择标识符技巧

整数类型

类型	位数
TINYINT	8
SMALLINT	16
MEDIUMINT	24
INT	32
BIGINT	64

超出范围是插不进去的，会报Out of range value for column 'xxx' at row 1错误。
在这里插入图片描述

业务有些时候可以使用unsigned摒弃负值，使存储正值的大小提升一倍，其它性能都是一样的，仅仅存储范围发生改变。

MySQL为整数指定宽度是没有意义的，比如INT(10)，只改变了显示字符的个数，对于范围是没有影响的。

实数类型

实数是带有小数部分的数字，可以将它分为浮点型(FLOAT单精度和DOUBLE双精度)和DECIMAL类型。

FLOAT使用4个字节存储，DOUBLE占用8个字节，和整数类型一样，能选择的只是存储类型，MySQL使用DOUBLE作为内部浮点计算的类型。

FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算，但是会有损失，如果涉及到小数运算，尽量不要用浮点型。

DECIMAL类型用于存储精确的小数。MySQL服务器自身实现了DECIMAL的高精度计算，因为CPU不支持对DECIMAL的直接计算，所以计算速度会略慢于浮点型，在MySQL 5.0和更高版本，DECIMAL类型支持精确计算，对于一些银行金融业务，DECIMAL无疑是最好的选择。但在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

不推荐定义精度，因为MySQL会悄悄选择不同的数据类型，或者在存储时对值进行取舍。

字符串类型

VARCHAR

VARCHAR类型用于存储可变长字符串，相对节省空间。使用ROW_FORMAT=FIXED可以设置定长。

VARCHAR使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。比如VARCHAR(10)需要11个字节，而VARCHAR(300)需要302个字节。

如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。

以下场景推荐使用VARCHAR：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

CHAR

CHAR类型用于存储定长字符串。当存储CHAR值时，MySQL会删除所有的末尾空格，也就是’hello ‘会存储为’hello’。

以下场景推荐使用CHAR：较短的字符串，例如MD5密码值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。还有非常短的，比如使用CHAR(1)存储单字节字符集Y和N，相对于VARCHAR不需要额外的长度来记录字符串长度。

BLOB和TEXT

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

当存储的值过大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1～4个字节存储一个指针，然后在外部存储区域存储实际的值。

BLOB和TEXT之间仅有的不同是BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规。

Memory引擎不支持BLOB和TEXT类型，如果查询使用了BLOB或TEXT列并且需要使用隐式临时表，将不得不使用MyISAM磁盘临时表。这会造成额外的开销，尽量避免。无法避免的话可以在所有用到BLOB字段的地方都使用SUBSTRING（column，length）将列值转换为字符串，但要注意截取的长度，如果超过了临时表的max_heap_table_size或者tmp_table_size，还是会使用磁盘临时表的。

日期和时间类型

DATETIME

DATETIME的存储范围为1001年-9999年，精度为秒，它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，使用8个字节的存储空间。通常MySQL会使用ANSI标准定义的日期和时间表示方法来进行显示，例如’‘2021-05-21 13:14:00’’。

DATETIME与时区无关。

TIMESTAMP

TIMETAMP类型保存了从1970年1月1日午夜（格林尼治标准时间）以来的秒数。存储范围为1970年-2038年，TIMESTAMP只使用4个字节的存储空间，因此它的范围比DATETIME小得多。

使用FROM_UNIXTIME()可以把Unix时间戳转换为日期，同时使用UNIX_TIMESTAMP()可以把日期转换为Unix时间戳。

TIMESTAMP与时区有关，以格林尼治标准时间为准，针对不同时区会进行转换。

这两种类型都不支持微妙级别，如果有需求，可以使用DOUBLE存储秒之后的小数部分，或者使用BIGINT类型存储微秒级别的时间截，也可以使用MariaDB替代MySQL。

位数据类型

BIT

BIT列的最大长度是64个位，根据定义长度可以存储一定位的数据，BIT（1）定义一个包含单个位的字段，BIT（2）存储2个位等等。

BIT的行为因存储引擎而异，对于17个单独的BIT列，MyISAM只需要3个字节就能存储这17个BIT列，而Memory和InnoDB是以为每个BIT列使用一个足够存储的最小整数类型来存放。

注意：MySQL把BIT当作字符串类型，而不是数字类型。当存储一个"00110000"的字符串时，得到的结果会是ASCII码的0。

BIT类型慎用。

SET

SET类型在MySQL内部是以一系列打包的位的集合来表示的，节省了空间，但是改变列的定义的代价较高，而且无法在SET列上通过索引查找。

选择标识符技巧

整数通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREMEN来进行自增。
ENUM和SET列适合存储固定信息，例如有序的状态、产品类型、人的性别，一般情况慎重选择。
字符串类型不适合作为标识列，无论是从空间和速度来说都不是最佳选择。MyISAM默认对字符串使用压缩索引会导致查询慢得多。
对于像MD5()、SHA1()或者UUID()产生的随机字符串。这些函数生成的新值会任意分布在很大的空间内，这会导致INSERT以及一些SELECT语句变得很慢。UUID可以移除“-”符号或者用UNHEX()函数转换UUID值为16字节的数字，并且存储在一个BINARY（16）列中，检索时使用HEX()函数来格式化为十六进制格式。
IPv4地址实际上是32位无符号整数，不是字符串，最佳的存储是使用MySQL提供的INET_ATON()和INET_NTOA()函数通过整数存储来进行转换。