本文是阅读《高性能mysql》的笔记，主要内容是原书的第四章 Schema与数据类型优化。
表结构和数据类型优化以下的理论都是基于MYSQL的INNODB存储引擎，其他存储引擎可能有不一样的情况。
字段数据类型选择原则出于设计一个高性能数据表的目的，选择字段的数据类型有以下几个原则。（原则只是从所有业务场景中总结归纳出来的，使用时还是要根据具体业务的具体需求和数据库的扩展需求选用数据类型，不能一昧的遵循原则）
选择更小的数据类型一般情况，尽量选择可以适用于字段的最小的数据类型。更小的数据类型意味着占用更少的磁盘、内存、CPU缓存和CPU时间片。但要确保业务变化不会要求字段改变数据类型，在数据量很大的情况修改数据表的字段类型是非常耗时的操作。
选择简单的数据类型这里所说的简单是指对于各种操作消耗更少的CPU空间的数据类型。比如可以存储为整型时不要存为字符串类型，因为对整型数据的操作对CPU消耗更小，比如IP地址。另一个例子是存储时间时使用data、datatime、timestamp、time，不要用字符串类型，这些类型对于处理时间数据有更好的性能。
整数类型MYSQL定义了5种整数类型，TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。分别占8、16、24、32、64位存储空间。同时MYSQL为整数类型提供了unsigned，即无符号。将整数类型定义为无符号可以提升一倍的非负数范围内的上限，但同时不能表示负数。
实数类型如果需要存储的类型是浮点数一般使用FLOAT和DOUBLE类型（32位和64位），浮点数的运算取决于MYSQL所部属的平台。
对于业务上需要精确浮点数，比如汇率、余额、售价等，不能使用FLOAT和DOUBLE，因为这两个浮点数类型在运算中会出现精度丢失的现象。
精确浮点数必须使用DECIMAL类型，DECIMAL可以声明浮点数的精度，例如 decimal(18,9) 。意为18位浮点数，其中小数点后9位。
精确的浮点数如果小数点后位数确定，也可以转为整数存储。例如余额可以转为以分为单位存储为INT类型，对于存储空间和运算复杂度都有一定的提升。
字符串类型MYSQL主要提供的两种字符串类型是CHAR和VARCHAR。这两种类型的存储方式和所使用的引擎有关。
MYSQL5.0以上版本VARCHAR在插入时会保留字符串末尾的所有空格，而CHAR则会删除末尾的所有空格。
VARCHARvarchar是可变长字符串，定义了一个字段的类型为varchar(10),如果只插入5字节的字符串，那么实际存储的长度会以真实的数据长度为准，长度为5字节。
同时varchar处于实现变长字符串的目的，需要额外的使用1-2个字节来表达字符串长度（长度不大于255使用1个，超过255使用2个）。所以上文的例子varchar(10) 插入5字节字符串，整个列占空间为6字节。
VARCHAR更节省存储空间，同时也提升了性能。但在update时因为空间的变化会产生额外的消耗。所以平均长度远小于最大长度，并且更新操作比较少的字段适合用VARCHAR。
不要因为VARCHAR是变长字符串就设定很大的范围。如果数据大小不超过10字节，就使用varchar(10)，而不要使用varchar(200)。定于过大的范围虽然在存储时占用的磁盘空间不会变多，但是在使用临时表排序时会增大内存和磁盘消耗，降低性能。
CHARCHAR是定长的字符串，如果插入值的长度小于定义的长度，会以空格填充补齐长度。
CHAR适合存储长度基本差不多或者长度一致，修改操作比较多的字段，比如密码的MD5值。
BINARY和VARBINARYBINARY和VARBINARY存储的是二进制字符串，当需要存储二进制数据时最好使用这两种类型，会获得比VARCHAR和CHAR更好的性能。
BLOB和TEXTBLOB和TEXT是被设计来存储很大的数据的。BLOB类型包含TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB，TEXT类型也是如此。（BLOB是SMALLBLOB的同义词，长度一样。TEXT也一样）区别在于TEXT有字符集和排序规则而BLOB类型存储的是二进制数据。
INNODB处理BLOB和TEXT类型时会在行内用一个1-4字节的指针指向外部的实际存储地址。而把实际数据存储在外部存储区域。
MYSQL没法对TEXT和BLOB的全部字符串做索引，只能做前缀索引。
不建议使用blob和text类型，如果确实业务需要应该另起一张表用主键来做对应。不要放在原表中以免影响读写效率。
ENUM类型MYSQL支持把字段定于为ENUM类型，定义的时候声明枚举的几个值。MYSQL会构建一张表存储‘序号-字符串’对应表（序号从1开始）。而在字段中只存储对应的序号数字值，只占用1-2字节的空间。查询 ‘字段名+0’ 可以查看枚举值对应的数字。
但是枚举的缺点是添加或修改枚举值时需要 alter table。所以如果不是业务非常需要，不建议用ENUM。
存储数字字符串禁止用ENUM，要用整型存储。
日期和时间类型MYSQL存储时间的最小粒度是秒，但是同时支持用微秒级的粒度进行临时运算。一般使用DATETIME和TIMESTAMP两种类型来保存时间。也有做法用整型来保存时间戳，但是实际上不方便处理，也不会带来很大的效率提升，所以不建议使用这种做法。
DATETIME占8个字节，可以保存公元1001-9999年的时间，精度为秒。保存的格式为 ‘YYYYMMDD HHMMSS’。
TIMESTAMPTIMESTAMP即时间戳，占四个字节。可以保存1970年1月1日零点 - 2038年的时间。通常建议使用TIMESTAMP来保存时间，因为执行的效率更高。
位数据类型MYSQL支持使用BIT、SET类型来保存位数据。
理论上bit(1)只需要一个bit的空间，但实际上INNODB需要为每个bit类型的数据列分配足以存下其内容的最小整数类型的空间（不同的是myisam引擎这会合并所有bit列，共同分配整字节的存储空间）。即bit(1)需要1字节空间，四个bit(1)的列需要4字节。其实对存储空间没有多大的提升。
SET支持将很多true、false数据保存在一个由位组成的集合中，可以有效的节省空间。并且Mysql提供了FIND_IN_SET() , FIELD()方法来方便对SET数据的查询。但是缺点和ENUM类型很相似，如果要增加或减少一个bit就必须修改表结构。解决方法是使用tinyint unsigned来表示8个bit的true、false数据，当然也有缺点是不容易查询，并且表示的意思不够清晰（比如第5位被设置成true是什么意思，这就需要编写额外的文档提供支持）
BIT类型的坑点当查询bit数据时，比如一个bit(8)的列存放的值是‘00111001’，对应十进制数57，但是查询到的数据却是57在ascii码表中对应的值‘9’。这是种情况导致BIT类型的使用复杂度大大提升。尽量不用使用bit类型。
选择标识符标识列经常被用于查询中的比较，并且经常被作为其他表的外键。所以选择标识列的类型不仅要考虑存储也要考虑比较和运算的效率。
标识列最好的选择是整数类型，因为比较运算速度快，并且可以支持auto increment。
不要用set、enum类型作为标识列，在其他字段也尽量不使用。能不用尽量不要使用char、varchar作为标识列。
表设计中的常见错误 列过多MYSQL在查询数据时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层解析成一个一个列，如果列太多的话，在这个转换阶段的开销就会非常大。
所以尽量不要把表的字段设计的太多。如果的确需要这么多的字段，按冷热数据进行垂直分表，可以有效的优化性能。
关联操作过多查询时连接了过多的表会导致查询速度下降，作者的经验是单次查询关联的最大表数是12张表，尽量不要多余12张表。
枚举使用ENUM和SET类型在更新时如果表数据量大会耗费大量时间，并且在MYSQL 5.0及以前的版本甚至会阻塞读写。
NULL数据最好将一张表的所有字段都设置为not null，NULL值需要占用额外的空间并且限制了索引的优化
如果业务确实需要某个字段可表示NULL值，一种方法是用不可能属于合理数据的值域中的某个值来代替NULL，例如 -1 来表示一个整数字段的NULL值。缺点是编码复杂度的提升。
范式 范式化设计的优点和缺点 优点- 范式化数据的更新一般更快（范式化的表一般更新时只需要更新较少的行即可，可见经典的【雇员-部门-领导】例子）
- 范式表一般会更小（字段更少），执行操作更快（mysql增删改成都会先从磁盘读入内存，越小的数据在内存中自然效率更高）
- 很少有多余字段，所以更少需要distinct和group by 操作。
- 操作需要关联，增加时间消耗，并且某些情况下会造成索引失效。
- 最直接的优点就是避免关联操作，尤其是在表行数大的情况下，这样能显著的提升性能。（数据量很大的情况下，即使是不用索引全表查询也比需要关联的查询快的多，因为避免了随机I/O）
- 缺点自然是冗余数据，表越大，冗余数据多占的磁盘空间就越多。一方面带来磁盘空间消耗，另一方面对查询效率也有影响。
真实的开发环境不会存在绝对的范式和绝对的反范式，只会根据需求混用范式和反范式设计以追求最佳性能。
缓存表和汇总表 缓存表对于”缓存表“其实没有官方的准确定义，作者的定义是用来存储那些可以从比较简单地从其他表获取的数据（但是每次查询的速度比较慢）的表。比如对一张数据量很大的表建立一个包含了其常用字段的表，然后查询时先查缓存表再查原表，原表查到后存入缓存表。可以定时的维护数据或者重建数据表以免数据量过大。
汇总表”汇总表“是建立来存放一些其他表的累计结果数据的表。比如一个需求要提供一个接口来返回当日网站的目前访问次数并且要求精确结果，当然不能每次调用的时候都去查询访问记录表（尤其在网站访问量很大的时候，这种查询效率并不高）。
合理的做法是建立一个汇总表，存放每整数小时内的访问量。网站后台设一个定时任务每整数小时统计本小时的访问量存入汇总表。这样查询的时候只需要从汇总表查询本日的所有访问数据累加起来，再加上一个整数小时到目前为止的访问量即可。查询的速度会比直接查记录表更高效。
加快ALTER TABLE 的操作速度对于数据量很大的表 ALTER TABLE 是一个极耗时的操作，并且很多情况下会锁住整张表，使MYSQL服务中断。所以对于生产环境的表最好不要使用直接ALTER TABLE的方式修改表结构。
替换主库一种方法是使用一台不提供服务的MYSQL服务器，在上面ALTER TABLE 后和主库进行切换。
影子拷贝用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。有一些第三方工具提供了帮助完成影子拷贝的功能。
只修改.frm文件Alter Table耗时的主要原因是需要重建整张表。如果只修改.frm文件(定义表结构的文件)而不重建整个表的话，速度是非常快的。比如alter修改列的默认值这个操作是不需要重建表的，只需要修改.frm文件中的定义即可，所以就算是很大的表，alter的速度也很快。
问题在于mysql有时候在没有必要重建表的时候也会重建表，所以如果我们确定alter的操作是不需要重建整张表的，可以采用直接修改.frm文件的方式。（有一点风险）以下操作可能不需要重建表：
- 移除一个列的auto_increment
- 增加、移除、更改ENUM和SET常量
通过修改.frm文件来实现Alter Table的步骤是
- 创建一张相同结构的空表，并做修改
- 执行FLUSH TABLES WITH READ LOCK，关闭所有正在使用的表，并禁止任何表被打开
- 交换原表和现有表的.frm文件。在操作系统层面直接修改
- 执行UNLOCK TABLES 释放第二部的读锁
