mysql中select和update语句执⾏过程
前⾔
本⽂基于MySQL5.7版本。
前⾯⼏篇MySQL系列的⽂章介绍了索引，事务和锁相关知识，那么今天就让我们来看看当我们执⾏⼀条select语句和⼀条update语句的时候,MySQL要经过哪些步骤，才能返回我们想要的数据。
⼀条select语句的执⾏流程
MySQL从⼤⽅向来说，可以分为 Server 层和存储引擎层。⽽Server层包括连接器、查询缓存、解析器、预处理器、优化器、执⾏器等，最后Server层再通过API接⼝形式调⽤对应的存储引擎层提供的接⼝。如下图所⽰(图⽚来源于《⾼性能MySQL》)：
在这⾥插⼊图⽚描述
根据流程图，⼀条select查询⼤致经过以下六个步骤：
1、客户端发起⼀个请求时，⾸先会建⽴⼀个连接
2、服务端会检查缓存，如果命中则直接返回，否则继续之后后⾯步骤
3、服务器端根据收到的sql语句进⾏解析，然后对其进⾏词法分析，语法分析以及预处理
4、由优化器⽣成执⾏计划
5、调⽤存储引擎层API来执⾏查询
6、返回查询到的结果
查询流程也可以通过如下图表⽰(图⽚来源于丁奇MySQL45将)：
在这⾥插⼊图⽚描述
建⽴连接
第⼀步建⽴连接，这个很容易理解，需要特别指出的是MySQL服务端和客户端的通信⽅式采⽤的是半双⼯协议。
通信⽅式主要可以分为三种：单⼯，半双⼯，全双⼯，如下图：
在这⾥插⼊图⽚描述
单⼯：通信的时候，数据只能单向传输。⽐如说遥控器，我们只能⽤遥控器来控制电视机，⽽不能⽤电视机来控制遥控器。
半双⼯：通信的时候，数据可以双向传输，但是同⼀时间只能有⼀台服务器在发送数据，当A给B发送数据的时候，那么B就不能给A发送数据，必须等到A发送结束之后，B才能给A发送数据。⽐如说对讲机。
全双⼯：通信的时候，数据可以双向传输，并且可以同时传输。⽐如说我们打电话或者⽤通信软件进
⾏语⾳和视频通话等。
半双⼯协议让MySQL通信简单快速，但是也在⼀定程度上限制了MySQL的性能，因为⼀旦从⼀端开始发送数据，另⼀端必须要接收完全部数据才能做出响应。所以说我们批量插⼊的时候尽量拆分成多次插⼊⽽不要⼀次插⼊太⼤数据，同样的查询语句最好也带上limit限制条数，避免⼀次返回过多数据。
MySQL单次传输数据包的⼤⼩可以通过参数max_allowed_packet控制，默认⼤⼩为4MB
SHOW VARIABLES LIKE 'max_allowed_packet';
在这⾥插⼊图⽚描述
查询缓存
连接上了之后，如果缓存是打开的，那么就会进⼊查询缓存阶段，可以通过如下命令查看缓存是否开启：
SHOW VARIABLES LIKE 'query_cache_type';
在这⾥插⼊图⽚描述
我们可以看到，缓存默认是关闭的。这是因为MySQL的缓存使⽤条件⾮常苛刻，是通过⼀个⼤⼩写敏感的哈希值去匹配的，这样就是说⼀条查询语句哪怕只是有⼀个空格不⼀致，都会导致⽆法使⽤缓存。⽽且⼀旦表⾥⾯有⼀⾏数据变动了，那么关于这种表的所有缓存都会失效。所以⼀般我们都是不建议使⽤缓存，MySQL最新的8.0版本已经将缓存模块去掉了。
解析器和预处理器
跳过了缓存模块之后，查询语句会进⼊解析器进⾏解析。
词法解析和语法解析(Parser)
这⼀步主要的⼯作就是检查sql语句的语法对不对，在这⾥，⾸先会把我们整个SQL语句打碎，⽐如：select name from test where
id=1，就会被打散成select，name，from，test，where，id，=，1 这8个字符，并且能识别出关键字和⾮关键字，然后根据sql语句⽣成⼀个数据结构，也叫做解析树(select_lex)，如下图：
sql优化的几种方式
在这⾥插⼊图⽚描述
预处理器(Preprocessor)
经过了前⾯的词法和语法解析，那么⾄少我们⼀条sql语句的语法格式是满⾜要求了，接下来我们还需要做什么呢？⾃然是检查表名，列名以及其他⼀些信息等是不是真实存在的，预处理就是做⼀个表名和字段名等相关信息合法性的检测。
查询优化器(Query Optimizer)
经过上⾯的步骤，到这⾥就得到了⼀句有效的sql语句了。⽽对⼀个查询语句，尤其是复杂的多表查询
语句，我们可以有很多种执⾏⽅式，每种执⾏⽅式的效率也不⼀样，所以这时候就需要查询优化器去选择⼀种它认为最⾼效的执⾏⽅式。
查询优化器的⽬的就是根据解析树⽣成不同的执⾏计划（Execution Plan），然后选择⼀种最优的执⾏计划，MySQL ⾥⾯使⽤的是基于开销（cost）的优化器，哪种执⾏计划开销最⼩，就选择哪种。
我们可以通过变量Last_query_cost来查询开销：
1. SELECT * FROM test;
2. show status like 'Last_query_cost';
在这⾥插⼊图⽚描述
上图中展⽰的结果就表⽰MySQL认为SELECT * FROM test 查询语句需要做⾄少2个数据页的随机查才能完成上⾯的查询。
这个结果是通过⼀系列复杂的运算得到的，包括每个表或者索引的页⾯个数，索引的基数，索引和数
据⾏的长度，索引分布的情况。
优化器在评估成本的时候，不会考虑任何缓存的作⽤，⽽是假设读取任何数据都需要经过⼀次IO操作。
优化器可以做哪些优化
优化器可以替我们做很多优化，下⾯列举⼀些常⽤的优化：
重新定义关联的顺序。优化器并不⼀定按照我们写的查询关联语句中的关联顺序，⽽是会按照优化后的顺序进⾏查询。
将外连接转为为内连接。
使⽤等价转换原则。⽐如a<b and a=5会被转换为a=5 and b>5
优化COUNT(),MIN()和MAX()
预估并转化为常数表达式
覆盖索引扫描。想要详细了解覆盖索引的可以点击这⾥。
的查询优化。
提前终⽌查询。⽐如我们使⽤了⼀个不成⽴的条件，则会⽴刻返回空。
等值传播。
优化IN()语句。在其他很多数据库中in等同于or语句，但是MySQL中会讲in中的值先进⾏排序，然后按照⼆分查的⽅法来确定是否满⾜条件。
实际当中优化器能做的优化远远⽐上⾯列举的更多，所以有时候我们不要觉得⽐优化器更聪明，所以⼤部分情况下我们都可以让优化器做出优化就可以了，如果有些我们确定优化器没有选择最优的查询⽅案，我们也可以在查询中通过添加hint提⽰告知到优化器，⽐如通过force index强制使⽤索引或者straight_join语句强制优化器按我们想要的表顺序进⾏关联。
优化器并不是万能的
MySQL优化器也并不是万能的，并不是总能把我们写的糟糕的sql语句优化成⼀个⾼效的查询语句，⽽且也有很多种原因会导致优化器做出错误的选择：
统计信息不准确。MySQL评估成本依赖于存储引擎提供的的统计信息，然⽽存储引擎提供的统计信息有时候会有较⼤偏差。
执⾏计划的成本估算不等于实际的执⾏成本。⽐如估算成本的时候不考虑缓存，⽽实际执⾏有些数据在缓存中。
优化器认为的最优可能并不是我们需要的最优。⽐如有时候我们想要时间最短，但是优化器
优化器从不考虑其他并发的查询。
优化器并不总是基本成本的优化。有时候也会基于规则，⽐如当存在全⽂索引，查询时使⽤了match()⼦句时，即使选择其他索引更优，优化器仍然会选择全⽂索引。
优化器不将不受其控制的操作计算为成本。如执⾏存储过程或者⽤户⾃定义函数的成本。
优化器有时候⽆法估算所有的执⾏计划，所以也有可能错过最优执⾏计划。
优化器如何得到查询计划
优化器听起来⽐较抽象，给⼈⼀种看不见摸不着的感觉，但是实际上我们也可以通过参数打开优化器追踪，优化器追踪默认是关闭的，因为开启后会影响性能，所以建议是在需要定位问题的时候开启，并及时关闭。
1. SHOW VARIABLES LIKE 'optimizer_trace';
2. set optimizer_trace='enabled=on';
接下来执⾏⼀句查询语句：
SELECT t1.name AS name1,t2.name AS name2 FROM test t1 INNER JOIN test2 t2 ON t1.id=t2.id
这时候优化器的分析过程已经被记录下来了，可以通过下⾯语句查询：
SELECT * FROM information_schema.optimizer_trace;
得到如下结果：
上⾯的图是为了看数据效果，如果需要⾃⼰操作的话，需要⽤shelll命令窗⼝去执⾏，sqlyog⼯具中直接查询出来TRACE列是空的，shell 中返回的TRACE列信息如下：
从截图中的轮廓可以看出来这是⼀个json数据格式。
跟踪信息主要分为以下三部分(上图并未将全部内容展⽰出来，感兴趣的可以⾃⼰去尝试⼀下，开启之后记得及时关闭哦)：
准备阶段(join_preparation)：expanded_query中的查询语句就是优化后的sql
优化阶段(join_optimization)：considered_execution_plans中列出来所有的执⾏计划
执⾏阶段(join_execution)
存储引擎查询
当Server层得到了⼀条sql语句的执⾏计划后，这时候就会去调⽤存储引擎层对应的API，执⾏查询了。因为MySQL的存储引擎是插件式的，所以每种存储引擎都会对Server提供了⼀些对应的API调⽤。
返回结果
最后，将查询出得到的结果返回Server层，如果开启了缓存，Server层返回数据的同时还会写⼊缓存。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。