InnoDB 和 MyISAM 引擎区别
事务、ACID
事务隔离级别、脏读、不可重复读、幻读
索引原理 B + 树、索引优缺点
主键索引、普通索引、联合索引、最左前缀
慢查询、explain 执行计划
备份 mysqldump
1、说说 InnoDB 和 MyISAM 核心区别?
事务:InnoDB 支持事务(ACID);MyISAM 不支持事务
锁:InnoDB 是行级锁(加锁粒度小,并发高);MyISAM 是表级锁,一锁锁整张表,并发差
外键:InnoDB 支持外键;MyISAM 不支持
崩溃恢复:InnoDB 有 redo log,宕机可以安全恢复;MyISAM 崩溃容易损坏数据
主键:InnoDB 必须有主键(没有会自动生成隐藏主键);MyISAM 无强制要求
场景:InnoDB 是 MySQL 默认引擎,业务读写、高并发首选;MyISAM 适合只读、静态数据(现在很少用)
一句话速记:InnoDB 支持事务、行锁、安全恢复;MyISAM 表锁、无事务,并发差。
2、 什么是事务?事务的 ACID 四个特性分别是什么?
事务:一组 SQL 语句,要么全部执行成功,要么全部失败回滚,不会出现中间状态。比如转账:扣钱、加钱两条 SQL 必须一起成功或一起失败。
ACID:
A 原子性(Atomicity):事务是不可分割的原子,全部成功或者全部回滚
C 一致性(Consistency):事务执行前后,数据整体合法、状态一致(比如转账前后总额不变)
I 隔离性(Isolation):多个事务并发执行,互相之间隔离,互不干扰
D 持久性(Durability):事务提交成功后,修改永久写入磁盘,宕机也不会丢失
一句话速记:原子不可分、数据一致、并发隔离、提交永久生效。
3、 MySQL 事务四个隔离级别,以及各自会产生什么问题?
四个隔离级别(从低到高):
读未提交(Read Uncommitted) 可以读到别的事务还没提交的数据。 问题:脏读、不可重复读、幻读。
读已提交(Read Committed,RC) 只能读到其他事务已经提交的数据。 解决:脏读;存在:不可重复读、幻读。 Oracle 默认级别。
可重复读(Repeatable Read,RR,MySQL InnoDB 默认) 同一个事务内,多次读取同一条数据,结果一致。 解决:脏读、不可重复读;InnoDB 通过 MVCC + 间隙锁,在 RR 下解决幻读(标准 SQL 定义里 RR 允许幻读)。
串行化(Serializable) 最高级别,事务串行执行,完全隔离。 解决:脏读、不可重复读、幻读;缺点:并发极差,性能很低,很少用。
三个现象释义:
脏读:读到别的事务未提交的数据,对方回滚,读到的数据就是脏的
不可重复读:同一个事务,两次读同一行,中间被别的事务修改提交,两次结果不一样
幻读:同一个事务,两次查询,中间别的事务插入 / 删除新行,行数变了
一句话速记: RU 啥都没解决;RC 解决脏读;MySQL RR 解决脏读 + 不可重复读 + 幻读;串行化全解决,并发最差。
4、什么是 MVCC?InnoDB MVCC 是怎么实现的?
MVCC:多版本并发控制 作用:实现 InnoDB 的可重复读(RR)隔离级别,不加锁实现读,提升并发性能,读和写不互相阻塞。
依靠三个核心实现:
隐藏列:每行数据自带 DB_TRX_ID(最近修改的事务 ID)、DB_ROLL_PTR(回滚指针,指向 undo log 里旧版本)、DB_ROW_ID(无主键时的隐藏主键)
undo log(回滚日志):保存数据修改前的旧版本,多个版本通过回滚指针串成版本链
read view(读视图):事务快照,记录当前活跃事务,用来判断这条版本能不能被当前事务看见
读取规则: 查询时,拿行的事务 ID 和 read view 对比,找到符合可见条件的版本返回,不是读最新行。
一句话速记:MVCC 靠隐藏列 + undo 版本链 + read view,快照读不加锁,实现 RR 隔离。
⚠️区分: 快照读(普通 select)走 MVCC;当前读(select ... for update、update、delete)走行锁,不走 MVCC。
5、 什么是快照读、当前读?举例子。
快照读(普通读) 不加锁,走 MVCC,读取数据快照版本,读写不阻塞。 语句:
select * from user;当前读(最新读) 加行锁,读取数据最新版本,会阻塞其他写操作。 语句:
select ... for update(排他锁)select ... lock in share mode(共享锁)update / delete / insert
一句话速记:普通 select 是快照读,MVCC 不加锁;带锁查询、增删改都是当前读,会加行锁。
补充:RR 隔离级别下,快照读不会看到别的事务新提交数据;当前读每次拿最新数据。
6、 什么是间隙锁?作用是什么?什么场景触发?
间隙锁(Gap Lock):InnoDB 在 RR 隔离级别下的锁,锁定索引记录之间的间隙,不是锁定某一行数据,锁定一段索引空隙。
作用:防止别的事务在这个间隙插入新数据,用来解决幻读。
触发条件:
隔离级别是 RR(可重复读)
是当前读(update、delete、select ... for update)
查询条件走索引,但是没有命中任何一条记录,锁住这个间隙; 或者范围查询,会锁住范围内所有间隙。
记录锁 + 间隙锁 = 临键锁(Next-Key Lock),InnoDB RR 默认的行锁算法。
⚠️坑点:间隙锁之间不冲突,多个事务可以同时加同一个间隙锁;它只阻止插入操作。RC 隔离级别没有间隙锁。
一句话速记:间隙锁锁索引空隙,RR 下防止幻读;RC 没有间隙锁。
7、 什么是索引?索引优缺点?
索引:是帮助数据库快速查找数据的有序数据结构,类似书的目录。InnoDB 默认用 B + 树。
✅优点:
大幅加快查询速度
排序、分组可以利用索引,减少文件排序
❌缺点:
占用额外磁盘空间
降低写入性能(insert/update/delete,需要同步维护索引树)
索引不是越多越好,索引太多写操作会很慢
一句话速记:索引加速查询,代价是占空间、拖慢增删改。
8、 InnoDB 主键索引、普通索引(二级索引)区别?什么是回表?
InnoDB 是聚簇索引:
主键索引(聚簇索引) 叶子节点存放整行完整数据,索引和数据放在一起;一张表只能有一个主键索引。
二级索引(普通索引) 叶子节点只存主键值,不存全部数据。
回表: 用二级索引查到主键之后,再拿着主键去主键索引树里面,查询完整行数据,这个过程就叫回表。
覆盖索引:查询字段全部在二级索引里,不用回表,性能更好。
一句话速记:主键索引叶子是整行数据;二级索引叶子是主键;查到主键再去主键索引拿数据就是回表。
9、什么是覆盖索引?好处是什么?
覆盖索引:查询需要的所有字段,都包含在二级索引里面,不需要回表去主键索引拿数据。
例子:
PLAINTEXT
-- 联合索引:idx_name_age(name,age)
select name,age from t where name='test';
索引里已经有 name 和 age,直接返回,不用回表。
✅好处:
避免回表,减少 IO,查询速度更快
二级索引本身更小,扫描更快
一句话速记:查询字段全部在索引里,不用回表,就是覆盖索引。
10、 联合索引最左前缀原则是什么?举例子。
最左前缀原则:联合索引是按顺序排序的,查询要从索引最左边的列开始匹配,才能走这个索引。 联合索引:idx(a,b,c),索引排序顺序:先按 a 排序,a 相同再按 b,b 相同再按 c。
✅能走索引: where a=1 where a=1 and b=2 where a=1 and b=2 and c=3
❌不能正常走索引: where b=2、where c=3、where b=2 and c=3(跳过最左边 a)
⚠️补充坑: 如果中间字段出现范围查询(> < between),范围后面的列会失效 例:where a=1 and b>2 and c=3,c 用不上索引。
一句话速记:联合索引从左往右匹配,不能跳过左边列;范围查询之后,后面索引失效。
11、 索引失效有哪些常见原因?
违背最左前缀:联合索引跳过左边字段
索引列做运算、函数、隐式类型转换 例:
where id+1=100、where date(create_time)='2026-11-19'、字符串字段用数字匹配like 以 % 开头:
like '%abc',前缀模糊不走 B + 树索引;like 'abc%'可以or 两边有一边没有索引
MySQL 优化器判断全表扫描更快:数据量占表很大比例,不走索引
范围查询(> < between)后面的联合索引字段失效
一句话速记:运算函数、左模糊 %、跳最左、or 无索引、类型不匹配,都容易索引失效。
12、 什么是回滚日志 undo log、重做日志 redo log,各自作用?
undo log(回滚日志) 记录修改前的数据版本。 作用:
事务回滚的时候,用 undo log 恢复成修改之前的数据;
配合 MVCC,保存数据旧版本,实现快照读。
redo log(重做日志) 记录修改之后的数据变化,是物理日志。 作用:
保证事务持久性;
刷盘机制:先写 redo log,再异步刷磁盘数据页(WAL 预写日志);
宕机恢复:重启后,把 redo log 里已经提交、还没刷到数据文件的变更重做,防止丢数据。
一句话速记:undo 存旧数据,用来回滚、MVCC;redo 存新变更,宕机恢复、保证持久。
13、 binlog 是什么?和 redo log 区别?
binlog(二进制日志):MySQL 服务层的逻辑日志,记录执行的 SQL 语句或者数据变更记录。 作用:主从复制、数据备份恢复。
和 redo log 对比:
层次:binlog 是 MySQL Server 层;redo log 是 InnoDB 引擎层
日志类型:binlog 是逻辑日志(记录 SQL / 行变更);redo log 是物理日志(记录磁盘页修改)
作用:binlog 用于主从同步、备份;redo log 用于崩溃恢复、保证事务持久
保存:binlog 可以归档,长期保留;redo log 是循环覆盖写,固定大小循环复用
多引擎:binlog 所有引擎都有;redo log 只有 InnoDB 才有
一句话速记:binlog 逻辑日志,做主从备份;redo log 物理日志,InnoDB 崩溃恢复。
14、 MySQL 主从复制原理是什么?
主从复制依靠 binlog,一共 3 个线程配合:
主库 dump 线程:主库开启 binlog,数据变更写入 binlog;从库连接主库后,dump 线程把 binlog 事件推送给从库。
从库 IO 线程:接收主库传过来的 binlog,写入本地 relay log(中继日志)。
从库 SQL 线程:读取 relay log,回放里面的 SQL,在从库执行,保持数据和主库一致。
流程一句话:主库写 binlog → IO 线程拉到从库存 relay log → SQL 线程重放。
✅用途:读写分离、数据备份、故障切换。 ⚠️坑点:默认是异步复制,主库写完 binlog 就返回给客户端,不等从库同步,极端情况主库宕机可能丢数据;还有半同步复制。
一句话速记:dump 推 binlog,IO 收存中继日志,SQL 线程回放。
15、读写分离是什么?有什么问题?
读写分离:写请求全部走主库,查询读请求分发到从库。利用主从复制,分担主库压力,提升查询吞吐量。
常见问题:
主从延迟:主库刚写入,binlog 还没同步到从库,立刻查从库读不到新数据(脏读)。 解决:强一致性查询直接查主库;业务上做延迟容忍。
主从同步异常:从库 SQL 线程报错,同步中断,数据不一致。
增加架构复杂度:程序中间件(MyCat、ShardingSphere)需要做路由。
扩容瓶颈:写压力依然全部压在主库,读写分离解决不了写瓶颈。
一句话速记:写主读从,分担查询压力;最大痛点是主从延迟,会出现读不到刚写入的数据。
16、 explain 执行计划有哪些核心字段?type 字段值从优到劣排序?
explain 核心字段: id、select_type、table、type、key、key_len、rows、Extra
type 访问类型,从优到劣顺序: system > const > eq_ref > ref > range > index > ALL
释义:
system:表只有一行,极少见到
const:主键 / 唯一索引等值匹配,最多一行
eq_ref:关联查询,主键 / 唯一索引一对一匹配
ref:普通索引等值匹配,可能多行
range:范围查询(> < between in)
index:扫整个索引树,不扫数据文件
ALL:全表扫描,最差,需要优化
一句话速记:最好 const、ref、range;尽量避免 ALL 全表扫描。
17、 SQL 慢查询怎么排查?
开启慢查询日志,设置阈值(比如超过 1 秒的 SQL 记录下来),拿到慢 SQL;
拿到慢 SQL,用
explain看执行计划,重点看 type、key、rows,判断有没有走索引、是不是全表扫描;检查索引:是否缺少索引、索引失效、联合索引没遵守最左前缀;
检查 SQL 写法:有没有 like 左 %、函数运算、隐式转换、大 in、多表笛卡尔积;
看表数据量、是否有锁等待、事务长时间未提交阻塞;
优化方案:加合适索引、改写 SQL、分表、读写分离。
一句话速记:慢日志抓 SQL → explain 看执行计划 → 检查索引与 SQL 写法 → 排查锁、事务。
18、 limit 大分页问题,比如 limit 100000,20 为什么慢?怎么优化?
原因:limit 100000,20 数据库不是直接拿第 100000 行之后 20 条,而是先扫描并丢弃前面 100000 条,再取 20 条,扫描行数多、IO 高,越往后分页越慢。
优化方案:
主键索引分页(推荐):先查到上一页最大主键,where 主键 > 上一页最大值 limit 20
PLAINTEXT
select * from t where id > 100000 limit 20;子查询先定位偏移:
PLAINTEXT
select * from t where id >= (select id from t order by id limit 100000,1) limit 20;业务层面:不提供深度分页,只允许上一页、下一页,不跳页。
一句话速记:大 offset 会扫描丢弃大量数据;优先用主键条件分页,避免直接大 offset。
19、 什么是锁?InnoDB 有哪些锁?
锁是用来解决并发事务争抢数据,防止数据错乱的机制。
InnoDB 锁分类:
按粒度
表锁:锁住整张表,MyISAM 常用,InnoDB 也有(lock tables),并发差
行锁:只锁命中的索引行,InnoDB 默认,并发高;又分:
记录锁:锁一条存在的索引记录
间隙锁:锁索引之间的空隙(RR 隔离级别)
临键锁(Next-Key Lock)= 记录锁 + 间隙锁,RR 默认算法
按兼容性
共享锁(S 锁 / 读锁):多个事务可以同时加 S 锁;S 和 X 互斥
select ... lock in share mode排他锁(X 锁 / 写锁):只能一个事务持有,其他事务读、写都阻塞
select ... for update / update / delete / insert
一句话速记:行锁粒度小并发高;RR 下临键锁由记录锁 + 间隙锁组成;S 读锁共享,X 写锁排他。
20、 什么是死锁?产生死锁四个条件?怎么避免死锁?
死锁:两个或者多个事务,互相持有对方需要的锁,同时又等待对方释放锁,循环等待,谁都无法继续执行。
死锁四个必要条件(同时满足才会产生)
互斥条件:资源是排他锁,一个事务拿到,别的事务拿不到
请求与保持:事务已经拿到一部分锁,再去申请新锁,不释放手上已有的锁
不可剥夺:锁不能被别的事务强行抢走,只能持有者主动释放
循环等待:形成闭环等待,A 等 B、B 等 A
避免死锁的办法:
统一加锁顺序:所有事务按固定顺序更新行,比如都先更新 id 小的行
尽量缩小事务范围,事务尽快提交,不要长时间持有锁
避免在事务里写复杂逻辑、长时间查询
控制隔离级别,RC 级别没有间隙锁,死锁概率更低
设置超时时间,超时自动回滚
一句话速记:互斥、持有再申请、不可抢占、循环等待;统一加锁顺序是最常用的规避手段。
