对于大型应用程序,大量数据会导致性能问题,本指南提供了几个关于如何提高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,外部联接将查看指定列之外的其他数据。
有时,查询中包含UNION和DISTINCT命令,像外部联接一样,可以在必要时使用这些表达式。
使用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函数评估数据库。
相关文章