【2025最新】MySQL的各种锁有哪些？各种索引优化有哪些（索引覆盖，索引下推，索引跳跃扫描等）？怎么设计索引？排查索引？
MySQL 锁机制与索引优化全面解析
在数据库中，有各种各样的锁，按锁的粒度划分，可分为全局锁、表级锁、行级锁和页级锁。
在InnoDB中，有全局锁、表级锁、行级锁，但是是不支持页级锁的。
一、MySQL 锁机制
1. 按锁粒度分类
| 锁类型 | 描述 | 特点 |
|---|---|---|
| 全局锁 | FLUSH TABLES WITH READ LOCK | 锁定整个数据库实例，用于备份 |
| 表级锁 | LOCK TABLES...//意向锁、AUTO-INC锁、字典锁 | 锁定整张表，MyISAM默认锁 |
| 行级锁 | InnoDB引擎实现。记录锁，间隙锁。临键锁 | 锁定单行记录，并发度高 |
2. InnoDB 行锁类型
Innodb中的锁在锁的级别上一般分为两种，一种是共享锁（S锁），一种是排他锁（X锁）。
共享锁&排他锁
共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。
排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的锁。获得排他锁的事务既能读数据，又能修改数据。
除了S锁和X锁之外，Innodb还有两种锁，是IX锁和IS锁，这里的I是Intention 的意思，即意向锁。IX就是意向排他锁，IS就是意向共享锁。
数据库的行级锁根据锁的粒度不同，可以叫做不同的名字。
Record Lock表示记录锁，锁的是索引记录。
Gap Lock是间隙锁，锁的是索引记录之间的间隙。
Next-Key Lock是临键锁，是Record Lock和Gap Lock的组合，同时锁索引记录和间隙。他的范围是左开右闭的。
(1) 记录锁（Record Lock）
Record Lock，翻译成记录锁，是加在索引记录上的锁。例如，SELECT c1 FROM t WHERE c1 = 10 For UPDATE;会对c1=10这条记录加锁，为了防止任何其他事务查询、插入、更新或删除c1值为10的行。
Record Lock是一个典型的行级锁，但是需要特别注意的是，Record锁的不是这行记录，而是锁索引记录。并且Record lock锁且只锁索引！
如果没有索引怎么办？对于这种情况，InnoDB 会创建一个隐藏的聚簇索引，并使用这个索引进行记录锁定。
如果我们在一张表中没有定义主键，那么，MySQL会默认选择一个唯一的非空索引作为聚簇索引。如果没有适合的非空唯一索引，则会创建一个隐藏的主键（row_id）作为聚簇索引。
为了防止任何其他事务插入、更新或删除id值为1的行。
-- 对id=1的记录加锁 SELECT * FROM users WHERE id = 1 FOR UPDATE;
(2) 间隙锁（Gap Lock）
在索引记录之间的间隙上的锁，或者在第一个索引记录之前或最后一个索引记录之后的间隙上的锁。指的是InnoDB的索引数据结构中可以插入新值的位置。
例如，如果选择所有大于10的值进行更新，间隙锁将阻止另一个事务插入大于10的新值。
既然是锁，那么就可能会影响到数据库的并发性，所以，间隙锁只有在Repeatable Reads这种隔离级别中才会起作用。
在Repeatable Reads这种隔离下，对于锁定的读操作（select ... for update 、 lock in share mode)、update操作、delete操作时，会进行如下的加锁：
● 对于具有唯一搜索条件的唯一索引，InnoDB只锁定找到的索引记录，而不会锁定间隙。
● 对于其他搜索条件，InnoDB锁定扫描的索引范围，使用gap lock或next-key lock来阻塞其他事务插入范围覆盖的间隙。
也就是说，对于SELECT FOR UPDATE、LOCK IN SHARE MODE、UPDATE和DELETE等语句处理时，除了对唯一索引的唯一搜索外都会获取gap锁或next-key锁，即锁住其扫描的范围。
-- 锁定id在(5,10)区间的间隙 SELECT * FROM users WHERE id BETWEEN 5 AND 10 FOR UPDATE;
(3) 临键锁（Next-Key Lock）
Next-Key锁是索引记录上的记录锁和索引记录之前间隙上的间隙锁的组合。
Next-Key Lock和Gap Lock一样，只有在InnoDB的RR隔离级别中才会生效。
-
记录锁 + 间隙锁的组合
-
解决幻读问题的关键
假设一个索引包含值10、11、13和20。此索引可能的next-key锁包括以下区间:
(-∞, 10](10, 11]
(11, 13]
(13, 20]
(20, ∞ ]
(4) 意向锁（Intention Lock）
当一个事务请求获取一个行级锁或表级锁时，MySQL会自动获取相应的表的意向锁。
1IS锁： 表示事务打算在资源上设置共享锁（读锁）。这通常用于表示事务计划读取资源，并不希望在读取时有其他事务设置排它锁。
2IX锁： 表示事务打算在资源上设置排它锁（写锁）。这表示事务计划修改资源，并不希望有其他事务同时设置共享或排它锁。
意向锁是数据库管理系统中用于实现锁协议的一种锁机制，旨在处理不同锁粒度（如行锁和表锁）之间的并发性问题
当一个事务请求获取一个行级锁或表级锁时，MySQL会自动获取相应的表的意向锁。这样，其他事务请求获取表锁时，就可以先基于这个意向锁来发现是否有人加过锁，并根据该锁的类型（意向共享锁/意向排他锁）来判断自己是否可以获取锁。这样可以在不阻塞其他事务的情况下，为当前事务锁定资源。
意向锁其实是一个表级锁！
并且他会在触发意向锁的事务提交或者回滚后释放。
-
IS（意向共享锁）：准备加共享锁前先加IS锁
-
IX（意向排他锁）：准备加排他锁前先加IX锁
(5)AUTO-INC锁、
AUTO-INC 锁是一种特殊的表级锁，由插入带有 AUTO_INCREMENT 列的表的事务获取。在最简单的情况下，如果一个事务正在向表中插入值，任何其他事务都必须等待，以便执行它们自己的插入操作，这样第一个事务插入的行就会接收到连续的主键值。
(6)字典锁
字典锁，英文名叫做MetaData Lock，也叫做MDL锁，它是一种用于管理元数据的锁机制，而不是数据本身的锁。
MDL锁用于控制对数据库对象的元数据的并发访问，数据库会在执行DDL（Data Defination Language）操作时加上字典锁。字典锁的主要目的是保护数据库中的元数据对象，如表、列、索引、视图等，以确保在DDL操作期间，不会出现数据一致性问题和竞争条件。
(7)乐观锁、悲观锁
悲观锁：
在MySQL中，悲观锁是需要依靠数据库提供的锁机制实现的，在InnoDB引擎中，要使用悲观锁，需要先关闭MySQL数据库的自动提交属性，然后通过select ... for update来进行加锁。
通过for update的方式进行加锁，然后再进行修改。这就是比较典型的悲观锁策略。
在数据库中，悲观锁的流程如下：
●在对记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）。
●如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。具体响应方式由开发者根据实际需要决定。
●如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。
●其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。
//0.开始事务
begin;
//1.查询出商品信息
select quantity from items where id=1 for update;
//2.修改商品quantity为2
update items set quantity=2 where id = 1;
//3.提交事务
commit;
乐观锁：
MySQL中的乐观锁主要通过CAS的机制来实现，一般通过version版本号来实现。
CAS是项乐观锁技术，当多个线程尝试使用CAS同时更新同一个变量时，只有其中一个线程能更新变量的值，而其它线程都失败，失败的线程并不会被挂起，而是被告知这次竞争中失败，并可以再次尝试。
//查询出商品信息，quantity = 3
select quantity from items where id=1
//根据商品信息生成订单
//修改商品quantity为2
update items set quantity=2 where id=1 and quantity = 3;
具体流程：
我们在更新之前，先查询一下库存表中当前库存数（quantity），然后在做update的时候，以库存数作为一个修改条件。当我们提交更新的时候，判断数据库表对应记录的当前库存数与第一次取出来的库存数进行比对，如果数据库表当前库存数与第一次取出来的库存数相等，则予以更新，否则认为是过期数据。
因为虽然在使用乐观锁的时候，我们没有显式的加锁，也没有用到对他的相关锁机制。但是乐观锁是使用updata语句过程中实现的，update的过程是有锁的。数据库在更新时，会根据where条件对索引添加行级锁（可能还有gap 或者 next key）
所以，乐观锁的过程中，并不是完全无锁的。
那么，乐观锁既然也有锁，那么他相比悲观锁意义在哪里呢？
乐观锁最大的好处就是通过CAS的方式做并发校验，这个过程不需要提前加锁，只需要在更新的那一刻加一个短暂的锁而已，而悲观锁的话，需要你先select for update，锁的时长要长得多。
（8）MySQL的加锁原则
前面介绍过了Record Lock、Gap Lock和Next-Key Lock，但是并没有说明加锁规则。关于加锁规则，包含了两个“原则”、两个“优化”和一个“bug”：
原则 1：加锁的基本单位是 next-key lock。是一个前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
3. 锁兼容矩阵
| X | IX | S | IS | |
|---|---|---|---|---|
| X | × | × | × | × |
| IX | × | √ | × | √ |
| S | × | × | √ | √ |
| IS | × | √ | √ | √ |
4：Online DDL（Innodb加索引，这个时候会锁表吗？）
DDL，即Data Defination Language，是用于定义数据库结构的操作。DDL操作用于创建、修改和删除数据库中的表、索引、视图、约束等数据库对象，而不涉及实际数据的操作。以下是一些常见的DDL操作：
●CREATE
●ALTER
●DROP
●TRUNCATE
在 MySQL 5.6 之前，InnoDB 索引构建期间会对表进行排它锁定，这意味着在索引构建期间，其他会话不能读取或修改表中的任何数据。这将导致长时间阻塞和性能问题。
在MySQL 5.6之前，所有的ALTER操作其实是会阻塞DML（INSERT、DELETE和UPDATE等）操作的，如：添加/删除字段、添加/删除索引等，都是会锁表的。
自 MySQL 5.6 开始，InnoDB 使用一种称为“Online DDL”的技术，允许在不阻塞其他会话的情况下完成DML操作。Online DDL 针对不同的操作，有多种实现方式，包括COPY，INSTANT以及INPLACE。
尽最大可能保证DDL期间不阻塞DML动作。但是需要注意，这里说的尽最大可能意味着不是所有DDL语句都会使用OnlineDDL加锁。
因为DDL有很多种操作，比如创建索引、增加字段、增加外键等，所以不同的操作支持的类型也不一样。在我们创建、删除或者重命名一个索引的时候，是会用到in place的模式。
需要注意的是，虽然Online DDL 可以减少锁定时间和对性能的影响，但在索引构建期间仍然可能出现锁定和阻塞。例如，在添加索引时，如果表中有许多未提交的事务，则需要等待这些事务提交后才能开始索引构建。因此，建议在非高峰期进行此类操作，以避免影响用户的正常使用。在进行任何DDL操作之前，最好进行充分的测试和规划，并且确保有备份和回滚计划，以防意外情况的发生。
二、索引优化技术
1. 索引覆盖（Covering Index）
覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。
-- 创建覆盖索引 CREATE INDEX idx_covering ON users(name, age);-- 查询只需扫描索引，无需回表 SELECT name, age FROM users WHERE name = '张三';
2. 索引下推（ICP，Index Condition Pushdown）
索引下推是 MySQL 5.6引入了一种优化技术，默认开启，使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。
-- 5.6+版本自动启用 -- WHERE条件在存储引擎层过滤 SELECT * FROM users WHERE name LIKE '张%' AND age > 20;
使用了索引下推技术，则MYSQL首先会返回符合zipcode=’95054’的索引，然后根据lastname LIKE ‘%etrunia%’来判断索引是否符合条件。
如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。 有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。
当一条SQL使用到索引下推时，explain的执行计划中的extra字段中内容为：Using index condition。
上面的例子中，提到了like，包括MySQL官网中也只提到了like，但是其实不止有Like。因为我认为索引下推其实是解决索引失效带来的效率低的问题的一种手段。
3. 索引跳跃扫描（Index Skip Scan）
支持不符合组合索引最左前缀原则条件下的SQL，依然能够使用组合索引，减少不必要的扫描。
-- 8.0+版本特性 -- 对复合索引(a,b)，即使a条件缺失也能利用索引 CREATE INDEX idx_skip ON users(gender, name); SELECT * FROM users WHERE name = '张三'; -- 可能使用idx_skip
对于SELECT f1, f2 FROM t1 WHERE f2 = 40;SQL执行过程如下：
●获取f1字段第一个唯一值，也就是f1=1
●构造f1=1 and f2 = 40，进行范围查询
●获取f1字段第二个唯一值，也就是f1=2
●构造f1=2 and f2 = 40，进行范围查询
●一直扫描完f1字段所有的唯一值，最后将结果合并返回-- 变成这样
SELECT f1, f2 FROM t1 WHERE f1 =1 and f2 = 40
UNION
SELECT f1, f2 FROM t1 WHERE f1 =2 and f2 = 40;
可以发现这种查询优化比较适合于f1的取值范围比较少，区分度不高的情况（如性别），一旦f1的区分度特别高的话（如出生年月日），这种查询可能会更慢。
真正要不要走索引跳跃扫描，还是要经过MySQL的优化器进行成本预估之后做决定的。
所以，这种优化一般用于那种联合索引中第一个字段区分度不高的情况。但是话又说回来了，我们一般不太会把区分度不高的字段放在联合索引的左边，不过事无绝对，既然MySQL给了一个优化的方案，就说明还是有这样的诉求的。
但是，我们不能依赖他这个优化，建立索引的时候，还是优先把区分度高的，查询频繁的字段放到联合索引的左边。
除此之外，在MySQL官网中，还提到索引跳跃扫描还有一些其他的限制条件：
●表T至少有一个联合索引，但是对于联合索引(A,B,C,D)来说，A和D可以是空的，但B和C必须是非空的。
●查询必须只能依赖一张表，不能多表join
●查询中不能使用GROUP BY或DISTINCT语句
●查询的字段必须是索引中的列
4. 其他优化技术
-
MRR（Multi-Range Read）：减少随机IO
-
BKA（Batched Key Access）：批量连接优化
-
松散索引扫描：GROUP BY优化
三、索引设计原则
1. 设计流程
-
分析查询模式：EXPLAIN分析慢查询
-
选择合适列：
-
高选择性列（区分度高）
-
常用于WHERE、JOIN、ORDER BY的列
-
选择适合的索引类型
-
考虑查询的频率和效率：
-
考虑联合索引，考虑索引覆盖
-
避免使用过长的索引：合适的索引长度：
-
-
列顺序原则：
-
等值查询列在前，范围查询列在后
-
常用列在前
-
-
避免过度索引：一般单表不超过5-6个索引
2. 复合索引设计示例
-- 良好设计示例 CREATE INDEX idx_optimal ON orders(user_id, status, create_time);-- 能高效处理以下查询： SELECT * FROM orders WHERE user_id = 100 AND status = 1; SELECT * FROM orders WHERE user_id = 100 ORDER BY create_time;
索引类型:
InnoDB存储引擎支持两种常见的索引数据结构：B+树索引（适合于范围查找和排序操作，维护成本比较高，有序存储）、Hash（适合于等值查询，维护成本相对较低，无序存储）索引，其中B+树索引是目前关系型数据库系统中最常见、最有效的索引。
B+树索引：
1B+树是一棵平衡树，每个叶子节点到根节点的路径长度相同，查找效率较高；
2B+树的所有关键字都在叶子节点上，因此范围查询时只需要遍历一遍叶子节点即可；
3B+树的叶子节点都按照关键字大小顺序存放，因此可以快速地支持按照关键字大小进行排序；
4B+树的非叶子节点不存储实际数据，因此可以存储更多的索引数据；
5B+树的非叶子节点使用指针连接子节点，因此可以快速地支持范围查询和倒序查询。
6B+树的叶子节点之间通过双向链表链接，方便进行范围查询。
1支持范围查询，B+树在进行范围查找时，只需要从根节点一直遍历到叶子节点，因为数据都存储在叶子节点上，而且叶子节点之间有指针连接，可以很方便地进行范围查找。
2支持排序，B+树的叶子节点按照关键字顺序存储，可以快速支持排序操作，提高排序效率；
3存储更多的索引数据，因为它的非叶子节点只存储索引关键字，不存储实际数据，因此可以存储更多的索引数据；
4在节点分裂和合并时，IO操作少。B+树的叶子节点的大小是固定的，而且节点的大小一般都会设置为一页的大小，这就使得节点分裂和合并时，IO操作很少，只需读取和写入一页。
5有利于磁盘预读。由于B+树的节点大小是固定的，因此可以很好地利用磁盘预读特性，一次性读取多个节点到内存中，这样可以减少IO操作次数，提高查询效率。
6有利于缓存。B+树的非叶子节点只存储指向子节点的指针，而不存储数据，这样可以使得缓存能够容纳更多的索引数据，从而提高缓存的命中率，加快查询速度。
数据库中的B+树索引分为聚集索引和非聚集索引。聚集索引就是按照每张表的主键构造一个B+树，聚集索引就是B+树的叶子节点中记录着表中一行记录的所有值。只要找到这个叶子节点也就得到了这条记录的所有值。非聚簇索引（非主键字段创建的索引）的叶节点中不包含行记录的所有值。只包含索引值和主键的值。通过非聚簇索引的查询，需要进行一次回表，就是先查到主键ID，再通过ID查询所需字段。
根据索引的唯一性，有可以把索引分为唯一索引和普通索引。唯一索引要求索引的列值必须唯一，不能重复。
另外，在MySQL 5.6中还增加了全文索引，5.7版本之后通过使用ngram插件开始支持中文。
四、索引问题排查
update时没有用到索引，加什么锁？
mysql的行级锁锁的是索引，但是当update语句的where条件中没有用到索引的话，他会做全表扫描，但是也不是全部都锁定。而是把符合条件的记录锁住。
1. 检查工具
-- 查看索引使用情况 SHOW INDEX FROM table_name;-- 分析查询执行计划 EXPLAIN SELECT * FROM users WHERE name = '张三';--首先，key一定要有值，不能是NULL --其次，type应该是ref、eq_ref、range、const等这几个 --还有，extra的话，如果是NULL，或者using index，using index condition都是可以的-- 8.0+版本更详细的执行计划 EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';
2. 常见问题诊断
(1) 索引失效场景
-
使用函数：
WHERE YEAR(create_time) = 2023 -
索引列隐式转换：
WHERE user_id = '100'（user_id是INT） -
前导模糊查询：
WHERE name LIKE '%张' -
OR条件不当：
WHERE a=1 OR b=2（a、b无联合索引） -
不符合最左前缀法则：索引的最左边的一部分来进行匹配。
-
不等于比较，！=
-
索引列参与计算
-
in操作，太多的话不会走索引
-
order by操作，数据小直接在内存排序导致不走索引
-
非空判断：is not null
(2) 性能分析
-- 查看索引使用统计（需先开启） SELECT * FROM sys.schema_index_statistics WHERE table_schema = 'your_db';-- 检查冗余索引 SELECT * FROM sys.schema_redundant_indexes;
五、实战建议
-
监控：定期检查
performance_schema中的索引统计 -
维护：对碎片率高的索引执行
OPTIMIZE TABLE -
版本特性：MySQL 8.0+的不可见索引、降序索引等新特性
-
工具链：使用pt-index-usage等工具分析索引使用情况