有了关系型数据库，为什么还需要NOSQL? #

nosql数据库无需提前设计表结构，数据可以根据需要自由存储和组织，相较于关系型数据库，nosql高效灵活，非常适合复杂化、高效化、高并发的场景中。

• 关系型数据库

  • 数据以行和列的方式存储

  • 采用结构化查询语言来对数据进行查询

  • 强调ACID规则：原子性atomicity、一致性consistency、隔离性isolation、持久性durability

  • 强调数据一致性，因此牺牲了读写性能

  • 通常存储在硬盘中

    mysql也可以基于内存，即内存表技术。它运行将数据和索引存储在内存中，从而提高查询和修改效率。

    如何创建？

    与创建普通表一样，使用CREATE TABLE，需要将存储引擎设置为：ENGINE=MEMORY

• 非关系型数据库

  • 以键值对来存储
  • 没有固定的要求和限制
  • 事务一般无法回滚（部分可以，MongoDB)
  • 需要通过key来查询
  • 基于内存存储（MongoDB基于磁盘）
  • 不支持范围查询

数据库存储引擎 #

数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可以控制访问权限并快速处理事物，从而满足业务需求。

• 查看当前使用什么引擎

  show engines

• 查看当前默认存储引擎

  show variables like %storage_engine%

• 查看某表用什么引擎

  show create table 表名

  结果参数中有

• 如何指定引擎

  • 创建表时：create语句后面加上engine=“”
  • 修改表时：alter table 表名 engine=""

InnoDB和MyISM的区别 #

InnoDB和MyISM的区别

mysql存储引擎是基于表的吗 #

是，不是基于数据库

联合索引 和 mysql 调优的关系 #

mysql 调优的一个核心动作，就是通过联合索引实现索引覆盖。

在MySQL中，合理使用联合索引可以提高查询效率，通过 联合索引 实现 索引覆盖 ，常常需要注意一些技巧：

• 选择合适的列：联合索引的列顺序非常重要。应该优先选择最频繁用于查询条件的列，以提高索引效率。其次考虑选择性高的列，这样可以过滤出更少的数据。
• 避免冗余列：联合索引的列应该尽量避免包含冗余列，即多个索引的前缀相同。这样会增加索引的维护成本，并占用更多的存储空间。
• 避免过度索引：不要为每个查询都创建一个新的联合索引。应该根据实际情况，分析那些查询是最频繁的，然后创建针对这些查询的索引。
• 覆盖索引：如果查询的列都包含在联合索引中，并且不需要访问表的其他列，那么MySql可以直接使用索引来执行查询，不必访问表，这种索引称为覆盖索引，可以提高查询性能。
• 使用EXPLAIN进行查询计划分析： 使用MySQL的EXPLAIN语句可以查看MySQL执行查询的执行计划，以便优化查询语句和索引的使用。
• 定期优化索引： 随着数据库的使用，索引的效率可能会下降，因此需要定期进行索引的优化和重建，以保持查询性能的稳定性。
• 分析查询日志： 监控数据库的查询日志，分析哪些查询是最频繁的，以及它们的查询模式，可以帮助确定需要创建的联合索引。
• 避免过度索引更新： 避免频繁地更新索引列，因为每次更新索引都会增加数据库的负载和IO操作。

综上所述，联合索引是mysql 调优的一个核心动作， 通过 联合索引进行mysql 调优时，需要综合考虑列的选择、索引的覆盖、查询的频率和模式等因素，以提高MySQL数据库的查询性能。

MySQL索引机制 #

数据库索引，官方定义如下

在关系型数据库中，索引是一种单独的、物理的数据，对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合，以及相应的指向表中物理标识这些值的数据页的逻辑指针清单。

通俗的理解为

在关系型数据库中，索引是一种用来帮助快速检索目标数据的存储结构。

索引的创建 #

MySQL可以通过CREATE、ALTER、DDL三种方式创建一个索引。

1. 使用CREATE语句创建

 CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]);

1. 使用ALTER语句创建

ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);

1. 建表时DDL语句中创建

CREATE TABLE tableName(  
     columnName1 INT(8) NOT NULL,   
     columnName2 ....,
     .....,
     INDEX [indexName] (columnName(length))  
   );

索引的查询 #

  SHOW INDEX from tableName;

索引的删除 #

  ALTER  TABLE  table_name   DROP  INDEX  index_name;
  DROP   INDEX  index_name   ON  table_name;

MySQL联合索引 #

什么是联合索引 #

联合索引（Composite Index）是一种索引类型，它由多个列组成。

MySQL的联合索引（也称为复合索引）是建立在多个字段上的索引。这种索引类型允许数据库在查询时同时考虑多个列的值，从而提高查询效率和性能。

• 联合索引：也称复合索引，就是建立在多个字段上的索引。联合索引的数据结构依然是 B+ Tree。
• 当使用(col1, col2, col3)创建一个联合索引时，创建的只是一颗B+ Tree，在这棵树中，会先按照最左的字段col1排序，在col1相同时再按照col2排序，col2相同时再按照col3排序。

联合索引存储结构 #

联合索引是一种特殊类型的索引，它包含两个或更多列。

在MySQL中，联合索引的数据结构通常是B+Tree，这与单列索引使用的数据结构相同。

当创建联合索引时，需要注意列的顺序，因为这将影响到索引的使用方式。

如下图所示，表的数据如右图，ID 为主键，创建的联合索引为 (a，b)，注意联合索引顺序，下图是模拟的联合索引的 B+ Tree 存储结构

最左前缀匹配原则 #

联合索引还是一颗B+树，只不过联合索引的健 数量不是一个，而是多个。

构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。

假如创建一个（a,b)的联合索引，联合索引B+ Tree结构如下：

结合上述联合索引B+ Tree结构，可以得出如下结论：

1.a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。

所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。

2.当a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。

所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。

例如a = 1 and b = 2 ，a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

最左匹配原则：

最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

下面我们以建立联合索引（a,b,c）为例，进行详细说明

1 全值匹配查询时 #

下述SQL会用到索引，因为where子句中，几个搜索条件顺序调换不影响查询结果，因为MySQL中有查询优化器，会自动优化查询顺序。

select * from table_name where a = '1' and b = '2' and c = '3' 
select * from table_name where b = '2' and a = '1' and c = '3' 
select * from table_name where c = '3' and b = '2' and a = '1' 

2 匹配左边的列时 #

下述SQL，都从最左边开始连续匹配，用到了索引。

select * from table_name where a = '1' 
select * from table_name where a = '1' and b = '2'  
select * from table_name where a = '1' and b = '2' and c = '3'  

下述SQL中，没有从最左边开始，最后查询没有用到索引，用的是全表扫描。

select * from table_name where  b = '2' 
select * from table_name where  c = '3'
select * from table_name where  b = '1' and c = '3' 

下述SQL中，如果不连续时，只用到了a列的索引，b列和c列都没有用到

select * from table_name where  a = '1' and c = '3' 

3 匹配列前缀 #

如果列是字符型的话它的比较规则是先比较字符串的第一个字符，第一个字符小的哪个字符串就比较小，如果两个字符串第一个字符相通，那就再比较第二个字符，第二个字符比较小的那个字符串就比较小，依次类推，比较字符串。

如果a是字符类型，那么前缀匹配用的是索引，后缀和中缀只能全表扫描了

select * from table_name where  a like 'As%';   //前缀都是排好序的，走索引查询
select * from table_name where  a like '%As';   //全表查询
select * from table_name where  a like '%As%';  //全表查询

4 匹配范围值 #

下述SQL，可以对最左边的列进行范围查询

select * from table_name where  a > 1 and a < 3

多个列同时进行范围查找时，只有对索引最左边的那个列进行范围查找才用到B+树索引，也就是只有a用到索引。

在1<a<3的范围内b是无序的，不能用索引，找到1<a<3的记录后，只能根据条件 b > 1继续逐条过滤。

select * from table_name where  a > 1 and a < 3 and b > 1;

5 精确匹配某一列并范围匹配另外一列 #

如果左边的列是精确查找的，右边的列可以进行范围查找，如下SQL中，a=1的情况下b是有序的，进行范围查找走的是联合索引

select * from table_name where  a = 1 and b > 3;

6 排序 #

一般情况下，我们只能把记录加载到内存中，再用一些排序算法，比如快速排序，归并排序等在内存中对这些记录进行排序，有时候查询的结果集太大不能在内存中进行排序的话，还可能暂时借助磁盘空间存放中间结果，排序操作完成后再把排好序的结果返回客户端。

Mysql中把这种再内存中或磁盘上进行排序的方式统称为文件排序。文件排序非常慢，但如果order子句用到了索引列，就有可能省去文件排序的步骤

select * from table_name order by b,c,a limit 10;

因为b+树索引本身就是按照上述规则排序的，所以可以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了，order by的子句后面的顺序也必须按照索引列的顺序给出，比如下SQL：

select * from table_name order by b,c,a limit 10;

在以下SQL中颠倒顺序，没有用到索引

select * from table_name order by a limit 10;
select * from table_name order by a,b limit 10;

以下SQL中会用到部分索引，联合索引左边列为常量，后边的列排序可以用到索引

select * from table_name where a =1 order by b,c limit 10;

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

最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

如下，我们以age，name两个字段建立一个联合索引，非叶子节点中记录age，name两个字段的值，而叶子节点中记录的是age，name两个字段值及主键Id的值，在MySQL中B+ Tree 索引结构如下：

在上述联合索引存储数据过程中，首先会按照age排序，当age相同时则按照name排序。

1. 结合上述索引结构，可以看出联合索引底层也是一颗B+Tree，在联合索引中构造B+Tree的时候，会先以最左边的key进行排序，如果左边的key相同时，则再依次按照右边的key进行排序。
2. 所以在通过索引查询的时候，也需要遵守最左前缀匹配的原则，也就是需要从联合索引的最左边开始进行匹配，这时候就要求查询语句的where条件中，包含最左边的索引的值。

一定要遵循最左前缀匹配吗？ #

最左前缀匹配原则，也就是SQL的查询条件中必须要包含联合索引的第一个字段，这样才能命中联合索引查询，但实际上这条规则也并不是100%遵循的。

因为在MySQL8.x版本中加入了一个新的优化机制，也就是索引跳跃式扫描，这种机制使得咱们即使查询条件中，没有使用联合索引的第一个字段，也依旧可以使用联合索引，看起来就像跳过了联合索引中的第一个字段一样，这也是跳跃扫描的名称由来。

我们来看如下例子，理解一下索引跳跃式扫描如何实现的。

比如此时通过(A、B、C)三个列建立了一个联合索引，此时有如下一条SQL：

SELECT * FROM table_name WHERE B = `xxx` AND C = `xxx`;

按正常情况来看，这条SQL既不符合最左前缀原则，也不具备使用索引覆盖的条件，因此绝对是不会走联合索引查询的。

但这条SQL中都已经使用了联合索引中的两个字段，结果还不能使用索引，这似乎有点亏啊？

因此MySQL8.x推出了跳跃扫描机制，但跳跃扫描并不是真正的“跳过了”第一个字段，而是优化器为你重构了SQL，比如上述这条SQL则会重构成如下情况：

SELECT * FROM `table_name ` WHERE B = `xxx` AND C = `xxx`
UNION ALL
SELECT * FROM `table_name ` WHERE B = `xxx` AND C = `xxx` AND A = "yyy"
......
SELECT * FROM `table_name ` WHERE B = `xxx` AND C = `xxx` AND A = "zzz";

通过MySQL优化器处理后，虽然你没用第一个字段，但我（优化器）给你加上去，今天这个联合索引你就得用，不用也得给我用。

但是跳跃扫描机制也有很多限制，比如多表联查时无法触发、SQL条件中有分组操作也无法触发、SQL中用了DISTINCT去重也无法触发等等，总之有很多限制条件，具体的可以参考《MySQL官网8.0-跳跃扫描》。

最后，可以通过通过如下命令来选择开启或关闭跳跃式扫描机制。

set @@optimizer_switch = 'skip_scan=off|on';

联合索引注意事项 #

1. 选择合适的列：应选择那些经常用于查询条件的列来创建联合索引。

2. 考虑列的顺序：在创建联合索引时，应该根据实际的查询需求来安排列的顺序，以确保索引能够被有效利用。

3. 避免过长的索引：虽然联合索引可以包含多个列，但过长的索引可能会增加维护成本，并且在某些情况下可能不会带来预期的性能提升。

4. 避免范围查询：如果查询中包含范围操作符（如BETWEEN, <, >, LIKE），则MySQL可能无法有效地利用联合索引，因为它需要检查索引中的每个范围边界。

5. 考虑索引的区分度：如果某个列的值重复率很高，那么该列作为联合索引的一部分可能不会提供太大的性能提升，因为它不能有效地区分不同的记录。

   联合索引作为数据库中的一种索引类型，它由多个列组成，在使用时，一般遵循最左匹配原则，以加速数据库查询操作。

char和varchar的区别 #

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

• 优点：定长字符串类型，减少内存碎片，并且无需额外的磁盘空间去存储长度信息。
• 缺点：丢失末尾空格信息。“abc “,“abc “,默认补空格

varchar是一种可变长度的数据类型，它只会存储实际字符串内容，不会填充空格。varchar的长度范围是0-2^16

• 优点：变长字符串，兼容性好。
• 缺点：可能会产生内存碎片，还需要额外1到2字节存储长度信息；update语句可能会导致页分裂。
• 存储身份证、长度固定的订单号等

InnoDB页分裂，页合并 #

数据页是InnoDB存储引擎中用于存储数据的基本单位，通常为16KB。B+树的每个节点都对应着一个数据页，包括根节点、非叶子节点和叶子节点。B+树通过节点之间的指针连接了不同层级的数据页，从而构建了一个有序的索引结构。

页分裂是指将该页面中的一部分索引记录移动到一个新页面中，从而为新记录腾出空间。这样可以保持B+树的平衡和性能。

那么，当我们向Innodb中添加数据的时候，如果索引是随机无序的，那么就导致页分裂。而且分裂这个动作还可能会引起连锁反应，从叶子节点沿着树结构一路分裂到根节点。

当索引页面中的索引记录删除后，页面可能会变得稀疏。为了节省空间和提升性能，可能会发生页合并操作。

页合并是指将两个相邻索引页面合并成一个大的页面，减少B+树层级，提高查询性能。

页合并危害 #

页分裂和页合并涉及大量的数据移动和重组操作。频繁进行这些操作会增加数据库的I/O负担和CPU消耗，影响数据库整体性能。

分裂和合并可能导致B+树索引结构频繁调整，这个过程也会影响插入及删除操作的性能。

页分裂和合并可能会导致磁盘上存在较多的空间碎片，新分出的一个页一般会有很多空闲空间，使得数据库表占用更多的磁盘空间，而导致浪费。

如何避免页分裂 #

使用varchar或uuid作为主键，都会导致页分裂

尽量使用自增id作为索引，尤其是主键索引

如果要大量插入数据，尽量选择批量插入，而不是逐条插入。

频繁删除操作可能导致页合并，一般建议使用逻辑删除，而不是物理删除。

逻辑删除：即在记录中添加一个标记，来代表此记录被删除，而不是真的从数据库删除。

还可以根据实际情况，适当调整InnoDB的配置参数，如页大小、填充因子、叶子页合并阈值等，优化数据库性能。

填充因子：用于控制索引页（B+树节点）物理存储密度的参数，它决定了每个数据页在初始插入数据时的填充比例。

填充因子越低，页中预留空间越多，减少后续插入导致的页分裂。

填充因子越高，页中数据越紧密，存储效率更高，索引更紧凑，可能提升缓存命中率。

MySQL为什么会有存储碎片？有什么危害？ #

这种情况通常发生在频繁执行插入、删除和更新操作的数据库中。这些操作会导致表中的数据页部分空间未被有效利用或数据在物理存储上的排列不连续，从而形成碎片。

• insert

  如果使用uuid，会导致页分裂现象，页分裂导致数据分散在磁盘多个不同的位置。新创建的页可能在物理存储上与原始页相距甚远，这些数据在物理上是不连续的，就会存在碎片。

• update

  如果更新操作导致数据行大小增加，而原始位置没有足够的空间容纳更多的行，这些行数据会被移动到数据文件的其他部分，留下空闲位置，导致碎片

• delete

  innoDB，只给数据做了删除标记，但空间不会立即释放。这导致数据页中可能存在大量未使用空间，增加了数据的分散程度，这就是碎片

• varchar、text这种可变长度字段存储的时候

碎片的危害 #

当表的碎片增多时，数据在物理磁盘上的存储变得不连续，将导致数据库在查询时需要更多的磁盘I/O操作，从而降低查询效率。

碎片导致数据库实际占用的存储空间比数据实际需要的空间大，这会大大浪费磁盘空间，还可能影响缓存效率。

碎片会增加备份文件的大小，同时也会使得备份和恢复过程变得更慢。

如何避免碎片 #

• 使用自增ID，而不是uuid
• 对固定长度的字符串，用char代替varchar
• 避免在高度易变的列上创建索引，这样会频繁触发页分裂
• 使用OPTIMIZE TABLE命令可以重新组织表和索引的物理存储，可以有效减少碎片，优化表的存储和访问速度。

OPTIMIZE TABLE是MySQL中的一个维护命令，用于重建表数据、整理碎片、回收空间，从而提升表性能和存储效率。

如何清理碎片 #

• OPTIMIZE TABLE

  • MyISAM会锁表
  • InnoDB，会对表进行共享锁定，只能读，不能写

  索引多、数据量大、系统资源（CPU、磁盘I/O）不足会增加操作时间

如何查看碎片 #

• show table status 或者 INFORAMTION_SCHEMA

  Data_free字段，表示未使用空间，通常表示表中的碎片空间。

什么是数据库范式，为什么要反范式？ #

1NF：属性不可再分 #

2NF：要有主键，其他字段都依赖主键 #

在一个成绩表中，我们使用“学号”和“课程号”作为联合主键来唯一确定一条成绩记录。

在这个表中，“姓名”只依赖于“学号”，而“课程名称”只依赖于“课程号”。它们都只依赖于联合主键的一部分，这就产生了部分依赖。这种设计会导致数据冗余（“张三”和“数据库原理”被多次存储）和更新异常（如果课程名称变更，需要修改多条记录）。

修改如下：

学生表 (Student):

课程表 (Course):

成绩表 (Score):

通过拆分，成绩表中的非主键字段“成绩”完全依赖于联合主键（“学号”和“课程号”）。学生姓名和课程名称不再冗余存储，数据一致性也得到了更好的保证。

3NF：非主键外的所有字段都互不依赖 #

让我们在学生表中加入“院系”和“院系主任”两个字段：

在这个表中，“院系主任”依赖于“院系”，而“院系”依赖于“学号”。这样就形成了传递依赖（学号 -> 院系 -> 院系主任）。这种设计同样会带来数据冗余和更新异常。如果某院系的系主任发生变动，就需要更新该院系所有学生的记录。

修改如下：

学生表 (Student):

院系表 (Department):

通过这样的拆分，学生表中的非主键字段只直接依赖于主键“学号”。院系信息被独立存储，消除了传递依赖，进一步减少了数据冗余，提高了数据的维护性。

我们在做表结构设计的时候，完全遵守数据库三范式，确实可以避免一些写时异常，提升写入性能，但是同时也会丢失一些读取性能。

因为在遵守范式的数据库设计中，表中不能有任何冗余字段，这就使得查询的时候会经常有多表关联查询，这无疑是比较耗时的。

反范式 #

是一种针对遵从设计范式的数据库的性能优化策略；反范式不等于非范式化，反范式化一定发生在满足范式设计的基础上。先遵守所有规定，再进行局部调整。

比如在表中增加一些冗余字段，方便数据查询，不需要做表join，本质上就是用空间换时间的概念。需要保证数据一致性。

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

主要原因是join效率比较低

MySQL是使用了嵌套循环的方式实现关联查询的，简单来说通过两层循环，用第一张表做外循环，第二张表做内循环，外循环的每条记录跟内循环的记录做比较，符合条件就输出。

两张表的话，复杂度O(n^2),O(n^3),。。。

join #

join有三种

• inner join（内连接，或等值连接）：获取两个表中字段匹配关系的记录

  取两个表的交集部分

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

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

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

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

在配合join一起使用的还有on关键字，用来指明关联查询的一些条件。

嵌套循环算法 #

MySQL使用了嵌套循环（Nested-Loop join)的方式来实现关联查询的

具体算法：

• simple nested loop join

  做法简单粗暴，就是全量扫描连接两张表进行数据的两两对比，所以复杂度可认为为：N*M

  N是驱动表的数量，M是被驱动表的数量

• index nested loop

  当inner loop的表用到的字段有索引的话，可以用索引进行查询数据，因为索引是B+树的，复杂度可以近似认为是N*logM

• block nested loop

  引入一个buffer，会提前把外循环的一部分数据提前放到buffer中，然后内循环的每一行都和整个buffer的数据比较。虽然比较次数还是N*M，但是因为buffer是基于内存的，所以效率高很多。

不做join如何关联查询 #

• 在内存中自己作关联，即先从数据库中把数据查出来之后，我们在代码中再进行二次查询，然后再进行关联。

  categoryMap := make(map[uint]string)
  for _, c := range categories {
      categoryMap[c.ID] = c.Name
  }
  
  // 为每本书添加分类名称
  for i := range books {
      books[i].CategoryName = categoryMap[books[i].CategoryID]
  }
  

• 数据冗余，就是把一些重要数据在表中作冗余

• 宽表，就是基于一定的join关系，把数据库中多张表的数据打平做一张大宽表，可以同步到ES或干脆直接在数据库中直接查询

  • 可在应用层自己构建宽表
  • 数据库也可直接创建宽表

  Elasticsearch（简称 ES） 是一个开源的 分布式搜索和分析引擎，基于 Apache Lucene 构建，专为处理大规模数据而设计。它提供 近实时（NRT, Near Real-Time） 的全文搜索能力，支持结构化、非结构化数据的快速检索和分析。

MySQL一条SQL语句的执行过程 #

select * form users where age='18' and name='hool'

• 使用连接器，通过客户端/服务器通信协议与MySQL建立连接，并查询是否有权限。
• MySQL8.0之前检查是否开启缓存，开启Query Cache且命中完全相同的SQL语句，则将查询结果直接返回给客户端。
• 由解析器（分析器）进行语法和语义分析，并生成解析数。如查询是select、表名users、条件是age=‘18’ and name=‘hool’,预处理器则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表或数据列是否存在等。
• 由优化器生成执行计划。根据索引看看是否可以优化
• 执行器来执行sql语句，这里具体执行操作MySQL的存储引擎来执行SQL语句，根据存储引擎类型，得到查询结果。若开启了 Query Cache，则缓存，否则直接返回。

MySQl 5.x和8.0有什么区别 #

• 性能：8.0比5.x快约2倍

• NoSQL：5.7版本开始提供NoSQL存储功能，8.0在其上面做了进一步改进。

• 窗口函数：8.0开始，新增一个窗口函数的概念，它可以用来实现若干新的查询方式。窗口函数与SUM()、COUNT()这种集合函数类似，但它不会将多行查询结果合并为一行，而是将结果放回多行中。即窗口函数不需要group by(分组）。

  统计每个部门的员工数量

  SELECT 
      department, 
      COUNT(*) AS employee_count
  FROM 
      employees
  GROUP BY 
      department;
  

• 隐藏索引：8.0中，索引可以被隐藏和显示。隐藏时，它不会被优化器所使用。可以用来进行性能调试。索引隐藏后， 若性能没变化，证明索引多余。

• 取消查询缓存：8.0出于性能和可维护方面取消了查询缓存，通过使用索引、更好的查询计划优化、以及缓存结果集而不是整个查询。

• select for update支持no wait：如果另一个事物已经锁定了一些行，当前事物将等待直到哪些锁被释放。加上NOWAIT关键字后，如果尝试锁定的行被其他事物锁定，立即抛出错误，不再等待。

• 降序索引：8.0为索引提供按降序方式进行排序的支持，在这种索引中的值也会按降序的方式进行排序

• UTF-8编码：从8.0开始，utf8mb4作为mysql默认字符集

• json:8.0大幅改进了对json的支持

• 可靠性增加

• 高可用性：innoDB集群集成了原生高可用性解决方案

• 安全性增强

InnoDB支持哪几种行格式？ #

数据库事务 #

事务

InnoDB的一次更新事务过程是怎样的？ #

• 在buffer pool中读取数据：当InnoDB需要更新一条记录时，首先会在buffer pool中查询该记录是否在内存中。如果没有在内存中，则从磁盘读取该页到buffer pool中。

  Buffer Pool 是 InnoDB 存储引擎的 核心内存组件，主要用于缓存表数据和索引数据，是 MySQL 性能优化的关键所在。

  Buffer Pool（缓冲池） 是 InnoDB 在内存中开辟的 数据缓存区域，用于：

  • 缓存磁盘上的数据页（减少磁盘 I/O）
  • 加速读写操作（内存访问比磁盘快几个数量级）
  • 管理脏页（Dirty Page）（待写入磁盘的修改数据）

• 记录Undo log 回滚日志:在修改操作前，InnoDB会在Undo log中记录修改前的数据。Undo log的写入最开始写入到内存中，然后由一个后台线程定时刷新到磁盘中。

  若未刷盘，会回滚事务，超时重试、强制刷盘等机制

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

• 记录RedoLog buffer 重做日志：InnoDB在Buffer Pool中记录修改操作的同时，InnoDB会先将修改操作写入到redo log buffer中

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

• 写入磁盘：在提交过程中，InnoDB会将Buffer Pool的脏页写入磁盘，以保证数据的持久性。但这个写入过程并不是立即执行的，是后台线程异步执行的，所以会延迟写入。

• 记录bin log :在提交过程中，InnoDB会将事务提交的信息记录到Binlog中。binlog是mysql用来实现主从复制的一种机制，用于将主库上的事务同步到从库上。在bin log中记录的信息包括：事务开始时间、数据库名、表名、事务ID、sql语句等。

需要注意的是，在binlog和redo log的写入过程中，其实是分了两阶段的，通过两阶段提交的方式保证一致性。

什么是事务的2阶段提交 #

过程是：

• Prepare (准备）阶段

  这个阶段SQL已经成功执行并生成redolog写入磁盘，出于Prepare阶段

• Binlog持久化

  binlog提交，将binlog写入磁盘

• commit

  在执行引擎内部执行事务操作，写入redolog，处于commit阶段。

write和fsync是与文件系统和磁盘I/O相关的两个不同操作

write操作将数据写入文件的缓冲区，这意味着write操作完成后，并不一定立即将数据持久化到磁盘上，而是将数据暂时存储在内存中。

fsync用于强制将文件的修改持久化到磁盘上。它通常与write配合使用，以确保文件的修改在fsync操作完成后被写入磁盘。

除了这两个之外，还有一个fdatasync，它将指定文件等数据部分从操作it缓存同步到磁盘中，但可能不会刷新元数据（如文件修改时间、权限等）

二阶段提交主要为了保证bin log和redo log的提交一致性

若先写redo log，系统崩了，bin log没写，重启后，事务状态可恢复，但从数据库无法同步

若先写bin log，系统崩了，redo log没写，重启后，事务不可恢复，从数据库多一条记录。

• 第一阶段提交后崩了，即写入redo log，处于prepare状态的时候

  此时已经写入了redolog，处于prepare状态，binlog还没写，崩了直接回滚事务

• 第二阶段崩了

  此时redolog处于prepare状态，binlog写完了，这时候检测binlog事务是否存在并且完整，如果存在且完整，则直接提交事务，否则回滚事务

• 第三阶段崩了

  同阶段二类似处理

如何判断binlog和redolog达成一致了？ #

mysql写完redo log并将它改为prepare状态时，会在redo log中记录一个 XID，全局唯一标识这个事务。

第二阶段binlog刷新磁盘后，binlog结束位置上也会有一个XID。

对比XID确保一致性。

MySQL能保证数据100%不丢失吗？ #

不能

InnoDB有基于内存的，基于内存的如果断电，就会丢失

InnoDB设置了二次提交防止数据丢失。InonDB会默认设置将日志数据立即刷入磁盘，有两个参数，值设置为1。但这样还可能会丢失：

• fsync只请求刷盘，不一定会真的落到磁盘介质

  有些硬盘会欺骗fsync

• 写缓存未关闭

  如果设备层还启用了缓存，操作系统或数据库会认为写入完成，但实际上数据还在硬件缓存中。

• 断电或非正常宕机

  如果没用使用UPS（不间断电源）或BBU（电池保护），哪怕你调用了fsync，掉电依旧可能导致数据丢失

• 磁盘损坏等

什么是脏读、幻读、不可重复读、丢失修改？ #

脏读、丢失修改、不可重复读、丢失修改

不可重复读是读到的不一样，幻读是多了几行数据

MySql中的事务隔离级别？ #

隔离级别

可串行化解决了丢失修改

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

通过mvcc机制解决脏读和不可重复读，通过mvcc+间隙锁解决幻读

• 脏读

  脏读读解决依赖了Read View,Read View会告诉我们本次事务应该看到那个快照，不应该看到那个快照。

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

• 不可重复读

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

• 幻读

  基于MVCC+间隙锁，在某种程度可以避免幻读发生，但没有办法完全避免。

  事务1先进行快照读，事务2插入数据并提交，事务1再进行update新插入的这条记录是可以成功的。

  事务1先进行快照读，事务2插入数据并提交，事务1再进行当前读，会发生幻读。

MVCC解决幻读 #

mvcc有两种读，一种是快照读，一种是当前读

• 快照读

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

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

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

  也就是说在快照读情况下，mvcc可以解决幻读问题。

• 当前读

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

  在RR级别下，使用select … for update的时候，会进行加锁，不仅仅对行记录加锁，还会对记录之间的间隙进行加锁，这就叫间隙锁。

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

  所以在RR级别中，通过加入间隙锁的方式，就避免了幻读的发生。

解决不了的幻读 #

• 未来得及加间隙锁

• 事务1第一次查询不加锁，用快照读，然后事务2插入数据并提交；事务1再加锁查询第二次，发生了当前读。

根据快照读的定义，如果本事务中发生了数据修改，就会更新快照。

如何避免幻读 #

最高隔离级别，可串行化

如果一定要加锁，一定要在事务一开始就加锁，这样就会有间隙锁，有效避免幻读发生。

如何理解MVCC？ #

多版本并发控制系统，和数据库锁一样，它是一种解决方案。

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

UndoLog #

undo log是回滚日志，在事务未提交之前，MySQl会先提交undo log，当事务崩溃时，利用undo log进行回退。

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

行记录的隐式字段 #

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

• db_row_id:隐藏主键，如果我们没有给这个表创建主键，那么会以这个字段创建聚簇索引。
• db_trx_id：对这条数据做出修改的最新事务ID
• db_roll_ptr:回滚指针，指向这条记录的上一个版本，就是undo log的上一个版本的快照地址

**注意：**以上字段，只有在聚簇 索引的行记录中才有，而在普通二级索引中是没有这些值的。

二级索引的MVCC支持

因为每一次记录变更前都会先存储一份快照到undo log中，那么这几个隐式字段也会跟着记录一起保存在undo log中。因此，就形成了一个快照链表。

要读那个快照呢？

Read View #

见下方什么是Read View，什么样的ReadView可见？

一个事务，能看到的是在他开始之前就已经提交的事务结果，而未提交的事务结果都是不可见的。

如果不可见怎么办？使用undo log

当事务ID不符合Read View规则的时候，那就需要从undo log里面获取数据的历史快照，然后数据快照的事务ID再和Read view进行可见性比较，如果能找到一条快照则返回，否则返回空。

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

什么是Read View，什么样的ReadView可见？ #

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

RR要求在一个事务中，多次读取的结果是保持一致的，RC要求在一个事务中，每次读取最新的结果。

在可重复读级别下，快照在事务开始后第一次查询时创建，并在整个事务期间保持不变。

在读已提交级别下，快照会在每次查询时重新创建，以反映数据库中的最新提交更改。

在Read View中有个几重要的属性：

• trx_ids:表示在生成Read View时当前系统中活跃的读写事务的事务id列表
• low_limit_id：应该分配给下一个事务的id值
• up_limit_id：未提交事务中最小的事务ID
• creator_trx_id：创建这个Read View的事务ID

假如一个ReadView的内容为：

trx_ids = [5,6,8)
low_limit_id = 8
up_limit_id = 5
creator_trx_id = 7

假设当前事务要读取某一个记录行，该记录行的 db_trx_id（即最新修改该行的事务ID）为 trx_id，那么，就有以下几种情况了：

• trx_id<up_limit_id，即小于5的事务，说明这些事务在生成ReadView之前就已经提交了，那么该事务的结果就是可见的。
• trx_id>=low_limit_id，即大于8的事务，说明该事务在生成 ReadView 后才生成，所以该事务的结果就是不可见的。
• up_limit_id<trx_id<low_limit_id，即大于等于5，小于8，这种情况下，会再拿事务ID和Read View中的trx_ids进行逐一比较。
  • 如果，事务ID在trx_ids列表中，如6，那么表示在当前事务开启时，这个事务还是活跃的，那么这个记录对于当前事务来说应该是不可见的。
  • 如果，事务id不在trx_ids列表中，如7，那么表示的是在当前事务开启之前，其他事务对数据进行修改并提交了，所以，这条记录对当前事务就应该是可见的。
  • 当然这里有个例外情况，那就是这个trx_id=creator_trx_id，那么就肯定是可见的

总之，一个事务，能看到的是在他开始之前就已经提交的事务结果，而未提交的事务结果都是不可见的。

mysql的select * 会用到事务吗？ #

会，在innoDB中，所有的修改操作都必须在事务中进行

即使没有明确的开启事务的语句，InnoDB存储引擎也会为查询自动开启一个隐式事务

查询语句事务不会持有任何锁，在查询结束后立即提交。这种隐式事务通常被称为自动事务。

为什么MySQl默认使用RR隔离级别？ #

Mysql定位是提供一个稳定的关系型数据库。为了解决单点故障问题，主要采用主从复制。就是搭建Mysql集群，整体对外提供服务，集群中的机器分为主服务器和从服务器，主服务器提供写服务，从服务器提供读服务。

MySql在主从复制的过程中，数据的同步是通过bin log进行的。

早期bin log只有statement这一种格式，里面记录的就是SQL语句原文。

如果使用读未提交和读已提交这两种隔离级别会出现以下问题： #

在主数据库中，如果是RC隔离级别，事务2插入操作看不到事务1删除操作，所以数据库中后面仍然会留下5

但由于事务提交顺序不同，在bin log中会留下 插入数据5 删除小于10的数据 的SQL语句。这样bin log被同步到从数据库中，会导致数据不一致。

为了避免上述情况，才讲数据库默认隔离级别设置成了RR。

在RR隔离级别下，事务1在更新数据的时候，不仅对更新的行加行级锁，还会增加GAP锁和临键锁。所以事务2执行时会被卡住，等事务1结束后才能继续执行。

因此Mysql还禁止在使用statement格式的bin log情况下，使用RC 事务隔离级别。会报错。

MySql的binlog有几种格式？ #

• statement

  用的很少，binlog记录的SQL原文，会导致主从数据库数据不同步情况

  • 一种是RC隔离级别下的不同步
  • 一种是delete或update时，指定了limit，但并没有使用order by。

• row

  binlog会记录每个数据更改的具体细节。意味着二进制日志中的每个条目都会详细列出发生变更的行的内容和修改。

  • 优点：不会发生主从不一致情况
  • 缺点：会记录更多的数据，在数据恢复的时候比较慢，也会导致磁盘IO和网络IO比较高。

• mixed

  结合row 和 statement 的优点，MySQL 会根据情况自动选择切换。

MySQL的limit+order by什么会数据重复？ #

如果order by的列中，多行具有相同的值，服务器可以自由地以任何顺序返回这些行，并且根据整体执行计划的不同可能会以不同的方式返回它们。

可能结果1：

可能结果2（相同查询，不同执行计划）：

因此进行limit+order by时，一定要避免使用可能重复的字段，如时间、名称、分数等。应该选择唯一性的字段，如主键ID。

为什么默认RR，大厂要改成RC？ #

主要是为了提高并发度，降低死锁发生概率。

• 一致性读

  又称快照读。

  在mysql中，只有RR和RC两种隔离级别才会使用快照读。

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

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

  在RC这种隔离级别中，还支持**“半一致读”**，一条update语句，如果where条件匹配到的记录已经加锁，那么InnoDB会返回记录最近提交的版本，由MySQL上层判断是否需要真的加锁。

• 锁机制

  在MySQL中，有三种类型锁

  • Record Lock：记录锁，锁的是索引记录

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

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

    为什么左开右闭？

  在RC中，只会对索引加记录锁，不会添加间隙锁和Next-key Lock

  在RR中，为了解决幻读问题，都支持

  在RR中，什么时候加Record lock，什么时候加Gap lock，什么时候加Next-key lock?

• 主从同步

  在RC隔离级别下，可以支持row格式日志

为什么使用？ #

• 提升并发

  RC只会对索引加记录锁，使得并发比RR高，RC还支持**“半一致读”**，可以大大减少更新语句时行锁的冲突；对于不满足更新条件的记录，可以提前释放锁，提升并发度。

• 减少死锁

  在RR中，为了解决幻读问题，支持三种锁，使得锁粒度变大，死锁概率变大

通过其他方式解决不可重复读问题，或者不可重复读问题并不严重。商品大促时，突然修改了商品价格，部分用户按原价格下单，又能怎？

介绍一下InnoDB的锁机制？ #

在InnoDB中，在锁的级别上分为两种，共享锁（s锁）、排他锁（X锁）

共享锁、排他锁 #

共享锁又称读锁，是读操作时创建的锁。只能读，不能改。

select ... lock in share mode;

在查询语句后面增加上面语句，mysql会对查询结果中的每行都加共享锁。

当这一行数据获取了共享锁，那么其他事务依然可以对这一行数据添加共享锁，但不能添加排他锁

排他锁又称写锁，其他事务不能读，也不能改。

select ... for update;

在查询语句后面增加上面语句，mysql会对查询命中的每条记录都加排他锁。

当这一行数据获取了排他锁，那么其他事务就不能在对这一行数据添加共享锁或者排他锁。

意向锁 #

意向锁（Intention Lock） 是一种“提前声明”，告诉别人：“我可能要锁整张表，你们锁行的时候注意点！”

它不直接锁数据，而是标记一个意向，避免其他事务不小心锁表，导致冲突。

所以，意向锁是数据库管理系统中用于实现锁协议的一种锁机制，旨在处理不同锁粒度（如行锁和表锁）之间的并发性问题。（相同锁粒度（如多个行级锁）之间的并发性问题通过行级互斥锁解决。）

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

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

IX锁意向排他锁、IS锁 意向共享锁

• 意向共享锁（IS Lock）：
  • 相当于你在桌子上贴个纸条：“我可能要读这张表里的某些行，别删整张表！”
  • 其他事务可以继续读，但不能直接锁整张表（比如不能加排他锁）。
• 意向排他锁（IX Lock）：
  • 相当于你贴纸条：“我可能要改这张表里的某些行，别动整张表！”
  • 其他事务可以读某些行，但不能直接锁整张表。

记录锁 #

Record Lock,是加在索引记录上的锁。

Record Lock是一个典型的行级锁，Record Lock锁的不是这行记录，而是锁索引记录。并且Record Lock锁且只锁索引。

**如果没有索引怎么办？**InnoDB会创建一个唯一的 隐藏的 非空聚簇索引，并使用这个索引进行记录锁定。如果没有适合的非空唯一索引，则会创建一个隐藏主键（row_id)作为聚簇索引。

插入意向锁 #

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

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

AUTO- INC锁 #

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

innodb_autoinc_lock_mode变量控制用于自增锁定的算法。它允许你选择如何在可预测的自增值序列和插入操作的最大并发性之间进行权衡。

InnoDB中的表级锁、页级锁、行级锁 #

在数据库中，按锁的粒度划分，可分为全局锁、表级锁、行级锁、页级锁。

InnoDB中不支持页级锁。

全局锁 #

全局锁，是一种影响整个MySQL实例的锁。

例如，FLUSH TABLES WITH READ LOCK 命令会锁定整个数据库实例的所有表，主要用于全局备份等操作。这个命令是全局读锁定，执行了命令之后库实例中的所有表都被锁定为只读。

页级锁 #

✅ 比表锁灵活，只锁一部分数据（InnoDB 的"页"通常是 16KB 数据块）。

❌ MySQL 默认不用页锁（InnoDB 主要用行锁）。

行级锁 #

✅ 并发度最高，只锁一行，其他人可以随便操作其他行。

锁的是索引

update时没有用到索引，加什么锁？

当update和where条件中没有用到索引的话，他会做全表扫描，但也不是全部锁定。而是将符合条件的记录锁定。

字典锁 #

字典锁：MetaData Lock，也叫MDL锁，它是一种用于管理元数据的锁机制，而不是数据本身的锁。

MDL锁用于控制对数据库对象的元数据的并发访问，数据库会在执行DDL（Data Defination Language）操作时加上字典锁。字典锁的主要目的是保护数据库中的元数据对象，如表、列、索引、视图等，以确保在DDL操作期间，不会出现数据一致性问题和竞争条件。

触发数据库加字典锁的一些情况：

• 创建/修改/删除表结构
• 创建/修改/删除索引
• 修改列定义
• 创建/修改/删除视图
• 其他DDL操作：创建、删除、修改存储过程、触发器、事件等也能设计到元数据的锁的。

字典锁升级 #

在数据库中， 通常有两种锁级别，即共享锁和排他锁。字典锁也有两种锁级别：

• 共享字典锁
• 排他字典锁

表级锁 #

InnoDB中的表级锁并不是没有用，而是因为他划分的太细了，意向锁、AUTO- INC锁、字典锁等。而剩下的普通的排他锁和共享锁，确认很少才能用的上。

**InnoDB会倾向于选择行级锁来进行并发控制。**但如果在一些极端情况下，比如说UPDATE操作需要扫描整个表且对表中许多行进行更新，InnoDB可能会评估行级锁的成本过高，而采用表级锁。

高并发情况下自增主键会不会重复，为什么？ #

单库单表情况下，不会重复，

Mysql通过AUTO-INC锁机制确保了自增主键的唯一性。

AUTO-INC是InnoDB存储引擎专门用来管理自增长列（AUTO_INCREMENT）increment 值分配的一种内部机制。

AUTO-INC的工作原理是：**当一个事务尝试向一个包含自增列的表中插入一条或多条新记录时，InnoDB会为该表上的自增列申请一个AUTO- INC锁。**这个锁确保在事务插入记录期间，没有其他事务可以插入新记录到同一个表中。

mysql5.1之前，它是一个表级锁，在插入开始的时候被获取，一直等到事务结束被释放。5.1开始，引入了一种新的AUTO-INC锁策略；AUTO-INC锁在插入操作完成后就被释放，不用等事务结束。这种锁被称为“轻量级AUTO-INC锁”。

乐观锁和悲观锁如何实现？ #

锁按使用方式划分为乐观锁和悲观锁。

MySQL的行级锁锁的到底是什么？ #

Record Lock #

select c1 from t where c1=10 for update;

会对c1=10这条记录加锁，为了防止任何其他事务插入、更新或删除c1=10的行。

Gap Lock #

**指的是在索引记录之间的间隙上的锁。**或者在第一个索引记录之前或最后一个索引记录之后的间隙上的锁。

Gap指的是InnoDB的索引数据结构中可以插入新值的位置。

Gap lock 只在可重复读（RR)隔离级别中生效。

• 对于具有唯一搜索条件的唯一索引，InnoDB只锁定找到的索引记录，而不会锁定间隙。
• 对于其他搜索条件，InnoDB锁定扫描的索引范围。使用gap lock 或 net-key lock来阻塞其他事务插入范围覆盖的间隙。

也就是说，对于SELECT FOR UPDATE、LOCK IN SHARE MODE、UPDATE和DELETE等语句处理时，除了对唯一索引的唯一搜索外都会获取gap锁或next-key锁，即锁住其扫描的范围。

Next-key Lock #

Next-key Lock是索引记录上的记录锁和间隙锁的组合。

假设一个索引包含值10、11、13和20。此索引可能的next-key锁包括以下区间:

(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, ∞ ]

对于最后一个间隙，∞不是一个真正的索引记录，因此，实际上，这个next-key锁只锁定最大索引值之后的间隙。

所以，Next-Key 的锁的范围都是左开右闭的。

Next-Key Lock和Gap Lock一样，只有在InnoDB的RR隔离级别中才会生效。

Next-key Lock 的 左开右闭 设计是 InnoDB 在 效率（减少不必要的锁冲突）和 正确性（彻底解决幻读）之间的平衡：

• 左开：避免锁住不相关的记录（如前驱记录）。
• 右闭：确保查询范围的终止点被严格锁定，防止幻读。

MySql的加锁原则 #

两个原则、两个优化、一个bug

原则1:加锁的基本单位是next-key lock。

原则2:查找过程中访问到的对象才会加锁。

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

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

一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

• 当我们执行update t set d=d+1 where id = 7的时候，由于表 t 中没有 id=7 的记录，所以：

  • 根据原则 1，加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；
  • 根据优化 2，这是一个等值查询 (id=7)，而 id=10 不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。

• 当我们执行select * from t where id>=10 and id<11 for update的时候：

  • 根据原则 1，加锁单位是 next-key lock，会给 (5,10]加上 next-key lock，范围查找就往后继续找，找到 id=15 这一行停下来
  • 根据优化 1，主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。
  • 根据原则 2，访问到的都要加锁，因此需要加 next-key lock(10,15]。因此最终加的是行锁 id=10 和 next-key lock(10,15]。

• 当我们执行select * from t where id>10 and id<=15 for update的时候：

  • 根据原则 1，加锁单位是 next-key lock，会给 (10,15]加上 next-key lock，并且因为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。
  • 但是，InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20。而且由于这是个范围扫描，因此索引 id 上的 (15,20]这个 next-key lock 也会被锁上。

• 当我们执行select id from t where c=5 lock in share mode的时候：

  • 根据原则 1，加锁单位是 next-key lock，因此会给 (0,5]加上 next-key lock。要注意 c 是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的，需要向右遍历，查到 c=10 才放弃。
  • 根据原则 2，访问到的都要加锁，因此要给 (5,10]加 next-key lock。
  • 根据优化 2：等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件，因此退化成间隙锁 (5,10)。
  • 根据原则 2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁。

• 当我们执行select * from t where c>=10 and c<11 for update的时候：

  • 根据原则 1，加锁单位是 next-key lock，会给 (5,10]加上 next-key lock，范围查找就往后继续找，找到 id=15 这一行停下来
  • 根据原则 2，访问到的都要加锁，因此需要加 next-key lock(10,15]。由于索引 c 不是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。