他的回复:
微信昵称：阿静华为云账号：hw66179654第2天学习笔记一：嵌套循环 NESTED LOOP嵌套循环的算法驱动表返回一行数据，驱动表返回多少行，被驱动表就要被扫描多少次在执行计划中，离 nested loops关键字最近的表就是驱动表嵌套循环使用场景驱动表应该返回少量数据，关联条件的索引命中的数据必须很少。提问：两表关联走不走NL是看两个表关联之后返回的数据量多少?还是看驱动表返回的数据量多少？所以判断两表关联是否应该走NL应该直接查看两表关联之后返回的数据量，如果两表关联之后返回的数据量少，可以走NL；返回的数据量多，应该走HASH连接。此外，还需要关注被驱动表的过滤条件和是否需要回表提问：select * from a,b where a.id=b.id; 如果a有100条数据，有100万行数据，a与b是1∶N关系，N很低，应该怎么优化SQL?回答：因为a与b是1∶N关系，N很低，我们可以在b的连接列（id）上创建索引，让a与b走嵌套循环（a nl b），这样b表会被扫描100次，但是每次扫描表的时候走的是id列的索引（范围扫描）。外联结使用嵌套循环 (nest loop anti)当两表使用外连接进行关联，如果执行计划是走嵌套循环，那么这时无法更改驱动表，驱动表会被固定为主表。思考为什么？为什么外连接的从表有过滤条件会变成内连接呢？因为外连接的从表有过滤条件已经排除了从表与主表没有关联上显示为NULL的情况。嵌套循环的提示use_nl(d,e)表示让两表走嵌套循环，在书写HINT的时候，如果表有别名，HINT中一定要使用别名，否则HINT不生效；如果表没有别名，HINT中就直接使用表名。提问：两表关联走不走NL是看两个表关联之后返回的数据量多少?还是看驱动表返回的数据量多少？回答：如果两个表是1∶N关系，驱动表为1，被驱动表为N并且N很大，这时即使驱动表返回数据量很少，也不能走嵌套循环，因为两表关联之后返回的数据量会很多。所以判断两表关联是否应该走NL应该直接查看两表关联之后返回的数据量，如果两表关联之后返回的数据量少，可以走NL；返回的数据量多，应该走HASH连接。此外，还需要关注被驱动表的过滤条件和是否需要回表提问：大表是否可以当嵌套循环（NL）驱动表？回答：可以，如果大表过滤之后返回的数据量很少就可以当NL驱动表。提问：select * from a,b where a.id=b.id; 如果a有100条数据，b有100万行数据，a与b是1∶N关系，N很低，应该怎么优化SQL?回答：因为a与b是1∶N关系，N很低，我们可以在b的连接列（id）上创建索引，让a与b走嵌套循环（a nl b），这样b表会被扫描100次，但是每次扫描表的时候走的是id列的索引（范围扫描）。二：HASH连接HASH连接的适用场景哈希连接只支持等值连接。哈希连接的适用场景就是嵌套循环的不适用场景，即两表连接条件关联的结果集比较大。Used-Mem表示HASH连接消耗了多少PGA，当驱动表太大、PGA不能完全容纳驱动表时，驱动表就会溢出到临时表空间，进而产生磁盘 HASH连接，这时候HASH连接性能会严重下降。嵌套循环不需要消耗PGA。思考：怎么优化HASH连接？回答：因为HASH连接需要将驱动表的select列和join列放入PGA中，所以，我们应该尽量避免书写select * from....语句，将需要的列放在select list中，这样可以减少驱动表对PGA的占用，避免驱动表被溢出到临时表空间，从而提升查询性能。外连接的HASH对于左外连接，Oracle会选择小表为hash表（执行计划上面的表）。Oracle如果想改变hash表，需要使用swap_join_inputs提示（leading不起作用）。三.排序合并连接（SORT MERGE JOIN）适用场景排序合并连接主要用于处理两表非等值关联，比如>，>=，，=，，但是不能用于instr、substr、like、regexp_like关联，instr、substr、like、regexp_like关联只能走嵌套循环。如果两表是等值关联，一般不建议走排序合并连接。因为排序合并连接需要将两个表放入PGA中，而HASH连接只需要将驱动表放入PGA中，排序合并连接与HASH连接相比，需要耗费更多的PGA。算法介绍排序合并连接的算法：两表关联，先对两个表根据连接列进行排序，将较小的表作为驱动表，然后从驱动表中取出连接列的值，到已经排好序的被驱动表中匹配数据，如果匹配上数据，就关联成功。驱动表返回多少行，被驱动表就要被匹配多少次，这个匹配的过程类似嵌套循环，但是嵌套循环是从被驱动表的索引中匹配数据，而排序合并连接是在内存中（PGA中的workarea）匹配数据。思考：怎么优化排序合并连接？回答：如果两表关联是等值关联，走的是排序合并连接，我们可以将表连接方式改为HASH连接。如果两表关联是非等值关联，比如>，>=，，=，，这时我们应该先从业务上入手，尝试将非等值关联改写为等值关联，因为非等值关联返回的结果集“类似”于笛卡儿积，当两个表都比较大的时候，非等值关联返回的数据量相当“恐怖”。如果没有办法将非等值关联改写为等值关联，我们可以考虑增加两表的限制条件，将两个表数据量缩小。四.笛卡儿连接（CARTESIAN JOIN）什么是笛卡儿连接两个表关联没有连接条件的时候会产生笛卡儿积，这种表连接方式就叫笛卡儿连接。执行计划中MERGE JOIN CARTESIAN就表示笛卡儿连接。在多表关联的时候，两个表没有直接关联条件，但是优化器错误地把某个表返回的Rows算为1行（注意必须是1行），这个时候也可能发生笛卡儿连接。思考：当执行计划中有笛卡儿连接应该怎么优化呢？首先应该检查表是否有关联条件，如果表没有关联条件，那么应该询问开发与业务人员为何表没有关联条件，是否为满足业务需求而故意不写关联条件。其次应该检查离笛卡儿连接最近的表是否真的返回1行数据，如果返回行数真的只有1行，那么走笛卡儿连接是没有问题的，如果返回行数超过1行，那就需要检查为什么Rows会估算错误，同时要纠正错误的Rows。纠正错误的Rows之后，优化器就不会走笛卡儿连接了。我们可以使用HINT /+opt_param('_optimizer_mjc_enabled', 'false') / 禁止笛卡儿连接。五.标量子查询（SCALAR SUBQUERY）什么是标量子查询？当一个子查询介于select与from之间，这种子查询就叫标量子查询 。标量子查询类似一个天然的嵌套循环，而且驱动表固定为主表。标量子查询中子查询的表连接列也必须包含在索引中。图片：标量子查询优化建议尽量避免使用标量子查询，假如主表返回大量数据，主表的连接列基数很高，那么子查询中的表会被多次扫描，从而严重影响SQL性能。当SQL里面有标量子查询，我们可以将标量子查询等价改写为外连接，从而使它们可以进行HASH连接。六.半连接（SEMI JOIN）什么是半连接？两表关联只返回一个表的数据就叫半连接。半连接一般就是指的in和exists。 in和exists一般情况下都可以进行等价改写。半连接和内连接的等价写法（性能不如半连接）A . 半连接以子查询为驱动，对主表嵌套循环适用于子查询的数据集非常小并且主表有索引的场景。Oracle hint小技巧1。对于in子查询可以用/*+ qb_name(a) */定义子查询，然后在提示中用a引用对应的表2。Oracle会为每个查询块定义一个名字，提示中也可以用这个名字，block名字通过下面这种方式获得B. 以主表为驱动表，对子查询嵌套循环以主表为Hash表做hash连接适用于主表数据集较少，子查询连接条件有索引的场景C.以主表为Hash表做hash连接适用于两表连接结果集比较大，主表较小的场景D.以子表为Hash表做hash连接适用于两表连接结果集比较大，子表较小的场景思考题现有如下SQL：select * from a where a.id in (select id from b);假设a有1000万，b有100行，请问如何优化该SQL？以a为主表，为a.id 添加索引；b为子表， 然后对2表进行半连接查询假设a有100行，b有1000万，请问如何优化该SQL？以a为主表；b为子表，为b.id 添加索引， 然后以主表为hash表做hash连接假设a有100万，b有1000万，请问如何优化该SQL？以a为主表；b为子表，然后以主表为hash表做hash连接七.反连接（ANTI JOIN）什么是反连接？两表关联只返回主表的数据，而且只返回主表与子表没关联上的数据，这种连接就叫反连接。反连接一般就是指的not in和not exists。SQL> select * from dept where deptno not in (select deptno from emp);SQL> select * from dept where not exists (select null from emp where dept.deptno = emp.deptno);not in和not exists的区别not in的话，只要子查询中有一个null，结果就为空，而not exists不会，说明not in认为null是未知的，可以等于任意数。将not exists等价改写为not in的时候，要注意null。一般情况下，如果反连接采用not in写法，我们需要在where条件中剔除null。not in和not exists的执行计划1）嵌套循环Not in要指定is not null或者有非空约束，注意：驱动表必须是主表2）hash连接Not in要指定is not null或者有非空约束，否则可能会很慢，如果想改变hash表，可以使用提示swap_join_inputs思考题现有如下SQL1）select * from a where a.id not in (select id from b where id is not null);假设a 有1000万条，b有1000条，请问如何优化该SQL？以a为主表，为a.id 添加索引；b为子表， 然后对2表进行半连接查询假设a有1000条，b有1000万条，请问如何优化该SQL？以a为主表；b为子表，为b.id 添加索引， 然后以主表为hash表做hash连接假设a有100万条，b有1000万条，请问如何优化该SQL？以a为主表；b为子表，然后以主表为hash表做hash连接2）IN与EXISTS谁快谁慢？第一：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。第二：若子查询结果集较小，in执行的就快，相反，Exsts执行的就快3）SQL语句性能优化的本质是什么？SQL优化的本质就是：1、缩短响应时间；2、提升系统吞吐量；3、提升系统负载能力。八.子查询无法展开什么叫子查询展开以及为什么要展开SELECT * FROM sales WHERE cust_id IN ( SELECT cust_id FROM customers );就是优化器将嵌套的子查询展开成一个等价的join，然后去优化这个join。上面的语句，不展开的情形是，从sales表中获取的每1条数据，都要代入子查询进行匹配。一般情况下效率都是比较低的。展开的结果可能是将sales表和customers表做一个hash join semi，从而提高效率。因为转换为join后可以从访问路径、连接方法、连接顺序等方面优化整个查询。如果子查询不能展开，执行计划一般会显示为filter，类似嵌套循环，固定以主表为驱动表。在Oracle中，如果子查询有or条件或者rownum条件，往往会出现这种情况。此时效率可能非常低，要避免出现，or语句可以改造为union。四. 三种连接工作方式比较： Hash join的工作方式是将一个表（通常是小一点的那个表）做hash运算，将列数据存储到hash列表中，从另一个表中抽取记录，做hash运算，到hash 列表中找到相应的值，做匹配。 Nested loops 工作方式是从一张表中读取数据，访问另一张表（通常是索引）来做匹配，nested loops适用的场合是当一个关联表比较小的时候，效率会更高。 Merge Join 是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配，因为merge join需要做更多的排序，所以消耗的资源更多。通常来讲，能够使用merge join的地方，hash join都可以发挥更好的性能。
