这是我参与8月更文挑战的第21天,活动详情查看:8月更文挑战
前言: 本篇文章 是我关于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》