学新通技术网

(十六)MySQL调优:单机数据库如何在高并发场景使用

juejin 18 1
(十六)MySQL调优:单机数据库如何在高并发场景使用

引言

   在当前的IT开发行业中,系统访问量日涨、并发暴增、线上瓶颈等各种性能问题纷涌而至,性能优化成为了现时代中一个炙手可热的名词,无论是在开发、面试过程中,性能优化都是一个常谈常新的话题。而MySQL作为整个系统的后方大本营,由于是基于磁盘的原因,性能瓶颈往往也会随着流量增大而凸显出来。

   但在一个业务系统中,性能优化其实可以从多个角度出发考虑,如 架构优化、前端调优、中间件调优、网关调优、容器调优、JVM调优、接口调优、服务器调优、数据库调优.... 等,从优化类型上而言,主体可以分为三类:

  • 结构/架构优化:优化应用系统整体架构做到性能提升的目的。如:读写分离、集群热备、分布式架构、引入缓存/消息/搜索中间件、分库分表、中台架构(大数据中台、基础设施中台)等。
  • 配置/参数优化:调整应用系统中各层面的配置文件、启动参数达到优化性能的目标。如:JVM、服务器、数据库、操作系统、中间件、容器、网关参数调整等。
  • 代码/操作优化:开发者编写程序时,从代码、操作方面进行调节,达到效率更高的初衷。如:代码中使用更优秀的算法思想/设计模式、SQL优化、对中间件的操作优化等。

本章则重点讲述MySQL的性能优化策略,一般MySQL都是整个业务系统中,调优选项的重中之重,毕竟MySQL作为系统大本营,当它出现瓶颈时,会影响整个系统其他节点的运行,比如:
MySQL出现了性能瓶颈时,就算Java程序没出现瓶颈,也会因此受到限制,这也是著名的木桶效应:一个木桶能装多少水,完全取决于木桶中最短的那块木板

一、系统中性能优化的核心思维

   性能调优与线上排查问题一样,是建立在经验的基础之上才能做好的,对于调优要实事求是,任何的调优手段或技巧不要纸上谈兵,只有经过实践的才能用于生产环境,千万不要将一些没有实际依据的调优策略用于线上环境,否则可能会导致原本好好运行的应用程序,反而由于调优被调到崩溃。

1.1、单个节点层面调优的核心思想

   在一个程序中,所有的业务执行实体都为线程,应用程序的性能跟线程是直接挂钩的。而程序中的一条线程必须要经过CPU的调度才可执行,线程执行时必然也会需要数据、产生数据,最终也会和内存、磁盘打交道。因而单个节点的性能表现,不可避免的会跟CPU、内存、磁盘沾上关系。
线程越多,需要的CPU调度能力也就越强,需要的内存也越大,磁盘IO速率也会要求越快。因此CPU、内存、磁盘,这三者之间的任意之一达到了瓶颈,程序中的线程数量也会达到极限。达到极限后,系统的性能会成抛物线式下滑,从而可能导致系统整体性能下降乃至瘫痪。

由于如上原因,在考虑性能优化时,必然不能让CPU、内存、磁盘等资源的使用率达到95%+,一般而言,最大利用率控制在80-85%左右的最佳状态。

   同时,由于程序的性能跟线程挂钩,所以线程工作模型也是影响性能的重要因素。目前程序设计中主要存在三种线程处理模型:BIO、NIO、AIO(NIO2)BIO是最传统的一对一处理模型,也就是一个客户端请求分配一条线程处理。NIO的最佳实践为reactor模型,而proactor模型又作为了NIO2/AIO的落地者。绝大部分情况下,AIO的性能优于NIO,而NIO的性能又远超于BIO

所以在做性能优化时,你应该要清楚系统的性能瓶颈在哪儿,到底是要调哪个位置?是线程模型?或是CPU调度?还是内存回收?亦是磁盘IO速率?针对不同层面有不同的优化方案,并非为了追求“热词/潮流”而盲目的调优。

1.2、优秀且适用的系统架构胜过千万次调优

   一个单体架构(Tomcat+MySQL)部署的系统遇到性能问题时,能力再强,本事再大,任凭使出浑身解数也无法将其调到处理万级并发的程序,正常服务器部署的一台MySQL服务做到极致调优也难以在一秒内承载5000+QPS。一味的追求极致的优化,其实也难以解决真正大流量下的并发冲击,因此一套优秀的系统架构胜过自己千万次的调优。

   当然,也并非说项目实现时,越多的技术加进来越好,一套完善的分布式架构就必然比单体架构要好吗?其实也不见得,因为当引入的技术越多,所需要考虑的问题也会更多,耗费的成本也会越高,一个项目收益60W,结果用上最好的配置(高端的开发者+顶级的服务器+完善的分布式架构)成本耗费200W,这值得吗?答案显而易见。因此,并没有最好的技术架构,只有最适用的架构,能从现有环境及实际业务出发,选用最为合适的技术体系,这才是我们应该做的事情。如:

  • 项目业务中读写参半,单节点难以承载压力,项目集群、双主热备值得参考。
  • 项目业务中写大于读,引入消息中间件、DB分库、项目集群也可以考虑。
  • 项目业务中读大于写,引入缓存/搜索中间件、动静分离、读写分离是些不错的选择。
  • .......

当你的系统原有架构遇到性能瓶颈时,你甚至可以考虑进一步做架构优化,如:设计多级分布式缓存、缓存中间件做集群、消息中间件做集群、Java程序做集群、数据库做分库分表、搜索中间件做集群.....,慢慢的,你的系统会越来越庞大复杂,需要处理的问题也更为棘手,但带来的效果也显而易见,随着系统的结构不断变化,承载百万级、千万级、亿级、乃至更大级别的流量也并非难事。

   但只有当你的业务流量/访问压力在选用其他架构无法承载时,你才应该考虑更为庞大的架构。当然,如果项目在起步初期就有预估会承载巨大的流量压力,那么提前考虑也很在理,采用分布式/微服务架构也并非失策,因为对比其他架构体系而言,微服务架构的拓展性更为灵活。但也需要记住:分布式/微服务体系是很好,但它不一定适用于你的项目。

1.3、预防大于一切,调优并非“临时抱佛脚”

   当问题出现时再想办法解决,这种策略永远都属于下下策,防范于未然才是最佳方案,提前防范问题出现主要可分为两个阶段:

  • ①项目初期预测未来的流量压力,提前根据业务设计出合适的架构,确保上线后可以承载业务的正常增长。
  • ②项目上线后,配备完善的监控系统,在性能瓶颈来临前设好警报线,确保能够在真正的性能瓶颈到来之前解决问题。

对于项目初期的架构思考,值得牢记的一点是:不要“卡点”设计,也不能过度设计造成性能过剩,举例:

项目上线后的正常情况下,流量大概在“一木桶”左右,结果你设计时直接整出一个承载“池塘”级别的流量结构,这显然是不合理的,毕竟架构体系越庞大,项目的成本也自然就越高。
当然,也不能说正常情况下压力在“一木桶”左右,就只设计出一套仅能够承载“一木桶”流量的结构,这种“卡点”设计的策略也不可取,因为你需要适当考虑业务增长带来的风险,如果“卡点”设计,那么很容易让项目上线后,短期内就遭遇性能瓶颈。
因此,如果项目正常的访问压力大概在“桶”级别,那将结构设计到“缸”级别最合理,这样即不必担心过度设计带来的性能过剩,导致成本增高,也无需考虑卡点设计造成的:项目短期遭遇性能瓶颈。
但设计时的这个度,必须由你自己根据项目的业务场景和环境去思量,不存在千篇一律的方法可教。

有人曾说过:“如果你可以根据业务情景设计出一套能确保业务增长,且在线上能稳定运行三年时间以上的结构,那你就是位业内的顶尖架构”,但老话说的好:“计划永远赶不上变化”,就算思考到业务的每个细节,也不可能设计出一套一劳永逸的结构出现,我们永远无法判断意外和明天哪个先来。因而,项目上线后,配备完善的监控警报系统也是必不可少的。不过值得注意的是:

监控系统的作用并不是用来提醒你项目“嗝屁”了的,而是用来提醒你:线上部署的应用系统可能会“嗝屁”或快“嗝屁”了,毕竟当项目灾难已经发生时再给警报,那到时候的情况就是:“亡羊补牢,为时已晚”。
通常情况下,在监控系统上面设置的性能阈值都会比最大极限值要低5~15%,如:最大极限值是85%,那设置告警值一般是75%左右就会告警,不会真达到85%才告警,只有这样做才能留有足够的时间让运维和开发人员介入排查。当系统发出可能“嗝屁”的警告时,开发和运维人员就应当立即排查相关的故障隐患,然后再通过不断的修改和优化,提前将可能会出现的性能瓶颈解决,这才是性能调优的正确方案。
因此,最终结论为:绝不能等到系统奔溃才去优化,预防胜于一切

1.4、无需追求完美,理性权衡利弊

   “追求极致,做到完美”这点是大部分开发者的通病,很多人会因为这个思想导致自己在面临一些问题时束手无策,比如举个例子:

业务:MacBookPro一元购活动,预计访问压力:10000QPS
环境:单台机器只能承载2000QPS,目前机房中还剩余两台空闲服务器。
状况:此时就算将空闲的两台机器加上去,也无法顶住目前的访问压力。
此时你会怎么做?很多人都会茫然、会束手无策,这看起来好像是没办法的事情呀.....

但事实真的如此吗?并非如此,其实这种情况也有多种解决方案,如:

  • ①停掉系统中部分非核心的业务,将服务器资源暂时让给该业务。
  • ②抛弃掉部分用户的请求,只接受处理部分用户的请求,对于抛弃的用户请求直接返回信息提示。
  • ③........

这些方案是不是可以解决上面的哪个问题呢?答案是肯定的,所以适当舍弃一部分是这种场景下最佳方案,千万不能抱有追求完美的想法,例如:

  • 系统中的服务不能停啊,得保持正常服务啊,否则影响程序功能的完善性。
  • 用户的请求怎么能抛,用户的访问必须得响应啊,否则影响用户体验感。

但事实告诉你的是:类似于京东、淘宝、12306等这些国内的顶级门户网站,在处理高并发场景时照样如此。好比阿里,在双十一的时候都会抽调很多冷门业务的服务器资源给淘宝使用,也包括你在参与这些电商平台的抢购或秒杀类活动时,你是否遇到过如下情况:

  • 服务器繁忙,请稍后重试......
  • 服务器已满,排队中.....
  • 前方拥堵,排队中,当前第x位.....

如果当你遇到了这些情况,答案显而易见,你的请求压根就没有到后端,在前端就给你pass了,然后给你返回了一个字符串,让你傻傻的等待,实则你的请求早就被抛弃了.....

   这个例子要告诉大家的是:在处理棘手问题或优化性能时,无需刻意追求完美,理性权衡利弊后,适当的做出一些决断,抛弃掉一部分不重要的,起码比整个系统挂掉要好,何况之后也同样可以恢复。

1.5、性能调优的通核心步骤

   性能优化永远是建立在性能瓶颈之上的,如果你的系统没有出现瓶颈,那则无需调优,调优之前需要牢记的一点是:不要为了调优而调优,而是需要调优时才调

这就好比古代的赛马,本来你拥有一匹上等马,在赛马活动中基本上每次都能拿到不菲的成绩,但你嫌它还不够好,就一直想办法给马喂各种秘方,最终可能会导致拥有的这匹上等马吃出问题,这明显与初衷背道而驰,所以要牢记:千万不要为了调优而调优!就好比刚刚的上等马例子中,只有当上等马真正的出现问题时,比如近期拉肚子、近期腿受伤了.....等这类情况出现时,再对症下药,给马喂对应的“良药”,以此来保证上等马可正常工作。

   而发现性能瓶颈的方式有两种,一种是你的应用中具备完善的监控系统,能够提前感知性能瓶颈的出现。另一种则是:应用中没有搭载监控系统,性能瓶颈已经发生,从而导致应用频繁宕机。大型的系统一般都会搭载完善的监控系统,但大多数中小型项目却不具备该条件,因此,大部分中小型项目发现性能瓶颈时,大多数情况下已经“嗝屁”了。

通常而言,性能优化的步骤可分为如下几步:

  • ①发现性能瓶颈:如有监控系统,那它会主动发出警报;如若没有,那出现瓶颈时应用肯定会出问题,如:无响应、响应缓慢、频繁宕机等。
  • ②排查瓶颈原因:排查瓶颈是由于故障问题导致的,还是真的存在性能瓶颈。
  • ③定位瓶颈位置:往往一个系统都会由多个层面协同工作,然后对外提供服务,当发现性能瓶颈时,应当确定瓶颈的范围,如:网络带宽瓶颈、Java应用瓶颈、数据库瓶颈等。
  • ④解决性能瓶颈:定位到具体的瓶颈后对症下药,从结构、配置、操作等方面出发,着手解决瓶颈问题。

本章则重点是阐述MySQL相关的调优操作,但需要先提前说明的是:

单层面的性能调优其实只能当成锦上添花的作用,但绝对不能成为系统性能高/低、响应快/慢、吞吐量大/小的决定性要素。应用系统的性能本身就还算可以,那么调优的作用是让其性能更佳。但如若项目结构本身就存在问题,那么能够带来的性能提升也是有限的,如果你想让你的项目快到飞起,那么还需要从多个层面共同着手才能达到目的。

二、MySQL的性能优化实践

   其实MySQL的优化,在大型企业中都会有专业的DBA来负责,但作为一个后端开发者,我们也应该具备这个能力,毕竟大多数企业中都未曾划分出DBA这类的岗位,因此当MySQL需要做调优时,这个活最终也会落到后端身上。当然,更重要的一点是:这些内容掌握后,能够让大家面试造火箭的能力更上一层楼

2.1、MySQL调优的五个维度

   聊到MySQL的性能优化,其实也可以从多个维度出发,共计优化项如下:

  • ①客户端与连接层的优化:调整客户端DB连接池的参数和DB连接层的参数。
  • MySQL结构的优化:合理的设计库表结构,表中字段根据业务选择合适的数据类型、索引。
  • MySQL参数优化:调整参数的默认值,根据业务将各类参数调整到合适的大小。
  • ④整体架构优化:引入中间件减轻数据库压力,优化MySQL架构提高可用性。
  • ⑤编码层优化:根据库表结构、索引结构优化业务SQL语句,提高索引命中率。

纵观现在MySQL中的各类优化手段,基本上都是围绕着上述的五个维度展开,这五个性能优化项中,通常情况下,带来的性能收益排序为④ > ② > ⑤ > ③ > ①,不过带来的性能收益越大,也就意味着成本会更高,因此大家在调优时,一定要记得按需进行,不要过度调优,否则也会带来额外的成本开销!

OK,闲话说了一大堆,现在也就开始真正的调优方案剖析,一起来聊聊吧~

2.2、MySQL连接层优化策略

   从《SQL执行篇》这章中可以得知:一个用户请求最终会在Java程序中分配一条线程处理,最终会变成一条SQL发往MySQL执行,而Java程序、MySQL-Server之间是通过建立网络连接的方式进行通信,这些连接在MySQL中被称为数据库连接,本质上在MySQL内部也是一条条工作线程负责执行SQL语句,那么思考一个问题:数据库连接数是越大越好吗?

对于这个问题,答案是NO,这是为什么呢?既然说一个客户端连接是一条线程,那数据库的最大连接数调整到1W,岂不是代表着同时可以支持1W个客户端同时操作啦?在不考虑硬件的情况下确实如此,但结合硬件来看待,答案就不相同的,一起来聊聊原因。

数据库连接数越大,也就意味着内部创建出的工作线程会越多,线程越多代表需要的CPU配置得更高,比如现在有300个客户端连接,内部创建了300条工作线程处理,但服务器的CPU仅有32个核心,那当前服务器最多只能支持32条线程同时工作,那其他268条线程怎么办呢?为了其他线程能够正常执行,CPU就会以时间片调度的模式工作,不同核心在不同线程间反复切换执行,但由于线程数远超核心数,因此会导致线程上下文切换的开销,远大于线程执行的开销。

正是由于上述原因,所以数据库的连接数该设置成多少合适呢?这就是一个值得探讨的问题,而连接池又会分为客户端连接池、服务端连接池,客户端连接池是指Java自身维护的数据库连接对象,如C3P0、DBCP、Druid、HikariCP...等连接池。服务端连接池是指MySQL-Server的连接层中,自身维护的一个连接池,用来实现线程复用的目的。

对于MySQL连接池的最大连接数,这点无需咱们关心,重点调整的是客户端连接池的连接数,为啥呢?因为MySQL实例一般情况下只为单个项目提供服务,你把应用程序那边的连接数做了限制,自然也就限制了服务端的连接数。但为啥不将MySQL的最大连接数和客户端连接池的最大连接数保持一致呢?这是由于有可能你的数据库实例不仅仅只为单个项目提供服务,比如你有时候会通过终端工具远程连接MySQL,如果你将两个连接池的连接数保持一致,就很有可能导致MySQL连接数爆满,最终造成终端无法连上MySQL

好的,话接正题,客户端的连接池大小该如何设置呢?先来借鉴一下PostgreSQL提供的计算公式:
最大连接数 = (CPU核心数 * 2) + 有效磁盘数

这个公式其实不仅仅只适用于PostgreSQL,在MySQL、Oracle...中同样适用,但公式中有一点比较令人产生疑惑,即“有效磁盘数”,这是个啥?其实是指SSD固态硬盘,如果部署数据库的服务器,其硬盘是SSD类型,那么在发生磁盘IO基本上不会产生阻塞。因为SSD相较于传统的机械硬盘,它并没有的磁片,无需经过旋转磁面的方式寻址,所以SSD硬盘的情况下,可以再+1

比如目前服务器的硬件配置为CPU:16core、硬盘类型:SSD,此时最佳的最大连接数则为16*2+1=33,而常驻连接数可以调整到30左右,这组配置绝对是当前场景下的最佳配置,如若再调大连接数,这反而会让性能更慢,因为会造成线程上下文切换的额外开销。

但是要注意:C3P0、DBCP、Druid、HikariCP...等连接池的底层本质上是一个线程池,对于线程池而言,想要处理足够高的并发,那应该再配备一个较大的等待队列,也就是当目前池中无可用连接时,其他的用户请求/待执行的SQL语句则会加入队列中阻塞等待。

当然,上述这个公式虽说能够应对绝大部分情况,但实际业务中,还需要考虑SQL的执行时长,比如一类业务的SQL执行只需10ms,而另一类SQL由于业务过为繁琐,每次调用时会产生一个大事务,一次执行下来可能需要5s+,那这两种情况都采用相同的连接池可以吗?可以是可以,但大事务会影响其他正常的SQL,因此想要完美的解决这类问题,最好再单独开一个连接池,为大事务或执行耗时较长的SQL提供服务。

2.2.1、偶发高峰类业务的连接数配置

啥叫偶发高峰类业务呢?就类似于滴滴打车这类业务,在早晚上下班时间段、周末假期时间段,其流量显然会比平常高很多,对于这类业务,常驻线程数不适合太多,因为并发来临时会导致创建大量连接,而并发过后一直保持数据库连接会导致资源被占用,所以对于类似的业务,可以将最大连接数按之前的公式配置,而常驻连接数则可以配成CPU核数+1,同时缩短连接的存活时间,及时释放空闲的数据库连接,以此确保资源的合理分配。

当然,这里用滴滴举例子,是为了说明偶发高峰类业务的概念,但实际滴滴的日常并发也不低,所以这个理论套上去是不合适的,这里主要是举例说明,不要深入纠结这个栗子!只是为了告诉大家类似场景下的配置规则。

2.2.2、分库分表情况下的连接数配置

前面叭叭了一大堆,但那些都是建立在单库情况下的配置,对于读写分离、双主双写、分库分表的情况下,就不适合这样配置,毕竟部署了多个MySQL节点,也就意味着拥有多台服务器的硬件资源,因此在数据库部署了多节点的情况下,请记得根据每个节点的硬件配置,来规划出合理的连接数。

2.2.3、连接层调优小结

对于连接层的调优,实际上是指调整它的参数,即常驻连接数、最大连接数、空闲连接存活时间以及等待队列的容量,在这里聊到过一点:合理的连接数才是最好的,而并非越大越好,这个道理其实也很简单,举个栗子:

现在有个工厂,招了200个工人来做事,里面有16个技术指导员,每个工人在指导员的指挥下做事,但由于指导员和工人的比例严重失调,因此会导致工作期间,指导员的大量时间会花费在不同工位的跑动上面,一天8小时的工作时间,至少有4~5小时会用在跑工位上。

上述这个案例中,工人就是数据库连接/线程,而指导员则是CPU核心,从这个例子中可以明显感受出:工人并不是越多越好,那如果按照之前的公式来规划合理的工人数会是什么情况呢?

此时工人锐减到32个,而16个指导员,每个人只需要负责指导两个工人,完全可以坐在两个工位中间,这样谁需要指导,只需要把头扭过去就可以了,能够在最大程度上提升工作饱和度,之前浪费在跑工位上的4~5小时,能够完全省去。

此时工人数量虽然锐减,但其实效率反而会提升很多,在这个例子中所谓的“跑工位”时间,换到程序中也就是线程上下文切换的开销,一颗CPU核心在2~3条线程之间切换不会存在太大的开销,但在十多条线程之间切换,则会导致切换开销远超出线程执行本身的开销。

如若部署MySQL的服务器硬件配置更高,那也可以手动将MySQL默认的最大连接数调大,set max_connections = n;即可。

最后提一嘴:对于最佳连接数的计算,首先要把CPU核数放首位考虑,紧接着是磁盘,最后是网络带宽,因为带宽会影响SQL执行时间,综合考虑后才能计算出最合适的连接数大小。

2.3、MySQL结构的优化方案

   所谓的MySQL结构优化,主要是指三方面,即表结构、字段结构以及索引结构,这些结构如果不合理,在某些场景下也会影响数据库的性能,因此优化时也可以从结构层面出发,但对于结构的优化,一般在项目的库表设计之初就要考虑,当性能瓶颈出现时再调整结构,早就为时过晚。

这是为啥呢?因为如果在项目之初没考虑好,到了性能瓶颈出现时再去更改,由于表中已经存在数据,所以发生结构变更时,势必会由于已经存在数据,而产生一系列的连锁问题出现,也有可能会出现一些棘手问题难以推进优化方案的落地。

2.3.1、表结构的优化

①表结构设计时的第一条也是最重要的一条,字段数量一定不要太多,之前我手里有个老项目要做二开,里面30~40个字段的表比比皆是,更有甚者达到了60~70个字段一张表,这种方式显然并不合理,因为根据之前聊过的《MySQL内存篇》大家应该能够得知:InnoDB引擎基本上都会将数据操作放到内存中完成,而当一张表的字段数量越多,那么能载入内存的数据页会越少,当操作时数据不在内存,又不得不去磁盘中读取数据,这显然会很大程度上影响MySQL性能。

咱们思考一个问题:一张40~50个字段的表结构,在实际业务中会使用到它每个字段吗?答案是No,一张40~50个字段的表中,常用的字段最多只有10~20个,这类字段可以理解成热点字段,而其他的字段都属于冷字段,但由于你将所有字段都设计到了一张表中,因此就会导致载入内存时,会将一整条数据全部载入,对应的冷字段会造成额外的额外的内存浪费。

因此对于表结构的设计,正常情况下应当遵循《数据库三范式》的原则设计,尽可能的根据业务将表结构拆分的更为精细化,一方面能够确保内存中缓存的数据更多,同时也更便于维护,而且执行SQL时,效率也会越高。

一张表最多最多只能允许设计30个字段左右,否则会导致查询时的性能明显下降。


②当然,也并不是说一定要遵守三范式的设计原则,有时候经常做连表查询的字段,可以适当的在一张表中冗余几个字段,这种做法的最大好处是能够减少连表查询次数,用空间换时间的思想。但也并非是无脑做冗余,否则又会回到前面的情况,一张表中存在大量的无用字段,这里的冗余是指经常连表查询的字段。


③主键的选择一定要合适。首先一张表中必须要有主键,其次主键最好是顺序递增的数值类型,最好为int类型,关于具体原因可参考之前的《MySQL索引原理篇-主键为何推荐自增ID?》。一张表如果业务中自带自增属性的字段,最好选择这些字段作为主键,例如学生表中的学号、职工表中的工号....,如果一张表的业务中不带有这类字段,那也可以设计一个与业务无关、无意义的数值序列字段作为主键,因为这样做最适合维护表数据(跟聚簇索引有关)。

只有当迫不得已的情况下,再考虑使用其他类型的字段作为主键,但也至少需要保持递增性,比如分布式系统中的分布式ID,这种情况下就无法依靠数据库int自增去确保唯一性,就必须得通过雪花算法这类的ID生成策略,以此来确保ID在全局的唯一性。


④对于实时性要求不高的数据建立中间表。很多时候咱们为了统计一些数据时,通常情况下都会基于多表做联查,以此来确保得到统计所需的数据,但如若对于实时性的要求没那么高,就可以在库中建立相应的中间表,然后每日定期更新中间表的数据,从而达到减小连表查询的开销,同时也能进一步提升查询速度。

啥叫中间表呢?举个最简单的例子,比如排名类的统计业务,就可以这么实现,好比MOBA游戏中的战力排名,以英雄联盟、王者荣耀为例,由于每个玩家的战力在一天内都会不断变化,同时一个用户在任何时间段都有可能去查询战力排名,所以每次查询都基于数据库的多张表去联查,基于这些游戏的用户量而言,其带来的开销必然的巨大的,因此可以对英雄战力设计一张中间表,每日凌晨五点统计一次.....

上述这种做法也是大多数MOBA游戏的实现方式,但实际场景中也会结合Redis来实现,毕竟这种方式速度会更快,但这里就不多拓展了,总之记住一点即可:适当的场景下建立中间表,是一种能够带来不小性能收益的手段。


⑤根据业务特性为每张不同的表选择合适的存储引擎。其实存储引擎这块主要是在InnoDB、MyISAM两者之间做抉择,对于一些经常查询,很少发生变更的表,就可以选择MyISAM引擎,比如字典表、标签表、权限表....,因为读远大于写的表中,MyISAM性能表现会更佳,其他的表则可以使用默认的InnoDB引擎。

2.3.2、字段结构的优化

字段结构的优化其实主要指选择合适的数据类型,大多数开发在设计表字段结构时,如果要使用数值类型一般会选择int,使用字符串类型一般会选择varchar,但这些字段类型可以适当的做些调整,在《MySQL命令大全-字段数据类型》中咱们提到过,同一种数据类型也有不同范围的具体类型可选,哪在有些情况下就可以选择更合适的类型,例如:

  • 对于姓名字段,一般都会限制用户名长度,这时不要无脑用varchar,使用char类型更好。
  • 对于一些显然不会拥有太多数据的表,主键ID的类型可以从int换成tinyint、smallint、mediumit
  • 对于日期字段,不要使用字符串类型,而更应该选择datetime、timestamp,一般情况下最好为后者。
  • 对于一些固定值的字段,如性别、状态、省份、国籍等字段,可以选择使用数值型代替字符串,如果必须使用字符串类型,最好使用enum枚举类型代替varchar类型。
  • .......

总之在选择字段的数据类型时有三个原则:

  • ①在保证足够使用的范围内,选择最小数据类型,因为它们会占用更少的磁盘、内存和CPU缓存,同时在处理速度也会更快。
  • ②尽量避免索引字段值为NULL,定义字段时应尽可能使用NOT NULL关键字,因为字段空值过多会影响索引性能。
  • ③在条件允许的情况下,尽量使用最简单的类型代替复杂的类型,如IP的存储可以使用int而并非varchar,因为简单的数据类型,操作时通常需要的CPU资源更少。

2.3.3、索引结构的优化

索引结构优化主要是指根据业务创建更合适的索引,这里主要可以从四个方面考虑,下面一起来聊一聊。

①索引字段的组成尽量选择多个,如果一个表中需要建立多个索引,应适当根据业务去将多个单列索引组合成一个联合索引,这样做一方面可以节省磁盘空间,第二方面还可以充分使用索引覆盖的方式查询数据,能够在一定程度上提升数据库的整体性能。

②对一个值较长的字段建立索引时,可以选用字段值的前N个字符创建索引,也就是对于值较长的字段尽量建立前缀索引,而不是通过完整的字段值建立索引,因为之前在聊《MySQL索引实现原理》说过:索引字段值越小,单个B+Tree的节点中能存储的索引键会越多,一个节点存下的索引键越多,索引树会越矮,查询性能自然会越高。

③索引类型的选择一定要合理,对于经常做模糊查询的字段,可以建立全文索引来代替普通索引,因为基于普通索引做like查询会导致索引失效,而采用全文索引的方式做模糊查询效率会更高更快,并且全文索引的功能更为强大。

④索引结构的选择可以根据业务进行调整,在某些不会做范围查询的字段上建立索引时,可以选用hash结构代替B+Tree结构,因为Hash结构的索引是所有数据结构中最快的,散列度足够的情况下,复杂度仅为O(1)

当然,对于索引建立与索引使用更为全面的介绍,可参考之前的《索引应用篇》

2.4、MySQL参数优化的选项

   基于MySQL的参数调优,这应该是所有优化项中最难的一点,因为MySQL内部的参数繁多,提供给用户控制的参数都有几百个,所以想要真正的做好参数优化,必须要对MySQL真正的熟悉才行,由于我自身并非专研数据库方向的技术人,所以对参数调优就不做细致阐述了,就稍微介绍一些能够带来最大性能收益的参数调整。

但首先来思考一个问题:为什么要做参数调优呢?这其实和JVM参数调优一样,本质上每个参数的默认值,为了兼容所有业务,因此都是经过MySQL官方精心设计过的,也正因如此,所以有些参数的默认值能发挥出的性能只能算中规中矩,在服务器硬件配置不错的情况下,我们可以适当调整一些参数,来试图进行一些激进的优化,从而达到性能更佳的效果。

2.4.1、调整InnoDB缓冲区

MySQL参数中,首先最值得调整的就是InnoDB缓冲区的大小,因为InnoDB将是MySQL启动后使用最多的引擎,所以为其分配一个足够大的缓冲区,能够在最大程度上提升MySQL的性能,但是缓冲区该分配多少内存呢?有人说是机器内存的80%,但这会让其他区域没有足够的内存使用,所以最佳比例应该控制在70~75%左右,比如一台服务器的内存为32GB,将innodb_buffer_pool_size = 22938M(23GB)左右最合理。

InnoDB的缓冲区分配了足够的大小后,运行期间InnoDB会根据实际情况,去自动调整内部各区域中的数据,如热点数据页、自适应哈希索引.....,调整该区域的大小后,能直接让MySQL性能上升一个等级,至于究竟是啥原因导致的,请参考《MySQL内存篇》

同时当InnoDB缓冲区空间大于1GB时,InnoDB会自动将缓冲区划分为多个实例空间,这样做的好处在于:多线程并发执行时,可以减少并发冲突MySQL官方的建议是每个缓冲区实例必须大于1GB,因此如果机器内存较小时,例如8/16GB,可以指定为1GB,但是机器内存够大时,比如达到了32GB/64GB甚至更高,哪可以适当将每个缓冲区实例调整到2GB左右。

比如现在假设缓冲区共计拥有40GB内存,哪设置将缓冲区实例设置为innodb_buffer_pool_instances = 20个比较合适。

2.4.2、调整工作线程的缓冲区

除开可以调整InnoDB的缓冲区外,同时还可以调大sort_buffer、read_buffer、join_buffer几个区域,这几个区域在《MySQL内存篇》中介绍过,属于线程私有区域,也就意味着每条线程都拥有这些区域:

  • sort_buffer_size:排序缓冲区大小,影响group by、order by...等排序操作。
  • read_buffer_size:读取缓冲区大小,影响select...查询操作的性能。
  • join_buffer_size:联查缓冲区大小,影响join多表联查的性能。

对于这些区域,最好根据机器内存来设置为一到两倍MB,啥意思呢?比如4GB的内存,建议将其调整为4/8MB8GB的内存,建议将其调整为8/16MB.....,但这些区域的大小最好控制在64MB以下,因为线程每次执行完一条SQL后,就会将这些区域释放,所以再调大也没有必要了。

OK~,对于排序查询的操作,还可以调整一个参数:max_length_for_sort_data,这个参数关乎着MySQL排序的方式,如果排序字段值的最大长度小于该值,则会将所有要排序的字段值载入内存排序,但如果大于该值时,则会一批一批的加载排序字段值进内存,然后一边加载一边做排序。

上述这两种排序算法,显然第一种效率更高,毕竟这种方式是基于所有的数据做排序,第二种算法则是一批一批数据做排序,每批数据都可能会打乱之前排好序的数据,因此可以适当调大该参数的值(但这个值究竟多少合适,要根据具体的业务来做抉择,否则交给还是使用MySQL自己来控制)。

2.4.3、调整临时表空间

同时还可以调整tmp_table_size、max_heap_table_size两个参数,这两个参数主要是限制临时表可用的内存空间,当创建的临时表空间占用超过tmp_table_size时,就会将其他新创建的临时表转到磁盘中创建,这显然是十分违背临时表的设计初衷,毕竟创建临时表的目的就是用来加快查询速度,结果又最后又把临时表放到磁盘中去了,这反而还多了一步开销。

那么这两个参数该设置多大呢?这要根据show global status like 'created_tmp%';的统计信息来决定,用统计出来的信息:Created_tmp_disk_tables / Created_tmp_tables * 100% = 120%,达到这个标准就比较合适,但调整这个区域的值需要反复重启MySQL以及压测,因此比较费时间,如果你在项目中很少使用临时表,哪也可以不关心这块参数的调整。

2.4.4、调整空闲线程的存活时间

兜兜转转再回到数据库连接数的配置,之前讲到过:其实对于MySQL最大连接数无需做过多控制,客户端连接池那边做了调整即可,对于这点是没错的,可以通过下述命令查看数据库连接的峰值:

  • show global status like 'Max_used_connections';

一般在客户端做了连接数控制后,这个峰值一般都会在客户端最大连接数的范围之内,对于数据库连接这块唯一需要稍微调整的即是空闲连接的超时时间,即wait_timeout、interactive_timeout两个参数,这两个参数必须一同设置,否则不会生效,MySQL内部默认为8小时,也就是一个连接断开后,默认也会将对应的工作线程缓存八小时后再销毁,这里我们可以手动调整成30min~1h左右,可以让无用的连接能及时释放,减少资源的占用。

2.4.5、MySQL参数调优小结

至此就对MySQL中,几个较为关键的性能参数做了介绍,其实再细致一点的话,还有几十个影响性能的参数,但这里就不再讲解了,毕竟一方面我自己也没弄过,以免写出来误导诸位,大家感兴趣的可以自行研究。

同时对于较为细致的调优参数,作为一个后端开发也无需研究过深,毕竟真要做到这个级别的调优,自然会有专业的DBA来负责~

对于上述的一些调优参数,可以在启动之后通过set global @@xxx = xxx的方式调整,但最好还是直接修改my.ini/my.conf配置文件,因为这样可以让这些参数在每次启动时都生效,避免了每次重启时还需要手动调整。

最后,对于某些安全性要求不高的业务中,也可以适当调整MySQL数据和日志的刷盘策略,将其调整到更长的间隔时间刷盘,虽然这样会导致安全性下降,出现一定的数据丢失,但可以换来不错的性能(也包括事务的隔离级别,也可以从RR调整到RC级别,这样也能够减少一定程度上的并发冲突,从而使得数据库的整体并发量提高)。

2.5、架构优化与SQL优化

   变更项目的整体架构,这是性能优化收益最大的手段,但项目架构与SQL语句优化,这两点牵扯繁多,会在后续开新的篇章来详细讲解,这里简单介绍一些架构优化的思想,后续的篇章中会逐步将本章提到的一些方案落地。

对于架构优化主要牵扯两块,一方面是从整个项目的角度出发,引入一些中间件来优化整体性能。另一方面则是调整MySQL的部署架构,以此来确保可承载更大的流量访问,提高数据层的整体吞吐,下面逐个介绍一些常用的架构。

2.5.1、引入缓存中间件解决读压力

正常的项目业务中,往往读请求的数量远超写请求,如果将所有的读请求都落入数据库处理,这自然会对MySQL造成巨大的访问压力,严重的情况下甚至会由于流量过大,直接将数据库打到宕机,因此为了解决这系列问题,通常都会在应用程序和数据库之间架设一个缓存,例如最常用的Redis,关系图如下:
App-Redis-MySQL
在项目中引入Redis作为缓存后,在缓存Key设计合理的情况下,至少能够为MySQL分担70%以上的读压力,查询MySQL之前先查询一次RedisRedis中有缓存数据则直接返回,没有数据时再将请求交给MySQL处理,从MySQL查询到数据后,再次将数据写入Redis,后续有相同请求再来读取数据时,直接从Redis返回数据即可。

2.5.2、引入消息中间件解决写压力

前面项目中引入Redis后,能够在很大程度上减轻MySQL的读请求压力,但当业务系统中的写操作也较为频繁时又该怎么办呢?Redis在这里似乎只能分担读操作的流量呀?这时就可以引入MQ消息中间件做削峰填谷,关系图如下:
App-MQ-MySQL
上面这幅图看起来就没有前面那张图容易理解,这里结合业务来说明一下,还是拿经典的下单业务来说明情况,一个下单业务通常由「提交订单、支付费用、扣减库存、修改订单状态、添加发票记录、添加附赠服务....」这一系列操作组成,其中「提交订单、支付费用」属于核心业务,因此当用户下单时,这两类请求可以发往MySQL执行落库操作,而对于「扣减库存、修改订单状态、添加发票记录、添加附赠服务....」这类操作则可以发往MQ,当写入MQ成功,则直接返回客户端下单成功,后续再由消费线程去按需拉取后执行。

当然,对于「扣减库存」而言,其实在Redis中也会缓存商品信息,在扣减库存时仅仅只会减掉Redis中的商品库存,确保其他用户看到的库存信息都是实时的,最终的减库存操作,是去异步消费MQ中的消息后,最终才落库的。

经过MQ做了流量的削峰填谷后,这能够在极大的程度上减轻MySQL的写压力,能够将写压力控制到一个相较平缓的程度,防止由于大量写请求直接到达MySQL,避免负载过高造成的宕机现象出现。

2.5.3、MySQL主从读写分离

前面聊到的Redis也好,MQ也罢,这都属于在MySQL之前架设中间件,以此来减少真正抵达数据库的请求数量,但打铁还需自身硬,万一经过Redis、MQ后,那些必须要走MySQL执行的请求依旧超出单机MySQL的承载范围时,如若MySQL依旧以单机形式在线上运行,这绝对会导致线上频繁宕机的情况出现。

下面则来介绍一些MySQL的架构优化方案,分别是指三种:主从架构、双主架构、分库分表架构。

主从复制,这是大多数中间件都会存在的一种高可用机制,而MySQL中也存在这种架构,也就是使用两台服务器来部署两个MySQL节点,一台为主机,另一台为从机,从节点会一直不断的从主节点上同步增量数据,当主节点发生故障时,从节点可以替换原本的主节点,以此来为客户端提供正常服务,架构模型如下:
主从读写分离
在上图中就是一个典型的主从架构,但如果从节点仅仅只是作为一个备胎,这难免有些浪费资源,因此可以在主从架构的模式下,再略微做些调整,即实现读写分离,由于读操作并不会变更数据,所以对于读请求可以分发到从节点上处理,对于会引发数据变更的写请求,则分发到主节点处理,这样从而能够进一步提升MySQL的整体性能。

主节点的数据变更后,从节点也会基于bin-log日志去同步数据,但这种模式下会存在些许的数据不一致性,因为同步是需要时间的,向主节点修改一条数据后,立马去从节点中查询,这时不一定能够看到最新的数据,因为这时数据也许还未被同步过来。

哪上述这个数据不一致性问题能不能有好的办法去解决呢?其实并没有太好的办法,选择用这种方案来提升性能,必然也会出现些许问题,这也是你必须要接受的,如果项目业务对数据实时性要求特别高,哪就不要考虑主从架构。

2.5.4、MySQL双主双写热备

前面主从读写分离的方案,更适用于一些读大于写的业务,但对于一些类似于仓储这种写大于读的项目业务,这种方案带来的性能收益不见得有多好,因此从机分担的读压力,可能仅是系统的10~20%流量,因此对于这种场景下,双主双写(双主热备)方案才是最佳选择,其架构图如下:
双主双写架构
似乎看起来和之前的图差不多,但在这里的两个MySQL节点都为主,同时它们也都为从,啥意思呢?其实就是指这两个节点互为主从,两者之间相互同步数据,同时都具备处理读/写请求的能力,当出现数据库的读/写操作时,可以将请求抛给其中任意一个节点处理。

但是为了兼容两者之间的数据,对于每张表的主键要处理好,如果表的主键是int自增类型的,请一定要手动设置一下自增步长和起始值,比如这里有两个MySQL节点,那么可以将步长设置为2,起始值分别为1、2,这样做的好处是啥?能够确保主键的唯一性,设置后两个节点自增ID的序列如下:
节点1[1、3、5、7、9、11、13、15、17、19.....]
节点2[2、4、6、8、10、12、14、16、18、20.....]

当插入数据的SQL语句发往节点1时,会按照奇数序列自增ID,发往节点2时会以偶数序列自增ID,然后双方相互同步数据,最终两个MySQL节点都会具备完整的数据,因此后续的读请求,无论发往哪个节点都可以读到数据。

有人或许会思考,既然两个节点互为主从可以实现双主双写,哪能不能搞三个节点、四个节点呢?答案是当然可以,不过没必要这么做,因为当需要上三主、四主....的项目,直接就做分库分表更实在,因为这种多主模式存在一个天大的弊端!!

2.5.5、MySQL分库分表思想

刚刚在聊多主架构时,提到过多主模式有一个天大的弊端!这个弊端是指存储容量的上限+木桶效应,因为多主模式中的每个节点都会存储完整的数据,因此当数据增长达到硬件的最大容量时,就无法继续写入数据了,此时只能通过加大磁盘的形式进一步提高存储容量,但硬件也不可能无限制的加下去,而且由于多主是基于主从架构实现的,因为具备木桶效应,要加得所有节点一起加,否则另一个节点无法同步写入数据时,就会造成所有节点无法写入数据。

也正是由于上述这个原因,因此我才不建议采用三主、四主....这种架构模式,毕竟需要用到这么多MySQL节点的业务,其数据的增长速度自然不慢,因此在存储容量方面很容易抵达瓶颈,这种情况下选择分库分表才是最佳方案。

分库分表相信每位接触过数据库的小伙伴都听说过,这实则是一种分布式存储的思想,如下:
MySQL-垂直分库
上述是分库分表的一种情况,这种分库的模式被称为垂直分库,也就是根据业务属性的不同,会创建不同的数据库,然后由不同的业务连接不同的数据库,各自之间数据分开存储,节点之间数据不会同步,以这种方式来部署MySQL,即提高了数据库的整体吞吐量和并发能力,同时也不存在之前的存储容量的木桶问题。

但不要认为分库分表是一种很完美的解决方案,实际上当你对项目做了分库分表之后,带来的问题、要解决的问题只会更多,只不过相较于分库分表带来的收益而言,解决问题的成本是值得的,所以才会使用分库分表技术。

分库分表是一门大学问,这里会在后续开多个篇章去阐述,分库分表也有多种方式,上图给出的仅是一种分库分表的方案,在后续关于《MySQL分库分表》的篇章中,会全面阐述分库分表的方式、带来的问题、问题的解决方案等,在本章就不展开叙述啦~。

三、MySQL优化篇总结

   对于MySQL数据库的一些调优策略,到这里就大致讲述完毕了,其中能够带来最大收益的方案则是优化项目架构和数据库架构,但这种方案成本也是最高的,一方面需要解决新的问题,同时还需要额外的部署成本,所以在无需使用更高规格的架构处理并发时,就不必提前做这些架构设计,因为能够根据业务特性控制成本,也是作为一个优秀的高级开发/架构师必备的思想。

   同时在本章中也未曾过多讲解SQL优化,这是每位中高级开发的重心,因为毕竟作为一个正常的程序员,代码才是日常工作中接触最多的东西,但由于SQL优化想要写明白、讲清楚,其篇幅也不会短,所以会单开下章《SQL优化篇》去全面讲解!

放在最后的结语:天下没有免费的午餐,任何的优化手段都具有主观的性质存在,而且每一种优化的手段都需要付出一定的代价。同时,性能优化并没有非常标准的优化方案参考,优化是一个永无止境的方向,随着工作经验的不断积累,你的优化手段和见解就有可能不同,最终落地的优化方案也会不同。

PS:单机MySQL最好的优化手段,请手动将MySQL版本升级到5.7及以上,因为MySQL5.6之后官方对后续的版本做了很大程度上的改进,引入了很多高性能技术,所以如果你项目的MySQL版本还在其之下,条件允许的情况下一定记得先做版本升级!

本文出至:学新通技术网

标签: