MySQL 调整优化措施

1. 关闭不必要的二进制日志和慢查询日志，仅在内存足够或开发调试时打开它们。使用下面的语句查看查询是否打开。

 

show variables like '%slow%';

 

还可以使用下面的语句查看慢查询的条数，定期打开方便优化。

 

show global status like '$slow%';

 

但是慢查询也会带来一些 CPU 损耗。建议间断性打开慢查询日志来定位性能瓶颈。

 

2. 适度使用 Query Cache。

 

show global variables like '%query_cache%';

 

通过检查 have_query_cache 变量来确定 MySQL 安装程序中的 Query Cache 是否配置和是否可用。

 

MySQL 的Query Cache 用来缓存和 Query 相关的数据。具体来说，Query Cache 缓存客户端提交给MySQL 的 SELECT 语句以及该语句的结果集。就是将  SELECT 语句和语句的结果做 Hash 映射关系后保存在一定的内存区域中。

 

可以通过检查多个状态变量来观察 Query Cache 的性能

 

mysql>show status like '%Qcache%';

 

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

| Variable_name           | Value   |

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

| Qcache_free_blocks      | 1       |

| Qcache_free_memory      | 1031448 |

| Qcache_hits             | 0       |

| Qcache_inserts          | 0       |

| Qcache_lowmem_prunes    | 0       |

| Qcache_not_cached       | 56      |

| Qcache_queries_in_cache | 0       |

| Qcache_total_blocks     | 1       |

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

8 rows in set (0.00 sec)

 

• Qcache_free_blocks： 表示query cache中目前还有多杀剩余的blocks，如果该值显示较大，则说明query cache中的内存碎片较多，需要进行整理了；
• Qcache_free_memory： 表示query cache目前剩余的内存大小；
• Qcache_hits： 表示query cache有多少次命中；
• Qcache_inserts： 表示未命中cache后将结果集再写入到cache中的次数；
• Qcache_lowmem_prunes： 表示多少条query因为内存不足而被清除出 query_cache 以给新的 cache 对象使用的次数；
• Qcache_not_cached： 表示因为query_cache_type的设置或者不能被cache的query的数量；
• Qcache_queries_in_cache： 表示当前cache的query的数量；
• Qcache_total_blocks： Query Cache 中的 block 数量。

但是，查询缓存有一个需要注意的问题，那就是缓存过期策略， MySQL 采用的机制是，当一个数据表有更新操作（比如 update 或者 insert）后，那么涉及这个表的所有查询缓存都会失效。

 

3. 增加 MySQL 允许的最大连接数。可用下面的语句查看 MySQL 允许的最大连接数。

 

show variables like 'max_connections';

 

4. 对于 MyISAM 表适当增加 key_buffer_size。当然这需要根据 key_cache 的命中率进行计算，例如：

 

show global status like 'key_read%';

key_cache_miss_rate = Key_reads / Key_read_requests * 100%;

 

当 key_cache_miss_rate 值大于 1% 时就需要适当增加 key_buffer_size 了。

 

那么为它分配多大的空间合适呢？

 

MyISAM 类型的表只缓存索引，不缓存数据，也就是说只缓存 MYI 文件，不缓存 MYD 文件。那么，如果计算出所有 MYI 文件的大小，自然就是当前索引缓存区需要的空间大小了。总之，如果你在大量使用 MyISAM 类型的表，给 key_buffer 预设一个较大的空间，并监控索引缓存的空间使用率是非常重要的。

 

对于 MyISAM，还需要注意 table_cache 的设置。当 table_cache 设置过小，MySQL 就会反复打开、关闭 FRM 文件，造成一定的性能损失；如果 table_cache 设置过大，MySQL 将会消耗很多 CPU 资源去处理 table_cache 的算法。因此 table_cache  值一定要设置合理，可以参考 opened_tables 参数的值，如果这个值一直增长，就需要适当增加 table_cache 值。

 

对于 InnoDB，需要重点注意 innodb_buffer_pool_size 参数，如果你在 MySQL 中大量使用 Innodb 类型表，则可以将缓冲池大小设置为物理内存的 80%，并持续关注它的使用率。

 

另外，通过设置 innodb_flush_method 选项：

 

innodb_flush_method = O_DIRECT

 

InnoDB 将可以跳过文件系统缓冲区，提高 I/O 性能，同时凭借自身的缓冲池更加高效地工作。

 

某些情况下，可以通过调节 innodb_thread_concurrency 选项提高线程性能。该参数默认值是 0 ，一般情况下，该值是足够的。但如果在多处理器及多个独立磁盘的服务器上运行 MySQL（并频繁使用 InnoDB），那么将此值设置为处理器个数加上独立磁盘数的和，系统性能会提高，确保 InnoDB 使用足够的线程最大化并发操作。

 

5. 从表中删除大量行后，可运行 OPTIMIZE TABLE TableName 进行碎片整理。

 

6. 使用数据库连接池技术。MySQL 采用多线程来处理并发的连接，使用数据库连接池，让连接进行排队和复用，一定程度上可以缓解高并发下的连接压力。我们可以在 my.cnf 中设置以下选项：

 

thread_cache_size = 100

 

这使得 MySQL 可以缓存 100 个线程。

 

7. 临时表优化。

 

我们看到一些查询在分析时出现 Using temporay 的状态，这意味着查询过程中需要创建临时表来储存中间数据，我们需要通过合理的索引来避免它。另一方面，当临时表在所难免时，我们也要尽量减少临时表本身的开销。通过以下命令可以查看临时表统计信息

 

SHOW GLOBAL STATUS LIKE '%tmp%'

 

MySQL 可以将临时表创建在磁盘（Disk table）、内存（Table）以及临时文件（File）中，显然，在磁盘上创建临时表的开销最大，所以我们希望 MySQL 尽量不要在磁盘上创建临时表。

 

在 MySQL 的配置中，可以通过 tmp_table_size 选项来设置用于储存临时表的内存空间大小，一旦这个空间不够用，MySQL 将会启用磁盘来保存临时表。