基础篇

执行一条 SQL 查询语句，期间发生了什么？

查询语句执行流程

可以看到， MySQL 的架构共分为两层：Server 层和存储引擎层

Server 层负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现。
存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

连接器
1. 与客户端进行 TCP 三次握手建立连接；
2. 校验客户端的用户名和密码，如果用户名或密码不对，则会报错；
3. 如果用户名和密码都对了，会读取该用户的权限，然后后面的权限逻辑判断都基于此时读取到的权限；
查询缓存
连接器得工作完成后，客户端就可以向 MySQL 服务发送 SQL 语句了，MySQL 服务收到 SQL 语句后，就会解析出 SQL 语句的第一个字段，看看是什么类型的语句。
如果 SQL 是查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。
如果查询的语句命中查询缓存，那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中。
对于更新比较频繁的表，查询缓存的命中率很低的，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。
所以，MySQL 8.0 版本直接将查询缓存删掉了，也就是说 MySQL 8.0 开始，执行一条 SQL 查询语句，不会再走到查询缓存这个阶段了。
对于 MySQL 8.0 之前的版本，如果想关闭查询缓存，我们可以通过将参数 query_cache_type 设置成 DEMAND。
这里说的查询缓存是 server 层的，也就是 MySQL 8.0 版本移除的是 server 层的查询缓存，并不是 Innodb 存储引擎中的 buffer poll。
解析SQL
1. 词法分析，识别关键字，构建SQL语法树
2. 语法分析，根据词法分析的结果与语法规则，检查SQL语句语法
这一步不检查表或者字段是否存在。
执行SQL
1. 预处理阶段（处理器）
  1. 检查SQL查询语句中的表或者字段是否存在
  2. 将 select * 中的 * 符号，扩展为表上的所有列；
2. 优化阶段（优化器）
  1. 优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。
  2. 要想知道优化器选择了哪个索引，可以在查询语句最前面加个 explain 命令，这样就会输出这条 SQL 语句的执行计划，然后执行计划中的 key 就表示执行过程中使用了哪个索引。如果查询语句的执行计划里的 key 为 null 说明没有使用索引，那就会全表扫描（type = ALL），这种查询扫描的方式是效率最低档次的。
3. 执行阶段（执行器）
  1. 主键索引查询
  2. 全表扫描
  3. 索引下推：索引下推能够减少二级索引在查询时的回表操作，提高查询的效率，因为它将 Server 层部分负责的事情，交给存储引擎层去处理了。

如何查看 MySQL 服务被多少个客户端连接了？

执行show processlist 命令查看。

空闲连接会一直占用着吗？

MySQL 定义了空闲连接的最大空闲时长，由 wait_timeout 参数控制的，默认值是 8 小时（28880秒），如果空闲连接超过了这个时间，连接器就会自动将它断开。

也可以手动断开空闲的连接，使用的是 kill connection + id 的命令。

一个处于空闲状态的连接被服务端主动断开后，这个客户端并不会马上知道，等到客户端在发起下一个请求的时候，才会收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server during query”。

MySQL 的连接数有限制吗？

MySQL 服务支持的最大连接数由 max_connections 参数控制，超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。

MySQL 的连接也跟 HTTP 一样，有短连接和长连接的概念，它们的区别如下：

// 短连接
连接 mysql 服务（TCP 三次握手）
执行sql
断开 mysql 服务（TCP 四次挥手）

// 长连接
连接 mysql 服务（TCP 三次握手）
执行sql
执行sql
执行sql
....
断开 mysql 服务（TCP 四次挥手）

使用长连接的好处就是可以减少建立连接和断开连接的过程，所以一般是推荐使用长连接。

但是，使用长连接后可能会占用内存增多，因为 MySQL 在执行查询过程中临时使用内存管理连接对象，这些连接对象资源只有在连接断开时才会释放。如果长连接累计很多，将导致 MySQL 服务占用内存太大，有可能会被系统强制杀掉，这样会发生 MySQL 服务异常重启的现象。

怎么解决长连接占用内存的问题？

有两种解决方式。

定期断开长连接。既然断开连接后就会释放连接占用的内存资源，那么我们可以定期断开长连接。
客户端主动重置连接。MySQL 5.7 版本实现了 mysql_reset_connection() 函数的接口，注意这是接口函数不是命令，那么当客户端执行了一个很大的操作后，在代码里调用 mysql_reset_connection 函数来重置连接，达到释放内存的效果。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

MyISAM和 InnoDB 的区别

原文链接：MyISAM和 InnoDB的选择

外键
InnoDB支持外键，而MyISAM是不支持的。如果将InnoDB类型的表转换为MyISAM类型的话，是会转换失败的。
索引
InnoDB是聚集索引，使用B+树作为索引结构。必须要有主键，通过主键索引效率会很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此主键不应该过大，因为主键过大的话，索引也要增大。
MyISAM是非聚集索引，它的索引与数据文件是分开的。InnoDB的索引与数据文件是绑在一块进行存储的。
存储文件
InnoDB是聚集索引。而MyISAM是非聚集索引，它的索引与数据文件是分开存储的，MyISAM在硬盘上的文件：
1. .frm文件是存储的表的框架结构，它的名字与表的名字是一样的。无论是MyISAM还是InnoDB，都有这个文件
2. MYD文件存放的是表的数据
3. MYI文件存放的是表的索引
MyISAM是非聚集索引，就是把索引与表中的数据分开存储了。聚集索引将数据与索引放在一个文件中。InnoDB的存储文件有：
1. .frm，与MyISAM一样，都是存放的表的框架结构。
2. ibd
事务
InnoDB支持事务，而且5.5版本之后，MySQL默认的存储引擎就是InnoDB了。而MyISAM不支持事务
全文索引
在MySQL5.7版本之前，InnoDB是不支持全文索引的，在5.7版本之后，InnoDB就支持索引了。MyISAM则是支持全文索引
是否存储表中行数
InnoDB不保存表的具体行数，比如在执行select count(*) from table时，会进行全表扫描。而MyISAM中内置了一个计数器，这个计数器存放了表中的行数，在查询表中的行数时，直接从计数器中获取即可。
锁的支持
InnoDB支持行锁、表锁，默认是行锁。MyISAM支持表锁。表锁指的是锁住的是整张表。在InnoDB中，它的行锁锁的是索引，而不是对整行数据进行加锁，如果在访问数据的时候，如果访问没有命中索引，也无法使用行锁，那么InnoDB就会退化到表锁。
是否有唯一索引
InnoDB必须要有唯一索引（如主键id），而MyISAM可以没有。InnoDB推荐自增id作为主键，因为索引的底层数据结构为B+树，使用自增的id的话，能够避免B+树的频繁合并与重排。
数据的删除
InnoDB删除数据时是一行一行进行删除的，而MyISAM删除数据时则是重新建表。

如何选择哪种存储引擎？

1、如果要支持事务，那么肯定是要选择InnoDB的，毕竟MyISAM是不支持事务的。而过不要求事务的话，可以考虑MyISAM。
2、如果在使用的时候，读操作占用了大多数，那么MyISAM是比较合适的，如果读写的比例差不多，那么InnoDB更合适
3、系统崩溃后，MyISAM恢复的速度是要比InnoDB慢的，
4、5.5版本后，默认的存储引擎就是InnoDB了，所以，大部分情况下，InnoDB是够用的。

向表中新增20条数据，删除3条数据后，重启数据库，再新增一条数据，那么这条数据的id是多少呢？

从InnoDB与MyISAM两种存储引擎方面来说。如果是InnoDB的话，因为InnoDB是将数据存放到内存中，重启数据库会导致最大ID丢失，再新增数据的时候，会从当前最大值还是累加，所以InnoDB此时的id为18.
MyISAM将数据信息存放到文件中，再重启数据库的时候，重新读取文件获取文件中数据的最大值，在此基础上进行操作即可，所以MyISAM引擎的话是21。

索引篇

什么是索引

索引的定义就是帮助存储引擎快速获取数据的一种数据结构，形象的说就是索引是数据的目录。

所谓的存储引擎，说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。MySQL 存储引擎有 MyISAM 、InnoDB、Memory，其中 InnoDB 是在 MySQL 5.5 之后成为默认的存储引擎。

索引的分类

按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
按「字段个数」分类：单列索引、联合索引。

一、按「数据结构」分类

MySQL 常见的存储引擎 InnoDB、MyISAM 和 Memory 分别支持的索引类型：

InnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎，B+Tree 索引类型也是 MySQL 存储引擎采用最多的索引类型。

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。

主键索引的 B+Tree 如图所示：

主键索引 B+Tree

数据库的索引和数据都是存储在硬盘的，我们可以把读取一个节点当作一次磁盘 I/O 操作。那么上面的整个查询过程一共经历了 3 个节点，也就是进行了 3 次 I/O 操作。

B+Tree 存储千万级的数据只需要 3-4 层高度就可以满足，这意味着从千万级的表查询目标数据最多需要 3-4 次磁盘 I/O，所以B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。

主键索引的 B+Tree 和二级索引的 B+Tree 区别如下：

主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

通过二级索引查询的过程如下：

先检二级索引中的 B+Tree 的索引值，找到对应的叶子节点，然后获取主键值
再通过主键索引中的 B+Tree 树查询到对应的叶子节点，然后获取整行数据。这个过程叫「回表」，也就是说要查两个 B+Tree 才能查到数据。

二、按「物理存储」分类

从物理存储的角度来看，索引分为聚簇索引（主键索引）、二级索引（辅助索引）。

这两个区别在前面也提到了：

主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

所以，在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。

因为表的数据都是存放在聚集索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚集索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个，而二级索引可以创建多个。

三、按「字段特性」分类

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

主键索引 PRIMARY KEY
主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。
唯一索引 UNIQUE KEY
唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。
普通索引 INDEX
普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。
前缀索引 INDEX(column_name(length))

四、按「字段个数」分类

从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。

建立在单列上的索引称为单列索引，比如主键索引；
建立在多列上的索引称为联合索引；

联合索引的非叶子节点用多个字段的值作为 B+Tree 的 key 值。当在联合索引查询数据时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；

需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。

联合索引失效的情况：

在没有遵循最左匹配原则的情况下，无法利用到联合索引。
联合索引的最左匹配原则，在遇到范围查询（>、<、between、like 包括like ‘x%’这种）的时候，就会停止匹配，也就是范围列可以用到联合索引，但是范围列后面的列无法用到联合索引。

在情况2中，联合索引的 B+Tree 找到第一个满足条件的主键值后，还需要判断其他条件是否满足：

在 MySQL 5.6 之前，只能从 ID2 （主键值）开始一个个回表，到「主键索引」上找出数据行，再对比 b 字段值。
而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

当查询语句的执行计划里，出现了 Extra 为 Using index condition，那么说明使用了索引下推的优化。

建立联合索引时的字段顺序，对索引效率也有很大影响。越靠前的字段被用于索引过滤的概率越高，实际开发工作中建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到。区分度计算公式：
$$
区分度 = \frac{distinct(column)}{count(*)}
$$
MySQL查询优化器发现某个值出现在表的数据行中的百分比（惯用的百分比界线是”30%”）很高的时候，它一般会忽略索引，进行全表扫描。

什么时候不需要创建索引

索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：

需要占用物理空间，数量越大，占用空间越大；
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。

什么时候适用索引？

字段有唯一性限制的，比如商品编码；
经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

什么时候不需要创建索引？

WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
表数据太少的时候，不需要创建索引；
经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

优化索引的方法

常见的四种方法：前缀索引优化、覆盖索引优化、主键索引设置为自增、索引设置为NOT NULL、防止索引失效。

前缀索引优化
前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引。
使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。
前缀索引有一定的局限性，例如：
- order by 无法使用前缀索引；
- 无法把前缀索引用作覆盖索引；
覆盖索引优化
覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。
假设我们只需要查询商品的名称、价格，可以建立一个联合索引，即 (商品ID, 名称, 价格) 作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。
所以，使用覆盖索引的好处就是，不需要查询出包含整行记录的所有信息，也就减少了大量的 I/O 操作。
主键索引最好是自增的
InnoDB 创建主键索引默认为聚簇索引，数据被存放在了 B+Tree 的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一条新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中。
1. 如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。
2. 如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。
另外，主键字段的长度不要太大，因为主键字段长度越小，意味着二级索引的叶子节点越小（二级索引的叶子节点存放的数据是主键值），这样二级索引占用的空间也就越小。
索引最好设置为 NOT NULL
为了更好的利用索引，索引列要设置为 NOT NULL 约束。有两个原因：
- 索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。
- 第二个原因：NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，会导致更多的存储空间占用，因为 InnoDB 默认行存储格式COMPACT，会用 1 字节空间存储 NULL 值列表。
防止索引失效
发生索引失效的情况：
1. 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
2. 当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
3. 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
4. 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

MySQL查询优化中扫描方式（Access Paths）

const（system）
常量访问，当通过表的**”主键”或”非空唯一索引”进行“常量等值匹配”访问表中数据时，访问类型就是const。此访问类型只会执行一次，查询最多只会查询出1条数据，因此在复杂的SQL中会被优先执行并直接替换为常量**，然后再执行SQL的其他部分，以提升效率。
eq_ref
等值引用，当表之间通过等值连接(连接条件是=)访问时，且使用**”主键”或“非空唯一索引”**访问被驱动表时，访问类型即是eq_ref，这是效率最高的连接访问方式。
ref
普通引用，当表之间同过等值(连接条件是=或<=>)访问时，使用普通索引（未使用”主键”或”非空唯一索引”）访问被驱动表时，访问类型即是ref，这是效率比较高的连接访问方式。
ref_or_null
这种访问方式和ref类似，只是MySQL在判断连接值时会额外做一步判断连接条件上的值是否包含null。这种连接类型大部分是在处理子查询的场景中使用。
fulltext
全文索引访问，全文索引是一种特殊的索引类型，只能建立在char，varchar或text类型的数据上，用来在大量的文本中匹配某种模式（大文本比like匹配快很多）。全文索引使用下面的语法进行查询，共3种检索方式，这里不详细叙述。

1	`MATCH (col1,col2,...) AGAINST (expr [search_modifier])`

index_merge
索引合并，MySQL通过对同一个表中的多个索引进行范围扫描（range），再将各个扫描结果集合计，得到最终结果集。注意参与索引合并的索引必须来自同一个表，不能跨表。此访问方法在同时使用多个索引时常用。
index_merge的算法有3种算法：
- index_merge intersection算法取多个索引范围扫描交集，在多个索引条件使用 and 连接时使用。
- index_merge union算法是取多个范围扫描并集，在多个索引条件使用 or 连接时使用。
- index_merge sort-union算法和union类似，唯一的区别是sort-union算法要先取回所有的结果后排序，再将结果返回给客户端。
unique_subquery
唯一子查询，在某些子查询场景替换eq_ref访问方法。

1	`value IN (SELECT primary_key FROM single_table WHERE some_expr)`

index_subquery
和unique_subquery类似，某些场景替换in子查询，只是在查询中使用的是非唯一索引。

1	`value IN (SELECT key_column FROM single_table WHERE some_expr)`

range
索引范围扫描，根据搜索条件扫描一个范围区间的索引，此访问方法非常常见，在where条件中使用=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() 这些操作符均会使用索引范围扫描。
这一级往上，索引的作用越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。
index
索引全扫描，和全表扫描（ALL）类似，只是这个扫描的是整个索引树。一般在两种场景下会使用此访问方法：
1. 索引覆盖，即索引中包含了 select 域中需要的所有信息，此时只需要扫描索引就可以满足查询，不再需要回表操作。
2. 全表扫描时，需要按某个索引排序。
all
人人都熟悉的全表扫描，大部分情况下是效率最低的访问方法（当表中数据较少时，全表扫扫描性能也可能优于索引，因为索引大部分是二次访问，随机读取，全表扫描是顺序读取）。特别当表中数据量较大时，使用全表扫描往往会消耗大量系统资源，严重时导致系统挂起。

explain 查看执行计划中的字段

possible_keys 字段表示可能用到的索引；
key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
key_len 表示索引的长度；
rows 表示扫描的数据行数；
type 表示数据扫描类型。

type 字段就是描述了找到所需数据时使用的扫描方式是什么。除了type，也要关注 extra 几个重要的参考指标：

Using filesort ：当查询语句中包含 group by 操作，而且无法利用索引完成排序操作的时候，这时不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。
Using temporary：使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by。效率低，要避免这种问题的出现。
Using index：所需数据只需在索引即可全部获得，不须要再到表中取数据，也就是使用了覆盖索引，避免了回表操作，效率不错。

InnoDB 是如何存储数据的？

记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。

因此，InnoDB 的数据是按「数据页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。

数据库的 I/O 操作的最小单位是页，InnoDB 数据页的默认大小是 16KB，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。

数据页包括七个部分，结构如下图：

数据页

这7个部分的作用如下：

文件头（File Header）：文件头，表示页的信息；
页头（Page Header）：页头，表示页；
最小和最大记录（Infimum+Supremum）：两个虚拟的伪记录，分别表示页中的最小记录和最大记录；
用户记录（User Records）：存储行记录的内容；
空闲空间（Free Space）：页中还没有被使用的空间；
页目录（Page Directory）：存储用户记录的相对位置，对记录起到索引作用；
文件尾（File Tailer）：校验页是否完整。

文件头中有两个指针，分别指向上一个数据页和下一个数据页，连接起来的页相当于一个双向链表：

双向链表

采用链表的结构是让数据页之间不需要是物理上的连续的，而是逻辑上的连续。

数据页的主要作用是存储记录，也就是数据库的数据，数据页中的记录按照「主键」顺序组成单向链表，单向链表的特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索。

因此，数据页中有一个页目录，起到记录的索引作用，页目录与记录的关系如下图：

页目录创建的过程如下：

将所有的记录划分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录；
每个记录组的最后一条记录就是组内最大的那条记录，并且最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段
页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录。

从图可以看到，页目录就是由多个槽组成的，槽相当于分组记录的索引。然后，因为记录是按照「主键值」从小到大排序的，所以我们通过槽查找记录时，可以使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到对应的记录，无需从最小记录开始遍历整个页中的记录链表。

因为记录都是单向链表串起来的，在槽内查找某个记录的时间复杂度是 O(n) ，所以InnoDB 对每个分组中的记录条数都是有规定的，槽内的记录就只有几条：

第一个分组中的记录只能有 1 条记录；
最后一个分组中的记录条数范围只能在 1-8 条之间；
剩下的分组中记录条数范围只能在 4-8 条之间。

B+ 树是如何进行查询的？

InnoDB 里的 B+ 树中的每个节点都是一个数据页，结构示意图如下：

B+树

通过上图，我们看出 B+ 树的特点：

只有叶子节点（最底层的节点）才存放了数据，非叶子节点（其他上层节）仅用来存放目录项作为索引。
非叶子节点分为不同层次，通过分层来降低每一层的搜索量；
所有节点按照索引键大小排序，构成一个双向链表，便于范围查询；

我们再看看 B+ 树如何实现快速查找主键为 6 的记录，以上图为例子：

从根节点开始，通过二分法快速定位到符合页内范围包含查询值的页，因为查询的主键值为 6，在[1, 7)范围之间，所以到页 30 中查找更详细的目录项；
在非叶子节点（页30）中，继续通过二分法快速定位到符合页内范围包含查询值的页，主键值大于 5，所以就到叶子节点（页16）查找记录；
接着，在叶子节点（页16）中，通过槽查找记录时，使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到主键为 6 的记录。

可以看到，在定位记录所在哪一个页时，也是通过二分法快速定位到包含该记录的页。定位到该页后，又会在该页内进行二分法快速定位记录所在的分组（槽号），最后在分组内进行遍历查找。

聚簇索引和二级索引的区别

索引可以分成聚簇索引和非聚簇索引（二级索引），它们区别就在于叶子节点存放的是什么数据：

聚簇索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚簇索引的叶子节点；
二级索引的叶子节点存放的是主键值，而不是实际数据。

因为表的数据都是存放在聚簇索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚簇索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个。

InnoDB 是如何创建聚簇索引的

InnoDB 在创建聚簇索引时，会根据不同的场景选择不同的列作为索引：

如果有主键，默认会使用主键作为聚簇索引的索引键；
如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键；
在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键；

一张表只能有一个聚簇索引，那为了实现非主键字段的快速搜索，就引出了二级索引（非聚簇索引/辅助索引），它也是利用了 B+ 树的数据结构，但是二级索引的叶子节点存放的是主键值，不是实际数据。

为什么 MySQL 采用 B+ 树作为索引？

MySQL 默认的存储引擎 InnoDB 采用的是 B+ 作为索引的数据结构，原因有：

B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。
B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；
B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

索引的数据结构

由于数据库的索引是保存到磁盘上的，因此当我们通过索引查找某行数据的时候，就需要先从磁盘读取索引到内存，再通过索引从磁盘中找到某行数据，然后读入到内存，也就是说查询过程中会发生多次磁盘 I/O，而磁盘 I/O 次数越多，所消耗的时间也就越大。

所以，我们希望索引的数据结构能在尽可能少的磁盘的 I/O 操作中完成查询工作，因为磁盘 I/O 操作越少，所消耗的时间也就越小。

另外，MySQL 是支持范围查找的，所以索引的数据结构不仅要能高效地查询某一个记录，而且也要能高效地执行范围查找。

所以，要设计一个适合 MySQL 索引的数据结构，至少满足以下要求：

能在尽可能少的磁盘的 I/O 操作中完成查询工作；
要能高效地查询某一个记录，也要能高效地执行范围查找；

B树与B+树的区别

单点查询
B 树进行单个索引查询时，最快可以在 O(1) 的时间代价内就查到，而从平均时间代价来看，会比 B+ 树稍快一些。
B 树的查询波动会比较大，因为每个节点即存索引又存记录，所以有时候访问到了非叶子节点就可以找到索引，而有时需要访问到叶子节点才能找到索引。
B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。
插入和删除的效率
B+ 树有大量的冗余节点，这样使得删除一个节点的时候，可以直接从叶子节点中删除，甚至可以不动非叶子节点，这样删除非常快；B 树则不同，B 树没有冗余节点，删除节点的时候非常复杂，比如删除根节点中的数据，可能涉及复杂的树的变形，B+ 树的插入也是一样，有冗余节点，插入可能存在节点的分裂（如果节点饱和），但是最多只涉及树的一条路径。而且 B+ 树会自动平衡，不需要像更多复杂的算法，类似红黑树的旋转操作等。
因此，B+ 树的插入和删除效率更高。
范围查询
B 树和 B+ 树等值查询原理基本一致，先从根节点查找，然后对比目标数据的范围，最后递归的进入子节点查找。
因为 B+ 树所有叶子节点间还有一个链表进行连接，这种设计对范围查找非常有帮助。
而 B 树没有将所有叶子节点用链表串联起来的结构，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。
因此，存在大量范围检索的场景，适合使用 B+树，比如数据库。而对于大量的单个索引查询的场景，可以考虑 B 树，比如 nosql 的MongoDB。

B+Tree 相比于 B 树、二叉树或 Hash 索引结构的优势

B+Tree vs 二叉树
对于有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。
在实际的应用当中， d 值是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 3~~4 层左右，也就是说一次数据查询操作只需要做 3~~4 次的磁盘 I/O 操作就能查询到目标数据。
而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。
B+Tree vs B Tree
B+Tree 只在叶子节点存储数据，而 B 树的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。
另外，B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。
B+Tree vs Hash
Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。
但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因。

MySQL 单表不要超过 2000W 行？

索引结构不会影响单表最大行数，2000W 也只是推荐值，超过了这个值可能会导致 B + 树层级更高，影响查询性能。

索引失效有哪些？

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。
当我们在查询条件中对索引列使用函数，就会导致索引失效。
因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。
不过，从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。
当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。
原因同上。
MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较（”10” → 10）。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

MySQL 使用 like “%x“，索引一定会失效吗？

使用左模糊匹配（like “%xx”）并不一定会走全表扫描，关键还是看数据表中的字段。

如果数据库表中的字段只有主键+二级索引，那么即使使用了左模糊匹配，也不会走全表扫描（type=all），而是走全扫描二级索引树(type=index)。

再说一个相似，联合索引要遵循最左匹配才能走索引，但是如果数据库表中的字段都是索引的话，即使查询过程中，没有遵循最左匹配原则，也是走全扫描二级索引树(type=index)

count(*) 和 count(1) 有什么区别？哪个性能最好？

count() 函数性能比较：

count(*) = count(1) > count(主键字段) > count(字段)

count() 是什么
count() 是一个聚合函数，函数的参数不仅可以是字段名，也可以是其他任意表达式，该函数作用是统计符合查询条件的记录中，函数指定的参数不为 NULL 的记录有多少个。
count(主键字段) 执行过程
在通过 count 函数统计有多少个记录时，MySQL 的 server 层会维护一个名叫 count 的变量。
server 层会循环向 InnoDB 读取一条记录，如果 count 函数指定的参数不为 NULL，那么就会将变量 count 加 1，直到符合查询的全部记录被读完，就退出循环。最后将 count 变量的值发送给客户端。
1. 如果表里只有主键索引，没有二级索引时，那么，InnoDB 循环遍历聚簇索引，将读取到的记录返回给 server 层，然后读取记录中的 id 值，就会 id 值判断是否为 NULL，如果不为 NULL，就将 count 变量加 1。
2. 如果表里有二级索引时，InnoDB 循环遍历的对象就不是聚簇索引，而是二级索引。这是因为相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间，所以二级索引树比聚簇索引树小，这样遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小，因此「优化器」优先选择的是二级索引。
count(1) 执行过程
InnoDB 循环遍历聚簇索引（主键索引），将读取到的记录返回给 server 层，但是不会读取记录中的任何字段的值，因为 count 函数的参数是 1，不是字段，所以不需要读取记录中的字段值。参数 1 很明显并不是 NULL，因此 server 层每从 InnoDB 读取到一条记录，就将 count 变量加 1。
可以看到，count(1) 相比 count(主键字段) 少一个步骤，就是不需要读取记录中的字段值，所以通常会说 count(1) 执行效率会比 count(主键字段) 高一点。
如果表里有二级索引时，InnoDB 循环遍历的对象就二级索引了。
count(*) 执行过程
当使用 count(*) 时，MySQL 会将 * 参数转化为参数 0 来处理。所以，count(*) 执行过程跟 count(1) 执行过程基本一样的，性能没有什么差异。
count(字段) 执行过程
count(字段)会采用全表扫描的方式来计数，所以它的执行效率是比较差的。

上述案例都是基于 Innodb 存储引擎来说明的，但是在 MyISAM 存储引擎里，执行 count 函数的方式是不一样的，通常在没有任何查询条件下的 count(*)，MyISAM 的查询速度要明显快于 InnoDB。

使用 MyISAM 引擎时，执行 count 函数只需要 O(1)复杂度，这是因为每张 MyISAM 的数据表都有一个 meta 信息有存储了row_count值，由表级锁保证一致性，所以直接读取 row_count 值就是 count 函数的执行结果。

而 InnoDB 存储引擎是支持事务的，同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的，所以无法像 MyISAM一样，只维护一个 row_count 变量。

如何优化 count(*)

近似值
如果你的业务对于统计个数不需要很精确，比如搜索引擎在搜索关键词的时候，给出的搜索结果条数是一个大概值。可以使用show table status 或者 explain 命令来表进行估算。
执行 explain 命令效率是很高的，因为它并不会真正的去查询。
额外表保存计数值
如果是想精确的获取表的记录总数，我们可以将这个计数值保存到单独的一张计数表中。
当我们在数据表插入一条记录的同时，将计数表中的计数字段 + 1。也就是说，在新增和删除操作时，我们需要额外维护这个计数表。

针对针对下面这条 SQL，你怎么通过索引来提高查询效率呢？

1	`select * from order where status = 1 order by create_time asc`

单独给 status 建立一个索引就可以了。
更好的方式给 status 和 create_time 列建立一个联合索引，因为这样可以避免 MySQL 数据库发生文件排序。

因为在查询时，如果只用到 status 的索引，但是这条语句还要对 create_time 排序，这时就要用文件排序 filesort，也就是在 SQL 执行计划中，Extra 列会出现 Using filesort。

所以，要利用索引的有序性，在 status 和 create_time 列建立联合索引，这样根据 status 筛选后的数据就是按照 create_time 排好序的，避免在文件排序，提高了查询效率。

事务篇

事务的特性

事务是由 MySQL 的引擎来实现的，我们常见的 InnoDB 引擎它是支持事务的。

不过并不是所有的引擎都能支持事务，比如 MySQL 原生的 MyISAM 引擎就不支持事务，也正是这样，所以大多数 MySQL 的引擎都是用 InnoDB。

事务看起来感觉简单，但是要实现事务必须要遵守 4 个特性，分别如下：

原子性（Atomicity）：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节，而且事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性（Consistency）：是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。
隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，因为多个事务同时使用相同的数据时，不会相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。
持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

InnoDB 引擎通过什么技术来保证事务ACID的特性

持久性是通过 redo log （重做日志）来保证的；
原子性是通过 undo log（回滚日志）来保证的；
隔离性是通过 MVCC（多版本并发控制）或锁机制来保证的；
一致性则是通过持久性+原子性+隔离性来保证；

并行事务会引发什么问题

MySQL 服务端是允许多个客户端连接的，这意味着 MySQL 会出现同时处理多个事务的情况。在同时处理多个事务的时候，可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题。

什么是脏读

如果一个事务「读到」了另一个「未提交事务修改过的数据」，就意味着发生了「脏读」现象。

如图，事务B读到事务A修改后的数据，但是事务A还未提交，发生了回滚操作，那么事务 B 刚才得到的数据就是过期的数据，这种现象就被称为脏读。

什么是不可重复读

在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了「不可重复读」现象。

假设有 A 和 B 这两个事务同时在处理，事务 A 先开始从数据库中读取数据，然后继续执行代码逻辑处理，在这过程中如果事务 B 更新了这条数据，并提交了事务，那么当事务 A 再次读取该数据时，就会发现前后两次读到的数据是不一致的，这种现象就被称为不可重复读。

不可重复读

什么是幻读

当同一个查询在不同的时间产生不同的结果集时，事务中就会出现所谓的幻象问题。例如，如果 SELECT 执行了两次，但第二次返回了第一次没有返回的行，就意味着发生了「幻读」现象。

事务的隔离级别

SQL 标准提出了四种隔离级别来规避脏读、不可重复读、幻读的现象，隔离级别越高，性能效率就越低，这四个隔离级别如下：

读未提交（read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到；
读提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到；
可重复读（repeatable read），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；
串行化（serializable ）；会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

针对不同的隔离级别，并发事务时可能发生的现象也会不同：

不同的隔离级别并发事务时可能发生的现象

举个具体的例子来说明这四种隔离级别，有一张账户余额表，里面有一条账户余额为 100 万的记录。然后有两个并发的事务，事务 A 只负责查询余额，事务 B 则会将我的余额改成 200 万，下面是按照时间顺序执行两个事务的行为：

在不同隔离级别下，事务 A 执行过程中查询到的余额可能会不同：

在「读未提交」隔离级别下，事务 B 修改余额后，虽然没有提交事务，但是此时的余额已经可以被事务 A 看见了，于是事务 A 中余额 V1 查询的值是 200 万，余额 V2、V3 自然也是 200 万了；
在「读提交」隔离级别下，事务 B 修改余额后，因为没有提交事务，所以事务 A 中余额 V1 的值还是 100 万，等事务 B 提交完后，最新的余额数据才能被事务 A 看见，因此额 V2、V3 都是 200 万；
在「可重复读」隔离级别下，事务 A 只能看见启动事务时的数据，所以余额 V1、余额 V2 的值都是 100 万，当事务 A 提交事务后，就能看见最新的余额数据了，所以余额 V3 的值是 200 万；
在「串行化」隔离级别下，事务 B 在执行将余额 100 万修改为 200 万时，由于此前事务 A 执行了读操作，这样就发生了读写冲突，于是就会被锁住，直到事务 A 提交后，事务 B 才可以继续执行，所以从 A 的角度看，余额 V1、V2 的值是 100 万，余额 V3 的值是 200万。

四种隔离级别具体是如何实现的？

对于「读未提交」隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；
对于「串行化」隔离级别的事务来说，通过加读写锁的方式来避免并行访问；
对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，可以把 Read View 理解成一个数据快照。「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。

讲一讲 Read View 在 MVCC 里是如何工作的

Read View 有四个重要的字段：

Read View

m_ids ：指的是在创建 Read View 时，当前数据库中「活跃事务」的事务 id 列表，注意是一个列表，“活跃事务”指的就是，启动了但还没提交的事务。
min_trx_id ：指的是在创建 Read View 时，当前数据库中「活跃事务」中事务 id 最小的事务，也就是 m_ids 的最小值。
max_trx_id ：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的 id 值，也就是全局事务中最大的事务 id 值 + 1；
creator_trx_id ：指的是创建该 Read View 的事务的事务 id。

对于使用 InnoDB 存储引擎的数据库表，它的聚簇索引记录中都包含下面两个隐藏列：

隐藏列

trx_id，当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；
roll_pointer，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。

在创建 Read View 后，记录中的 trx_id 可以划分这三种情况：

一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：

如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。
如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中：
1. 如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
2. 如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

这种通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）。

MySQL是如何避免幻读的？

针对快照读（普通 select 语句），是通过 MVCC 方式解决幻读的。
针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读。

MySQL 里除了普通查询是快照读，其他都是当前读，比如 update、insert、delete，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作。

可重复读隔离级别下虽然很大程度上避免了幻读，但是还是没有能完全解决幻读。

情景一：

情景一

情景二：

T1 时刻：事务 A 先执行「快照读语句」：select * from t_test where id > 100 得到了 3 条记录。
T2 时刻：事务 B 往插入一个 id= 200 的记录并提交；
T3 时刻：事务 A 再执行「当前读语句」 select * from t_test where id > 100 for update 就会得到 4 条记录，此时也发生了幻读现象。

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select … for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。

锁篇

MySQL 有哪些锁？

全局锁：FTWRL
执行以下语句之后，使用全局锁，整个数据库就处于只读状态了，这时其他线程执行对数据的增删改或者对表结构的更改操作操作，都会被阻塞。
1
flush tables with read lock
释放全局锁：
1
unlock tables
会话断开也会释放全局锁。
1. 全局锁的应用场景
  全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。
2. 全局锁的缺点
  业务只能读数据，而不能更新数据，这样会造成业务停滞。
3. 备份数据库数据的时候，使用全局锁会影响业务，那有什么其他方式可以避免？
  如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。

表级锁

表锁
对学生表（t_student）加表锁，可以使用下面的命令：
1
2
3
4
//表级别的共享锁，也就是读锁； lock tables t_student read; //表级别的独占锁，也就是写锁； lock tables t_stuent write;
表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。
也就是说如果本线程对学生表加了「共享表锁」，那么本线程接下来如果要对学生表执行写操作的语句，是会被阻塞的，当然其他线程对学生表进行写操作时也会被阻塞，直到锁被释放。
元数据锁（MDL）
当对数据库表进行操作时，会自动给这个表加上 MDL：
- 对一张表进行 CRUD 操作时，加的是 MDL 读锁；
- 对一张表做结构变更操作的时候，加的是 MDL 写锁；
MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。
所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。

意向锁

意向锁的使用场景：

在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前（行锁），需要先在表级别加上一个「意向共享锁」；
在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前（行锁），需要先在表级别加上一个「意向独占锁」；

当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。不过，select 也是可以对记录加共享锁和独占锁的，具体方式如下：

//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;
//先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables … read）和独占表锁（lock tables … write）发生冲突。

表锁和行锁是满足读读共享、读写互斥、写写互斥的。

如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

所以，意向锁的目的是为了快速判断表里是否有记录被加锁。

AUTO-INC锁
在为某个字段声明 AUTO_INCREMENT 属性时，之后可以在插入数据时，可以不指定该字段的值，数据库会自动给该字段赋值递增的值，这主要是通过以下两种方式实现
1. AUTO-INC 锁实现的。
  AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。那么，一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。
2. 一种轻量级的锁来实现自增。
  一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。
InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。
1. 当 innodb_autoinc_lock_mode = 0，采用 AUTO-INC 锁；
2. 当 innodb_autoinc_lock_mode = 2，采用轻量级锁；
3. 当 innodb_autoinc_lock_mode = 1，默认值，两种锁混用，如果能够确定插入记录的数量就采用轻量级锁，不确定时就采用 AUTO-INC 锁。
AUTO-INC 锁在对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。轻量级锁是性能最高的方式，但是会带来一定的问题。因为并发插入的存在，在每次插入时，自增长的值可能不是连续的，这在有主从复制的场景中是不安全的。

行级锁
InnoDB 引擎支持行级锁的，而 MyISAM 引擎并不支持行级锁。
1. Record Lock，记录锁，锁住的是一条记录。记录锁是有 S 锁（共享）和 X 锁（独占）之分
2. Gap Lock，间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。
3. Next Lock，临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。
4. 插入意向锁，一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。可以看成一种特殊的间隙锁。

MySQL 是怎么加锁的？

对记录加锁时，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间。

但是，next-key lock 在一些场景下会退化成记录锁或间隙锁。

唯一索引等值查询
1. 当查询的记录是存在的，在用「唯一索引等值进行查询」时，next-key lock 会退化成「记录锁」。
2. 当查询的记录是不存在的，在用「唯一索引等值进行查询」时，next-key lock 会退化成「间隙锁」。
唯一索引范围查询
先生成next-key lock，再看是否退化。
非唯一索引等值查询
1. 当查询的记录存在时，除了会加 next-key lock 外，还额外加间隙锁，也就是会加两把锁。
2. 当查询的记录不存在时，只会加 next-key lock，然后会退化为间隙锁，也就是只会加一把锁。
非唯一索引范围查询
生成next-key lock，并且不会退化。

非唯一索引和主键索引的范围查询的加锁规则不同之处在于：

唯一索引在满足一些条件的时候，next-key lock 退化为间隙锁和记录锁。
非唯一索引范围查询，next-key lock 不会退化为间隙锁和记录锁。

update 没加索引会锁全表？

当在数据量非常大的数据库表执行 update 语句时，如果没有使用索引，就会给全表的加上 next-key 锁，那么锁就会持续很长一段时间，直到事务结束，而这期间除了 select ... from语句，其他语句都会被锁住不能执行，业务会因此停滞。

但并不是update 语句的 where 带上索引就能避免全表记录加锁，关键还得看这条语句在执行过程种，优化器最终选择的是索引扫描，还是全表扫描，如果走了全表扫描，就会对全表的记录加锁了。

字节面试：加了什么锁，导致死锁的？

过程分析：

Time 1 阶段加锁分析
唯一索引等值查询，且记录不存在，此时事务 A 在主键索引上加的是间隙锁，锁范围是$$(20, 30)$$。
Time 2 阶段加锁分析
同样，唯一索引等值查询，且记录不存在，此时事务 B 在主键索引上加的是间隙锁，锁范围是$$(20, 30)$$。
间隙锁的意义只在于阻止区间被插入，因此是可以共存的。一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁，共享（S型）和排他（X型）的间隙锁是没有区别的，他们相互不冲突，且功能相同。
Time 3 阶段加锁分析
事务 A 的状态为等待状态（LOCK_STATUS: WAITING），因为向事务 B 生成的间隙锁（范围 $$(20, 30)$$）中插入了一条记录，所以事务 A 的插入操作生成了一个插入意向锁。尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。所以，插入意向锁和间隙锁之间是冲突的。
Time 4 阶段加锁分析
事务 B 在生成插入意向锁时而导致被阻塞，这是因为事务 B 向事务 A 生成的范围为 $$(20, 30)$$ 的间隙锁插入了一条记录，而插入意向锁和间隙锁是冲突的，所以事务 B 在获取插入意向锁时就陷入了等待状态。

事务 A 和事务 B 在执行完后 update 语句后都持有范围为 $$(20, 30)$$ 的间隙锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，满足了死锁的四个条件：互斥、占有且等待、不可强占用、循环等待，因此发生了死锁。