Schema与数据类型优化

​ 在选择数据类型的过程中需要遵守以下原则

  • 更小的通常更好

一般情况下，应尽量使用可以正确存储数据的最小类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存

  • 简单就好

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

  • 尽量避免NULL

如果查询中包含可能为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL中也要被特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节。简言之，如果要建立索引应当避免包含可为NULL的列。

MySQL中各数据类型的特点

整数类型

​ 整数类型包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。分别用8、16、24、32、64位存储空间。有一个有意思的地方在规定整数类型宽度的时候比如INT(5)，实际上不会限制值的合法范围，它只会限制我们在使用MySQL交互工具时显示的数字宽度。对计算和存储来说INT(1)和INT(10)一样。

实数类型

​ DECIMAL、FLOAT、DOUBLE，后两者使用标准的浮点运算进行近似计算，DECIMAL用于存储精确的小数以便支持精确计算。三者都可以指定精度。对于DECIMAL列，可以指定小数点前后所允许的最大位数。但这会影响列的空间消耗。MySQL的做法是将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例如，DECIMAL（18,9）小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的用4个字节，小数点本身占一个字节。

​ 因为需要额外的空间和计算开销，所以尽量只在对小数进行精确计算时才使用DECIMAL——例如财务数据。但在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一，则可以把所有金额乘以一百万，然后将结果存储在BIGINT中，这样就可以避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

字符串类型

​ VARCHAR用于存储变长字符串，是最常见的字符串数据类型，它比定长类型更省空间，因为它仅使用必要的空间。但这不意味着可以给VARCHAR随意的分配一个很大的位宽，应该更长的列会消耗更多的内存，当产生临时表和文件排序的时候性能会进一步下降。当以下情况发生时使用VARCHAR是合适的：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了UTF-8这样的复杂字符集，每个字符都使用了不同的字节数进行存储。

​ CHAR是定长的，她会自动使用空格进行填充。CHAR适合存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据CHAR也比VARCHAR更好，因为定长的CHAR不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR（1）来存储只有Y和N的值，如果采用单字节字符集字需要一个字节，但是VARCHAR（1）却需要两个字节，因为还有一个记录长度的额外字节。

BLOB和TEXT类型

​ BLOB和TEXT都是为存储很大数据而设计的字符串数据类型，区别在一个存的是二进制一个是字符串。在排序时TEXT有字符集和排序规则，而BLOB没有。一般不要使用者两个类型，如果一定要用，则在所有用到的地方使用SUBSTRING（cloumn，length）将列值变为字符串以使用内存临时表。同时要注意取的子字符串要足够短，使临时表的大小不超过max_heap_table_size或tmp_table_size。

使用枚举（ENUM）代替字符串类型

​ 有时候可以使用枚举列代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时非常紧凑、会根据列表值的数量压缩到一个或两个字节中。MySQL在内部会将每个值在列表中的位置保存成整数，并且在表的.frm文件汇总保存“数字-字符串”映射关系的“查找表”。

1
2
3
4
CREATE TABLE enum_test(
	e ENUM('fish','apple','dog')NOT NULL
	);
INSERT INTO enum_test(e) VALUES('fish'),('dog'),('apple');

​ 实际在存储时这三条数据是‘1’，‘2’，‘3’而并非字符串，而且枚举中数字的值是根据创建该枚举列时的定义顺序来决定的。枚举的缺点在于如果要给它添加、删除新的枚举量时必须使用ALTER TABLE。因此对于未来会变动的字段，使用枚举不是一个好主意。

日期和时间类型

​ DATETIME，从1001年到9999年，精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，和时区无关，使用8个字节的存储空间。

​ TIMESTAMP保存了1970年1月1日午夜（格林尼治标准时间）以来的秒数，它和UNIX时间戳相同，TIMESTMP只是用4个字节的存储空间，因此它的范围比DATETIME小得多，只能表示1970年到2038年。MySQL提供了FROM_UNIXTIME()函数把UNIX时间戳转换为日期，UNIX_TIMESTAMP()函数把日期转换为UNIX时间戳。

​ 一般情况下应该使用TIMESTAMP，因为它比DATETIME空间效率更高。