Java-MySQL与SQL优化

大类:数据库与中间件 · 共 36 题 · 检索页定位

选择题(17)

q052 · 简单

MySQL InnoDB 存储引擎的默认事务隔离级别是?
A. 读未提交(Read Uncommitted)
B. 读已提交(Read Committed)
C. 可重复读(Repeatable Read)
D. 串行化(Serializable)

参考答案要点

• InnoDB 默认隔离级别是可重复读(RR);Oracle/SQL Server 默认读已提交(RC)
• 读未提交存在脏读;读已提交解决脏读但存在不可重复读;可重复读解决不可重复读,InnoDB 通过 MVCC 与临键锁在很大程度上避免幻读;串行化最安全但性能最差
• RR 下事务首次快照读时生成 ReadView 并复用至事务结束;RC 每次读都生成新的 ReadView
• 可通过 SET SESSION TRANSACTION ISOLATION LEVEL 修改会话隔离级别

来源:[javabetter.cn](https://javabetter.cn/sidebar/sanfene/mysql.html)

q055 · 中等

表上建有联合索引 (a, b, c),下列查询中能够完整使用该索引(三列都参与索引定位)的是?
A. WHERE b = 2 AND c = 3
B. WHERE a = 1 AND b = 2 AND c = 3
C. WHERE b = 2
D. WHERE c = 3

参考答案要点

• 最左前缀原则:查询条件必须从联合索引最左列开始连续匹配,索引才能生效;B 完整匹配 a、b、c 三列
• A、C、D 缺少最左列 a,无法走该联合索引,只能全表扫描
• WHERE a=1 AND c=3 只能用 a 列,c 无法利用索引有序性(可通过索引下推在引擎层过滤减少回表)
• 范围查询(如 a>2 之后的列)会使后续列无法使用索引:a=1 AND b>2 AND c=3 只能用 a 和 b
• 优化器会自动重排等值条件的顺序,a=1 AND c=3 AND b=1 等价于按 a、b、c 匹配

来源:[javabetter.cn](https://javabetter.cn/sidebar/sanfene/mysql.html)

q547 · 中等

users 表中 name(varchar)、phone(varchar)与 age(int)各建有单列索引。下列查询中,【不会】造成对应索引失效的是?
A. WHERE age + 1 = 18
B. WHERE phone = 13800000000
C. WHERE name LIKE '张%'
D. WHERE name LIKE '%三'

参考答案要点

• 正确答案 C. 前缀确定的 LIKE '张%' 可以走 B+ 树的范围扫描(最左前缀仍然确定)
• A 错:索引列参与了算术运算,优化器需要对每行做计算,索引失效(应把运算移到常量侧:WHERE age = 17)
• B 错:varchar 列与数字比较触发隐式类型转换(等价于给列套 CAST),索引失效——这是经典的字符串列不加引号坑
• D 错:前导通配符 '%三' 无法利用索引有序性,只能全表/全索引扫描

来源:[javaguide.cn](https://javaguide.cn/database/mysql/mysql-index-invalidation.html)

q548 · 中等

关于 InnoDB 的聚簇索引与二级索引(辅助索引),下列说法正确的是?
A. 两类索引的叶子节点都保存完整行记录
B. 二级索引的叶子节点保存的是行记录的物理地址
C. 一张 InnoDB 表可以按业务需要创建多个聚簇索引
D. 二级索引的叶子节点保存索引列值与主键值;若查询所需列不全在该二级索引中,就需要回表

参考答案要点

• 正确答案 D. 二级索引叶子存『索引列+主键』,缺的列要拿主键回聚簇索引再查一遍(回表),这也是覆盖索引优化的动机
• A 错:只有聚簇索引的叶子保存整行
• B 错:存物理地址是 MyISAM 索引的做法
• C 错:聚簇索引每表只有一个,按主键组织(无主键则选第一个非空唯一索引,再没有就用隐藏的 row_id)

来源:[javaguide.cn](https://javaguide.cn/database/mysql/mysql-index.html)

q549 · 中等

InnoDB 表通常推荐使用自增整型主键。下列理由中【不成立】的是?
A. 自增主键保证新行顺序追加写入,减少页分裂与碎片
B. 主键越短,二级索引叶子节点里保存的主键副本就越小,二级索引整体更紧凑
C. 自增主键让插入总是发生在 B+ 树最右侧,写入位置可预期、性能稳定
D. 自增主键天然跨库全局唯一且不依赖数据库,最适合直接作为分布式全局 ID

参考答案要点

• 正确答案 D. 不成立——单机自增主键在分库分表后无法保证全局唯一(每张表各自计数),分布式 ID 需要雪花算法、号段模式、UUID 有序改造等独立方案
• A/B/C 都是自增主键的真实优势:顺序写少页分裂、短主键省二级索引空间、最右侧插入热点集中

来源:[javaguide.cn](https://javaguide.cn/database/mysql/mysql-index.html)

q550 · 中等

InnoDB 在 RR(可重复读)隔离级别下,下列属于快照读(基于 MVCC 版本数据、不加锁)的是?
A. SELECT * FROM t WHERE id = 1 FOR UPDATE
B. UPDATE t SET name = 'x' WHERE id = 1
C. SELECT * FROM t WHERE id = 1(普通 SELECT)
D. SELECT * FROM t WHERE id = 1 FOR SHARE(LOCK IN SHARE MODE)

参考答案要点

• 正确答案 C. 不加锁的普通 SELECT 是快照读,RR 下基于事务开始后的 ReadView 读取版本链数据
• A 错:FOR UPDATE 是当前读(排他锁定读),读取最新已提交版本并加排他锁
• D 错:FOR SHARE/LOCK IN SHARE MODE 也是当前读(共享锁定读)
• B 错:UPDATE/DELETE/INSERT 都是当前读,必须基于最新版本修改并加锁

来源:[javaguide.cn](https://javaguide.cn/database/mysql/mysql-questions-01.html)

q551 · 困难

关于 InnoDB 在 RR(可重复读)级别下与幻读的关系,下列说法正确的是?
A. RR 已经完全消除幻读,任何执行序列都不会出现幻影行
B. 幻读就是指一个事务读到了另一个未提交事务修改的数据
C. 快照读靠 MVCC 一致性视图避免幻读,当前读靠临键锁(Next-Key Lock)锁住记录与间隙避免幻读;但同一事务先快照读、后当前读时,仍可能读到快照中不存在的行
D. 只有 RC(读已提交)级别才有幻读问题,RR 与串行化级别都不存在幻读

参考答案要点

• 正确答案 C. InnoDB 的 RR 对幻读的防护是分手段的:MVCC 防快照读幻读、临键锁防当前读幻读,两种读混用时防护不闭合
• A 错:『完全消除』过于绝对,先快照读后 SELECT ... FOR UPDATE 的场景仍会出现新行
• B 错:把幻读与脏读混淆了(幻读是前后两次读的记录数不一致)
• D 错:RR 只是极大缓解幻读,串行化才是彻底杜绝

来源:[javaguide.cn](https://javaguide.cn/database/mysql/mysql-questions-01.html)

q552 · 困难

关于 InnoDB Buffer Pool 的 LRU 链表管理,下列说法正确的是?
A. 采用朴素 LRU,每次新读取的页都直接放到链表头部
B. 链表分为 young 与 old 两区:新加载的页先进 old 区,只有在该页于 old 区停留超过约 1 秒后再次被访问,才被移入 young 区,从而避免全表扫描等一次性访问冲刷热点页
C. 划分 old 区的主要目的是减少缓冲池的内存总占用
D. 预读加载的页与普通读取的页在 LRU 中的处理方式完全相同

参考答案要点

• 正确答案 B. young/old 分区 + 停留时间阈值(innodb_old_blocks_time 默认 1000ms)解决『全表扫描把热点页挤出』的缓存污染,也顺带缓解预读失效
• A 错:朴素 LRU 正是被一次性访问污染的方案,InnoDB 特意不用
• C 错:分区不改变内存占用总量,解决的是命中率问题
• D 错:线性预读的页同样先进 old 区观察,不会直接进 young 区

来源:[dev.mysql.com](https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool.html)

q553 · 中等

关于 InnoDB 的 undo log(回滚日志),下列说法正确的是?
A. undo log 的核心作用是崩溃恢复时重放已提交事务的修改
B. undo log 与 binlog 都是 MySQL Server 层的日志,作用可以互相替代
C. undo log 记录数据的逻辑反向操作(insert 记 delete、update 记旧值),用于事务回滚与 MVCC 多版本读(版本链),与崩溃恢复用的 redo log 分工不同
D. undo log 无需保证持久性,宕机丢失也不会有任何影响

参考答案要点

• 正确答案 C. undo 的两大职责:回滚 + MVCC 版本链,与 redo(前滚恢复)互补
• A 错:重放已提交事务是 redo log 的职责,undo 用于撤销未提交事务
• B 错:binlog 是 Server 层日志(复制/时间点恢复),undo/redo 是 InnoDB 引擎层日志,层次与用途都不同
• D 错:undo 本身也要保证持久(靠 redo 保护),丢了回滚与 MVCC 都无法工作

来源:[javaguide.cn](https://javaguide.cn/database/mysql/mysql-questions-01.html)

q554 · 简单

关于 MySQL 字段类型选择,下列说法正确的是?
A. CHAR 是定长字符串,适合长度基本固定的列(如手机号、MD5);VARCHAR 是变长类型,按实际长度存储并额外占用 1~2 字节长度信息
B. VARCHAR(100) 与 VARCHAR(10) 存同样的内容,磁盘存储与所有场景的开销完全一样
C. timestamp 与 datetime 的可表示范围都是 1000-01-01 至 9999-12-31
D. datetime 存储时会自动进行时区换算,而 timestamp 不会

参考答案要点

• 正确答案 A. 定长取 CHAR(不足补空格)、变长取 VARCHAR 是基本选型原则
• B 错:行内虽按实际长度存,但内存临时表、排序缓冲等按定义长度 N 分配,过大的 N 有隐性代价
• C 错:timestamp 是 4 字节 UTC 秒值,范围 1970~2038 年(2038 问题),datetime 才是 1000~9999
• D 错:恰好说反——timestamp 随 time_zone 自动换算,datetime 存的是原样字面时间

来源:[javaguide.cn](https://javaguide.cn/database/mysql/mysql-questions-01.html)

q555 · 简单

关于 DROP、TRUNCATE、DELETE 三者的区别,下列说法正确的是?
A. DELETE 不能带 WHERE 条件,只能一次清空全表
B. TRUNCATE 属于 DDL:快速清空全表数据、重置 AUTO_INCREMENT 计数,不能带 WHERE,通常无法在事务中回滚,也不逐行触发触发器
C. TRUNCATE 会逐行删除数据并逐行触发 DELETE 触发器
D. DROP 只删除表中数据,表结构仍然保留

参考答案要点

• 正确答案 B. TRUNCATE 本质是删表重建(或等价 DDL),快、重置自增、不触发行级触发器、不可回滚
• A 错:DELETE 是 DML,支持 WHERE 精确删除,且在事务中可以回滚
• C 错:逐行删除并触发触发器的是 DELETE
• D 错:DROP 连表结构(定义)一起删除,TRUNCATE 才是只清数据保留结构

来源:[dev.mysql.com](https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html)

q573 · 中等

假设 InnoDB 主键为 bigint,非叶子页每条索引记录(主键值+页指针)约 14 字节,叶子页每行数据约 1KB,页大小 16KB。一棵共 3 层(2 层非叶子+1 层叶子)的 B+ 树大约能存放多少行数据?
A. 约 2 万行
B. 约 200 万行
C. 约 2000 万行
D. 约 2 亿行

参考答案要点

• 页是 InnoDB 管理存储空间的基本单位,默认 16KB,索引树的一个节点就是一个页
• 非叶子页可容纳约 16KB/14B≈1170 条索引记录,即扇出约 1170
• 叶子页按 1KB/行计算约存 16 行
• 总容量≈1170×1170×16≈2190 万行,故选 C
• 三层 B+ 树即可支撑约两千万行,一次主键查询最多 3 次页 IO;这也是建议主键自增、顺序插入的原因

来源:[javabetter.cn](https://javabetter.cn/sidebar/sanfene/mysql.html)

q576 · 中等

EXPLAIN 输出中 type 字段按性能从差到好的正确排序是?
A. ALL < index < range < ref < eq_ref < const
B. index < ALL < range < ref < eq_ref < const
C. ALL < range < index < ref < eq_ref < const
D. range < ALL < index < ref < const < eq_ref

参考答案要点

• 完整排序:system > const > eq_ref > ref > range > index > ALL
• ALL 全表扫描;index 全索引扫描,免回表但开销仍大
• range 索引范围扫描(<、>、between、in);ref 非唯一索引等值匹配
• eq_ref 多表 join 中被驱动表走主键或唯一索引;const 主键/唯一索引与常量等值比较
• SQL 优化一般至少达到 range,出现 ALL 通常要优化索引或改写 SQL

来源:[xiaolincoding.com](https://www.xiaolincoding.com/interview/mysql.html)

q579 · 中等

关于 Read Committed(RC) 与 Repeatable Read(RR) 下 ReadView 的生成时机,下列说法正确的是?
A. 两者都在事务开始时生成一次并全程复用
B. RC 在每条快照读语句执行前重新生成,RR 在事务中第一次快照读时生成并全程复用
C. RC 在事务提交时生成,RR 在事务开始时生成
D. 两者都随每条查询重新生成

参考答案要点

• RC:每条 SELECT 都新建 ReadView,能看到其他事务最新已提交数据,导致不可重复读
• RR:仅第一次快照读时创建 ReadView 并全程复用,实现可重复读
• begin/start transaction 并不立即创建 ReadView,第一条快照读语句才创建
• 这一生成时机差异是两种隔离级别可见性不同的根源
• RR 下 MVCC 解决不可重复读,临键锁解决当前读下的幻读,两者配合

来源:[xiaolincoding.com](https://www.xiaolincoding.com/interview/mysql.html)

q584 · 中等

关于 MySQL binlog 的三种格式,下列说法正确的是?
A. STATEMENT 记录每行数据的变更,主从复制最安全
B. ROW 记录 SQL 语句原文,日志体积最小
C. MIXED 由 MySQL 在 STATEMENT 与 ROW 间自动切换,MySQL 5.7 之后默认格式为 ROW
D. 使用 ROW 格式后 binlog 无法用于数据恢复

参考答案要点

• STATEMENT 记录逻辑 SQL 原文,体积小,但 now()/uuid()/limit 等不确定函数可能导致主从不一致
• ROW 记录行的变更前后镜像,复制最安全,但日志量大
• MIXED 默认用 STATEMENT,检测到不安全语句时自动切换为 ROW
• MySQL 5.7.7 起以及 8.0 默认 ROW 格式
• binlog 在两阶段提交中充当协调者,崩溃恢复时用于判断 prepare 事务是否应提交

来源:[interview.javaguide.cn](https://interview.javaguide.cn/database/mysql.html)

q588 · 中等

关于 InnoDB 中的 COUNT 用法,下列说法正确的是?
A. COUNT(字段) 会统计该列为 NULL 的行
B. COUNT() 会忽略 NULL 行,结果小于 COUNT(1)
C. COUNT(1) 与 COUNT() 语义和性能等价,都统计所有行且包含 NULL
D. COUNT(主键) 需要解析主键值,总是最慢的

参考答案要点

• COUNT(*) 与 COUNT(1) 都统计总行数且包含 NULL,优化器会选取最小的可用索引树遍历,性能等价
• COUNT(字段) 只统计该列不为 NULL 的行
• COUNT(主键) 与 COUNT(*) 性能接近,都会选择代价最小的索引
• InnoDB 不像 MyISAM 那样缓存总行数,MyISAM 无 WHERE 条件的 COUNT(*) 是 O(1)
• 大表实时计数优化方向:缓存计数器、近似值(explain 估算)、独立计数表、binlog 异步累加

来源:[javabetter.cn](https://javabetter.cn/sidebar/sanfene/mysql.html)

q589 · 中等

MySQL 8.0 中对千万级大表执行 ALTER TABLE ... ADD COLUMN,想要秒级完成、不拷贝数据,应使用的 DDL 算法是?
A. COPY
B. INPLACE
C. INSTANT
D. GH-OST

参考答案要点

• COPY:重建整表拷贝数据,允许并发读、阻塞写,代价最大
• INPLACE:在原表上重建,多数情况不阻塞 DML,但仍消耗 IO 与时间,常用于加索引
• INSTANT:8.0.12 起支持加列等操作,仅修改元数据,秒级完成,不拷贝数据
• INSTANT 仅限部分操作(加列、设置默认值等),加索引不支持
• 第三方方案 gh-ost/pt-osc:建影子表,通过 binlog 或触发器增量同步,最后原子 rename,支持限速与暂停,适合超大表在线变更

来源:[javabetter.cn](https://javabetter.cn/sidebar/sanfene/mysql.html)

简答题(16)

q026 · 简单

一条接口响应变慢,定位到是 SQL 问题。说说你优化慢 SQL 的完整流程:从发现、分析到验证。

参考答案要点

• 发现:慢查询日志(long_query_time)/监控APM定位具体SQL
• 分析:EXPLAIN看type(至少range)/key/rows/Extra(Using filesort·temporary危险信号)
• 常见优化:加/改索引(联合索引最左前缀)、避免函数操作索引列、覆盖索引消回表、深分页改游标
• 结构优化:大字段拆分、反范式冗余、读写分离、分库分表(最后手段)
• 验证:优化前后EXPLAIN对比+压测,并确认没有劣化其他查询
• 有完整方法论和验证闭环,不是背索引口诀

来源:seed

q027 · 中等

索引失效的常见场景有哪些?EXPLAIN 输出里你最关注哪几列,为什么?

参考答案要点

• 失效场景:最左前缀缺列、索引列上函数/运算/隐式类型转换(字符串列传数字)、like '%xx'前置通配、or连接非索引列、优化器判断全扫更快(回表成本)
• 关注列:type(访问类型,ALL→index→range→ref→const)、key/possible_keys(实际用没用上)、rows(扫描行数量级)、Extra(Using index覆盖索引好;Using filesort/Using temporary要警惕)
• 隐式转换原理:列上有函数等价于值被改写,优化器放弃索引,能讲原理不是背场景
• 索引不是越多越好:写放大、优化器选择成本,能表达trade-off

来源:seed

q028 · 中等

什么情况下 MySQL 会发生锁等待甚至死锁?InnoDB 的行锁到底锁的是什么?说说你怎么排查和预防线上死锁。

参考答案要点

• 行锁实质锁的是索引记录,无索引可用→锁升级为全表记录(后果严重),这是核心原理
• 锁等待:事务持锁时间长(大事务)、间隙锁(Gap Lock)在RR级别扩大锁定范围
• 死锁典型:两事务以不同顺序更新相同两行;唯一索引插入与间隙锁冲突
• 排查:SHOW ENGINE INNODB STATUS看LATEST DETECTED DEADLOCK,锁等待超时innodb_lock_wait_timeout
• 预防:统一加锁顺序、小事务、索引避免锁扩大、必要时降RC(去间隙锁)、重试兜底
• 能讲间隙锁与RR的关系是区分度高的信号

来源:seed

q053 · 中等

InnoDB 为什么选择 B+ 树作为索引结构,而不是 B 树、哈希表或二叉树?

参考答案要点

• 对比二叉树/红黑树:B+ 树是多路平衡树,更矮胖,3-4 层即可支撑千万级数据,大幅减少磁盘 IO 次数(InnoDB 页默认 16KB,单页可存约 1200 个键)
• 对比 B 树:B+ 树非叶子节点只存键不存数据,单页能容纳更多键,树更矮;数据全部在叶子节点,查询路径长度固定,性能稳定
• B+ 树叶子节点通过双向链表相连,范围查询与排序只需定位起点后顺序扫描;B 树范围查询需要中序遍历回溯,效率低
• 对比哈希索引:哈希只支持等值查询,不支持范围查询和排序;Memory 引擎支持哈希,InnoDB 有自适应哈希索引(AHI)但不可人为干预
• 量化:bigint 主键 + 16KB 页,3 层 B+ 树约可存 1170117016 ≈ 2000 万条记录

来源:[javabetter.cn](https://javabetter.cn/sidebar/sanfene/mysql.html)

q054 · 中等

列举至少五种索引失效的常见场景。

参考答案要点

• 对索引列使用函数或表达式:如 WHERE YEAR(create_time)=2023、WHERE price*2>100,应改写为范围查询
• LIKE 以通配符开头:LIKE '%xxx' 无法走索引(LIKE 'xxx%' 可以)
• 联合索引不满足最左前缀原则:如索引(a,b,c)查询只用了 b 或 c;中间断列后后续列无法使用
• 使用 != 或 <> 不等值查询、NOT IN,或 OR 连接的条件中有字段无索引,可能放弃索引走全表扫描
• 隐式类型转换:如字符串列与数字比较(varchar 列写 WHERE col=123),索引失效;还有排序字段与索引方向不一致触发 filesort
• 优化手段:改写 SQL、建立合适的联合索引/覆盖索引,并用 EXPLAIN 验证 key 与 Extra(Using index 表示覆盖索引)

来源:[javabetter.cn](https://javabetter.cn/sidebar/sanfene/mysql.html)

q056 · 困难

讲一讲 InnoDB 的 MVCC 机制,它是如何实现的?

参考答案要点

• MVCC(多版本并发控制)让读操作不加锁:读不阻塞写、写不阻塞读,靠 Undo Log 版本链 + ReadView 实现
• 每行记录有隐藏列 DB_TRX_ID(最近修改事务 ID)和 DB_ROLL_PTR(回滚指针),多次修改通过 roll_ptr 串成版本链
• ReadView 包含 creator_trx_id、活跃事务列表 m_ids、min_trx_id、max_trx_id;判断规则:trx_id < min 可见;≥ max 不可见;在区间内则看是否在活跃列表(在则不可见,不在则可见);不可见时沿版本链找前一版本
• RC 与 RR 的区别:RC 每次快照读都生成新 ReadView(能看到别的事务已提交的新数据);RR 只在第一次读时生成并复用整个事务(实现可重复读)
• 普通 SELECT 是快照读走 MVCC;SELECT ... FOR UPDATE / LOCK IN SHARE MODE 以及 UPDATE/DELETE 是当前读,读最新版本并加锁,RR 下靠临键锁(Next-Key Lock=记录锁+间隙锁)防止幻读

来源:[javabetter.cn](https://javabetter.cn/sidebar/sanfene/mysql.html)

q057 · 中等

MySQL 事务的 ACID 四大特性分别靠什么机制保证?

参考答案要点

• 原子性(Atomicity):由 Undo Log 保证,记录修改前的旧值,事务失败时按 undo log 回滚到初始状态
• 持久性(Durability):由 Redo Log 保证(WAL 先写日志再刷数据页),崩溃后重放 redo log 恢复已提交数据;配合双写缓冲、Checkpoint 机制
• 隔离性(Isolation):由 MVCC(读)+ 锁机制(写,行锁/间隙锁/临键锁)共同保证
• 一致性(Consistency):由原子性、隔离性、持久性协同保证,加上应用层的业务约束
• 加分项:redo log 与 binlog 通过两阶段提交(XID)保证主从一致;redo log 是 InnoDB 引擎层的物理日志、循环写,binlog 是 Server 层的逻辑日志、追加写

来源:[javabetter.cn](https://javabetter.cn/sidebar/sanfene/mysql.html)

q574 · 中等

什么是索引下推(Index Condition Pushdown,ICP)?以联合索引 (name, age) 执行 SELECT * FROM user WHERE name LIKE '张%' AND age = 10 为例,说明开启 ICP 前后的执行差异。

参考答案要点

• MySQL 5.6 引入,默认开启,EXPLAIN 的 Extra 显示 Using index condition
• 无 ICP:引擎按 name 前缀过滤后,所有满足前缀的记录都回表,由 Server 层再过滤 age
• 有 ICP:age 条件下推到存储引擎层,直接在二级索引里同时用 name+age 过滤,只对同时满足的记录回表
• 本质是减少回表次数以及存储引擎与 Server 层的交互次数
• 适用于 range/ref/eq_ref/ref_or_null 等访问方式,InnoDB 与 MyISAM 都支持

来源:[javabetter.cn](https://javabetter.cn/sidebar/sanfene/mysql.html)

q575 · 中等

什么是回表?SELECT * FROM user WHERE name = '张三'(name 为普通二级索引)为什么可能需要回表?如何用覆盖索引优化,并在 EXPLAIN 中如何验证?

参考答案要点

• 二级索引叶子节点只存索引列值+主键值,不含整行数据
• 回表:先在 name 索引树定位到主键 id,再到聚簇索引 B+ 树查整行,一次查询两棵树查找
• 覆盖索引:查询所需字段全部包含在索引中,例如只查 id 和 name,或建立 (name, age) 联合索引直接返回 age
• EXPLAIN 的 Extra 出现 Using index 即表示覆盖索引生效,无需回表
• 工程上避免 SELECT *,按查询模式设计联合索引实现覆盖

来源:[javabetter.cn](https://javabetter.cn/sidebar/sanfene/mysql.html)

q577 · 中等

EXPLAIN 的 Extra 字段出现 Using filesort 和 Using temporary 分别说明什么?各举一个常见触发场景并给出优化思路。

参考答案要点

• Using filesort:无法直接利用索引顺序得到结果,需要额外排序,常见于 order by 非索引列、group by 与 order by 混用
• filesort 优化:为排序/分组列建立合适索引,使索引序与排序序一致,消除额外排序
• Using temporary:需借助临时表完成,常见于 group by、distinct、union 未走索引
• temporary 优化:分组列建索引;优化 join 顺序小表驱动;复杂子查询改写
• 两者同时出现且扫描行数大时通常就是慢 SQL 的直接原因,可配合 show warnings 查看优化器改写后的语句

来源:[xiaolincoding.com](https://www.xiaolincoding.com/interview/mysql.html)

q578 · 困难

ReadView 中包含哪四个核心字段?请完整描述判断 undo log 版本链上某个数据版本对当前事务是否可见的规则。

参考答案要点

• m_ids:生成 ReadView 时所有活跃(未提交)事务的 id 列表
• min_trx_id:m_ids 中最小的事务 id;max_trx_id:生成 ReadView 时系统将要分配给下一个事务的 id(注意不是 m_ids 的最大值)
• creator_trx_id:创建该 ReadView 的事务自身 id
• 判定顺序:版本 trx_id 等于 creator_trx_id 则可见;小于 min_trx_id 说明早已提交,可见;大于等于 max_trx_id 说明之后才开启,不可见
• 介于两者之间:若在 m_ids 中说明生成版本时未提交,不可见;不在 m_ids 中说明已提交,可见
• 不可见时沿 undo log 版本链回溯到上一版本继续判断,直到找到可见版本或链尾

来源:[xiaolincoding.com](https://www.xiaolincoding.com/interview/mysql.html)

q580 · 困难

InnoDB 的记录锁(Record Lock)、间隙锁(Gap Lock)、临键锁(Next-Key Lock)分别锁住什么?RR 隔离级别下 UPDATE 语句的 WHERE 条件列没有索引会发生什么?

参考答案要点

• 记录锁锁定索引上的单条记录;间隙锁锁定索引记录之间的开区间,目的是阻止其他事务在间隙内插入
• 临键锁=记录锁+间隙锁,左开右闭区间 (a,b],既锁记录本身又锁记录前的间隙
• 间隙锁之间彼此兼容:两个事务可以同时持有同一间隙的间隙锁
• RR 下当前读通过临键锁锁住范围,防止幻读;唯一索引等值命中会退化为纯记录锁
• UPDATE/DELETE 条件列无索引会走全表扫描,给扫描到的所有记录加临键锁,效果近似锁全表
• 因此更新与删除语句务必保证 WHERE 条件能命中索引

来源:[xiaolincoding.com](https://www.xiaolincoding.com/mysql/lock/show_lock.html)

q582 · 中等

redo log 和 binlog 有哪些核心区别?为什么有了 binlog 还需要 redo log?

参考答案要点

• 层次不同:redo log 是 InnoDB 引擎层日志,binlog 是 Server 层日志,所有存储引擎都可写 binlog
• 内容不同:redo log 是物理日志(某个页某个偏移做了什么修改),binlog 是逻辑日志(语句或行变更事件)
• 形式不同:redo log 固定大小循环写,写满触发检查点刷脏页;binlog 追加写,写满切换新文件
• 用途不同:redo log 用于崩溃恢复保证持久性(WAL 先写日志);binlog 用于主从复制、数据归档与按时间点恢复
• binlog 只在事务提交时写,无法恢复运行中已修改的脏页;redo log 在事务执行过程中就不断写入,crash-safe

来源:[xiaolincoding.com](https://www.xiaolincoding.com/mysql/log/how_update.html)

q583 · 困难

描述 redo log 与 binlog 的两阶段提交流程。为什么必须两阶段提交?崩溃恢复时 InnoDB 如何决定事务提交还是回滚?

参考答案要点

• 阶段一 prepare:写 redo log 并将状态置为 prepare,按 innodb_flush_log_at_trx_commit=1 落盘
• 阶段二 commit:写 binlog 并按 sync_binlog=1 落盘,随后将 redo log 置为 commit 状态
• 必须两阶段:redo log 与 binlog 是两份独立日志,任何一份先单独提交都可能宕机造成主从数据或恢复结果不一致
• 崩溃恢复规则:redo log 处于 prepare 时,检查 binlog 中是否存在该事务的 XID:完整存在则提交,不存在或不完整则回滚
• 反证法分析:若 redo 已 prepare 而 binlog 未写,回滚后主从都没有该数据,一致;若 binlog 已写完整,主从都提交,一致
• 双 1 配置(innodb_flush_log_at_trx_commit=1 与 sync_binlog=1)是保证事务不丢的基线

来源:[xiaolincoding.com](https://www.xiaolincoding.com/mysql/log/how_update.html)

q585 · 中等

描述 MySQL 主从复制的完整流程(三种线程各自职责),并列举至少三种主从延迟的原因与对应缓解手段。

参考答案要点

• 主库事务提交后写 binlog,log dump 线程把 binlog 事件推送给从库
• 从库 IO 线程连接主库拉取 binlog,写入本地 relay log 中继日志
• 从库 SQL 线程读取 relay log 回放,MySQL 5.7+ 支持基于组提交的并行复制
• 延迟原因:大事务与长事务(binlog 要等整个事务完成才写)、从库回放能力不足、从库机器规格差或读压力大、网络带宽受限
• 缓解:拆小大事务;开启并行复制(replica_parallel_workers);关键读强制走主库;半同步复制降低丢数据风险
• 监控 Seconds_Behind_Master 与 GTID 位点,建立延迟告警

来源:[xiaolincoding.com](https://www.xiaolincoding.com/interview/mysql.html)

q587 · 中等

SELECT * FROM orders ORDER BY id LIMIT 900000, 20 为什么慢?给出至少两种优化方案并说明适用条件。

参考答案要点

• LIMIT offset,n 需要扫描并丢弃 offset+n 行,本例扫描约 90 万行,偏移量越大成本越高,且每行可能回表
• 方案一延迟关联:先在覆盖索引上分页取出 20 个主键,再 JOIN 回表取整行,回表次数从 90 万降到 20
• 方案二游标或书签:记录上一页最大 id,WHERE id > last_id ORDER BY id LIMIT 20,利用索引直接定位,只扫 20 行
• 方案三业务侧限制跳页(只允许上一页下一页)或交给搜索引擎承接深翻页
• 游标法要求排序字段连续有序(如自增主键)且不支持随机跳页;延迟关联通用性更好

来源:[javabetter.cn](https://javabetter.cn/sidebar/sanfene/mysql.html)

场景题(3)

q058 · 困难

上线后发现一个接口越来越慢,MySQL 中存在执行时间超过 2 秒的 SQL。请描述你的排查与优化思路。

参考答案要点

• 定位:开启慢查询日志(slow_query_log、long_query_time),或 show processlist 查看当前执行中的 SQL,结合监控系统找到慢 SQL
• 分析:EXPLAIN 查看执行计划,重点关注 type(ALL 全表扫描需优化)、key(是否用上索引)、rows(预估扫描行数)、Extra(Using filesort/Using temporary 需处理)
• 索引优化:为 WHERE/JOIN/ORDER BY 高频字段建索引,选择区分度高的列,联合索引遵守最左前缀并把等值列放前;利用覆盖索引避免回表(EXPLAIN 出现 Using index)
• SQL 改写:避免 SELECT *,减少函数包裹索引列;深分页用延迟关联或游标书签(WHERE id > last_max_id)替代大 OFFSET;JOIN 小表驱动大表且关联字段有索引,关联表不超过 3 张
• 架构层面:热点数据加 Redis 缓存、读写分离、大表分库分表;参数层面按需调整 sort_buffer_size 等
• 验证:优化后再次 EXPLAIN 与压测,确认执行计划与响应时间达标

来源:[javabetter.cn](https://javabetter.cn/sidebar/sanfene/mysql.html)

q581 · 困难

RR 隔离级别,表 t(id 唯一索引)中存在 id=1、2、3 的记录。事务 A 执行 SELECT * FROM t WHERE id=5 FOR UPDATE(记录不存在),事务 B 执行相同查询,两者都成功;随后 A 和 B 各自 INSERT id=5 的记录,其中一个事务收到死锁回滚。请解释死锁成因,并给出至少两种避免方案。

参考答案要点

• id=5 不存在,唯一索引等值查询未命中,RR 下加的是 (3, supremum] 的间隙锁
• 间隙锁之间兼容,A、B 的查询互不阻塞,各自持有该间隙的间隙锁
• INSERT 需要加插入意向锁,插入意向锁与对方持有的间隙锁冲突,A、B 循环等待形成死锁
• InnoDB 死锁检测(innodb_deadlock_detect 默认开启)会回滚代价较小的事务
• 避免方案:降低隔离级别到 RC(基本无间隙锁);改用 INSERT ... ON DUPLICATE KEY UPDATE 或捕获重复键异常代替先查后插;减少对不存在记录的 FOR UPDATE 查询;缩短事务持锁时间

来源:[xiaolincoding.com](https://www.xiaolincoding.com/mysql/lock/show_lock.html)

q586 · 中等

系统采用读写分离,用户修改昵称后立即刷新个人主页,偶发仍显示旧昵称。请分析原因并给出至少三种解决方案,说明各自取舍。

参考答案要点

• 原因:主从异步复制,写主库后立刻读从库,从库尚未回放该 binlog
• 方案一:写后一段时间内或同一请求链路内强制读主库,框架层路由标记实现,简单但增加主库压力
• 方案二:记录写库时的 binlog 位点或 GTID,读从库前等待从库回放到该位点,精确但引入等待延迟
• 方案三:半同步复制让至少一个从库确认收到 binlog,缩小不一致窗口,但仍非绝对一致
• 兜底:前端对关键写后读做延时或重试;重要数据以缓存写库后的更新为准
• 取舍:强一致读牺牲扩展性,等待 GTID 增加尾延迟,应按业务敏感度分级选择

来源:[xiaolincoding.com](https://www.xiaolincoding.com/interview/mysql.html)