MySQL-数据库-Schema-设计的性能优化①：高效的模型设计

code高级开源

于 2024-03-18 02:25:28 发布

阅读量781

点赞数 20

分类专栏： Java程序员文章标签：数据库 mysql 性能优化

本文链接：https://blog.csdn.net/code8889/article/details/136795140

版权

Java程序员专栏收录该内容

8 篇文章 0 订阅

订阅专栏

优化后实现列表功能的 Query 和执行计划：

sky@localhost : example 09:14:06> explain
-> SELECT t.id, t.subject, t.user_id, t.author
-> FROM group_message t
-> WHERE group_id = 1
-> ORDER BY gmt_modified DESC LIMIT 1,10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: ref
possible_keys: group_message_gid_ind
key: group_message_gid_ind
key_len: 4
ref: const
rows: 1
Extra: Using where; Using filesort

从优化前和优化后的执行计划可以看出两者的差别非常大的，优化前必须检索 2 个表（group_message 和 user）才能得到结果，而优化后只需要检索 group_message 一个表就可以完成，因为我们将“作者”信息冗余到了 group_message。

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

在大部分应用系统中，类似于上面示例中的这种查询频繁但是更新较少的数据非常非常多，很多时候如果我们一味的追求范式化理论的 Schema 设计在高性能要求的系统中是非常不合适的。我个人认为，数据库的规范化理论其实质是在概念上的单一化，虽然规范后的数据库中的表一般都较小，使表中相关列最少。这虽然可能在某些情况下增强了数据库的可维护性，但在系统要完成一些数据的查询检索时，可能要用复杂的 Join 才能实现，这势必会造成查询检索的性能低下。如果我们通过拆分 Join，通过多次简单的查询来在应用中实现 Join 逻辑，那所带来的网络开销将会是非常巨大的。

②大字段垂直分拆 - summary 表优化

实际上，在上面的示例中我们同时还用到了另外一种优化策略，也就是“大字段垂直拆分”策略。大字段垂直拆分策略相对于前面介绍的适度冗余策略在做法上可以说产不多是完全相反的做法。适度冗余策略是将别的表中的字段拿过来在自己身上也存一份数据，而大字段垂直拆分简单来说就是将自己身上的字段拆分出去放在另外（单独）的表里面。

可能很多读者朋友都会有疑惑了，我们刚刚才分析出了将别的字段拿过来放自己表里面为什么现在又要将自己的字段分出去呢？这样不是有些自相矛盾了吗？

其实并没有任何矛盾，前面我们将别人的字段那过来，是因为我们很多时候的查询需要使用该字段，为了减少 Join 带来的性能消耗才拿过来的。而我们将大字段拿出去，也是将一些我们在大部分查询中并不需要使用该字段的时候才会拿出去。而且，在我们拿出去之前，我们肯定会通过全面的评估比较之后才能做出拆分出去的决定。

那到底什么样的字段适合于从表中拆分出去呢？

首要肯定是大字段。为什么？原因很简单，就是因为他的大。大字段一般都是存放着一些较长的 Detail 信息，如文章的内容，帖子的内容，产品的介绍等等。

其次是和表中其他字段相比访问频率明显要少很多。由于大字段存放的内容较多，大部分情况都是占整条记录的 80%以上，而数据库中数据在数据文件中的格式一般都是以一条一条记录为单位来存放。也就是说，如果我们要查询某些记录的某几个字段，数据库并不是只需要访问我们需要查询的哪几个字段，而是需要读取其他所有字段（可以在索引中完成整个查询的情况除外），也无法做到只读取我们需要的几个字段的数据。这样，我们就不得不读取包括大字段在内的很多并不相干的数据。而由于大字段所占的空间比例非常大，自然所浪费的 IO 资源也就非常之大了。

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

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

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

实际上，在有些时候，我们甚至都不一定非要大字段才能进行垂直分拆。在有些场景下，有的表中大部分字段平时都很少访问，而其中的某几个字段却是访问频率非常高。对于这种表，也非常适合通过垂直分拆来达到优化性能的目的。

③大表水平分拆 - 基于类型的分拆优化

“大表水平拆分”策略在性能优化方面可能被人使用的频率并不是太多，但是如果使用得当，很可能会给我们带来不小的惊喜。

我们还是直接通过实例来说明问题吧。假设我们将前面示例中的需求稍微做一下扩展，我们希望 group 系统总管理员能够发布系统消息，而且在每一个 group 的讨论帖的没一页都能置顶显示。

在得到该需求之后，我们的第一反应肯定是通过在 group_message 表中增加一个标识列，用来存放帖子的类型，标识出是普通会员的讨论贴还是系统管理员的置顶帖。然后在每个列表展示页面都通过对 group_message表的两次查询（一次置顶信息，一次普通讨论帖）然后在应用程序中合并再展示。这样的结果是由于整个 group_message 表的数据较大，查询置顶信息的 Query 成本会相对有些高。

下面我们换一个思路来考虑一下这个问题：

首先，置顶信息和其他讨论帖完全不会产生任何关联交互；
其次，置顶信息的变化相对于其他讨论帖来说变化很少；
再次，置顶信息的访问频率非常高；
最后，置顶信息的量和普通讨论帖来比非常之少；

通过上面的这几个分析，如果我们将置顶信息单独存放在普通讨论帖之外的其他表里面，首先不会带来什么附加的性能消耗，而且可以使每次检索置顶信息的成本都有所下降。由于访问频率非常的高，则因为每次检索置顶信息的成本下降而得到较大的节省。数量少而且变化不怎么频繁的特点则非常适合使用 MySQL 的 Query Cache，而如果和普通讨论帖在一起由于普通讨论帖的频繁变化带来 group_message 表相关的 Query Cache 失效问题会让他无法使用 Query Cache 功能。

通过上面的分析，我们很容易得出一个更为优化的方案来存放这些置顶信息，那就是新增一张类似于 group_message 的表来专门存放置顶信息，我们暂且命名为 top_message 如下：

由于是全局的，所以省略了 group_id 信息，而 content 信息，还是同样可以存放在 group_message_content 表中。

上面仅仅只是一个示例，可能在实际应用中并不是如此的简单，但这里只是给大家一个思路，让大家知道如何通过大表的水平拆分来对通过优化 Schema 设计提供系统的整体性能。在很多大型的应用中，由于数据量非常庞大，并发访问又非常高，到达单台主机都无法支撑单个表的访问的时候，常常会通过这种大表的水平拆分，存放在多台主机的多个数据库中实现整体扩展性的提升。

④统计表 - 准实时优化

为什么要准实时？

很多人看到这个优化策略之后可能都会提出这样的质疑，为什么要改变需求将“可以实时”的统计信息做成准实时的呢？原因很简单，因为实时统计的性能消耗成本太高。因为每一次展示（也就是每一次刷新页面）都需要进行统计计算，带来大量的重复资源浪费。而做成准实时的统计信息之后，我们每次只需要访问很小的数据量即可，不需要频繁的统计计算的工作。

小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。