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;