您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

这些 MySQL 调优配置,你都知道吗?

时间:2020-12-24 14:43:30  来源:  作者:

MySQL 数据库,基本上运维都会接触到,而对于数据库的性能优化,想必是大家比较关注的,下面分享一些调优技巧。

这些 MySQL 调优配置,你都知道吗?

 

先决条件

  • 安装 MySQL,版本大于 5.7
  • 有部分数据
  • 操作系统、数据库管理员权限

 

MySQL 系统性能优化

在系统层面,调整硬件和软件选项以提高 MySQL 性能。

1. 平衡四个主要硬件资源

存储

如果使用的是传统的 HDD,可以升级到 SSD。

使用 sysstat 工具包中的 iotop、sar 监控输入/输出速率,如果磁盘使用率远远高于其他资源,请考虑添加更多存储或升级到更快的存储。

 

CPU

通常认识处理器是衡量系统速度的指标,使用 top 命令可以查看关于 CPU 详细的使用信息,注意 MySQL 进程及其CPU 使用率。

这些 MySQL 调优配置,你都知道吗?

 

top - 10:41:15 up 205 days, 23:03,  1 user,  load average: 1.07, 1.02, 0.98
Tasks: 424 total,   1 running, 423 sleeping,   0 stopped,   0 zombie
%Cpu(s):  8.1 us,  0.5 sy,  0.0 ni, 91.1 id,  0.3 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem : 13145304+total, 59450800 free, 53483316 used, 18518920 buff/cache
KiB Swap: 16777212 total, 16546300 free,   230912 used. 76746272 avail Mem 

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                 
 7133 mysql     20   0  103.1g  45.5G  10148 S 205.6 36.3  94658:39 mysqld                                                                     
 6754 mongod    20   0 4504956   3.2g  11872 S  64.8  2.6   1916:34 mongod                                                                     
29632 root      20   0   36.5g 506904   6884 S   0.0  0.4 154:00.13 JAVA                                                                       
15795 gdm       20   0 5776296 199816  15260 S   0.0  0.2  72:39.84 gnome-shell                                                                
31684 root      20   0  353572  98864   1308 S   1.9  0.1 386:24.16 redis-server                                                               
31686 root      20   0  320804  98496   1108 S   1.9  0.1 288:01.69 redis-server                                                               
31688 root      20   0  326948  97988   1108 S   0.0  0.1 288:37.66 redis-server                                                               
16576 gdm       20   0  701592  60952    856 S   3.7  0.0   2688:28 gsd-color                                                                  
20199 root      20   0  292132  39696   1212 S   0.0  0.0 483:14.41 redis-server                                                               
 6437 root      20   0   84744  39352  39100 S   0.0  0.0   1:16.05 systemd-journal                                                            
17429 root      20   0  123500  30252   4668 S   0.0  0.0   4476:21 node_exporter                                                              
12977 root      20   0  411672  25976  21820 S   0.0  0.0   8:10.97 rsyslogd                                                                   
12480 polkitd   20   0  620076   8848   2552 S   0.0  0.0   9:42.49 polkitd                                                                    
30362 root      20   0  350468   6728   4636 S   0.0  0.0   0:00.01 abrt-dbus                                                                  
13071 root      20   0  435300   5552   3940 S   0.0  0.0   2:36.75 X                                                                          
30333 root      20   0  116212   4728   3680 S   0.0  0.0   0:00.02 sshd                                                                       
12491 root      20   0  519268   3872   1984 S   0.0  0.0  63:00.71 udisksd                                                                    
16584 gdm       20   0 1069144   3688   1248 S   0.0  0.0   0:31.78 gsd-media-keys                                                             
16544 root      20   0  398376   3612   1124 S   0.0  0.0   0:34.98 boltd            

CPU 升级成本更高,但是如果已经成为瓶颈了,可能还是需要升级的。

 

内存

内存表示 MySQL 服务器中的内存总量。可以调整内存缓存(下面细说),以提高性能。但是如果没有足够的内存,或者现有内存没有经过优化,那就不要乱来

与其他资源一样,如果服务器不断耗尽内存,可以通过添加内存升级。如果内存不足,服务器将缓存数据存储到硬盘上,降低性能。

 

网络

监控网络流量,确保基础架构没问题。

网络过载可能导致延迟,数据包丢失甚至服务中断。

确保有足够的网络带宽来容纳正常水平的数据库流量。

 

2.使用 InnoDB 而不是 Myisam

Myisam 是 MySQL 5.1 及之前的版本使用的存储引擎,不支持事务以及行级锁,而且一个毫无疑问的缺陷是崩溃后无法安全恢复,效率也较低。InnoDB 是 MySQL 5.5 版本以后的默认存储引擎,InnoDB 是为 Mysql 处理巨大数据量时的最大性能设计。它的 CPU 效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。在数据量大的网站或是应用中 Innodb 是倍受青睐的。

InnoDB 使用聚集索引并将数据保留在页面中,这些页面存储在连续的物理块中。 如果一个页面的值太大,InnoDB会将其移动到另一个位置,然后索引该值。 此功能有助于将相关数据保存在存储设备上的同一位置,这意味着物理硬盘驱动器需要较少的时间来访问数据。

3. 使用最新版本的 MySQL

如果由于历史遗留问题,只能用旧版本的 MySQL,那没办法。但是只要有可能,都用最新版的 MySQL,因为新版性能更好,高级特效更多。

 

软件性能调整

这些方法涉及调整 MySQL 配置文件,编写更高效的 SQL 语句以及创建索引等等

注意:调整时,一次只进行一个更改,进行测试,然后进行下一个。如果一次调整很多,出现性能下降或故障,排查问题不好下手。

 

4. 使用自动性能改进工具

和大多数工具一样, 不一定适用所有的 MySQL 版本。下面提供几个工具来评估 MySQL 数据库及提供更改建议。

MySQL Tuning Primer 这个工具使用于 MySQL >= 5.5 <= 5.7, 它可以分析数据库并建议设置以提高性能。 例如,如果感觉系统处理查询较慢,则建议提高 query_cache_size 参数。

MySQLTuner-perl 使用 Perl 编写的脚本,可快速查看 MySQL 安装并进行调整以提高性能和稳定性。 检索当前配置变量和状态数据,并以简短的格式显示一些基本的性能建议。

这些 MySQL 调优配置,你都知道吗?

 

该脚本适用于 MySQL 多个版本。

这些 MySQL 调优配置,你都知道吗?

 

日志文件建议是列表中的第一条建议,在底部,则可以看到有关提高MySQL性能的一般建议。

5. 优化查询

查询是用于搜索数据库中与特定值匹配的数据的编码请求。有些查询需要很长时间才能返回结果。如果您的环境依赖于自动查询,它们可能会影响性能,检查并及时终止堆积的 MySQL 进程。

 

6. 适当的时候使用索引

查询经常使用类似于以下的结构:

SELECT … WHERE

 

这些查询涉及评估,过滤和检索结果。 可以通过为相关表添加索引,加快查询速度。

 

7. 谓词中的函数

避免在谓词查询中使用函数,如:

SELECT * FROM MYTABLE WHERE UPPER(COL1)='123'

UPPER 创建一个函数,这个函数必须在 SELECT 运行期间进行操作,这会使查询正在做的工作加倍,尽量避免。

 

8. 避免在谓词中使用%通配符

通配符%表示零个或多个字符。 如果查询如下所示:

SELECT * FROM MYTABLE WHERE COL1 LIKE '%123'

表达式 "%123" 包含所有以 123 结尾的值。% 符号代表任何类型或数量的前面字符,包括任何字符。 这样必须执行全表扫描,尽量避免

 

9.在 SELECT 函数中指定列

一种常见的表达方式是使用 SELECT *扫描所有数据库列。 如果指定所需的列,则查询将不需要扫描不相关的列。

 

10.适当使用 ORDER BY ORDER BY 表达式

按指定的列对结果进行排序。可用于一次按两列排序。这些应该以相同的顺序(升序或降序)进行排序。如果尝试按不同顺序对不同的列进行排序,则会降低性能。可以将其与索引结合使用以加快排序速度。

 

11. JOIN, UNION, DISTINCT

尽可能尝试使用内部联接。 外部联接将查看指定列之外的其他数据。 如果您需要这些数据,那很好,但是包含不必要的数据会浪费性能。 查询有时包含UNION和DISTINCT命令。 就像外部联接一样,可以在必要时使用这些表达式。 但是,它们增加了数据库的其他排序和读取。 如果您不需要它们,最好找到一个更有效的表达方式。

 

12.使用EXPLAIN功能

 

将 EXPLAIN 表达式附加到查询的开头将读取并评估查询。 如果表达式效率低下或结构混乱,EXPLAIN 可以帮助您找到它们。 然后,您可以调整查询的措词,以避免意外的表扫描或其他性能影响。

 

13.MySQL服务器配置

此配置涉及更改 my.cnf 文件。 请谨慎操作,并一次进行较小的更改。

 

  • query_cache_size –指定等待运行的MySQL查询的缓存大小。建议从较小的值开始,大约10MB,然后再增加到不超过 100-200MB。如果缓存的查询过多,您会遇到 Waiting for cache lock. 的级联查询。如果您的查询一直在备份,则更好的过程是使用 EXPLAIN 评估每个查询并找到提高效率的方法。
  • max_connection –指允许进入数据库的连接数。如果您由于Too many connections而出错,则增加此值可能会有所帮助。
  • innodb_buffer_pool_size –此设置将系统内存分配为数据库的数据缓存。如果有大量数据,请增加此值。注意运行其他系统资源所需的RAM。
  • innodb_io_capacity -此变量设置存储设备的输入/输出速率。这与存储驱动器的类型和速度直接相关。 5400 rpm HDD的容量将比高端 SSD 或 Intel Optane 低得多。可以调整此值以更好地匹配硬件。

结论

现在,您应该知道如何提高 MySQL 性能和调整数据库。

查找瓶颈(硬件和软件)、执行更多工作所需的查询,并考虑使用自动化工具和EXPLAIN 函数来评估数据库。



Tags:MySQL 调优   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
MySQL 数据库,基本上运维都会接触到,而对于数据库的性能优化,想必是大家比较关注的,下面分享一些调优技巧。 先决条件 安装 MySQL,版本大于 5.7 有部分数据 操作系统、数据库管理...【详细内容】
2020-12-24  Tags: MySQL 调优  点击:(142)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(6)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(17)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(16)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(20)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(27)  评论:(0)  加入收藏
相关文章
    无相关信息
最新更新
栏目热门
栏目头条