【四】高性能MySql笔记——规矩或者不规矩数据库设计？

    设计是程序高性能的基石。嗯~？这句话是我说的，算是个人的心得体会吧。没有好的设计，性能的优化也很难有预期的效果。

    规矩是指定教科书式的设计。所以文章题目的意思就是我们到底应该遵循规矩还是不规矩？在数据库的设计中，之前的文章中我们有讨论过范式。范式是一种规范化的设计，但却不一定是最优的设计选择。就像马克思说的：实践是检验真理的唯一标准。所以在数据库设计时我们应该根据实际出发，并非要遵循范式。今天我们谈谈数据库（schema）设计中的“坑”。

    太多的列

    在MySql内部服务层和存储引擎层之间会通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。这种转换代价很高。这种情况在MyISAM变长行结构和InnoDB中都存在。列的数量和代价成正比。这也说明了EAV（实体-属性-值）模式的糟糕。经过测试，如果希望查询执行得快速且并发性好，单个查询最好在12个表以内做关联。

    全能的枚举

    在上篇文章中我们对MySql的存储类型做了全面的分析，从中我们了解到枚举类型的好处，但是我们也应该记得枚举类型的修改是很消耗性能的操作，而且过多的枚举值也使得表的理解难度增加。更好的替代方法是将枚举值放在字典表中进行关联查询。

    变相的枚举

    枚举（ENUM）列允许在列中存储一组定义值中的单个值，集合（SET）列则允许在列中存储一组定义值中的一个或多个值。这样可能会引起混乱，所以应该正确选择枚举和集合，或者可以考虑我们上篇中提到的变相存储true/false的方法，这种方法会更优。

    NULL

    我们一直建议列的默认值不要使用NULL，应尽量使用其他值（如：0）替代。但是当实际中缺少必须使用NULL时也不必一定遵守之前的建议。因为一味的避免使用NULL可能使我们的代码复杂度升高。注意：不要使用0000-00-00 00：00：00的伪造值作为DATETIME的默认值，可以通过设置SQL_MODE来禁止使用不可能的日期值。MySql会在索引中保存NULL，而Oracle不会。

    范式的优缺点

    优点：

        ①范式化的更新操作更快；

        ②具有较少或者没有重复数据；

        ③范式化的表通常更小，可以更好的放在内存中，执行操作也会更快；

        ④更少需要DISTINCT和GROUP BY语句。

    缺点：

        ①通常需要关联，稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联。关联查询的代价也是需要在设计时考虑的因素。

    反范式的优缺点：

    优点：

         ①需要的数据都在一张表中，可以很好的避免关联；

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

         ③避免在索引中即排序又过滤。

    缺点：范式的优点就是反范式的缺点。

    混用范式和反范式：完全范式和反范式都只是实验室中的东西，所以我们应该根据实际需求混合使用。部分的范式化schema、缓存表、以及其他技巧的混用才是解决问题的根本方法。例如：在不同的表中复制存储相同的列，通过触发器更新对应列的缓存值。或者我们可以多增加一个列用于保存缓存统计值。这样就可以避免使用昂贵的子查询进行统计查询。根据实际需求，我们可以设计缓存表和混总表，虽然产生了冗余数据，但是往往可以极大的提升性能。缓存表对优化搜索和检索查询语句都非常有效。我们可以对缓存表使用不同的存储引擎，充分利用各个引擎的特长。缓存表和汇总表需要选择定期重建或实时维护数据。定期重建可以节省资源，同时可以保持表不会有很多碎片，以及有完全顺序组织的索引（更高效）。重建的方式一般需要通过“影子表”来实现。同时还有物化视图、计数器表等等，我们应该根据需要选择混合使用这些优化方法。

===========================================

名词解释：

物化视图：实际上是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新。原生MySql并不支持物化视图，我们可以使用三方工具（如：Flexviews）或自己实现。