MySQL

一、InnoDB和MyISAM有什么区别？

InnoDB和MyISAM是MySQL中比较常用的两个执行引擎，MySQL在5.5之前版本默认存储引擎是MyISAM，5.5之后版本默认是InnoDB，MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用。

它们主要有以下区别：

1、InnoDB支持事务，MyISAM不支持。

2、InnoDB是聚集索引，MyISAM是非聚集索引。MyISAM是采用了一种索引和数据分离的存储方式，InnoDB的聚簇索引中索引和数据在一起。

3、InnoDB支持外键，MyISAM不支持。

4、InnDB最小的锁粒度是行锁，MyISAM是表锁。

5、InnoDB不支持FULLTEXT类型的索引（5.6之前不支持全文索引）。

 

二、char和varchar的区别？

char和varchar都用于在数据库中存储字符串的数据类型。它们之间的主要区别在于存储空间的使用方式：

char是一种定长的数据类型，它的长度固定且存储时会字段在结尾添加空格来将字符串填满指定的长度。char的长度范围是0~255。

varchar是一种可变长度的数据类型，它只会存储实际的字符串内容，不会填充空格。因此，在存储短字符串时，varchar可以节省空间。varchar的长度范围是0~65535。

优缺点及适应场景

对于字段值经常改变的数据类型来说，char比varchar也更有优势，因为char的长度固定，不会产生碎片。

varchar的优点是变长的字符串类型，兼容性更好；但是同时也会带来一些问题，如使用varchar可能会产生内存碎片、varchar会额外需要1到2个字节存储长度信息、以及update语句可能会导致页分裂。

适用：存储产品描述（可变长度）、存储用户地址、存储用户名称。

char的优点是定长的字符串类型，减少内存碎片，并且无需额外的磁盘空间去存储长度信息。但是它的缺点是会删除列末尾的空格信息。

适用：身份证号，订单号，国家编码等。

 

三、为什么大厂不建议使用多表join？

之所以不建议使用join查询，最主要的原因就是join的效率比较低。

MySQL是使用了嵌套循环的方式来实现关联查询的，就是要通过两层循环，用一张表做外循环，第二张表做内循环，外循环每一条记录跟内循环中的记录做比较，符合条件就输出。如果有两张表的话，复杂度最高是 O(n^2)，三张表则是 O(n^3) ，随着表越来越多，表中的数据量越多，join的效率就会呈指数级下降。

join

在MySQL中，可以使用join在两个或者多个表中进行联合查询，join有三种，分别是inner join、left join、right join。

inner join（内连接）：获取两个表中字段匹配关系。

取两个表的交集部分

left join（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。

取两个表的交集 + 左表中的数据

right join（右连接）：与left join相反，获取右表所有记录，即使左表没有对应匹配的记录。

取两个表的交集 + 右表中的数据

 

四、说一说MySQL一条SQL语句的执行过程？

如：select * from users where age = '18' and name = 'jiege' ；

执行过程如下图：

① 使用连接器，通过客户端 / 服务器通信协议与MySQL建立连接。并查询是否有权限。

② MySQL 8.0之前检查是否有缓存，开启了 Query Cache 且命中完全相同的SQL语句，则将查询结果直接返回给客户端。

③ 由解析器进行语法分析和语义分析，并生成解析树。如查询是 select，表明是 users、条件是 age = '18'  and name = 'jiege' ，预处理器则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表或数据列是否存在等。

④ 由优化器生成执行计划。根据索引看看是否可以优化。

⑤ 执行器来执行SQL语句，这里具体的执行会操作MySQL的存储引擎来执行SQL语句，根据存储引擎类型，得到查询结果。若开启了Query Cache ，则缓存，否则直接返回。

 

五、什么是数据库事务？

数据库事务是访问并可能操作各种数据项的一个数据库操作操作序列，这些操作要么全部执行，要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束直接执行的全部数据库操作组成。

事务应该具有4个属性：原子性，一致性，隔离性，持久性。称为 ACID。

① 原子性：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。

② 一致性：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。

③ 隔离性：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。

④ 持久性：一个事务一旦提交，它对数据库的修改一个永久保存在数据库中。

 

六、什么是脏读、幻读、不可重复读？

脏读：读到了其他事务还没有提交的数据。

不可重复读：对某数据进行读取过程中，有其他事务对数据进行了修改，导致第二次读取的结果不同。

幻读：事务在做范围查询过程中，有另外一个事务对范围内新增了记录，导致范围查询的结果条数不一致。

 

七、MySQL中的事务隔离级别？

未提交读（Read uncommitted）：最低的隔离级别。在这种隔离级别下，一个事务可以读到另外一个事务未提交的数据，会存在脏读、不可重复读、幻读。

提交读（Read committed）：在一个事务修改数据过程中，如果事务还没提交，其他事务不能读该数据。所以这种隔离级别可以避免脏读。

可重复读（Repeatable Read）：可以避免脏读、不可重复读，但无法彻底解决幻读。

可串行化（Serializable）：最高的隔离级别，可以解决幻读。

 

八、InnoDB如何解决脏读、不可重复读、幻读的？

在InnoDB中，通过MVCC解决脏读和不可重复读，通过MVCC + 间隙锁解决幻读。

① 脏读的解决。当事务在 “读已提交” 隔离级别下执行读取操作时，InnoDB获取当前最新的全局事务id，这个id表示在当前时刻所有已提交事务的最新状态。InnoDB会检查每个数据行的版本，如果该版本是由一个小于或等于当前事务id的事务修改的，并且该事务已经提交，则这个版本是可见的。这保证了事务只能看到在它开始之前已经提交的数据版本。

② 不可重复读的解决。在RR隔离级别下，InnoDB使用MVCC来解决不可重复读的问题。当我们使用快照进行数据读取的时候，只会在第一次读取的时候生成一个Read View，后续的所有快照读都有用的同一个快照，所以就不会发生不可重复读的问题了。

③ 幻读的解决。InnoDB的RR 级别最中，基于MVCC + 间隙锁，在某种程度上可以避免幻读的发生，但没办法完全避免，当一个事务中发生当前读的时候，会导致幻读的发生。

 

九、InnoDB的 RR 到底有没有解决幻读？

InnoDB中的 RR 级别通过间隙锁 + MVCC 解决了大部分的幻读，但并不是所有的幻读都能解决，想要彻底解决幻读，需要使用Serializable 的隔离级别。

RR中，通过间隙锁解决了部分当前读的幻读问题，通过间隙锁将记录之间的间隙锁住，避免新的数据插入。

RR中，通过MVCC机制，解决了快照读的幻读问题，RR中的快照读只有第一次会进行数据查询，后面都是直接读取快照，所以不会发生幻读。

但是，如果两个事务，事务1先进行快照读，然后事务2插入了一条记录并提交，再在事务1中进行update，新插入的这条记录是可以更新出成功的，这就是发生了幻读。

还有一种场景，如果两个事务，事务1先进行快照读，然后事务2插入了一条记录并提交，在事务1中进行了当前读之后，再进行快照读也会发生幻读。

 

MVCC解决幻读

MVCC 即多版本并发控制，和数据库锁一样，它也是一种并发控制的解决方案。主要用来解决读-写并发的情况。

在MVCC中，有快照读和当前读。

快照读：读的是快照生成的那一刻的数据，像常用的普通的select语句在不加锁的情况下就是快照读。

当前读：当前读就是读取最新的数据，所以，加锁的select，或者对数据进行增删改都会进行当前读。

快照读是MVCC实现的基础，当前读是悲观锁实现的基础。

在RC中，每次读取都会重新生成一个快照，总是读取行的最新版本。

在RR中，快照会在事务中第一次select语句执行时生成，只有在本地事务对数据进行更改才会更新快照。

也就是说，在RR下，一个事务的多次查询，是不会查询到其他事务中的变更内容的，所以也就是可以解决幻读的。

间隙锁与幻读

当前读的幻读问题的解决。在RR的级别下，当我们使用select...FOR UODATE的时候，会进行加锁，不仅仅会对行记录进行加锁，还会对记录之间的间隙进行加锁，这就叫间隙锁。

因为记录之间的间隙被锁住了，所以事务2的插入操作九被阻塞了，一直到事务1把锁释放掉它才能执行成功。

因为事务2无法插入数据成功，所以也就不会存储幻读的现象。所以，在RR级别中，通过加间隙锁的方式，避免了幻读现象的发生。

解决不了的幻读

如果在一个事务中发生了当前读，并且在另一个事务插入数据之前没来得及加锁的话，还是会出现幻读。

 上面两次查询结果完全不一样，没加锁的情况下，就是快照读，读到的数据和第一次查询是一样的，就不会发生幻读。但是第二次查询加了锁，就是当前读，那么读取到的数据就是有其他事务提交的数据了，就发生了幻读。

 上面的例子和第一个类型，因为MVCC只能解决快照读中的幻读问题，而对于当前读（select for update、update、delete等操作）还是会发生幻读的现象。即，在同一个事务里面，如果既有快照读，又有当前读，那就会产生幻读。

UPDATE语句也是一种当前读，所以它会读取到其他事务提交的结果。

如何避免幻读

如果要彻底解决幻读的问题，在InnoDB中只能使用Serializable这种隔离级别。

如果想在一定程度上避免幻读，可以使用RR，但是RC、RU肯定是不行的。

 

十、如何理解MVCC?

MVCC --- 多版本并发控制。

在数据库种，对数据的操作有2种，分别是读和写，而在并发场景种，有三种情况：

① 读-读并发

② 读-写并发

③ 写-写并发

在没有写的情况下，读-读并发是不会出现问题的，而在写-写并发这种情况常用的就是通过加锁的方式实现。那么，读-写并发就可以通过MVCC机制解决。

UndoLog

undo log是MySQL比较重要的事务日志之一，顾名思义，undo log是一种用于回退的日志，在事务没提交之前，MySQL会先记录更新前的数据在undo log日志文件种，当事务回滚回执数据库崩溃时，可以利用undo log来进行回退。

这里面undo log中的 “”更新前的数据” 就是所谓的快照。所以，undo log是实现MVCC的重要手段。

但是，一条记录在同一时刻可能会有多个事务在执行，undo log会有一条记录的多个快照，如果在这一时刻发生select 要进行快照读的时候，要读那个快照呢？

行记录的隐式字段

在数据库的每行记录中，除了保存我们自己定义的一些字段以外，还有一些重要的隐士字段：

① db_row_id：隐藏主键，如果我们没有给这个表创建主键，那么会以这个字段来创建聚簇索引。

② db_trx_id：对这条记录做了最新一次修改的事务id。

③ db_roll_ptr：回滚指针，指向这条记录的上一个版本，其实它指向的就是Undo Log中的上一个版本的快照的地址。

因为每一次记录变更之前都会存储一份快照到undo log中，那么这几个隐式字段也会跟着记录一起保存到undo log中，就这样每一个快照中都有一个db_trx_id字段，表示对这个记录做了最新一次的修改的事务id，以及一个db_roll_ptr字段，指向上一个快照的地址。

这样就形成一个快照链：

 但是有了undo log又有隐式字段，但还是不知道具体应该读取哪个快照，该怎么办？

Read View

Read View主要帮忙解决可见性的问题，即它会告诉我们本次事务应该看到哪个快照，不应该看到哪个快照。

在Read View中有鸡哥重要的属性：

① trx_ids，系统当前未提交的事务ID的列表。

② low_limit_id，应该分配给下一个事务的id值。

③ up_limit_id，未提交的事务最小的事务id。

④ creator_trx_id，创建这个Read View的事务id。

每次开启一个事务，都会从数据库中获取一个事务id，这个事务id是自增长的，通过id的大小，我们就可以判断事务额时间顺序。

具体判断规则：

其实比较简单，那就是事务id大的事务应该能看到事务id小的事务的变更结果，反之则不能！

 所以，在InnoDB中，MVCC是通过Read View  + Undo Log来实现的，undo log保存了历史快照，而Read View用来判断具体哪一个快照是可见的。

 

十一、InnoDB中的索引类型？

InnoDB存储引擎支持两种常见的索引数据结构：B+树索引、Hash索引，其中B+树索引是目前关系型数据库中最常见、最有效的索引。

数据库的B+树索引分为聚簇索引和非聚簇索引。聚簇索引就是按照每张表的主键构造一个B+树，B+树的叶子节点记录着表中一行记录的所有值。只要找到这个叶子节点也就得到了这条记录的所有值。非聚簇索引的叶子节点不包含行记录的所有值，只包含索引值和主键的值。

根据索引的唯一性，可以把索引分为唯一索引和普通索引。唯一索引要求索引的值必须唯一，不能重复。

另外，在MySQL 5.6 中还增加了全文索引，5.7 之后还通过使用ngram插件支持中文。

B+树索引和Hash索引

B+树特点：

① B+树是一颗平衡树，每个叶子节点到根节点的路径长度相同，查找效率较高。

② B+树的所有关键字都在叶子节点上，因此范围查询时只需要遍历一遍叶子节点即可。

③ B+树的叶子节点都按照关键字的大小顺序存放，因此可以快速的支持按照关键字大小进行排序。

④ B+树的非叶子节点不存储实际数据，因此可以存储更多的索引数据。

⑤ B+树的非叶子节点使用指针连接叶子节点，因此可以快速的支持范围查询和倒叙查询。

⑥ B+树的叶子节点之间通过双向链表链接，方便支持范围查询。

 所以，使用B+树实现索引，就有以下优点：

① 支持范围查询，B+树在进行范围查询时，只需要从根节点一直遍历到叶子节点，因为数据都存储在叶子节点上，而叶子节点又是双向链表连接，可以很方便的进行范围查找。

② 支持排序，B+树的叶子节点按照关键字顺序存储，可以快速支持排序查找，提高排序效率。

③ 存储更多的索引数据，因为它的非叶子节点只存储索引关键字，不存储实际数据，因此可以存储更多的索引数据。

④ 在节点分裂和合并时，IO操作少。B+树的叶子节点的大小是固定的，而且节点的大小一般都会设置为一页的大小，这就使得节点分裂和合并时，IO操作很少，只需读取和写入一页。

B+树索引和Hash索引有什么区别？

① B+树将索引列的值按照大小排序后存储，因此B+树适合于范围查询和排序操作；而哈希索引是将索引列的值通过哈希函数计算后得到一个桶的编号，然后将桶内的记录保存在一个链表或者树结构中。因此哈希索引更适合等值查询，但不适合范围查询和排序操作。

② B+树索引插入和删除数据时要调整索引结构，这个过程可能会涉及到页分裂和页合并等操作，因此B+树索引的维护成本比较高；而哈希索引在插入和删除数据时只需要计算哈希值并插入或者删除链表中的记录，因此哈希索引的维护成本相对比较低。

③ B+树索引在磁盘上是有序存储的，因此在进行区间查询时可以利用磁盘预读的优势提高查询效率；而哈希索引在磁盘上是无序存储的，因此在进行区间查询时可能会要随机访问磁盘，导致查询效率降低。

④ B+树索引在节点中存储多个键值对，因此可以充分利用磁盘块的空间，提高空间利用率；而哈希索引由于需要存储哈希值和指针，因此空间利用率相对较低。

 

聚簇索引和非聚簇索引

聚簇索引：就是将数据和索引放到了一起，找到了索引也就找到了数据。对于聚簇索引来说，它的非叶子节点存储的是索引字段的值，而它的叶子节点存储的是这条记录的整行数据。

在InnoDB中，聚簇索引指的是按照每张表的主键构建的一种索引方式，它是将表数据按照主键的顺序存储在磁盘上的一种方式。这种索引方式保证了行的物理存储顺序和主键的逻辑顺序相同，因此查询聚簇索引的速度非常快。

非聚簇索引：就是将数据和索引分开存储，叶子节点包含索引字段值及指向数据页数据行的逻辑指针。

在InnoDB中，非聚簇索引是根据非主键字段创建的索引，也就是通常说的二级索引。它不影响表中数据的物理存储顺序，而是单独创建一张索引表，用于存储索引列和对应行的指针。

在InnoDB中，主键索引就是聚簇索引，而非主键索引，就是非聚簇索引。

** 对于聚簇索引来说，它的非叶子节点上存储的是索引值，而它的叶子节点存储的是整行记录。

** 对于非聚簇索引来说，它的非叶子节点存储的都是索引值，而它的非叶子节点存储的是主键的值和索引值。

所以，通过非聚簇索引的查询，需要进行一次回表，就是先查到主键id，在通过id查询所需字段。

回表是什么？怎么减少回表的次数？

当我们根据非聚簇所以查询的时候，会先通过非聚簇索引查到主键的值，之后，还需要通过主键的值再进行一次查询才能得到我们要查询的数据。这个过程就是回表。

所以，在InnoDB中，使用主键索引查询的时候，是效率更高的，因为这个过程不需要回表。另外依赖覆盖索引、索引下推等技术，我们也可以通过优化索引结构以及SQL语句减少回表的次数。

什么是索引覆盖、索引下推？

覆盖索引：覆盖索引指的是一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了覆盖索引。

当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样就避免了查到索引之后再进行回表操作，减少I/O提高效率。

如表convering_index_sample中有一个普通索引 idx_key1_key2（key1，key2）.

当我们通过sql语句：

 这样就可以通过覆盖索引查询，无需回表。

但是以下sql，因为不符合最左前缀匹配，虽然是覆盖索引，但是页无法用到索引（会扫描索引树）：

 但是如果sql中查询的信息不包含在联合索引中，那么就不会走覆盖索引。如：

 索引下推

索引下推是MySQL 5.6引入的一种优化技术，默认开启，使用SET optimizer_switch = ' index_condition_pushdown = off ' ；可以将其关闭。

官方给的例子和解释：

people表中（zipcode，lastname。firstname） 构成一个索引。

select * from people where zipcode = ' 95054 ' and lastname like ' %etrunia% ' and address like  ' Main Street% '  ；

如果没有使用索引下推技术，则MySQL会通过 zipcode = ' 95054 ' 从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于 lastname like ' %etrunia% ' 和 address like  'Main Street% '

来判断数据是否符合条件。

如果使用了索引下推技术，则MySQL首先会返回符合zipcode = ' 95054 ' 的索引，然后根据 lastname like ' %etrunia% ' 来判断索引是否符合条件。

如何符合条件，则根据该索引来定位对应的数据，如果不符合，直接reject掉。有了索引下推优化，可以在like条件查询的情况下，减少回表的次数。

当一条SQL使用到索引下推时，explain的执行计划中的extra字段中内容为 ：Using index condition

索引下推不只like

在我看来，我认为索引下推其实是解决索引失效带来的效率低的问题的一种手段。

所以当联合索引中，某个非前导列因为索引失效而要进行扫表并回表时，就可以进行索引下推进行优化了。

如，有a，b联合索引，类型都是varchar，以下SQL也可以用到索引下推：

 b字段因为类型不匹配导致索引失效，但是通过索引下推优化可以减少回表的次数。

 

唯一性索引

MySQL是如何保证索引的唯一性的？

MySQL通常使用B+树作为唯一性索引的数据结构。这种结构允许高效的数据检索和插入操作。当插入一个新行或更新现有的索引列时，MySQL首先在索引中检查是否已经存在相同的键值。如果发现索引列的新值已经存在唯一性索引中，MySQL将阻止该插入或更新操作，并返回一个错误。

在支持事务的存储引擎（InnoDB）中，事务机制和锁定协议帮助维护索引的唯一性。当一个事务正在修改索引列时，其他事务对相同的键值的修改会被适当地阻塞，直到第一个事务提交或回滚，确保了数据的一致性和唯一性。

并且，在实际写入数据到磁盘之前，MySQL也会执行约束检查，确保不会违反唯一性约束。

唯一索引允许NULL值吗？

唯一索引在MySQL中可以允许 NULL 值的，但是这些 NULL的表现都是未知的，未知就是它们不相等，但是也不能说它们不等。

并且，InnoDB存储引擎在MySQL中支持唯一索引中有多个NULL值。这是因为在MySQL中，NULL被认为是 ”未知“ 的，每个NULL值都被视为互不相同。因此，即使一个列被定义了唯一索引，它也可以包含多个NULL值。

唯一性索引查询更快吗？

唯一性索引查询通常会比非唯一性索引查询更快，因为唯一性索引能够快速定位到唯一的记录，而非唯一性索引则需要扫描整个索引并匹配符合条件的记录。

唯一性索引的缺点？

首先，唯一性索引需要保证索引列的唯一性，因此在插入数据时需要检查是否存在相同的索引值，这回对插入性能产生一定的影响。

如果需要更新唯一性索引列的值，需要先删除旧纪录，再插入新记录，这会对更新操作的成本产生影响。

 

唯一索引和主键索引的区别？

唯一索引和主键索引都需要保证唯一性，但是还是有很对区别。

唯一性：主键索引其实是一种特殊的唯一索引，它们都具有唯一性，也就是在一张表中，不能有两行相同的主键，在同一张表中，主键索引字段的所有值都是唯一的。

是否为空：主键索引是不能为NULL的，而唯一索引是可以为NULL的。

是否可以有多个：主键索引在一张表中之能有一个，而唯一性索引通常是非聚簇索引。

索引结构：在InnoDB中，主键索引就是聚簇索引，而唯一索引是非聚簇索引。

为什么说唯一索引通常是非聚簇索引呢？

因为在有的时候，我们可能没有创建主键，那么MySQL会默认选择一个唯一的非空索引作为聚簇索引。所以，唯一索引也可能被选为聚簇索引。

是否回表：基于主键索引的查询一定不需要回表，基于唯一索引的查询，通常是需要回表的。

外键：主键可以被其他表引用为外键，而唯一索引是不可以的。

 

十二、设计索引的时候有哪些原则？

1、考虑查询的频率和效率：在决定创建索引之前，需要分析查询频率和效率。对于频繁查询的列，可以创建索引来加速查询，但对于不经常查询或者数据量较少的列，可以不创建索引。

2、选择合适的索引类型：MySQL提供了多种索引类型，如B+树、哈希索引和全文索引等。不同类型的索引适用于不同的查询操作，是需要根据实际情况选择适合的索引类型。

3、考虑区分度：尽量不要选择区分度不高的字段作为索引，比如性别，但也不绝对，对于一些数据倾斜比较严重字段，虽然区分度不高，但是如果有索引，查询占比少的数据时效率也会提升。

4、考虑联合索引：联合索引是将多个列组合在一起创建的索引。当多个列一起被频繁查询时，可以考虑创建联合索引。

5、考虑索引覆盖：联合索引可以通过索引覆盖而避免回表查询，可以大大提升效率，对于频繁的查询，可以考虑将select 后面的字段和where 后面的条件放在一起创建联合索引。

 

十三、什么是最左前缀匹配，为什么要遵守？

在MySQL中，最左前缀匹配是指在查询中利用索引的最左边的一部分来进行匹配。当执行程序时，如果查询条件涉及到了联合索引的前几个列，MySQL就可以利用这个复合索引来进行匹配。

单个字段的索引也需要遵循最左前缀匹配，及有一个字段的值为 ' abc ' 时，当我们使用like进行模糊匹配时，like  " abc % " 是可以走索引的，而 " % abc "是不行的。

为什么要遵循最左前缀匹配？

因为索引底层是一个B+树，如果是联合索引的话，在构造B+数的时候，会先按照左边的key进行排序，左边的key相同时再依次按照右边的key排序。

所以，在通过索引查询的时候，也需要遵循最左前缀匹配的原则，也就是需要从联合索引的最左边开始进行匹配，这时候就要求查询语句的where条件中，包含最左边的索引的值。

MySQL索引一定遵循最左前缀匹配吗？

MySQL一定是遵循最左前缀匹配的，这句话以前是正确的，没有任何毛病。但是在MySQL 8.0 中，就不一定的，因为MySQL8.0.13 中引入了索引跳跃扫描

 

十四、binlog、redolog、undolog区别？

binlog主要用来对数据库进行数据备份、崩溃恢复和数据复制等操作，redo log和undo log主要用于事务管理，记录的是数据修改操作和回滚操作。redolog用来恢复，undolog用来回滚。

在MySQL中，redolog和undolog只适用于InnoDB存储引擎，因为要支持事务。而不适用于MyISAM等其他存储引擎。而binlog则适用于所有存储引擎。

binlog是MySQL用于记录数据库中的所有DDL语句和DML语句的一种二进制日志。它记录了所有对数据库结构和数据的修改操作，如INSERT、UPDATE和DELETE等。binlog主要用来对数据库进行数据备份、灾难恢复和数据复制等操作。binlog的格式分为基于语句的格式和基于行的格式。

Redo Log是MySQL用于实现崩溃恢复和数据一致性的一种机制。在事务进行过程中，MySQL会将事务做了什么改动记录到Redo Log中。当系统崩溃或者发生异常情况时，MySQL会利用Redo Log中的记录信息来进行恢复操作，将事务所作的修改持久化到磁盘中。

Undo Log则用于在事务回滚或系统崩溃时撤销事务做的修改。当一个事务执行过程中，MySQL会将事务修改前的数据记录到Undo Log中。如果事务需要回滚，则会从Undo Log中找到相应的记录来撤销事务所做的修改。另外，Undo Log还支持 MVCC 机制，用于在并发实现执行时提供一定的隔离性。

 undolog和redolog的区别？

目的：redolog的目的是为了保证事务的持久性，主要用于崩了恢复，而undolog的目的是为了保证事务的原子性和一致性，主要用于事务回滚。

记录内容：redolog记录了事务的所有数据更改（还有具体操作）。而undolog记录的是事务执行前的内容。

 

十五、数据库死锁如何解决？

数据库死锁问题是指多个并发事务中，彼此出现了相互等待的情况，导致有事务无法执行，称为死锁。

数据库的死锁发生通常由如下原因导致：

1、资源竞争：多个事务试图同时访问相同的资源，如数据库表、行、页或者锁。但是它们请求资源的顺序不同，导致互相等待。

2、未释放资源：事务在使用完资源后未及时释放资源，导致其他事务无法获得所需要得资源。这可能由于程序中得错误或异常情况引起的。

3、不同事务的执行速度不同：如果一个事务在获取资源后执行速度很慢，而其他事务需要等待该事务释放资源，那么可能会导致其他事务超时，从而发生死锁。

4、操作的数据量过大：在持有锁的同时，又请求获取更多的锁，导致互相等待。

解决（避免）死锁的方法：
1、减少锁的数量：比如使用RC来替代执行RR来避免因为 gap锁和next-key锁而带来的死锁情况。

2、减少锁的时长：加快事务的执行速度，降低执行时间，也能减少死锁发生的概率。

3、固定顺序访问数据：事务在访问同一张表时，应该以相同的顺序获取锁，这样可以避免死锁的发生。

4、减少操作的数据量：尽量减少事务操作的数据量，尽量减少锁的持有时间，这样可以避免降低死锁发生的机率。

 

十六、索引失效的问题如何排查？

MySQL的索引失效是一个比较常见的问题，这种情况一般会在慢SQL发生时需要考虑，考虑是否存在索引失效的问题。

在排查索引失效的时候，第一步一定是找到要分析的SQL语句，然后通过explain查看它的执行计划。主要关注type、key和extra这几个字段。

我们需要通过 key + type + extra 来判断一条SQL是否用到了索引。如果有用到索引，是走了覆盖索引？还是索引下推、还是扫描了整个索引树？或者用到了索引跳跃扫描等等。

一般来说，比较理想的走索引有以下几种情况：

1、key一定要有值，不能是NULL。

2、type应该是ref、eq_ref、range、const等这几个。

3、extra，如果是NULL，或者using index、using index condition都是可以的。

如果通过执行计划之后，发现一条SQL没有走索引，比如type = ALL，key = NULL，extra = Using where

那么就要进一步分析没有走索引的原因了。需要知道的是，到底要不要走索引，走哪个索引，是MySQL的优化器决定的，它会根据预估的成本来做一个决定。

有以下几种情况可能会导致没有走索引：

1、没有正确的创建索引：当查询语句中的where条件中的字段，没有创建索引，或者不符合最左前缀匹配的话，就是没有正确的创建索引。

2、索引区分度不高：如果索引的区分度不高，那么可能会不走索引，因为这种情况走索引的效率并不高。

3、表太小：当表中的数据很小，优化器认为扫全表的成本也不高的时候，也可能不走索引。

4、查询语句中，索引字段因为使用了函数、类型不一致等导致了索引的失效。

这个时候开始逐一分析：

1、如果没有正确的创建索引，那么就根据SQL语句，创建合适的索引。如果没有遵守最左前缀，就调整以下索引或者SQL语句。

2、索引区分度不高的话，可以考虑换一个索引字段。

3、表态小这种情况也没啥优化的必要了，用不用索引可能影响都不大。

4、排查具体失效原因，然后针对性的调整SQL语句就行了。

 

十七、索引失效的情况有哪些？

现在有一张表：

 ① 索引列参与计算

 但是如果是如下形式的计算还是可以走索引的：

 ② 对索引列进行函数操作

 ③ OR

使用OR的话，并且两边存在 < 或者 > 的时候，就是索引失效，如：

但是如果OR 两边都是 = 判断，并且两个字段都有索引，那么也是可以走索引的，如：

④ like操作

⑤ 隐式类型比较

以上情况是name 是一个 varchar类型，但是用int类型查询，这种会导致索引失效。

 以下这种特例，如果字段为int类型，而条件中添加了单引号或者双引号，则MySQL会将参数转化为int类型，这种情况也走索引。

⑥ 不等式比较

 != 这种情况，索引失效与索引的选择性、数据分布情况等因素有关，不能简单地说是由于查询条件中包含 != 这个操作符而导致索引失效的。

⑦ is not null

⑧ order by

当进行order by的时候，如果数据量很小，数据库可能会直接在内存中进行排序，而不使用索引。

⑨ in

使用in的时候，有可能走索引，也有可能不走，一般在in 中的值比较少的时候会走索引，但是如果选项比较多的时候，可能会不走索引。

 

十八、如何进行SQL调优？

一般来说，SQL调优需要从以下几个方面和步骤入手。

首先需要定位到具体的SQL语句，这个可以通过各类监控平台或者工具来失效，通过定位到SQL语句之后，就知道具体是哪张表、哪个SQL慢了。

可能有以下几种原因：

1、索引失效

2、多表join

3、查询字段太多

4、表中数据量太大

5、索引区分度不高

6、数据库连接数不够

7、数据库的表结构不合理

8、数据库IO或者CPU比较高

9、数据库参数不合理

10、事务比较长

11、锁竞争导致的等待

所以，一次完整的SQL调优，一般需要考虑以上几个因素，一般会涉及到其中的一个或者多个问题，然后进行逐个优化。

① 索引失效的问题一般先通过执行计划分析是否走了索引，以及所走的索引是否符合预期，如果因为索引设计的不合理、或者索引失效导致的，那么就可以修改索引或者修改SQL语句，或者强制执行使用某个索引。具体可以参考 十六 ：索引失效的问题如何排查？

② 多表join也是SQL执行的比较慢的一个原因，详细请看：三、为什么大厂不建议用多表join？

③ 查询字段太多，这个有的时候因为我们错误的用到了 select * 导致的，一般来说，查询字段小于1000个，都不是特别大的问题，除非字段真的非常非常多，这时候可以采用两种办法解决。第一个就是不要查询那么你不关心的字段，只查询少部分字段。第二个就是做分表，垂直分表，把数据拆分到多张表中，但这么做可能也会带来需要多表join的问题，所以拆分的时候也需要考虑冗余。

④ 表中数据量太大，一般来说，单表超过1000万，会导致查询效率变低，即使使用索引可能也会比较慢，所以如果表中的数据量太大的话，这个时候可能通过建索引并不一定能完全解决了。具体的方案有几种：

1、数据归档，把历史数据移出去，比如只保留最近半年的数据，半年前的数据做归档。

2、分库分表、分区。把数据拆分开，分散到多个地方去。、

3、使用第三方的数据库，比如把数据同步到支持大量查询的分布式数据库中，如oceanbase、tidb，或者搜搜引擎中，如ES等。

⑤ 索引区分度不高，这个其实也和索引不合理有关，但是其实到底快不快，用不到索引，并不是区分度高不高导致，其实还是索引扫描的行数的成本导致。所以，有的时候不能认为区分度不高就一定会效率低，或者一定就不适合创建索引。

⑥ 数据库连接数不够，这个要具体分析，第一个就是业务量太大了，单库确实扛不住了，那就选择分库。

第二个就是可能存在一些慢SQL，或者长事务导致的，慢SQL占用数据库连接，数据库连接数不够，其他的查询就会阻塞，就更慢。

⑦ 数据库的表结构不合理，这个也是一个关键原因，有的时候比如某个字段中存了很长的内容，或者没有做合理的冗余需要多表关联查询等待。解决思路就是重构，或者分表。

⑧ 数据库IO 或者CPU比较高，这种问题也比较常见，当数据库整体IO或者CPU飙高的时候，查询速度就有可能下降，所以需要分析背后的原因及解决思路，

⑨ 存在长事务，这个和慢SQL同理，都是占用了数据库连接，导致其他请求要等待。

⑩ 锁竞争导致的等待，当有大并发量争抢共享资源的时候，就会导致锁等待，这个过程就会拉长耗时，导致SQL变慢。也可以参考CPU被打满的问题。

数据库参数不合理

这个也是经常遇到的，针对具体的业务场景，做一些适当的参数调整，有时候也能大大的提升SQL的效率。比如调整内存大小、缓存大小、线程池大小等。

首先可以使用show variables like " innodb%" ；命令来查看当前的innoDB的参数设置，这些参数包括缓冲池大小、刷新间隔、日志大小等。

innodb_buffer_pool_size：缓冲池的大小是InnoDB存储引擎的核心参数之一，它控制着InnoDB存储引擎使用的内存大小。通常，我们可以将该参数设置为系统可用内存的70%-80%。例如，如果系统有8GB内存可用，可用将innodb_buffer_pool_size设置为6GB。在MySQL中，可以使用以下命令进行设置：

innodb_read_io_threads 和 innodb_write_io_threads：这两个参数控制着InnoDB存储引擎使用的I/O线程数量。通常情况下，我们可以将这两个参数设置为 CPU 核心数的一半，可以使用以下命令进行设置：

innodb_log_file_size：这个参数控制着事务日志文件的大小。这个值得默认为5M，是远远不够的。在MySQL中，可以使用以下命令进行设置：

一般在设置这个字段的时候，都是先需要进行数据采样，看一下一般业务高峰期，2小时左右可以写入多少日志，然后把这个量作为你的日志文件的大小即可。如过简单点的话，一般设置为1GB左右，或者系统内存的1/4即可。

 

十九、区分度不高的字段建索引一定没用吗？

不一定的

假如一张表中有一个性别字段，它的区分度肯定是不高的，只有男和女两种。一般情况下，如果表中数据量很大的话，用这个字段查询会导致没办法过滤掉很多数，就可能没办法发挥索引的效果。

但是，如果有一种特殊的情况，如男女比例是95：5，那么，这个时候，如果我用 "女" 作为性别的查询条件的话，还是可以走索引，并且有很大的性能提升，原因就是因为它可以过滤掉大部分数据。走索引可以大大提升效率。

这种一般在任务表中比较多，比如任务表中有状态，两种情况：INIT 和 SUCCESS，大多数情况下，任务都是SUCCESS的，只有一少部分是INIT，这个时候就可以给这个字段加索引。这样当我们扫描任务表执行任务的时候，还是可以大大提升查询效率的。、

 

二十、慢SQL的问题如何排查？

慢SQL是指数据库中查询时间超过指定阈值的SQL，这个阈值根据不同的业务来说一般是不一样的，在我们内部，这个阈值是1s，也就是说，如果一条SQL执行超过1s，就认为是一个慢SQL。

慢SQL的问题排查一般分为几个步骤：

发现问题

一般来说，慢SQL的问题，是比较容易发现的。首先如果有很成熟的监控体系的话，会把慢SQL进行统计，然后以报警的形式推送出来。

数据库自己也是可以配置慢SQL日志的，配置方式如下：

1、找到MySQL的配置文件 my.cnf （或my.ini ，取决于操作系统），通常位于MySQL的安装目录下的 etc 或 conf文件夹。

2、在配置文件中启用慢SQL查询日志：找到或添加以下配置项，并取消注释（如果有注释），确保以下行存在或添加到配置文件中：

3、重启MySQL服务：保存配置文件并重新启动MySQL服务，使配置生效。配置生效后，如果有SQL执行时长超过 long_query_time 配置的时间阈值，就会打印慢SQL日志。

4、查询慢查询日志：使用文本编辑器打开慢查询日志文件，路径为在配置文件中指定的路径。如Linux下可以用以下命令：

 如果有慢SQL，内容如下：

定位问题

在如上的各种监控，报警以及日志中，我们就可以找到对应的慢SQL的具体SQL了，然后就可以进一步分析为什么这个SQL是慢SQL了，主要就是排查它慢在哪里。

一般来说，导致一个SQL慢的原因有以下几个：

1、没有索引

2、用错索引（没用遵守最左前缀，索引区分度不高）

3、查询字段太多

4、回表次数多

5、多表join

6、深度分页

7、... 其他 ....

详见：十八、如何进行SQL调优？

一般来说，大多数情况下，是可以通过执行计划分析出一条SQL慢的原因的，大部分来说，主要就是索引和join的问题了。

详细见：十六、索引失效的问题如何排查？

解决问题

在定位问题之后，就是解决问题了。

其实最难的不是解决问题，而是定位问题，因为一旦一个问题被定位到了，解决起来都比较容易。缺索引就加索引，join太多就拆分好了。

 

二十一、InnoDB的一次更新事务是怎么实现的？

一次InnoDB的update操作，涉及到BufferPool、BinLog、UndoLog、RedoLog以及物理磁盘，完整的一次操作如下：

1、在Buffer Pool中读取数据：当InnoDB需要更新一条记录时，首先会在Buffer Pool中查找该记录是否在内存中。如果没有在内存中，则从磁盘读取该页搭配Buffer Pool中。

2、记录UndoLog：在修改操作前，InnoDB会在UndoLog中记录修改前的数据。UndoLog是用来保证事务原子性和一致性的一种机制，用于发生事务回滚等情况时，将修改操作回滚到修改前的状态，以达到事务的原子性和一致性。UndoLog的写入最开始是写到内存中的，然后由一个后台线程定时刷新到磁盘中的。

3、在Buffer Pool中更新：当执行update语句时，InnoDB会先更新已经读取到的Buffer Pool中的数据，而不是直接写入磁盘。同时，InnoDB会将修改后的数据页状态设置为 " 脏页 " （Dirty Page）状态，表示该页已经被修改但尚未写入磁盘。

4、记录RedoLog Buffer：InnoDB在Buffer Pool中记录修改操作的同时，InnoDB会将修改操作写入redo log buffer中。

5、提交事务：在执行完所有修改操作后，事务被提交。在提交事务后，InnoDB会将Redo Log写入磁盘，以保证事务持久性。

6、写入磁盘：在提交过程后，InnoDB会将Buffer Pool中的脏页写入磁盘，以保证数据的持久性。但是这个写入过程并不是立即执行的，是有一个后台线程异步执行的，所以可能会延迟写入，总之就是MySQL会选择合适的时机把数据写入磁盘做持久化。

7、记录BinLog：在提交过程中，InnoDB会将事务提交的信息记录到BinLog中。BinLog是MySQL用来失效主从复制的一种机制，用于将主库上的事务同步到从库上。在BinLog中记录的信息包括：事务开始的时间、数据库名、表名、事务ID、SQL语句等。

需要注意的是，在binlog和redolog的写入过程中，其实是分成了2阶段的，通过2阶段提交的方式来保证唯一性的。

 

二十二、MySQL主从复制的过程？

MySQL的主从复制，是基于binlog实现的，主要过程是这样的：

1、从服务器在开启主从复制后，会创建2个线程：I/O线程和SQL线程。

2、从服务器的I/O线程，会尝试和主服务器建立连接，相对应的，主服务中也有一个binlog dump线程，是专门来和从服务器的I/O线程做交互的。

3、从服务器的I/O线程会告诉主服务的dump线程自己要从什么位置开始接受binlog。从服务器还有一个SQL线程，它会不断地读取它自己的relay log中的内容，把它解析成具体的操作，然后写入到自己的数据表中。

4、主服务器在更新过程中，将更新记录保存到自己的binlog中，根据不同的binlog格式，记录的内容可能不一样。

5、在dump线程检测到binlog变化时，会从指定位置开始读取内容，然后会被slave的I/O线程把它拉取过去。（注意：实际上是从服务器向主服务器拉的内容）

6、从服务器的I/O线程接收到通知事件后，会把内容保存在relay log中。

复制方式

异步复制：这是MySQL默认的复制方式，在异步复制的方式中主库在执行完事务操作以后，会立刻给客户端返回。它不需要关心从库是否完成该事务的执行。

这种方式会导致一个问题，那就是当主库出现故障时，主库虽然事务执行完了，但是可能还没来得及把数据同步给从库，就挂了。那么当从库升级为主库之后，它会丢失了这次事务的变更内容。

全同步复制：全同步复制的这个方式中，当主库执行完一个事务之后，它会等待所有的从库完成数据复制之后，才会给客户端反馈。

这种方式安全性可以保障，但是性能很差。如果从库比较多的话，会导致整个过程更加长。

半同步复制：半同步复制是介于全同步复制和异步复制之间的一种方案。它在执行完一个事务之后，也不会立即给客户端反馈，但是也不会等所有从库都完成事务，而是等其中一个从库完成接收到事件之后，在反馈给客户端。

在半同步复制这个方案中，会在事务提交的2阶段都完成之后，等待从库接收到binlog，然后再返回超成功。

 

二十三、介绍一下InnoDB的数据页，和B+树的关系是什么？

InnoDB的数据页是InnoDB存储引擎中用于存储数据的基本单位。它是磁盘上的一个连接区域，通常大小为16KB，也可以通过配置进行调整。16KB就意味着InnoDB的每次读写都是以16KB为单位的，一次从磁盘到内存的读取最小是16KB，一次从内存到磁盘的持久化也是最小16KB。

B+树的每个节点都对应着一个数据页，包括根节点、非叶子节点和叶子节点。

B+树通过节点之间的指针连接了不同层级的数据页，从而构建了一个有序的索引结构。

通过B+树的搜索过程，可以从根节点开始逐层遍历，最终到达叶子节点，找到所需的数据行。

所以，数据页是存储数据行的实际物理空间，以页为单位进行磁盘读写操作。B+树通过节点和指针的组织，构建了一个层次结构的索引，用于快速定位和访问数据行。

B+树的非叶子节点对应着数据页，其中存储着主键+指向子节点（即其他数据页）的指针。B+树的叶子节点包含实际的数据行，每个数据行存储在一个数据页中。

通过这种方式，InnoDB利用B+树和数据页的组合，实现了高效的数据存储和检索。B+树提供了快速的索引查找能力，而数据页提供了实际存储和管理数据行的机制。它们互相配合，使得InnoDB能够处理大规模数据的高效访问。

数据页的构成

一个数据页包含了7个部分，分别是文件头、页头、最小和最大记录、用户记录、空闲空间、页目录以及文件尾。

 

二十四、MySQL执行大事务会存在什么问题？

所谓大事务，一般是指事务中要执行的SQL很多，事务的时间比较长。

这样的事务，会带来很多问题。

1、占用数据库连接：这个很容易理解，SQL多了，执行的就会很慢，那么大的事务就会很长时间占用数据库连接，但是因为数据库连接是有限的，被长事务占用后，就会导致其他事务可能无法获取连接，导致应用的吞吐量下降，影响系统可用性。

2、难以回滚：由于大事务涉及的数据量较大，执行回滚操作可能会变得非常耗时。如果事务需要回滚或失败，可能需要花费很长时间才能完全回滚所有修改，这会对数据的可用性和性能造成负面影响。

3、锁竞争：大事务的话，写操作多了就可能要锁定许多数据。这可能导致其他并发事务在访问相同资源时遇到锁竞争，从而导致性能下降和延迟增加。长时间的锁定还可能导致其他事务的等待和阻塞。

4、日志空间占用：大事务会生成大量的日志，尤其是binlog，当单个事务最大允许使用的Binlog文件的大小超过了max_binlog_cache_size时，会导致报错：

Muit-statement transaction required more than ' max_binlog_cache_size ' bytes of storage ；increase this mysqld variable and try again

解决方案：

拆分，把一个大事务，拆成多个事务。把不需要在事务中的操作，如读操作，内存计算操作、IO操作、远程调用等，放到事务外处理。

 

二十五、MySQL怎么做热点数据高效更新

MySQL的热点数据更新问题，一直都是行业内的一个难题，对于秒杀场景来说至关重要。一旦处理不好，就可能会导致数据库被打垮。

那么，如果一定要在MySQL这个层面上，抗住高并发的热点数据并发更新，有什么方案呢？那库存扣减案例

1、库存拆分，把一个大的库存拆分成多个小库存，拆分后，一次扣减动作就可以分散到不同的库、表中进行，降低锁粒度提升并发。

优点：实现比较简单

缺点：存储碎片问题、库存调控不方便

2、请求合并，把多个库存扣减请求，合并从一个，进行批量更新。

优点：简单

缺点：适用于异步场景，或者经过分析后认为可以合并的场景

3、把update转换成insert ，直接插入一次占用记录，然后异步统计剩余库存，或者通过SQL统计流水方式计算剩余库存。

优点：没有update，无锁冲突

缺点：insert时控制不好容易超卖、insert 后剩余库存不好统计

除了以上三种，还有一种，那就是改造MySQL

主要思路就是，针对于频繁更新或秒杀类业务场景，大幅度优化对于热点行数据的update操作性能。当开启热点数据共享自动探测时，系统会自动探测是否有单行的热点更新，如果有，则会让大量的并发update排队执行，以减少大量行锁造成的并发性能下降。

也就是说，它们改造了MySQL，让同一个热点行的更新语句，在执行层进行排队。这样的排队相比update的排队，要轻量的多，因为它不需要自旋，不需要抢锁。

这个方案的好处就是开发不需要做额外的事情，只需要开启热点检测就行了。缺点就是改造MySQL数据库有成本。不过现在很多云上数据库都支持了。如：腾讯云数据库，阿里云数据库。

 

二十六、什么是buffer pool？

我们都知道，MySQL的数据是存储在磁盘上面的（Memory引擎除外），但是如果每次查询数据和修改都直接和磁盘交互的话，性能是很差的。

于是，为了提升读写性能，InnoDB引擎就引入了一个中间层，就是buffer pool。

buffer是在内存上的一块连续空间，它主要的用途就是用来缓存数据页的，每个数据页的大小是16KB。

有了buffer pool之后，当我们想要做数据查询的时候，InnoDB会首先检查Buffer Pool中是否存在该数据。如果存在，数据就可以直接从内存中获取，避免了频繁的磁盘读取，从而提高查询性能。如果不存在再去磁盘中进行读取，磁盘中如果找到了的数据，则会把该数据所在的页直接复制一份到buffer pool中，并返回给客户端，后续的话再次读取就可以从buffer pool中就近读取了。

当需要修改的时候也一样，需要先在buffer pool中做修改，然后再把它写入到磁盘中。

但是因为buffer pool是基于内存的，所以空间不可能无限大，它的默认大小是128M，当然这个大小不是完全固定的，我们可以调整，可以通过修改MySQL配置文件中的innodb_buffer_pool_size参数来调整Buffer Pool的大小。

 

二十七、介绍下InnoDB的锁机制？

InnoDB中的锁在锁的级别上一般分为两种，一种是共享锁（S锁），一种是排它锁（X锁）。

共享锁&排他锁

共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。

如果事务T对数据加上共享锁后，则其他事务只能对A再加共享锁，不能加排它锁。获得共享锁的事务只能读数据，不能修改数据。

SELECT ... LOCK IN SHARE MODE

在查询语句后面增加LOCK IN SHARE MODE，MySQL会对查询结果中的每行都加共享锁，当没有其他线程对于查询结果集中的任何一行使用排它锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

 

排它锁又称写锁，如果事务T对数据A加上排它锁后，则其他事务不能再对A加任何类型的锁。获得排它锁的事务既能读数据，又能修改数据。

SELECT ... FOR UPDATE

在查询语句后面增加FOR UPDATE，MySQL会对查询命中的每条记录都加有排它锁（在有索引的情况下其实是通过索引加锁，无索引时会锁表），当没有其他线程对查询结果集中的任何一行使用排它锁时，可以成功申请排它锁，否则会被阻塞。

除了S锁和X锁之外，InnoDB还有两种锁，是IX锁和IS锁，这里的 I 是Intention 的意思，即意向锁。IX就是意向排它锁，IS就是意向共享锁。

什么是意向锁？

MySQL的InnoDB引擎中，支持多种锁级别，包括了行级锁和表级锁。当多个事务想要访问一个共享资源的时候，如果每个事务都直接请求获取锁，那么就可能导致互相阻塞，甚至导致死锁。

所以，为了解决这个问题，MySQL引入了意向锁。

注意：

1、意向锁并不是直接锁定资源，而是为了通知其他事务，以防止它们在资源上设置不兼容的锁。

2、意向锁并不是直接由用户请求的，而是由MySQL管理的。

当一个事务请求获取一个行级锁或表级锁时，MySQL会自动获取相应的表的意向锁。这样，其他事务请求获取表锁时，就可以先基于这个意向锁来发现是否有人加过锁，并根据该锁的类型（意向共享锁 / 意向排它锁）来判断自己是否可以获取锁。这样可以在不阻塞其他事务的情况下，为当前事务锁定资源。

意向锁有两种类型：意向共享锁和意向排它锁。

1、意向共享锁：表示事务打算在资源上设置共享锁（读锁）。这通常用于表示事务计划读取资源，并不希望在读取时有其他事务设置排它锁。

2、意向排它锁：表示事务打算在资源上设置排它锁（写锁）。这表示事务计划修改资源，并不希望有其他事务同时设置共享锁或排它锁。

意向锁是一个表级锁，并且它会在触发意向锁的事务提交或者回滚后释放。

记录锁

Record Lock，翻译成记录锁，是加在索引记录上的锁。例如，select c1 from t where c1 = 10 For UPDATE；会对c1 = 10 这条记录加锁，为了防止任何其他事务插入、更新或删除c1 值为10的行。

Record Lock是一个典型的行级锁，但需要注意的是，Record Lock锁的不是这行记录，而是锁索引记录。并且Record Lock锁只锁索引！

如果没有索引怎么办？对于这种情况，InnoDB会创建一个隐藏的聚簇索引，并使用这个索引进行记录锁定。

关于记录锁的加锁原则，以及Gap Lock和Next Key Lock，请看：二十八、MySQL的行级锁锁的到底是什么？

插入意向锁

插入意向锁是一种由插入操作在行插入之前设置的间隙锁。这种锁表明了插入的意图，以这样一种方式，如果多个事务插入到同一索引间隙内的相同位置插入，则它们不需要互相等待。

假设有索引记录的值为 4 和 7。分别尝试插入值为5和6的不同事务，在获取插入行独占锁之前，各自用插入意向锁锁定4和7之间的间隙，但由于行不冲突，所以它们不会相互阻塞。但是如果它们的都要插入6，那么就需要阻塞了。

AUTO-INC锁

AUTO-INC锁是一种特殊的表级锁，由插入带有AUTO_INCREMENT列的表的事务获取。在最简单的情况下，如果一个事务正在向表中插入值，任何其他事务都必须等待，以便执行它们自己的插入操作，这样第一个事务插入的行就会接收到连续的主键值。

 

二十八、MySQL的行级锁锁的到底是什么？

数据库的行级锁根据锁的粒度不同，可以叫做不同的名字。

Record Lock表示记录锁，锁的是索引记录。

Gap Lock是间隙锁，锁的是索引记录之间的间隙。

Next-Key Lock是Record Lock和Gap Lock的组合，同时锁索引记录和间隙。它的范围是左开右闭的。

总结

InnoDB的锁机制，一共有三种锁，分别是Record Lock、Gap Lock和Next-Key Lock。

InnoDB的RR级别中，加锁的基本单位是Next-Key Lock，只要扫描到的数据都会加锁。唯一索引上的范围查询会访问到不满足条件的第一个值为止。

同时，为了提升性能和并发度，也有两个优化点：

1、索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。

2、索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。

但是RR的隔离级别引入的这些锁，虽然一定程度上可解决很多如幻读的问题，但是也会带来一些副作用，比如并发度降低、容易导致死锁等。