mysql存储引擎对应数据结构（块存储设备上的MySQL数据库性能优化方案）

mysql存储引擎对应数据结构（块存储设备上的MySQL数据库性能优化方案）上图中笔者只画出了InnoDB数据库引擎在insert/update一个事务的过程中所涉及的重要工作区域，InnoDB的实际工作细节要比上图所示的步骤复杂得多。上文已经说到InnoDB数据库引擎是一个支持事务的数据库引擎，那么如何解决异常崩溃情况下的数据一致性问题就是它的设计中最重要的任务之一。InnoDB数据库引擎采用日志来解决这个问题，请注意这里说的InnoDB数据库引擎日志，并不是MySQL数据库全局的二进制日志。InnoDB数据库引擎日志还有另外一个名字：重做日志（redo log），这是因为这部分日志主要的作用就是在数据库异常崩溃并重启后进行InnoDB引擎中数据的恢复。要了解MySQL数据库中的性能问题，就首先要搞清楚在客户端向MySQL数据库提交一个事务操作时后者到底做了些什么事情，以及主要是怎么做的。本节所描述的工作过程主要围绕InnoDB数据库引擎进行：BLACKHOLE

1、MySQL概述

从本文开始我们将讨论建立在块存储方案之上的关系型数据库的性能优化方案。关系型数据库的选型将以创业公司、互联网行业使用最广泛的MySQL数据为目标，但是MySQL的安装过程和基本使用方法等知识并不在我们讨论的范围内。后续几篇文章我们首先讨论影响单个MySQL节点性能的主要因素，然后介绍MySQL读写分离、数据表横纵拆分的原理和技术方案。

MySQL数据库目前已被Oracle收购，并发展处多个版本。目前使用最广泛且免费的MySQL版本是MySQL Community（社区版），另外还有三个付费的MySQL版本MySQL Standard（MySQL标准版）、MySQL Enterprise（MySQL企业版）、MySQL Cluster（MySQL集群版），这三个版本是按照CPU内核进行费用计算，并且价格由低到高。最后Oracle还提供了两个微型的MySQL版本：MySQL Classic（经典版），这个版本的MySQL只提供了MyISAM存储引擎但是安装快速，占用空间较少；MySQL Embedded（嵌入式版本），这个版本的竞争软件是SQLite。虽然社区版本是免费的并且这个版本提供的功能也没有企业级版本丰富，同样的硬件条件下单节点性能也没有企业基本版优秀。但是我们可以借助社区版本自身提供的功能和一些第三方软件配合使用，搭建起相对廉价且性能不俗的MySQL数据库集群。

2、数据库引擎的选择

MySQL数据库中最重要的一个概念就是数据库引擎，不同的数据库引擎的工作原理存在很大差异最终造成MySQL数据库服务的性能差异。例如如果数据库引擎需要支持事务，就必须满足事务的基本特性——AICD特性（AICD：原子性、隔离性、一致性和永久性。属于基础知识所以不在这里赘述），那么自然就需要一定处理机制来实现这些特性。这样做的现实效果就是导致写入同样数据量的情况下，支持事务的数据库引擎比不支持事务的数据库引擎耗费更多的时间。这里我们首先为读者列举MySQL数据库社区版中支持的数据库引擎（部分）：

MEMORY：MEMORY存储引擎将表的数据完全存放在内存中。在MySQL数据库的历史版本中和该数据库引擎类似的其它引擎是HEAP，后者曾是MySQL数据库中访问速度最快的数据库引擎。但由于这两种数据库引擎完全工作在内存中，所以如果MySQL或者服务器重新启动，数据库引擎中保存的数据将会丢失。

BLACKHOLE：中文名“黑洞”，使用BLACKHOLE数据库引擎的数据表不存储任何数据，只根据数据库操作过程记录二进制日志。它的主要作用是作为MySQL主从复制的中继器，并且可以在其上面添加业务过滤机制。

MyISAM：MyISAM数据库引擎是MySQL数据库默认的数据库引擎。MyISAM使用一种表格锁定的机制，来优化多个并发的读写操作（实际上就是使用的一种避免数据脏读的机制）。但是这种机制对存储空间的使用有一定的浪费。MyISAM还有一些有用的扩展，例如用来修复数据库文件的MYISAMCHK工具和用来恢复浪费空间的MYISAMPACK工具。本文所介绍的MySQL数据库相关技术将不涉及到这种数据库引擎。

InnoDB：InnoDB数据库引擎是在各种版本的MySQL数据库中使用最广泛的一种数据库引擎，本文后续的介绍中如果没有特别说明都默认是在说InnoDB数据库引擎。InnoDB数据库引擎使用日志机制提供事务的支持。

3、基本I/O性能

要了解MySQL数据库中的性能问题，就首先要搞清楚在客户端向MySQL数据库提交一个事务操作时后者到底做了些什么事情，以及主要是怎么做的。本节所描述的工作过程主要围绕InnoDB数据库引擎进行：

上图中笔者只画出了InnoDB数据库引擎在insert/update一个事务的过程中所涉及的重要工作区域，InnoDB的实际工作细节要比上图所示的步骤复杂得多。上文已经说到InnoDB数据库引擎是一个支持事务的数据库引擎，那么如何解决异常崩溃情况下的数据一致性问题就是它的设计中最重要的任务之一。InnoDB数据库引擎采用日志来解决这个问题，请注意这里说的InnoDB数据库引擎日志，并不是MySQL数据库全局的二进制日志。InnoDB数据库引擎日志还有另外一个名字：重做日志（redo log），这是因为这部分日志主要的作用就是在数据库异常崩溃并重启后进行InnoDB引擎中数据的恢复。

为了提高MySQL数据库的性能，InnoDB数据库引擎的数据操作过程基本上都在内存中完成，然后通过一定的策略（后文会详细介绍）将InnoDB Log Buffer内存区域中的日志数据同步到磁盘上的InnoDB File Log Group区域。InnoDB File Log Group区域主要用于存储InnoDB数据库引擎的日志文件，它由多个大小相同的日志文件构成并且这些文件都采用顺序读写。innodb_log_file_size参数将决定每个文件的大小，而innodb_log_files_in_group参数将决定整个日志组中有多少个日志文件。

当MySQL数据库完成初始化过程后这些日志文件将会按照参数的设置值，在磁盘上预占一个连续的磁盘空间。这样做的现象就是虽然数据库中还没有任何数据，但是日志文件的总大小就已经是 innodb_log_file_size * innodb_log_files_in_group所得到的数值了：

# InnoDB数据库引擎 日志文件示例 .... total 1.0G -rw-rw---- 1 mysql mysql 500M May 4 06:09 ib_logfile0 -rw-rw---- 1 mysql mysql 500M May 4 06:09 ib_logfile1 ....

这样做的目的是保证了后续同步日志数据的操作都是顺序写，而不是随机写。当日志数据写到最后一个文件的末尾时，下一条日志数据又会重新从第一个日志文件的开始位置进行写入。

3-1、I/O 性能问题的产生

InnoDB Log Buffer内存空间中的四个标识指针是InnoDB数据库引擎日志处理部分最重要元素，它们分别是：Log sequence、Log flushed、Pages flushed和Last checkpoint，这四个标识涉及到InnoDB在崩溃重启时不同的数据恢复策略，以及I/O性能优化中的关键原理。这四个标识实际上是四个数值它们共享一个数值池（名叫LSN，日志序列号，其总长度是64位无符号整数），代表当前InnoDB对事务操作的处理状态。并且它们数值有以下特点：

Log sequence >= Log flushed >= Pages flushed >= Last checkpoint

每当InnoDB接收到一个完整数据库insert/update请求事务后，就会创建一个新的LSN。新的LSN = 旧的LSN 本次写入的日志大小。这条最新的日志将会使用Log sequence进行标记，并且如果出现接收到多个事务请求的情况下，InnoDB也会按照一个既定的顺序对这些日志进行排序，然后依次生成新的LSN。这一步骤是完全在内存中进行的，所以不存在I/O性能问题。

接下来Mysql就会开始执行这个事务中的各种细节操作。InnoDB数据库引擎专门有一个InnoDB Buffer Pool内存空间用来进行数据更改或数据新增。其大小由innodb_buffer_pool_size参数控制，其数据来源于innoDB data file并且以Page的形式存在于InnoDB Buffer Pool中。当日志中有insert操作时则生成新的Page；当日志中有update操作时，InnoDB会检查该数据是否已经存在于Page Cache中，如果存在（命中）就直接更新这个Page Cache中的内容，如果不存在（未命中）就会继续从InnoDB data file中读取原始数据到InnoDB Buffer Pool中然后再更新。这里要注意几个问题：

还记得我们在讨论磁盘设备时提到的“预读”技术吗？这个技术的思路是，如果某个区域的数据被读取和使用那么在不久的将来与其相邻的区域也将会被读取和使用。所以为了提高读取效率，磁盘控制芯片会将磁盘上目标块和其相邻的若干块一起读取出来。InnoDB数据库引擎同样使用了这个思路，即读取某个Page时将会同时读取临近的Page，但是是否能起到提到I/O性能的目的还是要分不同的运行环境（后文进行说明）。

当InnoDB完成InnoDB Buffer Pool中的数据操作后，更改后数据所涉及到的Page将和此时存储在磁盘上的数据不一样，这样的Page称为脏页。如何控制脏页将是保持数据一致性的关键，InnoDB数据库引擎的做法是首先向InnoDB File Log Group日志文件中写入这个事务的日志信息。这里的写入策略由三种，通过innodb_flush_log_at_trx_commit参数可以进行控制：

innodb_flush_log_at_trx_commit = 0时，InnoDB将按照1秒钟为单位向磁盘写入这个阶段所有已完成的事务日志信息。这里的写入成功并不是说写入到Linux操作系统的Page Cache中就算成功，而是需要等待操作系统真正写到了物理磁盘上的通知（具体请参见之前讲解文件系统的文章）。这意味着即使InnoDB Buffer Pool中的数据操作是成功的，但是一旦数据库系统异常崩溃，那么业务系统将会丢失前1秒内写入的数据：因为没有磁盘介质上的日志就无法在异常重启后恢复数据信息。

innodb_flush_log_at_trx_commit = 1时，InnoDB按照完成一个日志操作就向磁盘写入事务日志信息的方式来工作（执行一个事务就写入一个事务日志）。同样，这里的写入成功同样是要等待操作系统返回真正写入了物理磁盘的通知。

innodb_flush_log_at_trx_commit = 2时，InnoDB按照完成一个日志操作就向磁盘写入日志信息的方式来工作。但是，这种工作模式下InnoDB不会等待操作系统返回物理磁盘上写入成功的通知，就会继续工作。实际上这个时候，数据一般还存在于Linux操作系统的cache memory区块中，所以这种模式下最好使用带有日志功能的文件系统，并且确认开启了文件系统的日志功能。

InnoDB数据库引擎在这一步骤的最后一个动作是更改Log flushed标识指针值为当前最后完成刷新动作的事务日志LSN值。实际上执行完这个步骤，一个事务处理操作才算真正成功。

但是涉及数据变动的脏页还没有更新到磁盘上，为什么事物的处理就可以算作成功了呢？这是因为即使这个时候数据库异常崩溃了，就凭存储在磁盘上的完整日志我们也可以重做数据。好吧，最好还是要同步脏页是吧。在第三个步骤InnoDB数据库引擎将会把最近Log flush时所涉及到的脏页（最旧脏页）更新到磁盘上。当完成脏页向磁盘的同步操作后，InnoDB数据库引擎将会更新Pages flushed标识点的LSN值，表示这个LSN值所代表的事务（以及之前的事务）都已经完成了内存和磁盘上的数据同步动作。当InnoDB数据库引擎进行脏页更新时，将会按照一定的周期策略批量提交脏页到Linux操作系统的cache memory区块中。每一次批量提交的脏页数量由innodb_io_capacity参数决定。

不同版本InnoDB数据库引擎支持的pages flush策略是不一样的，但最基本的规则没有变化，就是周期性刷新。从Mysql version 5.6开始InnoDB数据库引擎向管理者提供了一个innodb_adaptive_flushing参数，当这个参数设置为“no”时InnoDB数据库引擎将检测脏页在InnoDB Buffer Pool中的比例，以及即时I/O状态等情况来决定pages flush的周期。如果脏页在InnoDB Buffer Pool中的比例达到了由innodb_max_dirty_pages_pct（默认为75）参数设置的百分比阀值，这时InnoDB数据库引擎将按照innodb_io_capacity_max（默认值2000）参数设置的数量将这写脏页一起同步到磁盘。

当磁盘I/O性能不足且innodb_io_capacity设置过大时，会导致产生较长的I/O队列造成I/O请求阻塞，一旦累积到innodb_max_dirty_pages_pct阀值，又会产生更长的I/O阻塞队列；反之则会造成物理服务器的I/O性能没有被去完全使用。所以innodb_io_capacity的设置非常重要，特别是当读者在硬件层采用SSD固态硬盘和高速磁盘阵列时。

Checkpoint是InnoDB数据库引擎中最后一个标识点。这个标识点代表着当数据库异常崩溃重启后，小于或者等于这个标识点LSN值的所有日志信息、数据信息都无需进行重做检查。而LSN值大于Checkpoint的所有事务都需要重做，只是重做策略将视LSN值所在标识区域的不同而不同：

当代表事务的LSN数值在Log sequence——Log flushed范围内时（不包括Log flushed），说明在数据库崩溃时内存中的事务并没有处理完，这部分事务操作将在恢复时被丢弃。

当代表事务的LSN数值在Log flushed——Pages flushed范围内时（不包括Pages flushed），说明数据库崩溃时磁盘上已经拥有这些事务完整的日志记录。InnoDB数据库引擎将读取这些日志数据，并继续执行下去，直到代表这些事务的LSN值被标记为Checkpoint（或者小于Checkpoint标识的LSN值）。这里要注意，在数据库崩溃时处于这个范围内的某些事务可能已经完成了一部分的数据同步动作，但是肯定是不完整的。所以即使是这样的事务也要重新进行磁盘同步，才能保证数据的一致性。

实际上在MySQL version 5.5的早期版本，InnoDB数据库引擎中只有三个标识：Log sequence、Log flushed和Checkpoint。也就是说当脏页成功同步到磁盘后，就会直接更新Checkpoint标识的LSN值。后续版本的MySQL数据库增加了Pages flushed标识点，这样做的目的是保证Checkpoint和Pages flush的更新可以拥有独立的周期，从而降低其带来的性能消耗。

3-2、I/O 性能问题要点

Log flush和Pages flush

从上一小节的描述中，我们大致知道了在InnoDB数据库引擎中一个事务的处理过程中有两个步骤存在I/O操作：Log flush和Pages flush。

Log flush的过程是将完成的事务日志写入到日志文件中，由于InnoDB数据库引擎中日志文件的组织方式，所以Log flush中对磁盘的操作是顺序写。并且技术团队还可以通过innodb_flush_log_at_trx_commit参数来调整InnoDB Log Buffer到InnoDB File Log Group的同步策略，这有助于进一步提高Log flush性能。这也就是为什么实际环境中往往将MySQL数据库（大部分关系型数据库都适用）直接建立在块存储方案上，而不是建立在文件存储方案或者对象存储方案上的原因。

Pages flush的过程就没有那么幸运了，InnoDB数据库引擎不可能事先知道数据库会存放哪些数据，也不可能知道下次的update操作和select操作的目标数据存放在哪个区域。所以InnoDB数据库引擎针对Page的读取和更新都只能基于随机读写。那么Pages flush过程就需要在如何保持I/O性能这问题上想更多的解决办法。

例如在读取Page时，采用“预读”思路将目标Page所临近的Page一起读取出来；在写入Page时将目标Page所临近的Page一起写入（“临近写”）。“预读”策略可以通过innodb_read_ahead_threshold参数进行设置，并通过read thread完成，另外 innodb_flush_neighors参数可以控制是否开启“临近写”策略。总的来说“预读”/“临近写”在默认情况下都是开启的，但“预读”/“临近写”思路本身就需要一定的准确性，低命中率的“预读”反而会降低InnoDB的I/O性能。还有一种“随机预读”，它在MySQL version 5.6版本中默认就是关闭的，并且在随后的版本中将会慢慢废除，所以这里就不再介绍了。

例如将向磁盘提交Page的动作设计为周期性且批量进行，并且始终保持InnoDB Buffer Pool内存区域的脏页（Dirty Page）在一定的比例，这些策略主要由innodb_io_capacity、innodb_max_dirty_pages_pct、innodb_io_capacity_max等参数控制。

例如通过调整Innodb_Buffer_Pool_size参数获得更大的InnoDB Buffer Pool内存区域，存储更多的Page。实际上Innodb Buffer Pool区域不仅包括我们已经介绍的Page Cache数据部分，还包括其它的数据区块。例如为了快速定位B 树索引的Hash Index结构。调整Innodb_Buffer_Pool_size参数将会使这些数据区域都享受到内存容量带来的优势——至少不会频繁地发生内容空间的强制清理。

基础硬件条件

按照本专题之前文章介绍的块存储方案来看（《架构设计：系统存储（1）——块存储方案（1）》、《架构设计：系统存储（2）——块存储方案（2）》），如果存储MySQL数据的底层硬件介质就只是一块机械磁盘，那么无论怎样优化MySQL的其它各参数，MySQL实际对磁盘的顺序I/O速度理论上也只有100MB/S左右。这还是不计算硬件层校验、不计算不同文件系统处理耗时等等时间，所以实际I/O速度只会更慢。另外如果采用单块机械磁盘存储MySQL的数据，那么磁盘空间的扩容也是一个问题。目前市场上能买到的容量最大的单块机械磁盘，它的存储空间也只有10TB。当这部分容量使用完后想要进行扩容就基本上是就一个不可能完成的任务了。最后这种存储方式还有安全性的问题，单块机械磁盘在持续的高I/O环境下是很容易损坏的，只要是有一定资金支持的公司，机械磁盘本身就看做耗材。

所以即使是初创型公司的线上生产环境，本文也不推荐使用单块机械磁盘存储任何需要持久保存的业务数据。如果是因为资金问题，则推荐使用一些云服务商提供的现成PaaS环境，原因是这些PaaS

环境本身就支持数据恢复功能，且利用云服务商已经建设好的价格不菲的硬件/软件环境，MySQL数据库的I/O性能和计算性能暂时还不会成为业务系统的瓶颈。

3-3、突破I/O性能

为了解决上一节中提到的I/O性能问题，本文这里基于之前介绍的块存储方案的知识，列出这个问题的几种解决方案。除了根据I/O吞吐量要求对MySQL数据库特别是InnoDB引擎的配置参数进行更改以外，本文提到的硬件层解决方法所需要花费的资金和能够得到的I/O性能和扩展能力基本上成正比。

3-3-1、对MySQL中的I/O相关参数进行调整

上一节我们已经对InnoDB数据库引擎（以下简称InnoDB引擎）进行事务操作时的I/O过程进行了简单说明，主要介绍了Log flush和Pages flush两个过程。如果我们需要挖掘正式生产环境上MySQL数据库服务的性能潜力，那么对MySQL数据库服务中的默认参数进行更改就是必须要做的事情。在进行配置修改之前我们先来看看如何查看当前MySQL数据库特别是InnoDB引擎的工作状态：

# 通过执行以下命令，我们可以查看当前InnoDB引擎的工作状态

show engine innodb status;

执行后可以得到类似如下的执行结果（已省去一部分与本文没有涉及到的知识点所相关的状态描述信息）：

...... # master thread是InnoDB引擎中优先级最高的线程 # 这个线程存在的主要作用是控制InnoDB中各种I/O操作的策略，驱动其它read/write thread # 并根据当前的InnoDB的InnoDB Log Buffer、InnoDB Buffer Pool区域状态和配置参数决定处理逻辑 ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active 0 srv_shutdown 79085 srv_idle srv_master_thread log flush and writes: 79085 ...... # 这些是负责进行磁盘读写的I/O线程，日志和数据Page的读写操作最终都是靠它们完成 # 它们的数量可以通过innodb_write_io_threads参数和innodb_read_io_threads参数进行设置 -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) ...... I/O thread 8 state: waiting for completed aio requests (read thread) I/O thread 9 state: waiting for completed aio requests (read thread) I/O thread 10 state: waiting for completed aio requests (write thread) ...... I/O thread 13 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0 0 0 0 0 0 0 0] aio writes: 0 [0 0 0 0] ibuf aio reads: 0 log i/o's: 0 sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 524 OS file reads 5 OS file writes 5 OS fsyncs 0.00 reads/s 0 avg bytes/read 0.00 writes/s 0.00 fsyncs/s ...... # 这些信息反馈了InnoDB引擎中的日志标识 --- LOG --- # 当前生成的最大日志LSN值 Log sequence number 9902833 # 当前已完成日志信息向磁盘同步的最大日志LSN值 Log flushed up to 9902833 # 当前已完成数据信息向磁盘同步的最大日志LSN值 Pages flushed up to 9902833 # 当前已检查点同步的最大日志LSN值。小于和等于这是LSN值的日志在异常重启后都无需进行“重做”。 Last checkpoint at 9902833 # 当前InnoDB中待写的日志操作、待写的检查点操作 0 pending log writes 0 pending chkp writes 8 log i/o's done 0.00 log i/o's/second # 这些信息反馈了InnoDB引擎中Buffer Pool内存空间的状态 ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 4395630592; in additional pool allocated 0 Dictionary memory allocated 99235 # 当前Buffer Pool的大小 Buffer pool size 262136 # 当前Buffer Pool的可用大小 Free buffers 261619 # Page数量 Database pages 506 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0 flush list 0 single page 0 Pages made young 0 not young 0 0.00 youngs/s 0.00 non-youngs/s Pages read 506 created 0 written 1 0.00 reads/s 0.00 creates/s 0.00 writes/s No buffer pool page gets since the last printout # 状态信息的这行反馈了Page的预读信息，以及预读信息的未命中剔除情况 Pages read ahead 0.00/s evicted without access 0.00/s Random read ahead 0.00/s LRU len: 506 unzip_LRU len: 0 I/O sum[0]:cur[0] unzip sum[0]:cur[0] ......

对于上一小节提到的重要参数信息，可以采用如下的设定规则：

innodb_log_file_size：单个日志文件的大小不宜过小，例如设置为500MB。由于InnoBD引擎对日志文件采用顺序写的操作方式，所以不必担心日志文件的操作消耗比数据文件操作更多的性能。

innodb_log_files_in_group：该参数控制了文件组中日志文件的总数。设置为2-5的范围都不会有太大影响。更重要的是读者应该清楚innodb_log_file_size * innodb_log_files_in_group就是InnoDB引擎在磁盘上可用日志空间的总大小。

innodb_log_buffer_size：这个参数决定了InnoDB引擎可使用的日志内存空间。只要没有类似插入blob类型数据的操作（也不建议有这样的操作），这个内存空间都不需要设置得太大。5MB-10MB是一个推荐的设置值，不过这个参数还是要和innodb_flush_log_at_trx_commit参数配合使用。

innodb_flush_log_at_trx_commit：该参数可以说是InnoDB引擎日志操作策略部分最重要的设置参数之一。我们已经介绍过该参数的三个值和它们代表的策略意义。如果您将innodb_flush_log_at_trx_commit设置为0，代表着InnoDB引擎将会按照1秒钟的周期进行日志从内存到磁盘的同步。这时innodb_log_buffer_size的值就不能过小，因为在一个同步周期内如果待刷新的日志超过了innodb_log_buffer_size设置的大小，InnoDB就会强制执行同步操作。如果您的Linux操作系统使用的是带有日志功能的文件系统并且日志功能是开启的，那么还是建议将该参数设置为2。

innodb_buffer_pool_size：这个参数调整分配给InnoDB引擎使用的可用数据内存区域的大小。实际上这个数据区域不止包括了本文中一直强调的Page Cache区域，它还有很多数据区域。例如InnoDB中用来进行查询排序的Sort Buffer区域。建议的设置大小是MySQL数据库服务所在物理服务器上总内存的60%——80%（文件系统的Cache Memory/Buffer Memory等其它程序还要使用）。8GB的物理服务器可设置6GB的InnoDB Buffer Pool可用内存区域。注意，当MySQL数据库启动时并不是立刻就会占据所有数据区域。

innodb_buffer_pool_instances：本小节和本文中多处位置都提及到innodb_buffer_pool_size参数以及它的含义。这个参数值在生产环境下一般设置得都比较大（例如4GB、8GB、12GB、24GB等等）。但是由于脏数据刷盘的周期性，在I/O性能强劲的物理机器上可能就会存在I/O间歇性低谷。为了将I/O操作一直保持在一定的工作效能上，也为了发挥CPU的计算性能，InnoDB引擎允许将innodb_buffer_pool划分为多个独立的运行实例，当InnoDB需要读取新的Page时，它们会按照一定的算法被分配到某个独立运行的buffer pool instance中。这些buffer pool instance有各自独立的LRU算法队列、独立计算脏页比例，并且独立进行脏页刷新。innodb_buffer_pool_instances参数在具有较高I/O性能并且具有较大innodb_buffer_pool_size设定值的物理设备上能够对I/O性能产生非常明显的影响。如果您采用的是固态磁盘或者磁盘阵列作为MySQL服务器的硬件层存储介质，那么建议1-2GB的innodb_buffer_pool就分配一个独立的运行实例（这样算下来12GB的buffer pool可以设置6-12个运行实例，注意进行生产环境下的实测调整哦^-^）。但如果您只是使用的机械磁盘又或者innodb_buffer_pool_size的值并不大，那么将innodb_buffer_pool_instances参数设置为1就可以了。

innodb_io_capacity：该参数控制着InnoDB Buffer Pool数据内存区域进行磁盘同步时每次可以同步的脏页数量。在磁盘I/O性能不足时，如果innodb_io_capacity参数值过大就会造成I/O阻塞，并且造成InnoDB引擎性能较大的降低。但如果您使用的是固态硬盘或者RAID磁盘阵列 就可以将innodb_io_capacity参数默认的200设置大一些，例如设置成500——800）。

innodb_adaptive_flushing：该参数一定要打开，保证脏页的同步周期由InnoDB引擎根据实时I/O性能情况自行控制同步频率（实际上只有两种频率：1秒或者10秒）。

innodb_max_dirty_pages_pct：该参数默认为75，一般情况下无需更改。另外innodb_io_capacity_max参数表示当脏页数量在InnoDB Buffer Pool内存中的比例超过了innodb_max_dirty_pages_pct参数设置的上限后，就按照innodb_io_capacity_max设置的脏页数量强制进行脏页的刷新（建议采用默认值即可）。但是设想一下这个问题：什么情况下最可能使脏页在内存中的占比超过上限呢？当然是InnoDB引擎的事务不断快速执行，并且I/O性能又不足以快速完成同步。这时InnoDB引擎将停止事务的执行，并且进行强制刷新。所以，当问题真正发生时innodb_io_capacity_max参数设置得再大也不可能解决I/O拥堵的问题，反而可能使问题更严重。

3-3-2、对MySQL中的其它参数进行调整

innodb_page_size：该参数决定了InnoDB引擎中每一页的大小。每一个page包含多条row数据，更大的page size意味着内存中存储的每页信息有更多的数据条数。由于文件系统和底层硬件设置的结构，所以该值都为4KB的整数倍（默认值为16KB，可选值为4KB、8KB、16KB）。注意如果您需要更改这个参数值，那么就必须在MySQL数据库初始化启动时，就加入到my.cnf配置文件中。否则一旦创建了用户数据表，再对这个参数进行修改，MySQL数据库就会报错。

innodb_read_io_threads：该参数设置InnoDB数据库中的负责从磁盘上读取数据的线程数量，另外这些线程还负责在预读选项开启时承担起预读的工作任务。innodb_read_io_threads的建议值为CPU的内核数量。

innodb_write_io_threads：该参数设置InnoDB数据库中负责将脏页同步到磁盘上的线程数量。innodb_write_io_threads的建议值为CPU的内核数量。

innodb_read_ahead_threshold：该参数表示InnoDB引擎中的顺序预读阀值。在buffer pool中的page也有一个组织结构：64个page组成一个extent结构。当InnoDB发现在一个extent结构中**已经连续读取**N个page，那么InnoDB会接着将另外64 - N个后续的page读入到buffer pool中。顺序预读在“连续读”性能较高的硬件设备上，对性能的影响非常小。所以如果读者使用了I/O性能比较强劲的固态磁盘环境或者磁盘阵列环境，则建议直接关闭该功能（设置为0即可）。

innodb_random_read_ahead：该参数表示是否开启随机预读，默认是关闭的。

innodb_flush_neighbors：既然InnoDB引擎提供Page的预读功能，当然就提供预写功能。该参数表示当Buffer Pool中的脏页被同步到磁盘时，是否一起刷新和这个脏页临近的页信息。该参数在I/O性能比较强劲的固态磁盘环境或者磁盘阵列环境下，对性能提升并不明显。所以建议在这样的情况下直接关闭这个功能（设置为0即可）。

sort_buffer_size：后文介绍数据库查询优化时会讨论到这个参数。该参数对数据库引擎的查询性能，特别是有对结果进行排序要求的查询性能影响非常大。

join_buffer_size：后文介绍数据库查询优化时会讨论到这个参数。该参数对数据库引擎的查询性能，特别是有各种join连接要求的查询性能影响非常大。

binlog_cache_size：在MySQL数据库中处理InnoDB层存在“重做日志”以外，在数据库管理层还有一个独立工作的二进制日志模块。这个日志模块的工作方式和“重做日志”的工作方式相似，它们采用的办法都是在内存中进行日志数据变更，然后再按照一定的策略周期性/直接同步到磁盘上。binlog_cache_size参数设置的就是可供二进制日志在内存中进行暂存的空间大小。需要注意的是：binlog_cache_size和innodb_buffer_pool_size不同的是，前者的大小以MySQL数据库的客户端连接为单位。也就是说MySQL数据库会为两个独立的数据库客户端连接分别分配独立运行的binlog cache空间。正式环境的数据库中为每一个数据库连接设置的binlog cache空间不需要太大，当然这还要考虑实际的客户端请求频度和数据类型，还要考虑下面将介绍的sync_binlog参数设定。该参数建议的几个设置值为：32768（32KB为默认值，没有特别的要求可以保留该设置）、65536（64KB）、131072（128KB）、262144（KB）、524288（512KB）、1048576（1MB）以内。

sync_binlog：在MySQL数据库中除了InnoDB的“重做日志”需要同步以外，二进制日志也需要进行同步。这个参数是指MySQL数据库在内存中进行X次二进制日志操作后，就将内存中的二进制日志同步到磁盘中。

3-3-3、调整后的参数情况

以下是一组可以在配置有固态硬盘和磁盘阵列的正式MySQL数据库环境下使用的配置项参考，主要是为读者总结InnoDB引擎中和I/O性能相关的重要参数（只和I/O性能有关，因为后续的文章中还会介绍其他参数）。读者在进行参数配置是还是需要按照自己团队的生产环境情况，对配置项进行调整（这些参数信息都在MySQL数据库的my.cnf主配置文件中进行设置）：

# 设置单个日志文件的大小为500MB innodb_log_file_size = 524288000 # 设置日志文件组中有两个日志文件 innodb_log_files_in_group = 2 # 设置日志内存区域为10MB innodb_log_buffer_size = 10485760 # 设置日志数据同步策略为“2” innodb_flush_log_at_trx_commit = 2 # 设置buffer pool的大小为8GB innodb_buffer_pool_size = 8G # 设置正常情况下每一次脏页到磁盘的同步数量为800个 # （当然读者要确定磁盘I/O性能够用，否则改大这个值有害无益） innodb_io_capacity = 800 # 打开InnoDB提供的自监控频率 innodb_adaptive_flushing = on # 已经介绍过三次了，不再赘述 innodb_max_dirty_pages_pct = 75 innodb_io_capacity_max = 2000 # 设置InnoDB的buffer pool区域一共有8个独立运行的实例 innodb_buffer_pool_instances = 8 # 设置每一个数据页“page”的大小为16KB。为4KB的整数倍 innodb_page_size = 16384 # 设置每次二进制日志操作都提交到文件系统的Cache中 sync_binlog = 0 # 也可设置二进制日志在内存区域每操作1000次后，就进行磁盘同步 #sync_binlog = 1000 # 关闭顺序预读 innodb_read_ahead_threshold = 0 # 关闭随机读 innodb_random_read_ahead = off # 关闭临近写 innodb_flush_neighbors = 0

配置完成后可以通过以下命令查看当前MySQL数据库和InnoDB引擎中相关的配置参数（为节约篇幅，已省去一部分查询结果）：

# 查询配置参数 show variables like 'innodb%'; # 或者查询全局工作状态也行 # show global status like 'innodb%'; ...... innodb_adaptive_flushing ON innodb_adaptive_flushing_lwm 10 innodb_adaptive_hash_index ON innodb_adaptive_max_sleep_delay 150000 innodb_additional_mem_pool_size 8388608 innodb_api_disable_rowlock OFF innodb_api_enable_binlog OFF innodb_api_enable_mdl OFF innodb_buffer_pool_instances 8 innodb_buffer_pool_size 8589934592 innodb_disable_sort_file_cache OFF innodb_doublewrite ON innodb_fast_shutdown 1 innodb_file_per_table ON innodb_flush_log_at_timeout 1 innodb_flush_log_at_trx_commit 2 innodb_flush_neighbors 0 innodb_flushing_avg_loops 30 innodb_io_capacity 800 innodb_io_capacity_max 2000 innodb_lock_wait_timeout 50 innodb_locks_unsafe_for_binlog OFF innodb_log_buffer_size 10485760 innodb_log_compressed_pages ON innodb_log_file_size 524288000 innodb_log_files_in_group 2 innodb_log_group_home_dir ./ innodb_lru_scan_depth 1024 innodb_max_dirty_pages_pct 75 innodb_max_dirty_pages_pct_lwm 0 innodb_mirrored_log_groups 1 innodb_online_alter_log_max_size 134217728 innodb_open_files 2000 innodb_page_size 16384 innodb_random_read_ahead OFF innodb_read_ahead_threshold 0 innodb_read_io_threads 8 innodb_write_io_threads 4 innodb_sort_buffer_size 1048576 innodb_table_locks ON innodb_use_native_aio ON innodb_use_sys_malloc ON innodb_version 5.6.22 ......

3-3-4、提供更优异的硬件方案

使用磁盘阵列替代单块磁盘

这是最基本的硬件层改造方式，目前大多数厂商提供的PC Server基本上都集成了RAID控制器。所以这样做一般不需要额外增加购买硬件设备的费用。在MySQL官网上并没有明确推荐使用哪一种磁盘阵列模式，但是从搭建磁盘阵列支持MySQL的实际引用情况来看，更多是使用RAID 10阵列模式（另外磁盘阵列的整体性能和阵列控制芯片有很大关系）。RAID 10阵列模式可以在提升了整个系统I/O性能的基础上兼顾了存储的安全性。为了使用RAID 10磁盘阵列模式，读者至少需要为准备4块磁盘。其中2/4的磁盘容量用来存储数据冗余，另外2/4的磁盘容量用来分散存储数据。对于RAID 10磁盘阵列模式的详细工作方式介绍，读者可以参看另一篇文章（《架构设计：系统存储（2）——块存储方案（2）》）

使用固态硬盘进一步替代机械磁盘

以上解决方案中，每一个机械磁盘的I/O性能将会成为整个RAID 10磁盘阵列的性能瓶颈（不考虑阵列控制芯片的处理性能）。所以如果技术团队还有多余的资金支持那么下一步要做的就是将构成RAID 10磁盘阵列多个机械磁盘全部替换成固态磁盘。如下图所示：

进一步使用外置磁盘阵列柜 光纤结构

USB3.0接口的理论带宽只有600MB/S，而且PC Server内置的磁盘阵列控制器由于服务器内部空间的限制，也存在磁盘数量扩展困难的问题。如果读者确认生产环境的某个物理服务器将以I/O读写操作为主，且I/O性能将成为其上工作的应用软件的瓶颈。那么这时最好的硬件方案就是直接采用外置企业级磁盘阵列柜 光纤接口的方式搭建硬件层支持。

目前主流的光纤线路带宽为16Gb/s，这远远高于USB3.0 6Gb/s的理论带宽、高于SAS 12Gb/s的理论带宽。另外单个企业级磁盘阵列柜可容纳的磁盘数量就已经很高了（例如IBM Storwize V5000 单柜提供24个磁盘位，单柜支持最大72TB存储容量），并且这些企业级盘柜一般支持扩展成多柜。这两种特性有效解决了硬件层面磁盘I/O速度和容量的问题，但代价就是这些IT基础折本的价格一般比较昂贵，技术团队所在企业需要有比较宽裕的项目/产品建设预算。

4、影响SQL性能的要素

MySQL数据库的性能不止受到性能参数和底层硬件条件的影响，在这两个条件一定的情况下，开发人员对SQL语句的优化能力更能影响MySQL数据库的性能。由于MySQL中不同数据库引擎对SQL语句的处理过程不尽相同，所以对SQL语句的优化就一定要首先确定使用的数据库引擎的类型。例如MyISAM引擎中统计某一个数据表的总行数时，只需要读取出已保存好的数据总行数就OK了。但InnoDB引擎要完成这个动作，就必须进行table scan/index scan，而是否为被扫描的字段创建了索引又直接影响了扫描速度。本文我们和读者一起来讨论一下InnoDB数据引擎下SQL语句常见的工作方式和优化规则。

4-1、索引

我们都知道无论使用哪种主流的关系型数据库，为SQL查找语句所依据的字段创建索引要比不创建索引时的性能高出几个数量级（当然这也要看SELECT查询语句的具体写法）。那么为什么会出现这样的情况呢？我们又依据什么样的原理来创建数据库的索引呢？本小节将首先为读者进行原理性描述。

4-1-1、B树

同样由于本文的定位，所以我们并不会讨论怎样使用脚本创建索引等基本操作问题。而是直接进行InnoDB数据库引擎中索引原理的讲述。InnoDB中数据库字段的索引采用树结构进行组织，这种树本质上是为了解决数据检索问题的平衡N阶树，又被称为B树。B树及其变体是大学《数据结构》课程中的基础知识，本人虽然工作许多年但始终对《数据结构》这门课程中的主要知识烂熟于心，并认为它和《 离散数学》一样已经成为笔者大学时期学习过的，对笔者实际工作帮助最大的两门课程。

为了帮助读者回忆起B树及其变体的基本结构，也为了后续内容能够正常铺开。我们非常需要使用相当的篇幅对它进行介绍。那么首先使用下图回答树结构的几个基本概念：节点、深度、子树等。

B树是一颗平衡的多叉检索树，它具有以下性质：

所谓检索树是指这样的树：树中任意非叶子节点A作为根节点的子树，其左子树上节点中的元素值均小于或等于节点A中元素的值；其右子树上节点中的元素值均大于或等于节点A中元素的值。检索树又称为排序树、有序树，如果将检索树降维成表结构则同样可以使用二分查找法进行节点检索，且时间复杂度基本不变。

如果不加任何构造限制，那么在树结构中检索元素的时间复杂度可能为O(n)。这显然失去了检索树的意义，如果一颗检索树能够保证树的高度H限制在节点数N的对数阶范围内（H=O(logn)），这样的检索树就称为平衡树。在编程实践中只要保证树中任意两个子树深度差的绝对值不大于1，就可以保证前述条件成立。另外B树中对深度差绝对值做了更严格的规定，即所有叶子结点都位于同一深度。

一颗B树的非叶子节点能够最多关联的子节点数量称为阶数。B树中的阶数至少为3，因为当阶数为2时B树进行节点分裂就可能会出现某叶子节点没有任何元素的情况。

树中每个非根节点所包含的元素个数 j 满足：(N/2) - 1 <= j <= N - 1，其中N表示B树的阶数。例如阶数为3的B树每一个非叶子结点能够存储的元素个数可能为 0个、1个和2个（但0个元素没有任何检索意义，还会造成树中任意两个子树深度差的绝对值改变）。

树中一个节点可关联的子节点数量比以上文字中提到的元素最大个数多1。也就是说阶数为3的B树每个节点最多可关联3个子节点。

上图展示了一颗3阶B树，它的每个节点最多可以有3个子节点，并且每个子节点中最多有2个元素。可以观察到B树满足检索树的基本规则：凡是比给定元素值大的所有元素，都作为该元素的子元素排列在该元素的左子树上；凡是比给定元素值大的所有元素，都作为该元素的子元素排列在该元素的右子树上。这样一来开发人员就可以使用和二分查找法类似的查找方式定位要查询的元素，或者在插入一个新元素前定位到新元素将要进行插入的位置。

下图演示了在B树中依次添加元素时的分裂和节点间关联过程，这些元素的值依次增大分别是：3、5、7、9、14、13、15、16、18、22、25、31、33。在实际应用开发中，虽然我们并不能保证插入B树的元素值都是增加的，但是对B树的插入操作过程却是相同的（两者的区别只是定位的将要插入新元素的位置不一样）：

4-1-2、变体B 树

B树的优点很明显：无论进行新元素的插入定位还是进行指定元素的查找，都可以快速完成这些定位/查找动作，其查询性能的时间复杂度相当于二分查找法（O(log(n))）。但是B树的缺点也很明显，首先插入新元素时可能涉及到树深度的改变，当然这个问题可以通过增加B树的阶数来解决。也就是让每一个节点可以拥有更多的子节点，这样就可以在存储元素总量不变的情况下减少树的理论深度，从而减少发生树深度改变的情况，

另一个问题就稍微严重一些了，那就是B树并不适合管理InnoDB引擎中的数据（这个在后文会进行说明）。还好B树结构有一个变体结构，称为B 树。两者最大的区别是，后者在B树的结构基础上扩展出了一个链表存储结构，并且在树的叶子节点对非叶子结点元素进行了冗余存储。如下图所示：

B树和B 树在新元素插入、元素删除、元素查找等基本处理方式上没有太大的区别。但是B 树的两个典型的结构变动刚好可以改进树结构在InnoDB引擎中的应用：

由于B 树的叶子节点存储了非叶子节点的冗余元素，所以我们可以在非叶子节点只记录某条数据的索引信息，而在叶子节点记录具体的数据信息。那么MySQL数据库就可以在InnoDB引擎启动时就加载B 树的非叶子节点到内存特定区域，这样做的最大好处是可以在内存空间和查找速度两个维度上找到最好的平衡点。

特别注意的是，实际在InnoDB引擎中的B 树结构，其叶子节点并不是存储一行数据（要真是这样，这颗B 树不知道需要有多大。。。），而是和Data Page作为对应。在之前介绍InnoDB引擎的文章中已经说明过数据库中Page的概念。Page是InnoDB引擎中最基本的数据操作单元，无论是InnoDB从磁盘上读取数据还是改变数据，都以Page作为操作单位。同样，InnoDB引擎中的索引结构也以Page为单位在叶子节点关联具体数据。

另外B 树在最底层将所有叶子节点串成了一个链表结构（不用担心某个叶子节点没有任何元素，因为B 树遵循所有B树的基本约束），这样一来在进行数据查找时就可以使用表结构进行元素的依次查找，而无需再进行树的遍历操作。实际上在InnoDB引擎中每一个叶子节点都是一个Page信息，构成链表结构后就可以检索每一个Page的上一个Page和下一个Page信息，这恰好也是InnoDB引擎中预读功能的实现基础。

4-1-3、InnoDB中的索引类型

InnoDB数据引擎使用B 树构造索引结构，其中的索引类型依据参与检索的字段不同可以分为主索引和非主索引；依据B 树叶子节点上真实数据的组织情况又可以分为聚族索引和非聚族索引。每一个索引B 树结构都会有一个独立的存储区域来存放，并且在需要进行检索时将这个结构加载到内存区域。真实情况是InnoDB引擎会加载索引B 树结构到内存的Buffer Pool区域。

聚簇索引（聚集索引）

聚簇索引指的是这样的数据组织结构：索引B 树的每个叶子节点直接对应了真实的Data Page。并且B 树所有的叶子节点在最底层共同描述了一个可以直接进行行数据顺序扫描的Data Page结构。如下图所示：

InnoDB引擎在组织索引和数据时，就是通过聚簇索引检索具体Data Page。而聚簇索引B 树的非叶子节点一般由数据表中的主键负责构造（当然也可能不是主键，这个后文会进行说明）。

主索引（主键索引/一级索引）

基于InnoDB引擎工作的每一张数据表都需要有一个主索引，这是因为上一段文字中提到的InnoDB引擎需要使用聚簇索引查找到具体的Data Page，而工作在InnoDB引擎下的数据表有且只有主索引采用聚簇索引的方式组织数据。也就是说主索引B 树的叶子节点都对应了真实的Data Page信息。

主索引在数据表的索引列表中使用PRIMARY关键字进行标识，一般来说是数据表的主键字段（也有可能是复合主键）。如果开发人员删除了InnoDB引擎中某张数据表的主索引，那么这个数据表将自行寻找一个非空且带有唯一约束的字段作为主索引。如果还是没有找到那样的字段，InnoDB引擎将使用一个隐含字段作为主索引（ROWID）。

B 树的构造特性在这里就得到了充分利用，因为只需要将主索引B 树的非叶子节点加载到内存中。当检索请求需要读取某一个具体的Data Page时，再从磁盘上进行读取。还记得在之前的文章中提到的预读操作吗？B 树最底层叶子节点组成的链表结构，让InnoDB引擎能够轻松进行临近的Data Page的读取——如果参数设定了需要那样做的话。

非聚簇索引（非聚集索引）

非聚族索引首先也是一颗B 树，只是非聚簇索引的叶子节点不再关联具体的Data Page信息，而是关联另一个索引值。InnoDB引擎下工作的每一个数据表虽然都只有一个聚簇索引，那就是它的主索引。但是每一张数据表可以有多个非聚簇索引，而后者的叶子节点全部存储着对应的数据主键信息（或者其它可以在聚簇索引中进行检索的关键值）。

注意上图所示的B 树的叶子节点不再关联具体的Data Page信息，而只是关联了构成聚簇索引非叶子结点的主键信息。

非主索引（辅助索引/二级索引）

数据表索引列表中除去主索引以外的其它索引都称为非主索引。非主索引都是使用非聚簇索引方式组织数据，也就是说它们实际上是对聚簇索引进行检索的数据结构依据。

例如当开发人员创建了一个以字段A作为索引的非聚簇索引结构，并且在SQL中使用字段A作为查询条件执行检索时。InnoDB会首先使用非聚簇索引检索出对应的主键信息，然后再通过主索引检索这个主键对应的数据。

关于索引和执行计划调整的介绍，将在下一篇文章中提到。

4-2、Query Cache

为了加快查询语句的执行性能，从MySQL早期的版本开始就提供了一种名叫Query Cache的缓存技术。这个缓存技术和技术人员使用哪种数据库引擎无关，它完全独立工作于各种数据库引擎的上层，并使用独立的内存区域。

Query Cache的工作原理描述起来也比较简单，当某一个客户端连接（session）进行SQL查询并得到返回信息时，MySQL数据库除了将查询结果返回给客户端外，还在特定的内存区域缓存这条SQL查询语句的结果，以便包括这个客户端在内的所有客户的再次执行相同查询请求时，MySQL能够直接从缓存区返回结果。这里有两个关键点需要明确：

什么是“相同的查询语句”？Query Cache使用K-V结构对查询结果进行记录，其中的K就是查询语句本身（当然还要附加上诸如database name这样的关键信息）。所以“select * from A”和“select * from a”这样的语句将被看成是两条不同的查询语句。“select * from A”和“select * from A”也将被视为两条不同的查询语句（空格数量不一样）。

怎样避免“缓存数据不一致”的问题？

一旦被缓存的查询结果所涉及的数据表发生了“写”操作，那么无论“写”操作本身是否影响到被缓存的数据，涉及到数据表的所有缓存数据都将被清除。这种简单暴力的处理方式，不仅绕过了数据一致性问题，还节约了宝贵的时间——因为在大多数数据库应用中，读请求是远远多余写请求的。如果您所在团队开发的应用会使MySQL数据库读写请求比例达到或查过1：1，那么使用Query Cache就没有什么意义，建议直接关闭。

4-2-1、Query Cache基本设置

您可以通过“show variables like ‘query_cache%’”语句查询当前为MySQL服务设定的和Query Cache相关的参数值。

# show variables like 'query_cache%'; query_cache_limit 1048576 query_cache_min_res_unit 4096 query_cache_size 0 query_cache_type OFF query_cache_wlock_invalidate OFF

这些设置参数的定义可以简单描述如下：

query_cache_size：这是参数设置了MySQL服务中Query Cache的全局大小，单位为byte。该参数在MySQL version 5.5及以后版本中的默认值都为0，也就是说如果在这些版本中要使用Query Cache则需要自己设置Query Cache的大小。query_cache_size不应该这是太大（最大支持256M），这是因为当某张数据表进行写操作时，MySQL服务需要从Query Cache抹去的相关数据也就越多，反而会增加耗时。query_cache_size设置为33554432（32M）是比较好的。

query_cache_limit：该参数设置单条查询语句允许缓存到Query Cache中的最大结果容量值，1048576（1M）是它的默认值。也就是说如果查询语句返回的查询结果集合大于1M，则这个查询结果集合不会缓存到Query Cache区域。

query_cache_min_res_unit：该参数设置Query Cache每次分配内存的最小大小，默认值为4096（4KB）。

query_cache_type：注意，既是单独设置query_cache_size为0，也不会使MySQL服务关闭Query Cache功能。一定要设置query_cache_type参数为0（OFF）才行。另外当该参数值为1（ON）时，代表开启Query Cache功能，此时必须在SQL查询语句中明确使用SQL_NO_CACHE，才能关闭这条查询语句的Query Cache功能；该参数的值还可以为2（DEMAND），此时只有当SQL查询语句明确使用SQL_CACHE关键字，才能让这条查询语句使用Query Cache功能。

query_cache_wlock_invalidate：该参数设置Query Cache中数据的失效时刻（非常重要）。如果该值为1（ON），则在数据表被写锁定的同时该表中数据涉及的所有Query Cache都将失效；如果该值为0（OFF），则表示在数据表写锁定的同时，Query Cache中该数据表的相关数据都还继续有效。

您还可以通过“show status like ‘Qcache%’”语句查询当前MySQL服务中Query Cache的工作状态

# show status like 'Qcache%' Qcache_free_blocks 0 Qcache_free_memory 0 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 0 Qcache_queries_in_cache 0 Qcache_total_blocks 0

各位读者看到以上示例中所有和Query Cache相关的状态值都为0，这是因为在演示的MySQL服务中默认关闭了Query Cache功能（主要是设置了query_cache_type的值为0）。不过以上展示的Query Cache状态信息中一些状态项还是要进行说明：

Qcache_free_memory：该指标说明了当前Query Cache专用内存区域还有多少剩余内存。

Qcache_hits：该指标说明当前Query Cache从MySQL服务启动到现在的命中次数。

Qcache_lowmem_prunes：该指标说明因为Query Cache内存不足而被清除的查询结果数量。

其它的状态项可参见MySQL的官方文档《The MySQL Query Cache》

4-2-2、Query Cache的局限性和使用建议

为什么MySQL Version 5.5及以后的版本会默认关闭Query Cache功能呢？这至少说明官方并不建议在任何场景下都是用Query Cache功能，甚至是大多数场景下。首先，Query Cache存在功能局限性：

早期版本（Version 5.1）的Query Cache功能并不支持变量绑定，也就是说类似“select * from A where field = ?”这样的SQL查询结果不会被放入Query Cache中。

存储过程、触发器等基于数据库引擎类型工作的特定功能，如果其中使用了查询语句，这些查询语句的结果也不会放入Query Cache中。

复杂的SQL查询中，往往包含多个子句。这些子句的查询结果能够被放入Query Cache中。但是用于包含这些子句的外部查询结果却不能放入Query Cache。

以上提到的Query Cache功能局限性在每次MySQL版本升级的过程中，MySQL开发团队都逐渐进行了调整，所以这写功能性限制并不是什么太大的问题。例如以上说到的在存储过程中的SQL查询不会加入到Query Cache中，这个实际上就不是什么大问题，目前来看业务系统中业务逻辑处理部分还都是放在上层业务代码中来解决，使用复杂存储过来处理业务逻辑的情况不多见。MySQL官方默认关闭Query Cache主要还是因为Query Cache的性能局限性：

“Waiting on query cache mutex”这种错误是典型的使用Query Cache不当所引起的错误。由于Query Cache设计的暴力清除策略，导致只要有数据表进行写操作，Query Cache中和这个数据表相关的所有结果都要失效的现象。所有需要从Query Cache中读取相关数据的客户端session就要等待数据清除完毕，所以就会出现以上错误提示。

如果这时query_cache_size设置得过大，反而会加剧这个问题的严重程度。因为过大的Query Cache区域意味着可能存储了和这个被写操作关联的数据表的更多查询结果集，也就需要更多时间去清除数据。

如果这张数据表又是写密集度非常高的数据表，那么这个问题会更加严重。因为Query Cache中相关数据会被频繁的擦除、重写。客户端session也会不停的进入锁定等待状态。

在实际业务应用中，笔者并不建议直接关闭Query Cache。而是建议在将query_cache_type参数设置为2（DEMAND）并分配不大的内存总空间（query_cache_size 设置为16MB足够了）的前提下，由业务层代码显式控制Query Cache的使用。

只有满足以下所有特点的SQL查询操作才建议显示开启Query Cache功能：写操作并不密集的数据表、读写操作比最好大于10：1（或者根据读者自己的业务特性规定的更大比值）。毕竟只有业务层才清楚哪些数据表的读写操作比大于10：1，并且写操作并不时常进行。而满足以上操作特性的数据表通常都是基础性码表：例如行政区域表、电话分区表、身份证分区表、车辆号牌表。

对于复杂的SQL查询、读写比不大的数据表、写操作频繁或者写操作并发特别大的数据表并不建议开启Query Cache功能。例如订单表、库存物品表、车辆承运表、评论信息表等业务写操作频繁的数据表。

4-3、InnoDB中的锁

虽然锁机制是InnoDB引擎中为了保证事务性而自然存在的，在索引、表结构、配置参数一定的前提下，InnoDB引擎加锁过程是一样的，所以理论上来说也就不存在“锁机制能够提升性能”这样的说法。但如果技术人员不理解InnoDB中的锁机制或者混乱、错误的索引定义和同样混乱的SQL写操作语句共同作用，那么导致死锁出现的可能性就越大，需要InnoDB进行死锁检测的情况就越多，最终导致不必要的性能浪费甚至事务执行失败。所以理解InnoDB引擎中的锁机制可以帮助我们在高并发系统中尽可能不让锁和死锁成为数据库服务的一个性能瓶颈。

4-3-1、InnoDB中的锁类型

本文讲解的锁机制主要依据MySQL Version 5.6以及之前的版本（这是目前线上环境使用最多的版本），在MySQL Version 5.7以及最新的MySQL 8.0中InnoDB引擎的锁类型发生了一些变化（后文会提及），但基本思路没有变化。InnoDB引擎中的锁类型按照独占形式可以分为共享锁和排它锁（还有意向性共享锁和意向性排它锁）；按照锁定数据的范围可以分为行级锁（其它引擎中还有页级锁的定义）、间隙锁、间隙复合锁？？和表锁；为了保证锁的粒度能够至上而下传递，InnoDB中还设计有不能被用户干预的意向共享锁和意向排它锁。

共享锁（S锁）

由于InnoDB引擎支持事务，所以需要锁机制在多个事务同时工作时保证每个事务的ACID特性。共享锁的特性是多个事务可以同时为某个资源加锁后进行读操作，并且这些事务间不会出现相互等待的现象。

排它锁（X锁）

排它锁又被称为独占锁，一旦某个事务对资源加排它锁，其它事务就不能再为这个资源加共享锁或者排它锁了。一直要等待到当前的独占锁从资源上解除后，才能继续对资源进行操作。排它锁只会影响其他事务的加锁操作，也就是说如果其它事务只是使用简单的SELECT查询语句检索资源，就不会受到影响，因为这些SELECT查询语句不会试图为资源加任何锁，也就不会受资源上已有的排它锁的影响。我们可以用一张表表示排它锁和共享锁的互斥关系：

排它锁和共享锁的互斥关系

行级锁（Record lock）

行级锁是InnoDB引擎中对锁的最小支持粒度，即是指这个锁可以锁定数据表中某一个具体的数据行，锁的类型可以是排它锁也可以是共享锁。例如读者可以在两个独立事务中同时使用以下语句查询指定的行，但是两个事务并不会相互等待：

# lock in share mode 是为满足查询条件的数据行加共享锁 # 注意它和直接使用select语句的不同特性 select * from myuser where id = 6 lock in share mode;

间隙锁（GAP锁）

间隙锁只有在特定事务级别下才会使用，具体来说是“可重复读”（Repeatable Read ）这样的事务级别，这也是InnoDB引擎默认的事务级别，它的大致解释是无论在这个事务中执行多少次相同语句的当前读操作，其每次读取的记录内容都是一样的，并不受外部事务操作的影响。间隙锁主要为了防止多个事务在交叉工作的情况下，特别是同时进行数据插入的情况下出现幻读。举一个简单的例子，事务A中的操作正在执行以下update语句的操作：

...... # 事务A正在执行一个范围内数据的更新操作 # 大意是说将用户会员卡号序列大于10的所有记录中user_name字段全部更新为一个新的值 update myuser set user_name = '用户11' where user_number >= 10; ......

其中user_number带有一个索引（后续我们将讨论这个索引类型对间隙锁策略的影响），这样的检索条件很显然会涉及到一个范围的数据都将被更新（例如user_number==10、13、15、17、19、21……），于此同时有另一个事务B正在执行以下语句：

...... # 事务B正在执行一个插入操作 insert into myuser(......... 'user_number') values (......... 11); # 插入一个卡号为11的新会员，然后提交事务B ......

如果InnoDB只锁住user_number值为10的非聚簇索引和相应的聚簇索引，显然就会造成一个问题：在A事务处理过程中，突然多出了一条满足更新条件的记录。事务A会很纠结的，很尴尬的。如果读者是InnoDB引擎的开发者，您会怎么做呢？正确的做法是为满足事务A所执行检索条件的整个范围加锁，这个锁不是加在某个或某几个具体的记录上，因为那样做还是无法限制类似插入“一个卡号为11的新纪录”这样的情况，而是加在到具体索引和下一个索引之间，告诉这个索引B 树的其它使用者，包括这个索引在内的之后区域都不允许使用。这样的锁机制称为间隙锁（GAP锁）。

间隙锁和行级锁组合起来称为Next-Key Lock，实际上这两种锁一般情况下都是组合工作的。

表级锁：没有可以检索的索引，就无法使用InnoDB特定的锁。另外，索引失效InnoDB也会为整个数据表加锁。如果表级锁的性质是排它锁（实际上大多数情况是这样的锁），那么所有试图为这张数据表中任何资源加共享锁或者排它锁的事务都必须等待在数据表上的排它锁被解除后，才能继续工作。表级锁可以看作基于InnoDB引擎工作的数据表的最悲观锁，它是InnoDB引擎为了保持事务特性的一场豪赌。例如我们有如下的数据表结构：

uid（PK） varchar user_name varchar user_sex int

这张数据表中只有一个由uid字段构成的主索引。接着两个事务同时执行以下语句：

begin; select * from t_user where uid = 2 lock in share mode; #都先不执行commit，以便观察现象 #commit;

这里的select查询虽然使用的检索依据是uid，但是设置检索条件时uid的varchar类型却被错误的使用成了int类型。那么数据表将不再使用索引进行检索，转而进行全表扫秒。这是一种典型的索引失效情况，最终读者观察到的现象是，在执行以上同一查询语句的两个事务中，有一个返回了查询结果，但是另外一个一直为等待状态。以上的小例子也可以让读者看到，科学管理索引在InnoDB引擎中是何等重要。本文后续部分将向读者介绍表级锁的实质结构。

意向共享锁（IS锁）和意向排它锁（IX锁）

为了在某一个具体索引上加共享锁，事务需要首先为涉及到的数据表加意向共享锁（IS锁）；为了在某一个具体所以上加排它锁，事务需要首先为涉及到的数据表加意向排它锁（IX锁）。这样InnoDB可以整体把握在并发的若干个事务中，让哪些事务优先执行更能产生好的执行效果。意向共享锁是InnoDB引擎自动控制的，开发人员无法人工干预，也不需要干预。

4-3-2、加锁过程实例

InnoDB引擎中的锁机制基于索引才能工作。对数据进行锁定时并不是真的锁定数据本身，而是对数据涉及的聚集索引和非聚集索引进行锁定。在之前的文章中我们已经介绍到，InnoDB引擎中的索引按照B 树的结构进行组织，那么加锁的过程很明显就是在对应的B 树上进行加锁位置检索和进行标记的过程。并且InnoDB引擎中的非聚簇索引最终都要依靠聚簇索引才能找到具体的数据记录位置，所以加锁的过程都涉及到对聚簇索引进行操作。

SELECT关键字的查询操作一般情况下都不会涉及到锁的问题（这种类型的读操作称为快照读），但并不是所有的查询操作都不涉及到锁机制。只要SELECT属于某种写操作的前置子查询/检索或者开发人员显式为SELECT加锁，这些SELECT语句就涉及到锁机制——这种读操作称为当前读。而执行Update、Delete、Insert操作时，InnoDB会根据会根据操作中where检索条件所涉及的一条或者多条数据加排它锁。

为了进一步详细说明各种典型的加锁过程，本小节为读者准备了几个实例场景，并使用图文混合的方式从索引逻辑层面上进行说明。后续的几种实例场景都将以以下数据表和数据作为讲解依据：

CREATE TABLE `myuser` ( `Id` int(11) NOT NULL AUTO_INCREMENT `user_name` varchar(255) NOT NULL DEFAULT '' `usersex` int(9) NOT NULL DEFAULT '0' `user_number` int(11) NOT NULL DEFAULT '0' PRIMARY KEY (`Id`) UNIQUE KEY `number_index` (`user_number`) KEY `name_index` (`user_name`) )

这张表中有三个索引，一个是以id字段为依据的聚簇索引，一个是以user_name字段为依据的非唯一键非聚簇索引，最后一个是以user_number字段为依据的唯一键非聚簇索引。我们将在实例场景中观察唯一键索引和非唯一键索引在加锁，特别是加GAP锁的情况的不同点。这张数据表中的数据情况如下图所示：

4-3-2-1、 行锁加锁过程

首先我们演示一个工作在InnoDB引擎下的数据表只加行锁的情况。

begin; update myuser set user_name = '用户11' where id = 10; commit;

以上事务中只有一条更新操作，它直接使用聚簇索引作为检索条件。聚簇索引肯定是一个唯一键索引，所以InnoDB得出的加锁条件也就不需要考虑类似“insert into myuser(id ………) values(10 ………)”这样的字段重复情况。因为如果有事务执行了这样的语句，就会直接报错退出。那么最终的加锁结果就是：只需要在聚簇索引上加X锁。

（额~~~你要问我为什么树结构会是连续遍历的？请重读B 树的介绍）

其它事务依然可以对聚簇索引上的其它节点进行操作，例如使用update语句更新id为14的数据：

begin; update myuser set user_name = '用户1414' where id = 14; commit;

当然，由于这样的执行过程没有在X锁临近的边界加GAP锁，所以开发人员也可以使用insert语句插入一条id为11的数据：

begin; insert into myuser(id user_name usersex user_number) values (11 '用户1_1' 1 '110110110'); commit;

4-3-2-2、间隙锁加锁过程

工作在InnoDB引擎下的数据表，更多的操作过程都涉及到加间隙锁（GAP）的情况，这是因为毕竟大多数情况下我们定义和使用的索引都不是唯一键索引，都在“可重复读”的事务级别下存在“幻读”风险。请看如下事务执行过程：

begin; update myuser set usersex = 0 where user_name = '用户8' commit;

这个事务操作过程中的update语句，使用非唯一键非聚簇索引’name_index’进行检索。InnoDB引擎进行分析后发现存在幻读风险，例如可能有一个事务在同时执行以下操作：

begin; insert into myuser(id user_name usersex user_number) values (11 '用户8' 1 '110110110'); # 或者执行以下插入 # insert into myuser(id user_name usersex user_number) values (11 '用户88' 1 '110110110'); commit;

所以InnoDB需要在X锁临近的位置加GAP锁，避免幻读：

以上示意图有一个注意点，在许多技术文章中对GAP锁的讲解都是以int字段类型为基准，但是这里讲解所使用的类型是varchar。所以在加GAP锁的时候，看似’用户8’和’用户9’这两个索引节点没有中间值了。但是字符串也是可以排序的，所以’用户8’和’用户9’这两个字符串之间实际上是可以放置很多中间值的，例如’用户88’、’用户888’、’用户8888’等。

这就是为什么另外的事务执行类似”insert into myuser(id user_name usersex user_number) values (11 ’用户88’ 1 ’110110110’);”这样的语句，同样会进入等待状态：因为有GAP锁进行独占控制。

4-3-2-3、表锁加锁过程

上文已经提到，索引一旦失效InnoDB也会为整个数据表加锁。那么“为整个数据表加锁”这个动作怎么理解呢？很多技术文章在这里一般都概括为一句话“在XXX数据表上加锁”。要弄清楚表锁的加载位置，我们就需要进行实践验证。首先，为了更好的查看InnoDB引擎的工作状态和加锁状态，我们需要打开InnoDB引擎的监控功能：

# 使用以下语句开启锁监控 set GLOBAL innodb_status_output=ON; set GLOBAL innodb_status_output_locks=ON;

接下来我们就可以使用myuser数据表中没有键立索引的“usersex”字段进行加锁验证：

begin; update myuser set user_name = '用户1414' where usersex = 1; # 先不忙使用commit，以便观察锁状态 #commit;

在执行以上事务之前，myuser数据表中最新的记录情况如下图所示：

可以看到myuser数据表中一共有13条记录，其中满足“usersex = 1”的数据一共有9条记录。那么按照InnoDB引擎行锁机制来说，就应该只有这9条记录被锁定，那么是否如此呢？我们通过执行InnoDB引擎的状态监控功能来进行验证：

show engine innodb status; # 以下是执行结果（省略了一部分不相关信息） ===================================== 2016-10-06 22:22:49 2f74 INNODB MONITOR OUTPUT ===================================== ....... ------------ TRANSACTIONS ------------ Trx id counter 268113 Purge done for trx's n:o < 268113 undo n:o < 0 state: running but idle History list length 640 LIST OF TRANSACTIONS FOR EACH SESSION: ...... ---TRANSACTION 268103 ACTIVE 21 sec 2 lock struct(s) heap size 360 14 row lock(s) undo log entries 9 MySQL thread id 5 OS thread handle 0x1a3c query id 311 localhost 127.0.0.1 root cleaning up TABLE LOCK table `qiang`.`myuser` trx id 268103 lock mode IX RECORD LOCKS space id 1014 page no 3 n bits 152 index `PRIMARY` of table `qiang`.`myuser` trx id 268103 lock_mode X Record lock heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; Record lock heap no 79 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; 1: len 6; hex 000000041723; asc #;; 2: len 7; hex 2c000001e423fd; asc # ;; 3: len 8; hex e794a8e688b73130; asc 10;; 4: len 4; hex 80000000; asc ;; 5: len 4; hex 80018a92; asc ;; Record lock heap no 80 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 4; hex 8000000e; asc ;; 1: len 6; hex 000000041721; asc !;; 2: len 7; hex 2b000001db176a; asc j;; 3: len 8; hex e794a8e688b73134; asc 14;; 4: len 4; hex 80000000; asc ;; 5: len 4; hex 80022866; asc (f;; Record lock heap no 81 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 4; hex 80000012; asc ;; 1: len 6; hex 00000004171f; asc ;; 2: len 7; hex 2a000001da17b2; asc * ;; 3: len 8; hex e794a8e688b73138; asc 18;; 4: len 4; hex 80000000; asc ;; 5: len 4; hex 8002c63a; asc :;; Record lock heap no 82 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 4; hex 80000016; asc ;; 1: len 6; hex 00000004171d; asc ;; 2: len 7; hex 290000024d0237; asc ) M 7;; 3: len 8; hex e794a8e688b73232; asc 22;; 4: len 4; hex 80000000; asc ;; 5: len 4; hex 80035c3c; asc \<;; Record lock heap no 86 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000000041747; asc G;; 2: len 7; hex 41000002580110; asc A X ;; 3: len 10; hex e794a8e688b731343134; asc 1414;; 4: len 4; hex 80000001; asc ;; 5: len 4; hex 80002b67; asc g;; ...... 这里为节约篇幅，省略了6条行锁记录...... Record lock heap no 93 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 4; hex 80000008; asc ;; 1: len 6; hex 000000041747; asc G;; 2: len 7; hex 410000025802b4; asc A X ;; 3: len 10; hex e794a8e688b731343134; asc 1414;; 4: len 4; hex 80000001; asc ;; 5: len 4; hex 80015b38; asc [8;; Record lock heap no 94 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 4; hex 80000009; asc ;; 1: len 6; hex 000000041747; asc G;; 2: len 7; hex 410000025802f0; asc A X ;; 3: len 10; hex e794a8e688b731343134; asc 1414;; 4: len 4; hex 80000001; asc ;; 5: len 4; hex 8001869f; asc ;; ......

通过以上日志我们观察到的比较重要情况是，编号为268103的事务拥有两个锁结构（2 lock struct(s)），其中一个锁结构是意向性排它锁IX，这个锁结构一共锁定了一条记录（这条记录并不是myuser数据表中的一条记录）；另外一个锁结构是排它锁（X），这个锁结构加载在主键索引上（“page no 3 n bits 152 index ‘PRIMARY’ of table ‘qiang’.’myuser’”），并且锁定了13条记录。这13条记录就是myuser数据表中的所有数据记录，并非我们最先预计的9条记录。

这就是表锁在锁定规律上的具体表现：因为不能基于索引检索查询条件，所以就只能基于聚集索引进行全表扫描。因为不能确定聚集索引上哪些Page中数据满足检索条件，所以只能用排它锁一边锁定数据一边进行检索。因为要满足事务的ACID特性，所以在事务完成执行（或错误回滚）前都不能解除锁定：

由于我们一直讨论的InnoDB引擎默认的事务级别是“可重复度”（Repeatable Read），所以为了避免幻读，InnoDB还会在每一个排它性行锁周围都加上间隙锁（GAP）。那么在这个事务级别下表锁最终的逻辑表现就如下图所示：

是的，没有索引可以提供检索依据的数据表正在进行一场豪赌！这还是只有13条数据的情况下，那么试想一下如果数据表中有10 000 000条记录呢？这不仅造成资源的浪费，更重要的是表锁是造成死锁的重要原因，而且由此引发的InnoDB自动解锁代价非常昂贵（后文会详细讲到）。

4-3-3、死锁

一旦构成死锁，InnoDB会尽可能的帮助开发者解除死锁。其做法是自动终止一些事务的运行从而释放锁定状态。在上一小节我们示范的多个加锁场景，它们虽然都构成锁等待，但是都没有构成死锁。那么本文就要首先说明一下，什么样的情况才构成死锁。

4-3-3-1、什么是死锁

两个或者多个事务相互等待对方已锁定的资源，而彼此都不为协助对方达成操作目而主动释放已锁定的资源，这样的情况就称为死锁。请区分正常的锁等待和死锁的区别，例如以下示意图中的锁等待并不构成死锁：

上图中的情况只能称为锁资源等待，这是因为当A事务完成处理后就会释放所占据的资源上的锁，这样B事务就可以继续进行处理。并且在这个过程中没有任何因素阻止A事务完成，也没有任何因素阻止B事务在随后的操作中获取锁。但是，以下示意图中的两个事务就在相互等待对方已锁定的资源，这就称为死锁：

上图中A事务已为id1和id2这两个索引项加锁，当它准备为id4这个索引加锁时，却发现id4已经被事务B加锁，于是事务A进行等待过程。恰巧的是，B事务在为id4、id5加锁后，正在等待为id2这个索引项加锁。于是最后造成的结果就是事务A和事务B相互等待对方释放资源。注意，由于需要保证事务的ACID特性，所以A事务已经锁定的索引id1、id2在事务A的等待过程中，是不会被释放的；同样事务B已经锁定的索引id4、id5在等待过程中也不会被释放。很明显如果没有外部干预，这个互相等待的过程将一直持续下去。这就是一个典型的死锁现象。在实际应用场景中，往往会由超过两个事务共同构成死锁现象，甚至会出现强制终止某一个等待的事务后依然不能解除死锁的复杂情况。

4-3-3-2、死锁出现的原因

死锁造成的根本原因和上层MySQL服务和下层InnoDB引擎的协调方式有关：在上层MySQL服务和下层InnoDB引擎配合进行Update、Delete和Insert操作时， 对满足条件的索引加X锁的操作是逐步进行的。

当InnoDB进行update、delete或者insert操作时，如果有多条记录满足操作要求，那么InnoDB引擎会锁定一条记录（实际上是相应的索引）然后再对这条记录进行处理，完成后再锁定下一条记录进行处理。这样依次循环直到所有满足条件的数据被处理完，最后再统一释放事务中的所有锁。如果这个过程中某个将要锁定的记录已经被其它事务抢先锁定，则本事务就进入等待状态，一直等待到锁定的资源被释放为止。

要知道在正式的生成环境中，可能会同时有多个事务对某一个数据表上同一个范围内的数据进行加锁（加X锁后进行写操作）操作。而InnoDB引擎和MySQL服务的交互采用的这种方式很可能使这些事务各自持有某些记录的行锁，但又不构成让事务继续执行下去的条件。那为什么说在生产环境下，多数死锁状态的出现是因为表锁导致的呢？

首先，表锁本身并不会导致死锁，它只是InnoDB中的一种机制。但是表锁会一次锁定数据表中的所有聚集索引项。这就增加了表锁所在事务需要等待前序事务执行完毕才能继续执行的几率。而且这种等待状态还很可能在一个事务中出现多次——因为有多个事务在同时执行嘛。在这个过程中由于表锁逐渐占据了聚簇索引上绝大多数的索引项，所以这又增加了和其它正在执行的事务抢占锁定资源的，最终增加了死锁发生的几率。

由于需要进行表锁定的事务，需要将数据表中的所有聚集索引全部锁定后（如果在默认的事务级别下还要加GAP锁），才能完成事务的执行过程，所以这会导致后序事务全部进入等待状态。而InnoDB引擎根本无法预知表锁所在事务是否占据了后续资源需要使用的索引项。这就与之前的提到的情况一样，增加了死锁发生的几率。

4-3-3-3、避免死锁的建议

上一篇文章我们主要介绍了MySQL数据库中锁的基本原理、工作过程和产生死锁的原因。通过上一篇文章的介绍，可以确定我们需要业务系统中尽可能避免死锁的出现。这里为各位读者介绍一些在InnoDB引擎使用过程中减少死锁的建议。

正确使用读操作语句

经过之前文章介绍，我们知道一般的快照读是不会给数据表任何锁的。那么这些快照读操作也就不涉及到参与任何锁等待的情况。那么对于类似insert…select这样需要做当前读操作的语句（但又不是必须进行当前读的操作），笔者的建议是尽可能避免使用它们，如果非要进行也最好放到数据库操作的非高峰期进行（例如晚间）。

基于索引进行写操作，避免基于表扫描（聚集索引扫描）进行写操作

基于索引进行写操作的目的是保证一个写操作性质的事务中，被锁住的索引和需要请求的锁定资源被控制在最小范围内。而避免使用表锁的原因是保证一个写操作性质的事务中，不会额外锁住完全不需要的索引资源或者抢占完全不需要的索引资源。表锁虽然不会直接导致死锁，但是由于表锁的工作方式，导致它成为死锁原因的几率增大了。

避免索引失效

使用索引一定要注意索引字段的类型，例如当字段是一个varchar类型，赋值却是一个int类型，就会导致索引失效。如下所示：

explain select * from myuser where user_name = 1 # user_name 字段的类型是varchar，该字段建立了一个非唯一键索引 # 但是以上语句在使用字段进行检索时，却使用了一个int作为条件值。 # 通过MySQL的执行计划可以看到，InnoDB引擎在执行查询时并未使用索引，而是走的全表扫描 ---- ------------- ------- ------ --------------- ----- ------ ------------- | id | select_type | table | type | possible_keys | key | rows | Extra | ---- ------------- ------- ------ --------------- ----- ------ ------------- | 1 | SIMPLE | myuser| ALL | name_index | | 13 | Using where | ---- ------------- ------- ------ --------------- ----- ------ -------------

关键业务的delete、update语句应该使用执行计划进行审核：从MySQL version 5.6 版本开始，MySQL中的执行计划功能已经支持对delete、update语句进行执行过程分析了。如果需要执行比较复杂和相关操作或者关键业务的写操作，都应该首先在执行计划中观察其运行方式。后文我们马上开始执行计划的讲解。

5、SQL执行计划

为了帮助开发人员根据数据表中现有索引情况，了解自己编写的SQL的执行过程、优化SQL结构，MySQL提供了一套分析功能叫做SQL执行计划（explain）。下面我们就为大家介绍一下执行计划功能的使用。

5-1、执行计划基本使用

5-1-1、简单实例

首先我们给出几个执行计划的具体案例，这里使用的数据表还是上一篇文章中展示各种示例所使用的数据表。为了便于读者查看，这里再一次给出数据表的结构：

# 我们所示例的数据表和SQL语句均是工作在InnoDB数据库引擎下 # myuser数据表一共有4个字段，3个索引。 # user_name字段上创建了非唯一键非聚簇索引 # user_number字段上创建了唯一键非聚簇索引 # id字段上是聚簇索引 CREATE TABLE `myuser` ( `Id` int(11) NOT NULL AUTO_INCREMENT `user_name` varchar(255) NOT NULL DEFAULT '' `usersex` int(9) NOT NULL DEFAULT '0' `user_number` int(11) NOT NULL DEFAULT '0' PRIMARY KEY (`Id`) UNIQUE KEY `number_index` (`user_number`) KEY `name_index` (`user_name`) )

您可以使用任何一种MySQL数据库客户端执行以下执行计划：

不使用任何查询条件

explain select * from myuser; ---- ------------- ------- ------ --------------- ----- --------- ----- ------ ------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ---- ------------- ------- ------ --------------- ----- --------- ----- ------ ------- | 1 | SIMPLE | myuser| ALL | | | | | 13 | | ---- ------------- ------- ------ --------------- ----- --------- ----- ------ ------- # 检索数据表中的所有记录，由于没有使用任何检索条件，所以InnoDB引擎从聚簇索引上扫描出所有的数据行

使用非唯一建索引作为查询条件

explain select * from myuser where user_name = '用户1'; # 省去了表头，因为不好排版（可以参考上一个示例的表头） ...... |1 | SIMPLE | myuser | ref |name_index|name_index | 767 | const | 6 | Using index condition | # InnoDB引擎首先从非聚簇索引上查找满足条件的多个索引项，然后在聚簇索引上找到具体的数据

直接使用主键作为查询条件

explain select * from myuser where id = 1; # 省去了表头，因为不好排版（可以参考上上一个示例的表头） ...... | 1 | SIMPLE | myuser | const | PRIMARY | PRIMARY | 4 | const | 1 | --这列没有信息-- | #使用聚簇索引直接定位数据

使用非索引字段作为查询条件

explain select * from myuser where usersex = 1 ---- ------------- ------- ------ --------------- ----- --------- ----- ------ ------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ---- ------------- ------- ------ --------------- ----- --------- ----- ------ ------------- | 1 | SIMPLE | myuser| ALL | | | | | 13 | Using where | ---- ------------- ------- ------ --------------- ----- --------- ----- ------ ------------- # 由于没有创建索引，所以在聚簇索引上进行全表扫秒，并且过滤出满足条件的信息。

5-1-2、执行计划结果项

虽然本文还没有针对以上执行计划示例的分析结果进行讲解，但是为了让各位读者能够无阻碍的看下去，本文需要首先说明一下执行计划中的各个结果项的基本含义。在以上的示例中我们使用的MySQL的版本为MySQL version 5.6，根据不同的数据库版本，执行计划的分析结果可能会有一些不同。

# 以下是MySQL 5.6版本的执行计划的分析结果的表头 ---- ------------- ------- ------ --------------- ----- --------- ----- ------ ------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ---- ------------- ------- ------ --------------- ----- --------- ----- ------ -------

以上表头的各个字段项目的大致意义如下：

id：每个被独立执行的操作的标识，表示对象被操作的顺序；ID值大，先被执行；如果相同，执行顺序一般从上到下。

select_type: 数据库引擎将SQL拆分成若干部分的子查询/子操作，每个查询select子句中的查询类型（后文详细讲解）。

table: 本次子查询所查询的目标数据表。SQL查询语句即使再复杂，一次子查询也只可能最多关联一张数据表。

partitions: 本次查询所涉及的数据表目标分区信息（如果有分区的话）。后文将对分区的概念进行概要说明。

type: 子查询类型，非常重要的性能衡量点。这个字段项可能显示的值包括：“ALL->index->range->ref->eq_ref->const | system->NULL”这些值所表示的查询性能，从左至右依次增加（注意，按照数据库基本思想——B 树，查询性能可能呈几何级的变化也可能差异不大）。这些值所代表的查询动作，在后文中会详细进行介绍。

possible_keys: 本次子查询可能使用的索引（前提是，您要建立了索引）。如果查询所使用的检索条件可能涉及到多个索引，这里将会列出这些所有的可能性。

key: 本次子查询最终被选定的执行索引。有的时候possible_keys可能有值，但keys可能没有，这就代表InnoDB引擎最终并没有使用任何索引作为检所依据。

key_len： 被选定的索引键的长度。

ref: 表示本次子查询参照的参照条件/参照数据表，参照条件/参照数据表，这个字段的值还可能是一个常量。

rows： 执行根据目前数据表的实际情况预估的，完成这个子查询需要扫描的数据行数。

Extra：包含不适合在其他列中显示但十分重要的额外信息。这个字段所呈现的信息在后文也会进行详细说明。

5-1-3、MySQL数据库中的分区（partitions）

InnoDB引擎和MYISAM引擎都支持分区功能，只是不同的数据引擎实现细节不一样。分区功能是指将某一张数据表中的数据和索引按照一定的规则在磁盘上进行存储。分区功能只限于数据和索引的存储，是否对数据表进行了分区都不会影响索引在内存中的组织方式，并且分区功能的优势在数据量较小的情况下，是不怎么体现出来的。

目前主要的分区方式包括：按照某个字段的值范围进行分区（Range）、按照某一个或者多个字段的Hash Key进行分区（Hash）、按照某个字段的固定值进行分区（List）。并且开发人员还可以同时使用多种分区方式，对数据表进行复合分区。以下是一个分区的示例：

# 为partitionTable数据表建立四个存储分区 CREATE TABLE `partitionTable` ( `Id` int(11) NOT NULL AUTO_INCREMENT `FieldA` varchar(255) NOT NULL DEFAULT '' `FieldB` int(9) NOT NULL DEFAULT '0' PRIMARY KEY (`Id`) ) ENGINE=innodb PARTITION BY HASH(Id) PARTITIONS 4;

接着我们可以到MySQL的基础库中观察到partitionTable数据表的数据和索引计数结构：

# 查询partitiontable数据表的存储状态（库名为mysql） # 为节约篇幅，省略了不相关的行和列 select * from innodb_table_stats where table_name like 'partitiontable%' --------------------- -------- ---------------------- -------------------------- | table_name | n_rows | clustered_index_size | sum_of_other_index_sizes | --------------------- -------- ---------------------- -------------------------- | partitiontable#p#p0 | 0 | 1 | 0 | | partitiontable#p#p1 | 0 | 1 | 0 | | partitiontable#p#p2 | 0 | 1 | 0 | | partitiontable#p#p3 | 0 | 1 | 0 | --------------------- -------- ---------------------- --------------------------

有一定数据量的情况下（至少应该超过100万），当数据按照某个字段进行分区存储，且这个字段（或者几个字段）并没有创建索引，那么查询操作的性能将会有明显提高，而且数据表的数据量越大性能提高越明显；如果这个字段（或者几个字段）创建了索引，则查询操作的性能提升并不明显——因为检索还是依靠索引结构。在执行计划的分析结果中有一个列，名字叫做partitions。该列的信息实际上是说明执行计划可能涉及的分区情况。

5-2、关键性能点

在我们根据SQL的执行计划进行查询语句和索引调整时，我们主要需要注意以下这些字段显示的值，以及它们背后所代表的性能表述。它们是：select_type列、type列、Extra列和key列。

5-2-1、select_type概要说明

一个复杂的SQL查询语句，在进行执行时会被拆分成若干个子查询。这些子查询根据存在的位置、执行先后顺序等要素被分解为不同的操作类型。当然还有的操作可能不涉及到任何实际数据表，例如两个子查询间的连接操作过程。在执行计划分析结果的select_type列，显示了拆分后这些子查询的类型，它们是：

SIMPLE（常见）：简单的 SELECT查询。没有表UNION查询，没有子查询（嵌套查询）。我们在本节之前内容中给出的示例基本上属于这种查询类型，它基本上不需要也不能再进行子查询拆分。

PRIMARY（常见）：由子查询（嵌套查询）的SQL语句下，最外层的Select 作为primary 查询。

DERIVED（常见）：在from语句块中的子查询，属于衍生查询。例如以下的查询中接在“from”后面的子查询就属于这种类型的子查询：

explain select * from (select * from t_interfacemethod_param where name = 'uid') t_interfacemethod_param

SUBQUERY 和 DEPENDENT SUBQUERY：这两种类型都表示第一个查询是子查询。区别是SUBQUERY表示的子查询不依赖于外部查询，而后者的子查询依赖于外部查询。

UNCACHEABLE SUBQUERY：子查询结果不能被缓存， 而且必须重写（分析）外部查询的每一行

UNION：从第二个或者在union 之后的select 作为 union 查询。这种查询类型出现在结果集与结果集的UNION操作中。

UNION RESULT：结果集是通过union 而来的。这种查询类型出现在结果集与结果集的UNION操作中。

DEPENDENT UNION：从第二个或者在union 之后的select 作为 union 查询， 依赖于外部查询。这种查询类型出现在结果集与结果集的UNION操作中。

UNCACHEABLE UNION：第二个 或者 在UNION 查询之后的select 属于不可缓存的查询。这种查询类型出现在结果集与结果集的UNION操作中。

5-2-2、type概要说明

执行计划的type列中，主要说明了子查询的执行方式。它的值可能有如下的这些项目（根据MySQL数据库的执行引擎和版本还会有一些其它选项）：

ALL：全表扫描，实际上是扫描数据表的聚簇索引，并在其上加锁还会视事务隔离情况加GAP间隙锁。在数据量非常少的情况下，做全表扫描和使用聚簇索引检索当然不会有太大的性能差异。但是数据量一旦增多情况就完全不一样了。

index：进行索引进行的扫描，它和ALL一样都是扫描，不同点是index类型的扫描只扫描索引信息，并不会对聚簇索引上对应的数据值进行任何形式的读取。例如基于主键的函数统计：

# 以下语句还是要进行全表扫描，但是它并不需要读取任何数据信息。 explain select count(*) from myuser

range：在索引（聚簇索引和非聚簇索引都有可能）的基础上进行检索某一个范围内满足条件的范围，而并不是指定的某一个或者某几个值，例如：

# 以下查询语句在聚簇索引上检索一个范围 explain select * from myuser where id >= 10

ref：在非聚簇索引的基础上使用“非唯一键索引”的方式进行查找。例如：

# 在myuser中已基于user_name字段建立了非聚簇索引，且并非唯一键索引 explain select count(*) from myuser where user_name = '用户1'

const | system：const可以理解为“固定值”查询，常见于使用主键作为“简单查询”的条件时。system是比较特殊的const，当这个数据表只有一行的情况下，出现system类型。例如以下查询的操作类型就是const：

# 直接使用主键值就可以在索引中进行定位，无论数据量多大，这个定位的性能都不会改变 explain select * from myuser where id = 1

5-2-3、Extra概要说明

执行计划分析结果中的Extra字段，包含了结果中其他字段中没有说明但是对性能分析有非常有帮助的信息。甚至有的时候可以但从这个字段分析出某个子查询是否需要调整、涉及到的索引是否需要调整或者MySQL服务的环境参数配置是否需要进行调整。Extra字段还可以看成是对特定子查询的总结。

Using index：使用了索引（无论是聚簇索引还是非聚簇索引）并且整个子查询都只是访问了索引信息，而没有访问真实的数据信息，那么在Extra字段就会出现这个描述。请看如下示例：

explain select user_name from myuser where user_name = '用户1'; -------- ------------------------------------- | ...... | Extra | -------- ------------------------------------- | ...... | Using where; Using index | -------- ------------------------------------- # 使用user_name字段进行查询，原本需要再从聚簇索引中查找数据信息 # 但是InnoDB引擎发现只需要输出一个字段，且这个字段就是user_name本身，甚至不需要去找全部数据了。

Using where 和 Using index condition：此where关键字并不是SQL查询语句中的where关键字（此where非彼where），而是指该子查询是否需要依据一定的条件对满足条件的索引（全表扫描也是扫描的聚簇索引）进行过滤。示例如下：

# user_number 是一个非聚簇唯一键索引，所以where条件后的user_number只会定位到唯一一条记录 # 不需要再根据这个条件查询是否还有其它满足条件的索引项了 explain select * from myuser where user_number = 77777 -------- ------------- | ...... | Extra | -------- ------------- | ...... | | -------- ------------- # user_name 是一个非聚簇非唯一键索引，索引where条件后的user_name可能定位到多条记录 # 这时数据库引擎就会对这些索引进行检索，以便定位满足查询条件的若干索引项 #（由于B 树的结构，所以这些索引项是连续的） explain select * from from myuser where user_name = '用户1' -------- ------------------------------ | ...... | Extra | -------- ------------------------------ | ...... | Using index condition | -------- ------------------------------

为什么以上示例中显示的是“Using index condition”而不是“Using where”呢？这是MySQL Version 5.6 的新功能特性，Index Condition Pushdown (ICP)。简单的说就是减少了查询执行时MySQL服务和下层数据引擎的交互次数，达到提高执行性能的目的。如果您关闭MySQL服务中的ICP功能（这个功能默认打开），以上示例的第二个执行语句就会显示“Using where”了。

Using temporary：Mysql中的数据引擎需要建立临时表进行中间结果的记录，才能完成查询操作。这个常见于查询语句中存在GROUP BY 或者 ORDER BY操作的情况。但并不是说主要子查询中出现了GROUP BY 或者 ORDER BY就会建立临时表，而如果Group By 或者 Order By所依据的字段（或多个字段）没有建立索引，则一定会出现“Using temporary”这样的提示。另一种常见情况发生在子查询join连接时，连接所依据的一个字段（或多个字段）没有建立物理外键和索引。一旦在Extra字段中出现了“Using temporary”提示，一般来说这条子查询就需要重点优化。

Using filesort：Mysql服务无法直接使用索引完成排序时，就需要动用一个内存空间甚至需要磁盘交换动作辅助才能完成排序操作。这句话有两层含义，如果排序所依据的字段（一个或者多个）并没有创建索引，那么肯定无法基于索引完成排序；即使排序过程能够依据正确的索引完成，但是由于涉及到的查询结果太多，导致用于排序的内存空间不足，所以MySQL服务在进行排序时还会有磁盘交换动作。负责配置某一个客户（session）可用的内存空间参数项名字为“sort_buffer_size”。默认的大小为256KB，如果读者对查询结果集有特别要求，可以将该值改为1MB。一旦在Extra字段中出现了“Using filesort”提示，那么说明这条子查询也需要进行优化。

explain select * from myuser order by usersex -------- ----------------------- | ...... | Extra | -------- ----------------------- | ...... | Using filesort | -------- ----------------------- # 由于usersex并没有创建索引，所以使用filesort策略进行排序。

注意，在子查询中为Group By和Order by操作创建索引时，有时需要联合where关键字使用的查询字段一起创建复合索引才能起作用。这是因为子查询为了检索，所首先选择一个可用的索引项，随后进行排序时，却发现无法按照之前的索引进行排序，所以只有走filesort了。例如以下示例：

# user_name字段和user_number字段都独立创建了索引 explain select * from myuser where user_name = '用户1' group by user_number -------- ------------ ---------------------------------------------------------- | ...... | key | Extra | -------- ------------ ---------------------------------------------------------- | ...... | name_index | Using index condition; Using where; Using filesort | -------- ------------ ---------------------------------------------------------- # 为了首先完成条件检索，InnoDB引擎选择了user_name字段的索引 # 但是排序时发现无法按照之前的索引字段完成，所以选择走filesort

Using join buffer：使用InnoDB引擎预留的join buffer区域（一个专门用来做表连接的内存区域），这是一个正常现象主要涉及到两个子查询通过join关键字进行连接的操作。每一个客户端连接（session）独立使用的join buffer区域大小可以通过join_buffer_size参数进行设置。这个参数在MySQL 5.6 Version中的默认值为128KB。如果开发人员经常需要用到join操作，可以适当增加区域大小到1MB或者2MB。

# 以下语句是一个左外连接的操作 # 并且t_interfacemethod.uid和t_interfacemethod_param.interfacemethod之间有外键和索引存在 explain select * from t_interfacemethod_param left join t_interfacemethod on t_interfacemethod.uid = t_interfacemethod_param.interfacemethod -------- ---------------------------------------------------------- | ...... | Extra | -------- ---------------------------------------------------------- | ...... | | -------- ---------------------------------------------------------- | ...... | Using where; Using join buffer (Block Nested Loop) | -------- ----------------------------------------------------------

5-3、执行计划的局限性

执行计划不考虑Query Cache : 执行计划只考虑单次语句的执行效果，但实际上MySQL服务以及上层业务系统一般都会有一些缓存机制，例如MySQL服务中提供的Query Cache功能。所以实际上可能查询语句的重复执行速度会快一些。

执行计划不能分析insert语句：insert语句的执行效果实际上是和其他语句相互作用的，所以执行计划不能单独分析insert语句的执行效果。不过update和delete语句都是可以分析的（请使用MySQL Version 5.6 版本）。

执行计划不考虑可能涉及的存储过程、函数、触发器带来的额外性能消耗。

总的来说经过各个MySQL版本对执行计划功能的优化，现在这个功能得到的分析结果已经非常接近真实执行效果了。但是MySQL执行性能最关键的依据还是各位技术人员的数据库设计能力，起飞吧程序猿！

6、更高效的InnoDB引擎

MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL。Google早已将他们服务器运行的上万个MySQL服务替换成了MariaDB，从公开的资料看淘宝技术部门也掀起一股使用MariaDB替换MySQL的技术思潮。

MariaDB数据库的产生和发展和甲骨文公司收购MySQL事件有关，它是MySQL之父Widenius先生重新主导开发的完全和MySQL兼容的产品，其下运行的核心引擎还是InnoDB（这个版本的InnoDB引擎，也被称为XtraDB ）。各位读者所在的技术团队也不妨尝试一下，因为这两中数据库的使用从业务层开发人员来看完全没有任何区别，DBA的维护手册甚至都不需要做任何更改。

————————————————

版权声明：本文为CSDN博主「说好不能打脸」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。

原文链接：https://blog.csdn.net/yinwenjie/article/details/52757457