Cao Note

外观

MySQL ​

配套知识点：MySQL 深入

怎么用这份笔记

  • 分级：★★★ 必须吃透 / ★★☆ 了解即可（记结论就行） / ★☆☆ 可以跳过（一句话看一眼）
  • 【简历亮点，高频追问】：简历里写过的技术，面试官会顺着往下挖，这段必须能展开讲
  • 每个考点按「定义 → 原理 → 要点 / 易错点」组织，只讲知识点

1. InnoDB 与 MyISAM 区别 ★★★ ​

InnoDB 支持事务、行级锁、外键、崩溃恢复，索引是聚簇的（数据即索引）；MyISAM 不支持事务、外键和崩溃恢复，只有表级锁，索引和数据分离，但无事务开销，某些只读查询略快。MySQL 5.5 之后默认就是 InnoDB，涉及事务的场景只能选 InnoDB。

按维度对比：

  • 事务与外键：InnoDB 每条 SQL 默认封装成事务、自动提交，会影响速度，多条 SQL 最好显式放在 begin / commit 之间组成一个事务；MyISAM 不支持事务和外键，带外键的 InnoDB 表转 MyISAM 会失败。
  • 索引结构：InnoDB 是聚簇索引，数据文件和索引绑在一起，必须要有主键，主键索引效率高；辅助索引需要两次查询，先查到主键再回表取数据，所以主键不应过大，否则其它索引也会跟着变大。MyISAM 是非聚簇索引，数据文件分离，索引保存的是数据文件的指针，主键索引和辅助索引相互独立。
  • 行数统计：InnoDB 不保存表的具体行数，select count(*) from table 需要全表扫描；MyISAM 用一个变量保存整表行数，执行时直接读出该变量，速度很快。
  • 全文索引：InnoDB 不支持全文索引，MyISAM 支持，查询效率上 MyISAM 更高。

结论：生产环境一律 InnoDB。

补充（原文详解）

  1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提 交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

  2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

  3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。

但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的， 索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

  1. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用 一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

  2. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

2. 索引为什么用 B+ 树 ★★★ ​

选 B+ 树的核心是磁盘 IO：树越矮，访问磁盘的次数越少。

排除其它结构的原因：

  • 哈希：等值查询很快，但不支持范围查询和排序。
  • 二叉搜索树 / 红黑树：每个节点最多两个分支，树高随数据量增长，磁盘 IO 次数跟着树高走，数据量大时 IO 过多。
  • B 树：多路搜索树，但每个节点既存索引又存数据，一页能放的索引项少，树也高。
  • B+ 树：非叶子节点只存索引、不存数据，一页能塞更多索引，树更矮（3 层能存千万级数据）；数据全在叶子节点，叶子之间用双向链表连起来，天然支持范围查询和排序。

因此按「桩编号 + 时间范围」这类条件查询时，可以沿叶子链表顺序扫描，这也是时间字段要放进联合索引的原因。

3. 聚簇索引、回表、覆盖索引 ★★★ ​

聚簇索引（主键索引）的叶子节点存整行数据，一张表只能有一个。二级索引的叶子存「索引列 + 主键值」，用它查到主键后还得回聚簇索引再捞一次整行，这叫回表。如果查询需要的列全在二级索引里，就不用回表，这就是覆盖索引，性能最好，也是最常用的优化手段。

主键建议自增：顺序插入减少页分裂；UUID 随机插入会频繁页分裂、产生碎片，还占空间，所以不建议做主键。

4. 最左前缀与联合索引 ★★★ ​

联合索引 (a,b,c) 按 a、b、c 的字典序排列，所以查询必须从最左列开始连续匹配才能用上索引：(a)、(a,b)、(a,b,c) 能命中，直接查 b、c 不行。

范围查询会「中断」匹配：a=1 and b>2 能用上 a 和 b，但 b 后面的 c 就用不上了。所以设计联合索引时，等值条件、区分度高的列放最左，范围查询列尽量往后放，例如「桩编号（等值）+ 订单状态（等值）+ 创建时间（范围）」这种顺序。

5. 索引失效场景 ★★★ 【简历亮点，高频追问】 ​

常见失效场景：

  • 索引列上做函数或运算：YEAR(create_time)=2024、id+1=5；
  • 隐式类型转换：字符串字段没加引号，比如手机号传成数字，或反过来；
  • 不满足最左前缀；
  • 范围查询后面的列；
  • 使用 !=、NOT IN；
  • 前导模糊 LIKE '%xx'；
  • OR 连接了非索引列；
  • 数据量特别小时，优化器判断全表扫描更快。

隐式类型转换是最容易忽略的一种：字段类型和参数类型不一致就会触发隐式转换，索引直接失效，现象是建了索引查询依然很慢，修复方式是保证参数类型和字段类型一致。判断有没有走索引，用 EXPLAIN 看 type 和 key。

6. EXPLAIN 关键字段 ★★★ ​

重点看四列：

  • type：访问类型，从好到差为 system、const、eq_ref、ref、range、index、ALL。看到 ALL 基本就是缺索引或索引失效，即全表扫描。
  • key：实际使用的索引。
  • rows：预估扫描行数，越小越好。
  • Extra：Using index 表示覆盖索引，是好的；Using filesort 表示需要额外排序，要优化；Using temporary 表示用了临时表，通常是 group by / distinct 没走索引。

排查慢 SQL 的顺序就是先看 type 是否为 ALL，再看 Extra 有没有 filesort 或 temporary。

7. 事务 ACID 及实现原理 ★★★ ​

ACID 各自的实现机制：

  • 原子性（A）：靠 undo log，操作失败可以回滚。
  • 持久性（D）：靠 redo log，提交后即使崩溃也不丢。
  • 隔离性（I）：靠锁加 MVCC。
  • 一致性（C）：由 A、I、D 共同保证的结果，不是单独某个机制。

两种日志的分工：redo log 记「改了什么」，用于崩溃恢复；undo log 记「改前的值」，用于回滚和 MVCC。

事务不能写太大：大事务会长时间占锁、undo 膨胀、还可能拖慢主从复制，批量场景应分批提交。

8. 隔离级别与并发问题 ★★★ ​

三种并发问题：

  • 脏读：读到别的事务还没提交的数据。
  • 不可重复读：同一事务内两次读同一行，中间别的事务 update 了，两次结果不同。
  • 幻读：同一事务内两次读，中间别的事务 insert / delete，行数变了。

四个隔离级别：

  • READ-UNCOMMITTED（读未提交）：三种问题都有。
  • READ-COMMITTED（读已提交，RC）：解决脏读，仍可能不可重复读和幻读；Oracle 默认。
  • REPEATABLE-READ（可重复读，RR）：解决脏读和不可重复读，MySQL 默认；InnoDB 配合 MVCC 和间隙锁基本解决幻读。
  • SERIALIZABLE（串行化）：全部解决，但事务串行执行，性能最差。

RR 下间隙锁较多，并发写容易死锁，批量更新场景要控制锁范围、缩短事务。

补充（原文详解）

SQL 标准定义了四个隔离级别：

READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别脏读不可重复读幻影读
READ-UNCOMMITTED√√√
READ-COMMITTED×√√
REPEATABLE-READ××√
SERIALIZABLE×××

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过 SELECT @@tx_isolation; 命令来查看

java
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

这里需要注意的是：与 SQL 标准不同的地方在于 InnoDB 存储引擎在 REPEATABLE-READ（可重读） 事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server) 是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE- READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串行化) 隔离级别。因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是

READ-COMMITTED(读取提交内容)，但是你要知道的是InnoDB 存储引擎默认使用 REPEAaTABLE-READ（可重读） 并不会有任何性能损失。

InnoDB 存储引擎在分布式事务的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。

9. MVCC 多版本并发控制 ★★★ 【简历亮点，高频追问】 ​

MVCC 的作用是让「读不加锁、写不阻塞读」，读写互不阻塞。

实现依赖三样东西：

  • 每行记录有隐藏列：最近修改它的事务 id、指向 undo log 旧版本的回滚指针，构成版本链；
  • undo log 保存历史版本；
  • ReadView（快照）决定当前事务能看到哪些版本。

RC 与 RR 的区别就在 ReadView 的生成时机：RC 每次 SELECT 都新建一个，所以能看到别的事务新提交的数据；RR 在事务内第一次 SELECT 时建一次然后复用，所以可重复读。

由此可以解释两个现象：长事务里读到的始终是老数据；长事务会让 undo 版本链变长、拖慢查询。所以事务应尽量短，尤其不要在事务里做远程调用。

10. MySQL 锁：行锁、间隙锁、临键锁 ★★★ ​

InnoDB 支持行锁，按锁范围分三种：

  • 记录锁（Record Lock）：锁住单行记录；
  • 间隙锁（Gap Lock）：锁的是索引记录之间的空隙，防止别的事务往空隙里插入；
  • 临键锁（Next-Key Lock）：记录锁 + 间隙锁，锁「左开右闭」的区间，RR 下用来解决幻读。

死锁排查用 SHOW ENGINE INNODB STATUS，看 LATEST DETECTED DEADLOCK 段。

减少死锁的做法：更新条件尽量走主键或唯一索引，把范围锁降成单行锁；死锁真发生时 MySQL 会自动回滚其中一个事务，业务侧做好重试。

11. 三大范式与反范式 ★★☆ ​

三大范式逐级递进：

  • 1NF：列不可再分，每个字段都是原子值；
  • 2NF：在 1NF 基础上消除部分依赖，非主属性完全依赖主键；
  • 3NF：在 2NF 基础上消除传递依赖，非主属性不能依赖其它非主属性。

反范式就是故意冗余字段（比如订单表冗余桩编号、用户名），用空间换时间、减少 join。实践中常用「范式打底 + 关键查询适度冗余」的折中。

12. 三大日志：redo、undo、binlog ★★★ ​

  • redo log：InnoDB 的物理日志，记「数据页改了什么」，顺序写、循环写，用于崩溃恢复，保证持久性。
  • undo log：记「改前的值」，用于回滚和 MVCC。
  • binlog：Server 层的逻辑日志，记录所有变更，追加写，用于主从复制和数据恢复，主流格式是 row。

redo 与 binlog 靠两阶段提交保证一致，否则可能出现主库已提交、从库没收到的半成功状态，导致主从数据不一致。两者定位的区别是：redo 是引擎层物理日志、binlog 是 Server 层逻辑日志。

13. 主从复制原理 ★★★ ​

流程：主库写 binlog → 从库 IO 线程拉取 binlog，写到本地 relay log → 从库 SQL 线程重放 relay log。

作用是读写分离、备份、高可用。

延迟原因：从库重放慢（早期单线程重放）、存在大事务、从库硬件较差、网络延迟。

读写分离要注意：写完立刻要读的场景（比如下单后马上查订单）必须强制走主库，否则主从延迟会读到旧数据。

14. 慢查询优化思路 ★★★ 【简历亮点，高频追问】 ​

四步走：

  1. 用慢查询日志 + EXPLAIN 定位问题 SQL；
  2. 看 type 是不是 ALL、key 是否为空，判断缺不缺索引；
  3. 加合适的索引：满足最左前缀、尽量做覆盖索引、避开索引失效场景；
  4. 再优化 SQL 本身：别 SELECT *、别在索引列上用函数、小表驱动大表。

四步都做完还不行，才上读写分离、缓存、分库分表。原则是先看执行计划再动手，不要一上来就加缓存或分表。

15. 分库分表 ★★☆ ​

两种拆法：

  • 垂直拆：按业务拆库（订单库、用户库），或按列拆表（冷热字段分离）；
  • 水平拆：按行分，用 id 哈希或范围把数据分散到多表多库。

中间件常用 ShardingSphere。分库分表会带来这些问题：分布式主键（雪花算法）、跨库 join、分布式事务、路由。

它是最后手段，优化顺序是「索引优化 → 缓存 → 读写分离 → 分库分表」；一般到单表千万级、单库出现瓶颈时才考虑。

16. 大表如何优化 ★★★ ​

从轻到重：

  • 索引优化，用覆盖索引避免回表；
  • 冷热数据分离，历史数据归档；
  • 读写分离 + 缓存热点；
  • 垂直 / 水平拆分；
  • 最后才考虑列式存储 / 大数据引擎。

典型做法是近期数据留在 MySQL（热），历史数据归档到冷库或 ClickHouse（冷），查询时路由到对应数据源，让主表一直保持较小。

补充（原文详解）

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

1. 限定数据的范围 ​

务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；

2. 读/写分离 ​

经典的数据库拆分方案，主库负责写，从库负责读；

3. 垂直分区 ​

根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。 如下图所示，这样来说大家应该就更容易理解了。

1583307481617

垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外， 垂直分区可以简化表的结构，易于维护。

垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

4. 水平分区 ​

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

1583308353521

水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问

题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库。

水平拆分能够支持非常大的数据量存储，应用端改造也少，但分片事务难以解决，跨节点Join性能较差，逻辑复杂。《Java工程师修炼之道》的作者推荐尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。

下面补充一下数据库分片的两种常见方案：

客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC、阿里的TDDL是两种比较常用的实现。

中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat、360的Atlas、网易的DDB等等都是这种架构的实现。

详细内容可以参考： MySQL大表优化方案: https://segmentfault.com/a/1190000006158186

17. SQL 执行顺序 ★★☆ ​

执行顺序：FROM → JOIN / ON → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT。

记住它才不会写错 SQL：

  • WHERE 在分组之前执行，所以不能用聚合函数，聚合条件必须写进 HAVING。
  • SELECT 的别名在 WHERE 里不能用，因为 SELECT 比 WHERE 晚执行。
  • 写统计 SQL 时，靠这个顺序判断条件该放 WHERE 还是 HAVING。

18. 索引下推（ICP）★★★ ​

ICP 是 MySQL 5.6 引入的优化：把「能在索引层判断的条件」下推到存储引擎层过滤，减少回表次数。

例子：联合索引 (name, age) 查 name like '张%' and age=20。

  • 没有 ICP：先把所有 name 以「张」开头的记录回表，再在 Server 层过滤 age；
  • 有 ICP：直接在索引层用 age 过滤，只有 name 和 age 都满足的记录才回表。

限制：只对二级索引生效；条件里用了函数或运算就不会下推。这也解释了为什么「联合索引里带上过滤字段」能加速——可以在索引层就过滤掉大部分数据。

19. 索引设计原则 ★★★ ​

  • 选区分度高的列建索引；
  • 为 WHERE、ORDER BY、GROUP BY、JOIN 涉及的列建索引；
  • 联合索引按最左前缀设计，等值列在前、范围列在后；
  • 尽量做成覆盖索引；
  • 索引不能建太多，否则拖慢写入；
  • 长字符串用前缀索引；
  • 主键用自增。

区分度极低的列（性别、状态枚举）单独建索引没有意义，但可以作为联合索引里的过滤列。

20. 如何优化表结构 ★★☆ ​

  • 字段类型选够用的最小类型，能用 int 就不用 varchar，状态、类型用 tinyint；
  • 尽量 NOT NULL 并给默认值；
  • 大字段（TEXT / BLOB，如报文原文）拆到扩展表，别撑大主表、拖慢查询；
  • 范式打底 + 适度反范式。

21. MySQL 如何避免死锁 ★★★ ​

死锁是两个事务按不同顺序加锁、互相等待对方释放锁。避免手段：

  • 按固定顺序访问表和行；
  • 用主键 / 唯一索引精确定位，缩小锁范围；
  • 事务尽量小、尽快提交；
  • 一次申请完所需的所有锁；
  • 设置加锁超时，依赖死锁检测。

发生死锁时 MySQL 会自动检测并回滚代价较小的那个事务，业务侧重试即可。排查用 SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 段。

22. 大量数据插入的性能优化 ★★★ ​

批量插（一条 INSERT 带多个 VALUES）比一条条插快很多，省的是网络往返、事务提交和 SQL 解析的开销。

进一步的手段：

  • autocommit=0，手动批量提交；
  • 用 LOAD DATA INFILE 导入数据；
  • 导入期间临时关闭唯一性 / 外键检查；
  • 必要时先关索引，导完再重建索引。

注意批次不是越大越好：批次太大就变成大事务，会长时间占锁、undo 膨胀、还可能拖慢主从复制。通常每 500~1000 条一批提交，兼顾速度和事务大小。

23. 大数据量批量写会导致什么问题 ★★☆ ​

  • 大事务：undo 膨胀、占锁久、造成主从延迟；
  • 锁竞争：批量更新锁范围大，容易死锁或长时间阻塞；
  • binlog 膨胀；
  • 主从延迟；
  • 随机主键插入还会页分裂。

解决方式：分批提交、控制批次大小、用顺序主键、避开业务高峰；一次性更新几十万行最容易出问题。

24. IP 地址如何存数据库 ★★☆ ​

  • IPv4：推荐用 INT UNSIGNED 存（4 字节），配 INET_ATON() / INET_NTOA() 互相转换。好处是省空间（比 varchar 短）、比较大小快、IP 段范围查询能走索引。
  • IPv6：128 位，用 VARBINARY(16) 存，配 INET6_ATON() / INET6_NTOA() 转换。

25. drop、truncate、delete 的区别 ★★★ ​

  • delete 是 DML，逐行删除、可带 WHERE、走 undo log 可回滚、会触发触发器，自增主键不重置，速度最慢。
  • truncate 是 DDL，清空整张表、不能带 WHERE、隐式提交不可回滚、不触发触发器、自增值重置，本质是重建表（或直接释放数据页），速度快。
  • drop 也是 DDL，直接删掉表结构和数据，连表定义都没了，速度最快。

一句话：delete 删数据、truncate 清空表、drop 删表。

易错点：truncate 是 DDL、会隐式提交、不能回滚，误清数据救不回来。线上清理历史数据可以用 truncate 提效，但业务主表要走归档脚本分批 delete 或按分区清理。

26. MySQL 关联查询（JOIN 类型及原理）★★★ ​

JOIN 类型：

  • INNER JOIN：只保留两表都匹配的行；
  • LEFT JOIN：左表全保留，右表没匹配的字段补 NULL；
  • RIGHT JOIN：右表全保留，左表没匹配的字段补 NULL；
  • FULL JOIN：MySQL 不支持，用 LEFT JOIN UNION RIGHT JOIN 模拟。

执行原理是嵌套循环连接（Nested-Loop Join）：驱动表逐行到被驱动表找匹配。由此有两条铁律：

  1. 小表驱动大表，减少外层循环次数；
  2. 被驱动表的连接字段必须有索引，否则驱动表每行都要对被驱动表做一次全表扫描，复杂度 O(N*M)，直接爆炸。

MySQL 8.0 还引入了 Hash Join 用于等值连接。JOIN 数量控制在两三个以内，再多的表改用冗余字段或拆成多次查询。

27. 分库分表之后，id 主键如何处理（雪花算法等分布式主键方案）★★★ ​

单表自增主键在分库分表后会重复，需要分布式主键。主流方案：

  • 雪花算法（Snowflake）：64 位长整型，1 位符号 + 41 位时间戳（约 69 年）+ 10 位机器 id + 12 位序列号。本地生成、趋势递增、全局唯一、性能高；缺点依赖机器时钟，时钟回拨可能生成重复 id。
  • 数据库号段模式（如美团 Leaf）：从一张表里批量取号段放内存，用完再取。严格有序，但多一次数据库依赖。
  • Redis INCR 自增：简单，但依赖 Redis 的可用性。
  • UUID：最简单，但无序，做主键会随机插入导致页分裂，且占空间，不推荐做主键。

雪花算法应对时钟回拨：等待时钟追上、复用上次的序列号，或做校验兜底。

28. 一条查询 SQL 的执行流程（连接器→分析器→优化器→执行器→存储引擎）★★★ ​

  1. 连接器：建立 TCP 连接，校验用户名密码和权限；长连接可复用，但连接数有限制。
  2. 查询缓存：key 是 SQL 语句、value 是查询结果，命中直接返回。MySQL 8.0 已整体移除该功能，8.0 之后不存在这一步。
  3. 分析器：做词法分析和语法分析，识别关键字、表名、列名并生成解析树；语法错误在这一层报出。
  4. 优化器：决定用哪个索引、多表 join 的连接顺序，生成执行计划（也可能选错索引）。
  5. 执行器：校验权限后调用存储引擎接口，逐行取数据、过滤，返回客户端。
  6. 存储引擎 InnoDB：负责真正的数据读写，走 buffer pool、索引和回表。

由此可以解释两类现象：连接数打满报 too many connections，属于连接器 / 连接池层的问题；EXPLAIN 显示走了不该走的索引，是优化器基于过期统计信息选错，可用 force index 或 analyze table 更新统计信息纠正。

29. DDL / DML / DCL / DQL 的区别 ★★☆ ​

  • DDL（数据定义语言）：操作表结构，CREATE、DROP、ALTER、TRUNCATE，会隐式提交、不可回滚。
  • DML（数据操纵语言）：操作数据，INSERT、UPDATE、DELETE，可回滚。
  • DCL（数据控制语言）：管理权限，GRANT、REVOKE。
  • DQL（数据查询语言）：SELECT。

常考易错点：truncate 归 DDL 而不归 DML，所以它不能回滚、不触发触发器，这正是它与 delete 本质区别的来源。

生产上 DDL 要格外小心：大表 alter 会锁表或整表重建，需要专门的上线窗口，必要时用在线变更工具；DML 批量写走分批提交。

30. 存储过程和触发器的作用及优缺点 ★★☆ ​

存储过程是把一段 SQL 逻辑预编译后存到数据库里，可以传参调用。

  • 优点：减少网络往返、复用逻辑、权限可控。
  • 缺点：难调试、难维护、难版本化、跨库迁移麻烦，逻辑藏在数据库里不好排查。

触发器是绑定在表上、在 INSERT / UPDATE / DELETE 前后自动执行的逻辑，常用于审计、数据校验、同步。

  • 缺点：逻辑隐蔽、逐行触发影响写入性能、出问题难定位。

互联网项目普遍把逻辑放应用层（可版本管理、可单测、可灰度），少用或不用存储过程和触发器。

31. MySQL 如何行转列和列转行（CASE WHEN、GROUP_CONCAT、UNION）★★☆ ​

  • 行转列：把同一维度下的多行变成多列。用 CASE WHEN（或 IF）做条件判断，外面套聚合函数 SUM / MAX，再配合 GROUP BY 分组。例：统计每个场站待支付、充电中、已完成三类订单数，就是按 status 做 CASE WHEN 再 SUM。
  • 列转行：把多列拆成多行，用 UNION ALL 把每列分别 SELECT 出来拼在一起。
  • GROUP_CONCAT：把多行合成一行里的一个字符串列，可指定分隔符；默认长度有限制，受 group_concat_max_len 控制。

32. UNION 和 UNION ALL 的区别 ★★★ ​

  • UNION：合并两个结果集并去重，内部要对合并结果做排序去重，有额外开销，慢。
  • UNION ALL：直接拼接、不去重，快。

两者都要求两个 SELECT 的列数、类型、顺序一致。

规则：能确定结果无重复、或本来就允许重复时，一律用 UNION ALL，不要白花去重排序的代价。例如把热数据（MySQL 主表）和冷数据（归档）合并成完整列表时，两边天然不重复，用 UNION 反而多一次去重排序拖慢接口。

33. HAVING 和 WHERE 的区别 ★★★ ​

  • WHERE 在分组（GROUP BY）之前过滤行，不能用聚合函数，也不能用 SELECT 的别名。
  • HAVING 在分组之后过滤分组，可以使用聚合函数。

执行顺序：WHERE → GROUP BY → HAVING → SELECT。

性能原则：能放 WHERE 的就放 WHERE，先把数据量过滤小再分组；HAVING 只处理必须基于聚合结果的条件（如 COUNT(*) > 10）。例如查订单数超过 10 的场站，场站状态这种行级条件放 WHERE，COUNT(*) > 10 只能放 HAVING。

34. IN 和 EXISTS 子句的区别及选择 ★★★ ​

  • IN：先执行子查询拿到结果集，再和外层逐行做集合匹配。适合子查询结果集小、外层表大的场景。
  • EXISTS：外层每行去子查询探测是否存在，找到一个匹配就返回 true、可提前终止，不加载整个结果集。适合外层表小、子查询大、且子查询能走索引的场景。

最大的坑：NOT IN 的子查询结果里只要有 NULL，整个结果就会变成空——NULL 参与比较结果为 unknown，所有行都被过滤；NOT EXISTS 没有这个问题。所以否定判断优先用 NOT EXISTS，排查「明明有数据却查不出来」时也要先想到它。

选择原则：子查询小用 IN、外层小用 EXISTS。

35. MySQL 如何处理 NULL 值，对索引/查询的影响 ★★★ ​

NULL 表示「未知 / 缺失」，它不等于任何值、包括它自己：NULL = NULL 的结果是 NULL 而不是 true，判断必须用 IS NULL / IS NOT NULL。

对聚合的影响：

  • COUNT(列) 会忽略 NULL 行，COUNT(*) 才统计全部行；
  • SUM / AVG 也会忽略 NULL。

对索引的影响：

  • 唯一索引允许插入多个 NULL，因为 NULL 之间被认为不相等；
  • 普通索引可以存 NULL，IS NULL 一般能走索引。

常见坑：NOT IN 子查询含 NULL 导致结果为空；字段可空时用 = 判断查不到 NULL 行；COUNT 的统计口径也容易混乱。因此建表尽量 NOT NULL 并给默认值，可空字段（如结束时间、退款时间）查询时用 IS NULL 而不是 = NULL。

36. 如何在 MySQL 中处理和避免全表扫描 ★★★ ​

全表扫描就是 EXPLAIN 里 type=ALL，逐行读整张表的所有数据页，数据量大时必然慢。

避免手段：

  1. 建对索引并确保走索引：满足最左前缀，避开索引列上用函数、隐式类型转换等失效场景；
  2. 避免 SELECT *，只取需要的列，尽量用覆盖索引；
  3. 用 WHERE 有效缩小范围，避免无过滤条件或低区分度的单列条件；
  4. JOIN 时小表驱动大表，被驱动表连接字段建索引；
  5. 深分页优化：大 OFFSET 会扫大量行，改用「先查主键再回表」的延迟关联或游标分页；
  6. 冷热分离、历史归档，让主表一直保持小，从根上降低全表扫描的代价。

最终用 EXPLAIN 确认 type 不是 ALL。例如订单列表变慢就是 type=ALL，加联合索引后降到 range；深分页用先按主键查一页 id 再回表，避免扫几万行。

附：原始题库（旧版逐题，与上文重叠，可折叠跳过） ​

展开查看原始题库

37. 数据库的三范式是什么 ​

第一范式：列不可再分第二范式：行可以唯一区分，主键约束第三范式：表的非主属性不能依赖与其他表的非主属性外键约束且三大范式是一级一级依赖的，第二范式建立在第一范式上，第三范式建立第一第二范式上。

38. MySQL数据库引擎有哪些 ​

如何查看mysql提供的所有存储引擎

java
mysql> show engines;
mysql> show engines;

MySQL数据库引擎有哪些

mysql常用引擎包括：MYISAM、 Innodb、 Memory、 MERGE

MYISAM：全表锁，拥有较高的执行速度，不支持事务，不支持外键，并发性能差，占用空间相对较小，对事务完整性没有要求，以select、 insert为主的应用基本上可以使用这引擎

Innodb:行级锁，提供了具有提交、回滚和崩溃回复能力的事务安全，支持自动增长列，支持外键约束，并发能力强，占用空间是MYISAM的2.5倍，处理效率相对会差一些

Memory:全表锁，存储在内容中，速度快，但会占用和数据量成正比的内存空间且数据在

mysql重启时会丢失，默认使用HASH索引，检索效率非常高，但不适用于精确查找，主要用于那些内容变化不频繁的代码表

MERGE：是一组MYISAM表的组合

39. 数据库的事务 ​

什么是事务？： 多条sql语句，要么全部成功，要么全部失败。

事务的特性：

数据库事务特性：原子性(Atomic)、一致性(Consistency)、隔离性(Isolation)、持久性 (Durabiliy)。简称ACID。

原子性：组成一个事务的多个数据库操作是一个不可分割的原子单元，只有所有操作都成功， 整个事务才会提交。任何一个操作失败，已经执行的任何操作都必须撤销，让数据库返回初始状态。

一致性：事务操作成功后，数据库所处的状态和它的业务规则是一致的。即数据不会被破坏。 如A转账100元给B，不管操作是否成功，A和B的账户总额是不变的。

隔离性：在并发数据操作时，不同的事务拥有各自的数据空间，它们的操作不会对彼此产生干扰

持久性：一旦事务提交成功，事务中的所有操作都必须持久化到数据库中。

40. 索引是什么 ​

官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

41. SQL优化手段有哪些 ​

1、查询语句中不要使用select *

2、尽量减少子查询，使用关联查询（left join,right join,inner join）替代

3、减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代

4、or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时， union all会更好)

5、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

6、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有 null值，然后这样查询： select id from t where num=0

42. 什么是视图 ​

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易， 相比多表查询。

43. 什么是内联接、左外联接、右外联接？ ​

内联接（Inner Join）：匹配2张表中相关联的记录。

左外联接（Left Outer Join）：除了匹配2张表中相关联的记录外，还会匹配左表中剩余的记录，右表中未匹配到的字段用NULL表示。

右外联接（Right Outer Join）：除了匹配2张表中相关联的记录外，还会匹配右表中剩余的记录，左表中未匹配到的字段用NULL表示。在判定左表和右表时，要根据表名出现在Outer Join 的左右位置关系。

44. 并发事务带来哪些问题? ​

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数

据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数

据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别：

不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

45. 分库分表之后,id 主键如何处理？ ​

因为要是分成多个表之后，每个表都是从 1 开始累加，这样是不对的，我们需要一个全局唯一的 id 来支持。

生成全局 id 有下面这几种方式：

UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯

一的名字的标示比如文件的名字。

数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。

利用 redis 生成 id :性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。

Twitter的snowflake算法：Github 地址： https://github.com/twitter-archive/snowflake。 美团的Leaf分布式ID生成系统：Leaf 是美团开源的分布式ID生成器，能保证全局唯一性、趋

势递增、单调递增、信息安全，里面也提到了几种分布式方案的对比，但也需要依赖关系数据库、 Zookeeper等中间件。感觉还不错。美团技术团队的一篇文章： https://tech.meituan.co m/2017/04/21/mt-leaf.html。

46. 说说在 MySQL 中一条查询 SQL 是如何执行的？ ​

比如下面这条SQL语句：

select name from t_user where id=1

  1. 取得链接，使用使用到 MySQL 中的连接器。

  2. 查询缓存 ，key 为 SQL 语句，value 为查询结果，如果查到就直接返回。不建议使用次缓存， 在 MySQL 8.0 版本已经将查询缓存删除，也就是说 MySQL 8.0 版本后不存在此功能。

  3. 分析器，分为词法分析和语法分析。此阶段只是做一些 SQL 解析，语法校验。所以一般语法错 误在此阶段。

  4. 优化器，是在表里有多个索引的时候，决定使用哪个索引；或者一个语句中存在多表关联的时 候（join），决定各个表的连接顺序。

  5. 执行器，通过分析器让 SQL 知道你要干啥，通过优化器知道该怎么做，于是开始执行语句。执 行语句的时候还要判断是否具备此权限，没有权限就直接返回提示没有权限的错误；有权限则 打开表，根据表的引擎定义，去使用这个引擎提供的接口，获取这个表的第一行，判断 id 是都

等于 1。如果是，直接返回；如果不是继续调用引擎接口去下一行，重复相同的判断，直到取到这个表的最后一行，最后返回。

47. 索引有什么优缺点？ ​

索引有什么优缺点？

48. MySQL 中 varchar 与 char 的区别？ varchar(30) 中的 30 代表的涵义？ ​

varchar 与 char 的区别，char 是一种固定长度的类型， varchar 则是一种可变长度的类型。

varchar(30) 中 30 的涵义最多存放 30 个字符。 varchar(30) 和 (130) 存储 hello 所占空间一样，但后者在排序时会消耗更多内存，因为 ORDER BY col 采用 fixed_length 计算 col 长度 （ memory 引擎也一样）。

对效率要求高用 char，对空间使用要求高用 varchar。

49. int(11) 中的 11 代表什么涵义？ ​

int(11) 中的 11，不影响字段存储的范围，只影响展示效果。

50. 为什么 SELECT COUNT(*) FROM table 在 InnoDB 比 MyISAM 慢？ ​

对于 SELECT COUNT(*) FROM table 语句，在没有 WHERE 条件的情况下，InnoDB 比 MyISAM 可

能会慢很多，尤其在大表的情况下。因为，InnoDB 是去实时统计结果，会全表扫描；而 MyISAM 内部维持了一个计数器，预存了结果，所以直接返回即可。

51. MySQL 索引类型有哪些？ ​

主键索引

索引列中的值必须是唯一的，不允许有空值。

普通索引

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。

唯一索引

索引列中的值必须是唯一的，但是允许为空值。

全文索引

只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。

空间索引

MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。 MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。

前缀索引

在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

其他（按照索引列数量分类）

  1. 单列索引

  2. 组合索引

组合索引的使用，需要遵循最左前缀匹配原则（最左匹配原则）。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。

52. 什么时候不要使用索引？ ​

  1. 经常增删改的列不要建立索引；

  2. 有大量重复的列不建立索引；

  3. 表记录太少不要建立索引。

53. 说说什么是 MVCC？ ​

多版本并发控制（MVCC=Multi-Version Concurrency Control），是一种用来解决读 - 写冲突的无锁并发控制。也就是为事务分配单向增长的时间戳，为每个修改保存一个版本。版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照（复制了一份数据）。这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读。

54. MVCC 可以为数据库解决什么问题？ ​

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能。同时还可以解决脏读、幻读、不可重复读等事务隔离问题，但不能解决更新丢失问题。

55. 说说 MVCC 的实现原理 ​

MVCC 的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中的 3 个隐式字段、undo 日志、 Read View 来实现的。

56. 请说说 MySQL 数据库的锁？ ​

关于 MySQL 的锁机制，可能会问很多问题，不过这也得看面试官在这方面的知识储备。

MySQL 中有共享锁和排它锁，也就是读锁和写锁。

  1. 共享锁：不堵塞，多个用户可以同一时刻读取同一个资源，相互之间没有影响。

  2. 排它锁：一个写操作阻塞其他的读锁和写锁，这样可以只允许一个用户进行写入，防止其他用 户读取正在写入的资源。

  3. 表锁：系统开销最小，会锁定整张表，MyISAM 使用表锁。

  4. 行锁：容易出现死锁，发生冲突概率低，并发高，InnoDB 支持行锁（必须有索引才能实现， 否则会自动锁全表，那么就不是行锁了）。

57. 说说什么是锁升级？ ​

MySQL 行锁只能加在索引上，如果操作不走索引，就会升级为表锁。因为 InnoDB 的行锁是加在索引上的，如果不走索引，自然就没法使用行锁了，原因是 InnoDB 是将 primary key index 和相关的行数据共同放在 B+ 树的叶节点。 InnoDB 一定会有一个 primary key，secondary

index 查找的时候，也是通过找到对应的 primary，再找对应的数据行。

当非唯一索引上记录数超过一定数量时，行锁也会升级为表锁。测试发现当非唯一索引相同的内容不少于整个表记录的二分之一时会升级为表锁。因为当非唯一索引相同的内容达到整个记录的二分之一时，索引需要的性能比全文检索还要大，查询语句优化时会选择不走索引，造成索引失效，行锁自然就会升级为表锁。

58. 说说悲观锁和乐观锁 ​

悲观锁

说的是数据库被外界（包括本系统当前的其他事物以及来自外部系统的事务处理）修改保持着保守态度，因此在整个数据修改过程中，将数据处于锁状态。悲观的实现往往是依靠数据库提供的锁机制，也只有数据库层面提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统汇总实现了加锁机制，也是没有办法保证系统不会修改数据。

在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。

乐观锁

相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。

而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（Version）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

59. 怎样尽量避免死锁的出现？ ​

  1. 设置获取锁的超时时间，至少能保证最差情况下，可以退出程序，不至于一直等待导致死锁； 2. 设置按照同一顺序访问资源，类似于串行执行；

  2. 避免事务中的用户交叉；

  3. 保持事务简短并在一个批处理中；

  4. 使用低隔离级别；

  5. 使用绑定链接。

60. 使用 MySQL 的索引应该注意些什么？ ​

使用 MySQL 的索引应该注意些什么？

61. CHAR 和 VARCHAR 的区别？ ​

CHAR 和VARCHAR 类型在存储和检索方面有所不同

CHAR 列长度固定为创建表时声明的长度，长度值范围是1 到255当 CHAR 值被存储时，它们被用空格填充到特定长度，检索CHAR 值时需删除尾随空格。

62. 主键和候选键有什么区别？ ​

表格的每一行都由主键唯一标识,一个表只有一个主键。主键也是候选键。按照惯例，候选键可以被指定为主键，并且可以用于任何外键引用。

63. 主键与索引有什么区别？ ​

主键一定会创建一个唯一索引，但是有唯一索引的列不一定是主键；

主键不允许为空值，唯一索引列允许空值；

一个表只能有一个主键，但是可以有多个唯一索引；

主键可以被其他表引用为外键，唯一索引列不可以；

主键是一种约束，而唯一索引是一种索引，是表的冗余数据结构，两者有本

64. MySQL 如何做到高可用方案？ ​

MySQL 高可用，意味着不能一台 MySQL 出了问题，就不能访问了。

  1. MySQL 高可用：分库分表，通过 MyCat 连接多个 MySQL

  2. MyCat 也得高可用：Haproxy，连接多个 MyCat

  3. Haproxy 也得高可用：通过 keepalived 辅助 Haproxy

附：一句话速记（可以跳过 ★☆☆） ​

  • 全局锁/表锁：备份、DDL 场景用，日常业务基本不碰，知道有这三层锁就行。
  • 分区表：物理上把一张表按时间/范围切分，能提高大表维护效率，但和分表不是一回事。

Copyright © 2024-present xiaocao