mysql schema设计优化

77 阅读 0 评论 51 点赞

我是靠谱客的博主懵懂戒指，最近开发中收集的这篇文章主要介绍mysql schema设计优化，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

mysql前期的设计问题会给后期的开发维护造成很大的问题，所以在mysql数据库的设计阶段就应该保证尽可能的高效，尽可能的减小后期开发和维护代价。

如何设计

1.适度冗余，让query尽量减少join

虽然optimizer会对query进行一定的优化，但有时候遇见复杂的join，优化效果并不令人满意，再加上本来join的性能开销，所以需要尽量的减少join，而需要通过冗余来实现。比如：有两个数据表分别为用户信息表和用户发帖表，在展示发帖列表时，如果没有冗余的话，两个表要join以取得想要的发帖信息和用户昵称，但如果考虑冗余，用户昵称占用空间不大，如果在发帖表里增加这么一个字段的话，在展示列表时就不用做join操作了，性能会得到很大的改善。

但冗余也会带来一些问题，比如在发帖表里增加了用户昵称字段，就得维护两份用户昵称数据，为了保证数据的一致性，在用户昵称发生改变时，就得向两个表做更新操作，程序中就得做更多的处理。但相比的话，更新频率显然不及查询频率，这样通过增加少量的更新操作会换来更大的性能提升，这也是在项目中经常采用的优化手段。

2. 大字段垂直分拆

所谓的大字段，没有一个很严格的标准，常用的是如果一个字段的大小占整条记录的50%以上，我们就视为其为大字段。

大字段垂直分拆相比适度冗余是完全相反的操作，适度冗余是将别的表中的字段放进一个表中，而大字段分拆是将自身的大字段拆分出去放进另一个表中。

这两个优化策略貌似是矛盾的，但要根据具体的应用场景来分析，适度冗余是因为在频率较高的查询中要使用该字段，为了减少join的性能开销。而大字段垂直分拆是将在查询中不使用的大字段拿出去，虽然不使用该字段但mysql在查询时并不是只需要访问需要查询的那几个字段，而是读取所有的字段，所以即使不使用字段，mysql也会读取该字段，为了节省IO开销，所以将查询中不常使用的大字段分拆出去。

比如：拿博客系统为例，常用的作法是将博客内容从博客列表里分拆出去建立一个博客内容表，因为访问博客列表时并不需要读取博客内容，分拆出去之后，访问博客列表的性能将会大大的提升。但同时访问博客内容时就得做一次join操作了，性能对比的话，join操作两个表是一对一的关系，性能开销会很低。

3. 大表水平分拆

举例说明：在一个论坛系统里，管理员经常会发一些帖子，这些帖子要求在每个分类列表里都要置顶。

设计方案一：在发帖表里增加一列用来标示是否是管理员发帖，这样在每个分类列表展示时就需要对发帖表查询两次，一次是置顶帖，一次是普通帖，然后将两次结果合并。如果发帖表内容较大时，查询置顶帖的性能开销会比较大。

设计方案二：将置顶帖存放在一个单独的置顶表里。因为置顶帖数量相比会很少，但访问频率很高，这样从发帖表里分拆开来，访问的性能开销会少很多。

4.准实时优化

对于一些频繁访问但对准确性要求不高的数据完全可以缓存起来，设置一个可以接受的缓存时间，这样服务器性能会提高很多，对于一些需要做统计的数据，后台可以cront跑一个脚本定时的去算统计数据，然后将数据缓存起来，策略有很多，选择合适了可以让性能提高很多。

选择合适的数据类型

要选择合适的数据类型必须要先了解不同数据类型间的差异。

数字类型有整数类型和浮点数类型，还有一类是通过二进制格式以字符串来存放的数字类型，如DECIMAL(size,d)，其存放长度主要通过定义的size决定，size定义多大，则实际存放就有多长。默认的size为10，d为0。这种类型的存放长度较长而且完全可以用整形来代替实现，所以不推荐使用。

时间类型主要使用DATE，DATETIME和TIMESTAMP三种类型，TIMESTAMP占用存储空间最少，只要4个字节，其它两种类型都要占用8个字节。从存储内容来看，TIMESTAMP只能存储1970年之后的时间，另外两种都能存储从1001开始的时间。

特别要说明的是varchar类型，varchar(size)，在mysql5.0.3之前size表示的是字节数，mysql5.0.3之后size表示的是字符数。这里我们只关注mysql5.0.3之后的表示，size表示的字符数最大限制和字符集有关，如果是gbk编码，最大长度为(65535-1-2)/2=32766，减1的原因是实际行存储从第二个字节开始，减2的原因是varchar头部的2个字节表示长度，除2因为是gbk编码；如果是utf8编码，最大长度为(65535-1-2)/3=21844。

如果数据量一样，但数据类型更小的话，数据存放同样的数据就会占用更少的空间，这样检索同样的数据所带来的IO消耗自然会降低，性能也就很自然的得到提升。此外，mysql对不同类型的数据，处理方式也不一样，比如在运算或者排序操作中，越简单的数据类型操作性能越高，所以对于要频繁进行运算或者排序的字段尽量选择简单的数据类型。