数据库性能优化之道-优快云博客

Schema与数据类型优化

1.1选择优化的数据类型

1.2MYSQL schema设计中的陷阱

1.3范式和反范式

1.4缓存表和汇总表

1.5加快alter table操作的速度

1.6总结

Schema与数据类型优化

良好的逻辑设计和物理设计是高性能的基石,应根据系统将要执行的查询语句来设计schema.

1.1选择优化的数据类型

选择正确的数据类型对于获得高性能至关重要,不管要存储哪种类型的数据,有几个简单的原则有助于做出更好的选择.

更小的通常更好:尽量使用可以正确存储数据的最小数据类型.更小的数据类型通常更快.因为它们占用更少的磁盘,内存和CPU缓存,处理时需要的CPU周期也更少.
简单就好:简单数据类型的操作通常需要更少的CPU周期.如整型比字符型操作代价更低.应当用mysql内建的数据类型来存储日期和时间,而不是用字符串,应该用整型存储IP地址.
尽量避免NULL:如果查询中包含可为NULL的列,对mysql来说更难优化,因为可为NULL的列使得索引,索引统计和值比较都更复杂.可为NULL的列会使用更多的存储空间,当可为NULL的列被索引时,每个索引记录需要一个额外的字节.如果计划在列上建索引,就应该尽量避免设计成可为NULL的列.

为列选择数据类型时,第一步需要确定合适的大类型(数字.字符串,时间等),下一步是选择具体类型.

1.1.1整数类型

类型	所占位数	值的范围(N为位数)
TINYINT	8位	-2^(N-1)到2^(N-1)-1
SMALLINT	16位	-2^(N-1)到2^(N-1)-1
MEDIUMINT	24位	-2^(N-1)到2^(N-1)-1
INT	32位	-2^(N-1)到2^(N-1)-1
BIGINT	64位	-2^(N-1)到2^(N-1)-1

整数类型有可选的UNSIGNED属性,表示不允许负值,大致可以使正数的上限提高一倍.

有符号数和无符号数使用相同的存储空间,并且具有相同的性能.

mysql可以为整数指定宽度,例如INT(11),然并卵,这只是限制了mysql的一些交互工具用来显示字符的个数,对于存储和计算来说,INT(1)和INT(20)是相同的.

1.1.2实数类型

实数是带有小数部分的数字,它们不只是为了存储小数部分,也可以使用DECIMAL存储比BIGINT还大的整数.

MYSQL既支持精确类型,也支持不精确类型.FLOAT(4字节)和DOUBLE(8字节)类型支持使用标准的浮点运算进行近似计算.与具体平台有关.

DECIMAL类型用于存储精确的小数,CPU不支持对DECIMAL的直接计算,mysql服务器自身实现了DECIMAL的高精度计算.相对而言,CPU直接支持原生浮点计算,所以浮点运算明显可以更快.

浮点和DECIMAL类型都可以指定精度.

mysql使用DOUBLE作为内部浮点计算的类型.

存储货币可以使用BIGINT,可以同时避免浮点计算不精确和DECIMAL精确计算代价高的问题.

1.1.3字符串类型

varchar和char是两种最主要的字符串类型,存储方式和具体的存储引擎有关.

存储引擎存储char或者varchar值的方式在内存中和在磁盘上可能不一样,所以mysql服务器从存储引擎读出的值可能需要转换为另一种存储格式.

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

varchar节省了存储空间,所以对性能也有帮助.但是,由于行是变长的,在update时可能使行变得比原来更长,这就导致需要做额外的工作.如果一个行占用的空间增长,并且在页内没有更多的空间可以存储,InnoDB需要分裂页来使行可以放进页内.MyISAM会将行拆成不同的片段存储.

适合使用varchar 的场景:字符串列的最大长度比平均长度大很多;列的更新很少,所以碎片不是问题;使用了UTF-8字符集,每个字符都使用不同的字节数进行存储.

msyql在存储和检索时会保留末尾空格.

InnoDB可以把过长的varchar存储为blob.

对于char类型,mysql总是根据定义的字符串长度分配足够的空间.当存储char值时,mysql会删除所有的末尾空格.char值会根据需要采用空格进行填充以方便比较.

char适合存储很短的字符串;或者所有值都接近同一长度.例如char非常适合存储密码的MD5值,对于经常变更的数据,char也比varchar更好,因为定长的char类型不容易产生碎片.对于非常短的列,char也比varchar在存储空间上更有效率

与char和varchar类似的类型还有binary和varbinary,他们存储的是二进制字符串.mysql填充binary采用的是\0而不是空格.在检索时也不会去掉填充值.

二进制比较比字符比较简单很多,所以也就更快.

使用更短的列的优势:更长列会消耗更多的内存,因为mysql通常会分配固定大小的内存块来保存内部值.尤其是使用内存临时表进行排序或操作时会特别糟糕,在利用磁盘临时表进行排序时也同样糟糕.

blob和text都是为了存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储.

mysql把每个blob和text值当做一个独立的对象处理.存储引擎在存储时通常会做特殊处理.当blob和text值太大时,InnoDB会使用专门的"外部"存储区域来进行存储,此时每个值在行内需要1~4个字节存储一个指针,然后在外部存储区域存储实际的值.

二进制字符串存储的是二进制数据,没有排序规则或字符集.而字符字符串有字符集和排序规则.

mysql不能将blob和text列全部长度的字符串进行索引,也不能使用这些索引消除排序.

又时候可以使用枚举列代替常用的字符串类型.

1.1.4日期和时间类型

mysql能存储的最小时间粒度为秒,但是mysql也可以使用微秒级的粒度进行临时运算.

mysql提供两种相似的日期类型:datetime和timestamp.

datetime能保存大范围的值,从1001年到9999年,精度为秒.它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中,与时区无关,使用8个字节的存储空间,可排序.

timestamp类型保存了从1970年1月1日午夜以来的秒数,和UNIX时间戳相同.使用4个字节的存储空间,能表示从1970年到2038年的范围,timestamp显示的值依赖于时区.默认情况下,如果插入时没有指定第一个timestamp列的值,mysql则设置这个列的值为当前时间,在插入一行记录时,默认也会更新第一个timestamp列的值.timestamp列默认为not null.

推荐使用timestamp,因为它比datetime空间效率更高.

需要存储比秒更小粒度的日期和时间值怎么办?可以使用bigint类型存储微秒级别的时间戳,或者使用double存储秒之后的小数部分.

1.1.5位数据类型

mysql有少数几种存储类型使用紧凑的位存储数据.这些类型,不管底层存储格式和处理方式如何,从技术上来说都是字符串类型.

bit列在一列中存储一个或多个true/false值.bit(1)定义一个包含单个位的字段bit(2)存储两个位,依次类推.bit列的最大长度是64位.

InnoDB为每个bit列使用一个足够存储的最小整数类型来存放,所以不能节省存储空间.

应避免使用bit类型,如果想在一个bit的存储空间中存储一个true/false值,另一个方法是创建一个可以为空的char(0)列.该列可以保存空值(NULL)或者长度为零的字符串(空字符串).

如果需要保存很多true/false值,可以考虑合并这些列到一个set数据类型,它在mysql内部是以一系列打包的位的集合来表示的.set的类型的缺点是改变列的定义的代价较高,需要alter table,这对于大表来说是非常昂贵的操作.一般来说,也无法再set列上通过索引查找.可以用来保存权限的访问控制列表.

一种替代set的方式是用一个整数包装一系列的位.可以把8个位包装到一个tinyint中,并且按位操作来使用.