第 11 章常用存储引擎优化

最新推荐文章于 2024-11-13 17:54:30 发布

dengkuituo0680

最新推荐文章于 2024-11-13 17:54:30 发布

阅读量147

点赞数

文章标签：运维数据库数据结构与算法

原文链接：http://www.cnblogs.com/crazylqy/p/4065348.html

版权

前言：

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

11.1 MyISAM存储引擎优化

我们知道，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完全一样的。如下图所示：

当我们从“.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 Innodb 存储引擎优化

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在事务隔离级别方面支持的信息如下：

READ UNCOMMITTED

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

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（幻读）。

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的可能性。

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 相关的内容，将在架构设计篇中再进行比较详细的介绍。

转载于:https://www.cnblogs.com/crazylqy/p/4065348.html