第4章，Schema与数据类型优化
4.1 选择优化的数据类型
一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。
很多表都包含可为NULL（空值）的列，即使应用程序并不需要保存NULL也是如此，这是因为可为NULL是列的默认属性。通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。
如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使会使用更多的存储空间，在MySQL里也需要特殊处理。
这是设计数据表结构应该遵守的两个规则，能够一定程度提升性能或简化程序的逻辑。
4.1.1 整数类型
你的选择决定MySQL是怎么在内存和磁盘中保存数据的。然而，整数计算一般使用64位的BIGINT整数，即使在32位环境也是如此。（一些聚合函数是例外，它们使用DECIMAL或DOUBLE进行计算）。
MySQL可以为整数类型指定宽度，例如INT（11），对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，INT（1）和INT（20）是相同的。
类型TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间，并不能通过指定宽度而改变存储空间的大小。
4.1.2 实数类型
浮点和DECIMAL类型都可以指定精度。对于DECIMAL列，可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例如，DECIMAL（18,9）小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节。
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。但在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。
‘在数据量比较大的时候，可以考虑使用BITINT代替DECIMAL’，这里有一个前提，就是在数据量比较大的情况，因为BITINT一般使用64位，也就是8个字节。如果使用BITING表示比较小的数，那么使用DECIMAL表示此数字可能所占用的空间更小一些，所以需要特别注意。
4.1.3 字符串类型
这些情况下使用VARCHAR是合适的：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
知道在什么情况下使用VARCHAR很重要，因为使用VARCHAR类型的机会比较多。
CHAR适合存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR（1）来存储只有Y和N的值，如果采用单字节字符集(5)只需要一个字节，但是VARCHAR（1）却需要两个字节，因为还有一个记录长度的额外字节。
其中一些观点，比如对于经常变更的数据，CHAR更好，因为不易产生碎片，这悖于我之前的认识，但是仔细想想也是合理。
更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。
这个要知道，当MYSQL使用内存临时表进行排序或操作，相对于数据表来说会占用更大的内存空间，比如某列的类型为VARCHAR(100)，但是存储的值都是10个字符上下。那么在使用临时表排序或其他操作时，MYSQL将会分配100字符的大小的空间来存储这个10个左右的字符。
使用枚举（ENUM）代替字符串类型
枚举列按照枚举定义顺序排序，一种绕过这种限制的方式是按照需要的顺序来定义枚举列。另外也可以在查询中使用FIELD()函数显式地指定排序顺序，但这会导致MySQL无法利用索引消除排序。
枚举最不好的地方是，字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE。因此，对于一系列未来可能会改变的字符串，使用枚举不是一个好主意，除非能接受只在列表末尾添加元素，这样在MySQL5.1中就可以不用重建整个表来完成修改。
了解枚举类型的优缺点，才能更好的使用此种类型。
TIMESTAMP
TIMESTAMP也有DATETIME没有的特殊属性。默认情况下，如果插入时没有指定第一个TIMESTAMP列的值，MySQL则设置这个列的值为当前时间。在修改一行记录时，MySQL默认也会更新第一个TIMESTAMP列的值（除非在UPDATE语句中明确指定了值）。你可以配置任何TIMESTAMP列的插入和更新行为。最后，TIMESTAMP列默认为NOT NULL，这也和其他的数据类型不一样。
除了特殊行为之外，通常也应该尽量使用TIMESTAMP，因为它比DATETIME空间效率更高。
因为TIMESTAMP只需4个字节的存储空间，而DATETIME需要8字节的存储空间，这样空间利用效率更好。再加上TIMESTAMP还有一些其他的特征，所以一般采用TIMESTAMP类型。
如果需要存储比秒更小粒度的日期和时间值怎么办？MySQL目前没有提供合适的数据类型，但是可以使用自己的存储格式：可以使用BIGINT类型存储微秒级别的时间截，或者使用DOUBLE存储秒之后的小数部分。这两种方式都可以，或者也可以使用MariaDB替代MySQL。
记录在这里，以后遇到此类场景，可以参考使用。
4.1.6 选择标识符（identifier）
一旦选定了一种类型，要确保在所有关联表中都使用同样的类型。类型之间需要精确匹配，包括像UNSIGNED这样的属性。混用不同数据类型可能导致性能问题，即使没有性能影响，在比较操作时隐式类型转换也可能导致很难发现的错误。 整数通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREMENT。
标识符最好选用整数，如果不选择整数，也要确保选择的类型尽量简单，且与外键的类型以及属性保持一致，防止可能的性能问题以及隐式的类型转换。
4.1.7 特殊类型数据
另一个例子是一个IPv4地址。人们经常使用VARCHAR（15）列来存储IP地址。然而，它们实际上是32位无符号整数，不是字符串。用小数点将地址分成四段的表示方法只是为了让人们阅读容易。所以应该用无符号整数存储IP地址。MySQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换。
SELECT INET_ATON(‘10.0.81.142’);
SELECT INET_NTOA(167793038);
如何高效存储IP地址的方法。
4.2 MySQLschema设计中的陷阱
MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。MyISAM的定长行结构实际上与服务器层的行结构正好匹配，所以不需要转换。然而，MyISAM的变长行结构和InnoDB的行结构则总是需要转换。转换的代价依赖于列的数量。
这就是为什么读取多个列影响性能的原因。
4.5 加快ALTER TABLE操作的速度
MySQL的ALTER TABLE操作的性能对大表来说是个大问题。MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。
大部分ALTER TABLE操作将导致MySQL服务中断。我们会展示一些在DDL操作时有用的技巧，但这是针对一些特殊的场景而言的。对常见的场景，能使用的技巧只有两种：一种是先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换；另外一种技巧是“影子拷贝”。影子拷贝的技巧是用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。
对于ALTER TABLE操作，上面提供了两个方法，对于方法一，操作起来可能要复杂一些，假设你在备库上执行ALTER TABLE操作，当次操作执行完毕之后，你需要停止主库的写入服务，并等待备库完成数据的同步，之后再完成主备库的切换。而方法二，有一些工具帮助来实现，如果自己手动去做，也将面对一些问题，如何完整的复制数据，因为旧表也会有新的数据插入和记录的更新。
4.5.1 只修改.frm文件
基本的技术是为想要的表结构创建一个新的.frm文件，然后用它替换掉已经存在的那张表的.frm文件，像下面这样：
1，创建一张有相同结构的空表，并进行所需要的修改（例如增加ENUM常量）。
2，执行FLUSH TABLES WITH READ LOCK。这将会关闭所有正在使用的表，并且禁止任何表被打开。
3，交换.frm文件。
4，执行UNLOCK TABLES来释放第2步的读锁。
这种方法比较简单，但是需要承担一定的风险，这并不是文档化的方法，需要着这样做之前先对数据进行备份，另外此类表结构的修改需要兼容于现有的数据，最好应用到线上环境之前现在测试或开发环境测试一下。