MySQL性能优化,高效的模型设计

Anonymity | | 访问(133)

  1、适度冗余——让Query尽量减少Join

  适度冗余策略是将别的表中的字段拿过来在自己身上也存一份。

  虽然处理普通Join时一般都能比较智能地得到比较高效的执行计划,但是当遇到一些自查询或较为复杂的Join时,很容易出现不太合理的执行计划,不少时候对各表的访问顺序选择得并不合适,造成复杂Query的整体执行效率低下。所以,为了让Query执行计划尽可能地优化,最直接有效的方式就是减少Join,而要减少Join,就不可避免地需要通过表字段的冗余来实现。

  从数据库范式理论来看,这样的设计是不太合理的。因为可能造成user表和group_message表中的用户昵称数据不一致。每次更新用户昵称时,都须要更新两个表的数据,为了尽可能让两者数据保证一致,应用程序中须要处理更多的逻辑。但是,从性能角度来看,这种冗余是非常有价值的,虽然数据更新逻辑复杂了,但是在考虑更新带来的附加成本时,还应考虑到底会有多少更新发生在用户昵称上面。我们须要考虑的是一个系统的整体性能,而不是系统中单个行为的性能。就像示例中的昵称数据,虽然更新的成本增加了,但是查询的效率提高了,而且发生该查询的频率要远大于更新的频率,通过少部分操作的成本投入换取更大的性能收获,实际上是系统性能优化中经常使用的策略。

  2、大字段垂直分拆——summary表优化

  大字段垂直拆分简单来说就是将自己身上的字段拆分出去放在另外(单独)的表里。

  首先大字段一般都是存放着一些较长的Detail信息,如文章的内容、帖子的内容、产品的介绍等。

  其次是和表中其他字段相比访问频率明显要少很多的。数据库中数据在数据文件中的格式一般都是以单条记录为单位来存放的。也就是说,如果要查询某些记录的某几个字段,数据库并不是只须访问要查询的那几个字段,而是须要读取其他所有字段(可以在索引中完成整个查询的情况除外)。这样,就不得不读取包括大字段在内的很多并不相干的数据。而由于大字段所占的空间比例非常大,自然所浪费的IO资源也就相当大了。

  在这样的场景下,须要将该大字段从原表中拆分出来,通过单独的表进行存放,让我们在访问其他数据时大大降低IO访问,从而使性能得到较大的改善。

  可能有人会疑惑,虽然移出之后访问其他字段的效率提高了,但是当需要大字段的信息时,就无法避免的须要通过Join来实现,而使用Join之后的处理效率可能会大打折扣。其实这样的担心是很合理的,这也就是在分拆出大字段之前还须考虑的第二个因素——访问频率。前面已经介绍了,决定一个字段是否要分拆出去,除了“大”之外,还要“频率低”才行,当然,这里的“频率低”只是“相对频率”而已。而且,分拆之后的两个表是完全确定的一一对应关系,使用Join在性能方面的影响也并不是特别大。

  那在移出大字段的同时,是否还须将其他字段也一并移出呢?其实如果已经确定有大字段须要分拆出主表,对于其他字段,只要和大字段一样满足访问频率相对于表中其他字段低很多的,都可以和大字段同时分拆出来。

  在这种场景下,有的表中大部分字段平时都很少访问,而其中的某几个字段访问频率却非常高。对于这种表,也非常适合通过垂直分拆来达到优化性能的目的。

  3、大表水平分拆——基于类型的分拆优化

  假设有一个论坛帖子表,分普通会员发的和系统管理员发的!现在要求帖子列表每一页都要置顶显示管理员发的帖子!

  首先,置顶信息和其他讨论帖完全不会产生任何关联交互;

  其次,置顶信息的变化相对于其他讨论帖来说很少;

  再次,置顶信息的访问频率非常高;

  最后,置顶信息的量和普通讨论帖相比非常少;

  通过上面的分析,如果将置顶信息单独存放在普通讨论帖之外的其他表里,不但不会带来将会附加的性能消耗,而且可以使每次检索置顶信息的成本都有所下降。由于访问频率非常高,因为每次检索置顶信息的成本下降而总成本将会得到较大的节省。

  通过上面的分析,很容易得出一个更为优化的方案来存放这些置顶信息,即新增一张类似于group_message的表来专门存放置顶信息,暂且命名为group_message_top。