mysql底层原理面试题，看这一篇就足够

1. 什么是mysql？

针对这个问题，各方面文档很多，基本都是说mysql是一个开源的关系型数据库管理系统，其实，归根到底，说的通俗易懂一点，mysql是一个按照标准数据结构和格式存储的文件系统。

2. mysql存储引擎

2.1 mysql存储引擎有哪些

这是一个很常见的面试题，其实就是让我们说出并比较InnoDB和MyISAM。MySQL5.5之前，默认的存储引擎是MyISAM，之后都改为InnoDB了，具体你使用的mysql的存储引擎是什么，以及mysql存储引擎有哪些，我们可以在mysql上执行命令show engines;查看：

• 其中Support表示是否支持，DEFAULT表示默认的
• Transactions表示是否支持事务
• XA（eXtended Architecture）表示是否支持分布式事务标准，其实也就是我们常说的两阶段提交协议
• Savepoints是保存点，与rollback结合使用，实现回滚到指定保存点

2.2 各存储引擎的区别

其实看完2.1的图片，我们基本能够对InnoDB和MyISAM进行一个直观的比较了：

InnoDB引擎特点：
        1.支持事务：支持4个事务隔离界别，支持多版本读。
        2.行级锁定(更新时一般是锁定当前行)：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响。
        3.读写阻塞与事务隔离级别相关(有多个级别，这就不介绍啦~)。
        4.具体非常高效的缓存特性：能缓存索引，也能缓存数据。
        5.整个表和主键与Cluster方式存储，组成一颗平衡树。(了解)
        6.所有SecondaryIndex都会保存主键信息。(了解)
        7.支持分区，表空间，类似oracle数据库。
        8.支持外键约束，不支持全文索引(5.5之前)，以后的都支持了。
        9.和MyISAM引擎比较，InnoDB对硬件资源要求还是比较高的。
         
        小结：三个重要功能：Supports transactions，row-level locking，and foreign keys

MyISAM引擎特点：
        1.不支持事务
            事务是指逻辑上的一组操作，组成这组操作的各个单元，要么全成功要么全失败。
        2.表级锁定
            数据更新时锁定整个表：其锁定机制是表级锁定，也就是对表中的一个数据进行操作都会将这个表锁定，其他人不能操作这个表，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能。
        3.读写互相阻塞
            不仅会在写入的时候阻塞读取，MyISAM还会再读取的时候阻塞写入，但读本身并不会阻塞另外的读。
        4.只会缓存索引
            MyISAM可以通过key_buffer_size的值来提高缓存索引，以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据。
         
        5.读取速度较快
            占用资源相对较少
        6.不支持外键约束，但只是全文索引
        7.MyISAM引擎是MySQL5.5版本之前的默认引擎，是对最初的ISAM引擎优化的产物。

3. mysql事务隔离级别

3.1 什么是事务

事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。简单理解就是：一系列相关的操作，要么全部成功，要么全部不执行。

3.2 事务的 ACID

事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持续性（ Durability ）。这四个特性简称为 ACID 特性：

1 、原子性。事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做

2 、一致性。事 务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。

3 、隔离性。一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

4 、持续性。也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

3.3 mysql的四种隔离级别和分别存在的问题

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否
上面所说的存在的问题，都是针对并发场景下，多个事务之间并行操作，由于mysql设置的隔离级别而可能出现的问题，下面就一一进行详细说明：

• 脏读：A，B两个事务，A开启了事物（begin transaction），进行一个当前读操作（insert，update，delete），但是此时并未进行提交（commit），如果B事务能够读到A事物没有提交的内容，就叫做脏读，因为后续A可能进行回滚之类的操作，这个数据不是最终的数据。在读未提交（read-uncommitted）这个隔离级别下，就会出现这种情况。
• 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致，这个就叫做不可重复读不可重复读针对的是两个事物之间穿插update操作，导致每次读到的数据内容不一致
• 幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。幻读针对的是两个事物之间穿插insert或者delete操作，导致每次读到数据的条数不一致

4. MVCC多版本并发控制初探

4.1 当前读和快照读

深入探究MVCC之前，我们先要对当前读和快照读有一个大概的了解和概念区分：

• 当前读：就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操做
• 快照读：不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。
  Read Committed：每次select，都生成一个快照读，
  Repeatable Read：开启事务后第一个select语句才是快照读的地方。
  Serializable：快照读会退化为当前读。

4.2 什么是MVCC

• MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。常见的数据库并发问题如下：

读-读：不存在任何问题，也不需要并发控制
读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

MVCC就是为了实现读-写冲突（快找读和数据库的当前读冲突）不加锁，提升数据库性能设计的。而悲观锁或者乐观锁可以解决写写冲突，这样针对不同的场景，可以使用mvcc+乐观锁或者mvcc+悲观锁来实现数据库的事务，同时保障数据库的性能。

4.3 MVCC的实现依赖

MVCC的实现原理主要是依赖mysql每条记录中的 3个默认字段（DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID），和Read View（读视图）以及 undo日志来实现的。三个默认字段具体含义：

DB_TRX_ID：
6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
DB_ROLL_PTR：
7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
DB_ROW_ID：
6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引
实际还有一个删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag变了

针对MVCC实现，很多文档只会讲到隐藏的事务id列和删除事务id列（其实，也没有删除事务id列，mysql进行delete时，会给对象打上delete flag，标记为删除，最终的删除操作是purge线程完成的），然后可见性是满足两个条件：1.事务id列小于当前事务id；2.删除事物id为空或者大于当前事务id的列，其实光靠这两列是没法保证可重复读的。比如A事务先开始，事务id为10，然后B事务开始，事务id为11，这个时候按照上面的可见性条件，A事务对某条记录的更新，提交后对于B事务都是可见的。其实这种是为了方便你理解MVCC隐藏列和大概的MVCC机制。

在 InnoDb 存储引擎里，每个数据页中都会有两个虚拟的行记录，用来限定记录的边界，分别是：Infimum Record 和 Supremum Record，Infimum 是比该页中任何记录都要小的值，而 Supremum 比该页中最大的记录值还要大，这两条记录在创建页的时候就有了，并且不会删除/font>。

5. mysql事务日志

一条sql的执行历程：

mysql 主要分为两层，与客户端直接交互的是 server 层，包括连接的建立和管理、词法分析、语法分析、执行计划与具体 sql 的选择都是在 server 层中进行的，binlog 就是在 server 层中由 mysql server 实现的，而 innodb 作为具体的一个存储引擎，他通过 redolog 实现了 crash safe 的支持

5.1 redo log、bin log和undo log

log	作用	存储内容
redo log	mysql宕机之后进行数据页恢复	物理格式的日志，记录的是物理数据页面的修改的信息（数据库中每个页的修改），面向的是表空间、数据文件、数据页、偏移量等
bin log	进行数据同步，比如主从备份，cannal同步	逻辑格式的日志，可以简单理解是记录执行过的事务中的sql语句
undo log	用于事务回滚，同时可以提供多版本并发控制（MVCC）下的读	逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的

提交事务时，三个log写入顺序是：开启事务 → ddl&dml操作 → 生成反向sql写入undo log → 写redo log（状态为prepare） → 写binlog → 提交事务commit阶段，成功后将redo log状态改为commit

• redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。
  redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。
  在概念上，innodb通过force log at commit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化；
  为了确保每次日志都能写入到事务日志文件中，在每次将log buffer中的日志写入日志文件的过程中都会调用一次操作系统的fsync操作(即fsync()系统调用)。因为MariaDB/MySQL是工作在用户空间的，MariaDB/MySQL的log buffer处于用户空间的内存中。要写入到磁盘上的log file中(redo:ib_logfileN文件,undo:share tablespace或.ibd文件)，中间还要经过操作系统内核空间的os buffer，调用fsync()的作用就是将OS buffer中的日志刷到磁盘上的log file中。
• 事务提交的时候，一次性将事务中的所有sql语句按照一定的格式记录到bin log中
• undo log用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录

5.2 undo日志实现记录版本线性表（链表）

undo log主要分为两种：

• insert undo log
  代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
• update undo log
  事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

purge

 - 从前面的分析可以看出，为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的deleted_bit，并不真正将过时的记录删除。
 - 为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。为了不影响MVCC的正常工
作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;如果某个
记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全
清除的。

当前读操作时，数据库会先为该行加锁，然后把该行原始数据拷贝到undo log中，如果发现该行记录已经有undo log了，那么最新的旧数据作为链表的表头，插在该行记录的undo log最前面，修改完成之后，改隐藏字段的事务ID，回滚指针指向刚刚拷贝到undo log的副本记录。这样就形成了一个历史数据回溯的链表。MVCC模式下，在读视图确定可以看见的记录行时，可以通过undo log链表拉取对应的历史记录数据版本。

5.3 redo日志环形存储区域

mysql 有一个基本的技术理念，那就是 WAL，即 Write-Ahead Logging，先写日志，再写磁盘，从而保证每一次操作都有据可查，这里所说的“先写日志”中的日志就包括 innodb 的 redolog。

redolog 与持续向后添加的 binlog 不同，他只占用预先分配的一块固定大小的磁盘空间，在这片空间中，redolog 采用循环写入的方式写入新的数据。

RingBuffer的好处：

1. 基于数组实现，内存被循环使用，减少了内存分配、回收扩容等操作。
2. 对于只有单个读取和写入进程的场景下，读取写入分别在环的不同位置进行，因此，读写过程无需加锁，从而能够让缓存的读写更为高效。

6. Read View(读视图)

如果你不知道Read View(读视图)，去了解MVCC，基本都是没有触摸到真正的MVCC实现原理。

6.1 什么是Read View?

Read View就是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)
Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。
不同的隔离级别，生成ReadView的时机不同：
READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。
REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

6.2 Read View可见性算法?

Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的DB_TRX_ID（即当前事务ID）取出来，与系统当前其他活跃事务的ID去对比（由Read View维护），如果DB_TRX_ID跟Read View的属性做了某些比较，不符合可见性，那就通过DB_ROLL_PTR回滚指针去取出Undo Log中的DB_TRX_ID再比较，即遍历链表的DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的DB_TRX_ID, 那么这个DB_TRX_ID所在的旧记录就是当前事务能看见的最新老版本。

trx_list（当前视图的活跃事务列表）
一个数值列表，用来维护Read View生成时刻系统正活跃的事务ID
up_limit_id
记录trx_list列表中事务ID最小的ID
low_limit_id
ReadView生成时刻系统尚未分配的下一个事务ID，也就是目前已出现过的事务ID的最大值+1

• 首先比较DB_TRX_ID < up_limit_id, 如果小于，则当前事务能看到DB_TRX_ID 所在的记录，如果大于等于进入下一个判断
• 接下来判断 DB_TRX_ID 大于等于 low_limit_id , 如果大于等于则代表DB_TRX_ID 所在的记录在Read View生成后才出现的，此时通过DB_ROLL_PTR回滚指针去取出Undo Log中的DB_TRX_ID再比较，即遍历链表的DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的DB_TRX_ID, 那么这个DB_TRX_ID所在的旧记录就是当前事务能看见的最新老版本，没有的话则表示是后面提交的，不考虑，如果小于则进入下一个判断
• 判断DB_TRX_ID 是否在活跃事务之中，trx_list.contains(DB_TRX_ID)，如果在，则代表我Read View生成时刻，你这个事务还在活跃，还没有Commit，你修改的数据，我当前事务也是看不见的；如果不在，则说明，你这个事务在Read View生成之前就已经Commit了，你修改的结果，我当前事务是能看见的

7. mysql索引

7.1 什么是索引？

索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数

7.2 索引存储类型（B+Tree，B-Tree）

MySQL的索引是由存储引擎来实现的。由于存储引擎不同，所以具有不同的索引类型，如BTree索引，B+Tree索引，哈希索引，全文索引等。这里由于主要介绍BTree索引和B+Tree索引，我们平时使用最多的InnoDB引擎就是基于B+Tree索引的。

BTree(又称为B-Tree)是一个平衡搜索多叉树。BTree的结构如下图：

假设树的度为2d(d>1)，高度为h，那么BTree有以下性质：

• 每个叶子结点的高度一样，等于h；
• 每个非叶子结点由n-1个key和n个指针组成，key和指针相互隔离，结点两端一定是key；
• 叶子结点指针为null；
• 非叶子结点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的其它列的数据；
  在BTree中，对索引列是顺序存储的，所以很适合查找范围数据和ORDER BY操作。

B+Tree是BTree的一种变种。B+Tree和BTree的不同主要在于：

• B+Tree中的非叶子结点不存储数据，只存储键值；
• B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址；
• B+Tree的每个非叶子节点由n个键值key和n个指针point组成；

一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针，结构大致如下：

7.3 索引的优缺点

创建索引的优点，最简单的就是可以大大提高系统的性能，具体如下：

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？这种想法固然有其合理性，然而也有其片面性。虽然，索引有许多优点，但是，为表中的每一个列都增加索引，是非常不明智的。这是因为，增加索引也有许多不利的一个方面：

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

7.4 聚簇索引和非聚簇索引存储结构（innodb引擎）

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。

Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。

非聚簇索引，又叫二级索引，或者辅助索引。非聚簇索引底层也是一颗B+树，只是叶子节点中保存的不是行记录数据，而是行的主键值。当通过二级索引查找行，存储引擎需要在二级索引中找到相应的叶子节点，获得行的主键值，然后使用主键去聚簇索引中查找数据行（覆盖索引），，也就是我们俗称的回表（如果查询的字段在索引上的某个字段，或者查询的是主键，则不需要回表）。

7.5 联合索引及最左前缀(leftmost prefix of the index)原则

联合索引又叫复合索引，是指两个或更多个列上的索引。对于复合索引:Mysql从左到右的使用索引中的字段，遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

8. mysql锁

首先对mysql锁进行划分：

8.1 行锁

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。

8.2 表锁

表级锁为表级别的锁定，会锁定整张表，可以很好的避免死锁，是 MySQL 中最大颗粒度的锁定机制。

8.3 Gap Lock

间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。
使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。
间隙锁只阻止其他事务插入到间隙中，他们不阻止其他事务在同一个间隙上获得间隙锁，所以 gap x lock 和 gap s lock 有相同的作用。

8.4 Next-Key Locks

Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁

8.5 死锁产生的两种常见场景

待补充

9. mysql分库分表

9.1 同物理机的不分库只分表

9.2 同物理机的分库分表

9.3 不同物理机的分库分表

9.4 SnowFlake（雪花算法）

SnowFlake 算法，是 Twitter 开源的分布式 id 生成算法。其核心思想就是：使用一个 64 bit 的 long 型的数字作为全局唯一 id。在分布式系统中的应用十分广泛，且ID 引入了时间戳，基本上保持自增。
这种方案和UUID其实基本一致，大致来说是一种以划分命名空间的方案。

SnowFlake算法的优点：

（1）高性能高可用：生成时不依赖于数据库，完全在内存中生成。

（2）容量大：每秒中能生成数百万的自增ID。

（3）ID自增：存入数据库中，索引效率高。


SnowFlake算法的缺点：

依赖与系统时间的一致性，如果系统时间被回调，或者改变，可能会造成id冲突或者重复；

机器id的获取（10bit的workerId）

可以根据固定时间戳内生成的序列号推算数据量

其实算法不是唯一的，主要是思想，针对雪花算法的实践，可以参考美团leaf的实现，优化雪花算法和百度的UidGenerator实现

10. Mysql join查询算法和优化原理

针对多表的join，MySQL官方文档指出，不支持Hash Join，也不支持Sort Merge Join，只支持 Nested Loop Join算法。当然，mysql针对Nested Loop Join也做了一定的优化，主要有下面三种：

• Simple Nested Loop Join（SNLJ）就是两层循环全量扫描连接的两张表，得到符合条件的两条记录则输出，也就是让两张表做笛卡尔积，比较次数是R * S，是比较暴力的算法，会比较耗时。
• Index Nested Loop Join（INLJ）在SNLJ的基础上做了优化，MySQL数据库中使用较多的就是这种算法。在Join的优化时候，通常都会建议在内表建立索引，以此降低Nested-Loop Join算法的开销，通过连接条件确定可用的索引，在Inner Loop中扫描索引而不去扫描数据本身，通过索引可以大幅降低内表的Join的比较次数，每次比较1条外表的记录，其实就是一次indexlookup（索引查找），而每次index lookup的成本就是树的高度，即IndexHeight，所以比较次数是R * IndexHeight。而INLJ也有缺点，就是如果扫描的索引是非聚簇索引，并且需要访问非索引的数据，会产生一个回表读取数据的操作，这就多了一次随机的I/O操作。
  MySQL 5.6版本开始增加了提高表join性能的算法:Batched Key Access (BKA)的新特性，这个是针对内表建立了索引的情况下才会使用，也就是针对INLJ的优化。

Batched Key Access Join算法的工作步骤如下：

1) 将外部表中相关的列放入Join Buffer中。

2) 批量的将Key（索引键值）发送到Multi-Range Read（MRR）接口。

3) Multi-Range Read（MRR）通过收到的Key，根据其对应的ROWID进行排序，然后再进行数据的读取操作。

4) 返回结果集给客户端。

Batched Key Access Join配置：

开启 ：SET global optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on'

修改buffer size参数： join_buffer_size

• Block Nested Loop Join（BNLJ）一般情况下，MySQL优化器在索引可用的情况下，会优先选择使用INLJ算法，但是在无索引可用，或者判断full scan可能比使用索引更快的情况下，还是不会选择使用过于粗暴的SNLJ算法。这里就出现了BNLJ算法了，BNLJ在SNLJ的基础上使用了join buffer，会提前读取Inner Loop所需要的记录到buffer中，以提高Inner Loop的效率。