8455澳门新

8455网站 3
8455网站用户体验好的网站一定是个好站
图片 1
进口墙纸成新宠 品牌和服务是制胜利器

关键的十个MySQL性能优化技巧

  • 五月 08, 2020
  • 首页
  • 没有评论

MySQL性能优化技巧揭秘

• 2012年09月26日16:16

MySQL性能优化技巧揭秘

1.分析工作负载通过分析工作负载,你能够发现进一步调整中最昂贵的查询。在这种情况下,时间是最重要的东西。因为当你向服务器发出查询指令时,除了如何快速完成查询外,你很少关注其他的东西。2.理解四个基本资源功能性方面,一个数据库服务器需要四个基本资源:CPU、内存、硬盘和网络。如果这四个资源中任何一个性能弱、不稳定或超负载工作,那么就可能导致整个数据库服务器的性能低下。深圳网站建设,深圳网站制作。3.不要将MySQL作为队列使用队列以及与队列相似的访问方案会在你不知情的情况下悄悄地进入应用之中。例如,你设置了一个项目状态,以便在执行前,特定的Worker Process(工作进程)能够对其进行标记,那么你就等于在无意间创建了一个队列

与所有的关系型数据库一样,Mysql仿佛是一头让人难以琢磨的怪兽。它会随时停摆,让应用限于停滞,或者让你的业务处于危险之中。

 

事实上,许多最常见的错误都隐藏在MySQL性能问题的背后。为了确保你的MySQL服务器能够一直处于全速运行的状态,提供持续稳定的性能,杜绝这些错误是非常重要的。然而,这些错误又往往隐藏在工作负载和配置问题之中。

原文出处:

幸运的是,许多MySQL性能问题都有着相似的解决方案,这使得排除故障与调整MySQL成为了一项易于管理的任务。以下就是10个让MySQL发挥最佳性能的技巧。

8455网站, 

1、分析工作负载

MySQL的10个基本性能技巧

通过分析工作负载,你能够发现进一步调整中最昂贵的查询。在这种情况下,时间是最重要的东西。因为当你向服务器发出查询指令时,除了如何快速完成查询外,你很少关注其他的东西。分析工作负载的最佳方式是,使用诸如MySQL
Enterprise Monitor的查询分析器,或者Percona
Toolkit的pt-query-digest等工具。

 

这些工具能够捕捉服务器所执行的查询,以降序的方式根据响应时间列出任务列表。它们会将最昂贵的和最耗时的任务置顶,这样你就能知道自己需要重点关注哪些地方。工作负载分析工具将相似的查询汇聚在一行中,允许管理者查看速度慢的查询,以及查看速度快但已多次执行的查询。

与所有的关系数据库一样,MySQL正如一头怪兽一般,
它可能会在接到通知的一瞬间陷入停顿,让你的应用程序陷入困境,让你的业务处于危险之中。真是的情况是,常见的错误是导致MySQL性能问题的根源。
工作负载或配置陷阱中的一些微妙之处常常会掩盖这些信息,为了确保MySQL服务器以最快的速度运行,提供稳定一致的性能,消除这些错误是很重要的。
幸运的是,很多MySQL的性能问题都有相似的解决方案,使的故障排除和调优MySQL成为一项易于管理的任务。

2、理解四个基本资源

 

功能性方面,一个数据库服务器需要四个基本资源:CPU、内存、硬盘和网络。如果这四个资源中任何一个性能弱、不稳定或超负载工作,那么就可能导致整个数据库服务器的性能低下。理解基本资源在两个特定的领域中至关重要:选择硬件和排除故障。

MySQL性能提示1:配置您的工作负载

在为MySQL选择硬件时,应该确保全部选用性能优异的组件。这些组件相互匹配,彼此间能够实现合理平衡也很重要。通常情况下,企业会为服务器选择速度快的CPU和硬盘,但是内存却严重不足。在一些案例中,大幅提升性能的最廉价方式是增加内存,尤其是对于那些受制于磁盘读取速度的工作负载。这似乎看起来有点违背常理,但是在许多案例中,由于没有充足的内存以保存服务器正在使用的数据,因此导致了硬盘被过度使用。

了解服务器究竟把时间花在哪些地方的最佳方法是分析服务器的工作负载,
通过分析工作负载,您可以导出最大代价的查询以进行进一步调优,当向服务器发出请求的时候,时间就是最重要的指标,
你几乎不关心任何事情,只关心它完成得有多快。配置工作负载的最佳方法是使用MySQL
Enterprise
Monitor的查询分析器或Percona工具包中的pt-query-digest之类的工具。

关于获取这种平衡的另一个例子是CPU。在许多案例中,如果CPU速度快,那么MySQL的性能就非常出色,因为每一个查询都是单线程运行,而无法在CPU间并行运行。在进行故障排除时,应该检查这四个资源的性能和使用情况,关注它们是否性能低下或是超负荷工作。这方面的知识能够帮助你快速地解决问题。

这些工具捕获服务器执行的查询,并返回一个任务表,按照响应时间的顺序进行排序,立即将代价最大和最耗时的任务排在最前面,这样您就可以看到您的工作重点在哪里。
工作负载分析工具将类似的查询组合在一起,允许您查看缓慢的查询,以及快速但多次执行的查询。

3、不要将MySQL作为队列使用

译者注:找到一些top的sql或者说是执行频率高的sql,这部分是关注的重点

队列以及与队列相似的访问方案会在你不知情的情况下悄悄地进入应用之中。例如,你设置了一个项目状态,以便在执行前,特定的Worker
Process能够对其进行标记,那么你就等于在无意间创建了一个队列。例如,将电子邮件标记为未发送,然后发送它们,最后再将它们标记为已发送。

 

队列会导致出现一些问题,这里面有两大主要原因:它们对工作负载进行了序列化,阻碍任务被并行处理。这导致正在处理中的任务和以前在工作中处理过的历史数据会被根据序列排列在一个表单中。这样一来既增加了应用的延时,也增加了MySQL的负载。

MySQL性能提示2:了解四种基本资源

4、以最廉价的方式过滤结果

为了完成数据库服务的功能,数据库服务器需要四种基本的资源:CPU,内存,磁盘以及网络,
如果其中任意一项是弱项(瓶颈),不稳定或者超负荷,那么,数据库服务器的性能很可能很差。
了解基本资源在两个特定领域非常重要:选择硬件和故障排除问题。
在为MySQL选择硬件时,确保所有组件都具有良好的性能。同样重要的是,要很好地平衡它们。
通常,购买组织会选择具有快速cpu和磁盘的服务器,但这些服务器内存不足。在某些情况下,增加内存是提高性能的一种廉价方法,尤其是在磁盘绑定的工作负载上。
这看起来似乎违反直觉,但在许多情况下,磁盘被过度使用,因为没有足够的内存来容纳服务器的工作数据集。

优化MySQL的最佳方式是首先要做廉价和不精确的工作,然后再小规模地做困难的精确工作,最后再生成数据集。

这种平衡的另一个很好的与cpu有关的例子。
在大多数情况下,MySQL在使用快速cpu时表现良好,因为每个查询在单个线程中运行,不能在cpu之间并行化。
在进行故障排除时,请检查所有4种资源的性能和利用率,并仔细检查它们的性能是否很差,或者是否出现某些硬件超负载运行。这些知识可以帮助快速解决问题。

例如,假设你计算某一个地理坐标点给定半径内的面积。在许多程序员的工具箱里第一个工具就是球面半正矢公式,以计算出球面的长度。这一方法的问题是,该方程式需要许多三角函数运算,需要拥有很强运算能力的CPU。球面半正矢计算不仅运行速度慢,而且会导致机器CPU的使用率飙升。在使用球面半正矢公式前,你可以先分解计算。有些分解计算并不需要使用三角函数。

译者注:CPU,内存,磁盘以及网络需要匹配,任何一个短板,都可能造成性能上的问题

5、弄清两个扩展性死亡陷阱

 

扩展性可能并不像你认为的那样模糊。实际上,扩展性有着精确的数学定义,它们以方程式的形式被表示出来。这些方程式既指出了系统无法扩展的原因,同时也指出了它们应该进行扩展的原因。通用扩展定律(Universal
Scalability
Law)揭示和量化了系统的扩展性特征。其通过两个基础性成本解释了扩展问题:即序列化与串扰(Crosstalk)。

MySQL性能提示3:不要把MySQL当做队列使用

并行处理要求必须中止序列化,这就限制了它们的扩展性。同样的,如果并行处理需要始终进行彼此对话以协调工作,那么它就相互进行了限制。为了避免序列化与串扰,应用进行了更好的扩展。这些在MySQL内部被翻译成了什么?结果不尽相同。不过,一些案例应该避免锁定在特定的行之中。就像第3个技巧中所提到的,队列扩展性差的原因就是如此。

队列和类似队列的访问模式可以在您不知情的情况下潜入应用程序。
例如,如果您设置了一个项目的状态,以便某个特定的工作进程可以在对其进行操作之前声明它,那么您无意中创建了一个队列。
将电子邮件标记为未发送,发送,然后标记为发送是一个常见的例子。
队列导致问题的主要原因有两个:它们序列化您的工作负载,防止任务被并行执行,并且它们常常导致一个表,其中包含正在处理的工作以及来自很久以前处理的任务的历史数据。
既增加了应用程序的延迟,又将其加载到MySQL。

6、不要过分关注配置

译者注:MySQL不是做队列使用的,不要使用高频率的定时任务像用队列一样去刷数据库。

数据库管理员会花费许多时间调整配置。调整的结果通常不会有很大的改善,相反有时候会带来损害。我发现许多经过“优化的”服务器,在进行强度稍微高一点的运算时常常出现崩溃、内存不足和性能低下等问题。

MySQL性能提示4:先过滤最代价最小的结果
优化MySQL的一个好方法是先做一些廉价的、不精确的工作,然后再对较小的数据集进行艰苦的、精确的工作。
例如,假设您在一个给定的地理点半径范围内寻找某物。
许多程序员工具箱中的第一个工具是计算球体表面距离的大圆公式。
这种技术的问题是,这个公式需要大量的三角运算,这是非常cpu密集型的运算。大圆计算往往运行缓慢,使计算机的CPU利用率飙升。
在应用大圆公式之前,将您的记录减少到总数的一小部分,并将结果集修剪到一个精确的圆。
一个包含圆(精确或不精确)的正方形是一个简单的方法。这样一来,方块外的世界就不会受到那些昂贵三角函数的冲击。

虽然MySQL在交付时的默认设置严重过时,但是你并不需要对每一项都进行配置。最好是根据需要,进行基本纠正与设置调整。有10个选项调整正确,即可让服务器发挥95%的最大性能。在许多案例中,我们并不推荐所谓的调整工具,因为它们只是提供一个大概设置,对特定案例没有任何意义。有些工具甚至包含有危险的和错误的设备代码。

译者注:没看懂

7、注意分页查询

 

分页查询应用会使服务器性能大降。这些应用会在网页上显示搜索结果,然后通过链接跳转至相应网页上。通常这些应用无法使用索引进行聚合与分类,而是使用LIMIT和OFFSET语句,这导致服务器工作负载大幅增加,并放弃行。
在用户界面上常常会发现优化选项。替代在结果中显示网页数量,以及分别与每个网页相连的链接。这样便可以仅显示至下一页的链接。你还可以阻止查询者浏览与首页过远的网页。

MySQL性能提示5:了解两个可伸缩性死亡陷阱

8、保存统计数据,提高报警阀值

可伸缩性并不像您所认为的那样模糊。事实上,对于可伸缩性有精确的数学定义,可以用方程表示。这些方程强调了为什么系统不能像应有的那样伸缩。
以通用可伸缩性定律为例,该定义在表示和量化系统的可伸缩性特征方面非常方便。它从两个基本成本的角度解释了扩展问题:序列化和串扰。
为了实现序列化而必须停止的并行进程在可伸缩性方面天生有限。同样地,如果并行进程需要一直彼此聊天来协调它们的工作,那么它们就限制了彼此。
避免序列化和串扰,您的应用程序将更好地扩展。这在MySQL中意味着什么?
它会有所不同,但是有些例子会避免排它锁。由于这个原因,上面第三点的队列往往难以扩展。

监控与报警必不可少,但是监控系统被怎么处理了呢?当它们发布假的报警信息时,系统管理员会设置电子邮件过滤规则,以停止这些噪音。很快你的监控系统就彻底没用了。个人认为,应该以下面的两种方式进行监控:捕捉指标与报警。尽可能地捕捉与保存指标非常重要,因为在你试图搞明白系统中需要做哪些调整时,你会庆幸之前保存了它们。如果某一天出现奇怪问题时,你会很高兴自己有能力绘制出服务器工作负载变化的图形。

译者注:没看懂

9、了解索引的三大规则

 

索引可能是数据库中被误解最多的一项。因为它们的工作方式有许多种,这导致人们常常对索引如何工作,以及服务器如何使用它们感到困惑。要想彻底搞清楚它们需要花上很大一番功夫。在被正确设计时,索引在数据库中主要用于实现以下三个重要目的:

MySQL性能提示6:不要过渡关注配置

1)它们让服务器寻找相邻行群组,而不是单个行。许多人认为,索引的目的是寻找单个行,但是寻找单个行会导致随时磁盘操作,速度很慢。寻找行群组就要好许多,与一次寻找一个行相比,这更具吸引力。

dba倾向于花费大量时间来调整配置。结果通常不是很大的改善,有时甚至是适得其反的。
我看到过很多“优化”过(调整过某些配置参数)的服务器,在负载较重的时候,经常崩溃宕机、内存不足、性能表现的很差。
MySQL自带的默认设置是一刀切的,而且已经过时了,但是您不需要配置所有内容,并不意味着任何配置选项都要人为修改。
只有在需要的时候,最好是在了解其背景的情况下再去更改配置项。
在大多数情况下,通过正确设置10个(左右,常用)选项,您可以获得95%的服务器峰值性能。只有极少数情况下需要修改一些特殊的配置项。

2)它们让服务器避免以期望的读行顺序对检索结果排序,排序成本十分高昂。以期望的顺序读行速度将更快。

在大多数情况下,不建议使用服务器“调优”工具,因为它们往往提供的指导方针对特定情况没有意义。
有些甚至有危险的、不准确的建议,比如缓存命中率和内存消耗公式。这些永远都不对,而且随着时间的流逝,它们变得越来越不正确。

3)它们能够满足来自一个索引的所有查询,从根本上避免了访问表单的需求。这被称为覆盖索引或索引查询。

译者注:大多数情况下主需要关注几个基本配置就可以了,不需要关注所有的配置信息,随意修改配置,有可能会导致适得其反,
有人会说修改bufferpool配置之后性能怎么怎么样,因为由一些本来就很low的错误引起的问题,并不是需要过渡关注配置的理由。

如果你能设计出符合这三个规则的索引与查询,那么你的查询速度将大幅提升。

MySQL性能提示7:注意分页查询

10、利用同行的专业知识

涉及分页的应用程序往往会使服务器陷入瘫痪。
应用程序中在向您显示一个结果页面时,有一个链接指向下一个页面,
这些应用程序通常以无法使用索引的方式进行分组和排序,导致服务器消耗大量的资源,然后根据页面和显示行数的要求,然后显示这其中一部分数据。

不要孤军奋战。如果你在苦苦思考某个问题,并着手制订明智的解决方案,那么这非常不错。在20次中,有19次问题会被顺利解决。但是其中会有一次让你不知所措,导致耗费大量的资金和时间,准确地说,是因为你正在尝试的解决方案只是貌似合理。

优化常常可以在用户界面中找到。您可以只显示到下一个页面的链接,而不是显示结果和每个页面的链接的确切数目。
您还可以防止人们转到离首页太远的页面。

在查询端,您可以选择比需要的多一行,而不是使用LIMIT和offset(进行精确地显示具体的行),
当用户单击“下一页”链接时,您可以指定最后一行作为下一组结果的起点。
例如,如果用户查看了第101行到第120行的页面,那么还可以选择第121行;
要呈现下一页,您需要查询服务器上大于或等于121的行,限制21。

译者注:这里作者应该是想表达,分页的时候,如果没有合适的索引,每一次翻页,都会会造成大量的查询和排序,分页查询需要合理的索引以及一些设计上的技巧。

 

MySQL性能提示8:保存性能基线信息,必要时才发出告警

监视和警报是必不可少的,但是典型的监视系统会发生什么变化呢?
它开始发送误报,系统管理员设置了电子邮件过滤规则来阻止噪音。很快你的监控系统就完全没用了。
(译者注:不管是什么问题,随随便便就发出告警,慢慢就麻木了,其结果是会慢慢地忽略所有的告警信息)

我喜欢以两种方式考虑监视:捕获(性能)指标和警报。
捕获并保存所有可能的度量是非常重要的,因为当您试图确定系统中发生了什么变化时,您会很高兴地拥有它们。
有一天,如果出现一个奇怪的问题,您会通过一个图表并显示服务器工作负载变化的情况。

相比之下,人们往往过于警觉。
人们经常对诸如缓冲区命中率或每秒创建的临时表的数量之类的事情保持警惕。
问题是,对于这样一个比率,没有一个好的阈值。正确的阈值不仅在不同的服务器之间不同,而且随着工作负载的变化也会不同。
(译者注:很多指标并没有一个标准值,比如创建临时表的频率,跟服务器的软硬件环境以及工作类型都有关系
如果收集了历史的性能指标数据,遇到一些异常问题的时候,可以根据历史性能基线与当前情况对比,提供分析问题的依据)
因此,只有在表明一个明确的、可操作的问题的情况下,才要谨慎地发出警报(不是所有问题都需要告警的,只有严重的问题才需要)。
低缓冲区命中率是不可随意告警的,也不表示真正的问题,但是不响应连接请求的服务器是需要解决的实际问题。

译者注:正确合理的配置告警以及收集性能基线

 MySQL性能提示9:学习三种索引规则

索引可能是数据库中最容易被误解的话题,因为索引的工作方式有很多种,以及服务器如何使用它们。
要真正理解索引的工作原理,需要付出很多努力。
如果设计得当,索引在数据库服务器中有三个重要用途:

索引允许服务器查找相邻行的组,而不是单个行。
很多人认为索引的目的是找到单独的行,但是,查找单个行会导致随机磁盘操作,这很缓慢。
找到一组行比一次找到一行要好得多,所有行或大部分行都很有趣。
索引可以让服务器避免按需要的顺序读取行来进行排序。排序是昂贵的。按需要的顺序读取行要快得多。
索引使服务器能够单独满足来自索引的所有查询,从而完全避免访问表。这被称为覆盖索引或索引查询。

如果您可以设计索引和查询来利用这三个机会,那么您可以使您的查询快几个数量级。

译者注:索引,一个很大的话题,很多时候需要具体情况具体分析,没有定论,但是绝对不是网上那些low到爆的什么索引使用1,2,3,4,5……几条规则。

 

MySQL性能提示10:利用同行的专业知识

不要试图独自去做。如果你在苦苦思索一个问题,并且做着对你来说合乎逻辑和明智的事情,那就太好了。20次中有19次是这样的。
另一次,你会陷入一个非常昂贵和耗时的兔子洞,因为你正在尝试的解决方案似乎很有意义。
构建一个与mysql相关的资源网络——这超出了工具集和故障排除指南的范围。有一些知识渊博的人潜伏在邮件列表、论坛、问答网站等等。
会议、商展和本地用户组活动提供了宝贵的机会,可以获得见解,并与在紧急情况下可以帮助你的同行建立关系
对于那些正在寻找补充这些技巧的工具的人,您可以查看MySQL的Percona
Configuration向导、MySQL的Percona Query
Advisor以及Percona监视插件。(注意:您需要创建一个Percona帐户来访问前两个链接。它是免费的。)配置向导可以帮助您为新服务器生成一个基线my.cnf文件,该文件优于随服务器一起发布的示例文件。Percona监视插件是一组监视和绘图插件,可以帮助您急切地保存统计数据,并不情愿地发出警告(第8).所有这些工具都是免费的。

译者注:学无止境,保持谦虚,永远要向强人学习,不懂的不要瞎逼逼。

相关文章

No Comments, Be The First!
近期评论
    功能
    网站地图xml地图