记录些MySQL题集（17）

MySQL联合索引，最左匹配原则是什么？

联合索引 和 MySQL 调优的关系

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

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

1. 选择合适的列： 联合索引的列顺序非常重要。应该优先选择最频繁用于查询条件的列，以提高索引的效率。其次考虑选择性高的列，这样可以过滤出更少的数据。

2. 避免冗余列： 联合索引的列应该尽量避免包含冗余列，即多个索引的前缀相同。这样会增加索引的维护成本，并占用更多的存储空间。

3. 避免过度索引： 不要为每个查询都创建一个新的联合索引。应该根据实际情况，分析哪些查询是最频繁的，然后创建针对这些查询的索引。

4. 覆盖索引： 如果查询的列都包含在联合索引中，并且不需要访问表的其他列，那么MySQL可以直接使用索引来执行查询，而不必访问表，这种索引称为覆盖索引，可以提高查询性能。

5. 使用EXPLAIN进行查询计划分析： 使用MySQL的EXPLAIN语句可以查看MySQL执行查询的执行计划，以便优化查询语句和索引的使用。

6. 定期优化索引： 随着数据库的使用，索引的效率可能会下降，因此需要定期进行索引的优化和重建，以保持查询性能的稳定性。

7. 分析查询日志： 监控数据库的查询日志，分析哪些查询是最频繁的，以及它们的查询模式，可以帮助确定需要创建的联合索引。

8. 避免过度索引更新： 避免频繁地更新索引列，因为每次更新索引都会增加数据库的负载和IO操作。

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

基础知识：MySQL索引机制

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

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

通俗的理解为

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

索引的创建

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

• 使用CREATE语句创建

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

• 使用ALTER语句创建

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

• 建表时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去重也无法触发等等，总之有很多限制条件。

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

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

联合索引注意事项

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

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

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

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

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

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

MySQL什么时候 锁表？如何防止锁表？

MySQL调优的重点目标： 避免锁表

锁表会带来一系列问题，影响数据库的性能和系统的稳定性。

主要是下面的四个问题： 性能问题、死锁问题、可用性问题、一致性问题

1. 锁表带来的性能问题

锁表会阻止其他事务对该表的并发访问，包括读操作和写操作。

锁表会导致严重的性能问题：

• 系统吞吐量下降：多个事务需要依次等待锁的释放，导致系统整体的吞吐量下降。

• 用户体验差：用户的请求需要长时间等待，导致响应时间变长，影响用户体验。

2. 锁表引发死锁

在高并发环境下，锁表更容易导致死锁的发生。

锁表会导致严重的事务问题：

• 事务回滚：部分事务被迫回滚，影响数据一致性。

• 系统复杂性增加：需要数据库管理系统介入来检测和解决死锁问题，增加系统复杂性。

3. 锁表降低系统可用性

长时间的表锁定会影响数据库的可用性，使得应用程序无法及时处理用户请求。

锁表带来的系统可用性问题：

• 功能不可用：系统的部分功能无法使用，影响用户的正常操作。

• 业务中断：在严重情况下，可能导致整个系统不可用，影响业务连续性。

4. 锁表引发数据一致性问题

在高并发写操作场景下，锁表会导致数据一致性问题。

锁表带来的数据一致性问题：

• 数据不一致：无法及时更新数据，影响业务逻辑的正确执行。

• 状态混乱：例如，订单状态更新时，如果表被锁定，其他更新操作无法及时进行，可能会导致数据状态不一致。

那么，什么情况下导致锁表？ 如何解决锁表的问题呢？

回顾一下MySQL锁的分类

从操作的粒度可分为表级锁、行级锁和页级锁。

1.表级锁：

每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。

注意： 表级锁应用在MyISAM、InnoDB、BDB 等存储引擎中。

表锁的特点：

• 开销小，加锁快

• 不会出现死锁

• 锁定粒度大，发生锁冲突的概率最高，并发度最低

2.行级锁：

每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。

注意：行级锁应用在InnoDB 存储引擎中。MyISAM 没有行级锁

行锁的特点：

• 开销大，加锁慢

• 会出现死锁

• 锁定粒度小，发生锁冲突的概率最低，并发度最高

3.页级锁：

每次锁定相邻的一组记录，锁定粒度界于表锁和行锁之间，加锁开销和加锁时间界于表锁和行锁之间，并发度一般。

注意：页级锁 应用在BDB 等存储引擎中。

页锁的特点：

• 开销和加锁时间介于表锁和行锁之间

• 会出现死锁

• 锁定粒度介于表锁和行锁之间，并发度一般

MySQL 使用页级锁的情况相对较少，因为 MySQL 默认使用的是行级锁。

但在特定的情况下，MySQL 可能会使用页级锁，主要包括以下几种情况：

1. 表级锁定转换为页级锁定： 当表级锁无法满足需求时，MySQL 可能会将表级锁转换为页级锁。这通常发生在使用 BDB 存储引擎时，因为 BDB 存储引擎默认使用表级锁。在某些情况下，MySQL 可能会将表级锁转换为页级锁，以提高并发性能。

2. 隐式锁定大量行数据： 在某些情况下，如果一次性锁定了大量行数据，则 MySQL 可能会使用页级锁来减少锁的数量，从而减轻锁管理的负担。例如，在使用 BDB 存储引擎时，如果一次性锁定了大量行数据，则 MySQL 可能会将表级锁转换为页级锁。

3. DDL 操作： 在执行对表结构进行修改的 DDL（数据定义语言）操作时，MySQL 可能会使用页级锁来锁定整个表。例如，当执行 ALTER TABLE 操作时，MySQL 可能会锁定整个表的页，以防止其他对表结构的修改和查询操作。

总的来说，MySQL 使用页级锁的情况相对较少，因为页级锁通常会导致锁的粒度过大，影响并发性能。在设计数据库和应用程序时，通常会尽量避免使用页级锁，而是优先使用行级锁或其他更细粒度的锁。

InnoDB 存储引擎中的 表锁和行锁

对于 MySQL 来说，每种存储引擎都可以实现自己的锁策略和锁粒度，

比如 InnoDB 引擎支持行级锁和表级锁，不支持 页级锁

下面主要聚焦介绍 InnoDB 存储引擎中的两大锁：

• 表级锁

• 行级锁。

InnoDB 的表级锁

表锁，顾名思义就是对某个表加锁。

表级锁可以分为：表锁、元数据锁、意向锁三种。

表级锁之一：表锁

那什么时候会使用表锁呢？

一般情况是对应的存储引擎没有行级锁（例如：MyIASM），或者是对应的 SQL 语句没有匹配到索引。

对于第一种情况而言，因为对应存储引擎不支持行锁，所以只能是使用更粗粒度的锁来实现，这也比较好理解。

对于第二种情况而言，如果存储引擎支持行锁，但对应的 SQL 就没有使用索引，那么此时也是会全表扫描，那此时也是会使用表锁。

例如下面的语句没有指定查询列，或者指定了查询列但是并没有用到索引，那么也是会直接锁定整个表。

情况1：没有指定查询列
select * from user;

情况2：指定查询列，但是没有用到索引
select * from user where name = 'zhangsan';

上面说的索引，其实是判断是否会用行级锁的关键。

表级锁之二：元数据锁

元数据，指的是我们的表结构这些元数据。

元数据锁（Metadata Lock）自然是执行 DDL 表结构变更语句时，我们对表加上的一个锁了。

那什么时候会使用元数据锁这个表级锁呢？

当我们对一个表做增删改查操作的时候，会加上 MDL 读锁；当我们要对表结构做变更时，就会加 MDL 写锁。

在MySQL中，当对表进行写操作（如INSERT、UPDATE、DELETE）时，需要对相关的数据行加锁以确保数据的一致性和完整性。在某些情况下，MySQL需要锁定整个表而不是部分行，这种情况下会锁定整个表，导致其他会话不能访问表。

1.使用ALTER TABLE、TRUNCATE TABLE等语句对表进行结构性修改时，MySQL需要锁定整个表以防止其他会话对表进行操作。

2.使用LOCK TABLES语句手动锁定表时，MySQL将锁定整个表以确保其他会话不能访问它。

3.在使用MyISAM存储引擎时，当执行写操作时，MySQL会对整个表进行加锁。这是因为MyISAM使用表级锁定而不是行级锁定。

项目中最常见的锁表问题，都是由于UPDATE语句或者DELETE语句的where条件没有走索引导致的。因此我们需要在条件字段上加索引，从而将表锁变为行锁。

表级锁之三：意向锁

意向锁，本质上就是空间换时间的产物，是为了提高行锁效率的一个东西。

在 InnoDB 中，我们对某条记录进行锁定时，为了提高并发度，通常都只是锁定这一行记录，而不是锁定整个表。

而当我们需要为整个表加 X 锁的时候，我们就需要遍历整个表的记录，如果每条记录都没有被加锁，才可以给整个表加 X 锁。

而这个遍历过程就很费时间，这时候就有了意向锁。

意向锁，其实就是标记这个表有没有被锁，如果有某条记录被锁住了，那么就必须获取该表的意向锁。

所以当我们需要判断这个表的记录有没有被加锁时，直接判断意向锁就可以了，减少了遍历的时间，提高了效率，是典型的用空间换时间的做法。

那么什么时候会用到意向锁呢？

很简单，就是在对表中的行记录加锁的时候，就会用到意向锁。

InnoDB 存储引擎支持 多粒度（granular）锁定，就是说允许事务在行级上的锁和表级上的锁同时存在。

那么为了实现行锁和表锁并存，InnoDB 存储引擎就设计出了 意向锁（Intention Lock） 这个东西：

Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table.

很好理解：意向锁是一个表级锁，其作用就是指明接下来的事务将会用到哪种锁。

有两种意向锁：

• 意向共享锁（IS Lock）：当事务想要获得一张表中某几行的共享锁行级锁）时，InnoDB 存储引擎会自动地先获取该表的意向共享锁（表级锁）

• 意向排他锁（IX Lock）：当事务想要获得一张表中某几行的排他锁（行级锁）时，InnoDB 存储引擎会自动地先获取该表的意向排他锁（表级锁）

各位其实可以直接把 ”意向“ 翻译成 ”想要“，想要共享锁、想要排他锁，你就会发现原来就这东西啊（滑稽）。

意向锁之间是相互兼容的：

	IS 锁	IX 锁
IS 锁	兼容	兼容
IX 锁	兼容	兼容

但是与表级读写锁之间大部分都是不兼容的：

	X 锁	S 锁
IS 锁	不兼容	兼容
IX 锁	不兼容	不兼容

注意，这里强调一点：上表中的读写锁指的是表级锁，意向锁不会与行级的读写锁互斥！！！

来理解一下为什么说意向锁不会与行级的读写锁互斥。举个例子，事务 T1、事务 T2、事务 T3 分别想对某张表中的记录行 r1、r2、r3 进行修改，很普通的并发场景对吧，这三个事务之间并不会发生干扰，所以是可以正常执行的。

这三个事务都会先对这张表加意向写锁，因为意向锁之间是兼容的嘛，所以这一步没有任何问题。

那如果意向锁和行级读写锁互斥的话，岂不是这三个事务都没法再执行下去了，对吧。

OK，看到这里，我们来思考两个问题：

1）为什么没有意向锁的话，表锁和行锁不能共存？

2）意向锁是如何让表锁和行锁共存的？

首先来看第一个问题，假设行锁和表锁能共存，举个例子：事务 T1 锁住表中的某一行（行级写锁），事务 T2 锁住整个表（表级写锁）。

问题很明显，既然事务 T1 锁住了某一行，那么其他事务就不可能修改这一行。这与 ”事务 T2 锁住整个表就能修改表中的任意一行“ 形成了冲突。所以，没有意向锁的时候，行锁与表锁是无法共存的。

再来看第二个问题，有了意向锁之后，事务 T1 在申请行级写锁之前，MySQL 会先自动给事务 T1 申请这张表的意向排他锁，当表上有意向排他锁时其他事务申请表级写锁会被阻塞，也即事务 T2 申请这张表的写锁就会失败。

InnoDB 的行级锁

行级锁是存储引擎级别的锁，需要存储引擎支持才有效。

目前 MyISAM 存储引擎不支持行级锁，而 Innodb 存储引擎则支持行级锁。

而表级锁，则是 MySQL 层面就支持的锁。

那么什么时候会使用行级锁呢？

当增删改查匹配到索引时，Innodb 会使用行级锁。

如果没有匹配不到索引，那么就会直接使用表级锁。

InnoDB存储引擎三种行级锁

InnoDB引擎行锁是通过对索引数据页上的记录加锁实现的，主要实现算法有 3 种：Record Lock、Gap Lock 和 Next-key Lock,也就是InnoDB的三种行锁模式。

• RecordLock锁(行锁)：锁定单个行记录的锁。（RecordLock锁 是记录锁，RC、RR隔离级别都支持）

• GapLock锁：间隙锁，锁定索引记录间隙(不包括记录本身)，确保索引记录的间隙不变。（GapLock是范围锁，RR隔离级别支持。RC隔离级别不支持）

• Next-key Lock 锁(临键锁)：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别支持。RC隔离级别不支持）

行级锁之一：记录锁(Record Locks)

（1）记录锁, 仅仅锁住索引记录的一行，在单条索引记录上加锁。

（2）record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。

所以说当一条sql没有走任何索引时，那么将会在每一条聚合索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

行级锁之二：间隙锁(Gap Locks)

（1）区间锁, 仅仅锁住一个索引区间（开区间，不包括双端端点）。 （2）在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。

（3）间隙锁可用于防止幻读，保证索引间的不会被插入数据

比如在 100、10000中，间隙锁的可能值有 (∞, 100)，(100, 10000)，(10000, ∞)，

行级锁之三：临键锁(Next-Key Locks)

（1）record lock + gap lock, 左开右闭区间。

（2）默认情况下，innodb使用next-key locks来锁定记录。select … for update

（3）但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

（4）Next-Key Lock在不同的场景中会退化:

比如在 100、10000中，临键锁(Next-Key Locks)的可能有 (∞, 100]，(100, 10000] , 这里的关键是左开右闭

在MySQL中，当对表进行写操作（如INSERT、UPDATE、DELETE）时，需要对相关的数据行加锁以确保数据的一致性和完整性。在某些情况下，MySQL需要锁定整个表而不是部分行，这种情况下会锁定整个表，导致其他会话不能访问表。

1.使用ALTER TABLE、TRUNCATE TABLE等语句对表进行结构性修改时，MySQL需要锁定整个表以防止其他会话对表进行操作。

2.使用LOCK TABLES语句手动锁定表时，MySQL将锁定整个表以确保其他会话不能访问它。

3.在使用MyISAM存储引擎时，当执行写操作时，MySQL会对整个表进行加锁。这是因为MyISAM使用表级锁定而不是行级锁定。

InnoDB 如何加锁？

InnoDB 采用的是两阶段锁定协议（two-phase locking protocol），意思是：对于每一个事务Transaction，分为两个阶段

• 第一阶段：增长阶段（Growing Phase）

在这个阶段，事务可以获得锁定，但不能释放锁定。事务可以继续请求并获得锁定，直到它达到了其锁定点（Lock Point），也就是事务获得最后一个锁定的时间点。一旦事务进入第二阶段，它就不能再获得新的锁定。

• 第二阶段：缩减阶段（Shrinking Phase）

在这个阶段，事务可以释放已经持有的锁定，但不能再获取新的锁定。这个阶段的目的是确保事务不会在已经进入第二阶段后再次请求锁定

两阶段锁定协议（two-phase locking protocol），有下面的两个特点：

• 特点1：即在事务执行过程中，随时都可以执行加锁操作，

• 特点2：但是只有在事务执行 COMMIT 或者 ROLLBACK 的时候才会释放锁，并且所有的锁是在同一时刻被释放。

InnoDB 如何加锁？从下面三个维度来分开介绍：

第1个维度：InnoDB 如何加意向锁?

它比较特殊，是由 InnoDB 存储引擎自己维护的，用户无法手动操作意向锁，

在为数据行加读写锁之前，InnoDB 会先获取该数据行所在在数据表的对应意向锁。

第2个维度：InnoDB如何加表级锁?：

1）隐式锁定：对于常见的 DDL 语句（如 ALTER、CREATE等），InnoDB 会自动给相应的表加表级锁

2）显示锁定：在执行 SQL 语句时，也可以明确显示指定对某个表进行加锁（lock table user read(write)）

lock table user read; # 加表级读锁
unlock tables; # 释放表级锁

第3个维度：InnoDB 如何加行级锁?：

1）对于常见的 DML 语句（如 UPDATE、DELETE 和 INSERT），InnoDB 会自动给相应的记录行加写锁

2）默认情况下对于普通 SELECT 语句，InnoDB 不会加任何锁，但是在 Serializable 隔离级别下会加行级读锁

上面两种是隐式锁定，InnoDB 也支持通过特定的语句进行显式锁定，不过这些语句并不属于 SQL 规范：

3）SELECT * FROM table_name WHERE ... FOR UPDATE，加行级写锁

4）SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE，加行级读锁

另外，需要注意的是，InnoDB 存储引擎的行级锁是基于索引的，也就是说当索引失效或者说根本没有用索引的时候，行锁就会升级成表锁。

举个例子（这里就以比较典型的索引失效情况 “使用 or" 来举例），有数据库如下，id 是主键索引：

CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
12345

新建两个事务，先执行事务 T1 的前两行，也就是不要执行 rollback 也不要 commit：

这个时候事务 T1 没有释放锁，并且由于索引失效事务 T1 其实是锁住了整张表，

此时再来执行事务 2，你会发现事务 T2 会卡住，最后超时关闭事务：

什么情况下，MySQL会锁定整个表？

MySQL会在以下情况下锁定整个表:

1.对表进行结构性修改:

当使用ALTER TABLE或TRUNCATE TABLE等语句对表进行结构性修改时，MySQL需要锁定整个表以防止其他会话对表进行操作。

2.手动锁定表:

使用LOCK TABLES语句手动锁定表时，MySQL将锁定整个表以确保其他会话不能访问它。

3.MyISAM写操作:

在使用MyISAM存储引擎时，执行写操作会导致MySQL对整个表加锁，

这是因为MyISAM使用表级锁定而不是行级锁定。

4.两个或多个事务在同时修改一个表时:

事务中包含多条对同一个表进行修改的SQL语句时，该表会被锁定。

这是因为MVSQL采用行锁定机制，但当两个或多个事务在同时修改一个表时，未使用的修改请求会被放入等待队列。

5.索引操作:

对一个大表进行索引操作(如新建或删除索引)时，该表会被锁定。

索引操作锁定表的时间与表的大小和结构有关。

6.并发操作:

在一张表中插入大量数据的同时，尝试在同一时刻进行查询操作，会导致表被锁定。

这是因为查询和插入操作都需要获取相应的锁

7 索引不可用时加锁操作:

使用SELECT..FOR UPDATE语句进行行级锁定操作时，如果索引不可用，MySQL可能会升级为表锁。

8.索引选择不恰当:

在某些情况下，如果查询的索引选择不恰当，MVSQL可能会升级为表锁，尤其是当锁住的数据量较大时

9.更新和删除场景，where没命中索引（最常见场景）:

项目中最常见的锁表问题，都是由于UPDATE/DELETE时, where条件没有走索引导致的。

当执行UPDATE或DELETE语句且where条件未使用索引时，可能会导致全表扫描并锁定整个表。

因此我们需要在条件字段上加索引，从而将表锁变为行锁。

10.查询场景，索引失效的情况下，行锁升表锁

在索引失效的情况下，MySQL会把所有聚集索引记录和间隙都锁上，称之为锁表，或叫行锁升表锁.

在 MySQL 中，索引对于查询性能至关重要，但是有些情况下索引可能会失效，从而导致查询性能下降

以下是一些常见的索引失效原因及其解决方法：

10.1. 使用函数或操作符

• 原因：在 WHERE 子句中使用函数或操作符（如计算、转换函数）会导致索引失效。

• 示例：SELECT * FROM table WHERE YEAR(date_column) = 2023;

• 解决方法：在索引列上避免使用函数或操作符。可以改为：SELECT * FROM table WHERE date_column BETWEEN '2023-01-01' AND '2023-12-31';

10.2. 模糊查询

• 原因：在 LIKE 子句中以通配符开头的查询（如 '%abc'）会导致索引失效。

• 示例：SELECT * FROM table WHERE column LIKE '%value';

• 解决方法：避免在 LIKE 中使用前置通配符，可以使用后置通配符：SELECT * FROM table WHERE column LIKE 'value%';

10.3. 不符合最左前缀原则

• 原因：复合索引必须按照最左前缀原则使用，否则会导致索引失效。

• 示例：对于索引 (a, b, c)，查询 WHERE b = 1 AND c = 2会导致索引失效。

• 解决方法：确保查询条件按照索引的顺序使用，如 WHERE a = 1 AND b = 2 AND c = 3。

10.4. 数据类型不一致

• 原因：查询条件中的数据类型与索引列的数据类型不一致，会导致索引失效。

• 示例：SELECT * FROM table WHERE varchar_column = 123;

• 解决方法：确保查询条件的数据类型与索引列的数据类型一致：SELECT * FROM table WHERE varchar_column = '123';

10.5. 使用 OR 条件

• 原因：在多个列上使用 OR 条件时，如果其中一个列没有索引，整个查询会导致索引失效。

• 示例：SELECT * FROM table WHERE column1 = 1 OR column2 = 2;

• 解决方法：可以改为 UNION 查询以使用索引：SELECT * FROM table WHERE column1 = 1 UNION SELECT * FROM table WHERE column2 = 2;

10.6. 隐式类型转换

• 原因：隐式类型转换会导致索引失效。

• 示例：SELECT * FROM table WHERE varchar_column = 123;

• 解决方法：显式转换查询条件的数据类型：SELECT * FROM table WHERE varchar_column = '123';

10.7. 范围条件

• 原因：在复合索引中，范围条件（如 <, >, BETWEEN, LIKE）之后的索引列会失效。

• 示例：对于索引 (a, b)，查询 WHERE a > 1 AND b = 2 会导致索引 b 失效。

• 解决方法：尽量避免在复合索引中使用范围条件，如果必须使用，考虑调整索引顺序。

10.8. NULL 判断

• 原因：在某些情况下，对 NULL 的判断会导致索引失效。

• 示例：SELECT * FROM table WHERE column IS NULL;

• 解决方法：确保列上有合适的索引，并且在设计表结构时尽量避免使用 NULL。

10.9. 更新频繁的列

• 原因：在频繁更新的列上建立索引，可能会导致索引的维护成本高，从而影响查询性能。

• 解决方法：在设计索引时，尽量避免在频繁更新的列上建立索引。

10.10. 查询优化器选择

• 原因：有时候查询优化器可能错误地选择了全表扫描而不是使用索引，特别是在小表或索引列的选择性不高的情况下。

• 解决方法：可以使用 FORCE INDEX 强制使用特定索引，或调整查询语句和索引设计以帮助优化器选择正确的索引。

通过识别和解决这些索引失效的原因，可以显著提高 MySQL 查询的性能。

MySQL会锁表的场景总结

总之，MySQL在执行结构性修改、手动锁定、写操作、事务处理、索引操作、并发操作、加锁操作、以及在特定查询条件下索引失效，都可能需要对表进行锁定。

了解这些情况，有助于更好地进行MySQL性能优化

如何减少或避免锁表？

锁表会对系统性能、数据一致性和用户体验产生负面影响，甚至可能引发严重的业务中断和系统崩溃。

因此，避免锁表是数据库优化和系统设计中的重要任务。

可以通过优化索引、分解大事务、合理设计表结构、使用适当的事务隔离级别、读写分离等方法来减少锁表问题，提升系统的并发性能和稳定性。

在 MySQL 中避免锁表问题对于提高数据库性能和并发性至关重要。

以下是一些调优策略和最佳实践，以减少或避免锁表问题：

1. 使用合适的存储引擎

• InnoDB：使用支持行级锁的存储引擎，如 InnoDB。InnoDB 是 MySQL 的默认存储引擎，支持行级锁定和事务，能够有效减少锁冲突。

• 避免使用 MyISAM，因为它只支持表级锁定，容易导致锁表问题。

2. 优化查询和索引

• 索引：确保查询使用适当的索引来减少扫描的行数，从而减少锁定的范围和时间。

• 覆盖索引：使用覆盖索引以减少表扫描，提高查询效率。

• 优化查询语句：避免使用复杂的查询语句，尽量简化查询条件，减少锁定时间。

3. 分解大事务

• 分解大事务：将大事务分解成多个小事务，以减少单个事务持有锁的时间，降低锁冲突的概率。

• 事务控制：在事务中，尽量减少锁定时间。尽量避免在事务中进行用户交互操作。

4. 锁策略和隔离级别

• 锁策略：尽量使用行级锁定，而不是表级锁定。确保在需要时显式地使用合适的锁策略。

• 隔离级别：选择适当的事务隔离级别（如 Read Committed 或 Repeatable Read），以平衡一致性和并发性。尽量避免使用 Serializable 隔离级别，因为它会导致更多的锁定和冲突。

5. 分区和分表

• 表分区：对大表进行分区，以减少每次操作的行数和锁定范围，提高并发性。

• 分表：将数据分布到多个表中，以减少单个表的负载和锁定冲突。

6. 避免长时间的锁定操作

• 批量操作：将批量操作分成多个小批次，以减少每次操作的锁定时间。

• 在线DDL操作：使用 InnoDB 的在线 DDL 功能（例如，ALTER TABLE ... ALGORITHM=INPLACE），以减少对表的锁定时间。

7. 监控和分析

• 监控锁等待：使用 MySQL 的性能_schema 和相关工具（如 SHOW PROCESSLIST、INFORMATION_SCHEMA.INNODB_LOCKS、INFORMATION_SCHEMA.INNODB_LOCK_WAITS）监控锁等待情况。

• 分析慢查询：使用 slow query log 分析慢查询日志，找出可能导致锁表的查询，并进行优化。

8. 读写分离

• 主从复制：通过主从复制实现读写分离，将读操作分散到从库上，以减轻主库的负载和锁定压力。

9. 使用合适的锁

• 显式锁定：在需要时显式地使用合适的锁定策略（如 SELECT ... FOR UPDATE），但要谨慎使用，避免不必要的长时间锁定。

10. 业务层优化

• 批量提交：在业务逻辑中优化批量提交操作，减少锁冲突。

• 乐观锁定：在业务层使用乐观锁定机制，以减少数据库锁定冲突。

通过以上策略和最佳实践，可以有效减少或避免 MySQL 中的锁表问题，提高数据库的并发性和性能。