1.MySQL使用show status返回计数器信息,既有服务器级别的全局计数器,也有基于某个连接的会话级别的计数器信息。
2.show status并不是一款分析工具,它可以显示某些活动例如读索引的频繁程度,但无法给出消耗时间信息。
3.其中对于我们优化SQL来说,最有用的计数器包括句柄计数器,临时文件和表计数器等。下面的例子展示了如何将会话级别的计数器重置为0,然后执行SQL查询,再检查计数器的结果。
一,计数器重置:
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
二,执行查询:
mysql> select * from sc where score = 70; +------+------+-------+
| s_no | c_no | score |
+------+------+-------+
| s_01 | c_01 | 70 |
+------+------+-------+
1 row in set (0.00 sec)
三,查看show status:
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 | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 15 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
19 rows in set (0.00 sec)