[MySQL]MySQL学习笔记

最新推荐文章于 2024-10-16 15:26:08 发布

麻辣味蜗牛

最新推荐文章于 2024-10-16 15:26:08 发布

阅读量742

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/A_tina/article/details/121439021

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

SQL：Structured Query Language，意为结构化查询语言，是访问数据库的最常见的标准化语言。

关系型数据库和非关系型数据库

一、关系型数据库特点：

关系型数据库以表格的形式存在，基于行来存储数据，是一个二维模式。
关系型数据库存储的是结构化数据，数据存储有固定模式，存储的数据需要适应表结构，也就是说定义好表结构之后，数据不能随意插入。
关系型数据库可以设置管理不同数据字段之间关系的规则，例如一对一、一对多、多对多等。
大部分关系型数据库都支持 SQL（结构化查询语言），而且都支持复杂的关联查询。
关系型数据库支持事务，通过事务的 ACID 特性严格或者实时保证数据的一致性。

二、关系型数据库的缺点主要如下：

水平扩容不太友好，需要复杂的技术来支持，如分库分表等。
定义了严格的数据格式，因此存储的数据格式会受到限制，无法随意动态的插入不同格式的数据。
关系型数据库为了保证数据的持久性，一般会将数据持计划到磁盘，因此在高并发和海量数据场景下，读写性能会受到一定的影响。

三、非关系型数据库有以下特点：

非关系型数据库一般存储非结构化数据，比如文本，图片，音频等。
非关系型数据库的“表”之间没有关联关系，可扩展性强。
保证数据的最终一致性，遵循BASE理论。
高并发和海量数据场景下，仍然具有高效读写能力。
支持分布式，扩缩容简单。

而正因为关系型数据库有一定的缺点和局现象，所以有时候往往需要结合非关系型数据库来一起使用。目前已经有了所谓的NewSQL，其结合了关系型数据库和非关系型数据库的部分优点，如 TiDB，VoltDB，ScaleDB等.

MySQL逻辑架构

MySQL 中分为了 Server 层和存储引擎层，这也是 MySQL 数据库相比较其他数据库的一个重要特点：插件式存储引擎。

所以在 MySQL 中，存储引擎是基于单张表来定义的，而不是基于整个数据库，也就是说我们可以给每一张表定义不同的存储引擎，Server 层最终通过调用存储引擎层的接口来实现一系列操作。

MySQL存储引擎

InnoDB 引擎

InnoDB从 MySQL 5.5 开始成为了 MySQL 默认的存储引擎，大部分情况下，我们都会选择这个引擎。InnoDB 支持事务，它具有提交、回滚和崩溃恢复等能力来保护用户数据。其内部采用 MVCC 机制来实现高并发，支持行级锁，实现了 SQL 92 的四个标准隔离级别。为了保证数据的完整性，InnoDB 引擎还支持外键引用来实现完整性约束。
MyISAM 引擎

在 MySQL 5.1 及之前的版本，MyISAM 都是默认的存储引擎。MYISAM 引擎不支持事务和行级锁，其设计简单，数据以紧密格式存储，在某些场景如果可以忍受数据丢失（比如日志收集），那么也可以考虑使用 MyISAM 引擎。MyISAM 引擎拥有较高的插入和查询速度。
Memory 引擎

Memory 引擎所有的数据都保存在内存中，所以其速度是非常快的，不过因为其数据保存在内存中，重启之后数据会全部丢失（表结构不会丢失）。Memory 引擎表可以用于缓存，因为其支持 Hash 索引，所以查找速度非常快，但是 Memoery 表也只支持表级锁，因此并发的写入性能比较低。

如何选择存储引擎

如果业务系统中对数据要求比较高，且需要支持事务，那么 InnoDB 是不错的选择。
如果业务系统中查询多，更新少，而且对查询性能要求比较高，可以选择 MyISAM 引擎。
如果业务系统中在查询中有建立临时表的需求，那么可以考虑使用 Memory 引擎。

MySQL索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构。再直白点就是我们可以把索引理解成图书或者字典的目录。

索引结构（索引需要存储哪些信息）：

索引值：就是表里面索引列对应的值，因为我们查询就是通过索引值来查询的，所以索引值必然要存储。
数据的磁盘地址（通过磁盘地址找到当前数据）或者直接存储整条数据：通过索引搜索的目的其实是需要找到当前对应的整条数据，所以必然需要存储地址或者数据。
子节点的地址：任何时候查询都是从根节点开始的，当发现根节点的数据并不是我们想要的数据，我们需要继续往下查询，所以需要知道当前根节点中所有子节点的引用地址。

InnoDB 索引结构：

在 InnoDB 存储引擎中，页（Page）是用于管理数据的最小磁盘单位，页的默认大小为 16KB（不同版本会有差异）。而这个页也就是对应了上图中的每一个节点，每查询一次节点就需要进行一次 IO 操作。那么问题就来了，上图中，AVL 树一个节点上只存了一个关键字（索引值）+ 一个磁盘地址+ 左右节点的引用，这几个信息加起来占用了多大空间，我们可以来算一下：

索引关键字：假设是采用 32 位的 uuid 进行存储，那么就是占了 32 个字节。
数据磁盘地址：这一块其实在 InnoDB 中采用的是 8 个字节进行存储。
子节点的引用地址：假设有 2 个子节点，那么这一块也是占用了 8 * 2 字节。

上面三部分内容加起来总共是 56 个字节，而 16kb 有多少个字节呢？答案是 16384 个字节。

所以如果采用平衡二叉树来存储索引的话，我们一个节点存储的内容是远远小于 16kb，这样看来，一个节点只存储一个关键字，浪费了大量的空间。

而且因为二叉树只有两路，数据量一上来，整颗树就会变得非常深，这也会很影响查询性能，所以我们需要做的就是将一颗“瘦高”的树变成“矮胖”的树。将一颗“瘦高”的树变成“矮胖”的树最简单的办法就是将路数变多，也就是让一个节点存储更多的关键字。

B+树：

在 InnoDB 中，B+ 树有以下特点：

B+ 树的关键字的数量是跟路数相等的。
B+ 树的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据。而搜索到关键字也不会直接返回，也仍然会到最后一层的叶子节点。
B+ 树的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。

B+ 树相比较 B 树更有以下优势：

扫库、扫表能力更强：如果我们要对表进行全表扫描，只需要遍历叶子节点就可以了，不需要遍历整棵 B+ 树，因为其数据只存储在叶子节点。
B+ 树的磁盘读写能力相对于 B 树来说更强： B+ 树的根节点和枝节点不保存数据，所以一个节点可以保存更多的关键字，一次磁盘加载（即一次 IO 操作）能获取到相对更多的关键字。
天然具备排序能力：叶子节点上有下一个数据区的指针，数据形成了链表。
效率稳定：B+ 树永远是在叶子节点拿到数据，所以 IO 次数是稳定的，而 B 树运气好根节点就拿到数据，运气不好就要到叶子节点才能拿到数据，所花费的时间会有差异。

MyISAM的索引

每张 MyISAM 表在磁盘上会创建三个文件：.frm，.MYD 和 .MYI，其中 .frm 文件为表结构文件，每个存储引擎都会有这个文件，.MYD 文件用来存储数据，.MYI 文件用来存储索引，也就是说 MyISAM 表的数据和索引是分开存储的，这一点和 InnoDB 不一样。

MyISAM 的 B+ 树里面，叶子节点存储的是当前索引的值以及当前数据文件对应的磁盘地址。所以如果从索引文件 .MYI 中找到键值后，会根据其存储的磁盘地址到数据文件 .MYD 中获取相应的数据记录。

在 MyISAM 引擎中，主键索引和非主键索引没有差别，都是一样存储，查询速度也没有差别。

InnoDB的索引

InnoDB 为 MySQL 默认存储引擎。每张 InnoDB 表在磁盘上会创建两个文件：.frm 和 .ibd，其中 .frm 文件和 MyISAM 引擎一样，用来存储表结构的，.ibd 文件存储的是索引和数据，InnoDB 中索引和数据放在同一个文件中。

InnoDB 引擎和 MyISAM 引擎还有一个最大的不同就是 InnoDB 引擎是以主键索引来组织数据的（主键索引和非主键索引的存储结构是不同的），InnoDB 存储引擎中这种组织数据的方式被称之为索引组织表（index-organized table），其中的主键索引也被称之为聚集索引（clustered index）。

聚集索引（主键索引）

聚集的术语表示的是索引键值和数据紧凑的存储在一起。而数据又不可能同时存在两个地方，所以 InnoDB 每张表都有且只有一个聚集索引。换言之，也就是说每张表都必须有且只有一个主键。

建表的时候没有主键索引也可以建表成功，那么这又是为什么呢？

如果我们没有显示的指定主键，InnoDB 会选择一个非空的唯一索引列作为主键；
如果我们也没有创建非空的唯一索引，那么 InnoDB 就会选择其自己内置的一个 6 字节长的 ROWID 自增列作为主键。

非聚集索引

除了主键索引之外的其他索引都是非聚集索引。既然聚集索引的索引键值和数据行存放在一起，而聚集索引又只有一个，那么非聚集索引又是怎么存储数据的呢？

非聚集索引的叶子节点存储的是当前索引的键值和主键索引的键值。大致结构如下图所示（右边为非聚集索引，左边为聚集索引）：

所以非聚集索引查询数据和聚集索引查询数据是不同的，因为非聚集索引的叶子节点只有当前索引的键值和主键的键值，也就是说查询数据的时候获取到非聚集索引的叶子节点只能拿到当前索引值和主键索引值。

回表指的就是非聚集索引从叶子节点拿到数据（主键的键值）之后，还需要再根据主键键值去扫描主键索引的 B+ 树，这种操作就叫做回表。

回表操作需要扫描两颗 B+ 树，这也就是为什么在 InnoDB 中主键索引的效率相比较其他索引是最高的，因为主键索引只需要扫描一棵 B+ 树。

覆盖索引

查询只需要查询当前索引的值和主键的值，而不需要查其他数据，这时候就不需要回表了，直接就可以返回，这种也称之为覆盖索引。

因为非聚集索引中存储了当前索引的值和主键的值，所以如果我们不需要获取当前索引值和主键索引值之外的其他信息，那么在拿到主键索引值之后就可以直接返回，也就是只需要扫描一棵非聚集索引的 B+ 树，而因为非聚集索引不存储数据，所以非聚集索引的 B+ 树占用的空间是小于主键索引 B+ 树的，所以查询速度会更快。

这也是为什么通常不建议写 select * 语句的原因，因为 select * 肯定无法用到覆盖索引（除非整张表的字段是一个联合索引），而覆盖索引可以少扫描一棵聚集索引的 B+ 树，而且因为辅助索引不会存储整条数据，所以大小也要远小于聚集索引的 B+ 树，故而对性能有较大的提升。

索引数据类型

B树索引：平常使用最多；
哈希索引：哈希索引在 InnoDB 引擎中并不支持创建，其只是一种自适应哈希；
全文索引：全文索引也是 5.7 版本才开始支持的一种索引，相对来说使用并不太广泛。

B树索引中我们又可以细分为普通索引，唯一索引，前缀索引和多列联合索引等等。

索引优化方案

优化SQL：其实优化最主要的就是优化索引，创建合理的表结构，建立合适的索引，最终的目的都是为了让查询语句能使用上合适的索引，从而提升查询速度。

索引下推（ICP）

Index Condition Pushdown 索引条件下推。

如果没有ICP，存储引擎将遍历索引以定位基表中的行，并将它们返回给 MySQL Server 层，由 Server 层计算行的 where 条件。

在启用 ICP 的情况下，如果 where 条件的一部分可以通过只使用索引中的列来计算，那么 MySQL 服务器将这一部分 where 条件下推到存储引擎。然后，存储引擎使用索引条目来计算已推入的索引条件，只有满足这个条件，才从表中读取行。ICP 可以减少存储引擎必须访问基表的次数和 MySQL 服务器必须访问存储引擎的次数。

多范围读（MRR）

Multi-Range Read多范围读。

MRR 优化的目的是为了减少磁盘的随机 IO 访问，并且将随机访问转化为顺序的数据访问，所以 MRR 优化措施对 IO-bound 型的 SQL 查询语句可能带来极大的性能提升。

两个变量：

mrr=on
mrr_cost_based=on

mrr=on 表示启用，mrr_cost_based 表示是否通过基于开销的方式来启用 MRR，如果 mrr_cost_based=on，则即使满足了使用 MRR 的条件，优化器也会视当前查询的开销来决定是否使用 MRR 优化，如果我们想总是开启MRR，则可以进行如下设置：

SET optimizer_switch='mrr=on,mrr_cost_based=off';

MRR 工作一般经过以下三步：

将查询得到的辅助索引（非聚集索引）键值存放于缓存之中，注意，这时候缓存中的数据是根据辅助索引的键值排序的。
将缓存中的数据根据 row ID（主键）进行重排序。
然后再根据 row ID（主键）的顺序去访问。

如果我们通过辅助索引查找到了辅助索引的键值和主键的键值，这时候我们需要回表，假如辅助索引和主键索引顺序相差很大，那么回表查主键 B+ 树的时候，就是随机访问磁盘，也就是随机 IO 操作，而如果使用了 MRR 优化，就会按照主键进行重排序，这时候再回表就是顺序 IO，所以说 MRR 之所以能优化是因为顺序 IO 访问的效率是远远大于随机 IO 访问的。

索引合并（INDEX MERGE）

索引合并优化，这个意思就是我们在一个表中建立了很多单列索引，然后查询的时候同时用到了多列作为条件，MySQL 能够识别并分别使用单列索引进行扫描，然后将结果合并。这种算法一般用于以下三种情况：

or 条件的并集（union 或者 union all）。
and 条件的交集。
综合前面两种情况。

注意：过多的单列索引大部分情况下并不能提高性能。索引合并虽然是 MySQL 的优化方案，但是出现了这种现象，更多是说明索引建的很糟糕。

MySQL优化器选择最优索引

MySQL 选择索引是基于开销来选择的，并非基于特定的规则来决定使用哪个索引，所以我们大家耳熟能详的一些常用规则有些时候其实并不适用，因为当这些规则和开销产生了冲突，MySQL 会基于开销来进行选择。

索引判断基数 Cardinality

Cardinality：MySQL 最终选择哪个索引是基于这个字段来进行判定的。索引中唯一值的估计值。这个数字越接近总数，则表示索引的选择性越高，如果这个数很小，那么可以考虑删除这个索引，因为重复值太多，选择性就不高，用到索引的概率也相对较低。Cardinality 是通过采样来实现计算的，也就是说并不是一个精确值，而是一个统计值。

Cardinality 的更新策略：

上一次统计 Cardinality 之后，表中 1/M 的数据发生过变化。
stat_modifier_counter>2,000,000,000：这种情况主要针对的是假如少数行频繁的更改，表中的数据发生变化数达不到 1/M 的情况，所以在 InnoDB 引擎内部有一个计数器 stat_modifier_counter专门用来统计表发生变化的次数（注意这不是某一行变化的次数，而是整体的变化次数）

索引使用规则

最左匹配原则

最左匹配原则一般针对的是模糊查询或者多列联合索引的场景。

对模糊查询（like 语句）而言，会对关键字从左到右开始匹配，如果第一个字符就是不确定的，那么就无法使用索引，因为检索的时候无法从根节点根据关键字开始检索，只能全表查询；

对于多列联合索引而言，如果索引有 a 和 b 两列，当查询条件只有 b 这一列，因为 a 列并未作为条件，也会导致无法从根节点开始检索，从而导致索引失效。

其他无法使用索引场景

在索引列上使用函数（replace/substr/concat/sum/count/avg 等），使用表达式或者计算（+、-、*、/）。
字符串不加引号，会出现隐式转换，相当于使用函数 to_char()。
使用 !，<>，not like，not in 等反向查询。

注意：有些原则只是一般情况下适用，特殊情况下可能并不会遵循这些原则，比如按规则来说是无法使用索引的，然而有特例是可以用到索引的。因为 MySQL 并不是基于这些规则来决定是否使用索引的，而是基于开销来判断是否使用索引。

事务

事务（Transaction）是由一系列对数据库中的数据进行访问与更新的操作所组成的一个程序执行单元。

ACID特性

A（Atomicity）原子性

原子性指的是数据库事务是不可分割的一部分，只有一个事务中的所有操作都成功，这个事务才算执行成功，一旦有一个操作失败，那么其他成功的操作也必须回滚。

C（Consistent）一致性

一致性指的是在事务开始之前和事务结束之后，数据库的完整性约束都没有被破坏，事务执行的前后都是合法的数据状态。

具体点来说，一个合法的数据库状态包括了以下两种：

数据库自身的完整性约束。比如主键必须唯一，长度必须符合定义。
用户自定义的完整性约束。比如转账功能，无论两个账户之间怎么转账，最后总和应该保持不变。

I（Isolation）隔离性

隔离性就是说每个事务之间的操作应该相互隔离，互不干扰。比如说一个事务提交之前对另一个事务不可见。

D（Durable）持久性

事务一旦提交成功了，那么就应该是持久的，即使是数据库重启，服务器宕机等情况发生，数据都不会丢失。

常用的事务控制语句

START TRANSACTION 或者 BEGIN：显式的开启事务。需要注意的是在存储过程中只能用 START TRANSACTION 开启事务，因为存储过程本来有 BEGIN...END 语法，两者会冲突。
COMMIT：提交事务。也可以写成 COMMIT WORK。
ROLLBACK：回滚事务。也可以写成 ROLLBACK WORK。
SAVEPOINT identifier：自定义保存点，适用于长事务，可以回滚到我们自定义的位置，identifier 为自定义的一个唯一标识，只要保证唯一就行。
RELEASE SAVEPOINT identifier：删除指定保存点，identifier 为自定义的保存点唯一标识，当前语句如果使用一个不存在的保存点时，会直接报错。
ROLLBACK TO [SAVEPOINT] identifier：回滚到指定保存点。

COMMIT 和 COMMIT WORK 的区别（ROLLBACK 和 ROLLBACK WORK同）

可以通过变量 completion_type 来控制

事务的分类

扁平事务

扁平事务是最简单也是最常用的一种事务，这种事务中的所有操作都是原子的，要么全部成功，要么什么都不做，平常我们使用的事务绝大多数都属于扁平事务。

带有保存点的扁平事务

当一个事务过长时，为了避免执行快结束的时候报错导致所有语句都要重新执行，我们可以在指定位置定义好保存点，这样当事务处理到后面报错的时候，我们就可以不需要回滚整个事务，而是回滚到我们自定义好的某一个保存点。

注意：保存点并不会被持久化，所以在事务提交之前，如果系统发生崩溃，所有的保存点都将消失。

链事务

在提交一个事务之后，释放掉我们不需要的数据，将必要的数据隐式的传给下一个事务。（注意：提交事务操作和开始下一个事务操作是一个原子操作），这就意味着下一个事务能看到上一个事务的结果。

链事务可以看成带有保存点的特殊事务，他们的区别就是带有保存点的事务可以回滚到任意保存点，而链事务中只能回滚到最近的一个保存点（即最新的一个开始事务的点）。

嵌套事务

嵌套事务就是说一个事务之中嵌套另一个事务，事务之间存在父子关系，子事务的提交之后并不生效，需要等到父事务提交之后才会生效。

需要注意的是 MySQL 原生并不支持嵌套事务，但是可以通过保存点模拟嵌套事务，只是说这么模拟的话就没有真正的嵌套事务这么灵活。

分布式事务

分布式事务通常就是在分布式环境下，多个数据库下同时运行不同的扁平事务。多个数据库环境下运行的扁平事务就合成了一个分布式事务。

隔离级别

未提交读（Read Uncommitted）：表示一个事务可以读取到其他事务未提交的数据，这种也叫做脏读。

左边是事务 1，右边是事务 2，整个执行过程如下：

事务 1 先执行一次查询，查到 id 为 1 的数据 job_name=CTO。
事务 2 开启，并执行了一句更新操作，id=1 这条数据中的 job_name 由 CTO 改成了 CEO。
接下来事务 1 又进行了一次查询（此时事务 2 尚未提交），这时候查出来了 id=1 的数据中 job_name=CEO。
事务 2 执行 rollback 语句进行回滚，也就是说事务 2 回滚之后，那么 job_name 其实还是 CTO，并没有被改变，但是事务 1 却读到了 CEO，这就是脏读。

已提交读（Read Committed）：表示一个事务只能读取到其他事务已提交的数据。解决了未提交读产生的脏读问题，但是会出现不可重复读的问题。

假设事务 2 更新之后马上就提交，然后事务 1 第二次查询查出来的结果job_name=CEO，但是这次就不算是脏读了，原因是事务 2 提交了，并没有发生回滚，这种就叫不可重复读。

而之所以这种称之为不可重复读，就是因为事务 1 中前后两次相同的查询（同一个事务）的数据得到了不一样的结果。

可重复读（Repeatable Read）：在同一个事务中，不管在任何时候执行相同的查询语句，结果都是一样的。解决了不可重复读的问题，但是这种级别会出现幻读问题。（InnoDB 引擎例外，因为临键锁）

事务 1 进行了一个范围查询，第一次只能查出一条记录，这时候事务 2 来插入了一条数据，然后事务 1 再次执行同一个查询，这时候就能查出来两条记录，也就是多了一条，给人一种幻觉，所以称之为幻读。

注意：不可重复读和幻读本质上是一样的，但是不可重复读针对的是更新和删除操作，而幻读仅针对插入操作。

串行化（Serializable）：这种是隔离的最高级别，也就是说所有的事务都是串行执行的，也就不存在并发事务，脏读，可重复读和幻读问题自然也就没有了。

事务隔离方案

LBCC（Lock Based Concurrency Control）基于锁的并发控制。这种方案的实现就是当一个事务去读取一条数据的时候，就上锁，不允许其他事务来操作，很明显，如果说所有的地方都直接加锁，那么当读多写少的场景时这种方案可能会影响到操作效率。加锁去查询数据时，可以保证任何时候查询到的语句都是最新的，所以这种查询方式也称之为当前读。
MVCC（Multi Version Concurrency Control）多版本的并发控制。当修改数据的时候，可以为这条数据创建一个快照，后面就可以直接读取这个快照。可重复读的实现方式正是基于 MVCC 控制的，而 MVCC 模式下，查询的数据是从快照中读取，所以也被称之为快照读。

MVCC 实现原理

数据库锁

按锁粒度分为：全局锁，表锁和行锁。

全局锁

也被简称为 FTWRL，通过以下语句执行：

FLUSH TABLE WITH READ LOCK;-- 加全局锁
UNLOCK TABLES;-- 解锁

执行这个语句之后整个数据库都只允许读，不允许写。当然，也可以使用以下语句：

SET GLOBAL READ_ONLY=TRUE;

不过需要注意的是，如果需要加全局锁的话，一般我们推荐使用 FTWRL，主要是因为如果客户端因为异常断开了，FTWRL 会自动释放锁，而使用 READ_ONLY 语句则会一直使得数据库保持 read-only 状态。

表锁

就是直接锁表，在 MyISAM 引擎中就只有表锁。

表锁也可以分为两种，一种是通过 LOCK 命令加锁，另一种是加 MDL 锁。

Lock 锁，指的是通过 LOCK 命令对表进行加锁：

LOCK TABLE user_job READ;-- 此时本线程只能读user_job表，其他线程也只能读user_job表
LOCK TABLE user_job WRITE;-- 此时本线程只能读写user_job表，其他线程不能读写user_job表
UNLOCK TABLE; -- 解锁

加表读锁后，在当前线程内，只能读，不能写。而同样的，打开其他客户端也可以读，但是写的时候会直接阻塞。在加表级写锁的情况下，在执行加锁语句所在客户端中读写都没有问题，打开一个新的客户端这时候发现读写都阻塞了。

MDL（metadata lock）即元数据锁，MDL 锁主要是用来保证读写的正确性。

MDL 锁不能手动加，而是由 MySQL 在下面两种场景下自动加的锁：当对一个表做增删改查操作的时候，加 MDL 读锁（事务提交后自动释放）。当对一个表结构做变更操作时，加 MDL 写锁（事务提交后自动释放）。

MDL 读锁之间不互斥，可以对一张表加多个 MDL 读锁，读锁和写锁之间互斥，而一旦我们对一个数据库加上了 MDL 读锁或者写锁，那么其他任何事务都无法对表结构进行修改。

注意：MDL 锁是在 Server 层实现的，所以不论使用哪种引擎，其都具有 MDL 锁。

行锁

从名字上来看，就是锁住一行数据。然而，行锁的实际实现算法会相对复杂，有时候并不仅仅只是锁住某一条数据。

正常的思路是：锁住一行数据之后，其他事务就不能来访问这条数据了，那么我们想象，假如事务 A 访问了一条数据，只是拿出来读一下，并不想去修改，正好事务 B 也来访问这条数据，也仅仅只是想拿出来读一下，并不想去修改，这时候如果因此阻塞了，就有点浪费性能了。所以为了优化这种读数据的场景，我们又把行锁分为了两大类型：行共享锁和行排他锁。

共享锁，Shared Lock，又称之为读锁，S 锁，就是说一条数据被加了 S 锁之后，其他事务也能来读数据，可以共享一把锁。
排他锁，Exclusive Lock，又称之为写锁，X 锁。就是说一条数据被加了 X 锁之后，其他事务想来访问这条数据只能阻塞等待锁的释放，具有排他性。当我们在修改数据，如：insert，update，delete 的时候 MySQL 就会自动加上排他锁。

在 InnoDB 引擎中，是允许行锁和表锁共存的。假如事务 A 给 t 表其中一行数据上锁了，这时候事务 B 想给 t 表上一个表锁，这时候怎么办呢？事务 B 怎么知道 t 表有没有行锁的存在，如果采用全表遍历的情况，当表中的数据很大的话，加锁都要加半天，效率直线下降，所以为了避免这个问题， MySQL 中就又引入了意向锁。

意向锁

也是属于表锁的一种，分为两种类型：意向共享锁（Intention Shared Lock）和意向排他锁（Intention Exclusive Lock），这两种锁又分别可以简称为 IS 锁和 IX 锁。意向锁是 MySQL 自己维护的，用户无法手动加意向，意向锁有两大加锁规则：当需要给一行数据加上 S 锁的时候，MySQL 会先给这张表加上 IS 锁。当需要给一行数据加上 X 锁的时候，MySQL 会先给这张表加上 IX 锁。

细讲行锁

行锁锁住的是索引，一旦表没有索引，则会进行锁表。

当辅助索引被锁住后，其对应的主键索引也会被锁住，而在 InnoDB 中，索引即数据，所以主键索引被锁，就相当于整条数据被锁住。假如加锁的语句只用到了覆盖索引，那么也会锁住主键索引。

行锁有三种算法：记录锁（Record Lock），间隙锁（Gap Lock）和临键锁（Next-Key Lock）。MySQL 通过加锁来防止了幻读，但是如果行锁只是锁住一行记录，好像并不能防止幻读，之所以能做到防止幻读，正是临键锁起的作用。

记录锁（Record Lock）：当我们的查询语句能命中一条记录的时候，InnoDB 就会使用记录锁，锁住所命中的这一行记录。
间隙锁（Gap Lock）：当我们的查询没有命中记录的时候，InnoDB 就会使用间隙锁。间隙是根据索引值来划分的，比如表中的主键值分别为：1,5,8,10,20。那么就会有如下六个间隙锁的区间： (-∞,1),(1,5),(5,8),(8,10),(10,20),(20,+∞)。而如查询 4 ，正好落在了间隙(1,5)，所以 MySQL 会把这个间隙加一个间隙锁，防止数据插入，这样就防止了幻读。
- 间隙锁与间隙锁之间不冲突，也就是事务 A 加了间隙锁，事务 B 可以在同一个间隙中加间隙锁。
- 间隙锁主要是会阻塞插入操作。
临键锁（Next-Key Lock）：临键锁就是记录锁和间隙锁的结合，采用的区间是左开右闭。当我们进行一个范围查询，不但命中了一条或者多条记录，且同时包括了间隙，这时候就会使用临键锁，临键锁是 InnoDB 中行锁的默认算法。

注意：这里仅针对 RR（可重复读）隔离级别，对于 RC（读已提交）隔离级除了外键约束和唯一性约束会加间隙锁，其他情况并没有间隙锁，自然也就没有了临键锁，所以 RC 级别下加的行锁可以认为都是记录锁，没有命中记录则不加锁，也就是 RC 的隔离级别是没有解决幻读问题的。

一条 select 语句的执行过程

MySQL 从大方向来说，可以分为 Server 层和存储引擎层。而 Server 层包括连接器、查询缓存、解析器、预处理器、优化器、执行器等，最后 Server 层再通过 API 接口形式调用对应的存储引擎层提供的接口来执行增删改查操作。

根据流程图，可以得出一条 select 查询大致经过以下六个步骤：

客户端发起一个请求时，首先会建立一个连接。
服务端会检查缓存，如果命中则直接返回，否则继续之后后面步骤。
服务器端根据收到的 sql 语句进行解析，然后对其进行词法分析，语法分析以及预处理。
由优化器生成执行计划。
调用存储引擎层 API 来执行查询。
返回查询到的结果。

一、第一步建立连接，这一步很容易理解。 MySQL 服务端和客户端的通信方式采用的是半双工协议。

常见的通信方式主要可以分为三种：单工，半双工，全双工。

单工：通信的时候，数据只能单向传输。比如说遥控器，我们只能用遥控器来控制电视机，而不能用电视机来控制遥控器。
半双工：通信的时候，数据可以双向传输，但是同一时间只能有一台服务器在发送数据，当 A 给 B 发送数据的时候，那么 B 就不能给 A 发送数据，必须等到 A 发送结束之后，B 才能给 A 发送数据。比如说对讲机。
全双工：通信的时候，数据可以双向传输，并且可以同时传输。比如说我们打电话或者用通信软件进行语音和视频通话等。

半双工协议让 MySQL 通信简单快速，但是也在一定程度上限制了 MySQL 的性能，因为一旦从一端开始发送数据，另一端必须要接收完全部数据才能做出响应。所以说我们批量插入的时候尽量拆分成多次插入而不要一次插入太大数据，同样的查询语句最好也带上 limit 限制条数，避免一次返回过多数据。

二、缓存在 MySQL 8.0 之后的版本已经取消了，这是因为 MySQL 的缓存使用条件非常苛刻，是通过一个大小写敏感的哈希值去匹配的，这样就是说一条查询语句哪怕只是有一个空格不一致，都会导致无法使用缓存。而且一旦表里面有一行数据变动了，那么关于这种表的所有缓存都会失效，所以一般我们都是不建议使用缓存。

三、这一步主要的工作首先就是检查 sql 语句的语法对不对，比如：select name from lanqiao2 where id=1，就会被打散成 select，name，from，lanqiao2，where，id，=，1 这 8 个字符，并且能识别出关键字和非关键字，然后根据 sql 语句生成一个数据结构，也叫做解析树（select_lex）。

接下来是检查表名，列名以及其他一些信息等是不是真实存在的，这就是预处理的一个过程，预处理就是做一个表名和字段名等相关信息合法性的检测。

四、查询优化器的目的就是根据解析树生成不同的执行计划（Execution Plan），然后选择一种最优的执行计划，MySQL 里面使用的是基于开销（cost）的优化器，哪种执行计划开销最小，就选择哪种。这个结果是通过一系列复杂的运算得到的，包括每个表或者索引的页面个数，索引的基数，索引和数据行的长度，索引分布的情况。而且优化器在评估成本的时候，不会考虑任何缓存的作用，而是假设读取任何数据都需要经过一次 IO 操作。

五、存储引擎查询，当 Server 层得到了一条 sql 语句的执行计划后，这时候就会去调用存储引擎层对应的 API 来执行查询了。因为 MySQL 的存储引擎是插件式的，所以每种存储引擎都会对 Server 提供了一些对应的 API 进行调用。

六、返回结果，将查询出得到的结果返回 Server 层，如果有且开启了缓存，Server 层返回数据的同时还会写入缓存。MySQL 将查询结果返回是一个增量的逐步返回过程。例如：当我们处理完所有查询逻辑并开始执行查询并且生成第一条结果数据的时候，MySQL 就可以开始逐步的向客户端传输数据了。这么做的好处是服务端无需存储太多结果，从而减少内存消耗。