企业官网建设流程全解析

个人水平有限，可能有错，欢迎大家在评论区指正和讨论。如果觉得写的还行，请点点赞，你的赞是我更新的动力!
适用人群：快速了解、复习mysql常见八股

数据库基础

MySQL架构

执行一条 SQL 查询语句，期间发生了什么？

答：

• 连接器
• 查询缓存
• 解析 SQL：词法分析、语法分析
• 执行 SQL：预处理阶段、优化阶段、执行阶段

MySQL存储结构

表空间由**段（segment)、区（extent)、页（page)、行(row）**组成，InnoDB存储引擎的逻辑存储结构大致如下图：

默认每个页的大小为 16KB。

COMPACT行格式如下：

常见的存储引擎

**InnoDB:**有事务、行级锁，采用B+作为索引。索引即是数据。

**MyISAM:**通过索引找到行号，再找到数据。索引与数据分开。

MyISAM存储数据方式：

事务的ACID特性

答：

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务操作前后，数据满足完整性约束，数据库保持一致性状态。
• 隔离性（Isolation）：多个并发事务相互独立，互不干扰。
• 持久性（Durability）：事务一旦提交，对数据库的改变就是永久的，即使系统故障也不会丢失。

事务的隔离级别及对应的问题

并发事务的问题

答：

• 脏读：读到了未提交的数据
• 不可重复读：同一事务内，数据内容前后不一致（修改）
• 幻读：同一事务内，数据条数前后不一致（增删）

事务的隔离级别

答：

​MySQL事务有4种隔离级别，级别越高，一致性越强，性能越差：

• 读未提交：能读到其他事务未提交的数据
• 读已提交：只能读到其他事务已提交的数据（Oracle 默认）
• 可重复读：事务看到的数据与事务启动时看到的数据保持一致（MySQL 默认）
• 串行化：对记录加读写锁，事务依次执行，性能最低

对应关系：

• 读未提交：一个都解决不了
• 读已提交：解决脏读
• 可重复读：解决脏读 + 不可重复读
• 串行化：全部解决

MVCC（实现可重复读）

答：

​MVCC 是多版本并发控制，实现可重复读，维护多个版本数据，通过可见性规则，让事务只看到对应版本数据。MVCC分为以下3个部分：

• 隐藏字段：包含事务 ID（自增，记录操作事务）与回滚指针（指向数据上一版本）。
• undo log：存储历史版本的数据，通过回滚指针串联各版本，形成版本链。
• ReadView：用于判断事务对当前版本数据的可见性；RR 级别仅第一次查询生成 ReadView，RC 级别每次查询都生成。

实现图（加深印象）：

索引

索引的概念及优缺点

答：

​ MySQL 索引是快速查询和检索数据的数据结构。索引的作用就相当于书的目录。

优点：提高查询速度，减少磁盘 I/O 次数；加速排序分组

缺点：创建和维护耗时；占用存储空间

索引的底层数据结构

答：

​ InnoDB引擎采用的B+树结构来存储索引。优势如下：

• B + 树阶数大、层级少，磁盘IO次数少
• 非叶子节点存子页指针，叶子结点存储全部数据，查询性能更稳定
• 叶子节点形成有序双向链表，方便区间查询

索引的分类

答：

• 主键索引（聚簇索引、聚集索引）：B+树的叶子节点保存了整行数据，有且只有一个
• 二级索引（非聚簇索引）：B+树的叶子节点保存对应的主键，可以有多个
• 联合索引：多个字段组合成的索引，遵循最左匹配原则
• 唯一索引：索引列的值必须唯一（允许 NULL），避免重复

联合索引的最左匹配原则

答：

• 联合索引：用多个字段组成索引，使用时需要遵循最左匹配原则。
• 最左前缀匹配原则：指的是在使用联合索引时，MySQL会根据索引定义的字段顺序，从左往右依次匹配查询条件中的字段，如果能匹配，就使用索引过滤数据；如果遇到范围查询（比如 >、<、like 等），就会停止匹配。
• 设计规范：在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

覆盖索引、回表查询、索引下推

答：

• 覆盖索引：查询的字段全部包含在索引字段中，无需回表，查询效率极高
• 回表查询：使用二级索引查找时，叶子节点仅存主键+索引列的值，无法覆盖SQL查询的全部字段，需用通过主键再查主键索引，获取完整数据。
• 索引下推：使用二级索引查找时，在存储引擎层对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，再返还给Server层，从而减少回表次数。

索引失效的场景

1. 联合索引未遵循最左匹配原则
2. 在索引列上进行函数操作、类型转换
3. 发生隐式类型转换
4. 模糊查询以 % 开头（左模糊）
5. 查询条件使用不等号

索引设计的原则

1. 选择合适字段：优先选择经常查询、排序、分组，且区分度高、不为 null的字段。
2. 控制索引数量：单表索引不宜过多，避免降低增删改的性能。
3. 优先联合索引：尽量使用联合索引，提高覆盖索引的概率，减少回表次数。
4. 考虑表的数据量：选择数据量大、频繁查询的表。

SQL优化

Explain命令的使用

答： 可以采用MySQL自带的分析工具EXPLAIN

• 通过key和key_len检查是否命中了索引（索引本身存在是否有失效的情况）
• 通过type字段是表的访问方法，查看sql是否有进一步的优化空间
• 通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

分页查询优化

table表id,A,B,C, 索引是（A,B,C），select * from table where A = x limit 300000, 10

1. 问题表现

这条sql存在**深度分页问题**（offset），查询性能差。

2. 底层原理（核心：回表导致性能损耗）

​ 深度分页时，MySQL 会扫描所有满足条件的记录。使用二级索引查找时，叶子节点仅存主键和索引列，需用通过主键再查主键索引，获取完整数据。这样导致大量随机 I/O，查询性能极差；同时资源利用率极低，最终仅返回少量结果，前面扫描的数据全部丢弃。

3. 解决方案

方案一：延迟关联（推荐）

核心：采用延迟关联，子查询只查询主键，外层再查询完整信息，极大减少回表次数

SELECTt.*FROMtabletJOIN(SELECTidFROMtableWHERExxxLIMIT300000,10)AStmpONt.id=tmp.id;

方案二：记录上次 ID（业务最优）

核心：用主键 ID 条件替代大偏移量，利用主键索引直接定位数据起点。

-- id为自增主键，记录上一页最后一条数据的IDSELECT*FROMtableWHEREid>上一页最大IDLIMIT10;

锁机制

MySQL锁的分类

MySQL中的锁，按照锁的粒度分，分为以下三类：

• 全局锁：锁定数据库中的所有表
• 表级锁：每次操作锁住整张表
  • 表锁；
  • 元数据锁（MDL）：为了避免DML与DDL冲突，保证读写的正确性
  • 意向锁：目的是为了快速判断表里是否有记录被加锁
  • 自增锁（AUTO-INC ）：保证自增主键值唯一且连续
• 行级锁：每次操作锁住对应的行数据
  • 记录锁（Record Lock）：也就是仅仅把一条记录锁上；
  • 间隙锁（Gap Lock）：锁定一个范围，但是不包含记录本身；
  • 临键锁（Next-Key Lock）：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

间隙锁和临键锁(解决幻读)

查询条件字段没有索引时，InnoDB 会退化成全表扫描，加锁直接锁住全表所有临键区间（全表间隙锁 + 全表记录锁），相当于锁全表，任何写入都会阻塞。

间隙锁只管 “不能插新数据”，不管 “已有的数据能不能改 / 删”所以必须再配上记录锁，一起才能完整防止幻读 + 防止当前读被篡改。

MySQL InnoDB 临键锁/间隙锁 核心心法（最终4条·重点加粗版）

1. 加锁初始规则：按索引从左往右扫描，等值查询与范围查询默认先加临键锁，且覆盖完整范围。
2. 索引核心差异：唯一索引值唯一，允许锁退化；普通索引值可重复，需注意左右区间，且要对命中记录的主键加记录锁。
3. 边界命中判断：等值查询值、范围查询边界是否落在索引上，锁与退化规则不同。
4. 核心设计原则：在保证解决幻读的前提下，尽可能使用最小粒度锁，提升并发性能。

死锁的产生与解决

死锁的四个必要条件：互斥、持有且等待、不可强占用、循环等待。

解决方式：

1. 互斥：一般不破坏，锁资源需保证互斥性。
2. 持有并等待：一次性申请所有资源，或加锁超时后主动释放已持有的锁。
3. 不可剥夺：申请新资源失败时主动释放已有资源，或高优先级线程抢占低优先级资源。
4. 循环等待：对资源统一编号，线程按固定顺序申请资源。

日志与恢复

MySQL常见日志

答：

• undo log（回滚日志）：是Innodb存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
• redo log（重做日志）：是Innodb存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复;
• binlog：是Server层生成的日志，主要用于数据备份和主从复制；

其他核心问题

主从复制的原理

答：

​ MySQL 的主从复制依赖于binlog，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。

MySQL 集群的主从复制过程梳理成 3 个阶段：

• 写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
• 同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。(binlog dump 线程、IO线程)
• 回放 Binlog：回放 binlog，并更新存储引擎中的数据。（SQL线程）

原理图：

分库分表

1. 为什么要分单表数据超千万、读写慢、存储大、并发高。
2. 两种拆分方式
   1. 垂直分库：按业务拆（用户库、订单库）
   2. 垂直分表：按列拆（大字段拆分）
   3. 水平分库：同一张表按规则分到不同库
   4. 水平分表：同一张表按规则拆成多张表
3. 常见分片算法
   1. 范围分片：按 ID / 时间范围，适合区间查询
   2. 哈希分片：均匀分布，不适合扩容
   3. 一致性哈希：解决扩容问题，环 + 顺时针找节点
   4. 映射表分片、地理位置分片等
4. 分片键选择原则
   1. 覆盖大部分查询
   2. 数据分布均匀
   3. 值不轻易变更
5. 分库分表带来的问题（高频面试）
   1. 跨库无法JOIN
   2. 分布式事务
   3. 需要分布式 ID
   4. 跨库聚合（group by /order by）复杂
6. 主流方案
   1. 手动分库分表：ShardingSphere（Sharding-JDBC）
   2. 云原生 / 分布式数据库：TiDB（自动分库分表，无感扩容）

慢查询

慢查询优化整体分为四步：定位、分析、优化、复测。

定位：开启 MySQL 慢查询日志，设置时间阈值，记录执行耗时较长的 SQL，批量捕获慢语句。

分析：使用Explain解析执行计划，核心关注几个字段：

• type：代表查询类型，最差是 ALL 全表扫描，要尽量优化到 ref、range 级别；
• key：实际命中的索引；
• key_len：索引有效长度；
• rows：扫描行数，数值越大性能越差；
• Extra：额外信息，重点规避文件排序、临时表，优先出现覆盖索引。

优化： 优先优化 SQL 写法，遵循索引规范，避免索引失效； 合理建立联合索引，使用覆盖索引减少回表； 深度分页用主键分页、游标方案优化； 数据量大时做冷热分离、读写分离，必要时分库分表。

复测：优化后在测试环境压测验证，保证性能达标。

相关资料

推荐看一下资料进行深入学习！！！

Redis 常见面试题 | 小林coding

《mysql是怎样运行的》