存储引擎

表存储

MyISAM 表格将在哪里存储，并且还提供其存储格式

每个 MyISAM 表格以三种格式存储在磁盘上：

"frm" 文件存储表定义
数据文件具有 ".MYD"（MYData）扩展名
索引文件具有 ".MYI"（MYIndex）扩展名

INNODB

特性

InnoDB 引擎的 4 大特性：

插入缓冲（insert buffer）
二次写（double write）
自适应哈希索引（ahi）
预读（read ahead）

锁算法

InnoDB 存储引擎的锁的算法有哪三种？

Record lock：单个行记录上的锁
Gap lock：间隙锁，锁定一个范围，不包括记录本身
Next-key lock：record + gap 锁定一个范围，包含记录本身

MyISAM vs InnoDB

简述在 MySQL 数据库中 MyISAM 和 InnoDB 的区别

MyISAM

不支持事务，但是每次查询都是原子的
支持表级锁，即每次操作是对整个表加锁
存储表的总行数
一个 MYISAM 表有三个文件：索引文件、表结构文件、数据文件
采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性

InnoDb

支持 ACID 的事务，支持事务的四种隔离级别
支持行级锁及外键约束：因此可以支持写并发
不存储总行数：
一个 InnoDb 引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为 2G），受操作系统文件大小的限制；
主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持 B+ 树结构，文件的大调整

区别

InnoDB 索引是聚簇索引，MyISAM 索引是非聚簇索引
InnoDB 的主键索引的叶子节点存储着行数据，因此主键索引非常高效
MyISAM 索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据
InnoDB 非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效
MyISAM 索引和数据分开存储的，索引占用空间小，因此适合大数据量的排序、全部扫描、count(不能带 where) 之类的操作

其他

myisamchk 是用来做什么的？

它用来压缩 MyISAM 表，这减少了磁盘或内存使用

MyISAM Static 和 MyISAM Dynamic 有什么区别？

在 MyISAM Static 上的所有字段有固定宽度，动态 MyISAM 表将具有像 TEXT，BLOB 等字段，以适应不同长度的数据类型

MyISAM Static 在受损情况下更容易恢复

集群

主从复制

作用

主数据库出现问题，可以切换到从数据库
可以进行数据库层面的读写分离
可以在从数据库上进行日常备份

目的

MySQL主从复制解决的问题

数据分布：随意开始或停止复制，并在不同地理位置分布数据备份
负载均衡：降低单个服务器的压力
高可用和故障切换：帮助应用程序避免单点失败
升级测试：可以用更高版本的MySQL作为从库

工作原理

在主库上把数据更改记录到二进制日志
从库将主库的日志复制到自己的中继日志
从库读取中继日志的事件，将其重放到从库数据中

双主热备

架构

MySQL架构

如详细介绍下如下几点

MySQL 整体架构
MySQL 查询语句执行流程
MySQL 更新语句执行流程，包括 redo log、bin log 两阶段提交
待补充，如主从

详细可参考 MySQL 架构原理(详解)open in new window

执行流程

Select

MySQL 查询语句执行流程

参考 MySQL 一条查询 SQL 语句的完整执行流程open in new window

Update

MySQL 更新语句执行流程

见一条 Update 语句的执行过程是怎样的？open in new window

Buffer Pool

参考详解 MySQL 中的 Buffer Pool，深入底层带你搞懂它open in new window

管理 Page 页

INNODB 如何管理 Page 页

可以详细问 AI，以下回答的都比较简单
见 InnoDB 引擎如何管理 Page 页open in new window

MVCC 多版本并发控制

参考 MVCC 多版本并发控制open in new window

核心概念

MVCC (Multi-Version Concurrency Control) 是 InnoDB 实现事务隔离的机制，通过数据多版本实现：

读不加锁：非阻塞读
写加锁：保证数据一致性

核心实现

1. 隐藏字段

每行记录包含：

DB_TRX_ID：最近修改该行的事务ID
DB_ROLL_PTR：指向Undo Log的回滚指针
DB_ROW_ID：隐含自增ID（无主键时生成）

2. Undo Log

存储数据的历史版本
构成版本链（通过DB_ROLL_PTR串联）

3. ReadView

事务执行快照读时生成，包含：

trx_ids：当前活跃事务ID集合
low_limit_id：当前最大事务ID+1
up_limit_id：活跃事务最小ID
creator_trx_id：创建该ReadView的事务ID

可见性判断规则

事务访问记录时，按版本链顺序检查：

DB_TRX_ID == creator_trx_id → 当前事务修改，可见
DB_TRX_ID < up_limit_id → 事务已提交，可见
DB_TRX_ID >= low_limit_id → 未来事务修改，不可见
检查trx_ids集合：

存在 → 事务未提交，不可见
不存在 → 事务已提交，可见

隔离级别差异

隔离级别	MVCC应用场景
READ COMMITTED	每次读生成新ReadView
REPEATABLE READ	第一次读生成ReadView复用

面试回答要点

核心组件：版本链 + ReadView + Undo Log
核心优势：读不阻塞写，写不阻塞读
典型问题：RC级别不可重复读、RR级别幻读（需间隙锁配合）
实现代价：需要维护版本链，增加Undo Log空间

MySQL 主从同步原理

参考全解 MySQL 之主从篇open in new window

什么是索引下推

参考五分钟搞懂 MySQL 索引下推open in new window

MySQL 自适应 Hash 索引

参考一文带你了解 MySQL 之 Adaptive Hash Indexopen in new window

事务有哪些特性，靠什么保证

参考事务的四大特性，以及实现原理open in new window

谈谈 Innodb 的三大特性

参考 InnoDB 三大特性open in new window

自适应哈希
两次写
插入缓冲

行锁的具体实现及锁定范围是哪些

只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁

参考 MySQL 数据库——锁-行级锁open in new window

MySQL 查看数据锁

参考 mysql8.0 查看锁信息open in new window

MySQL 排序原理

参考排序原理open in new window

主要理解下全字段排序和 rowid 排序

全字段排序
- 流程为主键 ID 索引->找主键索引数据->加载全量数据到 sort buffer->sort buffer 排序->返回结果集
- sort buffer 容量不足，会使用临时文件辅助排序
- 查看执行明细信息
rowid 排序
- 多了次回表
- 排序过程占用更小的内存
- 阈值是根据配置 show variables like '%max_length_for_sort_data%' 决定，大于这个就会使用 rowid 排序

查看执行明细信息步骤如下，注意 filesort_summary 与 SQL 有关系，详细指令不在这里深入了

-- 打开optimizer_trace，只对本线程有效
SET optimizer_trace='enabled=on'; 

-- 执行语句
select * from employees.dept_emp e ORDER BY to_date  LIMIT 1000;

-- 查看 OPTIMIZER_TRACE 输出，里面有不同的属性描述相应信息
select * FROM information_schema.OPTIMIZER_TRACE;


SET optimizer_trace='enabled=off';

MySQL 行记录存储格式是怎样的

溢出页是普调的 InnoDB 数据页结构，溢出页通过前一个和下一个溢出页指针形成链表

参考 MySQL 一行记录是怎么存储的？open in new window
可以查看视频会详细很多 MySQL 一行记录是怎么存储的？open in new window

MySQL 的 NULL 值是怎么存放的？
MySQL 是怎么知道 varchar(n) 实际占用的大小？
varchar(n) 中的 n 最大取值为多少？
行溢出后，MySQL 是怎么处理的？

场景题 1

列设置为 AUTO INCREMENT 时，如果在表中达到最大值，会发生什么情况？

它会停止递增，任何进一步的插入都将产生错误，因为密钥已被使用

MySQL 数据库作发布系统的存储，一天五万条以上的增量，预计运维三年，怎么优化？

设计良好的数据库结构，允许部分数据冗余，尽量避免 join 查询，提高效率
选择合适的表字段数据类型和存储引擎，适当的添加索引
MySQL 库主从读写分离
找规律分表，减少单表中的数据量提高查询速度
添加缓存机制，比如 memcached、redis 等
不经常改动的页面，生成静态页面
书写高效率的 SQL。比如 SELECT * FROM TABEL改为SELECT field_1, field_2, field_3 FROM TABLE

你可以用什么来确保表格里的字段只接受特定范围里的值？

Check 限制，它在数据库表格里被定义，用来限制输入该列的值
触发器也可以被用来限制数据库表格里的字段能够接受的值，但是这种办法要求触发器在表格里被定义，这可能会在某些情况下影响到性能

大表数据查询，怎么优化？

优化 shema、SQL 语句 + 索引
第二加缓存，memcached, redis
主从复制，读写分离
垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统
水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的 sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，SQL 中尽量带 sharding key，将数据定位到限定的表上去查，而不是扫描全部的表

超大分页怎么处理？

超大的分页一般从两个方向上来解决:

数据库层面：这也是我们主要集中关注的(虽然收效没那么大),类似于 select * from table where age > 20 limit 1000000,10 这种查询其实也是有可以优化的余地的. 这条语句需要 load 1000000 数据然后基本上全部丢弃,只取 10 条当然比较慢. 当时我们可以修改为 select * from table where id in (select id from table where age > 20 limit 1000000,10)。这样虽然也 load 了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快. 同时如果 ID 连续的话,我们还可以select * from table where id > 1000000 limit 10，效率也是不错的，优化的可能性有许多种,但是核心思想都一样,就是减少 load 的数据
需求的角度：减少这种需求。主要是不做类似的需求(直接跳转到几百万页之后的具体某一页。只允许逐页查看或者按照给定的路线走，这样可预测，可缓存)以及防止 ID 泄漏且连续被人恶意攻击

字段为什么要求定义为 not null？

null 值会占用更多的字节，且会在程序中造成很多与预期不符的情况

如果要存储用户的密码散列，应该使用什么字段进行存储？

密码散列，盐，用户身份证号等固定长度的字符串应该使用 char 而不是 varchar 来存储，这样可以节省空间且提高检索效率

数据库结构优化

一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果

需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容
将字段很多的表分解成多个表：对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢
增加中间表：对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询
增加冗余字段：设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差

MySQL 数据库 cpu 飙升到 500% 的话怎么处理？

当 cpu 飙升到 500% 时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。
如果是 mysqld 造成的，show processlist，看看里面跑的 session 情况，是不是有消耗资源的 SQL 在运行。找出消耗高的 SQL，看看执行计划是否准确，index 是否缺失，或者实在是数据量太大造成。
一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 SQL、改内存参数)之后，再重新跑这些 SQL。
也有可能是每个 SQL 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。

一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 MySQL 数据库，又插入了一条数据，此时 id 是几

如果数据库引擎用的是 innodb，那么 id 为 6，因为 innodb 表把自增主键的最大 id 记录到内存中，所以重启数据库或者对表 OPTIMIZE 操作，都会使最大 id 丢失。
如果数据库引擎用的是 myisam，那么 id 为 8，因为 myisam 表把自增主键记录到数据文件里面，所以重启数据库，自增主键的最大 id 也不会丢失

场景题 2

场景题相对灵活，可以使用 DeepSeek 等咨询或查看更详细的内容

Mysql 有个 User 表,里面 name 列有索引，千万条数据量，查询张三是 0.2s 查询李四要 2s，怎么分析下原因？

数据分布不均匀。分析：使用 EXPLAIN 命令查看查询计划，检查是否使用了索引以及扫描的行数
缓存影响。注意 MySQL8 移除了缓存
锁竞争。分析：使检查是否有其他事务正在访问相同的数据，导致锁竞争
磁盘 I/O，即数据分布在磁盘的不同位置，可能导致更多的磁盘 I/O 操作。分析：检查磁盘 I/O 情况，确认是否有大量的磁盘读取操作
统计信息不准确，可能导致优化器选择不合适的执行计划。分析：使用 ANALYZE TABLE 命令更新表的统计信息，然后再次测试查询速度
查询计划不同。分析：使用 EXPLAIN 命令查看两个查询的执行计划，确认是否存在差异
数据碎片，如果表中有大量的数据碎片，可能导致查询速度变慢。分析：使用 OPTIMIZE TABLE 命令优化表，减少数据碎片
硬件资源，即性能瓶颈
查询语句本身的性能问题

如何设置 MySQL 分布式架构主键 ID，为什么不能使用自增 ID 或者 UUID 做主键，雪花算法生成的主键存在哪些问题？

为什么不能使用自增 ID？
主要是分布式环境下存在问题如单点故障、拓展性差、性能瓶颈、数据迁移困难
为什么不能使用 UUID？
UUDI 存在问题如存储空间大、索引效率低、查询性能差、可读性差
雪花算法（Snowflake）

优点：分布式唯一性、时间有序、高性能、存储空间小

缺点：时钟回拨、机器 ID 分配问题、扩展性问题

其他分布式 ID 解决方案

数据库分段发号
Redis 生成 ID
Leaf 算法（美团-双 Buffer 方案）
UUID 变种（基于时间戳有序）

以下是一些文档，可参考理解方案明细：分布式 ID|源码解析美团 Leaf 双 Buffer 优化方案open in new window

如何优化慢 SQL？

使用 explain 解析执行计划，优化执行计划(关注关键指标：如 rows（扫描行数）、filter（过滤条件）、cost（预估成本）等)
优化查询语句，如减少查询字段、减少子查询、优化 where 条件、分页优化(使用索引)、使用窗口函数、避免使用 or、!= 查询，适当使用前缀索引、避免列上函数运算等
索引优化，如创建合适的索引、避免过多索引、使用复合索引、使用覆盖索引、利用索引扫描排序
表结构优化，如数据类型优化、分库分表、使用历史表
数据库配置优化，如调整内存参数、并发优化、日志优化，升级硬件
优化 Join 操作，如减少 Join 数量，小表驱动大表、使用 exists 替代 in(某些场景更高效)、避免 join 太多的表
批量操作优化
缓存优化，如查询缓存和应用层缓存
定期维护，如重建索引、删除历史数据
工具辅助，如慢查询日志、性能监控工具
分布式与读写分离
避免锁竞争

TIP

5.0 以下版本 or 不能使用索引，高版本也只有少数场景不会失效，如 or 条件无序也会失效
子查询是嵌套查询，嵌套查询会创建临时表，而临时表的创建和销毁需要时间，因此影响性能
利用索引扫描排序，是避免了回表查询，优化时间

最左前缀一定要最左列?

如果覆盖索引比全表扫描快，MySQL 会优先使用索引，而不是全表扫描，即使联合索引没有最左列

update 在什么情况下行锁会升级表锁?

如果覆盖索引比全表扫描快，MySQL 会优先使用索引，而不是全表扫描，即使联合索引没有最左列。例举以下内容，DeepSeek 回答了更多其他内容

where 条件没有索引
where 条件索引失效。如 like、使用函数、数据分布不均、不符合最左原则等

为什么阿里禁止使用外键?

主要有如下几点原因：

性能问题。会多额外的数据一致性校验查询
并发问题。外键约束会启用行级锁，主表写入时会进入阻塞。并发时可能会导致更新风暴
级联删除问题。多层级联删除会让数据变得不可控，触发器也严格被禁用
数据耦合和迁移问题。数据库层面数据关系产生耦合，数据迁移维护困难

为什么 MySQL 的隔离级别是 RR, Oracle 的是 RC，大厂要改 RR 为 RC？

参考 MySQL 默认隔离级别是 RR，为什么阿里等大厂会改成 RC？open in new window

not in 不仅会导致索引失效，还会导致什么？

如果有 null 值，还会导致数据丢失，因为 null 做任何操作都是未知

为什么说 BigDecimal 适合数值计算？

实际个人开发交易系统时，都是用的带精度的 double 类型处理，因为 BigDecimal 太慢了

不小心删库了，怎么办？

可以通过全量备份和 binlog 日志恢复数据

binlog 日志恢复数据

大规模数据查询是否会内存溢出

略，参考以下理解，要点需要了解执行流程。

MySQL 优化器是如何选择最优执行计划的

略，参考以下理解，要点是根据统一信息等会生成多个执行计划，从中选择最优的

优化器是如何选择最优执行计划的open in new window

为什么大厂的线上数据库禁止使用 delete 语句？

略，参考以下理解

MySQL 自增主键一定是连续吗？

不是，有多种情况，参考面试被问懵了：MySQL 自增主键一定是连续的吗？open in new window

千万级数据分页查询 5 毫秒？

略，即分页查询优化

MySQL 索引为何使用 B+ 树结构

略。核心就是范围查询，B 树和红黑树节点不在同一层，需要遍历父节点后找相邻节点，范围查询效率不高
思考：跳表也支持范围查询，Redis 是这么勇的

还不知道 SQL 的执行过程？

略，参考 sql 执行流程概述open in new window

MySQL 死锁场景还原以及跟踪处理？

SQL 脚本准备

CREATE TABLE `user_demo` (
  `id` int NOT NULL AUTO_INCREMENT,
  `no` varchar(50) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
);

truncate user_demo ; 
INSERT INTO mytest.user_demo (no, name,age) VALUES
	 ('1', 'zhangsna',18),
	 ('2', 'lisi',30),
	 ('3', 'wangwu',22),
	 ('10', 'zhaoliu',31);

死锁再现

窗口 1 执行如下 sql

-- 步骤 1 执行
begin;
-- 锁 2 更新 3
select * from mytest.user_demo where id = 2 for update;

-- 步骤 3 执行 
update mytest.user_demo set name = 'lisi_update' where id = 3;
commit

窗口 2 执行如下 sql

-- 步骤 2 执行
begin;
-- 锁 3 更新 2
select * from mytest.user_demo where id = 3 for update;

-- 步骤 4 执行
update mytest.user_demo set name = 'zhangsan_update' where id = 2;
commit;

到执行步骤 4 的时候，窗口 2 会报死锁 Deadlock found when trying to get lock; try restarting transaction。

死锁分析

我们查看锁状态，还可以看 thread_id，其对应 performance_schema.thread 表的 id。

select object_schema, object_name, index_name, lock_type, lock_mode, lock_data 
from performance_schema.data_locks;

会得到类似如下 crv 结果。

"object_schema","object_name","index_name","lock_type","lock_mode","lock_data"
mytest,user_demo,,TABLE,IX,
mytest,user_demo,PRIMARY,RECORD,"X,REC_NOT_GAP","2"
mytest,user_demo,PRIMARY,RECORD,"X,REC_NOT_GAP","3"

其中我们看到，对 3、2 加上了行锁。
我们可以使用 show engine innodb status SQL 查看死锁日志，根据 LATEST DETECTED DEADLOCK 检测到死锁 SQL。

高优先级

什么情况下设置了索引但无法使用

详细可参考MySQL 中索引失效的常见场景与规避方法open in new window。

Explain

参考文章理解深入解析 MySQL 的 EXPLAINopen in new window。

你怎么设计一个 MySQL

MySQL 面试核心知识点精简版
一、基础架构
三层架构：

连接层（连接池、认证）

服务层（解析器、优化器、执行器）

存储引擎层（InnoDB/MyISAM）

SQL执行流程：

连接→解析→优化→执行→返回

二、存储引擎（InnoDB核心）
核心特性：

事务支持（ACID）

行级锁

MVCC多版本并发控制

内存结构：

Buffer Pool（数据页缓存）

Change Buffer（非唯一索引更新优化）

Log Buffer（redo日志缓冲）

磁盘结构：

表空间（.ibd文件）

双写缓冲（防页断裂）

Redo Log（持久性保障）

三、索引原理
B+树特点：

非叶子节点只存键值

叶子节点链表连接

3-4层可存百万级数据

索引类型：

聚簇索引（主键组织数据）

二级索引（需回表查询）

索引优化：

最左前缀原则

覆盖索引

索引下推

四、事务机制
ACID实现：

原子性：Undo Log

持久性：Redo Log

隔离性：锁+MVCC

一致性：前三者共同保证

隔离级别：

读未提交 → 串行化（重点掌握RC和RR）

MVCC原理：

版本链（Undo Log）

ReadView（trx_ids列表）

五、锁机制
锁类型：

行锁（Record Lock）

间隙锁（Gap Lock）

临键锁（Next-Key Lock）

死锁处理：

等待超时

死锁检测（默认）

六、性能优化
EXPLAIN关键列：

type（访问类型）

key（使用索引）

rows（预估行数）

Extra（额外信息）

慢查询分析：

开启慢查询日志

pt-query-digest分析

详见 deepseek。
快速记忆口诀。"一架构二引擎，三索四事五锁六优"（架构→引擎→索引→事务→锁→优化）。

目录CONTENT

MySQL-2