MySQL 复习总结（一）

前言

数据库的内容相对来说有点多，计划分两篇文章整理完。

本篇内容主要集中于对MySQL底层的理解。

ps：本文内容均由书籍、网络等方式整理而来，会加入一些个人见解，如有侵权可以联系我删除。如有错误也欢迎批评指正。

---

数据库三大范式

原子性、唯一性、独立性

---

MySQL 查询语句的执行流程

首先要明确MySQL的架构

• Server 层负责建立连接、分析和执行 SQL。
• 存储引擎层负责数据的存储和提取。

图片来自@小林coding

当我们键入一条select语句后，MySQL底层会先后做出这样的反应：

第一步：连接

服务器与客户端进行 TCP 三次握手建立连接；（一般采用长连接）

第二步：查询缓存

MySQL 会去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。（MySQL 8.0 版本直接将查询缓存删掉了，也就是说 MySQL 8.0 开始，执行一条 SQL 查询语句，会跳过这一步）

第三步：解析 SQL——解析器

1. 词法分析

MySQL 会根据输入的字符串识别出关键字出来（比如select、from这些）

2. 语法分析

• 根据词法分析结果，语法解析器会根据语法规则，判断输入的这个 SQL 语句是否满足 MySQL 语法，如果没问题就会构建出 SQL 语法树，方便后续模块读取表名、字段、语句类型；
• 但是不会去查表或者字段存不存在。

第四步：执行SQL

1.  prepare 阶段（预处理）

• 检查 SQL 查询语句中的表或者字段是否存在；
• 将 select * 中的 * 符号，扩展为表上的所有列；

2. optimize 阶段（优化）

• 优化器主要负责将 SQL 查询语句的执行方案确定下来，
• 基于查询成本的考虑，选择查询成本最小的执行计划；

3. execute 阶段（执行）

• 根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端（此时执行器开始与存储引擎交互）

---

存储引擎

对于存储引擎的理解

• 存储引擎层负责数据的存储和提取。
• 支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。
• 现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。
• 我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

InnoDB数据的读写方式

• InnoDB 的数据是按「数据页」为单位来读写的，默认数据页大小为 16 KB。每个数据页之间通过双向链表的形式组织起来，物理上不连续，但是逻辑上连续。
• 数据页内包含用户记录，每个记录之间用单项链表的方式组织起来，为了加快在数据页内高效查询记录，设计了一个页目录，页目录存储各个槽（分组），且主键值是有序的，于是可以通过二分查找法的方式进行检索从而提高效率。

InnoDB与MyISAM的区别

是否支持行级锁

• MyISAM 只有表级锁，
• InnoDB 支持行级锁和表级锁,默认为行级锁。

是否支持事务

• MyISAM 不提供事务支持。
• 只有InnoDB 提供事务支持。

是否支持外键

• MyISAM 不支持外键（外键是关系型数据库中用于建立表与表之间关系的字段）
• InnoDB 支持外键

是否支持数据库异常崩溃后的安全恢复

• MyISAM 不支持，
• InnoDB 支持。（数据库在异常崩溃后重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复过程依赖于 redo log（重做日志））

是否支持 MVCC

• MyISAM 不支持，
• InnoDB 支持。

索引实现不一样

 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构

• InnoDB 引擎中，其数据文件本身就是索引文件（聚簇索引）

• MyISAM中索引文件和数据文件是分离的（非聚簇索引）

---

索引

对于索引的理解

索引就是帮助存储引擎快速获取数据的一种数据结构

形象的说，索引就是数据的目录。

索引的优缺点

优点：

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
• 在使用分组和排序子句进行数据检索时，同时可以显著减少查询中分组和排序的时间。

缺点：

• 创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大。
• 创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用及空间也越大（数据表占据的是数据库的数据空间）
• 会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长

Mysql中的索引类型

按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。

按「物理存储」分类：聚簇索引、非聚簇索引。

按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。

按「字段个数」分类：单列索引、联合索引

聚簇索引和非聚簇索引

默认使用的都是 B+Tree 索引

对聚簇索引和非聚簇索引的理解

聚簇索引：将数据存储和索引放到了一块，找到了索引也就找到了数据

这里个人认为要先理解主键索引和聚簇索引的区别：

• 一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。
• 一般情况下，聚簇索引等同于主键索引，但有一个前提条件：这张表需要有主键。只有在有了主键的情况下，它才能拥有主键索引，从而等同于聚簇索引。
• 但是聚簇索引并不完全等于主键索引，因为一张表在结构上可以没有主键（索引）。如果没有主键（索引），那么聚簇索引就不再是主键索引了

非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行。（换个说法是，保存了数据的物理地址）

是在聚集索引之上创建的索引。

访问数据总是需要二次查找，第一次找到主键值，第二次根据主键值找到行数据：

两者的区别

简单来说：

聚集索引存储记录是物理上连续存在，

⾮聚集索引是逻辑上的连续，物理存储并不连续。

在于：叶子节点存放的是什么数据，导致查询数据的途径也不一样

• 聚簇索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户数据都存放在聚簇索引的叶子节点，可以通过索引直接找到数据
• 非聚簇索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

如果用 product_no 二级索引查询商品，如下查询语句：

select * from product where product_no = '0002';

会先检索非聚簇索引中的 B+Tree 的索引值（商品编码，product_no（图中橙色部分）），找到对应的叶子节点，然后获取主键值（图中绿色部分），

然后再通过主键索引中的 B+Tree 树查询到对应的叶子节点，然后获取整行数据。

——这个过程就叫「回表」，也就是说要查两个 B+Tree 才能查到数据。

通过非聚簇索引找到主键，由聚簇索引再找到需要的数据的过程（图源@小林coding）

聚簇索引的B+树

• B+Tree 是一种多叉树
• 叶子节点才存放数据，非叶子节点只存放索引
• 每个节点里的数据是按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息
• 每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表。

聚簇索引的B+树（图源@小林coding）

非聚簇索引的B+树

聚簇索引的B+树（图源@小林coding）

ps:索引的内容还剩下一些，抽空再补充。