精华内容
下载资源
问答
  • 当然,要完整描述一条SQL在数据库中的生命周期,这是一个非常巨大的问题,涵盖了SQL的词法解析、语法解析、权限检查、查询优化、SQL执行等一系列的步骤,简短的篇幅是绝对无能为力的。因此,本文挑选了其中的部分...

    1        问题描述

    一条SQL,在数据库中是如何执行的呢?相信很多人都会对这个问题比较感兴趣。当然,要完整描述一条SQL在数据库中的生命周期,这是一个非常巨大的问题,涵盖了SQL的词法解析、语法解析、权限检查、查询优化、SQL执行等一系列的步骤,简短的篇幅是绝对无能为力的。因此,本文挑选了其中的部分内容,也是我一直都想写的一个内容,做重点介绍:

     

    给定一条SQL,如何提取其中的where条件?where条件中的每个子条件,在SQL执行的过程中有分别起着什么样的作用?

     

    通过本文的介绍,希望读者能够更好地理解查询条件对于SQL语句的影响;撰写出更为优质的SQL语句;更好地理解一些术语,例如:MySQL 5.6中一个重要的优化——Index Condition Pushdown,究竟push down了什么?

     

    本文接下来的内容,安排如下:

    1. 简单介绍关系型数据库中数据的组织形式;

    2. 给定一条SQL,如何提取其中的where条件;

    3. 最后做一个小的总结;

     

     

    2        关系型数据库中的数据组织

     

    关系型数据库中,数据组织涉及到两个最基本的结构:表与索引。表中存储的是完整记录,一般有两种组织形式:堆表(所有的记录无序存储),或者是聚簇索引表(所有的记录,按照记录主键进行排序存储)。索引中存储的是完整记录的一个子集,用于加速记录的查询速度,索引的组织形式,一般均为B+树结构。

     

    有了这些基本知识之后,接下来让我们创建一张测试表,为表新增几个索引,然后插入几条记录,最后看看表的完整数据组织、存储结构式怎么样的。(注意:下面的实例,使用的表的结构为堆表形式,这也是Oracle/DB2/PostgreSQL等数据库采用的表组织形式,而不是InnoDB引擎所采用的聚簇索引表。其实,表结构采用何种形式并不重要,最重要的是理解下面章节的核心,在任何表结构中均适用)

     

    create table t1 (a int primary key, b int, c int, d int, e varchar(20));

     

    create index idx_t1_bcd on t1(b, c, d);

     

    insert into t1 values (4,3,1,1,’d’);

    insert into t1 values (1,1,1,1,’a’);

    insert into t1 values (8,8,8,8,’h’):

    insert into t1 values (2,2,2,2,’b’);

    insert into t1 values (5,2,3,5,’e’);

    insert into t1 values (3,3,2,2,’c’);

    insert into t1 values (7,4,5,5,’g’);

    insert into t1 values (6,6,4,4,’f’);

     

    t1表的存储结构如下图所示(只画出了idx_t1_bcd索引与t1表结构,没有包括t1表的主键索引):

     

     

     

    简单分析一下上图,idx_t1_bcd索引上有[b,c,d]三个字段(注意:若是InnoDB类的聚簇索引表,idx_t1_bcd上还会包括主键a字段),不包括[a,e]字段。idx_t1_bcd索引,首先按照b字段排序,b字段相同,则按照c字段排序,以此类推。记录在索引中按照[b,c,d]排序,但是在堆表上是乱序的,不按照任何字段排序。

     

    3        SQL的where条件提取

     

    在有了以上的t1表之后,接下来就可以在此表上进行SQL查询了,获取自己想要的数据。例如,考虑以下的一条SQL:

     

    select * from t1 where b >= 2 and b < 8 and c > 1 and d != 4 and e != ‘a’;

     

    一条比较简单的SQL,一目了然就可以发现where条件使用到了[b,c,d,e]四个字段,而t1表的idx_t1_bcd索引,恰好使用了[b,c,d]这三个字段,那么走idx_t1_bcd索引进行条件过滤,应该是一个不错的选择。接下来,让我们抛弃数据库的思想,直接思考这条SQL的几个关键性问题:

     

    l         此SQL,覆盖索引idx_t1_bcd上的哪个范围?

     

    起始范围:记录[2,2,2]是第一个需要检查的索引项。索引起始查找范围由b >= 2,c > 1决定。

    终止范围:记录[8,8,8]是第一个不需要检查的记录,而之前的记录均需要判断。索引的终止查找范围由b < 8决定;

     

    2        在确定了查询的起始、终止范围之后,SQL中还有哪些条件可以使用索引idx_t1_bcd过滤?

     

    根据SQL,固定了索引的查询范围[(2,2,2),(8,8,8))之后,此索引范围中并不是每条记录都是满足where查询条件的。例如:(3,1,1)不满足c > 1的约束;(6,4,4)不满足d != 4的约束。而c,d列,均可在索引idx_t1_bcd中过滤掉不满足条件的索引记录的。

    因此,SQL中还可以使用c > 1 and d != 4条件进行索引记录的过滤。

     

    3        在确定了索引中最终能够过滤掉的条件之后,还有哪些条件是索引无法过滤的?

     

    此问题的答案显而易见,e != ‘a’这个查询条件,无法在索引idx_t1_bcd上进行过滤,因为索引并未包含e列。e列只在堆表上存在,为了过滤此查询条件,必须将已经满足索引查询条件的记录回表,取出表中的e列,然后使用e列的查询条件e != ‘a’进行最终的过滤。

     

    在理解以上的问题解答的基础上,做一个抽象,可总结出一套放置于所有SQL语句而皆准的where查询条件的提取规则:

     

    所有SQL的where条件,均可归纳为3大类:Index Key (First Key & Last Key),Index Filter,Table Filter。

     

    接下来,让我们来详细分析者3大类分别是如何定义,以及如何提取的。

     

    l         Index Key

     

    用于确定SQL查询在索引中的连续范围(起始范围+结束范围)的查询条件,被称之为Index Key。由于一个范围,至少包含一个起始与一个终止,因此Index Key也被拆分为Index First Key和Index Last Key,分别用于定位索引查找的起始,以及索引查询的终止条件。

     

    Index First Key

     

    用于确定索引查询的起始范围。提取规则:从索引的第一个键值开始,检查其在where条件中是否存在,若存在并且条件是=、>=,则将对应的条件加入Index First Key之中,继续读取索引的下一个键值,使用同样的提取规则;若存在并且条件是>,则将对应的条件加入Index First Key中,同时终止Index First Key的提取;若不存在,同样终止Index First Key的提取。

    针对上面的SQL,应用这个提取规则,提取出来的Index First Key为(b >= 2, c > 1)。由于c的条件为 >,提取结束,不包括d。

     

    Index Last Key

     

    Index Last Key的功能与Index First Key正好相反,用于确定索引查询的终止范围。提取规则:从索引的第一个键值开始,检查其在where条件中是否存在,若存在并且条件是=、<=,则将对应条件加入到Index Last Key中,继续提取索引的下一个键值,使用同样的提取规则;若存在并且条件是 < ,则将条件加入到Index Last Key中,同时终止提取;若不存在,同样终止Index Last Key的提取。

    针对上面的SQL,应用这个提取规则,提取出来的Index Last Key为(b < 8),由于是 < 符号,因此提取b之后结束。

     

    2         Index Filter

     

    在完成Index Key的提取之后,我们根据where条件固定了索引的查询范围,但是此范围中的项,并不都是满足查询条件的项。在上面的SQL用例中,(3,1,1),(6,4,4)均属于范围中,但是又均不满足SQL的查询条件。

    Index Filter的提取规则:同样从索引列的第一列开始,检查其在where条件中是否存在:若存在并且where条件仅为 =,则跳过第一列继续检查索引下一列,下一索引列采取与索引第一列同样的提取规则;若where条件为 >=、>、<、<= 其中的几种,则跳过索引第一列,将其余where条件中索引相关列全部加入到Index Filter之中;若索引第一列的where条件包含 =、>=、>、<、<= 之外的条件,则将此条件以及其余where条件中索引相关列全部加入到Index Filter之中;若第一列不包含查询条件,则将所有索引相关条件均加入到Index Filter之中。

    针对上面的用例SQL,索引第一列只包含 >=、< 两个条件,因此第一列可跳过,将余下的c、d两列加入到Index Filter中。因此获得的Index Filter为 c > 1 and d != 4 。

     

    3         Table Filter

     

    Table Filter是最简单,最易懂,也是提取最为方便的。提取规则:所有不属于索引列的查询条件,均归为Table Filter之中。

    同样,针对上面的用例SQL,Table Filter就为 e != ‘a’。

     

    3.1 Index Key/Index Filter/Table Filter小结 

     

    SQL语句中的where条件,使用以上的提取规则,最终都会被提取到Index Key (First Key & Last Key),Index Filter与Table Filter之中。

     

    Index First Key,只是用来定位索引的起始范围,因此只在索引第一次Search Path(沿着索引B+树的根节点一直遍历,到索引正确的叶节点位置)时使用,一次判断即可;

     

    Index Last Key,用来定位索引的终止范围,因此对于起始范围之后读到的每一条索引记录,均需要判断是否已经超过了Index Last Key的范围,若超过,则当前查询结束;

     

    Index Filter,用于过滤索引查询范围中不满足查询条件的记录,因此对于索引范围中的每一条记录,均需要与Index Filter进行对比,若不满足Index Filter则直接丢弃,继续读取索引下一条记录;

     

    Table Filter,则是最后一道where条件的防线,用于过滤通过前面索引的层层考验的记录,此时的记录已经满足了Index First Key与Index Last Key构成的范围,并且满足Index Filter的条件,回表读取了完整的记录,判断完整记录是否满足Table Filter中的查询条件,同样的,若不满足,跳过当前记录,继续读取索引的下一条记录,若满足,则返回记录,此记录满足了where的所有条件,可以返回给前端用户。

     

     

    4        结语

     

    在读完、理解了以上内容之后,详细大家对于数据库如何提取where中的查询条件,如何将where中的查询条件提取为Index Key,Index Filter,Table Filter有了深刻的认识。以后在撰写SQL语句时,可以对照表的定义,尝试自己提取对应的where条件,与最终的SQL执行计划对比,逐步强化自己的理解。

     

    同时,我们也可以回答文章开始提出的一个问题:MySQL 5.6中引入的Index Condition Pushdown,究竟是将什么Push Down到索引层面进行过滤呢?对了,答案是Index Filter。在MySQL 5.6之前,并不区分Index Filter与Table Filter,统统将Index First Key与Index Last Key范围内的索引记录,回表读取完整记录,然后返回给MySQL Server层进行过滤。而在MySQL 5.6之后,Index Filter与Table Filter分离,Index Filter下降到InnoDB的索引层面进行过滤,减少了回表与返回MySQL Server层的记录交互开销,提高了SQL的执行效率。

    展开全文
  • 数据库中having的作用

    2019-06-19 19:53:39
    HavingGroup By之后使用 Having类似于Where的使用 差别是Where过滤行,Having是过滤组 Habing支持所有Where操作符

    Having在Group By之后使用

    Having类似于Where的使用
    差别是Where过滤行,Having是过滤组
    Habing支持所有Where操作符

    展开全文
  •  “where”是一个约束声明,查询数据库结果返回之前对数据库的查询条件做一个约束,即返回结果之前起作用,“where”后面不能跟聚合函数;  “having”是一个过滤声明,查询数据库结果返回之后进行过滤,即...

    从整体声明角度分析:

      “where”是一个约束声明,在查询数据库结果返回之前对数据库的查询条件做一个约束,即返回结果之前起作用,“where”后面不能跟聚合函数;

      “having”是一个过滤声明,在查询数据库结果返回之后进行过滤,即返回结果之后起作用,“having”后面可以加聚合函数;

    聚合函数:是对一组值进行计算,返回单一的值,例如:count(),sum(),max(),min()

    从使用角度分析:

      “where”   select  student_id,student_name from student where student_sorce>80;

      "having"  select sum(sorce) from student group by student_sex having student_id<10;

    转载于:https://www.cnblogs.com/xixi-66/p/9848152.html

    展开全文
  • where”是一个约束声明,查询数据库的结果返回之前对数据库中的查询条件进行约束,即结果返回之前起作用,且where后面不能使用“聚合函数”; “having”是一个过滤声明,所谓过滤是查询数据库的结果返回...

    数据库查询中where和having的用法

    类型

    “where”是一个约束声明,在查询数据库的结果返回之前对数据库中的查询条件进行约束,即在结果返回之前起作用,且where后面不能使用“聚合函数”;

    “having”是一个过滤声明,所谓过滤是在查询数据库的结果返回之后进行过滤,即在结果返回之后起作用,并且having后面可以使用“聚合函数”。

    使用的角度

    where后面之所以不能使用聚合函数是因为where的执行顺序是在聚合函数之前,如下面这个sql语句是错误的:

    select deptno,count(*) from emp group by deptno where count(*)>3 order by deptno;
    

    having既然是对查出来的结果进行过滤,那么就不能对没有查出来的值使用having,如下面这个sql语句是错误的:

    select deptno,count(*) from emp group by deptno,sal having sal > 800 order by deptno;
    

    转自:愚蠢的程序员

    展开全文
  • where 子句的作用对查询结果进行分组前,将不符合where条件的行去掉,即分组之前过滤数据,where条件不能包含聚组函数,使用where条件过滤出特定的行。 having 子句的作用是筛选满足条件的组,即分组之后...
  • Where” 是一个约束声明,使用Where来约束来之数据库的数据,Where结果返回之前起作用的,且Where中不能使用聚合函数。 “Having”是一个过滤声明,是查询返回结果集以后对查询结果进行过滤操作,Having...
  • 1.用的地方不一样 where可以用于select、update、delete和insert...into语句中。...WHERE 子句指定的检索条件中,不能引用包含聚合函数中的输入源中的列或者表达式 HAVING 子句聚合之后起作用,不能放在
  • 数据库中的索引以及作用

    千次阅读 2017-09-06 16:26:49
    什么是索引? 答:索引是对数据库表中一个或多个列(例如,employee 表姓名 (name) 列)...有了索引之后(必须是ID这一列上建立索引),即可索引查找。由于索引是经过某种算法优化过,因而查找次数要少
  • on和where后面所跟限制条件区别,主要与限制条件起作用的时机有关,on根据限制条件对数据库记录进行过滤,然后生产临时报表;而where临时报表生产之后,根据限制条件从临时报表筛选结果。 总结:左外连接...
  • 2.1 where用于初始表筛选查询,它是一个约束声明,用于约束数据,返回结果集之前起作用。 2.2 group by 先获取select查询数据结果,然后从每组取出一个指定字段或者表达式值。 3.执行顺序 : where &...
  • 数据库中where子句与having子句区别: where: ① where是一个约束声明,使用where来约束来自数据库数据; ② where结果返回之前起作用的; ③ where不能使用聚合函数(聚合函数包括:sum、count、max...
  • 我们先来看看这个语句的结果:select * from table where 1=1,其中where 1=1,由于1=1永远是... 一、不用where 1=1 多条件查询中的困扰  举个例子,如果您做查询页面,并且,可查询的选项有多个,同时,还让
  • where 1=1; 这个条件始终为True,不定...一、不用where 1=1 多条件查询中的困扰  举个例子,如果您做查询页面,并且,可查询的选项有多个,同时,还让用户自行选择并输入查询关键词,那么,按平时的查询语句
  • Where”是一个约束声明,查询数据库的结果返回之前对数据库中的查询条件进行约束,即结果返回之前起作用,且where后面不能使用“聚合函数”; “Having”是一个过滤声明,所谓过滤是查询数据库的结果返回...
  • where数据库中常用where关键字,用于初始表中筛选查询。它是一个约束声明,用于约束数据,返回结果集之前起作用。 group by:对select查询出来结果集按照某个字段或者表达式进行分组,获得一组组集合...
  • 例子:select org.id from org where inner_code like '12011601001' || '%' ...||oracle数据库中起到字符串拼接的作用,上面等同于'12011601001%' 转载于:https://www.cnblogs.com/zhenmingliu/p/3159207.html...
  • Where”是一个约束声明,查询数据库的结果返回之前对数据库中的查询条件进行约束,即结果返回之前起作用,且where后面不能使用“聚合函数”; “Having”是一个过滤声明,所谓过滤是查询数据库的结果返回...
  • id,title,actors,plot,catagory,release_date,rated FROM ".TBL_DATA." WHERE title = '".$q1."' "); while($row = mysql_fetch_array($result)) { echo "<div <p> ".$row['title']." <p> </...
  • 当我们面对大量的数据查询时,为了提高查询效率,我们在数据库中总要使用到索引。那么索引究竟是怎么样的东西呢? 索引本质 索引其实就是一种数据结构,他将数据库中的数据以一定的数据结构算法进行存储,能够帮助...
  • 最近项目发现很多sql用到了 where1=1 这个条件 然后,大概了解了一下他具体作用: select*from fore_payment_order where 1=1  and ta_no = #{taNo,jdbcType=VARCHAR}  and t_ta_no = #{tTaNo,...
  • 竟然还不懂这个? 上课时候老师讲sql语句时,感觉知道一点,就没太认真听,而且感觉大概...Where 是一个约束声明,使用Where约束来自数据库的数据,Where结果返回之前起作用的Where中不能使用聚合函数。...
  • having字句可以让我们筛选成组后各种数据,where字句聚合前先筛选记录,也就是说作用在group by和having字句前。而 having子句聚合后对组记录进行筛选。 SQL实例: 一、显示每个地区总人口数和总面积. ...
  • having字句可以让我们筛选成组后各种数据,where字句聚合前先筛选记录,也就是说作用在group by和having字句前。而 having子句聚合后对组记录进行筛选。我理解就是真实表没有此数据,这些数据是通过一些...
  • 数据库语句 select * from table where 1=1 的用法和作用 ...我们先来看看这个语句的结果:select * ...所以,这条语句,就相当于select * from table,返回查询表中的所有数据。 一、不用where 1=1 多条件
  • 搜索映像就是select * from table where,那为什么还要ES?当数据变多时,原有方案开始变得糟糕mysql对于一些较为固定,字段较少查询方式,可以通过简单增加索引来完成优化,大多数公司,即使你对索引...
  • 数据库中的集合操作主要包括3个方面:1. Union合并行union的作用是为了合并两个查询结果,而且合并的同时把相同的行去重。例如:如果我们需要显示全部的并集,即不去重,可以使用union all,如下:我们只需要使用...
  • having字句可以让我们筛选成组后各种数据,where字句聚合前先筛选记录,也就是说作用在group by和having字句前。而 having子句聚合后对组记录进行筛选。SQL实例:一、显示每个地区总人口数和总面积.SELECT ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 671
精华内容 268
关键字:

where在数据库中的作用