数据库优化（优化数据类型）

 通常更小更好

　　更小的数据类型通常更快，因为使用的磁盘空间，cpu缓存，内存更少。cpu周期也更少。

　　但是要确保不会低估需要保存的值。因为在架构中增加数据类型的范围事件极其费力的事情。

 越简单越好

　　简单的数据类型需要的cpu周期少。如整数的代价小于字符，因为字符集和排序规则是字符比较复杂。如使用mysql内建类型保存日期和时间而不是使用

字符串，二是使用整数保存ip地址。

 避免使用null

　　尽量把字段定义为not null，即使程序无须保存null，也有许多表包含了可空列。除非真的需要保存null，否则就把列定义为not null

　　mysql难以优化引用了可空的查询，它会使用索引、索引统计和值更加复杂。可空列需要更多的存储空间。还需要在mysql内部进行特殊处理。当可空列

被索引的时候每条记录都需要一个额外的字节，还能导致myisam中固定大小的索引变成可变大小的索引。

　　即使要在表中存储“没有值”的字段，还是有可能不使用null的。可以考虑使用0，特殊值或者空字符串来代替。

　　当然把null改为not null带来的性能提升很小，除非它确实带来的问题。否则就不要把它当成优先的优化措施。然后如果计划对列进行索引，就尽量避免把

该列设置为可空。

1、整数

　　整数类型有tinyint、smallint、mediumint、int、bigint，它们分别需要8、16、32和64位存储空间。范围是-2（^n-1）到2（^n-1）。整数还有可选的

unsigned属性，表示不允许为负数，并大致把正数提高了一倍。

　　mysql还可以对整数定义宽度，比如int（11）。这对大多数应用程序是没有意义的：这样不会限制值的范围，只是规定了mysql交互工具显示字符的个数。对于存储和计算，int（1）和int（20）是一样的。

2、实数

　　可以使用dicimal保存比bigint更大的整数。mysql同时支持精确和非精确类型。float和double类型支持使用标准的浮点型进行计算。decimal用于保存

精确的小数。它支持精确的数学运算。可以定义浮点型和dicimal类型的精度。对于decimal列可以定义小数点之前的和之后的最大位数。这会影响所需的存储空间。如：decimal（18,9）会在小数点前后都保存9位数字，共使用9个字节：小数点前4个字节，小数点一个字节，小数点后4个字节。

比起decimal类型，浮点型保存同样大小的数据占用的空间更少。float占用4个字节。double占用8个字节，而且精度更高，范围更大。

　　由于需要额外的空间和计算开销，只有在需要对小数进行精确计算的时候才使用decimal，比如保存金融数据。

3、字符串类型

　　（1）varchar 和char

　　varchar保存可变长度的字符串，使用最多。可以比固定长度类型占用更少的存储空间。以为它只占用资金需要的空间。

　　varchar使用额外的1到2个字节来存储长度，如果列的最大长度小于或等于255，则使用1个字节。否则使用2字节。

　　varchar可以节约空间，故对性能有帮助。但是由于行的长度是可变的，它们在更新的时候可能会会发生变化，这会引起额外的工作。

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

　　char

　　char是固定长度。保存char 的时候，mysql会去掉任何末尾的空格。但是在进行比较的时候空格会被填充到字符串末尾。

　　char在存储很短的字符串或者长度近似相同的字符串的时候很有用。如char合适存储用户密码MD5哈希值。对于经常变动的值，char也好于varchar，因为固定长度的行不容易产生碎片。对于很短的列，char 的效率也高于varchar。如：char（1）字符串用来存储单字节字符集，只会占用1个字节。但是varchar（1）会占用2个字节。因为1个字节用来存储长度信息。

4、blob和text类型

　　blob和text类型分别以二进制和字符串形式保存大量的数据

　　这两者唯一的区别是blob保存的是二进制数据，没有字符集和排序规则，但是text有字符集和排序规则。

　　mysql对blob和text列的排序方式和其他类型不同：它不会按照字符串的完整长度进行排序，而只是按照max_sort_length规定的若干个字节进行排序。

如果按照开始的几个字符排序，就可以减少max_sort_length的值或使用order by substring（column，length）

　　mysql不可以索引这些数据类型的完整长度，也不能为排序使用索引。

      注意：当使用blob和text列并且需要隐式临时表的查询将不得不适用磁盘上的myisam临时表。即使只有几列也会这样。这样导致严重的性能开销。最好的办法是尽可能的避免blob和text类型。如果不能避免，就可以使用order by substring（column，length）把这些值转换为字符串，让它们使用内存中的临时表。

5、使用enum（枚举类型）代替字符串类型

　　有时候可以使用enum列来代替传统的字符串类型。enum列可以存储65535个不同的字符串。mysql非常紧凑的保存了它们，在mysql会把它们保存为整数，以表示值在列表中的位置，并且还保存了一份查找表。来表示整数和字符串在表的.frm文件中的映射关系。

mysql>create table enum_test(
                e ENUM('fish','apple','dog') not null   
            ) ;
mysql>insert into enum_test(e),values('fish'),('dog'),('apple')   

以上代码存储的实际上是整数，而不是字符串。可以按照数字取出来，这样就可以了解它们的双重特性。

1 mysql> select e+0 from enum_test;
2 
3 +=========+
4 |  e+0    |
5 |=========|
6 |    1    |
7 |    2    |
8 |    3    |
9 +=========+

enum字段在内部是按照数字的顺序进行排序的，而不是字符排序。

1 mysql> select e from enum_test order by e;
2 
3 +=========+
4 |    e    |
5 |=========|
6 |    fish |
7 |    apple|
8 |    dog  |
9 +=========+

假如想按照想要的字符排序，可以使用FIELD()显示的规定排序顺序。这会造成排序无法使用索引。

1 mysql> select e from enum_test order by FIELD(e, 'apple', 'dog', 'fish');
2 
3 +=========+
4 |    e    |
5 |=========|
6 |    apple|
7 |    dog  |
8 |    fish |
9 +=========+

枚举类型（enum）最不好的地方就是字符串列表是固定的，并且添加或删除字符串必须使用alert table。因此，对于一系列未来可能改变的字符串，使用枚举类型不是个好主意。

6、日期和时间

mysql会使用多种类型保存各种日期和时间，如YEAR 和 DATE，mysql限制的最细的时间粒度是秒

mysql提供了两种时间的数据类型：DATETIME和TIMESTAMP

DATETIME

　　该类型保存大范围的值，从1001到9999，精度为秒。把日期和时间封装格式为：YYYYMMDDHHMMSS，使用8个字节存储空间

TIMESTAMP

　　该类型保存了自1970年1月1日以来的秒数，它和unix时间戳一样。只使用了4个字节的存储空间。范围比DATETIME小的多，只能表示从1970年到2038年。FROM_UNIXTIME()函数把Unix时间转换为日期。UNIX_TIMESTAMP（）函数把日期转换为时间戳。

除了这些特殊性质以外，保存时间通常应该使用TIMESTAMP，因为它比DATETIME更加节约空间。有时候人们把Unix时间戳保存为整数，这样没有任何好处。因为这种格式处理起来不太方便。不推荐使用。

假如要以秒以下的精度保存时间，mysql没有合适的数据类型，这时候我们结义使用自己的存储格式：使用BIGINT类型并且把它以毫秒的精度保存为时间戳格式，或者使用DOUBLE保存秒的分数部分。

7、标示符选择

（1）整数通常是标示符的最佳选择，因为它速度快，并且能够使用AUTO_INCREMENT

（2）ENUM和SET

这两个类型通常不适合做标示符。他们适合保存订单的状态、产品的类别或者性别这样的信息。

（3）字符串类型

尽可能避免使用字符串类型作为标示符。因为它们占用很多的空间并且比整数要慢。特别是在MyIsam表上不要使用字符串标示符。因为MyIsam默认会给字符串使用压缩索引（packed index）这样使得查询更加缓慢。

 8、特殊类型的数据

　　一些数据没有直接对应的内建数据类型。精度低于秒的时间戳就是个例子。

另外一个例子就是IP地址。人们通常使用varchar（15）来保存IP地址。但是IP地址实际上是无符号的32位整数，而不是字符串。使用小树点来分割纯粹是为了增加可读性。实际上应该使用无符号的整数来保存IP地址。Mysql提供了INET_ATON（）和INET_NTOA()函数在IP地址和整数之间相互转换。