数据库的数据类型优化

mysql支持非常多的数据类型，在设计表的时候需要精心的为每个列选择合适的数据类型以提高数据库的性能，这篇文章回顾了数据库中常用的几种数据类型，并总结了一些数据类型优化的技巧。
1.选择优化的数据类型
mysql支持非常多的数据类型,选择正确的数据类型对优化性能非常重要,下面几个原则适用于所有的数据类型。
1) 更小的类型通常更好
一般情况下应该使用能够正确存储数据的最小数据类型。小的数据类型意味着更少的磁盘 更少的cpu时间,能获得更好的性能。
2) 简单就好
简单数据类型的操作通常需要更少的cpu周期,比如说整型比字符型的操作代价更低,因为字符串和校验规则(排序规则)使得字符串比较操作更加复杂;两个更具体的例子:应该使用mysql内建的类型来存储日期和时间而不是字符串;应该使用整型来存储IP地址。
3) 尽量避免NULL
NULL是列的默认属性,所以很多情况下即使应用程序不需要NULL值,列中存储的也是NULL.问题在于可为null的列使得索引 索引统计和值的比较都变得麻烦起来,另外可为null的列会使用更多的存储空间,在mysql里也需要特殊处理。当可为null的列被索引时,每个索引记录需要一个额外的字节。总的来说,如果将数据库中所有可为null的列改成not null,性能的提升也是有限的,主要需要考虑的其实还是如果计划在列上建索引,那么就应该避免这个列是可为null的。
在为列选择数据类型的时候,显示要确定合适的大类型,比如数字 字符串 时间等,这一步通常是很直观的;第二步是选择具体类型,很多mysql都可以存储相同类型的数据,只是存储的范围 长度或精度不一样,需要磁盘存储空间不一样,相同大类型的不同子类型数据有时也会有些特殊的行为和属性。例如DATETIME 和TIMESAMP都可以用来存储相同的数据:比如时间和日期,精确到秒。但是TIMESAMP只需要DATETIME一半的存储空间,并且还能做到根据时区变化,但问题是TIMESAMP可以表示时间的范围比DATATIME小得多。下面来介绍各个具体的数据类型。
整数类型
有两种类型的数字:整数和实数。整数分为TINYINT SMALLINT MEDIUMINT INT BIGINT 分别使用8 16 24 32 64位存储空间;每种类型能表示的数字类型是 -2^(n-1)~2^(n-1)-1,其中n就是存储空间的位数。整数类型有可选的UNSIGNED,这大致可以使正数的表示范围扩大一倍,比如TINYINT可以表示的范围是-128-127,而UNSIGNED TINYINT可以表示的范围是0~255。有符号整型和无符号整型使用相同的存储空间,并具有相同的性能,所以可以视情况选择合适的类型。类型的定义可以决定mysql怎么在内存和磁盘中存储数据,然而整数计算一般使用64位的BIGINT的,即使是在32位的机器上也是如此。
mysql可以为整数类型指定宽度,例如INT(11),但对于大多数应用这是没有意义的,它不会限制值的合法范围,只是规定了一些mysql交互工具(mysql客户端)显示字符的个数,对于存储和计算来说INT(1)和INT(11)是一样的效果。

实数类型
实数是带有小数部分的数字,但是也可以用来存储整数,比如可以用DECIMAL存储比BIGINT还要大的整数。表示小数的一共有三种类型:FLOAT DOUBLE和DECIMAL,前两者称之为浮点类型,这三者都可以指定浮点列所需要的精度。FLOAT占用4字节空间,DOUBLE占用8字节的空间,相比FLOAT来说可以表示更高的精度和更大的范围;而在mysql 5.0及其以上版本,DECIMAL允许最多表示65个字符。FLOAT和DOUBLE是存储类型,内部浮点计算默认使用的都是DOUBLE类型,并且cpu支持原生的浮点计算，所以这种计算比较快,但是会出现浮点误差;而DECIMAL可以实现精确的小数计算,这是通过mysql自己的机制实现的,性能比较低。对于需要进行精确计算的小数,比如说财务数据,可以将原先的小数乘以一个单位化成BIGINT进行存储,可以避免浮点数计算不精确和DECIMAL计算代价高的问题。

字符串类型
mysql支持多种字符串类型,每种类型还有很多变种,并且从mysql4.1开始每个字符串列可以定义自己的字符集和排序规则,这些很大程度上会影响性能。
1) VARCHAR和CHAR类型
VARCHAR和CHAR是最常见的两种字符串类型,但是不同存储引擎在内存或磁盘对他们进行存储的格式是不一样的,所以得区分不同存储引擎来研究它们,下面以InnoDB和MYISAM来对其进行研究。
VARCHAR
VARCHAR用来存储可变长的字符串,是最常见的字符串类型。它比定长的字符串更节省空间,因为它只使用必要的存储空间。为了实现长度可变,VARCHAR需要额外使用一到二个字节来记录字符串的长度:如果字符串的长度少于255,则使用一个字节;如果长度大于255,则需要两个字节来进行记录。VARCHAR节省了存储空间,对性能也有提升。但因为是变长的,如果update操作增加了列的长度,就需要额外的工作,如果某个列的数据长度增加而页内没有足够的存储空间，这种情况下不会的存储引擎会有不同的处理策略,比如InnoDB会分裂页使得数据可以放到页内。下面的几种情况可以认为是适合使用VARCHAR的:
列的最大长度,比平均长度大很多;
列的更新很少,所以碎片也会很少；
使用了向utf-8这样复杂的字符集,每个字符都使用不同长度的字节进行存储。
注意在mysql 5.0或更高版本时mysql在存储和检索VARCHAR类型数据的时候会保留末尾的空格。
CHAR
CHAR是定长的:总是会根据定义的字符串长度分配足够大的空间。在存储的时候CHAR会去掉所有末尾的空格,CHAR会根据需要填充空格以便于比较。
CHAR适用于存储很短的字符串或长度都很接近的字符串。前者比如用CHAR来存储”Y”和”N”这两个字符,会比VARCHAR要少一个字节的空间(因为VARCHAR还需要一个额外的空间来存储长度),对于后者,CHAR比较适合用来存储密码的MD5值,因为他们长度都是一样的。对于经常需要更新的列,CHAR也会导致更少的碎片。

对变长和定长的字符串的存储规则是由不同的引擎实现的,Memory引擎对变长的字符串也会使用定长的空间进行处理,但是对于空格的截取和填充,都是一样的,因为都是在mysql服务层进行的。虽然VARCHAR(5)和VARCHAR(255)存储”hello”的磁盘大小是一致的,但是考虑到mysql有时候需要创建内存临时表进行操作时,后者需要更大的内存空间,所以对于VARCHAR而言也不会是越大越好,分配真正需要的空间才是最好的策略。

BLOB和TEXT
BLOB和TEXT是为了存储很大的数据而设计的字符串数据类型,分别使用二进制和字符形式进行存储。实际上他们是不用的数据类型,字符类型是TINYTEXT SMALLTEXT TEXT MEDIUMTEXT LONGTEXT,对应的二进制类型是 TINYBLOB SMALLBLOB BLOB MEDIUMBLOB LONG。
与其它类型不同mysql将每个BLOB和TEXT都当成一个独立的对象处理,存储引擎会对他们进行特殊处理。如果BLOB或TEXT太大,InnoDB会使用”外部的”存储空间进行存储,这时候会每个对象在行内存储一个1-4个字节的指针,然后再外部区域存储实际的值。BLOB与TEXT不同的地方仅仅在于BLOB存储的是二进制数据,没有字符集和排序规则,而TEXT有字符集和排序规则。
mysql对BLOB和TEXT的排序和对其它类型的排序是不同的,它只对每个列的前max_sort_length字节进行排序,MYSQL也不能对BLOB和TEXT列的全部长度建立索引,并用索引来消除排序。

使用枚举(ENUM)代替字符串类型
可以使用枚举来代替一些常用的字符串类型。mysql可以将一组不重复的字符串存储成一个预定义的集合,mysql在存储枚举时非常紧凑,会根据列表的数量压缩到一个或两个字节中去。mysql在内部会将每个枚举列的值保持为整数,并且在.frm文件中保存”数字-字符串”的查找表。下面建立一个仅包含枚举值的表,并向其中插入三个值。

create table enum_test( e ENUM('fish','apple','dog') not null );
insert into enum_test values('fish','dog','apple');

通过下面的语句可以看到列里面存储的是个整数的值:

select e+0 from enum_test

查询结果如下：

枚举类型还有个问题就是默认的排序会按列里存储的整数而不是其对应的字符串进行:

select e from enum_test order by e

解决这个问题的方式是按照需要的序列来定义枚举的序列,比如上面可以创建表的时候就定义成 e ENUM(‘apple’,’dog’,’fish’)这种形式,那么整数的顺序和字符串的顺序就相同了。另一种方式是需要排序的时候,显式的使用FIELD函数指定顺序,如下所示,但这里的问题是会导致不能使用索引消除排序操作。

select * from enum_test order by  FIELD(e,'apple','dog','fish');

枚举最大的不好是字符串集合是固定的,如果想要改变字符串集合,删除或增加字符串都需要进行ALTER TABLE操作。
枚举的显而易见的好处是可以明显的缩小表的大小,毕竟枚举列只需要存储一个整数值了。

日期和时间
mysql可以用很多类型来表示日期和时间,例如YEAR和DATE。mysql能存储的最小时间粒度为秒,但是可以用微秒粒度进行临时计算。大多数时间类型都没有什么替代品,所以唯一的问题是就是保存的时候需要怎么做。mysql提供了两种相似的日期类型:DATETIME和TIMESTAMP,对于很多场景两种都是适用的,但在一些情况下其中一个会工作的更好些。
DATETIME
这个能保存的时间范围是1001-9999,最小的精度是秒;它把日期和时间封装成YYYYMMDDHHMMSS的整数中,与时区无关,使用8个字节来存储。默认情况下mysql会以一种无歧义,可排序的格式显示DATETIME的值,例如”2018-01-01 12:00:00”,这是ANSI标准定义日期和时间的格式。
TIMESTAMP
这个类型保存了1970年1月1日午夜(格林威治标准时间)以来的秒数,使用4个字节进行存储,能够表示的最大时间是2038年。mysql提供了FROM_UNIXTIME()函数来将unix时间戳转换成日期,UNIX_TIMESTAMP()来将日期转换成时间戳。TIMESTAMP展示的值依赖于时区,而mysql服务器 客户端和操作系统都有时区设置,所以如何跨时区访问日期,那TIMESTAMP和DATETIME表现将不一样。
TIMESTAMP还有一些DATETIME没有的特殊属性,如果在插入的时候没有指定第一个TIMESTAMP的值,那么mysql将会自动设置这个列的值为当前时间。在更新的时候，如果没有在update语句中指定相应的值,那也会自动更新第一个TIMESTAMP的值。插入和更新时候TIMESTAMP的行为是可以配置的,还有一点比较特殊的是TIMESTAMP默认是not null,这和其它类型是不一样的。
如果一个表中有两个timestamp字段,那么就一定需要给第二个字段指定默认值,否则创建的时候就会报错。

create table timestamp_test ( utime TIMESTAMP, ctime TIMESTAMP default CURRENT_TIMESTAMP);

插入和更新timestamp时候都需要用标准展示格式,即’2014-12-08 12:08:02’这种形式,而不能传入一个unix_timestamp()函数中拿出来的整数。可以调用now()函数或表示当前时间的current_timestamp值。
除了特殊情况,通常尽量应该使用TIMESTAMP因为它的存储空间效率比较高,并且也不要用整型来存储时间戳,因为不好进行处理。mysql默认最小精度是秒,但如果确实要存储毫秒或微秒等更小的时间,可以使用BIGINT来进行存储或者使用double来存储秒之后的部分。

特殊的数据类型
某些类型的数据并不直接与内置数据类型一致,低于秒级精度的时间戳就是一个例子;另外一个例子是一个IPV4地址,人们经常使用VARCHAR(15)来保存IP地址,然而它实际上是一个32位无符号的整数,用小数点分隔的字符串形式只是为了阅读方便,所以应该使用无符号整数存储IP地址,Mysql提供INET_ATON(）和INET_NTOA函数在两种表现方法直接转换。

2.mysql数据设计中一些陷阱
上面讨论的都是通用性问题,但是一些问题是因为mysql的实现机制导致的,所以有必要特别拿出来讨论下。这里讨论的就是在mysql下设计数据库的一些问题。
1) 太多的列
mysql的存储引擎API工作时需要在服务层和存储引擎之间通过行缓冲格式拷贝数据,然后在服务层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。MYISAM的定长行结构正好与服务层的行结构吻合所以不需要转换,然后MYISAM的变长行结构和InnoDB的行结构则总是需要转换。而转换的代价依赖于列的数量,如果列的数量过多(几百甚至几千个字段),则性能会受到影响。
2) 太多的关联
所谓”实体-属性-值”(EAV)设置模式是一个常见的糟糕设计模式,尤其是在mysql下不能靠谱的工作。mysql限制了每个关联操作最多只能有61张表,但是很多使用EAV数据库需要许多自关联,所以很容易超过这个限制。实际上就算在关联表数少于61的情况下,解析和优化查询的代价也会成为mysql的性能问题。一般来说,如果希望查询执行的快且并发性好,单个查询最好在12个表内做关联。
3) 全能的枚举
避免枚举的滥用,主要考虑不要考虑使用枚举作为外键,整数才是最适合做外键的;另外,为枚举集合增加一个值属于alter table操作。在mysql 5.0之前,alter table是一种阻塞的操作;即使是在mysql 5.1之后,如果不是在集合的末尾增加一个值,也会是阻塞操作。
4) null
之前已经强调过不用null的好处(主要针对列可能被索引的情况),并且建议尽可能使用替代方案。比如说想要存储一个空值,也可以使用0 空字符等表示。但是如果有时候确实存在未知的值,还是可以使用null的。比如下面这种情况:

create table .....(
dt DATETIME NOTNULL DEFAULT '0000-00-00 00:00:00'
)

这种伪造的全0默认值可能会存在很多问题,还不如使用null来的好。