mysql查询单条_剖析单条查询

## 使用 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