MySQL-阿里云开发者社区

引言

本文整理了MySQL相关的知识，方便以后查阅。

基础架构

下图是 MySQL 的一个简要架构图，从下图你可以很清晰的看到用户的 SQL 语句在 MySQL 内部是如何执行的。
先简单介绍一下下图涉及的一些组件的基本作用帮助大家理解这幅图。

连接器：身份认证和权限相关(登录 MySQL 的时候)。
查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
分析器: 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
优化器：按照 MySQL 认为最优的方案去执行。
执行器: 执行语句，然后从存储引擎返回数据。

mysql-architecture
简单来说 MySQL 主要分为 Server 层和存储引擎层：

Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binlog 日志模块。
存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

基本组件

连接器

连接器主要和身份认证和权限相关的功能相关，就好比一个级别很高的门卫一样。

主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。

查询缓存

查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集。

连接建立后，执行查询语句的时候，会先查询缓存，MySQL 会先校验这个 sql 是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询预计，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。

MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。

所以，一般在大多数情况下我们都是不推荐去使用查询缓存的。

MySQL 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。

分析器

MySQL 没有命中缓存，那么就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：

第一步，词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。
第二步，语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。

完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。

优化器

优化器的作用就是它认为的最优的执行方案去执行（有时候可能也不是最优），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

可以说，经过了优化器之后可以说这个语句具体该如何执行就已经定下来。

执行器

当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

存储引擎

存储引擎就是真正保存数据，索引数据的容器。在MySQL中有两个主流的存储引擎，MyISAM和InnoDB。

区别

MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（事务型数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。

大多数时候我们使用的都是 InnoDB 存储引擎，但是在某些情况下使用 MyISAM 也是合适的比如读密集的情况下。（如果你不介意 MyISAM 崩溃回复问题的话）。

是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持事务，外部键等高级数据库功能。具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
是否支持外键： MyISAM不支持，而InnoDB支持。
是否支持MVCC ：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;MVCC可以使用乐观(optimistic)锁和悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。

一般情况下我们选择 InnoDB 都是没有问题的，但是某些情况下你并不在乎可扩展能力和并发能力，也不需要事务支持，也不在乎崩溃后的安全恢复问题的话，选择MyISAM也是一个不错的选择。但是一般情况下，我们都是需要考虑到这些问题的。

索引

MySQL索引使用的数据结构主要有BTree索引和哈希索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

MySQL的BTree索引使用的是B树中的B+Tree，但对于主要的两种存储引擎的实现方式是不同的：

MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
InnoDB: 其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

InnoDB数据结构

各个数据页可以组成一个双向链表
而每个数据页中的记录又可以组成一个单向链表

每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录
以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

所以说，如果我们写select * from user where id = '8'这样没有进行任何优化的sql语句，默认会这样做：

定位到记录所在的页，需要遍历双向链表，找到所在的页
从所在的页内中查找相应的记录，由于不是根据主键查询，只能遍历所在页的单链表了

哈希索引

除了B+树之外，还有一种常见的是哈希索引。

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

本质上就是把键值换算成新的哈希值，根据这个哈希值来定位。

看起来哈希索引很强，能快速找到对应的项，但其实哈希索引有好几个局限(根据他本质的原理可得)：

哈希索引也没办法利用索引完成排序
不支持最左匹配原则
在有大量重复键值情况下，哈希索引的效率也是极低的---->哈希碰撞问题。
不支持范围查询

最左匹配原则

最左匹配原则：

索引可以简单如一个列(a)，也可以复杂如多个列(a, b, c, d)，即联合索引。我们知道索引字段的数据必须是有序的，才能实现这种类型的查找，才能利用到索引。而在联合索引中，因为有多个字段，所以怎么排序索引就有讲究，它会按照联合索引 key 的顺序进行排序。例如(col1, col2)，可能的数据内容如下。
1. col1| col2
2. 0 | 4
3. 0 | 9
4. 1 | 1
5. 1 | 3
在使用上述联合索引时，索引只能用于查找key是否存在（相等），遇到范围查询(>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。为什么呢？如果我们查询 col1 >= 0 && col1 <= 1 的话，col2 的索引部分就已经不是有序的了，我们没办法在无序的数据上进行二分查找。所以当出现范围查询时，后续的索引就会失效。
再来说说最左匹配的查询过程，当我们要查询 col1 = 1 & col2 = 1 时，可以先根据 col1 的索引找到所有 col1 = 1 的数据，然后在根据 col2 的索引找到所有 col2=1 的数据。这都没有问题。
但是，当我们需要直接查询 col2 = 1 时，上述索引就没法使用了，因为纵观所有 col2 字段的所有索引值的话，你会发现它实际上是无序的。因此，搜索时条件列的排列顺序决定了可命中索引的列数。
当我们进行条件查询时，mysql 会按照查询条件的顺序，找到左前缀相匹配的索引使用，值得一提的是，如果我们的查询条件是 col2 = 1 & col1 = 1，优化器会为我们优化为 col1 = 1 & col2 = 1，这样就能使用上述索引。

事务

事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

ACID

原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据是独立的；
持久性：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

事务日志

事务日志可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时，只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据持久到磁盘。事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久化后，内存中被修改的数据可以在后台慢慢地刷回磁盘。目前大多数存储引擎都是这样实现的，我们通常称之为预写式日志，修改数据要刷写两次磁盘。

如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。

并发问题

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对统一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

隔离级别

事务隔离级别的出现是为了解决脏读、不可重复度、幻读这三类问题而发展出来的概念

事务隔离级别是用来描述读取数据时究竟能够读取到什么状态的数据，是已经提交的数据？还是正在修改中但是还没有提交的数据。这里需要注意，而在进行数据修改时，所参考的基础数据是按照已提交的最新数据作为标准的，和当时事务处于哪种隔离界别无关。

READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别	脏读	不可重复读	幻影读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看。

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

MVCC

很多应用的一个特点都是读多写少的场景，很多数据的读取次数远大于修改的次数，而读取数据间互相排斥显得不是很必要。所以就使用了一种读写锁的方法，读锁和读锁之间不互斥，而写锁和写锁、读锁都互斥。这样就很大提升了系统的并发能力。之后人们发现并发读还是不够，又提出了能不能让读写之间也不冲突的方法，就是读取数据时通过一种类似快照的方式将数据保存下来，这样读锁就和写锁不冲突了，不同的事务session会看到自己特定版本的数据。当然快照是一种概念模型，不同的数据库可能用不同的方式来实现这种功能。

InnoDB中通过UndoLog实现了数据的多版本，而并发控制通过锁来实现。

Undo Log除了实现MVCC外，还用于事务的回滚。

区分各种log

MySQL Innodb中存在多种日志，除了错误日志、查询日志外，还有很多和数据持久性、一致性有关的日志。

Bin log

binlog，是mysql服务层产生的日志，记录的都是事务操作内容，比如一条语句DELETE FROM TABLE WHERE i > 1之类的，它常用来进行数据恢复、数据库复制，常见的mysql主从架构，就是采用slave同步master的binlog实现的, 另外通过解析binlog能够实现mysql到其他数据源（如ElasticSearch)的数据复制。我们可以通过哦 mysqlbinlog -vv BINLOG 解析binlog的内容。

Redo log

redo log记录了数据操作在物理层面的修改(即每个物理页的修改)，mysql中使用了大量缓存，缓存存在于内存中，修改操作时会直接修改内存，而不是立刻修改磁盘，当内存和磁盘的数据不一致时，称内存中的数据为脏页(dirty page)。为了保证数据的安全性，事务进行中时会不断的产生redo log，在事务提交时进行一次flush操作，保存到磁盘中, redo log是按照顺序写入的，磁盘的顺序读写的速度远大于随机读写。当数据库或主机失效重启时，会根据redo log进行数据的恢复，如果redo log中有事务提交，则进行事务提交修改数据。这样实现了事务的原子性、一致性和持久性。

Undo log

Undo Log: 除了记录redo log外，当进行数据修改时还会记录undo log，undo log用于数据的撤回操作，它记录了修改的反向操作，比如，插入对应删除，修改对应修改为原来的数据，通过undo log可以实现事务回滚，并且可以根据undo log回溯到某个特定的版本的数据，实现MVCC。

log 的一致性

innodb 使用了两阶段提交，prepare 阶段先写入 redo log 和 undo log，因为它们是顺序写入磁盘所以速度还算快，将它们刷盘之后，我们就可以提交或者回滚了，然后我们可以同步写入 binlog 或者异步写入 binlog。

InnoDB prepare （持有prepare_commit_mutex，写redo log 和 undo log）
write/sync Binlog （可以同步，也可以异步，只要redo log写入之后就可以放心提交了，因为即便丢失了bin log也可以通过redo log恢复数据）
InnoDB commit (写入COMMIT标记后释放prepare_commit_mutex)

实现

InnoDB行记录中除了刚才提到的rowid外，还有trx_id和db_roll_ptr, trx_id表示最近修改的事务的id,db_roll_ptr指向undo segment中的undo log。

新增一个事务时，会为该事务分配一个 id，这个事务id是单调增加的，所以通过trx_id能够表示事务开始的先后顺序。

Undo log分为Insert和Update两种，delete可以看做是一种特殊的update，即在记录上修改删除标记。update undo log记录了数据之前的数据信息，通过这些信息可以还原到之前版本的状态。当进行插入操作时，生成的Insert undo log在事务提交后即可删除，因为其他事务不需要这个undo log。进行删除修改操作时，会生成对应的undo log，并将当前数据记录中的db_roll_ptr指向新的undo log

上图中每条 undo log 的增加过程都伴随着：加锁->添加一条记录->事务提交->释放锁 这个过程，当一个事务 A 开始时，我们为其分配 tid 1，该事务进行了一次数据读取，接着事务 2 对数据进行了修改&提交，接着事务 3 对数据进行了修改但是还没有提交，就会出现上图中的情况。这时候B+ 树叶子节点保存的事务 3 修改后结果（未提交），它指向事务 2 提交的结果（undo segment），而事务 2 对应的 undo segment 又指向了事务 1 开始时，数据的原始状态。当事务 1 再次进行读取时，会按照上述的节点链表找到满足该事务隔离级别的数据状态，例如：读未提交返回的是 leaf node segment，而读已提交返回的是最下面的 undo segment，而重复读返回的是最上面的 undo segment，当隔离界别为序列化时，事务 2 和事务3的修改都会被阻塞，也就没有上述的两个 undo segment，只会存在原始数据。

undo log在没有活动事务依赖（用于consistent read或回滚)便可以清除，innodb 中存在后台purge 线程进行后台轮询删除undo log。

可见性判断

CREATE TABLE `testunique` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) DEFAULT NULL,
  `ukey` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_uid` (`uid`),
  KEY `index_key` (`ukey`)
) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8;

隔离级别REPEATABLE READ

只有当session2 commit之后的查询才能查到session1插入的数据

事务可见性的处理过程:

RR级别下一个事务开始后第一个snapshot read的时候，会将当期活动的事务id记录下来，记录到read view中。RC级别则是每次snapshot read都会创建一个新的read view。

假设当前, read view中最大的事务id为tmax, 最小为tmin。则判断一个数据是否可见以及对应的版本的方法为：

如果该行中的trx_id, 赋值给tid, 如果tid和当前事务id相等或小于tmin，说明是事务内发生的或开启前的修改，则直接返回该版本数据;
如果trx_id大于tmax, 则查看该版本的db_roll_ptr中的trx_id，赋值给tid并从头开始判断。
如果tid小于tmax并且不在read view中，则返回，否则从回滚段中找出undo log的trx_id，赋值给tid从头判断。

所以可见性是，只有当第一次读之前提交的修改和自己的修改可见，其他的均不可见。

Current Read & Snapshot Read

REPEATABLE READ隔离级别下普通的读操作即select都不加锁，使用MVCC进行一致性读取，这种读取又叫做snapshot read。

在innoDB中，因为MVCC的实现，如果只进行普通读select * from t where a=1;那么是可以保证不可重复读和消除幻读，因为普通读操作会走snapshot read，innoDB会根据tid过滤掉事务开启前未提交的事务。

而update, insert, delete, select … for update, select … lock in share mode都会进行加锁，并且读取的是当前版本，也就是READ COMMITTED读的效果。这里大家一定要清楚地认识到，MVCC 是用来让读写之间并发进行的，写写之间是通过锁来隔离的，当一个事务 A 进行修改操作后，到事务提交之前会一直持有锁，这时候事务 B 是无法进行修改的。而且前面所述的事务隔离级别，都是用来描述能够读到什么样的数据的，而不是用来描述在写入数据时，参照的基础数据是什么。当进行数据写入时，无论事务个隔离级别是什么，都会以当前已经提交的数据作为基础进行修改，并且在事务提交or回滚之前都会一直持有改数据的锁。

我不知道有没有同学和我一样，在看 MVCC 的时候总是想到一个奇怪的问题：如果将事务的隔离级别适用到写写操作上，举例说就是写写操作不加锁，读未提交相当于读取了别的事务正在修改中的数据。其实这个问题在 SQL 中不作处理，任何写写操作之间都是通过锁进行互斥隔离的，也就是相当于序列化的隔离界别。

InnoDB中加锁的方法是锁住对应的索引，一个操作进行前会选择一个索引进行扫描，扫描到一行后加上对应的锁然后返回给上层然后继续扫描。InnoDB支持行级锁(record lock)，上述需要加锁的操作中，除了select … lock in share mode 是加shared lock（共享锁或读锁）外其他操作都加的是exclusive lock(即排他锁或写锁)。在加行级锁前，会对表加一个intention lock，即意向锁，意向锁是表级锁，不会和行级锁冲突，主要用途是表明一个要加行级锁或正在加锁的操作。

另外InnoDB中除了record lock外还有一种gap lock，即锁住两个记录间的间隙，防止其他事务插入数据，用于防止幻读。当要锁的索引是主键索引或唯一索引并且是单数据加锁时，不需要加gap lock，只通过行锁即可。对于唯一索引的范围加锁（> 或< 的情况）我们需要加gap锁。当要锁索引不是唯一索引或者是带等于的范围加锁时（>= 的情况），需要对索引数据和索引前的gap加锁，这种方式叫做next-key locking。

另外在插入数据时，还需要提前在插入行的前面部分加上insert intention lock, 即插入意向锁，插入意向锁之间不会冲突，会和gap锁冲突导致等待。当插入时遇到duplicated key错误时，会在要插入的行上加上share lock。

因为InnoDB的MVCC机制：自己修改过的数据在快照中可见&update操作是基于当前版本而不是快照版本，所以InnoDB的RR隔离级别存更新操作自动降级到了RC级别，即更新是在最新版本的数据上进行，故RR隔离级别下的非READ-ONLY事务，可能存在不可重复读和幻象的情况。

a事务先select，b事务update或insert确实会加一个gap锁，但是如果b事务commit，这个gap锁就会释放（释放后a事务可以随意dml操作）
a事务再select出来的结果在MVCC下还和第一次select一样，
接着a事务不加条件地update，这个update会作用在所有行上（包括b事务新加的）
a事务再次select就会出现b事务中的修改或者新添加的行，并且这一行数据已经被update修改了

从上面的例子中，我们看出innoDB的非READ-ONLY RR事务，退化成了RC级别。

为了保证InnoDB的RR隔离级别达到ANSI-RR级别，对于之后进行update的情况，需要显式地用for update加gap锁，防止其它事务的干扰，这样就能达到可重复读和消除幻读的目的。

锁

MyISAM和InnoDB存储引擎使用的锁：

MyISAM采用表级锁(table-level locking)。
InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

表级锁和行级锁对比：

表级锁： MySQL中锁定粒度最大的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。
行级锁： MySQL中锁定粒度最小的一种锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

InnoDB存储引擎的锁的算法有三种：

Record lock：单个行记录上的锁
Gap lock：间隙锁，锁定一个范围，不包括记录本身
Next-key lock：record+gap 锁定一个范围，包含记录本身

InnoDB存储引擎总结

从MySQL5.6开始，InnoDB是MySQL数据库的默认存储引擎。它支持事务，支持行锁和外键，通过MVCC来获得高并发。

MySQL5.6的InnoDB存储引擎体系结构图如下:

InnoDB缓冲池

InnoDB缓冲池是内存中用来缓存表数据和索引的一片区域。

当缓冲池大小达到GB级别时，通过设置多个缓冲池实例，可以提高并发处理能力，减少数据库内部资源竞争。对于存储到或从缓冲池中读取的每个页，都使用哈希函数随机分配到不同的缓冲池实例中。

innodb_buffer_pool_size: 设置缓冲池的大小(单位: Byte)，重启生效。
innodb_buffer_pool_instances: 设置缓冲池实例的个数，每个缓冲池管理自己的free lists，flush lists,LRU list和其他数据结构，并受到自己的互斥锁保护。此值默认为1，最大为64。只有当 innodb_buffer_pool_size 超过1G时，此参数才会起作用。重启生效。

缓冲池的LRU算法

InnoDB以列表的方式管理缓冲池，使用优化后的LRU算法。此算法可以最大限度地减少进入缓冲池并从未被再次访问的页的数量，这样可以确保热点页保持在缓冲池中。

当缓冲池的free list没有可用的空闲页时，InnoDB会回收LRU列表中最近最少使用的页，并将新读取到的页添加到LRU列表的midpoint位置，称之为“midpoint insertion strategy”。它将LRU列表视为两个子列表，midpoint之前的列表称为new子列表，包含最近经常访问的页；midpoint之后的列表称为old子列表，包含最近不常访问的页。

最初，新添加到缓冲池的页位于old子列表的头部。当在缓冲池中第一次访问这些页时，会将它们移到new子列表的头部，此时发生的操作称为page made young。随着数据库的运行，缓冲池中没有被访问到的页由于移到LRU列表的尾部而变老，最终会回收LRU列表尾部长时间未被访问的页。

可以通过 innodb_old_blocks_pct 参数设置old子列表在LRU列表中所占的比例。默认值为37，对应3/8的位置。取值范围从5到95。

为什么要将新读取到的页放在midpoint位置而不是LRU列表的头部？若直接将读取到的页插入到LRU列表的头部，当出现全表扫描或索引扫描的时候，需要将大量的新页读入到缓冲池中，导致热点页从缓冲池刷出，而这些新页可能仅在这次查询中用到，并不是热点数据，这样就会额外产生大量的磁盘I/O操作，影响效率。为了避免此问题，InnoDB引擎引入了参数：innodb_old_blocks_time，此参数表示第一次读取old子列表中的页后，需要等待多少毫秒才会将此页移到new子列表。默认值为1000。增加此值可以让更多的页更快的老化。

Change Buffer

change buffer用来缓存不在缓冲池中的辅助索引页(非唯一索引)的变更。这些缓存的的变更，可能由INSERT、UPDATE或DELETE操作产生，当读操作将这些变更的页从磁盘载入缓冲池时，InnoDB引擎会将change buffer中缓存的变更跟载入的辅助索引页合并。

不像聚簇索引，辅助索引通常不是唯一的，并且辅助索引的插入顺序是相对随机的。若不用change buffer，那么每有一个页产生变更，都要进行I/O操作来合并变更。使用change buffer可以先将辅助索引页的变更缓存起来，当这些变更的页被其他操作载入缓冲池时再执行merge操作，这样可以减少大量的随机I/O。change buffer可能缓存了一个页内的多条记录的变更，这样可以将多次I/O操作减少至一次。

在内存中，change buffer占据缓冲池的一部分。在磁盘上，change buffer是系统表空间的一部分，以便数据库重启后缓存的索引变更可以继续被缓存。

innodb_change_buffering 参数可以配置将哪些操作缓存在change buffer中。可以通过此参数开启或禁用insert操作，delete操作(当索引记录初始标记为删除时)和purge操作(当索引记录被物理删除时)。update操作是inset和delete操作的组合。该参数的取值如下：

all: 默认值，包含inserts、deletes和purges
none: 不缓存任何操作
inserts: 缓存insert操作
deletes: 缓存标记删除(delete-marking)操作
changes: 缓存inserts和deletes
purges: 缓存后台进程发生的物理删除操作

自适应哈希索引

自适应哈希索引是InnoDB表通过在内存中构造一个哈希索引来加速查询的优化技术，此优化只针对使用 '=' 和 'IN' 运算符的查询。MySQL会监视InnoDB表的索引查找，若能通过构造哈希索引来提高效率，那么InnoDB会自动为经常访问的辅助索引页建立哈希索引。

这个哈希索引总是基于辅助索引(B+树结构)来构造。MySQL通过索引键的任意长度的前缀和索引的访问模式来构造哈希索引。InnoDB只为某些热点页构建哈希索引。

可通过 innodb_adaptive_hash_index 参数开启或禁用此功能，默认是开启状态。开启此功能后， InnoDB会根据需要自动创建这个哈希索引，而不用人为干预创建，这就是叫自适应的原因。此功能并不是在所有情况下都适用，且AHI需要的内存都是从缓冲池申请的，所以此功能的开启或关闭需要通过测试来具体确定。可以通过 SHOW ENGINE INNODB STATUS 命令查看AHI的使用状况。

Redo Log

重做日志用来实现事务的持久性。其由两部分组成：一是内存中的重做日志缓冲(redo log buffer)，其是易失的；二是磁盘上的重做日志文件(redo log file)，其是持久的。

在MySQL数据库宕机恢复期间会用到重做日志文件，用于更正不完整事务写入的数据。

InnoDB通过Force Log at Commit机制实现事务的持久性，即在事务提交前，先将事务的重做日志刷新到重做日志文件。

重做日志在磁盘上由一组文件组成，通常命名为 ib_logfile0 和 ib_logfile1。

MySQL以循环方式将日志写入重做日志文件。假设现在有两个重做日志文件：ib_logfile0和ib_logfile1。重做日志先写入到ib_logfile0，当ib_logfile0写满后再写入ib_logfile1。当ib_logfile1也写满后，再往ib_logfile0中写，而之前的内容会被覆盖。

innodb_log_file_size 参数用来设置每个重做日志文件的大小(单位：Byte)。innodb_log_files_in_group 参数用来设置重做日志文件组中日志文件的个数。 innodb_log_group_home_dir 参数设置重做日志文件所在的路径。从MySQL5.6.3开始，重做日志文件总大小的最大值从之前的4GB提升到了512GB。

重做日志(redo log)跟二进制日志(binlog)的区别：

重做日志在InnoDB存储引擎层产生；而二进制日志在MySQL数据库上层产生，不仅仅针对InnoDB引擎，MySQL中的任何存储引擎对于数据库的变更都会产生二进制日志。
两者记录的内容形式不同，二进制日志是一种逻辑日志，其记录的是对应的SQL语句；重做日志是物理格式日志，其记录的是每个页的变更。
两种日志记录写入磁盘的时间点不同，二进制日志只在事务提交完成后进行写入；而重做日志在事务进行中被不断的写入，也就是日志并不是随事务提交的顺序写入的。

重做日志缓冲是一块内存区域，用来缓存即将被写入到重做日志文件的数据。InnoDB引擎首先将重做日志信息缓存到重做日志缓冲，然后定期将其刷新到磁盘上的重做日志文件。

如下三种情况会将重做日志缓冲中的数据刷新到磁盘的重做日志文件中：

Master Thread会定期将重做日志缓冲刷新到重做日志文件，即使这个事务还没有提交。

事务提交时
当重做日志缓冲没有足够的空间时
InnoDB通过Force Log at Commit机制实现事务的持久性，即在事务提交前，先将事务的重做日志刷新到到重做日志文件。

innodb_flush_method 定义用于将数据刷新到InnoDB数据文件和日志文件的方法，会影响I/O吞吐量。默认值为NULL，可选项包含：fsync、O_DIRECT和其他。若在Unix-like系统上此参数设置为NULL，那么默认使用fsync。

fsync: InnoDB调用系统的fsync()刷新数据文件和日志文件。

O_DIRECT: InnoDB使用O_DIRECT方式打开数据文件，然后使用fsync()刷新数据文件和日志文件。启用后将绕过操作系统缓存，直接写文件。

为确保每次重做日志缓冲都能写入到磁盘的重做日志文件，在每次将重做日志缓冲写入重做日志文件后，InnoDB引擎都需要调用一次fsync操作。由于默认情况下 innodb_flush_method 参数未设置为O_DIRECT，因此重做日志缓冲先写入文件系统缓存。

innodb_log_buffer_size 参数可以设置重做日志缓冲的大小。

innodb_flush_log_at_trx_commit 参数用来控制重做日志缓冲刷新到磁盘的策略。取值范围如下：

1: 默认值。表示事务提交时必须调用一次fsync操作。
0: 表示事务提交时不进行刷新日志到磁盘。这个操作在master thread中完成，在master thread中每1秒会进行一次刷新日志到磁盘操作。
2: 表示事务提交时将重做日志缓冲写入重做日志文件，但仅写入文件系统的缓存中，不进行fsync操作。此情况下，若操作系统发生宕机，会丢失未从文件系统缓冲刷新到重做日志文件的那部分日志。

Undo Log

undo log(也称为rollback segment)用来存储被事务修改的记录的副本。

undo日志有两个作用：一个是实现事务的原子性，即当事务由于意外情况未能成功运行时，可以使事务回滚，从而让数据恢复到事务开始时的状态；另一个作用是实现MVCC机制，当用户读取一行记录时，若该记录已经被其他事务占有，当前事务可以通过undo日志读取该记录之前的版本信息，以此实现一致性非锁定读。

每个回滚段(rollback segment)记录了1024个undo段(undo segment)，InnoDB引擎在每个undo段中进行undo页的申请。

undo log分为insert undo log和update undo log。

insert undo log指事务在INSERT操作中产生的undo日志。因为INSERT操作的记录仅对当前事务可见，所以该undo日志在事务提交后可以直接删除。
update undo log通常保存的是对DELETE和UPDATE操作产生的undo日志。该undo日志可能需要提供MVCC机制，因此其不能在事务提交后立即删除。当事务提交后，它会放入回滚段的history链表的头部，等待purge线程进行最后的删除。

默认情况下，undo日志位于系统表空间(system tablespace)中。从MySQL5.6起，可以通过 innodb_undo_tablespaces 和 innodb_undo_directory 参数将undo日志存放在独立表空间中。

系统表空间

InnoDB系统表空间包含InnoDB数据字典(InnoDB相关对象的元数据)、双写缓冲(doublewrite buffer)、change buffer和undo logs。此外，还包含用户在系统表空间中创建的表数据和索引数据。由于多个表的数据可以在共同存放在系统表空间中，以此其也称为共享表空间。

系统表空间可由一个或多个文件组成。默认情况下，在MySQL数据目录中有一个命名为 ibdata1 的系统表空间文件。innodb_data_file_path 参数可以设置系统表空间文件的大小和数量。

可以通过 innodb_file_per_table 参数启用独立表空间。即每创建一个表就会产生一个单独的 .ibd 文件存放此表的记录和索引。若未启用此参数，那么InnoDB引擎创建的表就会存在于系统表空间中。

双写缓冲

双写缓冲技术是为了解决partial page write问题而开发的。doublewrite buffer是系统表空间上的连续的128个页(两个区)，大小为2M。

当发生数据库宕机时，可能InnoDB存储引擎正在写入某个页到表中，而这个页只写了一部分，比如16KB的页（默认页大小），只写了前4KB（因为大部分磁盘页大小是4KB，它只能保证4KB的原子写入），之后就发生了宕机，这种情况被称为部分写失效（partial page write）。

doublewrite的工作原理是：在将缓冲池中的页写入磁盘上对应位置之前，先将缓冲池中的页copy到内存中的doublewrite buffer，之后顺序地将内存中doublewrite buffer中的页写入系统表空间中的doublewrite区域，然后立即调用系统fsync函数，同步数据到磁盘文件中，避免缓冲写带来的问题。在完成doublewrite页的写入之后，再将内存上doublewrite buffer中的页写入到自己的表空间文件。
double-write
通过双写缓冲，数据文件和双写缓冲文件中，至少有一份数据是正确无误的，如果写磁盘过程发生了崩溃，那么MySQL重启时可以通过校验和来确认是否有错误数据，如果双写缓冲文件错误了，就从数据文件中拉取原始数据根据redo log得出正确的目标数据，而如果数据文件错误了，则将双写缓冲中的数据重新写入数据文件。

大表优化

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

限定数据的范围
- 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内
读/写分离
- 经典的数据库拆分方案，主库负责写，从库负责读
垂直分区
- 根据数据库里面数据表的相关性进行拆分。例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。
- 简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。
- 垂直拆分的优点：可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
- 垂直拆分的缺点：主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂。
水平分区
- 保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。
- 水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。
- 水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库。
- 水平拆分能够支持非常大的数据量存储，应用端改造也少，但分片事务难以解决，跨节点Join性能较差，逻辑复杂。尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。

查询优化

在日常的系统开发中，由于初期考虑的不周，或者因为疏忽，会导致 SQL 的执行效率，很低。所以 SQL 执行的优化在这种情况下就显得很重要，而 SQL 优化的过程中，我们主要会用到 2 个MySQL 提供的功能，一个是慢查询log，一个是查询计划分析。

慢查询日志

MySQL5.0以上的版本可以支持将执行比较慢的SQL语句记录下来。我们可以通过设置 slow_query_log 属性来告诉 MySQL 是否需要启动慢查询日志功能。

show variables like 'slow_query_log';
set global slow_query_log='ON'

而且，我们还能通过参数long_query_time控制执行时间超过多少秒时，才会对该 SQL 进行记录，它的默认值是 10。

show variables like 'long_query_time';

开启慢查询日志后，MySQL 会自动帮我们统计执行时间超过上述阈值的 SQL 指令，并记录起来，存储的位置我们可以通过 slow_query_log_file 属性查看。

show variables like 'slow_query_log_file';

通过慢查询日志，我们就能确认具体是哪条 SQL 执行时间过长。

查询计划分析

明确了哪条 SQL 执行时间过长之后，我们就可以尝试进行优化，在优化之前我们可以用explain这个命令来查看一个SQL语句的执行计划，该SQL语句有没有使用上了索引，有没有做全表扫描，都可以通过explain命令来查看。

explain select * from user;

id	select\_type	table	partitions	type	possible\_keys	key	key\_len	ref	rows	filtered	Extra
1	SIMPLE	user	NULL	ALL	NULL	NULL	NULL	NULL	27	100	NULL

expain出来的信息有10列，分别是id、select_type、table、type、partitions、possible_keys、key、key_len、ref、rows、filtered、Extra,下面对这些字段出现的可能进行解释：

id

id为SELECT的标识符, SQL执行的顺序的标识,SQL从大到小的执行

id相同时，执行顺序由上至下
如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

select_type

查询中每个select子句的类型

SIMPLE(简单SELECT,不使用UNION或子查询等)
PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
UNION(UNION中的第二个或后面的SELECT语句)
DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
UNION RESULT(UNION的结果)
SUBQUERY(子查询中的第一个SELECT)
DEPENDENT SUBQUERY(子查询中的第一个SELECT，表示这个subquery的查询要受到外部表查询的影响)
DERIVED(派生表的SELECT, FROM子句的子查询)
materialized(被物化的子查询)
UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
UNCACHEABLE UNION：UNION操作中，内层的不可被物化的子查询（类似于UNCACHEABLE SUBQUERY）

table

显示这一行的数据是关于哪张表的，有时不是真实的表名字,看到的是<derived N>(N是个数字,表示第几步执行的结果)。如果是尖括号括起来的<union M,N>，与<derived N>类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

mysql> explain select * from (select * from ( select * from t1 where id=2602) a) b;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | <derived3> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  3 | DERIVED     | t1         | const  | PRIMARY,idx_t1_id | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

type

依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。

system：表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index
const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描
eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref
ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。
fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引
ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。

例如：
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;

index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range
unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值
index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
range：索引范围扫描，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。
index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。按照官方文档的说法：

● If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, the Extracolumn says Using index. An index-only scan usually is faster than ALL because the size of the index usually is smaller than the table data.
● A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra column.
以上说的是索引扫描的两种情况，一种是查询使用了覆盖索引，那么它只需要扫描索引就可以获得数据，这个效率要比全表扫描要快，因为索引通常比数据表小，而且还能避免二次查询。在extra中显示Using index，反之，如果在索引上进行全表扫描，没有Using index的提示。
all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

partitions

版本5.7以前，该项是explain partitions显示的选项，5.7以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。

CREATE TABLE trb1 (id INT, name VARCHAR(50), purchased DATE)
    PARTITION BY RANGE(id)
    (
        PARTITION p0 VALUES LESS THAN (3),
        PARTITION p1 VALUES LESS THAN (7),
        PARTITION p2 VALUES LESS THAN (9),
        PARTITION p3 VALUES LESS THAN (11)
    );

INSERT INTO trb1 VALUES
    (1, 'desk organiser', '2003-10-15'),
    (2, 'CD player', '1993-11-05'),
    (3, 'TV set', '1996-03-10'),
    (4, 'bookcase', '1982-01-10'),
    (5, 'exercise bike', '2004-05-09'),
    (6, 'sofa', '1987-06-05'),
    (7, 'popcorn maker', '2001-11-22'),
    (8, 'aquarium', '1992-08-04'),
    (9, 'study desk', '1984-09-16'),
    (10, 'lava lamp', '1998-12-25');

比如上述根据 id 进行分区，当我们执行 explain 时，就能看到 partitions 不为 NULL 的情况。

mysql> EXPLAIN SELECT * FROM trb1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: trb1
   partitions: p0,p1,p2,p3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using filesort

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询。

key

key列显示MySQL实际决定使用的键（索引），select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意，mysql的ICP特性使用到的索引不会计入其中。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。不损失精确性的情况下，长度越短越好。

ref

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

rows

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

filtered

这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。

Extra

该列包含MySQL解决查询的详细信息,有以下几种常见情况：

distinct：在select部分使用了distinc关键字
no tables used：不带from字句的查询或者From dual查询
使用not in()形式子查询或not exists运算符的连接查询，这种叫做反连接。即，一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表。
using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中
using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。
using join buffer（block nested loop），using join buffer（batched key accss）：5.6.x之后的版本优化关联查询的BNL，BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。
using sort_union，using_union，using intersect，using sort_intersection：
1. using intersect：表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集
2. using union：表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集
using sort_union和using sort_intersection：与前面两个对应的类似，只是他们是出现在用and和or查询信息量大时，先查询主键，然后进行排序合并后，才能读取记录并返回。
using temporary：表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。
using where：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition
firstmatch(tb_name)：5.6.x开始引入的优化子查询的新特性之一，常见于where字句含有in()类型的子查询。如果内表的数据量比较大，就可能出现这个
loosescan(m..n)：5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个

除了这些之外，还有很多查询数据字典库，执行计划过程中就发现不可能存在结果的一些提示信息

总结

EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
EXPLAIN不考虑各种Cache
EXPLAIN不能显示MySQL在执行查询时所作的优化工作
部分统计信息是估算的，并非精确值
EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

文章说明

更多有价值的文章均收录于贝贝猫的文章目录

stun

创作声明：本文基于下列所有参考内容进行创作，其中可能涉及复制、修改或者转换，图片均来自网络，如有侵权请联系我，我会第一时间进行删除。

参考内容

[1] 一条sql语句在mysql中如何执行的
[2] MySQL
[3] 索引和锁
[6] 《高性能 MySQL》
[7] MySQL binlog格式解析
[8] MySQL Explain详解
[9] Mysql优化之explain详解

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL

引言