当前位置: 澳门新濠3559 > 数据库 > 正文

由此想到了几个优化MySQL的简单方法,MySQL 的性能

时间:2019-11-09 00:17来源:数据库
公司升级了服务器的配置,内存吃紧的问题得以解决,由此想到了几个优化MySQL的简单方法。首先明确两件事,我们的业务场景是常见的高并发web服务,查询速度是重中之重。还有就是

公司升级了服务器的配置,内存吃紧的问题得以解决,由此想到了几个优化MySQL的简单方法。首先明确两件事,我们的业务场景是常见的高并发web服务,查询速度是重中之重。还有就是优化前一定要备份数据库,不然出问题就只能跑路了。

我并不期望成为一个专家级的 DBA,但是,在我优化 MySQL 时,我推崇 80/20 原则,明确说就是通过简单的调整一些配置,你可以压榨出高达 80% 的性能提升。尤其是在服务器资源越来越便宜的当下。

姓名:郑红                                  学号:17101223385

1.使用固态硬盘

警告

本文转自

这个优化方法毫无技术含量,完全就是花钱买性能,但是不得不说这个方法相当的简单、粗暴、有效。借助固态硬盘高频的读写速度可以大幅提升MySQL的各项性能

1.没有两个数据库或者应用程序是完全相同的。这里假设我们要调整的数据库是为一个“典型”的 Web 网站服务的,优先考虑的是快速查询、良好的用户体验以及处理大量的流量。

【嵌牛导读】:在不同的情况和场景下,该指标会有所不同。比如说:对于移动购物应用来说,其响应时间不能超过几秒钟;而对于一个员工的人力资源页面而言,其响应时间则允许比几秒钟更长。

2.添加索引

2.在你对服务器进行优化之前,请做好数据库备份!

【嵌牛鼻子】:数据库    MySQL

当数据量达到一定级别时,添加合适的索引是必须的。MySQL是B+树或者其变种树,将数据按照索引的数据结构排序以优化查询效果。如果表中的数据要频繁的插入、删除时,这些操作会损害索引,导致索引占用了很大的无效空间。我就见过一张数据表的所以比数据量大很多,查询速度超慢甚至不及不添加索引,原因就是频繁的插入删除导致的,这种场景需要定期删除索引然后重新生成索引

1、 使用 InnoDB 存储引擎

【嵌牛提问】:MySQL 的性能为什么需要优化?

3.去掉外键

如果你还在使用 MyISAM 存储引擎,那么是时候转换到 InnoDB 了。有很多的理由都表明 InnoDB 比 MyISAM 更有优势,如果你关注性能,那么,我们来看一下它们是如何利用物理内存的:

【嵌牛正文】:随着容量和负载的增加,MySQL 的性能会日趋缓慢。这里有七点建议能够保证 MySQL 的平稳运行。

大学期间,老师强调了使用外键来约束数据一致性是很有必要的。上学时我也严格奉行了这一观点,毕竟这可以让我少写代码,把一致性问题抛给数据库。工作后结合真正的业务场景,数据库常常成为性能瓶颈,而服务并不会成为性能瓶颈,所以将数据一致性问题的解决方案写入服务中以此减少数据库压力很有必要。

  • MyISAM:仅在内存中保存索引。
  • InnoDB:在内存中保存索引和数据。

澳门新濠3559 1

4.使用InnoDB

结论:保存在内存的内容访问速度要比磁盘上的更快。

性能是我们衡量应用的一种方式,而应用性能的一项指标就是用户体验,也就是平时我们常说的:“用户需要等待超过合理的时间,才能获得他们想要的东西吗?”

很多事实都表明InnoDB比MyISAM更具优势。InnoDB对内存的使用更全面,内存的访问速度显然比磁盘要快

下面是如何在你的表上去转换存储引擎的命令:

在不同的情况和场景下,该指标会有所不同。比如说:对于移动购物应用来说,其响应时间不能超过几秒钟;而对于一个员工的人力资源页面而言,其响应时间则允许比几秒钟更长。

5.设置InnoDB内存

ALTER TABLE table_name ENGINE=InnoDB; 

因此,不管是什么样的标准,维持应用程序的良好性能都是至关重要的,否则就会引发用户的抱怨(或更糟的是用户转而使用其他的应用)。而数据库性能就是影响应用程序性能的因素之一。

innodb_buffer_pool_size参数代表分配给InnoDB的内存大小,分配内存时要给操作系统留下足够的内存。根据同事的经验之谈是可以为innodb_buffer_pool_size分配服务器80%的内存,当然了,这么做的前提条件是你的服务上基本只有MySQL,如果服务器上存在数据解析等其他很费内存的服务时,innodb_buffer_pool_size数值要酌情减少

注意:你已经创建了所有合适的索引,对吗?为了更好的性能,创建索引永远是第一优先考虑的事情。

可以说,应用程序、网站和数据库之间的交互会直接影响到应用服务水平的确立。

6.设置InnoDB多任务

2、 让 InnoDB 使用所有的内存

这种交互的一个核心组成部分是:各种应用程序如何去查询数据库,以及数据库是如何响应各种请求的。

如果分配给innodb_buffer_pool_size的内存大于2G时,我们可以考虑将InnoDB的缓冲池划分为多个,我们可以修改配置中的innodb_buffer_pool_instances参数。对于高并发服务,性能瓶颈往往是多线程访问MySQL,划分更多的缓冲池可以有效的缓解这个问题。当然缓冲池也不是越多越好,每个缓冲池的内存过低的话就不能发挥出多个缓冲池的优势了。官方的建议是每个缓冲池需要至少1G的内存。

你可以在 my.cnf 文件中编辑你的 MySQL 配置。使用 innodb_buffer_pool_size 参数去配置在你的服务器上允许 InnoDB 使用物理内存数量。

不论是哪一种标准,MySQL 都是时下最流行的数据库管理系统之一。越来越多的企业已将 MySQL(和其他开源的数据库)视为其生产环境中的数据库解决方案。

 

对此(假设你的服务器仅仅运行 MySQL),公认的“经验法则”是设置为你的服务器物理内存的 80%。在保证操作系统不使用交换分区而正常运行所需要的足够内存之后 ,尽可能多地为 MySQL 分配物理内存。

MySQL 有许多配置方法可以确保您的数据库能够快速地响应各种查询,同时仅对应用程序性能造成细微的下降。

最后提醒大家,对MySQL的配置修改需要重启MySQL才能生效

因此,如果你的服务器物理内存是 32 GB,可以将那个参数设置为多达 25 GB。

以下就是能够帮助您优化 MySQL 数据库性能的 7 点必备技巧:

 

innodb_buffer_pool_size = 25600M 

学习如何使用EXPLAIN

*注意:(1)如果你的服务器内存较小并且小于 1 GB。为了适用本文的方法,你应该去升级你的服务器。 (2) 如果你的服务器内存特别大,比如,它有 200 GB,那么,根据一般常识,你也没有必要为操作系统保留多达 40 GB 的内存。 *

创建正确的索引

3、 让 InnoDB 多任务运行

拒绝默认设置

如果服务器上的参数 innodb_buffer_pool_size的配置是大于 1 GB,将根据参数 innodb_buffer_pool_instances 的设置, 将 InnoDB 的缓冲池划分为多个。

将数据库载入内存中

拥有多于一个的缓冲池的好处有:

由此想到了几个优化MySQL的简单方法,MySQL 的性能为什么需要优化澳门新濠3559。使用SSD存储

在多线程同时访问缓冲池时可能会遇到瓶颈。你可以通过启用多缓冲池来最小化这种争用情况:

横向扩展

对于缓冲池数量的官方建议是:

追求可视性

为了实现最佳的效果,要综合考虑 innodb_buffer_pool_instances 和 innodb_buffer_pool_size 的设置,以确保每个实例至少有不小于 1 GB 的缓冲池。

学习如何使用 EXPLAIN

因此,在我们的示例中,将参数 innodb_buffer_pool_size 设置为 25 GB 的拥有 32 GB 物理内存的服务器上。一个合适的设置为 25600M / 24 = 1.06 GB

在您对数据库做任何设计决策时,有两个方面非常重要:

innodb_buffer_pool_instances = 24

应用实体之间如何被映射到各个数据表(数据库模式架构)上。

注意!

应用程序如何获取(查询)到它们所需格式类型的数据。

在修改了 my.cnf 文件后需要重启 MySQL 才能生效:

复杂的应用程序必然有着复杂的模式架构和查询。如果您想让自己的各种应用具备所需的性能和扩展性,那就不能单纯依靠直觉去理解各种查询的执行机制。

sudo service mysql restart

建议您认真学习如何去使用 EXPLAIN 命令,而不是凭空猜想。该命令会向您展示查询是如何被执行的;并深入地演示有关性能的真实表现情况,以及查询是如何伴随着数据量的变化进行扩展的。

还有更多更科学的方法来优化这些参数,但是这几点可以作为一个通用准则来应用,将使你的 MySQL 服务器性能更好。

像许多 MySQL Workbench 之类的工具都可以将 EXPLAIN 的输出可视化地展示给您,不过您仍然需要了解与它相关的基本知识。

总结

EXPLAIN 命令的输出有两种不同的格式:老式的表格形式和较新的、能够提供更为细节化的、结构化的 JSON 文档。

以上所述是小编给大家介绍的优化 MySQL 3 个简单的小调整,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持!

如下所示:

您可能感兴趣的文章:

  • MySQL 4G内存服务器配置优化
  • MYSQL开发性能研究之批量插入数据的优化方法
  • Mysql中基本语句优化的十个原则小结
  • mysql处理海量数据时的一些优化查询速度方法
  • Mysql占用过高CPU时的优化手段(必看)
  • MySQL优化之缓存优化
  • MySQL优化之InnoDB优化
  • mysql如何优化插入记录速度
  • 简单谈谈MySQL优化利器-慢查询

mysql> explain format=jsonselectavg(k)fromsbtest1whereidbetween1000and2000 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`between1000and2000)”

}

}

}

其中您需要重点查看的部分是:查询成本。查询成本是指基于查询执行的总体成本和许多不同的因素考虑,MySQL 判定一次查询所付出的花销。

一般简单查询的成本会小于 1000。介于 1000 到 100,000 的成本值被视为中等成本的查询。

因此,如果您每秒只是运行上百个(并非几万个)此类查询的话,一般速度应该比较快。

查询成本如果是超过 100,000 的话,那么开销就比较大了。而通常当您的系统只有单个用户时,此类查询仍然可以被迅速地执行。

当然,您需要仔细考虑一下在交互式应用程序中,使用此类查询的频率(尤其在用户数量增长的时候)。

虽然这些只是大概的数字,但是它们却能够反映出总体的规律。实际情况下,您的系统在处理查询请求负载时会表现得更好还是更糟,完全取决于自身的架构与配置。

决定查询成本的一个首要因素是:查询是否正确地使用了各种索引。如果您没有使用索引进行查询,那么会被 EXPLAIN 命令所指出来,通常源于索引是如何在数据库中被创建的,以及查询本身是如何被设计的。

这也正是为什么 EXPLAIN 值得去好好学习和使用的原因。

创建正确的索引

索引是通过减少在数据库里查询时,必须扫描的数据量来提高查询的自身效率。

在 MySQL 中,索引被用于加快对数据库的访问,并有助于遵循数据库的各种约束(例如 UNIQUE 和 FOREIGN KEY)。

数据库索引就像书的索引一样,它们的位置信息被保存,并且包含有数据库的主要信息。

它们是数据位置的一种参考方法或映射,因此索引并不会更改数据库中的任何数据。它们只是指向数据存放的位置而已。

不过,索引并不总能匹配上任何的负载请求。在系统运行中,您应当不断为查询的上下文环境创建各种索引。

虽然有着良好索引的数据库会运行更快速,但是如果出现单个索引的缺失,则会拖慢整个数据库的效率。

因此,我们需要使用 EXPLAIN 来查找缺失的索引,并将其添加上去。

需要注意的是:不要添加您所不需要的索引,因为不必要的索引会反过来拖慢数据库。

拒绝默认设置

就像其他任何软件那样,MySQL 也能通过各种可配置的设置,来修改其行为并最终优化其性能。

同时这些配置的设置经常会被管理员所忽略,并一直保持着默认值的状态。

为了让 MySQL 获得最佳的性能,了解如何配置 MySQL,以及将它们设置为最适合您的数据库环境的状态是非常重要的。

在默认情况下,MySQL 是针对小规模的发布、安装进行调优的,而并非真正的生产环境规模。

因此,通常您需要将 MySQL 配置为使用所有可用的内存资源,并且能允许您的应用程序所需的最大连接数。

这里有三个有关 MySQL 性能优化的设置,值得您去仔细地配置:

innodb_buffer_pool_size

数据和索引被用作缓存的缓冲池。当您的数据库服务器有着大量的系统内存时,可以用到该设置。

如果您只运行 InnoDB 存储引擎,那么您通常可以分配 80% 左右的内存给该缓冲池。

而如果您要运行非常复杂的查询或者您有大量的并发数据库连接,亦或您有非常大的数据表的情况,那么就可能需要将此值下调一个等级,以便为其他的调用分配更多的内存。

您在设置 InnoDB 缓冲池大小的时候,要确保其设置既不要过大,也不要频繁引起交换(swapping),因为这些绝对会降低您的数据库性能。有一个简单的检查方法就是在“Percona 监控和管理”。

澳门新濠3559 2

如图所示,如果你看到有大于 1MB 每秒的持续交换活动的话,您就需要减少缓冲池的大小了,或者使用其他的内存。

如果您一开始并没有将innodb_buffer_pool_size的值设置正确,也不必担心。

从 MySQL 5.7 开始,您可以动态地改变 InnoDB 缓冲池的大小,而不需要重新启动数据库服务器了。

innodb_log_file_size

这是指单个 InnoDB 日志文件的大小。默认情况下,InnoDB 使用两个值,这样您就可以通过将其增加一倍,来让 InnoDB 获得循环的重做日志空间,以确保交易的持久性。这同时也优化了对数据库的写入性能。

设置innodb_log_file_size的值是很值得推敲的:如果分配了较大的重做空间,那么对于写入密集型的工作负载来说性能会越好。

但是如果您的系统遭受到断电或其他问题导致崩溃的时候,那么其恢复时间则会越长。

您可能会问:怎么才能知道自己的 MySQL 性能是否受限于当前的 InnoDB 日志文件大小呢?

您可以通过查看未实际使用的重做日志空间大小来判定。最简单的方法就是查看“Percona 监控和管理”的 InnoDB 指标仪表板。

在下图中,InnoDB 的日志文件不够大,使用空间已经屡屡接近于可用的重做日志空间了,如红线所示:

澳门新濠3559 3

因此,您的日志文件应该至少比使用量大 20%,从而保持系统处于最佳的性能状态。

max_connections

大型应用程序通常需要比默认数量多得多的连接。不同于其他的变量,如果您没能将该值设置正确,您就会碰到性能方面的问题。

也就是说,如果连接的数量不足以满足您的应用需求,那么应用程序将根本无法连接到数据库,在用户看来就像宕机了一样。由此可见,将它设置正确是非常重要的。

对于在多台服务器上运行着具有多个组件的复杂应用来说,您想获知到底需要多少个连接是非常困难的。

幸运的是,MySQL 能够在峰值操作时轻易地获悉所用到的连接数量。通常,您需要确保在应用程序所使用到的最大连接数和可用的最大连接数之间至少有 30% 的差额。

查看这些数字的一个简单方法是:在“Percona 监控和管理”的系统概述界面中查看使用 MySQL 连接图。

下图显示了一个健康的系统,它有着足够数量的可用额外连接。

澳门新濠3559 4

还有一点需要记住:如果您的应用程序所创建的连接数量过多,通常会导致数据库运行缓慢。

在这种情况下,您应该在数据库性能上做文章,而不是简单地允许建立更多的连接。更多的连接会使得潜在的性能问题更加恶化。

将数据库载入内存中

近年来,出现了固态硬盘(SSD)方向上的转变。尽管固态硬盘比传统机械旋臂硬盘快得多,但是它们仍然敌不过将数据存在内存里。

这种差别不仅来自于存储性能本身,还来自于数据库从磁盘或 SSD 里存取数据时所产生的额外工作。

随着近年来硬件技术的改进,不管您是运行在云端,还是管理着自己的硬件,将数据库载入内存已经变得可行。

更令人振奋的是:您并不需要将整个数据库载入内存以获得其性能优势,您只需要将最频繁访问的数据集放入其中便可。

您可能已经看过一些文章,有介绍将数据库多少比例(如:10% 到 33%)载入到内存里。

而事实上并不存在着“一刀切”的规律,数据的访问量决定着载入内存所获得的最佳性能的提升程度。

您与其去寻找某个特定的“神奇”数字,不如去检查数据库达到稳定运行状态时的 I/O(通常是在它开始运行的几个小时之后)。

请查看一下数据的读取,因为如果您的数据库已载入到内存里的话,那么读取会完全结束;而只要有内存可用,写入操作总是会发生的。

下图是“Percona 监控和管理”的 InnoDB 指标仪表板中的 InnoDB I/O图:

澳门新濠3559 5

如上图所示,那些峰值高达每秒 2,000 的 I/O 操作表明(至少是流量负载的一部分)它们与载入内存中数据库的数据集并不相配。

使用 SSD 存储

无论您的数据库是否已被载入内存,您都需要使用快速存储来处理写入操作,并且避免在数据库启动后(重启之后)出现性能问题。这里的快速存储就是指固态硬盘。

一些所谓的“专家”仍在基于成本和可靠性的基础上,主张使用机械旋臂硬盘。坦率地说,当涉及到数据库操作时,这些建议往往是过时的或是完全错误的。现如今,固态硬盘的性能已经非常卓越、可靠且价格低廉了。

并非所有的固态硬盘都是同等生产的。对于数据库服务器来说,您应该选用那些专供服务器工作负载、且能精心呵护数据的 SSD。

例如:防止断电损坏的,而避免使用那些专为台式和笔记本电脑设计的商用固态硬盘。

通过 NVMe 或英特尔 Optane 技术来直接连接的 SSD 往往能够提供最佳的性能。

即使远程连接到 SAN、NAS 或云端的块设备上,固态硬盘也能比机械旋臂硬盘提供更为优越的性能。

横向扩展

即使是性能最高的服务器也有局限性。业界一般用两种方法来进行扩展:纵向和横向。

纵向扩展意味着购买更多的硬件。这样做不但成本昂贵,而且硬件折旧速度快。

而横向扩展,则在处理负载方面有如下几点优势:

您可以从更小型、成本更低的系统中获益。

横向扩展使得系统的线性扩展更方便、更快捷。

由于数据库会横跨增长到多个物理机上,横向扩展在保护数据库的同时,消除了硬件单点故障。

尽管横向扩展有着诸多优势,不过它还是具有一定的局限性。横向扩展需要数据复制,例如基本的 MySQL Replication 或是用于数据同步的 Percona XtraDB 群集。

但是作为回报,您也会获得更高的性能和可用性。如果您需要更高级的扩展性,那么请考虑使用 MySQL 分片(sharding)。

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

当然,过早地规划横向扩展,会增加分布式数据库的复杂性。最近发布的 MySQL 8 候选版本已声称自己能够在单一的系统上处理超过 200 万个简单查询。

追求可视性

可视性是系统设计的最佳境界,MySQL 也不例外。

一旦完成了 MySQL 环境的搭建、运行并调优,您千万不要认为已经万事大吉了。

数据库环境既会受到来自系统更改或流量负荷的影响,也会遇到例如流量高峰、应用程序错误以及 MySQL 自身的各种问题。

为了快速、有效地解决各种问题,您需要建立和实施一些监控机制,从而能获悉数据库环境的状态,并在出现错误时及时分析服务器上的数据。

因此理想情况就是在系统出现问题或是被用户所察觉之前就做到防范于未然。

常用的监测工具有:

MySQL企业监控器(Enterprise Monitor)。

Monyog。

具有免费与开源版本的 Percona 监控和管理(PMM)。

这些工具在监控和故障排除方面提供了很好的操作可视性。

随着越来越多的公司在大规模生产环境中使用开源的数据库(特别是MySQL)来管理和服务他们的业务数据,他们需要把工作重心放在保持数据库的调优和运行效率上。

MySQL 的确是一款能够提升您的应用程序和网站性能的优秀数据库,当然您需要通过对它进行调整,以满足业务需求,监测、发现并防止任何瓶颈和性能方面的问题。

编辑:数据库 本文来源:由此想到了几个优化MySQL的简单方法,MySQL 的性能

关键词:

  • 上一篇:没有了
  • 下一篇:没有了