select count(status=1 or null) as s1_count, count(status=2 or null) as s2_count from order; 注意：count是返回匹配条件的行数
demo() { //以shop为主表 $res = Db::table('shop') ->alias("a") //取一个别名 //与shop_class表进行关联...，取名i，并且a表的shop_class字段等于i表的class_code字段 //表一和表二相同的地方：'a.shop_class = i.class_code' ->join
SELECT a.id as lang_id, a.name as name, b.cnt as cnt FROM programming_lang a LEFT JOIN cnt b on...SELECT a.id as lang_id, a.name as name, b.cnt as cnt FROM programming_lang a right JOIN cnt b on
t2中获取到的结果进行合并,将结果放入结果集 循环上3个步骤,直到无法满足条件,将结果集返回给客户端 整个过程会读取t2表所有数据(100行数据),然后遍历每行数据字段a的值,根据t2表中a的值扫描t1...,则将数据连接后放入结果集中(就是外层循环的结果和内存结果组合成一条数据),然后,外层的驱动表扫描获取第二条数据,并和被驱动表进行条件匹配,将匹配成功数据连接后放入结果集中,剩余的数据以此类推,最后,将结果集返回给客户端...中（默认内存大小为256k,如果数据量多,会进行分段存放,然后进行比较） 把表t1的每一行数据,跟join_buffer中的数据批量进行对比 循环上两个步骤,直到无法满足条件,将结果集返回给客户端 这个例子里表...比如 t2 表有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录，然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果...1、永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量) 2、为匹配的条件增加索引(减少内层表的循环次数) 3、增大join buffer size的大小（一次缓存的数据越多，那么外层表循环的次数就越少
语句 Q1 返回的数据集是 6 行，表 a 中即使没有满足匹配条件的记录，查询结果中也会返回一行，并将表 b 的各个字段值填成 NULL。 2. 语句 Q2 返回的是 4 行。...从逻辑上可以这么理解，最后的两行，由于表 b 中没有匹配的字段，结果集里面 b.f2 的值是空，不满足 where 部分的条件判断，因此不能作为结果集的一部分。...今天，我们再一起分析一次 SQL 语句的 explain 结果。 可以看到，这条语句是以表 b 为驱动表的。...然后，顺序遍历被驱动表的所有行，每一行数据都跟 join_buffer 中的数据进行匹配，匹配成功则作为结果集的一部分返回。...Simple Nested Loop Join 算法的执行逻辑是：顺序取出驱动表中的每一行数据，到被驱动表去做全表扫描匹配，匹配成功则作为结果集的一部分返回。
image.png 需要注意的是，在第二步中，根据 a 字段去表t1中查询时，使用了索引，所以每次扫描只会扫描一行(从explain结果得出，根据不同的案例场景而变化)。...CPU、磁盘I/O 内存、临时空间 内存、临时空间 特点 当有高选择性索引或进行限制性搜索时效率比较高，能够快速返回第一次的搜索结果 当缺乏索引或者索引条件模糊时，Hash Join 比 Nested...当连接字段有索引或者提前排好序时，比 hash join 快，并且支持更多的连接条件 缺点 无索引或者表记录多时效率低 建立哈希表需要大量内存，第一次的结果返回较慢 所有的表都需要排序。...比如订单记录里一般只有订单用户的 user_id，返回信息时需要取得用户姓名，可能的实现方案有如下几种： 一次数据库操作，使用 join 操作，订单表和用户表进行 join，连同用户名一起返回； 两次数据库操作...，分两次查询，第一次获得订单信息和 userid，第二次根据 userid 取姓名，使用代码程序进行信息合并； 使用冗余用户名称或者从 ES 等非关系数据库中读取。
需要注意的是，在第二步中，根据 a 字段去表t1中查询时，使用了索引，所以每次扫描只会扫描一行(从explain结果得出，根据不同的案例场景而变化)。 假设驱动表的行数是N，被驱动表的行数是 M。...CPU、磁盘I/O 内存、临时空间 内存、临时空间 特点 当有高选择性索引或进行限制性搜索时效率比较高，能够快速返回第一次的搜索结果 当缺乏索引或者索引条件模糊时，Hash Join 比 Nested...当连接字段有索引或者提前排好序时，比 hash join 快，并且支持更多的连接条件 缺点 无索引或者表记录多时效率低 建立哈希表需要大量内存，第一次的结果返回较慢 所有的表都需要排序。...比如订单记录里一般只有订单用户的 user_id，返回信息时需要取得用户姓名，可能的实现方案有如下几种： 一次数据库操作，使用 join 操作，订单表和用户表进行 join，连同用户名一起返回； 两次数据库操作...，分两次查询，第一次获得订单信息和 user_id，第二次根据 user_id 取姓名，使用代码程序进行信息合并； 使用冗余用户名称或者从 ES 等非关系数据库中读取。
1.EXISTS子查询 语法： select .......from 表名 where exists(子查询) 子查询有返回结果： EXISTS子查询结果为TRUE，则执行外层查询 子查询无返回结果：...EXISTS子查询结果为FALSE,外层查询不执行 当数据量大的时候使用exists,如数据量于一万以上使用，数据量少时可以使用in 示例： /*1.检查‘logic java’ 课程最近一次考试成绩*...student中数据逐条匹配表result中的数据 1.匹配，返回到结果集 2.无匹配，NULL值返回到结果集 示例： /*左外连接 left join 前面的表为主表，以主表里的字段为依据，把从表里的数据填充给主表...，从表里面没有的字段以null填充 left join 后面的表为从表*/ select s.studentName as 学生姓名, r.subjectNo as 科目编号, r.studentResult...右表逐条去匹配记录；否则NULL填充 示例： /*右外连接 right join前面的表为主表，以主表里的字段为依据，把从表里的数据填充给主表，从表里面没有的字段以null填充 right join
嵌套循环连接 Nested-Loop Join(NLJ) 算法 （NLP） 定义 一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行...） 拿到t2 结果集中的一条记录中的关联字段 a , 去t1表中查找 取出 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端 重复上述步骤 我们来算一下这个操作MySQL要读取多少行数据...，跟 join_buffer 中的数据做对比 返回满足 join 条件的数据 我们来算一下这个操作MySQL要读取多少行数据 整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000...举个例子 比如 t2 表有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录，然后从 t1 表里取数据跟 join_buffer...中数据对比得到部分结果，然后清空 join_buffer ，再放入 t2 表剩余200行记录，再次从 t1 表里取数据跟 join_buffer 中数据对比。
所以，该语句里： t1 是驱动表 t2是被驱动表 使用索引字段join的 explain结果 t2的字段a上有索引，join过程用了该索引，因此该语句执行流程： 从t1读入一行数据 R...从数据行R中，取出a字段到t2里查找 取出t2中满足条件的行，跟R组成一行，作为结果集一部分 重复执行步骤1到3，直到t1的末尾循环结束 这个过程是先遍历t1，然后根据从t1中取出的每行数据中的a值，去...循环遍历这100行数据： 从每一行R取出字段a的值$R.a 执行select * from t2 where a=$R.a 把返回的结果和R构成结果集的一行 该查询过程，也扫描了200行，但共执行了101...扫描t2，把t2中的每一行取出来，对比join_buffer数据，满足join条件的，作为结果集的一部分返回。...join_buffer，放完第88行join_buffer满了，继续第2步 扫描t2，把t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回 清空join_buffer
单行子查询 多行子查询 分类方式2： 我们按内查询是否被执行多次，将子查询划分为 相关(或关联)/不相关(或非关联) 子查询 子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，...`employee_id`; 非自连接：上面写的都属于非自连接 角度3：内链接 vs 外链接 内连接 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行 外连接 两个表在连接过程中除了返回满足连接条件的行以外还返回左...没有匹配的行时, 结果表中相应的列为空(NULL)。 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。...FROM table2 UNION操作符 UNION 操作符返回两个查询的结果集的并集，去除重复记录。 UNION ALL操作符 UNION ALL操作符返回两个查询的结果集的并集。...`department_id` IS NULL #语法小结 #左中图 #实现A - A∩B select 字段列表 from A表 left join B表 on 关联条件 where 从表关联字段 is
二 原理 2.1 Nested Loop Join算法 NLJ 算法:将驱动表/外部表的结果集作为循环基础数据，然后循环从该结果集每次一条获取数据作为下一个表的过滤条件查询数据，然后合并结果。...如果有多表join，则将前面的表的结果集作为循环数据，取到每行再到联接的下一个表中循环匹配，获取结果集返回给客户端。 Nested-Loop 的伪算法如下: ?...因为普通Nested-Loop一次只将一行传入内层循环, 所以外层循环(的结果集)有多少行, 内存循环便要执行多少次.在内部表的连接上有索引的情况下，其扫描成本为O(Rn),若没有索引,则扫描成本为O(...表(内部表)中直接匹配这10行数据，内存循环就可以一次与这10行进行比较, 这样只需要比较10次，对内部表的扫描减少了9/10。...从图中可以看到把t1和t2的结果集放到join_buffer中，而不用每次t1和t2关联后马上有和t3关联，这也是没有必要的，然后只需一次扫描t3即可完成这个查询；需要注意的是join buffer中只保留查询结果中出现的列值
子查询 (内查询) 在主查询之前一次执行完成。 子查询的结果被主查询(外查询)使用 。 可以用一个子查询替代上边的的表名。 子查询，将查询操作嵌套在另一个查询操作中。...内连接 INNER JOIN 内连接(INNER JOIN)，返回连接表中符合连接条件和查询条件的数据行。(所谓的链接表就是数据库在做查询形成的中间表)。...从保证某个表的数据的完整性来说的话，LEFT JOIN 左外连接，保证左表的完整性，RIGHT JOIN 右外连接，保证右表的完整性 （1)左外连接LEFT JOIN或LEFT OUTER JOIN 左外联接的结果集包括...（2)右外链接RIGHT JOIN 或 RIGHT OUTER JOIN 右外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。...（3)全外连接（全连接）FULL JOIN 或 FULL OUTER JOIN 完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。
一旦找到匹配的行，MySQL将这些行组合在一起，并将它们作为结果集返回。...中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。...这条SQL语句的explain结果如下所示： 图片 可以看到，在这个过程中，MySQL对表 t1 和 t2 都做了一次全表扫描，因此总的扫描行数是1100。...扫描被驱动表跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。 清空join_buffer，重复上述步骤。...其实我们可以从表t1里一次性地多拿些行出来，先放到一个临时内存，一起传给表t2。这个临时内存不是别人，就是join_buffer。
从上述执行计划，我们可以看出，t2表为驱动表，先与t3做关联，得到结果后再与t1做关联，最后将结果集返回给客户端。 我们都知道，MySQL从server层返回数据给client，是一行一行返回的。...也就是上层结果集与t1表每关联一行，有结果后，在没有排序的情况下，就是直接返回，并不会等所有行关联完后一起返回。...那么整个关联路径，是怎么样的呢，简化流程后应该是下面两种情况的一个 从t2取出所有数据，与t3表关联得到所有结果集后；再从t1中取一行关联，每得到一行结果，返回一次数据 从t2取一行数据，与t3表关联得到一行结果后...，也可以看出来，在测试使用的SQL结构中，关联顺序是方法2，也就是从t2取一行数据，与t3表关联得到一行结果后，再从t1中取一行关联，每得到一行结果，返回一次数据。...第12行，对从存储引擎层返回的数据，做进一步过滤，这里也循环99次 第8行，t2 表与t3表的关联，关联后返回记录20条，完成关联耗时为0.834毫秒 第13行，以普通索引等值查询，从t1表中获取数据,
这种算法的代价是，外表和内表在build阶段进行一次读IO和一次写IO，在probe阶段进行了一次读IO，所以整体IO开销是3*(M+N)。相对于之前需要k次扫描内表的方式，当前算法更优。...Join返回第一行的时间： 不允许落盘时，需要立即执行Probe探测当前Hash Table，从而“尽快”返回上层算子执行结果；探测完成一遍后，需要重复填充Hash Table，探测再重新执行 允许落盘时...，左表“一次性”执行完成build hash table 过程，从而“较晚”返回上层算子执行结果，但是探测过程可以“一口气”执行完成 是否允许allow_spill_to_disk主要基于规则：上层算子是否期望所有结果集...即，Hash Join不落盘时需要立即处理当前Hash Table中的数据，形成连接结果来返回上层，而不是对build input table（左表，构建hash table）全部构建好以后才探测。...根据是否能立即输出结果，可以分为两类： 前两种，在不存在第三种时（On-disk Hash Join能够存入内存 或 Basic Hash Join build_input能一次存入内存），可以直接输出结果
条数据，跳过前10000条数据，查询结果将返回"employees"表中第10001到第10010条记录。...优化方案：排序时返回的字段尽可能少，即在排序子查询时得到的结果集字段少，如只有id，再根据id 去查找其他字段。...嵌套循环连接（Nested-Loop Join） 算法 NLJ 算法就是一次一行循环地从连接的第一张表（驱动表）中读取数据行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的数据行...从第一步中取出关联字段 a，到被驱动表 t1 中查找。 从第二部中取出满足条件的数据行，与 t2 表中获取的结果合并，作为结果返回。 重复上述三步骤。...再把被驱动表 t1 中每一行数据取出来，跟join_buffer 中数据进行对比。 返回满足条件的数据结果集。
操作被称为连接，作用是能连接多个表的数据（通过连接条件），从多个表中获取数据合并在一起作为结果集返回给客户端。...从使用的角度来说，还是推荐使用显示连接，这样可以更清楚的显示出多个表之间的连接关系和连接依赖的属性。 二、外连接 左外连接 1. 语法：A left join B 2....INLJ内层循环读取的是索引，可以减少内存循环的次数，提高join效率，但是也有缺点的，就是如果扫描的索引是非聚簇索引，并且需要访问非索引的数据，会产生一个回表读取数据的操作，这就多了一次随机的I/O操作...尽量增加连接条件，减少join后数据集的大小 2. 用小结果集驱动大结果集，将筛选结果小的表首先连接，再去连接结果集比较大的表 3. 被驱动表的被join的字段要建立索引，且使用上索引。...A：要通过where筛选，连接条件只影响连接过程，不影响连接返回的结果数（某些情况下连接条件会影响连接返回的结果数，例如左连接中，右侧匹配的数据不唯一的时候） Q：被驱动表匹配的数据行不唯一导致最终连接数据超过驱动表数据量该怎么办
select * from depart where a = R.a 把返回的结果和 R 组合构成结果集的一行 可以看到，这套流程一共需要扫描的行数其实也是 200 行 但是！...，就要做一次全表扫描，整个执行流程是这样的： 从 user 表中读入一行数据 R 从数据行 R 中，取出 name 字段到表 depart 上做全表查询，并取得对应的主键 根据主键回表查询，取出 depart...，跟 join_buffer 中的数据做对比，满足 on 条件的，就作为结果集的一部分返回 上张图看一下： 需要注意的是，join_buffer 中的数据都是无序存储的。...，满足 join 条件的，作为结果集的一部分返回 清空 join_buffer（重点就是这一步） 继续扫描表 user，顺序读取最后的 40 行数据放入 join_buffer 中，然后继续执行第 2...，跟 join_buffer 中的数据做对比，满足 on 条件的，就作为结果集的一部分返回 join_buffer 中的数据都是无序存储的，由于没有用上被驱动表的索引，所以对表 B 中的每一行，取出来后需要跟
领取专属 10元无门槛券
手把手带您无忧上云
