Mysql通过show status 命令了解各种SQL 的执行频率

MySQL 客户端连接成功后，通过show [session|global]status 命令可以提供服务器状态信息，也可以在操作系统上使用mysqladmin extended-status 命令获得这些消息。show[session|global] status 可以根据需要加上参数“session”或者“global”来显示session 级（当前连接）的统计结果和global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。
下面的命令显示了当前session 中所有统计参数的值：

mysql> show status like 'Com_%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Com_admin_commands | 0 |
| Com_alter_db | 0 |

| Com_alter_event | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 1 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
……

Com_xxx 表示每个xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。
 Com_select：执行select 操作的次数，一次查询只累加1。
 Com_insert：执行INSERT 操作的次数，对于批量插入的INSERT 操作，只累加一次。
 Com_update：执行UPDATE 操作的次数。
 Com_delete：执行DELETE 操作的次数。
上面这些参数对于所有存储引擎的表操作都会进行累计。下面这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。
 Innodb_rows_read：select 查询返回的行数。
 Innodb_rows_inserted：执行INSERT 操作插入的行数。
 Innodb_rows_updated：执行UPDATE 操作更新的行数。
 Innodb_rows_deleted：执行DELETE 操作删除的行数。
通过以上几个参数，可以很容易地了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的SQL 大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会进行累加。
对于事务型的应用，通过Com_commit 和Com_rollback 可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。
此外，以下几个参数便于用户了解数据库的基本情况。
 Connections：试图连接MySQL 服务器的次数。
 Uptime：服务器工作时间。
 Slow_queries：慢查询的次数。

 

-----------------------------------------------------------------------------------

mysql数据库的性能状态监控点非常多，其中很多量都是不能忽视的必须监控的量，且90%以上的内容 可以在连接上mysql后执行show status 或是 show veriables的输出值 获得，需要注意的是以上的命令获得的状态值实际上是累计值，所以如果 要计算时段内的变化 量还需要稍加处理，下面看下几项需要重点关注的性能状态：

 

1.  key buffer 命中率

 

key buffer 命中率代表了myisam类型表的索引cache命中率，命中率的大小直接影响myisam类型表的读写性能。key buffer 命中率实际上包括读命中率和写命中率两种，mysql中并没有直接给出这两个命中率的值，但是可以通过如下方式计算：

 

key_buffer_read_hits=(1-key_reads/key_read_requests) * 100%

key_buffer_write_hits=(1-key_writes/key-write-requests)*100%

 

获取所需要状态的变量值：

 

show status like 'key%'

 

mysql> show status like 'key%';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| Key_blocks_not_flushed | 0     |

| Key_blocks_unused      | 13396 |

| Key_blocks_used        | 19    |

| Key_read_requests      | 71    |

| Key_reads              | 19    |

| Key_write_requests     | 1     |

| Key_writes             | 1     |

+------------------------+-------+

7 rows in set (0.00 sec)

 

命中率过低，说明myisam类型表的读写存在问题。

 

2. innodb buffer 命中率

 

这里innodb buffer 所指的是innodb_buffer_pool，也就是用来缓存innodb类型表和索引的内在空间。类似key buffer，同样可以根据mysql提供的相应的状态信息计算其命中率：

 

innodb_buffer_read_hits=(1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests) * 100%;

 

获取所需状态变量值：

mysql> show status like 'innodb_buffer_pool_read%';

+---------------------------------------+----------+

| Variable_name                         | Value    |

+---------------------------------------+----------+

| Innodb_buffer_pool_read_ahead_rnd     | 0        |

| Innodb_buffer_pool_read_ahead         | 0        |

| Innodb_buffer_pool_read_ahead_evicted | 0        |

| Innodb_buffer_pool_read_requests      | 27269024 |

| Innodb_buffer_pool_reads              | 827      |

+---------------------------------------+----------+

 

命中率过低，说明innodb类型表的读写存在问题。

 

3. query cache命中率

 

query cache 是mysql的查询cache，在my.cnf配置文件若打开，则可以对查询过的语句结果进行cache。对于一些用户数不高或一次性统计平台建议关闭查询缓存。若开启query cache，则对query cache 命中率进行监控也是需要的，它可以告诉我们是数据库是否在正确使用query cache。query cache命中率计算如下：

 

query_cache_hits =(Qcache_hits/(Qcache_hits+Qcache_inserts))* 100%;

 

获取变量值：

mysql> show status like 'Qcache%';

+-------------------------+-------+

| Variable_name           | Value |

+-------------------------+-------+

| Qcache_free_blocks      | 0     |

| Qcache_free_memory      | 0     |

| Qcache_hits             | 0     |

| Qcache_inserts          | 0     |

| Qcache_lowmem_prunes    | 0     |

| Qcache_not_cached       | 0     |

| Qcache_queries_in_cache | 0     |

| Qcache_total_blocks     | 0     |

+-------------------------+-------+

8 rows in set (0.00 sec)

 

4. table_cache 命中率

 

table_cache指定表调整缓存的大小，当mysql访问某个表时，若表缓存空间还有空间，则将该表就被打开并将数据放入其中，下次访问此表时可以更快的访问表的内容。通过查峰值时间的状态值open_tables 和 opened_tables可以决定是否需要增加table_cache值。需要注意的是table_cache设置很太高，可能会造成文件描述符不足，从而造成性能不稳定或是连接失败。

table_cache的当前状态量可以帮助我们判断系统参数table_open_cache的设置是否合理。如果状态量open_tables与opened_tables之间的比率过低，则代表table cache设置过小，网上有人认为这值的比率设置在80%最好。

 

获取变量：

 

mysql> show status like 'open%';

+--------------------------+---------+

| Variable_name            | Value   |

+--------------------------+---------+

| Open_files               | 6       |

| Open_streams             | 0       |

| Open_table_definitions   | 87      |

| Open_tables              | 26      |

| Opened_files             | 1554504 |

| Opened_table_definitions | 0       |

| Opened_tables            | 0       |

+--------------------------+---------+

 

5. thread cache命中率

 

在mysql中，为了尽可能提高客户端连接的过程，实现 了一个thread cache池，将空闲的连接线程存放在其中，而不是请求完成后销毁，当有新的连接请求的时候，mysql首先检查thread cache是否存储空闲的连接线程，如果存在则取出来直接使用，如果没有空闲连接线程，才创建新的线程。

 

thread cache命中率能直接反应出系统参数thread_cache_size设置是否合理。一个合理的read_cache_size参数能够节约大量创建新连接时所需要消夏的资源。

 

thread cache命中率计算方式如下：

 

thread_cache_hits = (1- threads_created/connections) * 100 %;

 

获取所需要状态变量值：

 

 

mysql> show status like 'thread%';

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| Threads_cached    | 0     |

| Threads_connected | 1     |

| Threads_created   | 23581 |

| Threads_running   | 1     |

+-------------------+-------+

 

正常来说，thread cache命中率在90%以上才算合理。

 

6. tmp table相关状况分析

 

tmp table 主要用于监控mysql使用临时表的量是否过多，是否有临时表过大而不得不从内存中换出到磁盘文件中，临时表使用状态 信息获取如下：

 

mysql> show status like 'created_tmp%';

+-------------------------+-------+

| Variable_name           | Value |

+-------------------------+-------+

| Created_tmp_disk_tables | 0     |

| Created_tmp_files       | 65    |

| Created_tmp_tables      | 0     |

+-------------------------+-------+

 

Created_tmp_disk_tables为临时表过大无法在内存中完成，而不得不使用磁盘的次数。若create_tmp_tables非常大，则可甬系统排序操作过多，或者可能是连接方式不是很优化。而如果是create_tmp_dis_table/create_tmp_tables比率过高，如超过10%，则需要考虑tmp_table_size参数是否需要调整大些。建议tmp_table_size与max_heap_table_size需要设置成一样大。

 

7. binlog cache

 

若打开binlog日志功能，则需要考虑binlog cache问题。binlog不是一有数据就写到binlog中，而是先写入到binlog cache中，再写入到binlog中。

 Binlog_cache_disk_use为binlog使用硬盘使用量， Binlog_cache_use  为binlog已使用的量。若 Binlog_cache_disk_use大于0，则说明binlog_cache不够用。

 

mysql> show status like 'binlog_cache%';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| Binlog_cache_disk_use | 58    |

| Binlog_cache_use      | 39785 |

+-----------------------+-------+

 

8. innodb_log_waits

 

innodb_log_waits状态变量直接反应innodb log buffer 空间不足造成等待的次数。innodb_log_waits直接反应系统的写入性能，当值 达到每秒1次时，就需要增加innodb_log_buffer_size的值，适当的增加不会造成内存不足的问题。

 

9. 复制延时量

 

复制延时量：复制延时量将直接影响了Slave数据库处于不一致状态的时间长短。如果我们是通过Slave来提供读服务，就不得不重视这个延时量。我们可以通过在Slave节点上执行“SHOWSLAVESTATUS”命令，取Seconds_Behind_Master项的值来了解Slave当前的延时量（单位：秒）。当然，该值的准确性依赖于复制是否处于正常状态。每个环境下的Slave所允许的延时长短与具体环境有关，所以复制延时多长时间是合理的，只能由读者朋友根据各自实际的应用环境来判断。

 

mysql> show slave status\G

  Seconds_Behind_Master: NULL

 

10. 锁状态

 

mysql的锁有表锁和行锁，myisam最小锁为表锁，innodb最小锁为行锁，可以通过以下命令获取锁定次数、锁定造成其他线程等待次数，以及锁定等待时间信息。

 

mysql> show status like '%lock%';

+------------------------------------------+---------+

| Variable_name                            | Value   |

+------------------------------------------+---------+

| Com_lock_tables                          | 0       |

| Com_unlock_tables                        | 0       |

| Innodb_row_lock_current_waits            | 0       |

| Innodb_row_lock_time                     | 0       |

| Innodb_row_lock_time_avg                 | 0       |

| Innodb_row_lock_time_max                 | 0       |

| Innodb_row_lock_waits                    | 0       |

| Key_blocks_not_flushed                   | 0       |

| Key_blocks_unused                        | 13396   |

| Key_blocks_used                          | 19      |

| Performance_schema_locker_lost           | 0       |

| Performance_schema_rwlock_classes_lost   | 0       |

| Performance_schema_rwlock_instances_lost | 0       |

| Qcache_free_blocks                       | 0       |

| Qcache_total_blocks                      | 0       |

| Table_locks_immediate                    | 1570736 |

| Table_locks_waited                       | 7294    |

+------------------------------------------+---------+

 

如当Table_locks_waited与Table_locks_immediate的比值较大，则说明我们的表锁造成的阻塞比较严重，可能需要调整Query语句，或者更改存储引擎，亦或者需要调整业务逻辑。当然，具体改善方式必须根据实际场景来判断。而Innodb_row_lock_waits较大，则说明Innodb的行锁也比较严重，且影响了其他线程的正常处理。同样需要查找出原因并解决。造成Innodb行锁严重的原因可能是Query语句所利用的索引不够合理（Innodb行锁是基于索引来锁定的），造成间隙锁过大。也可能是系统本身处理能力有限，则需要从其他方面来考虑解决。