MySQL 优化原理（三）
聊聊 MySQL 配置。
大多数开发者可能不太会关注 MySQL 的配置，毕竟在基本配置没有问题的情况下，把更多的精力放在 schema 设计、索引优化和 SQL 优化上，是非常务实的策略。这时，如果再花力气去优化配置项，获得的收益通常都比较小。更多的时候，基于安全因素的考量，普通开发者很少能够接触到生产环境的 MySQL 配置。正是这样，导致开发者（包括我）对 MySQL 的配置不甚了解，希望本文能帮你更好的了解 MySQL 配置。
如果让你在某种环境上安装配置 MySQL，你会怎么做？安装后，直接 copy 修改示例配置文件，应该是大多数人的做法。但强烈建议不要怎么做，首先，示例配置文件有非常多注释掉的配置项，它可能会诱使你打开一个你并不了解的配置，而且这些注释还不一定准确。其次，MySQL 的一些配置对于现代化的硬件和工作负载来说，有点过时了。
MySQL 有非常多的配置项可以修改，但大多数情况下，你都不应该随便修改它，因为错误或者没用的配置导致的潜在风险非常大，而且还很难定位问题。确保基本配置正确，然后小心诊断问题，确认问题恰好可以通过某个配置项解决，紧接着再修改这个配置吧。
其实，创建一个好的配置，最快方法不是从学习配置项开始，也不是问哪个配置项应该怎么设置或者怎么修改开始，更不是从检查服务器行为和询问哪个配置项可以提升性能开始。最好是从理解 MySQL 内核和行为开始，然后利用这些知识来指导你配置 MySQL。
就从理解 MySQL 配置的工作原理开始吧。
MySQL 配置的工作原理
MySQL 从哪儿获得配置信息：命令行参数和配置文件。类 Unix 系统中，配置文件一般位于 /etc/my.cnf 或者 /etc/mysql/my.cnf。在启动时，可以通过命令行参数指定配置文件的位置，当然命令行中也可以指定其它参数，服务器会读取配置文件的内容，删除所有注释和换行，然后和命令行选项一起处理。
任何打算长期使用的配置项都应该写入配置文件，而不是在命令行中指定。一定要清楚的知道 MySQL 使用的配置文件位置，在修改时不能想当然，比如，修改了 /etc/my.cnf 的配置项，但 MySQL 实际并未使用这个配置文件。如果你不知道当前使用的配置文件路径，可以尝试：
1 | |
一个典型的配置文件包含多个部分，每个部分的开头是一个方括号括起来的分段名称。MySQL 程序通常读取跟它同名的分段部分，比如，许多客户端程序读取[client]部分。服务器通常读取[mysqld]这一段，一定要确认配置项放在了文件正确的分段中，否则配置是不会生效的。
MySQL 每一个配置项均使用小写，单词之间用下划线或者横线隔开，虽然我们常用的分隔符是下划线，但如果在命令行或者配置文件中见到如下配置，你要知道，它们其实是等价的：
1 | |
配置项可以有多个作用域：全局作用域、会话作用域 (每个连接作用不同)、对象作用域。很多会话级配置项跟全局配置相等，可以认为是默认值，如果改变会话级配置项，它只影响改动的当前连接，当连接关闭时，所有的参数变更都会失效。下面有几个示例配置项：
- query-cache-size 全局配置项
- sort-buffer-size 默认全局相同，但每个线程里也可以设置
- join-buffer-size 默认全局，且每个线程也可以设置。但若一个查询中关联多张表，可以为每个关联分配一个关联缓存 (
join-buffer)，所以一个查询可能有多个关联缓冲。
配置文件中的变量 (配置项) 有很多 (但不是所有) 可以在服务器运行时修改，MySQL 把这些归为动态配置变量：
1 | |
动态的设置变量，MySQL 关闭时这些变量都会失效。如果在服务器运行时修改了变量的全局值，这个值对当前会话和其他任何已经存在的会话都不起效果，这是因为会话的变量值是在连接创建时从全局值初始化而来的。注意，在配置修改后，需要确认是否修改成功。
你可能注意到，上面的示例中，有些使用 “=”，有些使用 “:=”。对于 set 命令本身来说，两种赋值运算符没有任何区别，在命令行中使用任一运算符符，均可以生效。而在其他语句中，赋值运算符必须是 “:=”，因为在非 set 语句中 “=” 被视为比较运算符。具体可以参考如下示例：
详细示例可以参考：stackoverflow
1 | |
有一些配置使用了不同的单位，比如table-cache变量指定表可以被缓存的数量，而不是表可以被缓存的字节数。而key-buffer-size则是以字节为单位。
还有一些配置可以指定后缀单位，比如1M=1024*1024字节，但需要注意的是，这只能在配置文件或者作为命令行参数时有效。当使用 SQL 的 SET 命令时，必须使用数字值 1048576 或者 1024*1024 这样的表达式，但在配置文件中不能使用表达式。
小心翼翼的配置 MySQL
我们常常动态的修改配置，但请务必小心，因为它们可能导致数据库做大量耗时的工作，从而影响数据库的整体性能。比如从缓存中刷新脏块，不同的刷新方式对 I/O 的影响差别很大 (后文会具体说明)。最好把一些好的习惯作为规范合并到工作流程中去，就比如：
好习惯 1：不要通过配置项的名称来推断一个变量的作用
不要通过配置项的名称来推断一个变量的作用，因为它可能跟你想象的完全不一样。比如：
read-buffer-size：当 MySQL 需要顺序读取数据时，如无法使用索引，其将进行全表扫描或者全索引扫描。这时，MySQL 按照数据的存储顺序依次读取数据块，每次读取的数据块首先会暂存在缓存中，当缓存空间被写满或者全部数据读取结束后，再将缓存中的数据返回给上层调用者，以提高效率。read-rnd-buffer-size：和顺序读取相对应，当 MySQL 进行非顺序读取（随机读取）数据块的时候，会利用这个缓冲区暂存读取的数据。比如：根据索引信息读取表数据、根据排序后的结果集与表进行 Join 等等。总的来说，就是当数据块的读取需要满足一定的顺序的情况下，MySQL 就需要产生随机读取，进而使用到read-rnd-buffer-size参数所设置的内存缓冲区。
这两个配置都是在扫描 MyISAM 表时有效，且 MySQL 会为每个线程分配内存。对于前者，MySQL 只会在查询需要使用时才会为该缓存分配内存，并且一次性分配该参数指定大小的全部内存，而后者同样是需要时才分配内存，但只分配需要的内存大小而不是参数指定的数值，max-read-rnd-buffer-size(实际上没有这个配置项) 这个名字更能表达这个变量的实际含义。
好习惯 2：不要轻易在全局修改会话级别的配置
对于某些会话级别的设置，不要轻易的在全局增加它们的值，除非你确认这样做是对的。比如：sort-buffer-size，该参数控制排序操作的缓存大小，MySQL 只会在查询需要做排序操作时才会为该缓冲分配内存，一旦需要排序，就会一次性分配指定大小的内存，即使是非常小的排序操作。因此在配置文件中应该配置的小一些，然后在某些查询需要排序时，再在连接中把它调大。比如：
1 | |
好习惯 3：配置变量时，并不是值越大越好
配置变量时，并不是值越大越好，而且如果设置的值太高，可能更容易导致内存问题。在修改完成后，应该通过监控来确认变量的修改对服务器整体性能的影响。
好习惯 4：规范注释，版本控制
在配置文件中写好注释，可能会节省自己和同事大量的工作，一个更好的习惯是把配置文件置于版本控制之下。
说完了好习惯，再来说说不好的习惯。
坏习惯 1：根据一些 “比率” 来调优
一个经典的按 “比率” 调优的经验法则是，缓存的命中率应该高于某个百分比，如果命中率过低，则应该增加缓存的大小。这是非常错误的意见，大家可以仔细思考一下：缓存的命中率跟缓存大小有必然联系吗？(分母变大，值就变大了？) 除非确实是缓存太小了。关于 MyISAM 键缓冲命中率，下文会详细说明。
坏习惯 2：随便使用调优脚本
尽量不要使用调优脚本！不同的业务场景、不同的硬件环境对 MySQL 的性能要求是不一样的。比如有些业务对数据的完整性要求较高，那么就一定要保证数据不丢失，出现故障后可恢复数据，而有些业务却对数据的完整性要求没那么高，但对性能要求更高。因此，即使是同一个变量，在这两个不同场景下，其配置的值也应该是不同的。那你还能放心的使用网上找到的脚本吗 ？
本小节示例的几个配置项，仅用于举例说明，并不代表它们有多么重要，请根据实际应用场景配置它们。就比如
sort-buffer-size，你真的需要 100M 内存来缓存 10 行数据？
给你一个基本的 MySQL 配置
前面已经说到，MySQL 可配置性太强，看起来需要花很多时间在配置上，但其实大多数配置的默认值已经是最佳的，最好不要轻易改动太多的配置，你甚至不需要知道某些配置的存在。这里有一个最小的示例配置文件，可以作为服务器配置文件的一个起点，其中有一些配置项是必须的。本节将为你详细剖析每个配置有何作用？为什么要配置它？怎么确定合适的值？
1 | |
分段
MySQL 配置文件的格式为集中式，通常会分成好几部分，可以为多个程序提供配置，如[client]、[mysqld]、[mysql]等等。MySQL 程序通常是读取与它同名的分段部分。
[client]客户端默认设置内容[mysql]使用 mysql 命令登录 MySQL 数据库时的默认设置[mysqld]数据库本身的默认设置
例如服务器 mysqld 通常读取[mysqld]分段下的相关配置项。如果配置项位置不正确，该配置是不会生效的。
GENERAL
首先创建一个用户 mysql 来运行 mysqld 进程，请确保这个用户拥有操作数据目录的权限。设置默认端口为 3306，有时为了安全，可能会修改一下。默认选择 Innodb 存储引擎，在大多数情况下是最好的选择。但如果默认是 InnoDB，却需要使用 MyISAM 存储引擎，请显式地进行配置。许多用户认为其数据库使用了某种存储引擎但实际上却使用的是另外一种，就是因为默认配置的问题。
接着设置数据文件的位置，这里把 pid 文件和 socket 文件放到相同的位置，当然也可以选择其它位置，但要注意的是不要将 socket 文件和 pid 文件放到 MySQL 编译的默认位置，因为不同版本的 MySQL，这两个文件的默认路径可能会不一致，最好明确地设置这些文件的位置，以免版本升级时出现问题。
在类 UNIX 系统下本地连接 MySQL 可以采用 UNIX 域套接字方式，这种方式需要一个套接字（socket）文件，即配置中的
mysql.sock文件。当 MySQL 实例启动时，会将自己的进程 ID 写入一个文件中——该文件即为 pid 文件。该文件可由参数
pid-file控制，默认位于数据库目录下，文件名为主机名.pid。
DATA STORAGE
datadir用于配置数据文件的存储位置，没有什么好说的。
为缓存分配内存
接下来有许多涉及到缓存的配置项，缓存设置多大，最直接的因素肯定是服务器内存的大小。如果服务器只运行 MySQL，所有不需要为 OS 以及查询处理保留的内存都可以用在 MySQL 缓存。为 MySQL 缓存分配更多内存，可以有效的避免磁盘访问，提升数据库性能。大部分情况来说最为重要的缓存：
- InnoDB 缓冲池
- InnoDB 日志文件和 MyISAM 数据的操作系统缓存 (MyISAM 依赖于 OS 缓存数据)
- MyISAM 键缓存
- 查询缓存
- 无法配置的缓存，比如：bin-log 或者表定义文件的 OS 缓存
还有一些其他缓存，但它们通常不会使用太多内存。关于查询缓存，前面文章 (参考本系列的第一篇) 已有介绍，大多数情况下我们不建议开启查询缓存，因此上文的配置中query-cache-type=0表示禁用了查询缓存，相应的查询缓存大小query-cache-size=0。除开查询缓存，剩下关于 InnoDB 和 MyISAM 的相关缓存，在接下来会做详细介绍。
如果只使用单一存储引擎，配置服务器就会简单许多。如果只使用 MyISAM 表，就可以完全关闭 InnoDB，而如果只使用 InnoDB，就只需要分配最少的资源给 MyISAM（MySQL 内部系统表使用 MyISAM 引擎）。但如果是混合使用各种存储引擎，就很难在他们之间找到恰当的平衡，因此只能根据业务做一个猜测，然后在运行中观察服务器运行状况后做出调整。
MyISAM
key-buffer-size
key-buffer-size用于配置 MyISAM 键缓存大小，默认只有一个键缓存，但是可以创建多个。MyISAM 自身只缓存索引，不缓存数据 (依赖 OS 缓存数据)。如果大部分表都是 MyISAM，那么应该为键缓存设置较多的内存。但如何确定该设置多大？
假设整个数据库中表的索引大小为 X，肯定不需要把缓存设置得比 X 还大，所以当前的索引大小就成为这个配置项的重要依据。可以通过下面两种方式来查询当前索引的大小：
通过 SQL 语句查询
1
SELECT SUM(INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'MYISAM'统计索引文件的大小
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15$ du -sch `find /path/to/mysql/data/directory/ -name "*.MYI"`
比如：
root@dev-msc3:# du -sch `find /var/lib/mysql -name "*.MYI"`
72K /var/lib/mysql/static/t_global_region.MYI
40K /var/lib/mysql/mysql/db.MYI
12K /var/lib/mysql/mysql/proxies_priv.MYI
12K /var/lib/mysql/mysql/tables_priv.MYI
4.0K /var/lib/mysql/mysql/func.MYI
4.0K /var/lib/mysql/mysql/columns_priv.MYI
4.0K /var/lib/mysql/mysql/proc.MYI
4.0K /var/lib/mysql/mysql/event.MYI
4.0K /var/lib/mysql/mysql/user.MYI
4.0K /var/lib/mysql/mysql/procs_priv.MYI
4.0K /var/lib/mysql/mysql/ndb_binlog_index.MYI
164K total
你可能会问，刚创建好的数据库，根本就没什么数据，索引文件大小为 0，那如何配置键缓存大小？这时候只能根据经验值：不超过为操作系统缓存保留内存的 25%~50%。设置一个基本值，等运行一段时间后，根据运行情况来调整键缓存大小。总结来说，索引大小与 OS 缓存的 25%~50% 两者间取小者。当然还可以计算键缓存的使用情况，如果一段时间后还是没有使用完所有的键缓存，就可以把缓冲区调小一点，计算缓存区的使用率可以通过以下公式：
(key_blocks_unused * key_cache_block_size) / key_buffer_size
说明：
key_blocks_unused 的值可以通过 SHOW STATUS 获取
key_cache_block_size 的值可以通过 SHOW VARIABLES 获取
键缓存块大小是一个比较重要的值，因为它影响 MyISAM、OS 缓存以及文件系统之间的交互。如果缓存块太小，可能会碰到写时读取 (OS 在写数据之前必须先从磁盘上读取一些数据)，关于写时读取的相关知识，大家可以自行查阅。
关于缓存命中率，这里再说一点。缓存命中率有什么意义？其实这个数字没太大的作用。比如 99% 和 99.9% 之间看起来差距很小，但实际上代表了 10 倍的差距。缓存命中率的实际意义与应用也有很大关系，有些应用可以在命中率 99% 下良好的工作，有些 I/O 密集型应用，可能需要 99.99%。所以从经验上来说，每秒未命中次数这个指标实际上会更有用一些。比如每秒 5 次未命中可能不会导致 IO 繁忙，但每秒 100 次缓存未命中则可能出现问题。
MyISAM 键缓存的每秒未命中次数可以通过如下命令监控：
1 | |
最后，即使没有使用任何 MyISAM 表，依然需要将key-buffer-size设置为较小值，比如 32M，因为 MySQL 内部会使用 MyISAM 表，比如 GROUP BY 语句可能会创建 MyISAM 临时表。
myisam-recover
myisam-recover选项用于配置 MyISAM 怎样寻找和修复错误。打开这个选项会通知 MySQL 在打开表时，检查表是否损坏，并在找到问题时进行修复，它可以设置如下值：
- DEFAULT：表示不设置，会尝试修复崩溃或者未完全关闭的表，但在恢复数据时不会执行其它动作
- BACKUP：将数据文件备份到. bak 文件，以便随后进行检查
- FORCE：即使. myd 文件中丢失的数据超过 1 行，也让恢复动作继续执行
- QUICK：除非有删除块，否则跳过恢复
可以设置多个值，每个值用逗号隔开，比如配置文件中的BACKUP,FORCE会强制恢复并且创建备份，这样配置在只有一些小的 MyISAM 表时有用，因为服务器运行着一些损坏的 MyISAM 表是非常危险的，它们有时可能会导致更多数据损坏，甚至服务器崩溃。然而如果有很大的表，它会导致服务器打开所有的 MyISAM 表时都检查和修复，大表的检查和修复可能会耗费大量时间，且在这段时间里，MySQL 会阻止这个连接做其它任何操作，这显然是不切实际的。
因此，在默认使用 InnoDB 存储引擎时，数据库中只有非常小的 MyISAM 表时，只需要配置key-buffe-size于一个很小的值 (32M) 以及myisam-recover=BACKUP,FORCE。当数据库中大部分表为 MyISAM 表时，请根据上文的公式合理配置key-buffer-size，而myisam-recover则可以关闭，在启动后使用CHECK TABLES和REPAIR TABLES命令来做检查和修复，这样对服务器的影响比较小。
SAFETY
基本配置设置到位后，MySQL 已经比较安全了，这里仅仅列出两个需要注意的配置项，如果需要启用一些使服务器更安全和可靠的设置，可以参考 MySQL 官方手册，但需要注意的是，它们其中的一些选项可能会影响性能，毕竟保证安全和可靠需要付出一些代价。
max-allowed-packet
max-allowed-packet防止服务器发送太大的数据包，也控制服务器可以接收多大的包。默认值 4M，可能会比较小。如果设置太小，有时复制上会出问题，表现为从库不能接收主库发过来的复制数据。如果表中有 Blob 或者 Text 字段，且数据量较大的话，要小心，如果数据量超过这个变量的大小，它们可能被截断或者置为 NULL，这里建议设置为 16M。
max-connect-errors
这个变量是一个 MySQL 中与安全相关的计数器值，它主要防止客户端暴力破解密码。如果某一个客户端尝试连接 MySQL 服务器失败超过 n 次，则 MySQL 会无条件强制阻止此客户端连接，直到再次刷新主机缓存或者重启 MySQL 服务器。
这个值默认为 10，太小了，有时候网络抽风或者应用配置出现错误导致短时间内不断尝试重连服务器，客户端就会被列入黑名单，导致无法连接。如果在内网环境，可以确认没有安全问题可以把这个值设置的大一点，默认值太容易导致问题。
LOGGING
接下来看下日志的配置，对于 MySQL 来说，慢日志和 bin-log 是非常重要的两种日志，前者可以帮助应用程序监控性能问题，后者在数据同步、备份等方面发挥着非常重要的作用。
关于 bin-log 的 3 个配置，log-bin用于配置文件存放路径，expire_logs_days让服务器在指定天数之后清理旧的日志，即配置保留最近多少天的日志。除非有运维手动备份清理 bin-log，否则强烈建议打开此配置，如果不启用，服务器空间最终将会被耗尽，导致服务器卡住或者崩溃。
sync-binlog
sync-binlog控制当事务提交之后，MySQL 是否将 bin-log 刷新到磁盘。如果其值等于 0 或者大于 1 时，当事务提交之后，MySQL 不会将 bin-log 刷新到磁盘，其性能最高，但存在的风险也是最大的，因为一旦系统崩溃，bin-log 将会丢失。而当其值等于 1 时，是最安全的，这时候即使系统崩溃，最多也就丢失本次未完成的事务，对实际的数据没有实质性的影响，但性能较差。
需要注意的是，在 5.7.7 之前的版本，这个选择的默认值为 0，而之后的版本默认值为 1，也就是最安全的策略。对于高并发的性能，需要关注这一点，防止版本升级后出现性能问题。
剩下的 4 个配置项就没太多要说的。
- log-error：用于配置错误日志的存放目录
- slow-query-log：打开慢日志，默认关闭
- slow-query-log-file：配置慢日志的存放目录
- log-queries-not-using-indexes：如果该 sql 没有使用索引，会将其写入到慢日志，但是否真的执行很慢，需要区分，默认关闭。
CACHES AND LIMITS
tmp-table-size && max-heap-table-size
这两个配置控制使用 Memory 引擎的内存临时表可以使用多大的内存。如果隐式内存临时表的大小超过这两个值，将会被转为磁盘 MyISAM 表 (隐式临时表由服务器创建，用户保存执行中的查询的中间结果)。
如果查询语句没有创建庞大的临时表 (通过合理的索引和查询设计来避免)，可以把这个值设大一点，以免需要把内存临时表转换为磁盘临时表。但要谨防这个值设置得过大，如果查询确实会创建很大的临时表，那么还是使用磁盘比较好，毕竟并发数一起来，所需要的内存就会急剧增长。
应该简单的把这两个变量设为同样的值，这里选择了 32M，可以通过仔细检查created-tmp-disk-tables和created-tmp-tables两个变量来指导你设置，这两个变量的值将展示临时表的创建有多频繁。
query-cache-type && query-cache-size
看前面
max-connections
用于设置用户的最大连接数，保证服务器不会应为应用程序激增的连接而不堪重负。如果应用程序有问题，或者服务器遇到连接延迟问题，会创建很多新连接。但如果这些连接不能执行查询，那打开一个连接没什么好处，所以被 “太多的连接” 错误拒绝是一种快速而且代价小的失败方式。
在服务器资源允许的情况下，可以把max-connections设置的足够大，以容纳正常可能达到的负载。若认为正常情况将有 300 或者更多连接，可以设置为 500 或者更多 (应对高峰期)。默认值是 100，太小了，这里设置为 500，但并不意味着其是一个合理的值，应该监控应用有多少连接，然后根据监控值 (观察max_used_connections随时间的变化) 来设置。
thread-cache-size
线程缓存保存那些当前没有与连接关联但是准备为后面新连接服务的线程。当一个新的连接创建时，如果缓存中有线程存在，MySQL 则从缓存中删除一个线程，并且把它分配给这个新连接。当连接关闭时，如果线程缓存还有空间的话，MySQL 又会把线程放回缓存。如果没有空间的话，MySQL 会销毁这个线程。只要 MySQL 在缓存里还有空闲的线程，它就可以迅速响应连接请求，因为这样就不用为每个连接创建新线程。thread-cache-size指定 MySQL 可以保存在缓存中的线程数量。如果服务器没有很多的连接请求，一般不需要配置这个值。
如何判断这个值该设置多大？
观察threads-connected变量，如果threads-connected在 100-120，那么thread-cache-size设置为 20。如果它保持在 500-700，200 的线程缓存应该足够大了。可以这么理解：当同时有 700 个连接时，可能缓存中没有线程。在 500 个连接时，有 200 个缓存的线程准备为负载再次增加到 700 个连接时使用。
open-files-limit
在类 Uinux 系统上我们把它设置得尽可能大。现代 OS 中打开句柄开销都很小，如果此参数设置过小，可能会遇到 “打开的文件太多 (too many open files)” 错误。
table_cache_size
表缓存跟线程缓存类似，但存储的对象是表，其包含表. frm 文件的解析结果和一些其他数据。准确的说，缓存的数据依赖于存储引擎，比如，对于 MyISAM，缓存表的数据和索引的文件描述符。表缓存对 InnoDB 的存储引擎来说，重要性会小很多，因为 InnoDB 不依赖它来做那么多的事。
从 5.1 版本及以后，表缓存就被分为两个部分：打开表缓存和定义表缓存，分别通过table-open-cache-size和table-definition-cache-size变量来配置。通常可以把table-definition-cache-size设置得足够高，以缓存所有的表定义，因为大部分存储引擎都能从table-definition-cache获益。
InnoDB
InnoDB 应该是使用最广发的存储引擎，最重要的配置选项是下面这两个：innodb-buffer-pool-size与innodb-log-file-size，解决这两个配置基本上就解决了真实场景下的大部分配置问题。
innodb-buffer-pool-size
如果大部分是 InnoDB 表，那么 InnoDB 缓冲池或许比其他任何东西都更需要内存，InnoDB 缓冲池缓冲的数据：索引、行数据、自适应哈希索引、插入缓冲、锁以及其他内部数据结构。InnoDB 还使用缓冲池来帮助延迟写入，这样就可以合并多个写入操作，然后一起顺序写入，提升性能。总之，InnoDB 严重依赖缓冲池，必须为其分配足够的内存。
当然，如果数据量不大且不会快速增长，就没有必要为缓冲池分配过多的内存，把缓冲池配置得比需要缓存的表和索引还要大很多，实际上也没有什么意义。很大的缓冲池也会带来一些挑战，例如，预热和关闭都会花费很长的时间。如果有很多脏页在缓冲池里，InnoDB 关闭时可能会花很长时间来把脏页写回数据文件。虽然可以快速关闭，但是在启动时需要做更多的恢复工作，也就是说我们无法同时加速关闭和重启两个操作。当有一个很大的缓冲池，重启服务需要花费很长时间（几小时或者几天）来预热，尤其是磁盘很慢的时候，如果想加快预热时间，可以在重启后立刻进行全表扫描或者索引扫描，把索引载入缓冲池。
可以看到示例的配置文件中把这个值配置为 12G，这不是一个标准配置，需要根据具体的硬件来估算。那如何估算？
前面的小节，我们说到，MySQL 中最重要的缓存有 5 种，可以简单的使用下面的公式计算：
InnoDB 缓冲池 = 服务器总内存 - OS 预留 - 服务器上的其他应用占用内存 - MySQL 自身需要的内存 - InnoDB 日志文件占用内存 - 其它内存 (MyISAM 键缓存、查询缓存等)
具体来看，至少需要为 OS 保留 1~2G 内存，如果机器内存大的话可以预留多一些，建议 2GB 和总内存的 5% 为基准，以较大者为准，如果机器上还运行着一些内存密集型任务，比如，备份任务，那么可以为 OS 再预留多一些内存。不要为 OS 缓存增加任何内存，因为 OS 通常会利用所有剩下的内存来做文件缓存。
一般来说，运行 MySQL 的服务器很少会运行其他应用程序，但如果有的话，请为这些应用程序预留足够多的内存。
MySQL 自身运行还需要一些内存，但通常都不会太大。需要考虑 MySQL 每个连接需要的内存，虽然每个连接需要的内存都很少，但它还要求一个基本量的内存来执行任何给定的查询，而且查询过程中还需要为排序、GROUP BY 等操作分配临时表内存，因此需要为高峰期执行大量的查询预留足够的内存。这个内存有多大？只能在运行过程中监控。
如果大部分表都是 InnoDB，MyISAM 键缓存配置一个很小值足矣，查询缓存也建议关闭。
公式中就剩下 InnoDB 日志文件了，这就是我们接下来要说的。
innodb-log-file-size && innodb-log-files-in-group
如果对 InnoDB 数据表有大量的写入操作，那么选择合适的innodb-log-file-size值对提升 MySQL 性能很重要。InnoDB 使用日志来减少提交事务时的开销。日志中记录了事务，就无须在每个事务提交时把缓冲池的脏块 (缓存中与磁盘上数据不一致的页) 刷新到磁盘。事务修改的数据和索引通常会映射到表空间的随机位置，所以刷新这些变更到磁盘需要很多随机 I/O。一旦日志安全的写入磁盘，事务就持久化了，即使变更还没有写到数据文件，在一些意外情况发生时 (比如断电了)，InnoDB 可以重放日志并且恢复已经提交的事务。
InnoDB 使用一个后台线程智能地刷新这些变更到数据文件。实际上，事务日志把数据文件的随机 I/O 转换为几乎顺序地日志文件和数据文件 I/O，让刷新操作在后台可以更快的完成，并且缓存 I/O 压力。
整体的日志文件大小受控于innodb-log-file-size和innodb-log-files-in-group两个参数，这对写性能非常重要。日志文件的总大小是每个文件的大小之和。默认情况下，只有两个 5M 的文件，总共 10M，对高性能工作来说太小了，至少需要几百 M 或者上 G 的日志文件。这里要注意innodb-log-files-in-group这个参数，它控制日志文件的数量，从名字上看好似配置一个日志组有几个文件，实际上，log group表示一个重做日志的文件集合，没有参数也没有必要配置有多少个日志组。
修改日志文件的大小，需要完全关闭 MySQL，然后将旧的日志文件迁移到其他地方，重新配置参数，然后重启。重启时需要将旧的日志迁移回来，然后等待 MySQL 恢复数据后，再删除旧的日志文件，请一定要查看错误日志，确认 MySQL 重启成功后再删除旧的日志文件。
想要确定理想的日志文件大小，需要权衡正常数据变更的开销，以及崩溃时恢复需要的时间。如果日志太小，InnoDB 将必须要做更多的检查点，导致更多的日志写，在极个别情况下，写语句还会被拖累，在日志没有空间继续写入前，必须等待变更被刷新到数据文件。另一方面，如果日志太大，在崩溃时恢复就得做大量的工作，这可能增大恢复时间。InnoDB 会采用 checkpoint 机制来刷新和恢复数据，这会加快恢复数据的时间，具体可以参考：
innodb-flush-log-at-trx-commit
前面讨论了很多缓存，InnoDB 日志也是有缓存的。当 InnoDB 变更任何数据时，会写一条变更记录到日志缓存区。在缓冲慢的时候、事务提交的时候，或者每一秒钟，InnoDB 都会将缓冲区的日志刷新到磁盘的日志文件。如果有大事务，增加日志缓冲区大小可以帮助减少 I/O，变量innodb-log-buffer-size可以控制日志缓冲区的大小。通常不需要把日志缓冲区设置的非常大，毕竟上述 3 个条件，任一条件先触发都会把缓冲区的内容刷新到磁盘，所以缓冲区的数据肯定不会太多，出入你的数据中有很多相当大的 BLOB 记录。通常来说，配置 1M~8M 即可。
既然存在缓冲区，怎样刷新日志缓冲就是我们需要关注的问题。日志缓冲必须刷新到磁盘，以确保提交的事务完全被持久化。如果和持久化相比，更在乎性能，可以修改 innodb-flush-log-at-trx-commit 变量来控制日志缓冲刷新的频率。
- 0：每 1 秒钟将日志缓冲写到日志文件并刷新到磁盘，事务提交时不做任何处理
- 1：每次事务提交时，将日志缓冲写到日志文件并刷新到磁盘
- 2：每次事务提交时，将日志缓冲写到日志文件，然后每秒刷新一次到磁盘
1 是最安全的设置，保证不会丢失任何已经提交的事务，这也是默认的设置。0 和 2 最主要的区别是，如果 MySQL 挂了，2 不会丢失事务，但 0 有可能，2 在每次事务提交时，至少将日志缓冲刷新到操作系统的缓存，而 0 则不会。如果整个服务器挂了或者断电了，则还是可能会丢失一些事务。
innodb-flush-method
前面都在讨论使用什么样的策略刷新、以及何时刷新日志或者数据，那 InnoDB 具体是怎样刷新数据的？使用innodb-flush-method选项可以配置 InnoDB 如何跟文件系统相互作用。从名字上看，会以为只能影响 InnoDB 怎么写数据，实际上还影响了 InnoDB Windows 和非 Windows 操作系统下这个选项的值是互斥的，也就是说有些值只能 Windows 下使用，有些只能在非 Windows 下使用，其中 Windows 下可取值：async_unbuffered、unbuffered、normal、Nosync与littlesync，非 Windows 取值：fdatasync、0_DIRECT、 0_DSYNC。
这个选项既会影响日志文件，也会影响数据文件，而且有时候对不同类型的文件的处理也不一样，导致这个选项有些难以理解。如果有一个选项来配置日志文件，一个选项来配置数据文件，应该会更好，但实际上它们混合在同一个配置项中。这里只介绍类 Unix 操作系统下的选项。
fdatasync
InnoDB 调用fsync()和fdatasync()函数来刷新数据和日志文件，其中fdatasync()只刷文件的数据，但不包含元数据 (比如：访问权限、文件拥有者、最后修改时间等描述文件特征的系统数据)，因此fsync()相比fdatasync()会产生更多的 I/O，但在某些场景下fdatasync()会导致数据损坏，因此 InnoDB 开发者决定用fsync()来代替fdatasync()。
fsync()的缺点是操作系统会在自己的缓存中缓冲一些数据，理论上双重缓冲是浪费的，因为 InnoDB 自己会管理缓冲，而且比操作系统更加智能。但如果文件系统能有更智能的 I/O 调度和批量操作，双重缓冲也并不一定是坏事：
- 有的文件系统和 os 可以累积写操作后合并执行，通过对 I/O 的重排序来提升效率、或者并发写入多个设备
- 有的还可以做预读优化，比如连续请求几个顺序的块，它会通知硬盘预读下一个块
这些优化在特定的场景下才会起作用，fdatasync为innodb-flush-method的默认值。
0_DIRCET
这个设置不影响日志文件并且不是所有的类 Unix 系统都有效，但至少在 Linux、FreeBSD 以及 Solaris 是支持的。这个设置依然使用 fsync 来刷新文件到磁盘，但是它完全关闭了操作系统缓存，并且是所有的读和写都直接通过存储设置，避免了双重缓冲。如果存储设备支持写缓冲或预读，那么这个选项并不会影响到设备的设置，比如 RAID 卡。
0_DSYNC
这个选项使得所有的写同步，即只有数据写到磁盘后写操作才返回，但它只影响日志文件，而不影响数据文件。
说完了每个配置的作用，最后是一些建议：如果使用类 Unix 操作系统并且 RAID 控制器带有电池保护的写缓存，建议使用 0_DIRECT，如果不是，默认值或者 0_DIRECT 都可能是最好的选择。
innodb-file-per-table
最后一个配置，说说 InnoDB 表空间，InnoDB 把数据保存在表空间内，它本质上是一个由一个或者多个磁盘文件组成的虚拟文件系统。InnoDB 表空间并不只是存储表和索引，它还保存了回滚日志、插入缓冲、双写缓冲以及其他内部数据结构，除此之外，表空间还实现了很多其它的功能。可以通过 innodb-data-file-path 配置项定制表空间文件，innodb-data-home-dir配置表空间文件存放的位置，比如：
1 | |
这里在 3 个文件中创建了 3G 表空间，为了允许表空间在超过了分配的空间时还能增长，可以像这样配置最后一个文件自动扩展
1 | |
innodb-file-per-table选项让 InnoDB 为每张表使用一个文件，这使得在删除一张表时回收空间容易很多，而且特别容易管理，并且可以通过查看文件大小来确定表大小，所以这里建议打开这个配置。
总结
MySQL 有太多的配置项，这里没有办法一一列举，重要的是了解每个配置的工作原理，从一个基础配置文件开始，设置符合服务器软硬件环境与工作负载的基本选项。