mysql性能优化之数据类型(持续更新)

    总结一下常用的数据类型创建的注意事项，后期会持续更新。
1.对于主键id使用bigint还是int？
2.创建表字段时，选择null还是not null？
3.IP地址使用varchar数据类型存放？

    mysql中最常见的需求是创建表时将主键id设置为自增，那问题来了，使用bigint好还是int好呢。如果单纯从磁盘占用空间大小角度来讲，使用int要优于bigint。因为bigint为8字节，int为4字节。对于无符号型的int类型来讲，int能表示的最大范围可以到2^32大概是43亿，一般的业务场景中，单表的数据量一般是在千万级别，所以对于大部分的业务场景来讲，43亿算是够用了。另外在来讲磁盘存储空间问题，8字节要比4字节占用空间更大，就好比是房间里面放一升水，本来用2L的水桶就可以，但是用4L的水桶去盛放势必占用的空间更大，毕竟房间大小是一定的，磁盘空间同理。

    先看官方文档关于null的介绍：

NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.
1

翻译：
    NULL列在行中需要额外的空间以记录其值是否为NULL。 对于MyISAM表，每个NULL列都多花一位，四舍五入到最接近的字节。可以简单理解为别用null。
    另外字段中如果存在null，可能会导致统计查询出现数据不准确、运算异常问题，具体说明可以参考：
mysql中null引发的错误案例说明以及处理方案
所以在创建表字段时，没有特殊要求还是老老实实使用not null。

    IP地址格式：IP地址通常用“点分十进制”表示成(a.b.c.d)的形式,其中,a,b,c,d都是0~255之间的十进制整数。也就是说使用varchar数据类型存放时，最长15位就可以满足。对应字节数应该需要占用16个字节（varchar需要使用1或2个额外字节记录字符串的长度:如果列的最大长度小于或等于255字节,则只使用1个字节表示,否则使用2个字节。一个varchar(10)的列需要11个字节的存储空间。varchar(1000)的列则需要1002个字节,因为需要2个字节存储长度信息）。
    在实际存储时可以使用int进行存储ip地址对应的数值(通过INET_ATON函数转化)，获取ip地址的时候使用INET_NTOA函数(ip地址数值类型转化为字符串)。使用int存储仅占用4字节，相对于字符串能节省3/4的磁盘空间。ip地址数据类型转化如下：

SET @ip='127.0.0.2';
SELECT @ip,INET_ATON(@ip) str_to_i,INET_NTOA(INET_ATON(@ip)) i_to_str;
1
2

执行结果：