了解MySQL系统状态

关注系统状态：

mysql> show processlist; 这个命令用来获取当前所有连接的线程列表，将每一个连接的线程，作为一条独立的记录输出

mysql> show full processlist;

(system@localhost) [(none)]> show full processlist;

+----+--------+-----------+------+---------+------+-------+-----------------------+

| Id | User   | Host      | db   | Command | Time | State | Info                  |

+----+--------+-----------+------+---------+------+-------+-----------------------+

|  2 | system | localhost | NULL | Query   |    0 | init  | show full processlist |

要清除2号线程，执行KILL命令

mysql> kill 2;

 

作为DBA有必要了解SQL语句在执行时具体做了什么，以及所操作的各项开销，因为这些信息对于我们后续的性能优化至关重要

 

SHOW PROFILES命令显示最近执行过的语句(以及语句执行的时间开销)。资源统计是由一个名为profiling的状态变量控制。

(system@localhost) [(none)]> show variables like '%profiling%';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| have_profiling         | YES   |

| profiling              | OFF   |

| profiling_history_size | 15    |

+------------------------+-------+

3 rows in set (0.00 sec)

SHOW PROFILE命令用于显示单个语句执行时的详细资源信息。

profiling默认是禁用状态，首先启用它，只针对当前会话设置

(system@localhost) [(none)]> set profiling=1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

(system@localhost) [(none)]> select user,host from mysql.user;

+--------+-------------+

| user   | host        |

+--------+-------------+

| rep1   | 192.168.1.% |

| system | localhost   |

| xtrabk | localhost   |

+--------+-------------+

3 rows in set (0.00 sec)

(system@localhost) [(none)]> show profiles;

+----------+------------+----------------------------------+

| Query_ID | Duration   | Query                            |

+----------+------------+----------------------------------+

|        1 | 0.00094775 | select user,host from mysql.user |

+----------+------------+----------------------------------+

1 row in set, 1 warning (0.00 sec)

(system@localhost) [(none)]> show profile;

+----------------------+----------+

| Status               | Duration |

+----------------------+----------+

| starting             | 0.000228 |

| checking permissions | 0.000016 |

| Opening tables       | 0.000083 |

| init                 | 0.000039 |

| System lock          | 0.000029 |

| optimizing           | 0.000009 |

| statistics           | 0.000028 |

| preparing            | 0.000021 |

| executing            | 0.000005 |

| Sending data         | 0.000399 |

| end                  | 0.000011 |

| query end            | 0.000006 |

| closing tables       | 0.000026 |

| freeing items        | 0.000023 |

| cleaning up          | 0.000025 |

+----------------------+----------+

15 rows in set, 1 warning (0.00 sec)

mysql>show profile cpu,source;

mysql>show profile all;

mysql>show profile for query 2;

profiliing收集和分析语句执行时的资源开销，本身也会带来相应的资源开销，对于性能有一定的影响