数据类型优化

选择数据类型

• 更小的通常更好
  尽量使用可以正常存储数据的最小数据类型，占用更少的磁盘空间、内存和CPU缓存
• 简单就好
  简单数据类型的操作通常需要更少的CPU周期
• 尽量避免NULL
  如果查询中包含可为NULL的列，MySQL更难优化

整数类型

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
- 分别使用8,16,32,64,128位存储空间，存储的值范围:

-2^{N-1} - 2^{N-1}-1

• 整数有UNSIGNED属性，表示无符号整数
• MySQL可以为整数类型指定宽度，例如INT(11)，这个只是规定了MySQL的一些交互工具用来显示字符的个数， 对于 存储计算是没有意义的

实数类型

FLOAT, DOUBLE, DECIMAL
- DOULE和FLOAT使用标准的浮点运算进行近似计算，DECIMAL用于存储精确的小数
- FLOAT占4个字节，DOULE占8个字节。DECIMAL可以指定小数点前后所允许的最大位数，会影响空间消耗，每4个字节存9个数字，小数点本身占一个字节，最多允许65个数字。
- DECIMAL只是一种存储格式，在计算中会转换为DOUBLE类型。
- 因为额外的空间和计算开销，尽量只在对小数进行精确计算时才使用DECIMAL，最好使用BIGINT代替DECIMAL

字符串类型

VARCHAR, CHAR, BLOB, TEXT

VARCHAR和CHAR

• VARCHAR存储可变字符串，比定长类型更节省空间，仅使用必要的空间。但如果MySQL使用ROW_FORMAT=FIXED建表的话，每一行都会使用定长存储
• VARCHAR需要使用1 （最大长度小于255字符）或2（最大长大于255字节）个额外字节记录字符串长度。
• 由于VARCHAR是变长，在UPDATE时可能使行变得比原本更长，导致需要做额外 的工作
• 在字符串最大长度比平均长度大很多，列更新很少;使用UTF-8这种复杂的字符集的情况下，适合使用VARCHAR

使用VARCHAR(5)和VARCHAR(200)存储一个5字节的字符串的空间开销是一样的，但是更长的列会消耗更多的内存，MySQL通常会分配固定的内存块内部值。在使用内存临时表或磁盘临时表进行排序时会消耗过多的不必要的空间。

CHAR

• CHAR类型是定长的，MySQL总是根据定义的字符串长度分配足够的空间
• CHAR适合存储很短的字符串或者是所有值都接近同一长度
• 对于经常变更的数据，CHAR也比VARCHAR更好，因为定长类型不容易产生碎片

与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY，它们存数的是二进制字符串。存储的是字节码而不是字符，使用\0而不是空格进行填充。

BLOB和TEXT

• 都是为了存储很大的数据，分别采用二进制和字符串方式存储
• MySQL把每个BLOB和TEXT值当作一个独立的对象处理。当值太大时，InnoDB会使用专门的外部存储区域进行存储，此时每个值在行内需要1~4个字节存储一个指针，外部存储实际值。
• MySQL对这两种类型排序只对每个列的最前max_sort_length进行排序。

因为Memory引擎不支持BLOB和TEXT，所以，如果查询使用了这两种类型列并且需要使用隐式临时表，将不得不使用MyISAM磁盘临时表，会导致严重的性能开销。尽量避免使用这两种类型，如果实在无法避免，在用到时，使用SUBSTRING(column,length)将列值转换为字符串（要足够短，不会使临时表的大小超过max_heap_table_size或tmp_table_size，超过以后会转为磁盘临时表），就中以使用内存临时表了。

枚举

• 使用枚举代替常用的字符串类型
• MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或两个字节中，MySQL在内部会将每个值在列表中的位置保存为整数，并在表的frm文件中保存映射关系
• 枚举排序是按内部存储的整数而不是定义的字符串进行排序

日期和时间类型

MySQL能存储的最小时间粒度为秒，但是MysQL可以使用微秒级的粒度进行临时运算。

DATETIME

• 从1001年到9999年，精度为秒。把日期和时间封半到格式为YYYYMMDDHHMMSS的整数中，与时区无产
• 使用8个字节的存储空间

TIMESTAMP

• 保存从1970年1月1日午夜以来的秒数（格林尼治标准时间）
• 4个字节的存储空间，从1970年到2038年，显示的值依赖时区

- 默认为NOT NULL，插入时如果字段为空为插入当前时间。

范式与反范式

数据库三范式

• 第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列。
• 第二范式（2NF）：首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
• 第三范式（3NF）：首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

范式的优点和缺点

• 更新操作通常比反范式要快
• 由于只有很少或者没有重复的数据，所以只需要修改更少的数据
• 表通常更小，可以更好地放在内存里，执行速度更快
• 更少的DISTINCT或者GROUP BY语句
• 复杂一点的查询语句关联太多，也可能使索引失效，影响效率

反范式的优点和缺点

• 避免关联
• 更好的使用索引
  

如果不需要关联表，对大部分最差的查询情况，如全表扫描，当数据比内存大时比关联要快得多，因为这样避免了随机I/O，全表扫描基本上是顺序I/O