MySQL面试题汇总—进阶知识与性能优化

约束

MySQL有哪些约束类型？各自的业务作用是什么？如何选择合适的约束策略？

MySQL的约束类型包括主键约束、唯一约束、非空约束、外键约束和检查约束五种。

主键约束的业务作用是提供表的唯一标识，支持高效的数据检索和关联查询。唯一约束确保业务关键字段的唯一性，如用户邮箱、订单号等。非空约束保证关键业务数据的完整性，避免因空值导致的业务逻辑错误。外键约束维护数据一致性，确保关联数据的有效性。检查约束在数据库层面实现业务规则验证，提高数据质量。

选择合适的约束策略需要综合考虑业务需求、性能影响和维护成本。对于核心业务表，应该使用主键约束和必要的非空约束。对于需要唯一性验证的字段，使用唯一约束并配合索引优化。外键约束在数据一致性要求高的场景下使用，但需要考虑性能影响。检查约束适用于简单的业务规则验证，复杂逻辑建议在应用层处理。

外键约束还涉及到级联操作的概念

可以通过 ON DELETE 和 ON UPDATE 子句定义外键的级联行为
还包括 CASCADE、SET NULL、RESTRICT 等选项

CASCADE表示级联删除或更新，当父表记录被删除时，子表中引用该记录的行也会被自动删除。SET NULL表示将外键值设置为NULL，RESTRICT表示阻止删除操作。

主键约束会自动为主键列创建聚簇索引，表中的数据行会按照主键值的顺序物理存储，让基于主键的查询非常高效，而主键的选择策略直接影响表的性能

外键约束在两个表之间建立引用关系，当在子表中插入或更新数据时，MySQL会检查引用的父表记录是否存在，当删除或更新父表记录时，MySQL会检查是否违反了外键约束。这种检查机制虽然保证了数据完整性，但也会带来一定的性能开销。

MySQL 中 AUTO_INCREMENT 列达到最大值时会发生什么？

AUTO_INCREMENT 列必须是整数类型，就TINYINT，SMALLINT，INT，BIGINT 等，不同类型的最大值不同，达到最大值就会出现异常啊

自增列默认是有符号的，比如 INT 类型的自增列，最大值是 2147483647，而非无符号的 4294967295。

当自增列的值达到对应数据类型的最大值后，新插入数据时会触发主键冲突（Duplicate key）错误

普通插入

未指定自增列的值，然后自增列达到最大值后，还会按照自增约束去继续加一，但该值超出数据类型范围，且会与已存在的最大主键值的行产生主键冲突（主键唯一约束）
1
2
3
4
5
-- 最大值行
INSERT INTO test_auto_inc (id, name) VALUES (2147483647, 'max_value');

-- 再加就触发错误了
INSERT INTO test_auto_inc (name) VALUES ('new_data');
手动指定自增列值

如果手动插入的值超出数据类型最大值，直接触发「数值超出范围」错误
1
INSERT INTO test_auto_inc (id, name) VALUES (2147483648, 'manual');

自增列存在不回退的特性，即使删除了最大值行，自增列的「下一个值」也不会回退：

-- 删除最大值行
DELETE FROM test_auto_inc WHERE id = 2147483647;
-- 再次插入新数据
INSERT INTO test_auto_inc (name) VALUES ('after_delete');

仍会触发主键冲突错误，因为 MySQL 的自增计数器（AUTO_INCREMENT 计数器）会持久化在表结构中，删除数据不会重置计数器。

所以说，当计数器 + 1 超出数据类型范围时，MySQL 不会自动扩容，而是截断为该类型的最大值，导致与已有主键冲突；若手动插入超出范围的值，直接触发溢出错误。

排序

MySQL 中的数据排序是怎么实现的？

MySQL 排序数据有两条路：利用索引天然有序，或者自己动手做 Filesort。

用 EXPLAIN 看执行计划，如果 Extra 列没有 Using filesort，说明直接利用了索引排序。

EXPLAIN 里出现 Using filesort，说明 MySQL 自己动手排序了。这不一定是坏事，数据量小的时候内存排序也很快，但数据量大就会成为性能瓶颈。

如果 ORDER BY 的字段正好有索引，而且排序方向一致，MySQL 直接顺着 B+ 树叶子节点扫描就行，不用额外排序，效率非常高。

要是没命中索引，或者索引顺序不对，就得走 Filesort 文件排序了。这时候 MySQL 会用 sort_buffer 这个内存缓冲区来排序，它的大小由 sort_buffer_size 控制，默认 256KB。

数据量小就在内存里搞定，数据量大塞不下就得借助磁盘做外部排序，用归并算法把小块排好序的数据合并成大文件。

内存排序还分单路排序和双路排序两种模式：

单路排序：把所有 SELECT 字段都放进 sort_buffer 排，排完直接返回
双路排序：只放排序字段和行 ID，排好序之后再回表取其他字段

对于双路排序

如果 SELECT 的字段总长超过 max_length_for_sort_data，为了省空间，sort_buffer 只放排序字段和 row_id。

比如执行这条 SQL：
1
SELECT a, b, c FROM t1 WHERE a = '111' ORDER BY b;
假设 a、b、c 三列数据长度加起来超过了阈值，只会把 id 和 b 这两列放到 soft_buffer 中来排序，排序完成后，再通过 id 回表查询拿到 a、b、c，最终把结果集返回给客户端。

这个过程需要两次数据访问，所以叫双路排序。早期 MySQL 只有这种方式，后来才优化出了单路排序。

对于单路排序

如果 SELECT 字段总长没超过 max_length_for_sort_data，MySQL 会把所有字段都放进 sort_buffer：

排序后直接就是完整结果集，返回给客户端就行，不用再回表。相比双路排序少了一次 IO，效率更高。

但单路排序占用内存大，如果 sort_buffer 装不下就会溢出到磁盘，反而变慢。所以不是无脑用单路就好。

字段少用单路快，字段多可能双路更合适。

所以说，强行用单路排序一定比双路排序快吗？

不一定。单路排序省了回表那次 IO，但它把所有字段都放进 sort_buffer，占用内存大。如果 SELECT 字段很多，sort_buffer 装不下，数据就得溢出到磁盘做外部排序，这时候可能反而比双路排序慢。

所以字段少用单路快，字段多可能双路更合适。

ORDER BY 和 GROUP BY 的排序有什么区别？

索引

简单说一下 MySQL 索引的机制，类型有哪些？

MySQL 可以按照四个角度来分类索引

按数据结构分类：B+tree 索引、Hash 索引、全文索引
按物理存储分类：聚簇索引（主键索引）、二级索引（辅助索引）。
按字段特性分类：主键索引、唯一索引、普通索引、前缀索引。
按字段个数分类：单列索引、联合索引。

然后是这些索引的特点

按数据结构分类

每种存储引擎支持的索引类型不一定相同，表中总结了 MySQL 常见的存储引擎 InnoDB、MyISAM 和 Memory 分别支持的索引类型
按物理存储分类
- 聚簇索引即索引结构和数据一起存放的索引，对于 InnoDB，索引的非叶子节点存储索引，叶子节点存储索引和索引对应的实际数据
- 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。
在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这就是覆盖索引

如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，然后就能查到数据了，这就是回表

创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引
- 如果有主键，默认会使用主键作为聚簇索引的索引键
- 如果没有主键，就选择第一个不包含NULL值的唯一列作为聚簇索引的索引键
- 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键
其它索引都属于二级索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。
按字段特性分类：

主键索引就是建立在主键字段上的索引，一张表最多一个，不允许 NULL

唯一索引是建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许可重复的出现NULL

普通索引就是建立在普通字段上的索引

前缀索引只能给字符串类型来使用，因为它是对文本的前几个字符创建索引
按字段个数分类
- 建立在单列上的索引称为单列索引，比如主键索引；
- 通过将多个字段组合成一个索引，该索引就被称为联合索引
  
  最左匹配原则，也就是按照最左优先的方式进行索引的匹配。
  
  联合索引的最左匹配原则会一直向右匹配直到遇到「范围查询」就会停止匹配。
  
  范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引

有无排查索引失效的经验，展开讲讲？

索引失效的常见情况包括对索引进行函数操作、发生了隐式数据类型转换、违反最左前缀原则、OR 条件查询、NOT IN/NOT EXISTS、LIKE左模糊匹配等。

说一下这两个，OR条件无法有效利用单一索引，LIKE ’%keyword’无法确定搜索起点。

可以从 Arthas 入手定位性能瓶颈，发现某个请求非常慢，用dashboard命令查看当前 JVM 的线程状态、CPU / 内存占用，可以定位到执行该接口的业务线程处于阻塞状态，判定性能瓶颈在 IO 层，使用 Arthas 的 trace 命令分析得出调用持久层的方法耗时高，考虑是不是数据库部分出现了慢 SQL

使用 EXPLAIN 来查看 SQL 的执行计划，通过执行计划分析，判断SQL是否走了索引，如果没有走索引，就代表索引发生了失效

EXPLAIN 执行计划支持 SELECT、DELETE、INSERT、REPLACE 以及 UPDATE 语句。我们一般多用于分析 SELECT 查询语句，使用起来非常简单，语法如下：
1
EXPLAIN + SELECT 查询语句；

对于 EXPLAIN 的执行输出，可以看到：

type：表示数据扫描类型
select_type：SELECT 关键字对应的查询类型
possible_keys：字段表示可能用到的索引
key：字段表示实际用的索引，如果这一项为NULL，说明没有使用索引
key_len：表示索引的长度
rows：表示扫描的数据行数
filtered：按表条件过滤后，留存记录的百分比

select_type 查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询，常见的值有

SIMPLE：简单查询，不包含 UNION 或者子查询
PRIMARY：查询中如果包含子查询或其他部分，外层的 SELECT 将被标记为 PRIMARY。
SUBQUERY：子查询中的第一个 SELECT。
UNION：在 UNION 语句中，UNION 之后出现的 SELECT。
DERIVED：在 FROM 中出现的子查询将被标记为 DERIVED。
UNION RESULT：UNION 查询的结果。

type 字段就是描述找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为

ALL：全表扫描，最坏的情况
index：全索引扫描，虽然 index 和 all 差不多，只不过 index 对索引表进行了全扫描，虽然索引有序，不再需要对数据进行排序，但是开销依然很大。
range：索引范围扫描，一般在 where 子句中使用 < 、>、in、between 等关键词，只检索给定范围的行，属于范围查找。从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式
ref：非唯一索引，或者是唯一索引的非唯一性前缀，返回数据返回可能是多条。因为虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用了索引进行快速查询，找到了第一条数据后依旧不能停止，要进行目标值附近的小范围扫描，但是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描
eq_ref：唯一索引扫描，使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref
const：结果只有一条的主键或唯一索引扫描，表示使用了主键或者唯一索引与常量值进行比较，比如 select name from product where id=1。需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，const 是与常量进行比较，查询效率会更快，而 eq_ref 更多出现在多表联查中
system：如果表使用的引擎对于表行数统计是精确的，且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。

extra 显示的结果也比较重要：

Using filesort ：当查询语句中包含 group by 操作，而且无法利用索引完成排序操作的时候，不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。
Using temporary：使了用临时表保存中间结果，常见于 ORDER BY 和 GROUP BY。效率低，要避免这种问题的出现。
Using index：所需数据只需在索引即可全部获得，不须要再到表中取数据，也就是使用了覆盖索引，避免了回表操作，效率很好。
Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。

对于 rows：MySQL 预估的扫描数据行数，该值越接近实际返回行数，说明执行计划越优

索引失效的场景有哪些

我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这种都会造成索引失效
当我们在查询条件中对索引使用函数，也会导致索引失效
当我们在查询条件中对索引列进行表达式计算，也会导致索引失效
数据类型出现隐式转换的时候不会命中索引，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数。所以说特别是当列类型是字符串，一定要将字符常量值用引号引起来。
联合索引要正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效

MySQL有哪些索引类型？如何正确设计索引？

MySQL的索引类型包括主键索引、唯一索引、普通索引、全文索引、空间索引等。

主键索引是聚簇索引，索引和数据存储在一起，不用回表，性能最优

唯一索引保证字段值唯一性，支持NULL值。唯一约束就是隐式建立了一个唯一索引。

普通索引是最基础的索引类型，允许重复值。

索引按存储结构分为聚簇索引和非聚簇索引。聚簇索引决定表的物理存储顺序，一个表只能有一个聚簇索引，通常是主键。非聚簇索引通过主键指向数据行，查询时需要回表操作。。InnoDB的二级索引都是非聚簇索引。

正确设计索引需要遵循选择性原则、最左前缀原则、覆盖索引原则。

首先要考虑是否有需要建立索引，而不是建完索引等用到的时候再用，因为索引本身有开销，并降低写操作的性能，不要造成过多的索引列
选择合适的字段创建索引
- 一般情况下，只给查询频率高、选择性高的列建索引，如果一个列中大部分的内容都一样，建索引反而会降低性能
- 创建索引的字段应该是查询操作非常频繁的字段，避免给频繁写入的列建索引，维护索引的成本也是不小的
- 最少空间原则，创建索引的关键字要尽可能占用空间小，例如对备注字段做索引就不如对 id 做
- 索引字段的数据应该尽量不为 NULL
- 被作为 WHERE 条件查询的字段，应该被考虑建立索引，这样就能有效实践索引下推
- 频繁需要排序的字段需要建立索引，索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
- 被经常频繁用于连接的字段，可以考虑建立索引，提高多表连接查询的效率
优先使用联合索引，一定要注意最左前缀原则，避免索引失效，组合索引列顺序一般是选择性高的列在前，查询频率高的列在前
正确选择索引类型

事务

事务的 ACID 特性是什么？各自的含义理解吗？和 Java 的线程安全有什么关系？

事务（Transaction）就是一组操作，要么全部成功，要么全部失败。

Atomicity 原子性

事务是不可分割的最小单位。那么，上述特性实际上就是原子性的体现。
Consistency 一致性

事务执行前后，数据的完整性约束不变。也就是说，数据永远合法、合理。
Isolation 隔离性

多个事务同时运行，互相看不见对方的中间状态，大家互不干扰
Durability 持久性

事务一旦提交，结果永久保存，事务提交的影响永远存在。

ACID：原子（全做 / 全不做）、一致（数据合法）、隔离（互不干扰）、持久（提交不丢）。

事务和 Java 线程安全的关系

事务的 ACID 是数据库层面保障多事务并发时数据正确的规则；

Java 线程安全是应用程序层面保障多线程并发访问共享资源时数据正确的规则。

都是解决的是并发竞争问题，本质都是多个执行流同时操作共享数据，只不过解决层面和手段完全按不同，在 Java 项目中，并发事务安全和线程安全都需要保证才能保证端到端的数据正确。

介绍一下事务的三种并发问题？

下面三个都是多个事务同时操作数据库时才会出现的问题

脏读

一个事务读到了另一个事务还没有提交的数据
- 万一对方后来回滚了，你读到的就是假数据、脏数据。
  
  A 给 B 转 100，还没提交。
  
  B 查到余额多了 100（读到了未提交数据）。
  
  结果 A 回滚，不转了。
  
  → B 认为自己200块，读到的就是脏数据。
不可重复读

同一个事务内，两次读同一行数据，结果不一样。
- 同一个事务里，读同一行变了，这就是不可重复读。
  
  事务 1：第一次读余额 = 100
  
  事务 2：把余额改成 200 并提交
  
  事务 1：再读余额 = 200
幻读

同一个事务内，两次查询的结果条数不一样。
- 因为别的事务插入 / 删除了数据。
  
  事务 1：第一次查 “年龄 = 18” → 10 条
  
  事务 2：插入一条年龄 = 18 的数据并提交
  
  事务 1：再查 → 11 条
  
  → 突然多了或少了的幻觉，是幻读。

事务的隔离级别？各隔离级别解决了什么问题？它们都是如何实现的？

SQL 标准定义了四种事务隔离级别，用来平衡事务的隔离性（Isolation）和并发性能。级别越高，数据一致性越好，但并发性能可能越低。这四个级别是：

read-uncommitted读未提交

最低的隔离级别，如字面意思，允许读取事务尚未提交的数据，可能会导致脏读、幻读或不可重复读。

这种级别在实际应用中很少使用，因为它对数据一致性的保证非常弱。
read-committed 读已提交

允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

这是大多数数据库（如 Oracle, SQL Server）的默认隔离级别。
repeatable-read可重复读

对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

这是 MySQL InnoDB 存储引擎的默认隔离级别。并且，InnoDB 在此级别下通过 MVCC（多版本并发控制）和 Next-Key Locks（间隙锁+行锁）机制，在很大程度上解决了幻读问题。
serializable 可串行化

最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务串行执行，这样事务之间就完全不可能产生干扰，该级别可以防止脏读、不可重复读以及幻读。

标准的 SQL 隔离级别定义里，repeatable-read 是无法防止幻读的，但 InnoDB 的实现通过以下机制很大程度上避免了幻读，但是也没用办法完全避免

快照读：普通的 SELECT 语句，通过 MVCC 机制，事务启动时创建一个数据快照，后续的快照读都读取这个版本的数据，从而避免了看到其他事务新插入的行（幻读）或修改的行（不可重复读）

当前读：像 SELECT ... FOR UPDATEX锁，SELECT ... LOCK IN SHARE MODES锁，INSERT，UPDATE，DELETE 这些操作。InnoDB 使用 Next-Key Lock 来锁定扫描到的索引记录及其间的间隙，防止其他事务在这个间隙内插入新的记录，造成幻读。

Next-Key Lock 是行锁（Record Lock）和间隙锁（Gap Lock）的组合。

值得注意的是，虽然通常认为隔离级别越高、并发性越差，但 InnoDB 存储引擎通过 MVCC 机制优化了repeatable-read级别。对于许多常见的只读或读多写少的场景，其性能和read-committed差不多，不过，在写密集型且并发冲突较高的场景下，RR 的间隙锁机制可能会比 RC 带来更多的锁等待。

此外，在某些特定场景下，如需要严格一致性的分布式事务，例如XA Transactions，InnoDB 要求存储引擎的事务隔离级别必须设置为 serializable

serializable 隔离级别是通过锁来实现的，read-committed 和 repeatable-read 隔离级别是基于 MVCC 实现的。不过， serializable 之外的其他隔离级别可能也需要用到锁机制，就比如repeatable-read在当前读情况下需要使用加锁读来保证不会出现幻读。

日志

什么是redo log？redo log如何保证事务的持久性？

为什么需要 undo log？说说 Undo Log 的作用？Undo Log 如何实现的事务回滚和 MVCC

锁

乐观锁和悲观锁的实现方式？有什么区别？项目中如何选择？

悲观锁很悲观，认为每次并发读写都会出现冲突，因此在操作数据前先获取锁，确保独占访问。MySQL的SELECT FOR UPDATE就是悲观锁的典型实现，Java 层面就是synchronized、ReentrantLock，适用于冲突频繁的写密集场景。优点是数据一致性强，缺点是可能导致性能瓶颈和死锁。

乐观锁和乐观，认为并发冲突不会发生，不会预先加锁，而是在提交时检查数据是否被其他事务修改过。MySQL侧常通过版本号或时间戳机制实现，如果检测到冲突则重试。乐观锁无锁、无阻塞、性能高，适用于读多写少、冲突较少的场景，能够提供更好的并发性能。但是更新失败需要重试，别用错地方。

在项目里，我会根据并发冲突概率、一致性要求、性能压力来选择锁

比如我在xxxx项目管理平台中，用户数据统计，报表生成的时候使用的就是版本号乐观锁，而状态流转、协作文档编辑同一处，任务分配就可能会使用悲观锁 SELECT ... FOR UPDATE 加行锁，确保同一时间只有一个事务修改

理解当前读和快照读吗？当前读和快照读有什么区别？

行锁和表锁的区别？什么情况下行锁会升级为表锁？

了解共享锁和排他锁吗？介绍一下它们及其使用？

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：

共享锁 S锁：又称读锁，用于事务只读操作，事务在读取记录的时候获取共享锁，允许多个事务同时持有同一资源的 S 锁
排他锁 X锁：又称写锁/独占锁，用于事务修改操作，在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁。同一资源同一时间只能有一个事务持有 X 锁

这是不是很类似于ReentrantReadWriteLock的锁，读共享，写互斥，而且它也是读读兼容，读写写写互斥

而且就算重入性，ReentrantReadWriteLock支持重入，InnoDB 的行锁也支持重入

而且ReentrantReadWriteLock需要手动释放，数据库 S/X 锁需要事务提交 / 回滚来自动释放

排他锁就是互斥锁，与任何的锁都不兼容，共享锁仅和共享锁兼容。

由于 MVCC 的存在，对于一般的 SELECT 语句，InnoDB 不会加任何锁。不过，你可以通过以下语句显式加共享锁或排他锁。

共享锁（S 锁）的使用
1
SELECT ... LOCK IN SHARE MODE;

排他锁（X 锁）的使用

-- 修改操作自动加
-- INSERT/UPDATE/DELETE 语句会自动为涉及的行加 X 锁，无需显式声明

-- 显式加
-- 意思是读取数据并准备修改，提前锁定数据防止其他事务读写
SELECT ... FOR UPDATE

除了行级锁，表级锁也有 S/X 锁，这是 MyISAM 引擎那边常用的了，不多说

意向锁有什么作用？讲讲你对它的理解？

如果需要用到表锁的话，如何判断表中的记录没有行锁呢，一行一行遍历肯定是不行，性能太差。

我们需要用到意向锁来快速判断是否可以对某个表使用表锁。意向锁的作用就是提前标记表的锁定意图，让表级锁的兼容性检查从遍历所有行简化为检查表级意向锁，大幅提升锁检查效率。

意向锁是表级锁，共有两种：

意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InnoDB 会先获取该数据行所在的数据表中对应意向锁。

它们之间的兼容性是这样的：

意向锁之间是互相兼容的，IS 和 IX 可以共存
意向锁与表级锁的兼容规则：
- 表级 S 锁 ↔︎ IS 锁：兼容；IX 锁：互斥；
- 表级 X 锁 ↔︎ IS/IX 锁：都互斥；
意向锁不影响行级锁的加锁（行级锁仍遵循 S/X 锁的兼容规则）。

虽然意向锁是 InnoDB 自动加 / 释放的，但是需要注意它对性能的影响

长事务持有 IX 锁，会导致表级锁阻塞

比如一个事务执行UPDATE t SET ... WHERE id=1后未提交，会一直持有表的 IX 锁；此时其他事务想加表级 S/X 锁（如LOCK TABLES t READ/WRITE）会被阻塞。
意向锁升级导致锁冲突

若 DML 修改语句没有命中索引，InnoDB 会扫全表，然后给表中所有行加 X 锁，同时表级 IX 锁升级为表级 X 锁，导致全表阻塞。

主从节点

MySQL主从复制有哪些延迟问题？如何解决数据延迟？

MySQL主库故障如何处理？故障切换的方案有哪些？

性能优化

能不能介绍一下 MySQL 性能如何优化？思路是什么？

MySQL 性能优化是一个系统性工程，涉及多个方面，从核心问题入手，再逐步扩展

慢 SQL 定位与分析

性能优化的第一步永远是找到瓶颈。建议先从 慢 SQL 定位和分析 入手
1. 使用监控工具：
  - 慢 SQL 监控工具：主要就算 MySQL 慢查询日志slow_query_log = ON，而且直接看原始日志可读性差，可以使用工具解析mysqldumpslow，pt-query-digest
  - 实时监控工具：Navicat 自带慢 SQL 监控模块，而且 MySQL 自带 SHOW PROCESSLIST实时查看当前运行的 SQL
2. EXPLAIN 命令：找到慢 SQL 后，需要查看执行计划，分析其中索引使用情况，锁的情况，事务的情况等内容，搞清楚为什么慢，然后对症下药
索引、表结构和 SQL 优化

定位到慢 SQL 后，接下来就要针对具体问题进行优化。
1. 索引优化：
  
  首先，查看建索引的列是否合理，是不是高频查询、高区分度的字段
  
  然后关注索引是不是失效，联合索引的使用是不是遵守最左前缀原则，有没有出现了索引失效的问题
  
  然后可以关注覆盖索引的使用情况，查询的列是不是都在索引中，这样无需回表
2. 表结构优化
  
  表结构优化核心是字段最小化、拆分大字段、合理主键、适度反范式；
  
  表结构设计不合理，索引和 SQL 优化再到位也难根治问题，可以从以下方面入手
  1. 字段类型优化：符合需求的情况下，优先选占用空间小的类型
  2. 表结构设计优化：拆分宽表，也就是字段过多的表。拆分大字段，拆分到子表。而且可以根据业务的情况适当反范式化，适度冗余字段，减少 JOIN。然后避免用 UUID 作为主键。比较关键的还有避免 NULL 值
3. SQL 优化
  
  SQL 优化核心原则是：减少扫描、减少计算、减少 IO。
  1. 杜绝SELECT *，只查需要的列
  2. 优化 JOIN 查询：控制 JOIN 表数量，不超过 3 张；小表 JOIN 大表，减少循环次数；
  3. 优化分页查询：关注是不是出现了深度分页的问题，考虑延迟关联和基于上一页最后一条数据的 ID 分页来解决，或者就是调整业务
  4. 批量操作代替循环单条操作：批量插入能减少网络交互和事务开销
  5. 子查询优化：MySQL 对子查询优化较差，建议改为 JOIN
  6. 事务与锁优化：控制事务粒度，只包含必要的 SQL，避免长事务，因为长事务会持有锁，导致其他 SQL 阻塞。
架构优化

当如果有必要的时候，数据库已经在上述的尽量优化后依旧陷入了瓶颈，就需要考虑新的架构了
1. 读写分离
  
  基于读多写少的业务特征，将数据库拆分为主库（Master） 和从库（Slave），主库负责所有写操作加上核心读操作，从库负责绝大部分读操作，主从通过 binlog 同步数据，保证数据一致性。
2. 分库分表
  
  当单库 QPS 超 10 万或者单表数据量超过 1000 万行，查询性能会明显下降，需分表，可以水平分表，可以垂直分表，然后分表后需通过中间件管理例如Sharding-JDBC，避免业务代码耦合
  
  对于分库也有垂直分库（按业务拆分）和水平分库（按数据分片），分库也应该通过中间件管理分片逻辑
3. 冷热分离
  
  根据数据访问频率将数据分为
  - 热数据：近期、高频访问的数据（如近 3 个月订单）；
  - 冷数据：远期、低频访问的数据（如 3 个月前订单）；
  将热数据存于高性能存储（SSD + 主库），冷数据存于低成本存储（HDD + 从库 / 归档库），降低核心存储压力。
4. 缓存
  
  缓存是最低成本、最高效的架构优化手段，核心是将高频访问数据从数据库拉到内存，减少数据库 IO 压力。一般来说，Caffeine+Redis多级缓存是比较常用的，但是需要注意Redis的缓存核心问题
其他层次的优化手段
1. Java 服务层优化：
  
  Java 服务作为数据库的直接调用方，其代码质量、线程模型、请求处理逻辑直接决定了数据库的压力大小，核心优化思路是：减少无效请求、控制并发数、优化调用方式。
  1. 连接池优化：数据库连接是稀缺资源，不合理的连接池配置会导致连接耗尽，请求需要排队，最大连接数不是越大越好，压测时观察show processlist，若大量连接处于Sleep状态，说明最大连接数设高了；若频繁出现Waiting for connection，说明连接数不足。
  2. ORM使用懒加载或者避免全表扫描的 Mapper 写法
2. 使用中间件
  1. 使用消息队列：数据库的瞬时高并发是性能瓶颈的主要诱因，MQ 能将同步请求转为异步处理，平滑数据库压力。
  2. MySQL 不擅长复杂的模糊查询、多维度筛选，这类查询会导致全表扫描或复杂 JOIN，用 Elasticsearch 替代
3. 最后是基础设施层也要跟上
  
  提升硬件性能也是优化的重要手段之一。使用高性能服务器、增加内存、使用 SSD 硬盘等硬件升级，都可以有效提升数据库的整体性能。

MySQL 分页有什么性能问题？对于这种深度分页问题怎么优化？

为了实现分页，我们可以使用 LIMIT 关键字

-- 基础分页：第page页，每页size条
SELECT * FROM table_name 
WHERE [条件] 
ORDER BY [排序字段] 
LIMIT 开始, size;

-- 第1页，每页10条就是如下
SELECT * FROM user ORDER BY id DESC LIMIT 0, 10;
-- 第2000页，每页10条就是如下
SELECT * FROM user ORDER BY id DESC LIMIT 1990，10；

那么问题来了。同样都是拿10条数据，查第1页和第2000页的查询速度是一样的吗？为什么？

上面的两种查询方式。对应limit offset, size 和 limit size 两种方式。而其实 limit size ，相当于limit 0, size。也就是从 0 开始取size条数据，两种方式的区别在于offset是否为0。

MySQL 执行 LIMIT offset, rows 的底层逻辑具体是

server 层会在 InnoDB 里的主键索引中获取到第 0 到（offset + size）条完整行数据，返回给 server 层之后根据 offset 的值挨个抛弃，最后只留下最后面的 size 条，放到 server 层的结果集中，返回给客户端。

所以，当 offset 非 0 时，server层会从引擎层获取到很多无用的需要丢弃的数据，而获取的这些无用数据都是要耗时的。

可知，拷贝完整数据跟只拷贝行数据里的其中一两个列字段耗时是不同的，前面的数据都是不要的，所以完全可以只查 ID，可以将sql语句修改成下面这样。

1
2

SELECT * FROM page WHERE id >= (SELECT id FROM page ORDER BY id LIMIT 60000,1) ORDER BY id limit 10;
-- 在innodb中的主键索引中获取到6000000+1条数据，然后server层会抛弃前6000000条，只保留最后一条数据的id。

上面提到的是主键索引的执行过程，我们再来看下基于非主键索引的 limit 执行过程。

比如下面的sql语句

1	SELECT * FROM page ORDER BY user_name LIMIT 0,10;

完整的执行流程差不多，只不过是聚簇索引变成了二级索引，server层会调用innodb的接口，在innodb里的非主键索引中获取到第0到9条数据对应的主键id后，回表到主键索引中找到完整的对应数据，返回给server层，server层将其放到结果集中，返回给客户端。对于 offset 的抛弃也是一样的。

也就是说，非主键索引的limit过程，比主键索引的limit过程，多了个回表的消耗。

当offset变得非常大时，百万级别那种，优化器判断此时使用索引回表简直是累赘，不如全表扫描，因此，当 limit offset 过大时，非主键索引查询非常容易变成全表扫描，真是性能杀手。

这种情况也能通过上述方式去优化。

1	SELECT * FROM page t1,(SELECT id FROM page ORDER BY user_name LIMIT 60000,100) t2 WHERE t1.id = t2.id;

通过SELECT id FROM page ORDER BY user_name LIMIT 60000,100，先走 InnoDB 层的 user_name 非主键索引取出 id，因为只拿 id，不用回表，然后返回server层之后，同样抛弃前6w条数据，保留最后的100个id。

然后再用这100个 id 去跟 t1 表做 id 匹配，此时走的是主键索引，将匹配到的100条行数据返回。这样就绕开了之前的600w条数据的回表。

这里就产生了个专门的术语，叫深度分页。

目前不管是mysql还是es都没有很好的方法去解决这个问题。只能通过限制查询数量或分批获取的方式进行规避。

如果你希望获取到所有的数据然后去处理一些内容，那么你通过limit offset size分页就会出现深度分页问题。这种情况下我们可以将所有的数据根据id主键进行排序，然后分批次取，将当前批次的最大id作为下次筛选的条件进行查询。这样查询性能比较稳定

遇到深度分页的问题，多思考其原始需求，从需求设计上规避这个问题，例如你给用户展示信息，用户看到百千页以后的情况非常少，除非是排行榜想看看自己在哪，这样你就可以限制跳页，控制展示的结果数，或者只支持上一页或下一页。

分页查询、排序、模糊查询如何进行索引优化？

分页查询的索引优化需要避免传统LIMIT offset, count，在大偏移量时的性能问题。一般是上面提到的游标分页（基于ID范围），基于上一页最大ID，延迟关联（先查ID再关联详细信息），子查询优化等。对于ORDER BY分页，确保排序字段有索引，避免Using filesort。

排序查询要充分利用索引的有序性避免额外排序操作。当ORDER BY字段顺序与索引字段顺序一致时，可以直接利用索引顺序。复合索引排序需要遵循最左前缀原则，等值查询字段在前，排序字段在后。对于多字段排序，索引字段顺序要与ORDER BY完全匹配才能避免filesort。

模糊查询的索引优化分情况处理。右模糊查询（LIKE ‘prefix%’）可以有效使用索引，左模糊查询（LIKE ‘%suffix’）需要使用全文索引或反向索引。对于复杂的文本搜索需求，建议使用ElasticSearch等专门的搜索引擎，或者通过ngram分词、关键词提取等技术进行优化。