高性能Mysql之Schema 与数据类型优化

选择优化的数据类型

MySQL支持的数据类型非常多，选择正确的数据类型对于获取高性能至关重要，不管存储那种类型的数据，有几个原则都有助于我们做出更好的选择

• 更小的通常更好
  一般的情况下应该尽量使用最小的数据类型，因为他们更快，占用的磁盘、内存和CPU缓存更少，而且在处理他们的时候需要的CPU周期也更少。但是要确保没有低估所需存储的值的范围，如果无法确定那个数据类型是最好的，就选择你不会超过范围的最小类型。
• 简单就好
  简单数据的操作通常需要更少的CPU周期。例如，整形比字符型操作代价更低，因为字符集和排序规则使字符类型比整形要复杂。
• 尽量避免null
  可为null的列使得索引、索引统计和值比较都更复杂。可为null的列会使用更多的存储空间，在Mysql里也需要特殊处理，当可为null的列被索引时每个索引记录需要一个额外的字节，在MyISAM里甚至可能导致固定大小的索引变成可变大小的索引。通常把可为null的列改为not null带来的性能提升比较小，所以调优时没有必要首先在现有表中查找并修改掉这类问题，除非确定这会导致问题，但是如果计划在列上建立索引，就应该避免设计成为可为null的列。

MySQL schema设计中的陷阱

下面是一些设计MySQL的schema的问题，这也许会帮助我们避免这些错误，并且可以找到一些更好的替代方案。

• 太多的列
  MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过缓冲格式进行拷贝数据，然后在服务器将缓冲内容解码成各个列，这个代价是非常高的，而且转换的代价依赖于列的数量。如果计划使用数千个字段，必须意识到服务器的性能运行特征会有一些不同。

• 太多的关联
  所谓的”实体-属性-值“（EAV）设计模式是一个糟糕的设计模式，尤其是在MySQL下不能靠谱的工作。MySQL限制了每个关联操作最多只能有61张表，但是在EAV数据库需要许多自关联，事实上许多关联少于61张表的情况下，解析和优化查询也会成为MySQL的问题。如果希望查询执行的快速而且并发性好，单个查询最好能在12个表以内做关联。

• 全能的枚举
  注意防止过度使用枚举。例子：
  CREATE TABLE ...( country enum('','1','2',...'31'))
  这种模式的表设计非常凌乱，这么使用枚举是一个有问题的设计方案，应该使用整数作为外键关联到字典表或者查找表来查找具体的值。在MySQL中，当需要在枚举列表中增加一国家就要做一次ALTER TABLE，在之前的版本这个操作是一个阻塞操作（5.1之后的版本，如果是在末尾添加值是不需要ALTER TABLE的）

• 变相的枚举
  枚举列允许在列中存储一组定义值中的单个值，集合（SET）列则允许在列中存储一组定义值中的一个或多个值。这时候容易导致混乱，例子：
  CREATE TABLE ...( is_default set ('Y','N') not null default('N'))
  如果这里真和假两种情况不会同时存在，那么应该用枚举代理集合列。

• 非此发明的NULL
  我们之前讨论了避免使用NULL的好处，并且尽量可能的考虑代替方案。即使需要存储一个事实的空值到表中，我们可以用0、某个特殊字符或者空字符串代替。 。
  但是遵循这个原则也不要走极端。当确实需要表示未知值也不要害怕使用NULL。在某些场景下使用NULL可能会比某个神奇的神奇常数要好。从特定的类型的值域选择一个不可能的值。例如-1代表一个未知的整数，可能会使代码变复杂，或者引入bug。处理NULL确实不容易，但有时候使用NULL会比替代它的方案更好。下面举个例子：
  CREATE TABLE ...( dt DATETIME not null default '0000-00-00 00:00:00')
  这样可能会导致很多问题（MySQL会在索引中存储NULL，而Oracle不会）

范式和反范式

在范式化的数据库中，每个数据会出现并且只出现一次，而在反范式化的数据库中，信息是冗余的，可能存在多个地方。（如果不熟悉范式，可以先去了解一下）

• 范式的优点和缺点
  优点
  1、范式化的更新操作通常比反范式更快
  2、当数据较好的范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据
  3、范式化的表通常很小，可以更好地放在内存里，所以执行操作会更快
  4、很少有冗余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句
  缺点
  1、范式化的表设计通常需要关联，稍微复杂一点的查询语句在符合范式的表上都可能需要一次关联，也许更多。这不但价格昂贵，也可能使一些索引策略无效。
• 反范式的优点和缺点
  优点
  1、反范式化的表因为所有数据都在一张表中，可以很好的避免关联
  2、如果不需要关联表，则对大部分查询最差的情况-即使没有使用索引-是全表扫描，当数据比内存大时这样可能会比关联快得多，因为这样避免了随机I/O。
  3、可以更有效的使用索引策略。
  缺点
  可以参考范式的优点

#没有使用反范式
SELECT message_text,user_name from message INNER JOIN 
user ON message,user_id=user.id WHERE user.account_type='premiumv'
ORDER BY message.published DESC LIMIT 10;
#使用反范式
SELECT message_test,user_name FROM message
WHERE account_type='premiumv' 
ORDER BY message.published DESC LIMIT 10;

• 混用范式化和反范式化
  通常我们的数据库设计在，没有绝对的范式化和反范式化。在实际应用中经常需要混合使用。
  最常见的反范式化数据的方法时复制和缓存，在不同的表中存储相同的特定列。在MySQL5.0和更新版本中，可以使用触发器更新缓存值，这使得这个方案变得简单。
  在上面的例子中我们在user表和message中都存储account_type字段，而不用完全的反范式化。这避免了完全反范式化的插入和删除问题，因为即使没有消息的同时也绝对不会丢失用户的信息，这样也不会把message表搞得太大，有利于高效地获取数据，但是有个问题，这样的话用户的账户类型更新的代价就变高了，因为需要操作两张表。这时候我们就需要考虑更新的频率以及更新的时长，并和执行查询的频率进行比较。

缓存表和汇总表

有时候提升性能最好的办法是在同一张表中保存衍生的冗余数据，然而，有时候也需要创建一张完全独立汇总表或者缓存表，如果能容许少量的脏数据，这是非常好的办法，但是有时确实没有选择的余地（例如：需要避免复杂、昂贵的实时更新操作）
仍然以网站为例，加入需要计算之前24小时内发送的小数，在一个很繁忙的网站不可能维护一个实时精确的计数器。作为替代方案，可以每小时生成一张汇总表。这样也许不是一条简单的查询就可以做到，并且比实时维护计数器要高效得多。缺点是计数器并不是100%准确。

加快ALTER TABLE操作的速度

这一块没怎么用过，就先放一个链接吧，有需要可以看看加快ALTER TABLE 操作速度