作为开发人员，平时和MySQL数据库打交道时，更加关注的是如何写出满足业务需求的SQL语句，而作为MySQL DBA除了关心开发人员写的业务SQL语句之外，对MySQL实例的整体运行状态也要十分关注。MySQL实例就像是一部精密的“机器”，如果某个方面出现短板，则将会对这部“机器”整体运行性能造成负面影响，影响MySQL数据库运行性能的方面有很多，本文将主要从内存方面、IO与文件句柄限制方面、以及像慢查询、Binary日志和连接数方面来展开谈谈如何对MySQL数据库进行状态监控并根据经验值进行优化。从而使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数据库影响十分巨大。
1、查看当前数据库实例配置的内存大小
show variables like '%innodb_buffer_pool_size%';
一般来讲，在使用InnoDB引擎的MySQL数据库需要将innodb_buffer_pool_size设置为实际物理内存的1/2或2/3，原因是通过提高查询的内存命中率来减少对磁盘IO操作。
2、查询缓存
查询缓存，作为加速查询的重要手段，其大小设置的是否合理也关系到MySQL数据库的性能好坏。启用查询缓存，可以极大地减少数据库服务器的CPU使用率。
查看查询缓存设置情况：show variables like '%query_cache%';
查看查询缓存使用情况：show status like 'qcache%';
对于某些不想使用缓存的语句，可以这样使用：select SQL_NO_CACHE count(*) from users where email = 'hello';
3、排序操作监控(sort operations)
show status like 'Sort_merge_passes';
show status like 'Sort_scan';
show status like 'Sort_range';
根据如下公式，评估参数sort_buffer_size设置是否合理
Sort_merge_passes/( Sort_scan+ Sort_range),如果其值大于2的话，则说明sort_buffer_size设置偏小，需要将其调大。
4、连接操作监控(join operations)
show status like 'Select_full_join';
show status like 'Select_range_check';
当监控到这两个状态值为0时，说明数据库中索引被合理的利用，当其中有一项不为0时，则说明join_buffer_size的尺寸偏小，需要将其调大。
【表对象及文件使用方面监控及优化】
1、检查文件打开数
show variable like 'open_files_limit';
show status like 'Open_files';
当打开文件比率超过75%时，说明open_files_limit需要调大，但该值受限于操作系统参数ulimit –u
2、表缓存状态监控
show variable like ‘table_cache';
show status like 'Open_tables';
show status like 'Opened_tables';
show status like 'Open_table_definitions';
按照经验，当open_tables*100/opened_tables小于85%或者当open_tables*100/table_cache大于95%时，需要增加table_cache参数的大小。
3、临时表使用情况监控(tmp table)
show status like 'Created_tmp_tables';
show status like 'Created_tmp_disk_tables';
按照经验，当created_tmp_disk_tables*100/(created_tmp_tables+created_tmp_disk_tables的比率大于25时，说明有过多的内存-磁盘数据交换用于临时表的处理，需要做的处理是增加tmp_table_size 和 max_heap_table_size的大小来缓解临时表的使用对数据库性能的影响。
4、全表扫描的监控(table scan)
show status like 'Com_select';
show status like 'Handler_read_rnd_next';
Handler_read_rnd_next这个涉及到table scans，肯定是越小越好。根据平时的经验，当Handler_read_rnd_next/ Com_select超过4000时，意味着需要调大read_buffer_size的值来缓解全表扫描造成的mysql整理性能下降的问题。
5、表锁等待(table locking)
关于表锁等待的情况是发生在使用MyISAM，如果使用的是InnoDB存储引擎的话，则不需要考虑对表锁等待状态的监控。该状态只在使用MyISAM引擎时关心，涉及到的状态值如下：
show status like ' Table_locks_waited';
show status like ' Table_locks_immediate';
如果table_locks_immediate/table_locks_waited的比率小于5000，则要考虑使用InnoDB引擎来替代MyISAM引擎了。
【慢查询、Binary日志和连接数优化】
1、慢查询日志状态监控(slow queries)
慢查询日志默认是不开启的，当使用log_slow_queries=on开启慢查询日志之后，需要关心的状态值Slow_queries，
show status like ' Slow_queries';
show variable like ' long_query_time ';
一般对于慢查询日志的时间阀值建议是小于5秒，也就是long_query_time不宜设置超过5秒。
2、二进制日志状态监控(binary log)
二进制日志，是MySQL Server中最为重要的日志之一，其记录所有更改数据的语句。还用于复制。 二进制日志是记录着mysql所有事件的操作，可以通过二进制日志做完整恢复，基于时间点的恢复，和基于位置的恢复。二进制日志有关的数据库参数如下：
show variable like 'log_bin';
show variable like 'max_binlog_size';
show variable like 'expire_logs_days';
show variable like 'sync_binlog';
参数expire_logs_days用于控制mysql实例对二进制日志的保留时间控制，超期日志将会被mysql实例自动删除，同时我们也可以通过RESET MASTER 或者PURGE MASTER LOGS命令来手工删除二进制日志文件。
sync_binlog参数是为了保证当数据库实例宕机时，日志内容不会丢失。
3、线程和连接数状态监控
每秒钟监控Threads_created，如果差值大于2的话，说明线程数在迅速增长，需要注意thread_cache_size的值是否过小，导致连接数没有合理缓存。
show status like ' Threads_created';
对于连接数的监控，使用如下语句：
show variable like 'max_connections';
show status like 'Max_used_connections';
show status like 'Threads_connected';
根据经验，当max_used_connections*100/max_connections大于85%的时候，max_connections设置偏小，需要将其调大。
当max_used_connections*100/max_connections小于15%的时候，说明max_connections设置偏大，需要将其调小。
【综述】
影响MySQL数据库的运行性能的因素有很多，本文是从对MySQL数据库实例的角度来考虑优化因素，其实最关键的还是sql语句因素，如果sql语句本身写的不合规格，例如使用 like ‘%关键字%’方式做模糊查询，即便是对参数都调整过了，最终还是选择全表扫描的处理方式。所以，站在开发人员的角度，还是要考虑如何将sql写的更加合理规范，只有在sql语句合理规范的前提下，再对数据库其他方面的参数进行综合考量和调优，才能起到如虎添翼的作用。
恒生技术原创文章，未经授权禁止转载。详情见(点击)转载须知。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
