个人主页 for_ever_love__ 欢迎各位大佬莅临其他栏目: 大模型开发从0到1 其他栏目: iOS项目总结大全 其他栏目: 我想学python了 其他栏目: iOS UI 文章目录MySQL 高频面试题 30 问索引、事务、锁、日志、主从与分库分表MySQL 8.0 版一、索引篇Q1 - Q8Q1为什么索引用 B 树而不是二叉查找树或 B 树Q2聚簇索引和二级索引有什么区别什么是回表Q3联合索引的最左前缀原则是怎么回事Q4哪些情况会导致索引失效Q5什么是索引下推ICPQ6EXPLAIN 里你主要看哪几列Q7COUNT(*)、COUNT(1)、COUNT(列) 有什么区别Q8为什么推荐用自增 ID 做主键UUID 有什么问题二、事务与锁篇Q9 - Q14Q9ACID 分别靠什么实现Q10四种隔离级别MySQL 默认哪个Q11MVCC 是怎么实现的RC 和 RR 的差别在哪Q12快照读和当前读有什么区别Q13InnoDB 有哪几种锁什么是 Next-Key LockQ14死锁怎么排查和避免三、日志与存储篇Q15 - Q19Q15redo log、undo log、binlog 有什么区别Q16为什么要有两阶段提交Q17一条 UPDATE 语句在 MySQL 里经历了什么Q18InnoDB 和 MyISAM 的区别Q19为什么 8.0 移除了查询缓存Query Cache四、SQL 优化篇Q20 - Q24Q20深分页 LIMIT 1000000, 20 为什么慢怎么优化Q21JOIN 有哪些算法Q22GROUP BY 和 ORDER BY 什么时候会用到临时表 / filesortQ23慢查询的排查流程是什么Q24CHAR 和 VARCHAR 怎么选VARCHAR(10) 能存 10 个汉字吗五、架构与运维篇Q25 - Q28Q25主从复制的原理是什么延迟怎么解决Q26读写分离会遇到什么问题Q27大表加一个字段怎么做到不影响业务Q28分库分表怎么选分片键全局 ID 怎么生成六、场景题Q29 - Q30Q29一个表有 (a, b, c) 联合索引下面几条 SQL 谁走索引Q30线上发现 CPU 100%怎么定位小结面试回答的三个层次MySQL 高频面试题 30 问索引、事务、锁、日志、主从与分库分表MySQL 8.0 版面试 MySQL 有个特点题目就那些但每一问都能往下追三层。只会背ACID 是原子性一致性隔离性持久性的人第一层就结束了。这篇按模块整理 30 个高频问题每题给出能答到第二层的答案和常见的追问点。默认语境是 MySQL 8.0 InnoDB遇版本差异会单独标注。一、索引篇Q1 - Q8Q1为什么索引用 B 树而不是二叉查找树或 B 树结构问题二叉查找树可能退化成链表树高 n平衡二叉树AVL/红黑树每个节点只存一个 key树高太高。100 万行数据树高约 20就要 20 次磁盘 IOB 树多叉树高降低但每个节点都存数据一个页能放的 key 变少树高反而更高B 树✅非叶子节点只存 key 不存数据一个 16KB 的页能塞上千个 key → 树高只有 3~4 层且叶子节点用链表串起来范围查询不用回溯到根一句话总结B 树把树高压到 3~4 层意味着亿级数据点查只需 3~4 次磁盘 IO叶子链表让它天然擅长范围查询。追问为什么不用 Hash 索引—— Hash 只支持等值查询不支持范围和排序且有哈希冲突。InnoDB 的自适应哈希索引AHI是内部优化用户不能显式创建MEMORY 引擎除外。Q2聚簇索引和二级索引有什么区别什么是回表聚簇索引叶子节点存整行数据。InnoDB 每张表有且仅有一个通常是主键二级索引叶子节点存索引列 主键值通过二级索引找到主键值后再去聚簇索引查整行这个过程叫回表。-- 假设 idx_name 建在 name 上SELECT*FROMuserWHEREname张三;-- ① 走 idx_name 找到主键 id100-- ② 用 id100 回聚簇索引取整行 ← 回表减少回表的办法就是覆盖索引-- 建联合索引让查询需要的列都在索引里CREATEINDEXidx_name_ageONuser(name,age);SELECTid,name,ageFROMuserWHEREname张三;-- Extra: Using index不用回表Q3联合索引的最左前缀原则是怎么回事索引(a, b, c)在 B 树里是按a → b → c的顺序排序的所以只能从最左边开始连续匹配条件能否用上索引用到哪些列a 1✅aa 1 AND b 2✅a, ba 1 AND b 2 AND c 3✅a, b, cb 2❌无没有 a无法定位a 1 AND c 3部分只有 ab 断掉了a 1 AND b 2部分只有 a范围条件之后的列用不上⚠️8.0 的索引跳跃扫描Index Skip Scan可以在没有最左列时跳着用索引但只在该列基数很低比如性别只有 2 个值时才划算不能依赖它。Q4哪些情况会导致索引失效场景例子说明索引列上用函数WHERE YEAR(create_time) 2024改成范围条件隐式类型转换WHERE phone 13800138000phone 是 varchar字符串列传了数字全表扫隐式字符集转换两表 JOIN 字段字符集不同utf8mb4 JOIN utf8 会失效LIKE %xxxWHERE name LIKE %三前缀通配符无法定位三%可以OR连接非索引列WHERE a1 OR b2b 无索引改成 UNION 或给 b 加索引违反最左前缀见 Q3—优化器判断不划算结果集占比太高回表代价大于全表扫描优化器会选全表-- ❌ 失效SELECT*FROMordersWHEREDATE(create_time)2024-06-01;-- ✅ 改写SELECT*FROMordersWHEREcreate_time2024-06-01ANDcreate_time2024-06-02;Q5什么是索引下推ICPMySQL 5.6 引入。联合索引(name, age)查询WHERE name LIKE 张% AND age 20没有 ICP存储引擎只按name LIKE 张%返回所有匹配行Server 层再过滤 age有 ICP存储引擎在索引内部就顺手判断 age20不符合的直接跳过减少回表次数执行计划里表现为Extra: Using index condition。8.0 默认开启可用optimizer_switchindex_condition_pushdownoff关闭验证。Q6EXPLAIN 里你主要看哪几列列关注点type从优到劣system const eq_ref ref range index ALL。见到 ALL 和 index 就要警惕key实际用到的索引possible_keys有但key为 NULL 说明索引没被选中rows预估扫描行数与实际返回行数差太多说明统计信息不准ExtraUsing index覆盖索引好、Using filesort排序没走索引、Using temporary用了临时表通常要优化filtered8.0 有表示经过条件过滤后剩余的比例EXPLAINFORMATJSONSELECT...;-- 更详细含 cost 估算EXPLAINANALYZESELECT...;-- 8.0.18给出真实执行耗时和行数Q7COUNT(*)、COUNT(1)、COUNT(列)有什么区别写法行为性能COUNT(*)统计所有行数8.0 对COUNT(*)有特殊优化通常最快COUNT(1)与COUNT(*)几乎等价基本一样COUNT(列)统计该列非 NULL的行数可能慢且语义不同COUNT(主键)统计主键非 NULL 行数主键非空等于总行数结论直接用COUNT(*)别再纠结COUNT(1)的都市传说了。追问InnoDB 为什么不把总行数存起来—— 因为 MVCC不同事务看到的行数不一样没法存一个全局值。Q8为什么推荐用自增 ID 做主键UUID 有什么问题维度自增 IDUUID插入性能顺序写页分裂少随机写频繁页分裂产生碎片索引体积8 字节BIGINT36 字节字符串或 16 字节BINARY(16)聚簇索引紧凑二级索引都要存主键主键大则所有索引都变大分布式需要额外方案雪花、号段天然全局唯一安全性可推测有信息泄露风险安全关键原因是页分裂InnoDB 数据按主键顺序存随机主键会让新行插到已有页中间页满了就分裂、移动数据产生碎片并降低页填充率。追问8.0 能用UUID_TO_BIN(uuid(), 1)存成 BINARY(16) 并且把时间部分提前从而让 UUID 变得相对有序缓解这个问题。二、事务与锁篇Q9 - Q14Q9ACID 分别靠什么实现特性实现机制原子性 Atomicityundo log回滚日志一致性 Consistency前三者共同保证 应用逻辑隔离性 IsolationMVCC 锁持久性 Durabilityredo logQ10四种隔离级别MySQL 默认哪个级别脏读不可重复读幻读READ UNCOMMITTED❌可能❌可能❌可能READ COMMITTED✅❌可能❌可能REPEATABLE READMySQL 默认✅✅✅ InnoDB 下靠间隙锁解决SERIALIZABLE✅✅✅MySQL 默认 RROracle / PostgreSQL 默认 RC这是常被对比的点。Q11MVCC 是怎么实现的RC 和 RR 的差别在哪MVCC undo log 版本链 ReadView。每行有三个隐藏字段DB_TRX_ID最后修改的事务 ID、DB_ROLL_PTR回滚指针、DB_ROW_ID。通过DB_ROLL_PTR把历史版本串成一条链读的时候按 ReadView 规则挑一个可见版本。RC 和 RR 唯一的实现差异是 ReadView 生成时机级别ReadView 生成时机效果RC每条 SELECT 都重新生成每次看到最新已提交数据 → 不可重复读RR事务内第一次 SELECT 时生成之后复用整个事务看同一个快照 → 可重复读Q12快照读和当前读有什么区别SELECT*FROMtWHEREid1;-- 快照读走 MVCC不加锁SELECT*FROMtWHEREid1FORUPDATE;-- 当前读加排他锁读最新版本UPDATEtSETa1WHEREid1;-- UPDATE 本质是当前读⚠️经典坑在 RR 下SELECT看到 1000别的事务改成 900 并提交你再UPDATE ... SET balance balance - 100UPDATE 是当前读会基于 900 算。所以先读后写必须用FOR UPDATE加锁否则丢失更新。Q13InnoDB 有哪几种锁什么是 Next-Key Lock锁类型说明行锁 Record Lock锁单条索引记录间隙锁 Gap Lock锁两条记录之间的间隙防止插入Next-Key Lock行锁 间隙锁左开右闭区间RR 下的默认加锁单位意向锁 IS / IX表级锁用于快速判断表里有没有行锁自增锁 AUTO-INC插入自增列时的特殊表级锁间隙锁是 RR 下解决幻读的关键。RR 下SELECT ... FOR UPDATE会加 Next-Key Lock阻止别的事务在区间内插入所以当前读也不会出现幻读。⚠️RC 下基本没有间隙锁只剩外键和唯一性检查场景这是很多团队把 MySQL 改成 RC 的原因锁范围小、死锁少、并发高。Q14死锁怎么排查和避免-- 查看最近一次死锁只保留最后一条SHOWENGINEINNODBSTATUS\G-- 建议打开配置把所有死锁写进 error logSETGLOBALinnodb_print_all_deadlocksON;死锁产生的四个必要条件互斥、占有且等待、不可抢占、循环等待。破坏任意一个即可工程上最有效的是破坏循环等待所有业务按固定顺序访问资源比如先扣 A 账户再扣 B 账户永远按 id 排序缩小事务范围把查询逻辑移到事务外避免长事务避免事务里有 RPC / 慢查询合理设置innodb_lock_wait_timeout默认 50s可降到 10~30s 快速失败三、日志与存储篇Q15 - Q19Q15redo log、undo log、binlog 有什么区别redo logundo logbinlog层级InnoDB 引擎层InnoDB 引擎层MySQL Server 层作用崩溃恢复保证持久性回滚 MVCC 版本链主从复制、数据恢复内容物理日志页的修改逻辑日志反向操作逻辑日志SQL 或行变更写入方式循环写固定大小随事务生成追加写可归档所有引擎都有❌❌✅Q16为什么要有两阶段提交redo log 和 binlog 是两套独立的日志必须保证它们状态一致否则主从会不一致。1. prepare 阶段redo log 写入并标记 prepare 2. 写 binlog 3. commit 阶段redo log 标记 commit崩溃恢复时的判断逻辑redo 里有 commit 标记 → 提交redo 里只有 prepare但能找到对应 binlog→ 提交redo 里只有 prepare找不到 binlog → 回滚Q17一条 UPDATE 语句在 MySQL 里经历了什么1. 连接器权限校验 2. 分析器词法、语法分析 3. 优化器选索引、定执行计划基于 cost 4. 执行器调用 InnoDB 接口 ├─ 数据页在 Buffer Pool不在就从磁盘加载 ├─ 写 undo log ├─ 更新 Buffer Pool 中的数据页变脏页 ├─ 写 redo logprepare ├─ 写 binlog └─ redo log 标记 commit 5. 后台线程异步刷脏页⚠️ 注意顺序先改内存再写日志这就是 WALWrite-Ahead Logging。Q18InnoDB 和 MyISAM 的区别维度InnoDBMyISAM事务✅❌锁粒度行锁表锁外键✅❌崩溃恢复✅redo log❌MVCC✅❌索引结构聚簇索引数据即索引非聚簇索引存物理地址行数统计不保存COUNT(*)要扫保存COUNT(*)极快全文索引5.6 支持支持8.0 分区✅ 唯一支持分区的主流引擎❌ 8.0 已移除分区支持结论除非只读且不需要事务极少见一律用 InnoDB。5.5 之后它就是默认引擎。Q19为什么 8.0 移除了查询缓存Query Cache查询缓存在 5.7 默认关闭8.0 直接移除。原因缓存失效太激进表里任何一行改动该表所有缓存全部失效写多读少的场景下维护缓存的开销大于收益有全局锁竞争是并发瓶颈现在这个活儿交给应用层Redis或者用 Buffer Pool 缓存数据页粒度更细、更有效。四、SQL 优化篇Q20 - Q24Q20深分页LIMIT 1000000, 20为什么慢怎么优化MySQL 必须先扫描并丢弃前 1000000 行只返回后 20 行。-- ❌ 慢SELECT*FROMordersORDERBYidLIMIT1000000,20;-- ✅ 方案一延迟关联用覆盖索引先拿到 idSELECTo.*FROMorders oJOIN(SELECTidFROMordersORDERBYidLIMIT1000000,20)tONo.idt.id;-- ✅ 方案二游标分页推荐尤其无限下拉场景SELECT*FROMordersWHEREid1000000ORDERBYidLIMIT20;方案二最优但要求排序键唯一且连续且不支持直接跳到第 N 页。Q21JOIN 有哪些算法算法版本说明Simple Nested Loop Join老版本双层循环最差Index Nested Loop Join一直有被驱动表走索引最常见的最优路径Block Nested Loop Join5.x被驱动表无索引时用 join buffer 批量比对Hash Join8.0.18无索引的等值 JOIN 场景下取代 BNL性能大幅提升优化要点小表驱动大表、被驱动表的 JOIN 字段必须有索引。Q22GROUP BY 和 ORDER BY 什么时候会用到临时表 / filesortORDER BY的字段顺序、升降序与索引不一致 →Using filesortGROUP BY的字段没有合适索引 →Using temporary Using filesort8.0 之前GROUP BY会隐式排序8.0 起不再默认排序除非显式写ORDER BY-- 建 (status, create_time) 联合索引下面这条就能直接走索引有序性SELECTstatus,COUNT(*)FROMordersGROUPBYstatus;Q23慢查询的排查流程是什么-- 1. 开启慢日志SETGLOBALslow_query_logON;SETGLOBALlong_query_time1;-- 2. 找出最慢的 SQLSELECTDIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT/1e12ASavg_secFROMperformance_schema.events_statements_summary_by_digestORDERBYAVG_TIMER_WAITDESCLIMIT10;-- 3. EXPLAIN 分析EXPLAINSELECT...;-- 4. 看优化器到底怎么选的SEToptimizer_traceenabledon;SELECT...;SELECT*FROMinformation_schema.OPTIMIZER_TRACE\G排查顺序一般是有没有走索引 → 扫描行数是否过大 → 有没有 filesort/临时表 → 能不能改写 SQL。Q24CHAR 和 VARCHAR 怎么选VARCHAR(10)能存 10 个汉字吗CHAR(n)VARCHAR(n)长度固定右边补空格可变检索尾部空格被去掉保留额外开销无1~2 字节长度前缀适用长度几乎固定的短串如状态码、MD5、手机号长度变化大的字符串VARCHAR(10)能存 10 个字符utf8mb4 下一个汉字 4 字节所以实际占 40 字节 前缀。括号里的数字是字符数不是字节数这是 MySQL 5.0 之后的约定。追问INT(11)的 11 是什么——显示宽度只影响ZEROFILL时的补零不改变存储范围INT 永远是 4 字节。8.0.17 起该显示宽度属性已废弃。五、架构与运维篇Q25 - Q28Q25主从复制的原理是什么延迟怎么解决主库事务提交时写 binlogbinlog dump 线程负责推送 从库IO 线程拉取 binlog 写入 relay log 从库SQL 线程回放 relay log延迟的常见原因原因解法从库单线程回放跟不上开并行复制replica_parallel_workers8LOGICAL_CLOCK主库有大事务拆分大事务一次删 100 万行改成每次 1 万行从库硬件比主库差从库配置不低于主库从库承担大量读查询分流降低从库负载无主键表ROW 模式下回放慢所有表必须有主键Q26读写分离会遇到什么问题最典型的是主从延迟导致刚写完读不到方案说明写后读主库关键业务下单后查订单强制走主库二次读取从库读不到再读一次主库缓存标记写操作后打标记短时间内读主库等 GTID8.0 可用WAIT_FOR_EXECUTED_GTID_SET()等从库追上牺牲延迟换一致性Q27大表加一个字段怎么做到不影响业务优先级从高到低8.0.12 的 INSTANT ADD COLUMN只改数据字典秒级完成首选gh-ost / pt-osc5.7 或需要改类型时的标准方案原生 Online DDLINPLACE小表可以大表风险高row log 溢出、MDL 排队无论哪种执行前必查长事务SELECT*FROMinformation_schema.innodb_trxWHERETIMESTAMPDIFF(SECOND,trx_started,NOW())60;因为 DDL 要拿排他 MDL 锁前面有个长事务就会连锁阻塞所有业务 SQL。Q28分库分表怎么选分片键全局 ID 怎么生成分片键选择原则选查询频率最高的那个维度订单一般按 user_id让单用户查询落在一片避免热点按时间分片会导致新片成为热点尽量避免跨片 JOIN 和跨片事务全局 ID 方案对比方案优点缺点数据库号段segment简单、单调递增、可控有单点需做好高可用雪花算法 Snowflake本地生成、高性能、趋势递增依赖机器时钟时钟回拨会出问题UUID最简单无序、占空间、索引性能差Redis INCR简单依赖 Redis 持久化六、场景题Q29 - Q30Q29一个表有(a, b, c)联合索引下面几条 SQL 谁走索引-- 表 t索引 idx_abc(a,b,c)SELECT*FROMtWHEREa1ANDb2ANDc3;-- 用到 a、bc 用不上范围后断SELECT*FROMtWHEREc3ANDb2ANDa1;-- ✅ 三个都用上优化器会重排条件顺序SELECT*FROMtWHEREa1ORDERBYb,c;-- ✅ 走索引且排序免 filesortSELECT*FROMtWHEREa1ORDERBYc;-- ❌ 用 a但排序要 filesortb 缺失SELECT*FROMtWHEREb2ORDERBYa;-- ❌ 无 a走不了索引关键点WHERE里的条件顺序不影响索引使用优化器会重排但列的连续性会影响——断在哪一列后面的列就用不上了。Q30线上发现 CPU 100%怎么定位-- 1. 看当前在跑什么SELECTid,user,host,db,command,time,state,LEFT(info,120)FROMinformation_schema.PROCESSLISTWHEREcommandSleepANDtime5ORDERBYtimeDESC;-- 2. 看哪类 SQL 消耗最多SELECTDIGEST_TEXT,COUNT_STAR,SUM_TIMER_WAIT/1e12AStotal_sec,SUM_ROWS_EXAMINEDASrows_examinedFROMperformance_schema.events_statements_summary_by_digestORDERBYSUM_TIMER_WAITDESCLIMIT5;-- 3. 在 OS 层确认是哪个线程top-H-p $(pidof mysqld)-- 把 OS 线程号对应到 MySQL 内部SELECT*FROMperformance_schema.threadsWHERETHREAD_OS_IDpid;常见原因排序慢 SQL全表扫描 / 排序 高并发 锁等待 后台刷脏。CPU 100% 第一反应永远是找那条慢 SQL而不是加机器。小结面试回答的三个层次层次表现例子第一层背定义“索引是 B 树”第二层讲清为什么“因为 B 树非叶子只存 key16KB 页能塞上千 key树高压到 3~4 层”第三层讲清取舍和场景“所以它适合范围和点查但写多、基数低的列建索引收益不大反而拖慢写入”面试官真正在意的不是你记住了多少而是能不能讲清楚为什么——每个设计背后都有取舍有没有踩过坑——说出一个真实事故比背十个定义有用知不知道边界——“这个方案在XX场景下不适用”这句话非常加分把这一系列索引、SQL 优化、事务、锁、日志、备份、参数调优串起来看你会发现 MySQL 的所有设计都围绕一条主线用内存换磁盘 IO、用空间换时间、用一致性换可用性——而调优的本质就是根据你的业务在这三者之间找到那个平衡点。
