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

旧城等待， 2022-07-14 08:50 280阅读 0赞

## 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到2N−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.csdn.net/wsyw126/article/details/53454823][http_blog.csdn.net_wsyw126_article_details_53454823]  
作者：WSYW126

[http_blog.csdn.net_wsyw126_article_details_53454823]: http://blog.csdn.net/wsyw126/article/details/53454823