sql性能分析器

查询是否开启性能分析器
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)


开启性能分析器
mysql> set global profiling=1;
Query OK, 0 rows affected (0.00 sec)

查询你之前查询过的语句
mysql> show profiles;
+----------+------------+--------------------------------------------+
| Query_ID | Duration   | Query                                      |
+----------+------------+--------------------------------------------+
|        1 | 0.00010550 | select @@version_comment limit 1           |
|        2 | 0.00009900 | select @@profiling                         |
|        3 | 0.00038875 | select User,Host,Password from mysql.mysql |
|        4 | 0.00065050 | select User,Host,Password from mysql.user  |
|        5 | 0.00008325 | select @@profiling                         |
|        6 | 0.00008600 | set global profiling=1                     |
+----------+------------+--------------------------------------------+

分析你之前查询过的语句
mysql> show profile for query 6;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000037 |
| Opening tables     | 0.000008 |
| query end          | 0.000002 |
| closing tables     | 0.000002 |
| freeing items      | 0.000036 |
| logging slow query | 0.000001 |
| cleaning up        | 0.000001 |
+--------------------+----------+
7 rows in set (0.00 sec)

SQL 性能分析器可以帮助我们对一些比较难以确定性能问题的 SQL 进行诊断，找出问题根源。
用explain看看是否用到索引

mysql> explain select User,Host,Password from mysql.user\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra:
1 row in set (0.00 sec)
mysql>