物理设计-数据类型的选择

下面我们来看看如何为表中的字段选择合适的数据类型,其实呢如何为表中的字段选择合适的数据类型呢,

说真的他真是一个技术活,我发现大多数这种开发人员呢,对于如何选择合适的表的字段类型呢,并不是太重视,

对于字符串类型呢,他们喜欢简单的使用255,默认长度,其实一般类型的选择呢,对于数据库的性能又很大的影响,

而过大的数据类型呢,往往会浪费更大的内存,磁盘IO,而这两种资源在数据库中,都是十分珍贵的,我们先来看一下

对字段选择的一个原则,当一个列可以选择多个数据类型时,应该首先考虑数字类型,其次是日期和二进制类型,最后

才是字符类型,而对于相同级别的数据类型呢,应该选择占用空间小的数据类型,之所以这样说呢,主要是由于以下几个原因,

首先在对数据进行比较,比如查询条件啊,关联排序时,字符类型,处理当前排序的规则,是相关的,而数字与二进制呢,

不需要参照排序规则,其处理是按照二进制大小来进行的,同样字符串处理要比数字处理要慢,另外在数据库中,数据库中

是以页为单位的,每页存储的数据量是一定的,在innodb是16k,长度越小呢,意味着能融入的行就越多,这样在加载同样数据

时呢,使用宽度较小的类型呢,要比宽度较大的类型呢,所加载的磁盘页就越小,减少了磁盘IO,有利于磁盘的提升,这种原则

看上起很简单,但是真的要做好真的不容易,这样我们就针对各种数据类型的使用,来给大家详细的分析一下

首先呢我们先来看看整数类型,MYSQL一共有5种整数类型,包括tinyint,smallint,bigint,还有int,从上面的

可以看到,整数可以存储值的最大,最大是2的n次幂,最小是2的n次幂-1,其中n是存储空间的位数,比如tinyint来说,

从下表中可以看到,他的存储空间是一个字节,是-2的8减1,也就是-128到127之间的整数呢,都是可以存储到tinyint

中,此外整数还包括可选的unsigned属性,这个属性值呢,称之为符号的属性值,也就是说,不允许在无符号的整数存储

负值,比如对于tinyint来说,无符号的值就可以存储0到255,比之前的有符号值最大127呢,差不多就大了一倍,下表中

其他的数据类型呢,也具有类似的特点,我们可以看到smallint呢,是两个字节的存储空间,meidumint是占用三个字节的

存储空间,而int是占用四个字节的存储空间,bigint就占用了8个字节的存储空间,而在平时工作中呢,我经常会遇到这种

情况,开发人员在建立表结构时呢,喜欢使用int(2)来指定int类型的宽度,因为他们认为呢,int(2)代表只会存储两位小数,

这样可以节约存储空间,而实际上这种限制呢,对整数类型来说,他的宽度是没有任何意义的,如上面所说的int类型,同样

还会占用4个字节存储空间,而且存储的范围也不会有任何的影响,所以如果我们只要存储两位数的话,就不要使用int类型,

而要使用tinyint才能达到我们节约空间的目的

下面我们来看看如何为我们的列呢选择正确的实数类型,实数类型和整数类型呢,最大的不同是,实数类型可以

存储小数部分,但是他们不只是为了存储小数而使用的,比bigint更大的整数,一共有三种实数类型,如下表,

其中float和double是非精确的,这一点我们可以通过下面的查询来了解,对非精确的一个含义,我们来到

我们的演示系统

在这里来给大家演示一下,大家就明白了,我们在这里建好了一个t_double表

show create table t_double;

这个表中一共有三列,其中第一列i是int类型的,而第一列和第二列呢,分别是两个double类型,我之前已经

将表中插入了一些数据,大家来看一下

select * from t_double;

 

有正有负,很简单的数据,下面呢我们对这个数据进行汇总操作,大家看会出现什么样的情况,我们按照i列进行汇总,

这个SQL是十分简单的,就是按i列进行分组,并得出第一列和第二列的分组值

select i,sum(d1) as a,sum(d2) as b from t_double group by i having a<>b;

我们可以看到,第一例的值是,101和负的80,大家可以看到,详细情况是这样的,所以这两个值的和,是21.4,而在查询结果

中呢,是21.4006,这说明了什么问题呢,这就是double这种类型的值呢,是一种非精确的一种值,两个double汇总得到的结果呢,

跟我们的实际结果是有差距的,除了float和double之外呢,MYSQL有另一个值类型就是Decimal,这个Decimal类型呢,是一个精确

的存储类型,所以我们在使用DECIMAL类型,可以重新进行上面的实验,进行验证,我们来看一下,同样是t_decimal这张表

select * from t_decimal;

我们来看看这个表结构

show create table t_decimal\G

和上面的t_double表结构非常的相似,只不过第一列和第二列使用的类型是decimal,同样我们在这个表上呢,

进行汇总操作,会出现什么样的情况呢,我们将第二列进行简单的汇总,并且求出来a不等于b的列

select i,sum(d1) as a,sum(d2) as b from t_decimal group by i having a<>b;

这个查询结果呢,不像t_double表那样,产生了差异,这个decimal结果呢,跟我们刚才算的结果是一样的,

DECIMAL列可以保证小数的精度,但是其所占的存储空间呢,就要比上面两种要多,DECIMAL类型是每四个字节,

可以存储9个数字,并且小数点也可以占用一个字节,下面这个DECIMAL定义类型来说,就是DECIMAL(18,9),就是需要

9个字节来存储,其中小数点前面的数字呢,使用4个字节存储,后面的也要4个字节来存储,因为在小数点两边是有9个

数字的,另外加上一个小数点所占的字节,一共9个字节,另外在MYSQL5.0版本之后啊,DECIMAL一共只能存储65个数字,

不过对于我们来说这也已经足够了,那么对于以下3种实数类型该如何选择呢,这和我们存储的业务数据类型呢,都有

很大的关系,从他们的特点可以看出来,我们要存储的是财务相关的数据,我们就必须使用精确的DECIMAL类型,而对于其他

类型的情况呢,我们就可以使用double或者float类型,来进行存储

varchar和char两种最主要的字符串类型,其在磁盘上的存储方式呢,和我们所使用的存储引擎呢,有很大的

关系,这里我就以最常用的innodb存储引擎呢来说一说这两种类型应该如何使用,才能对数据库的性能有更有利

的影响,为了使大家能够理解如何使用varchar这种数据类型,我们先来看一看他在存储上有些什么样的特点,首先

varchar是用于存储变长字符串的,所谓的变长字符串呢,就是指字符串的长度不固定的,可能很长,也有可能很短,

但是无论长短,varchar只会占用必要的存储空间,比如说我们有一个,宽度是50的varchar列,如果我们只存储了10个

字符,那么这个列的实际存储空间呢,只占用这10个字符的宽度,注意我这里说的是字符,而不是字节,因为一个字符有

可能是有多个字节的,以utf8为例,一个字符就占用3个字节,另外还有一点也要注意,就是varchar和char中定义的宽度呢,

是以字符为单位的,而不是以字节,这一点有的开发人员经常搞不清楚,以utf8字符为例呢,要在varchar中存储10个字符呢,

需要varchar列的长度是30,而实际上只要定义varchar的宽度为10就可以了,这也是经常不能正确定义varchar列的原因,

那么varchar列的第二个特点呢,是每一个varchar列的最大长度呢,如果小于255的话,则会占用一个额外的字节,用于记录变长

字符串的长度,而当列的宽度大于255的时候呢,就会占用额外的两个字节,用于记录字符串的实际长度,这也就决定了varchar

列的最大宽度呢,实际上就是65535字节,但是在实际中呢,我们不可能对varchar建立这么大的宽度,因为对于innodb来说,

65535个字节,一个行中所有varchar共享的长度,如果我们要定义非常大的数据类型呢,我们就应该使用text类型来存储,

既然说到了varchar的宽度问题,那我们下面来说一说应该如何来确定varchar列的宽度,这应该是在物理设计中呢,非常

关键的一个问题,varchar列应该是我们最常见的一种数据类型了

所以为varchar列选择合适的宽度呢,也对数据库的性能有非常重要的影响,我们在决定varchar列宽度的时候呢,

有一个总的原则,使用最小的符合需求的长度,这个原则看上去是很简单的,但是要做到呢并不容易,首先我们很难

确定什么是最小的符合需求的长度,为了解决这个问题呢,就需要我们对这个业务呢,十分的了解,只有真正的了解了

业务所需要存储的数据,才知道我们什么样的长度是一个合理的范围,比如说呢,用户姓名这一列,如果我们只要存储中文

名的话,这一列不会超过10个字符,因为很少有中国人的名字呢,会有10个字,而如果我们还要存储外国人的姓名,这个列

可能要增加到20个字符了,因为我们知道呢,外国人的姓名通常是很长的,但是无论如何呢,因为呢,不会有人起255个字符

长度的名字,另外还有一个点也要注意,虽然我们希望varchar列的长度尽可能的小,但是也并不是说,不管业务的实际情况,

只顾把varchar列的宽度设置的很小,想以后长度不够了我们就使用更大的长度,那就可以了,这种想法是十分的不正确的,

因为一旦这个业务上线,如果我们再进行列的修改的话,那样做的成本是非常大的,在MYSQL5.7之前的版本中,对于varchar列的

宽度,无论是改大还是改小,都是要锁表的,就算是在MYSQL5.7中,5.7版本之后呢,如果之前varchar是255个字节以内,如果以后

我改变后的宽度是255个字符,这样的话是可以不锁表的,大家要注意,一旦在生产环境中呢,发现了锁表的一种问题,那么在一个

繁忙的系统中,一定会产生很严重的系统性能问题的,那么听了前面关于varchar列的介绍,大家也许会有这样的疑问,比如说,

使用varchar(5)和varchar(200)来存储MYSQL的字符串,由于这两种列的宽度呢,都是没有超过255的,所以只需要占用一个

额外的存储字节,来存储这种字符串的长度,所以要存储MYSQL这种字符,实际上只需要6个字节,存储空间就够了,我们还有必要

选择最小列的宽度吗,难道不是超过255个字节,存储像这类字符串,所占的空间不都是一样的吗,其答案我们还确实需要最小列

的宽度的,是因为MYSQL能够更有效地优化查询,所以在内存中,对字符串使用的是固定的宽度,所以如果我们把列的宽度定义的太长,

消耗更多的内存,所以这才是我们要求对MYSQL的列呢,宽度选择最符合要求的最小长度的一个真实的原因,前面我们已经介绍了

很多关于varchar列的内容了

下面我们来看看varchar类型更适合存储什么样的数据,首先varchar类型比较适合于,存储那种业务上来说,

最大长度要比平均长度要大很多的字符串,因为这种情况下呢,能够发挥出varchar变长存储的特点,而第二点呢,

就是varchar列呢,更适合存储很少被更新的数据,因为varchar类型的更新呢,由于字符串的长度呢,它会产生变化,

所以会引起存储列的分页,这样就会造成很多的存储碎片,所以varchar类型更适合于很少被更新的字符串数据,最后

varchar比较适合与utf8多字符字符集,因为这样的字符集呢,每一个字符都可能使用不同类的字节数,来进行存储,

以utf8为例呢,如果我们要存储中文,需要三个字节,如果存储英文或数字呢,只需要一个字节

下面我们来看看如何使用char类型,同样我们还是先看看char类型在存储上的特点,和varchar最大的不同是,

char类型是定长的,MYSQL总是根据定义的char类型的宽度,来分配足够的空间,来存储char类型的数据,也就是说

如果我们定义了一个char类型的宽度是char(50),其实我们在这个列中只存了10个字符,那么在存储时呢,也会分配

50个字符的空间,而另外呢,当字符串存储在char类型时呢,如果字符串末尾包含了空格,则这个空格会被自动删除,

而同样的字符呢,存储在varchar列中呢,则字符串末尾的空格是不会被删除的,最后呢char类型的最大宽度呢,只有

255个字符,所以我们要存储超过这个大小的字符串呢,就要选择varchar类型,这就是char类型在存储上的特点,我们下面

来看看char类型,比较适合存储什么样的数据

也就是char类型适合的场景,从char类型的存储特点上看呢,可以知道,char类型适合存储具有下面特征的字符串,

首先是存储在char类型的字符串中的数据呢,应该具有相似的长度,因为char类型呢,是定长的,所以呢如果存储的长度

接近的话,那么就可以定义一个固定的长度,这样就不会浪费存储空间和内存,这类字符串呢,有一个很好的例子,我们在

存储用户密码的时候呢,经常要把用户密码加密后,再进行存储,所以在这种情况下呢,就比较适合使用char类型,当然对于

身份证手机号这种,定长的字符串呢,也可以存储在char类型中,第二种适合存储char类型的字符串就是比较短小的字符串,

对于短的字符串来说呢,存储在char类型中,比存储在varchar类型中更加的节省空间,比如我们有一个列用于存储用户的性别,

这种列只有两种值,男或女,这个时候使用char比varchar更适合,同样以utf8为例,char类型存储这个值呢,需要三个字节,

而因为varchar1呢,需要额外一个字节来存储字符串的长度,所以如果使用varchar来存储性别信息的话,需要四个字节,

所以从这一点上来看,这种短字符串的存储呢,使用char比varchar要更加的有效率,最后和varchar类型相比呢,char类型适合

存储经常更新的字符串,因为char类型的长度是固定的,MYSQL会一次性的分配存储空间,所以在多次更新时呢,也不会产生列分裂的

情况,至于经常更新的字符串,使用char类型可以避免产生存储碎片,这样可以获得更好的IO性能,除了字符串类型之外,日期

类型也是我们工作中要经常使用到的类型