MYSQL 状态变量性能调优

 

Mysql状态变量通过”show global status”（自Mysql上次启动以来的统计）获取，重要的参数包括各种SQL执行频率，索引使用情况、锁资源使用情况等。

长时间运行的Mysql 服务器，运行flush status; 可以重置一些计数器优化性能。

例如DB 服务器是4 核16G 内存，通过状态变量，可以优化Mysql 静态变量和SQL ：

 

参数	说明
基本情况
Connections	连接服务器( 不管是否成功) 的次数
Uptime	服务器工作时间
Max_used_connections	同时使用的最大连接数量
Open_tables	当前打开的表的数量。
Opened_tables	已经打开的表的数量。调优静态变量表缓存数 table_cache ：如果open_tables 接近table_cache ，并且opened_tables 不断增长，就需要增加table_cache 的值。 table_cache 是所有线程打开的表的数目( 一个表使用2 个文件描述符) ，表数量多，就要大一些。增大该值可以增加mysqld 需要的文件描述符的数量。根据数据库系统中表数量来决定该值，如2048 。
线程使用情况
Threads_cached	线程缓存内的线程数
Threads_connected	当前打开的线程数
Threads_created	创建过的线程数。调优静态变量线程缓存数 thread_cache ：如果该值增加很快，当前thread_cache_size 的值可能太小。缓存访问率是Threads_created/Connections 。 服务器应缓存多少线程以便重新使用。当客户端断开连接时，如果线程少于thread_cache_size ，则客户端的线程被放入缓存，一般配置8 。
Threads_running	运行( 非睡眠) 状态的线程数
查询缓存
Qcache_free_blocks	缓存中相邻内存块的个数。数目大说明可能有碎片。调优方法： FLUSH QUERY CACHE; 会对缓存中的碎片进行整理，从而得到一个空闲块，如果flush 运行的时间很长，说明缓存太大了，可以适当调小静态变量query_cache_size 的值。
Qcache_free_memory	缓存中剩余的内存。调优静态参数 query_cache_size ：如果剩余内存不足，可以增加该值，如设置query_cache_size=64M
Qcache_hits	查询缓存命中次数，该值越大越好
Qcache_inserts	插入查询缓存的次数。缓存命中率 = 1 – Qcache_hits/ Qcache_inserts 。80% 以上的查询缓存命中率就算合格。
Qcache_lowmem_prunes	查询缓存过低的次数。缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks 和 free_memory 可以告诉您属于哪种情况）。
Qcache_not_cached	不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句。
Qcache_queries_in_cache	当前缓存的查询（和响应）的数量。
Qcache_total_blocks	缓存中块的数量。
SQL执行频率
Com_select	执行select 操作次数
Com_insert	执行insert 操作次数
Com_update	执行update 操作次数
Com_delete	执行delete 操作次数
Com_commit	事务执行commit 操作次数
Comm_rollback	事务执行rollback 操作次数。如果回滚频繁，就说明程序存在某些问题。
Slow_queries	慢查询的次数。调优 SQL 性能 ：如果该值增加很快，需要分析慢查询日志，针对查询SQL 优化。
Innodb_rows_read	执行select 返回的行数。以下几个InnoDB 的。
Innodb_rows_inserted	执行insert 操作的行数。通过这几个参数，可以知道数据库是查询为主还是插入为主。
Innodb_rows_updated	执行update 操作的行数
Innodb_rows_deleted	执行delete 操作的行数
Sort_merge_passes	排序算法已经执行的合并的数量。调优静态变量 sort_buffer_size ：如果该值很大，说明排序缓冲区太小，如设置sort_buffer_size = 5M 当MySQL 必须要进行排序时，就会在从磁盘上读取数据时分配一个排序缓冲区来存放这些数据行。如果要排序的数据太大，那么数据就必须保存到磁盘上的临时文件中，并再次进行排序。
索引使用情况
Handler_read_first	使用全索引扫描的次数。如SELECT col1 FROM foo ，假定col1 有索引
Handler_read_key	使用索引次数，该值越高越好。
Handler_read_next	按照键顺序读下一行的请求数。使用索引描述时，从数据文件取数据的次数
Handler_read_prev	使用索引描述时，按索引倒序从数据文件取数据的次数。一般是order by/desc 查询
Handler_read_rnd	查询直接操作数据文件的次数，有可能未使用索引
Handler_read_rnd_next	在数据文件中读下一行的请求数。若该值非常大，说明使用了大量的表扫描，索引使用率不高或没有使用索引。Handler_read_rnd_next/Com_select 是表扫描比率，如果该值超过 4000 ，就应该调优静态参数 read_buffer_size 。如read_buffer_size=1M ，若超过8M ，那么就要优化SQL 了。
锁使用情况
Innodb_row_lock_current_waits	当前等待行锁的行数
Innodb_row_lock_time	行锁定用的总时间(ms)
Innodb_row_lock_time_avg	行锁定的平均时间(ms) 。该值大，说明锁冲突大
Innodb_row_lock_time_max	行锁定的最长时间(ms)
Innodb_row_lock_waits	行锁定必须等待的时间(ms) 。该值大，说明锁冲突大