DML:
mysql> show create table orders\G
*************************** 1. row ***************************
Table: orders
Create Table: CREATE TABLE `orders` (
`o_orderkey` int(11) NOT NULL,
`o_custkey` int(11) NOT NULL,
`o_orderstatus` char(1) NOT NULL,
`o_totalprice` decimal(15,2) NOT NULL,
`o_orderdate` date NOT NULL,
`o_orderpriority` char(15) NOT NULL,
`o_clerk` char(15) NOT NULL,
`o_shippriority` int(11) NOT NULL,
`o_comment` varchar(79) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show create table lineitem\G
*************************** 1. row ***************************
Table: lineitem
Create Table: CREATE TABLE `lineitem` (
`l_orderkey` int(11) NOT NULL,
`l_partkey` int(11) NOT NULL,
`l_suppkey` int(11) NOT NULL,
`l_linenumber` int(11) NOT NULL,
`l_quantity` decimal(15,2) NOT NULL,
`l_extendedprice` decimal(15,2) NOT NULL,
`l_discount` decimal(15,2) NOT NULL,
`l_tax` decimal(15,2) NOT NULL,
`l_returnflag` char(1) NOT NULL,
`l_linestatus` char(1) NOT NULL,
`l_shipdate` date NOT NULL,
`l_commitdate` date NOT NULL,
`l_receiptdate` date NOT NULL,
`l_shipinstruct` char(25) NOT NULL,
`l_shipmode` char(10) NOT NULL,
`l_comment` varchar(44) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
select
o_orderpriority
from
orders
where
o_orderdate >= date '1993-07-01'
and o_orderdate < date '1993-07-01' + interval '3' month
and exists (
select
*
from
lineitem
where
l_orderkey = o_orderkey
and l_commitdate < l_receiptdate
)
limit 10;
mysql> explain
-> select
-> o_orderpriority,
-> count(*) as order_count
-> from
-> orders
-> where
-> o_orderdate >= date '1993-07-01'
-> and o_orderdate < date '1993-07-01' + interval '3' month
-> and exists (
-> select
-> *
-> from
-> lineitem
-> where
-> l_orderkey = o_orderkey
-> and l_commitdate < l_receiptdate
-> )
-> group by
-> o_orderpriority
-> order by
-> o_orderpriority
-> limit 10\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: orders
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 148632
filtered: 11.11
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: lineitem
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 596568
filtered: 3.33
Extra: Using where
2 rows in set, 2 warnings (0.00 sec)
逻辑定位:
find_field_in_table_ref
- 该函数处的item的 name=0x7fbb80002ae8 "o_orderkey", 该属性是外表orders中的
- 但是查看table_list发现是lineitem表
(gdb) p table_list->alias
$24 = 0x7fbb80002750 "lineitem"
mysql/innodb对于exists子查询的查询计划
一. 精简后的原始SQL
select
o_orderpriority
from
orders
where
o_orderdate >= date '1993-07-01'
and o_orderdate < date '1993-07-01' + interval '3' month
and exists (
select
*
from
lineitem
where
l_orderkey = o_orderkey
and l_commitdate < l_receiptdate
)
limit 10;
二. 在mysql/innodb中等价于
对orders表做了条件上推
select
o_orderpriority
from
orders
where
o_orderdate >= date '1993-07-01'
and o_orderdate < date '1993-07-01' + interval '3' month
and o_orderkey = (
select
l_orderkey
from
lineitem
where
l_commitdate < l_receiptdate
)
limit 10;
三. 用关系代数表达式的规则表示，则是
select
* as T_orders
from
orders
where
o_orderdate >= date '1993-07-01'
and o_orderdate < date '1993-07-01' + interval '3' month
limit 10;
------------------------------------------------
select
* as T_lineitem
from
lineitem
where
l_commitdate < l_receiptdate
------------------------------------------------
select
*
from T_orders
inner join T_lineitem
on T_lineitem.l_orderkey = T_orders.o_orderkey;
四. 对关系代数做优化, 可以将量表关联条件做下推到orders表
参考: 数据库系统实现.5.3.3
