从内存到连接数，如何有效提升MYSQL性能（一）？

作为开发人员,平时和MySQL数据库打交道时,更加关注的是如何写出满足业务需求的SQL,而作为MySQL DBA除了关心开发人员写的业务SQL语句之外,对MySQL实例的整体运行状态也要十分关注,MySQL实例就像是一部精密的“机器”,如果某个方面呈现短板,则将会对这部“机器”整体运行性能造成负面影响,影响MySQL数据库运行性能的方面有很多.

本文将主要从内存方面、IO与文件句柄限制方面、以及像慢查询、Binary日志和连接数方面来展开谈谈如何对MySQL数据库进行状态监控并依据经验值进行优化.从而使MySQL这部“机器”更高效的运转.

【“工欲善其事,必先利其器” 】

▪ 如何获取MySQL状态值以便于开展后续的诊断调优工作?

查看mysql参数的命令,在mysql客户端输入"show variables"

如果想要查看某个具体的值,可以使用如下命令：show variables like "%具体变量%";

例如：查看最年夜连接数: show variables like '%max_connections%'；如图1：

(图1)

查看mysql状态的命令,在mysql客户端输入"show status"之后将会看到如下输出；如图2：

(图2)

如果想要查看某个具体的值,可以使用如下命令：show status like "%具体变量%";例如：查看历史最年夜连接数: show status like '%max_used_ connections%'；如图3：

(图3)

【内存方面监控及优化 】

目前MySQL数据库默认使用的存储引擎为InnoDB引擎,该引擎的存储机制同Oracle很相似,因此这就意味着内存的合理设置对于使用InnoDB存储引擎的MySQL数据库影响十分巨年夜.

一、查看当前数据库实例配置的内存年夜小

show variables like '%innodb_buffer_pool_ size%';

一般来讲,在使用InnoDB引擎的MySQL数据库必要将innodb_buffer_pool_size设置为实际物理内存的1/2或2/3,原因是通过提高查询的内存命中率来减少对磁盘IO操作.

二、查询缓存

作为加速查询的重要手段,其年夜小设置的是否合理也关系到MySQL数据库的性能好坏.启用查询缓存,可以极年夜地减低数据库服务器的CPU使用率.

查看查询缓存设置情况：show variables like '%query_cache%';

查看查询缓存使用情况：show status like 'qcache%';

对付某些不想使用缓存的语句,可以这样使用：

三、排序操作监控(sort opera-tions)

依据如下公式,评估参数sort_buffer_size设置是否合理 ：

Sort_merge_passes/(Sort_scan+Sort_ range),如果其值大于2的话,则说明sort_buffer_size设置偏小,必要将其调大.

四、连接操作监控(join opera-tions)

当监控到这两个状态值为0时,说明数据库中索引被合理的利用,当其中有一项不为0时,则说明join_buffer_size的尺寸偏小,必要将其调大.

本文我们从MYSQL的状态值和内存方面来介绍MYSQL性能调优,下文我们会从表工具和文件方面使用监控和慢查询、Binary日志和连接数方面来介绍影响数据库性能的因素.