Mysql - Schema与数据类型的优化

选择优化的数据类型

Mysql支持的数据类型非常多，有个简单的选择原则：

• 更小的通常更好。一般情况下尽量使用可以正确存储数据的最小数据类型。会占用更少的磁盘、内存和CPU缓存。但是要确保没有低低估需要的存储范围，如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型
• 简单就好。简单数据类型的操作通常需要更少的CPU周期。
• 尽量避免使用NULL。NULL会使得索引、索引统计和值比较更复杂。并且占用更多的存储空间。当可以NULL的列被索引时，每个索引记录都需要一个额外的字节。在MYSIAM中甚至会使固定大小的索引变为可变大小的索引。虽然影响小。InnoDB使用单位的位存储NULL值，所以对于稀疏数据有很好的空间效率

整数类型

• 整数 :TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8,16,24,32,64位存储空间。整数类型有UNSIGNED属性，表示不允许负值。例如，TINYINT UNSIGNED可以存储的范围从 -128 ~ 127 变为 0 ~ 255。MySql可以为整数类型指定宽度，但是他们存储空间是一样的，只是表示显示字符的个数。例如INT(1)和INT(11)，它们的存储和计算是一样的
• 实数类型 ：DECIMAL类型用于存储精确的小数。MySQL5.0及以上，DECIMAL支持精确计算。因为CPU不支持低DECIMAL的直接计算，所以MYSQL服务器自身实现了DECIMAL的高精度计算。相对而言，CPU直接支持原生浮点计算，所以浮点运算比较快。FLOAT占用4个字节，DOUBLE占用8个字节。因为需要额外的空间和计算开销，尽量在只对小数进行精确计算时才使用DECIMAL。例如存储财务数据。
• 字符串类型：
  • VARCHAR： 用于存储可变长字符串。比定长更节省空间。但是MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储，会浪费空间。VARCHAR需要使用1或2个额外字节存储字符串的长度(小等于255占1个字节)，由于长度是可变的，导致在UPDATE的时候如果长度变长久需要做额外的工作。如果在页内没有更多的空间存储，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。MySQL5.0以上，存储会保留末尾的空格。
  • CHAR：定长，Mysql会剔除末尾空格。适合存储很短的字符串，或者长度接近的值。例如密码的MD5值。对于需要经常变更的数据，CHAR也比VARCHAR更好，因为不容易产生碎片。
  • BLOB 和TEXT类型：都是为了存储很大的数据，分别采用了二进制和字符方式存储。当BLOB和TEXT值太大时，InnoDB会使用1到4个字节存储一个指针，然后再外部存储区存储实际的值。BLOB没有排序规则或字符集，而TEXT类型有排序规则和字符集。MySQL只对每个列最前max_sort_length字节排序。MySQL不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。
  • 使用ENUM代替字符串类型：ENUM可以把一些不重复的字符串存储或预定义的集合。MYSQL在内部会将每个值在列表中的位置保存为整数，并且在*.frm文件中保存"数组 - 字符串"映射关系的查找表。同时ENUM是按照内部存储的整数而不是定义的字符串进行排序的*。但是在添加或修改内部的字符串时必须使用ALTER TABLE。因此对于未来可能会改变的字符串，ENUM并不合适，除非能接受只在列表的末尾添加元素。在特定的情况下，把CHAR/VARCHAR列与ENUM列进行关联可能会比直接关联CHAR/VARCHAR列更慢。

与CHAR和VARCHAR相似的还有BINARY和VARBINARY，他们存储的是二进制字符串，存储的是字节码而不是字符。

• 日期和时间类型
  • DATETIME: 能保存大范围的值，从1001到9999年，精度为秒。把时间封装为整数，与时区无关，使用8个字节存储。
  • TIMESTAMP: 4个字节的存储空间。范围小：1970 ~ 2038年。MYSQL提供了FROM_UNIXTIME()函数将UNIX时间戳转换为日期，UNIX_TIMESTAMP将日期转换为时间戳。依赖时区。

通常使用TIMESTAMP，空间效率更高。

• 位数据类型
  • BIT : MYSQL5.0之前BIT是TINYINT的同义词。 可以使用BIT列存储一个或多个true/false值。BIT列的最大长度为64个位。MYISAM会打包存储所有的BIT列，所以17个单位的BIT列只需要17个位存储。InnoDB个Memory,为每个BIT列使用一个足够存储的最小整数类型来存放，所以不能节省存储空间。
  • SET：存储很多false/true值。
• 选择标识符
  • 整数类型 ：通常是标识符最好的选择，很快并且可是使用AUTO_INCREMENT。
  • ENUM和SET类型：适合有序的状态、产品类型、人的性别。
  • 字符串类型：尽量避免，比较消耗空间，并且速度比整数慢。
• 特殊数据类型

MYSQL Schema 设计中的陷阱

• 太多的列 ： MYSQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。操作代价非常高。
• 太多的关联：MYSQL限制了每个关联操作最多只能有61张表。如果希望查询执行的最快且并发性最好，单个查询最好在12个表以内做关联。
• 全能的枚举：避免过度使用枚举，除非在末尾添加，不然需要ALTER TABLE，会发生阻塞操作。
• 变相的枚举
• NULL的使用：除非必要，不然可以使用特殊值代替NULL。

范式和反范式

范式的优点和缺点：

• 范式化的更新操作通常比反范式要快
• 当数据范式化时，就只有很少或者没有重复的数据，所以更新只需要修改更少的数据
• 范式化的表通常更小，可以很好的放在内存中，所以执行速度更快
• 更少需要DISTINCT或者GROUP BY语句

反范式化的优缺点

• 避免了关联
• 避免了随机I/O

加快ALTER TABLE操作的速度

• 在一台不提供服务的机器上执行ALTER TABLE的操作，然后和提供服务的主库进行切换
• 用要求的表结构创建一张和原表无关的新表，然后通过重命名和删表操作交换两张表

只修改.frm文件

• 创建一张有相同结构的空表，修改需要的
• 执行FLUSH TABLES WITH READ LOCK。这会关闭所有正在使用的表，并且禁止任何表被打开
• 交换.frm文件
• 执行UNLOCK TABLES来释放第二步的读锁

快速创建MyISAM索引

先禁用索引，载入数据，然后重新启用索引

ALTER TABLE test.load_data DISABLE KEYS;
-- load data
ALTER TABLE test.load_data ENABLE KEYS;

由于构建索引的工作被延迟到数据完全载入以后，这个时候可以通过排序来构建索引。(对唯一索引无效，因为DISABLE KEYS只对非唯一索引有效)