MySQL性能调优与架构设计——第11章 常用存储引擎优化

第11章 常用存储引擎优化

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

当我们从“.MYI”文件中读入File Block到Key Cache中Cache Block时候，如果整个Key Cache 中已经没有空闲的Cache Block可以使用的话，将会通过MySQL实现的LRU相关算法将某些Cache Block 清除出去，让新进来的File Block有地方呆。 我们先来分析一下与MyISAM索引缓存相关的几个系统参数和状态参数： ◆ key_buffer_size，索引缓存大小； Key_Size = key_number * (key_length+4)/0.67 ◆ 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_cache_age_threshold和key_cache_division_limit 这两个参数的值，大都是使用系统的默认值。

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

MySQL官方建议在比较繁忙的系统上一般可以设置三个Key Cache： 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； 在查询方面，MyISAM存储引擎的并发并没有太大的问题，而且性能也非常的高。而且如果觉得光靠Key Cache来缓存索引还是不够快的话，我们还可以通过Query Cache功能来直接缓存Query的结果集。 1. 打开concurrent_insert的功能，提高INSERT操作和SELECT之间的并发处理，使二者尽可能并行。大部分情况下concurrent_insert的值都被设置为1，当表中没有删除记录留下的空余空间的时候都可以在尾部并行插入。这其实也是MyISAM的默认设置。如果我们的系统主要以写为主，尤其是有大量的INSERT的时候。为了尽可能提高INSERT的效率，我们可以将concurrent_insert设置为2，也就是告诉MyISAM，不管在表中是否有删除行留下的空余空间，都在尾部进行并发插入，使INSERT和SELECT能够互不干扰。 2. 控制写入操作的大小，尽量让每次写入操作都能够很快的完成，以防止时间过程的阻塞动作。 3. 通过牺牲读取效率来提高写入效率。为了尽可能让写入更快，可以适当调整读和写的优先级别，让写入操作的优先级高于读操作的优先级。 对于一个表级锁定的存储引擎来说，除了concurrent_insert这个比较特殊的特性之外，可以说基本上都只能是串行的写。所以虽然上面给出了三点建议，但是后面两点也只能算是优化建议，并不是真正意义上的并发优化建议。

其他可以优化的地方

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

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

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

11.2.1 Innodb 缓存相关优化

内存分配为如下几大部分： a) 系统使用，假设预留800M； b) 线程独享，约2GB = 500 * (1MB + 1MB + 1MB + 512KB + 512KB)，组成大概如下: 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_%'; 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，甚至是同一个事务中的，均设置并作为它自己的最新快照。 我想大部分人都清楚，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让系统的整体性能尽可能的高。 在Innodb上面出了聚族索引之外的索引被成为secondary index，每个secondary index上都会包 含有聚族索引的索引键信息，方便通过其他索引查找数据的时候能够更快的定位数据位置所在。

当然，聚族索引也并不是只有好处没有任何问题，要不然其他所有数据库早就大力推广了。聚族索引的最大问题就是当索引键被更新的时候，所带来的成本并不仅仅只是索引数据可能会需要移动，而是相关的所有记录的数据都需要移动。所以，为了性能考虑，我们应该尽可能不要更新Innodb的主键值。

Page *************************** 1. row ***************************11.2.4 Innodb 其他优化

除了上面这些可以优化的地方之外，实际上Innodb还有其他一些可能影响到性能的参数设置： ◆ Innodb_flush_method innodb_thread_concurrency参数的设置范围是0～1000，但是在MySQL5.0.19之前的版本，只要该值超过20，Innodb就会认为不需要对并发线程数做任何限制，也就是说Innodb不会再进行并行线程的数目检查。同样，我们也可以通过设置为0来禁用并行线程检查，完全让Innodb自己根据实际需要创建并行线程，而且在不少场景下设置为0还是一个非常不错的选择，尤其是当系统写IO压力较大的时候。 总的来说，innodb_thread_concurrency参数的设置并没有一个很好的规则来判断什么场景该设置多大，完全需要通过不断的调整尝试，寻找出适合自己应用的设置。

◆ autocommit 设置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◆ 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”的文件专门做了较为详细的分析，大家可以通过访问 MyISAM 和 Innodb 两种存储引擎各有特点，很多使用者对这两种存储引擎各有偏好，认为某一种要优于另外一种，实际上这是比较片面的认识。两种存储引擎各自都存在对方没有的优点，也存在自身的缺点，我们只有充分了解了各自的优缺点之后，在实际应用环境中根据不同的需要选择不同的存储引擎，才能将 MySQL 用到最好。

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

摘自：《MySQL性能调优与架构设计》简朝阳

转载请注明出处：

作者：JesseLZJ

海外公司注册、海外银行开户、跨境平台代入驻、VAT、EPR等知识和在线办理：https://www.xlkjsw.com

原标题：MySQL性能调优与架构设计——第11章 常用存储引擎优化

关键词：MYSQL