MySQL数据类型优化

选择优化的数据类型

1、更小的通常更好

一般情况下，应该尽量使用可以正确存储数据的最小数据类型。够用就行，比如说只需要保存1-200的话，tinyint unsigned更好。只需要更小的数据类型，更快速，因为占用更小的磁盘，内存，CPU缓存，并且处理时需要的CPU周期也更少；只要保证你的数据最大值不超过你的数据类型范围即可

2、简单就好

简单的数据类型操作需要更少的CPU周期，整型比字符操作代价更低；例如整形比字符集操作代价更低，因为整形数在比大小的时候很方便，而字符集比大小规则很复杂。再例如时间类型用Date,datetime等，不用字符串；还有IP地址用整型等

3、尽量避免NULL(我是禁止NULL)

null对MySQL来说优化更难，NULL会使索引失效，NULL很特殊，会使索引统计，和值比较变得复杂；一般不允许保存NULL值。可以为null的列会使用更多的存储空间，在MySQL里边也要进行特殊处理。

正数类型

整型所占空间大小

类型	tinyint	smallint	mediumint	int	bigint
长度(单位 bit)	8	16	24	32	64

如果没有负值还可以加上 unsigned 属性，相同大小的存储空间unsigned能表示的范围更大，比如128 如果是tinyint是不能存储的，但是如果这个字段不可能有负数的值，那么我们就只需要tinyint unsigned就能存储，而不需要用smallint类型；

实数类型

 

实数类型的对比如下：

实数类型	空间大小（Byte）	取值范围	计算精度
FLOAT	4	负数：-3.4E+38～-1.17E-38；非负数：0、1.17E-38~3.4E+38	近似计算
DOUBLE	8	负数：-1.79E+308～-2.22E-308；非负数：0、2.22E-308~1.79E+308	近似计算
DECIMAL	与精度有关	同DOUBLE	精确计算

从上面可以看出，FLOAT和DOUBLE都有固定的空间大小，但同时由于是使用标准的浮点运算，所以只能近似计算。而DECIMAL则可以实现精确计算，与此同时占用的空间会相较更大，所耗费的计算开销也更多。

对于精度问题，建议只指定数据类型，不指定精度。精度越高需要的空间和计算成本成本越高，所以只有精度要求很高的时候才考虑使用decimal，比如财务数据。我们可以考虑使用整形数来代替decimal。

 字符串类型

最常用的字符串类型当属VARCHAR和CHAR。VARCHAR作为可变长字符串，会使用1或2个额外字节记录字符串的长度，当最大长度未超过255时，只需1个字节记录长度，超过255，则需2个字节。VARCHAR的适用场景：

1. 最大长度比平均长度大很多;
2. 列的更新少，避免碎片;
3. 使用复杂的字符集，如UTF-8，每个字符能使用不同的字节存储。

CHAR则为定长字符串，根据定义的字符串长度分配足够的空间，适用场景：

1. 长度短;
2. 长度相近，例如MD5;
3. 经常更新。

除了VARCHAR和CHAR，针对存储大字符串，可以使用BLOB和TEXT类型。BLOB和TEXT的区别在于，BLOB是以二进制方式存储，而TEXT是以字符方式存储。这也导致，BLOB类型的数据没有字符集的概念，无法按字符排序，而TEXT类型则有字符集的概念，可以按字符排序。两者的使用场景，也由存储格式决定了，当存储二进制数据时，例如图片，应使用BLOB，而存储文本时，例如文章，则应使用TEXT类型。

日期和时间类型

MySQL中所能存储的最小时间粒度为秒，常用的日期类型有DATETIME和TIMESTAMP。

类型	存储内容	空间大小（Byte）	时区概念
DATETIME	格式为YYYYMMDDHHMMSS的整数	8	无
TIMESTAMP	从1970年1月1日零点以来的秒数	4	有

TIMESTAMP显示的值将依赖于时区，意味在不同时区查询到的值将不一样。除了以上列出的不同，TIMESTAMP还具有一个特殊属性，在插入和更新时，如果没有指定第一个TIMESTAMP列的值，将会设置这个列的值为当前时间。

我们在开发过程中，应尽量使用TIMESTAMP，主要是因为其空间大小仅需DATETIME的一半，空间效率更高。

如果我们想存储的日期和时间精确到秒之后，怎么办?由于MySQL并未提供，所以我们可以使用BIGINT存储微妙级别的时间戳，或者使用DOUBLE存储秒之后的小数部分。