资深架构师带你深刻理解MYSQL的sql执行流程以及性能调优

829 09-10

什么是mysql的解析器？

MySQL解析器由两部分组成：句法扫描器和语法规则模块。语法扫描器讲整个查询分解为多个令牌（一些不可分割的元素，列如列名称），同时语法规则模块找到一个SQL语法规则组合，该组合产生这一序列，并执行与这些规则相关的代码。最后产生一棵解析树，现在优化器可以使用。

设想服务器收到下列查询：

select count(*),state from table_name group by state
这条SQL语句就会通过句法扫描器检查查询字符流，将其分解为令牌，然后识别每个令牌。如下：

select
count
(
*
)
,
state
from
table_name
group
by
state

每个令牌都有一个类型，列如一个关键字，一个字符串，一个数字，一个操作符或一个函数名称。语法规则模块安装一系列规则与名片六相应匹配，然后找出正确的规则，而上面的SQL是select 规则（sql/sql_yacc.yy）他相应地初始化解析树结构。

MySQL会通过sql/gen_lex_hash.cc 生成有效的关键字查询散列，然后通过扫描器sql/sql_lex.cc讲每个名片标记为一个关键字，一个函数名称，大量特定类型或一些在语法规则中具有某种意义的特殊符号,然后这些内容通过解析器根据语法规则创建对应的解析树。

（解析树主要是是lex类型对象代表，在sql/sql_lex.h中有定义成员很多比如：enum_sql_command sql_command 它显示我们正在执行的SQL查询类型，是select，update，delete，insert还是其他类型）

什么是优化器?

优化器接收到解析器返回的解析树的内容然后会做如下几件事情（因为优化器的内容过多暂时可以先了解做的操作即可）

MySQL优化器具有几个重要功能

确定使用哪个键从表中获取记录，并选择最合适该表的建。
(join)针对每个表确定扫描标识符比读取建更好，如果有很多记录与建值匹配，则建的优点下降，而表扫描速度更快。
(join)当查询中出现一个以上的表示，确定表的连接顺序
重写where从句，以删除死代码，减少不必要的计算，尽可能更改限制条件，以便为键使用开辟道路
(join)从链接中删除未使用的表
确定是否能将键用于order by 和 group by
(join)是这是用一个内部连接替换一个外部连接
(join)试着简化子查询，并决定其结果的高速缓存程度
(join)合并试图（扩大视图引用，使其成为一个宏）

总结:

总的来说当客户端向MySQL请求一条query命令解析器模块完成请求分类，区别出是select并转发给优化器时，优化器会根据相应的规则对于query进行优化，处理掉一些常量表达式的预算，直接换掉常量值。并对query中的查询条件进行简化和转换，如去掉一些无用或显而意见的条件、结构调整等。然后分析query中的Hint(类似于标识)信息,如果有hint就会执行query的计划，没有就会读取设计对象的统计信息，根据query进行写相应的计算分析，然后再得出最后的计划。

优化器是一个数据库软件非常核心的功能，虽然说起来只是简单的几句话，单在MySQL内部，优化器实际上经过了很多复杂的运算分析，才得出的执行计划。对于优化器结合于后面的知··识点涉及讲解。

SQL语句的错误使用

常见问题：可不可以多次查询优化查询效率（针对join 或者 in ）

注意这个问题并没有很直接的正确答案，问题本身是模棱两可的既可以多次查询也可以一起；

对于SQL的错误使用有两种情况就是过强和过弱这是常常会可能出现的问题：

比如查询粉丝最多的前十个用户的文章总数

粉丝关注表：user_fans

Field	Type	Null	Key	Default	Extra
id	int(11)	NO	PRI	0	
user_id	int(11)	NO	| MUL	NULL	
fans_id	int(11)	NO	| MUL	NULL

查询的方法

方案一、

select user_id from (
  select
    user_id,count(*) c
  from
    user_fans
  group by
    user_id
  order by c desc limit 0,5
) as fans_count;

然后再通过循环查询出用户的id信息并输出

for (fans_count)

select count(*) from article where user_id = ?

方案二、

select user_id from (
  select
    user_id,count(*) c
  from
    user_fans
  group by
    user_id
  order by c desc limit 0,5
) as fans_count;

如上内容不变

select count(*) from article where user_id in (?) group by user_id;

对比：

从MySQL执行的query数量来看第一种为 1 + 10 = 11 条，第二种为 1 + 1 =2
对应的交互数量11 ： 2
io操作对比假设一条SQL就是一个IO，第一种最少有11次IO，第二种小余或等于11次IO；如果数据非常离散就是11次；
复杂度：前缀的SQL不管只看第二条SQL；第一种简单一些，第二种就增加了group by
数据库对于结果的返回地中是11次，第二种是2次，但是第二种方法中的第二次结果是第一次的10倍
从应用程序的数据处理来看，第二种比第一种多了一个拼接photo_id的过程
根据上面的点做对比分析：

由于MySQL不管客户端每次调教的query是相同还是不同，都需要进行完全解析这个动作主要消耗的资源师数据库主机的CPU，那么这里第一种方案和第二种方案消耗CPU的比例是11:2. query语句的解析动作在整个query语句执行过程中的整体消耗的CPU比例是比较多的；
网络交互对比：11:2
IO比 <= 1:1
在数据量少的情况下分组性能问题不大
所以相对来说第二种会更好一点-》这个情况就是过度的弱化SQL 或者说数据库的处理能力

但是如果说根据第二种方法把两条SQL合在一起呢？

select count(*) from article where user_id in (
  select user_id from (
    select
      user_id,count(*) c
    from
      user_fans
    group by
      user_id
    order by c desc limit 0,5
  ) as fans_count;
) group by user_id;

优化SQL的思路(重点)

query 功能的优化 -> 涉及了很多操作不是一条SQL语句一定要先找问题分析

优化SQL -> 可能会给我们数据表带来变动 => 建立索引修改配置 => 配置影响整体

优化不仅仅只是只SQL语句

索引可以提高数据表的查询的速度 => 降低写操作速度 => 索引可能我使用会失效

需要知道更需要去优化的功能 -> 优化更需要优化SQL
定位优化他的性能瓶颈还可能与数据表有关系, CPU, IO操作次数
明确目标

优化不是影响的局部地区,还会影响其他地区;在优化的时候已经想到优化方案表写入的时候次数是否频繁

优化在不影响功能的情况下,会牺牲某一些点,提高整体的体验 => 代码设计需要事先想好的

索引对于SQL的使用

标签： MYSQL