死磕mysql-一条查询语句是如何执行的？

我不是女神ヾ 2023-03-01 13:29 9阅读 0赞

# 0 废话 #

数据库作为系统的持久化工具，以其高效CRUD效率以及它的ACID特性已经成为我们软件设计不可或缺的一部分。在平时工作、学习和面试的过程中一定会遇到很多关于sql优化的问题，那么我们应该如何更好的来掌握mysql的知识来更好的应对工作中遇到的问题和面试过程中面试官的追问呢？  
在接触过不少项目的人肯定知道，其实很多项目中我们都会使用mysql，那么一款存储软件为何占据这么大的市场呢？很显然是它能够解决我们大部分的问题，还有一点就是开源免费。从mysql的应用范围，以及它在这个软件辈出的年代它可以一直这么稳健，可以看出mysql还是很多优点的。那么如此优秀的软件又解决了我们这么多问题，那么这款软件肯定也不是谁都可以代替，或者也不能是很简单就可以把所有的原理都搞定。所以在学习mysql 优化的时候不要急于求成，学习是一个循序渐进的过程，需要条理清晰各个击破。

不知道大家对于学习有什么好的方法，我个人的学习方法就是：想学一个东西，首先是快速的了解这个东西是干嘛的，快速的在脑海里面形成一棵树，或者一个大的骨架，比如：学习spring的时候我首先搭建一个spring的项目，然后启动项目可以实现从一个url调到后台打印日志，ok。不知道你在第一次实现的时候是否兴奋不已，然后在这个基础之上加mysql，mybatis，shiro，mongo……等等。那么我们学习mysql的时候如何入手呢？首先是对mysql的基本语法有一个比较熟练的认识才比如CURD， ACID，join，索引，引擎，等等。对于应用层面的东西有一个熟练的掌握之后，对与常用的数据的定义有一个清楚的认识之后，才可以步入sql优化的阶段。因为sql的优化如果是仅仅表面的去看，或者记别的经验是非常难受的一件事情。因为废了很大的劲儿还是没有达到融会贯通的效果，如果是我其实还是很心虚的。那么问题来了我们需要怎么想学习才可以达到融会贯通的效果呢？知其然知其所以然。在现在这个信息爆炸的年代，或许我们获取知识的的成本更低了，但是我们的诱惑更多了，可能我们很想好好学习一门技术，但是技术的浮躁乏味，往往比不过抖音，B站有有诱惑力。给自己立个flag，重新做人哈哈，至少做个讲原则的人，可以娱乐但是也要保证学习时间。over

# 1 查询sql的执行流程 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw_size_16_color_FFFFFF_t_70]  
mysql 整体分为三部分：client，server，innodb。因为mysql的存储引擎是以插件的形式存在的所以并不包含在mysql 的server里面。

## 1.1 链接 ##

客户端连接服务端的方式是多种多样的，可以是同步的也是异步的，可以长连接也可以是短连接，可以是TCP也可以是Unix Socket（win还有命名管道和内存共享）。

我们怎么查看myql当前多少个连接？  
可用show status命令，模糊匹配Thread： `show global status like 'Thread%'`  
![在这里插入图片描述][20200728094524561.png]  
1、Thread\_cached 缓存的线程连接数  
2、Thread\_connected当前断开的连接数  
3、Thread\_created为处理链接创建的线程数  
4、Thread\_sunning非睡眠状态的连接数，通常指并发连接数

问题：为什么是查看线程？客户端的连接服务端的线程有什么关系？  
没产生一个连接或者一个会话，在服务端就会创建一个线程来处理。反过来如果杀死会话，就是kill掉线程。  
既然是分配线程的话，保持链接肯定会消耗服务端的资源。mysql会把长时间不活动的链接自动断开。默认28800秒也就是8小时。

show VARIABLES like 'max_connections%'

![在这里插入图片描述][2020072809582445.png]  
通过如上指令我们可以看到mysql允许的最大链接数是151，最大连接数是100000。  
[max\_connections官网链接][max_connections]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw_size_16_color_FFFFFF_t_70 1]  
mysql中的参数分为session和global级别，分别是在当前会话中生效和全局生效，如上图如果scope是global则是全局的，如果包含global也包含session则这个参数需要分别设置。默认是session如果需要查看global或者设置global的时候需要加上global参数。如果想了解更详细的东西请去官网查询，其实在学习的过程中官网才是最好的学习资料，建议学习的时候以官网为主，以其他资料为辅。

## 1.2 缓存 ##

先来看看mysql的缓存设置`show VARIABLES like 'query_cache%'`

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw_size_16_color_FFFFFF_t_70 2]  
我们看到了5个参数，那分别是什么意思呢？

### query\_cache\_limit ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw_size_16_color_FFFFFF_t_70 3]  
我用的mysql5.7，上图参数的意思是缓存限制的大小（1048576b = 1024\*1024b = 1024 \* 1K = 1M）。

### query\_cache\_min\_res\_unit ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw_size_16_color_FFFFFF_t_70 4]  
查询缓存的最小单元默认是4096k = 4\*1024b = 4k

### query\_cache\_size ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw_size_16_color_FFFFFF_t_70 5]  
默认分配的缓存大小

### query\_cache\_type ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw_size_16_color_FFFFFF_t_70 6]  
缓存类型有三种，1 关闭 2 打开 3 按需

query\_cache\_wlock\_invalidate myisam存储引擎的锁限制。这里就不做展示了，如果想了解可以去官网看一下。

### 小结 ###

我们在上面的参数中发现，在8.0这些参数已经被删除（查询缓存已经移除），而且默认缓存是关闭的。我们都知道缓存可以提高我们的查询效率那么我们我什么要关闭呢？  
默认关闭的意思就是不推荐使用，为什么mysql不推荐使用它自带的缓存呢？1、主要是因为mysql自带的缓存场景有限，例如中间多一个空格，字母大小写不通都会被任务是不通的sql。这样存在很多冗余缓存。  
2、第二个是表里面的任何一条数据返生变化的时候，这张表所有的缓存都会失效，所以对于有大量数据更新的应用，也不适合。  
缓存这一块我们可以交给ORM框架，（比如mybatis默认开启了一级缓存）或者独立缓存服务，比如redis来处理缓存更合适。

## 1.3 语法解析和预处理（Parser & Preprocessor） ##

为什么我的一条sql语句能够被识别呢？它怎么知道我执行的DDL还是DML还是DQL？  
比如我们随便执行一个字符串`zheshiyishouxiaoqing`  
zheshiyishouxiaoqing  
报错如下：

> 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘zheshiyishouxiaoqing’ at line 1  
> 时间: 0.013s

这就是语法分析器和词法分析器的作用，  
那么预处理是什么东西呢？个人理解，预编译的作用是把将要执行的sql编译成为执行指令，预留参数站位，后续只需要传入参数即可，这样可以防止sql注入。

### 1.3.1 词法解析 ###

在语法分析器里面有词法解析和语法解析，词法分析就是把一个完整的sql语句打碎一个个的单次。  
比如一个简单的sql语句：

select name from user where id = 1

这是为语法分析器做准备。

### 1.3.2 语法分析器 ###

语法分析会对sql做一些语法检查，比如单引号有没有闭合等等。  
根据mysql定义的语法规则，根据sql语句生成一个数据结构。这个数据结构我们叫他解析数（select\_lex）

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw_size_16_color_FFFFFF_t_70 7]  
词法语法分析是一个非常基础的功能，编译，搜索引擎如果要识别语句，必须也要有词法语法分析器功能。  
任何数据的中间件，要解析sql完成路由功能，也必须要有词法和语法分析功能，比如mycat，sharding-jdbc用到了（durid parser）。在市面上也有很多的开源词法解析的工具（LEX，Yacc）。

### 1.3.3 预处理器 ###

问题：如果我写了一个词法和语法都正确的sql，比如：

select * from uu；

但是表名或者字段不存在，会在哪里报错？是解析的时候报错还是执行的时候报错？

似乎解析器可以分析语法，但是他怎么知道数据库里面有什么表？表里有什么字段呢？哪它必须把表结构存起来才可以。

实际上还是在解析的时候报错，解析sql的环节有个预处理器。预处理器会检查生成解析数，解决解析器无法解析的语意。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。

预处理之后生成一颗新的解析树。 可以看做将要执行的执行树。

## 1.4 查询优化（Query Optimizer）与查询执行计划 ##

### 1.4.1 什么是优化器 ###

解析树是一个可以被执行器认识的数据结构，得到解析树之后，是不是可以执行然后操作数据了呢？  
这里我们有一个问题，一条sql语句是不是只有一种执行方式？或者说数据库最终执行的sql是不是就是我们发送的SQL？  
这个答案是否定的。一条sql语句可以有很多种执行方式的，最终返回相同的结果，他们是等价的。但是如果有这么多执行方式，这些执行方式怎么得到的？最终选择哪一种去执行？根据什么判断标准去选择？  
这个就是mysql查询优化器的模块Optimizer所要做要做的工作。

### 1.4.2 优化器可以做什么？ ###

查询优化器的主要作用就是根据解析树生成不通的执行计划（Execution Plan）,然后选择一种最优的执行计划。  
注意：mysql里面使用的是基于成本（cost）的优化器，哪种执行成本最小就用哪种。  
处理执行路径的选择之外，优化器还可以对sql语句进行自动优化。  
mysql的优化器能处理哪些优化类型呢？  
举个例子：  
1、当我们对多张表进行关联查询的时候，一下那个表的数据作为基表（先访问那张表）  
2、有多个索引可以使用的时候，选择哪个索引。  
3、对于查询条件的优化，比如移除 1=1 之类的恒等式，移除不必要的括号，表达式的计算，子查询和链接查询优化  
……  
所以优化器对于所有的数据库来说都是非常重要的一个模块。  
那是不是有了优化器之后，我的sql语句就可以随便写了？  
肯定不是，不然世界上根本不存在慢sql了，所以优化器也不是万能的。

经过优化器处理之后就会得到一条执行路径，我们把它叫做执行计划，执行计划也是一个数据结构。  
当然这个执行计划也不一定是最优的执行计划，因为mysql也有可能覆盖不到所有的执行计划。

### 1.4.3 优化器得到结果 ###

我们怎么查看mysql的执行计划呢？比如多张表关联查询，先插那张表？在执行查询的时候可能用到哪些索引？实际上用到什么索引？  
mysql提供了一个执行计划的工具。我们在sql语句前面加上explain，就可以看到执行计划的信息。

EXPLAIN select name freom user where id = 1

如果想要得到详细的信息，还可以用Format=JSON ，或者开启optimizer trace

EXPLAIN FORMAT = JSON select name from user where id = 1

## 1.5 存储引擎 ##

得到执行计划这个数据结构之后，执行器是不是终于可以执行了？  
问题又来了：从逻辑角度来说，或者说从数据库用户看到的表面，我们的数据放在那里的，或者说放在一个什么结构里面？

未完……

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw_size_16_color_FFFFFF_t_70]: /images/20230208/8425866bae6e40ed93f788a321a18967.png
[20200728094524561.png]: https://img-blog.csdnimg.cn/20200728094524561.png
[2020072809582445.png]: https://img-blog.csdnimg.cn/2020072809582445.png
[max_connections]: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_connections
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200728100700392.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200728101606498.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200728101837472.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200728102307486.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200728103155212.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200728103835450.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200728114350234.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bwd3dw,size_16,color_FFFFFF,t_70