MySQL基础

MySQL是关系型数据库RDB

什么是SQL？
SQL(Structured Query Language)，专门用于和数据库打交道，从数据库中高效提取数据。
什么是MySQL？
MySQL是一种关系型数据库，用于持久化我们系统中的一些数据，比如用户信息。
整数类型的UNSIGNED属性有什么用？
UNSIGNED可以将表示的正整数范围提升一倍，比如TINYINT只能表示-128-127，如果使用UNSIGNED TINYINT就可以变为0-255，可以用在年龄，员工id等一些值不为负的场景。
CHAR和VARCHAR有什么区别？

CHAR：
- 长度：定长字符串，比如CHAR(10)，无论输入abc还是abcdefghij，都只会占用10个字符（如果输入超过十个就截断）
- 适用场景：长度固定的字符串，比如身份证号，性别，MD5算法加密后的密码等。
- 类比：CHAR 就像定长的信封，无论你放几张纸，都塞满；
VARCHAR：
- 长度：变长字符串，VARCHAR 在存储时需要使用 1（长度小于255）或 2（长度大于255）个额外字节记录字符串的长度，检索时不需要处理。比如我存放’abc’，还需要一个字节来记录长度，所以一共是四字节。VARCHAR(10)代表最多能存放10个字节
- 适用场景：字段长度不确定，如用户名、地址等。
- 类比：VARCHAR 是塑料袋，放多少张纸占多少空间，节省但形状不规则。

DECIMAL和/FLOAT/DOUBLE有什么区别

DECIMAL(定点数)
- 精度高，用于存储精确的小数，不会有误差；
- 适用于金融、电商、计量等要求精确的小数场景；
FLOAT/DOUBLE
- 近似值，以二进制表示，会有微小误差；
- 适用于科学计算、图像处理等对速度要求高但可容忍误差的场景；

DATETIME和TIMESTAMP的区别是什么？
DATETIME 类型没有时区信息，TIMESTAMP 和时区有关。

DATETIME：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
Timestamp：1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

NULL和’’(空字符串)的区别是什么？

含义不同：
- NULL 表示值缺失、未知或未定义；
- ‘’ 是一个已知的空字符串，值是明确存在的，只是没有内容。
比较运算：
- NULL 无法直接参与 =、!= 等运算，任何和 NULL 比较的结果都是 NULL（未知），只能用 IS NULL / IS NOT NULL；
- ‘’ 可以正常比较，比如 ‘’ = ‘’ 是 TRUE。
聚合函数行为：
- NULL 会被 SUM、AVG 等聚合函数自动忽略；
- COUNT(*) 统计所有行，包括 NULL；
- COUNT(col) 只统计非 NULL 值；
- ‘’ 会被当成普通值参与运算，比如 SUM(‘’) 视为 0，MIN(‘’) 是空字符串。
  
  为什么不建议使用 NULL 作为默认值？
  NULL 表达不明确，查询时需要特殊处理，无法高效使用索引，也容易被聚合函数忽略，导致语义和性能问题。除非明确表示“未知/缺失”，否则推荐使用更有意义的默认值如 0、’’、1970-01-01 等。

BOOLEAN该如何表示？
MySQL中没有专门的BOOLEAN，使用TINYINT来存储0/1来表示false/true
InnoDB有什么强大之处？

支持行级锁，实现高并发
支持事务（ACID）
- A：原子性一组操作要么全部完成，要么全部回滚
- C：一致性执行前后数据保持一致，符合约束和逻辑
- I：隔离性并发事务彼此隔离，不互相干扰
- D：持久性提交后的数据永久保存，即使系统崩溃也不丢失
  
  只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。也就是说 A、I、D 是手段，C 是目的！

MySQL索引

为什么 MySQL 采用 B+ 树作为索引？
MySQL是将数据持久化在硬盘上，磁盘 I/O 成本高，索引的核心目标就是减少磁盘访问次数。B+树只在叶子节点存放索引和数据，且最底层是双向链表，叶子节点通过链表串联，可以高效支持范围查询和排序；非叶子节点只存键值，不存数据。
索引的分类有哪些？
有主键索引和二级索引

主键索引的B+树的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的B+树的叶子节点里
- 主键索引通过PRIMARY KEY创建，主键索引是聚簇索引（索引结构和数据一起存放的索引）
二级索引的B+树的叶子节点存放的是主键值，而不是实际数据。查完了数据要回表，也就是查两次B+树
- 二级索引是非聚簇索引（索引结构和数据分开存放的索引）

什么时候使用索引/不使用索引？
索引需要占用物理空间，创建索引和维护索引要耗费时间，所以要看场景决定是否使用。

使用索引
- 字段有唯一限制的，比如商品编码
- 经常用where
- 经常用group by 和order by的字段，因为b+树已经自动排序了。
不使用索引
- 不能快速定位
- 大量重复数据，比如性别
- 表数据太少
- 经常更新的字段

有什么优化索引的办法？

前缀索引优化
- 使用某个字段的前几个字符串建立索引
覆盖索引优化
- 覆盖索引就是“只从索引就能拿到结果”的优化技巧，避免回表，提高效率。 多用于只查几个字段的业务场景。
主键索引最好自增
- 避免新开空间割裂。
防止索引失效

请问在什么情况下索引会失效？你遇到过哪些索引失效的场景？怎么优化？

模糊查询左边加通配符：

1	SELECT * FROM user WHERE name LIKE '%abc' -- 索引失效

使用 LIKE ‘abc%’ 才能走索引。

字段运算或函数包裹：

1	WHERE YEAR(create_time) = 2024 -- 索引失效

改成 create_time BETWEEN ‘2024-01-01’ AND ‘2024-12-31’

隐式类型转换（尤其字符串 vs 数字）：

1	SELECT * FROM user WHERE id = '123' -- id为int类型，索引失效

优化：确保类型一致，参数写成 123 而非 ‘123’

OR连接多个条件，但其中一个没有索引：

1	WHERE name = 'Tom' OR age = 20

确保每个字段都有索引，或用 UNION 拆分查询。

在MySQL中，索引会因为模糊匹配、函数包裹、类型不一致、OR条件混用或未遵循最左前缀原则等原因失效。我在项目中习惯使用EXPLAIN来查看执行计划，确认SQL是否真正使用索引，并通过改写条件或建组合索引提升查询效率。

我们刚讲了单列索引。现在请你谈谈组合索引是什么？什么是“最左前缀原则”？如果你建了一个 (a, b, c) 的联合索引，分别在哪些条件下能使用这个索引？哪些情况会导致索引失效？

组合索引是给多个字段一起建的索引，比如：

1	CREATE INDEX idx_abc ON user(a, b, c);

这个索引是按 (a, b, c) 顺序组织的，就像一本书里按姓（a）、名（b）、出生年（c）排序的目录。

组合索引是多个字段组合建索引，遵循最左前缀原则。只有从最左字段开始连续使用时才能命中索引。比如索引是 (a, b, c)，必须从 a 开始匹配，否则无法使用索引，可能退化为全表扫描。

MySQL日志

有三种日志

undo log（回滚日志）

是Innodb存储引擎层生成的日志，实现了事务中的原子性，用于事务的回滚和MVCC

MVCC

MVCC（Multi-Version Concurrency Control），是Innodb实现高并发读写的核心机制，它允许多个事务并发读写而不加锁，实现读写不冲突。
MVCC让读操作不必等写操作完成，写操作也不会阻塞读，大大提升并发性能
如何做到的？
- InnoDB为每行记录维护了两个隐藏字段：
  - trx_id：创建/最后修改这行的事务id
  - roll_pointer：指向undo log的指针，可回溯历史版本
- 读操作会根据当前事务的版本号判断哪些数据是“自己能看到的旧版本”。
  
  MVCC 让读操作看到的是符合事务隔离的“历史快照”，避免加锁冲突，是 InnoDB 实现高并发的核心机制。

日志增加

每当InnoDB引擎对一条记录进行增删改时，要把回滚时所需的信息记录到undo log里。
- 在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了；
- 在删除一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了；
- 在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了。

redo log（重做日志）

写入磁盘很慢，但事务必须保证“提交就不能丢”，所以InnoDB先把sql语句记录在redo log上，再异步刷新到磁盘上，一旦宕机，只要redo log在，就能重做，保证事务数据不丢。
redo log是循环写，会边写边擦除日志，binlog是全局日志。如果数据库被删了，要通过binlog还原。

binlog（归档日志）

binlogBinary Log是 MySQL 层的日志，记录所有涉及数据修改的 SQL 语句如 INSERT、UPDATE、DELETE，作用是：

主从复制的基础
- MySQL 主库会将 binlog 传给从库；从库通过 binlog 重放主库的操作，实现数据同步。
数据恢复（逻辑级）
- 搭配全量备份 + binlog，可以恢复任意时间点的数据（类似“时光机”）；比如误删了数据，可以通过 binlog 找出执行的 SQL 语句。
  
  binlog 是 MySQL 层的“操作录像机”，记录了所有数据变更语句，是主从复制和数据恢复的核心。

问题：那有了binlog，为什么还要有redo log？

binlog 是“逻辑日志”，写入在事务提交之后：
- 如果系统崩溃在提交前，binlog 根本没写，还原不了；
- 会导致事务丢失，违背了“已提交不能丢”的承诺。
binlog 只能“重演 SQL”，不能“恢复内存页”：
- redo log 是物理日志，可以快速恢复脏页（Buffer Pool）；
- binlog 没法修复页级一致性。
  ✅ 二者配合流程（WAL机制）：
执行 SQL，修改内存页（Buffer Pool）；
写 redo log（物理变更） → 写入 redo log buffer；
写 binlog（逻辑变更）；
同时持久化 redo log + binlog；
执行 COMMIT，事务才算真正提交成功。
为什么需要两阶段提交？

有两种情况：
- 如果在将redolog刷入到磁盘之后，MySQL突然宕机了，而binlog还没有来得及写入。MySQL重启后，通过 redo log 能将 Buffer Pool 中 id =1这行数据的 name字段恢复到新值 xiaolin,但是 binlog里面没有记录这条更新语句，在主从架构中，binlog会被复制到从库，由于binlog丢失了这条更新语句，从库的这一行name字段是旧值jay，与主库的值不一致性;
- 如果在将binlog刷入到磁盘之后，MySQL突然宕机了，而redolog还没有来得及写入。由于redolog还没写，崩溃恢复以后这个事务无效，所以id=1这行数据的name字段还是旧值jay，而binlog里面记录了这条更新语句，在主从架构中，binlog会被复制到从库，从库执行了这条更新语句，那么这一行name字段是新值xiaolin，与主库的值不一致性;
所以MySQL为了避免半成功，使用了两阶段提交
把单个事务拆分成了两部分：准备和提交
- 准备：将XID(内部XA事务的ID)写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log持久化到磁盘(innodb_flush_log_at_trx_commit = 1的作用);
- 提交：把XID写入到binlog，然后将binlog持久化到磁盘(sync_binlog=1的作用)，接着调用引擎的提交事务接口，将redolog状态设置为commit，此时该状态并不需要持久化到磁盘，只需要write到文件系统的page cache中就够了，因为只要binlog写磁盘成功，就算redo log的状态还是prepare也没有关系，一样会被认为事务已经执行成功；