深入理解 MySQL 执行过程及执行顺序.docx

《深入理解 MySQL 执行过程及执行顺序.docx》由会员分享，可在线阅读，更多相关《深入理解 MySQL 执行过程及执行顺序.docx（11页珍藏版）》请在第一文库网上搜索。

1、深入理解MySQL执行过程及执行顺序MySQL在我们的开发中基本每天都要面对的，作为开发中的数据的来源，MySQL承担者存储数据和读写数据的职责。因为学习和了解MySQL是至关重要的，那么当我们在客户端发起一个SQL到出现详细的查询数据，这其中究竟经历了什么样的过程？ MySQL服务端是如何处理请求的，又是如何执行SQL语句的？本篇博客将来探讨这些问题。MySQL执行过程MySQL整体的执行过程如下图所示:1 .更新记5HD=2这行Server 层命中则直接返回结果3.查询缓存十2.连接器3.分析器4.优化器管理连接,权限验证词法/语法分析执行计划生成.索引选择操作引掌.返回结果5.执行器引擎

2、层6,写undo log存储回法段指舒和事务2通过回滚段指计找到对应的C通过事务来判断记录的可夕undolog可以回滚/崩澹恢复7.记录所在的目标页存在于内存中唯一索引唯一索引普通索引8.将数据页从磁盘读入内存f判断冲突与否，更新8.找到数据更新内存8.找到数据判断数据冲突与否更新内存9.写入redo logWAL用于事务崩溃恢复以及将鳍机写变成顺序写提高性健10.写binlog用于备份/主从同步11.提交事务12.刷redo log 盘处于commit，prepare 阶连接器连接器的主要职责就是：1、负责与客户端的通信，是半双工模式，这就意味着某一固定时刻只能由客户端向服务器请求或者服务器

3、向客户端发送数据，而不能同时进行，其中MySQL在与客户端连接TC/IP的。2、验证请求用户的账户和密码是否正确，如果账户和密码错误，会报错：Accessdenied for user rootlocalhost (using password: YES)3、如果用户的账户和密码验证通过，会在MySQL自带的权限表中查询当前用户的权限。MySQL中存在4个控制权限的表,分别为user表,db表,tables_priv表，columns_priv 表：user表：存放用户账户信息以及全局级别(所有数据库)权限，决定了来自哪些主机的哪些用户可以访问数据库实例db表：存放数据库级别的权限，决定了来自

4、哪些主机的哪些用户可以访问此数据库tables_priv表：存放表级别的权限,决定了来自哪些主机的哪些用户可以访问数据库的这个表columns_priv表：存放列级别的权限，决定了来自哪些主机的哪些用户可以访问数据库表的这个字段MySQL权限表的验证过程为：1、先从user表中的Host, User, Password这3个字段中判断连接的IP、用户名、密码是否存在，存在则通过验证。2、通过身份认证后，进行权限分配,按照user, db, tables priv, columns priv的顺序进行验证。即先检查全局权限表user,如果user中对应的权限为Y,则此用户对所有数据库的权限都为Y

5、,将不再检查db, tables_priv, columns_priv；如果为N,贝IJ至IJ db表中检查此用户对应的具体数据库，并落至IJ db中为Y而权限;如果db中为N,则检查tables priv中此数据库对应的具体表，取得表中的权限Y,以此类推。另外，搜索公众号编程技术圈后台回复“算法心得”，获取一份惊喜礼包。3、如果在任何一个过程中权限验证不通过，都会报错。缓存MySQL的缓存主要的作用是为了提升查询的效率，缓存以key和value的哈希表形式存储，key是具体的SQL语句，value是结果的集合。如果无法命中缓存，就继续走到分析器的这一步，如果命中缓存就直接返回给客户端。不过需

6、要注意的是在MySQL的&0版本以后，缓存被官方删除掉了。之所以删除掉，是因为查询缓存的失效非常频繁，如果在一个写多读少的环境中，缓存会频繁的新增和失效。对于某些更新压力大的数据库来说，查询缓存的命中率会非常低，MySQL为了维护缓存可能会出现一定的伸缩性的问题，目前在5. 6的版本中已经默认关闭了，比较推荐的一种做法是将缓存放在客户端，性能大概会提升5倍左右。分析器分析器的主要作用是将客户端发过来的SQL语句进行分析，这将包括预处理与解析过程,在这个阶段会解析SQL语句的语义，并进行关键词和非关键词进行提取、解析，并组成一个解析树。具体的关键词包括不限定于以下：select/update/d

7、elete/or/in/where/group by/having/count/1 imit 等。如果分析到语法错误，会直接给客户端抛出异常：ERROR: You have an error in yourSQL syntax.”。比如：select * from user where userid =1234;在分析器中就通过语义规则器将select from where这些关键词提取和匹配出来，MySQL会自动判断关键词和非关键词，将用户的匹配字段和自定义语句识别出来。这个阶段也会做一些校验：比如校验当前数据库是否存在user表，同时假如user表中不存在userid这个字段同样会报错：u

8、nknown column in fieldlist. ”。优化器能够进入到优化器阶段表示SQL是符合MySQL的标准语义规则的并且可以执行的，此阶段主要是进行SQL语句的优化，会根据执行计划进行最优的选择，匹配合适的索引，选择最佳的执行方案。比如一个典型的例子是这样的：表T,对A、B、C列建立联合索引，在进行查询的时候，当SQL查询到的结果是:select xx where B=x and A=x and C=x,很多人会以为是用不到索引的，但其实会用到，虽然索引必须符合最左原则才能使用，但是本质上，优化器会自动将这条SQL优化为：where A=x and B=x and C=X,这种优化

9、会为了底层能够匹配到索引，同时在这个阶段是自动按照执行计划进行预处理，MySQL会计算各个执行方法的最佳时间，最终确定一条执行的SQL交给最后的执行器。执行器在执行器的阶段，此时会调用存储引擎的API, API会调用存储引擎，主要有一下存储的引擎，不过常用的还是myisam和innodb：存储引擎myisaminnodb存储限制256TB64TB事务支持索引支持支持锁的粒度表锁行锁数据压缩支持一外键支持引擎以前的名字叫做：表处理器（其实这个名字我觉得更能表达它存在的意义）负责对具体的数据文件进行操作，对SQL的语义比如select或者update进行分析，执行具体的操作。在执行完以后会将具体的

10、操作记录到binlog中，需要注意的一点是：select不会记录到binlog中，只有update/delete/insert才会记录到binlog中。而update会采用两阶段提交的方式，记录都redolog中。牛逼啊！接私活必备的N个开源项目！赶快收藏吧执行的状态可以通过命令：show full processlist,展示所有的处理进程，主要包含了以下的状态，表示服务器处理客户端的状态，状态包含了从客户端发起请求到后台服务器处理的过程，包括加锁的过程、统计存储引擎的信息，排序数据、搜索中间表、发送数据等。囊括了所有的MySQL的所有状态，其中具体的含义如下图：状态表元sleepquery

11、locked线程正在等待书线程正在执行查询或基mysql服务器层analying and statisticscopying to temp tableon disksorting resultsending data线程正在收集存储引擎的统线程正在执行查询，并且将结果复制到临时表中,将内存中的昭线程正在对工线程在多个状态之间传递数据或，SQL的执行顺序事实上，SQL并不是按照我们的书写顺序来从前往后、左往右依次执行的，它是按照固定的顺序解析的，主要的作用就是从上一个阶段的执行返回结果来提供给下一阶段使用，SQL在执行的过程中会有不同的临时中间表，一般是按照如下顺序：fromonjoinwhe

12、resql执行顺序|e 顺序 goup byhdving+聚合函数selectorder byi limit仞ij子:select distinct s. id from T t join S s on t. id=s. id wheret. name=Yrion group by t. mobile having count (*) 2 order by s. create_timelimit 5;from第一步就是选择出from关键词后面跟的表，这也是SQL执行的第一步：表示要从数据库中执行哪张表。实例说明：在这个例子中就是首先从数据库中找到表T。join onjoin是表示要关联的表，o

13、n是连接的条件。通过from和join on选择出需要执行的数据库表T和S,产生笛卡尔积，生成T和S合并的临时中间表Tempi。on：确定表的绑定关系，通过on产生临时中间表Temp2o实例说明：找到表S,生成临时中间表Tempi,然后找到表T的id和S的id相同的部分组成成表Temp2, Temp2里面包含着T和Sid相等的所有数据。wherewhere表示筛选，根据where后面的条件进行过滤，按照指定的字段的值（如果有and连接符会进行联合筛选）从临时中间表Temp2中筛选需要的数据，注意如果在此阶段找不到数据，会直接返回客户端，不会往下进行。这个过程会生成一个临时中间表Temp3。注意

14、：在where中不可以使用聚合函数，聚合函数主要是（minmaxcountsum 等函数）。实例说明：在ten）p2临时表集合中找到T表的name二Yrion的数据，找到数据后会成临时中间表Temp3, To emp3里包含name列为Yrion的所有表数据。group bygroup by是进行分组，对where条件过滤后的临时表Temp3按照固定的字段进行分组，产生临时中间表Temp4,这个过程只是数据的顺序发生改变，而数据总量不会变化，表中的数据以组的形式存在。实例说明：在Temp3表数据中对mobile进行分组，查找出mobile 一样的数据，然后放到一起，产生Temp4临时表。Having对临时中间表Temp4进行聚合，这里可以为count等计数，然后产生中间表Temp5,在此阶段可以使用select中的别名。实例说明：在Temp4临时表中找出条数大于2的数据，如果小于2直接被舍弃掉,然后生成临时中间表Temp5。select对分组聚合完的表挑