MySQL数据库Schema设计的性能优化

MySQL数据库Schema设计的性能优化

很多人都认为性能是在通过编写代码（程序代码或者是数据库代码）的过程中优化出来的，其实这是一个非常大的误区。真正影响性能最大的部分是在设计中就已经产生了的，后期的优化很多时候所能够带来的改善都只是在解决前期设计所遗留下来的一些问题而已，而且能够解决的问题通常也比较有限。本章将就如何在MySQL 数据库Schema 设计的时候保证尽可能的高效，尽可能减少后期的烦恼。

高效的模型设计

最规范的就一定是最合理的吗？

在数据库Schema 设计理论方面，一直有一个被大家奉为“葵花宝典”的规范化范式理论。通过范式理论所设计的数据库Schema 逻辑清晰，关系明确，扩展方便，就连存储的数据量也做到了尽可能的少，尤其是当范式级别较高的时候，几乎找不到任何的冗余数据。在很多人眼里，数据库Schema 满足的范式级别越高则该Schema 设计的越优秀。

但是，很多人忽略了一点，那就是产生该理论的时期和出发点。关系性数据库的规范化范式理论诞生于上世纪七十年代初，最根本的目的是让数据库中尽量的去除数据的冗余，保持数据的一致，使数据的修改简单。

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

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

所以，对于基于性能的数据库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 er_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, er_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 逻辑，那所带来的网络开销将会是非常巨