(转)根据mysql的status信息优化mysql
1, 查看MySQL服务器配置信息
mysql> show variables;
2, 查看MySQL服务器运行的各种状态值
mysql> show global status;
3, 慢查询
mysql> show variables like '%slow%'; 
+------------------+-------+ 
| Variable_name    | Value | 
+------------------+-------+ 
| log_slow_queries | OFF   | 
| slow_launch_time | 2     | 
+------------------+-------+ 
mysql> show global status like '%slow%'; 
+---------------------+-------+ 
| Variable_name       | Value | 
+---------------------+-------+ 
| Slow_launch_threads | 0     | 
| Slow_queries        | 279   | 
+---------------------+-------+
mysql> show variables like '%slow%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| log_slow_queries | OFF   |
| slow_launch_time | 2     |
+------------------+-------+
mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 279   |
+---------------------+-------+
配置中关闭了记录慢查询（最好是打开，方便优化），超过2秒即为慢查询，一共有279条慢查询
4, 连接数

mysql> show variables like 'max_connections'; 
+-----------------+-------+ 
| Variable_name   | Value | 
+-----------------+-------+ 
| max_connections | 500   | 
+-----------------+-------+ 

mysql> show global status like 'max_used_connections'; 
+----------------------+-------+ 
| Variable_name        | Value | 
+----------------------+-------+ 
| Max_used_connections | 498   | 
+----------------------+-------+
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+
mysql> show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 498   |
+----------------------+-------+
设置的最大连接数是500，而响应的连接数是498
max_used_connections / max_connections * 100% = 99.6% （理想值 ≈ 85%）
5, key_buffer_size
key_buffer_size是对MyISAM表性能影响最大的一个参数, 不过数据库中多为Innodb

mysql> show variables like 'key_buffer_size'; 
+-----------------+----------+ 
| Variable_name   | Value    | 
+-----------------+----------+ 
| key_buffer_size | 67108864 | 
+-----------------+----------+ 

mysql> show global status like 'key_read%'; 
+-------------------+----------+ 
| Variable_name     | Value    | 
+-------------------+----------+ 
| Key_read_requests | 25629497 | 
| Key_reads         | 66071    | 
+-------------------+----------+
mysql> show variables like 'key_buffer_size';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 67108864 |
+-----------------+----------+
mysql> show global status like