MySQL状态变量(ServerStatusVariables)_MySQL

MySQL状态变量是当前服务器自启动后累计的一些系统状态信息，主要用于评估当前系统资源的使用情况以进一步分析系统性能而做出相应的调整决策。这些状态变量我们可以理解为等同于Oracle数据库的动态性能视图。MySQL的状态变量有很多，比如SQL执行频率，索引的使用情况，锁资源的使用情况等等。状态变量可以分区全局以及会话级别的状态变量。状态变量不可修改，为只读属性，由系统更新。本文演示了状态变量的一些示例，仅为抛砖引玉之用。

1、状态变量

2、show方式查看状态变量

--当前演示环境root@localhost[(none)]> show variables like 'version';+---------------+------------+| Variable_name | Value      |+---------------+------------+| version       | 5.5.39-log |+---------------+------------+a、查看所有状态变量root@localhost[(none)]> show status;+------------------------------------------+-------------+| Variable_name                            | Value       |+------------------------------------------+-------------+| Aborted_clients                          | 0           || Binlog_stmt_cache_use                    | 1           || Bytes_received                           | 135         || Bytes_sent                               | 266         ||               ................           |             | | Threads_running                          | 1           || Uptime                                   | 76242       || Uptime_since_flush_status                | 76242       |+------------------------------------------+-------------+312 rows in set (0.00 sec)   --可以看出当前版本5.5.39有312个状态变量b、查看仅有global的状态变量(connections)            --查看指定的状态变量，以下两个与connection相关的都为全局状态变量root@localhost[(none)]> show global status like 'connection%';+----------------------+-------+| Variable_name        | Value |+----------------------+-------+| Connections          | 11    | --连接到MySQL服务器的数量(包含成功或失败的)。+----------------------+-------+suse11b:~ # mysql -ufredfred@localhost[(none)]> show global status like '%connection%';+----------------------+-------+| Variable_name        | Value |+----------------------+-------+| Connections          | 12    | --连接之后，我们看到Connections的值变为12了。+----------------------+-------+c、查看既有global又有session状态的变量--查看session状态变量opened_tablesroot@localhost[tempdb]> show session status like 'opened_tables';+---------------+-------+| Variable_name | Value |+---------------+-------+| Opened_tables | 0     |+---------------+-------+root@localhost[tempdb]> select count(*) from tb_slow;+----------+| count(*) |+----------+|   424448 |+----------+root@localhost[tempdb]> show session status like 'opened_tables';+---------------+-------+| Variable_name | Value |+---------------+-------+| Opened_tables | 1     | --值变为1+---------------+-------+--从information_schema.session_status表查询状态变量OPENED_TAB<strong>本文来源gao@daima#com搞(%代@#码@网2</strong>LESroot@localhost[tempdb]> select * from information_schema.session_status    -> where variable_name like 'opened_tables';+---------------+----------------+| VARIABLE_NAME | VARIABLE_VALUE |+---------------+----------------+| OPENED_TABLES | 1              |+---------------+----------------+--查看全局状态变量opened_tablesroot@localhost[tempdb]> show global status like 'opened_tables';+---------------+-------+| Variable_name | Value |+---------------+-------+| Opened_tables | 54    | +---------------+-------+root@localhost[tempdb]> select count(*) from mysql.db;+----------+| count(*) |+----------+|        2 |+----------+root@localhost[tempdb]> show global status like 'opened_tables';+---------------+-------+| Variable_name | Value |+---------------+-------+| Opened_tables | 55    |  --值变为55+---------------+-------+--从information_schema.global_status表查询状态变量OPENED_TABLESroot@localhost[tempdb]> select * from information_schema.global_status    -> where variable_name like 'opened_tables';+---------------+----------------+| VARIABLE_NAME | VARIABLE_VALUE |+---------------+----------------+| OPENED_TABLES | 55             | +---------------+----------------+--清洗状态变量统计信息root@localhost[tempdb]> flush status;Query OK, 0 rows affected (0.00 sec)--下面的查询结果可以看出，session级别的opened_tables被重置为0root@localhost[tempdb]> show session status like 'opened_tables';+---------------+-------+| Variable_name | Value |+---------------+-------+| Opened_tables | 0     |+---------------+-------+--Author: Leshami--Blog  : http://blog.gaodaima.com/leshami--而全局级别的opened_tables未受到任何影响root@localhost[tempdb]> show global status like 'opened_tables';+---------------+-------+| Variable_name | Value |+---------------+-------+| Opened_tables | 55    |+---------------+-------+

3、使用mysqladmin extended-status获取状态变量

suse11b:~ # mysqladmin extended-status|grep Connections| Connections                              | 18          |suse11b:~ # mysqlroot@localhost[(none)]> system mysqladmin extended-status|grep Connections| Connections                              | 20          |root@localhost[(none)]> exitByesuse11b:~ # mysqladmin --help |more    #mysqladmin与状态变量有关的使用extended-status       Gives an extended status message from the serverflush-status          Clear status variables

喜欢 (0)赏

[搞代码]

分享 (0)