mysql rows_MySQL 执行计划里的rows

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<2000

Plan 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：查看真实基数

比如下面这个SQL

SQL> 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(*)

----------

946432

CBO估算它只返回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根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数