第四章 Schema与数据类型优化

一、选择优化的数据类型

一般情况下，应该尽量使用可以正确存储数据的最小数据类型。但是要确保没有低估需要存储的值的范围，因为在schema中的多个地方增加数据类型的范围是一个非常耗时的操作。
在使用字符类型时需要考虑其校对规则（排序规则）。
尽量避免使用NULL，因为可以为NULL的列会使用更多的存储空间，当它被索引时，每个索引的记录需要一个额外的字节用于记录它是否为NULL。

二、整数类型

MySQL可以为整数类型指定宽度，对于大多数应用这是没有意义的，它不会限制值的合法范围，只是规定了MySQL的一些交互工具（如命令行客户端）用来显示字符的个数。

三、实数类型

实数是带小数部分的数字，MySQL即支持精确类型，也支持不精确类型。FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算；DECIMAL类型用于存储精确的小数。

因为需要额外的控件和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL--如存储财务数据。但在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数位数乘以相应的倍数即可。

四、字符串类型

字符串定义的长度不是字节数，如varchar(10)，定义的是字符长度为10的varchar类型。
varchar类型用于存储可变长字符串，它需要使用1或2个额外的字节记录字符串的长度。
由于varchar是变长的，在update时，可能新的值比原先的长，从而使得新的行比原来的行长，此时导致需要做一些额外的操作，不同的存储引擎处理方式不一样，InnoDB会通过分裂页的方式来使行可以放进页内（产生碎片）。
下面这些情况适合使用varchar：
a. 字符串列的最大长度比平均长度大很多；
b. 列的更新很少，减少碎片的产生。
c. 使用了像UTF-8这样复杂的字符集，每个字符都使用了不同的字节数进行存储。
char类型是定长的，Mysql总是根据定义的字符串长度分配足够的空间。char适合存储很短的字符串，或者所有值都接近同一个长度。对于经常变更的数据，char也比varchar更好，因为定长不易产生碎片
慷慨是不明智的，使用varchar(5)和varchar(200)存储‘hello’的空间开销是一样的，但是在实际使用时，我们应该按照实际的存储长度来定义，因为Mysql在运行时，更长的列会消耗更多的存储内存。Mysql通常会分配固定大小的内存块来保存内部值，尤其是使用内存临时表进行排序操作时使用更长的列会显得特别糟糕。
BLOB和TEXT类型
a. Mysql把每个BLOB和TEXT值当作一个独立的对象处理，存储引擎在存储是通常会做特殊处理
b. BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值
c. BLOB类型存储的是二进制数据，没有排序规则或字符集
d. Mysql只对每个TEXT列的最前max_sort_length字节而不是整个字符串做排序
e. Mysql不能将BLOB和TEXT列全部长度的字符串进行索引。

四、其他类型

使用枚举类型时要注意，枚举字段是按照内部存储的证书而不是定义的字符串进行排序的。枚举最不好的地方是，字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE，而在旧版本的MySQL中，该操作是会锁表的。
日期和时间类型
a. DATETIME精度为秒，使用8个字节的存储。
b. TIMESTAMP使用4个字节存储。默认情况下，如果插入数据时，没有指定TIMESTAMP列的值，MySQL则设置这个列的值为当前时间。
d. TIMESTAMP列默认为NOT NULL.
在选择标识列（identifier，主键）的类型时，不仅仅需要考虑存储类型，还需要考虑mysql对这种类型怎么执行计算和比较。通常整数是标识列最好的选择，因为计算快，而且可以使用AUTO_INCREMENT；因该避免使用字符串类型作为标识列，因为它们很消耗空间，并且通常比数字类型慢，MyISAM默认对字符串使用压缩索引，这会导致查询慢很多。
太多的列 Mysql在执行查询操作时需要在执行层和存储引擎层之间通过行缓冲格式拷贝数据，然后在执行层将缓冲的内容解码成各个列，转换的代价依赖于列的数量

五、范式和反范式

在范式化的数据库中，每个事实数据会出现并且只出现一次。相反，在反范式化的数据库中，信息是冗余的，可能会存储在多个地方。

六、加快ALTER TABLE操作的速度

Mysql执行修改表结构的操作方法是用新的结构创建一个空表，从旧表中查处所有数据插入新表，然后删除旧表。
一般而言，大部分ALTER TABLE操作将导致Mysql服务中断。常使用两个技巧来完成相关的修改操作：一种是先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换； 另一种是通过“影子拷贝”，影子拷贝的技巧是用要求的表结构创建一张和源表无关的新表（影子表），然后通过重命名和删表操作交换两张表。
不是所有的ALTER TABLE操作都会引起表重建。例如，有两种方法可以改变或删除一个列的默认值。
eg: ALTER TABLE tb MODIFY COLUMN tb.col_1 TINYINT(3) NOT NULL DEFAULT 5;
列的默认值实际上存在表的.frm文件中，所以理论上可以直接修改这个文件而不需要改动表本身，但是Mysql并没有采用这种优化，所有的MODIFY COLUMN 操作都将导致表重建。
但是通过ALTER COLUMN来操作的话则会直接修改.frm文件而不涉及表数据，所以这个操作非常快。如下所示：
eg: ALTER TABLE tb ALTER COLUMN tb.col_1 TINYINT(3) NOT NULL DEFAULT 5;