MySQL---Schema与数据类型优化

良好的逻辑设计和物理设计是高性能的基石，应该根据系统要执行的查询语句来设计schema。
什么是schema？MySQL官方文档指出，从概念上讲，schema是一组相互关联的数据库对象，如表，表列，列的数据类型，索引，外键等等。但是从物理层面上来说，模式与数据库是同义的。你可以在MySQL的SQL语法中用关键字SCHEMA替代DATABASE，例如使用CREATE SCHEMA来代替CREATE DATABASE。

DBMS的标准结构包含三层模式和两层映像
External schema —局部数据描述
Conceptial schema —数据之间的内在本质联系
Internal schema

E-C Mapping 外模式映射为概念模式
C-I Mapping 概念模式映射为内在模式

1 选择优化的数据类型

MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。
有以下几个简单原则有助于做出更好的选择。

更小的通常最好
一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小数据类型占用更少的磁盘、内存和CPU缓存。但是要确保没有低估需要存储的值得范围。

简单就好
简单的数据类型的操作通常需要更少的CPU周期。

尽量避免NULL
NULL是列的默认属性，通常最好指定列为NOT NULL，除非真的要存储NULL，因为查询中包含NULL的列，对MySQL来说更难优化，NULL的列索引，索引统计和值都比较复杂。前面说的是创建阶段，但是如果在调优阶段就没必要将NULL改为NOT NULL了，这样，除非确定NULL会带来问题。因为将NULL的列改为NOT NULL 带来的性能提升很小。

1.1 整数类型

数字分为：整数和实数
存储整数的几种数据类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。分别使用8,16,24,32,64位存储空间。

整数类型有可选的UNSIGNED属性，表示非负值。

MySQL可以为整数型指定宽度，如INT(11),它不会改变值的合法范围，是用来在交互工具中显示字符的个数。

1.2 实数类型

MySQL既支持精确类型，也支持不精确类型。
FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算，
float 单精度浮点数在机内占 4 个字节，用 32 位二进制描述。
double 双精度浮点数在机内占 8 个字节，用 64 位二进制描述。

DECIMAL类型用于存储精确小数，
1、CPU不支持对DECIMAL的直接计算，CPU直接支持原生浮点计算，所以浮点运算明显更快。
2、浮点和DDECIMAL都可以指定精度，对于DECIMAL列，可以指定小数点前后允许的最大位数。
3、DECIMAL只是一种存储格式，在计算时DECIMAL会转换为DOUBLE类型

1.3 字符串类型

VARCHAR 和 CHAR
VARCHAR
1、VARCHAR类型用于存储可变长的字符串，是最常见的字符串数据类型，比定长类型更节省空间。
2、VARCHAR需要使用额外一个或两个字节记录字符串的长度；如果列的最大长度小于或等于255字节，则使用一个字节，否则使用两个字节。
3、由于行是变长的，在更新时可能比原来更长，而页内没有更多的空间可以存储。此时MyISAM会将行拆成不同的片段，InnoDB则需要分裂页来使行可以放进页内。、

CHAR
1、CHAR类型是定长的，适合存储很短的字符串或者所有值都接近同一个长度。对于需要经常更新的数据，CHAR也比VARCHAR更好

慷慨是不明智的：最好的策略是只分配真正需要的空间。

BLOB和TEXT类型
BLOB和TEXT是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

与其他类型不同，MySQL把每个BLOB和TEXT值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当BLOB和TEXT值太大时，InnoDB会使用专门的存储区域来进行存储，此时每个值在行内存储一个指针，然后在玩不存储区域存储实际的值。

BLOB和TEXT不同的是，BLOB“类型存储的是二进制数据没有排序规则或字符集，而TEXT有。
MySQL对BLOB和TEXT列进行排序与其他类型不同，它只对列的最前max_sort_length字节而不是整个字符串做排序。

使用枚举（ENUM）代替字符串
枚举列·可以把一些不重复的字符串存储成一个预定义的集合。MySQL在内部将每个值在列表中的位置保存为整数，并且在表的 .frm文件保存”数字-字符串“映射关系的”查找表“。

1.4 日期和时间类型

DATATIME：能保存大范围的值，从1001年到9999年，精度为秒，使用了8个字节的存储空间。不包含时区。
TIMESTAMP：时间戳， 保存了格林尼治标准时间以来的秒数，他和UNIX时间戳相同。
使用了四个字节的存储空间。包含时区。
MySQL提供了FROM_UNIXTIME()函数将UNIX时间戳转化为DATETIME，UNIX_STAMP()函数将DATETIME转化为UNIX时间戳。

除特殊行为外，通常情况下应该尽量使用UNIX时间戳，因为它比DATETIME空间效率更高。

1.5 位数据类型

位数据类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型。
BIT BIT列在一列存储一个或多个true/false值。
SET：如果需要保存很多true/false值，可以考虑合并这些列到一个set数据类型，它再MySQL内部以一系列打包的位的集合来表示的，缺点在于改变列的定义代价过高。

1.6 选择标识符（identifier）

为标识列选择合适的数据类型非常重要。一般来说更有可能使用标识列与其他值进行比较，如关联操作中，为标识列选择数据类型的时候，应该选择跟关联表中对应列一样的数据类型。相同的数据类型关联时查找效率更高。
通常情况下整数通常是标识列最好的选择，因为他们很快并且可以使用AUTO_INCREMENT.

2 MySQL schema设计中的陷阱

1、太多的列
MySQL的存储引擎API工作时需要在服务层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中编码过的列转换成行数据结构的操作代价非常高。MySQL中定长结构不需要转换，但是变长结构和InnoDB的行结构则总是需要转换。转换的代价依赖于列的数量。

2、太多的关联
一个粗略的经验法则：如果希望查询执行的快速且并发性好，单个查询最好在12个表以内做关联。
MySQL限制每个关联操作最多只能有61个表。

3、过度使用枚举

4、非此发明的NULL
通常情况系避免使用NULL是比较好的，但是遵循这个原则不要走极端，当确实需要表示未知值也不要害怕使用NULL。

3 范式与非范式

3.1 范式的优点和缺点

当为性能问题寻求帮助时，对schema进行范式化设计，尤其是写密集的场景，是比较的选择。
1、范式化的更新操作通常比反范式画要快
2、当数据较好地进行范式化，就只有很少或者没有重复数据，所以只需要修改很少的数据。
3、范式化的表更小，可以更好地放在内存，执行操作更快。
4、很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句

缺点是，范式化设计的schema是通常需要关联。

3.2 反范式的优点和缺点

反范式化的schema因为所有数据都在一张表中，可以很好地避免关联。

如果不需要关联表，则对大部分查询最差的情况—及时表没有使用索引—是全表扫描。当数据比内存大时可能比关联要快很多。

3.3 混用范式化和反范式化

最常见的反范式数据的方法是复制或者缓存，在不同的表中存储相同的特定列。

4 缓存表和汇总表

“缓存表”：表示存储那些可以比较简单地从schema其他表获取数据的表。
“汇总表”：保存的是使用GROUP BY语句聚合数据的表。

在使用缓存表和汇总表时，必须决定是实时维护数据还是定期重建。定期重建并不只是节省资源，也可以保持表不会有很多碎片，以及完全顺序组织的索引。

4.1 物化视图

物化视图是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新。
物化视图是相对于普通视图来说的。
普通视图是虚拟表，应用局限大，任何对视图的操作，实际上都被转化为视图SQL语句的查询。这样对整体查询性能的提高，并没有实质上的好处。物化视图实际是上一种表（实际存在），会占用数据库磁盘空间，有其自己的物理存储属性。
MySQL并不原生支持物化视图，然而我们可以使用Justin Swanhart 开源工具Flecviews自己实现物化视图。

Flexviews由以下三个部分组成：
1、变更数据抓取功能，可以读取服务器的二进制日志并且解析相关行的变更。
2、一系列可以帮助创建和管理视图的定义的存储过程。
3、一些可以应用变更到数据库中的物化视图的工具。

Flexviews通过提取对源表的更改，可以增量地重新计算物化视图的内容。这意味着不需要查询原始数据来更新视图。基于二进制日志包含行更新前后的镜像，所以Flexviews不仅仅可以获得每行的新值，还可以不需要查找源表就能知道每行数据的旧版本。计算增量数据比从源表中读取数据的效率要高很多。

4.2 计数器表

计数器表在web应用中很常见，可以用这种表缓存一个用户的朋友数、文件下载次数等。
通常情况下创建一张独立的表存储计数器是个好主意，这样可以使计数器小且快，可以帮助避免查询缓存失效。

为了提高事务的并发更新功能，可以将计数器保存在多行，任意一个事务随机选择一行进行更新，最后要获得查询结果，可以使用聚合查询SUM()

5 加快ALTER TABLE操作的速度

MySQL的 ALTER TABLE 操作的性能对大表来说是个大问题。MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表查出所有数据插入新表，然后删除旧表。这样的操作可能需要花费很长时间。

5.1 只修改 .frm文件

不是所有的ALTER TABLE操作都会引起表重建。例如需要修改某列的默认值，列的默认值实际上存储在表的 .frm文件中，所以可以直接修改这个文件而不需要改动表本身。
所有的 MODIFY COLUMN操作都会导致表重建，而通过ALTER COLUMN会直接修改 .frm文件而不涉及表数据。

5.2 快速创建MyISAM索引

为了高效地载入数据到MyISAM表中，有一个常用的技巧是先禁用索引、载入数据，然后在重新启用索引。这个技巧能够发挥作用的原因在于，构件索引的工作被延迟到数据完全载入之后，这个时候已经可以通过排序来构建索引。

6 总结

尽可能保持任何东西小而简单总是好的
1、尽量避免过度设计
2、使用小而简单的合适数据类型，除非真是数据模型中确切的需要，否则应该尽可能地避免使用NULL值。
3、尽量使用相同的数据类型来存储相似或者相关的值，尤其是在关联表中。
4、注意可变长字符串，其在临时表和排序时可能导致悲观地按最大长度分配内存。
5、尽量使用整形定义标识列（自动增列）
6、避免使用MySQL已经遗弃的特性，例如浮点数的精度。
7、小心使用枚举和SET。

参考文献：高性能MySQL