MySQL Schema设计优化

Published on 2016 - 06 - 07

高效的模型设计

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

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

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

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

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

所以,对于基于性能的数据库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表),如示例代码1所示:

优化后实现列表功能的Query和执行计划如示例代码2所示:

从优化前和优化后的执行计划可以看出两者的差别非常大,优化前必须检索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设计以提高系统的整体性能。在很多大型的应用中,由于数据量非常庞大,并发访问又非常多,当单台主机无法支撑单个表的访问时,常常会通过这种大表的水平拆分,并存放在多台主机的多个数据库中实现整体扩展性的提升。

统计表——准实时优化

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

为什么要准实时?

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

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

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

  • 首先,统计信息的准确性要求并不是特别严格;
  • 其次,统计信息对时间并不是太敏感;
  • 再次,统计信息的访问非常频繁,重复执行较多;
  • 最后,参与统计数据量较大;

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

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

合适的数据类型

实际上在很多数据库的设计优化文档中都有关于优化数据类型的说明内容,在MySQL中,同样也可以通过数据类型的优化达到优化整个Schema设计的目的。

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

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

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

数字日期类型

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

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

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

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

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

字符存储类型

再来看看存放字符的数据类型,见表2。

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

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

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

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

  • 不能设置默认值;
  • 只有TEXT可以使用TEXT[(M)]这样的方式通过M设置大小;
  • 基于这4种类型的索引必须指定前缀长度。

其他常用类型

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

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

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

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

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

规范的对象命名

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

对于数据库对象的命名规范其实可以很简单,而且业界也并不存在一个严格的统一规定,只需要在一个公司内足够统一基本就可以了。
一般来说,应该注意以下一些方面:

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

这样,当DBA维护相关数据库对象时,新开发人员进行程序开发时,相关技术(或非技术)人员整理业务逻辑和数据关系时,都能非常容易地理解其中的关系。

  • 服务于同一子模块的一类表尽量以子模块名(或部分单词)为前缀或后缀

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

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

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

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

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

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

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

  • 约束等其他对象也应该尽可能包含所属表或其他对象的名称,以表明各自关系

参考文档