选择优化的数据类型
选择数据类型的原则
- 更小的通常更好。应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型占用更少的磁盘、内存和 CPU 缓存，处理时需要的 CPU 周期也更少。但也要确保没有低估需要存储的值的范围。(个人觉得字符串的长度限制有时候真的很难把握)
- 简单就好。简单数据类型的操作通常需要更少的 CPU 周期，整型的代价比字符串低，内建时间类型比字符串低，用整型存储 IP 比字符串低。
- 尽量避免使用
NULL。查询可为 NULL 的列对于 MySQL 来说更难优化，这些列使得索引、索引统计和值比较都更复杂。
整数类型
整数计算一般使用 64 位的BIGINT类型。
MySQL 可以为整数类型制定宽度，例如INT(11)，但对于大多数应用没有意义，不会限制值的合法范围，只是规定了交互工具用来显示字符的个数。
实数类型
FLOAT 和 DOUBLE 类型支持标准的浮点运算进行近似计算。
DECIMAL 类型用于存储精确的小数。在MySQL 5.0以上，MySQL 服务器自身实现了 DECIMAL 的高精度计算，相较原生浮点计算明显更慢。
DECIMAL 类型可以指定小数点前后的最大位数，如DECIMAL(18,9)小数点两边各存储 9 个数字，以二进制字符串的形式存储，每 4 个字节存储 9 个数字，小数点本身占一个字节，共计 9 个字节。
需要精确计算时，也可以考虑使用 BIGINT 代替 DECIMAL，根据需要的精度乘以响应的倍数即可。如存储财务数据要求精确到万分之一分，则可以把所以金额乘以一百万，以整形存储。
字符串类型
从 4.1 开始每个字符串列可以定义自己的字符集和排序规则。
VARCHAR 需要使用 1 或 2 个额外字节记录字符串的长度：如果列长度小于等于 255 用 1 个，否则用 2 个。VARCHAR 节省空间但是在更新时需要许多额外工作。适合使用的情况：最大长度比平均长度大很多；列很少更新；使用 UTF-8 这种每个字符字节数不同的字符集。
**5.0 以上版本 VARCHAR 保留末尾空格，CHAR 会删除所有空格。**字符串长度定义不是字节数，是字符数。
BINARY 和 VARBIANRY 存储二进制字符串，比较时更快。
使用
VARCHAR(5)和VARCHAR(200)存储 ‘hello’ 的空间开销是一样的，但 MySQL 会分配固定大小的内存块来保存内部值，尤其是使用内存临时表进行排序等操作。所以最好的策略是分配真正需要的空间。
BLOB 和 TEXT 分别使用二进制和字符串方式存储大数据。BLOB 没有排序规则和字符集，而 TEXT 类型有。
对于 BLOB 和 TEXT，MySQL 只对每列的前max_sort_length字节而不是整个串进行排序。来着两种类型不能对全部长度进行索引也不能使用索引消除排序。
Memory 引擎不支持 BLOB 和 TEXT，如果查询中使用这两种类型的列并且需要使用隐式临时表，将使用 MyISAM 磁盘临时表，开销很大。应尽量避免使用这两种类型，实在无法避免可以在用到该字段时使用
SUBSTRING(column, length)将列值转换为字符串。
ENUM（枚举）类型有利有弊不太常用。
日期和时间
MySQL 中时间格式精度为秒（MariaDB 支持微秒级别的时间类型）。
- DATETIME 使用 8 字节存储，存储范围大（1001~9999），与时区无关
- TIMESTAMP 使用 4 字节存储，存储范围小（1970~2038），与时区相关
通常使用 TIMESTAMP 类型，效率更高。
位数据类型
不太常用
选择标识符
选择标识列（identifier column）类型时，不仅要考虑存储类型还要考虑如何进行计算和比较，一旦选定了一种类型，还要确保所有关联表中使用同样的类型，类型之间需要精确匹配（包括 UNSIGNED 这样的属性）
整数通常是 ID 列最好的选择。
使用 MD5(),SHA1(),UUID() 产生的字符串的值会随机分布在很大的空间中，导致 INSERT 和一些 SELECT 语句变得很慢：
- 插入值随机写到索引的不同位置，导致页分裂，磁盘随机访问等，详见第五章
- 逻辑上相邻的行会分布在磁盘和内存的不同地方
- 随机值使得缓存赖以工作的
访问局部性原理失效。
存储 UUID 值应该异出“-”符号；最好使用 UNHEX() 函数将 UUID 值转换为 16 字节的数字，存储在 BINARY(16) 列中。检索时可以通过 HEX() 函数格式化成十六进制格式。
特殊类型数据
IP 的本质是 32 位无符号整数，分成四段只是为了方便人进行阅读。MySQL 使用 INET_ATON() 和 INET_NTOA() 函数在两种表示方法之间转换。
MySQL schema 设计中的陷阱
- 太多的列
MySQL 的存储引擎 API 需要在服务器曾和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将换缓冲内容解码成各个列。从行缓冲将编码过的列转换成行数据结构的操作代价很高。转换的代价依赖于列的数量。 - 太多的关联
如果希望查询执行得快速且并发行好，单个查询最好在 12 个表以内做关联 - 防止过度使用枚举
范式和反范式
范式的优点和缺点
- 范式化的更新操作通常更快，只需修改更少的数据
- 范式化的表通常更小，可以更好地放在内存中，执行操作更快
- 减少 DISTINCT 或者 GROUP BY 语句的使用
范式化设计的缺点是需要更多关联操作。范式化可能将列存放在不同的表中，而这些列如果在一个表中可以属于同一个索引。
反范式的有点和缺点
反范式可以避免关联，当数据比内存大是比关联要快得多，因为这样避免了随机 IO。
假设想要查询付费用户最近的 10 条信息，涉及user_name, account_type, message_text, message_published_time等字段。使用范式化设计，字段存放在两个表格中，使用 user_id 进行关联。使用反范式化组织数据，并增加一个索引（account_type, published），就可以不通过关联进行高效查询。
混用范式化和反范式化
现实中往往混合使用，平衡时间和空间开销
缓存表和汇总表
有时提升性能的最好方法是在同一张表中保存衍生的冗余数据，又是也需要创建一张独立的汇总表或缓存表。
汇总表：如统计过去 24 小时发送的消息数，可以创建msg_per_hr来存储每个小时的发帖数，从中查出完成的 23 小时的发帖数，在从原始表中查出头尾不完整的数量，相加得到。
事事计算统计值是很昂贵的操作，要么扫描表中的大部分数据，要么查询语句只能在某些特定的索引上生效，而这类特定索引一般会对 UPDATE 操作造成影响。
缓存表：有时可能需要很多不同的索引组合来加速各种类型的查询。这些矛盾的需求可以通过创建一张包含部分列的缓存表解决。对缓存表使用不同的存储引擎可获得更高的效率，如主表使用 InnoDB，用 MyISAM 作为缓存表的引擎得到更小的索引占用空间，并且可以全文搜索。Elastic Search 等也可以看成是缓存表。
物化视图
物化视图实际上是预先计算并存储在硬盘上的表，可以通过各种策略进行刷新和更新。MySQL 并不原声支持物化视图，使用 Flexviews 可以自己实现。
计数器表
假设需要记录网站的点击次数，可使用 1 行记录，每次点击对改行进行更新，但写锁使得更新操作只能顺序执行。要获取更高的并发更新性能，可以将计数器保存在多行中，每次随机选择一行进行更新。
ON DUPLICATE KEY UPDATE可在主键已经存在时进行更新。
加快 ALTER TABLE 操作的速度
ALTER TABLE 是一个很费事的操作，对于常见的场景能使用的技巧：一种是离线修改后进行主库的切换；另一种是，创建新表，拷贝数据后修改表名。
对于一些特殊的场景，可以只通过修改表的元数据加快操作。
只修改 .frm 文件
不需要重建表的操作：
- 移除一个列的 AUTO_INCREMENT
- 增加、移除或修改 ENUM 和 SET 常量
操作过程：
- 创建一张有相同结构的表，并进行所需要的操作
- 执行
FLUSH TABLES WITH READ LOCK，关闭所有正在使用的表 - 交换
.frm 文件 - 执行
UNLOCK TABLES释放锁
快速创建 MyISAM 索引
MyISAM 中的唯一索引无法通过禁用索引、载入数据、重启索引的方式导入大量数据，也可以通过直接修改元文件的方式操作