第四章 Schema与数据类型优化（重要）

一.选择优化的数据类型

下面几个原则有助于作出更好选择：
（1）选择更小的
尽量使用可以正确存储数据的最小数据类型。
但是要确保没有低估需要存储的值的范围。
（2）简单数据类型就好
简单数据类型的操作通常需要更少的CPU周期。
这里有两个例子：
应该使用MySQL内建的类型而不是字符串来存储日期和时间。
应该用整型存储IP地址。
（3）尽量避免NULL
MySQL对包含NULL的查询更难优化，因为这使得索引更复杂。
可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。
当可为NULL的列被索引时，每个索引记录需要一个额外的字节。

1.整数类型

有以下几种整数类型：
TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT，分别使用8,17,24,32,64位存储空间。
MySQL可为整数类型指定宽度，例如INT(11)，这对大多数应用是没有意义的：它不会限制值的合法范围，只是规定了一些交互工具用来显示字符的个数。

2.实数类型

实数是带有小数部分的数字。
DECIMAL类型用于存储精确的小数。
浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE占用8个字节。
因为需要额外的空间和计算开销，尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。在数据量比较大的时候，考虑使用BIGINT代替DECIMAL，将需要存储的单位根据小数的位数乘以相应的倍数即可。

3.字符串类型

（1）VARCHAR和CHAR类型

VARCHAR
VARCHAR类型用于存储可变长字符串。
比定长类型更节省空间。
需要使用1或2个额外字节记录字符串的长度。

CHAR
CHAR类型是定长的。
CHAR适合存储很短的字符串，或者所有值都接近同一个长度。例如，存储密码的MD5值。

慷慨是不明智的
使用VARCHAR(5)和VARCHAR(200)存储’hello’的空间开销是一样的，那么，使用更短的列有什么优势吗？

有很大的优势！
更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内布置。
最好的策略是只分配真正需要的空间。

（2）BLOB和TEXT类型

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。
与其他类型不同，Mysql把每个BLOB和TEXT当作一个独立的对象处理。

（3）使用枚举（ENUM）代替字符串类型

MYSQL在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或者两个字节中。

4.日期和时间类型

MYSQL能存储的最小时间粒度为秒（MariaDB支持微秒级别的时间类型）。
MYSQL提供两种相似的日期类型：DATETIME和TIMESTAMP。
DATETIME
能保存大范围的值，从1001年到9999年，精度为秒。
格式为YYYYMMDDHHMMSS。
使用8个字节的存储空间。
TIMESTAMP
保存从1970年1月1日午夜以来的秒数。
只使用4个字节的存储空间。
表示从1970年到2038年。

除了特殊行为之外，应该尽量使用TIMESTAMP，因为它比DATETIME空间效率更高。
如果要存储比秒更小粒度的日期和时间怎么办？
可以使用BIGINT类型存储微秒级别的时间戳。

5.位数据类型

MYSQL有几种存储类型使用紧凑的位存储数据。这些位类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型。

BIT
可以使用BIT列在一列中存储一个或多个true/false值。
MYSQL把BIT当做字符串类型，而不是数字类型。

SET
如果需要保存很多true/false值，可以考虑合并这些列到一个SET数据类型，它在MYSQL内部是以一系列打包的位的集合来表示的。

6.选择标识符（identifier）

为标识列（identifier column）选择合适的数据类型非常重要。
一旦选择了一种类型，要确保在所有关联表中都使用同样的类型。

下面是一些小技巧：
整数类型
整数通常是标识列最好的选择。
ENUM和SET类型
糟糕。
字符串类型
尽量避免。

二.MYSQL schema设计中的陷阱

1.太多的列

MYSQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。这个操作的代价是非常高的。

2.太多的关联

如果希望查询执行得快且并发性好，单个查询最好在12个表以内做关联。

3.防止过度使用枚举

在MYSQL中，需要在枚举中增加一个新的值时需要做一次ALTER TABLE操作。

四.范式和反范式

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

1.范式的优点和缺点

优点
（1）范式化的更新操作通常较快
（2）很少或者没有重复数据，所以只需要修改更少的数据
（3）范式化的表通常更小，可以更好的放在内存里，执行操作会更快

缺点
通常需要关联。

2.反范式的优点和缺点

优点
（1）反范式化的schema因为所有数据都在一张表中，可以很好地避免关联。
（2）单独的表也能使用更有效的索引策略。

3.混用范式化与反范式化

实际应用中经常需要混用。

四.缓存表和汇总表

缓存表表示存储哪些可以比较简单地从schema其他表获取（但是每次获取的速度比较慢）数据的表（例如，逻辑上冗余的数据）。
汇总表指保存的是使用GROUP BY语句聚合数据的表。

1.物化视图

物化视图是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新。

2.计数器表

如果应用在表中保存计数器，则在更新计数器时可能碰到并发问题。

五.加快ALTER TABLE操作的速度

ALTER TABLE操作的性能对于大表来说是个大问题。
MYSQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。

一般而言，大部分ALTER TABLE操作将导致MYSQL服务中断。
能使用的技巧有两种：
（1）先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换
（2）影子拷贝：用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。

1.只修改.frm文件

理论上，MYSQL可以跳过创建新表的步骤，列的默认值实际上存在表的.frm文件中，所以可以直接修改这个文件而不需要改动表本身。

2.快速创建MYISAM索引

为了高效地载入数据到MYISAM表中，有一个常用技巧是先禁用索引、载入数据，然后重新启用索引。

六.总结

1.尽量避免过度设计
2.使用小而简单的合适数据类型
3.尽量使用相同数据类型存储相似或相关的值
4.注意可变长字符串
5.尽量使用整型定义标识列