MySQL 常见面试题及详解

左Python右Java
116 阅读9分钟

MySQL 常见面试题及详解

一、基础概念

1. 什么是 MySQL？常见的存储引擎有哪些？它们有什么区别？

答案： MySQL 是一种开源的关系型数据库管理系统（RDBMS），使用结构化查询语言（SQL）进行数据库管理。

常见存储引擎：

  • InnoDB：MySQL 默认存储引擎，支持事务、行级锁、外键约束。适合高并发读写场景。
  • MyISAM：MySQL 5.1 版本前的默认存储引擎，不支持事务和外键，默认表级锁。适合只读场景，查询性能较高。
  • Memory：数据存储在内存中，速度快，断电即失。适合临时表和缓存。
  • Archive：用于存储大量归档数据，只支持 INSERT 和 SELECT 操作。
特性InnoDBMyISAM
事务支持✓✗
外键支持✓✗
锁粒度行级锁、表级锁表级锁
数据恢复支持（事务日志）不支持
表结构.ibd 和 .frm数据 .MYD，索引 .MYI
索引类型聚簇索引非聚簇索引

2. 什么是事务？MySQL 的事务特性是什么？

答案： 事务是保证一组操作要么全部执行成功，要么全部回滚的机制。

MySQL 的事务特性（ACID）：

  • 原子性（Atomicity）：事务是一个不可分割的工作单位，要么全部执行，要么全部不执行。
  • 一致性（Consistency）：事务执行前后，数据库从一个一致性状态转移到另一个一致性状态。
  • 隔离性（Isolation）：不同事务之间相互隔离，互不影响。
  • 持久性（Durability）：事务提交后，数据的修改是永久的，即使系统崩溃也不会丢失。

3. MySQL 的数据类型有哪些？选择建议？

答案：

  • 数值型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL
  • 字符串型：CHAR、VARCHAR、TEXT、BLOB
  • 日期型：DATE、DATETIME、TIMESTAMP

选择建议：

  • 使用定长字符串选择 CHAR，变长字符串选择 VARCHAR
  • 金融数据用 DECIMAL 代替 FLOAT，避免精度问题
  • 表设计时尽量使用合适长度的类型，避免浪费存储空间
  • INT(10) 中的 10 只是显示宽度，不影响存储范围

二、索引与优化

4. 什么是索引？索引的优缺点是什么？

答案： 索引是帮助 MySQL 高效获取数据的数据结构，类似于书的目录。

优点：

  • 大大提高数据查询速度
  • 加速表与表之间的连接
  • 使用索引可以在查询时避免排序和分组操作

缺点：

  • 创建和维护索引需要耗费时间
  • 索引需要占用物理空间
  • 对表进行增删改操作时，索引也需要动态维护，降低了写操作的性能

5. MySQL 中索引的类型有哪些？

答案：

  • 主键索引：唯一标识一行记录，主键字段自动创建
  • 唯一索引：列值唯一，但允许有空值
  • 普通索引：没有唯一性限制
  • 全文索引：用于快速搜索文本内容（FULLTEXT）
  • 组合索引：由多个列组成的索引

6. 什么是最左前缀匹配原则？

答案： 最左前缀原则是指复合索引中，查询条件需要从索引的最左列开始匹配。

例如，创建索引 CREATE INDEX idx_user ON users (name, age, city);：

  • WHERE name = 'John' - 可以使用索引
  • WHERE name = 'John' AND age = 25 - 可以使用索引
  • WHERE age = 25 - 不能使用索引
  • WHERE name LIKE '张%' - 可以使用索引
  • WHERE name LIKE '%张' - 不能使用索引

7. 什么是回表操作？如何避免？

答案： 回表是指通过普通索引查询到主键后，再根据主键去聚簇索引中查询完整数据行的过程。

例如：

SELECT * FROM user WHERE card_no = '1101011990d3d73677';

执行过程：

  1. 先通过 card_no 索引找到对应的主键 id
  2. 再通过主键 id 去聚簇索引中查询完整数据

避免回表的方法：

  • 使用覆盖索引（查询的字段都在索引中存在）
  • 使用联合索引包含查询中需要的字段

8. 什么是索引覆盖？

答案： 索引覆盖是指查询的字段在索引中都存在，可以直接从索引中获取数据，不需要回表查询。

例如：

SELECT id FROM user WHERE card_no = '1101011990d3d73677';

由于 id 是主键，card_no 索引中已经包含了主键信息，所以不需要回表。

9. 什么是索引下推？

答案： 索引下推（Index Condition Pushdown）是 MySQL 5.6 版本引入的优化技术，指在联合索引查询中，MySQL 会在索引遍历过程中对索引中包含的字段进行条件过滤，减少回表次数。

例如，对于联合索引 (name, age)：

SELECT * FROM user WHERE name LIKE 'zhang%' AND age = 10;

启用索引下推后，MySQL 会在索引层直接过滤 age = 10 的条件，只返回符合条件的记录进行回表。

三、事务与锁

10. MySQL 的事务隔离级别有哪些？默认是什么？

答案： MySQL 的事务隔离级别由低到高依次是：

  • 读未提交（READ UNCOMMITTED）：可能读取到其他事务未提交的数据（脏读）
  • 读已提交（READ COMMITTED）：只能读取到其他事务已提交的数据，避免脏读，但可能出现不可重复读
  • 可重复读（REPEATABLE READ）：MySQL 默认隔离级别，同一事务中多次读取同一数据结果一致，避免脏读和不可重复读，但可能出现幻读
  • 串行化（SERIALIZABLE）：最高隔离级别，完全锁定表，避免所有并发问题，但性能最差

11. 什么是脏读、不可重复读、幻读？

答案：

  • 脏读：读取到其他事务未提交的数据
  • 不可重复读：同一事务中多次读取同一数据，结果不一致
  • 幻读：同一事务中多次查询同一范围的数据，结果行数不一致

12. MySQL 中的锁机制有哪些？

答案：

  • 表级锁：锁定整个表，并发性能低，MyISAM 默认使用
  • 行级锁：锁定单行数据，并发性能高，InnoDB 默认使用
  • 页级锁：锁定数据页，粒度介于表级和行级之间

行级锁的类型：

  • 共享锁（S锁）：允许读取但不允许修改
  • 排他锁（X锁）：不允许其他事务读取或修改

四、性能优化

13. 如何分析 SQL 的性能？

答案： 使用 EXPLAIN 分析 SQL 执行计划：

EXPLAIN SELECT * FROM users WHERE id = 1;

关键分析字段：

  • type：查询类型，优化顺序为 system > const > ref > range > index > ALL
  • key：实际使用的索引
  • rows：预估扫描的行数
  • Extra：额外信息，如 Using filesort、Using temporary 等

14. 如何优化慢查询？

答案：

  • 索引优化：
    • 创建合适的索引
    • 优化复合索引顺序，遵循最左前缀原则
    • 删除冗余索引
  • SQL 优化：
    • 避免 SELECT *，只查询需要的字段
    • 避免在 WHERE 子句中使用函数操作索引列
    • 避免在 WHERE 子句中使用 != 或 NOT IN
    • 使用 LIMIT 限制返回行数
  • 数据库配置优化：
    • 调整 innodb_buffer_pool_size
    • 优化连接池配置

15. 如何优化大表的分页查询？

答案： 对于大数据量的分页查询，使用 OFFSET 可能导致性能问题：

-- 效率较低
SELECT * FROM user LIMIT 25000, 1000;

-- 优化方案1：使用 ID 范围
SELECT * FROM user WHERE id > 25000 LIMIT 1000;

-- 优化方案2：使用覆盖索引
SELECT * FROM user WHERE id IN (SELECT id FROM user LIMIT 25000, 1000);

16. 数据库设计的三大范式是什么？

答案：

  • 第一范式（1NF）：确保每列保持原子性，所有字段值都是不可分解的原子值
  • 第二范式（2NF）：确保表中的每列都和主键完全相关，消除部分依赖
  • 第三范式（3NF）：确保每列都和主键列直接相关，消除传递依赖

五、高级特性

17. 什么是死锁？如何避免？

答案： 死锁是指两个或多个事务互相持有对方需要的锁，导致循环等待的情况。

避免方法：

  • 以固定顺序获取锁
  • 减少事务持有锁的时间
  • 设置合理的锁超时时间
  • 使用乐观锁替代悲观锁

18. 什么是 MVCC？

答案： MVCC（多版本并发控制）是 InnoDB 实现隔离级别的技术，通过保存数据的历史版本，实现并发读而不阻塞写，写不阻塞读。

MVCC 通过以下机制实现：

  • 每行数据保存创建版本号和删除版本号
  • 事务读取时，只读取创建版本号小于等于当前事务版本号，且删除版本号大于当前事务版本号或为空的数据

19. 聚簇索引和非聚簇索引的区别？

答案：

  • 聚簇索引：索引和数据存储在一起，索引的叶子节点存储数据行。InnoDB 的主键索引是聚簇索引。
  • 非聚簇索引：索引和数据分开存储，索引的叶子节点存储指向数据行的指针。MyISAM 的索引都是非聚簇索引。

20. MySQL 的执行计划中的 type 字段有哪些值？

答案： type 字段表示查询使用的访问类型，从好到差依次是：

  • system：表只有一行记录
  • const：通过主键或唯一索引查询单行
  • eq_ref：连接查询中，被驱动表通过唯一索引访问
  • ref：使用非唯一索引查询
  • range：使用索引进行范围查询
  • index：扫描整个索引
  • ALL：全表扫描

六、运维相关

21. MySQL 的备份方式有哪些？

答案：

  • 物理备份：直接复制数据文件，如 cp、tar 命令
  • 逻辑备份：使用 mysqldump 等工具导出 SQL 语句
  • 增量备份：基于二进制日志的备份
  • 热备份：备份过程中不影响数据库正常使用
  • 冷备份：备份过程中数据库需要停机

22. 如何处理 MySQL 主从复制延迟？

答案：

  • 优化主库的写入性能
  • 使用更高性能的硬件
  • 调整从库的配置参数
  • 减少从库的查询负载
  • 使用半同步复制

23. MySQL 慢查询日志如何配置？

答案： 在 my.cnf 中配置：

slow_query_log = ON
slow_query_log_file = /var/lib/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = ON

以上是 MySQL 常见面试题的汇总，涵盖了从基础概念到高级特性的各个方面，希望对您有所帮助！

avatar
文章
阅读
粉丝