图解mysql——基础篇

mysql执行流程

Server层按顺序执行sql的步骤为：

1.客户端请求->
2.连接器（验证用户身份，给予权限） ->
3.查询缓存（存在缓存则直接返回，不存在则执行后续操作）->
4.分析器（对SQL进行词法分析和语法分析操作） ->
5.优化器（主要对执行的sql优化选择最优的执行方案方法） ->
6.执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）->
7.去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

概括总结：

• 连接器：建立连接，管理连接、校验用户身份；
• 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
• 解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
• 执行 SQL：执行 SQL 共有三个阶段：
  1.预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
  2.优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
  3.执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；
  

1.连接器(管理连接、权限验证)

1.要使用mysql，首先要连接mysql服务
2.传输协议：TCP协议

命令

# -h 指定 MySQL 服务得 IP 地址，如果是连接本地的 MySQL服务，可以不用这个参数；
# -u 指定用户名，管理员角色名为 root；
# -p 指定密码，如果命令行中不填写密码（为了密码安全，建议不要在命令行写密码），就需要在交互对话里面输入密码
mysql -h$ip -u$user -p      连接mysql服务

show processlist     查看mysql服务的客户端
show variables like 'wait_timeout'  查看空闲连接的最大空闲时长
kill connection + id  手动断开空闲的连接
show variables like 'max_connections' 查看mysql最大连接数

长连接与短连接

// 短连接
连接 mysql 服务（TCP 三次握手）
执行sql
断开 mysql 服务（TCP 四次挥手）

// 长连接
连接 mysql 服务（TCP 三次握手）
执行sql
执行sql
执行sql
....
断开 mysql 服务（TCP 四次挥手）

长连接的好处就是可以减少建立连接和断开连接的过程，所以一般是推荐使用长连接

总结：

• 与客户端进行 TCP 三次握手建立连接；
• 校验客户端的用户名和密码，如果用户名或密码不对，则会报错；
• 如果用户名和密码都对了，会读取该用户的权限，然后后面的权限逻辑判断都基于此时读取到的权限

2.查询缓存（存在缓存则直接返回，不存在则执行后续操作

mysql8.0之前有

流程：
1.连接器工作结束，mysql服务收到客户端的SQL语句
2.解析SQL语句的第一个字段，分析类型
3.假如是select就会先去查询缓存，查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。
4.有就返回没有就会继续执行，等执行完后，查询的结果就会被存入查询缓存中。

命令：

SET GLOBAL query_cache_type = DEMAND;  关闭查询缓存

mysql8.0之后没有

TIP
这里说的查询缓存是 server 层的，
也就是 MySQL 8.0 版本移除的是 server 层的查询缓存，
并不是 Innodb 存储引擎中的 buffer pool。

3.分析器：对SQL进行词法分析、语法分析

词法分析。MySQL 会根据你输入的字符串识别出关键字出来，构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。

语法分析。根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

注意
表不存在或者字段不存在，并不是在解析器里做的，《MySQL 45 讲》说是在解析器做的， MySQL 源码（5.7和8.0）得出结论是解析器只负责构建语法树和检查语法，但是不会去查表或者字段存不存在。

询问ChatGPT看到的有趣的事儿

4.执行SQL

经过解析器后，接着就要进入执行 SQL 查询语句的流程了，每条SELECT 查询语句流程主要可以分为下面这三个阶段：

• prepare 阶段，也就是预处理阶段；
• optimize 阶段，也就是优化阶段；
• execute 阶段，也就是执行阶段；

预处理器

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

小林coding上的证明过程

上面的中间部分是 MySQL 报错表不存在时的函数调用栈，可以看到表不存在的错误是在get_table_share() 函数里报错的，而这个函数是在 prepare 阶段调用的。

不过，对于 MySQL 5.7 判断表或字段是否存在的工作，是在词法分析&语法分析之后，prepare 阶段之前做的。结论都一样，不是在解析器里做的。代码我就不放了，正因为 MySQL 5.7 代码结构不好，所以 MySQL 8.0 代码结构变化很大，后来判断表或字段是否存在的工作就被放入到 prepare 阶段做了。

优化器

优化器主要负责将 SQL 查询语句的执行方案确定下来

例子

在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。

这时 product 表就有主键索引（id）和普通索引（name）。假设执行了这条查询语句：

select id from product where id > 1  and name like 'i%';

这条查询语句的结果既可以使用主键索引，也可以使用普通索引，但是执行的效率会不同。这时，就需要优化器来决定使用哪个索引了。

很显然这条查询语句是覆盖索引，直接在二级索引就能查找到结果（因为二级索引的 B+ 树的叶子节点的数据存储的是主键值），就没必要在主键索引查找了，因为查询主键索引的 B+ 树的成本会比查询二级索引的 B+ 的成本大，优化器基于查询成本的考虑，会选择查询代价小的普通索引。

在下图中执行计划，我们可以看到，执行过程中使用了普通索引（name），Exta 为 Using index，这就是表明使用了覆盖索引优化。

执行器

用三种方式执行过程，跟大家说一下执行器和存储引擎的交互过程

• 主键索引查询
• 全表扫描
• 索引下推

主键索引查询

select * from product where id = 1;

执行流程：

• 执行器第一次查询，会调用 read_first_record 函数指针指向的函数，因为优化器选择的访问类型为 const，这个函数指针被指向为 InnoDB 引擎索引查询的接口，把条件 id = 1 交给存储引擎，让存储引擎定位符合条件的第一条记录。
• 存储引擎通过主键索引的 B+ 树结构定位到 id = 1的第一条记录，如果记录是不存在的，就会向执行器上报记录找不到的错误，然后查询结束。如果记录是存在的，就会将记录返回给执行器；
• 执行器从存储引擎读到记录后，接着判断记录是否符合查询条件，如果符合则发送给客户端，如果不符合则跳过该记录。
• 执行器查询的过程是一个 while 循环，所以还会再查一次，但是这次因为不是第一次查询了，所以会调用 read_record 函数指针指向的函数，因为优化器选择的访问类型为 const，这个函数指针被指向为一个永远返回 - 1 的函数，所以当调用该函数的时候，执行器就退出循环，也就是结束查询了。

全表扫描

select * from product where name = 'iphone';

执行流程：

• 执行器第一次查询，会调用 read_first_record 函数指针指向的函数，因为优化器选择的访问类型为 all，这个函数指针被指向为 InnoDB 引擎全扫描的接口，让存储引擎读取表中的第一条记录；
• 执行器会判断读到的这条记录的 name 是不是 iphone，如果不是则跳过；如果是则将记录发给客户的（是的没错，Server 层每从存储引擎读到一条记录就会发送给客户端，之所以客户端显示的时候是直接显示所有记录的，是因为客户端是等查询语句查询完成后，才会显示出所有的记录）。
• 执行器查询的过程是一个 while 循环，所以还会再查一次，会调用 read_record 函数指针指向的函数，因为优化器选择的访问类型为 all，read_record 函数指针指向的还是 InnoDB 引擎全扫描的接口，所以接着向存储引擎层要求继续读刚才那条记录的下一条记录，存储引擎把下一条记录取出后就将其返回给执行器（Server层），执行器继续判断条件，不符合查询条件即跳过该记录，否则发送到客户端；
• 一直重复上述过程，直到存储引擎把表中的所有记录读完，然后向执行器（Server层） 返回了读取完毕的信息；
• 执行器收到存储引擎报告的查询完毕的信息，退出循环，停止查询。

索引下推

select * from t_user  where age > 20 and reward = 100000;

注意：联合索引当遇到范围查询 (>、<) 就会停止匹配，也就是 age 字段能用到联合索引，但是 reward 字段则无法利用到索引

执行流程（不使用索引下推 mysql5.6之前）

• Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录；
• 存储引擎根据二级索引的 B+ 树快速定位到这条记录后，获取主键值，然后进行回表操作，将完整的记录返回给 Server 层；
• Server 层在判断该记录的 reward 是否等于 100000，如果成立则将其发送给客户端；否则跳过该记录；
• 接着，继续向存储引擎索要下一条记录，存储引擎在二级索引定位到记录后，获取主键值，然后回表操作，将完整的记录返回给 Server 层；
• 如此往复，直到存储引擎把表中的所有记录读完。

执行流程（使用索引下推）

• Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录；
• 存储引擎定位到二级索引后，先不执行回表操作，而是先判断一下该索引中包含的列（reward列）的条件（reward 是否等于 100000）是否成立。如果条件不成立，则直接跳过该二级索引。如果成立，则执行回表操作，将完成记录返回给 Server 层。
• Server 层在判断其他的查询条件（本次查询没有其他条件）是否成立，如果成立则将其发送给客户端；否则跳过该记录，然后向存储引擎索要下一条记录。
• 如此往复，直到存储引擎把表中的所有记录读完。

总结： 使用索引下推节省了 很多回表操作。
因为把判断reward=100000的任务交给了存储引擎。