Schema与数据类型优化

关于数据类型选择的一些记录

选择优化的数据类型

• 更小的通常更好。一般应该尽可能使用能正确存储数据的最小数据类型，更小的数据类型通常更快，因为他们占用更少的磁盘、内存和CPU缓存，并且处理需要的CPU周期更少。
• 简单就好。整型比字符串操作代价更低。
• 尽量避免null。可为NULL的列使得索引、索引统计和值比较都更复杂。NULL列被索引的时候，每个索引记录都需要一个额外的字节。NULL列会使用更多的存储空间，在MySQL也需要特殊处理。（通常把可为NULL的列改为NOT NULL带来的性能提升比较小，但是如果打算在这个列上建索引，那就应该避免设计为可为NULL。）
• 大类型： 整型 > 字符串 > 时间

整型类型

TINYINT(8), SMALLINT(16), MEDIUMINT(24), INT(32), BIGINT(64) 可选属性：UNSIGNED。MySQL可以为整型指定宽度，如int(11)，但大多数时候没有意义，只是规定了一些交互工具用来显示字符的个数。

实数类型

• FLOAT, DOUBLE, DECIMAL(可以指定前后所允许的最大位数，这会影响列的空间消耗)
• CPU支持原生浮点计算，DECIMAL的高精度计算是MySQL服务器自身实现的，所以浮点运算明显更快。
• 因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL。
• 但在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，这样可以避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

字符串类型

从MySQL4.1开始，每个字符串列可以自定义自己的字符集和排序规则，或者说校对规则，这些东西对性能影响很大。

• VARCHAR: 用于存储变长字符串，使用额外的存储空间来存储字符串长度。如果UPDATE时使得行比原来更长，这时需要额外的工作，MyISAM会将行拆分成不同的片段存储，InnoDB则需要分裂也来使行可以放进页内。会保留存储字符串的末尾空格。
• VARCHAR适用于字符串列的最大长度比平均长度大很多；列更新很少，所以碎片不是问题；使用了像UTF8这样负责的字符集，每个字符使用不同的字节数进行存储。
• CHAR：定长，会删除字符串末尾空格
• CHAR适合存储定长或长度接近的数据；对于经常变更的数据CHAR不容易产生碎片；对于非常短的列CHAR在存储空间上更有效率。

• VARCHAR(5), VARCHAR(200)存储'HELLO'的代价是一样的，但是使用更短的列依旧有优势：更长的列会消耗更多的内存，因为MySQL通常分配固定大小的内存块来保存内布值。尤其是使用内存临时表进行排序或操作时会特别糟糕。磁盘临时表也同样。

• BLOB和TEXT类型，存储大数据，分别采用二进制和字符方式。MySQL会把BLOB和TEXT当作一个独立的对象处理，存储引擎在存储的时候通常会做特殊处理，如果值过大，则在外部区域存储，行内需要1-4个字节存储指针。BLOB没有排序规则或字符集，TEXT有。

• 使用枚举（ENUM）代替字符串。对于枚举类型在表.frm文件中会保存“数字-字符串”的映射关系，行内存储的是值在列表中的位置（从1开始），排序的时候是按照列表中声明的顺序，使用枚举类型可以减少表的大小。

• 枚举类型最大的缺点在于增加或删除字符串的时候必须使用ALTER TABLE, 除非只是在末尾添加字符串，不然需要重建整个表。枚举列在进行关联查询的时候，如果和CHAR OR VARCHAR关联，性能会比较差甚至不如直接用字符串，但是枚举和枚举关联，性能不错。在某些情况下，即使可能出现ENUM和VARCHAR关联的情况这也是值得的，因为可以减少I/O。

日期和时间类型

• DATETIME可表示1001-9999年，精确到秒；
• TIMESTAMP表示1970-2038年，保存了从1970-01-01午夜到现在的秒数，显示的值与时区有关。
• FROM_UNIXTIME():从时间戳到日期，UNIX_TIMESTAMP()：从日志到时间戳，通常TIMESTAMP比DATETIME空间效率更高。

位数据类型

• BIT: BIT列最长64为。BIT(64)需要8个字节来存储。MySQL把BIT当作字符串类型而非数字类型，所以查询BIT列的时候得到的是所存储的二进制值作为ASCII码对应的字符串，如果用BIT列和其他数字比较或计算，那么查询到的是数字。这个相当费解，建议谨慎使用。
• SET: 在MySQL内部以一系列打包的位的集合来表示，可以有效的利用存储空间，并且有FIND_IN_FIELD(), FIELD()这样的函数可以在查询时使用。主要缺点是改变列的定义代价比较大。
• 一种替代SET的方案就是用一个整数包装一系列的位。类似于Linux文件系统的权限管理那样。

选择标识符

• 选择标识符的时候不仅要考虑存储类型，还要考虑到MySQL对于这种类型如何执行计算和比较。例如，存储ENUM和SET使用整型，比较的时候转换为字符串。
• 一旦选定类型，尽量确保所有关联表中都使用相同的类型。类型之间需要精确匹配，包括像UNSIGNED这样的属性。混用不同类型可能导致性能问题，像ENUM和VARCHAR做关联，即使没有性能问题，在比较操作做隐式转换的时候也可能导致很难发现的错误。
• 在可以满足值的范围，并且预留了未来增长空间的前提下，应该选择最小的数据类型。
• 整数类型最优先选择，很快且可以AUTO_INCREMENT
• ENUM,SET比较糟糕
• 字符串类型，尽量避免。很消耗空间，比数字类型慢，而且在MyISAM表里默认对字符串使用压缩索引，这会导致查询慢很多。
• 对于完全随机的字符串也需要多注意，MD5(),SHA1()和UUID()，这会导致INSERT和SELECT很慢。
  • 字符串随机写到索引的不同位置，所以INSERT慢，还会导致页分裂，磁盘随机访问。
  • SELECT慢是因为逻辑相邻的列，物理上不一定相邻。
  • 随机值导致缓存对所有类型的查询语句效果都很差，因为会使缓存赖以工作的访问局部性原理失效。
• 存储UUID值应当移除“-”符号，或者更好的做法是用UNHEX()函数转换为16字节的数字，存储在BINARY(16)列中，查询的时候通过HEX()来格式化为16进制格式。UUID值虽然分布也不均匀，但是还是有一定顺序。

特殊类型

IP地址： 通过INET_ATON()和INET_NTOA()函数在IP地址和无符号整数之间做转换。

范式和反范式

• 范式优点：
  • 范式化的更新操作比反范式化要快
  • 当数据较好地范式化的时候，就只有很少或者没有重复数据，所以只需要修改更少的数据
  • 范式化的表通常更小，可以更好的放在内存里，所以执行操作会更快
  • 很少有多余的数据意味着检索列表数据时更少需要DISTINCT和GROUP BY这样的语句。
• 范式缺点：
  • 通常需要关联
  • 代价昂贵，也可能是一些索引策略无效
• 反范式优点：
  • 不需要关联查询
• 反范式缺点：
  • 数据冗余
  • 更新和插入操作需要做更多

缓存表和汇总表

这个感觉就看具体需求了，对于缓存或者汇总的数据实时性要求有多高，然后采取不同的策略。

影子表：是指在一张真是表“背后”创建同样的表，然后建好之后通过原子的重命名操作切换。

mysql> DROP TABLE IF EXISTS my_summary_new, my_summary_old;
mysql> CREATE TABLE my_summary_new LIKE my_summary;
mysql> RENAME TABLE my_summary TO my_summary_old, my_summary_new TO my_summary；

物化视图

MySQL并不支持物化视图，可是使用开源工具Flexviews来实现。

加快ALTER TABLE的速度

ALTER TABLE一般都很慢，操作将导致MySQL服务中断。一般来说是会新建个表，然后从旧表读取数据插入到新表中，这样效率很慢。

但并不是所有的都这么慢，比如修改一个列的默认值有两种办法：

mysql> ALTER TABLE film MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

mysql> ALTER TABLE file ALTER COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5

第一种方法是通过上面所说的读取插入的方式。 第二种方法会直接修改.frm文件不涉及数据，所以很快。

下面有一些修改表的操作是不需要重建表的：

• 移除一个列的AUTO_INCREMENT属性
• 增加、移除或更改ENUM和SET常量。如果移除的是已经有行用到的，查询将返回一个空字符串。

基本思路是为想要的表结构创建一个新的.frm文件，然后替代已有的那个：

• 创建相同结构的表，并进行修改
• 执行FLUSH TABLES WITH READ LOCK。这将关闭所有正在表，并且禁止任何表被打开。
• 交换.frm文件
• 执行UNLOCK TABLES释放锁。

为InnoDB表增加索引的时候也可以尝试一种比较骇客的方法（官方并不支持，注意备份数据）：

• 用需要的表结构创建一张表，但是不包括索引。
• 载入数据到表中以构建.MYD文件
• 按照需要的接口创建另外一张空表，这次包含索引。这会创建需要的.frm和.MYI文件。
• 获取读锁并刷新表。
• 重命名第二张表的.frm和.MYI文件，让MySQL以为是第一张表的文件。
• 释放读锁。
• 使用REPAIR TABLE来重建表的索引。