在平时被问及最多的问题就是关于 MySQL 数据库性能优化方面的问题，因此最近打算写一个MySQL数据库性能优化方面的系列文章，但愿对初中级 MySQL DBA 以及其余对 MySQL 性能优化感兴趣的朋友们有所帮助。mysql
这是 MySQL数据库性能优化专题 系列的第一篇文章：MySQL 数据库性能优化之缓存参数优化linux
数据库属于 IO 密集型的应用程序，其主要职责就是数据的管理及存储工做。而咱们知道，从内存中读取一个数据库的时间是微秒级别，而从一块普通硬盘上读取一个IO是在毫秒级别，两者相差3个数量级。因此，要优化数据库，首先第一步须要优化的就是 IO，尽量将磁盘IO转化为内存IO。本文先从 MySQL 数据库IO相关参数（缓存参数）的角度来看看能够经过哪些参数进行IO优化：算法
Query cache 做用于整个 MySQL Instance，主要用来缓存 MySQL 中的 ResultSet，也就是一条SQL语句执行的结果集，因此仅仅只能针对select语句。当咱们打开了 Query Cache 功能，MySQL在接受到一条select语句的请求后，若是该语句知足Query Cache的要求（未显式说明不容许使用Query Cache，或者已经显式申明须要使用Query Cache），MySQL 会直接根据预先设定好的HASH算法将接受到的select语句以字符串方式进行hash，而后到Query Cache 中直接查找是否已经缓存。也就是说，若是已经在缓存中，该select请求就会直接将数据返回，从而省略了后面全部的步骤（如 SQL语句的解析，优化器优化以及向存储引擎请求数据等），极大的提升性能。sql
固然，Query Cache 也有一个致命的缺陷，那就是当某个表的数据有任何任何变化，都会致使全部引用了该表的select语句在Query Cache 中的缓存数据失效。因此，当咱们的数据变化很是频繁的状况下，使用Query Cache 可能会得不偿失。数据库
Query Cache的使用须要多个参数配合，其中最为关键的是 query_cache_size 和 query_cache_type ，前者设置用于缓存 ResultSet 的内存大小，后者设置在何场景下使用 Query Cache。在以往的经验来看，若是不是用来缓存基本不变的数据的MySQL数据库，query_cache_size 通常 256MB 是一个比较合适的大小。固然，这能够经过计算Query Cache的命中率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）来进行调整。query_cache_type能够设置为0(OFF)，1(ON)或者2(DEMOND)，分别表示彻底不使用query cache，除显式要求不使用query cache（使用sql_no_cache）以外的全部的select都使用query cache，只有显示要求才使用query cache（使用sql_cache）。缓存
Binlog Cache 用于在打开了二进制日志（binlog）记录功能的环境，是 MySQL 用来提升binlog的记录效率而设计的一个用于短期内临时缓存binlog数据的内存区域。性能优化
通常来讲，若是咱们的数据库中没有什么大事务，写入也不是特别频繁，2MB～4MB是一个合适的选择。可是若是咱们的数据库大事务较多，写入量比较大，可与适当调高binlog_cache_size。同时，咱们能够经过binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够，是否有大量的binlog_cache因为内存大小不够而使用临时文件（binlog_cache_disk_use）来缓存了。数据结构
Key Buffer 多是你们最为熟悉的一个 MySQL 缓存参数了，尤为是在 MySQL 没有更换默认存储引擎的时候，不少朋友可能会发现，默认的 MySQL 配置文件中设置最大的一个内存参数就是这个参数了。key_buffer_size 参数用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。若是咱们有足够的内存，这个缓存区域最好是可以存放下咱们全部的 MyISAM 引擎表的全部索引，以尽量提升性能。架构
此外，当咱们在使用MyISAM 存储的时候有一个及其重要的点须要注意，因为 MyISAM 引擎的特性限制了他仅仅只会缓存索引块到内存中，而不会缓存表数据库块。因此，咱们的 SQL 必定要尽量让过滤条件都在索引中，以便让缓存帮助咱们提升查询效率。性能
和key_buffer_size同样，这个参数一样也仅做用于使用 MyISAM存储引擎，用来缓存批量插入数据的时候临时缓存写入数据。当咱们使用以下几种数据写入语句的时候，会使用这个内存区域来缓存批量结构的数据以帮助批量写入数据文件：
insert … select …
insert … values (…) ,(…),(…)…
load data infile… into… (非空表)
当咱们使用InnoDB存储引擎的时候，innodb_buffer_pool_size 参数多是影响咱们性能的最为关键的一个参数了，他用来设置用于缓存 InnoDB 索引及数据块的内存区域大小，相似于 MyISAM 存储引擎的 key_buffer_size 参数，固然，可能更像是 Oracle 的 db_cache_size。简单来讲，当咱们操做一个 InnoDB 表的时候，返回的全部数据或者去数据过程当中用到的任何一个索引块，都会在这个内存区域中走一遭。
和key_buffer_size 对于 MyISAM 引擎同样，innodb_buffer_pool_size 设置了 InnoDB 存储引擎需求最大的一块内存区域的大小，直接关系到 InnoDB存储引擎的性能，因此若是咱们有足够的内存，尽可将该参数设置到足够打，将尽量多的 InnoDB 的索引及数据都放入到该缓存区域中，直至所有。
咱们能够经过 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率，并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化。
这个参数咱们平时调整的可能不是太多，不少人都使用了默认值，可能不少人都不是太熟悉这个参数的做用。innodb_additional_mem_pool_size 设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，因此当咱们一个MySQL Instance中的数据库对象很是多的时候，是须要适当调整该参数的大小以确保全部数据都能存放在内存中提升访问效率的。
这个参数大小是否足够仍是比较容易知道的，由于当太小的时候，MySQL 会记录 Warning 信息到数据库的 error log 中，这时候你就知道该调整这个参数大小了。
这是 InnoDB 存储引擎的事务日志所使用的缓冲区。相似于 Binlog Buffer，InnoDB 在写事务日志的时候，为了提升性能，也是先将信息写入 Innofb Log Buffer 中，当知足 innodb_flush_log_trx_commit 参数所设置的相应条件（或者日志缓冲区写满）以后，才会将日志写到文件（或者同步到磁盘）中。能够经过 innodb_log_buffer_size 参数设置其可使用的最大内存空间。
注：innodb_flush_log_trx_commit 参数对 InnoDB Log 的写入性能有很是关键的影响。该参数能够设置为0，1，2，解释以下：
0：log buffer中的数据将以每秒一次的频率写入到log file中，且同时会进行文件系统到磁盘的同步操做，可是每一个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操做；
1：在每次事务提交的时候将log buffer 中的数据都会写入到log file，同时也会触发文件系统到磁盘的同步；
2：事务提交会触发log buffer 到log file的刷新，但并不会触发磁盘文件系统到磁盘的同步。此外，每秒会有一次文件系统到磁盘同步操做。
此外，MySQL文档中还提到，这几种设置中的每秒同步一次的机制，可能并不会彻底确保很是准确的每秒就必定会发生同步，还取决于进程调度的问题。实际上，InnoDB 可否真正知足此参数所设置值表明的意义正常 Recovery 仍是受到了不一样 OS 下文件系统以及磁盘自己的限制，可能有些时候在并无真正完成磁盘同步的状况下也会告诉 mysqld 已经完成了磁盘同步。
这个参数和上面的各个参数不一样，他不是用来设置用于缓存某种数据的内存大小的一个参数，而是用来控制在 InnoDB Buffer Pool 中能够不用写入数据文件中的Dirty Page 的比例（已经被修但尚未从内存中写入到数据文件的脏数据）。这个比例值越大，从内存到磁盘的写入操做就会相对减小，因此可以必定程度下减小写入操做的磁盘IO。
可是，若是这个比例值过大，当数据库 Crash 以后重启的时间可能就会很长，由于会有大量的事务数据须要从日志文件恢复出来写入数据文件中。同时，过大的比例值同时可能也会形成在达到比例设定上限后的 flush 操做“过猛”而致使性能波动很大。
注：以上取值范围仅仅只是个人根据以往遇到的数据库场景所获得的一些优化经验值，并不必定适用于全部场景，因此在实际优化过程当中还须要你们本身不断的调整分析，也欢迎你们随时经过 Mail 与我联系沟通交流优化或者是架构方面的技术，一块儿探讨相互学习。
原文：http://isky000.com/database/mysql-perfornamce-tuning-cache-parameter