MySQL表锁和行锁详解及如何避免锁表 -- 知识铺
尼恩说在前面
在40岁老架构师 尼恩的读者交流群(50+)中，最近有小伙伴拿到了一线企业如 字节、得物、阿里、滴滴、极兔、有赞、希音、百度、网易、美团、蚂蚁、得物的面试资格，遇到很多很重要的面试题：
什么情况下，MySQL会锁定整个表？
如何减少或避免锁表？
如何解决提升msyql的性能，避免锁表？
最近有小伙伴在面试字节，都到了这个的面试题。
小伙伴没回答好，支支吾吾的说了几句，面试官不满意，面试挂了。
所以，尼恩给大家做一下系统化、体系化的梳理，帮大家展示一下大家雄厚的 “技术肌肉”，让面试官爱到 “不能自已、口水直流”，然后实现”offer直提”。
当然，这道面试题，以及参考答案，也会收入咱们的 《尼恩Java面试宝典PDF》V175版本，供后面的小伙伴参考，提升大家的 3高 架构、设计、开发水平。
《尼恩 架构笔记》《尼恩高并发三部曲》《尼恩Java面试宝典》的PDF，请到文末公号【技术自由圈】获取
本文目录
- 尼恩说在前面：
- 本文的2个重量级作者：
- Mysql调优的重点目标： 避免****锁表
- 1.锁表带来的性能问题
- 2.锁表引发死锁
- 3.锁表降低系统可用性
- 4.锁表引发数据一致性问题
- 回顾一下mysql锁的分类
- 1.表级锁：
- 2.行级锁：
- 3.页级锁：
- InnoDB存储引擎中的 表锁和行锁
- InnoDB的表级锁
- 表级锁之一：表锁
- 表级锁之二：元数据锁
- 表级锁之三：意向锁
- InnoDB的行级锁
- InnoDB存储引擎三种行级锁
- 行级锁之一：记录锁(Record Locks)
- 行级锁之二：间隙锁(Gap Locks)
- 行级锁之三：临键锁(Next-Key Locks)
- InnoDB如何****加锁？
- 第1个维度：InnoDB 如何加意向锁?
- 第2个维度：InnoDB如何加表级锁?：
- 第3个维度：InnoDB 如何加行级锁?：
- 回到面试题：什么情况下，MySQL会锁定整个表？
- 1.对表进行结构性修改:
- 2.手动锁定表:
- 3.MyISAM写操作:
- 4.两个或多个事务在同时修改一个表时:
- 5.索引操作:
- 6.并发操作:
- 7索引不可用时加锁操作:
- 8.索引选择不恰当:
- 9.更新和删除场景，where没命中索引（最常见场景）:
- 10.查询场景，索引失效的情况下，行锁升表锁
-10.1.使用函数或操作符
-10.2.模糊查询
-10.3.不符合最左前缀原则
-10.4.数据类型不一致
-10.5.使用 OR 条件
-10.6.隐式类型转换
-10.7.范围条件
-10.8. NULL判断
-10.9.更新频繁的列
-10.10.查询优化器选择
- MySQL会锁表的场景总结
- 回到面试题第二问：如何减少或避免****锁表？
- 1.使用合适的存储引擎
- 2.优化查询和索引
- 3.分解大事务
- 4.锁策略和隔离级别
- 5.分区和分表
- 6.避免长时间的锁定操作
- 7.监控和分析
- 8.读写分离
- 9.使用合适的锁
- 10.业务层优化
- 技术自由圈几个 和MYSQL 有关的核心面试真题
- 说在最后：有问题找老架构取经
本文的2个重量级作者：
-
第一重量级作者 Mark（资深架构师，负责写初稿 ）
-
第二重量级作者 尼恩 （40岁老架构师， 负责提升此文的 技术高度，让大家有一种 俯视 技术的感觉）
《尼恩Java面试宝典》 是大家 面试的杀手锏， 此文当最新PDF版本，可以找43岁老架构师尼恩获取。
Mysql调优的重点目标： 避免锁表
锁表会带来一系列问题，影响数据库的性能和系统的稳定性。
主要是下面的四个问题： 性能问题、死锁问题、可用性问题、一致性问题
1. 锁表带来的性能问题
锁表会阻止其他事务对该表的并发访问，包括读操作和写操作。
锁表会导致严重的性能问题：
-
系统吞吐量下降：多个事务需要依次等待锁的释放，导致系统整体的吞吐量下降。
-
用户体验差：用户的请求需要长时间等待，导致响应时间变长，影响用户体验。
2. 锁表引发死锁
在高并发环境下，锁表更容易导致死锁的发生。
锁表会导致严重的事务问题：
-
事务回滚：部分事务被迫回滚，影响数据一致性。
-
系统复杂性增加：需要数据库管理系统介入来检测和解决死锁问题，增加系统复杂性。
3. 锁表降低系统可用性
长时间的表锁定会影响数据库的可用性，使得应用程序无法及时处理用户请求。
锁表带来的系统可用性问题：
-
功能不可用：系统的部分功能无法使用，影响用户的正常操作。
-
业务中断：在严重情况下，可能导致整个系统不可用，影响业务连续性。
4. 锁表引发数据一致性问题
在高并发写操作场景下，锁表会导致数据一致性问题。
锁表带来的数据一致性问题：
-
数据不一致：无法及时更新数据，影响业务逻辑的正确执行。
-
状态混乱：例如，订单状态更新时，如果表被锁定，其他更新操作无法及时进行，可能会导致数据状态不一致。
那么，什么情况下导致锁表？ 如何解决锁表的问题呢？
回顾一下mysql锁的分类
从操作的粒度可分为表级锁、行级锁和页级锁。
1.表级锁：
每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。
注意： 表级锁应用在MyISAM、InnoDB、BDB 等存储引擎中。
表锁的特点：
-
开销小，加锁快
-
不会出现死锁
-
锁定粒度大，发生锁冲突的概率最高，并发度最低
2.行级锁：
每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。
注意：行级锁应用在InnoDB 存储引擎中。MyISAM 没有行级锁
行锁的特点：
-
开销大，加锁慢
-
会出现死锁
-
锁定粒度小，发生锁冲突的概率最低，并发度最高
3.页级锁：
每次锁定相邻的一组记录，锁定粒度界于表锁和行锁之间，加锁开销和加锁时间界于表锁和行锁之间，并发度一般。
注意：页级锁 应用在BDB 等存储引擎中。
页锁的特点：
-
开销和加锁时间介于表锁和行锁之间
-
会出现死锁
-
锁定粒度介于表锁和行锁之间，并发度一般
MySQL 使用页级锁的情况相对较少，因为 MySQL 默认使用的是行级锁。
但在特定的情况下，MySQL 可能会使用页级锁，主要包括以下几种情况：
-
表级锁定转换为页级锁定： 当表级锁无法满足需求时，MySQL 可能会将表级锁转换为页级锁。这通常发生在使用 BDB 存储引擎时，因为 BDB 存储引擎默认使用表级锁。在某些情况下，MySQL 可能会将表级锁转换为页级锁，以提高并发性能。
-
隐式锁定大量行数据： 在某些情况下，如果一次性锁定了大量行数据，则 MySQL 可能会使用页级锁来减少锁的数量，从而减轻锁管理的负担。例如，在使用 BDB 存储引擎时，如果一次性锁定了大量行数据，则 MySQL 可能会将表级锁转换为页级锁。
-
DDL 操作： 在执行对表结构进行修改的 DDL（数据定义语言）操作时，MySQL 可能会使用页级锁来锁定整个表。例如，当执行 ALTER TABLE 操作时，MySQL 可能会锁定整个表的页，以防止其他对表结构的修改和查询操作。
总的来说，MySQL 使用页级锁的情况相对较少，因为页级锁通常会导致锁的粒度过大，影响并发性能。在设计数据库和应用程序时，通常会尽量避免使用页级锁，而是优先使用行级锁或其他更细粒度的锁。
以上内容比较重要，也比较复杂，
具体的视频讲解，请参见《尼恩Java面试宝典》配套视频。
InnoDB 存储引擎中的 表锁和行锁
对于 MySQL 来说，每种存储引擎都可以实现自己的锁策略和锁粒度，
比如 InnoDB 引擎支持行级锁和表级锁，不支持 页级锁
下面主要聚焦介绍 InnoDB 存储引擎中的两大锁：
-
表级锁
-
行级锁。
InnoDB 的表级锁
表锁，顾名思义就是对某个表加锁。
表级锁可以分为：表锁、元数据锁、意向锁三种。
表级锁之一：表锁
那什么时候会使用表锁呢？
一般情况是对应的存储引擎没有行级锁（例如：MyIASM），或者是对应的 SQL 语句没有匹配到索引。
对于第一种情况而言，因为对应存储引擎不支持行锁，所以只能是使用更粗粒度的锁来实现，这也比较好理解。
对于第二种情况而言，如果存储引擎支持行锁，但对应的 SQL 就没有使用索引，那么此时也是会全表扫描，那此时也是会使用表锁。
例如下面的语句没有指定查询列，或者指定了查询列但是并没有用到索引，那么也是会直接锁定整个表。
情况1：没有指定查询列
<section><br><span>select * from user; <br></span></section>
情况2：指定查询列，但是没有用到索引
<section><br><span>select * from user where name = 'zhangsan'; <br></span></section>
上面说的索引，其实是判断是否会用行级锁的关键。
表级锁之二：元数据锁
元数据，指的是我们的表结构这些元数据。
元数据锁（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 申请这张表的写锁就会失败。
以上内容比较重要，也比较复杂，
具体的视频讲解，请参见《尼恩Java面试宝典》配套视频。
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, ∞)，
’ fill=’%23FFFFFF’%3E%3Crect x=‘249’ y=‘126’ width=‘1’ height=‘1’%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E)
以上内容比较重要，也比较复杂，
具体的视频讲解，请参见《尼恩Java面试宝典》配套视频。
行级锁之三：临键锁(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在不同的场景中会退化:
’ fill=’%23FFFFFF’%3E%3Crect x=‘249’ y=‘126’ width=‘1’ height=‘1’%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E)
比如在 100、10000中，临键锁(Next-Key Locks)的可能有 (∞, 100]，(100, 10000] , 40岁老架构师尼恩提示，这里的关键是左开右闭
’ fill=’%23FFFFFF’%3E%3Crect x=‘249’ y=‘126’ width=‘1’ height=‘1’%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E)
在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 的时候才会释放锁，并且所有的锁是在同一时刻被释放。
以上内容比较重要，也比较复杂，
具体的视频讲解，请参见《尼恩Java面试宝典》配套视频。
InnoDB 如何加锁？从下面三个维度来分开介绍：
第1个维度：InnoDB 如何加意向锁?
它比较特殊，是由 InnoDB 存储引擎自己维护的，用户无法手动操作意向锁，
在为数据行加读写锁之前，InnoDB 会先获取该数据行所在在数据表的对应意向锁。
第2个维度：InnoDB如何加表级锁?：
1）隐式锁定：对于常见的 DDL 语句（如 ALTER、CREATE等），InnoDB 会自动给相应的表加表级锁
2）显示锁定：在执行 SQL 语句时，也可以明确显示指定对某个表进行加锁（lock table user read(write)）
<section><span>lock table user read; # 加表级读锁<br>unlock tables; # 释放表级锁<br><br></span></section>
第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 是主键索引：
<section><span>CREATE TABLE `test` (<br> `id` int(11) NOT NULL AUTO_INCREMENT,<br> `username` varchar(255) DEFAULT NULL,<br> PRIMARY KEY (`id`)<br>) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;<br>12345<br></span></section>
新建两个事务，先执行事务 T1 的前两行，也就是不要执行 rollback 也不要 commit：
’ fill=’%23FFFFFF’%3E%3Crect x=‘249’ y=‘126’ width=‘1’ height=‘1’%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E)
这个时候事务 T1 没有释放锁，并且由于索引失效事务 T1 其实是锁住了整张表，
此时再来执行事务 2，你会发现事务 T2 会卡住，最后超时关闭事务：
’ fill=’%23FFFFFF’%3E%3Crect x=‘249’ y=‘126’ width=‘1’ height=‘1’%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E)
回到面试题：什么情况下，MySQL会锁定整个表？
回到字节的面试题的核心：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条件未使用索引时，可能会导致全表扫描并锁定整个表。
因此我们需要在条件字段上加索引，从而将表锁变为行锁。
以上内容比较重要，也比较复杂，
具体的视频讲解，请参见《尼恩Java面试宝典》配套视频。
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会锁表的场景总结
总之，MVSQL在执行结构性修改、手动锁定、写操作、事务处理、索引操作、并发操作、加锁操作、以及在特定查询条件下索引失效，都可能需要对表进行锁定。
了解这些情况，有助于更好地进行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 中的锁表问题，提高数据库的并发性和性能。
技术自由圈几个 和MYSQL 有关的核心面试真题
上面的核心面试题，一定要掌握，背到滚瓜烂熟、滚瓜烂熟、滚瓜烂熟
更多的核心面试题，请参见5000页的鸿篇巨制 ：《尼恩Java面试宝典》
说在最后：有问题找老架构取经
MySQL表锁、行锁的相关知识： 如果大家能对答如流，如数家珍，基本上 面试官会被你 震惊到、吸引到。
最终，让面试官爱到 “不能自已、口水直流”。offer， 也就来了。
在面试之前，建议大家系统化的刷一波 5000页《尼恩Java面试宝典PDF》，里边有大量的大厂真题、面试难题、架构难题。很多小伙伴刷完后， 吊打面试官， 大厂横着走。
在刷题过程中，如果有啥问题，大家可以来 找 40岁老架构师尼恩交流。
另外，如果没有面试机会，可以找尼恩来改简历、做帮扶。
遇到职业难题，找老架构取经， 可以省去太多的折腾，省去太多的弯路。
尼恩指导了大量的小伙伴上岸，前段时间，刚指导一个40岁+被裁小伙伴，拿到了一个年薪100W的offer。
狠狠卷，实现 “offer自由” 很容易的， 前段时间一个武汉的跟着尼恩卷了2年的小伙伴， 在极度严寒/痛苦被裁的环境下， offer拿到手软， 实现真正的 “offer自由” 。
部分历史案例
-
**绝地逢生：**9年经验自考小伙伴，跟着尼恩狠卷3月硬核技术，面试机会爆表，2周后收3个offer ，满血复活
- 原文作者：知识铺
- 原文链接：https://index.zshipu.com/geek001/post/20240507/MySQL%E8%A1%A8%E9%94%81%E5%92%8C%E8%A1%8C%E9%94%81%E8%AF%A6%E8%A7%A3%E5%8F%8A%E5%A6%82%E4%BD%95%E9%81%BF%E5%85%8D%E9%94%81%E8%A1%A8--%E7%9F%A5%E8%AF%86%E9%93%BA/
- 版权声明：本作品采用知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议进行许可，非商业转载请注明出处（作者，原文链接），商业转载请联系作者获得授权。
- 免责声明：本页面内容均来源于站内编辑发布，部分信息来源互联网，并不意味着本站赞同其观点或者证实其内容的真实性，如涉及版权等问题，请立即联系客服进行更改或删除，保证您的合法权益。转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。也可以邮件至 sblig@126.com
