MySql（九）：MySQL性能调优——Schema设计的性能优化

一、高效的模型设计

先了解下数据库设计的三大范式

第一范式：要求有主键，并且要求每一个字段原子性不可再分

第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖

第三范式：所有非主键字段和主键字段之间不能产生传递依赖

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

1.适度冗余- 让Query 尽量减少Join

熟悉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: <derived2>

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逻辑，那所带来的网络开销将会是非常巨大的。

简单总结下，在一些复杂的join查询里，如果某个join的列很少更新，却频繁查询，可以适当冗余，减少join的复杂度。

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

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

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

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

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

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

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

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

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

那我们在移出大字段的同时，是否还需要将其他字段也一并移出呢？

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

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

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

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

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

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

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

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

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

再次，置顶信息的访问频率非常高；

最后，置顶信息的量和普通讨论帖来比非常之少；

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

4.统计表- 准实时优化

简单来说就是通过定时统计数据来替代实时统计查询。

为什么要准实时？

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

当然，并不是所有的统计数据都适合于通过准实时的统计表优化策略来实现的，即使我们希望，产品经理们也不会允许，即使产品经理们也希望那样，我们的使用者肯定也会不同意。

什么类型的统计信息适合通过准实时统计表来优化实现？

首先，统计信息的准确性要求并不是特别的严格；

其次，统计信息对时间并不是太敏感；

再次，统计信息的访问非常频繁，重复执行较多；

最后，参与统计数据量较大；

看看上面的要求，还真不少。不过，大家所维护的系统中确实很可能存在这样的统计数据展示功能。如系统当前在线人数，论坛系统当前总帖数、回帖数等，多条件大结果集查询页面的总结果数以及总页数，某些虚拟积分的top n排名等等。

这些统计的计算都会涉及到大量的数据，同时也需要大量的计算资源，访问频率也都非常的高。如果都通过实时统计，恐怕只要数据量稍微大一些，都会带来非常大的硬件资源开销。但在短时间内的不够精确，又并不会带来太大用户体验的降低。所以完全可以通过定时任务程序，每隔一定时间段进行一次统计后存放在专门设计的统计表中。这样，在统计数据需要展示的时候，我们只需要从统计好的结果数据中取出即可。这样每次统计数据的展示性能将会成数量级的提升，反而会使整体的用户体验上升。

二、合适的数据类型

优化数据类型提高性能的主要原理在于以下几个方面：

1. 通过选用更“小”的数据类型减少存储空间，使查询相同数据需要的IO 资源降低；
2. 通过合适的数据类型加速数据的比较；

下面我们还是通过分析一些常用数据类型的数据存储格式和长度来看看哪些数据类型可以在优化中利用上吧。

数字日期类型

我们先来看看存放长度基本固定的一些数据类型的存储长度和取值范围。

类型（同义词）	存储长度	最小值（无符号）	最大值（无符号）
整型数字
TINYINT	1	－128（0）	127（255）
SMALLINT	2	-32768（0）	32767（65535）
MEDIUMINT	3	-8388608（0）	8388607（16777215）
INT（INTEGER）	4	-2147483648（0）	2147483647（4294967295）
BIGINT	8	-9223372036854775808 （0）	9223372036854775807 （18446744073709551615）
小数支持
FLOAT[(M[,D])]	4 or 8	-3.402823466E+38-1.175494351E-38 0 1.175494351E-38～3.402823466E+38
DOUBLE[(M[,D])]（REAL, DOUBLE PRECISION）	8	-1.7976931348623157E+308～-2.2250738585072014E-308; 0 2.2250738585072014E-308～ 1.7976931348623157E+308

对于数字类型，这里分别列出了整数类型和小数类型，也就是浮点数类型。实际上，还有一类通过二进制格式以字符串来存放的数字类型如DECIMAL(DEC)[(M[,D])]，NUMERIC[(M[,D])]，由于其存放长度主要通过其定义时候的的M所决定，M定义为多大，则实际存放就有多长。M代表整个位数长度，而D则表示小数点后的位数，默认M为10，D为0。一般来说，主要用在固定精度的场合，由于其存放长度较大，而且考虑到这种数据完全可以变化形式以整数存放，所以笔者个人并不是特别推荐。

对于数字的存储，一般使用到浮点型数据的场合也不应该太多。主要出于两个原因，一个是浮点型数据本身实际上是一个并不精确的数字，只是一个近似值，另一个原因就是完全可以通过乘以一个固定的系数转换为整型数据来存放。这样不仅可以解决数据不精确的问题，同时也让数据的处理更为高效。

时间类型

DATETIME	8	1001-01-01 00:00:00		9999-12-31 23:59:59
DATE	3	1001-01-01		9999-12-31
TIME	3	00:00:00		23:59:59
YEAR	1	1001		9999
TIMESTAMP	4	1970-01-01 00:00:00

时间存储格式总类并不是太多，我们常用的主要就是DATETIME，DATE和TIMESTAMP这三种了。从存储空间来看TIMESTAMP最少，四个字节，而其他两种数据类型都是八个字节，多了一倍。而TIMESTAMP的缺点在于他只能存储从1970年之后的时间，而另外两种时间类型可以存放最早从1001年开始的时间。如果有需要存放早于1970年之前的时间的需求，我们必须放弃TIMESTAMP类型，但是只要我们不需要使用1970年之前的时间，最好尽量使用TIMESTAMP来减少存储空间的占用。

上面所列出的主要是一些存放固定长度，且我们平时可能常用到的一些类型。通过这个对照表格，我们可以很直观的看出哪种类型占用的存储空间大，哪种占用的空间小。这样，在数据类型选择的时候，我们就可以结合各种类型的存储范围以及业务中可能存在的数据作出对应，然后选择存储空间最先的类型来使用。

字符存储类型

我们再来看看存放字符的数据类型。

类型	存储占用最大空间
CHAR[(M)]	255 characters(independent of charset)
VARCHAR[(M)]	65535 bytes or 255 characters
TINYTEXT[(M)]	255 characters（sigle-byte）
TEXT[(M)]	65535 characters（sigle-byte）
MEDIUMTEXT[(M)]	16777215 characters（sigle-byte）
LONGTEXT[(M)]	4294967295 characters（sigle-byte）

CHAR[(M)]类型属于静态长度类型，存放长度完全以字符数来计算，所以最终的存储长度是基于字符集的，如latin1则最大存储长度为255字节，但是如果使用gbk则最大存储长度为510字节。CHAR类型的存储特点是不管我们实际存放多长数据，在数据库中都会存放M个字符，不够的通过空格补上，M默认为1。虽然CHAR会通过空格补齐存放的空间，但是在访问数据的时候，MySQL会忽略最后的所有空格，所以如果我们的实际数据中如果在最后确实需要空格，则不能使用CHAR类型来存放。在MySQL5.0.3之前的版本中，如果我们定义CHAR的时候M值超过255，MySQL会自动将CHAR类型进行转换为可以存入对应数据量的TEXT类型，如CHAR(1000)会自动转换为TEXT，CHAR(10000)则会转为MEDIUMTEXT。而从MySQL5.0.3开始，所有超过255的定义MySQL都会直接拒绝并给出错误信息，不再自动转换。

VARCHAR[(M)]属于动态存储长度类型，仅存占用实际存储数据的长度。其存放的最大长度与MySQL版本有关，在5.0.3之前的版本VARCHAR以字符数控制最存储的最大长度，最大只能存放255个字符，占用存储空间的实际大小与字符集有关。但是从5.0.3开始，VARCHAR的最大存储限制已经更改为字节数限制了，扩展到可以存放65535 bytes的数据，不同的字符集可能存放的字符数并不一样。也就是说，在MySQL5.0.3之前的版本，M所代表的是字符数，而从5.0.3版本开始，M的代表意思已经是字节数了。VARCHAR的存储特点是不管我们设定M为多大的值，真正占用的存储空间都只有我们所存入的实际数据的大小，和CHAR不同的是VARCHAR会保留我们存入数据最后的空格，也就是说我们存入是什么样，MySQL返回给我们的也会是什么样。在VARCHAR类型字段的数据中，MySQL会在每个VARCHAR数据中使用1个或者2个字节用来存放VARCHAR数据的实际长度，当我们的实际数据在255字节之内的时候，会使用1字节来存放实际长度，而大于255字节的时候，则需要使用2字节来存放。

更多比较：MySQL中char与varchar的区别

TINYTEXT，TEXT，MEDIUMTEXT和LONGTEXT这四种类型同属于一种存储方式，都是动态存储长度类型，不同的仅仅是最大长度的限制。四种类型的定义都是通过最大字符数来限制，但是他们的字符数限制实际上是可以理解为字节数限制的，因为当我们使用多字节字符集的时候，实际能存放的字符数并没最大字符数那么多，而是以单字节字符来计算的字符数。此外，由于是动态存储长度类型，所以和VARCHAR一样，每个字段数据之前都需要一个存放实际长度的空间。TINYTEXT需要1个字节来存放，TEXT需要2个字节，MEDIUMTEXT和LONGTEXT则分别需要3个和4个字节来存放实际数据长度。实际上，除了MySQL内嵌的最大长度限制之外，他们还受到客户端与服务器端的网络通信缓冲区最大值（max_allowed_packet）的限制。

这四种TEXT类型和CHAR及VARCHAR在实际使用中存在几个不一样的地方：

◆     不能设置默认值；

◆     只有TEXT可以使用TEXT[(M)]这样的方式通过M设置大小；

◆     基于这四种类型的索引必须指定前缀长度；

其他常用类型

除了上面这些字段类型之外会被我们经常使用到之外，我们还会使用到的数据类型主要有以下这些。

类型	存储占用最大空间
BIT[(M)]	(M+7)/8 bytes ,最大(64+7)/8
SET('v1','v2'...)	1,2,4 or 8 bytes（取决于存储值的数目，最大64个值）
ENUM('v1','v2'...)	1 or 2 bytes （取决于存储值的数目，最大65535个值）

对于BIT类型，M表示每个值的bits数目，默认为1，最大为64 bits。对于MySQL来说这是一个新的类型，因为从MySQL5.0.3才开始真正实现（在之前实际上是TINYINT（1）），而且仅仅支持MyISAM存储引擎，但是从MySQL5.0.5开始Memory，Innodb和NDB Cluster存储引擎也开始“支持”了。在MyISAM中，BIT的存储空间很小，是真正的实现了通过bit来存储，但是在其他的一些存储引擎中就不一样了，因为他们是转换为最小的INT类型存储的，所以占用的空间也没有节省，还不如直接使用INT类的数据类型存放来得直观。

对于SET和ENUM类型，主要内容基本处于较少变化状态且值比较少的字段。虽然这两个字段所占用的存储空间都较少，但是由于在使用方面较其他的数据类型要略为复杂一些，所以在实际环境中一般使用还是较少。

更多参阅：mysql中enum类型理解

谁都知道，数据量（这里主要指数据记录条数）的增加肯定会让数据库的检索查询效率降低。所以很多时候人们大都希望通过减少数据库中关键表的记录条数来获得数据库性能的提升。实际上，除了这种通过控制数据记录条数来控制数据总量的办法之外，我们还可以通过选择更小的数据类型来让数据库通过更小的空间存放相同的数据量，这对于检索同样的数据所带来的IO消耗自然会降低，性能也就很自然得到了提升。

此外，由于CPU对不同数据的处理方式不一样，就会造成不同类型的数据在各种运算处理如比较，排序等方面的处理效率存在差异。所以，对于需要经常进行比较计算以及排序等消耗CPU资源的字段，应该尽量选择处理更为迅速的字段类型。如通过整数类型代替浮点数或者字符类型。

三、规范的对象命名

规范的命名本身并不会对性能有任何影响，在这里单独列出一节来讲，主要是因为这是一个不太被人重视，但是对后期的数据库维护影响非常大的内容。就像编程语言各自的一些不成文编码基本规范一样，虽然在最初使用的时候并看不错太多的利益，反而会被人认为是一种束缚，但是当每一个人在接手维护一段编写很不规范的代码的时候，我估计大部分人都会非常郁闷，甚至在心里暗骂当初的编写者。其实任何系统都一样，没有任何规范可循，完全一个天马行空的作风，只会给后人（甚至可能是自己）留下一个让人摸不着头脑的烂摊子，难以维护。

对于数据库对象的命名规范其实可以很简单，而且业界也并不存在一个严格的统一规定，只需要在一个公司内足够统一基本就可以了。

一般来说，我个人建议需要注意以下一些方面：

1、 数据库和表名应尽可能和所服务的业务模块名一致；

这样，在 DBA 维护相关数据库对象的时候，新开发人员程序开发过程中，相关技术（或非技术）人员整理业务逻辑和数据关系的时候，都能够非常容易理解其中的关系。

2、 服务于同一子模块的一类表尽量以子模块名（或部分单词）为前缀或后缀；

对同类功能的表增加前缀或者后缀，也是让查看使用该表的各类人员能够很快的根据相关对象的名称就联想到相应的功能，以及相关业务。不论是从维护角度，还是从使用角度来看都会带来非常大的便利性。

3、 表名应尽量包含与所存放数据相对应的单词；

这对于新员工来说尤其重要，要想尽快的熟悉数据，尽快了解相关业务，快速的定位数据库中各表对应的数据意义是非常有帮助的。

4、 字段名称也尽量保持和实际数据相对应

这一点的意义我想各位读者朋友应该都非常的清楚，每个表都会有很多的字段对应数据的各种不同属性，要搞清楚各自代表的含义，除了完整规范的说明文档之外，命名清晰合理的字段名也是一个有用的补充，而且更为直接。

5、 索引名称尽量包含所有的索引键字段名或者缩写，且各字段名在索引名中的顺序应与索引键在索引中的索引顺序一致，且尽量包含一个类似于 idx 或者 ind 之类的前缀或者后缀，以表名其对象类型是索引，同时还可以包含该索引所属表的名称；

这样做最大的好处在于 DBA 在维护过程中能够非常直接清晰的通过索引名称就了解到该索引大部分的信息。

6、 约束等其他对象也应该尽可能包含所属表或其他对象的名称，以表名各自关系。

上面列出的只是一个比较初略的规范建议，各位读者朋友完全可以根据各自公司的习惯，制定自己的命名规范，只要适用，就可以了。规范不在多，而在实用。而且一旦制定的规范，就必须严格的按照规范执行，否则就变成了个花架子没有任何实际的意义了。

四、小结

通过这一章的内容，希望能够让大家明白一个道理，“数据库系统的性能不是优化出来的，更多的是设计出来的”。数据库 Schema 的设计并不如很多人想象的那样只是一个简单的对象对应实现，而是一个系统工程。要想设计出一个既性能高效又足够满足业务需求，既逻辑清晰又关系简单的数据库 Schema 结构，不仅仅需要足够的数据库系统知识，还需要足够了解应用系统的业务逻辑。