数据库面试精讲：MySQL 核心问题梳理

最新推荐文章于 2025-08-25 09:26:33 发布

Qt程序员

最新推荐文章于 2025-08-25 09:26:33 发布

阅读量859

点赞数 23

CC 4.0 BY-SA版权

分类专栏： C++ 文章标签：数据库面试 mysql 八股文数据库面试题 sqlite 八股文面试题

本文链接：https://blog.csdn.net/hw5230/article/details/150111052

C++ 专栏收录该内容

74 篇文章

订阅专栏

事务的四大特性是什么？

首先得提一下ACID，这可是数据库事务的灵魂所在：

原子性（Atomicity）：要么全部成功，要么全部失败回滚。
一致性（Consistency）：确保数据在事务前后都处于一致状态，就像A和B两个人转账前后钱总数不变一样。
隔离性（Isolation）：事务之间相互隔离，防止脏读、不可重复读等问题。
持久性（Durability）：一旦提交，即便系统崩溃也不影响已提交的数据。

那么，事务的隔离级别有哪些？

这里头有四个等级：

Serializable：最高级别，串行化处理，彻底解决幻读问题。
Repeatable read：默认级别，解决了不可重复读的问题。
Read committed：能避免脏读。
Read uncommitted：最低级别，所有事务都可以看到其他未提交的变更。

不可重复读 vs 脏读： 脏读是读到了别人没提交的脏数据；不可重复读是读到了别人提交了的修改。
幻读 vs 不可重复读： 都是读了别人提交的事务，但不可重复读重点是改了数据值，幻读重点是新增或删除了数据行。

索引那些事儿

索引就像是给数据库开了个加速通道，但也有它的两面性：

优点：加快查找速度、优化排序和连接操作。

缺点：占用额外空间，增删改效率受影响。

什么事索引？

简单说，它就是数据库表的“目录”！是存储引擎搞出来帮你加速查表的一种数据结构！没它？查数据得全表扫描，慢如蜗牛！有它？直接按目录翻，快得飞起！

📈 索引的优缺点？

优点：

- 查找速度快。
- 排序、分组、连接也快。

缺点：

- 占空间。
- 插入、更新、删除慢，因为要维护索引。

🎯 索引的作用？

数据库的数据都存磁盘上，查数据要是不加索引，就得全盘扫描，那叫一个慢。加了索引，就像查字典用拼音表，直接翻几页就找到了，B+树一般就2~4层，最多查4次磁盘，效率起飞！

✅ 什么情况下需要建索引？

经常被用来查询的字段。
经常用来连接的字段。
经常用来排序的字段。

❌ 什么情况下不建索引？

where里用不到的字段。
表太小，建了也白搭。
经常被增删改的字段。
参与计算的字段。
区分度低的字段（比如性别，男和女，建了也没啥用）。

📚 索引的数据结构有哪些？

B+树索引：MySQL默认，支持范围查询、排序，适合数据库。

mysql> show index from blog\G;*************************** 1. row ***************************        Table: blog   Non_unique: 0     Key_name: PRIMARY Seq_in_index: 1  Column_name: blog_id    Collation: A  Cardinality: 4     Sub_part: NULL       Packed: NULL         Null:   Index_type: BTREE      Comment:Index_comment:      Visible: YES   Expression: NULL

哈希索引：适合精确查询，比如等于、in这种，但不支持范围、排序。

🔍 Hash索引和B+树索引的区别？

特性	Hash索引	B+树索引
排序支持	不支持	支持
范围查询	不支持	支持
模糊匹配	不支持	支持
多列索引	不支持最左匹配	支持最左匹配
性能稳定性	不稳定（哈希冲突）	稳定

🤔 为什么B+树比B树更适合实现数据库索引？

叶子节点全存数据，方便扫库。
非叶子节点只存索引，一页能放更多节点，减少I/O。
查询路径一样长，查询效率稳定。
区间查询效率高，适合数据库频繁的范围查询。

📁 索引有哪些分类？

主键索引（PRIMARY）：唯一、非空，一个表只能一个。
唯一索引（UNIQUE）：值唯一，允许空值。
组合索引（复合索引）：多个字段组合，遵循最左前缀原则。
全文索引（FULLTEXT）：只能在MyISAM引擎中用，适合长文本。

📌 什么是最左匹配原则？

对(a, b, c)建索引，查询条件是a、ab、abc都可以走索引；bc不行。遇到范围查询（>、<、like等）后面的字段就失效了。

如下图，对(a, b) 建立索引，a 在索引树中是全局有序的，而 b 是全局无序，局部有序（当a相等时，会根据b进行排序）。

当a的值确定的时候，b是有序的。例如a = 1时，b值为1，2是有序的状态。当执行a = 1 and b = 2时a和b字段能用到索引。而对于查询条件a < 4 and b = 2时，a字段能用到索引，b字段则用不到索引。因为a的值此时是一个范围，不是固定的，在这个范围内b的值不是有序的，因此b字段无法使用索引。

📦 什么是聚集索引？

InnoDB的主键索引就是聚集索引，叶子节点直接存数据。主键没指定？那它会选第一个不为空的唯一索引。再没有？它就偷偷给你加个隐藏主键，长度6字节，自增。

🧠 什么是覆盖索引？

查询字段全部在索引里，不需要回表查询，这就叫覆盖索引。用explain看，Extra列会显示Using index。

⚙️ 索引的设计原则？

区分度越高越好。
尽量用短索引。
索引不是越多越好。
遵循最左前缀原则。

💥 索引什么时候会失效？

不用最左字段。
like以%开头。
类型不一致，隐式转换。
对字段做运算。
or连接多个条件。

📚 什么是前缀索引？

对付超长字符串字段（比如网址、长文本）建索引的妙招！不索引整个字符串，只索引前N个字符。这样索引文件小很多，速度快很多！精髓在于：N要选得足够大，让前缀的区分度接近整个字段的区分度，保证索引效率。

ALTER TABLE table_name ADD KEY(column_name(prefix_length)); -- 比如 email(10) 索引前10个字符

💾 常见的存储引擎有哪些？

InnoDB：默认引擎，支持事务、行锁、外键、MVCC。
MyISAM：速度快，但不支持事务、行锁。
MEMORY：数据在内存中，速度快，但重启就没了。
ARCHIVE：适合存历史数据，压缩好，但不支持索引。

🤼 MyISAM 和 InnoDB 的区别？

特性	MyISAM	InnoDB	谁赢了？
行级锁	❌ 只有表锁	✅ 支持行级锁和表锁	InnoDB (并发强)
事务	❌	✅	InnoDB
崩溃后安全恢复	❌ (易丢数据)	✅	InnoDB
外键约束	❌	✅	InnoDB
MVCC (多版本控制)	❌	✅	InnoDB (高并发)
聚集索引	❌ (堆表)	✅	InnoDB
缓存	只缓存索引	缓存索引+数据	InnoDB
全文索引	✅	✅ (>=5.6)	平手
适用场景	读多写少、小数据、只读	高并发读写、事务安全、大数据	看需求

🔄 MVCC 实现原理？

MVCC (多版本并发控制) 就是数据库玩的一个“时空穿越术”！同一份数据，保留多个历史版本（靠undo log版本链），让不同事务能看到数据在不同时间点的样子。核心目标：提高并发读性能！比直接加锁效率高多了！

实现靠两板斧：

版本链： 藏在每行数据里的三个“时光机”字段：
- DB_TRX_ID (6字节)：搞出这个版本的事务ID。靠ID大小判断事务谁先谁后。
- DB_ROLL_PTR (7字节)：回滚指针。指向这条记录上一个版本在undo log里的位置。靠这个指针把各个版本像糖葫芦一样串起来！
- DB_ROW_ID (6字节)：行ID。如果表没设主键，InnoDB自动生成这个当聚簇索引键。

Read View (读视图)： 相当于事务在启动时给数据库拍了个快照。它记录了：
- 当前活跃事务ID列表 (启动时还未提交的事务)。
- 最小活跃事务ID (up_limit_id)。
- 预分配的下一个事务ID (low_limit_id)。
- 创建该Read View的事务ID。

判断数据版本可见性 (时光机规则)：

当事务要读一行数据时，它用自己的Read View去版本链里找自己能看到的那个版本：

如果版本的事务ID DB_TRX_ID < up_limit_id：说明这个版本是Read View创建前就提交了的，可见。
如果 DB_TRX_ID >= low_limit_id：说明这个版本是Read View创建后才生成的，不可见。顺着版本链找上一个版本再判断。
如果 up_limit_id <= DB_TRX_ID < low_limit_id：
- 如果 DB_TRX_ID 在Read View的活跃事务列表里：说明这个版本是Read View创建时还没提交的事务改的，不可见。找上一个版本。
- 如果 DB_TRX_ID 不在活跃事务列表里：说明这个版本在Read View创建时已经提交了，可见。

不同隔离级别拍快照时机不同：

读已提交 (RC)： 每次 SELECT 都拍一个新的快照 (Read View)。能看到别人最新提交的改动。
可重复读 (RR)： 第一次 SELECT 时拍个快照，之后一直用这个快照。保证在整个事务里，多次读同一数据结果都一样 (Repeatable Read)。

👀 快照读和当前读的区别？

快照读： 读的是历史快照版本！普通 SELECT 就是快照读。靠 MVCC 实现，不加锁！在 RR 级别下，MVCC 能有效避免快照读时的幻读。

当前读：读的是最新、最新、最新的数据！UPDATE, DELETE, INSERT, SELECT ... LOCK IN SHARE MODE (共享锁), SELECT ... FOR UPDATE (排他锁) 都是当前读。当前读会加锁！ MVCC 防不住当前读的幻读。为啥？因为当前读每次读的都是最新提交的数据，别人在你两次读之间插了新行并提交了，你第二次当前读就能看见，幻读就发生了。

🔒 共享锁和排他锁？

共享锁 (S锁 / 读锁)： SELECT ... LOCK IN SHARE MODE。多个事务可以同时加共享锁读同一份数据。读读不互斥。

排他锁 (X锁 / 写锁)： SELECT ... FOR UPDATE, UPDATE, DELETE, INSERT。一个事务加了排他锁，其他事务甭想再加任何锁（共享锁、排他锁都不行）。读写、写写都互斥。

📦 大表怎么优化？

先看SQL和索引！ 90% 的慢查询是烂SQL和缺索引/索引失效搞的鬼！EXPLAIN 分析走起！

垂直拆分： 把大宽表按业务拆分成多个小表（一主多子），减少单表宽度。

水平拆分 (分库分表)： 把一张表的数据按某种规则（如用户ID取模、按时间范围）分散到多个库/多个表中。终极杀招，复杂度也高。

读写分离： 主库负责写，多个从库负责读，分摊压力。

冷热分离/归档： 把不常用的历史数据（冷数据）迁移到单独的归档库/表。

升级硬件： 简单粗暴（加内存、换SSD），但治标不治本。

考虑分区表： 把表数据物理上分成多个小文件，逻辑上还是一张表。管理方便点，但效果不如分库分表明显。

📝 bin log、redo log、undo log 的区别？

bin log (归档日志)： Server层搞的，记录所有逻辑修改（SQL语句）。用于主从复制和数据恢复（Point-In-Time-Recovery）。
redo log (重做日志)： InnoDB引擎搞的，记录数据页的物理修改。用于崩溃恢复，保证事务持久性。WAL (Write-Ahead Logging) 关键！先写日志，再写数据页。
undo log (回滚日志)： InnoDB引擎搞的，记录数据修改前的旧值。用于事务回滚和实现MVCC。

📈 bin log 和 redo log 的区别？

特性	bin log	redo log
层级	Server层	InnoDB引擎
内容	逻辑日志（SQL）	物理日志（页修改）
写入时机	提交事务时写入	随时写入
作用	主从复制、恢复	崩溃恢复

🧱 MySQL架构？

MySQL分为：

连接层：处理连接。
SQL层：解析SQL、优化器、执行器。
存储引擎层：实际操作数据，如InnoDB、MyISAM。

📁 分库分表？

水平分表：按行分，适合数据量大的表。
垂直分表：按列分，把冷热数据分开。
分库：把不同的业务数据分到不同的库中。

📁 什么是分区表？类型有哪些？

分区表是将一个大表分成多个小表，逻辑上还是一张表。

类型：

RANGE分区：按范围分。
LIST分区：按列表分。
HASH分区：按哈希分。
KEY分区：类似哈希，但用MySQL自己的算法。

🔍 查询语句执行流程？

连接器验证身份。
查询缓存（如果开了）。
分析器解析SQL语法。
优化器生成执行计划。
执行器调用存储引擎接口。
返回结果。

📝 更新语句执行过程？

执行器调用存储引擎接口。
存储引擎找到数据页（缓存池中）。
修改数据，生成undo log和redo log。
提交事务，写入bin log。
数据落盘（刷脏页）。

🔁 exist 和 in 的区别？

in：先查子表，然后查主表。
exists：先查主表，再根据主表的值去查子表。

🧼 truncate、delete与drop区别？

操作	是否可回滚	是否记录日志	清空自增列	是否释放空间
DELETE	是	是	否	否
TRUNCATE	否	否	是	是
DROP	否	否	—	是

🧩 having 和 where 的区别？

where：过滤行，在分组之前。
having：过滤分组，在分组之后。

🔄 什么是MySQL主从同步？为什么要做？

MySQL主从同步是一种数据复制机制，它能够将一个数据库服务器（主服务器）中的数据自动同步到一个或多个其他数据库服务器（从服务器）上。在这种架构中，主服务器负责处理数据更新操作，而从服务器则接收并应用这些变更，保持与主服务器的数据一致性。

主从同步的核心特点包括：

异步复制机制：从服务器不需要与主服务器保持持续连接，甚至可以间歇性地连接（如通过拨号方式）
灵活的复制配置：可以通过配置文件精确控制需要复制的范围，包括：
- 全库复制
- 单库复制
- 甚至精确到特定表的复制

实施主从同步的主要优势体现在：

读写分离：通过将读操作分散到从服务器，显著提升系统的整体并发处理能力
负载均衡：主服务器专注实时数据处理，从服务器承担数据分析任务，优化资源分配
数据安全：为主数据库提供实时备份，有效保障数据可靠性
高可用性：当主服务器出现故障时，可以快速切换到从服务器继续提供服务

乐观锁和悲观锁是什么？

数据库并发控制是确保多事务同时操作同一数据时保持数据一致性的关键技术。乐观锁和悲观锁是两种主要的并发控制策略：

悲观锁

核心思想：假定并发冲突必然发生，采取"先锁定后修改"的保守策略实现方式：

在数据查询阶段就直接加锁
保持锁定状态直至事务提交
使用数据库内置锁机制（如SELECT...FOR UPDATE）

典型应用场景：

写操作频繁的环境
对数据一致性要求极高的场景
短事务处理

乐观锁

核心思想：假设并发冲突概率较低，采用"先修改后验证"的乐观策略实现方式：

版本号机制：添加version字段，更新时校验版本
CAS算法（Compare And Swap）
更新时检查数据是否被修改过

典型应用场景：

读多写少的环境
对性能要求较高的场景
长事务处理

🧾 用过processlist吗？

SHOW PROCESSLIST 可以查看当前所有连接和执行状态，用来排查慢查询、死锁等问题。

基本用法

SHOW PROCESSLIST;       -- 查看基础进程信息SHOW FULL PROCESSLIST;  -- 查看完整SQL语句

关键字段解析

字段	说明
id	线程唯一标识，可用KILL id终止线程
db	当前操作的数据库
user	执行操作的数据库用户
host	客户端连接来源地址
command	当前执行命令类型
time	操作持续时间(秒)
state	线程状态
info	正在执行的SQL语句