文章

《MySQL 知识个人笔记》

发表于 更新于
作者 byte-wrangler
43 分钟阅读
《MySQL 知识个人笔记》

一、MyISAM 和 InnoDB 的区别

MyISAM：MySQL 的默认数据库引擎（5.5 版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但 MyISAM 不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。

InnoDB：5.5 版本之后，MySQL 引入了 InnoDB（事务性数据库引擎），MySQL 5.5 版本后默认的存储引擎为 InnoDB。

两者的对比

特性MyISAMInnoDB
行级锁❌ 只有表级锁✅ 支持行级锁和表级锁，默认为行级锁
事务支持❌ 不提供事务支持✅ 具有事务（commit）、回滚（rollback）和崩溃修复能力
外键❌ 不支持✅ 支持
MVCC❌ 不支持✅ 支持
性能查询速度更快并发性能更好
崩溃恢复不安全安全
适用场景以读操作为主的应用高并发、需要事务支持的应用

💡 MVCC：应对高并发事务，MVCC 比单纯的加锁更高效。MVCC 只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。MVCC 可以使用乐观锁和悲观锁来实现。

二、字符集和校对规则

定义

  • 字符集：一种从二进制编码到某类字符符号的映射
  • 校对规则：某种字符集下的排序规则

继承关系

MySQL 采用的是类似继承的方式指定字符集的默认值：

  • 每个数据库以及每张数据表都有自己的默认值
  • 它们逐层继承

📝 例如：某个库中所有表的默认字符集将是该数据库所指定的字符集。

默认设置

默认的 utf8 字符集对应的校对规则是 utf8_general_ci。

查看字符集

1

SHOW VARIABLES LIKE '%character%';

三、MySQL 的基本存储结构

MySQL 的基本存储结构是页（记录都存在页里边）：

页的组织方式

  1. ✅ 每个数据页中的记录可以组成一个单向链表
  2. ✅ 每个数据页都会为存储在它里边的记录生成一个页目录
  3. ✅ 多个数据页可以组成一个双向链表
  4. ✅ 在通过主键查找某条记录时，可以在页目录中使用二分法快速定位到对应的槽
    • 然后再遍历该槽对应分组中的记录即可快速找到指定的记录
  5. ⚠️ 以其他列（非主键）作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录

四、一条语句如何在 MySQL 中运行的

MySQL 架构

MySQL 主要分为两层：

Server 层

主要包括：

  • 连接器
  • 查询缓存
  • 分析器
  • 优化器
  • 执行器
  • binlog 日志模块（所有执行引擎都可以共用）

引擎层

插件式的，目前主要包括：

  • MyISAM
  • InnoDB
  • Memory

📌 注意：redolog 只有 InnoDB 有。

查询语句的执行流程

1
2
3
4
5
6
7
8

查询语句执行流程
├── 1. 权限校验（如果命中缓存）
├── 2. 查询缓存
├── 3. 分析器
├── 4. 优化器
├── 5. 权限校验
├── 6. 执行器
└── 7. 引擎

更新语句的执行流程

1
2
3
4
5
6
7
8

更新语句执行流程
├── 1. 分析器
├── 2. 权限校验
├── 3. 执行器
├── 4. 引擎
├── 5. redo log (prepare 状态)
├── 6. binlog
└── 7. redo log (commit 状态)

五、MySQL 索引

1. 索引分类

索引类型数据结构特点
BTree 索引B+Tree适用于大部分场景
哈希索引哈希表适用于等值查询

2. 索引详解

哈希索引

底层数据结构：哈希表

适用场景：

  • ✅ 绝大多数需求为单条记录查询的时候
  • ✅ 查询性能最快

不适用场景：

  • ❌ 范围查询
  • ❌ 排序

BTree 索引

底层数据结构：使用 B 树中的 B+Tree

MyISAM 的索引实现

特点：

  • B+Tree 叶节点的 data 域存放的是数据记录的地址
  • 在索引检索时：
    1. 首先按照 B+Tree 搜索算法搜索索引
    2. 如果指定的 Key 存在，则取出其 data 域的值
    3. 然后以 data 域的值为地址读取相应的数据记录

这被称为”非聚簇索引”

InnoDB 的索引实现

特点：

  • 数据文件本身就是索引文件
  • 数据文件本身就是按 B+Tree 组织的一个索引结构
  • 树的叶节点 data 域保存了完整的数据记录
  • 这个索引的 key 是数据表的主键
  • 因此 InnoDB 表数据文件本身就是主索引

这被称为”聚簇索引（或聚集索引）”

辅助索引（二级索引）：

  • 其余的索引都作为辅助索引
  • 辅助索引的 data 域存储相应记录主键的值而不是地址
  • 这也是和 MyISAM 不同的地方

查询过程：

  • 根据主索引搜索：直接找到 key 所在的节点即可取出数据
  • 根据辅助索引查找：需要先取出主键的值，再走一遍主索引（回表）

索引设计注意事项

为什么索引不能太长？

因为如果索引过长，对应页中的数据就会变少，减少效率。

为什么不建议使用非单调的字段？

因为容易频繁页分裂。

3. 覆盖索引

定义：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为”覆盖索引”。

原理：

  • 我们知道 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键 + 列值
  • 最终还是要”回表”，也就是要通过主键再查找一次
  • 这样就会比较慢
  • 覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

覆盖索引使用实例

假设我创建了索引 (username, age)，执行下面的 SQL 语句：

1

SELECT username, age FROM user WHERE username = 'Java' AND age = 22;

分析：

  • 在查询数据的时候，要查询出的列在叶子节点都存在
  • 所以不用回表，直接返回结果

4. 最左前缀原则

定义：MySQL 中的索引可以以一定顺序引用多列，这种索引叫作联合索引。

📝 例如：User 表的 name 和 city 加联合索引就是 (name, city)

最左前缀原则：如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。

💡 简单说：如下 (a, b, c) 联合索引，有 a 就行

示例

1
2
3
4
5
6
7
8

-- ✅ 可以命中索引
SELECT * FROM user WHERE name = 'xx' AND city = 'xx';

-- ✅ 可以命中索引
SELECT * FROM user WHERE name = 'xx';

-- ❌ 无法命中索引
SELECT * FROM user WHERE city = 'xx';

索引字段顺序建议

由于最左前缀原则，在创建联合索引时：

  • 索引字段的顺序需要考虑字段值去重之后的个数
  • 较多的放前面

📌 ORDER BY 子句也遵循此规则。

5. 引起索引失效的操作

  1. ❌ 使用 != 或者 <> 导致索引失效
  2. ❌ 类型不一致导致的索引失效
  3. ❌ 函数导致的索引失效
  4. ❌ 运算符导致的索引失效
  5. ❌ OR 引起的索引失效
    • 连接不同字段失效
    • 连接相同字段不失效
  6. ❌ NOT IN、NOT EXISTS 导致索引失效（某些情况下）
  7. ❌ IS NULL 不走索引，IS NOT NULL 走索引
  8. ❌ LIKE 两边均有 % 时和左边有 % 时，索引会失效
    • ✅ % 在右边时，索引可用

六、事务

ACID 特性

特性英文说明
原子性Atomicity事务是最小的执行单位，不允许分割
事务的原子性确保动作要么全部完成，要么完全不起作用
一致性Consistency执行事务前后，数据保持一致
多个事务对同一个数据读取的结果是相同的
隔离性Isolation并发访问数据库时，一个用户的事务不被其他事务所干扰
各并发事务之间数据库是独立的
持久性Durability一个事务被提交之后，它对数据库中数据的改变是持久的
即使数据库发生故障也不应该对其有任何影响

七、并发事务带来哪些问题？

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下问题。

1. 脏读（Dirty Read）

定义：

  • 当一个事务正在访问数据并且对数据进行了修改
  • 而这种修改还没有提交到数据库中
  • 这时另外一个事务也访问了这个数据，然后使用了这个数据
  • 因为这个数据是还没有提交的数据
  • 那么另外一个事务读到的这个数据是”脏数据”
  • 依据”脏数据”所做的操作可能是不正确的

2. 丢失修改（Lost to Modify）

定义：

  • 在一个事务读取一个数据时，另外一个事务也访问了该数据
  • 那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据
  • 这样第一个事务内的修改结果就被丢失，因此称为丢失修改

示例：

  • 事务 1 读取某表中的数据 A = 20
  • 事务 2 也读取 A = 20
  • 事务 1 修改 A = A - 1
  • 事务 2 也修改 A = A - 1
  • 最终结果 A = 19，事务 1 的修改被丢失

3. 不可重复读（Unrepeatable Read）

定义：

  • 在一个事务内多次读同一数据
  • 在这个事务还没有结束时，另一个事务也访问该数据
  • 那么，在第一个事务中的两次读数据之间
  • 由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样
  • 这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读

4. 幻读（Phantom Read）

定义：

  • 幻读与不可重复读类似
  • 它发生在一个事务（T1）读取了几行数据
  • 接着另一个并发事务（T2）插入了一些数据时
  • 在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录
  • 就好像发生了幻觉一样，所以称为幻读

不可重复读和幻读区别

问题重点说明
不可重复读修改多次读取一条记录发现其中某些列的值被修改
幻读新增或删除多次读取一条记录发现记录增多或减少了

八、事务隔离级别

SQL 标准定义了四个隔离级别：

隔离级别说明脏读不可重复读幻读
READ-UNCOMMITTED
（读取未提交）		最低的隔离级别
允许读取尚未提交的数据变更		✅ 可能✅ 可能✅ 可能
READ-COMMITTED
（读取已提交）		允许读取并发事务已经提交的数据
可以阻止脏读		❌ 不会✅ 可能✅ 可能
REPEATABLE-READ
（可重复读）		对同一字段的多次读取结果都是一致的
除非数据是被本身事务自己所修改
可以阻止脏读和不可重复读		❌ 不会❌ 不会✅ 可能
SERIALIZABLE
（可串行化）		最高的隔离级别
完全服从 ACID 的隔离级别
所有的事务依次逐个执行		❌ 不会❌ 不会❌ 不会

重要提示

⚠️ 与 SQL 标准不同的地方：

InnoDB 存储引擎在 REPEATABLE-READ（可重读） 事务隔离级别下使用的是 Next-Key Lock 锁算法，因此可以避免幻读的产生。

InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读） 并不会有任何性能损失。

💡 InnoDB 存储引擎在分布式事务的情况下一般会用到 SERIALIZABLE（可串行化） 隔离级别。

九、锁机制与 InnoDB 锁算法

1. 常见的锁

（1）读锁（共享锁、S 锁）

特点：

  • 若事务 T 对数据对象 A 加上 S 锁
  • 则事务 T 可以读 A 但不能修改 A
  • 其他事务只能再对 A 加 S 锁，而不能加 X 锁
  • 直到 T 释放 A 上的 S 锁

保证：其他事务可以读 A，但在 T 释放 A 上的 S 锁之前不能对 A 做任何修改。

（2）写锁（排他锁、X 锁）

特点：

  • 若事务 T 对数据对象 A 加上 X 锁
  • 事务 T 可以读 A 也可以修改 A
  • 其他事务不能再对 A 加任何锁
  • 直到 T 释放 A 上的锁

保证：其他事务在 T 释放 A 上的锁之前不能再读取和修改 A。

（3）表级锁

定义：操作对象是数据表。

特点：

  • Mysql 大多数锁策略都支持
  • 是系统开销最低但并发性最低的一个锁策略
  • 事务 T 对整个表加读锁，则其他事务可读不可写
  • 若加写锁，则其他事务增删改都不行

（4）行级锁

定义：操作对象是数据表中的一行。

特点：

  • 是 MVCC 技术用的比较多的
  • 行级锁对系统开销较大，但处理高并发较好

2. 表级锁和行级锁对比

特性表级锁行级锁
锁定粒度最大最小
并发度最低最高
加锁速度快慢
资源消耗少多
死锁不会出现会出现
锁冲突概率最高最低
实现简单性简单复杂
支持引擎MyISAM、InnoDBInnoDB

3. InnoDB 存储引擎的锁算法

InnoDB 存储引擎的锁算法有三种：

（1）Record Lock

单个行记录上的锁

（2）Gap Lock

间隙锁，锁定一个范围，不包括记录本身

（3）Next-Key Lock

Record + Gap，锁定一个范围，包含记录本身

相关知识点

  1. ✅ InnoDB 对于行的查询使用 next-key lock
  2. ✅ Next-key lock 为了解决 Phantom Problem 幻读问题
  3. ✅ 当查询的索引含有唯一属性时，将 next-key lock 降级为 record lock
  4. ✅ Gap 锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生

显式关闭 Gap 锁的方式

有两种方式（除了外键约束和唯一性检查外，其余情况仅使用 record lock）：

方式 A：将事务隔离级别设置为 RC（READ-COMMITTED）

方式 B：将参数 innodb_locks_unsafe_for_binlog 设置为 1

十、MVCC（多版本并发控制）

解释

MVCC（Multi-Version Concurrency Control）多版本并发控制指的是”维持一个数据的多个版本，使得读写操作没有冲突“这么一个概念。

目的

解决数据库使用悲观锁而导致的并发情况下读-写阻塞问题：

  • 即写时不能读，读时不能写
  • 采用的是快照读（不加锁的 select）
  • 即读到的可能是旧版本的数据

实现

MVCC 由三部分组成：

  1. 三个隐式列
  2. undo log
  3. read view（读视图）

支持的隔离级别

只适用于 MySQL 隔离级别中的：

  • ✅ 读已提交（Read Committed）
  • ✅ 可重复读（Repeatable Read）

名称解释

三个隐式字段

字段说明
DB_ROW_ID隐式的自增 ID
DB_TRX_ID该行数据最后一次修改的事务 ID
DB_ROLL_PTR回滚指针，指向这条记录的上一个版本
（存储于 rollback segment 里）

undo 日志

日志类型产生时机作用
insert undo log事务在插入新记录时产生只在事务回滚时需要
事务提交后可以被立即丢弃
update undo log事务在进行更新或删除时产生不仅在事务回滚时需要
在快照读时也需要
所以不能随便删除

Read View（读视图）

定义：事务进行快照读操作时生成的读视图（Read View）。

作用：

  • 在该事务执行快照读的那一刻，会生成数据库系统当前的一个快照
  • 记录并维护系统当前活跃事务的 ID
  • 主要作用来做一个可见性判断，判断当前事务能够看到哪个版本的数据

MVCC 工作流程

1. 开启事务 A

开启一个 A 事务快照读：

  • 生成一个读视图
  • 维护一个当前快照信息
  • 存储有当前数据以及三个隐式字段

2. 事务 B 写入并提交

同时另外开启一个 B 事务完成了写数据并且提交。

3. 事务 A 再次快照读

然后事务 A 再快照读，具体读到 B 事务修改之后的还是之前的，取决于当前事务的隔离级别：

RC（读已提交）隔离级别下
  • 每次快照读都会生成并获取最新的 Read View
  • 所以对于 B 事务的提交对它有影响
  • 会读到 B 事务提交后的数据
RR（可重复读）隔离级别下
  • 同一个事务中的第一次快照读才会创建 Read View
  • 之后的快照读都是使用的第一个 Read View
  • 所以事务 B 对它没影响
  • 不会读到 B 事务提交后的数据

十一、大表优化

1. 限定数据的范围

务必禁止不带任何限制数据范围条件的查询语句。

📝 例如：当用户在查询订单历史时，我们可以控制在一个月的范围内。

2. 读/写分离

经典的数据库拆分方案：

  • 主库负责写
  • 从库负责读

3. 垂直分区

定义：根据数据库里面数据表的相关性进行拆分。

示例：

  • 用户表中既有用户的登录信息又有用户的基本信息
  • 可以将用户表拆分成两个单独的表
  • 甚至放到单独的库做分库

简单来说：垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。

优点

  • ✅ 可以使得列数据变小
  • ✅ 在查询时减少读取的 Block 数，减少 I/O 次数
  • ✅ 垂直分区可以简化表的结构，易于维护

缺点

  • ❌ 主键会出现冗余，需要管理冗余列
  • ❌ 会引起 Join 操作，可以通过在应用层进行 Join 来解决
  • ❌ 垂直分区会让事务变得更加复杂

4. 水平分区

定义：保持数据表结构不变，通过某种策略存储数据分片。

原理：

  • 这样每一片数据分散到不同的表或者库中
  • 达到了分布式的目的
  • 水平拆分可以支撑非常大的数据量

触发条件：表的行数超过 200 万行时，就会变慢

简单来说：水平拆分是指数据表行的拆分。

📝 例如：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成的影响。

十二、MySQL 主从复制

优缺点

特性说明
优点读写分离，高性能
缺点数据不一致，有延迟

意义

  1. ✅ 数据实时备份：当系统中某个节点发生故障时，可以方便地故障切换
  2. ✅ 高可用 HA
  3. ✅ 架构扩展：支持读写分离

三个线程的作用

1. Log Dump Thread（主节点）

工作流程：

  • 当有数据写入 master 时，先写入 data，然后再写入 binlog 日志
  • Log Dump Thread 会读取 binlog 日志
  • 同时对 binlog 进行加锁
  • 当读取完成，甚至在发送给从节点之前，锁会被释放
  • 然后将内容发送给 Slave 从节点

2. I/O Thread（从节点）

工作流程：

  • 当从节点上执行 start slave 命令之后
  • 从节点会创建一个 I/O 线程用来连接主节点
  • 请求主库中更新的 binlog
  • I/O 线程接收到主节点 binlog dump 进程发来的更新之后
  • 保存在本地 relay-log 中

3. SQL Thread（从节点）

工作流程：

  • SQL Thread 线程会从 relay log 中读取对应的信息
  • 然后执行 SQL
  • 解析成具体操作
  • 最终达到数据一致性

复制的基本过程

  1. 从节点上的 I/O 进程连接主节点，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容

  2. 主节点接收到来自从节点的 I/O 请求后，通过负责复制的 I/O 进程根据请求信息读取指定日志指定位置之后的日志信息，返回给从节点
    • 返回信息中除了日志所包含的信息之外
    • 还包括本次返回的信息的 bin-log file 的以及 bin-log position
  3. 从节点的 I/O 进程接收到内容后：
    • 将接收到的日志内容更新到本机的 relay log 中
    • 并将读取到的 binary log 文件名和位置保存到 master-info 文件中
    • 以便在下一次读取的时候能够清楚地告诉 Master：”我需要从某个 bin-log 的哪个位置开始往后的日志内容，请发给我”
  4. Slave 的 SQL 线程检测到 relay-log 中新增加了内容后：
    • 会将 relay-log 的内容解析成在主节点上实际执行过的操作
    • 并在本数据库中执行

主从复制模式

1. 异步模式

特点：

  • 主节点不会主动 push bin log 到从节点
  • 这样有可能导致 failover 的情况下
  • 也许从节点没有及时地将最新的 bin log 同步到本地

2. 半同步模式

特点：

  • 主节点只需要接收到其中一台从节点的返回信息，就会 commit
  • 否则需要等待直到超时时间然后切换成异步模式再提交

优点：

  • ✅ 可以使主从数据库的数据延迟缩小
  • ✅ 可以提高数据安全性
  • ✅ 确保了事务提交后，binlog 至少传输到了一个从节点上

缺点：

  • ⚠️ 不能保证从节点将此事务更新到 db 中
  • ⚠️ 性能上会有一定的降低，响应时间会变长

3. 全同步模式

特点：

  • 主节点和从节点全部执行了 commit 并确认才会向客户端返回成功

4. GTID 复制模型

GTID：Global Transaction ID（全局事务 ID）

作用：

  • 保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的 ID

binlog 日志记录格式

MySQL 主从复制有三种方式，对应的 binlog 文件的格式也有三种：

1. STATEMENT

Statement-based Replication (SBR)

特点：

  • 记录 SQL 语句在 bin log 中
  • MySQL 5.1.4 及之前的版本都是使用的这种复制格式

优点：

  • ✅ 只需要记录会修改数据的 SQL 语句到 binlog 中
  • ✅ 减少了 binlog 日志量，节约 I/O，提高性能

缺点：

  • ❌ 在某些情况下，会导致主从节点中数据不一致
  • 例如：sleep()、now() 等函数

2. ROW

Row-based Replication (RBR)

特点：

  • MySQL master 将 SQL 语句分解为基于 Row 更改的语句并记录在 bin log 中
  • 也就是只记录哪条数据被修改了，修改成什么样

优点：

  • ✅ 不会出现某些特定情况下的存储过程、函数、trigger 的调用或触发无法被正确复制的问题

缺点：

  • ❌ 会产生大量的日志，尤其是修改 table 的时候会让日志暴增
  • ❌ 同时增加 bin log 同步时间
  • ❌ 不能通过 bin log 解析获取执行过的 SQL 语句，只能看到发生的 data 变更

3. MIXED

Mixed-format Replication (MBR)

特点：

  • MySQL NDB cluster 7.3 和 7.4 使用的 MBR
  • 是以上两种模式的混合
  • 对于一般的复制使用 STATEMENT 模式保存到 binlog
  • 对于 STATEMENT 模式无法复制的操作则使用 ROW 模式来保存
  • MySQL 会根据执行的 SQL 语句选择日志保存方式

十三、面试题

1. CHAR 和 VARCHAR 区别

特性CHARVARCHAR
长度固定长度（创建表时声明）
长度值范围是 1 到 255		可变长度
存储方式用空格填充到特定长度按实际长度存储
检索检索时需删除尾随空格按原样检索
存储空间可能浪费空间节省空间
性能略快（不需要计算长度）略慢（需要计算长度）

2. BLOB 和 TEXT 有什么区别？

特性BLOBTEXT
定义二进制对象不区分大小写的 BLOB
存储内容可变数量的二进制数据可变数量的文本数据
排序和比较区分大小写不区分大小写

唯一区别：BLOB 和 TEXT 类型之间的唯一区别在于对 BLOB 值进行排序和比较时区分大小写，对 TEXT 值不区分大小写。

3. 一个表可以创建多少个索引？

任何标准表最多可以创建 16 个索引列。

4. 锁优化策略

  1. ✅ 读写分离
  2. ✅ 分段加锁
  3. ✅ 减少锁持有的时间
  4. ✅ 多个线程尽量以相同的顺序去获取资源

5. 慢 SQL 优化

开启慢 SQL 日志

慢 SQL 优化的话，我们可以：

  1. 先开启慢 SQL 日志
  2. 定义一个我们系统认为慢的超时时间

分析步骤

1. 分析 Explain
1

EXPLAIN SELECT * FROM table WHERE ...;

重点关注：

① type：索引是否失效（是否走了预想的索引）

② rows：是否扫描了额外的记录

③ key, possible_key：结合 type 分析

2. 定位问题

常见问题：

  • 查询的数据过大
  • SQL 语句的问题

解决方案

解决数据过大：重构查询

① 复杂查询 vs 多个简单查询

  • 根据具体情况决定将 SQL 写成一个复杂查询，还是多个简单查询
  • 在应用程序中合并结果集

② 切分操作

  • 例如：删 10 万条数据拆分成 10 次操作

③ 分解关联查询

  • 分为单表查询
  • 将结果集在应用程序进行关联

④ 覆盖索引

  • 尽量让二级索引实现覆盖索引
  • 避免回表
解决 SQL 语句问题

SQL Select 语句完整的执行顺序

逻辑执行顺序：

  1. FROM 子句：组装来自不同数据源的数据
    • FROM 后面的表关联，是自右向左解析的
    • 即在写 SQL 时，尽量把数据量大的表放在最右边来进行关联
  2. WHERE 子句：基于指定的条件对记录行进行筛选
    • WHERE 条件的解析顺序是自下而上，从右到左的
    • 即应把能筛选出大量数据的条件放在 WHERE 语句的最下面

  3. GROUP BY 子句：将数据划分为多个分组

  4. 使用聚集函数进行计算

  5. HAVING 子句：筛选分组

  6. 计算所有的表达式

  7. SELECT 的字段

  8. ORDER BY：对结果集进行排序

  9. LIMIT：选择指定数量或比例的行，返回给调用者

SQL 查询处理的步骤顺序

1
2
3
4
5
6
7
8
9
10

(8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
(12) LIMIT <limit_condition>

💡 重要：以上每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。

SQL 优化建议

（1）WHERE 语句优化

避免使用的操作：

  • ❌ 避免在 WHERE 子句中使用 IN、NOT IN、OR 或者 HAVING
  • ✅ 可以使用 EXISTS 和 NOT EXISTS 代替 IN 和 NOT IN
  • ✅ 对连续数值可以使用 BETWEEN
  • ✅ 可以使用表连接代替 EXISTS
  • ✅ HAVING 可以用 WHERE 代替，如果无法代替可以分两步处理

其他注意事项：

  • ❌ 应尽量避免在 WHERE 子句中对字段进行函数操作
  • ❌ 尽量避免在 WHERE 子句中对索引字段进行计算操作
  • ❌ 应尽量避免在 WHERE 子句中使用 != 或 <> 操作符
  • ❌ 应尽量避免在 WHERE 子句中对字段进行 null 值判断
  • ❌ 应尽量避免在 WHERE 子句中使用 OR 来连接条件

⚠️ 否则将导致引擎放弃使用索引而进行全表扫描

（2）SELECT 语句优化

*① 避免 SELECT **

  • ❌ 尽量不要使用 SELECT * FROM table 这种方式
  • ✅ 把要查询的具体字段列出来，不要返回任何用不到的字段

② 优先使用变长字段

  • ✅ 尽可能使用 VARCHAR/NVARCHAR 代替 CHAR/NCHAR
  • 因为变长字段存储空间小，可以节省存储空间
（3）连接语句优化

UNION vs UNION ALL：

操作特点
UNION会将各查询子集的记录做比较
自动去掉重复记录
速度较慢
UNION ALL不去重
速度更快

💡 建议：如果使用 UNION ALL 能满足要求的话，尽量使用 UNION ALL。

（4）Count(*) 和 Count(1) 以及 Count(column) 的区别

性能对比：

  • 一般情况，SELECT COUNT(*) 和 SELECT COUNT(1) 返回结果一样
  • 假如表没有主键（Primary key），那么 COUNT(1) 比 COUNT(*) 快
  • 如果有主键，那主键作为 count 的条件时 COUNT(主键) 最快
  • 如果你的表只有一个字段，那 COUNT(*) 最快

NULL 值处理：

操作是否包含 NULL
COUNT(*)✅ 包含
COUNT(1)✅ 包含
COUNT(column)❌ 不包含
（5）查询的模糊匹配

❌ 尽量避免在一个复杂查询里面使用 LIKE '%parm1%'

原因：百分号会导致相关列的索引无法使用，最好不要用。

（6）复杂操作

拆分复杂 SQL：

  • 部分 UPDATE、SELECT 语句写得很复杂（经常嵌套多级子查询）
  • 可以适当拆成几步
  • 先生成一些临时表，再进行关联操作

优先使用数字型字段：

  • ✅ 尽量使用数字型字段
  • ❌ 若只含数值信息的字段尽量不要设计为字符型

原因：

  • 这会降低查询和连接的性能，并会增加存储开销
  • 因为引擎在处理查询和连接时会逐个比较字符串中每一个字符
  • 而对于数字型而言只需要比较一次就够了

📚 参考资料：本文内容基于个人学习笔记整理

数据库, MySQL
MySQL 索引 事务 MVCC 主从复制
本文由作者按照 CC BY 4.0 进行授权