MySQL常用存储引擎优化

MySQL 提供的非常丰富的存储引擎种类供大家选择，有多种选择固然是好事，但是需要我们理解掌 握的知识也会增加很多。每一种存储引擎都有各自的特长，也都存在一定的短处。如何将各种存储引擎 在自己的应用环境中结合使用，扬长避短，也是一门不太简单的学问。本章选择最为常用的两种存储引 擎进行针对性的优化建议，希望能够对读者朋友有一定的帮助。

11.1 MyI SAM存储引擎优化

我们知道，MyISAM 存储引擎是MySQL 最为古老的存储引擎之一，也是最为流行的存储引擎之一。对 于以读请求为主的非事务系统来说，MyISAM 存储引擎由于其优异的性能表现及便利的维护管理方式无疑 是大家最优先考虑的对象。这一节我们将通过分析MyISAM 存储引擎的相关特性，来寻找提高MyISAM 存储 引擎性能的优化策略。

索引缓存优化

MyISAM 存储引擎的缓存策略是其和很多其他数据库乃至MySQL 数据库的很多其他存储引擎不太一样 的最大特性。因为他仅仅缓存索引数据，并不会缓存实际的表数据信息到内存中，而是将这一工作交给 了OS 级别的文件系统缓存。所以，在数据库优化中非常重要的优化环节之一“缓存优化”的工作在 使用MyISAM 存储引擎的数据库的情况下，就完全集中在对索引缓存的优化上面了。

在分析优化索引缓存策略之前，我们先大概了解一下MyISAM 存储引擎的索引实现机制以及索引文件 的存放格式。

MyISAM 存储引擎的索引和数据是分开存放于“.MYI”文件中，每个“.MYI”文件由文件头和实际的 索引数据。“.MYI”的文件头中主要存放四部分信息，分别称为：state（主要是整个索引文件的基本信 息），base（各个索引的相关信息，主要是索引的限制信息）， keydef（每个索引的定义信息）和 recinfo（每个索引记录的相关信息）。在文件头后面紧接着的就是实际的索引数据信息了。索引数据以 Block（Page）为最小单位，每个block 中只会存在同一个索引的数据，这主要是基于提高索引的连续读 性能的目的。在MySQL 中，索引文件中索引数据的block 被称为Index Block，每个Index Block 的大小 并不一定相等。

在“.MYI”中，Index Block 的组织形式实际上只是一种逻辑上的，并不是物理意义上的。在物理 上，实际上是以File Block 的形式来存放在磁盘上面的。在Key Cache 中缓存的索引信息是以 “Cache Block”的形式组织存放的，“Cache Block”是相同大小的，和“.MYI”文件物理存储的Block （ File Block ） 一样。在一条Query 通过索引检索表数据的时候， 首先会检查索引缓存 （key_buffer_cache）中是否已经有需要的索引信息，如果没有，则会读取“.MYI”文件，将相应的索 引数据读入Key Cache 中的内存空间中，同样也是以Block 形式存放，被称为Cache Block。不过，数据 的读入并不是以Index Block 的形式来读入，而是以File Block 的形式来读入的。以File Block 形式读 入到Key Cache 之后的Cache Block 实际上是于File Block 完全一样的。如下图所示：

http://simpleframework.net/$data/file-cache/41115.png

当我们从“.MYI”文件中读入File Block 到Key Cache 中Cache Block 时候，如果整个Key Cache 中已经没有空闲的Cache Block 可以使用的话，将会通过MySQL 实现的LRU 相关算法将某些Cache Block 清除出去，让新进来的File Block 有地方呆。

我们先来分析一下与MyISAM 索引缓存相关的几个系统参数和状态参数：

key_buffer_size，索引缓存大小；

这个参数用来设置整个MySQL 中的常规Key Cache 大小。一般来说，如果我们的MySQL 是运行 在32 位平台纸上，此值建议不要超过2GB 大小。如果是运行在64 位平台纸上则不用考虑此限制，但 也最好不要超过4GB。

key_buffer_block_size，索引缓存中的Cache Block Size；

在前面我们已经介绍了，在Key Cache 中的所有数据都是以Cache Block 的形式存在，而 key_buffer_block_size 就是设置每个Cache Block 的大小，实际上也同时限定了我们将 “.MYI”文件中的Index Block 被读入时候的File Block 的大小。

key_cache_division_limit，LRU 链表中的Hot Area 和Warm Area 分界值；

实际上，在MySQL 的Key Cache 中所使用的LRU 算法并不像传统的算法一样仅仅只是通过访问频 率以及最后访问时间来通过一个唯一的链表实现，而是将其分成了两部分。一部分用来存放使 用比较频繁的Hot Cacke Lock（Hot Chain），被成为Hot Area，另外一部分则用来存放使用 不是太频繁的Warm Cache Block（Warm Chain），被成为Warm Area。这样做的目的主要是为 了保护使用比较频繁的Cache Block 更不容易被换出。而key_cache_division_limit 参数则是 告诉MySQL该如何划分整个Cache Chain划分为Hot Chain和Warm Chain 两部分，参数值为Warm Chain 占整个Chain 的百分比值。设置范围1～100，系统默认为100，也就是只有Warm Chain。

key_cache_age_threshold，控制Cache Block 从Hot Area 降到Warm Area 的限制；

key_cache_age_threshold参数控制Hot Area 中的Cache Block 何时该被降级到Warm Area 中。 系统默认值为300，最小可以设置为100。值越小，被降级的可能性越大。

通过以上参数的合理设置，我们基本上可以完成MyISAM 整体优化的70％的工作。但是如何的合理设 置这些参数却不是一个很容易的事情。尤其是key_cache_division_limit 和key_cache_age_threshold 这两个参数的合理使用。

对于key_buffer_size 的设置我们一般需要通过三个指标来计算，第一个是系统索引的总大小，第 二个是系统可用物理内存，第三个是根据系统当前的Key Cache 命中率。对于一个完全从零开始的全新 系统的话，可能出了第二点可以拿到很清楚的数据之外，其他的两个数据都比较难获取，第三点是完全 没有。当然，我们可以通过MySQL 官方手册中给出的一个计算公式粗略的估算一下我们系统将来的索引 大小，不过前提是要知道我们会创建哪些索引，然后通过各索引估算出索引键的长度，以及表中存放数 据的条数，公式如下：

Key_Size = key_number * (key_length+4)/0.67
Max_key_buffer_size < Max_RAM - QCache_Usage - Threads_Usage - System_Usage
Threads_Usage = max_connections * (sort_buffer_size + join_buffer_size +
read_buffer_size + read_rnd_buffer_size + thread_stack)

当然，考虑到活跃数据的问题，我们并不需要将key_buffer_size 设置到可以将所有的索引都放下 的大小，这时候我们就需要Key Cache 的命中率数据来帮忙了。下面我们再来看一下系统中记录的与Key Cache 相关的性能状态参数变量。

Key_blocks_not_flushed，已经更改但还未刷新到磁盘的Dirty Cache Block；
Key_blocks_unused，目前未被使用的Cache Block 数目；
Key_blocks_used，已经使用了的Cache Block 数目；
Key_read_requests，Cache Block 被请求读取的总次数；
Key_reads，在Cache Block 中找不到需要读取的Key 信息后到“.MYI”文件中读取的次数；
Key_write_requests，Cache Block 被请求修改的总次数；
Key_writes，在Cache Block 中找不到需要修改的Key 信息后到“.MYI”文件中读入再修改的次数；
由于上面各个状态参数在MySQL 官方文档中都有较为详细的描述，所以上面仅做基本的说明。当我 们的系统上线之后，我们就可以通过上面这些状态参数的状态值得到系统当前的Key Cache 使用的详细 情况和性能状态。

Key_buffer_UsageRatio = (1 - Key_blocks_used/(Key_blocks_used + Key_blocks_unused)) *
100%
Key_Buffer_Read_HitRatio = (1 - Key_reads/Key_read_requests) * 100%
Key_Buffer_Write_HitRatio = (1 - Key_writes/Key_Write_requests) * 100%

通过上面的这三个比率数据，就可以很清楚的知道我们的Key Cache 设置是否合理，尤其是 Key_Buffer_Read_HitRatio 参数和Key_buffer_UsageRatio 这两个比率。一般来说 Key_buffer_UsageRatio 应该在99%以上甚至100%，如果该值过低，则说明我们的key_buffer_size 设置 过大，MySQL 根本使用不完。Key_Buffer_Read_HitRatio 也应该尽可能的高。如果该值较低，则很有可 能是我们的key_buffer_size 设置过小， 需要适当增加key_buffer_size 值， 也有可能是 key_cache_age_threshold和key_cache_division_limit的设置不当，造成Key Cache cache失效太快。 一般来说，在实际应用场景中，很少有人调整key_cache_age_threshold 和key_cache_division_limit 这两个参数的值，大都是使用系统的默认值。

多Key Cache 的使用

从MySQL4.1.1 版本开始，MyISAM 开始支持多个Key Cache 并存的的功能。也就是说我们可以根据不 同的需要设置多个Key Cache 了，如将使用非常频繁而且基本不会被更新的表放入一个Key Cache 中以 防止在公共Key Cache 中被清除出去，而那些使用并不是很频繁而且可能会经常被更新的Key 放入另外一 个Key Cache 中。这样就可以避免出现某些场景下大批量的Key 被读入Key Cache 的时候，因为Key Cache 空间问题使本来命中率很高的Key 也不得不被清除出去。

MySQL 官方建议在比较繁忙的系统上一般可以设置三个Key Cache：

一个Hot Cache 使用20%的大小用来存放使用非常频繁且更新很少的表的索引；
一个Cold Cache 使用20%的大小用来存放更新很频繁的表的索引；
一个Warm Cache 使用剩下的60%空间，作为整个系统默认的Key Cache；

多个Key Cache 的具体使用方法在MySQL 官方手册中有比较详细的介绍，这里就不再累述了，有兴趣 的读者朋友可以自行查阅研究。

Key Cache 的Mutex 问题

MySQL 索引缓存是所有线程共享的全局缓存，当多线程同时并发读取某一个Cache Block 的时候并不 会有任何问题，每个线程都可以同时读取该Cache Block。但是当某个Cache Block 正在被一个线程更 新或者读入的时候，则该线程就会通过mutex 锁定该Cache Block 以达到不允许其他线程再同时更新或 者读取。所以在高并发的环境下，如果Key Cache 大小不够充足是非常容易因为Cache Block 的Mutex 问题造成严重的性能影响。而且在目前正式发行的所有MySQL 版本中，Mutex 的处理机制存在一定的问 题，使得当我们的Active 线程数量稍微高一些的时候，就非常容易出现Cache Block 的Mutex 问题，甚 至有人将此性能问题作为Bug（#31551）报告给了MySQL AB。

Key Cache 预加载

在MySQL 中，为了让系统刚启动之后不至于因为Cache 中没有任何数据而出现短时间的负载过高或者 是响应不够及时的问题。MySQL 提供了Key Cache 预加载功能，可以通过相关命令（LOAD INDEX INTO CACHE tb_name_list ...），将指定表的所有索引都加载到内存中，而且还可以通过相关参数控制是否 只Load 根结点和枝节点还是将页节点也全部Load 进来，主要是为Key Cache 的容量考虑。 对于这种启动后立即加载的操作，可以利用MySQL 的init_file 参数来设置相关的命令，如下：

mysql@sky:~$ cat /usr/local/mysql/etc/init.sql
SET GLOBAL hot_cache.key_buffer_size=16777216
SET GLOBAL cold_cache.key_buffer_size=16777216
CACHE INDEX example.top_message in hot_cache
CACHE INDEX example.event in cold_cache
LOAD INDEX INTO CACHE example.top_message,example.event IGNORE LEAVES
LOAD INDEX INTO CACHE example.user IGNORE LEAVES,exmple.groups

这里我的init file 中首先设置了两个Key Cache（hot cache 和cold cache）各为16M，然后分别 将top_message 这个变动很少的表的索引Cache 到Hot Cache，再将event 这个变动非常频繁的表的索引 Cache 到了Cold Cache 中，最后再通过LOAD INDEX INTO CACHE 命令预加载了top_message，groups 这 两个表所有索引的所有节点以及event 和user 这两个表索引的非叶子节点数据到Key Cache 中，以提高 系统启动之初的响应能力。

NULL 值对统计信息的影响

虽然都是使用B-Tree 索引，但是MyISAM 索引和Oracle 索引的处理方式不太一样，MyISAM 的索引中 是会记录值为NULL 的列信息的，只不过NULL 值的索引键占用的空间非常少。所以，NULL 值的处理方式 可能会影响到MySQL 的查询优化器对执行计划的选择。所以MySQL 就给我们提供了myisam_stats_method 这个参数让我们可以自行决定对索引中的NULL 值的处理方式。

myisam_stats_method 参数的作用就是让我们告诉MyISAM 在收集统计信息的时候，是认为所有NULL 值都是等同还是认为每个NULL 值都认为是完全不相等的值，所以其可设置的值也为nulls_unequal 和 nulls_equal。

当我们设置myisam_stats_method = nulls_unequal，MyISAM 在搜集统计信息的时候会认为每个 NULL 值都不同，则基于该字段的索引的Cardinality 就会更大，也就是说MyISAM 会认为DISTINCT 值数 量更多，这样就会让查询优化器处理Query 的时候使用该索引的倾向性更高。

而当我们设置myisam_stats_method = nulls_equal 之后，MyISAM 搜集统计信息的时候则会认为每 个NULL 值的都是一样的，这样Cardinality 数值会降低，优化器选择执行计划的时候放弃该索引的倾向 性会更高。

当然，上面所说的都是相对于使用等值查询的时候，而且NULL 值占比较大的情况下，如果我们的 NULL 值本身就很少，那不管我们是使用nulls_unequal 还是nulls_equal，对优化器选择执行计划的影响 是很小很小的。

表读取缓存优化

在MySQL 中有两种读取数据文件的缓冲区，一种是Sequential Scan 方式（如全表扫描）扫描表数据 的时候使用，另一种则是在Random Scan（如通过索引扫描）的时候使用。虽然这两种文件读取缓冲区并 不是MyISAM 存储引擎所特有的，但是由于MyISAM 存储引擎并不会Cache 数据（.MYD）文件，每次对数据 文件的访问都需要通过调用文件系统的相关指令从磁盘上面读取物理文件。所以，每次读取数据文件需 要使用的内存缓冲区的设置就对数据文件访问的性能非常重要了。在MySQL 中对应这两种缓冲区的相关 参数如下：

read_buffer_size，以Sequential Scan 方式扫描表数据时候使用的Buffer；

每个Thread 进行Sequential Scan 的时候都会产生该Buffer，所以在设置的时候尽量不要太 高，避免因为并发太大造成内存不够。系统默认为128KB，最大为2GB，设置的值必须是4KB 的 倍数，否则系统会自动更改成小于设置值的最大的4KB 的倍数。 一般来说，可以尝试适当调大此参数看是否能够改善全表扫描的性能。在不同的平台上可能会 有不同的表现，这主要与OS 级别的文件系统IO 大小有关。所以该参数的设置最好是在真实环境 上面通过多次更改测试调整，才能选找到一个最佳值。

read_rnd_buffer_size，进行Random Scan 的时候使用的Buffer；

read_rnd_buffer_size 所设置的Buffer 实际上刚好和read_buffer_size 所设置的Buffer 相 反，一个是顺序读的时候使用，一个是随机读的时候使用。但是两者都是针对于线程的设置， 每个线程都可能产生两种Buffer 中的任何一个。read_rnd_buffer_size 的默认值256KB，最大 值为4G。

一般来说，read_rnd_buffer_size 值的适当调大，对提高ORDER BY 操作的性能有一定的效果。 这两个读取缓冲区都是线程独享的，每个线程在需要的时候都会创建一个（或者两个）系统中设置 大小的缓冲区，所以在设置上面两个参数的时候一定不要过于激进，而应该根须系统可能的最大连接数 和系统可用内存大小，计算出最大可设置值。

并发优化

在查询方面，MyISAM 存储引擎的并发并没有太大的问题，而且性能也非常的高。而且如果觉得光靠 Key Cache 来缓存索引还是不够快的话，我们还可以通过Query Cache 功能来直接缓存Query 的结果集。 但是，由于MyISAM 存储引擎的表级锁定机制，以及读写互斥的问题，其并发写的性能一直是一个让 人比较头疼的问题。一般来说，我们能做的主要也就只有以下几点了：

打开concurrent_insert 的功能，提高INSERT 操作和SELECT 之间的并发处理，使二者尽可能并行。大部分情况下concurrent_insert 的值都被设置为1，当表中没有删除记录留下的空余空间的时候都可以在尾部并行插入。这其实也是MyISAM 的默认设置。如果我们的系统主要以写为主，尤其是有大量的INSERT 的时候。为了尽可能提高INSERT 的效率，我们可以将concurrent_insert 设置为2，也就是告诉MyISAM，不管在表中是否有删除行留下的空余空间，都在尾部进行并发插入，使INSERT 和SELECT 能够互不干扰。
控制写入操作的大小，尽量让每次写入操作都能够很快的完成，以防止时间过程的阻塞动作。
通过牺牲读取效率来提高写入效率。为了尽可能让写入更快，可以适当调整读和写的优先级别，让写入操作的优先级高于读操作的优先级。对于一个表级锁定的存储引擎来说，除了concurrent_insert 这个比较特殊的特性之外，可以说基本上都只能是串行的写。所以虽然上面给出了三点建议，但是后面两点也只能算是优化建议，并不是真正意义上的并发优化建议。
其他可以优化的地方

除了上面我们分析的这几个方面之外，MyISAM 实际上还存在其他一些可以优化的地方和一些常用的 优化技巧。

通过OPTIMIZE 命令来整理MyISAM 表的文件。这就像我们使用Windows 操作系统会每过一段时间后都会做一次磁盘碎片整理，让系统中的文件尽量使用连续空间，提高文件的访问速度。MyISAM 在通过OPTIMIZE 优化整理的时候，主要也是将因为数据删除和更新造成的碎片空间清理，使整个文件连续在一起。一般来说，在每次做了较大的数据删除操作之后都需要做一次OPTIMIZE 操作。而且每个季度都应该有一次OPTIMIZE 的维护操作。
设置myisam_max_[extra]_sort_file_size 足够大，对REPAIR TABLE 的效率可能会有较大改善。
在执行CREATE INDEX 或者REPAIR TABLE 等需要大的排序操作的之前可以通过调整session 级别的myisam_sort_buffer_size 参数值来提高排序操作的效率。
通过打开delay_key_write 功能，减少IO 同步的操作，提高写入性能。
通过调整bulk_insert_buffer_size 来提高INSERT...SELECT...这样的bulk insert 操作的整体性能，LOAD DATA INFILE...的性能也可以得到改善。当然，在设置此参数的时候，也不应该一味的追求很大，很多时候过渡追求极端反而会影响系统整体性能，毕竟系统性能是从整体来看的，而不能仅仅针对某一个或者某一类操作。
11.2 I nnodb 存储引擎优化

Innodb 存储引擎和MyISAM 存储引擎最大区别主要有四点，第一点是缓存机制，第二点是事务支持， 第三点是锁定实现，最后一点就是数据存储方式的差异。在整体性能表现方面，Innodb 和MyISAM 两个存 储引擎在不同的场景下差异比较大，主要原因也正是因为上面这四个主要区别所造成的。锁定相关的优 化我们已经在“MySQL 数据库锁定机制”一章中做过相关的分析了，所以，本节关于Innodb 存储引擎优 化的分析，也将主要从其他三个方面展开。

11.2.1 Innodb缓存相关优化

无论是对于哪一种数据库来说，缓存技术都是提高数据库性能的关键技术，物理磁盘的访问速度永 远都会与内存的访问速度永远都不是一个数量级的。通过缓存技术无论是在读还是写方面都可以大大提 高数据库整体性能。

Innodb_buffer_pool_size 的合理设置

Innodb 存储引擎的缓存机制和MyISAM 的最大区别就在于Innodb 不仅仅缓存索引，同时还会缓存实 际的数据。所以，完全相同的数据库，使用Innodb 存储引擎可以使用更多的内存来缓存数据库相关的信 息，当然前提是要有足够的物理内存。这对于在现在这个内存价格不断降低的时代，无疑是个很吸引人 的特性。

innodb_buffer_pool_size 参数用来设置Innodb 最主要的Buffer(Innodb_Buffer_Pool)的大小，也 就是缓存用户表及索引数据的最主要缓存空间，对Innodb 整体性能影响也最大。无论是MySQL 官方手册 还是网络上很多人所分享的Innodb 优化建议，都简单的建议将Innodb 的Buffer Pool 设置为整个系统 物理内存的50% ～ 80% 之间。如此轻率的给出此类建议，我个人觉得实在是有些不妥。

不管是多么简单的参数，都可能与实际运行场景有很大的关系。完全相同的设置，不同的场景下的 表现可能相差很大。就从Innodb 的Buffer Pool 到底该设置多大这个问题来看，我们首先需要确定的是 这台主机是不是就只提供MySQL 服务？MySQL 需要提供的的最大连接数是多少？MySQL 中是否还有MyISAM 等其他存储引擎提供服务？如果有，其他存储引擎所需要使用的Cache 需要多大？

假设是一台单独给MySQL 使用的主机，物理内存总大小为8G，MySQL 最大连接数为500，同时还使用 了MyISAM 存储引擎，这时候我们的整体内存该如何分配呢？

内存分配为如下几大部分：

a) 系统使用，假设预留800M；
b) 线程独享，约2GB = 500 * (1MB + 1MB + 1MB + 512KB + 512KB)，组成大概如下:
sort_buffer_size：1MB
join_buffer_size：1MB
read_buffer_size：1MB
read_rnd_buffer_size：512KB
thread_statck：512KB
c) MyISAM Key Cache，假设大概为1.5GB；
d) Innodb Buffer Pool 最大可用量：8GB - 800MB - 2GB - 1.5GB = 3.7GB；

假设这个时候我们还按照50%～80%的建议来设置，最小也是4GB，而通过上面的估算，最大可用值 在3.7GB 左右，那么很可能在系统负载很高当线程独享内存差不多出现极限情况的时候，系统很可能就 会出现内存不足的问题了。而且上面还仅仅只是列出了一些使用内存较大的地方，如果进一步细化，很 可能可用内存会更少。

上面只是一个简单的示例分析，实际情况并不一定是这样的，这里只是希望大家了解，在设置一些 参数的时候，千万不要想当然，一定要详细的分析可能出现的情况，然后再通过不断测试调整来达到自 己所处环境的最优配置。就我个人而言，正式环境上线之初，我一般都会采取相对保守的参数配置策 略。上线之后，再根据实际情况和收集到的各种性能数据进行针对性的调整。

当系统上线之后，我们可以通过Innodb 存储引擎提供给我们的关于Buffer Pool 的实时状态信息作 出进一步分析，来确定系统中Innodb 的Buffer Pool 使用情况是否正常高效：

sky@localhost : example 08:47:54> show status like 'Innodb_buffer_pool_%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_pages_data | 70 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 1978 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 2048 |
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 329 |
| Innodb_buffer_pool_reads | 19 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
+-----------------------------------+-------+

从上面的值我们可以看出总共2048 pages，还有1978 是Free 状态的仅仅只有70 个page 有数据， read 请求329 次，其中有19 次所请求的数据在buffer pool 中没有，也就是说有19 次是通过读取物理 磁盘来读取数据的，所以很容易也就得出了Innodb Buffer Pool 的Read 命中率大概在为：(329 - 19) / 329 * 100% = 94.22%。

当然，通过上面的数据，我们还可以分析出write 命中率，可以得到发生了多少次 read_ahead_rnd，多少次read_ahead_seq，发生过多少次latch，多少次因为Buffer 空间大小不足而产 生wait_free 等等。

单从这里的数据来看，我们设置的Buffer Pool 过大，仅仅使用70 / 2048 * 100% = 3.4%。 在Innodb Buffer Pool 中，还有一个非常重要的概念，叫做“预读”。一般来说，预读概念主要是 在一些高端存储上面才会有，简单来说就是通过分析数据请求的特点来自动判断出客户在请求当前数据 块之后可能会继续请求的数据快。通过该自动判断之后，存储引擎可能就会一次将当前请求的数据库和 后面可能请求的下一个（或者几个）数据库一次全部读出，以期望通过这种方式减少磁盘IO 次数提高IO 性能。在上面列出的状态参数中就有两个专门针对预读：

Innodb_buffer_pool_read_ahead_rnd，记录进行随机读的时候产生的预读次数；

Innodb_buffer_pool_read_ahead_seq，记录连续读的时候产生的预读次数；

innodb_log_buffer_size 参数的使用

顾名思义，这个参数就是用来设置Innodb 的Log Buffer 大小的，系统默认值为1MB。Log Buffer 的主要作用就是缓冲Log 数据，提高写Log 的IO 性能。一般来说，如果你的系统不是写负载非常高且以 大事务居多的话，8MB 以内的大小就完全足够了。

我们也可以通过系统状态参数提供的性能统计数据来分析Log 的使用情况：

sky@localhost : example 10:11:05> show status like 'innodb_log%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 6 |
| Innodb_log_writes | 2 |
+---------------------------+-------+

通过这三个状态参数我们可以很清楚的看到Log Buffer 的等待次数等性能状态。 当然，如果完全从Log Buffer 本身来说，自然是大一些会减少更多的磁盘IO。但是由于Log 本身是 为了保护数据安全而产生的，而Log 从Buffer 到磁盘的刷新频率和控制数据安全一致的事务直接相关， 并且也有相关参数来控制（innodb_flush_log_at_trx_commit），所以关于Log 相关的更详细的实现机 制和优化在后面的“事务优化”中再做更详细的分析，这里就不展开了。

innodb_additional_mem_pool_size 参数理解

innodb_additional_mem_pool_size 所设置的是用于存放Innodb 的字典信息和其他一些内部结构所 需要的内存空间。所以我们的Innodb 表越多，所需要的空间自然也就越大，系统默认值仅有1MB。当 然，如果Innodb 实际运行过程中出现了实际需要的内存比设置值更大的时候，Innodb 也会继续通过OS 来申请内存空间，并且会在MySQL 的错误日志中记录一条相应的警告信息让我们知晓。

从我个人的经验来看，一个常规的几百个Innodb 表的MySQL，如果不是每个表都是上百个字段的 话，20MB 内存已经足够了。当然，如果你有足够多的内存，完全可以继续增大这个值的设置。实际上， innodb_additional_mem_pool_size 参数对系统整体性能并无太大的影响，所以只要能存放需要的数据即 可，设置超过实际所需的内存并没有太大意义，只是浪费内存而已。

Double Write Buffer

Double Write Buffer 是Innodb 所使用的一种较为独特的文件Flush 实现技术，主要做用是为了通 过减少文件同步次数提高IO 性能的情况下，提高系统Crash 或者断电情况下数据的安全性，避免写入的 数据不完整。

一般来说，Innodb 在将数据同步到数据文件进行持久化之前，首先会将需要同步的内容写入存在于 表空间中的系统保留的存储空间，也就是被我们称之为Double Write Buffer 的地方，然后再将数据进 行文件同步。所以实质上，Double Write Buffer 中就是存放了一份需要同步到文件中数据的一个备份， 以便在遇到系统Crash 或者主机断电的时候，能够校验最后一次文件同步是否准确的完成了，如果未完 成，则可以通过这个备份来继续完成工作，保证数据的正确性。

那这样Innodb 不是又一次增加了整体IO 量了吗？这样不是可能会影响系统的性能么？这个完全不用 太担心，因为Double Write Buffer 是一块连续的磁盘空间，所有写入Double Write Buffer 的操作都是 连续的顺序写入操作，与整个同步过程相比，这点IO 消耗所占的比例是非常小的。为了保证数据的准确 性，这样一点点性能损失是完全可以接受的。

实际上，并不是所有的场景都需要使用Double Write 这样的机制来保证数据的安全准确性，比如当 我们使用某些特别文件系统的时候，如在Solaris 平台上非常著名的ZFS 文件系统，他就可以自己保证文 件写入的完整性。而且在我们的Slave 端，也可以禁用Double Write 机制。

Adaptive Hash Index

在Innodb 中，实现了一个自动监测各表索引的变化情况的机制，然后通过一系列的算法来判定如果 存在一个Hash Index 是否会对索引搜索带来性能改善。如果Innodb 认为可以通过Hash Index 来提高检 索效率，他就会在内部自己建立一个基于某个B-Tree 索引的Hash Index，而且会根据该B-Tree 索引的 变化自行调整，这就是我们常说的Adaptive Hash Index。当然，Innodb 并不一定会将整个B-Tree 索引 完全的转换为Hash Index，可能仅仅只是取用该B-Tree 索引键一定长度的前缀来构造一个Hash Index。 Adaptive Hash Index 并不会进行持久化存放在磁盘上面，仅仅存在于Buffer Pool 中。所以，在每 次MySQL 刚启动之后是并不存在Adaptive Hash Index 的，只有在停工服务之后，Innodb 才会根据相应 的请求来构建。

Adaptive Hash Index 的目的并不是为了改善磁盘IO 的性能，而是为了提高Buffer Pool 中的数据 的访问效率，说的更浅显一点就是给Buffer Pool 中的数据做的索引。所以，Innodb 在具有大容量内存 （可以设置大的Buffer Pool）的主机上，对于其他存储引擎来说，会存在一定的性能优势。

11.2.2 事务优化

选择合适的事务隔离级别

Innodb 存储引擎是MySQL 中少有的支持事务的存储引擎之一，这也是其成为目前MySQL 环境中使用 最广泛存储引擎之一的一个重要原因。由于事务隔离的实现本身是需要消耗大量的内存和计算资源，而 且不同的隔离级别所消耗的资源也不一样，性能表现也各不相同。所以我们首先我们大概了解一下Innodb 所支持的各种事务隔离级别。通过Innodb 的参考手册，我们得到 Innodb 在事务隔离级别方面支持的信息如下：

1. READ UNCOMMITTED

常被成为Dirty Reads（脏读），可以说是事务上的最低隔离级别：在普通的非锁定模式下 SELECT 的执行使我们看到的数据可能并不是查询发起时间点的数据，因而在这个隔离度下是非 Consistent Reads（一致性读）；

2. READ COMMITTED

这个事务隔离级别有些类似Oracle 数据库默认的隔离级。属于语句级别的隔离，如通过 SELECT ... FOR UPDATE 和SELECT ... LOCK IN SHARE MODE 来执行的请求仅仅锁定索引记录，而 不锁定之前的间隙，因而允许在锁定的记录后自由地插入新记录。当然，这与Innodb 的锁定实现机 制有关。如果我们的Query 可以很准确的通过索引定位到需要锁定的记录，则仅仅只需要锁定相关 的索引记录，而不需要锁定该索引之前的间隙。但如果我们的Query 通过索引检索的时候无法通过 索引准确定位到需要锁定的记录，或者是一个基于范围的查询，InnoDB 就必须设置next-key 或 gap locks 来阻塞其它用户对范围内的空隙插入。Consistent Reads 的实现机制与Oracle 基本类 似： 每一个Consistent Read，甚至是同一个事务中的，均设置并作为它自己的最新快照。 这一隔离级别下，不会出现Dirty Read，但是可能出现Non-Repeatable Reads(不可重复读) 和Phantom Reads（幻读）。

3. REPEATABLE READ

REPEATABLE READ 隔离级别是InnoDB 默认的事务隔离级。SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, 和DELETE ，这些以唯一条件搜索唯一索引的，只锁定所找到 的索引记录，而不锁定该索引之前的间隙。否则这些操作将使用next-key 锁定，以next-key 和 gap locks 锁定找到的索引范围，并阻塞其它用户的新建插入。在Consistent Reads 中，与前一 个隔离级相比这是一个重要的差别： 在这一级中，同一事务中所有的Consistent Reads 均读取第 一次读取时已确定的快照。这个约定就意味着如果在同一事务中发出几个无格式(plain)的SELECTs ，这些SELECT 的相互关系是一致的。

在REPEATABLE READ 隔离级别下，不会出现Dirty Reads，也不会出现Non-Repeatable Reads， 但是仍然存在Phantom Reads 的可能性。

4. SERIALIZABLE

SERIALIZABLE 隔离级别是标准事务隔离级别中的最高级别。设置为SERIALIZABLE 隔离级别之 后，在事务中的任何时候所看到的数据都是事务启动时刻的状态，不论在这期间有没有其他事务已 经修改了某些数据并提交。所以，SERIALIZABLE 事务隔离级别下，Phantom Reads 也不会出现。 以上四种事务隔离级别实际上就是ANSI/ISO SQL92 标准所定义的四种隔离级别，Innodb 全部都为我 们实现了。对于高并发应用来说，为了尽可能保证数据的一致性，避免并发可能带来的数据不一致问 题，自然是事务隔离级别越高越好。但是，对于Innodb 来说，所使用的事务隔离级别越高，实现复杂度 自然就会更高，所需要做的事情也会更多，整体性能也就会更差。

所以，我们需要分析自己应用系统的逻辑，选择可以接受的最低事务隔离级别。以在保证数据安全 一致性的同时达到最高的性能。

虽然Innodb 存储引擎默认的事务隔离级别是REPEATABLE READ，但实际上在我们大部分的应用场景 下，都只需要READ COMMITED 的事务隔离级别就可以满足需求了。 事务与IO 的关系及优化

我想大部分人都清楚，Innodb 存储引擎通过缓存技术，将常用数据和索引缓存到内存中，这样我们 在读取数据或者索引的时候就可以尽量减少物理IO 来提高性能。那我们修改数据的时候Innodb 是如何处 理的呢，是否修改数据的时候Innodb 是不是象我们常用的应用系统中的缓存一样，更改缓存中的数据的 同时，将更改同时应用到相应的数据持久化系统中？

可能很多人都会有上面的这个疑问。实际上，Innodb 在修改数据的时候同样也只是修改Buffer Pool 中的数据，并不是在一个事务提交的时候就将BufferPool 中被修改的数据同步到磁盘，而是通过另 外一种支持事务的数据库系统常用的手段，将修改信息记录到相应的事务日志中。

为什么不是直接将Buffer Pool 中被修改的数据直接同步到磁盘，还有记录一个事务日志呢，这样 不是反而增加了整体IO 量了么？是的，对于系统的整体IO 量而言，确实是有所增加。但是，对于系统的 整体性能却有很大的帮助。

这里我们需要理解关于磁盘读写的两个概念：连续读写和随机读写。简单来说，磁盘的顺序读写就 是将数据顺序的写入连续的物理位置，而随即读写则相反，数据需要根据各自的特定位置被写入各个位 置，也就是被写入了并不连续的物理位置。对于磁盘来说，写入连续的位置最大的好处就是磁头所做的 寻址动作很少，而磁盘操作中最耗费时间的就是磁头的寻址。所以，在磁盘操作中，连续读写操作比随 即读写操作的性能要好很多。

我们的应用所修改的Buffer Pool 中的数据都很随机，每次所做的修改都是一个或者少数几个数据 页，多次修改的数据页也很少会连续。如果我们每次修改之后都将Buffer Pool 中的数据同步到磁盘， 那么磁盘就只能一直忙于频繁的随即读写操作。而事务日志在创建之初就是申请的连续的物理空间，而 且每次写入都是紧接着之前的日志数据顺序的往后写入，基本上都是一个顺序的写入过程。所以，日志 的写入操作远比同步Buffer Pool 中被修改的数据要更快。

当然，由于事务日志都是通过几个日志文件轮循反复写入，而且每个日志文件大小固定，即使再多 的日志也会有旧日志被新产生的日志覆盖的时候。所以，Buffer Pool 中的数据还是不可避免的需要被刷 新到磁盘上进行持久化，而且这个持久化的动作必须在旧日志被新日志覆盖之前完成。只不过，随着被 更新的数据（Dirty Buffer）的增加，需要刷新的数据的连续性就越高，所需要做的随机读写也就越 少，自然，IO 性能也就得到了提升。

而且事务日志本身也有Buffer（log buffer），每次事务日志的写入并不是直接写入到文件，也都 是暂时先写入到log buffer 中，然后再在一定的事件触发下才会同步到文件。当然，为了尽可能的减少 事务日志的丢失，我们可以通过innodb_log_buffer_size 参数来控制log buffer 的大小。关于事务日志 何时同步的说明稍后会做详细分析。

事务日志文件的大小与Innodb 的整体IO 性能有非常大的关系。理论上来讲，日志文件越大，则 Buffer Pool 所需要做的刷新动作也就越少，性能也越高。但是，我们也不能忽略另外一个事情，那就是 当系统Crash 之后的恢复。

事务日志的作用主要有两个，一个就是上面所提到的提高系统整体IO 性能，另外一个就是当系统 Crash 之后的恢复。下面我们就来简单的分析一下当系统Crash 之后，Innodb 是如何利用事务日志来进行 数据恢复的。

Innodb 中记录了我们每一次对数据库中的数据及索引所做的修改，以及与修改相关的事务信息。同 时还记录了系统每次checkpoint 与log sequence number（日志序列号）。

假设在某一时刻，我们的MySQL Crash 了，那么很显然，所有Buffer Pool 中的数据都会丢失，也包 括已经修改且没有来得及刷新到数据文件中的数据。难道我们就让这些数据丢失么？当然不会，当 MySQL 从Crash 之后再次启动，Innodb 会通过比较事务日志中所记录的checkpoint 信息和各个数据文 件中的checkpoint 信息，找到最后一次checkpoint 所对应的log sequence number，然后通过事务日志 中所记录的变更记录，将从Crash 之前最后一次checkpoint 往后的所有变更重新应用一次，同步所有的 数据文件到一致状态，这样就找回了因为系统Crash 而造成的所有数据丢失。当然，对于log buffer 中 未来得及同步到日志文件的变更数据就无法再找回了。系统Crash 的时间离最后一次checkpoint 的时间 越长，所需要的恢复时间也就越长。而日志文件越大，Innodb 所做的checkpoint 频率也越低，自然遇到 长时间恢复的可能性也就越大了。

总的来说，Innodb 的事务日志文件设置的越大，系统的IO 性能也就越高，但是当遇到MySQL ，OS 或者主机Crash 的时候系统所需要的恢复时间也就越长；反之，日志越小，IO 性能自然也就相对会差一 些，但是当MySQL，OS 或者主机Crash 之后所需要的恢复时间也越小。所以，到底该将事务日志设置多 大其实是一个整体权衡的问题，既要考虑到系统整体的性能，又要兼顾到Crash 之后的恢复时间。一般 来说，在我个人维护的环境中，比较偏向于将事务日志设置为3 组，每个日志设置为256MB 大小，整体效 果还算不错。

前面所描述的场景还只是MySQL Crash 的场景，我们所丢失的仅仅只是Buffer Pool 中的数据。实际 上Innodb 事务日志也不一定每次事务提交或者回滚都保证会同步log buffer 中的数据到文件系统并通知 文件系统做文件同步操作。所以当我们的OS Crash，或者是主机断点之后，事务日志写入文件系统 Buffer 中的数据还是可能会丢失，这种情况下，如果我们的事务日志没有及时同步文件系统刷新缓存中 的数据到磁盘文件的话，就可能会产生日志数据丢失而造成数据永久性丢失的情况。

其实Innodb 也早就考虑到了这种情况的存在，所以在系统中为我们设计了下面这个控制Innodb 事务 日志刷新方式的参数：innodb_flush_log_at_trx_commit。这个参数的主要功能就是让我们告诉系统， 在什么情况下该通知文件系统刷新缓存中的数据到磁盘文件，可设置为如下三种值

innodb_flush_log_at_trx_commit = 0，Innodb 中的Log Thread 没隔1 秒钟会将log buffer 中的数据写入到文件，同时还会通知文件系统进行文件同步的flush 操作，保证数据确实已经 写入到磁盘上面的物理文件。但是，每次事务的结束（commit 或者是rollback）并不会触发 Log Thread 将log buffer 中的数据写入文件。所以，当设置为0 的时候，当MySQL Crash 和 OS Crash 或者主机断电之后，最极端的情况是丢失1 秒时间的数据变更。

innodb_flush_log_at_trx_commit = 1，这也是Innodb 的默认设置。我们每次事务的结束都会 触发Log Thread 将log buffer 中的数据写入文件并通知文件系统同步文件。这个设置是最安全 的设置，能够保证不论是MySQL Crash 还是OS Crash 或者是主机断电都不会丢失任何已经提 交的数据。

innodb_flush_log_at_trx_commit = 2，当我们设置为2 的时候，Log Thread 会在我们每次事 务结束的时候将数据写入事务日志，但是这里的写入仅仅是调用了文件系统的文件写入操作。 而我们的文件系统都是有缓存机制的，所以Log Thread 的这个写入并不能保证内容真的已经写 入到物理磁盘上面完成持久化的动作。文件系统什么时候会将缓存中的这个数据同步到物理磁 盘文件Log Thread 就完全不知道了。所以，当设置为2 的时候，MySQL Crash 并不会造成数据 的丢失，但是OS Crash 或者是主机断电后可能丢失的数据量就完全控制在文件系统上了。各种 文件系统对于自己缓存的刷新机制各不相同，各位读者朋友如果有兴趣可以自行参阅相关的手 册。

从上面的分析我们可以看出，当innodb_flush_log_at_trx_commit 设置为1 的时候是最安全的，但 是由于所做的IO 同步操作也最多，所以性能也是三种设置中最差的一种。如果设置为0，则每秒有一次 同步，性能相对高一些。如果设置为2，可能性能是三这种最好的。但是也可能是出现鼓掌后丢失数据最 多的。到底该如何设置设置，就要根据具体的场景来分析了。一般来说，如果完全不能接受数据的丢 失，那么我们肯定会通过牺牲一定的性能来换取数据的安全性，选择设置为1。而如果我们可以丢失很少 量的数据(比如说1 秒之内)，那么我们可以设置为0。当然，如果大家觉得我们的OS 足够稳定，主机硬 件设备，而且主机的供电系统也足够安全，我们也可以将innodb_flush_log_at_trx_commit 设置为2 让 系统的整体性能尽可能的高。

前面我们还提到了设置Log Buffer 大小的参数innodb_log_buffer_size。这里我们也简单的介绍一 下Log Buffer 的设置要领。Log Buffer 所存放的数据就是事务日志在写入文件之前在内存中的一个缓冲 区域。所以理论上来讲，Log Buffer 越大，系统的性能也会越高。但是，由于触发Log Thread 将Log Buffer 中的数据写入文件的事件并不仅仅是Log Buffer 空间用完的情况，还与 innodb_flush_log_at_trx_commit参数的设置有关。如果该参数设置为1 或者2，那么我们的Log Buffer 中仅仅只需要保存单个事务的变更量与系统最高并发事务的乘积。也就是说，如果我们的系统同时进行 修改的并发事务最高为20 的话，那么我们的Log Buffer 就只需要存放20 个事务所作的变更。当然，如 果我们设置为0 的话，Log Buffer 中所需要存放的数据则是1 秒内所有的变更量。所以，大家需要根据 自己系统的具体环境来针对性分析innodb_log_buffer_size 的设置大小。一般来说，如果不是特别高的 事务并发度或者系统中都是大事务的话，8MB 的内存空间已经完全够用了。

11.2.3 数据存储优化

从“MySQL 存储引擎简介”一章中我们已经对Innodb 存储引擎的物理结构有了一定的了解，这一节 我们将通过分析Innodb 的物理文件结构寻找可以优化的线索。

理解Innodb 数据及索引文件存储格式

Innodb 存储引擎的数据（包括索引）存放在相同的文件中，这一点和MySQL 默认存储引擎MyISAM 的 区别较大，后者分别存放于独立的文件。除此之外，Innodb 的数据存放格式也比较独特，每个Innodb 表 都会将主键以聚簇索引的形式创建。所有的数据都是以主键来作为升序排列在物理磁盘上面，所以主键 查询并且以主键排序的查询效率也会非常高。

由于主键是聚族索引的缘故，Innodb 的基于主键的查询效率非常高。如果我们在创建一个Innodb 存 储引擎的表的时候并没有创建主键，那么Innodb 会尝试在创建于我们表上面的其他索引，如果存在由单 个not null 属性列的唯一索引，Innodb 则会选择该索引作为聚族索引。如果也没有任何单个not null 属性列的唯一索引，Innodb 会自动生成一个隐藏的内部列，该列会在每行数据上占用6 个字节的存储长 度。所以，实质上每个Innodb 表都之少会有一个索引存在。在Innodb 上面出了聚族索引之外的索引被成为secondary index，每个secondary index 上都会包含有聚族索引的索引键信息，方便通过其他索引查找数据的时候能够更快的定位数据位置所在。 当然，聚族索引也并不是只有好处没有任何问题，要不然其他所有数据库早就大力推广了。聚族索 引的最大问题就是当索引键被更新的时候，所带来的成本并不仅仅只是索引数据可能会需要移动，而是 相关的所有记录的数据都需要移动。所以，为了性能考虑，我们应该尽可能不要更新Innodb 的主键值。

page

Innodb 存储引擎中的所有数据，不论是表还是索引，亦或是存储引擎自己的各种结构，都是以page 作为最小物理单位来存放，每个page 默认大小为16KB。

extent

extent 是一个由多个连续的page 组成一个物理存储单位。一般来说，每个extent 为64 个page。

segment

segment 在Innodb 存储引擎中实际上也代表“files”的意思，每个segment 由一个或多个extent 组成，而且每个segment 都存放同一种数据。一般来说，每个表数据会存放于一个单独的segment 中，实 际上也就是每个聚族索引会存放于一个单独的segment 中。

tablespace

tablespace 是Innodb 中最大物理结构单位了，由多个segment 组成。 当tablespace 中的某个segment 需要增长的时候，Innodb 最初仅仅分配某一个extent 的前32 个pages， 然后如果继续增长才会分配整个extent 来使用。我们还可以通过执行如下命令来查看Innodb 表空间的使 用情况：

sky@localhost : example 01:26:43> SHOW TABLE STATUS like 'test'\G
*************************** 1. row ***************************
Name: test
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 8389019
Avg_row_length: 29
Data_length: 249298944
Max_data_length: 0
Index_length: 123387904
Data_free: 0
Auto_increment: NULL
Create_time: 2008-11-15 01:26:43
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 5120 kB

通过上面的显示，我们可以看出 虽然每个索引页（index page）大小为16KB，但是实际上Innodb 在第一次使用该page 的时候，如 果是一个顺序的索引插入，都会预留1KB 的空间。而如果是随机插入的话，那么大约会使用(8- 15/16) KB 的空间，而如果一个Index page 在进行多次删除之后如果所占用的空间已经低于8KB（1/2）的话， Innodb 会通过一定的收缩机制收缩索引，并释放该index page。此外，每个索引记录中都存放了一个6 字节的头信息，主要用于行锁定时候的记录以及各个索引记录的关联信息。

Innodb 在存放数据页的时候不仅仅只是存放我们实际定义的列，同时还会增加两个内部隐藏列，其 中一个隐含列的信息主要为事务相关信息，会占用6 个字节的长度。另外一个则占用7 字节长度，主要用 来存放一个指向Undo Log 中的Undo Segment 的指针相关信息，主要用于事务回滚，以及通过Undo Segment 中的信息构造多版本数据页。

通过上面的信息，我们至少可以得出以下几点对性能有较大影响的地方：

为了尽量减小secondary index 的大小，提高访问效率，作为主键的字段所占用的存储空间越小越好，最好是INTEGER 类型。当然这并不是绝对的，字符串类型的数据同样也可以作为Innodb 表的主键；
创建表的时候尽量自己指定相应的主键，让数据按照自己预设的顺序排序存放，一提高特定条件下的访问效率；
尽可能不要在主键上面进行更新操作，减少因为主键值的变化带来数据的移动。
尽可能提供主键条件进行查询；
分散IO 提升磁盘响应

由于Innodb 和其他非事务存储引擎相比在记录数据文件的同时还记录有相应的事务日志 （Transaction Log），相当于增加的整体的IO 量，虽然事务日志是以完全顺序的方式写入磁盘，但总 是会有一定的IO 消耗，所以对于没有做Raid 的磁盘系统来说，建议将数据文件和事务日志文件分别存放 于不同的物理磁盘上面以降低磁盘的相互争用，提高整体IO 性能。我们可以通过 innodb_log_group_home_dir 参数来指定Innodb 日志存放位置，同时再通过设置数据文件位置 innodb_data_home_dir 参数来告诉Innodb 我们希望将数据文件存放在哪里。

当然，如果我们使用独享表空间的话，Innodb 会为每个Innodb 表创建一个表空间，并且会将该表空 间存放在和“.frm”文件相同的路径下。不过幸运的是，Innodb 允许通过软链接的方式来访问数据或者 日志文件。所以，如果我们有必要，甚至可以将每个表存放于单独的物理磁盘，然后再通过软链接的方 式来告诉Innodb 我们的实际文件在哪里。

当我们使用共享表空间的时候，最后一个数据文件必须是可以自动扩展的，这样就会带来一个疑 问，在每次扩展的时候，到底该扩展多大空间性能会比较好呢？Innodb 给我们设计了 innodb_autoextend_increment 这个参数，让我们可以自行控制表空间文件每次增加的大小。

11.2.4 Innodb其他优化

除了上面这些可以优化的地方之外，实际上Innodb 还有其他一些可能影响到性能的参数设置：

Innodb_flush_method

用来设置Innodb 打开和同步数据文件以及日志文件的方式，不过只有在Linux & Unix 系统上面 有效。系统默认值为fdatasync，即Innodb 默认通过fsync()来flush 数据和日志文件数据。 此外，还可以设置为O_DSYNC 和O_DIRECT，当我们设置为O_DSYNC，则系统以O_SYNC 方式打开和刷 新日志文件， 通过fsync() 来打开和刷新数据文件。而设置为O_DIRECT 的时候， 则通过 O_DIRECT（Solaris 上为directio()）打开数据文件，同时以fsync()来刷新数据和日志文件 总的来说，innodb_flush_method 的不同设置主要影响的是Innodb 在不同运行平台下进行IO 操 作的时候所调用的操作系统IO 借口的区别。而不同的IO 操作接口对数据的处理方式会有一定的 区别，所以处理性能也会有一定的差异。一般来说，如果我们的磁盘是通过RAID 卡做了硬件级 别的RAID，建议可以使用O_DIRECT，可以一定程度上提高IO 性能，但如果RAID Cache 不够的 话，还是需要谨慎对待。此外，根据MySQL 官方手册上面的介绍，如果我们的存储环境是SAN 环 境，使用O_DIRECT 有可能会反而使性能降低。对于支持O_DSYNC 的平台，也可以尝试设置为 O_DSYNC 方式看是否能对写IO 性能有所帮助。

innodb_thread_concurrency

这个参数主要控制Innodb 内部的并发处理线程数量的最大值，系统内部会有相应的检测机制进 行检测控制并发线程数量，Innodb 建议设置为CPU 个数与磁盘个数之和。但是这个参数一直是 一个非常有争议的参数，而且还有一个非常著名的BUG（#15815）一直被认为就于 innodb_thread_concurrency 参数所控制的内容相关。从该参数在系统中的默认值的变化我们也 可以看出即使是Innodb 开发人员也并不是很清楚到底该将innodb_thread_concurrency 设置为 多少合适。在MySQL5.0.8 之前，默认值为8，从MySQL5.0.8 开始到MySQL5.0.18，默认值又 更改为20，然后在MySQL5.0.19 和MySQL5.0.20 两个版本中又默认设置为0。之后，从 MySQL5.0.21 开始默认值再次被更改回8。

innodb_thread_concurrency 参数的设置范围是0～1000，但是在MySQL5.0.19 之前的版本，只 要该值超过20，Innodb 就会认为不需要对并发线程数做任何限制，也就是说Innodb 不会再进行 并行线程的数目检查。同样，我们也可以通过设置为0 来禁用并行线程检查，完全让Innodb 自 己根据实际需要创建并行线程，而且在不少场景下设置为0 还是一个非常不错的选择，尤其 当系统写IO 压力较大的时候。

总的来说，innodb_thread_concurrency 参数的设置并没有一个很好的规则来判断什么场景该设 置多大，完全需要通过不断的调整尝试，寻找出适合自己应用的设置。

autocommit

autocommit 的用途我想大家应该都很清楚，就是当我们将该参数设置为true(1)之后，在我们 每次执行完一条会修改数据的Query 之后，系统内部都会自动提交该操作，基本上可以理解为 屏蔽了事务的概念。

设置aotucommit 为true（1）之后，我们的提交相对于自己手工控制commit 时机来说可能会变 得要频繁很多。这样带来的直接影响就是Innodb 的事务日志可能会需要非常频繁的执行磁盘同 步操作，当然还与innodb_flush_log_at_trx_commit 参数的设置相关。

一般来说，在我们通过LOAD ... INFILE ... 或者其他的某种方式向Innodb 存储引擎的表加载 数据的时候，将autocommit 设置为false 可以极大的提高加载性能。而在正常的应用中，也最 好尽量通过自行控制事务的提交避免过于频繁的日志刷新来保证性能。

11.2.5 Innodb性能监控

我们可以通过执行“SHOW INNODB STATUS”命令来获取比较详细的系统当前Innodb 性能状态，如 下：

sky@localhost : example 03:11:19> show innodb status\G
*************************** 1. row ***************************
Status:
=====================================
081115 15:56:30 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 10 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 720, signal count 719
Mutex spin waits 0, rounds 16962, OS waits 460
RW-shared spins 489, OS waits 244; RW-excl spins 3, OS waits 3
------------
TRANSACTIONS
------------
Trx id counter 0 11605
Purge done for trx's n:o < 0 11604 undo n:o < 0 0
History list length 10
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 13383, OS thread id 2892274576
MySQL thread id 9, query id 54 localhost sky
show innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1123 OS file reads, 2791 OS file writes, 1941 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 138401, used cells 2, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 1072999334
Log flushed up to 0 1072999334
Last checkpoint at 0 1072999334
0 pending log writes, 0 pending chkp writes
1301 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 58787017; in additional pool allocated 1423616
Buffer pool size 2048
Free buffers 803
Database pages 1244
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 15923, created 22692, written 23332
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 13383, id 2966408080, state: waiting for server activity
Number of rows inserted 8388614, updated 0, deleted 0, read 8388608
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

通过上面的输出，我们可以看到整个信息分为7 个部分，分别说明如下：

SEMAPHORES，这部分主要显示系统中当前的信号等待信息以及各种等待信号的统计信息，这部 分输出的信息对于我们调整innodb_thread_concurrency 参数有非常大的帮助，当等待信号量 非常大的时候，可能就需要禁用并发线程检测设置innodb_thread_concurrency=0；

TRANSACTIONS，这里主要展示系统的锁等待信息和当前活动事务信息。通过这部分输出，我们 可以查追踪到死锁的详细信息；

FILE I/O，文件IO 相关的信息，主要是IO 等待信息；

INSERT BUFFER AND ADAPTIVE HASH INDEX；显示插入缓存当前状态信息以及自适应Hash Index 的状态；

LOG，Innodb 事务日志相关信息，包括当前的日志序列号（Log Sequence Number），已经刷新 同步到哪个序列号，最近的Check Point 到哪个序列号了。除此之外，还显示了系统从启动到 现在已经做了多少次Ckeck Point，多少次日志刷新；

BUFFER POOL AND MEMORY，这部分主要显示Innodb Buffer Pool 相关的各种统计信息，以及其 他一些内存使用的信息；

ROW OPERATIONS，顾名思义，主要显示的是与客户端的请求Query 和这些Query 所影响的记录统 计信息。

这里只是对输出做了一个简单的介绍，如果各位读者朋友希望更深入的了解相应的细节，建议查阅 Innodb 相关手册，此外，《High Performance MySQL》作者之一Peter Zaitsev 有一篇叫做“SHOW INNODB STATUS walk through”的文件专门做了较为详细的分析，大家可以通过访问 http://www.mysqlperformanceblog.com/网址去了解。

当然，如果我们总是要通过不断执行“SHOW INNODB STATUS”命令来获取这样的性能信息是在是有 些麻烦，所以Innodb 存储引擎为我们设计了一个比较奇怪的方式来持续获取该信息并输出到MySQL Error Log 中。

实现方式就是通过创建一个名为innodb_monitor，存储引擎为Innodb 的表，够奇特吧，如下： CREATE TABLE innodb_monitor(a int) ENGINE=INNODB;

当我们创建这样一个表之后，Innodb 就会每过15 秒输出一次Innodb 整体状态信息，也就是上面所 展示的信息到Error Log 中。我们可以通过删除该表停止该Monitor 功能，如下：

DROP TABLE innodb_monitor;

除此之外，我们还可以通过相同的方式打开和关闭 innodb_tablespace_monitor,innodb_lock_monitor,innodb_table_monitor这三种监控功能，各位读者 朋友可以自行尝试。

通过上面的各种监控信息的输出信息，我们可以比较详细的了解到Innodb 当前的运行状态，帮助我 们及时发现性能问题。

11.3 小结

MyISAM 和Innodb 两种存储引擎各有特点，很多使用者对这两种存储引擎各有偏好，认为某一种要 优于另外一种，实际上这是比较片面的认识。两种存储引擎各自都存在对方没有的优点，也存在自身的 缺点，我们只有充分了解了各自的优缺点之后，在实际应用环境中根据不同的需要选择不同的存储引 擎，才能将MySQL 用到最好。

此外，随着MySQL Cluster 的不断成熟，除了上面详细分析的两种存储引擎之外，实际上还有NDB Cluster 存储引擎正在被越来越多的使用，关于NDB Cluster 相关的内容，将在架构设计篇中再进行比 较详细的介绍。[/color]