MySQL Schema与数据类型的优化

[[207400]]

选择优化的数据类型：

1、 更小的通常更好：

一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为他们占用更少的磁盘，内存和cpu缓存，并且处理时需要的cpu周期也更少。

2、 简单就好

简单的数据类型操作通常需要更少的cpu周期。例如，整型比字符操作代价更低，因为字符集和校对规则(排序规则)使字符比较比整型更加复杂。注：应使用mysql内建的类型存储时间和日期，而不是字符串。

3、 尽量避免null

如果查询中包含可为null的列，对Mysql来说更难优化，因为可为null的列使得索引、索引统计和值都比较复杂。可为null的列会使用更多的存储空间，在mysql中也需要特别处理。当可为null的列被索引时，每个索引记录需要一个额外的字节。

通常把可为null的列改为not null带来的性能提升比较小。在调优时，没有必要首先在现有的shema中查找并修改掉这个情况，除非确定这会导致问题。但是如果列上建索引，就应尽量避免设计成可为null的列。

整数类型：

整数类型有TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。分别使用8，16,24,32,64位存储空间。他们可以存储的值的范围从-2的(n-1)次方到2的(n-1)次方-1，其中n是存储空间的位数。

整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以是正数的上限提高一倍。例如TINYINT UNSIGNED可以存储的范围是0~255，而TINYINT的存储范围是-128~127。

实数类型：

实数是带有小数部分的数字。然后，他们不仅仅为了存储小数部分;也可以使用DECIMAL存储比BITINT还大的整数。MYSQL既支持精确类型，也支持不精确类型。

DECIMAL类型用于存储精确的小数，因为double类型和float类型在进行计算时，会因为精度损失导致一些数据的偏差。但是DECIMAL数据类型cpu不支持对其直接计算，cpu直接支持原生浮点计算，所以浮点运算明显更快。

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL-例如存储财务数据。但是在数据量比较大的时候，可以考虑使用BITINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

字符串类型(varchar和char)

• Varchar：

Varchar类型用于存储可变长字符串，是最常见的字符串类型。他比定长类型更节省空间，因为它仅使用必要的空间(例如，越短的字符串使用越少的空间)。所以，varchar节省了存储空间，对性能也有帮助。但是，由于行是边长的，在update是可能使行变得比原来更长，这就导致需要做额外的操作。如果一个行占用的空间增长，并且页内没有更多的空间存储。MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。

注：InnoDB更加灵活，它可以把过长的varchar存储为BLOB。

• Char类型：

Char类型适合存储很短的字符串，或者所有值都接近同一个长度。例如，char类型非常适合存储密码的md5值，因为他是定长的值。还有用户的身份证号以及手机号码。对于经常变更的数据，char也比vachar更好，因为定长的char类型不容易产生碎片。对于非常短的列，char也比varchar在存储空间上更有效率。例如用char(1)存储Y和N的值，如果采用单字节字符集只需要一个字节，但是varchar(1)却需要两个字节，因为还有一个记录长度的额外字节。

Varchar(5)和varchar(200)存储‘hello’时空间开销是一样的，但是Varchar(5)会有很大的优势，因为更长的列会消耗更多的内存，因为Mysql通常会分配固定带下的内存块来保存内部值。尤其是使用内存临时表进行排序和操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。

时间类型

DateTime和TimeStamp

• DateTime

这个类型能保存大范围的值，从1001年到9999年，精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数里，与时区无关。使用8个字节的存储空间。

• TimeStamp：

存储1970年1月1日午夜以来的描述，他和Unix时间戳相同。TimeStamp只使用4个字节的存储空间，因此它的范围比DateTime小的多;只能表示从1970年到2038年。

TimeStamp显示的值也依赖于时区。Mysql服务器，操作系统，以及客户端连接都有时区设置。

TimeStamp也有DATETIME没有的特殊属性。默认情况下，如果插入时没有指定第一个TimeStamp列的值，Mysql会设置这个列的值为当前时间。在插入一行记录时，Mysql默认也会更新第一个TimeStamp列的插入和更新操作。最后，TimeStamp列默认为not null，这也和其他的数据类型不一样。