1 MySQL组件

1.1 MySQL整体架构

MySQL内部组件结构图.png

客户端：如navicat，sql server 等等。

Service层：从 Service 层和引擎层才是属于 MySQL Server 部分的组件。连接器、查询缓存（很鸡肋，MySQL8移除了该组件）、词法分析器、优化器、执行器。

引擎层：真正执行 SQL，对数据进行读写的地方。

1.2 组件

通过整体架构图可以知道 MySQL 里面重要的组件有哪些，它们执行的顺序是怎样的。现在我们来看下它们具体的职责。

1.2.1 连接器

接收客户端连接，进行认证授权的组件。

1.2.2 词法分析器

处理流程如下图

分析器工作流程图.png

词法分析
首先扫描文本，再识别 词元/token（每个词元代表一个基本的语法单元，如关键字、标识符、操作符、常量、字符串等）。生成有序的词元流(token stream)给到语法分析器。
语法分析 + 分析机
语法分析器会对词元流进行进一步的检查：
- 语法分析
  检查代码是否符合编程语言的语法规则，也就是是否遵循正确的语法结构。如果源代码包含语法错误，语法分析器会发现并报告错误，通常以编译错误的形式呈现。
- 语义分析
  语义分析阶段关注的是源代码的含义和语义规则。它确保代码在语法上正确的基础上也是有意义的，不会违反编程语言的语义规则。语义分析通常更加高级和复杂，因为它需要考虑语言的语义含义。
构建抽象语法树（Abstract Syntax Tree，AST）
SQL 语句的分析分为词法分析与语法分析，mysql 的词法分析由 MySQLLex（MySQL自己实现的）完成，语法分析由 Bison 生成。关于语法树可以参考：https://en.wikipedia.org/wiki/LR_parser。除了 Bison 外，Java 当中也有开源的词法结构分析工具例如 Antlr4，ANTLR 从语法生成一个解析器，可以构建和遍历解析树，可以在 IDEA 工具当中安装插件 antlr v4 grammar plugin。

1.2.3 优化器

分析 SQL，生成执行计划

优化器会对我们的 SQL 进行分析，看预计使用哪些索引，SQL 的执行顺序如何，实际会使用哪些索引（没有真的执行SQL），使用索引的情况等等。其实这些无非就是我们 explain 返回的执行计划那些信息。

1.2.4 执行器

执行器承担的任务相对加多，需要分场说明：

查询计划优化
这个场景通常配合优化器一起执行，优化器生成执行计划后，执行器负责执行计划。
查询执行
执行器负责执行SELECT查询语句，它会将查询语句发送到数据库引擎中的查询处理器，然后将查询结果返回给用户或应用程序。这包括从表中检索数据、应用筛选条件、排序和分组等操作，以满足用户的查询需求。
插入、更新和删除操作
执行器负责执行 INSERT、UPDATE 和 DELETE 等数据修改操作。它将这些操作请求传递给数据库引擎中的数据修改处理器，以便在数据库中插入新数据、更新现有数据或删除数据。
事务管理
执行器管理数据库事务的开始、提交和回滚。事务是一组SQL操作的逻辑单元，可以确保数据的一致性和完整性。执行器负责启动新事务、提交已完成的事务以及在必要时回滚事务。
并发控制
数据库可能会同时接收多个查询和事务请求，执行器需要进行并发控制，以确保多个事务之间的数据访问不会互相干扰。这包括锁管理、隔离级别的实现等。
错误处理
执行器负责处理SQL执行过程中可能出现的错误，它会将错误信息返回给用户或应用程序，以便进行故障排除和处理。

2 存储引擎与索引

存储引擎也是 MySQL 的重要组件，但是它会涉及到索引。而索引则属于数据结构，并且重要程度并不比组件低。因此，这里需要拆出来单独写。

存储引擎的作用是什么呢？读写磁盘，实现数据持久化。

SHOW ENGINES;

MySQL 支持很多的存储引擎，我们可以通过上面的 SQL 查询得到，下图是笔者的查询结果

存储引擎的种类.png

这里主要介绍 MyISAM 和 InnoDB。

2.1 MyISAM

使用 MyISAM 的表会生成三个文件，具体如下图

MyISAM的表保存数据对应的文件.png

*.frm : 存放表结构数据
*.MYD : 存放表记录
*.MYI : 存放索引

MyISAM 的表的主键索引和非主键索引都是非聚簇索引。

非聚簇索引指的是叶子节点存放 ID（主键），不存放记录信息。

非聚簇索引数据结构如下图（这用了主键索引做示例）

MyISAM索引图.png

优点：在高频读，低频写的场景下，读的性能十分好。
缺点：1、不支持事务；2、不支持行锁，支持表锁，所以多并发写数据场景下，性能差。

2.2 InnoDB

我们最常用的存储引擎就是 InnoDB

我们先看下在操作系统中，使用 InnoDB 会生成什么文件，请看下图

InnoDB的表保存数据对应的文件.png

*.frm：数据表结构相关信息存储的文件
*.idb：索引和数据存储的文件

聚簇索引就是索引和和非索引字段放在一起，也就是一行完整的记录，并且一个表最多只能有一个聚簇索引。

2.2.1 主键索引

使用 InnoDB 的表有且仅有一个聚簇索引。下面以主键索引为例（我的user表就只有三个字段）

InnoDB聚簇索引图.png

2.2.1.1 尽量使用自增整型主键

大部分有名的互联网公司的 DBA 都建议 InnoDB 表使用自增整型主键。

为什么要用主键？
如果表没有主键，那么MySQL会在该表内找一个具有唯一性的字段作为主键并构建主键索引；如果找不到具有唯一性的字段，就会创建一个符合主键要求的隐藏列，使用它作为主键并构建主键索引。因此，无论如何都是需要主键的，那么不如自己明确主键。
为什么大厂都建议主键尽量用自增整型？
这和构建主键索引有关系。
- 整型
  1. 比较大小，整型更简单
  2. 整型占用空间小，一个是节约空间，另外一个就是一次IO可以读取更多数据
- 自增
  B+Tree 要求叶子节点是有序的，所以当我们主键是单调递增时，直接链尾插入就好，节约了查询的时间，而且后面插入的数据一定再前面插入的数据后面的话，MySQL 还可以使用顺序IO提高性能。

2.2.2 二级索引

二级索引是非聚集的，主要是为了节约空间。二级索引是先通过过滤条件找到主键，通过主键回表得到结果集。

InnoDB二级索引图.png

2.2.3 联合索引（复合索引）

假如现在我有个用户表，用户表有4个字段：username、telephone、age、sex。
我们可以建两种类型的联合索引：联合主键，普通的联合索引。

2.2.3.1 联合主键

username、sex 构建成联合主键

联合主键索引.png

先根据名字转成的ascii码进行排序，如果 ascii 码一样，那么再根据性别的 ascii 码大小比较排序。

2.2.3.2 普通的联合索引

这个和上面的没差太多，只是 data 存的是主键，需要回表查找。

2.2.4 分表与索引

我们常说数据量太大了，要分表。阿里也给出了需要分表的一些参考值，推荐单表数据量超过 500W 或者表超过了 10G 时，应该分表。但是，这是阿里工程师们根据自己接触阿里内部的各种业务场景得出来的经验值！针对业务情况的不同，索引设置的不同，那么分表的阈值自然也就不同。

方法论
一个业务一般是有自己的预期收益，这个预期的收益是可以估算出我们技术设计里面表所需要的数据量的基本值。通过了解，这个业务的后续发展和使用预想，我们可以相应地去看这个数据量的增量，看是稳定后不会有太多增量，还是预想发展好会持续有增量的。然后我们就可以得出我们需不需要分表，要分多少张表了的结论了。

案例

业务背景
业务上需要用主播直播中的高光时刻的画面给主播做推荐。高光时刻不用管，这是大数据+AI得出的结论，我们只是根据它们的要求查出对应时间段的静图/动图。我们真正需要做的是存一段时间直播流推过来的图片而已。
具体数据
平均每日开播主播数量：8000+
平均开播时长：2H左右
直播流记录截图频率：5s/帧
图库表字段
id(8B)+uid(8B)+live_id(8B)+pic_url(1B * 100)+create_time(8B)
需要查询的时间范围是7天，也就是说超过7天可归档

教你如何优化SQL，干货满满