⼀条mysql查询语句的执⾏过程
当我们执⾏⼀条MySQL查询语句时，对于客户端⽽⾔是⼀个很简单的过程，但对于服务端来说其内部却会涉及到⼀些复杂的组件和处理逻辑。了解MySQL语句的内部执⾏原理，有助于我们更好地去处理⼀些复杂的SQL语句，帮助我们定位和解决问题。
MySQL体系结构
Connector：⽤来⽀持各种语⾔和SQL的交互，⽐如PHP、Python、Java的JDBC等；
Management Serveices & Utilities：系统管理和控制⼯具，包括备份恢复、MySQL复制、集等；
Connection Pool：连接池，管理需要缓冲的资源，包括⽤户密码、权限、线程等；
SQL Interface：⽤来接收⽤户的 SQL 命令，返回⽤户需要的查询结果；
Parser：⽤来解析SQL语句；
Optimizer：查询优化器；
Cache and Buffer：查询缓存，除了⾏记录的缓存之外，还有表缓存、Key 缓存、权限缓存等；
Pluggable Storage Engines：插件式存储引擎，它提供API给服务层使⽤，跟具体的⽂件打交道。
在上述结构的基础上，我们可以⼤致将MySQL分为三层，分别是连接层、服务层以及存储引擎层。
连接层：
负责管理客户端与MySQL服务器的所有连接，包含验证客户端的⾝份和权限。
服务层：
简单的mysql语句实现了MySQL的⼤多数核⼼服务功能，查询解析、分析、优化、缓存以及其他所有的内置函数、所有的跨存储引擎的功能都在这⼀层实现（⽐如存储过程、触发器、视图等）。
当连接层将SQL语句转交给服务层时，服务层会做出进⼀步的处理，对我们的SQL语句进⾏词法分析和语法分析（⽐如关键字识别、别名识别、语法检查等），然后就是优化器根据⼀定的规则对我们的SQL语句进⾏优化，最后再交给执⾏器去执⾏。
存储引擎层：
数据真正存放的地⽅，再往下就是内存或者磁盘。
MySQL⽀持不同的存储引擎（包含MYISAM、INNODB、MEMORY等），负责数据的存储和提取。现在最常⽤的存储引擎是INNODB，它从MySQL 5.5.5版本开始就成为了默认的存储引擎。也就是说，当我们执⾏create table语句创建数据表时，如果没有显⽰使⽤"engine=memory"来指定什么类型的存储引擎，那么MySQL默认为我们使⽤的就是INNODB。
不同存储引擎的表数据存取⽅式各有不同，⽀持的功能也不相同，但它们都共⽤⼀个Server层。Server层通过存储引擎API来与它们进⾏交互，这些接⼝屏蔽了不同存储引擎之间的差异，使得这些差异对于上层的查询⽽⾔尽可能地透明。这些API包含⼏⼗个底层函数，⽤于执⾏诸如“开始⼀个事务”或者“根据主键提取⼀⾏记录”等操作。
存储引擎不能解析SQL，互相之间也不能通信，只是单纯地响应上层服务器的请求罢了。
SQL语句的执⾏过程
step1：使⽤连接器与客户端建⽴连接
执⾏SQL语句操作数据库的前提是要先建⽴连接，连接器在这⾥负责与客户端建⽴连接、获取权限、维持和管理连接。
在完成了经典的TCP握⼿之后，连接器就要开始认证你的⾝份了，这时候⽤的就是你输⼊的⽤户名和密码：
1. 如果⽤户名或密码不正确，MySQL会报"Access denied for user"的错误，然后客户端程序结束执⾏；
2. 如果⽤户名密码都认证通过，连接器就会到权限表⾥去查询你所拥有的权限。之后在这个连接⾥⽤到的权限判断逻辑都是基于此时读
到的权限。也就是说，即使你⽤管理员账号对该⽤户的权限作出了修改，也不会影响到已经存在连接的权限，只会影响到后续新建的连接。
连接完成后，如果你没有后续的操作，那么这个连接就会处于空闲状态，⽽当客户端如果太长时间没有操作，连接器就会⾃动将它断开，这个时间由参数"wait_timeout"来控制，默认值是8⼩时，我们也可以⽤SQL语句来查看默认时间"show global variables like
‘wait_timeout’;"。
如果在连接被断开之后，客户端再次发送请求的话，就会收到⼀个错误提⽰"Lost connection to MySQL server during query"，这时候就需要重连再执⾏请求了。
在数据库中有两种连接：
1. 短连接：操作完毕后马上close掉，下次查询再重新建⽴⼀个；
2. 长连接：保持打开，减少服务端创建和释放连接的消耗，后⾯的程序访问的时候还可以使⽤这个连接。
由于建⽴连接的过程通常是⽐较复杂的，⾸先要发送请求，然后要去验证账号密码，验证通过后还得去查看你所拥有的权限，因此建议在使⽤中尽量要减少连接建⽴的动作，也就是尽量使⽤长连接。
但是全部使⽤长连接后，你可能会发现，有些时候MySQL的占⽤内存涨得特别快，这是因为MySQL在执⾏过程中临时使⽤的内存是管理在连接对象⾥⾯的。这些资源会在连接断开的时候才释放，所以如果长连接累积下来，可能导致内存占⽤太⼤，被系统强⾏杀掉（OOM），从现象看就是MySQL异常重启了。
如何解决这个问题呢，可以从以下⽅⾯考虑：
1. 定期断开长连接。使⽤⼀段时间，或者程序⾥⾯判断执⾏过⼀个占⽤内存的⼤查询后，断开连接，之后要查询再重连；
2. 如果你⽤的是MySQL 5.7或更新的版本，可以在每次执⾏⼀个⽐较⼤的操作后，通过执⾏"mysql_reset_connection"来重新初始化
连接资源，这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。
step2：查询缓存，有则直接返回
连接建⽴完成之后，你就可以开始执⾏SQL语句了，这⾥便会进⼊第⼆步：查询缓存；
MySQL内部⾃带了⼀个缓存模块，MySQL在拿到⼀个查询请求后，⾸先会先到查询缓存中看看之前是不是执⾏过这条语句，之前执⾏过的语句及其结果可能会以"key-value"键值对的形式被直接缓存在内存中（key是查询的语句，value是查询的结果）。如果你的查询能够直接在这个缓存中到key，那么这个value就会被直接返回给客户端。如果语句不在查询缓存中，就会继续后⾯的执⾏阶段。执⾏完成后，执⾏结果会被存⼊查询缓存中。你可以看到，如果查询命中缓存，MySQL甚⾄不需要执⾏后⾯的复杂操作，就可以直接返回结果，这个效率会很⾼。
我们可以将参数"query_cache_type"设置成DEMAND，这样对于默认的SQL语句就都不会使⽤查询缓
存，⽽对于你确定要使⽤查询缓存的语句，可以⽤"SQL_CACHE"显⽰指定，⽐如"select SQL_CACHE * from table where id = 1;"。
但是⼤多数的情况下并不建议怎么做，为什么呢？因为查询缓存往往弊⼤于利。⾸先查询缓存的失效⾮常频繁，只要有对⼀个表的更新，那么这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使⽤就被另⼀个更新全清空了。对于更新压⼒⼤的数据库来说，查询缓存的命中率会⾮常低，除⾮你的业务就是有⼀张静态表，很长时间才会更新⼀次，⽐如⼀个系统配置表，那这张表上的查询才适合使⽤查询缓存。其次，查询缓存要求SQL语句必须是⼀模⼀样，中间多⼀个空格、字母⼤⼩写不同都会被认为是不同的SQL。所以缓存还是交给ORM框架（⽐如MyBatis默认是开启了⼀级缓存）或者独⽴的缓存服务（⽐如Redis）来处理更加合适。
注意：在MySQL 8.0版本中，查询缓存的整个功能已经被移除了！
step3：语法解析和预处理
这⼀步要做的事情就是MySQL的解析器和预处理模块对语句进⾏基于SQL语法的词法、语法分析和语义的解析。
解析器如果没有命中查询缓存，就要开始真正执⾏语句了。MySQL⾸先需要知道你要做什么，因此需要对SQL语句做解析。
解析器先会做“词法分析”，把⼀个完整的SQL语句打碎成⼀个个的单词。假设你输⼊的是由多个字符串和空格组成的⼀条SQL语句，对于MySQL⽽⾔，它需要识别出⾥⾯的字符串分别是什么、代表什么。MySQL从你输⼊的"select"这个关键字识别出来这是⼀个查询语句，同时它也要把字符串"T"识别成“表名T”，把字符串"ID"识别成“主键id”。
做完上述这些识别分析以后，就要开始做“语法分析”，根据词法分析的结果，语法分析器会根据语法规则，判断你输⼊的这个SQL语句是否满⾜MySQL语法（⽐如单引号有没有闭合等），然后就会根据SQL语句⽣成⼀个数据结构，这个数据结构我们⼀般称之为解析树（select_lex）。如果你的语句不对，就会收到"You have an error in your SQL syntax"的错误提⽰。
完成了上述两步的分析识别后，解析环节中的预处理器会检查⽣成的解析树，解决解析器⽆法解析的语义，⽐如它会检查表名或者字段是否存在、检查名字和别名是否有歧义等，预处理过后会得到⼀个新的解析树。
step4：查询优化器确定SQL语句的执⾏⽅案
经过了解析环节后，MySQL就知道你要做什么了，但在真正开始执⾏SQL前，我们还需要先经过优化器的处理。
⼀条SQL语句可以有很多种执⾏⽅式，它们最终返回的结果是相同等价的。但是如果有这么多种执⾏⽅式，这些执⾏⽅式怎么得到的？最终选择哪⼀种去执⾏？根据什么样的判断标准去选择？这个就是MySQL的查询优化器的模块（Optimizer）需要做的事了。
查询优化器的⽬的就是根据解析树⽣成不同的执⾏计划（Execution Plan），然后选择⼀种最优的执⾏计划，MySQL⾥⾯使⽤的是基于开销（cost）的优化器，即哪种执⾏计划开销最⼩就⽤哪种。我们可以使⽤命令"show status like ‘Last_query_cost’;“来查看查询的开销。
对于每⼀种数据库来说，优化器的模块都是必不可少的，它们通过复杂的算法实现尽可能地优化查询效率的⽬标。优化器在数据表中存在多个索引的时候，决定使⽤哪个索引，或者在⼀个语句有多表关联（join）的时候，决定各个表的连接顺序。但优化器也不是万能的，并不是再垃圾的SQL语句都能⾃动优化，也不是每次都能选择到最优的执⾏计划，所以⼤家在编写SQL语句的时候还是要注意。
优化器最终会把解析树变成⼀个执⾏计划（execution_plans），执⾏计划是⼀个数据结构。当然这个执⾏计划不⼀定是最优的执⾏计划，因为MySQL也有可能覆盖不到所有的执⾏计划。那么我们怎么查看MySQL的执⾏计划呢？⽐如多张表关联查询，先查询哪张表？在执⾏查询的时候可能⽤到哪些索引，实际上⽤到了什么索引？这⾥MySQL为我们提供了⼀个执⾏计划的⼯具，我们可以在SQL语句前加上EXPLAIN，就能看到执⾏计划的信息了，⽐如"EXPLAIN select * from table;”。
在优化器阶段完成后，这个语句的执⾏⽅案就确定下来了。
step5：执⾏器执⾏SQL语句
MySQL通过解析器知道了你要做什么，通过优化器知道了该怎么做，于是就进⼊了执⾏器阶段，开始执⾏语句。
开始执⾏的时候，要先判断你对这个表有没有执⾏查询的权限：
1. 如果没有，就会返回没有权限的错误。在⼯程实现上，如果命中了查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会
在优化器之前调⽤precheck验证权限。
2. 如果有权限，就打开表继续执⾏。打开表的时候，执⾏器就会根据表的存储引擎定义，去使⽤这个存储引擎所提供的API接⼝。
在数据库的慢查询⽇志中我们可以看到⼀个"rows_examined"的字段，表⽰这个语句在执⾏过程中扫描了多少⾏。这个值就是在执⾏器每次调⽤引擎获取数据⾏的时候累加的。但在有些场景下，执⾏器调⽤⼀次，在引擎内部则扫描了多⾏，因此引擎扫描⾏数
跟"rows_examined"并不是完全相同的。
⽐如执⾏SQL语句"select * from table where age = 20;",age字段没有索引时，执⾏器的执⾏流程是这样的：
1. 调⽤存储引擎API接⼝取这个数据表的第⼀⾏，判断age值是不是等于20，如果不是则跳过，如果是则将这⾏存在结果集⾥；
2. 调⽤存储引擎API接⼝取下⼀⾏，重复进⾏相同的逻辑判断，直到取到这个数据表的最后⼀⾏；
3. 执⾏器将上述遍历得到的符合条件的⾏组成的结果集返回给客户端。
⽽对于有索引的情况，执⾏的逻辑也⼤同⼩异：
1. 第⼀次调⽤的是“取符合条件的第⼀⾏”这个API接⼝；
2. 之后再循环调⽤这个API接⼝，这些接⼝在存储引擎中都是事先定义好的。
tip1：存储引擎
在关系型数据库⾥⾯，数据是放在数据表⾥⾯的，我们可以把这个表理解成Excel电⼦表格的形式。所
以我们的表在存储数据的同时还要组织数据的存储结构，这个存储结构就是由我们的存储引擎所决定的，所以我们也可以把存储引擎叫做表类型。
在MySQL⾥⽀持多种存储引擎，他们是可以替换的，所以也叫做插件式的存储引擎。那为什么要⽀持这么多存储引擎呢？⼀种还不够⽤吗？
在MySQL⾥每⼀张数据表都可以指定它的存储引擎，⽽不是⼀个数据库只能使⽤⼀个存储引擎。存储引擎的使⽤是以数据表为单位的，⽽且即使在创建完表之后还可以修改它的存储引擎。
如何选择存储引擎:
1. 如果对数据⼀致性要求⽐较⾼，需要事务⽀持，可以选择INNODB；
2. 如果数据查询多更新少，对查询性能要求⽐较⾼，可以选择MyISAM；
3. 如果需要⼀个⽤于查询的临时表，可以选择MEMORY；
4. 如果所有的存储引擎都不能满⾜你的需求，并且你的技术能⼒⾜够强，那么你还可以根据官⽹内部⼿册⽤ C语⾔开发⼀个存储引擎。
（sql/doc/internals/en/custom-engine.html）
tip2：EXPALIN使⽤
EXPLAIN模拟优化器执⾏SQL语句，从⽽知道MySQL是如何处理SQL语句的，分析查询语句或者表结构的性能瓶颈。
作⽤：
1. 表的读取顺序；
2. 数据读取操作的操作类型；
3. 哪些索引可以使⽤；
4. 哪些索引被实际使⽤；
5. 表之间的引⽤；
6. 每张表有多少⾏被优化器查询等。
EXPLAIN + SQL语句得到的信息有10列，分别是：

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