Mysql数据类型浅析和优化

MySql数据类型概览

整数类型

TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT 分别使用8,16,24,32,64位存储空间，他们可以存储值的范围从

-2（n-1）到2（n-1）次方，n是存储空间的位数。整数类型选择UNSIGNED属性，标识不允许负值，上限大致可以提高一倍。

例如:TINYINT UNSIGNED 可以存储的范围是0到255，而TINYINT的存储范围是-128~127。MYSQL可以为整数类型指定宽度，

对于存储和计算来讲，INT(1)和INT(20)没有区别。

实数类型

实数类型分decimal(m,n), double(m,n), float(m,n)三种类型。对于DECIMAL列，可以指定小数点前后所允许的最大位数。

Mysql 5.0和更高版本将数字打包在一个二进制字符串中。下面是存储长度和所占用字节大小参照表。

占用字节大小举例： DECIMAL(18,9) 小数点左边9个数字，右边9个数字，9个数字需要4个字节，小数点需要一个字节，

总共需要9个字节。(Mysql 5.0和更高版本将数字打包在一个二进制字符串中)。Float 固定4个字节，double 固定8个字节，

存储同样范围值得时候，浮点类型比decimal占用更小的空间。但对于精度要求高的计算要用decimal。

tips:一般涉及财务问题等精确数学计算，需要用decimal来处理。因为float和double存在精度问题。其他酌情可用整数类型

      代替实数类型。

日期类型

DATETIME:占用8个字节， TIMESTAMP:占用4个字节。

两者区别:

1.datetime表示'YYYY-MM-DD HH:mm:ss'形式的日期加时间，timestamp与datetime显示形式一样。

2.datetime可表示的时间范围为'1000-01-01'到'9999-12-31'，timestamp由于受32位int型的限制，能

   表示'1970-01-01 00:00:01'到'2038-01-19 03:14:07'的UTC时间。

3.mysql在存储timestamp类型时会将时间转为UTC时间，然后读取的时候再恢复成当前时区。 假如你存储了

   一个timestamp类型的值之后，修改了mysql的时区，当你再读取这个值时就会得到一个错误的时间。而这

   种情况在datetime中不会发生。

4.timestamp类型提供了自动更新的功能，你只需要将它的默认值设置为CURRENT_TIMESTAMP。

   datetime和timestamp都保留到秒，而忽略毫秒。

tips:日期一般用TIMESTAMP进行保存，因为他占用的空间更少。

VARCHAR和CHAR类型

VARCHAR类型拥有存储可变长字符串,它比定长类型更节省空间，因为它仅使用必要的空间。疑问，varchar（5）和

varchar(200)空间开销是一样的，那么使用更短的列有什么优势吗？事实证明有很大的优势，更长的列会消耗更多的内存，

因为MYSQL通常会分配固定大小的内存块来保存内部值。尤其使用内存临时表进行排序或操作时会特别糟糕，所以最好的

策略是只分配需要的空间。

varchar需要1或2个额外字节记录字符串长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。

例如:Varchar(10) 10代表的是10个字符，例如用utf8编码格式，每个字符占用3个字节，总共占用空间31个字节。

tips:什么样数据适合用varchar?

      1.字符串列的最大长度比平均长度大很多。

      2.列的更新很少，碎片不是问题。在update操作时候，

       可以使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，

       在这种情况下不同的存储引擎的处理方式不一样，INNODB需要分裂来使行可以放进页内，进而产生了碎片。

      什么样的数据适合存储CHAR？

      1.定长值例如:密码的md5值固定28个字符，UUID 固定36个字符（咱们现在统一用的varchar）

      2.对于经常变更的数据，char也比varchar更好

      3.对于非常短的列，char比varchar更好，因为varchar需要额外1-2个字节存储长度。

BINARY和VARBINARY

他们存储的是二进制字符串。二进制字符串跟常规串非常相似，但是二进制字符串存储的是字节码而不是字符。当需要存储二进制数据，

并且希望MySQL使用字节码儿不是字符进行比较时，这些类型是非常有用的。

二进制比较的优势

1.大小写敏感。

2. MySQL比较BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比较比字符比较简单很多，所以更快。

适用场景：没有中文这些需要特殊编码格式的字符时。

tips:BINARY采用的是\0(零字节)而不是空格，在检索时也不会去掉填充值。

设计标识符

选择合适的标识符很重要，最普遍的就是，我们通过标识符进行关联查询。下面是一些小技巧。

 1.整数类型

  整数通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREMENT。

  2.字符串类型

  如果可以，应该避免使用字符串类型作为标识列，因为它们很消耗空间，并且通常比数字类型慢。对       于完全“随机”的字符串也需要多注意，

例如MD5(),UUID()。这些函数生成的新值会任意分布在很大  的空间内，这会导致INSERT以及一些SELECT语句变得很慢。因为逻辑上相邻的

行为会分布在磁盘和内存的不同地方。随机值导致缓存对所有类的查询语句效果都很差，因为会是的缓存赖以工作的访问局部性原理失效。如果

整个数据集都一样的“热”，那么缓存任何一部分特定数据到内存都没有好处，如果工作机比内存大，缓存将会很多刷新和不命中。

特殊类型数据

IPV4地址，人们经常使用carchar(15)列来存储IP地址。然而，他们实际上是32位无符号整数，不是字符串。所以应该用无符号整数存储IP地址，

MYSQL 提供INET_ATON()和INET_NTOA()函数在这两种方式间转换。

总结

•所有字段均定义为NOT NULL ，除非你真的想存Null

•字段类型在满足需求条件下越小越好，使用UNSIGNED存储非负整数 ，实际使用时候存储负数场景不多

•使用TIMESTAMP存储时间

•使用varchar存储变长字符串,当然要注意varchar(M)里的M指的是字符数不是字节数；utf-8编码格式存储一个字符用3个字节。

•使用UNSIGNED INT存储IPv4 地址而不是CHAR(15) ，这种方式只能存储IPv4，存储不了IPV6

•使用DECIMAL存储精确浮点数，用float有的时候会有问题

•少用blob,text,ENUM,BIT,SET等类型。

•避免使用mysql已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度。