查看: 4988|回复: 3
怎样用HINT 指定驱动表
论坛徽章:4
select a.*,b.* from
a,b where a.id = b.id
怎样强制指定a 为驱动表
论坛徽章:0
select /*+ ordered*/ a.*,b.* from
a,b where a.id = b.id
论坛徽章:0
select /*+ ordered*/ a.*,b.* from
a,b where a.id = b.id
论坛徽章:0
SELECT /*+USE_NL(emp dept)*/
FROM emp, dept
WHERE emp.deptno = dept.
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：　广播电视节目制作经营许可证：编号（京）字第1149号查看: 387|回复: 2
如何让子查询的结果集做驱动表
求职 : 论坛徽章:3
如何让子查询的结果集做驱动表,或者子查询里面的表做驱动表
create table t1 as select * from dba_objects where object_id&=1000;
create table t2 as select * from t1;
create table t3 as select * from t1;
set autotrace on
select /*+ use_nl(t3 t1) */a.object_id,b.object_name,c.object_type
from t1 a,t2 b,(select object_id,object_type from t3) c
where a.object_id=b.object_id
and a.object_id =c.object_id
and c.object_id=100;
/*+ leading(c) use_nl(c t1) */
上面的两个Hint都没效果，执行计划还是下面这样的：------------------------------------------------------------------------------
| Id&&| Operation& && && && & | Name | Rows&&| Bytes | Cost (%CPU)| Time& &&&|
------------------------------------------------------------------------------
|& &0 | SELECT STATEMENT& && &|& && &|& &&&1 |& &116 |& & 16& &(7)| 00:00:01 |
|*&&1 |&&HASH JOIN& && && && &|& && &|& &&&1 |& &116 |& & 16& &(7)| 00:00:01 |
|& &2 |& &MERGE JOIN CARTESIAN|& && &|& &&&1 |& &103 |& & 10& &(0)| 00:00:01 |
|*&&3 |& & TABLE ACCESS FULL&&| T2& &|& &&&1 |& & 79 |& &&&5& &(0)| 00:00:01 |
|& &4 |& & BUFFER SORT& && &&&|& && &|& &&&1 |& & 24 |& &&&5& &(0)| 00:00:01 |
|*&&5 |& &&&TABLE ACCESS FULL | T3& &|& &&&1 |& & 24 |& &&&5& &(0)| 00:00:01 |
|*&&6 |& &TABLE ACCESS FULL& &| T1& &|& &&&1 |& & 13 |& &&&5& &(0)| 00:00:01 |
------------------------------------------------------------------------------复制代码
论坛徽章:115
(select /*+ no_merge */ object_id,object_type from t3)
认证徽章论坛徽章:40
提示中要用表别名。即：
select /*+ leading(c) use_nl(a b) */ a.object_id,b.object_name,c.object_type
from t1 a,t2 b,(select&&object_id,object_type from t3) c
where a.object_id=b.object_id
and a.object_id =c.object_id
and c.object_id=100;
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：　广播电视节目制作经营许可证：编号（京）字第1149号随笔 - 1171, 文章 - 0, 评论 - 0, 引用 - 0
一哥们问我，怎么才能让子查询作为驱动表？ SQL如下：
from its_car_pass7 v
where 1 = 1
and pass_datetime &=
to_date(' :17:46:04', 'yyyy-mm-dd hh24:mi:ss')
and pass_datetime &=
to_date(' :18:46:06', 'yyyy-mm-dd hh24:mi:ss')
and v.pass_device_unid in
(select unid
from its_base_device
where dev_bay_unid in ('01685EFEC4DDAAEDD37393')
and dev_type = '1'
and dev_chk_flag = '1'
and dev_delete_flag = 'N')
order by v.pass_datetime asc
执行计划如下：
Execution Plan
----------------------------------------------------------
Plan hash value:
--------------------------------------------------------------------------------------------------------------------
| Operation
| Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
0 | SELECT STATEMENT
(50)| 00:00:01 |
SORT ORDER BY
(50)| 00:00:01 |
NESTED LOOPS
NESTED LOOPS
(0)| 00:00:01 |
PARTITION RANGE SINGLE
(0)| 00:00:01 |
INDEX SKIP SCAN
| IDX_VT7_DEVICEID
(0)| 00:00:01 |
INDEX UNIQUE SCAN
| PK_ITS_BASE_DEVICE |
(0)| 00:00:01 |
TABLE ACCESS BY INDEX ROWID| ITS_BASE_DEVICE
(0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("PASS_DATETIME"&=TO_DATE('
17:46:04', 'syyyy-mm-dd hh24:mi:ss') AND
"PASS_DATETIME"&=TO_DATE('
18:46:06', 'syyyy-mm-dd hh24:mi:ss'))
filter("PASS_DATETIME"&=TO_DATE('
17:46:04', 'syyyy-mm-dd hh24:mi:ss') AND
"PASS_DATETIME"&=TO_DATE('
18:46:06', 'syyyy-mm-dd hh24:mi:ss'))
6 - access("V"."PASS_DEVICE_UNID"="UNID")
7 - filter("DEV_BAY_UNID"='01685EFEC4DDAAEDD37393' AND "DEV_TYPE"='1' AND
"DEV_DELETE_FLAG"='N' AND "DEV_CHK_FLAG"='1')
Statistics
----------------------------------------------------------
recursive calls
db block gets
consistent gets
physical reads
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
这里我们就不管统计信息是否准确了，也不管SQL优化的问题，就单单讨论哥们问的问题吧。
那哥们说，怎么才能让子查询作为驱动表呢？他自己试了很多方法就是搞不定。 那我们来亲自搞搞吧
explain plan for
from its_car_pass7 v
where 1 = 1
and pass_datetime &=
to_date(' :17:46:04', 'yyyy-mm-dd hh24:mi:ss')
and pass_datetime &=
to_date(' :18:46:06', 'yyyy-mm-dd hh24:mi:ss')
and v.pass_device_unid in
(select unid
from its_base_device
where dev_bay_unid in ('01685EFEC4DDAAEDD37393')
and dev_type = '1'
and dev_chk_flag = '1'
and dev_delete_flag = 'N')
order by v.pass_datetime asc
执行计划如下
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ADVANCED -PROJECTION'));
-----------------------------------------------------------
Plan hash value:
---------------------------------------------------------------------------------------------------------------------------
| Operation
| Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
0 | SELECT STATEMENT
(1)| 06:58:26 |
NESTED LOOPS
NESTED LOOPS
(1)| 06:58:26 |
PARTITION RANGE SINGLE
(1)| 06:58:26 |
TABLE ACCESS BY LOCAL INDEX ROWID| ITS_CAR_PASS7
(1)| 06:58:26 |
INDEX RANGE SCAN
| IDX_VT7_DATETIME
(1)| 00:01:13 |
INDEX UNIQUE SCAN
| PK_ITS_BASE_DEVICE |
(0)| 00:00:01 |
TABLE ACCESS BY INDEX ROWID
| ITS_BASE_DEVICE
(0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
4 - SEL$5DA710D3 / V@SEL$1
5 - SEL$5DA710D3 / V@SEL$1
6 - SEL$5DA710D3 / ITS_BASE_DEVICE@SEL$2
7 - SEL$5DA710D3 / ITS_BASE_DEVICE@SEL$2
Outline Data
-------------
BEGIN_OUTLINE_DATA
NLJ_BATCHING(@"SEL$5DA710D3" "ITS_BASE_DEVICE"@"SEL$2")
USE_NL(@"SEL$5DA710D3" "ITS_BASE_DEVICE"@"SEL$2")
LEADING(@"SEL$5DA710D3" "V"@"SEL$1" "ITS_BASE_DEVICE"@"SEL$2")
INDEX(@"SEL$5DA710D3" "ITS_BASE_DEVICE"@"SEL$2" ("ITS_BASE_DEVICE"."UNID"))
INDEX_RS_ASC(@"SEL$5DA710D3" "V"@"SEL$1" ("ITS_CAR_PASS7"."PASS_DATETIME"))
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
UNNEST(@"SEL$2")
OUTLINE_LEAF(@"SEL$5DA710D3")
FIRST_ROWS
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("PASS_DATETIME"&=TO_DATE('
17:46:04', 'syyyy-mm-dd hh24:mi:ss') AND
"PASS_DATETIME"&=TO_DATE('
18:46:06', 'syyyy-mm-dd hh24:mi:ss'))
6 - access("V"."PASS_DEVICE_UNID"="UNID")
7 - filter("DEV_BAY_UNID"='01685EFEC4DDAAEDD37393' AND "DEV_TYPE"='1' AND "DEV_DELETE_FLAG"='N' AND
"DEV_CHK_FLAG"='1')
TMD 执行计划又变了，我们也先别管执行计划为啥变了，驱动表仍然是 ITS_CAR_PASS7，现在我们来改变驱动表
select /*+ leading(ITS_BASE_DEVICE@SEL$2) */ rowid rid
from its_car_pass7 v
where 1 = 1
and pass_datetime &=
to_date(' :17:46:04', 'yyyy-mm-dd hh24:mi:ss')
and pass_datetime &=
to_date(' :18:46:06', 'yyyy-mm-dd hh24:mi:ss')
and v.pass_device_unid in
(select unid
from its_base_device
where dev_bay_unid in ('01685EFEC4DDAAEDD37393')
and dev_type = '1'
and dev_chk_flag = '1'
and dev_delete_flag = 'N')
order by v.pass_datetime asc
Execution Plan
----------------------------------------------------------
Plan hash value:
-------------------------------------------------------------------------------------------------------------------------------
| Operation
| Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
0 | SELECT STATEMENT
(4)| 00:00:01 |
SORT ORDER BY
(4)| 00:00:01 |
TABLE ACCESS BY LOCAL INDEX ROWID| ITS_CAR_PASS7
(0)| 00:00:01 |
NESTED LOOPS
(0)| 00:00:01 |
TABLE ACCESS BY INDEX ROWID
| ITS_BASE_DEVICE
(0)| 00:00:01 |
INDEX RANGE SCAN
| IDX_DEVICE_DEV_BAY_UNID
(0)| 00:00:01 |
PARTITION RANGE SINGLE
(0)| 00:00:01 |
INDEX RANGE SCAN
| IDX_VT7_PASS_DEVICE_UNID |
(0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PASS_DATETIME"&=TO_DATE('
17:46:04', 'syyyy-mm-dd hh24:mi:ss') AND
"PASS_DATETIME"&=TO_DATE('
18:46:06', 'syyyy-mm-dd hh24:mi:ss'))
4 - filter("DEV_TYPE"='1' AND "DEV_DELETE_FLAG"='N' AND "DEV_CHK_FLAG"='1')
5 - access("DEV_BAY_UNID"='01685EFEC4DDAAEDD37393')
7 - access("V"."PASS_DEVICE_UNID"="UNID")
Statistics
----------------------------------------------------------
recursive calls
db block gets
consistent gets
physical reads
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
驱动表改了之后，逻辑读从11W降低为1.8W写在前面的话：
&& 不要求每个人一定理解 联表查询(join/left join/inner join等)时的mysql运算过程；
&&&不要求每个人一定知道线上（现在或未来）哪张表数据量大，哪张表数据量小；
&&&&但把mysql客户端（如SQLyog，如HeidiSQL）放在桌面上，时不时拿出来 explain 一把，这是一种美德！
在实例讲解之前，我们先回顾一下联表查询的基础知识。
&&联表查询的基础知识&&
引子：为什么第一个查询using temporary，第二个查询不用临时表呢？
下面两个查询，它们只差了一个order by，效果却迥然不同。
第一个查询：
EXPLAIN extended
SELECT ads.id
FROM ads, city&
& &city.city_id = 8005
& &AND ads.status = 'online'
& &AND city.ads_id=ads.id
ORDER BY&ads.id&desc
执行计划为：
&&& id& select_type& table&& type&&& possible_keys&& key&&&&& key_len& ref&&&&&&&&&&&&&&&&&&&& rows& filtered& Extra&&&&&&&&&&&&&&&&&&&&&&&& &------& -----------& ------& ------& --------------& -------& -------& --------------------& ------& --------& -------------------------------&&&& 1& SIMPLE&&&&&& city&&& ref&&&& ads_id,city_id& city_id& 4&&&&&&& const&&&&&&&&&&&&&&&&&& 2838&&& 100.00& Using temporary; Using filesort&&&& 1& SIMPLE&&&&&& ads&&&& eq_ref& PRIMARY&&&&&&&& PRIMARY& 4&&&&&&& city.ads_id&&&&&& 1&&& 100.00& Using where&&&&&&&&&&&&&&&&&&&
第二个查询：
EXPLAIN extended
SELECT ads.id
FROM ads,city&
& &city.city_id =8005
& &AND ads.status = 'online'
& &AND city.ads_id=ads.id
ORDER BY&city.ads_id&desc
执行计划里没有了using temporary：
&&& id& select_type& table&& type&&& possible_keys&& key&&&&& key_len& ref&&&&&&&&&&&&&&&&&&&& rows& filtered& Extra&&&&&&&&&&&&&&&&&&&& &------& -----------& ------& ------& --------------& -------& -------& --------------------& ------& --------& ---------------------------&&&& 1& SIMPLE&&&&&& city&&& ref&&&& ads_id,city_id& city_id& 4&&&&&&& const&&&&&&&&&&&&&&&&&& 2838&&& 100.00& U Using filesort&&&& 1& SIMPLE&&&&&& ads&&& eq_ref& PRIMARY&&&&&&&& PRIMARY& 4&&&&&&& city.ads_id&&&&&& 1&&& 100.00& Using where&&&&&&&&&&&&&&&
DBA告诉我们：
& & MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。
EXPLAIN 结果中，第一行出现的表就是驱动表（Important!）
以上两个查询语句，驱动表都是 city，如上面的执行计划所示！
对驱动表可以直接排序，对非驱动表（的字段排序）需要对循环查询的合并结果（临时表）进行排序（Important!）
因此，order by ads.id desc 时，就要先 using temporary 了！
驱动表的定义
&在 2006年总结说，当进行多表连接查询时，&[驱动表]&的定义为：1）指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]；2）未指定联接条件时，行数少的表为[驱动表]（Important!）。
忠告：如果你搞不清楚该让谁做驱动表、谁 join 谁，请让 MySQL 运行时自行判断
既然&未指定联接条件时，行数少的表为[驱动表]&了，
而且你也对自己写出的复杂的 Nested Loop Join 不太有把握（如下面的实例所示），
就别指定谁 left/right join 谁了，
请交给 MySQL优化器 运行时决定吧。
如果您对自己特别有信心，可以。
小结果集驱动大结果集
&在2012年总结说，不管是你，还是 MySQL，
优化的目标是尽可能减少JOIN中Nested Loop的循环次数，
以此保证：
永远用小结果集驱动大结果集（Important!）！
&&实例讲解&&
Nested Loop Join慢查SQL语句
先了解一下 mb 表有 千万级记录，mbei 表要少得多。慢查实例如下：
SELECT mb.id, &&
FROMmb&LEFT JOIN mbei ON mb.id=mbei.mb_id&INNER JOINu ON mb.uid=u.uid &
WHERE 1=1 &
ORDER BY mbei.apply_time DESC
limit 0,10
够复杂吧。Nested Loop Join 就是这样，
以驱动表的结果集作为循环的基础数据，然后将结果集中的数据作为过滤条件一条条地到下一个表中查询数据，最后合并结果；此时还有第三个表，则将前两个表的 Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此反复。
这条语句的执行计划如下：
&&& id& select_type& table&& type&&& possible_keys&& key&&&&&&&&&&&& key_len& ref&&&&&&&&&&&&&&&&&&&& rows& Extra&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& &------& -----------& ------& ------& --------------& --------------& -------& -------------------& -------& --------------------------------------------&&&& 1& SIMPLE&&&&&& mb&&&&& index&& userid&&&&&&&&& userid&&&&&&&&& 4&&&&&&& (NULL)&&&&&&&&&&&&&& 6060455& U U Using filesort&&&& 1& SIMPLE&&&&&& mbei&&& eq_ref& mb_id& mb_id& 4&&&&&&& mb.id&&&&&&&&&&&& 1&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& &&&&& 1& SIMPLE&&&&&& u&&&&&& eq_ref& PRIMARY&&&&&&&& PRIMARY&&&&&&&& 4&&&&&&& mb.uid&&&&&&& 1& Using index&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
由于动用了&LEFT JOIN&，所以攻城狮已经指定了驱动表，虽然这张驱动表的结果集记录数达到百万级！
如何优化？
优化第一步：LEFT JOIN改为JOIN
干嘛要 left join 啊？直接 join！
SELECT mb.id&&
FROM mb&JOIN mbei ON mb.id=mbei.mb_id&INNER JOINu ON mb.uid=u.uid &
WHERE 1=1 &
ORDER BY mbei.apply_time DESC
limit 0,10
立竿见影，驱动表立刻变为小表 mbei 了， Using temporary 消失了，影响行数少多了：
&&& id& select_type& table&& type&&& possible_keys&& key&&&&& key_len& ref&&&&&&&&&&&&&&&&&&&&&&&&&&&& rows& Extra&&&&&&& &------& -----------& ------& ------& --------------& -------& -------& ----------------------------& ------& --------------&&&& 1& SIMPLE&&&&&& mbei&&& ALL&&&& mb_id& (NULL)&& (NULL)&& (NULL)&&&&&&&&&&&&&&&&&&&&&&&& 13383& Using filesort&&&& 1& SIMPLE&&&&&& mb&&&&& eq_ref& PRIMARY,userid& PRIMARY& 4&&&&&&& mbei.mb_id&&&&&& 1&&&&&&&&&&&&&& &&&&& 1& SIMPLE&&&&&& u&&&&&& eq_ref& PRIMARY&&&&&&&& PRIMARY& 4&&&&&&& mb.uid&&&&&&&&&&&&&&& 1& Using index&&
优化第一步之分支1：根据驱动表的字段排序，好吗？
left join不变。干嘛要根据非驱动表的字段排序呢？我们前面说过&对驱动表可以直接排序，对非驱动表（的字段排序）需要对循环查询的合并结果（临时表）进行排序！&的。
SELECT mb.id&&
FROM mb LEFT JOIN mbei ON mb.id=mbei.mb_id&INNER JOINu ON mb.uid=u.uid &
WHERE 1=1 &
ORDER BY&mb.id DESC
limit 0,10
也满足业务场景，做到了rows最小：
&&& id& select_type& table&& type&&& possible_keys&& key&&&&&&&&&&&& key_len& ref&&&&&&&&&&&&&&&&&&& rows& Extra&&&& &------& -----------& ------& ------& --------------& --------------& -------& -------------------& ------& -----------&&&& 1& SIMPLE&&&&&& mb&&&&& index&& userid&&&&&&&&& PRIMARY&&&&&&&& 4&&&&&&& (NULL)&&&&&&&&&&&&&&&&&& 10&&&&&&&&&&& &&&&& 1& SIMPLE&&&&&& mbei&&& eq_ref& mb_id& mb_id& 4&&&&&&& mb.id&&&&&&&&&&& 1& Using index&&&& 1& SIMPLE&&&&&& u&&&&&& eq_ref& PRIMARY&&&&&&&& PRIMARY&&&&&&&& 4&&&&&&& mb.uid&&&&&& 1& Using index
优化第二步：去除所有JOIN，让MySQL自行决定！
写这么多密密麻麻的 left join/inner join 很开心吗？
SELECT mb.id&&
FROM mb,mbei,u& &WHERE && &mb.id=mbei.mb_id&& &and mb.uid=u.user_idorder by mbei.apply_time desclimit 0,10
立竿见影，驱动表一样是小表 mbei：
&&& id& select_type& table&& type&&& possible_keys&& key&&&&& key_len& ref&&&&&&&&&&&&&&&&&&&&&&&&&&&& rows& Extra&&&&&&& &------& -----------& ------& ------& --------------& -------& -------& ----------------------------& ------& --------------&&&& 1& SIMPLE&&&&&& mbei&&& ALL&&&& mb_id& (NULL)&& (NULL)&& (NULL)&&&&&&&&&&&&&&&&&&&&&&&& 13388& Using filesort&&&& 1& SIMPLE&&&&&& mb&&&&& eq_ref& PRIMARY,userid& PRIMARY& 4&&&&&&& mbei.mb_id&&&&&& 1&&&&&&&&&&&&&& &&&&& 1& SIMPLE&&&&&& u&&&&&& eq_ref& PRIMARY&&&&&&&& PRIMARY& 4&&&&&&& mb.uid&&&&&&&&&&&&&&& 1& Using index&&
最后的总结：
强调再强调：
不要过于相信你的运气！
不要相信你的开发环境里SQL的执行速度！
请拿起 explain 武器，
如果你看到以下现象，请优化：
出现了Using temporary；
rows过多，或者几乎是全表的记录数；
key 是 (NULL)；
possible_keys 出现过多（待选）索引。
记住，explain 是一种美德！
参考资源：
1）wwh999，2006，&；
2）de.cel，2012，&；
3）火丁，2013，；
赠图一枚：
阅读(...) 评论()