MySQL性能优化

1.列出MySQL配置

mysql> show variables;

mysql> show global status;

2.慢查询

mysql> show variables like '%slow%';
+---------------------------+----------------------------------------+
| Variable_name             | Value                                  |
+---------------------------+----------------------------------------+
| log_slow_admin_statements | OFF                                    |
| log_slow_extra            | OFF                                    |
| log_slow_slave_statements | OFF                                    |
| slow_launch_time          | 2                                      |
| slow_query_log            | OFF                                    |
| slow_query_log_file       | /usr/local/mysql/data/sdst112-slow.log |
+---------------------------+----------------------------------------+

  mysql> show global status like '%slow%';
  +---------------------+-------+
  | Variable_name | Value |
  +---------------------+-------+
  | Slow_launch_threads | 0 |
  | Slow_queries | 0 |
  +---------------------+-------+

当前配置文件中是没有开启慢查询，slow_query_log | OFF  所以系统中记录慢查询为0，当然打开慢查询会对性能有一定影响，如果是做了主从，可以在从库中开启慢查询，这样对性能的影响就不是很大。

3.MySQL连接数

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

可以看到当前设置的最大连接数是1000，默认MySQL的连接数是100，MySQL服务器过去的最大连接数是245，没有达到服务器连接数上限256，应该没有出现1040错误，比较理想的设置是
Max_used_connections / max_connections * 100% ≈ 85%
最大连接数占上限连接数的85％左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了。

4.Key_buffer_size

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

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads /key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%'获得）。
key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。
对于1G内存的机器，如果不使用MyISAM表，推荐值是16M（8-64M）

mysql> show global status like 'key_blocks_u%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_blocks_unused | 6698  |
| Key_blocks_used   | 0     |
+-------------------+-------+

Key_blocks_unused表示未使用的缓存簇(blocks)数，Key_blocks_used表示曾经用到的最大的blocks数，比如这台服务器，所有的缓存都用到了，要么增加key_buffer_size，要么就是过渡索引了，把缓存占满了。比较理想的设置：
Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

5.临时表

mysql> show global status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 21    |
+-------------------------+-------+

每次创建临时表，Created_tmp_tables增加，如果是在磁盘上创建临时表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数，比较理想的配置是：
Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%比如上面的服务器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝ 1.20%，应该相当好了。我们再看一下MySQL服务器对临时表的配置：

mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');

+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
| tmp_table_size      | 16777216 |
+---------------------+----------+

可以看到超过160M的都会放到硬盘，但是这里全部都用完了