理解索引(中):MySQL查询过程和高级查询


声明:本文转载自https://my.oschina.net/qqtalk/blog/1820465,转载目的在于传递更多信息,仅供学习交流之用。如有侵权行为,请联系我,我会及时删除。

上一篇 提到,最近有个需求,要修改现有存储结构,涉及查询条件和查询效率的考量,看了几篇索引和HBase相关的文章,回忆了相关知识,结合项目需求,说说自己的理解和总结。

总体目录如下,上篇介绍了前3小节,分析了索引为什么快,总结了它的优点和分类,以及索引的演化过程,中篇会重点介绍索引分析方法和常见索引优化。

  • 为什么需要索引
  • 索引的类别
  • MySQL索引演化
  • MySQL索引优化
  • HBase介绍
  • HBase存储结构
  • HBase索引介绍
  • 业务需求及设计

部分内容摘录了几个博友的文章,最后会给出文章链接,感谢他们的精彩分析。

通过中篇的介绍,你会了解到:

  • MySQL查询过程
  • 高级查询相关概念
  • explain命令详细介绍
  • 索引优化建议

MySQL查询过程

想要更好的优化查询,首先要了解其整体查询过程,从客户端发送查询请求,到接收到查询结果,MySQL服务器做了很多工作。

逻辑架构

MySQL逻辑架构整体分为三层,分别为客户端层、核心服务层、存储引擎层,共同协作完成。

MySQL逻辑架构

最上层为客户端层,比如:连接处理、授权认证、安全等功能等。

中间层是MySQL的核心服务,包括查询解析、分析、优化、缓存、内置函数(比如:时间、数学、加密等),另外,所有的跨存储引擎的功能也在这一层实现:存储过程、触发器、视图等。

最下层为存储引擎,负责数据存储和提取,中间的服务层通过API与存储引擎通信,这些API接口屏蔽了不同存储引擎间的差异。

具体执行过程

重点看下MySQL是如何优化和执行查询的,很多的查询优化工作就是遵循一些原则让MySQL的优化器能够按照预想的方式运行而已。

查询具体过程

先说下总体流程:

  • 客户端发送一条查询SQL给服务器;
  • 服务器先检查查询缓存,如果命中了缓存,则立即返回存储在缓存中的结果;
  • 服务器端进行SQL解析、预处理,再由优化器生成对应的执行计划;
  • 查询执行引擎根据优化器生成的执行计划,调用存储引擎的API来执行查询;
  • 将结果返回给客户端;

1.客户端/服务端通信协议

MySQL客户端和服务器之间的通信协议是「半双工」:在任何一个时刻,要么由服务器向客户端发送数据,要么由客户端向服务器发送数据,不能同时发生,这也就意味着没法进行流量控制。

客户端用一个单独的数据包将查询请求发送给服务器,服务器响应给用户的数据通常会很多,由多个数据包组成,需要注意的是当服务器响应客户端请求时,客户端必须完整的接收整个返回结果,而不能简单的只取前面几条结果,然后让服务器停止发送。

2.查询缓存

如果查询缓存是打开的,会检查这个查询语句是否命中查询缓存中的数据,如果命中,在检查一次用户权限后直接返回缓存中的结果。

查询缓存系统会跟踪查询中涉及的每个表,在任何的写操作时,MySQL必须将对应表的所有缓存都设置为失效,如果查询缓存非常大或者碎片很多,这个操作就可能带来很大的系统消耗。

另外,任何的查询语句在开始之前都必须经过检查,即使这条SQL语句永远不会命中缓存,如果查询结果可以被缓存,那么执行完成后,会将结果存入缓存,也会带来额外的系统消耗。

所以,打开缓存要慎重,只有当缓存带来的资源节约大于其本身消耗的资源时,才会给系统带来性能提升,可以将query_cache_type设置为DEMAND,这时只有加入SQL_CACHE的查询才会走缓存,其他查询则不会。

3.语法解析和预处理

通过关键字将SQL语句进行解析,生成一颗解析树,预处理则会根据MySQL规则进一步检查解析树是否合法。

4.查询优化

一条查询可以有很多种执行方式,优化器的作用就是找到这其中最好的执行计划,MySQL使用基于成本的优化器,它尝试预测一个查询使用某种执行计划时的成本,并选择其中成本最小的一个。

5.查询执行引擎

存储引擎接口提供了非常丰富的功能,但其底层仅有几十个接口,这些接口像搭积木一样完成了一次查询的大部分操作。

6.返回结果给客户端

结果集返回客户端是一个增量且逐步返回的过程,这样服务端就无须存储太多结果而消耗过多内存,也可以让客户端第一时间获得返回结果。

SELECT执行顺序

下面来看看SQL查询语句的执行顺序,每一步都会生成一个虚拟临时表,作为下一步的输入。

标准的SQL语法如下:

SELECT DISTINCT     < select_list > FROM     < left_table > < join_type > JOIN < right_table > ON < join_condition > WHERE     < where_condition > GROUP BY     < group_by_list > HAVING     < having_condition > ORDER BY     < order_by_condition > LIMIT < limit_number > 

但执行顺序是这样的:

FROM      <left_table> ON <join_condition> <join_type>  JOIN <right_table> WHERE      <where_condition> GROUP BY      <group_by_list> HAVING      <having_condition> SELECT  DISTINCT      <select_list> ORDER BY      <order_by_condition> LIMIT      <limit_number> 

1.FROM

当涉及多个表的时候,左边表的输出会作为右边表的输入,之后会生成一个虚拟表VT1:

  • 计算两个相关联表的笛卡尔积(CROSS JOIN) ,生成虚拟表VT1-J1;
  • 基于虚拟表VT1-J1进行过滤,过滤出所有满足ON谓词条件的行,生成虚拟表VT1-J2;
  • 如果使用了外连接(LEFT,RIGHT,FULL),主表(保留表)中的不符合ON条件的列也会被加入到VT1-J2中,生成虚拟表VT1-J3;

2.WHERE

对VT1过程中生成的临时表进行过滤,满足WHERE子句的列被插入到VT2表中:

  • 与ON的区别:如果有外连接,ON针对过滤的是关联表,主表会返回所有的列,如果没有外连接,效果相同;
  • 对主表的过滤应该放在WHERE;
  • 于关联表,先条件查询后连接则用ON,先连接后条件查询则用WHERE;

3.GROUP BY

这个子句会把VT2中生成的表按照GROUP BY中的列进行分组,生成VT3表:

  • 其后处理过程的语句,如SELECT,HAVING,所用到的列必须包含在GROUP BY中,对于没有出现的,得用聚合函数;

4.HAVING

对VT3表中的不同的组进行过滤,只用于分组后的数据,满足HAVING条件的子句被加入到VT4表中。

5.SELECT

这个子句对SELECT子句中的元素进行处理,生成VT5表:

  • 计算SELECT子句中的表达式,生成VT5-J1;
  • DISTINCT:寻找重复列,并删掉,会创建一张内存临时表VT5-J2,和虚拟表VT5-J1一样,不同的是对DISTINCT的列增加唯一索引,以此来除重复数据;

6.ORDER BY

从VT5-J2中的表中,根据ORDER BY 子句的条件对结果进行排序,生成VT6表,这是唯一可使用SELECT中别名的地方。

7.LIMIT

从上一步得到的VT6虚拟表中选出从指定位置开始的指定行数据。

高级查询相关概念

本小节介绍下常用的高级查询概念。

连接查询

将多张表按照某个指定的条件进行数据拼接,SQL中将连接查询分成四类: 内连接、外连接、自然连接、交叉连接,其中自然连接和交叉连接很少用到,就不过多介绍了。

1.内连接 inner join

从左表中取出每一条记录,分别与右表中所有的记录进行匹配,匹配必须左表和右表中都满足条件,匹配的会保留结果,否则不保留。

2.外连接 left/right join

外连接分为两种:

  • left join: 左外连接(左连接),以左表为主表
  • right join: 右外连接(右连接),以右表为主表

以某张表为主,取出里面的所有记录,不管能不能匹配上条件,主表最终都会保留,然后与另外一张表进行连接,如果不能匹配,其他表的字段都置空NULL。

子查询

是在某个查询结果之上再进行查询,也就是一条select语句内部包含了另外一条select语句。

按子查询所在位置,可以划分为:

  • From子查询:子查询跟在from之后;
  • Where子查询: 子查询出where条件中;
  • exists子查询: 子查询出现在exists里面;

下面举几个例子:

查找部门名称前缀为「小米」的所有员工:

 SELECT name , sex ,  sal         FROM emp         WHERE no in (              SELECT no FROM dept                  WHERE name LIKE '小米%'         ); 

查看所有员工的薪水,并按薪水排序:

SELECT name , sal        FROM (            SELECT name , sal                FROM emp ORDER BY sal        ); 
联合查询

将多次查询, 将结果进行拼接,字段不会增加,每一条select语句获取的字段数必须严格一致。

语法如下:

Select 语句1  Union [union选项]  Select语句2... 

Union选项:

  • All: 保留所有;
  • Distinct: 去重,默认选项;

又写多了,再加一篇吧,中篇未完待续。。。

参考文章:

  1. MySQL优化原理
  2. 步步深入:SQL解析顺序

欢迎扫描下方二维码,关注我的个人微信公众号,查看更多文章 ~

情情说

本文发表于2018年05月29日 20:00
(c)注:本文转载自https://my.oschina.net/qqtalk/blog/1820465,转载目的在于传递更多信息,并不代表本网赞同其观点和对其真实性负责。如有侵权行为,请联系我们,我们会及时删除.

阅读 2054 讨论 1 喜欢 0

抢先体验

扫码体验
趣味小程序
文字表情生成器

闪念胶囊

你要过得好哇,这样我才能恨你啊,你要是过得不好,我都不知道该恨你还是拥抱你啊。

直抵黄龙府,与诸君痛饮尔。

那时陪伴我的人啊,你们如今在何方。

不出意外的话,我们再也不会见了,祝你前程似锦。

这世界真好,吃野东西也要留出这条命来看看

快捷链接
网站地图
提交友链
Copyright © 2016 - 2021 Cion.
All Rights Reserved.
京ICP备2021004668号-1