ITKeyword,专注技术干货聚合推荐

注册 | 登录

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

情情说 分享于

2020腾讯云双十一活动,全年最低!!!(领取3500元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1073

2020阿里云最低价产品入口,含代金券(新老用户有优惠),
地址https://www.aliyun.com/minisite/goods

推荐:MYSQL 教程: 高级查询

查找出员工及其部门名 mysql> select employee.name as employeeName, department.name as departmentName from employee, department where employee.departmen

上一篇 提到,最近有个需求,要修改现有存储结构,涉及查询条件和查询效率的考量,看了几篇索引和HBase相关的文章,回忆了相关知识,结合项目需求,说说自己的理解和总结。 总体目录如下,上篇介绍了前3小节,分析了索引为什么快,总结了它的优点和分类,以及索引的演化过程,中篇会重点介绍索引分析方法和常见索引优化。 为什么需要索引 索引的类别 MySQL索引演化 MySQL索引优化 HBase介绍 HBase存储结构 HBase索引介绍 业务需求及设计 部分内容摘录了几个博友的文章,最后会给出文章链接,感谢他们的精彩分析。 通过中篇的介绍,你会了解到: MySQL查询过程 高级查询相关概念 explain命令详细介绍 索引优化建议 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

推荐:mysql 查询理解

1.单表主键查询:

语句: select

*

fromgmvcsbase.base_file

where

id='29830957'

执行计划: Id为base_file表的主键, Select_type为simple表示简单的s

_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: 去重,默认选项; 又写多了,再加一篇吧,中篇未完待续。。。 参考文章: MySQL优化原理 步步深入:SQL解析顺序 欢迎扫描下方二维码,关注我的个人微信公众号,查看更多文章 ~

推荐:MySQL索引和优化查询

恰当的索引可以加快查询速度,可以分为四种类型:主键、唯一索引、全文索引、普通索引。 主键:唯一且没有null值。 create table pk_test(f1 int not null,prima

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

相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。