schema和数据类型优化之选择优化的数据类型

schema和数据类型优化之选择优化的数据类型

更小的通常更好

尽量使用可正确存储数据的最小的数据类型，更小的数据类型，通常更快。因为它们占更少的磁盘内存 CPU缓存，处理时需要CPU周期更少），但是要确保没有低估存储的值得范围。

简单就好

简单类型的操作通常需要更少CPU周期。例如整形比字符型操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整形比较更为复杂。使用MySQL内建类型存时间，整型存IP。

尽量避免null

最好指定列为not null

• null列使用更多的存储空间，MySQL里需要特殊处理
• null使索引、索引统计和值比较更复杂；可为null的列被索引时，每个索引记录需额外的字节。在MyISAM可能会导致固定大小的索引变成可变大小。
• 通常把可为NULL的列改为NOT NULL带来的性能提升比较小，一般没有说必须优化，除非确定这会导致问题。
• 例外：InnoDB使用单独位（bit）存储NULL,so对于稀疏数据（很多值为null）有很好的空间效率，不适用于MyISAM。

选择合适的数据类型

TIMESTAMP只使用DATETIME一半的存储空间，并且会根据时区变化。但是TIMESTAMP允许的时间范围更小

关于TIMESTAMP，DATETIME如何选择推荐阅读【https://segmentfault.com/a/1190000017393602】

整数类型【参考】

整数(whole number)

• TINYINT(8位）、SMALLINT（16）、MEDIUMINT（24）、 INT（32）、 BIGINT（64）存储值的范围：-2^(N-1)-2(N-1) N是存储空间的位数
• 有无符号使用相同的存储空间，相同的性能
• 选择不同的整数类型决定MySQL怎么在内存和磁盘中保存数据。但是整数计算一般使用64位BIGINT整数（一些聚合函数例外，他们使用DECIMAL或DOUBLE进行计算）
• 可为整型指定宽度，例如INT(11)，对于大多数应用无意义，不会限制值的合法范围，只是规定了交互工具显示字符的个数，对于存储和计算，INT（1）和INT（20）是相同的；

实数(real number)

• 可以·使用DECIMAL存储比BIGINT大的整数
• MySQL既支持不精确类型也支持精确类型
• FLOAT和DOUBLE支持使用标准的浮点运算进行近似计算。mysql使用duble作为内部浮点计算的类型
• decimal：存储精确的小数。MySQL5.0上DECIMAL类型支持精确计算。CPU不支持DECIMAL的直接计算，所以MySQL5.0以上服务器自身实现了高精度计算。相对而言，CPU直接支持原生浮点计算，所以浮点运算个更快。尽量只在对小数进行精确计算时才使用，如财务数据数据量大时，考虑使用bigint代替，将需要存储的货币单位据小数的位数乘以相应的倍数，可以避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。
• FLOAT和DECIMAL都可以指定精度。MySQL5.0以上将数字打包保存到一个二进制字符串中（每4个字节存9个数字）DECIMAL(18,9)一共使用9个字节，前4后4，小数点1.
• MySQL5.0以上DECIMAL允许最多65个数字。早期是254。
• 浮点列建议：只指定类型、不定精度，这些精度非标准，mysql会悄选类型、或存时对值取
• 浮点类型存储同样范围的值时，比decimal更少的空间，float4字节， double8字节

字符串类型

VARCHAR和CHAR

前提：innoDB和MyISAM引擎，最主要的字符串类型

磁盘存储：存储引擎存储的方式与在内存、磁盘上的不能不一样，所以MySQL服务器从引擎取值需转格式

VARCHAR：

• 存储可变字符串，比定长节省空间（仅使用必要的空间，越短使用更小的空间），但如果表使用row_format=fixed，行会定长存储。
• 需使用1或2个额外字节记录字符串长度；列max长度<=255字节，1字节表示，否2字节。采用latinl字符集，varchar（10）列需11个字节的存储空间，varchar（1000）1002字节，2字节存储长度信息。
• 节省存储空间，利于性能；但在update可能使行变得比原来更长、需做额外工作

合适的情况：

• 字符串列最大长度比平均长度大很多；
• 列的更新少（不担心碎片）；
• 使用UTF-8字符串，每个字符均使用不同的字节数存储

CHAR：

• 定长，据长度分配空间，删除末尾空格；长度不够、空格填充
• 存储空间上更有效率，char(1)来存储只有Y， N的值 1个字节 ，varchar2字节，还有一个记录长度

适合的情况：

• 适合存储很短的字符串；
• 或所有值接近同一个长度；
• 经常变更的数据，存储不易碎片

对应空格、存储：

char类型存储时末尾空格被删；数据如何存储取决于存储引擎，Memory引擎只支持定长的行（最大长度分配空间）

binary，varbinary：存储二进制字符串，字节码，长度不够、\0来凑（不是空格）检索时不会去掉填充值

二进制比较比字符简单很多，所以也快。

varchar（5）和varchar（100）存储‘hell’空间开销一样，长的列消耗更多内存，因为MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或者操作时，磁盘临时表也一样。

关于磁盘临时表和内存临时表的差异推荐阅读【https://blog.csdn.net/a925907195/article/details/52279035】

blob和text

分别用二进制和字符方式存储，分别属于两组不同的数据类型：字符类型：TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT，对应的二进制类型是TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGTEXT。两类仅有的不同：BLOB类型存储的是二进制，无排序规则或字符集，TEXT有字符串，排序规则；

MySQL会把每个BLOB和TEXT当做独立的对象处理，存储引擎存储时会做特殊处理，当值太大，InnoDB使用专门的外部存储区域进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储实际的值；

mysql对他们的列排序：只对每列前max_sort_length字节排序；且不能将列全部长度的字符串进行索引，也不能使用这些索引消除排序；如果只需要排序前面一小部分字符，则可以减小max_sort_length的配置，或者使用order by sustring(column,length)

磁盘临时表和文件排序

因为Memory引擎不支持BLOB和TEXT类型，所以，如果查询了BLOB或TEXT列并且需要使用隐式临时表，将不得不使用MyISAM磁盘临时表。即使只有几行数据也是如此。

最好的解决方案是尽量避免使用这些类型，如果实在无法避免就在使用到BLOB的字段的地方都是使用 sustring(column,length)将列值转换为字符串，这样就可以使用内存临时表了。但是要确保截取的子字符串足够短，不会使临时表的大小超过max_heap_table_size或tmp_table_size,超过以后会转换为磁盘临时表。

如果explain执行计划的extra包含using temporary：这个查询使用了隐式临时表

使用enum代替字符串类型

定义时指定取值范围，对1～255个成员的枚举需要1个字节存储；对于256～65535个成员，需要2个字节存储。最多可以有65535个成员，ENUM类型只能从成员中选择一个。

可把不重复的固定的字符串存储成一个预定义的集合，MySQL在存储枚举时会据列表值的数量压缩到1-2字节中，在内部会将每个值在列表中的位置保存为整数（从1开始，必须进行查找才能转换为字符串，开销、列表小，可控），且在表的.frm文件中保持“数字-字符串”映射关系的“查找表”。

将一个数字存储到一个 ENUM 中，数字被当作为一个索引值，并且存储的值是该索引值所对应的枚举成员： 在一个 ENUM字符串中存储数字是不明智的，因为它可能会打乱思维；ENUM 值依照列规格说明中的列表顺序进行排序。(ENUM 值依照它们的索引号排序。)举例来说，对于 ENUM("a", "b") "a" 排在 "b" 后，但是对于 ENUM("b", "a")，"b" 却排在 "a" 之前。空字符串排在非空字符串前，NULL 值排在其它所有的枚举值前。为了防止意想不到的结果，建议依照字母的顺序定义 ENUM列表。也可以通过使用GROUP BY CONCAT(col) 来确定该以字母顺序排序而不是以索引值。

排序时安装创建表时的顺序排序的（应该是）；枚举最不好的地方：字符串列表是固定的，添加删除字符串须使用alter table；在‘查找表’时采用整数主键避免基于字符串的值进行关联。

使用show data_length可以看到：转换成ENUM可以让表的大小缩小1/3.转换后的主键也只有原来的一半大小。

日期和时间

通常尽量使用timestamp，因为空间效率更高。

可以使用bigint类型存储微妙级别的时间戳，或double存秒之后的小数部分，或使用MariaDB代替MySQL；

位

bit

mysql5.0之前与tinyint同义词。

可以使用BIT列在一列中存储一个或多个true或false。

bit（1）单个位的字段，bit（2）2个位，最大长度64个位。

行为因存储引擎而异，MyISAM打包存储all的BIT列（17个单独的bit列只需要17个位存储，myisam3字节ok），其他引擎Memory和innoDB为每bit列使用足够存储的最小整数类型来存放，不节省存储空间。

mysql把bit当做字符串类型，检索bit（1）值、结果是包含二进制0/1的字符串，数字上下文的场景检索，将字符串转成数字，大部分应用，最好避免使用。

set

如果需要保存喝多true和false，可以考虑合并这些列到一个SET数据类型，它在MySQL内部是以一系列打包的位的集合来表示的。缺点是改变列的代价高，需要alter table；无法在SET列上通过索引查找。

替代SET的方式是使用一个整数包装一系列的位。例如可以把8个位包装到一个TINYINT中，并且按位使用。但是这样查询语句较难写且难理解。

创建表时，就指定SET类型的取值范围 ：属性名 SET(‘值1’,‘值2’,‘值3’…,‘值n’)，“值n”参数表示列表中的第n个值，这些值末尾的空格将会被系统直接删除，字段元素顺序 系统自动按照定义时的顺序显示 重复 只存一次。

其基本形式与ENUM类型一样。SET类型的值可以取列表中的一个元素或者多个元素的组合。取多个元素时，不同元素之间用逗号隔开。SET类型的值最多只能是有64个元素构成的组合，根据成员的不同，存储上也有所不同。

1～8成员的集合，占1个字节。
9～16成员的集合，占2个字节。
17～24成员的集合，占3个字节。
25～32成员的集合，占4个字节。
33～64成员的集合，占8个字节。

选择标识符identifier

标识列：自增长列

• 可不用手动插入值，系统提供默认序列值;
• 不要求和主键搭配 ;
• 要求是unique key；
• 一个表最多一个；
• 类型只能是数值；
• 可通过set auto_increment_increment=3;

选择标识列类型时

考虑存储类型、mysql对这种类型怎么执行计算和比较，确定后确保在all关联表中使用same类型，类型间要精确匹配；

技巧：

1. 整数类型：整数通常最好的选择，很快且可使用auto_increment

2. enum和set类型，存储固定信息

3. 字符串：避免，耗空间较数字慢，MyISAM表特别小心（默认对字符串使用压缩索引、查询慢），测试有6倍的性能下降。

4. 完全“随机”字符串MD5/SHA1/UUID函数生成的新值 会任意分布在很大的空间内，导致insert及部分的select变慢：插入值随机的写到索引的不同位置，insert变慢(页分裂 磁盘随机访问 聚簇索引碎片）；select变慢、逻辑上相邻的行分布在磁盘和内存不同的地方；随机值导致缓存对所有类型的查询语句效果都变差（使缓存赖以工作的访问局部性原理失效）

聚簇索引，实际存储的循序结构与数据存储的物理结构一致，通常来说物理顺序结构只有一种，一个表的聚簇索引也只能有一个，通常默认都是主键，设置了主键，系统默认就为你加上了聚簇索引；

非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系，与数据的存储物理结构没有关系；一个表对应的非聚簇索引可以有多条，根据不同列的约束可以建立不同要求的非聚簇索引；

存储uuid，移除-符号，或者用unhex转换uuid值为16字节的数字，且存储在binary（16）列中，检索时通过hex函数格式化为16进制格式;

UUID生成的值与加密散列函数(sha1)生成的值不同特征：uuid分布不均匀，有一定顺序，不如递增整数

当心自动生成的schema：

严重性能问题，很大的varchar、关联列不同的类型；

orm会存储任意类型的数据到任意类型的后端数据存储中，并没有设计使用更优的类型存储，有时为每个对象每个属性使用单独行，设置使用基于时间戳的版本控制，导致单个属性会有多个版本存在；权衡

特殊类型数据

有些数据类型并不直接与内置类型一致。比如低于秒级别的时间戳。还有IPv4地址，应该使用无符号整数存储，而不是使用VARCHAR(15)，因为他们本质是32位无符号整数，用小数点分割是为了容易阅读。MySQL提供了INET_ATON()（把 ip 转换为数字）和INET_NTOA()函数在这两种表示方法之间转换。