08月17, 2016

MYSQL优化系列(二)——字段类型的选择

MYSQL字段类型的选择

在实际建表的过程中,经常会出现一种情况,MYSQL的其中一个字段可以用多种类型同时表示,最常见的就是和日期相关的列。 比如订单创建时间(2016-08-20 13:00:00),这个字段我们就可以用四中类型表示,Datatime,char(19),varchar(19),Int(放时间戳)。

字段类型选择原则

列的数据类型第一会影响单位数据所占存储空间的大小,另一方面也会影响查询时的性能。因此当一个列可以选择多种类型的时候,应当优先考虑数字类型,其次是日期或二进制类型,最后是字符类型。在字符类型相同的情况下(如datatime,timestamp || int,tinyint)应该优先选择占用空间小的数据类型。

那么在上述的例子中,我们的选择优先级就是int>datatime>char>varchar。

下面贴出不同的列类型所占的存储空间,方便大家查阅。

列类型 存储空间
TINYINT 1字节
SMALLINT 2字节
MEDIUMINT 3字节
INT 4字节
BINGINT 8字节
DATE 3字节
DATETIME 8字节
TIMESTAMP 4字节(最高存储到2037年)
CHAR(M) M字节, 1 <= M <= 255
VARCHAR(M) L+1字节,L <= M和1 <=M <=255

那么为什么我们要优先选择INT类型呢,原因主要有以下两个方面

  1. 在我们做查询排序的时候,字符处理要比数字处理慢,因为字符处理需要先查阅参考数据字典,而数字处理不用。
  2. 在数据库中,数据处理以页为单位,列的长度小,一页内存储的数据越多,所以我们查询的时候就只翻阅更少的页,提升了IO性能。

常见问题

char和varchar如何选择

  1. 如果列中要存储的数据长度差不多是一致的(如电话,md5后的密码等字段),应该用char;其他非定长的可以用varchar。
  2. 如果列中的最大数据长度小于50Byte,则一般也考虑用char。(注:utf8一个字符3个字节)

decimal与float如何选择

  1. decimal用于存储精确数据,而float只能用于存储非精确数据,精确数据用decimal类型。
  2. float存储空间比decimal小,非精确可以使用float。
  3. 常见的商品单价用int存储更好,单位是分

时间类型如何存储

  1. 使用int存储时间字段的优缺点:
    • 优点:字段长度比datatime小
    • 缺点:使用不方便,要用函数转换;最大只能存储到2038-01-19 11:14:07(原因是int占4个字节,1字节=8bit,所以int最大2的32次方2147483648,大家可以把这个数转成日期格式试一下)

本文链接:http://www.qiana.info/post/mysql_optimize_2.html

-- EOF --

Comments