Mysql 执行sql的全过程 （sql 优化）

一.MySQL执行过程

MySQL整体的执行过程如下图所示：

1.1:连接器

连接器的主要职责就是:

①负责与客户端的通信,是半双工模式,这就意味着某一固定时刻只能由客户端向服务器请求或者服务器向客户端发送数据,而不能同时进行,其中MySQL在与客户端连接TC/IP的

②验证请求用户的账户和密码是否正确,如果账户和密码错误,会报错:Access denied for user ‘root’@‘localhost’ (using password: YES)

③如果用户的账户和密码验证通过,会在MySQL自带的权限表中查询当前用户的权限:

MySQL中存在4个控制权限的表，分别为user表，db表，tables_priv表，columns_priv表,MySQL权限表的验证过程为：

• 1): User表:存放用户账户信息以及全局级别（所有数据库）权限，决定了来自哪些主机的哪些用户可以访问数据库实例

1.Db表:存放数据库级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库
2.Tables_priv表：存放表级别的权限，决定了来自哪些主机的哪些用户可以访问数据库的这个表
3.Columns_priv表：存放列级别的权限，决定了来自哪些主机的哪些用户可以访问数据库表的这个字段
4.Procs_priv表：存放存储过程和函数级别的权限

• 2):先从user表中的Host,User,Password这3个字段中判断连接的ip、用户名、密码是否存在，存在则通过验证。

• 3):通过身份认证后，进行权限分配，按照user，db，tables_priv，columns_priv的顺序进行验证。即先检查全局权限表user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db, tables_priv,columns_priv；如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推

• 4):如果在任何一个过程中权限验证不通过,都会报错

1.2:缓存

MySQL的缓存主要的作用是为了提升查询的效率，缓存以key和value的哈希表形式存储，key是具体的sql语句，value是结果的集合。如果无法命中缓存,就继续走到分析器的的一步,如果命中缓存就直接返回给客户端 。不过需要注意的是在MySQL的8.0版本以后，缓存被官方删除掉了。 之所以删除掉,是因为查询缓存的失效非常频繁,如果在一个写多读少的环境中,缓存会频繁的新增和失效。对于某些更新压力大的数据库来说，查询缓存的命中率会非常低,MySQL为了维护缓存可能会出现一定的伸缩性的问题,目前在5.6的版本中已经默认关闭了，比较推荐的一种做法是将缓存放在客户端，性能大概会提升5倍左右

1.3:分析器

分析器的主要作用是将客户端发过来的sql语句进行分析，这将包括预处理与解析过程，在这个阶段会解析sql语句的语义，并进行关键词和非关键词进行提取、解析，并组成一个解析树。具体的关键词包括不限定于以下：select/update/delete/or/in/where/group by/having/count/limit等.如果分析到语法错误，会直接给客户端抛出异常:ERROR:You have an error in your SQL syntax.
比如：

select * from user where userId =1234;

在分析器中就通过语义规则器将select from where这些关键词提取和匹配出来,MySQL会自动判断关键词和非关键词，将用户的匹配字段和自定义语句识别出来。这个阶段也会做一些校验:比如校验当前数据库是否存在user表，同时假如User表中不存在userId这个字段同样会报错：unknown column in field list.

1.4:优化器

能够进入到优化器阶段表示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,这种优化会为了底层能够匹配到索引，同时在这个阶段是自动按照执行计划进行预处理,MySQL会计算各个执行方法的最佳时间,最终确定一条执行的sql交给最后的执行器

1.5:执行器

在执行器的阶段,此时会调用存储引擎的API,API会调用存储引擎，主要有一下存储的引擎，不过常用的还是myisam和innodb:

引擎以前的名字叫做:表处理器(其实这个名字我觉得更能表达它存在的意义)负责对具体的数据文件进行操作,对sql的语义比如select或者update进行分析,执行具体的操作。在执行完以后会将具体的操作记录到binlog中,需要注意的一点是:select不会记录到binlog中,只有update/delete/insert才会记录到binlog中。而update会采用两阶段提交的方式,记录都redolog中

这里着重讲一下优化器

1.4.1优化器主要分为两个阶段

• 首先是逻辑查询优化，这个阶段主要是使用关系代数对SQL语句做一些等价变换，对条件表达式进行等价谓词重写、条件简化，对视图进行重写，对子查询进行优化，对连接语义进行了外连接消除、嵌套连接消除等。
• 其次是物理查询优化。物理查询优化一般分为两种：
  1)基于规则的优化（RBO，Rule-Based Optimizer）
  这种方式主要是基于一些预置的规则对查询进行优化。
  2)基于代价的优化（CBO，Cost-Based Optimizer）
  这种方式会根据模型计算出各个可能的执行计划的代价，然后选择代价最少的那个。它会利用数据库里面的统计信息来做判断，因此是动态的。

1.4.2 SQL优化通常包括两项工作:

一是逻辑优化,二是物理优化.这两项工作都要对语法分析树的形态进行修改，把语法分析树变为查询树。

• 其中，逻辑查询优化将生成逻辑查询执行计划。在生成逻辑查询执行计划过程中，根据关系代数的原理，把语法分析树变为关系代数语法树的样式，原先SQL语义中的一些谓词变化为逻辑代数的操作符等样式，这些样式是一个临时的中间状态，经过进一步的逻辑查询优化，如执行常量传递、选择下推等(如一些节点下移，一些节点上移)，从而生成逻辑查询执行计划。
• 在生成逻辑查询计划后，查询优化器会进一步对查询树进行物理查询优化。物理优化会对逻辑查询进行改造，改造的内容主要是对连接的顺序进行调整。SQL语句确定的连接顺序经过多表连接算法的处理，可能导致表之间的连接顺序发生变化，所以树的形态有可能调整。
  物理查询优化除了进行表的连接顺序调整外，还会使用代价估算模型对单个表的扫描方式、两表连接的连接算法进行评估，选择每一项操作中代价最小的操作为下一步优化的基础。
• 物理查询优化的最终结果是生成最终物理查询执行计划。

1.5 SQL执行

在SQL执行阶段，依据物理查询计划执行查询，逐步调用相关算法进行执行。算法包括一趟算法、嵌套循环连接、基于排序的两趟算法、基于散列的两趟算法、基于索引的算法、使用超过两趟的算法等。

二、 逻辑查询优化

2.1 逻辑查询优化思路

查询优化器在逻辑优化阶段主要解决的问题是: 如何找出SQL语句等价的变换形式，使得SQL执行更高效。
一条SQL查询语句结构复杂，包含多种类型的字句，优化操作依赖于表的一些属性(如索引和约束等)。可用于优化的思路包括:

• 字句局部优化: 每种类型字句都可能存在优化方式，如等价谓词重写、where和having条件化简中的大部分情况，都属于这种字句范围内的优化。
• 字句间关联优化: 字句与字句之间关联的语义存在优化的可能，如外连接消除、连接消除、子查询优化、视图重写等都属于字句间的关联优化，因为他们的优化都需要借助其他字句、表定义或列属性等信息进行。
• 局部与整体的优化: 需要协同考虑局部表达式和整体的关系，如OR重写并集规则需要考虑UNION操作(UNION师变换后的整体的形式)的花费和OR操作(OR是局部表达式)的花费。
• 形式变化优化: 多个字句存在嵌套，可以通过形式的变化完成优化，如嵌套连接消除。
• 语义优化:根据完整性约束、SQL表达的含义等信息对语句进行语义优化。

2.2 查询重写规则

传统的联机事务处理(OLTP)使用基于选择(SELECT)、投影(PROJECT)、连接(JOIN)3种基本操作相结合的查询，这种查询称为SPJ查询。
数据库在查询优化的过程中，会对这3种基本操作进行优化。优化的方式如下:

• 选择操作: 对应的是限制条件(格式类似field consant，field表示列对象，op是操作符，如=，>等)，优化方式是选择操作下推，目的是尽量减少连接操作前的远组数，使得中间临时关系尽量少(元组数少，连接得到的远组数就少)，这样可减少IO和CPU的消耗，节约内存空间。
• 投影操作: 对应的SELECT查询的目的列对象，优化方式是投影操作下推，目的是尽量减少连接操作前的列数，使得中间临时关系尽量小(特别注意差别:选择操作是使元组的个数"尽量少"，投影操作是使一条元组"尽量小")，这样虽然不能减少IO(多数数据库存储方式是行存储，元组是读取的最基本单位，所以要想操作列则必须读取一行数据)，但可以减少连接后中间关系的元组大小，节约内存空间)。
• 连接关系: 对应的是连接条件(格式类似field_1, field_2，field_1和field_2表示不同表上的列对象，op是操作符，如=，>等)，表示两个表连接的条件。这里涉及以下两个子问题:

1. 多表连接中每个表被连接的顺序决定着效率。如果一个查询语句只有一个表，则这样的语句很简单；但如果有多个表，则会涉及表之间以什么样的顺序连接效率最高效(如A、B、C三表连接，如果ABC、ACB、BCA等连接后的结果集一样，则计算哪种连接次序的效率最高，是需要考虑的问题)。
2. 多表连接每个表被连接的顺序由用户语义决定。查询语句多表连接有着不同的语义(如笛卡尔积、内连接 、还是外连接中的左外连接等)，这决定着表之间的额前后连接次序是不能随意更换的，否则结果集中数据是不同的。因此，表的前后连接次序是不能随意交换的。
   根据SQL语句的形式特点，可以针对SPJ的查询优化，如基于选择、投影、连接3种基本操作相结合的查询。

2.3 启发式规则再逻辑优化阶段的应用

逻辑优化阶段使用的启发式规则通常包括如下两类:

2.3.1 一定能带来优化效果的，主要包括:

• 优先做选择和投影(选择条件在查询树上下推)
• 子查询的消除
• 嵌套连接的消除
• 外连接的消除
• 连接的消除
• 使用等价谓词重写，对条件化简
• 语义优化
• 剪掉冗余操作(一些剪枝优化技术)、最小化查询块。

2.3.2 变换未必会带来性能的提高，需根据代价选择，主要包括:

• 分组的合并
• 借用索引优化分组、排序、DISTINCT等操作
• 对视图的查询变为基于表的查询
• 连接条件的下推
• 分组的下推
• 连接提取公共表达式
• 谓词的上拉
• 用连接取代集合操作
• 用UNIONALL取代OR操作

三、物理优化

查询优化器在物理优化阶段，主要解决的问题如下:

• 从可选的单表扫描方式中，挑选什么样的单表扫描方式是最优的？
  对于两个表连接时，如何选择是最优的？
• 对多个表连接，连接顺序有多种组合，是否要对每种组合都探索？如果不全部探索，怎么找到最优的一种组合？
• 在查询优化器实现的早期，使用的是逻辑优化技术，即使用关系代数规则和启发式规则对查询进行优化后，认为生成的执行计划就是最优的。
• 在引入了基于代价的查询优化方式后，对查询执行计划做了定量的分析，对每一个可能的执行方式进行评估，挑出代价最小的作为最优的计划。
• 目前数据库的查询优化器通常融合这两种方式。

3.1 查询代价估算

查询代价估算的重点是代价估算模型，这是物理查询优化的依据。除了代价模型外，选择率对代价求解也起着重要作用。

3.2 单表扫描算法

单表扫描需要从表上获取元组，直接关联到物理IO的读取，所以不同的单表扫描方式，有不同的代价。

3.3 索引

索引是 建立在表上的，本质上是通过索引直接定位表的物理元组，加快数据获取的方式，所以索引优化的手段应该归属到物理查询优化阶段。

3.4 两表连接算法

关系代数的一项重要操作是连接运算，多个表连接是建立在两表之间连接的基础上的。研究两表连接的方式，对连接效率的提高有着直接的影响。

3.5 多表连接算法

多表连接算法实现的是在查询路径生成的过程中，根据代价估算，从各种可能的候选路径中找出最优的路径(最优路径是代价最小的路径)。
多表连接算法需要解决两个问题:

• 多表连接的顺序: 表的不同连接顺序，会产生许多不同的连接路径；不同的连接路径有不同的效率。
• 多表连接的搜索空间:因为多表连接的顺序不同，产生的连接组合会有多种，如果这个组合的数据巨大，连接次数会达到一个很高的数量级，最大可能的连接次数是N！(N的阶乘)。比如N=5，连接次数是120；N=10，连接次数是362880。所有的连接可能构成一个巨大的"搜索空间"。如何将搜索空间限制在一个可接受的时间范围内，并高效地生成查询执行计划将成为一个难点。

四 MySQL查询优化器概述

MySQL 查询优化器的主要功能是完成SELECT语句的执行，在保证SELECT语句正确执行之外，还有一个重要的功能，就是使用关系代数、启发式规则、代价估值模型等不同种类的技术，提高SELECT语句的执行效率。