Schema与数据类型优化
选择的优化的数据类型
一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为他们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少。
使用简单的数据类型。例如，整型比字符串操作代价更低，因为字符串集和校对规则（排序规则）使字符比较比整形比较更复杂。另外，应该使用应该使用mysql的内建类型而不是字符串存储日期和时间，应该使用整形存储IP地址。
尽量避免NULL值。使用NULL的列会使用更多的存储空间，在MySQL里需要特殊处理。当可为NULL的列被索引时，每个索引需要一个额外的字节，在MyISAM里甚至可能导致固定大小的所有（例如只有一个整形列的索引）变成可大可小的索引。通过把可为NULL的列改为NOT NULL带来的性能提升比较小，如果不确定这回导致性能瓶颈问题不必要首先修改。如果计划在列上建索引，就尽量避免设计成可为NULL的列。
MySQL为了兼容性支持了很多别名，例如INTEGER、BOOL以及NUMERIC, 他们只是别名。这些别名不会影响性能。如果建表时采用数据类型的别名，用SHOW CREATE TABLE可以发现报告的是基本数据类型，而不是别名。
- 整型类型
整型类型有这几种：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。分别使用8，16， 24， 32， 64位存储空间。他们可以存储的范围从-2(n-1) ~ 2(n-1)-1 （-2的n-1次方到2的n-1次方-1），其中N是存储空间的位数。
整型类型有可选的UNSIGNED属性，表示不允许负值， 这大致可以使得正数的上限提高一倍。例如TINYINT UNSIGNED可以存储的范围是0~255。
MySQL可以为整数类型指定宽度，例如INT(11)，对大多数应用来说这是没有意义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说INT(11)和INT(20)是相同的。
- 实数类型
FLOAT和DOUBLE类型致辞使用标准的浮点预算进行近似计算。
DECIMAL类型用于存储精确的小数。在MySQL 5.0以及更高版本中，DECIMAL类型支持精度计算。因为CPU不支持对DECIMAL的直接计算，所以在MySQL 5.0以及更高的版本中，MySQL服务器自身实现了DECIMAL的高精度计算。相对而言，CPU直接支持原生浮点计算，所以浮点运算明显更快。
对于DECIMAL列，可以指定小数点前后所允许的最大位数。MySQL 5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例：DECIMAL(18，9）将存储9个字节（其中小数点占用1个字节）。
浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的存储空间。FlOAT使用4个字节存储。DOUBLE占用8个字节，相比FLOAT有更高的精度和更大的范围。MySQL使用DOUBLE作为内部浮点计算的类型。
因为需要额外的空间和计算开销，所以应该尽量对小数进行精确计算时才使用DECIMAL—例如存储财务数据。一个技巧是用BIGINT存储DECIMAL数值（只需要将数值乘以相应的倍数即可），避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。
- 字符串类型
- VARCHAR
可以存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它使用必要的空间。有一种情况例外，如果MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储，这会很浪费空间。
VARCHAR需要使用1个或者2个额外的字节记录字符串的长度；如果列的最大长度小于或者等于255个字节，则只使用1个字节，否则使用2个字节。
CHAR
CHAR类型是定长的。当存储CHAR值时，MySQL会删除所有的末尾空格。CHAR值适合存储定长的字段，或者说所有值都接近同一个长度（这种情况下，使用CHAR值存储不容易产生碎片）。BINARY和VARBINARY
这两种类型存储的是二进制字符串。二进制字符串使用字节码存储。BINARY采用\0(零字节）来填充使得长度达到指定的长度。当需要存储二进制数据，并且希望MySQL使用字节码而不是字符进行比较时，可以使用二进制类型。BLOB和TEXT类型
BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。
BLOB类型包含了TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB。BLOB是SMALLBLOB的同义词，TEXT是SMALLTEXT的同义词。
BLOB类型存储的是二进制数据，没有排序规则或者数据集，而TEXT类型有字符集和排序规则。
- 枚举类型
枚举类型可以把一些不重复的字符串存储在一个预定义的集合中。MySQL在内部将每个值在列表中的位置保存为整数，在表的.frm文件中保存“数字 - 字符串”映射关系的“查找表”。另外，枚举字段排序是安装内存存储的整数进行排序的。
缺点：字符串列表时固定的，添加或者删除元素，需要ALTER TABLE。所以未来会改变字符串的字段，使用枚举并不合适。
- 日期和时间类型
MySQL提供两种相似的时间类型：DATETIME和TIMESTAMP，两种时间类型支持存储的最小粒度为秒（MariaDB支持微妙级别的时间类型）。
DATETIME
这个类型能保存大范围的值，从1001年到9999年。存储的时间与时区无关，占用8个字节的存储空间。TIMESTAMP
这个类型保存的范围从1970年到2038年，占用4个字节的存储空间。TIMESTAMP显示的值依赖于失去。MySQL服务器、操作系统，以及客户端连接都有时区设置。
推荐尽量使用TIMESTAMP类型，因为它比DATETIME的空间利用率高。此外不推荐使用整型存储时间，不方便处理。
如果要存储微妙级别的时间，可以使用BIGINT存储微妙级别的时间戳，或者使用DOUBLE类型存储秒之后的小数部分。
- 选择标识符
为标识列选择数据类型时，应该选择跟关联表中的对应列一样的类型。类型之间要精确匹配，包括想UNSIGNED这样的属性。
应当避免使用字符串类型作为标识符，因为他们很消耗空间并且通常比数字慢。推荐使用整数作为标识符。
- 一些其它建议
如存储ipv4地址，使用UNSIGNED INT存储，它比使用CHAR(15)有更高的空间效率。使用inet_aton可将把ip转为无符号整型，使用inet_ntoa可把整型的ip转为电地址。
MySQL schema设计的一些指导原则
一个表不要有太多的列
减少表关联
适度使用NULL值，减少应用复杂程度，避免引入奇怪的BUG。
创建高性能的索引
索引的类型
B-Tree 索引
大家一般说索引的时候， 如果没有特别指明类型， 多半说的是B-Tree索引，它使用B-Tree数据结构来存储数据。
B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。
B-Tree索引使用与全键值、键值范围或者键值前缀前缀查找。其中键前缀查找只适用于根据最左前缀的查找。对如下类型的查询有效。
全值匹配
全值匹配值的是和索引中的所有列进行匹配。
匹配最左前缀
查询只使用到索引的第一列。
匹配列前缀
只匹配某列的值的开头部分。 如使用like查询索引匹配第一列的前面部分字符。
匹配范围值
使用B-Tree索引的限制
- 如果不是按照索引的最左列开始查找，则无法使用索引。
（查询必须包含最左列，并且不能查询左列以某字母结尾的行，才能使用到索引）
哈希索引
哈希索引（hash index）基于哈希表实现，只有精确匹配所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算出一个哈希码（hash code）,哈希码是一个较小的值，并且在不同的键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在所有中，同时在哈希表中保存指向每个数据行的指针。
哈希索引的限制
哈希索引数据不是按照索引值顺序存储的，无法用于排序。
哈希索引值只支持等值比较查询。
可以使用SRC32()做为哈希函数存储哈希值，但是如果数据量很大，会出现哈希冲突的情况。
不要使用SHA1()和MD5()作为哈希函数。这两个函数计算出来的哈希值是一个非常长的字符串，会浪费大量的空间，比较时也比较慢。
要解决冲突问题，必须在where条件中带入哈希值和对应的列值。
select world,crc from words where cc=CRC32('gnu') and word='gnu'
还可以使用FNV64()函数作为哈希函数解决冲突。FNV64()哈希值为64位，速度快，且冲突比CRC32()要少的多。
全文索引
全文索引必须要使用关键词MATCH和AGAINST，而不是使用WHERE进行搜索。
