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

最新推荐文章于 2024-08-28 10:00:00 发布

2401_83977689

最新推荐文章于 2024-08-28 10:00:00 发布

阅读量509

点赞数 13

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

本文链接：https://blog.csdn.net/2401_83977689/article/details/138332060

版权

程序员专栏收录该内容

215 篇文章 0 订阅

订阅专栏

实际上，尽量去除数据的冗余不仅仅是为了让我们查询相同的数据量的时候能够多返回几条记录，还有一个很重要的原因就是在当时的那个年代，数据的存储空间是及其昂贵的，而且存储设备的容量也都非常的小，这一点在硬件存储设备发展如此迅速的如今，空间大小已经不再是太大的问题了。

而范式理论中的数据一致性和使数据修改简单保证主要是依靠添在数据库中添加各种约束来保证，而各种约束对于数据库来说本身其实就是一个非常消耗资源的事情。

所以，对于基于性能的数据库 Schema 设计，我们并不能完全以规范化范式理论来作为唯一的指导。在设计过程中，应该从实际需求出发，以性能提升为根本目标来展开设计工作，很多时候为了尽可能提高性能，我们必须做反范式设计。

①适度冗余 - 让 Query 尽两减少 Join

熟悉 MySQL 的优化器的读者可能清楚，MySQL 的优化器虽然号称使用了新一代的优化器技术实现的非常优秀，但是由于目前 MySQL 所收集的数据统计信息还不是特别的多，所以起表现并不是特别的让人满意，也并非如 MySQL 官方所宣传的那样智能。虽然处理普通 Join 的时候一般都能比较智能的得到比较高效的执行计划，但是当遇到一些自查询或者较为复杂的 Join 的时候，很容易出现不太合理的执行计划，不少时候对各表的访问顺序选择的并不合适，造成复杂 Query 的整体执行效率低下。

所以，为了让我们的 Query 执行计划尽可能的最优化，最直接有效的方式就是尽量减少 Join，而要减少 Join，我们就不可避免的需要通过表字段的冗余来实现。

方案一 group_message 表中仅保存了发布信息者的 ID 信息，而通过冗余优化之后的 group_message 表中增加了发布信息者的 nick_name 信息存为 author。

优化前实现列表功能的 Query 和执行计划（group_message_bad 是优化前的表，优化后为 group_message 表）：

sky@localhost : example 09:13:41> explain
-> SELECT t.id, t.subject,user.id, user.nick_name
-> FROM (
-> SELECT id, user_id, subject
-> FROM group_message
-> WHERE group_id = 1
-> ORDER BY gmt_modified DESC LIMIT 1,10
-> ) t, user
-> WHERE t.user_id = user.id\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table:
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: user
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: group_message
type: ALL
possible_keys: group_message_gid_ind
key: group_message_gid_ind
key_len: 4
ref:
rows: 1
Extra: Using filesort

优化后实现列表功能的 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 在性能方面的影响也并不是特别的大。

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

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