mysql系统变量

1、Profiling 分析具体的sql语句消耗
MySQL5.0.37版本以上支持了Profiling – 官方手册。 此工具可用来查询 SQL 会执行多少时间， 并看出 CPU/Memory 使用量，执行过程中 System lock和Table lock 花多少时间等等。从启动profile之后的所有查询包括错误的语句都会记录。关闭会话或者set profiling=0 就关闭。这对帮助排查和分析sql语句，还是比较有用的。

mysql> set profiling=1;
  #开启
mysql> select * from li limit 2;
+--------+----------+
| id      | name  |
+--------+----------+
| 1458  | hello  |

| 1457  | world |

+--------+----------+
mysql> show profiles;
+--------------+----------------+------------------------------+
| Query_ID | Duration     | Query                            |
+--------------+----------------+------------------------------+
|        1        | 0.00013200   | SELECT DATABASE()     |

|        2        | 0.00044100   | select * from li limit 2 |
+--------------+----------------+------------------------------+
mysql> show profile for query 2;
+---------------------------+--------------+
| Status                         | Duration |
+---------------------------+--------------+
| starting                     |  0.000058  |

| Opening tables         |  0.000011  |
| System lock               |  0.000007  |
| Table lock                   |  0.000014  |
| init                             |  0.000027  |
| optimizing                |  0.000003  |
| statistics                   |  0.000010  |
| preparing                  |  0.000010  |
| executing                  |  0.000007  |
| Sending data            |  0.000068  |
| end                            |  0.000003  |
| query end                 |  0.000002  |
| freeing items           |  0.000208  |
| logging slow query |  0.000002  |
| logging slow query |  0.000009  |
| cleaning up              |  0.000002  |
+---------------------------+--------------+
#此外还有2个语句可以查看更多信息。
show profile cpu for query 2;
show profile IPC for query 2;