面试-MySql

发表于2025-12-20|更新于2026-01-17|面试

|浏览量:

SQL基础:

1.NOSQL和SQL的区别:

SQL是指关系型数据库,有:SQL Server,Oracle,MySql等; 关系型数据库存储结构化数据;这些数据逻辑上以二维表的形式存在;

NOSQL指非关系型数据库,有:MongoDB,Redis等; 非关系型数据库逻辑上提供了不同于二维表的存储方式,存储方式可以是 JSON文档,哈希表或者其他方式;

选择SQL,NOSQL的考虑因素:

1.ACIDvsBASE

关系型数据库支持ACID,即:原子性,一致性,隔离性,持久性; NOSQL采用更为宽松的模型BASE,即:基本可用,软状态,最终一致性;
例如:银行操作必须要符合ACID,使用关系型数据库; 社交软件等就没有这些需求,就更为宽松;

2.扩展性对比

NOSQL数据之间无关系,这样就容易扩展,也无形之间,在架构层上带来了可扩展的能力;比如Redis的主从复制模式,哨兵模式,切片集群模式;
相反关系型数据之间存在关联性,水平扩展教难,需要解决跨服务器JOIN,分布式事务问题;

2.数据库三大范式:

**1.第一范式(1NF):**要求数据库表的每一列都是不可分割的原子数据项;

**2.第二范式(2NF):**在1NF的基础上,非码属性必须完全依赖于候选码; 需要确保数据表中的每一列都和主键相关,而不能只与主键的某一部分相关;

**3.第三范式(3NF):**在2NF的基础上,任何非主属性不完全依赖于其他非主属性; 需要确保数据表中的每一列数据都和主键直接相关,而不是间接相关

3.MySql连表查询的方式:

1.内连接(INNER JOIN): 保存两表匹配的行;
**2.左外连接(LEFT JOIN):**保存左表完整,右表没匹配的数据就显示null;
**3.右外连接(RIGHT JOIN):**保存右表完整,左表没匹配的数据就显示null;
**4.全外连接(FULL JOIN):**保存两表都完整,没匹配数据就显示null;

4.MQSQL如何避免重复插入数据:

1.使用UNIQUE约束;

2.使用INSERT……ON DUPLICATE KEY UPDATE;

如果插入键与现有数据有冲突,会更新现在记录;
例如:

1
2
3

INSERT INTO orders(order_no, user_id)
VALUES ('A001', 1)
ON DUPLICATE KEY UPDATE user_id = VALUES(user_id);

3.使用INSERT IGNORE;

忽略重复键导致的插入错误;
例如:

1	INSERT IGNORE INTO orders(order_no, user_id) VALUES ('A001', 1);

5.CHAR和VARCHAR的区别:

CHAR 是定长字符串类型，在创建表时就固定了长度，不管实际存多少字符，都会占用指定的长度，不足的部分会用空格补齐。由于长度固定，MySQL 在存储和比较时不需要额外计算偏移量，数据读取更直接，性能相对稳定。
CHAR 适合长度固定、取值范围小、访问频繁的字段，例如性别、状态码、国家代码等。这类字段虽然会有一定空间浪费，但换来的是结构简单和查询效率稳定，在核心字段中很常见

VARCHAR 是变长字符串类型，实际占用的空间由真实数据长度决定，并额外使用 1～2 个字节来记录字符串长度，因此比 CHAR 更节省存储空间。它在存储时需要维护长度信息，读写和比较时会有少量额外开销。
VARCHAR 适合长度不确定或变化较大的字段，例如用户名、地址、描述信息等。在大多数业务系统中，VARCHAR 是最常用的字符串类型，能够在保证灵活性的同时有效控制存储成本。

VARCHAR(20) 括号中数字代表的是字符数不是字节数;

6.INT(1)和INT(10)在MYSQL中的不同:

INT(1) 和 INT(10) 在 MySQL 中本质没有区别，它们都占 4 字节、取值范围完全相同，括号里的数字只是显示宽度而不是存储长度，只有在配合 ZEROFILL 时才影响显示效果，对实际存储和性能没有影响。

下面用一个最直观的 MySQL 代码例子说明 INT(1) 和 INT(10) 没有本质区别

CREATE TABLE demo_int (
    a INT(1),
    b INT(10),
    c INT(5) ZEROFILL
);

INSERT INTO demo_int VALUES (1, 1, 1);
INSERT INTO demo_int VALUES (123, 123, 123);

SELECT * FROM demo_int;

查询结果示意：

1
2
3

a    b    c
1    1    00001
123  123  00123

关键说明（面试点）：

a INT(1) 和 b INT(10)
显示和存储完全一样，都能存 123
只有 INT(5) ZEROFILL
才会按显示宽度补 0
括号里的数字不限制数值大小，只影响显示

7.MYSQL中三种text类型的最大长度:

**1.TEXT :**65,535 bytes~ 64kb
**2.MEDIUMTEXT:**16,777,215 bytes16Mb
**3.LONGTEXT:**4,294,967,295 bytes4Gb

8.IP地址怎么在数据库里存储:

方案一：INT / UNSIGNED INT（推荐）

将 IPv4 转成 32 位整数 存储
占 4 字节
查询、排序、范围判断性能好

-- 建表
CREATE TABLE login_log (
    ip INT UNSIGNED
);

-- 插入
INSERT INTO login_log VALUES (INET_ATON('192.168.1.1'));

-- 查询
SELECT INET_NTOA(ip) FROM login_log;

“整数存 IP，省空间、好索引、好比较。”

方案二：VARCHAR(15)（不推荐但常见）

1	ip VARCHAR(15)

可读性好
占空间大
不利于范围查询（如 IP 段）
“简单但不专业。”

三、IPv6 的存储方式（进阶）

✅ BINARY(16)（推荐）

IPv6 是 128 位
用 16 字节二进制存
性能最好、最规范

1	ip BINARY(16)

应用层负责转换。

9.外键约束:

外键约束的作用是维护表和表之间的关系,确保数据的完整性和一致性;

10.MYSQL中的IN和EXISTS关键字:

都是用来处理子查询的关键字;

1.IN关键字:

IN 用于判断某个值是否存在于子查询返回的结果集中。
MySQL 会先执行子查询，把结果集算出来，然后再用外层查询的字段去和这个结果集做匹配，语义直观、可读性好。

SELECT *
FROM orders
WHERE user_id IN (
    SELECT id FROM users WHERE status = 1
);

特点（面试要点）：

子查询结果通常是一个列表
更适合 子查询结果集较小 的场景
如果子查询结果很大，性能可能下降
子查询一般会先执行一次

IN 是“先查子表，再用结果过滤主表”，适合小结果集。

2.EXISTS关键字:

EXISTS 用于判断子查询是否“存在满足条件的记录”。
它不关心子查询返回什么数据，只要找到一条满足条件的记录就立即返回 true，通常和外层查询形成相关子查询。

SELECT *
FROM orders o
WHERE EXISTS (
    SELECT 1
    FROM users u
    WHERE u.id = o.user_id AND u.status = 1
);

SELECT 1 中的 1 没有任何业务含义，只是一个占位常量，表示“存在一行即可”

特点（面试要点）：

返回的是 true / false
子查询通常依赖外层表
找到一条记录就停止
适合子查询数据量大的场景

EXISTS 是“逐条检查是否存在匹配记录”，适合大表。

11.MYSQL中的一些基本函数:

1️⃣ 字符串函数（String Functions）

用于字符串处理、截取、拼接等。

LENGTH(str)       -- 字符长度
CONCAT(a, b)      -- 字符串拼接
SUBSTRING(str, 1, 3) -- 截取字符串
REPLACE(str,from_str,to_str) -- 置换字符串
TRIM(str)：-- 去除字符串首尾空格

👉 常用于用户名、模糊查询、数据清洗。

2️⃣ 数值函数（Numeric Functions）

用于数值计算和取整。

ABS(x)  -- 返回绝对值
POWER(num,exponent) -- 返回指定数字的指定幂次方
ROUND(x, n) -- 四舍五入，保留 n 位小数
CEILING(x) -- 向上取整
FLOOR(x)  -- 向下取整

👉 常用于金额、统计数据处理。

3️⃣ 日期时间函数（Date & Time Functions）

用于时间获取和格式化。

NOW() -- 返回当前日期和时间
CURDATE() -- 返回当前日期
DATE_ADD(date, INTERVAL 1 DAY)DATE_ADD(date, INTERVAL n unit)：-- 日期加指定时间
DATE_SUB(date, INTERVAL n unit)：-- 日期减指定时间
DATEDIFF(date1, date2) -- 计算两个日期相差的天数

👉 日志、订单、时间区间查询必用。

4️⃣ 聚合函数（Aggregate Functions）

对多行数据进行统计计算。

COUNT(*)
SUM(col)
AVG(col)
MAX(col)
MIN(col)

👉 常与 GROUP BY 搭配使用。

12.SQL查询语句的执行顺序是怎么样的:

✅ 正确的执行顺序是：

1️⃣ FROM
2️⃣ JOIN / ON
3️⃣ WHERE
4️⃣ GROUP BY
5️⃣ HAVING
6️⃣ SELECT
7️⃣ DISTINCT
8️⃣ ORDER BY
9️⃣ LIMIT

13.如何用MySql实现一个可重入锁:

MySQL 可重入锁可以通过“唯一锁标识 + 持有者 + 重入计数”的方式实现，本质是用一张锁表模拟可重入行为。

一.表结构设计（关键）

CREATE TABLE reentrant_lock (
    lock_name   VARCHAR(64) PRIMARY KEY,
    owner_id    VARCHAR(64) NOT NULL,
    lock_count  INT NOT NULL,
    update_time DATETIME NOT NULL
);

字段说明：

lock_name：锁名称（唯一）
owner_id：锁持有者（如 requestId / threadId / uuid）
lock_count：重入次数
update_time：防止死锁（超时释放）

五、加锁逻辑（核心）

1.先开启事务;

1️⃣ 第一次加锁（抢锁）

1 2	INSERT INTO reentrant_lock(lock_name, owner_id, lock_count, update_time) VALUES ('order_lock', 'thread_1', 1, NOW());

成功：获得锁
失败（主键冲突）：说明锁已存在

2️⃣ 重入加锁（同一持有者）

UPDATE reentrant_lock
SET lock_count = lock_count + 1,
    update_time = NOW()
WHERE lock_name = 'order_lock'
  AND owner_id = 'thread_1';

更新成功 → 重入成功
更新失败 → 锁被别人持有

最后提交事务

“通过 owner_id 判断是否可重入。”

六、释放锁逻辑（必须成对）

开启事务

1️⃣ 减少重入次数

UPDATE reentrant_lock
SET lock_count = lock_count - 1
WHERE lock_name = 'order_lock'
  AND owner_id = 'thread_1'
  AND lock_count > 1;

2️⃣ 真正释放锁（最后一次）

DELETE FROM reentrant_lock
WHERE lock_name = 'order_lock'
  AND owner_id = 'thread_1'
  AND lock_count = 1;

提交事务

完整流程

加锁时先尝试 insert 抢锁；

如果失败且 owner 相同，则 lock_count++ 实现重入；

解锁时先递减计数，减到 0 才真正释放锁。

存储引擎:

1.执行一条SQL请求的过程是什么?

执行一条 SQL 的完整流程（重点）:

1️⃣ 客户端发送 SQL 请求

客户端（Java / MySQL CLI 等）
通过 TCP 连接 把 SQL 发给 MySQL Server

2️⃣ 连接层（Connection Management）

验证用户名、密码、权限
建立连接（或使用连接池）

连接建立成本高，所以要用连接池

3️⃣ 查询缓存（Query Cache，已淘汰）

MySQL 8.0 已移除
老版本会直接返回缓存结果

“8.0 已经没有查询缓存了。”

4️⃣ SQL 解析器（Parser）

词法分析（识别关键字）
语法分析（检查 SQL 是否正确）
生成 解析树

5️⃣ 预处理器（Preprocessor）

检查表、字段是否存在
检查权限
解析视图、函数

6️⃣ 查询优化器（Optimizer）

生成多个执行方案
选择 代价最低的执行计划
决定：
- 用不用索引
- 用哪个索引
- JOIN 顺序

👉 面试官最爱追问这里

7️⃣ 执行阶段

根据执行计划执行SQL查询语句,从存储引擎读取记录,返回给客户端;

执行流程图

Client → Connection → Parser → Preprocessor → Optimizer → Executor → Storage Engine → Result

2.MySql的引擎:

**1.InnoDB（默认）: **InnoDB 支持 事务（ACID）、行级锁 和 外键约束，并通过 MVCC 提供高并发能力，是 MySQL 默认也是最常用的存储引擎，适合对数据一致性要求高的核心业务场景

2.MyISAM: MyISAM 不支持事务和行锁，只支持 表级锁，具有较低的存储空间和内存消耗 , 读性能较好但并发写能力弱，崩溃后容易造成数据损坏，主要用于 读多写少、对事务要求不高 的场景

3.Memory: Memory 引擎将数据 存储在内存中，读写速度非常快，但 数据库重启后数据会丢失，只支持表级锁，且不支持 TEXT、BLOB 等复杂类型。它适合用于 临时数据、缓存表、会话数据 等场景，通常不作为核心数据存储。

InnoDB是默认引擎的原因: 支持事务,并发性能好,崩溃恢复(通过redolog实现);

3.InnoDB和MyISAM的区别:

1️⃣ 事务支持

InnoDB：支持 ACID 事务
MyISAM：不支持事务

“要事务就必须用 InnoDB。”

2️⃣ 锁机制

InnoDB：行级锁（并发性能好,锁粒度更小）
MyISAM：表级锁（并发写性能差）

3️⃣ 崩溃恢复

InnoDB：支持 redo log / undo log，可恢复
MyISAM：容易表损坏，恢复能力弱

4️⃣ 外键

InnoDB：支持外键约束
MyISAM：不支持外键

5️⃣ 存储结构

InnoDB：聚簇索引（数据和主键索引存一起）
MyISAM：非聚簇索引（索引和数据分离）

4.数据管理里,数据文件大体分为哪几种数据文件?

.opt 文件

.opt 文件用于保存数据库级别的默认字符集和排序规则信息，主要在创建数据库时生成，帮助 MySQL 确定该库中表的默认编码方式，本身不存储业务数据。

.frm 文件

.frm 文件用于保存表结构定义，包括字段、索引等元数据，是 MySQL 用来描述表结构的文件（MySQL 8.0 之前），不存储真实数据。

.ibd 文件

.ibd 是 InnoDB 独立表空间文件，用于存储表的数据和索引，当开启 innodb_file_per_table 时，每张表都会有一个对应的 .ibd 文件。

一句话总结:

.opt 存数据库编码信息，.frm 存表结构，.ibd 存 InnoDB 表的数据和索引。

索引:

1.索引是什么?有什么好处?

索引是数据库中一种用于快速定位数据的数据结构，类似书的目录，通过减少磁盘 IO 和扫描数据量，提高查询效率。
在 MySQL 中，索引通常以 B+ 树（或 Hash）等结构实现

可以显著提高查询速度, 提高排序和分组效率, 减少磁盘 IO;

2.索引的分类:

1.按数据结构分:
B+Tree 索引：MySQL 默认索引结构，支持范围查询、排序、最常用
Hash 索引：基于哈希表，只支持等值查询，不支持范围查询（Memory 引擎）

2.按物理存储分:

聚簇索引（Clustered Index）：索引和数据存储在一起，InnoDB 的主键索引
非聚簇索引（二级索引）：索引和数据分离，查询时可能需要回表

3.按字段特性分:

主键索引（PRIMARY KEY）：唯一且不能为空
唯一索引（UNIQUE）：值唯一，允许一个 NULL
普通索引（INDEX）：仅用于加速查询
全文索引（FULLTEXT）：用于文本内容检索

4.按字段个数分:

单列索引：基于一个字段建立
联合索引（组合索引）：基于多个字段建立，遵循最左前缀原则

3.哈希索引的使用场景:

哈希索引的优势是等值查询极快,能直接通过哈希函数定位数据位置,短板是不支持范围查询,排序,前缀匹配,所以使用场景是以等值查询为核心,不需要复杂查询操作的场景;

4.MySql中聚簇索引和非聚簇索引的区别:

一、聚簇索引（Clustered Index）

聚簇索引决定了数据的物理存储顺序，索引和数据行存储在同一个 B+Tree 中。
在 InnoDB 引擎中，主键索引就是聚簇索引，一张表只能有一个聚簇索引。

特点：

叶子节点存的是 完整行数据
按主键查询速度最快
插入、更新主键代价较高（可能引起页分裂）

二、非聚簇索引（二级索引 / Secondary Index）

非聚簇索引的叶子节点存的是主键值，索引和数据分开存储。
查询时如果不是覆盖索引，需要先查索引再根据主键回表查数据。

特点：

可以有多个
查询可能发生回表
维护成本低于聚簇索引

5.什么字段适合当主键?性别能加索引吗?

**适合当主键要符合:**1.唯一且不为空;2.有递增的趋势3.不建议用业务数据作为主键

性别不适合加索引,性别的取值通常只有两三种，区分度极低，即使走索引也会命中大量行，MySQL 优化器往往直接选择全表扫描，索引不仅提升不明显，还会增加写入和维护成本

在表中主键一般用自增ID,不用UUID, UUID是无序的,新插入的数据不一定存储在后面;

6.MySql中的索引是怎么实现的?

一、核心结论

MySQL 的索引本质是数据结构，InnoDB 主要使用 B+Tree 来实现索引。

二、B+Tree 索引是如何工作的？

InnoDB 中，每个索引都是一棵 B+Tree：

非叶子节点：只存 索引键值 + 子节点指针，用于导航
叶子节点：
- 聚簇索引：存 完整行数据
- 二级索引：存 索引列值 + 主键值
叶子节点之间通过双向链表连接，方便范围查询

三、查询过程（面试重点）

1️⃣ 从 B+Tree 根节点开始
2️⃣ 逐层向下查找匹配的索引键
3️⃣ 命中叶子节点
4️⃣ 二级索引则根据主键回表查询完整数据

四、为什么用 B+Tree 而不是其他结构？

树高低，减少磁盘 IO
范围查询友好（> < BETWEEN ORDER BY）
顺序读效率高

7.B+树的特性是什么?

1️⃣ 所有数据只存储在叶子节点,非叶子节点只存储键值和指向子节点的指针
不存真实数据。

2️⃣ 叶子节点按顺序排列，并通过链表相连
非常适合范围查询和排序操作。

3️⃣ 树的高度低，查询效率稳定
所有查询都必须到叶子节点，时间复杂度稳定为 O(log N)。

4️⃣ 每个节点可存放大量索引项
减少树的高度，从而减少磁盘 IO 次数。

5️⃣ 支持顺序访问和随机访问
既适合等值查询，也适合范围查询。

6 自平衡
在插入,删除和更新操作后会自动重新平衡,确保高度相对稳定,有良好的搜索性能;

二、与 B 树的关键区别

B+ 树 只有叶子节点存数据，B 树非叶子节点也存数据
B+ 树 范围查询和顺序访问更高效 ,B树叶子节点没有链表连接;

8.B+树和其他结构相比的优点?

一、相比二叉搜索树 / 红黑树

B+ 树的优势：

高度更低：一个节点可存多个 key，树高远小于二叉树
磁盘 IO 更少：一次 IO 可读取一个节点（页），效率更高
更适合磁盘存储：为磁盘访问而设计

👉 数据库是 IO 密集型系统，B+ 树比红黑树更合适

二、相比 B 树

B+ 树的优势：

非叶子节点不存数据，能存更多索引项
树更矮，查找更快
叶子节点有链表，范围查询和排序效率更高

三、相比 Hash 结构

B+ 树的优势：

支持范围查询（> < BETWEEN ORDER BY）
支持排序、模糊查询
更通用，适用场景更广

9.为什么MySql不用跳表?

B+树的高度在三层时存储的数据可能已经到达千万级别,但对于跳表而言同样去维护千万的数据量,那么所造成的跳表层数过高而导致的磁盘IO次数增多,也就是使用B+树在存储同样的数据下磁盘IO次数更少;

10.联合索引的实现原理?

联合索引（Composite Index） 是指 在一张表的多个字段上同时建立的一个索引，索引中的字段是有顺序的。

使用联合索引时,存在最左匹配原则,也就是按照最左优先的方式进行索引的匹配;

联合索引本质仍是一棵 B+ 树，只是把多个字段按顺序组合成一个索引 key，数据在 B+ 树中先按第一个字段排序，第一个字段相同再按第二个字段排序，依次类推，利用索引的前提是索引里的key是有序的,因此查询时只能从最左字段开始连续使用索引，这就是最左匹配原则的根本原因

11.创建联合索引时要注意什么?

创建联合索引时,要把区分度大的字段排在前面,这样区分度大的字段越有可能被更多的SQL使用到;

区分度: 区分度就是某个字段column不同值的个数除以表的总行数

联合索引的字段顺序非常重要，应将查询最频繁、区分度最高的字段放在最左边，等值条件放在前、范围条件放在后，并且联合索引只能从最左字段开始连续使用，否则会导致索引失效，同时要避免重复创建功能重叠的单列索引，减少索引维护成本。

12.索引失效的情况有哪些?

MySQL 索引失效主要包括：
1）索引列参与运算或函数
2）LIKE 以 % 开头或者以% 结尾的模糊查询时
3）隐式类型转换
4）!=、NOT IN、NOT EXISTS
5）在where字句中,OR 中存在非索引列
6）联合索引未遵循最左前缀原则
7）范围查询导致后续列失效
8）字段区分度低，优化器放弃索引

一、对索引列做运算或函数 —— 必失效

原因

索引里存的是原始值的有序结构，一旦对列做计算，MySQL 无法通过索引定位。

例子

-- ❌ 索引失效
SELECT * FROM user WHERE YEAR(create_time) = 2024;
SELECT * FROM user WHERE age + 1 = 20;

-- ✅ 正确写法
SELECT * FROM user WHERE create_time >= '2024-01-01'
                      AND create_time < '2025-01-01';

二、使用 `LIKE` 以 `%` 开头 —— 无法走索引

原因

B+Tree 索引是从左到右匹配的，前缀不确定无法定位。

例子

-- ❌ 索引失效
SELECT * FROM user WHERE name LIKE '%abc';

-- ✅ 可以使用索引
SELECT * FROM user WHERE name LIKE 'abc%';

📌 补充：LIKE '%abc%' 一定不走普通索引

三、隐式类型转换 —— 索引直接失效（非常坑）

原因

类型不一致时，MySQL 会对索引列做转换，相当于对列使用函数。

例子

-- id 是 INT
-- ❌ 索引失效
SELECT * FROM user WHERE id = '100';

-- ✅ 正确
SELECT * FROM user WHERE id = 100;

📌 面试常说：

“字符串和数字比较，索引必失效”

四、使用 `!=`、`<>`、`NOT IN`、`NOT EXISTS`

原因

这些条件会导致 扫描范围过大，优化器通常放弃索引。

例子

1
2
3

-- ❌ 通常不走索引
SELECT * FROM user WHERE age != 18;
SELECT * FROM user WHERE id NOT IN (1,2,3);

📌 不是 100% 失效，但 绝大多数情况下不会用

五、OR 条件中有列没有索引 —— 整体失效

原因

OR 任意一侧无法使用索引，优化器可能直接全表扫描。

例子

1
2
3

-- name 有索引，age 没有
-- ❌ 索引失效
SELECT * FROM user WHERE name = 'Tom' OR age = 20;

解决方案

-- 拆成两次查询
SELECT * FROM user WHERE name = 'Tom'
UNION
SELECT * FROM user WHERE age = 20;

六、联合索引未遵循最左前缀原则

原因

联合索引是按 (a, b, c) 顺序建立的。

例子

-- 联合索引 (a, b, c)

-- ❌ 索引失效
SELECT * FROM t WHERE b = 2;

-- ❌ 部分失效（只用到 a）
SELECT * FROM t WHERE a = 1 AND c = 3;

-- ✅ 完全生效
SELECT * FROM t WHERE a = 1 AND b = 2;

📌 口诀：

“从左开始，不能跳列”

七、范围查询后面的列无法再使用索引

原因

>、<、BETWEEN 会打断索引连续性。

例子

-- 联合索引 (a, b, c)

-- b、c 无法使用索引
SELECT * FROM t WHERE a = 1 AND b > 10 AND c = 3;

📌 面试常问：

“范围查询会导致后续索引列失效”

八、索引选择性太差（优化器主动放弃）

原因

如果字段重复值太多（如 gender、status），走索引还不如全表扫描。

例子

1 2	-- gender 只有 0 / 1 SELECT * FROM user WHERE gender = 1;

📌 这是优化器决策，不是 SQL 写错

九、使用 `IS NULL / IS NOT NULL`（部分情况）

说明

IS NULL：可能走索引
IS NOT NULL：大概率不走

1	SELECT * FROM user WHERE email IS NULL;

十、使用函数、表达式或 CASE WHEN

1
2
3

-- ❌ 索引失效
SELECT * FROM user 
WHERE CASE WHEN age > 18 THEN 1 ELSE 0 END = 1;

13.什么情况下会回表查询?

一、什么是回表查询

回表查询：

使用 二级索引 查到主键值后，再根据主键回到聚簇索引（主键索引）中取完整行数据 的过程。

只有 InnoDB 才有回表（因为有聚簇索引）。

二、什么情况下会发生回表查询（核心结论）

当使用的是二级索引，并且查询的列不在该索引中时，就会发生回表

14.什么是覆盖索引?

覆盖索引（Covering Index）：查询所需要的所有字段，都能直接从索引中获取，而不需要回表查询主键索引。

15.如果一个列即是单列索引,又是联合索引,单独查他会先走哪个?

MySql优化器会分析每个索引的查询成本,然后选择成本最低的方案来执行sql;

16.索引并不是建的越多越好,建的越多占用的空间越多,写入频繁操作的场景下,维护B+树所付出的性能消耗也越大;

17.索引的优缺点:

**优点:**索引通过有序的数据结构（如 B+Tree）大幅减少数据库查询时需要扫描的数据行数，将全表扫描转化为高效的索引查找，从而显著提升查询性能，并且能够优化 WHERE、ORDER BY、GROUP BY 等操作，在使用覆盖索引时还可以避免回表，减少磁盘 I/O，提高整体系统响应速度

**缺点:**索引会额外占用存储空间，并且在进行 INSERT、UPDATE、DELETE 等写操作时需要同步维护索引结构，增加写入开销和锁竞争;

三、什么场景适合建索引

适合

高频查询字段
WHERE / JOIN / ORDER BY / GROUP BY 中的列
区分度高的字段
主键、外键、唯一约束

18.索引优化:

1️⃣ 根据查询条件建索引（不是根据字段）

索引一定来自 SQL，而不是表结构。

-- 高频 SQL
SELECT order_id, status
FROM orders
WHERE user_id = ? AND status = 1;

✅ 合理索引：

1	(user_id, status)

❌ 错误做法：

1 2	(status) (order_id)

📌 面试表述：

索引应围绕 WHERE 条件设计，而不是围绕字段本身。

2️⃣ 联合索引字段顺序优化（非常重要）

原则

等值查询字段在前
范围查询字段在后
区分度高的字段靠前

1	WHERE user_id = ? AND status = ? AND create_time > ?

✅ 推荐索引：

1	(user_id, status, create_time)

📌 原因：

等值条件可以精确定位
范围查询会截断索引

3️⃣ 利用最左前缀原则

联合索引 (a, b, c) 可用情况：

1
2
3

WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

❌ 不可用：

1 2	WHERE b = 2 WHERE b = 2 AND c = 3

📌 面试口诀：

联合索引从左开始，不能跳列。

4️⃣ 使用覆盖索引，减少回表（性能提升关键）

-- ❌ 回表
SELECT * FROM user WHERE name = 'Tom';

-- ✅ 覆盖索引
SELECT name, age FROM user WHERE name = 'Tom';
-- 建索引
(name, age)

📌 覆盖索引的效果：

少一次 B+Tree 查询
少一次随机 IO

5️⃣ 避免索引失效写法（非常容易被问）

常见失效原因

对索引列使用函数
隐式类型转换
LIKE '%xxx'
OR 中存在非索引列
!=、NOT IN

-- ❌
WHERE YEAR(create_time) = 2024

-- ✅
WHERE create_time >= '2024-01-01'
  AND create_time <  '2025-01-01'

6️⃣ 合理使用范围查询

1 2	-- 联合索引 (a, b, c) WHERE a = 1 AND b > 10 AND c = 3;

📌 结果：

a、b 用索引
c 失效

👉 把 范围条件放最后

7️⃣ 索引区分度优化

1	区分度 = 不重复值 / 总行数

性别、状态：低选择性
用户 ID、订单号：高选择性

单独给低区分度字段建索引，价值不大。

8️⃣ 控制索引数量，避免冗余索引

1
2
3

(a)
(a, b)
(a, b, c)

📌 (a, b, c)
已经可以覆盖 (a)、(a, b) 场景（大部分）

👉 删除冗余索引，减少写开销。

19.前缀索引:

前缀索引：只对字符串字段的前 N 个字符建立索引，而不是对整列建索引，用于在保证一定查询性能的同时，减少索引体积。

使用前缀索引是为了减少索引字段大小,可以增加一个索引页中存储的索引值,有效提高索引的查询速度;在一些大字符串的字段作为索引时,使用前缀索引可以帮助我们减少索引项的大小;

事务:

1.事务的特性是什么?如何实现的?

一、事务的四大特性（ACID）

1. 原子性（Atomicity）

含义：
事务中的所有操作 要么全部成功，要么全部失败回滚，不会只执行一部分。

举例：
A 向 B 转账 100 元：

A 扣 100
B 加 100
这两个操作必须同时成功或同时失败。

如何实现： Undo Log（回滚日志）

InnoDB 在修改数据前，先记录 undo log
如果事务失败或执行 ROLLBACK
利用 undo log 将数据恢复到事务开始前的状态

2. 一致性（Consistency）

含义：
事务执行前后，数据库都必须处于 一致、合法的状态，不破坏约束规则。

举例：

主键不能重复
外键必须存在
转账前后总金额不变

如何实现：由多方面共同保证

原子性（不成功就回滚）
隔离性（事务之间互不干扰）
持久性（提交后数据不丢失）
数据库约束（NOT NULL / UNIQUE / FOREIGN KEY）
应用层业务逻辑校验

一致性不是某一个机制单独实现的，而是最终结果

3.隔离性（Isolation）

含义：
多个事务并发执行时，彼此之间互不影响。

可能出现的问题（并发问题）：

脏读（Dirty Read）
不可重复读（Non-repeatable Read）
幻读（Phantom Read）

隔离级别（由低到高）：

Read Uncommitted
Read Committed
Repeatable Read（MySQL 默认）
Serializable

如何实现：锁 + MVCC

锁机制
- 行锁、表锁
MVCC（多版本并发控制）
- Read View
- undo log 生成历史版本
- 快照读不加锁

InnoDB 在 RR 级别下通过 MVCC + 间隙锁解决幻读问题

4. 持久性（Durability）

含义：
事务一旦提交，数据永久保存，即使数据库宕机也不会丢失。

如何实现：👉 Redo Log（重做日志）

事务提交时，先写 redo log（顺序 IO）
数据页可能还在内存中
崩溃恢复时，通过 redo log 重新恢复已提交的数据

WAL 机制（Write-Ahead Logging）：

先写日志，再写磁盘数据

2.MySql可能出现什么和并发相关的问题?

一、三大经典并发问题（事务并发问题）

1.脏读（Dirty Read）

定义：
一个事务 读取到了另一个事务尚未提交的数据。

示例：

事务 A 修改账户余额，但未提交
事务 B 读取到了这个修改后的余额
事务 A 回滚 → B 读到的是无效数据

出现的隔离级别：

✅ Read Uncommitted
❌ Read Committed 及以上不会出现

解决方式：

提高隔离级别（至少 Read Committed）

2. 不可重复读（Non-repeatable Read）

定义：
同一个事务中，两次读取同一行数据，结果不一致。

示例：

事务 A 查询用户余额 → 100
事务 B 提交修改余额 → 200
事务 A 再查余额 → 200（前后不一致）

出现的隔离级别：

✅ Read Committed
❌ Repeatable Read 及以上不会出现

解决方式：

使用 Repeatable Read
或使用加锁读（SELECT ... FOR UPDATE）

3. 幻读（Phantom Read）

定义：
同一个事务中，两次查询同一范围数据，行数发生变化（多了或少了“幻影行”）。

示例：

事务 A 查询工资 > 10k 的员工 → 5 条
事务 B 插入一条工资 15k 的员工并提交
事务 A 再查 → 6 条（出现幻行）

出现的隔离级别：

✅ Read Committed
❌ InnoDB 的 Repeatable Read 基本解决
❌ Serializable 完全解决

解决方式：

InnoDB：RR + 间隙锁（Next-Key Lock）
或使用 Serializable

3.MySql是如何解决并发问题的? 事务隔离级别有哪些?

1. 通过事务机制保证并发一致性

InnoDB 支持事务，利用 ACID 特性 ,提供多种事务隔离级别保证并发环境下数据的正确性。

示例：

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;

上述操作要么全部成功，要么全部回滚，避免并发更新导致数据不一致。

2. 通过锁机制控制并发访问

锁用于防止多个事务同时修改同一数据。

常见锁类型：

行锁（Row Lock）
表锁（Table Lock）
意向锁（Intention Lock）
间隙锁（Gap Lock）
Next-Key Lock（行锁 + 间隙锁）

示例（行锁）：

START TRANSACTION;
SELECT * FROM account WHERE id = 1 FOR UPDATE;
-- 其他事务无法修改该行
UPDATE account SET balance = balance - 50 WHERE id = 1;
COMMIT;

作用：
防止丢失更新和脏写。

3. 通过 MVCC 解决并发读问题

MVCC（Multi-Version Concurrency Control）允许 读写不互相阻塞。

原理：

每条记录有多个版本
通过 undo log 保存历史版本
Read View 决定当前事务能看到哪个版本

示例：

1 2	START TRANSACTION; SELECT balance FROM account WHERE id = 1;

此时读取的是事务开始时的一致性快照，即使其他事务已提交修改，也不会影响当前事务的读取结果（Repeatable Read 下）。

作用：
解决脏读、不可重复读，提高并发性能

二、MySQL 的事务隔离级别

MySQL 支持 SQL 标准定义的四种事务隔离级别。

1. Read Uncommitted（读未提交）

特点：

可以读取未提交的数据
会发生脏读、不可重复读、幻读

示例：

1	SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

并发问题：
脏读、不可重复读、幻读都可能发生。

2. Read Committed（读已提交）

特点：

只能读取已提交的数据
避免脏读
仍可能发生不可重复读和幻读

示例：

1	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

常见数据库默认隔离级别（如 Oracle）。

3. Repeatable Read（可重复读，MySQL 默认）

特点：

同一事务中多次读取结果一致
避免脏读和不可重复读
InnoDB 基本避免幻读

示例：

1	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

实现方式：
MVCC + 间隙锁 + Next-Key Lock。

4. Serializable（串行化）

特点：

事务串行执行
完全避免所有并发问题
并发性能最低

示例：

1	SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

读提交和可重复读是通过Read View实现的,Read View 是事务在读取数据时生成的快照，用于判断其他事务对数据的修改是否对当前事务可见。它们在创建Read View的时机不同,读提交是在每个语句执行前会重新生成一个Read View,可重复读是在启动事务时生成一个Read View,然后整个事务期间都在用这个Read View;

MySQL 的 Repeatable Read 隔离级别通过 快照读结合 Read View 和 undo log 保证同一事务中多次读取同一行数据结果一致，利用 当前读（SELECT … FOR UPDATE / UPDATE）加行锁和间隙锁（Next-Key Lock） 防止幻读，同时通过 redo log 保证提交数据持久性，从而在高并发环境下实现数据一致性和安全性。

MySQL 的 串行化（Serializable）隔离级别通过 把所有事务都当作顺序执行来处理来实现，核心机制是 将普通的 SELECT 也升级为加锁读（SELECT … LOCK IN SHARE MODE / FOR UPDATE），每次读取和修改数据都会加 行锁或间隙锁，防止其他事务对同一数据或范围进行插入、更新或删除，从而完全避免脏读、不可重复读和幻读，但并发性能最低，事务必须串行执行。

4.MVCC实现原理:

2. MVCC的核心原理

2.1 版本控制

每一行数据都会有 版本信息，典型包括：
- trx_id：最后一次修改该行的事务ID
- roll_pointer 或 undo_log：指向旧版本数据
当事务读取数据时，它 根据自己的事务ID选择可见版本，而不直接锁住数据。

2.2 可见性判断

假设事务 T 的事务ID 为 T_id：

读取一行数据时：
1. 如果行的创建事务ID > T_id → 数据不可见（还未提交）
2. 如果行的删除事务ID ≤ T_id → 数据不可见（已被删除）
3. 否则 → 数据可见

这样就保证了 快照隔离（Snapshot Isolation）。

3. MySQL InnoDB 中的 MVCC 实现

InnoDB 是典型使用 MVCC 的存储引擎。

3.1 数据行结构

InnoDB 为每行数据维护两列隐藏字段：

DB_TRX_ID：创建或修改这行数据的事务ID
DB_ROLL_PTR：指向回滚段（undo log），用于构建旧版本

3.2 读取操作

SELECT … 默认使用 快照读（Snapshot Read）：
- 不加锁，不阻塞写
- 根据 DB_TRX_ID 和事务ID判断可见性
SELECT … FOR UPDATE 或 UPDATE：
- 会加行锁，防止其他事务写

3.3 写入操作

InnoDB 不直接覆盖原数据：
1. 写入新版本到行
2. 原版本保存在 undo log
3. 事务提交后，新版本可见

4. MVCC的优点

读操作 不阻塞写操作
写操作 不阻塞读操作
支持 快照隔离，减少死锁的可能性

5. 注意点

MVCC 无法解决幻读问题（需要锁定读或可重复读 + gap lock）
每次更新都会生成版本，undo log 会占用存储，需要定期清理

6. 简单示意图

时间轴：
T1: 读取快照
T2: 修改数据 -> 生成新版本
T1: 仍然看到旧版本
T2提交后,新事务看到新版本

数据库内部是通过 隐藏列 + undo log + 事务ID 来实现多版本。
每个事务读取的都是一个“快照”，而不是最新数据。

5.一条update也是原子性的,通过加锁和undo log日志保证原子性的;

6.滥用事务的弊端:

事务的资源在事务提交之后才会释放的,比如存储资源,锁; 滥用事务会导致性能下降和并发问题：长事务占用锁资源和 undo/redo 日志，增加死锁风险和回滚成本，同时降低系统吞吐量，并可能造成 MVCC 老版本堆积，占用大量存储，影响数据库稳定性和维护效率。

锁

1.MySql中有哪些锁?

一、按锁的作用范围（粒度）分类 ⭐⭐⭐

1️⃣ 全局锁（Global Lock）

作用范围：整个数据库实例
常见命令：
1
FLUSH TABLES WITH READ LOCK;
特点：
- 只允许读，不允许写
- 常用于 全库备份
缺点：阻塞所有写操作，影响业务

📌 面试点：线上备份一般不用全局锁，而是用 mysqldump + single-transaction

2️⃣ 表级锁（Table Lock）

作用范围：整张表
类型：
- 表读锁（READ）
- 表写锁（WRITE）
特点：
- 加锁快
- 并发性能差
使用引擎：
- MyISAM
- InnoDB 在某些 DDL 操作时也会用

1 2	LOCK TABLE table_name READ; LOCK TABLE table_name WRITE;

3️⃣ 行级锁（Row Lock）⭐⭐⭐（重点）

作用范围：表中的某一行
实现引擎：InnoDB
特点：
- 锁粒度最小
- 并发性能最好
- 加锁成本高

📌 注意：
InnoDB 的行锁是 基于索引实现的，没走索引会升级为表锁

二、按锁的功能分类（并发控制）⭐⭐⭐

4️⃣ 共享锁（S Lock / Read Lock）

含义：允许多个事务读同一行
不允许其他事务写

1	SELECT * FROM table WHERE id=1 LOCK IN SHARE MODE;

5️⃣ 排他锁（X Lock / Write Lock）

含义：独占资源
读写都不允许其他事务

1	SELECT * FROM table WHERE id=1 FOR UPDATE;

📌 增删改操作默认加排他锁

三、InnoDB 特有的锁（面试高频）⭐⭐⭐⭐

6️⃣ 意向锁（Intention Lock）

作用：表示事务“将要”在某些行上加锁
类型：
- 意向共享锁（IS）
- 意向排他锁（IX）
特点：
- 表级锁
- 用于快速判断表中是否存在行锁
用户不能手动加，由 InnoDB 自动维护

📌 面试一句话：

意向锁是为了提高表锁和行锁共存时的加锁效率

7️⃣ 间隙锁（Gap Lock）

锁定的是索引之间的“间隙”
防止幻读
只在 RR（可重复读）隔离级别下生效

例子：

1	SELECT * FROM user WHERE age BETWEEN 10 AND 20 FOR UPDATE;

👉 会锁住 (10,20) 之间的区间

8️⃣ 临键锁（Next-Key Lock）⭐⭐⭐

行锁 + 间隙锁
InnoDB 默认锁方式
解决幻读的核心机制

📌 本质：

1	(前一个索引值, 当前索引值]

2.两个线程的update语句同时处理一条数据会不会阻塞?

会阻塞。 在 InnoDB 中，两个线程同时 UPDATE 同一条数据时，先获得行级排他锁（X 锁）的事务执行，后来的事务会被阻塞等待锁释放。

3.两条update语句处理一张表的不同的主键范围记录,一个<10,一个>15,会不会阻塞?

不会阻塞,因为锁住的范围不一样,不会形成冲突;

第一条update sql (<10)锁住的范围是(- ∞,10);

第二条update sql (>15)锁住的范围是(15,+ ∞);

4.如果两个范围不是主键或者索引,还会阻塞吗?

如果两个范围查询的字段不是索引的话,那就代表update没有用到索引,这时候就触发了全表扫描,全部索引都会加行级锁,这时候第二条update执行的时候就会阻塞了;

日志

1.日志文件分为哪几种?

1️⃣ 重做日志（Redo Log）⭐⭐⭐⭐

所属层级：InnoDB 存储引擎
作用：保证事务的持久性（Durability）
记录内容：数据页的物理修改
特点：
- 先写日志，再写磁盘（WAL）
- 循环写
- MySQL 崩溃后可通过 redo log 恢复已提交事务

Redo Log 保证已提交事务在数据库宕机后不丢失。

2️⃣ 回滚日志（Undo Log）⭐⭐⭐⭐

所属层级：InnoDB 存储引擎
作用：
1. 事务回滚
2. 实现 MVCC（多版本并发控制）
记录内容：数据修改前的逻辑值
特点：
- 逻辑日志
- 事务提交后不会立即删除（供快照读使用）

Undo Log 用于事务回滚，并为 MVCC 提供历史版本数据。

3️⃣ 二进制日志（Binlog）⭐⭐⭐⭐

所属层级：MySQL Server 层
作用：
- 主从复制
- 增量数据恢复
记录内容：
- 所有 DDL
- 所有 DML（INSERT / UPDATE / DELETE）
不记录：SELECT
三种格式：
- STATEMENT
- ROW（推荐）
- MIXED

Binlog 是 Server 层日志，用于复制和数据恢复。

4️⃣ 中继日志（Relay Log）⭐⭐⭐

所属层级：从库（Slave）
作用：
- 临时保存从主库拉取的 Binlog
- 供从库 SQL 线程重放
本质：
- Binlog 在从库上的本地副本

Relay Log 是从库用于缓存主库 Binlog 的中间日志。

5️⃣ 慢查询日志（Slow Query Log）⭐⭐⭐

作用：记录执行时间超过阈值的 SQL
主要用途：
- SQL 性能分析
- 索引优化

关键参数：

1 2	slow_query_log = ON long_query_time = 2

慢查询日志用于发现和优化性能较差的 SQL。

六、面试对比速记表

日志类型	层级	主要作用	关键词
Redo Log	InnoDB	崩溃恢复	持久性
Undo Log	InnoDB	回滚 / MVCC	历史版本
Binlog	Server	复制 / 恢复	数据变更
Relay Log	Slave	重放 Binlog	主从复制
Slow Log	Server	SQL 优化	性能分析

2.Binlog

一、Binlog 是什么？

Binlog（Binary Log） 是 MySQL Server 层 的日志,所有存储引擎都可以使用，用来记录所有会修改数据的操作;

Binlog 是追加写,写满一个文件,就会创建一个文件继续写,不会覆盖以前的日志,保存的是全量的日志,用于备份恢复,主从复制;

主要用于：

主从复制
数据恢复（增量恢复）
审计数据变更

关键点（面试必说）：

属于 Server 层（不依赖存储引擎）
记录 逻辑操作
不记录 SELECT

二、Binlog 记录什么，不记录什么？

记录的内容

DML：INSERT / UPDATE / DELETE
DDL：CREATE / ALTER / DROP
事务边界：BEGIN / COMMIT

不记录的内容

SELECT
只读查询
回滚的事务（最终不会落盘）

三、Binlog 的三种格式（面试高频）

1. STATEMENT（语句格式）

记录：原始 SQL

1	UPDATE user SET score = score + 1 WHERE age > 18;

优点：

日志体积小

缺点：

可能主从不一致
与执行环境有关（如 NOW()、UUID()）

生产环境基本不用

2. ROW（行格式，推荐）

记录：哪一行被改了，改成什么样
不记录 SQL 本身
基于行的前后镜像

优点：

绝对不会主从不一致
安全可靠

缺点：

日志体积大

生产默认 / 推荐

3. MIXED（混合格式）

STATEMENT + ROW
MySQL 自动选择

实际使用较少

3.Undo log日志的作用是什么?

1. 用于事务回滚（Rollback）

当事务执行失败或主动 ROLLBACK
InnoDB 根据 Undo Log 中的旧值
把数据恢复到事务开始前的状态

Undo Log 记录修改前的数据，用于事务失败时回滚。

2. 用于 MVCC（多版本并发控制）

在 REPEATABLE READ / READ COMMITTED 隔离级别下
普通 SELECT 不加锁
通过 Undo Log + Read View 实现一致性读

原理简述：

每次更新都会生成一条 Undo Log
多条 Undo Log 串成 版本链
SELECT 根据 Read View 找到可见版本

Undo Log 为 MVCC 提供历史版本，实现非阻塞读。

二、Undo Log 记录的是什么？

逻辑日志
记录内容：
- INSERT：记录“删除该行”的反向操作
- DELETE：记录“插入该行”的反向操作
- UPDATE：记录“修改前的旧值”

不是物理页变化（这点和 Redo Log 不同）

三、Undo Log 的生命周期（面试常问）

事务开始
修改数据前，先写 Undo Log
执行数据修改
事务提交：
- Undo Log 不会立刻删除
- 供其他事务做一致性读
没有事务再需要该版本
- 由 Purge 线程 清理

面试点：

Undo Log 提交后延迟删除

4.有了undolog还要redolog的原因:

在 MySQL 的 InnoDB 引擎中，Undo Log 和 Redo Log 解决的是两个不同阶段、不同维度的问题，因此即使有了 Undo Log，仍然必须要有 Redo Log。
Undo Log 记录的是数据被修改之前的逻辑值，它的主要作用是在事务执行失败或主动回滚时，将数据恢复到修改前的状态，同时为 MVCC 提供历史版本数据，从而实现一致性读。但 Undo Log 并不保证数据已经安全地写入磁盘，它只能“撤销”已经发生的修改，不能“重做”已经提交但尚未落盘的数据。
而 Redo Log 记录的是数据页的物理修改信息，遵循先写日志再写数据页的 WAL 机制，在事务提交前即使数据页还停留在内存中，只要 Redo Log 已经刷盘，数据库在发生宕机后就可以通过重放 Redo Log 来恢复这些已提交的修改，从而保证事务的持久性。
因此，如果只有 Undo Log，在事务提交后数据库宕机的情况下，已提交的数据可能会丢失；如果只有 Redo Log，又无法支持事务回滚和 MVCC。正是因为两者职责不同、相互补充，InnoDB 才同时引入 Undo Log 和 Redo Log，以完整保证事务的原子性、一致性和持久性。

5.redolog怎么保持持久性?

一、WAL（Write-Ahead Logging，先写日志）

InnoDB 采用 WAL 机制：
数据页真正写入磁盘之前，必须先把对应的 Redo Log 写入磁盘。
事务执行时：
- 修改数据页（内存中）
- 同时生成 Redo Log，写入 Redo Log Buffer
事务提交时：
- Redo Log 刷盘成功
- 事务即可提交成功

关键点：

即使数据页还在内存中，只要 Redo Log 已落盘，事务就具备持久性。

二、Redo Log 的顺序写入机制（顺序 IO）

Redo Log 文件采用 顺序写入 + 循环写
每次只需要在日志文件末尾追加写入
不需要随机定位数据页位置

优势：

顺序 IO 性能高、失败概率低
减少磁盘寻址开销
提高日志刷盘成功率

面试点：

用顺序写日志，换取数据页的随机写延迟。

三、Checkpoint 机制（保证日志可回收）

数据页最终还是要落盘（脏页刷盘）
当脏页被写入磁盘后：
- 对应的 Redo Log 就 不再需要
InnoDB 会定期触发 Checkpoint：
- 将一批脏页刷盘
- 标记 Redo Log 的安全位置
- 释放旧日志空间

Checkpoint 触发条件包括：

Redo Log 空间不足
定时触发
系统空闲

Checkpoint 的作用：

防止 Redo Log 无限增长
缩短数据库崩溃后的恢复时间
保证 Redo Log 可持续使用

6.binlog两阶段提交过程是怎么样的?

一、为什么需要两阶段提交（背景）

Redo Log 属于 InnoDB 引擎层
Binlog 属于 MySQL Server 层
两种日志由不同模块维护
如果提交顺序不一致，可能导致：
- 主从数据不一致
- 崩溃后数据不一致

两阶段提交的目的：

保证 Redo Log 和 Binlog 的一致性

二、Binlog 两阶段提交的完整流程（核心）

以一个 UPDATE 事务为例：

第一阶段：Prepare 阶段（准备阶段）

客户端发起事务并执行 DML

数据页在内存中被修改（产生脏页）
同时生成 Undo Log

生成 Redo Log（prepare 状态）

InnoDB 写入 Redo Log
Redo Log 状态标记为 prepare
Redo Log 刷盘成功

此时：

数据页未必落盘
事务 还未真正提交

第二阶段：Commit 阶段（提交阶段）

Server 层写 Binlog

将该事务对应的 Binlog 写入 Binlog 文件
Binlog 刷盘成功

InnoDB 提交事务

将 Redo Log 状态由 prepare → commit
事务正式提交完成

7.update语句具体执行过程是怎么样的?

一条 UPDATE 语句会先在 Server 层完成解析、优化和执行计划生成，然后在 InnoDB 中定位记录并加排他锁，生成 Undo Log，修改内存数据页并生成 Redo Log，在事务提交时通过两阶段提交先写 Redo Log 再写 Binlog，最后提交事务并异步刷盘数据页。

为什么数据页可以延迟刷盘？

数据页和 Redo Log 的刷盘是两个独立过程

Redo Log 刷盘
- 发生在事务提交时
- 体积小、顺序写
- 成本低、速度快
数据页刷盘
- 发生在后台
- 数据量大、随机写
- 成本高、速度慢

事务提交时，只要求 Redo Log 刷盘成功，不要求数据页刷盘

在事务执行过程中redolog被存储在redolog buffer中,也就是在内存中,事务提交后,会把redolog写入磁盘;

8.MySql两次写

<具体看小林>

性能调优

1.MySql的explain有什么用?

一句话结论：

EXPLAIN 用于查看 MySQL 如何执行一条 SQL 语句，主要用来分析查询是否走索引、执行计划是否合理，从而进行 SQL 性能优化。

一、EXPLAIN 的主要作用（分点）

1. 查看 SQL 的执行计划

显示 MySQL 在执行 SQL 前生成的 执行计划
包括：
- 表的访问顺序
- 连接方式
- 扫描方式

2. 判断是否使用索引（最重要）

可以看到：
- 是否走索引
- 使用了哪个索引
- 是否是全表扫描

面试点：

优化 SQL 的第一步通常是 EXPLAIN

3. 分析扫描行数和成本

通过 rows 字段
估算 MySQL 需要扫描多少行数据
用于判断 SQL 是否高效

4. 分析 join 的执行方式

判断：
- 驱动表是谁
- join 类型是否合理
避免大表驱动小表

5. 辅助定位慢 SQL 的原因

结合慢查询日志
找出：
- 没走索引
- 索引失效
- 索引选择不合理的问题

二、EXPLAIN 常见关键字段（面试高频）

1 `id`

SQL 执行顺序
值越大越先执行

2. `type`（最重要）

表示访问类型（性能由好到差）：

1	system > const > eq_ref > ref > range > index > ALL

ALL = 全表扫描（需优化）

3. `key`

实际使用的索引
为 NULL 表示未使用索引

4. `rows`

预估扫描行数
越小越好

5. `Extra`

额外信息
常见值：
- Using index（覆盖索引）
- Using where
- Using filesort（需优化）
- Using temporary（需优化）

三、EXPLAIN 能发现哪些问题？（总结）

是否 全表扫描
是否 索引失效
是否使用 覆盖索引
是否存在：
- filesort
- temporary table

2.怎么查看表的索引?

一、最常用方式

1. `SHOW INDEX FROM 表名;`（最常用）

1	SHOW INDEX FROM user;

可以看到：

索引名（Key_name）
是否唯一（Non_unique）
索引列（Column_name）
索引顺序（Seq_in_index）
索引类型（BTREE / FULLTEXT / HASH）
基数（Cardinality，区分度）

我一般用 SHOW INDEX FROM table 查看表上所有索引及其字段组成。

二、通过建表语句查看（结构最清晰）

2. `SHOW CREATE TABLE 表名;`

1	SHOW CREATE TABLE user;

特点：

能看到 完整的索引定义
包括：
- 主键
- 唯一索引
- 普通索引
- 联合索引

适合：

排查索引是否建错
查看联合索引顺序

三、、从系统表查看（偏底层 / 高级）

3. `information_schema.statistics`

SELECT *
FROM information_schema.statistics
WHERE table_schema = 'db_name'
  AND table_name = 'user';

3.一张表发现查询速度很慢,有什么解决方法?

一、总体排查思路

查询慢通常从 SQL → 索引 → 执行计划 → 表结构与数据 → 系统层面 逐层排查。

二、具体解决方法（分点）

1. 确认是不是慢 SQL（第一步）

开启慢查询日志：

1	SHOW VARIABLES LIKE 'slow_query_log';

找出执行时间长、频率高的 SQL
确定是 哪条 SQL 慢，而不是“感觉慢”

优化一定从慢查询日志开始

2. 使用 EXPLAIN 分析执行计划

1	EXPLAIN SELECT ...;

重点关注：

type 是否为 ALL（全表扫描）
key 是否为 NULL
rows 是否过大
Extra 是否有：
- Using filesort
- Using temporary

常见问题：

没走索引
索引失效
扫描行数过多

3. 优化索引（最核心）

（1）该建索引却没建

WHERE / JOIN / ORDER BY / GROUP BY 中的字段
高区分度字段优先

（2）索引失效场景

对索引列使用函数
隐式类型转换
like %xxx
or 混用索引与非索引字段

（3）联合索引不合理

不符合最左前缀原则
字段顺序不合理

4. 优化 SQL 写法

避免 SELECT *
减少返回列
使用覆盖索引
拆分复杂 SQL
用 EXISTS 替代 IN（大数据量）

示例：

-- 不推荐
SELECT * FROM orders WHERE user_id = 1;

-- 推荐
SELECT id, status FROM orders WHERE user_id = 1;

5. 检查表数据量和表结构

表是否数据量过大
是否存在大量历史数据
字段类型是否合理（VARCHAR 长度过大）

解决方案：

分表 / 分库
冷热数据分离
归档历史数据

6. 检查锁和事务问题（update / select for update）

是否被行锁或表锁阻塞
长事务导致查询等待

查看：

1	SHOW ENGINE INNODB STATUS;

7. 系统层面优化（最后手段）

磁盘 IO 是否成为瓶颈
Buffer Pool 是否过小
是否频繁 Full GC
QPS 是否超过数据库承载能力

解决：

增大内存
加缓存（Redis）
读写分离

4.如果explain用到的索引不正确的话,有什么办法干预?

可以使用force index,强制走索引;

例如:

1
2
3

SELECT *
FROM orders FORCE INDEX (idx_user_id)
WHERE user_id = 1001;

架构

1.MySql主从复制:

主从复制是一种数据同步架构：

主库（Master）：
负责 写操作（INSERT / UPDATE / DELETE）
从库（Slave / Replica）：
通过复制主库的数据，主要负责 读操作（SELECT）

核心目标：

读写分离
高可用
数据备份
横向扩展读能力

二、主从复制的工作流程（重点必考）

1. Master 端

所有 数据变更操作
先写 Redo Log
再写 Binlog（二进制日志）

binlog 是主从复制的核心

2. Slave 端（三个线程）

（1）IO Thread

向 Master 发送请求
拉取 binlog
写入本地的 relay log（中继日志）

（2）SQL Thread

读取 relay log
重放 SQL / 行变更
应用到从库数据中

（3）（MySQL 5.7+）多线程 SQL Worker

并行回放，提高复制性能

3. 复制完整流程总结

Master 写数据
   ↓
生成 binlog
   ↓
Slave IO 线程拉取 binlog
   ↓
写入 relay log
   ↓
Slave SQL 线程重放
   ↓
从库数据同步完成

MySQL 主从复制通过 binlog 实现，主库写 binlog，从库拉取并重放 binlog，从而保持数据一致。

2.主从延迟有什么处理方法?

强制走主库方案:对于大事务或资源密集型操作,直接在主库上执行,避免从库的额外延迟;

3.分表和分库是什么?有什么区别?

一、一句话理解

分表：把一张大表拆成多张小表，解决单表数据量过大问题
分库：把数据分散到多个数据库中，解决数据库整体压力和并发瓶颈

二、什么是分表（Table Sharding）

1. 定义

分表是指：
将一张数据量非常大的表，按一定规则拆分成 多张结构相同的小表。

例如：

user_0
user_1
user_2
...

2. 为什么要分表

当单表数据量过大时会出现：

B+Tree 索引层级变深
查询、更新变慢
单表锁竞争严重
表文件过大，维护困难

一般经验值：

单表千万级（1000 万）以上就要考虑分表

3. 分表方式

（1）水平分表（最常用）

按行拆
每个表字段相同，数据不同

1 2	user_0 -- user_id % 4 = 0 user_1 -- user_id % 4 = 1

✅ 常见
❌ 跨表查询麻烦

（2）垂直分表

按列拆
冷热字段分离

1 2	user_base(id, name, phone) user_ext(id, avatar, intro)

三、什么是分库（Database Sharding）

1. 定义

分库是指：
将数据分散到 多个数据库实例 中。

例如：

1
2
3

db_0.user
db_1.user
db_2.user

2. 为什么要分库

单个数据库实例存在瓶颈：

连接数有限
CPU / 内存 / IO 有上限
写压力集中
容灾能力差

分库是为了扩展并发和写能力

3. 分库方式

（1）垂直分库（业务拆分）

1
2
3

user_db
order_db
pay_db

微服务常用

（2）水平分库（数据拆分）

1 2	order_db_0 order_db_1

按 user_id / order_id 分散数据

四、分表 vs 分库（重点对比表）

对比维度	分表	分库
拆分对象	表	数据库
解决问题	单表数据量	并发 / 写压力
数据量	还是在一个库	分散到多个库
实现复杂度	较低	较高
跨节点事务	不涉及	涉及（分布式事务）
常见搭配	单库多表	多库多表

文章作者: John Doe

文章链接: http://example.com/2025/12/20/%E9%9D%A2%E8%AF%95-MySql/

java

SQL基础:

1.NOSQL和SQL的区别:

选择SQL,NOSQL的考虑因素:

1.ACIDvsBASE

2.扩展性对比

2.数据库三大范式:

3.MySql连表查询的方式:

4.MQSQL如何避免重复插入数据:

1.使用UNIQUE约束;

2.使用INSERT……ON DUPLICATE KEY UPDATE;

3.使用INSERT IGNORE;

5.CHAR和VARCHAR的区别:

6.INT(1)和INT(10)在MYSQL中的不同:

关键说明（面试点）：

7.MYSQL中三种text类型的最大长度:

8.IP地址怎么在数据库里存储:

方案一：INT / UNSIGNED INT（推荐）

方案二：VARCHAR(15)（不推荐但常见）

三、IPv6 的存储方式（进阶）

✅ BINARY(16)（推荐）

9.外键约束:

10.MYSQL中的IN和EXISTS关键字:

1.IN关键字:

2.EXISTS关键字:

11.MYSQL中的一些基本函数:

1️⃣ 字符串函数（String Functions）

2️⃣ 数值函数（Numeric Functions）

3️⃣ 日期时间函数（Date & Time Functions）

4️⃣ 聚合函数（Aggregate Functions）

12.SQL查询语句的执行顺序是怎么样的:

✅ 正确的执行顺序是：

13.如何用MySql实现一个可重入锁:

一.表结构设计（关键）

五、加锁逻辑（核心）

1️⃣ 第一次加锁（抢锁）

2️⃣ 重入加锁（同一持有者）

六、释放锁逻辑（必须成对）

1️⃣ 减少重入次数

2️⃣ 真正释放锁（最后一次）

完整流程

存储引擎:

1.执行一条SQL请求的过程是什么?

执行一条 SQL 的完整流程（重点）:

1️⃣ 客户端发送 SQL 请求

2️⃣ 连接层（Connection Management）

3️⃣ 查询缓存（Query Cache，已淘汰）

4️⃣ SQL 解析器（Parser）

5️⃣ 预处理器（Preprocessor）

6️⃣ 查询优化器（Optimizer）

7️⃣ 执行阶段

执行流程图

2.MySql的引擎:

3.InnoDB和MyISAM的区别:

1️⃣ 事务支持

2️⃣ 锁机制

3️⃣ 崩溃恢复

4️⃣ 外键

5️⃣ 存储结构

4.数据管理里,数据文件大体分为哪几种数据文件?

.opt 文件

.frm 文件

.ibd 文件

一句话总结:

索引:

1.索引是什么?有什么好处?

2.索引的分类:

3.哈希索引的使用场景:

4.MySql中聚簇索引和非聚簇索引的区别:

一、聚簇索引（Clustered Index）

二、非聚簇索引（二级索引 / Secondary Index）

5.什么字段适合当主键?性别能加索引吗?

6.MySql中的索引是怎么实现的?

一、核心结论

二、B+Tree 索引是如何工作的？

三、查询过程（面试重点）

四、为什么用 B+Tree 而不是其他结构？

7.B+树的特性是什么?

二、与 B 树的关键区别

8.B+树和其他结构相比的优点?

一、相比 二叉搜索树 / 红黑树

一、相比二叉搜索树 / 红黑树

二、使用 `LIKE` 以 `%` 开头 —— 无法走索引

四、使用 `!=`、`<>`、`NOT IN`、`NOT EXISTS`

九、使用 `IS NULL / IS NOT NULL`（部分情况）