提高MySQL性能的7个技巧

图片 5

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

优化技巧 #6:横向扩展

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

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

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

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

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

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

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

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

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

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

译者注:没看懂

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

 

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

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

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

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

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

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

图片 1

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

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

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

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

优化技巧 #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上发表博客,并在世界各地的会议中发言。

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

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

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

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

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

优化技巧 #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文件的数量。但是,这不属于本文讨论的范畴。)

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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

原文:7 keys to better MySQL
performance

作者:Peter Zaitsev
译者:Peter

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

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

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

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

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

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

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

图片 5

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

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

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

 

译者注:没看懂