MySQL之数据类型优化

数值类型

整数类型：TINYINT 、SMALLINT、 MEDIUMINT 、INT、BIGINT 。分别使用8、16、24、32、64位存储空间。当然我们可以使用DECIMAL类型来存储比BIGINT更大的值。我们看到的INT(2)和INT(10) 他们能存储的值其实是一样的。只是显示宽度不一样而已。如果要想显示的不一样，需要再加上ZEROFILL来声明。

实数类型：FLOAT、DOUBLE。FLOAT占用四个字节，DOUBLE占用8个字节。所以DOUBLE能存储更高精度的数据。也可以通过DECIMAL来得到比DOUBLE更高的精度类型。

在建表的时候对于一些类型(type)、状态(status)时，我们可以采用TINYINT而不采用INT,这样会使得MYSQL数据库为这个列分配的空间更小，在查询、排序时消耗更小的内存。

字符串类型

CHAR和VARCHAR是最主要的两种字符串类型。CHAR是定长的而VARCHAR是变长的。例如CHAR(20) 和VARCHAR(20) 当你只存储一个字节时，CHAR也占用20个字节的空间，而VARCHAR只占用两个或者字节的空间。因为VARCHAR需要一个或者两个字节来存储字节的长度。

当我们能够确定业务中字符串的长度时，使用CHAR类型，若不能确定则使用VARCHAR类型。在使用VARCHAR类型时，最好尽可能的限制字符数量，例如姓名，我们就可以使用VARCHAR(30)，甚至更小，而不是使用VARCHAR(255)。

BLOB和TEXT

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

时间类型

DATETIME 能存储更大的值，能存储1001到9999这个区间的值，且他的值不会随着时区的变化而变化。

TIMESTAMP 相对于DATETIME来说能存储的值更小一些，他能存储的值为1970到2038，且是一个时间戳，为一个整数。他的值会自动随着时区的变化而变化。

数据表设计的陷阱

太多的列：

MySQL存储引擎API工作时，在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务层将缓冲内容解码成各个列，从缓冲内容中将编码过的列转换成行数据结构是非常耗时的。

太多的关联：

MySQL限制一个操作只能关联61张表，太多的关联会产生太多的虚拟表，占用更多的内存。如果涉及到食物操作的话，还会锁住更多的数据。

范式的优点和缺点

优点：

1、更新操作更快，因为不存在冗余的数据，只需更小较少的列，所以更快。

2、查询更快，只需要较少的（或者不使用）group by 或者distinct语句就能得到唯一的值。

3、较小的数据表，可以更好的放在内存中，因此操作更快。

缺点：

复杂一点的查询，或者需要的数据更为详细的时候需要关联查询。

总结：

在数据表设计的过程中，不存在完全的范式或者反范式，都会为了性能做一个折中的处理，在一定程度上允许一定的数据冗余。

 

Mysql支持很多的数据类型，但是不管选择哪种数据类型都可以参考以下几种原则：

1、更小的通常更好。

在没有低估需要存储的数据值范围的前提下，应尽量选择最小的数据类型。更小的数据类型通常更快，因为他们占用更小的磁盘、内存和CPU缓存。

2、简单就好。

简单数据类型的操作通常需要更小的CPU周期。例如：应该使用Mysql内建的数据类型来存储时间和日期，而不是使用字符串。应该使用整型来存储IP地址，而不是字符串。

3、尽量避免NULL。

虽然在优化SQL时将NULL改成NOT NULL提升的性能很小，但是如果考虑到需要建立索引，则应尽量避免NULL。