MySQL数据类型和Schema优化（二）

MySQL

良好的逻辑设计与物理设计是高性能的基石，应该根据具体业务需求的查询语句来设计schema，这往往需要权衡各种因素。

比如，反范式的设计可能会增加某些查询的速度，但也会带来数据同步的维护工作。

合适的数据类型

MYSQL支持的数据类型非常多，选择合适的数据类型将有助于MySQL的性能提升。

一般遵循以下规则：

1. 更小的数据类型
2. 简单的数据类型
3. 尽量避免NULL

更小的数据类型，意味着占用更小的磁盘、内存、CPU缓存等，当然一个比较重要的前提是对类型要有合理的预估，毕竟对类型的变更是一项痛苦的事情，alter table操作会锁住整个表。

简单的数据类型，意味着MySQL更少的处理，占用更少的CPU周期，比如整型比字符串操作代价更低，字符串需要处理字符集和校对规则(排序规则)，使得字符串的处理更加复杂。

尽量避免NULL，如果没有指定列是否为NULL，MySQL默认设置列为NULL。一般可以的话推荐设置Not Null，因为使用NULL对于MySQL更难优化。当为NULL的列被索引时，每个索引记录需要一个额外的字节。

日期和时间类型（datetime、timesamp）

• datetime 使用8个字节存储、timesamp使用4个字节存储
• datetime取值范围1001-9999年，timesamp取值范围在1970-2038年
• datetime没有时区概念，timesamp有时区的概念

注意：timesamp时区问题，可能会导致CPU耗尽。前提是设置time_zone=system，该设置会获取系统的时区配置，并且获取操作是全局锁，高并发的情况下会导致CPU耗尽。

整数类型

tinyint、smallint、mediumint、int、bigint

• 存储空间不同，由小到大分别是8,16,24,32,64位
• 整数类型可以设置UNSIGNED属性，表示无负数，正数取值可以扩大一倍，并且存储空间不变
• int(1) 和 int(11) 并不会改变取值范围，只是决定客户端显示的长度

实数类型(float\double\decimal)

• float\double支持标准的浮点数运算进行近似计算，不能精确表达
• float\double是CPU支持原生的浮点数计算，性能速度很快
• decimal是mysql支持的类型，支持精确的浮点数。
• decimal精确度要求比较高，比如财务数据可以选择使用
• decimal需要复杂的计算，性能相对比较差
• 数据量比较大的情况下，可以考虑使用bigint代替，通过乘相应小数点的倍数，比如100000000.1111 ,存储1000000001111，通过乘10000来解决，避免decimal复杂的计算代价。

字符串类型（Varchar、Char）

• Varchar是可变长字符串，它比定长字符串更节省空间，因为它可以合理利用空间，只使用必要的空间。
• Varchar需要额外的字节记录字符串的长度，如果长度超过255则使用2个字节，否则使用1个字节
• Varchar由于可变长，如果长度增加同时当前页没有空间存储，InnoDB需要分裂页存储，导致碎片产生。
• Char是定长字符串，对于固定长度的比如MD5加密串，非常适合使用Char进行存储，同时定长Char在更新时不容易产生碎片，存储空间上Char也不需要记录额外的长度，Varchar则需要使用1-2字节记录长度。

Schema设计

• 太多的列：MySQL存储引擎API工作时需要在服务器层和存储引擎之间通过行数据格式拷贝数据，然后在服务器层将缓存行内容解码各个列。这个代价时非常高的；比如尽量避免使用select * 操作.

• 范式与反范式：
  范式化的更新操作通常比反范式化要快，因为较好的范式化可以很少的或者没有重复数据，更新只要少部分的数据。
  举例子（错误的范式）：

department	employee	leader
A	小明	王总
A	小李	王总

当需要修改部门领导时，需要更新两条数据，如果遵循范式化，再新增一个部门和领导的实体表，则只需要更新一条数据即可

同时可以避免一些group by，distinct等操作，比如上面案例需要统计所有部门，就需要通过group by操作。

范式化的缺点是需要多表关联，稍微复杂一点可能就需要关联才能查询，此时可能会通过一些反范式的手段，减少关联查询。常用的做法就是在另一个表，保存相同的列。

通常设计是混用模式：范式+反范式配合使用

缓存表和汇总表

缓存表和汇总表没有标准的定义，通常来说缓存表来表示存储那些可以比较简单地从其他表schema获取（但是每次获取比较慢），可以通过缓存冗余数据，避免从其他表查询。

汇总表可以优先计算出结果，直接使用简单方式进行查询，减少实时查询计算的性能问题。

Java题库在线答题：www.51ctofx.com