MySQL

基础

数据库设计范式

• 1NF：字段不可分；(数据库表每列都是不可分割的基本数据项，同一列中不能有多个值，确保每列保持原子性)
• 2NF：非主属性全依赖主键；(第二范式需要确保数据 库表中的每一列都和主键相关，而不能只与主键的某一部分相关 （主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。)
• 3NF：非主属性无传递依赖。(确保数据表中的每一列数据都和主键直接相关，而不能间接相关)

CHAR和VARCHAR有什么区别

• CHAR是固定长度的字符串类型，定义时需要指定固定长度，存 储时会在末尾补足空格。CHAR适合存储长度固定的数据，如固 定长度的代码、状态等，存储空间固定，对于短字符串效率较 高。
• VARCHAR是可变长度的字符串类型，定义时需要指定最大长 度，实际存储时根据实际长度占用存储空间。VARCHAR适合存 储长度可变的数据，如用户输入的文本、备注等，节约存储空 间。

SQL语句的执行顺序

当一个查询语句同时出现了where,group by,having,order by的时 候, 编写顺序

select 字段
from 表名
where 条件列表
group by 分组条件
having 分组后筛选
order by 排序条件
limit 条数

索引

什么是索引

官方定义: 一种帮助mysql提高查询效率的数据结构

索引的优点:

• 大大加快数据查询速度

索引的缺点:

• 维护索引需要耗费数据库资源
• 索引需要占用磁盘空间
• 当对表的数据进行增删改的时候，因为要维护索引，速度会受到影响

索引分类

主键索引：设定为主键后数据库会自动建立索引(即设置主键时会系统自动为其设置主键索引)，innodb为聚簇索引

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

复合索引：即一个索引包含多个列，正好和单值索引对应。

唯一索引 ：索引列的值必须唯一，但允许有空值

单值索引、复合索引、唯一索引 = 二级索引**= 所有非主键索引**

二级索引 = 非聚簇索引 = 辅助索引

索引的底层原理

MySQL索引底层采用B+树结构

为什么用B+树？

• B树问题：节点同时存储key和data，导致每个节点能存储的key数量少，树高度高，I/O次数多
• B+树优化：
  • 非叶子节点仅存储key和子节点指针
  • 叶子节点存储所有数据，并通过链表连接
  • 每个节点能存储更多key，树高度更低

聚簇索引和非聚簇索引

• 聚簇索引： 将数据存储与索引放到了一块，叶子节点直接存储完整的数据行。
• 非聚簇索引：将数据与索引分开存储，叶子节点不存储完整数据，而是存储索引键 + 指向数据行的指针（可以是物理地址或主键值）。查询时需要先通过索引找到指针，再根据指针去查找完整数据，这个过程称为回表（Bookmark Lookup）

InnoDB 中的索引机制

• 聚簇索引（主键索引）

  • 表数据文件本身就是按主键组织的 B+ 树。
  • 叶子节点存放的是完整的数据行。

• 非聚簇索引（二级索引）

  • 叶子节点存储的是索引字段 + 主键值。

  • 查询时需通过主键值再次查找聚簇索引（回表）。

  • 示例：

    CREATE INDEX idx_name ON users(name);
    SELECT * FROM users WHERE name = 'Alice';
    — 先查 idx_name 得到主键，再回表查聚簇索引

  • 优势：支持覆盖索引（Covering Index）优化。如果查询字段都在索引中（如 SELECT name FROM users WHERE name='Alice'），则无需回表，性能极高。

MyISAM 中的索引机制

• 所有索引都是非聚簇索引
  • 数据存储在 .MYD 文件中，按插入顺序保存（堆表结构）。
  • 索引存储在 .MYI 文件中，使用 B+ 树结构。
  • 无论是主键还是普通索引，叶子节点都存储数据行的物理地址（文件偏移量）。
• 查询流程
  • 通过索引找到物理地址；
  • 根据地址直接跳转到 .MYD 文件读取数据；
  • 不涉及“回表”概念，但仍是两次 I/O（一次索引，一次数据）。

覆盖索引优化回表查询

什么是回表查询

聚集索引的叶子节点包含完整的行数据，而非聚集索引的叶子节点存储的是每行数据的二级索引键 + 该行数据对应的聚集索引键（主键值）。

回表查询是二级索引查询时必须通过主键值再次查询聚簇索引获取数据的过程

什么是覆盖索引

所有使用二级索引的SQL查询语句都必须两次回表吗？当然有特殊情况，如果二级索引树的叶子结点中的字段，已经覆盖了需要查询的所有字段，则不需要回表（回表的目的是获取二级索引树中没有的字段数据），覆盖索引我更愿意称之为索引覆盖，它还是归属于二级索引。

覆盖索引的目的就是避免发生回表查询，也就是说，通过覆盖索引，只需要扫描一次 B+ 树即可获得所需的行记录。

例子：

— 用户表（主键id，普通字段name和age）
CREATE TABLE user (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
) ENGINE=InnoDB;

— 二级索引（name）
CREATE INDEX idx_name ON user(name);

未优化查询（回表查询）

SELECT age FROM user WHERE name = '张三';

执行过程：

回表的目的是获取二级索引树中没有的字段数据, name索引中只有二级索引建name+主键id

优化方案：创建覆盖索引

— 创建覆盖索引（包含查询字段name和age）
CREATE INDEX idx_name_age ON user(name, age);

优化后查询

SELECT age FROM user WHERE name = '张三';

执行过程：

联合索引&最左前缀原则&索引下推

联合索引：在多个列上创建的索引（如 INDEX idx_A_B_C) (A, B, C)）。

索引排序规则：先按 A 排序，A相同再按 B排序，A 和 B 相同再按 C 排序。

所以把**叶子节点中的 B 值单独拎出来看它不是有序的，因为 B 和 C 列的有序性都是依托于 A 列的存在的**

"联合索引的最左前缀原则：索引 (A, B, C) 只能被查询条件 A 或 A+B 或 A+B+C 使用，跳过 A 直接用 B/C 会导致索引失效。

索引下推 ：当查询包含非索引列的条件时，数据库在索引扫描阶段时直接过滤非索引列条件，减少回表次数，让查询更快，尤其适用于联合索引覆盖不全的场景。（MySQL 5.6+ 默认开启）。

例子：

表结构：

CREATE TABLE users (
id INT PRIMARY KEY,
a INT,
b INT,
c INT,
d INT
);
CREATE INDEX idx_abc ON users(a, b, c); — 索引列：a, b, c

查询

SELECT * FROM users WHERE a = 1 AND b = 2 AND d = 3; — d 不在索引中

传统方式（无ICP）

索引下推（ICP）

当查询条件包含 非索引列 时（如 d=3），数据库在索引扫描阶段就应用该条件过滤，避免回表

索引失效场景

联合索引不符合最左前缀原则

• 失效情况：如创建了 (A, B, C) 的联合索引，查询条件中不包含最左列 A（如只查 B=1 或 C=1）；跳过了中间列（如 A=1 AND C=1 跳过 B）
• 失效原因：缺少最左列时，后续列的排序是局部的、不连续的。例如索引(A,B,C)，没有A时B和C在物理存储上是无序的，因为 B 和 C 列的有序性都是依托于 A 列的存在的

索引字段隐式显式转型

• 失效情况：

  — 假设user_id是字符串类型，但用数字查询
  SELECT * FROM users WHERE user_id = 123; — 应使用 '123'

• 失效原因：当字符串类型字段用数字查询时，MySQL 会隐式转换数字为字符串，导致索引失效。正确做法是：字符串字段查询必须用字符串（'123'），不要用数字（123）

字符串 LIKE 以 % 开头

• 失效情况：

  — 索引失效
  SELECT * FROM articles WHERE title LIKE '%数据库%';
  — 可以使用索引（当使用前缀匹配时）
  SELECT * FROM articles WHERE title LIKE '数据库%';

• 失效原因：

  • B+树的查找逻辑：
    WHERE title LIKE '数据库%' → 从 数据库 开头开始匹配（B+树能快速定位）。
    WHERE title LIKE '%数据库%' → 必须检查所有节点（因为“数据库”可能出现在任意位置）。
  • 类比字典：
    • 有效：查“数据”开头的单词（快速定位到 数据结构、数据库）。
    • 无效：查包含“数据”的单词（需翻遍整本字典）

使用 OR 连接条件（除非所有列都有索引)

• 失效情况：

  — 若age或name中有一个无索引，则索引失效
  SELECT * FROM users WHERE age > 30 OR name = 'John';

• 失效原因：

  只要 OR 两边有一个列无索引，MySQL 会放弃所有索引 → 全表扫描。（因为优化器无法高效合并索引，全表扫描成本更低）

  改写为 UNION（推荐）

  SELECT * FROM users WHERE name = 'John'
  UNION
  SELECT * FROM users WHERE age > 30;

对索引列使用函数或运算

• 失效情况：

  — 失效示例
  SELECT * FROM users WHERE YEAR(create_time) = 2023; — 对索引列使用函数
  SELECT * FROM products WHERE price + 10 > 100; — 对索引列进行运算

• 失效原因：对索引列使用函数或进行运算，破坏了索引的有序性，导致数据库无法直接利用 B 树索引，必须进行全表扫描。

引擎优化不需要走索引

• 失效情况：

  SELECT * FROM T WHERE age = 16; — 假设表 T 中的数据量非常小

• 失效原因：如果表中的数据量很小（例如只有几条记录），MySQL 可能会选择全表扫描而不是使用索引。因为在这种情况下，全表扫描的成本可能低于使用索引的成本，尤其是在数据量较少时，索引的维护和查找开销可能会超过直接扫描的开销。

索引设计

1：为WHERE条件添加索引

— 经常这样查询
SELECT * FROM orders WHERE user_id = 123;
SELECT * FROM orders WHERE status = 'paid';
SELECT * FROM orders WHERE create_time > '2024-01-01';

— 就应该创建这些索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_create_time ON orders(create_time);

2：为ORDER BY字段添加索引

— 经常按创建时间排序
SELECT * FROM articles ORDER BY create_time DESC LIMIT 10;

— 创建索引让排序飞快
CREATE INDEX idx_create_time ON articles(create_time);

3：复合索引的顺序很关键

— 如果经常这样查询
SELECT * FROM users WHERE city = '北京' AND age > 25 ORDER BY create_time;

— 索引字段顺序应该是：过滤性强的字段在前
CREATE INDEX idx_city_age_create_time ON users(city, age, create_time);

4：覆盖索引让查询更快

— 如果只需要这几个字段
SELECT id, name, email FROM users WHERE age = 25;

— 创建覆盖索引，连回表都省了
CREATE INDEX idx_age_name_email ON users(age, name, email);

事务

事务ACID特性

MySQL三大日志

1、undo log：记录事务执行前的数据状态

• 事务回滚：当事务执行失败需要撤销时，根据undo log恢复数据。
• 实现MVCC：实现无锁高并发读。

2、redo log ：记录事务修改后的数据状态

• 崩溃恢复：即使数据库崩溃，也能通过redo log恢复已提交的事务，保持事务持久性。
• 提升性能：比如一条数据已提交成功，并不会立即同步到磁盘，而是先记录到redo log中，等待合适的时机再刷盘

3、bin log：记录所有数据库的变更操作

• 主从复制：将主库的变更同步到从库

• 数据恢复：通过历史bin log恢复某个时间点的数据状态。

事务隔离级别

读未提交（RU）：直接读最新数据（无锁）,问题：脏读（某个事务读到了另一个未提交事务修改过的记录）

读已提交（RC）：MVCC（每次读取新快照），问题：不可重复读（Session B中提交了几个隐式事务，这些事务都修改了id为1的记录，每次事务提交之后，Session A中的事务都可以查看到最新的值）

可重复读（RR）MySQL默认级别：MVCC（事务开始时快照）+ 临键锁（Next-Key Lock），问题：幻读（在同一个事务中，两次执行相同的查询，却返回了不同的结果集（多了或少了某些行、事务B插入了新行），这些“新出现”的行被称为“幻行”。）

MySQL RR 隔离级别如何处理幻读？

串行化（Serializable）：读写全加锁（串行执行）

MVCC

快照读 & 当前读

快照读

同样顾名思义，就是读取快照的数据，这个快照一般指的是历史快照。

快照读包含的 SQL 语句为简单的 select 语句，就是不包含 …for update, …lock in share mode 关键字的。

因为查询不涉及数据的更新，一般查询只关注当前时机的历史快照数据，不像update那样更新数据是要最新版本才行。所以快照读能够在一定程度上提升 Mysql 的并发性能。

快照读的实现方式：MVCC。事务开始时生成 Read View，后续所有 SELECT读取 事务开始时的快照（不读新插入的行）。

当前读

顾名思义，就是读取当前版本的数据。

当前读包含的 SQL 语句如下：

• update , delete , insert
• select…for update
• select…lock in share mode

当前读, 对读取的记录加锁, 阻塞其他事务同时改动相同记录，避免出现安全问题。

那你有想过，为什么 Mysql 要对当前读的这些 SQL 加锁？这里有个例子：

假设要 update 一条记录，但另一个事务已经delete这条数据且commit了，如果不加锁就会产生冲突。所以update的时候肯定要是当前读，得到最新的信息并且锁定相应的记录。

当前读的实现方式是 next-key锁，即行记录锁+Gap间隙锁。至于到底是用记录锁，还是Gap间隙锁，得看索引命中情况，

MVCC

MVCC是什么？

MVCC：多版本并发控制。多版本：指MySQL维护着行数据的多个版本；并发控制：在多个事务同时操作某一行记录时，MySQL控制返回多个版本的行记录中的某个版本。

MVCC宏观概述：MySQL在InnoDB存储引擎下对RC、RR且快照读下基于MVCC做数据的多版本并发控制

注意

• MVCC 仅适用于 快照读（普通 SELECT）。
• 当前读（如 SELECT … FOR UPDATE、UPDATE）会加锁，不走 MVCC。

MVCC原理

• 隐藏字段
  trx_id：标记修改该行的事务ID
  roll_pointer：指向 undo log，用于回溯旧版本。

• Undo Log

  每次对记录进行改动，都会记录一条undo日志，roll_pointer属性将这些undo日志都连起来，串成一个Undo Log链

• Read View（读视图）

  Undo Log版本链记录了数据的不同版本，现在核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。

  → ReadView+可见性判断规则→决定返回数据的哪个版本

MVCC = 版本链 + Read View，实现无锁高并发读。

可见性判断规则（简化）
一行数据对当前事务是否可见，取决于其 trx_id 与 Read View 的关系：

• 是自己改的？→ 可见
• 修改事务已提交且在 Read View 创建前？→ 可见
• 修改事务未提交 or 在 Read View 创建后？→ 不可见 → 沿 undo 链找更早版本

隔离级别差异

• Read Committed（RC）：每次 SELECT 都新建 Read View → 总读最新已提交数据。
• Repeatable Read（RR）：事务首次 SELECT 创建 Read View 并复用 → 保证可重复读。

MVCC解决了什么问题

• 解决了读写阻塞

  如果某个行记录被加了一个独占写锁，在当前读(select…for update/lock in share mode)时会阻塞，快照读(普通select)时，根据MVCC的规则返回某一版的数据，即使可能不是最新记录,但避免了被阻塞。

• 在RR下快照读方面避免出现幻读现象

  • 原理：
    事务开始时生成 Read View，后续所有 SELECT 读取 事务开始时的快照（不读新插入的行）。
  • 效果：
    事务A第一次查询看到 id=10，事务B插入 id=15 并提交 → 事务A第二次查询仍看不到 id=15（因为读的是旧快照）。
  • 补充：快照读：MVCC；当前读：next-key锁

mysql的各种锁

全局锁

全局锁就是对整个数据库实例加锁，加上全局锁后，整个数据库将处于只读状态。全局锁的典型的使用场景是做全库的数据备份。

表级锁

• 表锁

  对于表锁，分为两类：

  • 表共享读锁（Read Lock，读锁）
  • 表独占写锁（Write Lock，写锁）

  读锁和写锁的区别

  读锁不会阻塞其他客户端的读，但是会阻塞其它客户端的写

  写锁既会阻塞其他客户端的读，也会阻塞其他客户端的写

• 元数据锁（Meta Data Lock，MDL)

  在对表进行增删查改操作的时候，不能更改表的结构

• 意向锁

  标记“表内有行锁”

行级锁

• 行锁（Record Lock）:``锁定单个行记录的锁`，防止其他事务对此行进行 update 和 delete 操作，在 RC、RR 隔离级别下都支持
• 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保案引记录间隙不变，防止其他事务在这个间隙进行 insert 操作，产生幻读，在 RR 隔离级别下都支持
• 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙 Gap，在 RR 隔离级别下支持

默认情况下，InnoDB 引擎在 REPEATABLE READ 事务隔离级别运行，InnoDB 使用 Next-Key Locks 锁（临键锁）进行搜索和索引扫描，以防止幻读

SQL 性能分析及优化

如何定位项目中的慢 SQL？

在实际项目中，慢 SQL 的定位通常有两种主流方式：

方式一：使用链路追踪工具（如 SkyWalking）
这个问题，在项目开发中，是非常常见的。 在我们之前的项目中，用到了链路追踪组件 SkyWalking，通过SkyWalking我们就能够知道，所有 请求的调用链路及执行耗时，在显示的报表中，我们就可以看出哪一个接口比较慢，也可以看到这个接口执行过程中，每一个部分的具体耗 时，包括SQL的执行具体时间也都可以看到，通过这个就可以定位慢SQL了。

方式二：开启 MySQL 慢查询日志（Slow Query Log）
那如果在一些项目中，没有用到这类的监控工具，也可以开启MySQL的慢查询日志，通过MySQL的慢查询日志来定位慢SQL。比如：我们可以 在配置文件中配置一下，只要SQL语句的执行耗时超过1秒，我就需要将其记录在慢查询日志中，最终我们只需要通过这份慢查询日志，就能 够知道哪些SQL的执行效率比较低。

SQL 优化经验总结

表的设计

• 比如数据类型的选择，数值类型到底选择 tinyint、int还是bigint，要根据实际需要选择。字符串类型，到底选择char还是varchar， 也需要根据具体业务确定。（char定长字符串，效率高；varchar变长字符串，效率略低）
• 还需要考虑主键的设计，主键在设计时，尽量考虑递增顺序插入的主键，比如：自增主键 或 雪花算法生成的主键。（这样可以规‘避页分裂、页合并现象的产生）

索引的创建

• 针对于数据量较大，且查询比较繁琐的表创建索引。（单表超过10w记录）
• 针对于经常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
• 尽量选择为区分度高的列建立索引，如果该字段是唯一的，建立唯一索引，效率更高。（区分度越高，效率越高）。
• 在varchar类型的字段上，建议指定索引长度（建立前缀索引），没必要对全字段建立索引，根据实际文本区分度决定索引长度就可 以。
• 尽量建立联合索引，而且在联合索引中将区分度高的字段放在前面，减少单列索引。（查询时，联合索引很多时候可以索引覆盖， 避免回表，提高效率）
• 在满足业务需求的前提下，建立适当的索引，索引不宜过多。（索引过多，会增加维护索引的成本，影响增删改的效率）

索引的使用

• 编写DQL时，在满足业务需要的情况下，要尽量避免索引失效的情况。
• 尽量使用索引覆盖，避免回表查询，提高性能。
• 那这些情况呢，都可以通过 explain 关键字来查看SQL语句的执行计划。

进阶优化手段（视业务规模而定）

• 那如果从数据库层面来讲，也可以基于读写分离的模式，来降低单台服务库的访问压力，从而提高效率。
• 当然，如果数据量过大，也可以考虑对目前项目中的数据库进行分库分表处理。