高性能MySQL-Schema与数据类型优化

本章覆盖了MySQL特有的schema设计方面的主题

1.选择优化的数据类型

MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。
更小的通常更好
要确保没有低估需要存储的值的范围，因为在schema中的多个地方增加数据类型的范围是非常耗时的过程。如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型。
尽量避免NULL
通常情况下，最好指定列为NOT NULL。
当然，在为列选择具体的数据类型时，第一步需要确定合适的大类型（数字、字符串、时间等）下一步是选择具体类型，很多MySQL的数据类型都可以存储相同类型的数据，只是存储的范围和精度不一样（例如datetime以及timestamp）

1.1整数类型

tinyint(8)、smallint(16)、mediumint(24)、int(32)、bigint(64)
整数类型有可选的unsigned属性，表示不允许负值，这大致可以使正数的上限提高一倍。
有符号和无符号类型使用相同的存储空间，并具有相同的性能。
整数计算一般使用64位的bigint整数，即使在32位的环境也是如此。
另外，MySQL可以为整数类型指定宽度，例如int(11)，对大多数应用来说这是一项没有意义的工作，它不会限制值的合法范围，只是规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。

1.2实数类型

实数是带有小数部分的数字，主要包括float以及double，当然，它们属于非精确数字(容易产生误差)。
如果要存储精确数字（比如money）,建议使用decimal(定点型：以字符串的形式保存数值)。

1.3字符串类型

varchar类型用语存储可变长字符串，需要使用1或者2个额外字节记录字符串的长度。varchar节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在update时可能使行变得比原来更长，这就导致需要做许多额外的工作。
char类型是定长的，MySQL总是根据定义的字符传长度分配足够的空间，且会删除所有的末尾空格。char适合存储很短的字符串，或者所有值都接近同一个长度。
与char和varchar类似的类型还有binary以及varbinary,它们存储的是二进制字符串。但是二进制字符串存储的是字节码而不是字符。填充也不一样：MySQL填充binary采用的是\0而不是空格，在检索时不会去掉填充值。
blob和text都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。实际上，它们分别属于两组不同的数据类型的家族：字符类型是tinytext、smalltext、text、mediumtext以及longtext，对应的二进制分别以blob为后缀。与其他类型不同，MySQL把每个blob和text值当作一个独立的对象处理。还需要注意：blob和text家族之间仅有的不同是blob类型存储的是二进制数据，没有排序规则和字符集，而text有排序规则和字符集。
enum(枚举)，枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举列时非常紧凑，会根据列表值的数量压缩到一个或者两个字节中。MySQL会在内部将每个值在列表中的位置保存为整数，并且在表的.firm文件中保存“数字-字符串”映射关系的“查找表”。下面有一个例子：

create table enum_table(e enum('fish','apple','dog')not null);
insert into enum_table(e)values('fish'),('dog'),('apple');

这三行数据实际存储为整数，而不是字符串。即存储的为132。
而且需要注意的是，枚举字段是按照内部存储的整数而不是字符串进行排序的。

select e from enum_test order by e;

显示的是 fish-apple-dog。
可以在查询时使用field()函数显式地指定排序顺序。

select e from enum_test order by field(e,'apple','dog','fish');

显示的是 apple-dog-fish。
使用枚举可以使关联变得更快并且可以使表的大小变得更小。

1.4日期和时间类型

MySQL能存储的最小时间粒度为秒。MySQL提供两种功能相似的日期类型：datetime以及timestamp
首先是datetime:这个类型能保存大范围的值，从1001年到9999年，精度为秒。他把日期和时间封装到格式为为YYYYMMDDHHMMSS的整数中，与时区无关。
timestamp:只能表示1970年到2038年，显示的值也依赖于时区。
除特殊行为外，通常应该尽量使用timestamp，因为它比datetime空间效率更高。

1.5位数据类型

bit 可以使用bit列在一列中存储一个或多个true/false值。bit(1)定义一个包含单个位的字段，bit(2)存储两个位，以此类推。bit列的最大长度是64位。bit的行为因存储引擎而异。MyISAM会打包存储所有的bit列，所以17个单独的bit列只需要17个位存储，这样MyISAM只使用3个字节就能存储3这17个列了。InnoDB为每一个bit列使用一个足够存储的最小整数类型来存放，所以不能节省存储空间。MySQL把bit当作字符串类型，而不是数字类型。
set如果要保存很多true/false值，可以考虑合并这些列到一个set数据类型，它在MySQL内部是以一系列打包的位的集合来表示的。

1.6选择标识符

标识列又称自增长列，可以不用手动地插入值，系统提供默认的序列值。
整数通常是标识列的最好选择，因为它们很快并且可以使用auto_increment。

1.7IPV4

人们通常使用varchar(15)即3*4+3列来存储IP地址。然而，它实际上是32位无符号整数，所以应该用无符号整数存储IP地址。

2.范式与反范式

如果不熟悉范式，我们可以先学习一下。简单来说，MySQL的范式就是将一个复杂的表拆分成一个个简单的表。在范式化的数据库中，每个实施数据会出现 并且只出现一次。相反，在反范式化的数据库中，信息是冗余的，可能会存储在多个地方。

employee	department	head
Jones	Accounting	Jones
Smith	Engineering	Smith
Brown	Accounting	Jones
Green	Engineering	Smith

这个schema的问题是修改数据时可能发生不一致。假如Brown接任了Accounting部门的领导，需要修改多行数据来反映这个变化，如果Jones这一行显示的部门领导和Brown这一行的不一样，这样就没办法知道哪一行是对的。此外，这个设计在没有雇员的情况下就无法表示一个部门-如果我们删除了所有Accounting部门的雇员，我们就失去了关于这个部门本身的所有记录。为了避免这个问题，我们需要对这个表进行范式化，方式就是拆分雇员和部门项。拆分之后可以用下面两张表分别存储雇员表：

employee_name	department
Jones	Accounting
Smith	Engineering
Brown	Accounting
Green	Engineering

和部门表：

department	head
Accounting	Jones
Engineering	Smith

2.1 范式的优点与缺点

当为性能问题而寻求帮助时，经常会被建议对schema进行范式化设计，尤其是写密集的场景时。
范式化的更新操作通常比反范式更快。当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要有更少的数据。范式化的表通常更小，执行操作会更快。
范式化设计的schema的缺点是通常需要关联。

2.2 反范式化的优点与缺点

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

2.3 混用范式化和反范式化

事实是，完全的范式化和完全的反范式化schema都是实验室里才有的东西，在实际应用中经常需要混用。

3.缓存表与汇总表

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然而，有时也需要创建一张完全独立的汇总表或缓存表（特别是为满足检索的需求时）。
缓存表来表示存储那些可以比较简单地从schema其他表获取数据的表。
汇总表则保存的是使用group by语句聚合数据的表。

3.1 物化视图

物化视图实际上是预先计算并且存储在磁盘上的表（先给个大致结果，后面的慢慢算）。

3.2 计数器表

如果应用在表中保存计数器，则在更新计数器时可能碰到并发问题。计数器表在Web应用中很常见。可以用这种表缓存一个用户的朋友数以及文件下载次数等。创建一张独立的表存储计数器是一个好主意。一般为了保证更高的并发更新性能（因为涉及到一个全局的互斥锁的原因，导致更新这些事务只能串行进行），可以将计数器保存在多行之中，再进行全局聚合。

4.alter table操作

MySQL执行大部分修改表结构操作的方法使用新的结构创建一个新表，从旧表中查出所有数据插入新表，然后删除旧表。
我们可以先在一台不提供服务的机器上执行alter table操作，然后和提供服务的主库进行切换。另外一种技巧是“影子拷贝”。影子拷贝的技巧是用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换这两张表。