MySql优化（一）一条SQL语句的执行过程是怎样的？

写在前面&＃xff1a;

• 我热爱技术&＃xff0c;热爱分享&＃xff0c;热爱生活&＃xff0c; 我始终相信&＃xff1a;技术是开源的&＃xff0c;知识是共享的&＃xff01;
• 博客里面的内容大部分均为原创&＃xff0c;是自己日常的学习记录和总结&＃xff0c;便于自己在后面的时间里回顾&＃xff0c;当然也是希望可以分享自己的知识。目前的内容几乎是基础知识和技术入门&＃xff0c;如果你觉得还可以的话不妨关注一下&＃xff0c;我们共同进步&＃xff01;
• 个人除了分享博客之外&＃xff0c;也喜欢看书&＃xff0c;写一点日常杂文和心情分享&＃xff0c;如果你感兴趣&＃xff0c;也可以关注关注&＃xff01;
• 微信公众号&＃xff1a;傲骄鹿先生

一条查询SQL是如何执行的&＃xff1f;

程序或者工具要操作数据库&＃xff0c; 第一步跟数据库建立连接

1、通信协议

首先&＃xff0c; MySQL 必须要运行一个服务&＃xff0c; 监听默认的端口&＃xff08;3306&＃xff09;。

通信协议&＃xff08;MySQL 支持多种通信协议。&＃xff09;

• 第一个就是 TCP/IP 协议&＃xff0c; 编程语言的连接模块都是用 TCP 协议连接到 MySQL 服务器的&＃xff0c; 比如&＃xff1a;mysql-connector-java-x.x.xx.jar
• 第二种是 Unix Socket。 比如我们在 Linux 服务器&＃xff0c; 不用通过网络协议&＃xff0c; 也可以连接到 MySQL 的服务器&＃xff0c; 它需要用到服务器上的一个物理文件&＃xff08;mysql.sock&＃xff09; 。
• 另外还有命名管道&＃xff08; Named Pipes&＃xff09; 和内存共享&＃xff08; Share Memory&＃xff09; 的方式。

通信方式

MySQL 使用半双工的通信方式。半双工意味着要么是客户端向服务端发送数据&＃xff0c; 要么是服务端向客户端发送数据&＃xff0c; 这两个动作不能同时发生。

所以客户端发送 SQL 语句给服务端的时候&＃xff0c; &＃xff08; 在一次连接里面&＃xff09; 数据是不能分成小块发送的&＃xff0c; 不管你的 SQL 语句有多大&＃xff0c; 都是一次性发送。

如果发送给服务器的数据包过大&＃xff0c; 我们必须要调整 MySQL 服务器配置 max_allowed_packet 参数的值&＃xff08; 默认是 4M&＃xff09; 。

另一方面&＃xff0c; 对于服务端来说&＃xff0c; 也是一次性发送所有的数据&＃xff0c; 不能因为你已经取到了想要的数据就中断操作。所以&＃xff0c; 我们一定要在程序里面避免不带 limit 的这种操作。

连接方式

MySQL 既支持短连接&＃xff0c; 也支持长连接。 短连接就是操作完毕以后&＃xff0c; 马上 close 掉。 长连接可以保持
打开&＃xff0c; 后面的程序访问的时候还可以使用这个连接。
长时间不活动的连接&＃xff0c; MySQL 服务器会断开。
show global variables like &＃39;wait_timeout&＃39;; &＃xff08;非交互式超时时间&＃xff0c; 如 JDBC 程序&＃xff09;

show global variables like &＃39;interactive_timeout&＃39;; &＃xff08;交互式超时时间&＃xff0c; 如 数据库工具&＃xff09;&＃xff0c;默认是28800s&＃xff0c;8小时。

MySQL 默认的最大连接数是 151 个 &＃xff0c; 最大是 16384&＃xff08;2^14&＃xff09; 。

使用 SHOW FULL PROCESSLIST;查看查询的执行状态。

一些常见的状态&＃xff1a;

2、 查询缓存(Query Cache)
MySQL 内部自带了一个缓存模块。 默认是关闭的。 主要是因为 MySQL 自带的缓存的应用场景有限&＃xff0c; 第一个是它要求 SQL 语句必须一模一样。 第二个是表里面任何一条数据发生变化的时候&＃xff0c; 这张表所有缓存都会失效。
3、 语法解析和预处理(Parser & Preprocessor)
假如我们随便执行一个字符串或者查询一个不存在表里面的数据&＃xff0c;会进行报错&＃xff0c;这个就是 MySQL 的 Parser 解析器和 Preprocessor 预处理模块。

这一步主要做的事情是对 SQL 语句进行词法和语法分析和语义的解析。

词法解析
词法分析就是把一个完整的 SQL 语句打碎成一个个的单词。

比如一个简单的 SQL 语句&＃xff1a;select name from user where id &＃61; 1;它会打碎成 8 个符号&＃xff0c; 记录每个符号是什么类型&＃xff0c; 从哪里开始到哪里结束。

语法解析
第二步就是语法分析&＃xff0c; 语法分析会对 SQL 做一些语法检查&＃xff0c;

比如单引号有没有闭合&＃xff0c; 然后根据 MySQL定义的语法规则&＃xff0c; 根据 SQL 语句生成一个数据结构。 这个数据结构我们把它叫做解析树。

预处理器&＃xff08;Preprocessor&＃xff09;
如果表名错误&＃xff0c; 会在预处理器处理时报错。它会检查生成的解析树&＃xff0c; 解决解析器无法解析的语义。 比如&＃xff0c; 它会检查表和列名是否存在&＃xff0c; 检查名字和别名&＃xff0c; 保证没有歧义。

4、 查询优化&＃xff08;Query Optimizer&＃xff09; 与查询执行计划

什么是优化器&＃xff1f;
这里存在一个问题&＃xff1a;一条 SQL 语句是不是只有一种执行方式&＃xff1f; 或者说数据库最终执行的 SQL 是不是就是我们发送的 SQL&＃xff1f;
这个答案是否定的。 一条 SQL 语句是可以有很多种执行方式的。 但是如果有这么多种执行方式&＃xff0c; 这些执行方式怎么得到的&＃xff1f; 最终选择哪一种去执行&＃xff1f; 根据什么判断标准去选择&＃xff1f;
这个就是 MySQL 的查询优化器的模块&＃xff08;Optimizer&＃xff09; 。
查询优化器的目的就是根据解析树生成不同的执行计划&＃xff0c; 然后选择一种最优的执行计划&＃xff0c; MySQL 里面使用的是基于开销&＃xff08;cost&＃xff09; 的优化器&＃xff0c; 那种执行计划开销最小&＃xff0c; 就用哪种。
使用如下命令查看查询的开销&＃xff1a;
show status like &＃39;Last_query_cost&＃39;;
如果我们想知道优化器是怎么工作的&＃xff0c; 它生成了几种执行计划&＃xff0c; 每种执行计划的 cost 是多少&＃xff0c; 应该怎么做&＃xff1f;

优化器是怎么得到执行计划的&＃xff1f;

https://dev.mysql.com/doc/internals/en/optimizer-tracing-typical-usage.html

首先我们要启用优化器的追踪&＃xff08;默认是关闭的&＃xff09; &＃xff1a;

启用优化器的追踪是会消耗性能的&＃xff0c;因为他要把优化分析的结果写到表里面去&＃xff0c;接着再执行一个sql语句&＃xff0c;优化器会生成执行计划&＃xff0c;这个时候优化器分析的的过程已经记录到系统表里面去了。

优化器可以做什么&＃xff1f;
MySQL 的优化器能处理哪些优化类型呢&＃xff1f;
比如&＃xff1a;
1、 当我们对多张表进行关联查询的时候&＃xff0c; 以哪个表的数据作为基准表。
2、 select * from user where a&＃61;1 and b&＃61;2 and c&＃61;3&＃xff0c; 如果 c&＃61;3 的结果有 100 条&＃xff0c; b&＃61;2 的结果有 200条&＃xff0c; a&＃61;1 的结果有 300 条&＃xff0c; 你觉得会先执行哪个过滤&＃xff1f;
3、 如果条件里面存在一些恒等或者恒不等的等式&＃xff0c; 是不是可以移除。
4、 查询数据&＃xff0c; 是不是能直接从索引里面取到值。
5、 count()、 min()、 max()&＃xff0c; 比如是不是能从索引里面直接取到值。
6、 其他。
优化器得到的结果
优化器最终会把解析树变成一个查询执行计划&＃xff0c; 查询执行计划是一个数据结构。当然&＃xff0c; 这个执行计划是不是一定是最优的执行计划呢&＃xff1f; 不一定&＃xff0c; 因为 MySQL 也有可能覆盖不到所有的执行计划。
MySQL 提供了一个执行计划的工具。 我们在 SQL 语句前面加上 EXPLAIN&＃xff0c; 就可以看到执行计划的信息。
EXPLAIN select name from user where id&＃61;1;

5、 存储引擎&＃xff08;Storage Engine&＃xff09;

我们的数据是放在哪里的&＃xff1f; 执行计划在哪里执行&＃xff1f; 是谁去执行&＃xff1f;

存储引擎基本介绍
在关系型数据库里面&＃xff0c; 数据是放在表里面的。 我们可以把这个表理解成 Excel 电子表格的形式。所以我们的表在存储数据的同时&＃xff0c; 还要组织数据的存储结构&＃xff0c; 这个存储结构就是由我们的存储引擎
决定的&＃xff0c; 所以我们也可以把存储引擎叫做表类型。在 MySQL 里面&＃xff0c; 支持多种存储引擎&＃xff0c; 他们是可以替换的&＃xff0c; 所以叫做插件式的存储引擎。

为什么要搞这么多存储引擎呢&＃xff1f; 一种还不够用吗&＃xff1f;
是因为我们在不同的业务场景中对数据操作的要求不同&＃xff0c; 这些不同的存储引擎通过提供不同的存储机制、 索引方式、 锁定水平等功能&＃xff0c; 来满足我们的业务需求。

查看存储引擎

查看当前数据库所支持的存储引擎&＃xff1a;SHOW ENGINES;

查看数据库表的存储引擎&＃xff1a;SHOW TABLE STATUS FROM &＃96;setalone&＃96;;

在 MySQL 里面&＃xff0c; 我们创建的每一张表都可以指定它的存储引擎&＃xff0c; 它不是一个数据库只能使用一个存储引擎。 而且&＃xff0c; 创建表之后还可以修改存储引擎。
数据库存放数据的路径&＃xff1a;
show variables like &＃39;setalone&＃39;;
每个数据库有一个自己文件夹&＃xff0c;任何一个存储引擎都有一个 frm 文件&＃xff0c; 这个是表结构定义文件。

关于存储引擎后续再进行详细探讨。

7、 执行引擎&＃xff08; Query Execution Engine&＃xff09; &＃xff0c; 返回结果
执行引擎&＃xff0c; 它利用存储引擎提供了相应的 API 来完成对存储引擎的操作。 最后把数据返回给客户端&＃xff0c; 即使没有结果也要返回。