1. 优化数据类型

更小的通常更好
尽量使用可可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为占用更少的磁盘、内存和 CPU 缓存，并且处理时需要的CPU周期也更少。
简单就好
简单数据类型操作通常需要更少的 CPU 周期。例如，整型比字符型操作代价更低；尽量使用 MySQL 内建的类型而不是字符串来存储日期和时间；应该用整型存储IP地址；

尽量避免 NULL
通常情况下最好指定列为 NOT NULL，除非真的需要存储 NULL 值。如果查询中包含 NULL 的列，对 MySQL来说更难优化。通常把可为 NULL 的列改为 NOT NULL 带来的性能提示比较小，但是，如果计划在列上建索引，那么这个列最好就设计为 NOT NUL L的列。

1.1 MySQL中的实数

浮点类型在储存同样范围的值时，通常比 DECIMAL 使用更少的空间。FLOAT 使用4个字节存储。DOUBLE 占用8个字节，相比 FLOAT 有更高的精度和更大的范围。MySQL 使用 DOUBLE 作为内部浮点计算的类型，同样可以考虑使用 BIGINT 代替浮点数，按要求倍数放大即可。

1.2 VARCHAR 和 CHAR 类型

VARCHAR

VARCHAR 类型用于储存可变长字符串，是最常见的字符串数据类型。比定长类型更节省空间。需要额外字节记录字符串长度：如果列大于等于255字节，则使用额外1字节，否则使用2个字节。假设 VARCHAR(10) 则列需要11个字节存储空间。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，MyISAM 引擎会将行拆成不同的片段存储，InnoDB 则需要分裂页来使行可以放进页内。

什么时候适合使用VARCHAR

  • 字符串列的最大长度比平均长度大很多
  • 列的更新很少，不在乎碎片

  • 使用了像 UTF-8 这种复杂字符集，每个字符都使用不同的字节进行存储

    CHAR

    CHAR 类型是定长的；会分配足够的空间给字符串。CHAR 适合存储很短的字符串，或者所有值都接近同一个长度。因为是定长的，所以频繁变更数据也不容易产生碎片。

    什么时候适合使用CHAR

  • 储存密码的 MD5 值

  • 使用 CHAR(1) 来存储只有 Y 或 N 的值

使用真正需要的空间的好处？ ** 更长的列会消耗更多的内存，因为 MySQL 通常会分配固定大小的内存块来保存内部的值。尤其是使用内存临时表进行排序或操作时会特别糟糕。在利用磁盘临时表排序时也同样糟糕。

1.3 BLOB 和 TEXT 类型

BLOB 使用二进制方式存储，TEXT 使用字符方式存储，二者都是为存储很大的数据而设计的字符串数据类型。
BLOB 相比 TEXT，不仅是 BLOB 类型储存的是二进制数据，同时也没有排序规则或字符集。

  • TINYTEXT / SMALLTEXT / TEXT / MEDIUMTEXT / LONGTEXT

  • TINYBLOB / SMALLBLOB / BLOB / MEDIUMBLOB / LONGBLOB

    1.4 使用枚举代替字符串类型

    枚举列代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义集合。MySQL 存储枚举时非常紧凑，会根据列表值得数量压缩到一个或两个字节中。枚举不好的地方是，字符串列表是固定的，添加或删除字符串必须使用 ALERT TABLE。由于 MySQL 把每个枚举值保存为整数，必须进行查找才能转换为字符串。

    1.5 日期和时间类型

    DATETIME

    范围从 1001 年到 9999 年，精度为秒，封装到 YYYYMMDDHHMMSS 格式中，与时区无关，使用 8 个字节的存储空间。可排序。

    TIMESTAMP

    时间从 1970 年 1 月 1 日午夜以来的秒数，和 UNIX 时间戳相同。TIMESTAMP 只使用 4 个字节存储空间，范围则从 1970 年到 2038 年。可使用 FROM_UNIXTIME() 和 UNIX_TIMESTAMP() 互相转换。与时区有关。

    建议

    尽量使用 TIMESTAMP；不要把 Unix 时间戳保存为整数值；如果需要存储比秒更小的粒度日期可以用 BIGINT 类型储存微妙级别的时间戳。

    1.6 选择标识符

    选择标识符非常重要，一般来说如果一个列要其他列的值进行比较，或者通过标识列寻找其他列。那么这两个列的类型最好是同一类型。混用不同类型数据可能导致性能问题，在操作时也比较容易出现难发现的隐式转换错误。

    整数类型

    整数通常是标识列最好的选择，因为它们很快并且可以使用 AUTO_INCREMENT。

    ENUM 和 SET 类型

    对于标识列来说，不建议选择 ENUM 和 SET 类型。

    字符串类型

    尽量避免使用字符串类型作为标识列，因为它们很消耗空间，并且通常比数字类型慢。尤其使用 MyISAM 表里使用字符串作为标识列要特别小心。MyISAM 默认对字符串使用压缩索引，这回导致查询慢得多。
    对于完全“随机”的 字符串也需要多加注意，例如 MD5 / SHA1 / UUID 产生的字符串。这些函数生成的新值会任意分布在很大的空间内，这会导致 INSERT 以及一些 SELECT 语句变得很慢。

  • 因为插入值会随机地写到索引的不同位置，使得 INSERT 语句更慢。这会导致页分裂、磁盘随机访问，以及对于聚簇储存引擎产生聚簇索引碎片。

  • SELECT 语句会变得更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方。
  • 随机值导致缓存对所有类型的查询语句效果都很差，因为是的缓存赖以工作的访问局部性原理失效。如果整个数据集都数据被访问的可能概率都一样的话，那么缓存任何一部分特定数据到内存都没有好处；如果工作集比内存大，缓存将会有很多刷新和不命中。

如果储存 UUID 的值，则应该移除“-”符号；或者更好的做法是，用 UNHEX() 函数转换 UUID 值为 16 字节的数字，并且储存在一个 BINARY(16) 列中。检索时可以通过 HEX() 函数来格式化为十六进制的格式。

小心自动生成的 schema
自动生成 shcema 的程序一般都会导致严重的性能问题。有些程序存储任何东西都会使用很大的 VARCHAR 列，或者对需要在关联时比较的列使用不同的数据类型。特别是使用 ORM 框架时，一些 ORM 系统会存储任意类型的数据到任意类型的后端存储中，通常没有涉及使用更优的数据类型来存储。甚至有时会为每个对象的每个属性单独使用的行、基于时间戳的版本控制，导致单个属性会有多个版本存在。

2. schema 设计中的陷阱

太多的列

MySQL 的存储引擎 API 工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后再服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的代价是非常的高的。MyISAM 的定长行结构实际上与服务器层的行结构正好匹配，所以不需要转换。然而，MyISAM 的变长行结构和 InnoDB 的行结构则总是需要转换。转换的成本依赖与列的数量 。

太多的关联

所谓的“实体-属性-值”（EVA）设计模式是一个常见的糟糕设计模式，尤其是在 MySQL 下不能靠谱地工作。MySQL 限制每个关联最多只能有61张表，但EVA 数据库需要许多自关联。一个粗略的经验法则，如果系统查询执行得快速且并发行好，单个查询最好在12个表以内。

什么是 EVA 设计模式 ** EAV 抽象出 Entitty-Atribute-Value 的关系形成一张EAV表，至少有3个字段：实体，属性，值，通过数据容易，一个实体多条记录的形式记录不同的属性。为了减少冗余，把额外的实体，属性信息抽象到额外的表中，这样形成关系模型其实就是 EVA 设计模式

不合适的 NULL

我们之前他到过避免使用 NULL 的好处，并且建议尽可能地考虑替代方案。但是遵循这个原则也不应该走极端，当确实需要表示未知值时不要害怕使用 NULL。MySQL 会在索引中存储 NULL 值，Oracle 则不会。

3. 范式和反范式

3.1 范式优点和缺点

优点

  • 范式化的更新操作比反范式化快
  • 数据较好的范式化后，就意味着只有很少或者没有重复数据，所以只需要修改更少的数据
  • 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快

  • 很少有多余的数据意味着检索列表数据时更少使用 DISTINCT 或者 GROUP BY 语句

    缺点

  • 范式化设计的 schema 通常需要关联，且代价昂贵且可能使一些索引策略失效

    3.2 反范式优点和缺点

    优点

  • 所有数据都在一张表中可以避免关联，当时数据比内存大时可能比关联快得多，可能这样会避免了随机 I/O

  • 单独的表也能使用更有效的索引策略

    缺点

  • 可能有较多的重复数据

  • 单表量可能会较大

    3.3 混用范式和反范式化

    最佳设计

    真实环境中很少有完全的范式化或反范式化，在实际应用中经常要混用。常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。冗余一些字段到一些表中有必要的，可以方便一些操作，比如排序等。

    4. 缓存表和汇总表

    物化视图

    许多数据库上比如 Oracle 或是 MSSQL 上都提供了一个被称为物化视图的功能。物化视图是加上是预先计算并且存储在磁盘上的表，可以通过各种各样的策略去刷新和更新。MySQL 原生不支持物化视图。

    计数器表

    不推荐使用 MySQL 的计数器表，实际上现在我们有更好的办法去实现计数器。例如使用 Redis、Memcache 这种基于内存的 Key-Value 中间件来实现计数器表的功能。良好的设计会有比 MySQL 更好的效率。

    5. 加快 ALERT TABLE 操作速度

    MySQL 的 ALERT TABLE 操作对大表来说有大的性能问题。MySQL 执行大部分修改表结构操作的方法是使用新的结构创建个空表，然后从旧表中查询所有的数据插入新表，然后删除旧表。一般来说，大部分的 ALERT TABLE 操作将会导致 MySQL服务中断。我们可以使用一下两种情况来针对不同的情况进行操作：一种是先在一台不提供服务的机器上执行 ALERT TABLE 操作，然后和提供服务的主库进行切换；另一种是“影子拷贝”，即用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。

    总结

    我们主要在这篇笔记中记录的是关于如何更好的设计 schema 。我们在建立字段的时候应该选择能够合理的并且满足要求的最小字段，因为更小的字段代表更少的磁盘占用、内存占用、CPU缓存和CPU的占用周期。尽量不要使用 NULL 值作为默认值，除非是迫不得已，要在字段上建立索引就更不应该允许字段为NULL。学习了 VARCHAR 和 CHAR 的区别，VARCHAR 需要额外字段记录长度，且适合于最大长度远大于平均长度的情况，因为是变长字段所以较容易产生碎片，由于变长的，所以在 UPDATE 的时候也比较慢。而 CHAR 类型适合于平均长度相符的字段，插入更新较快不容易产生碎片。适合存储 MD5 密码。DATETIME 和 TIMESTAMP 建议使用 TIMESTAMP ，后者占用更少空间，意味操作更快。在选择字段标识符的时候，尽量不要选择字符串选择作为标识符列，应该选用合理的 数字型。因为选择字符串作为标识符列，很消耗空间，对于完全“随机”的字符串类似于UUID 、SHA1 这种会任意分布到空间内，因为插入值会随机写到不同的位置，导致 INSERT 语句更慢，可能出现页分裂的问题、磁盘随机访问以及对于聚簇储存引擎产生聚簇索引碎片。会导致 SELECT 语句变慢，因为数据被随机分配在不同的地方。导致对所有数据类型的查询效率都很差，因为让访问局部性原理失效。
    关于 schema 我们尽量不要单表内出现过多的列，因为存储引擎 API 工作时，需要在 MySQL 的服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后再服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的代价是非常的高的。表之间的关联经验值应该控制在12个以下。
    关于范式我们应该适当的遵守和适当的破坏，实际应该按照业务逻辑来决定。
    如果遇到大表的 ALERT 通常都很慢，我们可以通过主库切换的方式来做更改。也可以通过“影子拷贝”的方式，即用一张新表重名并对旧表做替换。