高性能Mysql——Schema与数据类型优化

良好的逻辑设计和物理设计师高性能的基石
一、选择优化的数据类型
更小的通常更好
占用更小的磁盘、内存、CPU缓存和处理时需要的CPU周期
简单就好
操作需要更少的CPU周期,例如:整型比字符型操作代价更低,以为字符集和校对规则使字符比整型更复杂。应该使用Mysql内建的类型而不是字符串来存储日期和时间,另外一个是应该用整型存储IP地址。
尽量避免NULL
通常情况最好指定列为not null,除非真的需要存储null值。如果查询包含null的列,对Mysql的来说更难优化,null的列是的索引、索引统计和值比较都更复杂。当可为null的列被索引时,每个索引记录需要一个额外的字节,在Myisam里甚至还可能导致固定大小的索引变成可变大小的索引。通常可以把可为null的列该为not null带来的性能提升比较小。如果计划在列上建索引,就应该尽量避免设计成可为null的列。当然也有例外:InnoDB使用单独的位(bit)存储null值,所以对于稀疏数据(很多值null)有很好的空间效率。
1、整数类型
TINYINT 8
SMALLINT 16
MEDIUMINT 24
INT 32
BIGINT 64
整数类型可选UNSIGNED表示不允许为负数,可以使正数上线提高一倍。
Mysql可以为正数类型指定宽度,对于存储计算来说int(1)和int(20)是相同的。
2、实数类型
实数是带有小数部分的数字。decimal类型用于存储精确的小数
float使用4个字节存储,double使用8个字节
3、字符串类型
varchar和char类型
varchar类型用于存储可变长字符串,他比定长类型更节省空间,如果Mysql表使用row_format=fixed创建的话,每一行都会使用定长存储,这会很浪费时间。varchar需要使用1或2个额外字节记录字符串的长度,如果列的最大长度小于或等于255字节,则只使用1个字节表示,否则使用2个字节。varchar节省了存储空间,但是由于行是可变的,在update时可能使行变得比原来更长,下面这几种情况使用varchar是合适的:
字符串列的最大长度比平均长度大很多,列的更新很少,所以碎片不是问题;使用了像UTF-8这样复杂的字符集,每个字符都使用不同的字节数进行存储。
InnoDB可以把过长的varchar存储为blob
char类型是定长的:Mysql总是根据定义的字符串长度分配足够的空间。char适合存储很短的字符串,或者所有的值都接近同一个长度。例如:char非常适合存储密码的MD5值,因为这是一个定长的值。对于经常变更的数据char也比varchar更好,因为定长不容易产生碎片。对于非常短的列,char比varchar在存储空间上更有效率。例如:用char(1)来存储只有Y和N的值,如果采用单字节字符字符集(字符串长度定义不是字节数,是字符数,多字节字符需要更多的存储空间)只需要一个字节,但是varchar(1)却需要2个字节,因为还有一个记录长度的额外字节。
blob和text类型
都是为存储很大的数据而设计的,分别采用二进制和字符方式存储。Mysql不能将他们全部长度进行索引。
使用枚举型代替字符串类型
枚举可以把一些不重复的字符串存储成一个预定义好的集合。
create table enum_test(e enum(‘fish’,’apple’,’dog’) not null);
insert into enum_test(e) values(‘fish’),(‘dog’),(‘apple’);
4、日期和时间类型
datetime
精确度为秒,他把日期和时间封装到YYYYMMDDHHMMSS整数中,与时区无关使用8个字节存储空间
timestamp
他和Unix的时间戳相同,他只使用4个字节的存储空间,因此它的范围比datetime小的多,1970-2038,Mysql提供了from_unixtime()函数把Unix时间戳转换为日期,依赖于时区。
5、位数据类型
bit
可以使用bit列在一列中存储一个或多个true/false值。bit列最大长度是64个位,实际中使用较少。
set
如果需要保存很多true/false值可以考虑合并这些列到一个set数据类型。
6、选择标识符
标识列也可能在另外的表中作为外键使用,所以为标识列选择数据类型时,应该选择跟关联表中的对应列一样的类型。在可以满足值的要求,并且预留未来增长空间的前提下,应该选择最小的数据类型。
字符串类型
如果可能,应该避免使用字符串类型作为标识类,因为他们很耗空间,并且通常比数字类型慢。
如果存储UUID值,则应该移除”-“符号,或者更好的做饭是用unhex()函数转换UUID值为16字节数字,并且存储在一个binary(16)列中,检索时可以通过hex()函数来格式为十六进制格式。
7、特殊数据类型
IPv4我们经常使用varchar(15)列来存储IP地址,然而他们实际上市32位无符号整数,不是字符串。用小数点分成4段的表示方法只是为了让人们容易阅读。所以应该用无符号整数存储IP地址。Mysql提供inet_aton()和inet_ntoa()函数在这两种方法之间转换。
二、缓存表和汇总表
我们用缓存表来表示存储那些可以比较简单的从schema其他表获取数据的表。举个例子:
假如要统计一个网站之前24小时内发送的消息数。在一个很繁忙的网站不可能维护一个实时精确的计数器。作为代替方案,可以每小时才、生成一张汇总表,比维护实时计数器高效得多。

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。