澳门新浦京娱乐场网站-www.146.net-新浦京娱乐场官网
做最好的网站

关键的十个MySQL性能优化技巧,MySQL的10个基本性

 

原文:7 keys to better MySQL performance
作者:Peter Zaitsev
译者:Peter

姓名:郭金  学号:17101223407

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

随着负载和文件大小的增长,性能往往会降低。记住以下的7个关键点,让你的MySQL轻松保持平稳运行。

原文出处:

译者注: 随着尺寸和负载的增长,MySQL的性能会趋于下降。记住这些诀窍,便可保持MySQL的流畅运行。

转载自:

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

测量应用程序的方式之一是测量它的性能。用户体验是衡量应用程序性能的一个指标,这就意味着用户是否能在合理的时间内获得所需的内容。

 

图片 1

【嵌牛导读】:随着越来越多的公司转向开源数据库(特别是MySQL),以便在大规模生产环境中管理和服务其业务数据,他们将需要集中精力保持这些数据库的优化和最佳运行效率。与所有对您的业务目标至关重要的事情一样,您的数据库性能可能会导致或破坏你的业务目标或成果。MySQL是一个可以为应用程序和网站提供优质的数据库解决方案,但需要进行调整以满足您的需要,并进行监视以发现和防止瓶颈和性能问题。

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

有很多研究都表明,性能对用户的行为有很大的影响:

MySQL的10个基本性能技巧

测量应用程序的方法之一是看性能。而性能的指标之一便是用户体验,通俗的说法就是“用户是否需要等待更长的时间才能得到他们想要的东西”。

【嵌牛鼻子】:MySQL

1、分析工作负载

79%的用户表示不太可能再次打开一个缓慢的网站;

 

这个指标在不同的应用场合而有所改变。对于移动购物应用,响应时间不能超过几秒钟。对于员工的人力资源页面,可能需要多花几秒钟的时间。

【嵌牛提问】:如何知道MySQL的性能是否受到当前InnoDB日志文件大小的限制?

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

47%的用户期望网页能在2秒钟以内加载;

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

有很多关于性能如何影响用户行为的研究:

【嵌牛正文】:

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

40%的用户表示如果加载时间超过三秒钟,就会放弃这个网站;

 

  • 79%的客户不太可能回到慢速网站
  • 47%的消费者希望网页在2秒或更短的时间内完成加载
  • 40%的用户在网站加载时间超过3秒时会放弃
  • 页面加载时间的1秒延迟可能会导致7%的损失,页面浏览量减少11%

      随着尺寸和负载的增长,MySQL的性能会趋于下降。记住这些诀窍,便可保持MySQL的流畅运行。

2、理解四个基本资源

页面加载时间延迟一秒可能导致转换损失7%,页面浏览量减少11%。

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

无论采用何种标准,都必须保持良好的应用性能。否则,用户会抱怨(或者更糟的是,转到不同的应用程序)。影响应用程序性能的因素之一是数据库性能。应用程序、网站和数据库之间的交互对于建立应用程序性能的好坏至关重要。

测量应用程序的方法之一是看性能。而性能的指标之一便是用户体验,通俗的说法就是“用户是否需要等待更长的时间才能得到他们想要的东西”。

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

无论标准是什么,都要保持良好的应用性能是非常必要的。否则,用户就会抱怨(或转到另一个应用程序)。影响应用程序性能的一大因素就是数据库性能。应用程序、网站和数据库之间的交互对应用程序性能至关重要。

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

这种交互的一个核心组件是应用程序如何查询数据库以及数据库如何响应请求。无论如何,MySQL都是最受欢迎的数据库管理系统之一。在生产环境中,越来越多的企业正在转向使用MySQL(和其他开源数据库)作为数据库解决方案。

这个指标在不同的应用场合而有所改变。对于移动购物应用,响应时间不能超过几秒钟。对于员工的人力资源页面,可能需要多花几秒钟的时间。

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

这种交互的核心部分是应用程序如何查询数据库以及数据库对请求的响应。无论从哪一方面来说,MySQL都是最受欢迎的数据库管理系统之一。很多企业正在将MySQL(和其他开源数据库)作为其生产环境中的数据库解决方案。

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

有许多配置MySQL的方法可以帮助确保数据库对查询作出快速响应,并使应用程序性能降低到最低限度。

有很多关于性能如何影响用户行为的研究:

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

有很多配置MySQL的方法可以帮助确保您的数据库快速响应查询,并且减少应用程序性能下降。

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

以下是帮助优化MySQL数据库性能的一些基本技巧。

79%的客户不太可能回到慢速网站

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

以下是帮助您优化MySQL数据库性能的一些重要技巧。

 

优化技巧 #1:学习如何使用 EXPLAIN

使用任何数据库所做的两个最重要的决定是设计应用程序实体之间的关系如何映射到表(数据库模式),以及设计应用程序如何以所需的格式获得所需的数据(查询)。

复杂的应用程序可以有复杂的模式和查询。如果想得到应用程序所需要的性能和扩展性,不能仅仅依靠直觉来理解如何执行查询。

应该学习如何使用EXPLAIN命令,而不是随意的猜测和想象。此命令展示了如何执行查询,并让您了解所期望的性能,以及查询将如何随着数据大小的变化而伸缩。

有许多工具–比如MySQLWorkbench–可以可视化EXPLAIN输出,但仍然需要理解基础知识才能理解它。

EXPLAIN命令提供输出的有两种不同的格式:老式的表格式和更现代的结构化JSON文档,它提供了更多的细节(如下所示):

mysql> explain format=json select avg(k) from sbtest1 where id between 1000 and 2000 G
*************************** 1. row ***************************
EXPLAIN: {
  “query_block”: {
    “select_id”: 1,
    “cost_info”: {
      “query_cost”: “762.40”
    },
    “table”: {
      “table_name”: “sbtest1”,
      “access_type”: “range”,
      “possible_keys”: [
        “PRIMARY”
      ],
      “key”: “PRIMARY”,
      “used_key_parts”: [
        “id”
      ],
      “key_length”: “4”,
      “rows_examined_per_scan”: 1874,
      “rows_produced_per_join”: 1874,
      “filtered”: “100.00”,
      “cost_info”: {
        “read_cost”: “387.60”,
        “eval_cost”: “374.80”,
        “prefix_cost”: “762.40”,
        “data_read_per_join”: “351K”
      },
      “used_columns”: [
        “id”,
        “k”
      ],
      “attached_condition”: “(`sbtest`.`sbtest1`.`id` between 1000 and 2000)”
    }
  }
}

应该查看的一个组件是“query cost”。query cost是指MySQL根据查询执行的总开销来考虑这个特定查询的代价,并且基于许多不同的因素。

简单查询的查询开销通常小于1,000。开销在1,000到100,000之间的查询被认为是中等开销的查询,而且如果每秒只运行数百个这样的查询(而不是数万个),通常会比较快。

开销超过100,000的查询可以当作是昂贵的。通常,当您是系统上的单个用户时,这些查询仍会快速运行,但您应该仔细考虑在交互式应用程序中使用此类查询的频率(尤其是随着用户数量的增长)。

当然,这些数字只是性能的一个大概的体现,但它们展示了一般原则。您的系统可能更好地处理查询工作负载,也可能更糟,这取决于其体系结构和配置。

决定查询开销的主要因素是查询是否正确使用索引。EXPLAIN 命令可以告诉您查询是否使用索引(通常是因为索引是如何在数据库中创建的,或者查询本身是如何设计的)。这就是为什么学会使用 EXPLAIN 是如此重要。

47%的消费者希望网页在2秒或更短的时间内完成加载

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

MySQL优化关键1:了解如何使用EXPLAIN

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

优化技巧 #2:创建正确的索引

索引通过减少查询必须扫描的数据库中的数据量来提高查询效率。MySQL中的索引用于加速数据库中的访问,并帮助执行数据库约束(如 UNIQUE和FOREIGN KEY )。

数据库索引很像图书索引。它们被保存在自己的位置,并且包含主数据库中已经存在的信息。它们是指向数据所在位置的参考方法或映射。索引不会更改数据库中的任何数据。它们只是指向数据的位置。

没有完全适用于任何工作负载的索引。而应该始终在系统运行的查询上下文中查看索引。

索引良好的数据库不仅运行得更快,而且即使缺少一个索引也会使数据库慢如蜗牛。使用EXPLAIN(如前所述)查找缺少的索引并添加它们。但是要小心:不要添加你不需要的索引!不必要的索引会降低数据库的速度
(请查看关于MySQL索引最佳实践的介绍)。

40%的用户在网站加载时间超过3秒时会放弃

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

对于数据库,您做出的最重要的两个决策分别是:一、设计应用程序实体之间的关系如何映射到表(数据库模式)中,二、设计应用程序如何以所需格式(查询)获取所需的数据。

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

优化技巧 #3:拒绝使用默认设置

与任何软件一样,MySQL有许多可配置的设置,可用于修改行为(以及最终的性能)。与任何软件一样,管理员忽略了许多这些可配置的设置,最终在默认模式下使用。

要从MySQL中获得最佳性能,了解可配置的的MySQL设置是非常重要的,更重要的是将它们设置为最适合您的数据库环境。

默认情况下,MySQL用于小规模的开发安装,而不是生产规模。您通常希望配置MySQL以使用所有可用的内存资源,并允许应用程序需要的连接数量。

下面是三个MySQL性能优化设置,您应该始终仔细检查:

innodb_ buffer_ pool_size:缓冲池用于存放缓存数据和索引。这是使用具有大容量RAM的系统作为数据库服务器的主要原因。如果只运行InnoDB存储引擎,通常会将80%的内存分配给缓冲池。如果您正在运行非常复杂的查询,或者有大量的并发数据库连接,或大量的表,可能需要将此值降低一个档次,以便为其他操作分配更多的内存。

在设置InnoDB缓冲池大小时,需要确保不要设置得太大,否则会导致交换。这绝对会影响数据库性能。一种简单的检查方法是查看Percona Monitoring and Management中的系统概述图中的交换活动:

图片 2

如图所示,有时进行一些交换是可以的。但是,如果看到持续每秒1MB或更多的交换活动,则需要减少缓冲池大小(或其他内存使用)。

如果在第一次访问时没有正确地获得innodb_ Buffer_ pool_ size的值,不用担心。从MySQL5.7开始,便可以动态更改InnoDB缓冲池的大小,而无需重新启动数据库服务器。

innodb_ log_ file_ size:这是单个InnoDB日志文件的大小。默认情况下,InnoDB使用两个值,这样您就可以将这个数字加倍,从而获得InnoDB用于确保事务持久的循环重做日志空间的大小。这也优化了将更改应用到数据库。设置innodb_ log_ file_ size是一个权衡的问题。分配的重做空间越大,对于写密集型工作负载而言,性能就越好,但是如果系统断电或出现其他问题,崩溃恢复的时间就越长。

如何知道MySQL的性能是否受到当前InnoDB日志文件大小的限制?可以通过查看实际使用了多少可用的重做日志空间来判断。最简单的方法是查看Percona Monitor and Management InnoDB Metrics仪表板。在下图中,InnoDB日志文件的大小不够大,因为使用的空间非常接近可用的重做日志空间(由红线表示)。日志文件的大小应该至少比保持系统最佳运行所用的空间大20%。

图片 3

MAX_ Connections:大型应用程序连接数通常需高于默认值。不同于其它变量,如果没有正确设置它,就不会有性能问题(本身)。相反,如果连接的数量不足以满足您的应用程序的需要,那么您的应用程序将无法连接到数据库(在您的用户看来,这就像是停机时间)。所以正确处理这个变量很重要。

如果在多个服务器上运行多个组件的复杂应用程序,很难知道需要多少连接。幸运的是,MySQL可以很容易地看到在峰值操作时使用了多少连接。通常,您希望确保应用程序使用的最大连接数与可用的最大连接数之间至少有30%的差距。查看这些数字的一种简单方法是在Percona监控和管理的MySQL概述仪表板中使用MySQL连接图。下图显示了一个健全的系统,其中有大量的附加连接可用。

图片 4

需要记住的一点是,如果数据库运行缓慢,应用程序通常会创建过多的连接。在这种情况下,您应该处理数据库的性能问题,而不是简单地允许更多的连接。更多的连接会使底层的性能问题变得更糟。

(注意:当将max_Connections变量设置为明显高于默认值时,通常需要考虑增加其他参数,如表缓存的大小和打开的MySQL文件的数量。但是,这不属于本文讨论的范畴。)

页面加载时间的1秒延迟可能会导致7%的损失,页面浏览量减少11%

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

复杂的应用程序可能具有复杂的查询和模式。如果您要获得应用程序所需的性能和扩展性,不能仅仅直观的来了解查询是如何执行的。

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

优化技巧 #4:将数据库保存在内存中

近年来,我们看到了向固态磁盘(SSD)的过渡。尽管SSD比旋转硬盘快得多,但它们仍然无法与RAM中的数据相比。这种差异不仅来自存储性能本身,还来自数据库在从磁盘或SSD存储中检索数据时必须做的额外工作。

随着最新硬件的改进,无论是在云端运行还是管理自己的硬件,都越来越有可能将数据库存储在内存中。

更好的消息是,您不需要将所有数据库都放入内存中,就可以获得内存中的大部分性能优势。您只需将工作数据(最频繁访问的数据)集存入内存中。

你可能已经看到一些文章提供了一些具体的数字,说明应该将数据库的哪个部分保存在内存中,从10%到33%不等。事实上,没有“一刀切”的数字。适合内存的最佳性能优势的数据量与工作负载相关。与其寻找一个特定的“万能”数字,不如检查一下数据库在其稳定状态下运行的I/O(通常在启动后几个小时)。看看READ,因为如果数据库在内存中,则可以完全消除READ。写总是需要发生的,不管你有多少内存可用。

下面,您可以在Percona监控和管理的InnoDBMetrics仪表板中的 InnoDB I/O图中看到 I/O。

图片 5

在上面的图表中,您可以看到高达每秒2,000个I/O操作的峰值,这表明(至少对于工作负载的某些部分)数据库工作集不适合内存。

无论采用何种标准,都必须保持良好的应用性能。否则,用户会抱怨(或者更糟的是,转到不同的应用程序)。影响应用程序性能的因素之一是数据库性能。应用程序、网站和数据库之间的交互对于建立应用程序性能的好坏至关重要。

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

您应该学习如何使用EXPLAIN命令。此命令向您展示了应该如何执行查询,并让您深入了解可以预期的性能以及查询如何随着数据大小的变化而缩放。

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

优化技巧 #5:使用SSD存储

如果您的数据库不适合内存(即使不适合),您仍然需要快速存储来处理写操作,并在数据库升温时(重新启动后)避免性能问题。如今,SSD即是快速存储的代名词。

出于成本或可靠性的原因,一些“专家”仍然主张使用旋转磁盘(机械磁盘)。坦率地说,当涉及到操作数据库时,这些论点往往已经过时或完全错误。今天,SSD以较高的价格提供着可观的性能和可靠性。

然而,并非所有SSD都是适用的。对于数据库服务器,您应该使用为服务器工作负载设计的SSD,这种SSD会对数据起到保护作用(例如,在断电期间)。避免使用为台式计算机和笔记本电脑设计的商用SSD。

通过NVMe或Intel OpTan技术连接的SSD可提供最佳性能。即使作为SAN、NAS或cloud block设备远程连接,与旋转磁盘相比,SSD仍然具有更优越的性能。

这种交互的一个核心组件是应用程序如何查询数据库以及数据库如何响应请求。无论如何,MySQL都是最受欢迎的数据库管理系统之一。在生产环境中,越来越多的企业正在转向使用MySQL(和其他开源数据库)作为数据库解决方案。

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

类似于MySQL Workbench的工具,都可以为您显示EXPLAIN输出,但您仍然需要学习基础知识以理解它。

 

优化技巧 #6:横向扩展

即使是高性能的服务器也有其局限性。有两种扩展方式:up和out。纵向扩展意味着购买更多的硬件。这可能很昂贵,而且硬件很快就会过时。横向扩展以处理更多的负载有几个好处:

有许多配置MySQL的方法可以帮助确保数据库对查询作出快速响应,并使应用程序性能降低到最低限度。

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

EXPLAIN命令提供输出有两种不同格式:旧式表格格式和更现代化的结构化JSON文档,后者能提供更多的细节(如下所示):

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

      1.可以利用较小且成本较低的系统。

以下是帮助优化MySQL数据库性能的一些基本技巧。

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

对于一个组件来说应该关注的是“查询成本”。查询成本是指基于许多不同的因素上,MySQL在查询执行的总体成本考虑了该特定查询成本。

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

      2.通过横向扩展,进行线性扩展更快更容易。

优化技巧 #1:学习如何使用 EXPLAIN

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

图片 6

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

      3.因为数据库分布在多台物理机器上,所以数据库不会受到单个硬件故障点的影响。

虽然横向扩展是有好处的,但也有一定的局限性。扩展需要复制,例如基本的MySQL复制或Percona XtraDB Cluster,以实现数据同步。但是作为回报,可以获得额外的性能和高可用性。如果您需要更大的扩展,请使用MySQL分片。

您还需要确保连接到集群体系结构的应用程序能够找到所需的数据–通常通过一些代理服务器和负载平衡器(如ProxySQL或HAProxy)。

在计划横向扩展时,避免过早地扩展。使用分布式数据库往往更复杂。现代硬件和MySQL服务器只使用一台服务器就可以得到良好的体验。最近发布的MySQL 8候选版本表明,它能够在单个系统上处理200多万个简单查询。

使用任何数据库所做的两个最重要的决定是设计应用程序实体之间的关系如何映射到表(数据库模式),以及设计应用程序如何以所需的格式获得所需的数据(查询)。

6、不要过分关注配置

简单查询的查询成本通常低于1000。成本在1000到100000之间的查询被视为中等成本查询,如果您每秒只运行数百个这样的查询(而不是数万),通常认为是快速的。

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

优化技巧 #7:可观测性

设计最好的系统时要考虑到可观察性-MySQL也不例外.。

一旦您启动、运行并正确调整了MySQL环境,就不能仅仅设置而不进行管理。数据库环境会受到系统或工作负载更改的影响。准备好应对诸如流量高峰、应用程序错误和MySQL故障等意外。这些事情能够而且将会发生。

当发生问题时,你需要迅速而有效地解决它们。这样做的唯一方法是设置某种监视解决方案并对其进行适当的初始化。这使您能够在数据库环境在生产中运行时看到它正在发生的情况,并在出现问题时分析服务器数据。理想情况下,系统允许您在问题发生之前或在问题发展到用户可以看到其影响之前进行预防。

监控工具有诸如MySQL Enterprise Monitor、Monyog和Percona Monitoring and Management (PMM),后者具有免费和开源的额外优势。这些工具为监视和故障排除提供了很好的可操作性。

随着越来越多的公司转向开源数据库(特别是MySQL),以便在大规模生产环境中管理和服务其业务数据,他们将需要集中精力保持这些数据库的优化和最佳运行效率。与所有对您的业务目标至关重要的事情一样,您的数据库性能可能会导致或破坏你的业务目标或成果。MySQL是一个可以为应用程序和网站提供优质的数据库解决方案,但需要进行调整以满足您的需要,并进行监视以发现和防止瓶颈和性能问题。

PeterZaitsev是Percona的联合创始人和首席执行官,Percona时企业级MySQL和MongoDB解决方案和服务的提供商。由O‘Reilly出版的《High Performance MySQL》是最受欢迎的MySQL性能书籍之一。Zaitsev经常在PerconaDatabasePerformanceBlog.com上发表博客,并在世界各地的会议中发言。

复杂的应用程序可以有复杂的模式和查询。如果想得到应用程序所需要的性能和扩展性,不能仅仅依靠直觉来理解如何执行查询。

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

超过100000的查询认为是高成本查询。通常,当您是系统上的单个用户时,这些查询仍然运行得很快,但是必须要考虑到在交互式应用程序中使用这些查询的频率(尤其是随着用户数量的增长)。

译者注:没看懂

应该学习如何使用EXPLAIN命令,而不是随意的猜测和想象。此命令展示了如何执行查询,并让您了解所期望的性能,以及查询将如何随着数据大小的变化而伸缩。

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

虽然这都是一些大致的数字,但是它们表现出了一般原则。体系结构和配置可能会影响系统的处理查询工作负载。

 

有许多工具–比如MySQLWorkbench–可以可视化EXPLAIN输出,但仍然需要理解基础知识才能理解它。

7、注意分页查询

确定查询成本的主要因素是查询是否使用正确索引。 EXPLAIN命令可以告诉您查询是否要用索引。这就是为什么学习使用EXPLAIN 的重要原因。

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

EXPLAIN命令提供输出的有两种不同的格式:老式的表格式和更现代的结构化JSON文档,它提供了更多的细节(如下所示):

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

MySQL优化关键2:创建正确的索引

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

mysql> explain format=json select avg(k) from sbtest1 where id between 1000 and 2000 G

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

索引可以减少查询必须扫描数据量来提高查询性能。 MySQL中的索引用于加速数据库中的访问,并帮助实施数据库约束(例如UNIQUE和FOREIGN KEY)。

译者注:没看懂

*************************** 1. row ***************************

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

数据库索引很像书籍索引。它们保存在自己的位置,并且包含已经在主数据库中的信息。它们是一种数据所在的参考方法。索引不会更改数据库中的任何数据,只是指向数据的位置。

 

EXPLAIN: {

9、了解索引的三大规则

在系统运行查询中,您应该始终查看索引。

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

  “query_block”: {

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

一个缺失的索引也可能会使数据库运行速度速度降低。但要不要添加不需要的索引!不必要的索引会减慢数据库运行速度。

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

    “select_id”: 1,

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

MySQL优化关键3:不要使用默认模式!

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

    “cost_info”: {

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

像任何软件一样,MySQL有许多可配置的设置,可用于修改行为。但是管理员忽略了许多可配置的设置,始终在默认模式下运行。

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

      “query_cost”: “762.40”

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

为了获取MySQL的最佳性能,了解可配置设置是非常重要的,更重要的是将它们设置为最适合您的数据库环境。

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

    },

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

关键的十个MySQL性能优化技巧,MySQL的10个基本性能技巧。默认情况下,MySQL适合于小规模开发安装,而不是用于生产规模。您通常要配置MySQL,以使用可用的所有内存资源,并允许应用程序所需的连接数。

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

    “table”: {

10、利用同行的专业知识

这里有三个MySQL性能调优设置:

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

      “table_name”: “sbtest1”,

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

innodb_buffer_pool_size:缓冲池是缓存数据和索引的地方。这是使用具有大量RAM的系统作为数据库服务器的主要原因。如果您只运行InnoDB存储引擎,通常会为缓冲池分配大约80%的内存。如果运行非常复杂的查询、有大量的并行数据库连接或者有大量的表,那么可能需要将此值降低一个级别,为其他的运行分配更多内存。

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

      “access_type”: “range”,

当您设置InnoDB缓冲池大小时,不要将其设置得太大否则会导致互换。这绝对会破坏数据库性能。一个简单的检查方法是查看Percona监控和管理系统概述图中的交换活动:

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

      “possible_keys”: [

图片 7

 

        “PRIMARY”

如图所示,一些交换是非常频繁的。如果您看到持续的交换活动为每秒1MB或更多,那么将需要减少缓冲池大小(或其他内存使用)。

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

      ],

如果第一次没有获得innodb_buffer_pool_size的正确值,不用担心。从MySQL 5.7开始,可以动态更改InnoDB缓冲池的大小,无需重新启动数据库服务器。

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

关键的十个MySQL性能优化技巧,MySQL的10个基本性能技巧。      “key”: “PRIMARY”,

innodb_log_file_size:这是一个单独的InnoDB日志文件大小。默认情况下,InnoDB使用两个值,以便您可以将此数字加倍,以获取循环重做日志空间的大小,确保事务持久运行。这也优化了应用对数据库的更改。设置innodb_log_file_size是一个需要权衡的问题,分配的重做空间越大,写入密集型工作负载的性能越好,但如果系统遇到电源丢失或其他问题,花费的恢复时间也越长。

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

      “used_key_parts”: [

如何知道MySQL性能受当前InnoDB日志文件大小的限制呢?可以通过查看实际使用的重做日志空间来判断。最简单的方法是查看Percona Monitoring and Management InnoDB Metrics仪表板。在下图中,InnoDB日志文件大小不够大,因为使用的空间非常接近可用的重做日志空间(由红线表示)。日志文件大小应至少比用于保持系统执行最佳性能的空间大20%。

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

        “id”

图片 8

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

      ],

max_connections:大型应用程序通常需要比默认的连接数量多得多。与其他变量不同,如果不正确设置,就不会出现性能问题(本质上)。相反,如果连接数量不足以满足应用需求,那么您的应用程序将无法连接到数据库(这对用户来说看起来就像停机了)。获取这个政权变量是非常重要的。

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

      “key_length”: “4”,

在多个服务器上运行许多组件的复杂应用程序中,可能难以知道需要多少连接。但幸运的是,MySQL可以很容易地看到在峰值操作时使用了多少个连接。通常,为确保应用程序使用的最大可用连接数比最大连接数至少大30%。查看这些数字的简单方法是在Percona监控和管理的MySQL概述仪表板中使用MySQL连接图。下图显示了一个健康的系统,其中有很多额外的连接可用。

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

      “rows_examined_per_scan”: 1874,

图片 9

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

      “rows_produced_per_join”: 1874,

要记住的一点是,如果您的数据库运行缓慢,应用程序通常会创建过多的连接。在这种情况下,您应该处理数据库性能问题,而不是简单地允许更多的连接。过多的连接可能会使基础性能问题更糟。

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

      “filtered”: “100.00”,

(注意:当您将max_connections变量设置为显著高于默认值时,通常需要考虑增加其他参数,如表缓存的大小和MySQL允许的打开文件数)

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

      “cost_info”: {

MySQL优化关键4:将数据库保存在内存中

 

        “read_cost”: “387.60”,

近年来,我们看到了固态硬盘(SSD)的转型。即使SSD比旋转硬盘驱动器要快得多,但是它们仍然与RAM中的数据不兼容。这中差异不仅来自于存储性能本身,还来自数据库在从磁盘或SSD存储中检索数据时必须执行的其他工作。

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

        “eval_cost”: “374.80”,

随着硬件改进,无论您是在云端运行还是管理自己的硬件,都越来越有可能将您的数据库存储在内存中 -。java学习干货面试经验,不定期分享 Q群:282711949。更好的消息是,您不需要将所有数据库都装入内存,只需将常访问的工作数据集合放到内存中即可。检查数据库在稳定状态下运行的I / O数量(通常在启动后几个小时)。下图您可以在Percona监控和管理的InnoDB Metrics仪表板上的InnoDBI / O。

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

        “prefix_cost”: “762.40”,

图片 10

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

        “data_read_per_join”: “351K”

在上图中,您可以看到峰值高达每秒2000个I / O,这表明(至少对于工作负载的某些部分),数据库工作集与内存不匹配。

      },

MySQL优化关键5:使用SSD存储

      “used_columns”: [

如果您的数据库不适合内存,但仍然需要快速存储来处理写入,并避免数据库加速(重新启动之后)时出现性能问题。 这些快速存储意味着需要使用SSD。

        “id”,

由于成本或可靠性原因,一些“专家”仍然主张使用旋转磁盘。但在操作数据库中,这些观点往往是过时的或错误的。今天,SSD在友好的价格上提供了令人印象深刻的性能和可靠性。

        “k”

然而,不是所有的SSD都是相同的。对于数据库服务器,您应该使用专为服务器工作负载设计的SSD。

      ],

一种直接通过NVMe或Intel Optane技术直接连接的SSD可提供最佳性能。即使作为SAN,NAS或云块设备进行远程连接,与旋转磁盘相比,SSD仍然具有优异的性能。

      “attached_condition”: “(`sbtest`.`sbtest1`.`id` between 1000 and 2000)”

MySQL优化关键6:向外扩展

    }

即使是性能最好的服务器也有局限性。有两种扩展方式:up和out。up意味着购买更多的硬件,但硬件很贵且很快就会过时。out有几个好处:

  }

*可以利用更小、成本更低的系统。

}

*通过向外扩展能更快更容易的线性放缩。

应该查看的一个组件是“query cost”。query cost是指MySQL根据查询执行的总开销来考虑这个特定查询的代价,并且基于许多不同的因素。

*由于数据库分布在多台物理机上,因此数据库不会收到单椅硬件故障的影响。

简单查询的查询开销通常小于1,000。开销在1,000到100,000之间的查询被认为是中等开销的查询,而且如果每秒只运行数百个这样的查询(而不是数万个),通常会比较快。

虽然向外扩展有优势,但也有一定的局限性。味了数据同步,扩展需要复制,例如基本的MySQL或Percona XtraDB集群复制。

开销超过100,000的查询可以当作是昂贵的。通常,当您是系统上的单个用户时,这些查询仍会快速运行,但您应该仔细考虑在交互式应用程序中使用此类查询的频率(尤其是随着用户数量的增长)。

您还需要确保连接到集群架构的应用程序可以找到所需的数据,通常要通过一些代理服务器和负载平衡器来实现,如ProxySQL或HAProxy。

当然,这些数字只是性能的一个大概的体现,但它们展示了一般原则。您的系统可能更好地处理查询工作负载,也可能更糟,这取决于其体系结构和配置。

在计划扩展的同时,要避免过早的扩张,使用分布式数据库往往更复杂。

决定查询开销的主要因素是查询是否正确使用索引。EXPLAIN 命令可以告诉您查询是否使用索引(通常是因为索引是如何在数据库中创建的,或者查询本身是如何设计的)。这就是为什么学会使用 EXPLAIN 是如此重要。

MySQL优化关键7:拥有可观察性

优化技巧 #2:创建正确的索引

最好的系统在设计时要考虑到可观察性。

索引通过减少查询必须扫描的数据库中的数据量来提高查询效率。MySQL中的索引用于加速数据库中的访问,并帮助执行数据库约束(如 UNIQUE和FOREIGN KEY )。

您将MySQL环境设置好、运行并正确调整之后,也不能就将它放置不管,数据库环境可能受到系统或工作负载更改的影响。为流量达到峰值、应用程序错误和MySQL故障等情况做好准备。

数据库索引很像图书索引。它们被保存在自己的位置,并且包含主数据库中已经存在的信息。它们是指向数据所在位置的参考方法或映射。索引不会更改数据库中的任何数据。它们只是指向数据的位置。

当这些情况发生时,你需要快速有效地解决它们。实现这一点的唯一方法是设置一些监控解决方案并进行正确的检测。这可以让您看到数据库环境中正在运行的情况,并在出现问题时分析错误。理想情况下,系统能在发生事件之前进行拦截。

没有完全适用于任何工作负载的索引。而应该始终在系统运行的查询上下文中查看索引。

MySQL Enterprise Monitor,Monyog和Percona监控和管理(PMM)都是不错的监控工具,具有免费和开源的优势。这些工具为监控和故障排除提供了良好的操作可见性

索引良好的数据库不仅运行得更快,而且即使缺少一个索引也会使数据库慢如蜗牛。使用EXPLAIN(如前所述)查找缺少的索引并添加它们。但是要小心:不要添加你不需要的索引!不必要的索引会降低数据库的速度 (请查看关于MySQL索引最佳实践的介绍:

随着越来越多的公司转向开源数据库(特别是MySQL),以此来管理和服务于大规模生产环境中的业务数据,他们需要专注于保持这些数据库的调整和运行的最佳效率。数据库性能可能会导致或破坏您的业务目标,MySQL为您的应用程序和网站提供优质的数据库解决方案,但要根据您的需求进行调整,以满足您的需求并进行监控、查找、防止瓶颈和性能问题。

优化技巧 #3:拒绝使用默认设置

java学习干货面试经验,不定期分享 Q群:282711949。学习课程定期更新,知识点有:JDK高级API应用、多线程及网络编程、WEB高级技能专题、数据结构算法专题、MYSQL数据库技能、企业开发网络环境搭建、WEB应用框架专题、前端交互技术、项目实战、就业指导等。学习交流 Q群:282711949

与任何软件一样,MySQL有许多可配置的设置,可用于修改行为(以及最终的性能)。与任何软件一样,管理员忽略了许多这些可配置的设置,最终在默认模式下使用。

要从MySQL中获得最佳性能,了解可配置的的MySQL设置是非常重要的,更重要的是将它们设置为最适合您的数据库环境。

默认情况下,MySQL用于小规模的开发安装,而不是生产规模。您通常希望配置MySQL以使用所有可用的内存资源,并允许应用程序需要的连接数量。

下面是三个MySQL性能优化设置,您应该始终仔细检查:

innodb_ buffer_ pool_size:缓冲池用于存放缓存数据和索引。这是使用具有大容量RAM的系统作为数据库服务器的主要原因。如果只运行InnoDB存储引擎,通常会将80%的内存分配给缓冲池。如果您正在运行非常复杂的查询,或者有大量的并发数据库连接,或大量的表,可能需要将此值降低一个档次,以便为其他操作分配更多的内存。

在设置InnoDB缓冲池大小时,需要确保不要设置得太大,否则会导致交换。这绝对会影响数据库性能。一种简单的检查方法是查看Percona Monitoring and Management中的系统概述图中的交换活动:

图片 11

如图所示,有时进行一些交换是可以的。但是,如果看到持续每秒1MB或更多的交换活动,则需要减少缓冲池大小(或其他内存使用)。

如果在第一次访问时没有正确地获得innodb_ Buffer_ pool_ size的值,不用担心。从MySQL5.7开始,便可以动态更改InnoDB缓冲池的大小,而无需重新启动数据库服务器。

innodb_ log_ file_ size:这是单个InnoDB日志文件的大小。默认情况下,InnoDB使用两个值,这样您就可以将这个数字加倍,从而获得InnoDB用于确保事务持久的循环重做日志空间的大小。这也优化了将更改应用到数据库。设置innodb_ log_ file_ size是一个权衡的问题。分配的重做空间越大,对于写密集型工作负载而言,性能就越好,但是如果系统断电或出现其他问题,崩溃恢复的时间就越长。

如何知道MySQL的性能是否受到当前InnoDB日志文件大小的限制?可以通过查看实际使用了多少可用的重做日志空间来判断。最简单的方法是查看Percona Monitor and Management InnoDB Metrics仪表板。在下图中,InnoDB日志文件的大小不够大,因为使用的空间非常接近可用的重做日志空间(由红线表示)。日志文件的大小应该至少比保持系统最佳运行所用的空间大20%。

图片 12

MAX_ Connections:大型应用程序连接数通常需高于默认值。不同于其它变量,如果没有正确设置它,就不会有性能问题(本身)。相反,如果连接的数量不足以满足您的应用程序的需要,那么您的应用程序将无法连接到数据库(在您的用户看来,这就像是停机时间)。所以正确处理这个变量很重要。

如果在多个服务器上运行多个组件的复杂应用程序,很难知道需要多少连接。幸运的是,MySQL可以很容易地看到在峰值操作时使用了多少连接。通常,您希望确保应用程序使用的最大连接数与可用的最大连接数之间至少有30%的差距。查看这些数字的一种简单方法是在Percona监控和管理的MySQL概述仪表板中使用MySQL连接图。下图显示了一个健全的系统,其中有大量的附加连接可用。

需要记住的一点是,如果数据库运行缓慢,应用程序通常会创建过多的连接。在这种情况下,您应该处理数据库的性能问题,而不是简单地允许更多的连接。更多的连接会使底层的性能问题变得更糟。

(注意:当将max_Connections变量设置为明显高于默认值时,通常需要考虑增加其他参数,如表缓存的大小和打开的MySQL文件的数量。但是,这不属于本文讨论的范畴。)

优化技巧 #4:将数据库保存在内存中

近年来,我们看到了向固态磁盘(SSD)的过渡。尽管SSD比旋转硬盘快得多,但它们仍然无法与RAM中的数据相比。这种差异不仅来自存储性能本身,还来自数据库在从磁盘或SSD存储中检索数据时必须做的额外工作。

随着最新硬件的改进,无论是在云端运行还是管理自己的硬件,都越来越有可能将数据库存储在内存中。

更好的消息是,您不需要将所有数据库都放入内存中,就可以获得内存中的大部分性能优势。您只需将工作数据(最频繁访问的数据)集存入内存中。

你可能已经看到一些文章提供了一些具体的数字,说明应该将数据库的哪个部分保存在内存中,从10%到33%不等。事实上,没有“一刀切”的数字。适合内存的最佳性能优势的数据量与工作负载相关。与其寻找一个特定的“万能”数字,不如检查一下数据库在其稳定状态下运行的I/O(通常在启动后几个小时)。看看READ,因为如果数据库在内存中,则可以完全消除READ。写总是需要发生的,不管你有多少内存可用。

图片 13

下面,您可以在Percona监控和管理的InnoDBMetrics仪表板中的 InnoDB I/O图中看到 I/O。

图片 14

在上面的图表中,您可以看到高达每秒2,000个I/O操作的峰值,这表明(至少对于工作负载的某些部分)数据库工作集不适合内存。

优化技巧 #5:使用SSD存储

如果您的数据库不适合内存(即使不适合),您仍然需要快速存储来处理写操作,并在数据库升温时(重新启动后)避免性能问题。如今,SSD即是快速存储的代名词。

出于成本或可靠性的原因,一些“专家”仍然主张使用旋转磁盘(机械磁盘)。坦率地说,当涉及到操作数据库时,这些论点往往已经过时或完全错误。今天,SSD以较高的价格提供着可观的性能和可靠性。

然而,并非所有SSD都是适用的。对于数据库服务器,您应该使用为服务器工作负载设计的SSD,这种SSD会对数据起到保护作用(例如,在断电期间)。避免使用为台式计算机和笔记本电脑设计的商用SSD。

通过NVMe或Intel OpTan技术连接的SSD可提供最佳性能。即使作为SAN、NAS或cloud block设备远程连接,与旋转磁盘相比,SSD仍然具有更优越的性能。

优化技巧 #6:横向扩展

即使是高性能的服务器也有其局限性。有两种扩展方式:up和out。纵向扩展意味着购买更多的硬件。这可能很昂贵,而且硬件很快就会过时。横向扩展以处理更多的负载有几个好处:

可以利用较小且成本较低的系统。

通过横向扩展,进行线性扩展更快更容易。

因为数据库分布在多台物理机器上,所以数据库不会受到单个硬件故障点的影响。

虽然横向扩展是有好处的,但也有一定的局限性。扩展需要复制,例如基本的MySQL复制或Percona XtraDB Cluster,以实现数据同步。但是作为回报,可以获得额外的性能和高可用性。如果您需要更大的扩展,请使用MySQL分片。

您还需要确保连接到集群体系结构的应用程序能够找到所需的数据–通常通过一些代理服务器和负载平衡器(如ProxySQL或HAProxy)。

在计划横向扩展时,避免过早地扩展。使用分布式数据库往往更复杂。现代硬件和MySQL服务器只使用一台服务器就可以得到良好的体验。最近发布的MySQL 8候选版本表明,它能够在单个系统上处理200多万个简单查询。

优化技巧 #7:可观测性

设计最好的系统时要考虑到可观察性-MySQL也不例外.。

一旦您启动、运行并正确调整了MySQL环境,就不能仅仅设置而不进行管理。数据库环境会受到系统或工作负载更改的影响。准备好应对诸如流量高峰、应用程序错误和MySQL故障等意外。这些事情能够而且将会发生。

当发生问题时,你需要迅速而有效地解决它们。这样做的唯一方法是设置某种监视解决方案并对其进行适当的初始化。这使您能够在数据库环境在生产中运行时看到它正在发生的情况,并在出现问题时分析服务器数据。理想情况下,系统允许您在问题发生之前或在问题发展到用户可以看到其影响之前进行预防。

监控工具有诸如MySQL Enterprise Monitor、Monyog和 Percona Monitoring and Management (PMM),后者具有免费和开源的额外优势。这些工具为监视和故障排除提供了很好的可操作性。

本文由澳门新浦京娱乐场网站发布于数据库,转载请注明出处:关键的十个MySQL性能优化技巧,MySQL的10个基本性