4-Schema与数据库类型优化

4.1 选择优化的数据类型

DATETIME和TIMESTAMP列都可以存储相同类型的数据：时间和日期，精确到秒，然而TIMESTAMP只使用DATATIME一半的存储空间并且会根据时区变化，具有特殊的自动更新能力。另一方面，TIMESTAMP允许的时间范围要小的多，有时候它的特殊能力会成为障碍。

4.1.3 字符串类型

CREATE TABLE char_test(char_col CHAR(10));
INSERT INTO char_test(char_col ) VALUES ('string1'),(' string2'),('string3 ') ;
SELECT CONCAT("'",char_col,"'") FROM char_test;

concat("'",char_col,"'")  
--------------------------
'string1'                 
' string2'                
'string3'

string3的末尾空格被截断了。

CREATE TABLE varchar_test(varchar_col VARCHAR(10));
INSERT INTO varchar_test(varchar_col ) VALUES ('string1'),(' string2'),('string3 ') ;

SELECT CONCAT("'",varchar_col,"'") FROM varchar_test;

使用VARCHAR(10)字段存储相同的值，没有被截断。

慷慨是不明智的 使用VARCHAR(5)和VARCHAR(200)存储’hello’的空间开销是一样的。那么使用更短的列有什么优势？ 事实证明有很大的优势。更长的列会消耗更多的内存，因为MySQL通常会分配固定大小内存来保证内部值，尤其是使用内存临时表进行排序操作时特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。 最好的策略是只分配真正需要的空间。

BLOB和TEXT类型

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

BLOB和TEXT家族之间仅有的不同时BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT存储类型有字符集和排序规则。
MySQL对BLOB和TEXT列进行排序与其他类型是不同的：它只对每个最前max_sort_length字节而不是整个字符串做排序。如果只排序前面一小部分字符，则可减少max_sort_length位置。

使用枚举替代字符串类型

CREATE TABLE enum_test( e ENUM('fish','apple','dog')  NOT NULL);
INSERT INTO  enum_test(e) VALUES('fish'),('dog'),('apple');
SELECT  e+0 FROM enum_test ;
SELECT e FROM enum_test ORDER BY e ;

使用数字作为枚举常量，容易导致混乱。
枚举字段是按照存储的整数而不是按照定义的字符串进行排序的。

一种绕过这种限制的方式是按照需要的顺序来定义枚举列，另外也可以在查询中使用FIELE函数显示地指定排序顺序，但这种会导致MySQL无法利用索引消除排序。

4.1.4 日期和时间类型

MySQL能存储的最小时间粒度为秒(MariaDB支持微妙级别时间类型)。但是MySQL可以使用微妙级的粒度进行临时运算，
MySQL提供了两种相似的日期类型：DATETIME和TIMESTAMP。对于很多应用程序，它们都能工作，但是在某些场景，一个比另一个工作得好。

DATETIME

这个类型能保持大范围的值，从1001到9999年，精度为秒，它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。
默认情况下，MySQL以一种可排序的、无歧义的格式显示DATETIME值，例如“2008-01-16 22:37:08”。这是ANSI标准定义的日期和时间表示方法。

TIMESTAMP

TIMESTAMP类型保持了1970年1月1日午夜以来的秒，它和UNIX时间戳相同。TIMESTAMP只使用了4个字节的存储空间，因此它的范围比DATETIME小得多，只能表示1970年到2038年。MySQL提供了FROM_UNIXTIME()函数把Unix时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数把日期转换成Unix时间戳。

TIMESTAMP显示的值也依赖于时区，MySQL服务器，操作系统，以及客户端连接都有时区设置。
TIMESTAMP也有DATETIME没有的特殊属性，默认情况下，如果插入时没有指定第一个TIMESTAMP列的值，MySQL则设置整个列的值为当前时间。在插入一行记录时，MySQL默认也会更新第一个TIMESTAMP列的值。可以配置任何TIMESTAMP列的插入和更新行为，最后TIMESTAMP列默认为NOT NULL，这也和其他的数据类型不一样。
除了特殊行为之外，通常也应该尽量使用TIMESTAMP，因为它比DATETIME空间效率更高。

mysql5.6 开始支持毫秒。

4.5 加快ALTER TABLE操作的速度

MySQL的ALTER TABLE操作的性能对大表来说是个问题。MySQL执行大部分修改表结构曹组的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这样操作可能花费很长时间，入骨哦内存不足而表很大，而且还有很多索引的情况下尤其如此。许多人都有这样的经验，ALTER TABLE操作需要花费数个小时甚至数天才能完成。
MySQL5.1以及更新版本包含一些类型的“在线”操作的支持，这些功能不需要在整个操作过程中锁表。最新版本的InnoDB也支持通过排序来建索引，这使得索引更快并且有一个紧凑的索引布局。

不是所有的ALTER TABLE 操作都会引起表重建。例如，有两种方法可以修改或删除一个列的默认值(一种方法很快，另一种则很慢)。假如要修改电影的默认租赁期限，从三天改到5天。下面是很慢的方式。

ALTER TABLE sakila.`film`
MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

shou status显示这个语句做了1000次读和1000次插入操作。换句话说，它拷贝了整张表到一张新表，甚至列的类型，大小和可否为null属性都没有改变。

理论上，MySQL可以跳过创建新表的步骤，列的默认值实际上存在表的.frm文件中。所以直接修改这个文件而不需要改动表本身。然而MySQL还没有采用这种优化的方法。所有的MODIFY COLUMN操作都将导致表重建。
另外一种方法通过 ALTER COLUMN 操作来改变默认值

ALTER TABLE sakila.film ALTER COLUMN rental_duration SET  DEFAULT 5;

这个语句会直接修改.frm文件而不设计表数据，所以这个操作非常快。

4.5.1 只修改.frm文件

修改表的.frm文件是很快的，但MySQL有时候会在没有必要的时候也重建表。如果愿意冒一些风险，可以让MySQL做一些其他类型的修改而不用重建表。

下面的操作不受官方支持。也没有文档记录，冰爷也可能不正常工作，采用这些技术是需要承担风险。

下面这些操作是有可能不需要重新建表的。

• 移除(不是增加) 一个列的AUTO_INCREMENT属性
• 增加、移除、或更改ENUM和SET常量。如果移除的是已经有行数据用到其值的常量，查询将会返回一个空字符串值。

4.5.2 快速创建MyISAM索引

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

这个技巧能够发挥作用，是因为构建索引的额工作被延迟到数据完全载入以后，这个时候已经已经可以通过排序来构建索引。这样做会快很多，并且使得索引树的碎片更少。
不幸的是，这个办法对唯一索引无效。因为DISABLE KEYS只对非唯一索引有效。MyISAM会在内存中构造唯一索引，并且为载入的每一行检查唯一性。一旦索引的大小操作了有效内存大小，载入操作就会变得越来越慢。