MySQL面试题

此笔记为本人备考面试时整理，内容多源于网络搜集，仅作后续查阅之用，无盈利意图。若有侵权，请联系删除。

参考资料：

https://javaguide.cn/

https://www.xiaolincoding.com/

https://pdai.tech

https://javabetter.cn/

一、MySQL基础

1、数据库三大范式

  • 1NF 要求字段值必须是不可再分的原子值。

    反例：用户信息表中地址字段存储 “北京市海淀区”，未拆分为省、市、区，违反 1NF；
    正例：拆分为province、city、district，每个字段独立存储。

  • 2NF 1NF的基础上，要求非主属性完全依赖主键，避免部分依赖（如订单明细拆分）。

    场景：订单明细表（（订单号+商品ID为主键）+ 金额 ），若字段订单金额仅依赖订单号，则存在部分依赖（非主属性订单金额不依赖商品ID），违反 2NF；
    优化：拆分为订单表（订单号、金额）和订单明细表（订单号、商品 ID、数量）。

  • 3NF 2NF的基础上，非主键字段之间不能有依赖关系，消除传递依赖，如学生表与班级表分离。

    反例：学生表（学生 ID，姓名，班级 ID，班级地址）中，班级地址依赖班级ID，形成 “学生 ID→班级 ID→班级地址” 的传递依赖，违反 3NF；
    优化：拆分为学生表（学生 ID，姓名，班级 ID）和班级表（班级 ID，班级地址）。

2、char 和 varchar 的区别是什么？

特性 CHAR VARCHAR
存储方式 固定长度 可变长度
空间占用 始终占用定义长度（可能浪费） 仅占用实际数据长度 + 长度标识
存储开销 无额外开销 额外1~2字节存储长度
查询速度 更快（固定长度直接定位） 稍慢（需计算位置）
适用场景 长度固定的短字符串（如MD5） 长度变化大的字符串（如地址）
空格处理 存入时补足空格，查询时去除 原样存储和返回
  • CHAR 是定长分配，适合存储固定长度短字符串（如验证码），查询更快但可能浪费空间；
  • VARCHAR 是变长存储，适合长度不确定的数据（如用户名），空间利用率高但需额外长度标识。

3、varchar (100)和 varchar (10)的区别是什么？

1. 存储机制

  • 两者相同点：
    • 都是可变长度字符串类型
    • 实际存储空间 = 字符数 + 长度标识字节（1-2字节）
    • 存储”Hello”时都占用 5 字节 + 1 字节开销 = 6 字节
      存储相同的字符串，所占用磁盘的存储空间其实是一样的
  • 差异：
    • varchar (100) 最多存储100字符
    • varchar (10) 最多存储10字符

2. 性能影响

  • 内存分配：
    • 排序操作时，数据库可能按最大长度分配内存
    • VARCHAR(100) 的列可能比 VARCHAR(10) 多消耗 10 倍内存
  • 索引效率：
    • 索引大小：VARCHAR(100) 索引大约是 VARCHAR(10) 索引的 10 倍
  1. 适用场景：
    • varchar(10)：短文本（如验证码、商品编码前几位）；
    • varchar(100)：较长文本（如用户名、文章标题、地址片段）。

4、in和exists的区别？

IN 运算符

先执行子查询，将结果缓存到临时表，然后检查主查询的值是否在这个临时表中。

exists 运算符

对于主查询的每一行，都会执行一次子查询检查是否存在匹配记录

区别

特性 IN EXISTS
工作原理 检查值是否在结果集中 检查子查询是否返回任何行
执行顺序 先执行子查询，再执行主查询 主查询的每一行都执行子查询
性能特点 子查询结果集小时高效 主查询结果集小时高效
NULL 处理 NULL IN (结果集) 总是返回 UNKNOWN 不受子查询中 NULL 值影响
适用场景 静态值列表或小型结果集 关联子查询或大型结果集
可读性 更直观，易于理解 对初学者稍复杂

5、怎么存储 emoji?

因为 emoji（😊）是 4 个字节的 UTF-8 字符，而 MySQL 的 utf8 字符集只支持最多 3 个字节的 UTF-8 字符，所以在 MySQL 中存储 emoji 时，需要使用 utf8mb4 字符集。

1
ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

MySQL 8.0 已经默认支持 utf8mb4 字符集，可以通过 SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; 查看。

6、drop、delete 与 truncate 的区别？

DELETE 支持行级删除，可以带 WHERE 条件，可以回滚。

DROP 是物理删除，用来删除整张表，包括表结构，且不能回滚。

TRUNCATE 用于清空表中的所有数据，但会保留表结构，不能回滚。

7、UNION 与 UNION ALL 的区别？

UNION 会自动去除合并后结果集中的重复行。UNION ALL 不会去重，会将所有结果集合并起来。

8、count(1)、count(*) 与 count(列名) 的区别？

  1. count(*)：
  • count(*)会统计结果集中所有行的数量，包括所有列，不忽略任何行，即使某些列包含NULL值。
  • 它是一个标准的SQL函数，用于统计表中的总行数。
  1. count(1)：
  • count(1)中的1是一个常量值，表示对每一行进行计数。
  • 由于常量值永远不为NULL，因此count(1)实际上与count(*)一样计算所有行。
  • 在MySQL中，优化器通常会将count(1)优化为与count(*)相同的执行计划。
  1. count(列名)：
  • count(列名)会统计结果集中指定列非NULL值的数量。
  • 如果列中的所有值都是非空的，那么count(列名)的结果与count(*)的结果相同。
  • 但是，如果列中有空值（NULL），那么count(列名)的结果会小于count(*)的结果。

9、NULL 和 ‘’ 的区别是什么？

1. 本质含义

  • NULL：
    表示 “值不存在”或“未知”，相当于 “无值”。它不是空字符串，也不是数字 0，而是一个特殊的标记。

    如果列允许 NULL 且未指定默认值，则插入时默认为 NULL。

  • ''（空字符串）：
    表示 “空值”，是一个长度为 0 的有效字符串，明确表示 “这里有一个空字符串”。

    需要显式设置默认值为 ''（例如：ALTER TABLE tbl MODIFY col VARCHAR(10) DEFAULT '';）。

2. 存储空间

  • NULL：
    通常需要额外 1 位（bit）来标记字段是否为 NULL（对于允许 NULL 的列）。

  • ''：

    取决于存储引擎和字符集。例如：

    • 在 UTF-8 中，'' 占用 0 字节；
    • 在 varchar 中，可能需要 1~2 字节存储长度信息（即使长度为 0）。

3. 查询与比较

  • **NULL**：

    • 不能用 = 直接比较，必须用 IS NULL 或 IS NOT NULL。

      1
      2
      WHERE column IS NULL;  -- 正确
      WHERE column = NULL;   -- 错误！永远返回 false

    • 在聚合函数（如 COUNT()、SUM()）中会被忽略（除非用 COUNT(*)）。

  • **''**：

    • 可以用 = 直接比较。

      1
      WHERE column = '';  -- 正确

    • 在聚合函数中会被视为有效值。

关键判断标准：如果某字段必须有值（即使是空），用 ''；如果可能无需赋值，用 NULL。

10、mysql 的深度分页如何优化

深度分页是指当查询结果集很大时（如 LIMIT 1000000, 10），MySQL 需要扫描大量数据才能返回少量结果的性能问题场景。

方案1：游标分页

  • 原理：记录上一页最后一条数据的 标识（如 ID），用 WHERE 代替 OFFSET。
1
2
3
4
5
-- 第1页：初始查询
SELECT * FROM t ORDER BY id LIMIT 10; -- 记最后一条 ID 为 last_id  
-- 第2页：基于游标
SELECT * FROM t WHERE id < last_id ORDER BY id DESC LIMIT 10;

  • 优点：

    • 时间复杂度 O(1)
    • 无性能衰减
    • 支持高并发

  • 限制：

    • 只能顺序翻页，不能跳页，适合 APP 滚动加载。
    • 需有序且唯一的列（如自增ID、时间戳）

方案2：覆盖索引 + 延迟关联

  • 原理：利用索引先获取主键（减少扫描量），再回表取全字段。

    1
    2
    3
    4
    5
    6
    7
    SELECT * FROM orders
    INNER JOIN (
        SELECT id FROM orders
        ORDER BY create_time DESC
        LIMIT 1000000, 10  -- 索引覆盖扫描
    ) AS tmp USING(id);
    -- USING(id) 等价于 ON table1.id = table2.id

  • 优化效果：

    • 索引扫描代替全表扫描
    • 减少回表数据量
    • 性能提升 5-10 倍

方案3：范围分页

  • 原理：通过 WHERE 条件缩小扫描范围

    1
    2
    3
    SELECT * FROM t 
    WHERE create_time BETWEEN '2025-01-01' AND '2025-01-31' 
    ORDER BY id LIMIT 10;

  • 适用场景：

    • 时间范围明确的数据
    • 分区表的分区键查询

二、数据库架构

1、说说 MySQL 的基础架构？

MySQL 采用分层架构，主要包括连接层、服务层、和存储引擎层。

下图是 MySQL 的一个简要架构图，从下图你可以很清晰的看到客户端的一条 SQL 语句在 MySQL 内部是如何执行的。

image-20250625173507865

①、连接层主要负责客户端连接的管理，包括验证用户身份、权限校验、连接管理等。可以通过数据库连接池来提升连接的处理效率。

②、服务层是 MySQL 的核心，主要负责查询解析、优化、执行等操作。在这一层，SQL 语句会经过解析、优化器优化，然后转发到存储引擎执行，并返回结果。这一层包含查询解析器、优化器、执行计划生成器、日志模块等。

③、存储引擎层负责数据的实际存储和提取。MySQL 支持多种存储引擎，如 InnoDB、MyISAM、Memory 等。

binlog写入在哪一层？

binlog 在服务层，负责记录 SQL 语句的变化。它记录了所有对数据库进行更改的操作，用于数据恢复、主从复制等。

2、一条 SQL 查询语句在 MySQL 中的完整执行过程

案例 SQL

1
SELECT name, age FROM employees WHERE department = 'Sales' AND salary > 5000 ORDER BY hire_date DESC LIMIT 10;

执行过程详解

1. 连接阶段 (Connector)
  • 作用：建立客户端与服务器的连接
  • 案例过程：
    • 应用程序（如Java程序）通过JDBC驱动连接到MySQL
    • 验证用户名/密码（如：jdbc:mysql://localhost:3306/company）
    • 检查权限（确认用户有employees表的SELECT权限）
    • 建立连接线程（show processlist可查看）
2. 查询缓存 (Query Cache) [MySQL 8.0已移除]

MySQL 5.7中仍存在，但默认禁用

  • 作用：缓存SELECT查询结果
  • 案例过程：
    • 生成查询缓存Key：department='Sales', salary>5000, ...
    • 检查缓存中是否有完全匹配的结果
    • 若存在直接返回结果（本案例大概率不会命中，因包含动态条件）
3. 解析器 (Parser)
  • 作用：语法解析，生成解析树
  • 案例过程：
    • 词法分析：拆分关键词
      1
      2
      3
      4
      5
      6
      SELECT → 查询命令
      name, age → 目标列
      FROM employees → 数据源
      WHERE → 条件开始
      department = 'Sales' → 条件表达式
      ...
    • 语法分析：构建语法树 
      graph TD
A[SELECT] --> B[ColumnList]
A --> C[FROM]
A --> D[WHERE]
A --> E[ORDER BY]
A --> F[LIMIT]
B --> G[name]
B --> H[age]
C --> I[employees]
D --> J[AND]
J --> K[department='Sales']
J --> L[salary>5000]
E --> M[hire_date DESC]
F --> N[10]
4. 预处理器 (Preprocessor)
  • 作用：语义检查，表/列解析
  • 案例过程：
    • 检查employees表是否存在
    • 验证name, age, department, salary, hire_date列是否存在
    • 解析*符号（本案例未使用）
    • 检查权限（再次确认SELECT权限）
5. 优化器 (Optimizer)

  • 作用：生成最优执行计划

  • 案例过程：

    • 统计信息分析：

      • 表行数（show table status like 'employees'）
      • 索引分布（department索引？salary索引？）

    • 成本估算：

      • 全表扫描 vs 使用索引
      • 假设存在idx_dept_salary(department, salary)索引

    • 执行计划生成：

      1
      2
      3
      4
      5
      6
      7
      8
      9
      EXPLAIN SELECT ...
      -- 结果示例：
      id: 1
      select_type: SIMPLE
      table: employees
      type: ref
      key: idx_dept_salary
      rows: 100
      Extra: Using where; Using filesort

    • 关键决策：

      1. 使用idx_dept_salary索引快速定位’Sales’部门
      2. 在索引结果中过滤salary > 5000
      3. 对结果集进行文件排序（filesort）
      4. 应用LIMIT 10
6. 执行器 (Executor)
  • 作用：调用存储引擎执行计划
  • 案例过程：
    1. 打开employees表
    2. 调用存储引擎接口：
      1
      2
      3
      4
      5
      6
      7
      8
      // 伪代码
      index_read_first(idx_dept_salary, 'Sales')
      while (record = index_read_next()) {
          if (record.salary > 5000) {
              result_set.add(record.name, record.age);
              if (result_set.size() >= 10) break;
          }
      }
    3. 处理排序：
      • 收集所有符合条件的行
      • 在内存或磁盘进行快速排序（ORDER BY hire_date DESC）
    4. 应用LIMIT：取前10条结果
7. 存储引擎 (Storage Engine)
  • 作用：实际数据存取（以InnoDB为例）
  • 案例过程：
    • 索引扫描：
      • 使用B+树索引idx_dept_salary定位到第一个’Sales’部门记录
      • 沿叶子节点链表扫描部门为’Sales’的记录
    • 回表查询：
      • 通过主键获取完整行数据（因SELECT包含非索引列name, age, hire_date）
    • 过滤处理：
      • 对每条记录检查salary > 5000条件
    • 数据返回：
      • 将符合条件的name, age, hire_date返回给执行器
    • 事务支持：
      • 保证在READ COMMITTED隔离级别下看到一致的数据视图
8. 结果返回

  • 作用：将最终结果发送给客户端

  • 案例过程：

    1. 排序后的结果集放入网络缓冲区
    2. 通过TCP连接逐步发送给客户端
    3. 客户端（如MySQL命令行）显示结果：
      1
      2
      3
      4
      5
      6
      +----------+-----+
      | name     | age |
      +----------+-----+
      | John Doe | 32  |
      | Jane Smith| 28 |
      ...（共10行）
    4. 清理临时资源（排序内存、游标等）

关键流程总结

sequenceDiagram
    participant Client
    participant Connector
    participant Parser
    participant Optimizer
    participant Executor
    participant InnoDB
    
    Client->>Connector: 发送SQL查询
    Connector->>Parser: 权限验证后传递
    Parser->>Optimizer: 生成解析树
    Optimizer->>Executor: 最优执行计划
    Executor->>InnoDB: 调用索引扫描接口
    InnoDB->>Executor: 返回数据页
    Executor->>InnoDB: 请求回表查询
    InnoDB->>Executor: 返回完整行数据
    Executor->>Executor: 过滤/排序/LIMIT
    Executor->>Client: 返回最终结果集

性能优化关键点

  1. 索引设计：创建复合索引(department, salary)可加速WHERE过滤
  2. 覆盖索引：若索引包含所有SELECT列（如(department, salary, name, age, hire_date)），可避免回表
  3. 排序优化：添加hire_date索引可消除filesort
  4. LIMIT下推：在存储引擎层尽早应用行数限制
  5. 批量读取：顺序I/O读取多个数据页减少磁盘寻道

💡 实际建议：通过EXPLAIN分析执行计划，关注type列（扫描方式）、Extra列（排序/临时表）、rows列（扫描行数）等关键指标进行优化。

三、存储引擎

1、讲一讲mysql的引擎吧，你有什么了解？

  • InnoDB：
    • InnoDB是MySQL的默认存储引擎。
    • 具有ACID事务支持、行级锁、外键约束等特性。
    • 它适用于高并发的读写操作，支持较好的数据完整性和并发控制。
  • MyISAM：
    • MyISAM是MySQL的另一种常见的存储引擎。
    • 具有较低的存储空间和内存消耗，适用于大量读操作的场景。
    • MyISAM不支持事务、行级锁和外键约束，因此在并发写入和数据完整性方面有一定的限制。
  • Memory：
    • Memory引擎将数据存储在内存中，适用于对性能要求较高的读操作，但是在服务器重启或崩溃时数据会丢失。
    • 不支持事务、行级锁和外键约束。

四、索引

1、索引是什么？有什么好处？

  • 索引类似于书籍的目录，可以减少扫描的数据量，提高查询效率。

  • 如果查询的时候，没有用到索引就会全表扫描，这时候查询的时间复杂度是On

  • 如果用到了索引，那么查询的时候，可以基于二分查找算法，通过索引快速定位到目标数据， mysql 索引的数据结构一般是 b+树，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。

2、索引都分类

  • 从功能上分
    • 主键索引
    • 唯一索引
    • 普通索引
    • 联合索引
    • 全文索引
    • 空间索引
  • 从存储结构上分
    • 聚簇索引
    • 二级索引（非聚簇索引）

3、索引失效的几种情况

  • 查询条件使用函数或者计算
  • 不符合最左前缀原则
  • 隐式类型转换（类型不匹配）
  • like通配符以“%” 开头
  • 使用 or 时，两边有没有索引的字段
  • 使用is null 或 is not null
  • 使用not in
  • 使用范围(> ,<)查询
  • 使用 != 或者 <>