芮日志 个人写作与技术笔记

面试题库

MySQL 面试题

三范式、SQL 执行过程、索引、事务、锁、优化,共 107 题。

  • Java
  • MySQL
  • 面试

MySQL 面试题

来源：resource/御码IT教育-Java+Python智能体-MySQL+前端+场景+人事.pdf

一、MySQL

1、MySQL基础

1、数据库三范式是什么

①第一范式（1NF）：每一列都是最小单元，字段不可再分，反例id_name = ’ 1_zhangsan’ ②第二范式（2NF）：满足1N，不能只依赖主键的一部分，如果主键是联合主键（多个字段），其他字 段不能只依赖其中一部分主键。 ③第三范式（3NF）：满足2NF，消除传递依赖，非主键字段之间不能互相推导。反例：订单表里存用 户ID、用户名、用户地址，因为用户名可通过用户 ID 查到

2、一条SQL的执行过程是怎样的

一条 SQL 的执行过程可以大致分为以下几个步骤： ①建立连接：Java 客户端通过 JDBC 连接 MySQL，连接器验证客户端的身份和权限，确保用户有足够 的权限执行该 SQL 语句 ②查询缓存（Mysql 8.0已移除忽略）：连接器首先检查查询缓存，如果在缓存中找到匹配的结果，查 询缓存直接返回结果，避免了后续的执行过程 ③解析器：若查询不命中缓存，连接器将 SQL 语句传递给分析器，检查 SQL 语法是否正确，把 SQL 拆 成语法树 ④预处理器：检查表、字段是否存在，检查权限 ⑤优化器：决定用哪个索引，选择成本最低的执行方案。 ⑥执行器：调用存储引擎接口，读取数据 / 写入数据。 ⑦存储引擎：数据存储、检索和修改操作，从磁盘或内存中读取或写入相关数据。 ⑧返回结果：最后，连接器将结果发送回客户端，完成整个执行过程。 连接→解析→预处理→优化→执行→引擎取数→返回结果

3、对MySQL数据库去重的关键字是什么？

DISTINCT去掉重复

另一种去重关键字：GROUP BY 适合需要聚合统计的场景 — 单列去重

SELECTDISTINCT name FROMuser;

— 多列去重

SELECTDISTINCT name, age FROMuser;

4、MySQL的约束有哪些？

一句话速记：主键、非空、唯一、外键、默认、检查

① PRIMARY KEY: 约束字段非空+唯一，一张表只能有一个主键。 ② NOT NULL: 字段不能为 NULL，必须填值。 ③ UNIQUE: 字段值不能重复，可以为 NULL，一个表允许有多个Unique约束。 ④ FOREIGN KEY: 建立表与表之间的关联，保证数据引用完整性。 ⑤ DEFAULT：保存数据时，如果未指定该字段的值，则采用默认值 ⑥ CHECK: MySQL 8.0 正式支持，用于控制字段的值范围。

5、MySQL多表连接有哪些方式？有什么区别？

区别： Inner join 内连接，两边都有才显示 left join 在两张表进行连接查询时，左表全有，右表匹配才有。 right join 在两张表进行连接查询时，右表全有，左表匹配才有。 全外连接：两张表数据都保留

6、UNION和UNION ALL的区别？

都是合并结果集 去重：UNION自动去重，UNION ALL不去重 排序：UNION默认排序，UNION ALL 不排序 性能：UNION ALL 更快 — 左连接：

SELECTcolumnsFROM table1 LEFTJOIN table2 ON table1.common_field=
table2.common_field;

— 右连接：

SELECTcolumnsFROM table1 RIGHTJOIN table2 ON table1.common_field=
table2.common_field;

— 内连接：

SELECTcolumnsFROM table1 INNERJOIN table2 ON table1.common_field=
table2.common_field;
--全外连接(mysql暂时还不支持full join)：
SELECTcolumnsFROM table1 LEFTJOIN table2 ON table1.common_field=
table2.common_field
UNION
SELECTcolumnsFROM table2 LEFTJOIN table1 ON table2.common_field=
table1.common_field;

7、truncate、delete、drop的区别

TRUNCATE：清空表、不可回滚、重置自动增长。 DELETE：删数据、可回滚、可条件、不清除自动增长。 DROP：删表跑路，啥都没了。

8、mysql 中in 和exists 的区别

一句话背答案：子表小用 IN，子表大用 EXISTS。

执行逻辑： IN：先查子查询，得到结果集，再匹配主表 EXISTS：先查主表，再循环去子查询里判断是否存在 性能关键（MySQL 8.0 优化器很强，很多时候会自动优化，差别不大）： 子表小、主表大→用IN 主表小、子表大→用EXISTS 特点： IN：适合等值匹配 EXISTS：返回true/false，只判断存在与否，效率更高

9、MySQL 记录货币用什么字段类型

一句话背答案：存货币用 DECIMAL，绝对不用 float/double（会精度丢失，算错钱）。

10、CHAR 和 VARCHAR 的区别？

CHAR和VARCHAR的区别可以总结如下：

  1. 存储方式：CHAR是固定长度，而VARCHAR是可变长度。
  2. 占用空间：CHAR不管内容长短，都占定义的长度，而VARCHAR只占实际内容长度 + 1~2 字节。
  3. 尾随空格：CHAR查询时会自动去掉末尾空格，而VARCHAR保留末尾空格。
  4. 访问效率：由于CHAR查询快，碎片少。VARCHAR省空间，但稍慢。 一句话记忆：CHAR 定长快，VARCHAR 变长省空间。

11、count(1)、count(*)与count(列名) 的区别?

一句话总结：count (*) 和 count (1) 算总行数，效率高；count (列名) 不算 NULL，效率低。

12、MySQL常用函数

数学 count,sum,avg,round,abs,rand 类型转换 cast 字符串 concat,date_format,length,replace,locate 日期 curdate,curtime,now(),month,year 逻辑函数 if,case,ifnull

13、常用窗口函数

1.排序窗口函数 ① ROW_NUMBER()：行号，连续不重复：1,2,3,4… ② RANK()：并列排名，跳号：1,1,3,4… ③ DENSE_RANK()：并列排名，不跳号：1,1,2,3… 2.取值窗口函数 ① LAG (列名，N): 取上 N 行的数据 ② LEAD (列名，N): 取下 N 行的数据 ③ FIRST_VALUE (列名): 分组内第一行的值 ④ LAST_VALUE (列名): 分组内最后一行的值 3.聚合窗口函数 ① SUM() OVER() ② AVG() OVER() ③ COUNT() OVER() ④ MAX() OVER() ⑤ MIN() OVER() 一句话区别 ROW_NUMBER：纯行号，不并列 RANK：并列会跳号 DENSE_RANK：并列不跳号 LAG/LEAD：取上下行 聚合 + OVER：边分组边计算，不合并行

14、什么是存储过程？有哪些优缺点？

存储过程，就是一些编译好了的SQL语句，这些SQL语句代码像一个方法实现一些功能（对单表或多表 的增删改查），然后给这些代码块取一个名字，在用到这个功能的时候调用即可。 优点：速度快，减少网络传输，复用性强，安全

函数() OVER (
PARTITIONBY分组字段
ORDERBY排序字段
)

缺点：难维护，移植性差，不适合复杂逻辑

15、redo-log,undo-log,bin-log日志

redo log（重做日志）宕机重启后，把没刷盘的数据重做回来 undo log（回滚日志）想反悔？用 undo 恢复原来的数据 binlog（归档日志）数据丢了？用 binlog 恢复；主从同步靠它

1. Redo Log（重做日志）

1.1 介绍与作用

Redo Log记录了对InnoDB存储引擎中数据页修改的物理操作。它的主要目的是确保事务的持久性，即 使在系统崩溃时也能保证数据不丢失。当事务提交时，其相关更改首先被记录到Redo Log中，随后才会 标记事务状态为已提交。

1.2 默认存储位置

Redo Log存储在MySQL的数据目录下的ib_logfile* 文件中，如/var/lib/mysql/ib_logfile0 和
ib_logfile1 。

1.3 写入机制

— 创建存储过程

DELIMITER //  -- 临时修改结束符
CREATEPROCEDURE proc_get_user_count()
BEGIN

— 业务逻辑：查询用户总数

SELECTCOUNT(*)FROMuser;
END //
DELIMITER ;  -- 改回默认结束符
-- 调用
CALL proc_get_user_count();

— 带参数存储过程

DELIMITER //
CREATEPROCEDURE proc_get_user_by_id(IN p_id INT)
BEGIN
SELECT*FROMuserWHERE id = p_id;
END //
DELIMITER ;
-- 调用
CALL proc_get_user_by_id(10);
delete
insert ...
updateuserset age =2where id =1;  -- 缓冲池中 age = 2
commit;  -- DDL create/drop/alter
1.缓冲池中 age =2->  redo_log写入  age =2;

事务状态已提交

2.缓冲池中 age =2->数据文件

Redo Log采用循环写的方式，当一个日志文件写满后会切换到下一个日志文件继续写入。事务提交时， 相关日志会立即写入磁盘（即使事务尚未完成），这称为“预写式日志”（Write-Ahead Logging, WAL） 策略。

1.4 记录格式

Redo Log记录的是物理日志，即实际对数据页做的修改操作。

1.5 特点

●确保事务的持久性。 ●支持崩溃恢复，通过重做已记录的操作来恢复数据。

1.6 如何删除

Redo Log是循环使用的，不需要手动删除。MySQL会自动管理这些日志文件，旧的日志在新的日志被写 满并确认不再需要时会被覆盖。

2. Undo Log（回滚日志）

2.1 介绍与作用

Undo Log主要用于事务的回滚操作，记录了如何撤销对数据库的修改，以实现事务的原子性。当事务需 要回滚时，Undo Log能帮助恢复到事务开始前的状态。(没有修改前的状态，没有提交前的状态)

2.2 存储位置

Undo Log存储于InnoDB表空间内，具体位置依赖于表空间配置，一般位于ibdata文件或自定义的表空 间文件中。

2.3 写入机制

Undo Log同样采用预写日志方式，事务开始时写入Undo Log，事务提交或回滚后可能会被清理。

2.4 记录格式

Undo Log记录的是逻辑日志，描述了如何反向操作以撤销更改。

2.5 特点

●支持事务的原子性，允许回滚操作。 ●在MVCC（多版本并发控制）中，用于提供历史版本数据。

日志格式

记录内容

Statement 记录进行数据修改 SQL 语句。（当sql文中有函数调用的时候可能会和master不一 致) Row 记录每一行的数据变更，占用较多空间。（默认）(大量数据100W Row模式就支撑 不了) Mixed 前两者混合，判断是否可能引起数据不一致：可能则用Row 否则用Statement

2.6如何删除

Undo Log在事务提交且不再需要时会被自动清理，或者在表空间不足时按照一定的策略进行回收。

3. Binlog（二进制日志）

3.1 介绍与作用

Binlog记录了MySQL服务器上执行的所有更改数据的SQL语句（除了数据查询语句）。它主要用于数据 恢复、主从复制以及数据审计。

3.2 存储位置

Binlog文件默认存储在MySQL的数据目录下（/var/lib/mysql），文件名格式为mysql-bin.* 。

3.3 写入机制

Binlog采用追加写的方式，新事件不断被添加到日志文件末尾。MySQL支持多种写入模式，包括ROW （记录每一行的变化）、STATEMENT（记录执行的SQL语句）和MIXED（根据情况自动选择ROW或 STATEMENT）。 说明：需要开启Binlog日志，才会写入，开启方法一般修改mysql.ini(Windows)和my.cnf配置文件。

3.4 记录格式

Binlog记录的是逻辑日志，根据设置的不同，可以是SQL语句的文本或是行级别的变化。

3.5 特点

●支持数据恢复和复制。 ●对于主从复制，是同步数据的关键。 ●可用于审计和数据变更跟踪。

3.6 如何删除

可以通过PURGE BINARY LOGS 命令手动删除指定的或过期的Bin-log文件,或者使用reset 删除全部日志 （慎用）

16、Undo log是如何回滚事务的

在数据库中，Undo Log通常用于实现事务的回滚操作。当事务执行更新操作时，数据库会将相应的旧 数据记录在Undo Log中，用于回滚事务时还原到事务开始前的状态。以下是Undo Log回滚事务的一般 步骤： ① Undo log 保存数据修改前的镜像

对比项

MyISAM

InnoDB

事务支持 不支持

支持

锁机制 表锁

行锁

外键 不支持

支持

崩溃恢复 差

强

主键要求 非必须

必须有主键

并发性能 低

高

②事务 rollback 时，根据 undo log 把数据恢复成修改前的样子 ③同时支撑MVCC 多版本并发控制

17、Bin log有几种录入格式与区别

MySQL Binlog 有三种格式：STATEMENT、ROW、MIXED。

生产环境推荐使用 ROW 模式，数据最安全、主从一致。

Statement格式：存 SQL，小但可能不准 Row格式：存行变化，准、安全、生产默认 Mixed格式：混合，折中方案

18、简述MyISAM和InnoDB的区别

InnoDB 应用场景：99%项目都用它 MyISAM 应用场景：只读、查询极多、写极少、不在乎数据丢失

19、InnoDB存储引擎三大特性

①支持事务：保证操作要么全部成功，要么全部失败，数据安全可靠。 ②行级锁 + MVCC：高并发下读写不冲突，性能高、并发能力强。 ③崩溃安全恢复：数据库宕机重启后，能自动恢复数据，保证不丢失。

20、InnoDB 如何解决幻读

1、 Mysql 的事务隔离级别

Mysql 有四种事务隔离级别，这四种隔离级别代表当存在多个事务并发冲突时，可能出现的脏读、不可 重复读、幻读的问题。其中InnoDB 在RR 的隔离级别下，解决了幻读的问题。

2、什么是幻读？

幻读是指在同一个事务中，前后两次查询相同的范围时，得到的结果不一致

3、 InnoDB 如何解决幻读的问题

一个事务内，多次查询，结果条数不一样，像出现了 “幻觉”。 InnoDB 在可重复读级别下，使用Next‑Key Lock（临键锁）= 间隙锁（Gap Lock） + 记录锁 （Record Lock），在你查询的范围，锁定索引记录与间隙，禁止其他事务插入新数据，从而解决幻 读。

2、索引

1、索引的基本原理

索引就是给数据库建的「目录」，用 B+ 树实现，把 “逐行查找” 变成 “树查找”，大幅加快查询。 索引的原理：通过 B+ 树对字段排序，减少磁盘 I/O，实现快速查找的数据结构。

  1. 把创建了索引的列的内容进行排序。
  2. 对排序结果生成倒排表。
  3. 在倒排表内容上拼上数据地址链。
  4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

2、说一下索引的优势和劣势？

优势：提高查询速度，加速排序和分组，提升去重、关联效率 劣势：占用额外磁盘空间，降低增删改速度，索引过多会导致优化器选错，维护成本高

对比项

聚簇索引（Clustered Index）

非聚簇索引（Secondary Index）

含义 索引结构和数据存在一起 索引和数据分开存放 叶子节点存什么 存整行完整数据 只存索引字段 + 主键 数量 一张表只能有 1 个 一张表可以有多个 谁默认 InnoDB 主键就是聚簇索引 普通索引、唯一索引都是 查询速度

更快

查到主键后，通常要回表 写入速度 稍慢（数据要按顺序存放） 相对快一点

3、MySQL聚簇和非聚簇索引的区别

都是B+树的数据结构

聚簇索引：找到索引，就找到了整行数据。

非聚簇索引：找到索引，只拿到主键，还要再查一次（回表）。

4、MySQL索引的数据结构，各自优劣

MySQL InnoDB 只用 B+ 树做索引，因为它综合最强，支持范围、排序、模糊查询，磁盘 I/O 最少； Hash 只适合精确等值查询；B 树基本不用。 B+树：查询稳定，范围查询极快，磁盘 I/O 少，支持排序、分组、模糊查询。等值查询没有 Hash 快，写入稍慢 哈希索引：等值查询 = 超快（O(1)），冲突少时性能碾压 B+ 树。不支持范围查询，不支持排序、 模糊查询、最左匹配，只支持精确匹配 B树：单个节点就能存数据，某些小查询更快。范围查询慢，非叶子节点也存数据，I/O 更多，树 更高，性能不如 B+ 树

5、MySQL索引的设计原则

一句话总结：索引建在查询、排序、关联字段上，遵循最左匹配，使用覆盖索引，控制数量，选区分度 高的列。 ①经常用于 WHERE ，JOIN 关联，ORDER BY / GROUP BY 查询的字段加索引。 ②不适合建索引的字段： 1.数据重复度高 2.很少查询、很少用的字段 3.频繁更新的字段 4.数据量很小的表 ③联合索引遵循最左匹配原则。 ④优先使用覆盖索引 ⑤控制索引数量，一般不超过 5 个 ⑥选择区分度高的列建索引 ⑦不索引过长字段，长字符串可用前缀索引：INDEX(name(10))

对比项

B 树

B+ 树

数据存放

所有节点都存数据

只有叶子节点存数据，非叶子只存键 叶子节点 互不关联 用有序链表串联 范围查询 慢，需多次回溯 极快，直接遍历链表 查询稳定性 不稳定，可能快可能慢 稳定，每次都走到叶子节点 磁盘 I/O 更高 更低（非叶子更小，能加载更多） MySQL 使用 不用

InnoDB 索引默认使用

6、MySQL中B+树和B树的区别

一句话：B+ 树只在叶子存数据、叶子用链表相连，范围查询、排序、分页更快、I/O 更少，是 MySQL 索引的唯一选择。

7、MySQL索引底层结构为什么使用 B+树

B+树具有良好的平衡性、顺序访问性、存储效率、并发性和可扩展性，使得它成为一种理想的索引底层 结构。

8、索引失效

一句话记忆：计算、函数、% 开头、类型转换、最左不匹配、OR、NOT 都会让索引失效。

  1. 使用模糊匹配（如LIKE %xx或LIKE %xx）时，索引将失效。
  2. 在查询条件中对索引列应用函数会导致索引失效。
  3. 尽量避免使用 != 或 not in或 <> 等否定操作符
  4. 对索引列进行表达式计算同样无法使用索引。
  5. 索引不会包含有NULL值的列
  6. 当字符串与数字进行比较时，MySQL会自动将字符串转换为数字，这种隐式类型转换会导致索引失 效。
  7. 联合索引的使用必须遵循最左匹配原则，否则会导致索引失效。
  8. 在 WHERE 子句中，如果 OR 前的条件是索引列而 OR 后的条件不是，索引也会失效。

9、什么是最左前缀原则？

联合索引（复合索引），必须从左到右依次匹配，跳过左边的列，右边的索引直接失效。 能命中索引的情况： where a = ? where a = ? and b = ? where a = ? and b = ? and c = ?

INDEX idx(a, b, c)

索引失效 / 部分失效： where b = ?→跳过 a，失效 where c = ?→失效 where a = ? and c = ?→只用到 a ，c做筛选

10、where条件的顺序影响索引使用吗

在 MySQL 中，WHERE 条件的书写顺序，不影响联合索引的使用！ 你建立的联合索引顺序是固定的，MySQL 优化器会自动调整 WHERE 顺序 练习： a = 1：等值匹配，正常走索引 b > 2：范围查询→索引到此中断！ c = 3：因为 b 用了范围，联合索引中，遇到范围查询，当前列能用，右边所有列索引失效

11、什么是覆盖索引？

覆盖索引就是查询的字段全部包含在索引中，MySQL 直接通过索引返回结果，不需要回表查询，大大提 升查询效率。

12、什么是索引下推(ICP)？

一句话定义：把原本在Server 层做的索引过滤条件，下推到存储引擎层提前过滤，减少回表次数，提 升查询效率。 核心原理： 无 ICP：引擎先按索引取数据→回表→Server 层再过滤 有 ICP：引擎在索引遍历阶段就先过滤，只把符合条件的行回表 举个例子：

表：user(age, name, addr)
联合索引：idx(age, name)

查询：

where b=2and a=1and c=3  MySQL 会自动优化成：where a=1and b=2and c=3依然能完整

命中索引。 — 表有联合索引：

idx (a, b, c)
SELECT*FROM t WHERE a =1;  -- 命中索引
SELECT*FROM t WHERE b =1;  -- 没有命中
SELECT*FROM t WHERE a =1AND c =3; -- 命中a，后面c做条件筛选
SELECT*FROM t WHERE c =3AND b =2AND a =1;  -- 命中
SELECT*FROM t WHERE a=1AND b>2AND c=3; -- 命中a, 后面的b,c没用上

无 ICP（旧方式）

  1. 引擎按age=20 找到所有索引行（比如 1000 行）
  2. 全部回表查完整数据
  3. Server 层再过滤name LIKE ‘张%’ ，只剩 10 行→回表 1000 次，浪费 IO 有 ICP（新方式）
1. 引擎遍历age=20 的索引时，顺便检查name LIKE '张%'
  1. 只把符合条件的 10 行回表
  2. Server 层直接返回结果→回表 10 次，效率大幅提升

13、索引分类

普通索引（Index）：基础索引，无唯一性约束。 唯一索引（Unique Index）：确保列值唯一。 主键索引（Primary Key）：唯一且非空，自动创建。 组合索引（Composite Index）：对多个列的联合索引。 全文索引（Fulltext Index）：用于文本内容的模糊匹配。 空间索引（Spatial Index）：用于地理空间数据。 一旦你创建了空间索引，你就可以使用 MySQL 的空间函数来进行高效的查询。例如，查找所有位 于某个矩形区域内的城市：

SELECT*FROMuserWHERE age=20AND name LIKE'张%';
CREATEINDEX idx_last_name ON employees (last_name);
CREATEUNIQUEINDEX idx_employee_id ON employees (employee_id);
CREATEINDEX idx_last_name ON employees (id, last_name);
CREATEFULLTEXTINDEX idx_description ON products (description);
CREATETABLE locations (
id INT AUTO_INCREMENTPRIMARYKEY,
name VARCHAR(100),
location POINT NOTNULL,
SPATIALINDEX(location)  -- 空间索引
);
ALTERTABLE locations ADDSPATIALINDEX idx_location (location);
这个查询使用了MBRContains函数来检查location是否在指定的LINESTRING对象内。
ST_GeomFromText用于创建几何对象。

14、什么时候不要使用索引？

一句话：小表、重复度高、频繁更新、查询会失效、写多读少的场景，不要建索引。

15、MySQL 8的索引跳跃扫描是什么

在联合索引上，当查询不包含前导列条件、只用到后续列，且前导列基数很低（值种类少）时，MySQL 会把索引按前导列的每个值拆成多次范围扫描并合并结果，从而复用已有联合索引，避免全表扫描。

示例背景

假设有联合索引idx(gender, age) ，查询WHERE age > 30 ：

无 Skip Scan：无法用idx(gender, age) ，只能全表扫描 有 Skip Scan：

  1. 识别gender只有’m’ 、‘f’两个值（低基数）
  2. 拆成两个索引范围查询：
  3. 分别走索引、合并结果，避免全表

触发条件（必须同时满足）

①必须是联合索引，且查询不包含前导列条件，只用到后续列 ②前导列基数极低（值种类少，如性别、状态、小枚举） ③查询只涉及单表，无 JOIN

④无GROUP BY  / DISTINCT

⑤通常是覆盖索引 如何判断是否启用（EXPLAIN） ① type：index_skip_scan ② Extra：Using index for skip scan

16、如何使用EXPLAIN关键字？

EXPLAIN + SQL语句

SELECT id, name
FROM cities
WHERE MBRContains(LineString(ST_GeomFromText('LINESTRING(1 1, 5 5)')),
location);
WHERE gender ='m'AND age >30
UNIONALL
WHERE gender ='f'AND age >30
explainselect*from t_member where member_id =1;

id:选择标识符 select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

id相同时执行顺序从上到下, 在所有组中, id值越大, 优先级越高, 越先执行

id的结果共有3中情况

id相同，执行顺序由上至下 id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行 id相同不同，同时存在

select_type:查询类型

分别用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。 SIMPLE 简单的select查询，查询中不包含子查询或者UNION PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY SUBQUERY 在SELECT或WHERE列表中包含了子查询 DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些 子查询，把结果放在临时表中 UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子 句的子查询中，外层SELECT将被标记为：DERIVED UNION RESULT 从UNION表获取结果的SELECT table:指的就是当前执行的表

partitions:匹配的分区

type: type所显示的是查询使用了哪种类型，type包含的类型包括如下图所示的几种 const：通过索引一次命中，匹配一行数据 system: 表中只有一行记录，相当于系统表； eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配 ref：非唯一性索引扫描,返回匹配某个值的所有 range: 只检索给定范围的行，使用一个索引来选择行，一般用于between、<、>； index: 只遍历索引树； ALL: 表示全表扫描，这个类型的查询是性能最差的查询之一。就是随着表的数量增多，执行 效率越慢。 执行效率： ALL < index < range< ref < eq_ref < const < system。最好是避免ALL和index，至少也要有 ref。 ref: 非唯一性索引扫描，返回匹配某个单独值的所有行, 它可能会找到多个符合条件的行，所以他 应该属于查找和扫描的混合体。 range:只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引， 一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要 好 index： Full Index Scan， Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。 all： Full Table Scan 将遍历全表以找到匹配的行

possible_keys：查询时可能使用的索引

显示可能应用在这张表中的索引，一个或多个。 key：实际使用的索引 key_len：索引字段的长度 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长 度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据 表定义计算而得，不是通过表内检索出的。 ref：列与索引的比较 显示索引的哪一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上 的值。 rows：扫描出的行数 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的 越少越好 filtered：按表条件过滤的行百分比 extra：执行情况描述和说明 包含不适合在其他列中显式但十分重要的额外信息 Using filesort : 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进 行读取。 Using temporary: 使用了用临时表保存中间结果，常见于排序order by和分组查询group by。 Using index 表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率 不错。 如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。 Using where: 表明使用了where过滤

3、事务和锁

1、MySQL什么是死锁？怎么解决？

两个或多个事务，互相持有对方需要的锁，又都不释放自己的锁，无限等待下去，就是死锁。

有四个必要条件：互斥条件，请求和保持，循环等待，不可剥夺。 如何解决MySQL死锁问题 ①按固定顺序访问资源 ②让事务尽可能小、执行快，避免长事务 ③使用更低的隔离级别 ④合理加索引 ⑤开启死锁检测

2、MySQL事务的基本特性和隔离级别

事务基本特性ACID分别是： 原子性:指的是一个事务中的操作要么全部成功，要么全部失败。 一致性:指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如A转账给B100块 钱，假设A只有90块，支付之前我们数据库里的数据都是符合约束的,但是如果事务执行成功了,我们 的数据库数据就破坏约束了,因此事务不能成功,这里我们说事务提供了一致性的保证。 隔离性:指的是一个事务的修改在最终提交前，对其他事务是不可见的。 持久性：指的是一旦事务提交，所做的修改就会永久保存到数据库中。 隔离性有4个隔离级别，分别是： 读未提交：能读到别的事务未提交的数据，产生脏读。 读已提交：只能读到别的事务已提交的数据，解决脏读，但是不可重复读。 可重复读：这是mysql的默认级别，同一个事务内多次查询结果一致，解决脏读、不可重复读。幻 读（由间隙锁解决） 串行化，最高级别，事务串行执行，无并发问题，但性能最差 3 大并发问题： 脏读：读到未提交的数据 不可重复读：同一事务内，两次查询结果不一样 幻读：同一事务内，查询到了新增 / 删除的行

传播级别

含义（一句

话）

有无外部事务

关键特点

REQUIRED（默 认） 必须有事务 有：加入无：新建 共用事务，一起提交 / 回 滚

REQUIRES_NEW

新建独立事务 始终新建 内外事务独立，互不影响

NESTED

嵌套事务 有：嵌套无：新建 有保存点，内部可单独回 滚

SUPPORTS

支持事务 有：用无：不用 有无事务都行

NOT_SUPPORTED

不支持事务 有：挂起无：非事务 始终以非事务执行

MANDATORY

强制需要事务 有：用无：抛异常 必须在事务内调用

NEVER

强制不能有事 务 有：抛异常无：非事 务 禁止事务

3、并发事务带来哪些问题?

并发事务可以带来以下几个问题：

  1. 脏读（Dirty Read）：一个事务读取了另一个事务未提交的数据。假设事务A修改了一条数据但未 提交，事务B却读取了这个未提交的数据，导致事务B基于不准确的数据做出了错误的决策。
  2. 不可重复读（Non-repeatable Read）：一个事务在多次读取同一数据时，得到了不同的结果。 假设事务A读取了一条数据，事务B修改或删除了该数据并提交，然后事务A再次读取同一数据，发 现与之前的读取结果不一致，造成数据的不一致性。
  3. 幻读（Phantom Read）：一个事务在多次查询同一范围的数据时，得到了不同数量的结果。假 设事务A根据某个条件查询了一组数据，事务B插入了符合该条件的新数据并提交，然后事务A再次 查询同一条件下的数据，发现结果集发生了变化，产生了幻觉般的新增数据。
  4. 丢失修改（Lost Update）：两个或多个事务同时修改同一数据，并且最终只有一个事务的修改被 保留，其他事务的修改被覆盖或丢失。这种情况可能会导致数据的部分更新丢失，造成数据的不一 致性。

不可重复读和幻读区别：

不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者 删除比如多次读取一条记录发现记录增多或减少了。

4. 事务的传播级别

5、MySQL中ACID靠什么保证的？

一句话满分答案：原子性由 undo log 保证，持久性由 redo log 保证，隔离性由锁和 MVCC 保证，一致 性由 AID 共同保证。 A（原子性）由undo log日志保证，记录修改前的数据，事务回滚时用它恢复。 C（一致性）A + I + D + 约束 + 业务代码、最终让数据从一个合法状态变到另一个合法状态。 I（隔离性）由锁和 MVCC 保证，让并发事务互不干扰。 D（持久性）由内存+redo log来保证，记录修改后的数据，MySQL 宕机重启后用它恢复已提交事 务。。

6、MySQL中的MVCC是什么？

MVCC = Multi-Version Concurrency Control 多版本并发控制 一句话：MVCC 是多版本并发控制，通过 undo log 保存历史数据、readView 控制可见性，实现读不

加锁、读写不阻塞，解决脏读、不可重复读、幻读，支撑 InnoDB 高并发。。

MVCC只在READ COMMITTED和REPEATABLE READ两个隔离级别下工作。undo log：保存历史版 本。readView：事务快照，决定能看见哪个版本 聚簇索引记录中有两个必要的隐藏列： trx_id：用来存储每次对某条聚簇索引记录进行修改的时候的事务id。 roll_pointer：每次对哪条聚簇索引记录有修改的时候，都会把老版本写入undo日志中。这个 roll_pointer就是存了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获得上 一个版本的记录信息。(注意插入操作的undo日志没有这个属性，因为它没有老版本) 已提交读和可重复读的区别就在于它们生成ReadView的策略不同。 开始事务时创建ReadView，ReadView维护当前活动的事务id，即未提交的事务id，排序生成一个数组. 访问数据，获取数据中的事务id，对比ReadView： 如果在ReadView的左边（比ReadView都小），可以访问（在左边意味着该事务已经提交） 如果在ReadView的右边（比ReadView都大）或者就在ReadView中，不可以访问，获取roll_pointer， 取上一版本重新对比（在右边意味着，该事务在ReadView生成之后出现，在ReadView中意味着该事务 还未提交） 已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView,而可重复读隔离级别则在 第一次读的时候生成一个ReadView，之后的读都复用之前的ReadView。 这就是Mysql的MVCC,通过版本链，实现多版本，可并发读-写，写-读。通过ReadView生成策略的不同 实现不同的隔离级别 它的工作原理如下： 每条数据行都有一个隐藏的版本号或时间戳，记录该行的创建或最后修改时间。 当事务开始，它会获取一个唯一的事务ID，作为其开始时间戳。 在读取数据时，事务只能访问在其开始时间戳之前已提交的数据。这个版本的数据在事务开始前就 已存在。

维度

RR（默认）

RC（大厂常用）

为什么大厂选 RC

锁机

制

行锁 + 间隙锁（Gap）+ 临

键锁（Next-Key）

仅行锁（Record Lock），无间隙锁 锁范围更小、冲突更少、死锁 概率大幅降低

MVCC

快照

事务级快照（事务内所有 读同一份） 语句级快照（每次读 生成新快照） 长事务下 undo log 膨胀更 少、purge 更及时

并发

能力

低（锁竞争多、阻塞多） 高（读写冲突少、吞 吐更高） 高并发场景（如订单、库存） 性能提升明显

死锁

风险

高（间隙锁易导致跨范围 死锁） 低（仅锁目标行） 线上稳定性更好、告警更少

主从

复制

早期 statement 格式下易 主从不一致 配合 row 格式 binlog，主从更稳定 避免主从数据不一致

一致

性

解决脏读、不可重复读、 缓解幻读 仅解决脏读，允许不

可重复读、幻读

用唯一索引、UPDATE ...

WHERE等业务手段兜底 当事务更新数据，会创建新版本的数据，将更新后的数据写入新的数据行，并将事务ID与新版本关 联。 其他事务可以继续访问旧版本的数据，不受正在进行的更新事务影响。这种机制被称为快照读。 当事务提交，其所有修改才对其他事务可见。此时，新版本的数据成为其他事务读取的数据。

7、什么是当前读和快照读？

快照读：不加锁，读历史版本，靠 MVCC； 当前读：加锁，读最新数据，用于写 / 加锁查询。 当前读:加锁的 SELECT / 写操作,读取最新版本数据,会加行锁 / 间隙锁 快照读：普通的不加锁 SELECT，读取数据的历史版本，不加锁，读写不阻塞，靠MVCC实现

8、MySQL默认RR，大厂为啥要改成RC

RC 无间隙锁、锁粒度更细、死锁更少、并发更高、主从更稳；大厂用唯一索引、CAS、业务逻辑兜底， 可控地接受 “不可重复读 / 幻读”，换取性能与稳定性。

什么时候不建议改 RC

金融核心、账务对账、强一致统计（如日终批量） 事务内必须多次读同一份数据且结果绝对一致

SELECT ... FORUPDATE
SELECT ... LOCKINSHAREMODE
INSERT / UPDATE / DELETE
SELECT*FROMuserWHERE id=1;

9、MySQL中的锁类型有哪些？

全局锁锁全库，表锁锁表，行锁锁行； 共享锁读，排他锁写； InnoDB 有记录锁、间隙锁、临键锁、意向锁，RR 默认用临键锁防幻读。 在MySQL中，常见的锁包括以下几种： 颗粒度分 全局锁：锁整个数据库实例，用于全库备份。 表级锁：锁整张表，开销小、并发低。 行级锁：锁某一行，开销大、并发高，InnoDB 特有。 记录锁（Record Lock）：锁某一行记录。 间隙锁（Gap Lock）：锁索引之间的间隙，防止幻读。 临键锁（Next-Key Lock）：记录锁 + 间隙锁，RR 隔离级别默认。 意向锁：表级锁，提高加表锁时的判断效率。 按模式分 共享锁（S 锁）：读锁，可共享，互斥写。 排他锁（X 锁）：写锁，独占，互斥读写。

10、MySQL的行级锁锁的到底是什么

MySQL InnoDB 的行级锁，锁的是索引记录，不是数据行，更不是物理地址！ 只有通过索引检索数据时才会加行锁； 如果没有索引或索引失效，会导致锁全表，并发性能急剧下降。 ①必须通过索引才能加行锁：条件命中索引→加行锁；没有索引 / 索引失效→锁全部索引→相当于

锁全表

②即使你用主键 / 唯一键更新，也是锁索引 ③行锁与数据行无关：锁的是索引里的那条索引记录，不是磁盘上的那行数据 案例：

name没有索引

无法定位行，只能全表扫描 锁所有索引（聚簇索引） 结果：整张表被锁住

name有索引

结果：只锁对应索引项

间隙锁和 Next-key 锁

  1. 间隙锁（Gap Lock）：除了锁住索引项本身，InnoDB 在某些隔离级别下（如 Repeatable Read）还会锁住索引项之间的间隙，以防止幻读。
  2. 临键锁（Next-Key Lock）：是行锁与间隙锁结合形成的锁，用于精确地锁住相邻的索引记录和索 引间隙，以防止插入操作引起的幻读。
UPDATEuserSET name='xx'WHERE name='张三';

11、MySQL只操作同一条记录也会死锁吗

即使只操作同一条记录，也可能产生死锁。因为 MySQL 锁是加在索引上的，并且锁有队列、有兼容

性：比如 S 锁想升级 X 锁、同一事务多次更新排队、或加锁与判断条件分离，都可能导致两个事务互相

等待，形成死锁。

因为 MySQL 加锁不是一步完成的： ①先加锁 ②再判断条件 ③最后更新 / 删除 在唯一索引 / 主键下，同一条记录会出现：两个事务互相等待对方的锁→死锁。 案例： 事务A 事务B 此时 A 再执行，A 自己也会被阻塞！→A 等 B，B 等 A →死锁！ 原因：同一个事务内，对同一条记录多次加锁，也会因为锁队列排队而互相等待。

12、MySQL哪些命令会发生表级锁

手动LOCK TABLES直接表锁。

DDL（ALTER、DROP、TRUNCATE）会加 MDL 表锁。 无索引更新，InnoDB 行锁变表锁。 MyISAM 所有操作都是表锁。

13、MySQL加锁的情况

普通 SELECT 快照读，不加锁 FOR UPDATE、S 锁、写操作都是当前读，加 X 锁 锁加在索引上，不是数据行 唯一索引等值→记录锁； 范围 / 普通索引→临键锁； 无索引→锁全表 RR 有间隙锁，RC 只有记录锁 通过LOCK TABLES和UNLOCK TABLES命令可以对整个表进行显式的锁定和解锁操作

UPDATE t SET name='A'WHERE id=1; -- 加X锁
UPDATE t SET name='B'WHERE id=1; -- 等待A的锁
UPDATE t SET name='A2'WHERE id=1;

14、了解MySQL锁升级吗

严格来说：InnoDB 没有 “行锁升级为表锁” 这种锁升级机制！这是和 SQL Server、Oracle 最大的区 别。 MySQL “锁升级” 到底指什么： ①无索引 / 索引失效→行锁 “退化成” 全表锁效果，这不是真正的锁升级，是锁范围被放大了。 ②行锁数量太多，触发「表锁替代」 MySQL 有个参数innodb_concurrent_lock_threshold默认5000 即单次扫描 / 更新加的行锁超 过 5000 行MySQL 优化器会直接放弃行锁，改用表锁，这是 MySQL 内部的优化策略，不是标准的 “锁 升级”。 检测锁状态：

15、Innodb加索引的时候会锁表吗

InnoDB 在 MySQL 5.6 及以上版本，支持 Online DDL，添加二级索引默认使用 INPLACE 方式，不会

锁表，业务可正常读写。仅在开始和结束时加短暂的 MDL 元数据锁，若存在长事务可能导致阻塞。全

文索引、空间索引或旧版本下才会锁表。

  1. MySQL 5.5 及以前 加索引：锁表（COPY 方式），读写都会阻塞，线上不能随便加
  2. MySQL 5.6 ~ 8.0（现在几乎都是） 默认使用Online DDL，方式：INPLACE，允许 DML 并发（增删改查都可以）不会锁全表阻 塞业务 开始和结束瞬间，会加很短的 MDL 锁，只是拿元数据，不是全程锁表，若有长事务占着 MDL，会卡住 以下情况会退化成锁表 加全文索引 加空间索引
手动指定ALGORITHM=COPY

4、集群和设计

1、主键使用自增ID还是UUID，为什么？

MySQL InnoDB 强烈推荐用自增 ID 做主键，不推荐 UUID。

自增 ID 有序、占用空间小、碎片少、插入性能高、减少页分裂，适合 InnoDB；UUID 无序、空间大、

导致大量页分裂、查询效率低、可读性差。因此 InnoDB 主键优先自增 ID，分布式可使用自增

ID+UUID 唯一键。

— 查看当前锁等待

SELECT*FROM performance_schema.events_waits_current;

— 检查锁升级迹象

SHOWSTATUSLIKE'Innodb_row_lock%';

2、MySQL主从同步原理

MySQL 主从同步是：主库将写操作记入 binlog，从库通过 IO 线程拉取 binlog 生成 relay log，再由

SQL 线程重放执行，最终实现主从数据一致。

MySQL主从同步的过程： ①主库（Master）：所有写操作记录到binlog 二进制日志。 ②从库（Slave）：启动一个IO 线程，去主库拉取 binlog，写到本地relay log（中继日志）。 ③从库（Slave）：启动一个SQL 线程，读取 relay log，在从库重放执行，实现数据一致。

3、了解什么是表分区吗？表分区的好处有哪些？

表分区是将大表物理拆分成多个小分区，逻辑仍是一张表；

好处是查询更快、删除历史数据秒级、提升并发、便于冷热数据管理。

  1. 什么是表分区？ 把一张大表，按照某个字段（如时间、地区、ID）在物理上拆成多个小文件，但逻辑上还是一张 表。对业务 SQL 完全透明，不用改代码。
  2. 常见分区类型？ ①RANGE 分区：按范围（时间、ID 区间）最常用 ②LIST 分区：按枚举值（地区、状态） ③HASH 分区：哈希取模均匀分散数据 ④KEY 分区：按字段哈希 ⑤COLUMNS 分区：支持多列、字符串

企业最常用：RANGE 分区（按天 / 按月）

  1. 表分区的好处 ①查询更快：只扫描对应分区，不扫全表，减少 IO
②清理历史数据极快：直接ALTER TABLE ... DROP PARTITION; 秒级删除，不产生碎片，不锁

表。 ③提高并发：大表拆小，锁范围变小，更新 / 插入更快。 ④数据更易管理：冷热数据分离，热分区放 SSD，冷分区归档。 ⑤突破单文件大小限制：超大表不会因为单个文件过大影响性能。

4、MySQL 有哪些高可用方案

MHA 是基于主从复制的第三方高可用，成熟稳定，但有丢数据风险； MGR 是官方原生分布式集群，强一致、自动切换，但对环境和表结构要求高。

中小公司 / 老项目常用 MHA，新架构 / 金融支付更倾向 MGR。

MySQL的高可用方案主要有以下几种：

  1. 主从复制 + 手动 / 脚本切换：一主多从，binlog 同步
  2. MHA（Master High Availability）：基于主从复制，外部工具检测、选主、切换
  3. MGR（MySQL Group Replication）：原生高可用，基于Paxos分布式协议，自动故障转移、数据 强一致、多副本
  4. 主从 + Keepalived / VIP：通常配合 MHA 一起用
  5. 分布式中间件（分库分表 + 高可用）：MyCat、ShardingSphere适合超大数据量

5、MySQL数据库备份的3种方式

冷备需要停机，直接拷贝物理文件，特点：最简单最快最可靠，必须停机业务不可用；

温备运行中加读锁，常用 mysqldump，特点：逻辑备份，导出 SQL时加全局读锁，业务只能读不能

写；

热备不停机不锁表，生产用 XtraBackup，特点：物理备份，速度极快。支持增量备份，不阻塞读写

（大厂标准方案） MySQL 中进行不同方式的备份还要考虑存储引擎是否支持 MyISAM 热备 × 温备 √ 冷备 √ InnoDB 热备 √ 温备 √ 冷备 √

6、讲讲主从复制原理与延迟

主从复制是主库写 binlog，从库 IO 线程拉取生成 relay log，SQL 线程重放实现同步；

主从延迟主要因为从库单线程重放、大事务、硬件弱、锁阻塞和网络问题，

优化关键是开启并行复制、避免大事务、保证从库性能、业务层面读写分离。

延迟5大核心原因： 从库MySQL 5.6 及以前，重放是单线程，MySQL 5.7：并行复制 大事务：一个 SQL 更新 100 万数据，主库秒级，从库要执行很久。 从库配置太低：从库 CPU、IO、内存远不如主库。 锁冲突、阻塞：从库有人查询、备份，导致 SQL 线程被卡住。 网络延迟：主从网络差，binlog 拉取慢。

5、优化和场景

1、关心过业务系统里面的sql耗时吗？统计过慢查询吗？对慢查询都

怎么优化过？

关心，而且非常重视。

关注以下四点：

①接口响应时间 ② SQL 执行耗时 ③慢查询频率 ④数据库 CPU、IO、连接数

怎么统计慢查询？

①开启 MySQL 慢查询日志 ②使用工具分析：mysqldumpslow，监控系统（Prometheus + Grafana），APM 工具 （SkyWalking、Pinpoint、Arthas） ③日常会定期查看：高频慢查询，全表扫描 SQL，耗时 Top 10 SQL

慢查询怎么优化？

①先用 EXPLAIN 查看执行计划，重点看：type, key,rows, extra ②优化索引，避免索引失效 ③优化 SQL 语句本身 ④避免大事务、大批量操作：分批更新、分批删除 ⑤架构层面优化：加缓存，读写分离，大表分库分表/分区

2、MySQL数据库cpu飙升的话，要怎么处理呢？

MySQL CPU 飙升，我会先紧急处理： ①通过 top 定位 mysqld 进程，show processlist 找到耗时 SQL，kill 掉恢复业务。 ②然后排查原因，90% 是慢查询、索引缺失、大量排序分组、并发过高导致。 ③再通过慢查询日志和 explain 分析执行计划，优化索引和 SQL，最后配合限流、读写分离、连接池调 整，从根本解决 CPU 飙升问题。

3、自增主键会遇到什么问题

自增主键的问题：主从 / 双主下冲突、回滚导致 ID 断层、暴露业务数据、高并发有锁竞争、分库分表无 法全局唯一、数据迁移困难。

4、MySQL自增id用完怎么办?

自增 ID 用完会导致无法插入，int 最多存 42 亿，bigint 几乎用不完；

解决办法是把 int 改成 bigint，或归档历史数据，新项目直接用 bigint 避免问题。

5、7种MySQL亿级/千万级大表快速删除数据方案：锁影响与性能分

析

  1. 批量 LIMIT 删除：通用安全，适合大多数场景
  2. 按主键范围删除：速度快、无偏移
  3. TRUNCATE：秒清空全表，会锁表
  4. DROP PARTITION：分区表秒删历史数据
  5. 新建交换表：保留少量数据时最优
  6. 物理文件替换：快但风险高
  7. pt-archiver/mydumper：生产最稳、无侵入工具删除

千万 / 亿级大表千万不要一次性 DELETE，会长事务、锁全表、主从延迟、CPU 打满。最优方案是：普

通表用主键分批删除，时间序列表用分区 DROP，核心业务用pt-archiver，保留少量数据用新建交换 表。

ALTERTABLE t MODIFYCOLUMN id BIGINT NOTNULLAUTO_INCREMENT;

6、100W数据去重，该用distinct还是group by，说说理由？

100W 数据去重，优先用DISTINCT ； 只有需要聚合函数时，才用GROUP BY 。 没有聚合时，DISTINCT性能通常更好、语义更清晰。

7、count(*)很慢，具体如何提升性能？

InnoDB 的 count (*) 慢，是因为：

  1. InnoDB 必须真实扫描计数
  2. 要支持事务、MVCC，每行要判断可见性
  3. 没有小索引，就只能扫聚簇索引（整行数据），IO 爆炸 提升性能最稳妥、最常用的方法是：
  4. 建一个窄索引(字段少值多的列)，让 count (*) 走最小索引，减少 IO；
  5. 允许误差用 Redis 计数或 information_schema 估算；
  6. 允许延迟用统计表定时更新。

8、用了索引还是慢可能是什么原因

用了索引还慢，主要原因有：

  1. 索引区分度太低；
  2. 大量回表查询；
  3. 索引未覆盖查询字段；
  4. 索引宽度太大；
  5. 模糊查询、函数操作、类型转换导致索引低效；
  6. limit 偏移量过大；
  7. 统计信息不准，优化器选错索引analyze table重新统计；
  8. 锁等待或并发问题。

9、如何快速定位慢SQL

快速定位慢 SQL：先开启慢查询日志抓日志，用工具分析出耗时、高频、扫描行数多的 SQL；再用 show processlist 看实时慢 SQL；配合 performance_schema 或 APM 监控直接定位；最后用 explain 分析执行计划，找到索引、回表、排序、全表扫描等问题。

  1. 开启慢查询日志
  2. 用工具分析慢查询日志: mysqldumpslow官方工具或 pt-query-digest
  3. 查实时运行 SQL，重点：Sending data，Copying to tmp table，Using filesort，Locked
slow_query_log =1
long_query_time =1（超过1秒记日志）
log_queries_not_using_indexes =1（没走索引也记）
  1. 用performance_schema / sys定位
  2. 用APM 监控（生产最常用）: SkyWalking，Pinpoint，Arthas，Prometheus + Grafana

10、如何进行SQL调优

  1. 先通过慢查询、processlist 定位慢 SQL；
  2. 用 EXPLAIN 分析执行计划，看是否全表扫描、有无索引、是否排序或临时表；
  3. 优先优化索引，建立联合索引、覆盖索引，避免索引失效；
  4. 改写 SQL，减少回表、避免深分页、减少子查询；
  5. 优化表结构，使用合适字段类型，大表拆分；
  6. 最后用缓存、读写分离、分库分表等架构手段，从根本解决性能瓶颈。

11、NULL 值引发的 5 大问题

  1. 等值判断失效，查询结果不符合预期
  2. NOT IN 子查询里有 NULL，结果直接为空，因为NOT IN会转为!= ，遇到 NULL 直接判

unknown

  1. 索引失效 / 索引效率变差，索引不存储 NULL
  2. COUNT (字段) 会漏掉 NULL 值
  3. GROUP BY、ORDER BY、DISTINCT 把所有 NULL 当成同一组

12、为什么MySQL 8.0要取消查询缓存

MySQL 8.0 取消查询缓存，是因为它在高并发写场景下：全局锁导致严重竞争、表级失效导致命中率极 低、字节级匹配导致复用率差、内存管理低效，且有更优的应用层缓存与 InnoDB 缓冲池替代，移除后 系统更稳定、可扩展性更强。

  1. 全局锁竞争，高并发下性能雪崩
  2. 缓存失效太粗暴，写场景基本无效
  3. 命中条件极端苛刻，实际复用率极低
  4. 内存管理低效，易产生碎片
  5. 与现代优化器、存储引擎冲突
  6. 替代方案更优，架构更清晰
showprocesslist;
SELECT*FROM sys.schema_unused_indexes;
SELECT*FROM sys.statement_analysisORDERBY latency DESC;

13、为什么不建议使用存储过程

  1. 业务逻辑耦合在数据库，无版本控制、难以维护与测试；
  2. 调试困难，排查问题成本高；
  3. 数据库无法水平扩展，高并发下易成为性能瓶颈；
  4. 计算逻辑应放在应用层，数据库只做数据存储；
  5. 跨数据库兼容性差，后期迁移、升级代价大。

14、为什么大厂不建议使用多表join

  1. 分库分表后跨库无法 JOIN；
  2. JOIN 会加重数据库计算压力，容易产生临时表、文件排序，导致性能雪崩；
  3. 数据库是瓶颈，应只做简单读写，复杂逻辑放到应用层；
  4. 表耦合严重，不利于业务迭代、扩展和缓存；
  5. 实际开发中多用单表查询 + 应用层组装、字段冗余来替代 JOIN。

15、为什么还会有人认为MySQL单表不要超过2000W数据

单表不超过2000W不是 MySQL 的物理限制，而是行业经验值。 原因来自 InnoDB 的 B+ 树索引结构： 三层索引大概能支撑 2000W 行数据，查询只需要 2-3 次 IO； 超过后索引变四层，磁盘 IO 增加、内存命中率下降，性能会明显下滑。 同时大表会导致 DDL、备份、维护成本极高，优化器也容易选错索引。 但这不是绝对限制，只要索引合理、查询简单、内存足够，单表上亿也能跑。 实际开发中，我们一般把 2000W 作为预警线，超过就考虑分库分表或分区。

16、说下你对分库分表的理解

分库分表就是把单库单表按垂直或水平方式拆分成多个库表，解决 MySQL 单库单表数据量过大、并发 过高、性能瓶颈的问题。但会带来分布式事务、跨库分页、唯一 ID、无法跨库join、运维复杂等问题， 所以不到万不得已不轻易分库分表，实际项目一般用 ShardingSphere 实现。

分片算法：

范围分片：按时间、ID 区间 哈希取模：user_id % 8

一致性哈希

雪花算法 / 号段

两种方案： 客户端分片：ShardingSphere-JDBC 代理端分片：MyCat、ShardingSphere-Proxy

17、MySQL的深度分页如何优化

什么是深度分页？偏移量越大，越慢。 原因：MySQL 要先扫描 1000010 行，再丢弃前 1000000 行，只返回 10 行。 深度分页 4 种最优优化方案?

  1. 主键 ID 连续分页（最优、最常用）
  2. 延迟关联法（优化大 LIMIT，支持跳页）
  3. 禁止 SELECT *，使用覆盖索引
  4. 业务层面妥协（后台系统常用）
  5. 禁止跳到大页码
  6. 限制最大偏移（如最多查 1000 条）
  7. 用时间范围代替分页

18、业务系统每天增加五万条以上数据预计运维三年怎么优化

  1. 表结构合理设计，使用 BIGINT 主键，控制行大小；
  2. 按时间做水平分表或分区表，实现冷热数据分离；
  3. 历史数据定时归档，只查热数据；
  4. 搭建读写分离，统计查询走从库；
  5. 禁止深度分页，使用覆盖索引，避免慢 SQL；
  6. 定期数据清理，防止单表无限膨胀。
SELECT*FROMtable
WHERE xxx
LIMIT1000000, 10;
SELECT*FROMtable
WHERE id >上一页最后一条ID
LIMIT10;
SELECT t.*FROMtable t
JOIN(
SELECT id FROMtable  -- 子查询只查主键（索引覆盖、极快）再通过主键回表查完整数据
WHERE xxx
LIMIT1000000,10
)AS temp ON t.id= temp.id;
SELECT id,name FROMtable
WHERE create_time >'xxx'
LIMIT1000000,10;

19、高并发场景下，如何安全修改同一行数据

  1. 原子更新：直接在 SQL 里做 count=count+1，最简单性能最高；
  2. 悲观锁：使用 SELECT … FOR UPDATE，强一致但并发低；
  3. 乐观锁：通过版本号 CAS 机制，无锁并发，性能好；
  4. 分布式锁：跨服务 / 跨实例时使用 Redisson 实现。 实际项目中优先用原子更新，并发高用乐观锁 + 重试，必须强一致用悲观锁，从根源减少锁冲突，保证 安全与性能。

20、分表后非分片键的查询、排序怎么处理

分库分表后，非分片键的查询无法定位到具体分片，会触发全分片广播查询，然后在中间件层做内存聚 合、排序、分页，性能较差且有 OOM 风险。

解决方案有四种：

①低并发后台系统，直接使用广播查询兜底； ②业务上强制带上分片键，做精准路由，性能最优； ③建立异构索引表，通过非分片键先查到分片键，再精准查询； ④高并发、复杂查询，引入 ES、ClickHouse 等专门存储，负责条件查询、排序、分页，MySQL 只负 责主键 / 分片键查询。 实际项目中，大厂一般用：强制路由 + 异构索引表 + ES/CK 结合的方案 案例:

按user_id分片，需要按order_no、phone、create_time查询，会触发：广播查询（Broadcast

Query） ①中间件（ShardingSphere）向所有分表发送 SQL ②每个分表并行 / 串行执行 ③中间件把结果拉到内存，再做：去重、排序、分页、聚合 非分片键查询、排序、分页的 4 种解决方案？ 兜底方案：广播查询 + 内存聚合（最简单，但性能差） 最优方案：冗余分片键（强制路由，性能爆炸,这是大厂最推荐方案） 实用方案：建立异构索引表（反范式冗余）再建一张表，以非分片键作为分片键： 先通过order_no→查到user_id ,再用user_id去订单表精准查询,两次单分片查询，极快 大数据量方案（大厂标准架构）：引入搜索引擎 / 数仓 MySQL 只存按分片键查询,复杂条件、排序、分页、全文检索→走 ElasticSearch,查到 ID 后，再回查 MySQL

select*fromorder
where user_id =? and order_no =?  -- 查订单，必须带 user_id
-- 订单表：user_id 分片,订单索引表：order_no 分片
order_idx (
order_no varchar(64),
user_id bigint
)

21、问题模拟：

  1. 你们每天这么大的数据量，都是保存在关系型数据库中吗？
  2. 每天几百万数据，一个月就是几千万了，那你们有没有对于查询做一些优化呢？
  3. 那你能说说什么是索引吗？
  4. 那么索引具体采用的哪种数据结构呢？
  5. 既然你提到InnoDB使用的B+ Tree的索引模型，那么你知道为什么采用B+ 树吗？这和Hash索引比 较起来有什么优缺点吗？
  6. 除了上面这个范围查询的，你还能说出其他的一些区别吗？
  7. 刚刚我们聊到B+ Tree ，那你知道B+ Tree的叶子节点都可以存哪些东西吗？
  8. 那这两者有什么区别吗？
  9. 刚刚你提到主键索引查询只会查一次，而非主键索引需要回表查询多次。是所有情况都是这样的 吗？非主键索引一定会查询多次吗？
  10. 想问一下，你们在创建索引的时候都会考虑哪些因素呢？
  11. 那你们有用过联合索引吗？
  12. 那你们在创建联合索引的时候，需要做联合索引多个字段之间顺序你们是如何选择的呢？
  13. 为什么这么做呢？
  14. 那你知道最左前缀匹配吗？
  15. 你们线上用的MySQL是哪个版本啊呢？
  16. 那你知道在MySQL 5.6中，对索引做了哪些优化吗？
  17. 你们创建的那么多索引，到底有没有生效，或者说你们的SQL语句有没有使用索引查询你们有统计 过吗？
  18. 那排查的时候，有什么手段可以知道有没有走索引查询呢？
  19. 那什么情况下会发生明明创建了索引，但是执行的时候并没有通过索引呢？
  20. 你们线上数据的事务隔离级别是什么呀？
  21. 为什么用这种隔离级别？