四种mysql存储引擎

前言
数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以 获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
存储引擎查看
MySQL给开发者提供了查询存储引擎的功能，我这里使用的是MySQL5.1，可以使用：
SHOW ENGINES
命令来查看MySQL使用的引擎，命令的输出为（我用的Navicat Premium）：

看到MySQL给用户提供了这么多存储引擎，包括处理事务安全表的引擎和出来了非事物安全表的引擎。
如果要想查看数据库默认使用哪个引擎，可以通过使用命令：
SHOW VARIABLES LIKE ‘storage_engine’;
来查看，查询结果为：

在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。Support列的值表示某种引擎是否能使用：YES表示可以使用、NO表示不能使用、DEFAULT表示该引擎为当前默认的存储引擎 。下面来看一下其中几种常用的引擎。
InnoDB存储引擎
InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。InnoDB主要特性有：
1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合
2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的
3、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上
4、InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键
5、InnoDB被用在众多需要高性能的大型数据库站点上
InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件
MyISAM存储引擎
MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。MyISAM主要特性有：
1、大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持
2、当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成
3、每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16
4、最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上
5、BLOB和TEXT列可以被索引
6、NULL被允许在索引的列中，这个值占每个键的0~1个字节
7、所有数字键值以高字节优先被存储以允许一个更高的索引压缩
8、每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快
9、可以把数据文件和索引文件放在不同目录
10、每个字符列可以有不同的字符集
11、有VARCHAR的表可以固定或动态记录长度
12、VARCHAR和CHAR列可以多达64KB
使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）
MEMORY存储引擎
MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。MEMORY主要特性有：
1、MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度
2、MEMORY存储引擎执行HASH和BTREE缩影
3、可以在一个MEMORY表中有非唯一键值
4、MEMORY表使用一个固定的记录长度格式
5、MEMORY不支持BLOB或TEXT列
6、MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引
7、MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）
8、MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享
9、当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）
存储引擎的选择
不同的存储引擎都有各自的特点，以适应不同的需求，如下表所示：

如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率
如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果
如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive
使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能

MyISAM 和 InnoDB 的区别

先看下《高性能MySQL》中对于他们的评价：
InnoDB：MySQL默认的事务型引擎，也是最重要和使用最广泛的存储引擎。它被设计成为大量的短期事务，短期事务大部分情况下是正常提交的，很少被回滚。InnoDB的性能与自动崩溃恢复的特性，使得它在非事务存储需求中也很流行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

MyISAM：在MySQL 5.1 及之前的版本，MyISAM是默认引擎。MyISAM提供的大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM并不支持事务以及行级锁，而且一个毫无疑问的缺陷是崩溃后无法安全恢复。正是由于MyISAM引擎的缘故，即使MySQL支持事务已经很长时间了，在很多人的概念中MySQL还是非事务型数据库。尽管这样，它并不是一无是处的。对于只读的数据，或者表比较小，可以忍受修复操作，则依然可以使用MyISAM（但请不要默认使用MyISAM，而是应该默认使用InnoDB）

1、 存储结构
MyISAM：每个MyISAM在磁盘上存储成三个文件。分别为：表定义文件、数据文件、索引文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。
InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
2、 存储空间
MyISAM： MyISAM支持支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后，不会再进行修改操作，可以使用压缩表，极大的减少磁盘的空间占用。
InnoDB： 需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
3、 可移植性、备份及恢复
MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。
4、 事务支持
MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。
InnoDB：提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
5、 AUTO_INCREMENT
MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。
6、 表锁差异
MyISAM： 只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
InnoDB： 支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。
7、 全文索引
MySql全文索引
MyISAM：支持 FULLTEXT类型的全文索引
InnoDB：不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
8、表主键
MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。
InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。
9、表的具体行数
MyISAM： 保存有表的总行数，如果select count() from table;会直接取出出该值。
InnoDB： 没有保存表的总行数，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。
10、CRUD操作
MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。
InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。
11、 外键
MyISAM：不支持
*InnoDB：支持

MySQL索引详解

前言

由于MySQL的索引中最重要的数据结构就是B+树，所以前面我们先大概讲讲B+树的原理

B+ Tree 原理

1. 数据结构

B Tree 指的是 Balance Tree，也就是平衡树。平衡树是一颗查找树，并且所有叶子节点位于同一层。
B+ Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现，它具有 B Tree 的平衡性，并且通过顺序访问指针来提高区间查询的性能。
在 B+ Tree 中，一个节点中的 key 从左到右非递减排列，如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1，且不为 null，则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1。

2. 操作

进行查找操作时，首先在根节点进行二分查找，找到一个 key 所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出 key 所对应的 data。
插入删除操作会破坏平衡树的平衡性，因此在插入删除操作之后，需要对树进行一个分裂、合并、旋转等操作来维护平衡性。

3. 与红黑树的比较

红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B+ Tree 作为索引结构，主要有以下两个原因：
（一）更少的查找次数
平衡树查找操作的时间复杂度等于树高 h，而树高大致为 O(h)=O(logdN)，其中 d 为每个节点的出度。
红黑树的出度为 2，而 B+ Tree 的出度一般都非常大，所以红黑树的树高 h 很明显比 B+ Tree 大非常多，查找的次数也就更多。
（二）利用磁盘预读特性
为了减少磁盘 I/O，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的旋转时间，速度会非常快。
操作系统一般将内存和磁盘分割成固态大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。并且可以利用预读特性，相邻的节点也能够被预先载入。

MySQL 索引

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

1. B+Tree 索引

是大多数 MySQL 存储引擎的默认索引类型。
因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。
除了用于查找，还可以用于排序和分组。
可以指定多个列作为索引列，多个索引列共同组成键。
适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。
InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

2. 哈希索引

哈希索引能以 O(1) 时间进行查找，但是失去了有序性：

• 无法用于排序与分组；
• 只支持精确查找，无法用于部分查找和范围查找。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

3. 全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。
查找条件使用 MATCH AGAINST，而不是普通的 WHERE。
全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。
InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

4. 空间数据索引

MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。
必须使用 GIS 相关的函数来维护数据。

索引优化

1. 独立的列

在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。
例如下面的查询不能使用 actor_id 列的索引：

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;复制代码

2. 多列索引

在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引。

SELECT film_id, actor_ id FROM sakila.film_actor WHERE actor_id = 1 AND film_id = 1;复制代码

3. 索引列的顺序

让选择性最强的索引列放在前面。
索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，查询效率也越高。
例如下面显示的结果中 customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;复制代码

staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
               COUNT(*): 16049复制代码

4. 前缀索引

对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。
对于前缀长度的选取需要根据索引选择性来确定。

5. 覆盖索引

索引包含所有需要查询的字段的值。
具有以下优点：

• 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
• 一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。

• 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

  6. 最左前缀原则

  顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上
  联合索引本质：
  当创建(a,b,c)联合索引时，相当于创建了(a)单列索引，(a,b)联合索引以及(a,b,c)联合索引
  想要索引生效的话,只能使用 a和a,b和a,b,c三种组合。

  索引的优点

• 大大减少了服务器需要扫描的数据行数。

• 帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，因为不需要排序和分组，也就不需要创建临时表）。

• 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

  索引的使用条件

• 对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；

• 对于中到大型的表，索引就非常有效；
• 但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

聚簇索引 & 非聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。比如，InnoDB的聚簇索引使用B+Tree的数据结构存储索引和数据。
当表有聚簇索引时，它的数据行实际上存放在索引的叶子页(leaf page)中。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引（不过，覆盖索引可以模拟多个聚簇索引的情况）。

• 术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。
• 聚簇索引的二级索引：叶子节点不会保存引用的行的物理位置，而是保存行的主键值。

对于聚簇索引的存储引擎，数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的，如果主键不是自增id，可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。
对于非聚簇索引的存储引擎，表数据存储顺序与索引顺序无关，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。
下图1展示了聚簇索引的记录是如何存放的。注意到，节点页只包含了索引列，叶子页包含行的全部数据，这是B+Tree的数据结构。在这个案例中，索引列包含的是整数值。

图1 聚簇索引的数据分布
InnoDB将通过主键聚集数据，图1中的“被索引的列”就是主键列。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录，包含相邻键值的页面可能会相距甚远。
聚簇主键可能对性能有帮助，但也可能导致严重的性能问题。所以需要仔细地考虑聚簇索引，尤其是将表的存储引擎从InnoDB改成其他引擎的时候（反过来也一样）。
聚簇的数据有一些重要的优点：

• 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有聚簇索引，则每封邮件都可能多一次磁盘IO。
• 数据访问更快。聚簇索引将索引和数据保存在同一个B+Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

如果设计表和查询时能充分利用上面的优点，就能极大地提升性能。但是，聚簇索引也有一些缺点：

• 聚簇数据最大限度地提高了IO密集型应用的性能，但如果数据全部放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
• 插入速度严重依赖于插入顺序。按照主要的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用optimize table命令重新组织一下表。
• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
• 基于聚簇索引的表插入新行，或者主键被更新导致需要移动行的时候，可能面临”页分裂（page split)“的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次分裂操作。页分裂会导致表占用更多的磁盘空间。
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
• 二级索引（非聚簇索引）可能比想象的要更大，因为在二给索引的叶子节点包含了引用行的主键列。
• 二级索引访问需要两次索引查找，而不是一次。

最后一点可能让人有些疑惑，为什么二级索引需要两次索引查找？答案在于二级索引中保存的”行指针“的实质。要记住，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。
这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这里做了重复的工作：两次B-Tree查找而不是一次。对于 InnoDB，自适应哈希索引能够减少这样的重复工作。

InnoDB和MyISAM的数据分布对比

聚簇索引和非聚簇索引的数据分布有区别，以及对应的主要索引和二级索引的数据分布也有区别，通常会让人感到困扰和意外。来看看InnoDB和MyISAM是如何存储下面这个表的：

create table layout_test(
    col1 int not null,
    col2 int not null,
    primary key(col1),
    key(col2)
);

假设该表的主键取值为1~10000，按照随机顺序播放并使用optimize table命令做了优化。换句话说，数据在磁盘上的存储方式已经最优，但行的顺序是随机的。列col2的值是从1~100之间随机赋值，所以有很多重复的值。

MyISAM的数据布局

MyISAM的B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。MyISAM按照数据插入的顺序存储在磁盘上，如下图2所示，左边为行号(row number)，从0开始。因为元组的大小固定，所以MyISAM很容易的从表的开始位置找到某一字节的位置。

图2 MyISAM表layout_test的数据分布
MyISAM建立的primary key的索引结构大致如图3和图4所示。MyISAM不支持聚簇索引，索引中每一个叶子节点仅仅包含行号(row number)，且叶子节点按照col1的顺序存储。MyISAM是按列值与行号来组织索引的。

图3 MyISAM表layout_test的主键分布
在图4中，表一共有三列，假设以Col1为主键，可以看出，MyISAM的叶子节点中保存的实际上是指向存放数据的物理块的指针。从MYISAM存储的物理文件看出，MyISAM引擎的索引文件（.MYI）和数据文件(.MYD)是相互独立的，索引文件仅仅保存数据记录的地址。

图4 MyISAM主键索引的分布
下图5显示col2 的索引结构，与图3的primary key对比，索引中每一个叶子节点仅仅包含行号(row number)，且叶子节点按照col2的顺序存储。在图6中，在Col2建立一个辅助索引，与图4对比，MyISAM的叶子节点也是保存指向存放数据的物理块的指针。
所以，结论是MyISAM的primary key和辅助索引没有任何区别。只是Primary key要求key唯一非空，而辅助索引的key可以重复。

图5 MyISAM表layout_test的col2列索引的分布

图6 MyISAM辅助索引的分布
因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

InnoDB的数据布局

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
图7和与图3 MyISAM对比看出，InnoDB索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回流指针以及所有的剩余列（在这个例子中是col2)。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。这种索引叫做聚簇索引。
图8可以看到叶节点包含了完整的数据记录。
因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

图7 InnoDB表layout_test的主键分布

图8 InnoDB主键索引的分布
还有一点和MyISAM的不同是，InnoDB的二级索引和聚簇索引很不相同。InnoDB二级索引的叶子节点中存储的不是”行指针“，而是主键值，并以此作为指向行的“指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动行时无须更新二级索引中的这个“指针”。
下图9展示了示例表的二级索引col2索引。每一个叶子节点都包含了索引列（这里是col2)，紧接着是主键值(col1)。图10展示了InnoDB的所有辅助索引都引用主键作为data域。

图9 InnoDB表layout_test的col2列索引的分布

图10 InnoDB辅助索引的分布
InnoDB 表是基于聚簇索引建立的。因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过，它的辅助索引（Secondary Index， 也就是非主键索引）也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。如果想在表上定义 、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。

InnoDB与MyIASM索引和数据布局对比

图7描述InnoDB和MyISAM如何存放表的抽象图。对比InnoDB和MyISAM的主键索引与二级索引。
InnoDB的的二级索引的叶子节点存放的是KEY字段加主键值。因此，通过二级索引查询首先查到是主键值，然后InnoDB再根据查到的主键值通过主键索引找到相应的数据块。而MyISAM的二级索引叶子节点存放的还是列值与行号的组合，叶子节点中保存的是数据的物理地址。所以可以看出MYISAM的主键索引和二级索引没有任何区别，主键索引仅仅只是一个叫做PRIMARY的唯一、非空的索引，且MYISAM引擎中可以不设主键。

图7 聚簇和非聚簇表对比图
为了更形象说明这两种索引的区别，我们假想一个表如下图8存储了4行数据。其中id作为主索引，name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。
对于聚簇索引存储来说，行数据和主键B+树存储在一起，辅助键B+树只存储辅助键和主键，主键和非主键B+树几乎是两种类型的树。对于非聚簇索引存储来说，主键B+树在叶子节点存储指向真正数据行的指针，而非主键。
InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用”where id = 14”这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。
MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

图8 聚簇和非聚簇表形象对比图
我们重点关注聚簇索引，看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，这不是多此一举吗？聚簇索引的优势在哪？
1 由于行数据和叶子节点存储在一起，这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
2 辅助索引使用主键作为”指针” 而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个”指针”。也就是说行的位置（实现中通过16K的Page来定位，后面会涉及）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。

在InnoDB表中按主键顺序插入行

如果正在使用InnoDB表并且没有什么数据需要聚集，那么可以定义一个代理键作为主键，这种主键的数据应该和应用无关，最简单的方法是使用auto_increment自增列。这样可以保证数据行是按照顺序写入，对于根据主键做关联操作的性能也会更好。
最好避免随机的聚簇索引，特别对于I/O密集型的应用。例如，从性能的角度考虑，使用UUID作为聚簇索引会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。
为了演示这一点，我们做如下两个基准测试。第一个使用整数ID插入shopinfo表，整数ID自增且为主键：

CREATE TABLE `shopinfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  `shop_id` int(11) NOT NULL COMMENT '商店ID',
  `goods_id` int(11) NOT NULL COMMENT '物品ID',
  `pay_type` int(11) NOT NULL COMMENT '支付方式',
  `price` decimal(10,2) NOT NULL COMMENT '物品价格',
  `comment` varchar(4000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `shop_id` (`shop_id`,`goods_id`),
  KEY `price` (`price`),
  KEY `pay_type` (`pay_type`),
  KEY `idx_comment` (`comment`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商店物品表';

第二个例子是shopinfo_uuid表，除了主键改为UUID，其余和前面的shopinfo表完全相同。

CREATE TABLE `shopinfo_uuid` (
  `uuid` varchar(36) NOT NULL,
  `shop_id` int(11) NOT NULL COMMENT '商店ID',
  `goods_id` int(11) NOT NULL COMMENT '物品ID',
  `pay_type` int(11) NOT NULL COMMENT '支付方式',
  `price` decimal(10,2) NOT NULL COMMENT '物品价格',
  `comment` varchar(4000) DEFAULT NULL,
  PRIMARY KEY (`uuid`),
  UNIQUE KEY `shop_id` (`shop_id`,`goods_id`),
  KEY `price` (`price`),
  KEY `pay_type` (`pay_type`),
  KEY `idx_comment` (`comment`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商店物品表';

我们先向这两个表各插入1万条记录。然后再向这两个表继续插入9万条记录，观察这两个表的插入耗时和表索引大小，下表对测试结果进行比较。其中，查看指定库的指定表shopinfo的索引大小SQL语句：
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES WHERE table_schema = 'study' and table_name = 'shopinfo';

通过测试，插入同样的行数和内容（除主键内容），向UUID主键插入行不仅花费的时间更长，而且索引占用的空间也更大。这一方面是由于主键字段更长，另一方面毫无疑问是由于页分裂和碎片导致的。
如图9所示，由于主键的值是顺序的，InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB默认的最大填充因子是页大小的15/16，留出的部分空间用于以后修改），下一条记录就会写入新的页中。一旦数据按照这样顺序的方式加载，主键页就会近似于被顺序的记录填满，这也是所期望的结果。

图9 向聚簇索引插入顺序的索引值
而当采用UUID的聚簇索引的表往插入数据，如图10所示，因为新行的主键值不一定比之前的插入值大，所以InnoDB无法简单的总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置——通常是已有数据的中间位置——并且分配空间。这会增加很多额外的工作，并导致数据分布不够优化。

图10 向聚簇索引插入无序的值
下面总结使用UUID作为主键的一些缺点：

• 写入目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机I/O；
• 因为写入是乱序的，InnoDB不得不频繁的做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个，包含两个叶子节点和一个父节点。
• 由于频繁的页分裂，页会变得稀疏并被不规则的填充，所以最终数据会有碎片。

把这些随机值载入到聚簇索引以后，需要做一次optimize table来重建表并优化页的填充。
注意，顺序主键也有缺点：对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是auto_increment锁机制；如果遇到这个问题，则可能需要考虑重新设计表或者应用，比如应用层面生成单调递增的主键ID，插表不使用auto_increment机制，或者更改innodb_autonc_lock_mode配置。

存储格式

有了以上的索引知识我们在来分析数据是怎么存储的，InnoDB 存储引擎的逻辑存储结构从大到小依次可以分为：表空间、段、区、页、行。

表空间作为存储结构的最高层，所有数据都存放在表空间中，默认情况下用一个共享表空间 ibdata1 ，如果开启了 innodb_file_per_table 则每张表的数据将存储在单独的表空间中，也就是每张表都会有一个文件，
表空间由各个段构成，InnoDB存储引擎由索引组织的，而索引中的叶子节点用来记录数据，存储在数据段，而非叶子节点用来构建索引，存储在索引段，而回滚段我们在后面分析锁的时候在聊。区是由连续的页组成，任何情况下一个区都是 1MB ，
一个区中可以有多个页，每个页默认为 16KB ，所以默认情况下一个区中可以包含64个连续的页，页的大小是可以通过 innodb_page_size 设置，页中存储的是具体的行记录。一行记录最终以二进制的方式存储在文件里，我们要能够解析出一行记录中每个列的值，存储的时候就需要有固定的格式，至少需要知道每个列占多少空间，而 MySQL 中定义了一些固定长度的数据类型，例如 int、tinyint、bigint、char数组、float、double、date、datetime、timestamp 等，这些字段我们只需要读取对应长度的字节，然后根据类型进行解析即可，对于变长字段，例如 varchar、varbinary 等，需要有一个位置来单独存储字段实际用到的长度，当然还需要头信息来存储元数据，例如记录类型，下一条记录的位置等。下面我们以 Compact 行格式分析一行数据在 InnoDB 中是怎么存储的。

• 变长字段长度列表，该位置用来存储所申明的变长字段中非空字段实际占有的长度列表，例如有3个非空字段，其中第一个字段长度为3，第二个字段为空，第三个字段长度为1，则将用 01 03 表示，为空字段将在下一个位置进行标记。变长字段长度不能超过 2 个字节，所以 varchar 的长度最大为 65535。
• NULL 标志位，占 1 个字节，如果对应的列为空则在对应的位上置为 1 ，否则为 0 ，由于该标志位占一个字节，所以列的数量不能超过 255。如果某字段为空，在后面具体的列数据中将不会在记录。这种方式也导致了在处理索引字段为空的时候需要进行额外的操作。
• 记录头信息，固定占 5 字节，包含下一条记录的位置，该行记录总长度，记录类型，是否被删除，对应的 slot 信息等
• 列数据 包含具体的列对应的值，加上两个隐藏列，事务 ID 列和回滚指针列。如果没有申明主键，还会增加一列记录内部 ID。

下面我们以《MySQL 技术内幕》第二版中的例子分析下一行记录在表空间具体的存储结构。

CREATE TABLE mytest(
t1 varchar(10),
t2 varchar(10),
t3 char(10),
t4 varchar(10)
) engine = innodb;
insert into mytest VALUES('a','bb','bb','ccc');
insert into mytest VALUES('d',NULL,NULL,'fff');

该表定义了 3 个变长字段和 1 个定长字段，然后插入两行记录，第二行记录包含空值，我们打开表空间 mytest.ibd 文件，转换为 16 进制，并定位到如下内容：

//第一行记录
03 02 01 为变长字段长度列表，这里是倒序存放的，分别对应 ccc、bb、a 的长度。
00 表示没有为空的字段
00 00 10 00 2c 为记录头
00 00 00 2b 68 00 没有申明主键，维护内部 ID
00 00 00 00 06 05 事务ID
80 00 00 00 32 01 10 回滚指针
61 第一列 a 的值
62 62 第二列 bb 的值
62 62 20 20 20 20 20 20 20 20 第三列 bb 的值，固定长度 char(10) 以20进行填充
63 63 63 第四列 ccc 的值
//第二行记录
03 01 为变长字段长度列表，这里是倒序存放的，分别对应 fff、a 的长度，第二列位空。
06 转换为二进制为 00000110 表示第二列和第三列为空
00 00 20 ff 98 为记录头
00 00 00 2b 68 01 没有申明主键，维护内部 ID
00 00 00 00 06 06 事务ID
80 00 00 00 32 01 10 回滚指针
64 第一列 d 的值
65 65 65 第四列 fff 的值

到此，我们了解了一个数据行是怎么存储的，然而数据行并不是存储引擎管理的最小存储单位，索引只能够帮助我们定位到某个数据页，每一次磁盘读写的最小单位为也是数据页，而一个数据页内存储了多个数据行，我们需要了解数据页的内部结构才能知道存储引擎怎么定位到某一个数据行。InnoDB 的数据页由以下 7 个部分组成：

• 文件头（File Header） 固定 38 个字节 （页的位置，上一页下一页位置，checksum , LSN）
• 数据页头（ Page Header）固定 56 个字节 包含slot数目，可重用空间起始地址，第一个记录地址，记录数，最大事务ID等
• 虚拟的最大最小记录 （Infimum + Supremum Record）
• 用户记录 （User Records） 包含已经删除的记录以链表的形式构成可重用空间
• 待分配空间 （Free spaces） 未分配的空间
• 页目录 （Page Directory） slot 信息，下面单独介绍
• 文件尾 （File Trailer） 固定8个字节,用来保证页的完整性

页目录里维护多个 slot ，一个 slot 包含多个行记录。每个 slot 占 2 个字节，记录这个 slot 里的行记录相对页初始位置的偏移量。由于索引只能定位到数据页，而定位到数据页内的行记录还需要在内存中进行二分查找，而这个二分查找就需要借助 slot 信息，先找到对应的 slot ，然后在 slot 内部通过数据行中记录头里的下一个记录地址进行遍历。每一个 slot 可以包含 4 到 8 个数据行。如果没有 slot 辅助，链表本身是无法进行二分查找的。

排序

排序有好多种算法来实现，在 MySQL 中经常会带上一个 limit ,表示从排序后的结果集中取前 100 条，或者取第 n 条到第 m 条，要实现排序，我们需要先根据查询条件获取结果集，然后在内存中对这个结果集进行排序，如果结果集数量特别大，还需要将结果集写入到多个文件里，然后单独对每个文件里的数据进行排序，然后在文件之间进行归并，排序完成后在进行 limit 操作。没错，这个就是 MySQL 实现排序的方式，前提是排序的字段没有索引。

CREATE TABLE `person` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;
select city,name,age from person where city='武汉' order by name limit 100  ;

使用 explain 发现该语句会使用 city 索引，并且会有 filesort . 我们分析下该语句的执行流程

• 1.初始化 sortbuffer ，用来存放结果集
• 2.找到 city 索引，定位到 city 等于武汉的第一条记录，获取主键索引ID
• 3.根据 ID 去主键索引上找到对应记录，取出 city,name,age 字段放入 sortbuffer
• 4.在 city 索引取下一个 city 等于武汉的记录的主键ID
• 5.重复上面的步骤，直到所有 city 等于武汉的记录都放入 sortbuffer
• 6.对 sortbuffer 里的数据根据 name 做快速排序
• 7.根据排序结果取前面 1000 条返回
  这里是查询 city,name,age 3个字段，比较少，如果查询的字段较多，则多个列如果都放入 sortbuffer 将占有大量内存空间，另一个方案是只区出待排序的字段和主键放入 sortbuffer 这里是 name 和 id ,排序完成后在根据 id 取出需要查询的字段返回，其实就是时间换取空间的做法，这里通过 max_length_for_sort_data 参数控制，是否采用后面的方案进行排序。

另外如果 sortbuffer 里的条数很多，同样会占有大量的内存空间，可以通过参数 sort_buffer_size 来控制是否需要借助文件进行排序，这里会把 sortbuffer 里的数据放入多个文件里，用归并排序的思路最终输出一个大的文件。
以上方案主要是 name 字段没有加上索引，如果 name 字段上有索引，由于索引在构建的时候已经是有序的了，所以就不需要进行额外的排序流程只需要在查询的时候查出指定的条数就可以了，这将大大提升查询速度。我们现在加一个 city 和 name 的联合索引。

alter table person add index city_user(city, name);

这样查询过程如下：

• 1.根据 city,name 联合索引定位到 city 等于武汉的第一条记录，获取主键索引ID
• 2.根据 ID 去主键索引上找到对应记录，取出 city,name,age 字段作为结果集返回
• 3.继续重复以上步骤直到 city 不等于武汉，或者条数大于 1000

由于联合所以在构建索引的时候，在 city 等于武汉的索引节点中的数据已经是根据 name 进行排序了的，所以这里只需要直接查询就可，另外这里如果加上 city, name, age 的联合索引，则可以用到索引覆盖，不行到主键索引上进行回表。
总结一下，我们在有排序操作的时候，最好能够让排序字段上建有索引，另外由于查询第一百万条开始的一百条记录，需要过滤掉前面一百万条记录，即使用到索引也很慢，所以可以根据 ID 来进行区分，分页遍历的时候每次缓存上一次查询结果最后一条记录的 id ， 下一次查询加上 id > xxxx limit 0,1000 这样可以避免前期扫描到的结果被过滤掉的情况。

InnoDB 存储模型

InnoDB 通过一些列后台线程将相关操作进行异步处理，如下图所示，同时借助缓冲池来减小 CPU 和磁盘速度上的差异。当查询的时候会先通过索引定位到对应的数据页，然后检测数据页是否在缓冲池内，如果在就直接返回，如果不在就去聚簇索引中通过磁盘 IO 读取对应的数据页并放入缓冲池。一个数据页会包含多个数据行。缓存池通过 LRU 算法对数据页进行管理，也就是最频繁使用的数据页排在列表前面，不经常使用的排在队尾，当缓冲池满了的时候会淘汰掉队尾的数据页。从磁盘新读取到的数据页并不会放在队列头部而是放在中间位置，这个中间位置可以通过参数进行修。缓冲池也可以设置多个实例，数据页根据哈希算法决定放在哪个缓冲池。

InnoDB 在更新数据的时候会采用 WAL 技术，也就是 Write Ahead Logging ，这个日志就是 redolog 用来保证数据库宕机后可以通过该文件进行恢复。这个文件一般只会顺序写，只有在数据库启动的时候才会读取 redolog 文件看是否需要进行恢复。该文件记录了对某个数据页的物理操作，例如某个 sql 把某一行的某个列的值改为 10 ，对应的 redolog 文件格式可能为：把第5个数据页中偏移量为99的位置写入一个值 10 。redolog 不是无限大的，他的大小是可以配置的，并且是循环使用的，例如配置大小为 4G ，一共 4 个文件，每个文件 1G 。首先从第一个文件开始顺序写，写到第四个文件后在从第一个文件开始写，类似一个环，用一个后台线程把 redolog 里的数据同步到聚簇索引上的数据页上。写入 redolog 的时候不能将没有同步到数据页上的记录覆盖，如果碰到这种情况会停下来先进行数据页同步然后在继续写入 redolog 。另外执行更新操作的时候，会先更新缓冲池里的数据页，然后写入 redolog ， 这个时候真正存储数据的地方还没有更新，也就是说这时候缓冲池中的数据页和磁盘不一致，这种数据页称为脏页，当脏页由于内存不足或者其他原因需要丢弃的时候，一定要先将该脏页对应的redolog 刷新到磁盘里的真实数据页，不然下次查询的时候由于 redolog 没有同步到磁盘，而查询直接通过索引定位到数据页就会查询出脏数据。
更新的时候先从磁盘或者缓冲池中读取对应的数据页，然后对数据页里的数据进行更改并生成 redolog　到对应的缓冲池（redolog buffer）进行缓存，当事务提交的时候将缓存写入到　redolog　的物理磁盘文件上。这里由于操作系统的文件写入 InnoDB 并没有使用 O_DIRECT 直接写入到文件，为了保证性能而是先写入操作系统的缓存，之后在进行 flush ，所以事务提交的时候 InnoDB 需要在调用一次　fsync 的系统调用来确保数据落盘。为了提高性能 InnoDB 可以通过参数 innodb_flush_log_at_trx_commit 来控制事务提交时是否强制刷盘。默认为 1 ，事务每次提交都需要调用 fsync 进行刷盘，0 表示事务提交的时候不会调用 redolog　的文件写入，通过后台线程每秒同步一次，2 表示事务提交的时候会写入文件但是只保证写入操作系统缓存，不进行 fsync 操作。redolog 文件只会顺序写，所以磁盘操作性能不会太慢，所以建议生产环境都设置为　１　，以防止数据库宕机导致数据丢失。
在执行更新逻辑的时候还会写入另外一个日志：undolog 。这个文件存储在共享表空间中，也就是即使打开了 innodb_file_per_table 参数，所有的表的 undolog 都存储在同一个文件里。该文件主要用来做事务回滚和 MVCC 。undolog 是逻辑日志，也就是他不是记录的将物理的数据页恢复到之前的状态，而是记录的和原 sql 相反的 sql , 例如 insert 对应 delete , delete 对应 insert ，update 对应另外一个 update　。事务回滚很好理解，执行相反的操作回滚到之前的状态，而 MVCC 是指镜像读，当一个事务需要查询某条记录，而该记录已经被其他事务修改，但该事务还没提交，而当前事务可以通过 undolog 计算到之前的值。这里我们只需要知道和 redolog 一样， undolog 也是需要在执行 update 语句的时候在事务提交前需要写入到文件的。另外 undolog 的写入也会有对应的 redolog ，因为 undolog 也需要持久化，通过 WAL 可以提高效率。这里可以总结下，在事务提交的时候要保证 redolog 写入到文件里，而这个 redolog 包含 主键索引上的数据页的修改，以及共享表空间的回滚段中 undolog 的插入。另外 undolog 的清理通过一个后台线程定时处理，清理的时候需要判断该 undolog 是否所有的事务都不会用到。

熟悉 MySQL 的都知道，他通过 binlog 来进行高可用，也就是通过 binlog 来将数据同步到集群内其他的 MySQL 实例。binlog 和 redolog 的区别是，他是在存储引擎上层 Server 层写入的，他记录的是逻辑操作，也就是对应的 sql ,而 redolog 记录的底层某个数据页的物理操作，redolog 是循环写的，而binlog 是追加写的，不会覆盖以前写的数据。而binlog 也需要在事务提交前写入文件。binlog 的写入页需要通过 fsync 来保证落盘，为了提高 tps ，MySQL　可以通过参数　　sync_binlog 来控制是否需要同步刷盘，该策略会影响当主库宕机后备库数据可能并没有完全同步到主库数据。由于事务的原子性，需要保证事务提交的时候 redolog 和 binlog 都写入成功，所以 MySQL 执行层采用了两阶段提交来保证 redolog 和 binlog 都写入成功后才 commit，如果一方失败则会进行回滚。
下面我们理一下一条　update 语句的执行过程：

update person set age = 30 where id = 1;

• 1.分配事务 ID ，开启事务，获取锁，没有获取到锁则等待。
• 2.执行器先通过存储引擎找到 id = 1 的数据页，如果缓冲池有则直接取出，没有则去主键索引上取出对应的数据页放入缓冲池。
• 3.在数据页内找到 id = 1 这行记录，取出，将 age 改为 30 然后写入内存
• 4.生成 redolog undolog 到内存，redolog 状态为 prepare
• 5.将 redolog undolog 写入文件并调用 fsync
• 6.server 层生成 binlog 并写入文件调用 fsync
• 7.事务提交，将 redolog 的状态改为 commited 释放锁

  锁

数据库使用锁是为了对共享资源进行并发访问控制，从而保证数据的完整性和一致性。InnoDB 中锁的最小粒度为行，和 jdk 中的 ReadWriteLock 一样，InnoDB提供了共享锁和排他锁，分别用来读和写。共享锁之间可以兼容，其他都互斥。根据加锁的范围，可以分为：全局锁、表级锁、行锁。全局锁会把整个数据库实例加锁，命令为 flush tables withs read lock ，将使数据库处于只读状态，其他数据写入和修改表结构等语句会阻塞，一般在备库上做全局备份使用。而表级锁有两种，一种是表锁，命令为 lock table with read/write ，和读写锁一样，另外一种是元数据锁，也叫意向锁，不需要显示申明，当执行修改表结构，加索引的时候会自动加元数据写锁，对表进行增删改查的时候会加元数据读锁。这样当两条修改语句的事务之间元数据锁都是读锁不互斥，但是修改表结构的时候执行更新由于互斥就需要阻塞。还有一种行级锁称为间隙锁，他锁定的是两条记录之间的间隙，防止其他事务往这个间隙插入数据，间隙锁是隐式锁，是存储引擎自己加上的。 非锁定读 普通的 select 操作都是非锁定读，如果存在事务冲突，会利用 undolog 获取新事务操作之前的镜像返回，在读已提交的隔离级别下，会获取新事务修改前的最新的一份已经提交的数据，而在可重复读的隔离级别下，会读取该事务开始时的数据版本。当有多个事务并发操作同一行记录时，该记录会同时存在多个 undolog ，每个 undolog 就是一个版本，这种模式称为多版本并发控制（MVCC） ，该模式能够极大的提高数据库的性能，想一想，如果基于锁来控制的话，当对某个记录进行修改的时候，另一个事务将需要等待，不管他是要读取还是写入，MVCC 允许写入的时候还能够进行读操作，这对大部分都是查询操作的应用来说极大的提高了 tps 。 锁定读 有时候我们在查询的时候需要显示的给记录加锁来保证一致性，select for update 将对扫描到的记录加上排他锁，而 select in share lock 将对扫描的记录加上共享锁。这两个语句必须在一个事物内，也就是需要显示开启事物，begin transaction; 当事物提交的时候会释放锁。具体加锁的逻辑我们后面在分析。另外所有的锁定读都是当前读，也就是读取当前记录的最新版本，不会利用 undolog 读取镜像。另外所有的 insert、update、delete 操作也是当前读，update、delete 会在更新之前进行一次当前读，然后加锁，而 insert 因为会触发唯一索引检测，也会包含一个当前读。 自增长锁： 在主键设置为自增长的情况下，该表会维护一个计数器，每个插入操作都会先获取这个计数器的当前值，然后加 1 作为新的主键，显然这个计数器是一个共享变量需要加排他锁，而这个锁不需要等到事物提交后才释放，他在 sql 语句插入完成后就会释放，新版本的 innoDB 采用互斥量来实现提高了插入速度。 锁的问题

• 脏读
• 不可重复读
• 丢失更新
• 死锁和热点

脏读是指事务A对某个数据页进行了更改，但是并没有提交，这个数据就成为脏数据，这里稍微和上面提到的脏页做下区分，脏页是指内存中已经更改但是还没有刷新到磁盘的数据，脏页是正常的，而脏读是指一个事物读取了另外一个事物没有提交的数据，如果另外一个数据对这个数据又进行了更改，则出现数据一致性，脏读违背了数据库的隔离性。脏读目前只能出现在读未提交这个隔离级别下，目前 MySQL 默认的隔离级别为可重复读。
不可重复读是指一个事务先后两次读取同一条记录的结果不一样，因为第二次读取的时候可能其他事务已经进行更改并提交，不可重复读只发生在隔离级别为读未提交和读已提交里。
丢失更新是指两个事务同时更新某一条记录，导致其中一个事务更新失效，理论上任何一个隔离级别都不会发生丢失更新，因为更新的时候会加上排他锁，但是应用中却经常发生，例如一个计数器应用，事务A查询计数器的值 v=5,在内存中加 1 写入到数据库，在写入之前另外一个事务读取到计数器的值 v=5 ，然后加 1 写入数据库，这样本来应该为 7 ， 现在却是 6 ，这是因为我们是先读取在写入，而读取和写入对数据库而言是两个操作，并不是一个原子操作，这里可以通过把查询的记录加上排他锁 select for update 来防止丢失更新现象。当然这里直接将 sql 改为 v = v + 1 也可以。
死锁是指两个或两个以上事务因争夺资源而互相等待的情况，InnoDB 提供了死锁检测和超时机制来防止死锁的影响，死锁检测是非常耗 CPU 的，当很多个事务同时竞争同一个资源的时候，例如抢购的时候扣商品份额，或者支付的时候所有的订单都会用到一个公共账户，同一个资源竞争的事务越多，死锁检测越耗 CPU 。为了减少这种情况的影响，建议尽量在业务层减少热点的产生，例如将热点账户拆分成若个个同样功能的账户，万一发生高并发，建议在应用层做限流或者排队，当然也可以在数据库层做排队，这个需要修改数据库源码。

加锁的流程

InnoDB的加锁过程比较复杂，大致可以记住一个原则是：将所有扫描到的记录都加锁，范围查询会加间隙锁，然后加锁过程按照两阶段锁 2PL 来实现，也就是先加锁，然后所有的锁在事物提交的时候释放。怎么加锁和数据库的隔离级别有关，然而我们一般很少更改 MySQL 的隔离级别，所以下面我们均按照可重复读的隔离级别进行分析，另外一个因素是查询条件中是否包含索引，是主键索引还是普通索引，是否是唯一索引等。我们以下面这条 sql 语句来分析加锁过程。

select * from trade_order where order_no = '201912102322' for update;

order_no 是主键索引 ，这种情况将在主键索引上的 order_no = ‘201912102322’ 这条记录上加排他锁。
order_no 是普通索引，并且是唯一索引 将会对 普通索引上对应的一套记录加排他锁，对主键索引上对应的记录加排他锁
order_no 是普通索引，并且不是唯一索引 将会对 普通索引上 order_no = ‘201912102322’ 一条或者多条记录加锁，并且对这些记录对应的主键索引上的记录加锁。这里除了加上行锁外，还会加上间隙锁，防止其他事物插入　order_no = ‘201912102322’ 的记录，然而如果是唯一索引就不需要间隙锁，行锁就可以。

order_no 上没有索引，innoDB 将会在主键索引上全表扫描，这里并没有加表锁，而是将所有的记录都会加上行级排他锁，而实际上 innoDB 内部做了优化，当扫描到一行记录后发现不匹配就会把锁给释放，当然这个违背了 2PL 原则在事务提交的时候释放。这里除了对记录进行加锁，还会对每两个记录之间的间隙加锁，所以最终将会保存所有的间隙锁和 order_no = ‘201912102322’ 的行锁。

order_no = ‘201912102322’ 这条记录不存在的情况下，如果order_no 是主键索引，则会加一个间隙锁，而这个间隙是主键索引中 order_no 小于 201912102322 的第一条记录到大于 201912102322 的第一条记录。试想一下如果不加间隙锁，如果其他事物插入了一条 order_no = ‘201912102322’ 的记录，由于 select for update 是当前读，即使上面那个事物没有提交，如果在该事物中重新查询一次就会发生幻读。

如果没有索引，则对扫描到的所有记录和间隙都加锁，如果不匹配行锁将会释放只剩下间隙锁。回忆一下上面讲的数据页的结果中又一个最大记录和最小记录，Infimum 和 Supremum Record，这两个记录在加间隙锁的时候就会用到。

事务

ACID 特性

InnoDB 存储引擎的事务需完全符合 ACID 特性。下面我们一起看下 InnoDB 做了哪些事情。

• 原子性 : 是指一个事务内的所有操作要么全部成功要么全部失败，数据库中将 redolog 和 binlog 的写入采用两阶段提交就是为了保证事务的原子性。另外由于 InnodDB 是按页进行存储的，每个页大小为 16kb 而操作系统的一般以 4KB 为一页进行读取，所以可能出现一个 InnoDB 的数据页只写了一部分的情况。而 InnoDB 为了防止这种情况的发生采用双写机制，除了写入磁盘上的数据页还会在共享空间中写入。而 redolog 按块存储，每个块 512 字节，正好和扇区大小一样所以，可以保证原子性，不需要进行双写。
• 一致性 ：保证磁盘和缓存的数据一致，binlog 数据和 主库中的数据一致。
• 隔离性 ：默认为可重复读，采用 undolog 来实现。

• 持久性 ：事务一旦提交，其结果就是永久的，redolog 需要在事务提交前进行刷盘，磁盘采用 RAID 等。

  同时有多个事务在进行会怎么样呢?

  多事务的并发进行一般会造成以下几个问题:

• 脏读: A事务读取到了B事务未提交的内容,而B事务后面进行了回滚.

• 不可重复读: 当设置A事务只能读取B事务已经提交的部分,会造成在A事务内的两次查询,结果竟然不一样,因为在此期间B事务进行了提交操作.

• 幻读: A事务读取了一个范围的内容,而同时B事务在此期间插入了一条数据.造成”幻觉”.

  MySQL的事务隔离级别

  MySQL的四种隔离级别如下:

• 未提交读(READ UNCOMMITTED)

这就是上面所说的例外情况了,这个隔离级别下,其他事务可以看到本事务没有提交的部分修改.因此会造成脏读的问题(读取到了其他事务未提交的部分,而之后该事务进行了回滚).
这个级别的性能没有足够大的优势,但是又有很多的问题,因此很少使用.

• 已提交读(READ COMMITTED)

其他事务只能读取到本事务已经提交的部分.这个隔离级别有 不可重复读的问题,在同一个事务内的两次读取,拿到的结果竟然不一样,因为另外一个事务对数据进行了修改.

• REPEATABLE READ(可重复读)

可重复读隔离级别解决了上面不可重复读的问题(看名字也知道),但是仍然有一个新问题,就是 幻读,当你读取id> 10 的数据行时,对涉及到的所有行加上了读锁,此时例外一个事务新插入了一条id=11的数据,因为是新插入的,所以不会触发上面的锁的排斥,那么进行本事务进行下一次的查询时会发现有一条id=11的数据,而上次的查询操作并没有获取到,再进行插入就会有主键冲突的问题.

• SERIALIZABLE(可串行化)

这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此也不是很常用.

Innodb使用的是哪种隔离级别呢?

InnoDB默认使用的是可重复读隔离级别.

MySQL-InnoDB-MVCC多版本并发控制

入题

下面先引用一些前辈们比较优秀的文章:

阿里数据库内核’2017/12’月报中对MVCC的解释是:> 多版本控制: 指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。在内部实现中，与Postgres在数据行上实现多版本不同，InnoDB是在undolog中实现的，通过undolog可以找回数据的历史版本。找回的数据历史版本可以提供给用户读(按照隔离级别的定义，有些读请求只能看到比较老的数据版本)，也可以在回滚的时候覆盖数据页上的数据。在InnoDB内部中，会记录一个全局的活跃读写事务数组，其主要用来判断事务的可见性。> <高性能MySQL>中对MVCC的部分介绍

• MySQL的大多数事务型存储引擎实现的其实都不是简单的行级锁。基于提升并发性能的考虑, 它们一般都同时实现了多版本并发控制(MVCC)。不仅是MySQL, 包括Oracle,PostgreSQL等其他数据库系统也都实现了MVCC, 但各自的实现机制不尽相同, 因为MVCC没有一个统一的实现标准。
• 可以认为MVCC是行级锁的一个变种, 但是它在很多情况下避免了加锁操作, 因此开销更低。虽然实现机制有所不同, 但大都实现了非阻塞的读操作，写操作也只锁定必要的行。
• MVCC的实现方式有多种, 典型的有乐观(optimistic)并发控制 和 悲观(pessimistic)并发控制。
• MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别够和MVCC不兼容, 因为 READ UNCOMMITTED 总是读取最新的数据行, 而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

从书中可以了解到:

• MVCC是被Mysql中 事务型存储引擎InnoDB 所支持的;
• 应对高并发事务, MVCC比单纯的加锁更高效;
• MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;
• MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;
• 各数据库中MVCC实现并不统一
• 但是书中提到 “InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的”(网上也有很多此类观点), 但其实并不准确, 可以参考MySQL官方文档, 可以看到, InnoDB存储引擎在数据库每行数据的后面添加了三个字段, 不是两个!!

相关概念

1.read view, 快照snapshot

淘宝数据库内核月报/2017/10/01/此文虽然是以PostgreSQL进行的说明, 但并不影响理解, 在”事务快照的实现”该部分有细节需要注意:事务快照是用来存储数据库的事务运行情况。一个事务快照的创建过程可以概括为：查看当前所有的未提交并活跃的事务，存储在数组中选取未提交并活跃的事务中最小的XID，记录在快照的xmin中> 选取所有已提交事务中最大的XID，加1后记录在xmax中 注意: 上文中在PostgreSQL中snapshot的概念, 对应MySQL中, 其实就是你在网上看到的read view,快照这些概念;
比如何登成就有关于Read view的介绍;
而 此文 却仍是使用快照来介绍;
2.read view 主要是用来做可见性判断的, 比较普遍的解释便是”本事务不可见的当前其他活跃事务”, 但正是该解释, 可能会造成一节理解上的误区, 所以此处提供两个参考, 供给大家避开理解误区:

read view中的`高水位low_limit_id`可以参考 https://github.com/zhangyachen/zhangyachen.github.io/issues/68, https://www.zhihu.com/question/66320138
其实上面第1点中加粗部分也是相关高水位的介绍( 注意进行了+1 )

3.另外, 对于read view快照的生成时机, 也非常关键, 正是因为生成时机的不同, 造成了RC,RR两种隔离级别的不同可见性;

• 在innodb中(默认repeatable read级别), 事务在begin/start transaction之后的第一条select读操作后, 会创建一个快照(read view), 将当前系统中活跃的其他事务记录记录起来;
• 在innodb中(默认repeatable committed级别), 事务中每条select语句都会创建一个快照(read view);

• 参考

  With REPEATABLE READ isolation level, the snapshot is based on the time when the first read operation is performed.
  使用REPEATABLE READ隔离级别，快照是基于执行第一个读操作的时间。
  With READ COMMITTED isolation level, the snapshot is reset to the time of each consistent read operation.
  使用READ COMMITTED隔离级别，快照被重置为每个一致的读取操作的时间。

  4.undo-log

• Undo log是InnoDB MVCC事务特性的重要组成部分。当我们对记录做了变更操作时就会产生undo记录，Undo记录默认被记录到系统表空间(ibdata)中，但从5.6开始，也可以使用独立的Undo 表空间。

• Undo记录中存储的是老版本数据，当一个旧的事务需要读取数据时，为了能读取到老版本的数据，需要顺着undo链找到满足其可见性的记录。当版本链很长时，通常可以认为这是个比较耗时的操作（例如bug#69812）。
• 大多数对数据的变更操作包括INSERT/DELETE/UPDATE，其中INSERT操作在事务提交前只对当前事务可见，因此产生的Undo日志可以在事务提交后直接删除（谁会对刚插入的数据有可见性需求呢！！），而对于UPDATE/DELETE则需要维护多版本信息，在InnoDB里，UPDATE和DELETE操作产生的Undo日志被归成一类，即update_undo
• 另外, 在回滚段中的undo logs分为:insert undo log和update undo log
  • insert undo log : 事务对insert新记录时产生的undolog, 只在事务回滚时需要, 并且在事务提交后就可以立即丢弃。
  • update undo log : 事务对记录进行delete和update操作时产生的undo log, 不仅在事务回滚时需要, 一致性读也需要，所以不能随便删除，只有当数据库所使用的快照中不涉及该日志记录，对应的回滚日志才会被purge线程删除。

5.InnoDB存储引擎在数据库每行数据的后面添加了三个字段

• 6字节的事务ID(DB_TRX_ID)字段: 用来标识最近一次对本行记录做修改(insert|update)的事务的标识符, 即最后一次修改(insert|update)本行记录的事务id。
  至于delete操作，在innodb看来也不过是一次update操作，更新行中的一个特殊位将行表示为deleted, 并非真正删除。
• 7字节的回滚指针(DB_ROLL_PTR)字段: 指写入回滚段(rollback segment)的 undo log record (撤销日志记录记录)。
  如果一行记录被更新, 则 undo log record 包含 ‘重建该行记录被更新之前内容’ 所必须的信息。
• 6字节的DB_ROW_ID字段: 包含一个随着新行插入而单调递增的行ID, 当由innodb自动产生聚集索引时，聚集索引会包括这个行ID的值，否则这个行ID不会出现在任何索引中。
  结合聚簇索引的相关知识点, 我的理解是, 如果我们的表中没有主键或合适的唯一索引, 也就是无法生成聚簇索引的时候, InnoDB会帮我们自动生成聚集索引, 但聚簇索引会使用DB_ROW_ID的值来作为主键; 如果我们有自己的主键或者合适的唯一索引, 那么聚簇索引中也就不会包含 DB_ROW_ID 了 。
  关于聚簇索引, 《高性能MySQL》中的篇幅对我来说已经够用了, 稍后会整理一下以前的学习笔记, 然后更新上来。

6.可见性比较算法（这里每个比较算法后面的描述是建立在rr级别下，rc级别也是使用该比较算法,此处未做描述）
设要读取的行的最后提交事务id(即当前数据行的稳定事务id)为 trx_id_current
当前新开事务id为 new_id
当前新开事务创建的快照read view 中最早的事务id为up_limit_id, 最迟的事务id为low_limit_id(注意这个low_limit_id=未开启的事务id=当前最大事务id+1)
比较:

• 1.trx_id_current < up_limit_id, 这种情况比较好理解, 表示, 新事务在读取该行记录时, 该行记录的稳定事务ID是小于, 系统当前所有活跃的事务, 所以当前行稳定数据对新事务可见, 跳到步骤5.
• 2.trx_id_current >= trx_id_last, 这种情况也比较好理解, 表示, 该行记录的稳定事务id是在本次新事务创建之后才开启的, 但是却在本次新事务执行第二个select前就commit了，所以该行记录的当前值不可见, 跳到步骤4。
• 3.trx_id_current <= trx_id_current <= trx_id_last, 表示: 该行记录所在事务在本次新事务创建的时候处于活动状态，从up_limit_id到low_limit_id进行遍历，如果trx_id_current等于他们之中的某个事务id的话，那么不可见, 调到步骤4,否则表示可见。
• 4.从该行记录的 DB_ROLL_PTR 指针所指向的回滚段中取出最新的undo-log的版本号, 将它赋值该 trx_id_current，然后跳到步骤1重新开始判断。
• 5.将该可见行的值返回。

  案例分析

1. 下面是一个非常简版的演示事务对某行记录的更新过程, 当然, InnoDB引擎在内部要做的工作非常多:
   
2. 下面是一套比较算法的应用过程, 比较长
   

   当前读和快照读

   1.MySQL的InnoDB存储引擎默认事务隔离级别是RR(可重复读), 是通过 “行排他锁+MVCC” 一起实现的, 不仅可以保证可重复读, 还可以部分防止幻读, 而非完全防止;
   2.为什么是部分防止幻读, 而不是完全防止?

• 效果: 在如果事务B在事务A执行中, insert了一条数据并提交, 事务A再次查询, 虽然读取的是undo中的旧版本数据(防止了部分幻读), 但是事务A中执行update或者delete都是可以成功的!!
• 因为在innodb中的操作可以分为当前读(current read)和快照读(snapshot read):

3.快照读(snapshot read)

简单的select操作(当然不包括 select ... lock in share mode, select ... for update)

4.当前读(current read) 官网文档 Locking Reads

• select … lock in share mode
• select … for update
• insert
• update
• delete

在RR级别下，快照读是通过MVVC(多版本控制)和undo log来实现的，当前读是通过加record lock(记录锁)和gap lock(间隙锁)来实现的。
innodb在快照读的情况下并没有真正的避免幻读, 但是在当前读的情况下避免了不可重复读和幻读!!!

小结

1. 一般我们认为MVCC有下面几个特点：
   • 每行数据都存在一个版本，每次数据更新时都更新该版本
   • 修改时Copy出当前版本, 然后随意修改，各个事务之间无干扰
   • 保存时比较版本号，如果成功(commit)，则覆盖原记录, 失败则放弃copy(rollback)
   • 就是每行都有版本号，保存时根据版本号决定是否成功，听起来含有乐观锁的味道, 因为这看起来正是，在提交的时候才能知道到底能否提交成功
2. 而InnoDB实现MVCC的方式是:
   • 事务以排他锁的形式修改原始数据
   • 把修改前的数据存放于undo log，通过回滚指针与主数据关联
   • 修改成功（commit）啥都不做，失败则恢复undo log中的数据（rollback）
3. 二者最本质的区别是: 当修改数据时是否要排他锁定，如果锁定了还算不算是MVCC？

• Innodb的实现真算不上MVCC, 因为并没有实现核心的多版本共存, undo log 中的内容只是串行化的结果, 记录了多个事务的过程, 不属于多版本共存。但理想的MVCC是难以实现的, 当事务仅修改一行记录使用理想的MVCC模式是没有问题的, 可以通过比较版本号进行回滚, 但当事务影响到多行数据时, 理想的MVCC就无能为力了。
• 比如, 如果事务A执行理想的MVCC, 修改Row1成功, 而修改Row2失败, 此时需要回滚Row1, 但因为Row1没有被锁定, 其数据可能又被事务B所修改, 如果此时回滚Row1的内容，则会破坏事务B的修改结果，导致事务B违反ACID。 这也正是所谓的 第一类更新丢失 的情况。
• 也正是因为InnoDB使用的MVCC中结合了排他锁, 不是纯的MVCC, 所以第一类更新丢失是不会出现了, 一般说更新丢失都是指第二类丢失更新。

MySQL Join的底层实现原理

mysql只支持一种join算法：Nested-Loop Join（嵌套循环连接），但Nested-Loop Join有三种变种：Simple Nested-Loop Join，Index Nested-Loop Join，Block Nested-Loop Join
（注：参考公众号：InsideMySQL）

原理：

1.Simple Nested-Loop Join：

如下图，r为驱动表，s为匹配表，可以看到从r中分别取出r1、r2、……、rn去匹配s表的左右列，然后再合并数据，对s表进行了rn次访问，对数据库开销大

微信截图_20181122171451.png

2.Index Nested-Loop Join（索引嵌套）：

这个要求非驱动表（匹配表s）上有索引，可以通过索引来减少比较，加速查询。
在查询时，驱动表（r）会根据关联字段的索引进行查找，挡在索引上找到符合的值，再回表进行查询，也就是只有当匹配到索引以后才会进行回表查询。
如果非驱动表（s）的关联健是主键的话，性能会非常高，如果不是主键，要进行多次回表查询，先关联索引，然后根据二级索引的主键ID进行回表操作，性能上比索引是主键要慢。

微信截图_20181122171515.png

3.Block Nested-Loop Join：

如果有索引，会选取第二种方式进行join，但如果join列没有索引，就会采用Block Nested-Loop Join。可以看到中间有个join buffer缓冲区，是将驱动表的所有join相关的列都先缓存到join buffer中，然后批量与匹配表进行匹配，将第一种多次比较合并为一次，降低了非驱动表（s）的访问频率。默认情况下join_buffer_size=256K，在查找的时候MySQL会将所有的需要的列缓存到join buffer当中，包括select的列，而不是仅仅只缓存关联列。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。

微信截图_20181122171528.png

实例：

假设两张表a 和 b：

a结构：
comments_id        bigInt(20)    P
for_comments_if    mediumint(9)
product_id         int(11)
order_id           int(11)
...

b结构：
id            int(11)       p
comments_id   bigInt(20)
product_id    int(11)
...

其中b的关联有comments_id，所以有索引。

join:

SELECT * FROM a gc
JOIN b gcf ON gc.comments_id=gcf.comments_id
WHERE gc.comments_id =2056

使用的是Index Nested-Loop Join，先对驱动表a的主键筛选，得到一条，然后对非驱动表b的索引进行seek匹配，预计得到一条数据。
下面这种情况没用到索引:

SELECT * FROM a gc
JOIN b gcf ON gc.order_id=gcf.product_id

使用Block Nested-Loop Join，如果b表数据少，作为驱动表，将b的需要的数据缓存到join buffer中，批量对a表扫描

left join：

SELECT * FROM a gc
LEFT JOIN b gcf ON gc.comments_id=gcf.comments_id

这里用到了索引，所以会采用Index Nested-Loop Join，因为没有筛选条件，会选择一张表作为驱动表去进行join，去关联非驱动表的索引。
如果加了条件:

SELECT * FROM b gcf
LEFT JOIN a gc ON gc.comments_id=gcf.comments_id
WHERE gcf.comments_id =2056

就会从驱动表筛选出一条来进行对非驱动表的匹配。

left join：会保全左表数据，如果右表没相关数据，会显示null
fight join：会保全右表数据，如果左表没相关数据，会显示null
inner join：部分主从表，结果会取两个表针对on条件相匹配的最小集

作者：He_Yifeng
链接：https://www.jianshu.com/p/16ad9669d8a9
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
原文地址：https://www.yuque.com/lobotomy/java/aqf7l9