数据库数据类型优化教程

数据库类型的选择对数据库性能也有一点的影响,现在我们就来分析一下,数据库类型优化主要从三部分来说:1.数据类型优化 2.数据库设计优化 3.SQL查询优化.

本文不含数据库服务器配置优化,仅从应用程序设计的角度考虑数据库的设计优化方案.

主要从三部分来说:1.数据类型优化 2.数据库设计优化 3.SQL查询优化,细心的观众会觉得少了点什么,索引,对当然得有索引,索引忒重要,这个最后说.

这样既有宏观设计层面的,还有微观数据类型层面的,这就是俗话说的:上得了厅堂,下得了厨房,斗得了小三...

如果数据库遇到了性能瓶颈,通常需要从这4方面总体设计,而不是孤立的单方面考虑.

一、数据类型优化

1.原则:

1)越小越好

例如能用tinyint解决就不要使用smallint,能用int就不要用float，能用varchar(10)就不要使用 varchar(100),因为小的数据类型占用更少的磁盘空间，内存和CPU资源，消耗更少的CPU周期,varchar(10)和 varchar(100)虽然占用的存储空间一样，但是前者更有优势，后面介绍varchar类型时会提到，所以绝对是越小越好，当然得能够满足存储需求，这好像是废话，谁还不知道呢，哪有这么傻x的架构师，有吗?没有吗?这个还真有，好像工作中有些所谓的架构师真的很xx，实际工作中遇到较x的，笑笑就行了。

2)越简单越好

简单数据类型的操作需要消耗更少的CPU周期，例如，操作整型比操作字符串类型的代价更低。

2.整型

tinyint(8)->smallint(16)->mediumint(24)->int(32)->bigint(64),括号中的数字是使用多少位存储空间。

另外整型有unsigned属性,比如tinyint unsigned可以存储0~255,而tinyint可以存储-128~127.

3.实数

FLOAT和DOUBLE是不精确类型,DECIMAL是精确类型,由于CPU直接支持浮点计算所以FLOAT类型比DECIMAL更快,在数据量较大的时候,可以使用BIGINT替代DECIMAL,例如将金额乘以一百万,然后将结果存储在BIGINT中,可以提高计算速度.

4.字符串类型

CHAR和VARCHAR,这个地球人都知道varchar是变长的,char是定长的,就不说了.

varchar使用1到2位来描述实际字符串的长度。varchar可能会造成碎片问题,比如定义为varchar(1000),初始插入的时候插入了1个字符，后来将这条记录update到1000,这时候可能会造成当前页存储不下,就会造成页分裂,产生一个新页来存储这部分数据,虽然一般数据库会预留一些额外的页空间,但是这种仍然不能完全避免.

char存储时会删除末尾空格,如果存储一个很短的定长字符,比如Y/N,char仅需一位,而varchar还额外需要一位来存储字符串的长度.

varchar(10)和varchar(1000)一样吗?存储空间一样,但是varchar(1000)需要消耗更多的内存,尤其使用临时表排序时会更加明显,所以越小越好这个原则是不会错的.

5.大字段BLOB和TEXT

他们都用于存储一些大字段,例如大的文件内容,图片内容等等,他们的区别是BLOB存储的是二进制数据,而TEXT有字符集和排序规则,一般可以用记事本打开的存储为TEXT,其他的存储为BLOB.

6.日期和时间

MySQL支持的最小时间粒度到秒,这个有点粗,1秒对于计算机来说已经很长了.

TIMESTAMP存储的是从1970年开始到当前的时间戳,DATETIME则存储实际的时间值.TIMESTAMP的空间效率更高一些,但是TIMESTAMP只能存储到2038年,如果你的系统打算用到这之后,就果断放弃它吧.

7.主键

1)要保证表与表之间的关联字段数据类型一致,如果不一致到生产环境上遇到了性能问题再改,可不是闹着玩的.

2)主键使用整型性能更高.

如果使用字符串会导致:

a.插入性能差,因为插入需要维护索引,如果使用随机字符串,比如java中的uuid,由于是无序的,所以要维护索引到不同的位置,这会有较大的性能消耗.

b.SELECT会较慢,因为逻辑上相邻的行会分布在磁盘和内存的不同地方.

c.随机会导致缓存命中率降低,因为缓存进来的数据虽然在物理上相邻,但是逻辑上并不相邻,所以会导致更多的不命中,导致频繁刷新缓存,失去了缓存的意义,反而加大了消耗.