MySQL 执行计划里的rows

来源：互联网发布：matdem软件下载编辑：程序博客网时间：2024/05/21 10:43
<pre name="code" class="html">SQL> alter session set statistics_level=all;Session altered.SQL> select e.ename,e.job,d.dname from emp e,dept d  where e.deptno=d.deptno and e.sal<2000;          -------------------省略输出结果----------------------8 rows selected.SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------SQL_ID  70xt266nm9y32, child number 0-------------------------------------select e.ename,e.job,d.dname from emp e,dept d  where e.deptno=d.deptno and e.sal<2000Plan hash value: 351108634--------------------------------------------------------------------------------------------------| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |--------------------------------------------------------------------------------------------------|   1 |  NESTED LOOPS                |         |      1 |      4 |      8 |00:00:00.01 |      18 ||*  2 |   TABLE ACCESS FULL          | EMP     |      1 |      4 |      8 |00:00:00.01 |       8 ||   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      8 |      1 |      8 |00:00:00.01 |      10 ||*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |      8 |      1 |      8 |00:00:00.01 |       2 |--------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("E"."SAL"<2000)   4 - access("E"."DEPTNO"="D"."DEPTNO")21 rows selected.NESTED LOOPS表示嵌套循环，Starts表示某个操作执行的次数，那么这里嵌套循环操作一共执行了1次，EMP表离NESTED LOOPS最近，表示EMP表作为驱动表，它执行了1次(全表扫描)返回了8条记录。Oracle这时候就会从这8条记录中读取一行，然后去匹配内部表，因为驱动表返回了8条记录，所以内部循环会执行8次。技巧1：查看真实基数比如下面这个SQLSQL> explain plan for SELECT UOM.PROD_SKID,  2         MAX (UOM.RELTV_CURR_QTY) RELTV_CURR_QTY,  3         MAX (UOM.STAT_CURR_VAL) STAT_CURR_VAL,  4         MAX (UOM.BAR_CURR_CODE) BAR_CURR_CODE  5  --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit begin  6  FROM OPT_REF_UOM_TEMP_SDIM UOM,  7       REF_PROD_DIM PROD  8  WHERE UOM.RELTV_CURR_QTY = 1  9        AND PROD.CURR_IND = 'Y' 10        AND PROD.PROD_END_DATE = TO_DATE ('31-12-9999', 'dd-mm-yyyy') 11        AND PROD.PROD_SKID = UOM.PROD_SKID 12        AND PROD.BUOM_CURR_SKID = UOM.UOM_SKID 13  --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit end 14  GROUP BY UOM.PROD_SKID;Explained.SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------------------Plan hash value: 3215660883-------------------------------------------------------------------------------------------------------| Id  | Operation                     | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT              |                       |    78 |  4212 | 15507   (1)| 00:01:47 ||   1 |  HASH GROUP BY                |                       |    78 |  4212 | 15507   (1)| 00:01:47 ||   2 |   NESTED LOOPS                |                       |       |       |            |          ||   3 |    NESTED LOOPS               |                       |  3034 |   159K| 15506   (1)| 00:01:47 ||*  4 |     TABLE ACCESS FULL         | OPT_REF_UOM_TEMP_SDIM |  2967 |   101K|   650  (14)| 00:00:05 ||*  5 |     INDEX RANGE SCAN          | PROD_DIM_PK           |     3 |       |     2   (0)| 00:00:01 ||*  6 |    TABLE ACCESS BY INDEX ROWID| PROD_DIM              |     1 |    19 |     5   (0)| 00:00:01 |-------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   4 - filter("UOM"."RELTV_CURR_QTY"=1)   5 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID")   6 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('              9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y' AND              "PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")22 rows selected.请看ID=4这步，CBO估算它只返回2967行数据。ID=4这步前面有*，表示它有谓词过滤。4 - filter("UOM"."RELTV_CURR_QTY"=1)UOM是表OPT_REF_UOM_TEMP_SDIM 的别名那么我们手工计算它应该返回的真实基数：SQL> select count(*) from OPT_REF_UOM_TEMP_SDIM where "RELTV_CURR_QTY"=1;  COUNT(*)----------    946432CBO估算它只返回2967条数据，但是它应该返回94W条数据，这里相差太大了，说明执行计划走错了，它后面的索引会被扫描94W次。Oracle里的Rows 表示CBO估算它返回的行数Rows(E-Rows)     预估操作返回的记录条数在MySQL里呢？mysql> explain select t1.* from t2 ,t1 where t2.id=t1.id;+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL                                               ||  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using join buffer (Block Nested Loop) |+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+2 rows in set (0.00 sec)rowsrowsEstimate of rows to be examined   评估要检查的行ows表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
0 0