mysql参数优化

简介: mysql主要是用来存储数据的，涉及到了磁盘I/O，那么mysql的应能瓶颈就在I/O读写上。mysql是有缓存Buffer的，但也并非是缓存内存设置的越大越好,太大就会产生SWAP页交换。从而影响性能。

1、sql请求过程

基于上面的图片我们可以发现，在执行sql查询的时候，我们首先发现有一个query cache,他缓存的是sql相关的结果集。这里的缓存以我们sql语句的hash值作为key,返回结果集的value。

2、查询缓存

一条查询语句过来的时候，先会去缓存中查询这个sql语句是否有缓存 query cache 所以这个缓存的大小是可控制的我们查看相关参数：

SHOW STATUS LIKE 'Qcache%' 、、
SHOW VARIABLES LIKE 'query%'

相关参数：

(query_cache_size - Qcache_free_memory）/  Qcache_queries_in_cache

参数都可以进行查询出来。query cache虽然可以提高查询的效率但是这仅仅局限与我们不常修改的数据，如果我们的数据经常进行修改。缓存就要不断的进行失效，这样的话就给系统带来了额外的开销。

3、Innodb存储引擎参数调优

InnoDB Buffer Pool（简称 IBP）是 InnoDB 存储引擎的一个缓冲池。和MyISAM不同我们IBP即存储索引块，还存储数据。这样的话可以快速访问文件，而不需要访问磁盘文件。很明显该空间越大我们就会降低访问磁盘的频率，就可以获得更快的相应时间。

1、innodb_buffer_pool_size

IBP 默认的内存大小是 128M，我们可以通过参数 innodb_buffer_pool_size 来设置 IBP 的大小，IBP 设置得越大，InnoDB 表性能就越好。但是，将 IBP 大小设置得过大也不好，可能会导致系统发生 SWAP 页交换。所以我们需要在 IBP 大小和其它系统服务所需内存大小之间取得平衡。MySQL 推荐配置 IBP 的大小为服务器物理内存的 80%。我们也可以通过计算 InnoDB 缓冲池的命中率来调整 IBP 大小：

(1innodb_buffer_pool_reads/innodb_buffer_pool_read_request)*100

但如果我们将 IBP 的大小设置为物理内存的 80% 以后，发现命中率还是很低，此时我们就应该考虑扩充内存来增加 IBP 的大小。

2、innodb_buffer_pool_instances
innodb中IBP被分为了多个实例，对于IBP设置很大的系统中，我们将缓存池划分为多个实例可以减少不同线程读取和写入缓存页面时的争用，从而提高系统的并发性。该参数在IBP设置的大小为1G或者更大的时候才会生效。

为了获取最佳的性能，建议指定innodb_buffer_pool_instances的大小，保证我们为了实例至少有1G的内存，innodb_buffer_pool_size为我们实例的整数倍

3、innodb_read_io_threads / innodb_write_io_threads

MySQL 后台线程包括了主线程、IO 线程、锁线程以及监控线程等，其中读写线程属于 IO 线程，主要负责数据库的读取和写入操作，这些线程分别读取和写入 innodb_buffer_pool_instances 创建的各个内存页面。MySQL 支持配置多个读写线程，即通过 innodb_read_io_threads 和 innodb_write_io_threads 设置读写线程数量。

mysql的读写线程默认为4个，我们可以根据我们的cpu进行合理的设置，在分情况的情况下设置其大小。我们根据我们数据库的读写情况，可以参考参数：

SHOW GLOBAL STATUS LIKE 'Com_select';//读取数量

SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_insert', 'Com_update', 'Com_replace', 'Com_delete');//写入数量

更具我们的读写情况设置其大小。

4、innodb_log_file_size
InnoDB 中有一个 redo log 文件，InnoDB 用它来存储服务器处理的每个写请求的重做活动。执行的每个写入查询都会在日志文件中获得重做条目，以便在发生崩溃时可以恢复更改。当日志文件大小已经超过我们参数设置的日志文件大小时，InnoDB 会自动切换到另外一个日志文件，由于重做日志是一个循环使用的环，在切换时，就需要将新的日志文件脏页的缓存数据刷新到磁盘中（触发检查点）。理论上来说，innodb_log_file_size 设置得越大，缓冲池中需要的检查点刷新活动就越少，从而节省磁盘 I/O。那是不是将这个日志文件设置得越大越好呢？如果日志文件设置得太大，恢复时间就会变长，这样不便于 DBA 管理。在大多数情况下，我们将日志文件大小设置为 1GB 就足够了。

5、innodb_log_buffer_size

这个参数决定了 InnoDB 重做日志缓冲池的大小，默认值为 8MB。如果高并发中存在大量的事务，该值设置得太小，就会增加写入磁盘的 I/O 操作。我们可以通过增大该参数来减少写入磁盘操作，从而提高并发时的事务性能。

6、innodb_flush_log_at_trx_commit

该参数控制这我们buffer中的日志文件什么时候刷回磁盘，但是这中间还要经过我们的操作系统os buffer。Linux下该参数的值默认为1.
0： 数据库每隔一秒刷新到我们的os,os实时刷新到磁盘。
1： 数据实时刷新到os,os实时刷新到磁盘。
2： 数据实时刷新到os,os根据设置1S将我们的数据刷新到磁盘。

我们可以查看一个性能对比：

总结： mysql的参数设置非常的多，这里只是简单的介绍了内存有关的一些，下面是一个并发可能会用的到的参数