## 使用 SHOW ROFILE
当一条查询提交给服务器时,此工具会记录剖析信息到一张临时表,并且给查询赋予一个从1开始的整数标识符。
先查询是否已经开启 profile:
```
mysql> show variables like 'profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
2 rows in set (0.00 sec)
```
需要把 profiling 设为1 来开启 SQL性能剖析,profiling_history_size 设置是SQL性能剖析缓存的记录数。
```
mysql> show profiles;
+----------+------------+----------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------+
| 1 | 0.00178300 | show variables like 'profiling' |
| 2 | 0.00167800 | show variables like 'profiling%' |
| 3 | 0.00037900 | show databases |
| 4 | 0.00013975 | SELECT DATABASE() |
| 5 | 0.00031675 | show databases |
| 6 | 0.00015525 | show tables |
| 7 | 0.00035500 | show tables |
| 8 | 0.00030600 | select * from article |
| 9 | 0.00006550 | shwo profiles |
+----------+------------+----------------------------------+
9 rows in set, 1 warning (0.00 sec)
```
```
mysql> show profile for query 8;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000078 |
| checking permissions | 0.000012 |
| Opening tables | 0.000021 |
| init | 0.000025 |
| System lock | 0.000010 |
| optimizing | 0.000006 |
| statistics | 0.000017 |
| preparing | 0.000015 |
| executing | 0.000003 |
| Sending data | 0.000064 |
| end | 0.000005 |
| query end | 0.000008 |
| closing tables | 0.000009 |
| freeing items | 0.000021 |
| cleaning up | 0.000013 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)
```
可以通过查看耗时时间来定位查询慢的主要原因,进而进行选择性优化。
## 使用 SHOW STATUS
mysql的 SHOW STATUS 命令返回了一些计数器。既有服务器级别的全局计数器,也有基于某个连接的会话级别计数器。如果执行 SHOW GLOBAL STATUS,则可以查看服务器级别的从服务器启动时开始计算的查询次数统计。
执行 FLUSH STATUS ,可以将会话级别的计数器重置为0。
```
mysql> flush status;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from member as m inner join article as a on m.id=a.mid;
#这里的查询结果省略
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_external_lock | 4 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 2 |
| Handler_read_key | 2 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 8 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
21 rows in set (0.00 sec)
```
SHOW STATUS 输出的信息解释参考:https://www.cnblogs.com/zuxing/articles/7761262.html