八股文系列之Mysql-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_45130169/article/details/147455243

一、MySQL简介

1.1 体系结构

MySQL采用分层架构，包含以下核心组件：

连接池：管理客户端连接，复用线程资源以减少开销。
SQL接口：解析并验证SQL语法，转发请求至优化器。
查询优化器：生成执行计划，选择索引或连接顺序以提高性能。
存储引擎：插件式设计，InnoDB（支持事务）与MyISAM（高性能读）是常用引擎。
物理文件：包括表结构文件（.frm）、数据文件（.ibd）及日志（redo/undo log）。

1.2 存储引擎

InnoDB：支持事务、行级锁、外键，通过MVCC实现高并发。
MyISAM：不支持事务，表级锁，适用于读密集型场景。
Memory：数据存于内存，速度快但易丢失，适合临时表。

1.3 MySQL 字段类型全面解析

MySQL 提供了丰富的数据类型，主要分为以下几大类：

数值类型

整数类型

TINYINT：1字节，范围(-128_127)或(0255)无符号
SMALLINT：2字节，范围(-32,768_32,767)或(065,535)
MEDIUMINT：3字节，范围(-8,388,608_{8,388,607)或(0}16,777,215)
INT/INTEGER：4字节，范围(-2,147,483,648_{2,147,483,647)或(0}4,294,967,295)
BIGINT：8字节，极大整数范围

定点数类型

DECIMAL(M,D)：精确小数，M是总位数(1-65)，D是小数位数(0-30)
NUMERIC：DECIMAL的同义词

浮点数类型

FLOAT：4字节单精度浮点数
DOUBLE：8字节双精度浮点数
FLOAT§：根据精度p自动选择FLOAT或DOUBLE

字符串类型

普通字符串

CHAR(M)：固定长度(0-255字符)，存储时填充空格
VARCHAR(M)：可变长度(0-65,535字符)，仅存储实际内容
BINARY(M)：类似CHAR但存储二进制字节
VARBINARY(M)：类似VARCHAR但存储二进制字节

文本类型

TINYTEXT：最大255字符
TEXT：最大65,535字符(64KB)
MEDIUMTEXT：最大16,777,215字符(16MB)
LONGTEXT：最大4,294,967,295字符(4GB)

二进制大对象

TINYBLOB：最大255字节
BLOB：最大65,535字节(64KB)
MEDIUMBLOB：最大16,777,215字节(16MB)
LONGBLOB：最大4,294,967,295字节(4GB)

日期和时间类型

DATE：日期值，格式’YYYY-MM-DD’，范围1000-01-01到9999-12-31
TIME：时间值，格式’HH:MM:SS’，范围-838:59:59到838:59:59
DATETIME：日期时间组合，格式’YYYY-MM-DD HH:MM:SS’
TIMESTAMP：时间戳，范围1970-01-01 00:00:01到2038-01-19 03:14:07 UTC
YEAR：年份值，2位或4位格式(70-69或1901-2155)

特殊类型

ENUM：枚举类型，最多65,535个预定义值
SET：集合类型，最多64个成员
BIT(M)：位字段类型，M范围1-64
JSON：MySQL 5.7+支持的JSON文档存储
GEOMETRY：空间数据类型
POINT, LINESTRING, POLYGON：具体空间类型

选择数据类型的最佳实践

整数选择：
- 优先选择能满足需求的最小类型
- 自增ID通常用INT或BIGINT
小数选择：
- 需要精确计算(如金额)用DECIMAL
- 科学计算可用FLOAT/DOUBLE
字符串选择：
- 固定长度用CHAR
- 变长文本用VARCHAR
- 大文本用TEXT系列
时间选择：
- 只需要日期用DATE
- 需要时区支持用TIMESTAMP
- 大范围日期用DATETIME
特殊需求：
- 预定义选项用ENUM
- 多选选项用SET
- 结构化数据用JSON

类型属性

UNSIGNED：无符号数值(仅整数)
ZEROFILL：用零填充显示(已弃用)
AUTO_INCREMENT：自增属性
DEFAULT：设置默认值
NOT NULL：非空约束
CHARACTER SET：指定字符集
COLLATE：指定排序规则

二、基础语法与高级SQL

2.1 DDL（数据定义语言）

创建对象：CREATE DATABASE/TABLE，支持指定字符集和存储引擎。
修改结构：ALTER TABLE添加列或索引，DROP删除对象。

2.2 DML（数据操作语言）

增删改：INSERT INTO ... VALUES, UPDATE ... SET, DELETE FROM。

2.3 DCL（数据控制语言）

权限管理：GRANT授予权限（如SELECT、UPDATE），REVOKE撤销权限。

2.4 DQL（数据查询语言）

高级查询：
- 连接查询：JOIN（INNER/LEFT/RIGHT）关联多表。
- 聚合函数：SUM(), COUNT()结合GROUP BY分组统计。
- 子查询：嵌套查询，如SELECT * FROM t1 WHERE id IN (SELECT id FROM t2)。

2.5 JSON数据类型及函数

存储与查询：JSON_TYPE()验证格式，->>提取值，JSON_EXTRACT()路径查询。

2.6 递归公用表表达式（CTE）

层级查询：处理树形数据，如组织架构：

  WITH RECURSIVE cte AS (  
    SELECT id, parent_id FROM nodes WHERE id = 1  
    UNION ALL  
    SELECT n.id, n.parent_id FROM nodes n JOIN cte ON n.parent_id = cte.id  
  )  
  SELECT * FROM cte;

2.7 系列生成函数

生成序列：GENERATE_SERIES(start, end)生成数字序列（MySQL 8.0+）。

2.8 正则与全文索引

正则匹配：REGEXP实现模式匹配，如WHERE name REGEXP '^A'。
全文索引：FULLTEXT索引支持自然语言搜索，MATCH() AGAINST()实现关键词检索。

三、高级功能与特性

3.1 事务与并发控制

ACID特性：
- 原子性：事务要么全成功，要么全回滚（通过undo log实现）。
- 一致性：事务前后数据符合约束（如外键、唯一索引）。
- 隔离性：MVCC（多版本并发控制）与锁机制实现。
- 持久性：事务提交后数据永久保存（通过redo log强制刷盘）。
隔离级别与问题：

隔离级别脏读不可重复读幻读
READ UNCOMMITTED ✔️ ✔️ ✔️
READ COMMITTED ✖️ ✔️ ✔️
REPEATABLE READ ✖️ ✖️ ✔️
SERIALIZABLE ✖️ ✖️ ✖️
- 脏读：读取未提交数据。
- 不可重复读：同一事务内多次读取结果不同。
- 幻读：新增数据导致前后查询结果不一致。
MVCC机制：
通过隐藏列（事务ID、回滚指针）维护数据版本，实现非阻塞读。

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	✔️	✔️	✔️
READ COMMITTED	✖️	✔️	✔️
REPEATABLE READ	✖️	✖️	✔️
SERIALIZABLE	✖️	✖️	✖️

3.2 索引与查询优化

索引类型：
- B+树：默认结构，支持范围查询。
- 哈希索引：精确匹配快，但无法排序。
锁机制：
- 行锁：InnoDB通过锁住索引项实现。
- 间隙锁：防止幻读，锁定范围（如WHERE age > 20）。
优化建议：
- 避免全表扫描，利用覆盖索引减少回表。
- 控制事务大小，减少锁定时间。

3.3 备份与恢复

物理备份：直接复制数据文件（如mysqldump）。
逻辑备份：导出为SQL语句，适合跨版本迁移。

3.4 数据同步与迁移

主从复制：通过binlog实现数据同步。
跨数据库迁移：注意事务隔离级别差异（如Oracle默认READ COMMITTED，MySQL默认REPEATABLE READ）。

3.5 监控与调优

性能监控：
- 使用SHOW STATUS查看线程、锁状态。
- 分析慢查询日志（slow_query_log）。
调优策略：
- 选择合适隔离级别（如READ COMMITTED平衡性能与一致性）。
- 避免长事务，减少锁竞争。

四、场景分析题

1. 什么是内连接、外连接、交叉连接、笛卡尔积？

内连接（INNER JOIN） ：仅返回两个表中匹配条件的行。例如，SELECT * FROM A INNER JOIN B ON A.id = B.id 会返回两表中 id 相同的行。
外连接（OUTER JOIN） ：分为左外连接（LEFT JOIN）和右外连接（RIGHT JOIN），左连接返回左表所有行及右表匹配的行（不匹配的右表字段为NULL），右连接反之。例如，SELECT * FROM A LEFT JOIN B ON A.id = B.id 会包含左表所有记录。
交叉连接（CROSS JOIN） ：返回两表的笛卡尔积，即所有可能的行组合。例如，SELECT * FROM A CROSS JOIN B 会产生 A的行数 × B的行数 条记录。
笛卡尔积：无连接条件时，两表直接组合所有行。例如，隐式交叉连接 SELECT * FROM A, B 会生成笛卡尔积。

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

内连接：仅返回匹配的行，不匹配的行被过滤。
左连接：左表为主表，所有行保留，右表不匹配的字段补NULL。
右连接：右表为主表，所有行保留，左表不匹配的字段补NULL。
总结：内连接是严格匹配，外连接保留主表所有数据，方向由 LEFT 或 RIGHT 决定。

3. 数据库的三大范式？

第一范式（1NF） ：字段原子性，不可再分。例如，将“地址”拆分为省、市、街道。
第二范式（2NF） ：消除部分依赖，确保非主键字段完全依赖主键。例如，订单表中不应包含与订单无关的商品描述。
第三范式（3NF） ：消除传递依赖，非主键字段间无依赖。例如，学生表不应包含“学院电话”（应通过学院表关联）。

4. varchar 与 char 的区别？

char：定长，存储时用空格填充，适合长度固定的字段（如性别），检索时会去除末尾空格。
varchar：变长，按实际内容存储，适合长度不固定的字段（如用户名），节省空间。
示例：char(10) 存 “abc” 占10字节，varchar(10) 占3字节。

5. blob 和 text 有什么区别？

blob：存储二进制数据（如图片、文件），区分大小写。
text：存储文本数据（如文章），不区分大小写。
限制：text 有字符集，blob 无；text 不能有默认值。

6. DATETIME和TIMESTAMP的异同？

相同点：均可存储日期和时间。
不同点：
- 范围：DATETIME（1000-9999年），TIMESTAMP（1970-2038年）。
- 时区：TIMESTAMP自动转换为UTC存储，DATETIME按输入值存储。
- 空间：TIMESTAMP占4字节，DATETIME占8字节。

7. MySQL 中 in 和 exists 的区别？

in：适合子查询结果集较小的情况，如 SELECT * FROM A WHERE id IN (SELECT id FROM B)。
exists：适合外层查询大表，子查询用索引关联，如 SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id )。
性能：exists 通常用半连接优化，in 可能转换为 JOIN。

8. MySQL 里记录货币用什么字段类型比较好？

推荐 DECIMAL：精确小数，避免浮点误差。例如，DECIMAL(10,2) 表示最多10位，2位小数。
避免 FLOAT/DOUBLE：浮点数存在精度损失。

9. MySQL 怎么存储 emoji 表情？

字符集设为 utf8mb4：支持4字节编码（如emoji），需修改表、列或连接字符集。
操作步骤：
1. 修改表：ALTER TABLE table CONVERT TO CHARACTER SET utf8mb4。
2. 配置连接：SET NAMES utf8mb4。

10. drop，delete与truncate的区别？

delete：逐行删除数据，可加WHERE条件，事务可回滚。
truncate：清空表并重置自增ID，不可回滚，速度快。
drop：删除表结构和数据，释放空间。

11. UNION 与 UNION ALL 的区别？

UNION：合并结果并去重，需排序。
UNION ALL：直接合并，不去重，效率更高。
示例：SELECT a FROM t1 UNION SELECT a FROM t2 会去重，而 UNION ALL 包含重复行。

12. count(1)、count(*)与count(列名)的区别？

count(*)：统计所有行，包括NULL，InnoDB会优化为取最小索引。
count(1) ：与 count(*) 等效，统计所有行。
count(列名) ：统计该列非NULL的行数。

13. 一条SQL 查询语句的执行顺序？

FROM 和 JOIN 确定数据源。
WHERE 过滤行。
GROUP BY 分组。
HAVING 过滤分组。
SELECT 选择列。
ORDER BY 排序。
LIMIT 限制行数。

14. MySQL 的基础架构？

连接层：管理客户端连接，权限验证。
Server层：含查询缓存、分析器、优化器、执行器，处理SQL逻辑。
存储引擎层：如 InnoDB、MyISAM，负责数据存储和读写。

15. 一条SQL查询语句在MySQL中如何执行的？

连接器：建立连接，验证权限。
查询缓存：若命中缓存直接返回（8.0后已移除）。
分析器：语法解析，生成语法树。
优化器：选择执行计划（如索引选择）。
执行器：调用存储引擎接口获取数据。

16. MySQL 有哪些常见存储引擎？

InnoDB：支持事务、行锁、外键，默认引擎。
MyISAM：不支持事务，表锁，适合读多写少。
MEMORY：数据存内存，重启丢失。

17. 存储引擎应该怎么选择？

InnoDB：需要事务、高并发写、崩溃恢复。
MyISAM：读密集、无事务需求（如日志表）。
MEMORY：临时数据、高速访问（如会话缓存）。

18. InnoDB和MyISAM主要有什么区别？

事务：InnoDB支持ACID，MyISAM不支持。
锁：InnoDB行级锁，MyISAM表级锁。
外键：InnoDB支持，MyISAM不支持。
崩溃恢复：InnoDB有redo log保障。

19. MySQL日志文件有哪些？作用？

MySQL日志文件全面解析

错误日志（Error Log）

文件命名：通常为hostname.err或mysqld.log

核心作用：

记录MySQL服务器启动、运行和关闭过程中的错误信息
存储关键事件和警告信息
记录未捕获的异常和关键操作失败信息

配置参数：

log_error = /var/log/mysql/error.log
log_error_verbosity = 3  # 1=errors, 2=errors+warnings, 3=errors+warnings+notes

应用场景：

服务器启动失败诊断
运行期间异常问题排查
监控系统健康状态

二进制日志（Binary Log, Binlog）

文件命名：mysql-bin.000001（前缀可配置），带索引文件mysql-bin.index

核心作用：

记录所有更改数据的SQL语句（DDL和DML）
主从复制的核心组件
支持时间点恢复（PITR）
审计数据库变更历史

配置参数：

log_bin = ON
binlog_format = ROW|STATEMENT|MIXED  # 推荐ROW格式
expire_logs_days = 7  # 自动清理旧日志
sync_binlog = 1  # 每次事务提交都同步到磁盘

三种格式对比：

STATEMENT：记录SQL语句（可能因函数导致主从不一致）
ROW：记录行变化（安全但日志量大）
MIXED：混合模式，多数情况用ROW，安全情况用STATEMENT

慢查询日志（Slow Query Log）

文件命名：hostname-slow.log

核心作用：

记录执行时间超过阈值的SQL语句
识别需要优化的低效查询
分析数据库性能瓶颈

配置参数：

slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2  # 超过2秒的查询
log_queries_not_using_indexes = ON  # 记录未使用索引的查询
log_throttle_queries_not_using_indexes = 10  # 限制每分钟记录数量

分析工具：

mysqldumpslow：MySQL自带分析工具
pt-query-digest：Percona提供的强大分析工具

通用查询日志（General Query Log）

文件命名：hostname.log

核心作用：

记录所有收到的客户端连接和SQL语句
用于审计和问题排查
会产生大量IO，仅调试时建议开启

配置参数：

general_log = ON
general_log_file = /var/log/mysql/mysql-query.log
log_output = FILE|TABLE|NONE  # 输出到文件、表或关闭

重做日志（Redo Log）

InnoDB特有：通常为ib_logfile0和ib_logfile1（循环写入）

核心作用：

实现事务的持久性（ACID中的D）
WAL（Write-Ahead Logging）机制的核心
崩溃恢复时重放已提交事务
提高写入性能（顺序IO vs 随机IO）

配置参数：

innodb_log_file_size = 512M  # 单个日志文件大小
innodb_log_files_in_group = 2  # 日志文件数量
innodb_log_buffer_size = 16M  # 日志缓冲区大小

撤销日志（Undo Log）

InnoDB特有：存储在系统表空间或独立的undo表空间

核心作用：

支持事务回滚
实现MVCC（多版本并发控制）
存储事务修改前的数据映像

配置参数：

innodb_undo_directory = /path  # undo日志存储路径
innodb_undo_tablespaces = 8  # undo表空间数量
innodb_undo_log_truncate = ON  # 启用undo日志截断

中继日志（Relay Log）

主从复制专用：relay-bin.000001格式，带relay-bin.index

核心作用：

从库保存从主库接收到的二进制日志事件
作为从库的中转存储
SQL线程读取中继日志并应用到从库

配置参数：

relay_log = /path/to/relay-log
relay_log_index = /path/to/relay-log.index
relay_log_info_file = relay-log.info
relay_log_purge = ON  # 自动清理已应用的日志

数据字典日志（DDL Log）

MySQL 8.0+新增：用于原子DDL操作

核心作用：

保证DDL操作的原子性
记录元数据变更
支持崩溃恢复时回滚未完成的DDL

日志文件关系图

客户端请求
    │
    ├──→ 通用查询日志（记录所有请求）
    │
    ├──→ 错误日志（记录错误信息）
    │
    └──→ 执行引擎
            │
            ├──→ 慢查询日志（记录慢查询）
            │
            ├──→ InnoDB引擎
            │      │
            │      ├──→ 重做日志（保证持久性）
            │      │
            │      └──→ 撤销日志（支持回滚和MVCC）
            │
            └──→ 二进制日志（记录数据变更）
                   │
                   └──→ 中继日志（主从复制）

理解这些日志的作用和相互关系，对于MySQL数据库的管理、故障排查和性能优化至关重要。根据实际业务需求合理配置各类日志，可以显著提高数据库的可靠性和可维护性。

20. binlog和redo log有什么区别？

binlog：逻辑日志，记录所有写操作（如SQL语句），用于主从同步和归档。
redo log：物理日志，记录数据页修改，用于崩溃恢复。
写入时机：redo log 事务提交时刷盘，binlog 可配置为不同策略。

21. 一条更新语句怎么执行？

客户端请求阶段

连接建立

客户端通过TCP/IP或Unix Socket与MySQL服务器建立连接
连接器验证用户名、密码及权限
建立会话级系统变量和环境

SQL语句提交

UPDATE users SET balance = balance + 100 WHERE id = 5;

解析与验证阶段

语法解析

词法分析器将SQL拆分为token序列
语法分析器构建语法树
预处理器检查表/列是否存在
检查用户是否有UPDATE权限

优化器工作

生成执行计划（选择使用主键索引还是全表扫描）
决定是否使用索引条件下推(ICP)
估算需要扫描的行数（rows列可通过EXPLAIN查看）

InnoDB引擎处理阶段

事务启动（隐式或显式）

分配事务ID（trx_id）
创建read view（决定事务的可见性）
加入全局事务链表

记录定位

通过B+树索引定位到id=5的记录
如果使用二级索引，需要回表查询聚簇索引获取完整记录

加锁阶段

步骤1：对id=5的记录加排他锁(X锁)
步骤2：如果涉及间隙，加间隙锁(Gap Lock)防止幻读
步骤3：检查外键约束（如有）并对关联记录加锁

数据修改阶段

内存中修改

从缓冲池(Buffer Pool)读取数据页，如不存在则从磁盘加载
在内存中修改balance值（原值+100）
标记数据页为脏页(dirty page)

日志记录

Undo Log：记录修改前的数据镜像（用于回滚）

{
  "table": "users",
  "id": 5,
  "before": {"balance": 500},
  "after": {"balance": 600}
}

Redo Log Buffer：记录物理修改（先写入缓冲区）
Binlog Cache：记录逻辑修改（事务提交时写入）
事务提交阶段

两阶段提交（保证redo log与binlog一致性）

Prepare阶段：
- 将redo log buffer刷盘（fsync）
- 写入redo log标记为prepare状态
Commit阶段：
- 将binlog cache写入磁盘
- 在redo log写入commit标记

资源释放

释放所有行锁
清理undo log指针
从事务链表移除

后台处理阶段
脏页刷盘

由后台线程定期将脏页写入数据文件
触发条件：
- redo log写满（循环写入需要腾出空间）
- 缓冲池不足需要淘汰脏页
- 系统空闲时主动刷盘
- 关闭数据库时全量刷盘

Binlog归档

根据expire_logs_days设置自动清理旧binlog
可用于搭建从库或时间点恢复

关键组件协同示意图

客户端
  │
  ↓ 发送UPDATE语句
MySQL Server层
  │─ 解析器：语法解析
  │─ 优化器：生成执行计划
  │─ 执行器：调用存储引擎接口
  ↓
InnoDB引擎层
  │─ 事务系统：分配trx_id
  │─ 锁系统：获取行锁
  │─ 缓冲池：修改内存数据
  │─ 日志系统：
  │   ├─ undo log：记录旧值
  │   ├─ redo log：物理日志
  │   └─ binlog：逻辑日志
  ↓
磁盘存储
  ├─ 数据文件(.ibd)
  ├─ redo log文件(ib_logfileN)
  └─ binlog文件(mysql-bin.N)

22. 为什么要两阶段提交？

保证一致性：确保redo log和binlog逻辑一致，避免主从不一致或数据丢失。
崩溃恢复：若binlog未写入，事务回滚；若binlog已写入，redo log提交。

23. redo log 怎么刷入磁盘？

参数控制：innodb_flush_log_at_trx_commit：
- 0：每秒刷盘，可能丢失1秒数据。
- 1：每次提交刷盘（默认），最安全。
- 2：写入OS缓存，不保证立即刷盘。

24. 慢 SQL 如何定位？

开启慢查询日志：设置 long_query_time，记录执行时间超限的SQL。
EXPLAIN 分析：查看执行计划，确认索引使用情况。
性能监控工具：如 SHOW PROCESSLIST 查看当前执行线程。

25. 如何优化慢 SQL？

索引优化：添加缺失索引，避免索引失效。
重写SQL：简化JOIN、避免SELECT *、使用LIMIT分页。
分库分表：数据量过大时水平拆分。
调整参数：如增大 innodb_buffer_pool_size。

26. 怎么看执行计划（EXPLAIN），如何理解其中各个字段的含义？

EXPLAIN 是分析 SQL 查询性能的关键工具，输出字段含义如下：

EXPLAIN SELECT * FROM users WHERE age > 30;

输出列说明：

id：查询序列号，相同id按顺序执行，不同id越大优先级越高。
select_type：查询类型（SIMPLE、PRIMARY、SUBQUERY、DERIVED等）。
table：涉及的表名。
partitions：匹配的分区。
type：访问类型（性能从高到低）：
- system：表仅一行。
- const：通过主键或唯一索引查找。
- eq_ref：联表查询时使用主键或唯一索引。
- ref：使用非唯一索引查找。
- range：索引范围扫描（如BETWEEN）。
- index：全索引扫描。
- ALL：全表扫描。
possible_keys：可能使用的索引。
key：实际使用的索引。
key_len：索引使用的字节数（越短越好）。
ref：索引的哪一列被使用。
rows：预估需要扫描的行数。
filtered：查询条件过滤的行百分比。
Extra：额外信息（如Using where、Using index）。

27. 索引的分类？

主键索引（PRIMARY KEY） ：唯一且非空，每个表只能有一个。
唯一索引（UNIQUE） ：列值唯一，允许有空值。
普通索引（INDEX） ：加速查询，允许重复值。
组合索引（Composite Index） ：多列组合索引，遵循最左前缀原则。
全文索引（FULLTEXT） ：用于全文搜索（如MATCH(col) AGAINST('text')）。
空间索引（SPATIAL） ：用于地理数据（仅MyISAM支持）。

28. 为什么使用索引会加快查询？

减少磁盘I/O：索引使用B+树结构，树高较低，减少数据页访问次数。
有序性：B+树叶子节点形成链表，范围查询高效。
覆盖索引：直接从索引中获取数据，避免回表。

29. 创建索引有哪些注意点？

选择性高的列：区分度高的字段（如用户ID）效果更好。
避免冗余索引：如已有(a,b)索引，单独建a的索引是冗余的。
组合索引顺序：高频查询条件放左边。
控制索引数量：过多索引会增加写操作开销。
避免大字段索引：如TEXT类型需前缀索引。

30. 索引哪些情况下会失效？

对列使用函数或计算：WHERE YEAR(create_time) = 2023。
隐式类型转换：WHERE id = '100'（id为整型）。
OR条件非全覆盖：WHERE a=1 OR b=2（若a、b无联合索引）。
前导通配符：WHERE name LIKE '%abc%'。
索引列参与运算：WHERE a + 1 = 2。
违反最左前缀原则：组合索引(a,b,c)查询条件未包含a。

31. 索引不适合哪些场景？

写多读少的表：频繁更新导致索引维护成本高。
小数据量表：全表扫描可能更快。
数据重复率高的列：如性别字段（区分度低）。

32. 索引是不是建的越多越好？

否。索引会占用磁盘空间，降低写操作（INSERT/UPDATE/DELETE）速度，需权衡读写比例。

33. MySQL 索引用的什么数据结构？

B+树：主流选择，支持范围查询、排序和分组。
Hash：仅MEMORY引擎支持，等值查询O(1)，但无法范围查询。

34. 一棵 B+树能存储多少条数据？

假设：

页大小：16KB。
主键类型：BIGINT（8字节），指针6字节。
非叶子节点：存储键值+指针，每页可存 16KB/(8+6)≈1170 个键。
树高3层：
- 根节点：1页 → 1170个键。
- 第二层：1170页 → 1170×1170≈1.37M个键。
- 叶子层：每个叶子节点存约16KB/1KB（假设每行1KB）=16行。
- 总数据量：1170×1170×16≈21,902,400 行（约2千万）。

35. 为什么要用B+树而不用普通二叉树？

减少树高：B+树每个节点存大量键，树高更低（3层可存千万数据），减少磁盘I/O。
有序性：叶子节点形成链表，适合范围查询。

36. 为什么用B+树而不用B树？

更高的空间利用率：B+树非叶子节点不存数据，每页可存更多键。
查询更稳定：所有数据在叶子节点，查询路径长度相同。

特性	B树/B-树	B+树
数据存储位置	所有节点都存储数据	只有叶子节点存储数据
叶子节点链接	无	通过指针链接成链表
非叶子节点功能	存储键和值	仅存储键(索引)和指针
查找效率	平均O(log n)	稳定O(log n)
范围查询效率	需要回溯遍历	通过链表高效遍历
空间利用率	较低(节点存储数据)	较高(非叶节点只存索引)
插入删除复杂度	较高	相对较低
磁盘I/O次数	相对较多	更少(高度通常更低)

37. Hash索引和B+树索引区别？

特性	Hash索引	B+树索引
查询复杂度	O(1)	O(log n)
范围查询	不支持	支持
排序	不支持	支持
磁盘I/O	低（等值）	稳定（范围/排序）
适用场景	内存表、等值查询	磁盘存储、通用查询

38. 聚簇索引与非聚簇索引的区别？

聚簇索引（如InnoDB主键索引）：
- 数据与索引存储在一起，叶子节点存数据页。
- 主键顺序影响数据物理存储顺序。
非聚簇索引（如MyISAM索引）：
- 索引与数据分离，叶子节点存数据行地址。
- 查询需回表，多一次磁盘I/O。

39. 回表了解吗？

定义：通过非聚簇索引查到主键后，需回主键索引查找完整数据。
示例：索引(age)存主键id，查询SELECT * FROM users WHERE age=30需先查age索引找到id，再查主键索引拿数据。

40. 覆盖索引了解吗？

定义：索引包含查询所需字段，无需回表。
优化：建组合索引(a,b)，查询SELECT a,b FROM table WHERE a=1可直接从索引取数据。

41. 最左前缀原则？

规则：组合索引(a,b,c)，查询条件需包含最左列才能命中索引。
有效示例：
- WHERE a=1
- WHERE a=1 AND b=2
- WHERE a=1 AND b=2 AND c=3
- WHERE a=1 AND c=3（仅用a，c无法走索引）
无效示例：
- WHERE b=2
- WHERE b=2 AND c=3

42. 什么是索引下推优化？

作用：在存储引擎层过滤数据，减少回表次数。
示例：索引(a,b)，查询WHERE a=1 AND b LIKE '%abc%'，在引擎层直接过滤b条件，仅返回匹配的行。

43. MySQL 中有哪几种锁？

按粒度：
- 表级锁（MyISAM默认）。
- 行级锁（InnoDB默认）。
- 页级锁（BDB引擎）。
按模式：
- 共享锁（S锁）：读锁，允许其他S锁，阻塞X锁。
- 排他锁（X锁）：写锁，阻塞其他所有锁。
其他：
- 意向锁（IS/IX）。
- 间隙锁（Gap Lock）。
- 临键锁（Next-Key Lock）。

44. InnoDB 行锁实现？

记录锁（Record Lock） ：锁定索引记录。
间隙锁（Gap Lock） ：锁定索引区间（防止幻读）。
临键锁（Next-Key Lock） ：记录锁+间隙锁，锁定左开右闭区间。

45. 意向锁是什么？

目的：快速判断表是否被加锁，避免逐行检查。
类型：
- 意向共享锁（IS）：事务打算给某些行加S锁。
- 意向排他锁（IX）：事务打算给某些行加X锁。
兼容性：表级IS/IX与行级S/X锁不冲突，但IS与IX互斥。

46. 乐观锁和悲观锁？

悲观锁：假设并发冲突高，先加锁再操作。如 SELECT ... FOR UPDATE。

乐观锁：假设冲突低，通过版本号（CAS）控制。如：

UPDATE table SET col=new_val, version=version+1 
WHERE id=1 AND version=old_version;

47. 如何解决死锁？

监控：SHOW ENGINE INNODB STATUS 查看死锁日志。
超时机制：设置 innodb_lock_wait_timeout。
重试：捕获死锁异常后重试事务。
避免：按相同顺序访问多表，减少事务粒度。

48. MySQL 事务的四大特性？

原子性（A） ：事务要么全部完成，要么全部回滚。
一致性（C） ：事务前后数据库状态合法。
隔离性（I） ：并发事务互不干扰。
持久性（D） ：事务提交后数据永久保存。

49. ACID 靠什么保证？

原子性：undo log（回滚日志）。
一致性：应用层逻辑+数据库约束。
隔离性：锁+MVCC。
持久性：redo log（重做日志）。

50. 事务的隔离级别？

读未提交（Read Uncommitted） ：可能脏读、不可重复读、幻读。
读已提交（Read Committed） ：避免脏读。
可重复读（Repeatable Read） ：MySQL默认，避免脏读、不可重复读。
串行化（Serializable） ：最高隔离，避免所有问题但性能低。

51. 幻读、脏读、不可重复读的区别？

脏读：事务A读取到事务B未提交的数据，若B回滚，A读到的数据无效。

-- 事务B更新但未提交
UPDATE users SET balance=100 WHERE id=1;

-- 事务A读取未提交的数据（balance=100）
SELECT balance FROM users WHERE id=1;

不可重复读：事务A多次读取同一数据，期间事务B修改并提交，导致A两次结果不一致。

-- 事务A第一次读取（balance=100）
SELECT balance FROM users WHERE id=1;

-- 事务B更新并提交
UPDATE users SET balance=200 WHERE id=1;

-- 事务A第二次读取（balance=200）
SELECT balance FROM users WHERE id=1;

幻读：事务A读取某个范围的数据，事务B插入新数据并提交，导致A再次读取出现新行。

-- 事务A查询年龄>30的用户（返回2条）
SELECT * FROM users WHERE age > 30;

-- 事务B插入年龄=35的用户并提交
INSERT INTO users (name, age) VALUES ('Bob', 35);

-- 事务A再次查询（返回3条）
SELECT * FROM users WHERE age > 30;

52. 事务隔离级别的实现方式？

读未提交：直接读取最新数据，无锁或快照。
读已提交（RC）：
- 锁机制：写操作加行锁，读操作无锁。
- MVCC：每次读生成新快照（Read View），只读取已提交的数据。
可重复读（RR）：
- MVCC：事务首次读生成快照，后续读沿用该快照，保证一致性视图。
- 间隙锁：防止其他事务插入新行（解决幻读）。
串行化：
- 所有读操作加共享锁，写操作加排他锁，强制事务串行执行。

53. MVCC 的实现原理？

多版本并发控制（Multi-Version Concurrency Control）通过版本链和 Read View 实现：
1. 隐藏字段：
DB_TRX_ID：最近修改事务ID。
DB_ROLL_PTR：指向undo log的指针，形成版本链。
2. Read View：
包含当前活跃事务ID列表，用于判断数据版本可见性。
数据可见规则：
如果 DB_TRX_ID < 最小活跃事务ID：可见。
如果 DB_TRX_ID 在活跃事务列表中：不可见。

否则：可见（事务已提交）。
3. 示例：

事务ID=100 修改行R → 生成版本R1（DB_TRX_ID=100）
事务ID=200 修改行R → 生成版本R2（DB_TRX_ID=200）
事务ID=300 启动Read View，活跃事务=[200, 250]
→ R2的DB_TRX_ID=200 在活跃列表中，不可见；
→ 沿版本链找到R1（DB_TRX_ID=100 < 200），可见。

54. 数据库读写分离的作用？

读写分离将读操作和写操作分发到不同数据库节点：
- 主库：处理写操作（INSERT/UPDATE/DELETE）。
- 从库：处理读操作（SELECT），通过主从复制同步数据。
优点：
- 提升读性能：扩展多个从库分担查询压力。
- 提高可用性：主库故障时可切换从库。
缺点：
- 主从同步延迟导致数据不一致（如刚写入主库后立即查询从库）。

55. 读写分离的流量分配实现？

客户端层：在应用代码中区分读写数据源。

// Spring配置多数据源
@Bean
@Primary
public DataSource masterDataSource() { ... } // 主库

@Bean
public DataSource slaveDataSource() { ... }   // 从库

中间件层：使用代理工具自动路由。

MySQL Router：官方中间件，根据SQL类型转发。

ShardingSphere：通过配置读写分离规则。

rules:
- !READWRITE_SPLITTING
  dataSources:
    pr_ds:
      writeDataSourceName: write_ds
      readDataSourceNames: [read_ds_1, read_ds_2]

数据库驱动层：如阿里云Druid的SQLParser识别读写类型。

56. 主从复制原理？

步骤：
1. 主库：将数据变更写入binlog。
2. 从库IO线程：连接主库，拉取binlog到本地（relay log）。
3. 从库SQL线程：读取relay log，重放SQL语句，应用数据变更。
复制模式：
- Statement-Based（SBR） ：记录SQL语句（日志量小，但可能因函数导致不一致）。
- Row-Based（RBR） ：记录数据行变化（安全，日志量大）。
- Mixed：混合模式，自动选择SBR或RBR。

57. 主从同步延迟处理？

原因：主库并发写入高，从库单线程重放（或网络延迟）。
解决方案：
- 并行复制：从库开启多线程重放（设置slave_parallel_workers=4）。
- 半同步复制：主库等待至少一个从库确认收到binlog（配置rpl_semi_sync_master_enabled=1）。
- 延迟敏感读强制走主库：如查询订单支付状态时，使用主库数据源。
- 中间件缓存：将最近写入的数据的查询请求路由到主库。

58. 分库分表策略（垂直分库）？

垂直分库：按业务拆分数据库。
- 示例：
用户库（user_db）：用户表、权限表。
订单库（order_db）：订单表、支付表。
商品库（product_db）：商品表、库存表。
- 优点：降低单库压力，隔离不同业务数据。
- 缺点：跨库JOIN困难，需通过服务层聚合数据。

59. 水平分表策略？

水平分表：将单表数据按规则拆分到多个表。
- 范围分片：按时间或ID范围分表（如order_2023、order_2024）。
- 哈希分片：对分片键（如user_id）取模，分散数据。
```
-- 分表数为4，分片键=user_id
CREATE TABLE order_0 (id BIGINT, user_id BIGINT, ...);
CREATE TABLE order_1 (id BIGINT, user_id BIGINT, ...);
...
```
- 一致性哈希：避免扩容时数据大规模迁移。
- PARTITION分区：

分区表是MySQL中处理大规模数据的重要技术，它通过将一个大表物理分割为多个小表来提高查询性能和管理效率。下面我将全面介绍MySQL分区表的核心概念、实现机制和使用策略。

一、分区表基础概念

什么是分区表

逻辑表现：在应用层看来仍是一张完整的表
物理存储：数据被分散存储在多个物理文件中
透明访问：SQL语句无需修改即可访问分区表

分区与分表的区别
| 特性 | 分区 | 分表 |
|-------------|-----------------------------|-----------------------------|
| 透明性 | 完全透明，应用无感知 | 需要修改SQL或使用中间件 |
| 管理复杂度 | 自动管理 | 需要手动维护多个表 |
| 查询优化 | 优化器自动选择分区 | 需要应用层处理 |
| 跨分区查询 | 支持 | 需要UNION ALL等操作 |

二、分区类型详解

RANGE分区（最常用）
适用场景：按日期范围、数值范围划分

CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2018 VALUES LESS THAN (2019),
    PARTITION p2019 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

LIST分区
适用场景：离散值分类，如地区、状态码

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    store_id INT
) PARTITION BY LIST (store_id) (
    PARTITION pNorth VALUES IN (1,3,5),
    PARTITION pSouth VALUES IN (2,4,6),
    PARTITION pOther VALUES IN (7,8,9,10)
);

HASH分区
适用场景：均匀分布数据

CREATE TABLE products (
    id INT,
    name VARCHAR(50),
    category_id INT
) PARTITION BY HASH(category_id)
PARTITIONS 4;  -- 分为4个分区

KEY分区
特点：类似HASH但只接受MySQL计算的哈希值

CREATE TABLE log_entries (
    id INT AUTO_INCREMENT,
    entry_date DATETIME,
    message TEXT,
    PRIMARY KEY (id, entry_date)
) PARTITION BY KEY(entry_date)
PARTITIONS 12;

COLUMNS分区（MySQL 5.5+）
优势：支持多列分区键和非整数类型

CREATE TABLE temperature_log (
    log_date DATE,
    region VARCHAR(20),
    temp_reading DECIMAL(5,2)
) PARTITION BY RANGE COLUMNS(log_date, region) (
    PARTITION p0 VALUES LESS THAN ('2020-01-01', 'East'),
    PARTITION p1 VALUES LESS THAN ('2020-01-01', 'West'),
    PARTITION p2 VALUES LESS THAN ('2021-01-01', 'East'),
    PARTITION p3 VALUES LESS THAN ('2021-01-01', 'West')
);

三、分区表管理操作

分区维护

-- 添加RANGE分区
ALTER TABLE sales ADD PARTITION (
    PARTITION p2021 VALUES LESS THAN (2022)
);

-- 删除分区（会删除数据）
ALTER TABLE sales DROP PARTITION p2018;

-- 重组分区
ALTER TABLE sales REORGANIZE PARTITION p2019,p2020 INTO (
    PARTITION p2019_2020 VALUES LESS THAN (2021)
);

-- 截断分区（清空数据）
ALTER TABLE sales TRUNCATE PARTITION p2019;

分区信息查询

-- 查看分区定义
SHOW CREATE TABLE sales;

-- 查看分区元数据
SELECT * FROM information_schema.PARTITIONS 
WHERE TABLE_NAME = 'sales';

-- 查看分区使用情况
EXPLAIN PARTITIONS 
SELECT * FROM sales WHERE sale_date BETWEEN '2020-01-01' AND '2020-12-31';

四、分区表性能优化

分区剪枝（Partition Pruning）
原理：优化器自动排除不包含查询数据的分区

-- 只扫描p2020分区
EXPLAIN SELECT * FROM sales 
WHERE sale_date BETWEEN '2020-06-01' AND '2020-06-30';

索引策略

全局索引：跨越所有分区的索引
本地索引：每个分区独立维护的索引

最佳实践：

-- 分区键应包含在唯一索引中
CREATE TABLE sales (
    id INT,
    sale_date DATE,
    UNIQUE KEY (id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (...);

并行查询

MySQL 8.0+支持分区级并行扫描

配置参数：

[mysqld]
innodb_parallel_read_threads = 4

五、分区表限制与注意事项

主要限制

主键/唯一键必须包含分区键
不支持FULLTEXT索引
外键约束不能引用分区表
最大分区数：8192（MySQL 5.6+）

使用注意事项
分区列选择：
- 选择高频查询条件列
- 避免选择频繁更新的列
分区数量控制：
- 每个分区最好不超过2GB
- 避免创建过多分区（影响内存和文件描述符）

备份策略：

# 可以单独备份分区文件
cp /var/lib/mysql/db/sales#P#p2020.ibd /backup/

监控维护：

-- 监控分区使用情况
ANALYZE TABLE sales;

-- 定期优化分区
OPTIMIZE TABLE sales;

六、典型应用场景

时间序列数据

-- 按月份自动分区
CREATE TABLE log_data (
    id BIGINT AUTO_INCREMENT,
    log_time DATETIME,
    data JSON,
    PRIMARY KEY (id, log_time)
) PARTITION BY RANGE (TO_DAYS(log_time)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    -- 每月自动添加新分区
    PARTITION pFuture VALUES LESS THAN MAXVALUE
);

大数据量归档

-- 将历史数据迁移到归档分区
ALTER TABLE orders REORGANIZE PARTITION p2020 INTO (
    PARTITION p2020_active VALUES LESS THAN ('2020-12-01'),
    PARTITION p2020_archive VALUES LESS THAN ('2021-01-01')
);

-- 压缩归档分区
ALTER TABLE orders MODIFY PARTITION p2020_archive 
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

多租户系统

-- 按租户ID哈希分区
CREATE TABLE tenant_data (
    id BIGINT,
    tenant_id INT,
    data VARCHAR(255),
    PRIMARY KEY (id, tenant_id)
) PARTITION BY HASH(tenant_id)
PARTITIONS 16;

60. 水平分表路由方式？

客户端路由：应用代码计算分片位置。

int shard = userId % 4;
String sql = "SELECT * FROM order_" + shard + " WHERE user_id=" + userId;

代理层路由：中间件解析SQL并转发。

-- 用户查询（代理自动计算分片）
SELECT * FROM orders WHERE user_id=123;
-- 代理改写为
SELECT * FROM order_3 WHERE user_id=123;

分布式数据库：如TiDB自动处理分片。

61. 不停机扩容步骤？

双写：新数据同时写入旧库和新库。

public void insertOrder(Order order) {
  oldDataSource.insert(order); // 旧库
  newDataSource.insert(order); // 新库
}

数据迁移：使用工具（如DataX）迁移历史数据到新库。
流量切换：逐步将读请求切到新库，验证无误后停用旧库。
清理双写：移除旧库写入逻辑。

62. 分库分表中间件对比？

中间件	特点
ShardingSphere	生态完善，支持JDBC直连和代理模式，灵活配置
MyCat	基于Proxy，功能丰富，但社区活跃度下降
Vitess	Kubernetes友好，适合大规模场景（如YouTube）
TDDL	阿里开源，仅客户端模式，需集成代码

63. 分库分表带来的问题？

跨库事务：需使用分布式事务（如Seata）。
分布式ID：需全局唯一ID生成方案（雪花算法、Redis自增）。
跨库查询：无法直接JOIN，需业务层聚合或冗余数据。
运维复杂度：数据迁移、监控、备份难度增加。

64. 百万级数据删除优化？

分批删除：避免大事务锁表。

DELETE FROM logs WHERE created_at < '2020-01-01' LIMIT 10000;
-- 循环执行直到影响行数为0

分区表：按时间分区，直接DROP PARTITION秒删。
```
ALTER TABLE logs DROP PARTITION p2020;
```
归档后删除：将历史数据迁移到备份表再删除。

65. 大表添加字段方案？

Online DDL（MySQL 5.6+）：

ALTER TABLE users ADD COLUMN nickname VARCHAR(50), ALGORITHM=INPLACE, LOCK=NONE;

PT-Online-Schema-Change：第三方工具，通过触发器同步数据。

pt-online-schema-change --alter "ADD COLUMN nickname VARCHAR(50)" D=test,t=users

分阶段执行：低峰期操作，逐步应用变更。

66. CPU飙升排查步骤？

定位高负载进程：

SHOW PROCESSLIST; -- 查看当前执行的SQL

分析慢查询：

SELECT * FROM mysql.slow_log WHERE start_time > NOW() - INTERVAL 5 MINUTE;

优化索引：为频繁查询的字段添加索引。
缓存清理：如频繁查询导致缓存失效，调整query_cache_size。
紧急止血：Kill占用CPU高的线程。
```
KILL 1234; -- 线程ID
```

67. 分库分表场景示例？

用户表：按user_id哈希分库，每个库分16张表。
订单表：按order_id范围分表（每月一张表）。
日志表：按时间分库（如年库），再按哈希分表。

68. 电商分库分表设计？

分片键选择：
- 订单表：user_id（买家或卖家ID）或order_id。
分库策略：
- 买家库（buyer_db）：按buyer_id % 8分8个库。
- 卖家库（seller_db）：按seller_id % 8分8个库。
全局表：如商品类目表，全库冗余存储。

69. 每日百万级数据表设计？

时序数据库：使用InfluxDB或TDengine存储时间序列数据。

分区表：按天分区，快速删除旧数据。

CREATE TABLE logs (
  id BIGINT,
  log_time DATETIME,
  ...
) PARTITION BY RANGE (TO_DAYS(log_time)) (
  PARTITION p20230101 VALUES LESS THAN (TO_DAYS('2023-01-02')),
  PARTITION p20230102 VALUES LESS THAN (TO_DAYS('2023-01-03')),
  ...
);

冷热分离：近期数据存MySQL，历史数据转存HBase。

70. 其他存储方案选型？

一、OLTP事务型场景

常规事务处理
- MySQL/PolarDB：满足ACID事务、复杂SQL查询，适合订单、账户等核心业务。单表建议控制在千万级以内，超大规模需分库分表（如ShardingSphere）。
- TiDB：NewSQL数据库，兼容MySQL协议，支持分布式事务和强一致性，适合10TB以上超大规模OLTP场景，如金融级交易系统。
- MongoDB：无固定Schema设计，适合表结构频繁变更场景（如游戏装备属性存储），但需牺牲事务完整性。
高并发短事务
- Redis Cluster：内存数据库，单节点吞吐量可达10万级QPS，适合秒杀库存扣减、分布式锁等场景。需注意内存成本与持久化策略选择（RDB/AOF）。
- Memcached：纯内存KV缓存，协议简单性能更高，适合静态热点数据缓存（如商品基本信息）。

二、OLAP分析型场景

实时分析
- ClickHouse：列式存储+向量化引擎，单表查询性能比Hive快100倍，适合用户行为分析、广告实时归因。但写入吞吐量低，需配合Kafka做批处理。
- Doris/StarRocks：MPP架构，支持高并发点查询（如千人千面的推荐系统），查询延迟可控制在毫秒级，相比ClickHouse更易维护。
- PolarDB：云原生架构，存储计算分离，适合混合负载场景（如同时运行报表查询与事务处理）。
离线大数据分析
- Hive：基于Hadoop生态，适合TB/PB级历史数据批处理，配合Tez引擎可提升性能3-5倍。
- HBase+Kudu：HBase负责实时写入，Kudu提供SQL接口和列式存储，组合方案查询性能比纯HBase提升5-8倍。

三、搜索与复杂查询场景

全文搜索
- Elasticsearch：倒排索引+分片机制，支持模糊查询、语义分析，适合电商商品搜索（召回率>99%）。需注意数据同步延迟问题，可通过Logstash管道实现准实时同步。
- MongoDB Atlas Search：内置Lucene引擎，适合已有MongoDB数据存储的场景实现一站式搜索，减少数据冗余。
多维度聚合
- Cassandra：宽列存储支持动态列扩展，适合设备传感器数据多维度分析（如按时间+设备ID+指标类型聚合）。
- TiFlash：TiDB的列存引擎，支持实时HTAP，在TPC-H测试中比传统方案快10倍。

四、时序与日志场景

物联网时序数据
- InfluxDB：专用时序数据库，数据压缩率可达10:1，支持降采样和连续查询。但在集群版需商业授权。
- TDengine：国产时序数据库，单机每秒可写入百万数据点，适合智能电网等高频采集场景。
日志管理与分析
- ELK Stack：Filebeat采集+Logstash处理+ES存储+Kibana展示的全套方案，日均处理10TB日志成本比Splunk低60%。
- ClickHouse+Prometheus：ClickHouse存储指标数据，配合Grafana实现毫秒级监控大盘响应，存储成本比OpenTSDB低40%。

五、分布式存储与扩展场景

海量数据存储
- HBase：基于HDFS的LSM树存储，单集群可扩展至万台节点，适合社交网络Feed流存储（如微博历史消息查询）。
- CockroachDB：兼容PostgreSQL协议，全球多活架构下P99延迟<200ms，适合跨境电商订单系统。
混合云多活
- PolarDB-X：单元化部署支持跨地域读写分离，故障切换时间<30秒，适合同城双活/异地多活架构。
- YugabyteDB：基于Raft协议的多云数据库，数据自动分片平衡，扩容过程业务无感知。

六、特殊数据结构场景

图数据关系
- Neo4j：原生图存储引擎，3跳查询性能比MySQL快1000倍，适合社交关系分析、反欺诈检测。
- TigerGraph：分布式图数据库，支持万亿边规模，在金融反洗钱场景实现分钟级路径分析。
空间数据
- PostGIS：PostgreSQL插件，支持GIS数据存储与复杂空间运算（如多边形叠加分析），适合智慧城市地图服务。
- GeoMesa+HBase：时空大数据方案，单集群支持每天亿级轨迹点入库，查询响应<1秒。

七、成本敏感型场景

冷数据归档
- OSS+Iceberg：阿里云对象存储+开源表格式，存储成本比HDFS低70%，支持SQL查询。
- TiDB S3：将历史数据分层存储至S3，查询时自动拉取热数据，综合成本降低50%。
开发测试环境
- SQLite：单文件嵌入式数据库，零配置启动，适合移动端本地存储和小型工具开发。
- DuckDB：OLAP嵌入式引擎，在Python中直接处理GB级CSV文件，比Pandas快10倍。

技术选型决策矩阵（2025版）

场景特征	首选方案	备选方案	关键指标	典型案例参考
强事务+复杂SQL	TiDB/PolarDB	Oracle	TPS>10万, 延迟<10ms	银行核心系统
高并发写入+低查询延迟	HBase+Coprocessor	Cassandra	写入吞吐>50万/s	物联网数据采集
多维度实时聚合	ClickHouse+Doris	Kylin	查询响应<100ms	广告效果分析
全文搜索+相关性排序	Elasticsearch+IK分词	Solr	召回率>99%	电商商品搜索
图关系分析	Neo4j APOC扩展	TigerGraph	3跳查询<1s	社交网络推荐
混合云容灾	PolarDB-X多活	YugabyteDB	RTO<30s, RPO=0	跨境电商订单

架构设计原则

分层存储策略
- 热数据（Redis/Memcached）→ 温数据（MySQL/TiDB）→ 冷数据（HBase/OSS）三级存储，综合成本降低40%。
读写分离优化
- MySQL主库处理写操作，通过ProxySQL路由读请求到只读副本，查询吞吐量提升3倍。
数据管道设计
- 变更数据捕获（CDC）使用Debezium同步到Kafka，再分发至ES/HBase等系统，端到端延迟<1秒。
弹性伸缩机制
- 云数据库（如PolarDB）根据CPU/内存使用率自动扩容，突发流量下无需人工干预。

演进路线建议

初创阶段
- 单一MySQL+Redis缓存，快速验证业务模式。
快速增长期
- 引入分库分表（如ShardingSphere）+ES搜索，支撑百万DAU。
成熟期
- 构建HTAP体系：TiDB处理事务，ClickHouse做实时分析，HDFS存储历史数据。
全球化阶段
- 采用多活数据库（如PolarDB-X）+全球缓存加速（如Redis Geo-Distributed），保障跨区域用户体验。

通过以上多维度的技术选型策略，可构建既满足当前业务需求又具备长期演进能力的存储架构。实际选型中需结合团队技术栈、运维能力及成本预算综合决策，必要时采用混合型方案（如ES+MySQL组合实现搜索+事务双重保障）。

Mysql 的行级锁到底锁的是什么东西？

一、行级锁锁定的本质

行级锁实际上锁的是索引记录，而非物理行数据。这是理解MySQL行级锁的关键点：

锁的是索引而非数据行：InnoDB的行锁是通过对索引记录加锁实现的，这意味着：
- 如果表没有索引，InnoDB会使用隐藏的聚簇索引(主键)来锁定
- 当SQL语句无法通过索引访问行时，会退化为表锁
锁的具体对象：
- 对于主键索引：直接锁定主键索引记录
- 对于二级索引：先锁定二级索引记录，再锁定对应的主键索引记录(回表操作)
锁的粒度：
- 锁定的是索引记录而非磁盘上的物理行
- 即使访问同一物理行，如果使用不同索引访问，锁定的也是不同的索引记录

二、行级锁的主要类型

记录锁(Record Locks)

锁定范围：单个索引记录
锁定方式：
- 共享锁(S锁)：允许其他事务读但禁止写
- 排他锁(X锁)：禁止其他事务读和写
示例：SELECT * FROM table WHERE id = 1 FOR UPDATE会在id=1的索引记录上加X锁

间隙锁(Gap Locks)

锁定范围：索引记录之间的间隙，防止其他事务在间隙中插入
特点：
- 只在REPEATABLE READ隔离级别下有效
- 可以防止幻读问题
示例：SELECT * FROM table WHERE id > 10 AND id < 20 FOR UPDATE会锁定10到20之间的所有间隙

临键锁(Next-Key Locks)

锁定范围：记录锁+间隙锁的组合，锁定索引记录及其前面的间隙
特点：
- InnoDB默认的行锁类型
- 结合了记录锁和间隙锁的特性
示例：在REPEATABLE READ下，SELECT * FROM table WHERE id = 15 FOR UPDATE会锁定(10,15]区间

插入意向锁(Insert Intention Locks)

目的：表示事务想在某个间隙插入记录的意图
特点：
- 是一种特殊的间隙锁
- 多个事务可以在同一间隙插入不同位置的记录

mysql树的具体存储

一、B+树基本结构

B+树层级划分

        [非叶子节点]
       /     |      \
[非叶子节点] [非叶子节点] [非叶子节点]
   /   \      /   \     /   \
[叶子节点][叶子节点]...[叶子节点]

B+树特性

所有数据都存储在叶子节点
非叶子节点只存储键值和指针
叶子节点通过指针连接形成有序链表

二、聚簇索引(Clustered Index)

存储内容
聚簇索引的叶子节点存储的是完整的数据记录，即表的所有列数据。

非叶子节点结构

+---------------------+---------------------+-----+---------------------+
| 键值(主键) | 子节点指针 | ... | 键值(主键) | 子节点指针 |
+---------------------+---------------------+-----+---------------------+

存储主键值和指向下一级节点的指针
键值按顺序排列，用于快速定位子节点

叶子节点结构

+---------------------+-------------------------------+
| 键值(主键) | 表的所有列数据(完整行记录) |
+---------------------+-------------------------------+

存储完整的数据行
通过双向链表连接，支持范围查询

物理存储特点

表数据实际按照聚簇索引的顺序存储
每个InnoDB表有且只有一个聚簇索引
主键自动成为聚簇索引，若无主键则选择唯一非空列，否则隐式创建ROW_ID列

三、非聚簇索引(Secondary Index)

存储内容
非聚簇索引的叶子节点存储的是主键值，而不是完整数据记录。

非叶子节点结构

+---------------------+---------------------+-----+---------------------+
| 索引列值 | 子节点指针 | ... | 索引列值 | 子节点指针 |
+---------------------+---------------------+-----+---------------------+

存储索引列的值和指向下一级节点的指针
键值按索引列顺序排列

叶子节点结构

+---------------------+---------------------+
| 索引列值 | 对应主键值 |
+---------------------+---------------------+

存储索引列的值和对应的主键值
通过主键值回表查询获取完整数据

查询流程示例

-- 假设有索引 idx_name(name)
SELECT * FROM users WHERE name = '张三';

-- 查询过程：
1. 在idx_name索引树中查找'张三'
2. 找到对应的主键值(如id=5)
3. 用id=5到聚簇索引中查找完整记录

四、存储细节对比

节点内容差异
| 节点类型 | 聚簇索引 | 非聚簇索引 |
|---------|----------|------------|
| 非叶子节点 | 主键值+指针 | 索引列值+指针 |
| 叶子节点 | 完整数据记录 | 主键值 |
物理存储示例
表结构：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    INDEX idx_name (name)
);

聚簇索引存储：

非叶子节点: [id:3, ptr] [id:7, ptr] [id:12, ptr]
叶子节点: 
  [id:3, '张三', 25] <-> [id:7, '李四', 30] <-> [id:12, '王五', 28]

idx_name索引存储：

非叶子节点: [name:'李四', ptr] [name:'王五', ptr] [name:'张三', ptr]
叶子节点: 
  [name:'张三', id:3] 
  [name:'李四', id:7] 
  [name:'王五', id:12]

五、索引使用优化要点

覆盖索引优化：

-- 只需查询索引列和主键时，避免回表
SELECT id, name FROM users WHERE name = '张三';

索引列顺序：
- 联合索引(a,b,c)的非叶子节点存储a、b、c的值
- 叶子节点存储(a,b,c)组合和主键值
页分裂影响：
- 聚簇索引的插入可能导致页分裂，影响性能
- 自增主键可以减少页分裂
索引选择性：
- 高选择性列更适合建索引
- 区分度低的列索引效果差

六、InnoDB页结构细节

页基本结构(默认16KB)

+-----------------------+
| File Header           |
| Page Header           |
| Infimum + Supremum    |
| User Records (行记录) |
| Free Space            |
| Page Directory        |
| File Trailer          |
+-----------------------+

非叶子节点页

存储键值和指向子页的指针
每个指针占6字节(空间地址)
键值按顺序存储，支持二分查找

叶子节点页

聚簇索引：存储完整行记录
非聚簇索引：存储索引列+主键
通过Page Directory加速记录定位

七、实际存储计算示例

假设：

主键为BIGINT(8字节)
指针6字节
页大小16KB
行记录1KB

聚簇索引非叶子节点容量计算：

每项大小 = 8(主键) + 6(指针) = 14字节
每页可存储约 16KB / 14B ≈ 1170个键值

三层B+树存储量估算：

根节点：1170个键
第二层：1170个页 × 1170键/页 ≈ 1,368,900键
叶子层：1,368,900页 × 15行/页 ≈ 20,533,500行

页的存储结构和查找

一、InnoDB页基础结构

页的基本组成（默认16KB）

+-----------------------+
| File Header (38字节)  | → 页的元信息（页号、前后页指针等）
| Page Header (56字节)   | → 页的状态信息（记录数、空闲空间等）
| Infimum + Supremum (26字节) | → 虚拟的最小和最大记录
| User Records (变长)    | → 实际存储的行记录
| Free Space (变长)      | → 未使用空间
| Page Directory (变长)  | → 槽位指针（用于快速定位记录）
| File Trailer (8字节)   | → 校验信息
+-----------------------+

关键组成部分详解

File Header：

包含页号(4字节)、前后页指针(各4字节)、页类型(2字节)等信息
通过前后页指针形成双向链表，实现页的逻辑连接

Page Directory：

由多个槽(Slot)组成，每个槽2字节，指向页内的记录位置
槽指向的记录按主键顺序排列，支持二分查找
每4-8条记录分配一个槽，平衡查找速度和空间开销

二、行记录存储格式

行记录格式（COMPACT格式）

+---------------------+---------------------+---------------------+
| 记录头信息(5字节) | 事务ID(6字节) | 回滚指针(7字节) | 列数据... |
+---------------------+---------------------+---------------------+

记录头信息包含：

删除标记(1bit)
记录类型(4bit)：普通记录、B+树非叶子节点记录等
下一条记录相对位置(2字节)：形成单链表

行溢出处理
当行数据超过页大小时：

前768字节存储在原始页中
剩余部分存储在溢出页(overflow page)中
原始页存储20字节的指针指向溢出页

三、数据查找过程

页内查找流程（以主键查找为例）

1. 从Page Directory获取槽位数组
2. 对槽位数组进行二分查找，定位目标记录所在槽位区间
3. 在槽位区间内通过记录的单链表进行线性查找
4. 比较记录主键值，找到匹配记录

跨页查找流程

1. 从根页开始查找（固定存储在特定位置）
2. 在非叶子节点页中通过二分查找确定下一层页号
3. 重复步骤2直到到达叶子节点页
4. 在叶子节点页中按页内查找流程定位记录

范围查找优化

通过叶子节点的双向链表快速遍历相邻页
不需要每次都从根节点开始查找

四、不同索引类型的查找差异

聚簇索引查找

1. 从B+树根节点开始查找
2. 沿非叶子节点向下定位到包含目标记录的叶子页
3. 在叶子页中定位具体记录
4. 直接获取完整数据（无需回表）

非聚簇索引查找

1. 在非聚簇索引B+树中查找索引列值
2. 定位到叶子页获取对应的主键值
3. 用主键值回到聚簇索引执行步骤1-3（回表操作）

五、页分裂与合并

页分裂过程
当页空间不足时发生：

1. 创建新页
2. 将原页约50%记录移到新页
3. 更新父节点指针
4. 如果父页也满了，递归触发分裂

页合并条件
当页删除记录后空间利用率低于阈值时：

1. 检查相邻页是否可以合并
2. 合并数据到其中一个页
3. 更新父节点指针
4. 释放空页

八股文系列之Mysql

目录

一、MySQL简介

1.1 体系结构

1.2 存储引擎

1.3 MySQL 字段类型全面解析

二、基础语法与高级SQL

2.1 DDL（数据定义语言）

2.2 DML（数据操作语言）

2.3 DCL（数据控制语言）

2.4 DQL（数据查询语言）

2.5 JSON数据类型及函数

2.6 递归公用表表达式（CTE）

2.7 系列生成函数

2.8 正则与全文索引

三、高级功能与特性

3.1 事务与并发控制

3.2 索引与查询优化

3.3 备份与恢复

3.4 数据同步与迁移

3.5 监控与调优

四、场景分析题

1. 什么是内连接、外连接、交叉连接、笛卡尔积？

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

3. 数据库的三大范式？

4. varchar 与 char 的区别？

5. blob 和 text 有什么区别？

6. DATETIME和TIMESTAMP的异同？

7. MySQL 中 in 和 exists 的区别？

8. MySQL 里记录货币用什么字段类型比较好？

9. MySQL 怎么存储 emoji 表情？

10. drop，delete与truncate的区别？

11. UNION 与 UNION ALL 的区别？

12. count(1)、count(*)与count(列名)的区别？

13. 一条SQL 查询语句的执行顺序？

14. MySQL 的基础架构？

15. 一条SQL查询语句在MySQL中如何执行的？

16. MySQL 有哪些常见存储引擎？

17. 存储引擎应该怎么选择？

18. InnoDB和MyISAM主要有什么区别？

19. MySQL日志文件有哪些？作用？

20. binlog和redo log有什么区别？

21. 一条更新语句怎么执行？

22. 为什么要两阶段提交？

23. redo log 怎么刷入磁盘？

24. 慢 SQL 如何定位？

25. 如何优化慢 SQL？

26. 怎么看执行计划（EXPLAIN），如何理解其中各个字段的含义？

27. 索引的分类？

28. 为什么使用索引会加快查询？

29. 创建索引有哪些注意点？

30. 索引哪些情况下会失效？

31. 索引不适合哪些场景？

32. 索引是不是建的越多越好？

33. MySQL 索引用的什么数据结构？

34. 一棵 B+树能存储多少条数据？

35. 为什么要用B+树而不用普通二叉树？

36. 为什么用B+树而不用B树？

37. Hash索引和B+树索引区别？

38. 聚簇索引与非聚簇索引的区别？

39. 回表了解吗？

40. 覆盖索引了解吗？

41. 最左前缀原则？

42. 什么是索引下推优化？

43. MySQL 中有哪几种锁？

44. InnoDB 行锁实现？

45. 意向锁是什么？

46. 乐观锁和悲观锁？

47. 如何解决死锁？

48. MySQL 事务的四大特性？

49. ACID 靠什么保证？

50. 事务的隔离级别？

51. 幻读、脏读、不可重复读的区别？

52. 事务隔离级别的实现方式？

53. MVCC 的实现原理？

54. 数据库读写分离的作用？

55. 读写分离的流量分配实现？

56. 主从复制原理？

57. 主从同步延迟处理？

58. 分库分表策略（垂直分库）？