【三】MySql高性能优化——数据类型

    古人云：不积跬步，无以至千里；不积小流，无以成江海。

    MySql的优化方法有很多，但很多方法都会告诉你找到时间消耗最大的地方去做针对性的优化。这样做没有错，也是效果最好的。但我认为我们在表的设计和数据类型的选用等这些基础点上就应该做到极致（自己所能达到的最好），虽然这点优化不一定能解决我们的实际问题，但是这些优化都是顺手就可以完成的，本人曾经亲自测试过，选择合适的数据类型在单个查询中甚至可以提升0.005S的效率。这样的优化在大数据下是很有效的。同时这种注重基础设计的方式也体现了一个好的程序员素养。

    良好的逻辑设计和物理设计是高性能的基石。什么是范式设计和反范式设计？数据表应该如何选择字段类型？我们就这两个问题进行讲述。

    范式设计

        第一范式：表的列具备原子性，不可再分解；也就是说，只要数据库是关系型数据库；

        第二范式：只要保证表中的每条记录都是唯一的；

        第三范式：要求表中的数据不可存在冗余，也就是说，如果表中的信息能够被推导出来，就不应该单独的设计一个字段来存放。

    反范式设计就是范式设计的要求反着来。在数据库设计时，我们应该遵循小、简单既是好，要避免使用NULL。

    选择合适的大类：数字，字符串，时间等。在MySql中常常会有多种数据类型可以存储相同类型的数据，只是存储长度和范围不同、允许的精度不同、需要的物理空间不同。甚至在行为和属性上也会有差异。如：TIMESTAMP只使用DATETIME一半的存储空间，并且会根据时区变化，具体特殊的自动更新能力。缺点是可使用的时间范围小（TIMESTAMP只记录时间范围是1970~2038）。

    整数类型：TINYINT（8位）、SMALLINT（16位）、MEDIUMINT（24位）、INT（32位）、BIGINT（64位），可存储-2（N-1）~2（N-1）-1 其中（N-1）是幂。整数类型可以使用UNSIGNED属性（如：TINYINT UNSIGNED 的范围是0~255，而TINYINT 的范围是-128~127）。有符号和无符号具有相同的存储空间和性能。大家在设计数据库表时可能会有一个误区。INT（11）它不会限制值的合法范围，只是规定了MySql的一些交互工具用来显示字符的个数。对于存储和计算来说，INT（1）和INT（20）相同。

    实数类型：DECIMAL存储比BIGINT还大的整数（可存储最多65个数字），支持精确小数存储，可以精确计算（5.0版本之后）。但DECIMAL只是一种存储格式，在计算中会转换为DOUBLE类型。浮点类型存储占用空间比DECIMAL更小。因此应该只在需要对小数做精确计算时使用DECIMAL类型。可以使用BIGINT类型以货币单位小数乘以相应倍数的方式代替DECIMAL存储精度计算数据。CPU直接支持原生浮点计算，所以浮点运算明显更快。虽然有多种方法可以指定浮点精度，MySql内部会选择不同的数据类型或进行存储取舍。但这些精度定义是非标准的，所以建议只指定数据类型，不指定精度。

    字符串类型：可存储字符串的大类型有：VARCHAR、CHAR、BLOB、TEXT、ENUM。其中有的类型又有自己的兄弟类型。这些类型的存储方式跟存储引擎直接相关。下面根据存储量的大小进行拆分讲述。

        VARCHAR和CHAR：在计算机领域中，性能与资源往往只能二选一，高性能可能需要更多的资源支持。低的资源消耗往往需要以牺牲性能为代价。VARCHAR用于存储可变长字符串数据类型，比CHAR类型更节省空间。但存取性能上略逊与CHAR（差距很小）。VARCHAR需要额外的1~2个字节记录字符串长度（255字节以内1个字节）。VARCHAR适用于：字符串列的最大长度远大于平均长度，更新少，采用UTF-8，每个字符都使用了不同的字节数存储。在InnoDB中会把过长的字符串从VARCHAR类型转换成BLOB类型存储。CHAR适用于：存储很短的字符串，或者所有值都接近同一个长度，以及经常更新的数据（不易产生碎片）。对于非常短的列，CHAR比VARCHAR在存储空间上更高效。差异：CHAR在索引时会截断值后的空格，VARCHAR不会。在Memory引擎中VARCHAR只会根据最大长度分配空间。类似的还有存储二进制字符串（字节码）的BINARY和VARBINARY。填充BINARY使用\0替代空格。检索时并不截取\0。二进制比较值比字符更快。建议：更长的列会消耗更多的内存，因为MySql通常会分配固定大小的内存块来保存内部值。最好的策略是只分配真正需要的空间。

        BLOB和TEXT：分别采用二进制和字符串存储大数据字符串。BLOB的兄弟类型有：TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB。TEXT的兄弟类型有：TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT。MySql会将BLOB和TEXT值当作独立的对象处理。当值很大时会引用专门的外部存储区域（会多1~4个字节存储一个指针）。差异：BLOB类型没有排序规则或字符集。对这两个类型的排序会采用取每列前max_sort_length字节。优化排序方法：修改max_sort_length的配置，或使用ORDER_BY_SUSTRING（column，length）。Memory引擎并不支持这两种类型，如果查询中有使用会借用MyISAM磁盘临时表。建议：如果需要在Memory引擎使用BLOB或TEXT可使用SUSTRING（column，length）或ORDER BY转换为字符串，但长度不能超过max_heap_table_size或tmp_table_size。

    枚举（ENUM）：枚举类型可以替代字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySql存储枚举时非常紧凑，会将枚举类型压缩到一个或两个字节中。其“数字-字符串”映射关系保存于.frm文件中。枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。虽然可以使用FIELD（）函数显示的指定排序顺序，但这会导致MySQL无法利用索引消除排序。缺点：字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE。使用枚举会有查找映射的额外开销，所以尽量不要使用CHAR/VARCHAR列与枚举列进行关联。实际应用中，我们尽量在查找表时采用整数主键而避免采用基于字符串的值进行关联。优点：可以使表的Data_length缩小1/3，这样可以减少I/O性能消耗。同时也会减小主键大小，由于InnoDB中其他索引会保存有主键，所以其他索引的大小也会减小。

    时间和日期类型：日期：DATETIME（1001年~9999年，与时区无关，8字节存储）、TIMESTAMP（1970~2038，与时区有关，4字节存储）。可使用FROM_UNIXTIME（）和UNIX_TIMESTAMP（）函数进行日期和Unix时间戳之间的转换。TIMESTAMP默认为NOT NULL。MySql会默认在没有指定第一个TMIESTAMP列的值时设置其为当前时间。插入一行记录时也会默认更新第一个TIMESTAMP列的值。推荐使用TIMESTAMP。如果需要存储比秒更小的级别，可使用BIGINT或DOUBLE（存储秒之后的小数部分）或者直接使用MariaDB（Linux默认数据库）。

    位数据类型：位数据类型从技术上讲都是字符串类型。BIT（5.0版本之后）用于存储一个或多个true/false值。最大长度是64位。且与存储引擎相关。MyISAM的位存储最为节省空间。注意：在值的比较中，数字上下文的场景中检索时，结果将是位字符串转换成0或1的数字。存储true/false的替选方案是创建一个CHAR（0）来存储NULL或者长度为0的字符串。SET用于保存很多true/false，配合FIND_IN_SET（）和FIELD（）函数可方便查询。缺点：改变列需要使用ALTER TABLE。同时也无法在SET列上使用索引查找。替代SET的方案：使用整数包装一系列位，如：把8个位包装成一个TINYINT，每个位代表一个true/false。优点：不用ALTER TABLE改变字段代表的“枚举”值。缺点：查询语句更难写且更难理解。

    选择标识符（identifier）

    应该选择与关联表中对应列一样的类型。同时需要考虑MySql对这种类型如何执行计算和比较。注意类型之间需要精确匹配（如：UNSIGNED修饰关键字）。比较不通的数据类型可能存在隐式的数据转换，可能导致很难发现的错误。标识符类型的选择应尽量避免使用SET、ENUM和字符串类型。如果存储UUID值，应该移除“-”符号；或者使用UNHEX（）函数转换UUID值为16字节的数字存储在一个BINARY（16）列中，可使用HEX（）转换查询。UNID有一定的顺序，SHA1是完全的散列，最好还是递增的数字。

    对应IP地址，可使用32为的无符号整数存储，配合MySql提供的INET_ATON（）和INET_NTOA（）函数进行IP转换。

=======================================================

名词解释：

    热点：数据集在访问时会有热点区域（数据）访问。随机值会使整个数据集都成为热点数据，相当于消除了热点，使访问真题性能下降。