MySQL性能调优:11个优化技巧

分享于 

7分钟阅读

Linux

  繁體

对于大型应用程序,大量数据会导致性能问题,本指南提供了几个关于如何提高MySQL数据库性能的调优技巧。

前提条件

安装并正在Linux top命令有关如何使用资源的详细说明,注意MySQL进程及它所需的处理器使用百分比。

处理器的升级成本更高,但是如果你的CPU成为瓶颈,则可能需要升级。

节约内存

你可以调整内存缓存以提高性能,如果没有足够的内存,或者现有内存没有得到优化,那么瞎调整最终会损害性能而不是提高性能。

和其他瓶颈一样,如果你的服务器经常耗尽内存,则可以通过添加更多内存,如果内存不足,服务器将数据缓存到硬盘上,数据库会明显降低性能。

网络

监视网络流量以确保你具有足够的基础结构来管理负载非常重要。

网络过载可能导致延迟,数据包丢失甚至服务器中断,确保有足够的网络带宽来容纳正常的数据库流量。

使用InnoDB,而不是MyISAM

MyISAM是用于某些MySQL数据库的旧数据库样式,这是一个效率较低的数据库设计,较新的InnoDB支持更高级的功能,并具有内置的优化机制。

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

使用MySQL的最新版本

对于旧版和旧版数据库,使用最新版本并不总是可行,但是,只要有可能,就应该检查使用的MySQL版本,并且升级到最新版本。

软件性能调整

这些方法包括调整MySQL配置文件,编写更高效的数据库查询,以及构造数据库以更有效地检索数据。


注:调整配置设置时,最好进行小的增量调整,大调整可能会给另一个值带来负面作用,并降低性能。另外,建议你一次进行一次更改,然后马上进行测试,每次只更改一个变量时,更容易跟踪错误或错误配置。


考虑使用自动性能改进工具

有三个实用程序可以评估MySQL数据库,并给出建议,可以提高性能。

第一个是tuning-primer,这个工具有点老,MySQL 5.5 - 5.7可以使用,它可以分析你的数据库,并提供建议以提高性能,例如,如果感觉你的系统无法足够快地处理查询以保持高速缓存清晰,则建议你提高query_cache_size参数。

对于大多数现代MySQL数据库,第二个调优工具是MySQLTuner,这个脚本(mysqltuner.pl )是用Perl编写的,和tuning-primer一样,它分析数据库配置,查找瓶颈和效率低下,给出指标和建议。

第三个实用程序是phpMyAdmin Advisor,和另外两个实用程序一样,它评估数据库并推荐调整参数。

适当地使用索引

许多数据库查询都使用类似下面的结构:

SELECT ... WHERE

这些查询包括计算,筛选和检索结果,可以通过为相关表添加组索引来重构这些表,可以将查询指向索引以加快查询速度。

谓词中的函数

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

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

UPPER符号创建一个函数,该函数必须在SELECT操作期间操作,这使查询正在执行的工作加倍,如果可能,应该避免它。

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

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

SELECT * FROM MYTABLE WHERE COL1 LIKE '%123'

在SELECT函数中指定列

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

适当地使用ORDER BY

ORDER BY表达式按指定列对结果进行排序,如果尝试以不同的顺序对不同的列进行排序,将降低性能,可以将它与索引组合以加速排序。

加入,联盟,地区

尽可能使用inner join,外部联接将查看指定列之外的其他数据。

有时,查询中包含UNIONDISTINCT命令,像外部联接一样,可以在必要时使用这些表达式。

使用EXPLAIN函数

现代MySQL数据库包含一个EXPLAIN函数。

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

MySQL服务器配置

此配置涉及更改/etc/mysql/my.cnf文件,继续谨慎操作,一次进行细微的更改。

query_cache_size-指定等待运行的MySQL查询的缓存大小。建议是从10MB左右的小值开始,然后增加到不超过100-200MB,如果缓存的查询过多,则你会遇到一连串的查询 "Waiting for cache lock.",一个更好的方法是使用EXPLAIN评估每个查询,并且找到提高效率的方法。

max_connection-指允许进入数据库的连接数。如果你得到"too many connections"错误增加这个值会有帮助。

innodb_buffer_pool_size-此设置将系统内存分配为数据库的数据缓存。如果您有大量数据,请增加此值。

innodb_io_capacity-此变量设置存储设备的输入/输出速率。这直接关系到存储驱动器的类型和速度,你可以调整此值以更好地匹配硬件。

结束语

现在应该知道如何改进MySQL性能和优化数据库。

查找瓶颈(硬件和软件),并考虑使用自动化工具和EXPLAIN函数评估数据库。


相关文章