高性能MySQL（四）—Schema与数据类型优化（1）

Schema与数据类型优化

选择优化的数据类型

下面是一些简单的原则：

1. 更小的通常更好

   一般情况下，应该尽量使用可以正确存储的最小数据类型。如：只需要存储0-200， tinyint unsigned就比较好。小的数据类型占的磁盘、内存和CPU缓存都较少，并且处理时需要的CPU周期数也更少。

2. 简单就好

   简单数据类型额操作通常需要更少的CPU周期。如：应该使用MySQL的內建类型来存储时间和日期而不是字符串。如：应该用整型存储IP地址。

3. 尽量避免null

   1. 通常情况下最好指定列为NOT NULL，除非真的需要NULL。查询的列包含可为NULL的列时，对MySQL来说很难优化。因为NULL的列存在使索引、索引统计和值得比较都更复杂。并且为NULL的列需要更多的存储空间。当为NULL的列被索引时，每个索引记录需要一个额外字节。
   2. 通常将NULL改为NOT NULL带来的性能提升比较小，所以没必要在现存的schema中查找和修改。但是在计划加上索引的列，就尽量避免设计为NULL的列。
   3. 例外：InnoDB使用单独的位（bit）存储NULL，所以对于稀疏数据（很多值为NULL）有很好的空间效率。

选择数据类型时，第一步需要确定合适的大类型：数字、字符串、时间等。下一步是选择具体的类型。很多MySQL的数据类型可以存储相同类型的数据，只是存储的长度和范围不一样、允许的精确度不一样、或者需要的物理空间不同。如：datetime和timestamp可以存储相同类型的数据：日期和时间、精确到秒。然而timestamp只使用datetime一半的存储空间，并且会根据时区进行变化，具有特殊的自动更新能力。但是timestamp允许的时间范围要小的多。，有时候它的特殊能力会成为障碍。

整数类型

1. 存储整数的类型：TINYINT，SMALLINT，MEDIUMINT，INT，GIGINT。分别使用的存储空间为8，16，24，32，64位存储空间。它们而已存储的范围为：$(-2)^{N-1}$到$2^N -1$，N是存储空间的位数。
2. 整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使整数的上限提升一倍。
3. MySQL可以为整数类型指定宽度，例如INT(11)，对于大多数应用这是没有意义的。它不会限制值得合法范围。只是规定了MySQL的交互工具（客户端）用来显示字符的个数。对于存储和计算来讲INT(1)和INT(20)是一样的。

实数类型

实数是带有小数部分的数字。MySQL支持精确类型，也支持不精确类型。

1. float和double类型支持使用标准的浮点运算进行近似计算。
2. DECIMAL类型用于存储精确的小数。在MySQL5.0+版本中DECIMAL类型支持精确计算。
3. 浮点和DECIMAL类型都可以指定精确度。对于DECIMAL列，可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。

字符串类型

1. VARCHAR

   1. 用于可变长字符串。
   2. 需要1或者2个额外字节记录字符串长度。如果列的最大长度小于或者等于255，则值需要1个字节表示，否则使用2个字节。
   3. MySQL4.1-版本中MySQL会删除末尾空格，5.0+版本中MySQL在存储和检索时会保留末尾空格。
   4. InnoDB把过长的VARCHAR存储为BLOB。

2. CHAR

   1. 定长。会删除所有末尾的空格。
   2. 适合存储很短的字符串，或者所有值都接近同一个长度。
   3. 适合存储密码的MD5值，因为这是一个定长。
   4. 适合经常变更的数据，因为定长不容易产生碎片。
   5. 适合非常短的列。如果采用单字节字符集只需要一个字节，但是VARCHAR(1)需要两个字节。

3. 填充和截取空格的行为

   1. 在不用的存储引擎上都是一样的，因为这是在MySQL服务器层进行处理的。

   2. 使用下面的命令进行验证：

      CREATE TABLE `char_test` (
        `char_col` char(10) NOT NULL DEFAULT '',
        `varchar_col` varchar(10) NOT NULL DEFAULT ''
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      
      insert into char_test (char_col, varchar_col) values ('string1', 'string1'),(' string2', ' string2'),('string3 ', 'string3 ')
      
      select concat("'",char_col,"'"), concat("'",varchar_col,"'") from char_test
      
      select * from char_test where char_col = "string3 " and varchar_col = "string3"

      注意空格的填充和截取。

BINARY和VARBINARY

1. 存储二进制字符串。二进制码存储的是字节码而不是字符.
2. 填充也不一样：MySQL填充BINARY采用的是\0（另字节）而不是空格，在检索时也不会去掉填充值。

3. 二进制比较的优势：

   1. 大小写敏感。

      select binary 'ABCD'='abcd' COM1, 'ABCD'='abcd' COM2;
      +--------+-----------+
      | COM1 | COM2 |
      +--------+-----------+
      |      0     |      1      |
      +---------+-----------+
      1 row in set (0.00 sec)
      
      select * from usertest where username = binary  'ab'
      

   2. 根据字节的数值进行比较，因此二进制比较比字符比较简单很多，所以更快。

4. BLOB和TEXT类型

   1. 都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。
   2. 字符类型：TINYTEXT，SMALLTEXT，TEXT，MEDIUMTEXT，LONGTEXT。
   3. 二进制类型：TINYBLOB，SMALLBLOB，BLOB，MEDIUMBLOB，LONGBLOB。
   4. 与其他的类型不同，MySQL把每个BLOB和TEXT值当作一个独立的对象处理。存储引擎在存储时，通常会做特殊处理。当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。
   5. BLOB和TEXT之间的不同是BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规则。
   6. MySQL对BLOB和TEXT进行排序与其他类型时不同的：它只对每个列的最前max_sort_length字节而不是整个字符串做排序。

5. 使用枚举代替字符串类型

   1. MySQL在存储枚举时非常紧凑，会根据列表值得数量压缩至一个到两个字节。
   2. 不建议使用数字作为枚举类型进行存储。
   3. MySQL在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。
   4. 枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。
      
      1. 所以如果要绕过这种限制那么就按照需要的顺序来定义枚举列。
      2. 可以在查询是使用FIELD()函数显示的指定排序顺序，但这会导致MySQL无法利用索引消除排序。

日期和时间类型

1. DATETIME
   这个类型能保存的值：从1001年到9999年，精度为妙。它把时间和日期封装到格式YYYYMMDDHHMMSS的整数中，与时区无关，保存在8字节中。

2. TIMESTAMP

   1. 它保存了从1970年1月1日午夜一来的秒数，它与unix时间戳是相同的。
   2. 但它只用4个字节，所以表示的范围小一点。只能表示1970到2038年。
   3. MySQL中FROM_UNIXTIME()函数把unix时间戳转换为日期。
   4. UNIX_TIMESTAMP()函数将日期转换为Unix的时间戳。
   5. 依赖于时区。

位数据类型

所有的位数据，不论底层存储格式和处理方式如何，在技术上讲都是字符串类型。

1. BIT

   MySQL将bit当做字符串处理，而不是数字。当检索bit(1)时，结果是包含二进制0和1的字符串，而不是ascii码的“0”和“1”。

2. SET

   可以保存很多true和false的值。SET在MySQL内部是一系列打包的位的集合来表示的。

选择标识符

为标识列选择类型是非常重要的。

1. 整数类型

   整数通常是标识列的最好的选择。因为它们很快并且能使用AUTO_INCREMENT。

2. 枚举类型和SET类型

   不建议使用这两种当做标识列。

3. 字符串类型

   如果可能，应该避免使用字符串作为标识列，因为它们很消耗空间，并且通常比整数类型慢。

   1. 对于完全“随机”的字符串也要注意。例如：MD5()，SHA1()或者UUID()产生的字符串。这些函数生成的新值会任意的分布在很大的空间内，这会导致insert和select变得很慢。

   2. 原因：

      1. 因为插入值会随机地写到索引的不同的位置，所以使得insert语句很慢。
      2. select语句会变得更慢。因为逻辑上相邻的行会分布在磁盘和内存的不同的地方。
      3. 随机值导致缓存对所有类型的查询语句变得很差。因为缓存工作依赖访问局部性原理失效。会出现很多不命中。

特殊类型数据

某些数据类型并不直接和内置数据类型一致。

1. 低于秒级精确度的时间戳就是一个例子。
2. IP存储是32位无符号整数，而不是字符串。MySQL提供INET_ATON()和INET_NTOA()函数在整数和.表示之间转换。

---

参考资料：
高性能MySQL（第3版）
备注：
转载请注明出处：http://blog.youkuaiyun.com/wsyw126/article/details/53454823
作者：WSYW126