面试题之数据库相关-mysql篇

最近开始面试了，410面试了一家公司针对自己薄弱的面试题库，深入了解下，也应付下面试。在这里先祝愿大家在现有公司好好沉淀，定位好自己的目标，在自己的领域上发光发热，在自己想要的领域上（技术管理、项目管理、业务管理等）越走越远！希望各位面试都能稳过，待遇都是杠杠的！

mysql进阶及业务相关设计问题可以查看：两篇博客会进行不定期更新，更新慢了请大家谅解，大家有想了解的面试题相关，可以评论下，我会尽快更新进去。希望在下个月能读透所有的技术问题，并找到合适的一个公司.面试题之数据库-mysql高阶及业务场景设计-CSDN博客

基础概念

1.MySQL是什么？

开源关系型数据库，支持SQL，使用存储引擎（如InnoDB）。

2.什么是关系型数据库？

关系型数据库（RDBMS）是一种基于关系模型的数据库管理系统，通过表格存储数据，并使用SQL进行数据的管理和查询。

3.SQL和MySQL有什么区别？

SQL 是操作数据库的标准语言，MySQL 是使用 SQL 的一种具体数据库软件。

对比项	SQL	MySQL
本质	结构化查询语言（Structured Query Language）	开源的关系型数据库管理系统（RDBMS）
作用	用于增删改查（INSERT/DELETE/UPDATE/SELECT）	存储和管理数据，支持 SQL 语法
是否依赖数据库	不依赖，所有关系型数据库通用（如 Oracle、PostgreSQL）	依赖，是 SQL 的一种实现
扩展性	纯语法，无额外功能	提供存储引擎（InnoDB）、事务、复制等特性

❌ “MySQL 是 SQL 的升级版” → 错！MySQL 只是 SQL 的一种实现。
✅ 所有关系型数据库都支持 SQL，但各有方言（如 MySQL 的LIMIT vs Oracle 的ROWNUM）

其中 MySQL包含了innodb和myisam两种存储类型。

4.innodb引擎4大特性

事务（ACID）
- 作用：保证数据操作的原子性、一致性、隔离性、持久性。
- 实现：通过undo log回滚、redo log崩溃恢复、锁机制和MVCC实现。
行级锁
- 作用：提高并发性能，减少锁冲突。
- 实现：
  - 记录锁（Record Lock）：锁单行。
  - 间隙锁（Gap Lock）：防止幻读，锁范围间隙。
  - 临键锁（Next-Key Lock）：记录锁+间隙锁组合。
外键约束
- 作用：保证数据完整性（如禁止删除有订单的用户）。
- 代价：可能降低写入性能（需检查约束）。
MVCC（多版本并发控制）
- 作用：实现非阻塞读（读写不互斥），提高并发。
- 实现：
  - 通过undo log保存数据历史版本。
  - 通过ReadView判断事务可见性（解决脏读、不可重复读）。
InnoDB其他重要特性
- 缓冲池（Buffer Pool）：缓存热数据，减少磁盘IO。
- 自适应哈希索引（AHI）：自动优化高频查询。
- 崩溃恢复：通过redo log保证数据持久性。
Q:如何通过LRU算法管理页缓存？
- 冷热分离架构
```
冷热分离
┌───────────────────────┐
│        New Sublist    │ (新生代，5/8容量)
│   [最近频繁访问的热页]  │
└───────────┬───────────┘│ 晋升阈值▼
┌───────────────────────┐
│        Old Sublist    │ (老生代，3/8容量)
│   [较久未访问的冷页]   │
└───────────────────────┘
```
  - 关键参数控制
  - 参数名默认值建议配置作用说明
    innodb_old_blocks_pct 37 20-40 老生代占比(%)
    innodb_old_blocks_time 1000 500-2000(毫秒) 冷页晋升到热区需停留的最短时间
  - 新页加载过程
    - 从磁盘读取的页首先插入到Old Sublist头部
    - 页面必须存活超过innodb_old_blocks_time毫秒
    - 再次被访问时，才可能晋升到New Sublist
  - 晋升规则
    - 普通查询：第二次访问且超过停留时间后晋升
    - 预读页面：必须被真实访问才会晋升（避免预读污染）
  - 全表扫描保护
    - ```
    -- 全表扫描时，页面不会立即污染热区
    SELECT * FROM large_table;  -- 大量页面加载到Old区但不晋升
```
- 页面淘汰机制
  - LRU列表维护
    - ```
    ┌───┐    ┌───┐    ┌───┐    ┌───┐
    │N1 │←→ │N2 │←→ │O1 │←→ │O2 │   [N=New, O=Old]
    └───┘    └───┘    └───┘    └───┘New Sublist：最近访问的页面靠近头部Old Sublist：长时间未访问的页面移向尾部
```
- 触发淘汰机制
  - 缓冲池空间不足时
  - 后台线程定期扫描(innodb_lru_scan_depth控制深度)
- 淘汰优先级
  - 先淘汰Old Sublist尾部的干净页
  - 然后淘汰New Sublist尾部的干净页
  - 最后触发脏页刷盘后淘汰
- 监控指标
  - 关键监控指标 LRU相关运行状态，性能视图
  - 优化全表扫描冷区停留时间，增加LRU扫描深度，增加硬件环境添加大内存
- 特殊场景
  - 预热缓冲池，启动时加载缓冲数据
  - 避免缓冲池污染，大数据量查询时临时调整冷热去配置

参数名	默认值	建议配置	作用说明
`innodb_old_blocks_pct`	37	20-40	老生代占比(%)
`innodb_old_blocks_time`	1000	500-2000(毫秒)	冷页晋升到热区需停留的最短时间

Q:脏页（Dirty Page）刷盘策略？

InnoDB的脏页刷盘机制是保证数据持久性的核心组件，同时也是性能调优的关键点。

主动触发场景：
- 当redo log文件写满时强制刷盘（约占总容量75%），即redo log空间不足
- 需要空闲页时优先刷脏盘，即缓冲池空间不足
- 根据innodb_flush_neighbors决定是否刷盘，即事务提交
- 定期将脏页刷新到磁盘，即检查点（checkpoint）
被动触发场景
- 执行完全刷盘（full purge），即服务器正常关闭
- 后台线程page_cleaner自动工作，即空闲时段
- 重启时通过redo log重做未刷盘的脏页，即系统崩溃恢复

刷盘流程

┌─────────────┐    ┌─────────────┐    ┌─────────────┐
│   Buffer    │    │   Double    │    │   Data      │
│    Pool     │───▶│   Write     │───▶│   Files     │
└─────────────┘    └─────────────┘    └─────────────┘▲                   ▲                  ▲│                   │                  │└───── Redo Log ────┘                  │(WAL机制)                        ││┌───────┴───────┐│ 保证原子写入   │└───────────────┘

刷盘的关键性参数

参数	默认值	建议值	作用说明
`innodb_max_dirty_pages_pct`	75	50-75	触发强制刷盘的脏页比例上限
`innodb_max_dirty_pages_pct_lwm`	0	10-25	后台刷盘启动的脏页比例下限
`innodb_io_capacity`	200	SSD:2000-4000	每秒I/O能力基准值(影响刷盘速度)
`innodb_io_capacity_max`	2000	SSD:4000-8000	最大突发I/O能力
`innodb_flush_neighbors`	1	SSD设为0	是否刷相邻脏页(机械硬盘有效)

刷盘优化策略
- 存储设备适配：开启相邻页刷盘优化，关闭相邻页刷盘，增加刷盘线程等
- 检查点优化：启动模糊检查点（8.0+默认），根据负载动态调整，设置低水位线
- 监控与调优：查看脏页状态，关注待刷盘数量，已刷盘页数，当前脏页比例
特殊场景处理
- 大事务提交优化：大事务前临时调整，执行批量操作是修改
- 快速关闭保护：紧急关闭时跳过完成刷盘，需注意可能丢失数据
- 奔溃恢复加速：调整恢复是的IO

5. 说一下 ACID 是什么？

一句话：事务是一组要么全部成功、要么全部失败的操作，保证数据从一种合法状态变为另一种合法状态

Atomicity（原子性）：事务是不可分割的最小单元，要么全部执行，要么全部回滚；实现：通过 undo log 记录修改前的数据，失败时回滚。
Consistency（一致性）：事务执行前后，数据必须满足预定义的规则（如余额≥0、外键约束）；实现：由应用层和数据库共同保障（如字段约束、触发器）
Isolation（隔离性）：并发事务之间互不干扰，防止脏读、不可重复读、幻读；实现：通过锁或 MVCC（多版本并发控制）实现不同隔离级别。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
Durability（持久性）：事务一旦提交，数据永久保存，即使系统崩溃也不丢失；实现：通过 redo log 记录修改，崩溃后重放日志恢复数据

ACID 的底层实现

特性	实现机制
原子性	`undo log`（回滚日志）
一致性	应用代码 + 数据库约束
隔离性	锁 + MVCC
持久性	`redo log`（重做日志）

6.说一下数据库的事务隔离？

MySQL 的事务隔离是在 MySQL. ini 配置文件里添加的，在文件的最后添加：

transaction-isolation = REPEATABLE-READ

可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。

READ-UNCOMMITTED：读未提交，允许读取其他事务未提交的修改（可能读到"脏数据"）。
READ-COMMITTED：只能读取其他事务已提交的修改（解决脏读）。
REPEATABLE-READ：同一事务内多次读取相同数据，结果一致（解决脏读、不可重复读）。
SERIALIZABLE：序列化，完全隔离，所有操作串行执行（解决所有问题）。

根据事务的隔离级别会产生三种数据异常情况：

脏读：即一个事务读取到别的事务未提交的数据，当事务级别为RU时会产生。

不可重复读：同一个事务内，同一行数据被修改（update），多次读取数据不一致，当事务级别为RC、RU时会产生。

幻读：同一个事务内，多次读取同一个数据集返回数据不一致(insert、delete)，当事务级别为RR、RC、RU时会产生。

7.什么是MVCC

MySQL中的 MVCC（Multi-Version Concurrency Control，多版本并发控制） 是一种无锁并发控制机制，用于在保证事务隔离性的同时提高读写并发性能。

作用
- 读写不阻塞：读操作不会阻塞写操作，写操作也不会阻塞读操作（非阻塞读）。
- 解决部分隔离性问题：实现READ COMMITTED（读已提交）和REPEATABLE READ（可重复读）隔离级别
核心实现原理
- 版本链（Undo Log）
  - 每条记录隐藏两个字段：
    - trx_id（事务ID）：最近修改该记录的事务ID。
    - roll_pointer（回滚指针）：指向旧版本数据的指针（通过undo log构建版本链）。
- ReadView（一致性视图）
  - 事务在读数据时生成一个快照，包含：
    - m_ids：当前活跃事务ID列表，且未提交的事务。
    - min_trx_id：最小活跃事务ID。
    - max_trx_id：系统预分配的下一个事务ID。
    - creator_trx_id：创建该ReadView的事务ID。
- 可见性规则
  - 判断记录版本是否对当前事务可见：
    1. 如果记录的trx_id == creator_trx_id → 可见（当前事务修改的）。
    2. 如果记录的trx_id < min_trx_id → 可见（已提交的事务修改的）。
    3. 如果记录的trx_id >= max_trx_id → 不可见（未来事务修改的）。
    4. 如果trx_id在m_ids中 → 不可见（未提交的事务修改的）；否则可见
  - 上述4中补充：如果 trx_id 在 m_ids 中，表示该事务还未提交，根据事务隔离级别要求，其他事务不应该看到未提交的数据。

为什么MVCC高效？
- 读操作无需加锁：通过版本链和ReadView判断可见性，避免读写冲突。
- 写操作仅锁行：不影响其他事务读旧版本数据。
ReadView与undo log的协作流程？

ReadView和undo log是InnoDB实现MVCC(多版本并发控制)的两个核心组件，它们的协作流程决定了事务如何看到一致的数据快照。

··核心组件

组件	存储内容	作用
ReadView	事务元信息	判断数据版本对当前事务的可见性
undo log	数据修改前的历史版本	构建记录的多版本链，存储被修改前的数据

··协作流程详情

···ReadView创建时机

读已提交(RC)：每次查询都会生成新的ReadView

可重复读(RR)：事务中第一次读操作生成ReadView，后续复用

·MVCC在RR隔离级别下如何解决幻读问题

事务开始时创建一致性视图(read-view)
整个事务期间使用同一个快照
通过版本链判断数据对当前事务的可见性

具体机制：

快照读(Snapshot Read)
- 普通 SELECT 使用 MVCC 快照
- 事务内看到的数据保持一致，不受其他事务影响
当前读(Current Read)
- 对于 SELECT...FOR UPDATE、UPDATE、DELETE 等操作
- InnoDB 会添加间隙锁(Gap Lock)锁定索引记录间的间隙
- 防止其他事务在锁定范围内插入新数据
注意：

只有在 REPEATABLE READ 隔离级别下，MVCC 结合间隙锁才能有效防止幻读
纯 SELECT 查询(快照读)依靠 MVCC 防止幻读
锁定读(如 SELECT FOR UPDATE)依靠间隙锁防止幻读
在特殊情况下(如混合使用快照读和当前读)，仍可能出现幻读现象

8.InnoDB和MyISAM的区别？

1>.InnoDB支持acid事物，而MyISAM不支持事物
2>.InnoDB支持行级锁，而MyISAM支持表级锁
3>.InnoDB支持MVCC, 而MyISAM不支持
4>.InnoDB支持外键，而MyISAM不支持
5>.InnoDB不支持全文索引，而MyISAM支持。
6>.innodb内存中建立缓冲池，用于缓冲数据和索引所以启动比较慢，而myiasm启动较快。
7>.InnoDB不会保存表的行数，select count(*)时会统计扫描全表，而myisam会保存表的行数。
8>.innodb高并发场景下写的速度比较快，而myisam读数据的速度比较快。

9.myisam和innodb他们两个select count(*)哪个更快，为什么

MyISAM快，因为直接维护表的总行数在元数据中，COUNT(*) 无需扫描表，直接返回该值。所以myisam适合读密集且无需事务的场景（如日志分析、数据仓库）;而InnoDB由于支持事务和MVCC，不同事务可能看到不同的数据快照，无法缓存精确的行数。

Q:count(1) count(*) count(fileld)区别

count(1)：统计表中的数量，包含所有行，不论行对应的值是啥，功能及效率跟count(*)类似
count(*)：统计表中的数量，包含所有行，不论值是否为null
count(fileld)：统计制定fileld列中非null值的行数

Q:如何提升count(*)性能？

根据业务数据量和过滤条件来分析，若业务需要统计结果不需要特别精确的条件下，可以通过explain关键字看filter大概多少行，或者通过查询系统表 information_schema.tables
使用explain分析sql查看是否有优化的余地，最好能走主键索引
分批查询或者数据分片汇总统计：若查询的结果集很大，考虑分批进行，每次查询一部分数据，然后累加。当数据量大的时候分库存储或者分节点存储提高查询性能
使用缓冲或者直接新建统计表：若果查询结果不需要实时更新，可以将结果更新在缓冲中，避免每次count(*)。

10.数据库的三范式是什么？

三范式是设计关系型数据库的规范，用于减少数据冗余和避免异常。核心思想：一张表只做一件事。

第一范式：强调的是列的原子性，数据库表的每一列都是不可分割的原子数据项。即字段是原子的（不可再分）
第二范式：要求实体的属性完全依赖于主关键字，所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。即表必须有主键，且非主键字段完全依赖主键（消除部分依赖）
第三范式：任何非主属性不依赖于其它非主属性。即非主键字段不能传递依赖主键（直接依赖主键，不依赖其他非主键字段）

Q：为什么需要三大范式？

减少冗余：避免重复存储（如学生表中存储学院地址存100次）。
避免异常：
- 更新异常：修改一处需改多处（如学院地址变更）。
- 插入异常：无法单独插入学院信息。
- 删除异常：删除学生数据连带删除学院信息
反范式化（例外）

场景：为提升查询性能（如数据分析），故意冗余数据（如订单表直接存客户姓名）

11. 一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 MySQL 数据库，又插入了一条数据，此时 id 是几？

表类型如果是 MyISAM ，那 id 就是 8。
表类型如果是 InnoDB，那 id 就是 6。

InnoDB 表只会把自增主键的最大 id 记录在内存中，所以重启之后会导致最大 id 丢失。

Q:其他可能导致自增id不连续的原因：

唯一冲突，当定义A字段为唯一字段，且对该表插入已存在的数据（插入前数据为3条），则会因为唯一冲突导致数据无法插入，但是自增值不会回滚回去，下一次插入数据后，id不会为4而是直接变成5；
事务回滚，当事务提交前发生了回滚，已经获取到的自增值不会因此回滚
批量新增，mysql对批量新增提供自增ID为翻倍问题，第一次申请时，分配1个ID，第二次申请时，分配2个ID，第三次则为4个，以此类推，每次返回为上一次的两倍。但是由于数据量不会可能刚好小于分配的个数，这些就会浪费掉，比如申请三次，一共分配了7个id但是只有5条数据，则剩下的两个ID不会回滚，下一次自增是为8.

12. 如何获取当前数据库版本？

使用 select version() 获取当前 MySQL 数据库版本。

13. char 和 varchar 的区别是什么？

「char(n)」 ：固定长度类型，不足补空格，设CHAR(5) 存'abc' -> 'abc '占用5个字节。它的读取速度快，但浪费空间，适合长度固定的数据如MD5加密、性别等。底层实现：直接分配定义的长度（如CHAR(10)始终占10字节），检索时自动去除尾部空格。
「varchar(n)」 ：可变长度，按需存储，设varchar(10) 存'abc' -> 'abc'占用3个字节+长度标识。但它的读取速度稍慢，但节省空间，适合长度不定的数据如用户名、地址等。底层实现：存储格式：长度标识（1-2字节） + 实际数据。最大长度受限于定义和字符集（如UTF-8下每个汉字占3字节）

所以，从空间上考虑 varcahr 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡。

PS:在mysql中varchar和char类型表示字符长度，而其他类型表示的长度都是表示字节长度

Q：CHAR(10)和VARCHAR(10)存"abc"占多少字节？

CHAR(10)：10字节（英文1字节/字符，补7空格）。
VARCHAR(10)：4字节（长度标识1字节 + "abc"3字节）。

Q：如何选择？

用 CHAR：数据长度完全一致（如身份证、手机号）。
用 VARCHAR：长度变化大（如评论、描述）。

14. float 和 double 的区别是什么？

float ：单精度，存储传感器数据、百分比等低精度需求，内存敏感场景（如海量数据存储），最多可以存储 8 位的十进制数，并在内存中占 4 字节。
double ：双精度，财务计算、科学实验等高精度需求，避免累计误差（如 SUM() 聚合时），最多可以存储 16 位的十进制数，并在内存中占 8 字节。

15. MySQL 的内连接、左连接、右连接有什么区别？

连接类型	关键字	结果特点	示意图	使用场景
内连接	`INNER JOIN`	只返回两表中匹配的行，取两个表中的交集	🔵 ∩ 🔴	需要严格匹配的数据（如查询“有部门的员工”）。
左连接	`LEFT JOIN`	返回左表全部行 + 右表匹配的行（不匹配的右表补`NULL`）	🔵 → 🔴	保留主表全部数据（如“所有员工及其部门，包括未分配部门的”），最常用
右连接	`RIGHT JOIN`	返回右表全部行 + 左表匹配的行（不匹配的左表补`NULL`）	🔵 ← 🔴	保留副表全部数据（较少用，通常用左连接替代）

16. MySQL 索引是怎么实现的？

索引是为了在大数据量下，加速查询操作的一种机制，底层通过特定的数据结构（如B+树）组织存储的数据已提交检索效率。

索引的类型：聚簇索引和非聚簇索引。

特性	聚簇索引	非聚簇索引(二级索引)
数据存储方式	索引即数据，叶子节点存储完整记录	索引与数据分离，叶子节点存储主键值/指针
数量限制	每表只能有一个	每表可创建多个
查询效率	主键查询极快	需要回表查询，稍慢
存储引擎	InnoDB必有一个聚簇索引	MyISAM只有非聚簇索引
物理排序	数据按索引顺序存储	不影响数据物理存储顺序
存储内容	存完整数据行（InnoDB的主键索引）	存主键值（需回表查数据）
是否回表	否	是

mysql中常见的索引数据结构有：B+树索引（大多数场景），哈希索引（适合完全匹配查询），全文索引（适合查找文本中的关键字），R树（空间数据索引，主要用于地理数据存储）,二叉树

MySQL 索引的核心实现原理是 B+树数据结构，innodb和myisam默认使用B+树索引，其中myisam数据和索引分开存储。索引优势：将随机 I/O 变为顺序 I/O，大幅减少需要扫描的数据量，支持快速排序和分组操作；索引劣势：占用额外存储空间，增删改操作需要维护索引结构，不合理的索引反而会降低性能。

B+ 树索引的核心特点（为何选择B+树作为默认索引）

多路平衡查找树：
·B+ 树的每个节点最多可以有多个子节点(称为 M 阶)，相比二叉树，其高度更低，可减少磁盘 I/0 次数。

·B+树始终保持平衡，每次插入或删除数据时都会调整结构（所以为啥选择自增ID不选择uuid），保证树的高度最低。

B+树更适合磁盘存储：

·B+树的设计特性减少了磁盘 I/O: 每个节点存储多个关键字(而不是单个关键字)，从而减少了树的高度。
·磁盘每次读取的是一个页(Page)，B+树节点的设计使每个节点大小刚好等于一个磁盘页的大小，充分利用磁盘 I/O。

支持范围查询（有序性）：

·B+树的所有关键字都存储在叶子节点(不会出现在非叶子节点)，且所有叶子节点通过指针连接形成有序链表，叶子节点通过有序链表相连，可以高效地支持范围查询(如 BETWEEN 和查询区间)。而普通的 B 树中，关键字分布在内节点和叶子节点，无法高效支持范围查询

磁盘读取效率高：

·B+树非叶子节点只存储索引信息，每个节点的大小更小，内存可以一次加载更多非叶子节点，从而减少磁盘读取次数，(B树非叶子节点会存储数据）

层级低：通常 3-4 层就能存储海量数据（百万级数据只需 3 次磁盘 I/O）
数据聚集：InnoDB 中主键索引的叶子节点直接存储完整数据行

B+树索引的特性

特点	优势	示例
多叉平衡树	层级少（3-4层可存百万数据）	100万数据只需3次IO
叶子节点有序	支持高效范围查询（>、<、BETWEEN）	`WHERE id > 100 AND id < 200`
数据存叶子	非叶子节点只存键，减少树高度
双向链表	快速范围扫描

InnoDB 和 MyISAM 的索引实现差异

特性	InnoDB	MyISAM
主键索引	聚簇索引（数据与索引一体）	非聚簇索引（数据与索引分离）
二级索引	存储主键值（需回表）	存储数据指针
数据文件	.ibd 文件（索引+数据）	.MYD（数据）+ .MYI（索引）
事务支持	支持	不支持

为何不选择其他索引？

二叉树索引：

深度问题：二叉搜索树在数据倾斜的情况下会退化成链表，查询效率变慢（复杂度O（n））

磁盘利用率低：每个节点只存储一个关键字，树的高度较高，而数据库访问磁盘IO是耗时操作二叉搜索树会增加磁盘的读取此书。

哈希索引：

缺乏范围查询能力：哈希索引只适合精确匹配，无法用于范围查询（如between、小于等条件）

无序特征：哈希索引不存数据的排列顺序，因此也不能用于order by 场景

冲突问题：若发生哈希冲突，检索效率会降低

B树：

数据分布问题：B树的索引值既存储在叶子节点中，也存储在内节点中，非叶子节点也需要存储数据占用更多磁盘IO。

范围查询效率低：B树的非叶子节点不构成有序的链表，范围查询更需要复杂的算法

扫描性能较差：B树缺乏类似B+树的叶子节点顺序节点链表结构，扫描效率不如B+树

跳表：

内存索引为主：跳表更多用于内存存储中的KV数据库（如redis）,对于磁盘存储而言，跳表的IO层次劣于多叉树。

磁盘仓文效率低：跳表的查询对磁盘空间分割不如B+树高效。

插入/删除时如何分裂合并节点？

1）插入流程：

从根节点开始查找合适的叶子节点
如果叶子节点有空间(未满)，直接插入
如果叶子节点已满，触发分裂：
- a. 创建新叶子节点
- b. 原节点保留前50%数据
- c. 新节点存放后50%数据
- d. 向上更新父节点指针
若父节点也满，递归分裂直到根节点

示例：

-- 假设页面容量为4条记录，插入键值7
分裂前叶子节点: [5, 6, 8, 9]
分裂后:
叶子节点A: [5, 6]
叶子节点B: [7, 8, 9] -- 新插入的7
父节点更新: [6, 8] -- 新增分割点

优化策略：

顺序插入优化：当检测到连续插入时，会预留1/16空间避免频繁分裂
批量加载：ALTER TABLE...DISABLE KEYS可加速大量数据导入

2）删除流程：

定位到包含目标记录的叶子节点
标记记录为"已删除"(逻辑删除)
检查页面填充率：
- a. 若页面利用率<50%，考虑合并
- b. 优先与左兄弟节点合并
- c. 若左兄弟不可用，尝试右兄弟
更新父节点指针

示例：

-- 删除键值6后，页面利用率低于阈值
合并前:
叶子节点A: [5] (利用率25%)
叶子节点B: [7, 8, 9]
合并后:
叶子节点A: [5, 7, 8, 9]
父节点更新: [8] -- 删除原分割点6

空间回收：

物理删除：后台purge线程真正释放空间
合并延迟：非立即执行，避免频繁合并/分裂震荡

Q:mysql中聚簇索引就是唯一索引吗？

A：唯一索引为聚簇索引，反之不一定成立，因为聚簇索引需要唯一标识每一行，一个表中有多个唯一索引但只能有一个聚簇索引。当一个表建立了主键，则会将主键作为聚簇索引，（建表时默认需要建立主键加速查询），而没有建立主键索引时，他会先找第一个创建唯一索引且值非null的作为聚簇索引，而这个条件也不存在时，InnoDB会自动创建一个名为GEN_CLUST_INDEX的隐藏聚簇索引，这个隐藏索引使用6字节的rowid作为行标识符隐藏在表中，作为聚簇索引。

性能影响

聚簇索引优势：
- 数据按聚簇索引物理排序存储
- 通过聚簇索引访问数据最快(无需回表)
非聚簇唯一索引：
- 其他唯一索引是二级索引，需要额外查找
- 查询时需要先查二级索引再回表查聚簇索引，通过聚簇索引获取数据

最佳实践

显式定义主键而非依赖隐式rowid
主键应满足：
- 自增(避免随机插入导致页分裂)
- 简短(通常用INT/BIGINT而非字符串)
- 业务无关(避免因业务变化需要修改主键)
避免使用UUID等随机值作为聚簇索引，这会导致严重的写入性能问题

17. 怎么验证 MySQL 的索引是否满足需求？

EXPLAIN 分析：检查type、key、rows列情况。
- 检查 type 列：const/eq_ref/ref 为佳，ALL 表示全表扫描
- 检查 key 列：确认使用了预期索引
- 检查 rows 列：估算扫描行数应尽可能少
- 其他列说明：
  - id：查询唯一标识符，从1开始递增，如果有子查询会显示多个id
  - select_type：查询类型分成simple（简单查询）、primary key(主键）subquery(子查询）
  - table:查询所涉及的表，如果有多个表会显示多行
  - partitions:表分区情况
  - type:查询所有使用的访问类型：
    - const:只会基于主键或者唯一索引查看一行
    - eq_ref：基于主键或者唯一索引连接两个表
    - ref:将基于非唯一索引连接两个表
    - range:将使用非唯一索引搜索一系列行
    - index:将扫描整个索引（避免使用）
    - all:将扫描整个表（避免使用）
  - possible_keys：表示可能在查询中使用的索引
  - key:表示时机在查询中使用的索引
  - key_len:表示索引字段的长度
  - ref:显示对哪个列或者常量，索引被使用
  - rows:表示需要扫描表的行数估计值，也就是说需要对表中多少行进行处理来返回结果集
  - filtered:表示在获取结果后，锁过滤的行数的百分比，这个值就是执行where条件后，剩下与原数据量对比，越小越好
  - extra：sql执行查询的一些额外信息。例如是否使用临时表，使用那些索引覆盖查询等
性能对比测试
- 记录查询在有索引和无索引情况下的执行时间差异
- 使用 BENCHMARK() 函数或专业压测工具
索引使用统计
- 查看索引使用频率和效率
索引选择性分析
- 选择性 > 0.1 的列才适合建索引冗余索引检测 sql
慢查询日志分析 sql 复制

---- 启用慢查询日志SET GLOBAL slow_query_log = 'ON'; 
SET GLOBAL long_query_time = 1;

Performance Schema 监控

索引优化策略

优先 WHERE、JOIN、ORDER BY 子句中的列
使用覆盖索引避免回表
定期维护
- -- 更新统计信息 OPTIMIZE TABLE users;
- -- 重建表整理碎片
可视化工具 MySQL Workbench 可视化执行计划 Percona PMM 监控索引使用情况

18. 说一下 MySQL 常用的引擎？

InnoDB 引擎：mysql 5.1 后默认的数据库引擎，提供了对数据库 acid 事务的支持，并且还提供了行级锁和外键的约束，它的设计的目标就是处理大数据容量的数据库系统。MySQL 运行的时候，InnoDB 会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎是不支持全文搜索，同时启动也比较的慢，它是不会保存表的行数的，所以当进行 select count(*) from table 指令的时候，需要进行扫描全表。由于锁的粒度小，写操作是不会锁定全表的，所以在并发度较高的场景下使用会提升效率的。
MyIASM 引擎：不提供事务的支持，也不支持行级锁和外键。因此当执行插入和更新语句时，即执行写操作的时候需要锁定这个表，所以会导致效率会降低。不过和 InnoDB 不同的是，MyIASM 引擎是保存了表的行数，于是当进行 select count(*) from table 语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的，可以将 MyIASM 作为数据库引擎的首选。

19. mysql中锁的分类？说一下 MySQL 的行锁和表锁、页锁？

按颗粒度来分：表锁、行锁、页数。
按类型来分：读锁、写锁（都属于悲观锁）
按性能来分：乐观锁、悲观锁、意向锁
MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。
- 表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。
- 行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。
- 页锁：只有在BDB存储引擎支持页锁，页锁就是在页的力度上进行锁定，锁定的资源介于行锁与表锁之间。

20. 说一下乐观锁和悲观锁？如何实现？

乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。
悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。

数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。

21.innodb锁的粒度

类型	描述	SQL示例
记录锁	锁定单行记录	`WHERE id = 1 FOR UPDATE`
间隙锁	锁定索引记录间的间隙	`WHERE age BETWEEN 20 AND 30`
临键锁	记录锁+间隙锁组合	默认行锁行为
意向锁	表级锁，快速判断表是否被锁定	自动添加
插入意向锁	INSERT操作设置的间隙锁变种	插入时自动添加

22.特殊锁机制

自增锁 (AUTO-INC Lock)
- 专门用于自增列插入操作
- MySQL 8.0优化为轻量级原子操作
谓词锁 (Predicate Lock)
- 空间索引专用锁
- 解决GIS数据并发问题
元数据锁 (MDL)
- 保护表结构变更

23. MySQL 问题排查都有哪些手段？

使用 show processlist 命令查看当前所有连接信息。
开启慢查询日志，查看慢查询的 SQL。
使用 explain 命令查询 SQL 语句执行计划，通过key、type、rows查看是否走索引、扫描行数等。

24.如何优化慢查询？SQL调优？

避免SELECT *：浪费cpu、内存资源；增加网络消耗；增加解析器成本；不走索引
小表驱动大表：减少数据计算量，提交查询效率
用连接查询代替子查询：利用数据库索引，提高查询性能
提升group by效率：给高频分组字段添加索引，减少子查询，用连接查询join或者exists代替子查询in，缩小数据范围
批量操作：减少与数据库交互的次数提升性能
使用limit:提高查询效率，避免过度提取数据，优化分页查询，简化查询结果
用union all 代替union:更快的查询速度，更精准的数据结果，union all不去重速度快
join表不宜过多：查询效率下降，系统负载增加，维护难度加大
- 总结：减少数据扫描，返回更少的数据，减少交互次数，减少服务器CPU及内存开销

Q:如何解决深度分页问题

A：MySQL通过limit关键字来实现分页查询，语法结构如下：

select column_nmae(s) from table_name limit offset,row_count;

其中，offset标识其实偏移量，row_count标识要返回的行数，在执行select查询时，mysql首先会扫描整个表或者使用索引，找到所有符合where条件的记录。这个过程需要将所有记录都读入内存，然后根据limit自居的制定返回查询结果集中的一部分。

eg:从用户表中查询20001条数据开始返回10条数据

select * from user limit 20001,10

相同偏移量不同数据

从结果集来看数据量越大消耗的时间越多

不同偏移量，相同数据量

从上面的结果来看偏移量越大，消耗时间越多

数据量越大的优化方案
- 明确查询字段，避免使用select * ，减少mysql优化器负担
  - eg:select id,name,age from user limit 10000,10
- 按需查找字段，减少网络IO消耗
  - select id,name from user limit 10000,10
- 查询字段索引覆盖，通过辅助索引提升查询效率。先查询主键ID 在二次查询需要的信息
- 借助nosql缓存数据缓解mysql数据压力，将数据缓存到中间件中。
偏移量大的优化方案
- 偏移量大的场景我们可以使用数据量大的优化方案，除此之外改成使用id限定方式提升查询效率。
  - eg:增加索引where条件查询，缩减数据库范围：select * from user where id>10001 limit 100;

25. 如何做 MySQL 的性能优化？

为搜索字段创建索引。
避免使用 select *，列出需要查询的字段。
垂直分割分表。
选择正确的存储引擎。

Q:LIKE模糊查询如何优化？

A：like的模糊查询会导致性能降低，尤其是通配符%

合理的使用索引：前缀匹配最左原则
使用反向索引：需要后端匹配可以创建一个辅助列来存储反向字符串，并基于此列进行前缀匹配
限制扫描范围：加上限制条件，例如创建时间、性别等条件
使用缓存：若查询频繁可以通过应用层的缓存来处理减小数据库的负载
使用专业工具：通过elasticsearch、solr或者sphinx来代替mysql的like操作

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

多个事务互相等待对方释放锁。 如何避免：按固定顺序访问表、减小事务粒度、设置超时。

Q:死锁的检查原理？

等待图算法 (Wait-for Graph)
- InnoDB 会维护一个事务等待图，记录所有事务的资源请求和等待关系
- 当出现以下情况时触发检测：
  - 事务请求锁时需要等待超过 innodb_lock_wait_timeout（默认50秒）
  - 周期性检测（由参数控制）
检查过程：

graph LR
A[事务T1持有行R1的锁] -->|等待R2| B[事务T2持有行R2的锁]
B -->|等待R1| A

Q:死锁如何处理？

自动检测
- 通过深度优先搜索(DFS)算法检测等待图中的环
- 检测到死锁后，会选择"代价最小"的事务作为牺牲者(victim)
选择牺牲者的标准
- 默认选择回滚代价最小的事务（修改数据量最少的事务）
- 可通过 innodb_deadlock_detect 参数控制是否启用检测（默认ON）
处理方式
- 自动回滚被选中的事务
- 返回 1213 错误代码 (ER_LOCK_DEADLOCK)
- 其他事务可以继续执行
关键参数

参数名	默认值	说明
`innodb_deadlock_detect`	ON	是否启用死锁检测
`innodb_lock_wait_timeout`	50s	锁等待超时时间
`innodb_print_all_deadlocks`	OFF	是否打印所有死锁信息到错误日志

死锁查看的方法

查看最近一次死锁信息

SHOW ENGINE INNODB STATUS\G
在输出中查找 LATEST DETECTED DEADLOCK 部分

开启所有死锁日志记录

SET GLOBAL innodb_print_all_deadlocks=ON;
信息会记录到MySQL错误日志中

Q:如何避免死锁？

用层优化
- 保持事务短小精悍
- 按照固定顺序访问表和行（如按ID排序）
- 避免大事务
数据库设计优化
- 合理设计索引减少锁范围
- 考虑使用较低的隔离级别（如RC）
- 对于高并发场景，可以临时关闭死锁检测(SET GLOBAL innodb_deadlock_detect=OFF)
监控与预警
- 监控死锁发生频率
- 设置适当的锁等待超时时间

27.什么是覆盖索引？

覆盖索引是指一个索引包含了查询需要的所有字段，使得查询只需扫描索引而无需回表获取数据行。通过通过EXPLAIN查看Extra列，查看是否需要回表。

性能提升：减少I/O操作（只需读取索引），索引通常比数据行小，可缓存更多内容
使用建议：索引字段不宜过多（建议≤5个），TEXT/BLOB等大字段不适合建索引，频繁更新的列会导致索引维护成本高

28.主从复制原理？

MySQL主从复制是基于二进制日志(Binary Log)的异步复制过程，主要分为以下三个阶段：

主库写入阶段
- 二进制日志转储线程（binlog dump thread）,当从库线程连接的时候，主库可以将二进制日志发送给从库，当主库读取事件（event）的时候，会极为短暂的在binlog上加读锁，读取完成之后，在释放锁。主库记录二进制日志 binlog中。
从库读取阶段
- 从库I/O线程，从库上有一个IO线程，负责连接主库并发送读取二进制日志请求更新binlog，并写入到中继日志（relay log）中
从库应用阶段
- 从库SQL Thread会读取从库中的中继日志，并执行日志的事件，重放relay log，将从库的数据与主库保持同步。

组件	作用	关键参数
主库Binlog	记录所有数据变更事件	binlog_format=ROW/STATEMENT
Binlog Dump线程	读取binlog发送给从库	server_id
从库I/O线程	接收binlog写入relay log	relay_log
从库SQL线程	重放relay log中的事件	slave_parallel_workers
从库Relay Log	临时存储从主库接收的binlog事件	relay_log_purge

如何解决主从延迟问题：

并行复制

-- 启用基于组提交的并行复制
SET GLOBAL slave_parallel_workers=8;
SET GLOBAL slave_parallel_type='LOGICAL_CLOCK';

半同步复制

-- 确保至少一个从库接收日志
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

监控手段

SHOW SLAVE STATUS\G
-- 关注：
-- Seconds_Behind_Master
-- Slave_SQL_Running_State

29.分库分表怎么做？

水平拆分（横向扩展）

原理：将同一表的不同行分散到不同库/表中
适用场景：数据量大但表结构简单的场景（如用户表、订单表）

垂直拆分（纵向扩展）

原理：将不同列拆分到不同表
适用场景：表字段多且访问模式不同（如用户基础信息与用户详情）

分片路由方案：哈希分片、路由分片、目录分片

30.分表会产生哪些问题？如何解决？

SQL操作复杂度问题：
- Q:需要修改所有的SQL语句，增加表名后缀，跨表查询变得复杂，聚合统计困难
- A:中间件ShardingSphere-JDBC自动路由，视图封装，应用层抽取
分布式ID问题：
- Q:自增ID冲突，分页查询出现重复数据
- A：雪花算法、分布式序列服务、UUID优化方案
跨表查询性能问题：
- Q：多表union效率低下，排序分页性能差
- A:并行查询，结果集缓冲，预聚合方案
数据分布不均问题：
- Q:某些表数据量远大于其他表，热点数据集中
- A：动态分表策略，热点数据特殊处理，自动平衡机制
事务一致性问题：
- Q:跨表事务无法保证原子性，部分成功部分失败
- A:本地事务+消息队列实现最终一致性，TCC模式，SAGA模式
运维复杂度问题：
- Q:DDL变更困难，数据迁移复杂，监控难度大
- A:自动化工具链pt-online-scheme-change，统一监控平台，标准化流程
最佳实践：
- 拆分粒度控制：单表控制500万-5000万行，分表数量控制64个以内。
- 先垂直后水平
- 保留全量视图

31.分库会产生哪些问题？如何解决

事务一致性问题：
- Q:跨库事务无法保证ACID特性，部分成功部分失败导致数据不一致。
- A：使用分布式事务框架解决，保证数据最终一致性（消息队列+本地事务表，定时任务补偿机制）
跨库JOIN问题：
- Q:多表关联查询性能急剧下降，复杂SQL无法直接执行。
- A：对数据进行冗余，设置全局表，应用层拼接等方案。
全局ID生成问题：
- Q:自增ID冲突，分页查询出现重复数据。
- A：使用雪花算法、号段模式、UUID等方式处理。
数据倾斜问题：
- Q:某些分片数据量/访问量远高于其他，热点数据导致单点瓶颈。
- A：使用动态分片策略，根据数据特征动态路由、热点数据单独处理、一致性哈希算法。
运维复杂度问题：
- Q:监控难度大，数据迁移和扩容困难。
- A：统一管理平台，使用Shardingsphere-proxy集中管理，搭建可视化运维面板、标准化扩容流程，新节点数据初始化，路由配置热更新，数据自动再平衡、全路由监控。
最佳实践分析：
- 渐进式拆分，先分表在分库，先读写分离在分片。
- 容量规划：单库建议容量：数据量<1TB,表数量<500，单表行数<5000万。
- 回滚方案：保留双写机制，设计降级策略。

32.雪花算法（Snowflake）的优缺点？如何解决时钟回拨？

优点：
- 高性能：
  - 本地生成无网络开销（相比UUID、数据库自增等方案）
  - 单机每秒可生成400万+ ID（理论值）
- 趋势递增：
  - 时间戳在高位，整体ID随时间递增
  - 适合作为数据库主键（减少B+树分裂）
- 分布式友好：
  - 通过工作节点ID实现分布式部署
  - 无需中心化协调服务
- 信息隐含：
  - ID本身包含时间戳、节点信息
  - 可通过解析ID获取生成时间和节点
缺点
- 时钟回拨问题：
  - 服务器时钟回调会导致ID重复
  - 极端情况可能造成服务不可用
- 节点数量限制：
  - 最多支持1024个节点（10位）
  - 超大规模部署需要改造
- 时间依赖：
  - 强依赖系统时钟的准确性
  - NTP同步可能导致时钟跳跃
- 配置复杂度：
  - 需要手动或通过外部系统分配节点ID
  - 容器化环境部署较复杂
解决方案
- 轻度回拨（毫秒级）
- 解决方案：等待时钟追平

// 伪代码示例
long currentMillis = System.currentTimeMillis();
if (currentMillis < lastTimestamp) {long offset = lastTimestamp - currentMillis;if (offset <= 5) {  // 允许5ms内的回拨Thread.sleep(offset);currentMillis = System.currentTimeMillis();} else {throw new ClockMovedBackwardsException();}
}

中度回拨（秒级）
- 解决方案：使用扩展位
  - 增加1-2位回拨标志位
  - 发生回拨时设置标志位并继续生成ID
  - 解析ID时根据标志位进行时间补偿
严重回拨（无法自动恢复）
- 解决方案：
  - 报警人工干预：
    - 记录错误日志并触发告警
    - 人工检查NTP服务或服务器时间
  - 备用时间源：
    - 维护一个独立的时间序列服务
    - 回拨时从备用源获取时间
  - 节点冻结：
```
// 伪代码：暂停节点服务
if (currentMillis < lastTimestamp) {nodeService.disableNode(nodeId);alertAdmin("Clock rollback detected, node disabled");
}
```
美团Leaf方案改进
- 美团在Leaf-snowflake中的优化：
- ZooKeeper持久顺序节点：解决workerID分配问题
- 分段缓存：提前生成ID缓解时钟回拨影响
- 时钟监控：实时检测时钟异常

最佳实践

节点ID分配：
- 使用配置中心或数据库分配workerID
- 容器环境可使用Pod IP哈希值

监控措施：

prometheus

# 时钟偏移监控
- alert: ClockDriftexpr: abs(time() - node_time_seconds) > 5for: 5m

降级方案：
- 准备UUID或数据库序列作为备用方案
- 时钟异常时自动切换

参数调优：

// 调整序列号位数分配（根据业务需求）
private final long sequenceBits = 12;  // 默认12位
private final long workerIdBits = 10; // 可调整为8位，增加序列号位数

改进雪花算法的策略

变种名称	改进点	适用场景
Sonyflake	时间单位改为10ms，增加序列号位数	高并发场景
Boundary Flake	128位ID，增加随机性	极高并发系统
Instagram改进版	增加分片信息	分库分表场景
MongoDB ObjectID	时间+机器+进程+计数器	文档数据库

33.MySQL主要日志类型？

日志类型	所属组件	主要作用	生命周期	内容特点	是否持久化
Binlog	MySQL Server	主从复制 + 时间点恢复	长期保留	SQL语句/行变更数据	是
Undo Log	InnoDB引擎	事务回滚 + MVCC支持	事务结束后可回收	数据修改前的版本	是
Relay Log	从库I/O线程	临时存储主库传输的binlog	应用后删除	与主库binlog相同	是

三大日志协作示例

事务执行过程
- 事务开始：记录Undo Log
- 执行更新：
  - 写Undo Log（旧值）
  - 修改内存数据页
  - 写Redo Log（prepare状态）
事务提交：
- Redo Log刷盘（commit状态）
- Binlog刷盘
- 释放Undo Log（非立即删除）
崩溃恢复流程
- 检查Redo Log的prepare和commit状态
- 对比Binlog最后位置
- 决定回滚（Undo Log）或重做（Redo Log)

Q:事务提交时，binlog和redo log的写入顺序？

A:事务提交时binlog和redo log的写入顺序是一个关键的二阶段提交过程（2PC），确保数据的持久性和主从一致性。

准备阶段（Prepare Phase）
- 先将事务的redo log写入到redo log buffer
- 然后调用fsync()将redo log持久化到磁盘（状态标记为prepare）
写入binlog阶段
- 将事务的SQL语句或行变更写入binlog cache
- 调用fsync()将binlog持久化到磁盘
提交阶段（Commit Phase）
- 再次写入redo log，将状态从prepare改为commit
- 此时事务才算真正提交完成

总结流程

```
事务执行 -> 
```

生成redo log(状态prepare)并刷盘 ->

```
生成binlog并刷盘 -> 
```
```
修改redo log状态为commit
```

为何要这么设计

数据一致性保证：
- 如果第2步binlog写入失败，由于redo log还是prepare状态，事务会回滚
- 如果第3步redo log提交失败，但binlog已写入，重启后会根据binlog恢复数据
崩溃恢复逻辑：
- MySQL重启时，会检查redo log中prepare状态但无对应commit记录的事务
- 然后检查这些事务是否有对应的binlog记录：
  - 如果有binlog，则提交事务（认为第3步失败了）
  - 如果没有binlog，则回滚事务（认为第2步失败了）

6.提交优化

在mysql5.6之前还是串行化提交，之后进行了组提交优化

现代MySQL还使用了组提交技术来优化过程：

将多个事务的redo log prepare阶段合并

然后将这些事务的binlog一起写入

最后批量将redo log标记为commit

这种优化显著减少了磁盘I/O操作次数。

Q:binlog有几种录入格式与区别？

A：mysql中的binlog有三种录入格式，分别为statemet、row、mixed三种格式。

statement格式：
- 将sql本身记录到binlog中
- 记录的是主库上执行的sql语句，从库通过解析并执行相同的sql来达到复制的目的。
- 简单易读且节省存储空间，但由于某些情况下，由于执行计划或函数等因素影响，相同的sql语句在主从库上执行的结果可能不一致，导致复制错误。
ROW格式
- 记录被修改的每一行数据的变化
- 不记录具体的SQL语句，而是记录每行数据的变动情况，如增删改操作的前后值
- 保证了复制的准确性，不收SLQ语句执行结果的差异影响，但相比statement格式，row占用更多的存储空间
mixed格式
- statement和row格式的结合，mysql自动选择适合的格式
- 大多是情况下使用statement格式进行记录，但对于无法保证复制的情况，如使用非确定性函数，触发器等，会自动切换到row格式进行记录
- 结合了两种格式，既减少了存储空间的占用，又保证了复制的准确性。
总结：
- statement格式适用于简单的sql语句存储
- ROW格式使用与精确的复制场景
- mixed格式是综合考虑两种格式的优势而出现的折中方案。

34.大表在线DDL操作如何避免锁表

1）原生方案：

通过ALGORITHM=INPLACE和LOCK=NONE参数实现
支持的DDL操作包括：添加索引、添加/删除列（部分情况）、修改列类型（部分情况）

不是所有DDL都支持online操作（如修改主键、修改字符集等仍会锁表）

需要额外的临时空间（约为表大小的1.3倍）

对主库性能仍有影响，大表操作耗时可能很长

2）第三方工具

工具名称	原理	优点	缺点
pt-online-schema-change	触发器+影子表	通用性强	需要触发器权限
gh-ost	模拟从库binlog	无触发器依赖	需要binlog格式为ROW
Facebook OSC	外部程序解析binlog	对主库影响小	配置复

Q:pt-online-schema-change工具的实现原理？

1) 工作流程（基于触发器同步）：

创建与原表结构相同的影子表（_tablename_new）
在影子表上执行DDL变更
创建3个触发器（INSERT/UPDATE/DELETE）保持数据同步
分批拷贝原表数据到影子表
原子性切换（RENAME TABLE原表为旧表，影子表为原表）
清理旧表（默认不自动删除）

graph TD
A[创建影子表] --> B[执行DDL变更]
B --> C[创建触发器]
C --> D[分批拷贝数据]
D --> E[数据一致性校验]
E --> F[原子切换表名]
F --> G[清理旧表]

2) 关键技术

数据同步机制：

INSERT触发器：将新插入的数据同时写入影子表
UPDATE触发器：将变更同步到影子表
DELETE触发器：从影子表中删除对应记录

数据拷贝策略：

使用INSERT LOW_PRIORITY IGNORE INTO...SELECT分批拷贝
通过--chunk-size控制每批数据量（默认1000行）
自动调节拷贝速度（基于服务器负载）

安全机制：

自动检测主从延迟（--max-lag）
负载过高时暂停操作（--max-load）
外键约束自动处理（--alter-foreign-keys-method）

pt-online-schema-change \
--host=localhost \
--user=root \
--password=xxx \
--alter="ADD COLUMN age INT, DROP COLUMN obsolete_col" \
D=test,t=big_table \
--execute

3）与gh-ost区别

特性	pt-online-schema-change	gh-ost
同步机制	触发器	binlog解析
需要SUPER权限	是	否
影响主库性能	较高（触发器开销）	较低
暂停/恢复	不支持	支持
进度显示	简单	详细
切换方式	原子RENAME	原子RENAME

Q:添加索引真的不会锁表吗？

A:在mysql5.5及之前的版本，通常更改数据表结构操作DDL会阻塞对标数据的增删改操作

online ddl：
- 在不中断现有的数据读写操作的情况下，自动执行DDL语句（eg:增删改等）的机制。online ddl可以在MySQL进行表空间或者数据文件的变化是，自动执行DDL语句，从而避免了传统方式中，执行DDL语句时对数据库读写操作的干扰和中断。
执行过程
- online ddl一般分成三个阶段：初始化阶段、执行阶段、提交表定义阶段
- 初始化阶段
  - 评估存储引擎能力与DDL语句
  - 评估ALGORITHM-算法和LOCK-锁
  - 创建可升级的MDL读锁
- 执行阶段：
  - 此阶段氛围两个步骤：准备和执行DDL语句
  - 此阶段是否需要MDL写锁取决于初始化阶段评估的因素。如果需要MDL写锁的话，仅在准备过程会短暂的使用MDL写锁，然后降级为MDL读锁
  - DDL执行过程（最耗时）
- 提交表阶段
  - 此阶段会将MDL读锁升级到MDL写锁，此阶段一般比较快，因此独占锁的时间也比较端
  - 用新的表定义替换旧的表定义，释放MDL锁
- 初始化阶段中的算法和锁
  - ALGORITHM：基础配置分为默认算法DEFAULT、在原表上进行更改INPLACE、最原始的方式复制COPY；
    - default:默认算法，使用最高效的算法
    - inplace:在原表上进行更改，不需要生成临时表，不需要进行数据cope的过程
      - 添加索引的步骤
        创建索引（二级索引）数据字典
        加共享表锁，禁止DML，允许查询
        读取聚簇索引，构建新的索引项，排序并插入新索引
        等待打开当前表的所有只读事务提交
        创建索引结束
    - copy:最原始的方式，通过临时表的创建索引，需要多一倍的存储，还有更多的IO（类似5.6版本之前的处理过程）
      - 添加索引步骤
        新建带索引（主键索引）的临时表
        锁原表，禁止DML，允许查询
        将原表数据拷贝到临时表
        禁止读写，进行rename，升级字典锁
        完成创建索引操作
  - LOCK锁，锁分为default默认、none无锁、shared共享锁、exclusive排他锁
    - default：默认方式，mysql自行判断哪种锁模式，尽量不锁表
    - none：无锁，允许online ddl期间进行并发读写操作。如果online ddl操作不支持对标的继续写入，则ddl操作失败，对表修改无效
    - shared：共享锁，online ddl操作期间阻塞写入，不影响读取
    - exclusive：排他锁，online ddl操作期间不允许对锁表进行任何操作
  - 不是所有的ddl都支持online ddl，具体还是需要看下官网
- 总结：在online ddl执行过程中会两次获取MDL锁，并且需要等待已经持有的DML锁的并发事务提交或者回滚后才能继续执行，在实际执行时需要注意：
  - 进行DDL操作时尽量在业务低峰期进行操作
  - 在操作之前最好确认对奥操作的表没有任何进行中的操作、没有未提交的事务、也没有显式事务中的报错语句
  - 设置超时时间lock_wait_timeout，避免长时间的metedata锁等待

35.SQL的7种进阶用法

平常使用时，我们只会进行增删改，了解下7种进阶用法

自定义排序（order by field）
- 正常用法：select id,name from user order by id;
- 自定义name排序：select id,name form user order by field(name,'admin','zbq',...)
空值NULL排序（order by if(is null)）
- 在mysql中使用order by关键字加上我们需要排序的字段就可以完成该字段的排序，如果字段中存在NULL就会对我们的排序结果造成影响。可以使用order by if(isnull(字段)，0，1)语法将NULL值转换成0或者1，实现NULL值排序到数据集前面还是后面
- 正常写法：select * from user order by age,name;-- 此时所有年龄为空的数据会放在前面
- NULL降序：select * from user order by if(isnull(age),1,0),age,name;
case表达式（case...when）
- 在实际开发中，我们经常会写很多if ... else if .... else ，这时候我们可以使用case ... when表达解决这个问题。比如筛选出绩效评分为90以上 60-90 和其他
  - select *,case when score > 90 then 'A',when score > 60 then 'B' ELSE 'D' end 'level' from staff_score ;
分组连接函数（GROUP_CONCAT）
- 分组连接函数可以在分组后制定字段的字符串连接方式，并且还可以制定排序逻辑，连接字符串默认为英文逗号，比如根据学生进行分组，将对应的学生、学科、分数降序排序，而学科之间通过'_'拼接。
  - 默认连接使用,：
    - select name,group_concat(subject) as subject,group_concat(score) as score from user_score group by name;
  - 自定义连接符
    - select name,group_concat(subject) as subject,group_concat(score order by score desc separator '_') as score from user_score group by name;
分组统计数据后在进行统计汇总(with rollup)
- 在mysql中可以使用with rollup 在分组统计数据的基础上在进行数据统计汇总，即将分组后的数据进行汇总
  - 对数据进行普通分组：select name,sum(score) from user_score group by name;
  - 在普通分组上合计所有数值：select name,sum(score) from user_score group by name with rollup;
子查询提取（with as）
- 如果一整句查询中多个子查询都需要使用同一个查询的结果，可以使用with as 将共同的子查询提取出来并取一个别名，后面查询语句可以直接用，对于大量复杂的sql语句起到了很好的优化作用。例如统计小明的学科分数在60以上但是小于90分的数据
  - with m1 as (select * from user_score where score > 60), m2 as (select * from user_score where score >=90) select * from m1 where m1.id not in (select m2.id from m2) and m1.name = '小明';
优雅处理数据插入、更新时的主键、唯一键值重复（ignore）
- 在mysql中插入、更新数据有时候会遇到主键重复的场景，通常的做法就是先进行删除在插入达到可重复执行的效果，但可能会出现误删。
  - 使用ignore遇到主键冲突时，跳过执行否则插入，这样不影响其他数据操作
    - inser ignore into user (id,name,age) values(1,'zbq',33);-- 成功
    - inser ignore into user (id,name,age) values(1,'zbq',35);-- 失败跳过
    - inser ignore into user (id,name,age) values(2,'zbq2',33);-- 成功
  - 使用relace 进行替换插入，若已经存在则替换（删除+插入）否则直接插入
    - replace into user (id,name,age) values(1,'zbq',33);-- 成功
    - replace ignore into user (id,name,age) values(1,'zbq',35);-- 成功
    - replace ignore into user (id,name,age) values(2,'zbq2',33);-- 成功
  - 更新数据使用on duplicate key update.它的作用就是当插入的记录遇到主键或者唯一键重复时，会执行后面定义的update操作。相当于先执行insert操作，在根据主键或者唯一键执行update操作，有就更新，没有就插入
    - insert into score (id,name,score ) values(1,'zbq',33) on duplicate key update age = score + 1;

36.删除时优先考虑物理删除还是逻辑删除

优先考虑逻辑删除：物理删除恢复数据困难，物理删除需要重建索引。

37.表设计经验总结

命名规范，表名字段名使用小写字母，禁止使用数字开头，禁止使用拼音，一般不适用英文缩写；主键索引命令pk_字段名，唯一索引uk_字段名，普通索引idx_字段名；使用驼峰或者下花心分割见名之意。
选择合适的字段类型
主键设计要合理，不要与业务关联
选择合适的字段长度
优先考虑逻辑删除不是物理删除
每个表都要有通用字段id,create_time,update_time,creator_id,updator_id,remark等
一张表的字段不宜过多，尽量控制在20以内
定义字段尽可能not null，防止空指针，null存储也要占用额外的空间，null可能导致索引失效
不需要严格遵守3NF，有些字段经常用的话，保留一些冗余字段可以提高查询效率
避免使用mysql保留字
不搞外键关联，一般在业务应用中处理，外键增删改时会有性能问题，限制数据库的灵活性，可能导致死锁和死循环，增加维护成本
字段需要注释，说清楚字段用途
时间类型选择，date、datetime、time、timestamp、year
总结：sql编写优化建议：
- 避免使用select * from 语句，应该只选择需要的列，减少网络传输和提高查询性能
- 使用索引来提高查询速度，特别是对大型表进行查询时
- 避免使用外键约束，因为可能导致性能问题，特别是对大表进行增删改操作
- 使用limit1来限制查询结果只有一条记录
- 避免where自居中使用OR来连接条件，应使用union来连接查询
- 注意优化limit深分页问题，可使用offset来代替limit
- 使用where条件限制查询的数据，避免返回多余的行
- 尽量避免在索引列上使用mysql的内置函数，可能导致索引失效
- 尽量逼民在where子句中对字段进行表达式操作，可能导致索引失效
- 应尽量避免在where子句中使用!=或者<>操作符，可能导致索引失效
- 使用联合索引是，主要索引列的顺序，一般准信最左匹配原则
- 对查询进行优化，应考虑在where及order by设计的裂伤建立索引
- 大批量数据时，考虑批量插入
- 使用覆盖索引来代替*
- 使用explain分析sql进行慢查询分析

37.哪些场景下会导致索引失效？

索引列参与运算或函数操作

SELECT * FROM users WHERE YEAR(create_time) = 2023;
SELECT * FROM products WHERE price + 100 > 500;
-- 调整为
SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';
SELECT * FROM products WHERE price > 400;

使用不等于(!= 或 <>)条件

-- 索引可能失效
SELECT * FROM orders WHERE status != 'completed';

使用NOT、NOT IN、NOT LIKE

-- 索引可能失效
SELECT * FROM customers WHERE name NOT LIKE '张%';
SELECT * FROM products WHERE id NOT IN (1, 2, 3);

使用OR条件连接非索引列

-- 如果name有索引而age没有，索引失效
SELECT * FROM users WHERE name = '张三' OR age > 30;

LIKE以通配符开头

-- 索引失效
SELECT * FROM users WHERE name LIKE '%三';-- 可以使用索引
SELECT * FROM users WHERE name LIKE '张%';

隐式类型转换

-- 如果user_id是字符串类型，索引失效
SELECT * FROM orders WHERE user_id = 10086;-- 应改为
SELECT * FROM orders WHERE user_id = '10086';

复合索引未遵循最左前缀原则

-- 有复合索引 (a, b, c)
-- 以下情况索引可能部分或完全失效
SELECT * FROM table WHERE b = 2 AND c = 3;
SELECT * FROM table WHERE c = 3;

数据分布不均时优化器放弃索引

-- 当status='active'占表数据90%以上时，优化器可能选择全表扫描
SELECT * FROM accounts WHERE status = 'active';

使用ORDER BY与索引顺序不一致

-- 有索引(a ASC)
-- 索引可能失效
SELECT * FROM table ORDER BY a DESC;

索引列使用IS NULL或IS NOT NULL

-- 索引可能失效
SELECT * FROM employees WHERE department_id IS NULL;

where 子句中的or
优化建议：
- 使用EXPLAIN分析SQL执行计划
- 合理设计复合索引顺序
- 避免在WHERE子句中对索引列进行操作
- 注意数据类型的匹配
- 定期分析表统计信息(ANALYZE TABLE)
- 考虑使用覆盖索引减少回表操作

Q:NOT IN 除了导致索引失效还会导致什么问题？

A:NOT IN 可能导致数据丢失，在sql中使用not in 条件时，若其中包含了null,可能会导致意外结果，因为not in 不会返回任何匹配null值的行，因此我们在使用not in时根据情况搭配使用 or；

-- 设user表里面type[a,b,null]
select * from user where type not in ('a');--执行结果会出现type=b
select * from user where type not in ('a',null);--执行结果为空
-- 调整
select * from user where type not in ('a') or type is null;

NOT IN不会返回任何数据为NULL值的行？

这个问题涉及到sql逻辑：TRUE、FALSE、UNKNOWN。

当你选择NOT IN 条件时，若其中包含NULL值，则导致整个条件的结果不确定，这是因为SQL中的比较操作符（IN、NOT IN、= 、！=）对于NULL的处理方式是特殊的。

若一个值与NULL比较，结果是未知
若一个条件结果是未知，那么整个条件的结果也是未知。

因此，当你使用 not in 条件时，若其中包含了null值，会导致整个结果都为未知、在sql中任何未知的条件都视为不符合条件，因此都会被过滤掉，不包含在结果中。

所以当你要处理NULL值是，需要使用IS NULL或者将NULL视为一个单独的选项。

由于SQL需要郑航数据，所以不仅仅是区间段扫描，还需要回表，这种情况下，MYSQL会选择扫描全表，因为回表的成本比扫描全表的成本大，所以索引可能会失效。但是如果查询列都是索引列的话，就会使用到覆盖索引的方式来进行扫描，进而走索引。

Q:加了唯一索引导致数据重复？？如何给逻辑删除的表加唯一索引？

当唯一索引出现了null值，则唯一约束不会生效，所以我们在建表的时候，需要保证唯一索引不允许为空，否则会导致唯一索引失效。产生的原因涉及到sql的三个逻辑，真（true）假（false）未知（unknown）官方也指出了null与任何值都不相等。

给某个A表加上唯一索引a,b，当用逻辑删除时，设置delFlag=1,这会导致用户在给A表插入一样的ab数据值报错唯一索引冲突，无法插入。

解决方案：
- delFlag非0表示删除，在业务层我们只要规定delFlag非0都是删除，那么当A表只有唯一索引a、b、delFlag时，用户多次插入删除同样的数据时，可以根据delFlag值+1+2等方式来处理。
- 使用时间戳，在唯一索引a,b,delflag中在加上time作为唯一标识，则删除是也是0标识新增，1标识删除，这样就不用修改业务层代码
- 增加id，方案和时间戳类似，只是将时间戳改成deleteid作为标识
如何给历史数据加上唯一索引
- 若某个表已经存在重复的历史数据，我们该如何处理？最简单的是可以新增一个防重复的表，然后数据化初始化过去。若想在原表上处理，可以通过增加id的方式，在创建唯一索引之前，获取相同记录的最大id，然后将delid设置成1，然后将其他相同记录的delid字段设置成当前的主键，这样就能区分历史重复数据了。处理完成后，将a、b、delflag、delId四个加上唯一索引即可。

Q:SQL使用了哪些函数会对索引失效？

索引失效：
- 对索引列使用函数，如lower(column_name)导致索引无法被使用。这是因为索引基于原始值而构建的，函数改变了这些值
- 计算表达式：使用计算表达式column+1 可能导致mysql放弃使用表达式
索引不失效
- 函数在索引范围之外使用，函数不再索引范围即where或者on条件使用时，索引任然有效，如在select列表，order by，group by等地方使用函数通常不影响索引的使用。
建议：若一定要在索引列上使用函数
- 使用生成列：定期维护生成列的函数，生成列先预计算并索引它
- 在应用层处理：在应用层而不是数据库中处理某些需要函数的计算或者转换
- 分析查询函数：使用explian语句分析和验证查询是否有效利用索引。

Q:什么时候索引失效反而查询效率提高？

小表查询：mysql会自动忽略索引，开启全表扫描
读取大部分或者所有行：当一个查询返回表中大比例的行（如30%以上）时，使用索引查询可能好事更多，因为数据库必须调回主数据也以读取完整数据。全表扫描更有效
低选择性索引：若索引列的选择性非常低，比如布尔值字段
频繁更新表：对包含大量更新操作的表，所以的维护成本高
复杂查询的优化选择：对于复杂的多表连接查询，优化器会不使用某个索引或者部分失效提高计算效率
数据分布于优化器误判：特定情况下，导致误判数据分布或者行数，所动禁用索引或者提示优化器使用不同策略可能提升性能

38.常用的函数解析

数学函数
- count(filed)：统计行数
- sum(filed)：求和
- avg(filed)：求平均
- round(filed,x)：取整x位
- abs(filed)：取绝对值
- rand()：取1以内的浮点随机数
类型转换
- cast(data as type)：将一种类型数据转换成另一种类型
  - select cast('123' as signed);-- signed可以是正数、负数或者零；unsigned只能是零、正数不能为负数
字符串函数
- locate('find','source'): 返回字符串首次出现的位置，小标从1开始；select local('2','123');-- 2
- length('str'): 返回字符串长度。select length('123'); -- 3
- replace('source','replace','replace_as'): 替换字符串。
- find_in_set('find','str_list'); 查找制定的字符串在不在字符串列表中；
- concat(data,data2,...)：拼接字符串，如果其中一个数据为null则返回null
- concat_ws(separator,data,data2,data3...);根据separator分隔符来拼接字符串
- data_format(dataStr,pattern):格式化时间函数，将dataStr根据pattern转换时间。
- group_concat(fileld order by filed2 desc)：根据某个字段分组然后汇总数据
日期函数
- curdate()：返回今天的日期
- curtime()：返回现在的时间，无日期
- now()：返回劲头的日期和时间
逻辑函数
- if()：判断表达式是否成立，格式if(表达式，成立返回，不成立返回)，注意成立和不成立必须是一个类型。
- case 多表达式判断，返回最先成立的表达式结果。格式为case when 表达式1 then 表达式1 成立的结果 when 表达式2 then 表达式2成立的结果 else 都不成立结果 end 。
- ifnull('fileld','result') 若传入的为null ，就指定值，不为null 就返回传入的值。

39. truncate delete drop区别

三个操作命令都是数据库操作语句用于删数据或者表结构

truncate ：
- 删表中的数据，但是保留表结构
- 速度比delete快，因为他通常不记录当单独的行删除操作，也不触发行级触发器。
- 大部分情况被视为DDL操作，无法被回滚。
- 自动递增的值会被重置。
- 不会激活行级触发器
delete
- 用于删除指定行或者所有数据，但保留表结构和约束
- 删数据是比truncate慢，特别是大表处理，且会激活触发器
- 是一个DML操作，支持事务处理，可以进行回滚
- 自动递增值不会被重置
- 激活行触发器
drop
- 用于删除整个表、数据库或者其他数据库对象，包括其结构和数据
- 通常是理科操作，不可恢复
- 是一个DDL操作，不能回滚
- 删除表会删除所有的数据、索引、约束和权限信息

更多高阶设计问题正在梳理中，见下一个博客。

面试题之数据库相关-mysql篇

基础概念

1.MySQL是什么？

2.什么是关系型数据库？

3.SQL和MySQL有什么区别？

4.innodb引擎4大特性

事务（ACID）

行级锁

外键约束

MVCC（多版本并发控制）

InnoDB其他重要特性

Q:如何通过LRU算法管理页缓存？

Q:脏页（Dirty Page）刷盘策略？

5. 说一下 ACID 是什么？

ACID 的底层实现

6.说一下数据库的事务隔离？

7.什么是MVCC

作用

核心实现原理

版本链（Undo Log）

ReadView（一致性视图）

可见性规则

为什么MVCC高效？

ReadView与undo log的协作流程？