MySQL 性能查询 show status

545 阅读2分钟

这是我参与8月更文挑战的第21天,活动详情查看:8月更文挑战

猫和老鼠.jpg

前言:   本篇文章 是我关于MySQL的第21篇文章,水平一般、能力有限。文章写的比较浅,适合新手来看。接着上一篇来讲一下,MySQL单条sql查询刨析

show status

MySQL在执行show status命令后可以输出执行的计数器,它并不能显示出一条语句执行了多久。只能显示索引等执行时候的使用情况。

我们首先直接使用此命令看一下。

mysql> show status;
+-------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name                                         | Value                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+-------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Aborted_clients                                       | 4                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| Aborted_connects                                      | 111                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| Acl_cache_items_count                                 | 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| Binlog_cache_disk_use                                 | 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| Binlog_cache_use                                      | 1                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| Binlog_stmt_cache_disk_use                            | 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| Binlog_stmt_cache_use                                 | 10002                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| Bytes_received                                        | 287                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| Bytes_sent                                            | 15199                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| Caching_sha2_password_rsa_public_key                  | -----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA3b49kSZMsGL2Q0SrYVtZ
7Hr89K0jTAwRFwUHO57o9GZ7s5oUwqaBHgpTbyFWv+v8rW0oZ8HZ5Eu+Vjg2iAKq
2p4lYhRvIWjQvIlC/ewKGPR9TBSNDxYgLJ15S4AbZsN0vFAz+KDoryHGwZscyakh
OLQsMDkacpWBU+P/3fceW+dt+eEfki3Om8e66I1fVZ2ZJoAkVazRTyHUM5CGQes7
M2wQtFdjTOljQ5W4dQfqAao7VKVbAvz5brjj1acENPqxTQynQUYFTUSwYd8aToxy
5bNW1/lNySiIpW/Bg2UuBEBpFQnhumSWCyCPGuMj98dEG4lROiNWNUgJtgjXg73s
GwIDAQAB
-----END PUBLIC KEY-----
 |
| Com_admin_commands                                    | 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| Com_assign_to_keycache                                | 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| Com_alter_db                                          | 0                                                                                                                                                                                                                                                                                                                                                               +-------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
483 rows in set (0.00 sec)

执行结果竟然有483条,那么我们需要有用的信息要经过限定才能查出来了。应该只收集我们需要级别的查询。

首先我们将计数器重置为0;

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from food;
+-------+--------+------+
10003 rows in set (0.02 sec)

-- 展示服务器器删除语句次数
mysql> show status like 'com_update';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_update    | 0     |
+---------------+-------+
1 row in set (0.00 sec)

-- 展示服务器器运行时间
mysql> show status like 'uptime';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Uptime        | 770647 |
+---------------+--------+
1 row in set (0.00 sec)
-- 展示计数器重置之后一共查询了多少次。
mysql> show status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 2     |
+---------------+-------+
1 row in set (0.00 sec)
-- 使用线程情况
mysql> show status like 'Thread_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 8     |
| Threads_connected | 1     |
| Threads_created   | 9     |
| Threads_running   | 2     |
+-------------------+-------+
4 rows in set (0.01 sec)

然后执行查询表和索引操作限定

mysql> show status where variable_name like 'Handler%' or variable_name like 'Created%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Created_tmp_disk_tables    | 0     |
| Created_tmp_files          | 0     |
| Created_tmp_tables         | 0     |
| Handler_commit             | 2     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 22    |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 3     |
| Handler_read_key           | 15    |
| Handler_read_last          | 0     |
| Handler_read_next          | 5     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 10010 |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
21 rows in set (0.00 sec)
  • Created_tmp_tables:临时表个数
  • Handler_commit:提交次数
  • Handler_external_lock:表锁定次数
  • Handler_read_key:根据索引的查询行数,如果较高,说明查询和表的索引正确。
  • Handler_read_rnd_next:下一行请求数量
  • Handler_mrr_init:服务器使用存储引擎自己实现的多范围读取的次数。
  • Handler_prepare:用于两阶段提交操作的准备阶段的计数器。
  • Handler_read_first:索引中第一条被读的次数。如果较高,它建议服务器正执行大量全索引扫描;

参考文档

《高性能MySQL》