多表查询

本主题显示多表查询的样本 SET EXPLAIN 输出。

图: 多表查询的部分 SET EXPLAIN 输出

QUERY:
------
SELECT C.customer_num, O.order_num, SUM (I.total_price)
FROM customer C, orders O, items I
WHERE C.customer_num = O.customer_num
AND O.order_num = I.order_num
GROUP BY C.customer_num, O.order_num

Estimated Cost: 78
Estimated # of Rows Returned: 1
Temporary Files Required For: Group By

  1) virginia.o: SEQUENTIAL SCAN

  2) virginia.c: INDEX PATH

    (1) Index Keys: customer_num   (Key-Only)  (Serial, fragments: ALL)
        Lower Index Filter:
          virginia.c.customer_num = virginia.o.customer_num
NESTED LOOP JOIN 
  3) virginia.i: INDEX PATH

    (1) Index Keys: order_num   (Serial, fragments: ALL)
        Lower Index Filter: virginia.o.order_num = virginia.i.order_num
NESTED LOOP JOIN

SET EXPLAIN 输出列出数据库服务器访问表的顺序和读取每个表的存取计划。图 1 中的计划指示数据库服务器将执行以下操作：

数据库服务器将首先读取 orders 表。
因为 orders 表上没有过滤器，所以数据库服务器必须读取所有的行。按物理顺序读取表是成本最低的方法。
对于 orders 的每一行，数据库服务器将在 customer 表中搜索匹配的行。
搜索使用 customer_num 的索引。标志仅键意味着对于 customer 表只需要读取索引，因为只有 c.customer_num 列用在连接和输出中且该列是索引键。
对于 orders 中具有一个匹配 customer_num 的每一行，数据库服务器将使用 order_num 的索引在 items 表中搜索匹配行。