MySQL知识点 覆盖索引、MVCC、存储引擎、事务锁、性能优化等核心点

MySQL知识点

覆盖索引、MVCC、存储引擎、事务锁、性能优化等核心点

一、索引专题（最高频）

1. 什么是索引？有什么作用？

• 本质：加速数据检索的有序辅助数据结构，相当于字典的目录
• 核心作用：减少磁盘IO次数，把全表扫描变成索引查找，千万级表查询速度提升几个数量级
• 额外作用：利用索引有序性避免文件排序（Using filesort）、通过唯一索引保证数据完整性
• 缺点：增加写入开销（需维护索引结构）、占用额外磁盘空间、过多索引会增加优化器选择成本

2. MySQL索引为什么用B+树，不用二叉树、红黑树、B树、哈希表？

B+树核心优势：

1. IO次数最少：非叶子节点只存索引不存数据，一个页能存更多索引，树高仅3-4层（可存千万级数据）
2. 查询效率稳定：所有查询都要到叶子节点，时间复杂度都是O(logn)
3. 范围查询极快：叶子节点用双向链表串联，只需找到首尾节点遍历即可
4. 缓存友好：非叶子节点小，内存能缓存更多索引

3. 聚簇索引和非聚簇索引有什么区别？

核心结论：InnoDB必须有聚簇索引（无主键则自动生成6字节rowid），优先用自增主键做聚簇索引

4. 什么是回表？如何避免回表？

• 定义：通过二级索引查到主键值后，再去聚簇索引查完整行数据的过程，本质是两次B+树查找
• 避免方法：
  1. 使用覆盖索引：查询的所有字段都包含在索引中
  2. 尽量直接用主键查询
  3. 只查询需要的字段，避免SELECT *

5. 什么是覆盖索引？有什么好处？

• 定义：查询需要的所有字段都能在某个索引中找到，不需要回表
• 核心好处：消除回表的二次IO，查询性能提升数倍
• 示例：给order表建idx_userid_createtime(user_id, create_time)索引
  • SELECT order_id, create_time FROM order WHERE user_id=123→ 覆盖索引，不回表
  • SELECT * FROM order WHERE user_id=123→ 需回表查其他字段

6. 什么是最左匹配原则？

• 定义：联合索引查询时，必须从索引最左侧字段开始匹配，中间不能跳过字段
• 底层原因：联合索引的B+树先按第一个字段排序，再按第二个排序，以此类推
• 示例：联合索引idx_abc(a,b,c)
  • WHERE a=1 AND b=2 AND c=3→ 用全部3个字段
  • WHERE a=1 AND b=2→ 用前2个字段
  • WHERE a=1 AND c=3→ 只用a字段，c字段用不到
  • WHERE b=2 AND c=3→ 完全用不到索引
• 注意：MySQL优化器会自动调整where条件顺序，WHERE c=3 AND a=1 AND b=2也能用到索引

7. 什么是索引下推（ICP）？

Index Condition Pushdown

• 定义：MySQL 5.6引入的优化技术，把原本在Server层的条件过滤下推到存储引擎层
• 优化前：存储引擎只按最左字段筛选数据，返回所有符合条件的主键，Server层再过滤其他条件
• 优化后：存储引擎在遍历索引时，同时判断所有索引字段的条件，只返回符合条件的主键
• 效果：大幅减少回表次数，尤其当最左字段筛选性差、后续字段筛选性好时
• 示例：索引idx_age_name(age,name)，WHERE age>20 AND name LIKE 'li%'
  • 无ICP：查所有age>20的记录，回表后再过滤name
  • 有ICP：在索引中同时过滤age和name，只回表符合条件的记录

8. 索引有哪些设计原则？

1. 索引不是越多越好：单表索引不超过5个
2. 只给高频查询字段建索引：低频查询字段没必要
3. 优先给高基数字段建索引：基数>80%的字段（如手机号、用户名），性别、状态等低基数字段不建
4. 索引字段尽量小：用int不用bigint，用varchar(20)不用varchar(255)
5. 避免给允许NULL的字段建索引：NULL值会导致索引效率降低
6. 频繁更新的字段不建索引：会导致大量页分裂和页合并
7. 尽量用联合索引代替多个单列索引：能更多触发覆盖索引
8. 联合索引遵循"左高右低"：高频字段、等值字段、高基数字段放左边
9. 主键尽量用自增ID：避免随机插入导致的页分裂

9. 哪些情况会导致索引失效？

1. 索引字段用了函数或表达式：WHERE YEAR(create_time)=2026、WHERE age+1=26
2. 隐式类型转换：varchar类型字段用数字查询（WHERE phone=13800138000）
3. 模糊查询%在左边：WHERE name LIKE '%li'（'li%'不会失效）
4. 违反最左匹配原则：联合索引跳过左侧字段
5. 用OR连接非索引字段：WHERE name='zhangsan' OR age=20（age无索引则全表扫描）
6. 低基数字段建索引：优化器认为全表扫描更快
7. 查询结果集过大：返回超过表数据30%时，优化器放弃索引
8. 表数据量太小：几十行的表全表扫描更快
9. 表统计信息过时：执行ANALYZE TABLE 表名更新统计信息

10. 什么是前缀索引？什么时候用？

• 定义：只对字符串字段的前N个字符建索引
• 语法：CREATE INDEX idx_name ON user(name(10))
• 适用场景：长字符串字段（如邮箱、URL），前N个字符已经有足够的区分度
• 优点：减少索引占用空间，提高索引效率
• 缺点：无法用于ORDER BY和GROUP BY，也无法做覆盖索引

二、MVCC专题（必考题）

11. 什么是MVCC？解决了什么问题？

• 定义：多版本并发控制，通过维护数据的多个历史版本，实现读写互不阻塞
• 解决的核心问题：没有MVCC时，读写会互斥（读加共享锁，写加排他锁），高并发下性能极差
• 核心优势：读不加锁，写不阻塞读，大幅提升数据库并发性能

12. MVCC是如何实现的？

全称：Multi-Version Concurrency Control

中文：多版本并发控制

1. 作用：MySQL InnoDB 实现读不加锁、读写不阻塞，提升并发性能
2. 核心原理：每行数据存多个版本，通过undo log + 行隐藏字段生成数据快照
3. 生效隔离级别：主要用于RC（读已提交）、RR（可重复读）
4. 和锁区别：乐观并发机制，不靠锁而是靠数据版本控制并发

• InnoDB 三个隐藏字段：trx_id（事务 ID）、roll_pointer（回滚指针）、row_id（无主键时使用）
• RR 隔离级别下，MVCC + 间隙锁 解决幻读问题

依赖三个核心组件：

1. 隐藏字段：每行数据隐含两个字段
   • DB_TRX_ID：最后修改该行的事务ID（事务ID递增）
   • DB_ROLL_PTR：回滚指针，指向undo日志中的旧版本
2. Undo日志：存储数据的历史版本，同一行数据的多次修改会形成一条版本链
3. Read View（读视图）：事务启动时生成的快照，判断哪个版本对当前事务可见

13. MVCC如何实现可重复读？

• 可重复读隔离级别下，事务启动时生成一个Read View，整个事务期间都用这个Read View
• 可见性规则：
  1. 只能看到创建版本≤当前事务ID的数据
  2. 只能看到删除版本>当前事务ID（或未删除）的数据
• 效果：事务期间看到的数据是一致的，其他事务的修改对当前事务不可见

14. MVCC和锁的关系？

• MVCC只适用于普通SELECT查询（快照读），不需要加锁
• 对于写操作（INSERT/UPDATE/DELETE）和加锁读（SELECT ... FOR UPDATE），仍然需要加锁（当前读）
• 两者结合：InnoDB实现了"读写互不阻塞"，读操作不影响写操作，写操作也不影响读操作

三、存储引擎专题

15. InnoDB和MyISAM的核心区别？

核心结论：非特殊场景一律用InnoDB，MySQL 5.5之后默认引擎就是InnoDB

16. InnoDB为什么推荐用自增主键？

1. 避免页分裂：自增主键是顺序插入，不会在数据页中间插入数据，减少页分裂和页合并
2. 索引体积小：int类型比UUID等字符串类型小很多，一个页能存更多索引，树高更低
3. 减少回表开销：二级索引的叶子节点存主键值，主键越小，二级索引体积越小

四、事务与锁专题

17. 事务的ACID特性是什么？

• 原子性（Atomicity）：事务是不可分割的最小单位，要么全部成功，要么全部失败（由undo日志保证）
• 一致性（Consistency）：事务执行前后，数据的完整性约束不被破坏（由其他三个特性共同保证）
• 隔离性（Isolation）：多个事务并发执行时，互不干扰（由锁和MVCC保证）
• 持久性（Durability）：事务提交后，修改永久生效，即使数据库崩溃也不会丢失（由redo日志保证）

18. 事务的四个隔离级别？分别解决了什么问题？

• 脏读：读到其他事务未提交的数据
• 不可重复读：同一事务内两次查询同一行数据，结果不同（其他事务修改了数据）
• 幻读：同一事务内两次查询同一范围，结果行数不同（其他事务插入了数据）

MySQL默认隔离级别：可重复读（RR）

19. InnoDB如何解决幻读？

InnoDB在可重复读隔离级别下，通过MVCC + 间隙锁（Gap Lock）解决幻读：

1. 快照读（普通SELECT）：通过MVCC读取历史版本，看不到其他事务的插入
2. 当前读（加锁读/写操作）：通过间隙锁锁住记录之间的间隙，防止其他事务在间隙中插入数据

20. 什么是死锁？如何避免死锁？

• 定义：多个事务互相持有对方需要的锁，且都不释放，形成循环等待

  • 互斥：资源同一时间只允许一个事务持有

    请求并保持：事务已持有部分资源，又去申请新资源，不释放已有资源

    不可剥夺：已被占有的资源，不能被其他事务强行抢占

    循环等待：多个事务形成环形资源等待链

• 避免方法：

  1. 所有事务按相同顺序获取锁：比如都按ID从小到大的顺序更新
  2. 尽量用小事务：减少持有锁的时间
  3. 避免大范围加锁：尽量用行级锁，避免表级锁
  4. 设置合理的锁超时时间：innodb_lock_wait_timeout默认50秒
  5. 用乐观锁代替悲观锁：通过版本号或时间戳实现

五、日志专题

21. redo日志和undo日志的区别？

redo log（重做日志）

1. 崩溃恢复用，保证事务持久性，宕机后靠它把未刷盘的数据恢复。
2. MySQL崩溃恢复核心，先写日志再写磁盘，宕机后依据它重做未落地的数据，保障事务提交后数据不丢失。

undo log（回滚日志）

1. 事务回滚 + MVCC 用，保存数据旧版本，实现回滚和读写不阻塞。
2. 记录数据修改前的旧版本，一是供事务回滚，二是为 MVCC 提供历史快照，实现无锁读。

22. 什么是WAL（写前日志）机制？

全称：Write-Ahead Logging，中文：预写日志 / 写前日志

• 定义：事务提交时，先写redo日志，再异步刷盘数据
• 核心优势：把随机写磁盘变成顺序写redo日志，大幅提升写入性能
• 崩溃恢复：数据库宕机后，重启时通过redo重做日志 记录 所有已提交但未刷盘的修改，保证数据不丢失

WAL 规则：改数据前，先把修改记录写到 redo 日志

1. 修改内存数据页，同时把「这次做了什么修改」记录到redo log

2. redo log 是固定、连续的磁盘文件，所有事务的修改日志，

   从头到尾依次追加写入

   👉 这就是顺序写，几乎没有寻址开销，速度飞快。

3. 数据页不会立刻刷盘，MySQL 后台线程慢慢、批量把内存脏页刷回磁盘（异步刷盘）

六、执行计划与性能优化专题

23. EXPLAIN执行计划中最重要的四个字段是什么？怎么看？

type key rows Extra

1. type：查询效率，从好到坏：system > const > eq_ref > ref > range > index > ALL
   • 至少要达到ref或range，出现ALL就是全表扫描，必须优化
   • const：根据主键/唯一索引查询，只返回 1 行 →极快
   • ref：普通索引匹配多行 →良好
   • range：索引范围查询（between、in、>）→一般可用
   • index：遍历整个索引树 →慢
2. key：实际使用的索引，为NULL表示没用到索引
   1. possible_keys
   2. key_len 可以用来判断是否用到了联合索引的多个字段。
3. rows：预估扫描的行数，越小越好
4. Extra：额外信息

   • Using index：覆盖索引，极好

   • Using where：使用 where 条件过滤

   • MySQL 在服务器层对数据进行过滤，而不是在存储引擎层通过索引过滤

   • Using filesort：文件排序，严重性能问题

   • Using temporary：使用临时表，性能差

   • Using index condition：索引下推，优化

   • NULL：正常

   • no matching row in const table查询里用到了const类型的常量表（单表唯一匹配），但该表没有找到任何符合条件的数据，属于无数据匹配

   • Using join buffer (hash join)

     • MySQL 无法使用索引嵌套循环（Nested Loop Join），只能用哈希连接（Hash Join）

       哈希连接需要把一张表的数据读入join buffer内存，构建哈希表，再和另一张表做匹配

       只有当连接字段没有可用索引时，才会触发这个行为

较好的总结

1. type：别出现 ALL
2. key：别为 NULL
3. rows：越小越好
4. Extra：别出现 Using filesort / Using temporary

所有字段

id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

• id：执行顺序
• select_type：查询类型
• table：表名
• type：查询效率（最重要）
• possible_keys：可能用的索引
• key：实际用的索引
• key_len：索引长度
• ref：匹配条件
• rows：预估扫描行数
• Extra：额外关键信息（性能坑都在这）

id

id 查询执行的顺序编号

• 数字越大越先执行

• 子查询、联合查询会出现多个 id （看执行顺序是否合理。）

• 单表简单查询永远是1

select_type

含义：查询的类型（最重要的字段之一）

常见值：

• SIMPLE：简单查询（无子查询/union）→最好
• SUBQUERY：子查询
• DERIVED：派生表（from 里的子查询）
  • 主表为：PRIMARY
• UNION：union 查询
• DEPENDENT SUBQUERY：相关子查询 →性能差

好坏判断：
能 SIMPLE 就别 SUBQUERY、DERIVED。

derive 英 / dɪˈraɪv / 美 / dɪˈraɪv / 简明柯林斯 v. 获得，取得；起源于，来自；提取，衍生（化学物质）

ref

含义：与索引比较的列或常量
常见：

• const：和常量匹配（where id=1）
• 库.表.列：与另一张表的列关联

24. 什么是慢查询？如何分析慢查询？

• 定义：执行时间超过long_query_time（默认10秒，建议设为1秒）的SQL
• 开启慢查询日志：

  SETGLOBALslow_query_log=ON;SETGLOBALlong_query_time=1;

• 分析工具：mysqldumpslow、pt-query-digest
• 优化步骤：用EXPLAIN分析执行计划 → 检查是否用到索引 → 优化SQL语句或添加索引

25. 如何优化大量数据插入的性能？

1. 批量插入：合并多条INSERT为一条，每批不超过1000条
2. 关闭自动提交：手动控制事务提交，减少事务次数
3. 临时关闭索引：插入前删除索引，插入完成后重建
4. 调整参数：innodb_flush_log_at_trx_commit=0、bulk_insert_buffer_size
5. 使用LOAD DATA：比INSERT快10倍以上

26. 数据库性能优化的金字塔是什么？

从易到难，收益从高到低：

1. 架构优化：读写分离、分库分表、缓存、搜索引擎

2. SQL与索引优化：优化慢SQL、合理设计索引

3. MySQL配置优化：缓冲池、连接数、日志参数

4. 硬件与OS优化：SSD、RAID、文件系统、内核参数

   1. RAID：Redundant Array of Independent Disks

      中文：独立磁盘冗余阵列（早期也叫廉价磁盘冗余阵列）

      将多块物理硬盘通过硬件 / 软件组合为一个逻辑磁盘，核心解决两个问题：

      1. 提升读写性能

      2. 数据冗余容错（硬盘坏了不丢数据、不宕机）

         是服务器、数据库存储的标准方案。

七、其他高频题

27. 什么是行锁和表锁？

• 表锁：锁住整个表，粒度大，冲突概率高，并发性能差（MyISAM仅支持表锁）
• 行锁：锁住具体的行，粒度小，冲突概率低，并发性能好（InnoDB支持行锁）
• 注意：InnoDB的行锁是基于索引的，没有索引的查询会升级为表锁

28. 什么是乐观锁和悲观锁？

• 悲观锁：假设一定会发生并发冲突，操作前先加锁
  • 实现：SELECT ... FOR UPDATE
  • 适用：写多的场景
• 乐观锁：假设不会发生并发冲突，提交时检查是否有冲突
  • 实现：版本号机制（UPDATE table SET ..., version=version+1 WHERE id=1 AND version=old_version）
  • 适用：读多的场景

29. 为什么不推荐用SELECT *？

1. 增加IO开销：读取不需要的字段，尤其是大字段（TEXT、BLOB）
2. 无法使用覆盖索引：必须回表查询
3. 增加网络传输量：返回多余数据，占用带宽
4. 降低代码可读性：不知道具体查询了哪些字段

30. 什么是主从延迟？如何解决主从延迟？

• 定义：主库写入数据后，从库同步数据的时间差
• 原因：主库并发写入量大，从库单线程重放binlog
• 解决方法：
  1. 优化主库写入，减少大事务
  2. 升级从库硬件
  3. 采用多线程复制（MySQL 5.7+支持）
  4. 读写分离时，关键业务读主库
  5. 分库分表，分散压力

MySQL binlog

全称：Binary Log（二进制日志）

1. 作用：记录数据库所有修改类SQL（增/删/改、DDL），查询不记录。
2. 核心用途
   • 主从复制：主库把binlog同步给从库，实现数据同步
   • 数据恢复：误删/误改后，用binlog回滚找回数据
3. 三种格式
   • statement：记录SQL语句（体积小，可能有数据不一致）
   • row：记录行数据变更（精准，默认推荐）
   • mixed：混合前两种
4. 一句话总结：MySQL的数据变更流水账，用来做主从同步和数据恢复。