show [session|global] status
命令可以提供服务器状态信息,也可以在操作系统上使用mysqladmin extended-status
命令获得这些信息。
show [session|global] status 可以根据需要加上参数“session”或者“golbal”来显示session级的统计结果或者golbal级的统计结果。如果不写,默认使用的参数是“session”。
mysql> SHOW global STATUS LIKE 'Com_select'; -- 查询的次数
+---------------+---------+
| Variable_name | VALUE |
+---------------+---------+
| Com_select | 4150119 |
+---------------+---------+
1 ROW IN SET (0.00 sec)
mysql> SHOW STATUS LIKE 'Com_insert'; -- insert的次数
+---------------+-------+
| Variable_name | VALUE |
+---------------+-------+
| Com_insert | 0 |
+---------------+-------+
1 ROW IN SET (0.00 sec)
mysql> SHOW STATUS LIKE 'Com_update'; -- update的次数
+---------------+-------+
| Variable_name | VALUE |
+---------------+-------+
| Com_update | 0 |
+---------------+-------+
1 ROW IN SET (0.00 sec)
mysql> SHOW STATUS LIKE 'Com_delete'; -- delete的次数
+---------------+-------+
| Variable_name | VALUE |
+---------------+-------+
| Com_delete | 0 |
+---------------+-------+
1 ROW IN SET (0.00 sec)
mysql> SHOW STATUS LIKE 'Innodb_rows_inserted'; -- 执行insert操作插入的行数
+----------------------+---------+
| Variable_name | VALUE |
+----------------------+---------+
| Innodb_rows_inserted | 2855250 |
+----------------------+---------+
1 ROW IN SET (0.00 sec)
mysql> SHOW STATUS LIKE 'Innodb_rows_read'; -- select查询返回的行数
+------------------+---------+
| Variable_name | VALUE |
+------------------+---------+
| Innodb_rows_read | 2524106 |
+------------------+---------+
1 ROW IN SET (0.00 sec)
mysql> SHOW STATUS LIKE 'Innodb_rows_updated'; -- 执行update操作更新的行数
+---------------------+-------+
| Variable_name | VALUE |
+---------------------+-------+
| Innodb_rows_updated | 3 |
+---------------------+-------+
1 ROW IN SET (0.00 sec)
mysql> SHOW STATUS LIKE 'Innodb_rows_deleted'; -- 执行delete操作删除的行数
+---------------------+-------+
| Variable_name | VALUE |
+---------------------+-------+
| Innodb_rows_deleted | 0 |
+---------------------+-------+
1 ROW IN SET (0.01 sec)
mysql> SHOW STATUS LIKE 'Slow_queries'; -- 慢查询的次数
+---------------+-------+
| Variable_name | VALUE |
+---------------+-------+
| Slow_queries | 0 |
+---------------+-------+
1 ROW IN SET (0.00 sec)
通过Com_commit和Com_rollback可以了解事务提交和回滚的情况,对于回滚操作非常频繁的数据库,可能意味着应用编写存在问题
mysql> SHOW STATUS LIKE 'Com_begin';
+---------------+-------+
| Variable_name | VALUE |
+---------------+-------+
| Com_begin | 0 |
+---------------+-------+
1 ROW IN SET (0.00 sec)
mysql> SHOW STATUS LIKE 'Com_commit';
+---------------+-------+
| Variable_name | VALUE |
+---------------+-------+
| Com_commit | 0 |
+---------------+-------+
1 ROW IN SET (0.00 sec)
mysql> SHOW STATUS LIKE 'Com_rollback';
+---------------+-------+
| Variable_name | VALUE |
+---------------+-------+
| Com_rollback | 0 |
+---------------+-------+
1 ROW IN SET (0.00 sec)