架构优化和索引--数据类型的选择

 

优化通常需要权衡取舍，为了加快数据读取而添加索引会减慢更新的速度，同样，非规范化架构能加快某些类型的查询，但却会让其他类型的查询变慢。添加计数器和汇总表是优化查询的好方法，但是它们却要花费额外的维护代价。

 

1. 数据类型的选择

不管选择何种类型，下面的简单原则都有助于作出更好的选择：

通常越小越好：用正确的存储和表示数据的最小类型。更小的数据类型通常更快，因为他们使用了更小的磁盘空间、内存空间和cpu。

简单就好：越简单的数据类型，需要的cpu就越少。整数的比较的代价小于字符，因为字符集和排序规则使字符比较更复杂。因此，应该使用mysql内建的类型来保存日期和时间，而不是使用字符串；而是使用整数来保存ip地址。

尽量避免使用NULL：Mysql难以优化引用了可空列的查询，它会使索引、索引统计和值更加复杂。即使要在表中存储“没有值”的字段，还是有可能不使用NULL的。要考虑使用0或空字符串来代替。

 

整型：mysql有TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT，它们分别需要8、16、24、32、64。Signed和Unsigned类型占用的存储空间是一样的，性能也一样。MySql还可以对整数类型定义宽度，比如INT（11），这对于大多数应用程序是没有意义的，他不会限制范围，只是规定了Mysql交换工具（命令行）用来显示字符的个数。对于存储和计算，INT(1)和INT（20）是一样的。

实数：实数有分数部分。然而，它们并不仅仅是分数。可以使用DECIMAL保存比BIGINT还大的整数。MySQL同时支持精确与非精确类型。FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。DECIMAL类型用于保存精确的小数。在MySQL 5.0及以上版本，DECIMAL类型支持精确的数学运算。在MySQL 5.0及以上版本中，服务器自身进行了DECIMAL运算，因为CPU并不支持对它进行直接计算。浮点运算会快一点，因为计算直接在CPU上进行。MySQL 5.0及以上版本中的DECIMAL类型最多允许65个数字。

VARCHAR和CHAR类型：VARCHAR保存了可变长度的字符串，是使用得最多的字符串类型。它能比固定长度类型占用更少的存储空间，因为它只占用了自己需要的空间(也就是说较短的值占用的空间就较少)。例外情况是使用ROW-FORMAT=FIXED创建的MyISAM表，它为每行使用固定长度的空间，可能会造成浪费。

VARCHAR使用额外的1到2字节来存储值的长度。如果列的最大长度小于或等于255，则使用1字节，否则就使用2字节。假设使用latin 1字符集，VARCHAR(10)将会占用11字节的存储空间。VARCHAR(1000)则会占用1002字节，因为需要2个字节来保存长度信息。

VARCHAR能节约空间，所以对性能有帮助。然而，由于行的长度是可变的，它们在更新的时候可能会发生变化，这会引起额外的工作。如果行的长度增加并不再适合于原始的位置时，具体的行为则会和存储引擎相关。例如，MyISAM会把行拆开，InnoDB则可能进行分页。另外的存储引擎还可能不会在合适的位置更新数据。

当最大长度远大于平均长度，并且很少发生更新的时候，通常适合使用VARCHAR。这时候碎片就不会成为问题。还有当你使用复杂的字符集，比如UTF-8时，它的每个字符都可能会占用不同的存储空间。

在5.0及以上版本，无论是保存还是取值，MySQL都会保留字符串末尾的空格。但是在4.1及之前的版本，这些空格会被去掉。

CHAR是固定长度的。MySQL总是为特定数量的字符分配足够的空间。当保存CHAR值的时候，MySQL会去掉任何末尾的空格。(在MySQL4.1及之前版本，VARCHAR也是如此。CHAR和VARCHAR在逻辑上是一样的，只是存储格式不同。)进行比较的时候，空格会被填充到字符串末尾。

BLOB和TEXT类型：BLOB和TEXT分别以二进制和字符形式保存大量数据。事实上，它们各自有自己的数据类型家族:字符类型有TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT和二进制类型有TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB和LONGBLOB。BLOB等同于SMALLBLOB,TEXT等同于SMALLTEXT。

BLOB和TEXT唯一的区别就是BLOB保存的是二进制数据，没有字符集和排序规则，但是TEXT有字符集和排序规则。

使用ENUM代替字符串类型：有时可以使用ENUM列来代替传统的字符串类型。ENUM列可以存储65 535个不同的字符串。MySQL以非常紧凑的方式保存了它们，根据列表中值的数量，MySQL会把它们压缩到1到2个字节中。MySQL在内部把每个值都保存为整数，以表示值在列表中的位置，并且还保留了一份“查找表(Lookup Table)”来表示整数和字符串在表的.frm文件中的映射关系。

另外一个让人吃惊的事情是ENUM字段在内部是按数字顺序进行排序的，而不是按字符串排序。

绕过这个问题的方法就是按照想要的顺序来定义ENUM。还可以使用FIELD()显式地规定排序顺序，但这会造成排序无法使用索引。

ENUM最不好的一面是字符串列表是固定的，并且添加或删除字符串须使用ALTER TABLE。因此，对于一系列未来可能会改变的字符串，使用ENUM就不是一个好主意。

 

日期和时间类型：MySQL提供了两种相似的数据类型:DATETIME和TIMESTAMP。对于很多应用程序，它们都能工作，但是在某些情况下，一种会好于另外一种。

DATETIME

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

在默认情况下，MySQL以一种可排序的、清楚的格式显示DATETIME值，例如2008-01-16 22:37:08。这种表示日期和时间的方式符合ANSI标准。

TIMESTAMP

就像它的名字一样，TIMESTAMP类型保持了自1970年1月1日午夜(格林尼治标准时间)以来的秒数，它和Unix的时间戳相同。TIMESTAMP只使用了4字节的存储空间，因此它的范围比DATETIME小得多。它表示只能从1970年到2038年。MySQL提供了FROM_ UNIXTIME()函数把Unix时间戳转换为日期，并提供了UNIX TIMESTAMP()函数，把日期转换为Unix时间戳。

TIMESTAMP也有DATETIME没有的特殊性质。在默认情况下，如果插入的行没有定义TIMESTAMP列的值，MySQL就会把它设置为当前时间(注3)。在更新的时候，如果没有显式地定义TIMESTAMP列的值，MySQL也会自动更新它。可以配置T工MESTAMP列的插入和更新行为。最后，TIMESTAMP列默认是NOT NULL，这和其他的豹据举型都不一样。

除了这些特殊行为之外，通常应该使用TIMESTAMP，因为它比DATETIME更节约空间。有时人们把Unix的时间戳保存为整数值，但是这通常没有任何好处。因为这种格式处理起来不太方便，我们并不推荐它。

 

SET：果要保存许多truel/false值，可以考虑把许多列合并为SET数据类型，它在MySQL内部是以一系列位表示的。它有效地使用了存储空间，并且MySQL有诸如。FIND_IN_SET ()和FIELD()这样的函数，方便在查询中使用它。它的主要缺点是改变列定义的代价较高，需要ALTER TABLE，这在大型表上是很昂贵的操作  (但是本章稍后部分给出了解决办法)。通常说来，你也不能在SET列上使用索引进行查找。