企业官网建设流程全解析

MySQL数据库面试题（完整版）

目录

• 1. 三大范式
• 2. 数据存储类型
  • 2.1 varchar与char的区别
  • 2.2 blob和text有什么区别？
  • 2.3 DATETIME和TIMESTAMP的异同
  • 2.4 IN和EXISTS区别
• 3. Select语句完整的执行顺序
• 4. MySQL事务
  • 4.1 事务的基本要素ACID
  • 4.2 ACID靠什么保证
  • 4.3 事务的并发问题
  • 4.4 MySQL事务隔离级别
  • 4.5 MVCC
    • 4.5.1 MVCC是什么
    • 4.5.2 MVCC组成
    • 4.5.3 MVCC工作原理
    • 4.5.4 MVCC解决什么问题
• 5. MySQL索引
  • 5.1 索引的定义、类型及使用
  • 5.2 创建索引的注意点
  • 5.3 索引失效的情况
  • 5.4 建立索引的时机
  • 5.5 MySQL索引数据结构
    • 5.5.1 聚簇索引和非聚簇索引
  • 5.6 B树和B+树的区别
  • 5.7 为什么MySQL索引常用B+树
  • 5.8 索引数据结构选择依据
  • 5.9 MyISAM和InnoDB的区别
  • 5.10 最左前缀原则/最左匹配原则
• 6. MySQL锁
  • 6.1 乐观锁和悲观锁
  • 6.2 死锁问题及排查
  • 6.3 死锁解决办法
• 7. MySQL日志
• 8. MySQL高可用/高性能
  • 8.1 MySQL高可用方案
  • 8.2 主从同步延迟处理
• 9. SQL优化
  • 9.1 慢SQL定位与排查
  • 9.2 MySQL优化原则
• 10. 实际工作问题
  • 10.1 百万级数据删除
  • 10.2 大表添加字段
  • 10.3 MySQL CPU飙升处理
• 11. 常见面试SQL

1. 三大范式

• 第一范式：数据表中的每一列（每个字段）都不可以再拆分。
• 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能依赖于主键的一部分。例如订单表里存储商品信息时，需将商品ID和订单ID作为联合主键。
• 第三范式：在满足第二范式的基础上，表中的非主键只依赖于主键，而不依赖于其他非主键。例如订单表不能存储用户姓名、地址等用户信息。

2. 数据存储类型

2.1 varchar与char的区别

2.2 blob和text有什么区别？

2.3 DATETIME和TIMESTAMP的异同

2.4 IN和EXISTS区别

3. Select语句完整的执行顺序

SELECT语句完整执行顺序：
FROM->JOIN->WHERE->GROUP BY->HAVING->SELECT->DISTINCT->ORDER BY->LIMIT/OFFSET

分步说明：

1. FROM：确定查询数据来源表
2. JOIN：多表时按条件连接表
3. WHERE：筛选符合条件的行
4. GROUP BY：按指定列分组
5. HAVING：筛选分组后的结果
6. SELECT：指定返回的列
7. DISTINCT：去除重复行
8. ORDER BY：对结果集排序
9. LIMIT/OFFSET：限制返回行数和偏移量

4. MySQL事务

4.1 事务的基本要素ACID

• 原子性（Atomicity）：事务操作要么全部成功，要么全部失败回滚
• 一致性（Consistency）：事务执行前后，数据库数据保持一致状态
• 隔离性（Isolation）：多个事务并发执行时，互不干扰
• 持久性（Durability）：事务提交后，数据修改永久生效

4.2 ACID靠什么保证

• A（原子性）：undo log日志，记录回滚信息，事务回滚时撤销已执行的SQL
• C（一致性）：由其他三大特性+业务代码保证
• I（隔离性）：锁机制（共享锁、排他锁）+ MVCC多版本并发控制
• D（持久性）：内存+redo log，修改数据时同时记录到内存和redo log，宕机可恢复

4.3 事务的并发问题

4.4 MySQL事务隔离级别

4.5 MVCC

4.5.1 MVCC是什么

MVCC（多版本并发控制）是数据库的版本控制机制，通过维护数据多个版本，支持不同事务并发执行，解决脏读、幻读、不可重复读问题。

4.5.2 MVCC组成

• 数据版本：新版本、旧版本
• 事务ID：每个事务唯一ID
• 回滚段：存储数据旧版本

4.5.3 MVCC工作原理

• 读未提交：MVCC无作用
• 读已提交：事务开始创建一致性视图，查询取最新已提交版本，避免脏读，但存在不可重复读/幻读
• 可重复读：事务开始创建视图且全程不变，解决脏读、不可重复读；幻读通过MVCC+间隙锁缓解
• 串行化：MVCC无作用

4.5.4 MVCC解决什么问题

• 解决脏读、不可重复读、幻读
• 读写可同时进行，提高并发量，减少锁竞争
• 读不加锁，读写不冲突，保证事务隔离性

5. MySQL索引

5.1 索引的定义、类型及使用

定义：索引是提高查询速度的数据结构
类型：

1. 主键索引：唯一，非空，每个表仅一个
2. 唯一索引：值唯一，可含NULL，表可多个
3. 普通索引：无限制，基础索引类型
4. 全文索引：用于文本数据全文搜索
5. 组合索引：多列组合，遵循最左前缀原则

5.2 创建索引的注意点

1. 建在查询频繁的字段上
2. 索引数量适量（占用空间，更新需维护）
3. 频繁更新的值不做主键/索引
4. 小表不建索引
5. 离散度低的字段（如性别）不建索引

5.3 索引失效的情况

1. 查询条件含or、like、内置函数、运算（+/-/*/）、!=/<>=、not in、is null/is not null
2. 字符串字段查询未加引号（隐式类型转换）
3. 联合索引不遵循最左前缀原则
4. 左右连接时关联字段编码不一致

5.4 建立索引的时机

1. 表数据量大，查询慢
2. 多表关联的字段
3. GROUP BY/ORDER BY的字段

5.5 MySQL索引数据结构

• B树
• B+树（InnoDB/MyISAM默认）
• 哈希（Hash）（Memory引擎）

5.5.1 聚簇索引和非聚簇索引

• 聚簇索引（InnoDB）：B+树叶子节点包含完整数据行
• 非聚簇索引（MyISAM）：B+树叶子节点存储索引键值+主键值，查询需回表

5.6 B树和B+树的区别

• B树：平衡多路搜索树，键值分布在整棵树，非叶子节点可能找到目标
• B+树：B树变形，数据仅存于叶子节点，非叶子节点仅索引；叶子节点通过指针形成有序链表

5.7 为什么MySQL索引常用B+树

1. 磁盘I/O少：非叶子节点仅存索引，节点存储更多索引，树高度低
2. 范围查询优：叶子节点链表相连，便于范围查询
3. 查询稳定：每次查询都到叶子节点，效率稳定
4. 红黑树劣势：数据量大时树高，磁盘I/O多，且有变色旋转开销

5.8 索引数据结构选择依据

1. 精确查找为主：哈希索引（存储引擎支持时）
2. 范围查询/排序多：B+树索引
3. 通用场景：B+树（MySQL默认）

5.9 MyISAM和InnoDB的区别

5.10 最左前缀原则/最左匹配原则

InnoDB联合索引中，需匹配前一个/左边的值才能匹配下一个。
如创建组合索引(a1,a2,a3)，等价于创建：

• (a1)
• (a1,a2)
• (a1,a2,a3)

6. MySQL锁

6.1 乐观锁和悲观锁

悲观锁

• 认为数据随时会被修改，事务拿到锁后，其他事务无法修改，只能等待
• 数据库行锁、表锁、读锁、写锁均为悲观锁

乐观锁

• 认为数据变动不频繁，通过版本号（version）或时间戳（timestamp）实现
• 版本号为最常用方式

6.2 死锁问题及排查

行锁争用导致的死锁

原因：事务持有锁时间过长，行锁争用激烈
解决思路：减少事务持有锁时间，移出不必要操作，缩短执行时间

示例：

-- 事务1STARTTRANSACTION;UPDATEusersSETage=30WHEREid=1;-- 对id=1加排他锁-- 事务2STARTTRANSACTION;SELECT*FROMusersWHEREid=2FORUPDATE;-- 对id=2加排他锁UPDATEusersSETage=25WHEREid=1;-- 等待事务1释放锁-- 事务1UPDATEusersSETname='John'WHEREid=2;-- 等待事务2释放锁，形成死锁

事务顺序不一致导致的死锁

原因：不同事务操作表的顺序不一致
解决思路：统一事务操作表的顺序

示例：

-- 事务1STARTTRANSACTION;UPDATEtable1SETname1='A'WHEREid1=1;UPDATEtable2SETname2='X'WHEREid2=1;-- 事务2STARTTRANSACTION;UPDATEtable2SETname2='Y'WHEREid2=1;UPDATEtable1SETname1='B'WHEREid1=1;-- 顺序相反，易死锁

死锁排查步骤

1. 查看死锁日志：show engine innodb status;
2. 找出死锁SQL
3. 分析SQL加锁情况
4. 模拟死锁场景
5. 分析死锁日志
6. 得出解决方案

6.3 死锁解决办法

1. 统一操作顺序：所有事务对表的操作顺序一致（如先操作questions→answers→feedback）
2. 拆分事务：大事务拆分为小事务，减少锁持有时间
3. 设置超时时间：通过innodb_lock_wait_timeout参数设置锁等待阈值，超时自动回滚

7. MySQL日志

1. 错误日志（error log）：记录启动、运行、关闭过程，定位MySQL问题
2. 慢查询日志（slow query log）：记录执行时间超过long_query_time的SQL，用于优化
3. 一般查询日志（general log）：记录所有数据库请求，无论是否执行成功
4. 二进制日志（bin log）：记录DDL/DML语句（不含select/show），事件形式存储
5. InnoDB特有日志：
   • 重做日志（redo log）：记录事务日志，保证持久性
   • 回滚日志（undo log）：记录数据回滚信息，保证原子性

8. MySQL高可用/高性能

8.1 MySQL高可用方案

读写分离

原理：将读写操作分散到不同节点

• 搭建主从集群（一主一从/一主多从）
• 主机：负责读写操作
• 从机：仅负责读操作
• 主机通过复制同步数据到从机
• 业务端：写操作发主机，读操作发从机

实现方式：

• 程序代码封装：抽象数据访问层，实现读写分离和连接管理

分库分表

水平分表路由方式：

1. 范围路由：按有序列（整形/时间戳）分段
2. Hash路由：字段Hash运算后分散

常用中间件：

• sharding-jdbc
• Mycat

分库分表问题：

1. 事务问题：需分布式事务
2. 跨库JOIN：业务代码中关联查询

主从复制过程

1. master写入数据，更新binlog
2. master创建dump线程向slave推送binlog
3. slave创建IO线程接收binlog，写入relay log
4. slave创建SQL线程读取relay log并执行，完成同步
5. slave记录自身binlog

8.2 主从同步延迟处理

原因：从库仅一个线程读取binlog，主库大并发更新导致SQL积压

解决办法：

1. 写操作后的读操作指定主库
2. 读从机失败后重试主机
3. 关键业务读写走主机，非关键业务读写分离

9. SQL优化

9.1 慢SQL定位与排查

发现慢SQL

1. 开启慢查询日志：

   # my.cnf/my.ini配置 slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 2 # 阈值2秒

   重启MySQL生效
2. SHOW PROCESSLIST;：查看Time列，值大的为慢SQL

分析慢SQL

1. EXPLAIN分析执行计划：关注type（ALL全表扫描差）、possible_keys、key
2. 索引检查：验证过滤/排序/连接字段是否有索引，示例：

   CREATEINDEXidx_ageONusers(age);

3. 锁等待查看：查询information_schema.innodb_trx/innodb_locks
4. SQL优化：减少复杂子查询/连接，添加过滤条件

工具辅助

• pt-query-digest分析慢查询日志：

  pt-query-digest /var/log/mysql/mysql-slow.log

9.2 MySQL优化原则

1. 分析表，适当建立索引
2. 单表数据不超200W，适时分表；用explain分析慢查询
3. FROM子句后表为基础表，选记录少的表
4. 尽量缩小子查询结果

10. 实际工作问题

10.1 百万级数据删除

1. 删除索引
2. 删除无用数据
3. 重新创建索引

10.2 大表添加字段

方案1：中间表转换

1. 创建临时表，复制旧表结构
2. 添加新字段
3. 复制旧表数据到临时表
4. 删除旧表，临时表重命名为旧表名
5. 缺点：可能丢失部分数据

方案2：主从切换

1. 先在从库添加字段
2. 主从切换
3. 其他节点添加字段

10.3 MySQL CPU飙升处理

排查步骤

1. top命令确认是否mysqld导致
2. show processlist查看消耗资源的SQL
3. 分析SQL执行计划、索引、数据量

处理方式

1. kill消耗高的线程，观察CPU是否下降
2. 调整（加索引、改SQL、调内存参数）
3. 重新执行SQL

11. 常见面试SQL

例1：查询每门课都大于80分的学生姓名

数据表：

解法1：

SELECTDISTINCTnameFROMtableWHEREnameNOTIN(SELECTDISTINCTnameFROMtableWHEREfenshu<=80);

解法2：

SELECTnameFROMtableGROUPBYnameHAVINGMIN(fenshu)>80;

例2：删除学生冗余信息

数据表：

SQL：

DELETEtablenameWHERE自动编号NOTIN(SELECTMIN(自动编号)FROMtablenameGROUPBY学号,姓名,课程编号,课程名称,分数);

例3：查询球队所有比赛组合

数据表：team（name字段：a,b,c,d）

SQL：

SELECTa.name,b.nameFROMteam a,team bWHEREa.name<b.name;

例4：行转列查询

原表：

目标结果：

SQL：

SELECTyear,(SELECTamountFROMaaa mWHEREmonth=1ANDm.year=aaa.year)ASm1,(SELECTamountFROMaaa mWHEREmonth=2ANDm.year=aaa.year)ASm2,(SELECTamountFROMaaa mWHEREmonth=3ANDm.year=aaa.year)ASm3,(SELECTamountFROMaaa mWHEREmonth=4ANDm.year=aaa.year)ASm4FROMaaaGROUPBYyear;

例5：复制表结构（源表a，新表b）

MySQL：

CREATETABLEbASSELECT*FROMaWHERE1=2;

SQL Server：

SELECT*INTObFROMaWHERE1<>1;-- 1=1复制结构+数据

例6：添加及格标记

原表：

目标结果：

SQL：

SELECTcourseid,coursename,score,IF(score>=60,"pass","fail")ASmarkFROMcourse;

例7：查询购入2种以上商品的购物人

数据表：

SQL：

SELECT*FROM购物信息WHERE购物人IN(SELECT购物人FROM购物信息GROUPBY购物人HAVINGCOUNT(*)>=2);

例8：统计每日胜负次数

数据表：

目标结果：

解法1：

SELECTdate,SUM(CASEWHENresult="win"THEN1ELSE0END)AS"win",SUM(CASEWHENresult="lose"THEN1ELSE0END)AS"lose"FROMinfoGROUPBYdate;

解法2：

SELECTa.date,a.resultASwin,b.resultASloseFROM(SELECTdate,COUNT(result)ASresultFROMinfoWHEREresult="win"GROUPBYdate)ASaJOIN(SELECTdate,COUNT(result)ASresultFROMinfoWHEREresult="lose"GROUPBYdate)ASbONa.date=b.date;

例9：联合索引生效判断

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

1. WHERE a=1 AND b=1 AND c=1→ 生效
2. WHERE a=1 AND c=1→ 生效（a匹配最左前缀）
3. WHERE b=1 AND c=1→ 失效（无a）
4. WHERE b=1 AND a=1 AND c=1→ 生效（顺序不影响）

### 总结 1. 文档完整整合了所有MySQL面试题内容，修正了原始格式错误（如表格排版、代码缩进、标点符号），补充了缺失的逻辑内容； 2. 按照目录层级结构化梳理，每个知识点独立成节，关键内容用表格/代码块/列表呈现，可读性大幅提升； 3. 保留了所有核心考点（三大范式、事务、索引、锁、优化、面试SQL等），并补充了完整的SQL示例和配置示例，可直接用于复习和面试准备。