mysql面试题集

最新推荐文章于 2026-06-23 22:50:02 发布

原创最新推荐文章于 2026-06-23 22:50:02 发布 · 596 阅读

17 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#mysql #数据库

mysql 专栏收录该内容

1 篇文章

订阅专栏

存储引擎

1.MyISAM

特点：

表级锁：只支持表级锁。当执行写操作（INSERT, UPDATE, DELETE）时，会锁定整个表，导致并发性能低下。
不支持事务：发生意外时无法回滚。
全文索引：在 MySQL 5.6 之前，它是唯一支持全文索引（FULLTEXT） 的引擎。
高速读取：对于只读或读多写少的场景，由于其简单的结构，读取速度非常快。
计数：自带一个计数器，COUNT(*) 这种操作非常快，因为它直接读取存储的值。

适用场景：

读密集型的应用（如数据仓库、报表生成）。
不需要事务支持的轻量级应用。
大量 SELECT 操作，很少 INSERT/UPDATE 的操作。

不适用场景：

需要事务、行级锁或外键的应用。
写操作（INSERT/UPDATE/DELETE）频繁的应用，因为表锁会成为严重瓶颈。

重要提示： MyISAM 在 MySQL 8.0 中已被弃用，并在未来的版本中会被移除。强烈建议新项目不要使用它。

2.InnoDB

特点：

事务支持：完全支持 ACID（原子性、一致性、隔离性、持久性）事务。这是它成为默认引擎的关键原因。
行级锁：支持行级锁定，这大大提高了在高并发情况下的多用户性能。写操作只会锁定受影响的行，而不是整个表。
外键约束：支持外键（FOREIGN KEY），保证了数据的参照完整性。
崩溃恢复：具有强大的崩溃恢复能力，能够在服务器宕机后自动恢复数据。
MVCC：支持多版本并发控制，通过保存数据的快照来提高并发性能。

适用场景：

绝大多数场景：除非有特殊需求，否则 InnoDB 都是最佳选择。
需要事务的应用（如银行交易、订单系统）。
高并发读写、需要行级锁定的应用。
需要外键来保证数据完整性的应用。

不适用场景：

如果不需要事务，且主要是只读或大量插入操作，它的某些开销可能会显得稍高。

引擎常用sql

-- 查看支持的引擎：
SHOW ENGINES;

-- 查看某张表的引擎：
SHOW TABLE STATUS LIKE 'table_name';

-- 创建表时指定引擎：
CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE=InnoDB;

-- 修改现有表的引擎：
ALTER TABLE my_table ENGINE = InnoDB;

索引

索引的数据结构

1. B+Tree 索引 (默认)

它是 InnoDB 和 MyISAM 引擎的默认索引类型。
结构：一种平衡多路搜索树。所有数据都存储在叶子节点，并且叶子节点之间通过指针相连，形成了一个有序链表。
为什么是 B+Tree？
- 矮胖的树：层级很少，通常只需要 3-4 次 IO 就能从上亿条数据中找到目标，效率极高。
- 适合磁盘预读：磁盘按页（通常是 4KB）读写，B+Tree 的一个节点大小设计为一页，每次 IO 能加载大量键值。
- 范围查询高效：因为叶子节点是链表连接，一旦找到范围的起点，就可以顺序遍历到终点，而不需要从根节点重新查找。

2. Hash 索引

Memory 引擎默认使用 Hash 索引。
原理：对索引键计算一个哈希码，哈希码对应着数据行的指针。
优点：等值查询速度极快（O(1)时间复杂度）。
致命缺点：
- 不支持范围查询（WHERE a > 10）。
- 不支持排序（ORDER BY）。
- 不支持部分索引键查询（最左前缀匹配）。
- 哈希冲突可能影响性能。
InnoDB 提供一种 自适应哈希索引（Adaptive Hash Index），它是数据库内部自动创建的，用户无法控制，用于优化某些频繁的等值查询。

索引的类型

1. 普通索引 (INDEX)

CREATE INDEX idx_name ON table_name (column_name);

2. 唯一索引 (UNIQUE INDEX)

-- 与普通索引类似，但要求索引列的值必须唯一，允许有空值。
CREATE UNIQUE INDEX uni_idx_email ON users (email);

3. 主键索引 (PRIMARY KEY)

-- 一种特殊的唯一索引，不允许有空值。一张表只能有一个主键索引。InnoDB 的表如果没有显式定义主键，也会自动创建一个隐藏的主键。
-- 通常在 CREATE TABLE 时指定
CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (id) -- 主键索引
);

4. 组合索引（复合索引）(Composite Index)

-- 最重要的索引类型之一。在多个列上建立的索引。
CREATE INDEX idx_name_age ON users (last_name, first_name, age);

最左前缀原则（Leftmost Prefix Principle）：
组合索引的查询条件必须从最左边的列开始，且不能跳过中间的列，才能有效使用索引。

WHERE last_name = 'Wang' （使用索引）
WHERE last_name = 'Wang' AND first_name = 'Leo' （使用索引）
WHERE last_name = 'Wang' AND age = 30 （部分使用索引，只用了 last_name，跳过了 first_name）
WHERE first_name = 'Leo' （未使用索引，不满足最左前缀）

5. 全文索引 (FULLTEXT INDEX)

-- 用于全文搜索，适用于 MATCH AGAINST 操作，而不是简单的 WHERE。
CREATE FULLTEXT INDEX ft_idx_content ON articles (content);
SELECT * FROM articles WHERE MATCH(content) AGAINST('database');

事务

事务的四大特性 (ACID)

原子性：事务是一个不可分割的最小工作单元，事务中的所有操作要么全部提交成功，要么全部失败回滚。

一致性：事务执行前后，数据库必须从一个一致性状态转换到另一个一致性状态。一致性确保了数据的业务规则和完整性约束不被破坏。

隔离性：多个并发事务执行时，一个事务的操作不应影响其他事务，彼此相互隔离。数据库系统提供了不同的隔离级别来权衡隔离性和性能。

持久性：一旦事务提交，它对数据库的修改就是永久性的，即使后续系统发生故障（如宕机），数据也不会丢失。

事务的隔离级别 (Isolation Levels)

读未提交：性能最高，但允许读取未提交的数据，什么都不能避免。

读已提交 ：只能读取已提交的数据。解决了脏读，但可能发生不可重复读和幻读。Oracle/PostgreSQL 默认级别。

可重复读 ：MySQL InnoDB 默认级别。确保了在同一事务中多次读取同一数据的结果是一致的。解决了脏读和不可重复读。InnoDB 通过 MVCC 机制在一定程度上避免了幻读。

串行化：性能最低。强制事务串行执行，避免了所有问题。它通过给读取的行加锁来实现，并发性很差。

-- 查看当前会话隔离级别
SELECT @@transaction_isolation;

-- 查看全局隔离级别
SELECT @@global.transaction_isolation;

-- 设置当前会话隔离级别为 READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

并发事务可能带来的问题

脏读（Dirty Read）：一个事务读到了另一个未提交事务修改的数据。如果那个事务回滚了，读到的数据就是无效的（“脏”数据）。
不可重复读（Nonrepeatable Read）：在同一个事务中，多次读取同一数据，得到的结果不同。这是因为在两次读取之间，数据被另一个已提交的事务修改了。
幻读（Phantom Read）：类似不可重复读，但针对的是数据行的数量。一个事务在多次查询时，发现其他已提交的事务插入或删除了新的数据行，导致结果集的行数发生了变化。

事务操作过程

开启事务：START TRANSACTION; 或 BEGIN;
- 这会关闭当前会话的自动提交，后续的 SQL 语句都会在一个事务中。
执行SQL操作：执行一系列你希望组成一个事务的 SQL 语句（INSERT, UPDATE, DELETE 等）。
提交事务：COMMIT;
- 如果所有操作都成功，使用 COMMIT 来确认所有更改，使其永久生效。
回滚事务：ROLLBACK;
- 如果在过程中遇到任何错误，或者你想主动撤销所有操作，使用 ROLLBACK 可以撤销该事务内所有的更改，回到事务开始前的状态。

MySQL 如何实现事务？（InnoDB 引擎）

原子性 (A) 和 持久性 (D)：主要通过 事务日志（Redo Log 和 Undo Log） 实现。
- Undo Log：用于事务回滚（ROLLBACK）。它记录了事务发生前的数据状态。如果事务需要回滚，就可以根据 Undo Log 将数据恢复到之前的样子。
- Redo Log：用于保证持久性。它记录了事务对数据页的物理修改。事务提交时，会先写 Redo Log。即使系统宕机，重启后也能根据 Redo Log 重新执行已提交的事务，防止数据丢失。
隔离性 (I)：主要通过 锁机制（Locking） 和 多版本并发控制（MVCC） 实现。
- 锁：包括行级锁、间隙锁等，用于控制并发事务对同一数据的访问。
- MVCC：InnoDB 通过给每行数据增加隐藏的创建版本号和删除版本号来实现。查询时，通过版本号来获取事务开始时的数据快照，从而无需加锁就能实现非阻塞的读操作，极大提高了并发性能。这也是 可重复读（REPEATABLE READ） 级别实现的核心机制。

锁

锁的类型（粒度）

1. 全局锁

锁定范围：整个数据库。
操作：FLUSH TABLES WITH READ LOCK; (FTWRL)
效果：使数据库处于只读状态，所有数据变更操作（DML）和表结构变更操作（DDL）都会被阻塞。
使用场景：全库逻辑备份。这是一种非常粗粒度的锁，会对业务造成很大影响，生产环境慎用。现在更推荐使用 mysqldump --single-transaction（对于 InnoDB）利用 MVCC 进行一致性备份。

2. 表级锁

锁定范围：整张表。
类型：
- 表锁：LOCK TABLES table_name READ/WRITE;。手动加锁，MyISAM 引擎默认使用表锁。
- 元数据锁 (MDL)：MySQL 5.5+ 引入，由系统自动加锁。当对一个表做增删改查（DML）时，加 MDL 读锁；当要修改表结构（DDL）时，加 MDL 写锁。
  - 读锁之间不互斥：多个线程可以同时对一张表进行 CRUD。
  - 读写锁、写写锁之间互斥：DDL 操作会等待所有当前的 DML 操作完成，并且会阻塞后续所有的 DML 操作。如果有一个长事务未提交，后续的 DDL 操作会一直被阻塞，可能导致连接池被撑满。
特点：实现简单，资源消耗少，但并发度最低。

3. 行级锁 (InnoDB 的特色)

锁定范围：数据行（或索引记录）。
特点：粒度最细，并发度最高，但加锁开销大（需要扫描索引找到行才加锁）。
InnoDB 的行锁是基于索引实现的！如果查询条件没有用到索引，InnoDB 会退化为表锁。

InnoDB 行锁主要通过以下三种算法实现：
记录锁 ：锁住索引记录本身。 | 防止其他事务修改或删除这条特定记录。
间隙锁： 锁住索引记录之间的间隙（区间），但不锁记录本身。例如，锁住 (10, 20) 这个开区间。 | 防止其他事务在间隙中插入新记录，从而解决幻读问题。
临键锁：记录锁 + 间隙锁 的组合。锁住一条记录及其前面的间隙。例如，锁住 (10, 20] 这个左开右闭区间。 | InnoDB 默认的行锁算法。它既锁住了记录本身，也锁住了它之前的间隙，同时解决了“当前读”下的幻读问题。

示例：假设表 t 的 id 列有值 5, 10, 15, 20

SELECT * FROM t WHERE id = 10 FOR UPDATE;
- 会对 id=10 这条记录加上 记录锁。
SELECT * FROM t WHERE id BETWEEN 10 AND 15 FOR UPDATE;
- 为了防止幻读，不仅会锁住 id=10 和 id=15 的记录，还会锁住它们之间的间隙（(10, 15)），即 间隙锁。这样，其他事务就无法在 (10, 15) 之间插入 id=11,12,13,14 等新数据。
- 实际上，InnoDB 会使用 临键锁，锁定的范围可能是 (5, 10], (10, 15], (15, 20]。

锁的模式

1. 共享锁 (S锁 | 读锁)

操作：SELECT ... LOCK IN SHARE MODE;
特性：允许多个事务同时获取同一资源的 S 锁（共享读），但不允许任何事务获取该资源的 X 锁。
类比：就像很多人可以同时读同一本书，但只要还有人在读，就不能有人去修改书的内容。

2. 排他锁 (X锁 | 写锁)

操作：SELECT ... FOR UPDATE; 或 UPDATE/DELETE/INSERT 语句会自动加 X 锁。
特性：最排他。一个事务获取了某资源的 X 锁后，其他事务不能再获取该资源的任何锁（S 锁或 X 锁）。
类比：一个人正在修改书的内容（加了写锁），那么其他人既不能读这本书（不能加读锁），也不能修改它（不能加写锁）。

乐观锁与悲观锁

悲观锁 ：“悲观” 地认为并发冲突一定会发生，因此在操作数据之前先上锁。使用数据库固有的锁机制（如 SELECT ... FOR UPDATE）。写多读少，并发冲突严重的场景。
乐观锁 ：“乐观” 地认为并发冲突不常发生，只在提交更新时检查数据是否被改动。不真正加锁，通常通过版本号（version） 或时间戳字段实现。读多写少，并发冲突少的场景。性能更高。

三范式

第一范式 (1NF)：保证原子性

定义：数据库表的每一列都是不可再分的原子值。表中的每个字段都是单一的、不能再拆分的。每一列的数据类型相同（例如，都是整数或字符串）。每列都有唯一的列名。

第二范式 (2NF)：消除部分依赖

定义：要求数据库表中的所有非主键字段都必须完全依赖于整个主键，而不能只依赖于主键的一部分。

第三范式 (3NF)：消除传递依赖

定义：要求表中的所有非主键字段之间不能有传递依赖，即它们必须直接依赖于主键，而不是依赖于其他非主键字段。

慢查询排查

开启与配置慢查询日志

慢查询日志 (Slow Query Log) 是排查的根本，它记录了执行时间超过指定阈值的 SQL 语句。

-- 检查慢查询日志状态：
SHOW VARIABLES LIKE 'slow_query_log%';
-- 临时开启（重启失效）：
SET GLOBAL slow_query_log = 'ON';
-- 永久开启：修改 MySQL 配置文件 my.cnf (或 my.ini)：
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log  # Linux 常用路径
# slow_query_log_file = /usr/local/mysql/data/mysql-slow.log # macOS 常用路径
long_query_time = 2                 # 定义“慢”的阈值，单位：秒。通常设为1s或2s。
log_queries_not_using_indexes = 1   # 记录未使用索引的查询（谨慎开启，可能日志量巨大）
log_output = FILE                   # 输出到文件（也可以是TABLE）

使用工具分析慢日志文件

直接阅读慢日志文件是低效的。MySQL 官方提供了 mysqldumpslow 工具，但更推荐使用功能更强大的 pt-query-digest (Percona Toolkit 的一部分)。

# 使用 mysqldumpslow
# 查看帮助
mysqldumpslow --help

# 得到返回记录集最多的10条SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log

# 得到访问次数最多的10条SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/mysql-slow.log

# 得到按照时间排序，并且包含'LEFT JOIN'的前10条SQL
mysqldumpslow -s t -t 10 -g "LEFT JOIN" /var/lib/mysql/mysql-slow.log

使用 `pt-query-digest` (强烈推荐)

# 直接分析慢日志文件
pt-query-digest /var/lib/mysql/mysql-slow.log

# 将分析报告输出到文件
pt-query-digest /var/lib/mysql/mysql-slow.log > slow_report.log

# 分析最近12小时的慢查询（如果日志量很大）
pt-query-digest --since=12h /var/lib/mysql/mysql-slow.log > recent_slow_report.log

pt-query-digest 报告解读：
报告会生成一个概要，然后按 总耗时占比 降序列出所有慢查询。

Rank：排名。

Query ID：查询的指纹（ID）。

Response time：总响应时间及其占比。

Calls：执行次数。

R/Call：每次执行的平均时间。

Item：抽象的SQL语句（去掉了具体参数）。

深入分析单条慢查询

从 pt-query-digest 的报告中找到抽象的 SQL，然后去原始慢日志里找到带有具体参数的完整 SQL。使用 EXPLAIN 分析执行计划

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'completed';

重点关注 EXPLAIN 输出结果的以下几列：

type：访问类型。这是最重要的指标。从好到坏：

const、eq_ref：最佳

ref、range：很好，使用了索引

index：全索引扫描（需要优化）

ALL：全表扫描（必须优化！）

key：实际使用的索引。如果为 NULL，说明没用到索引。

rows：MySQL 预估需要扫描的行数。值越小越好。

Extra：额外信息，非常重要：

Using filesort：MySQL 需要额外的一次排序操作，通常需要优化。

Using temporary：使用了临时表，常见于排序和分组，需要优化。

Using index：覆盖索引，性能极佳，表示查询只通过索引就完成了。

Using where：在存储引擎检索行后进行了过滤。

使用 EXPLAIN ANALYZE (MySQL 8.0+)：
这是更强大的工具，它会实际执行查询并显示详细的执行成本和时间。
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 100 AND status = 'completed';