DROPPROCEDURE IF EXISTS idata; DELIMITER ;; CREATEPROCEDURE idata() BEGIN DECLARE i INT; SET i=1; WHILE (i <=1000) DO INSERTINTO t2 VALUES (i,i,i); SET i=i+1; END WHILE; END;; DELIMITER ; CALL idata();
CREATETABLE t1 LIKE t2; INSERTINTO t1 (SELECT*FROM t2 WHERE id<=100);
Index Nested-Loop Join
1 2 3 4 5 6 7 8 9 10 11
-- 使用JOIN,优化器可能会选择t1或t2作为驱动表 -- 使用STRAIGHT_JOIN,使用固定的连接关系,t1为驱动表,t2为被驱动表 SELECT*FROM t1 STRAIGHT_JOIN t2 ON (t1.a=t2.a);
mysql> EXPLAIN SELECT*FROM t1 STRAIGHT_JOIN t2 ON (t1.a=t2.a); +----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+ | id | select_type |table| partitions | type | possible_keys | key | key_len |ref|rows| filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+ |1| SIMPLE | t1 |NULL|ALL| a |NULL|NULL|NULL|100|100.00|Usingwhere| |1| SIMPLE | t2 |NULL|ref| a | a |5| test.t1.a |1|100.00|NULL| +----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+
-- Q1 mysql>SELECT*FROM a LEFTJOIN b ON (a.f1=b.f1) AND (a.f2=b.f2); +------+------+------+------+ | f1 | f2 | f1 | f2 | +------+------+------+------+ |3|3|3|3| |4|4|4|4| |5|5|5|5| |6|6|6|6| |1|1|NULL|NULL| |2|2|NULL|NULL| +------+------+------+------+
-- Q2 mysql>SELECT*FROM a LEFTJOIN b ON (a.f1=b.f1) WHERE (a.f2=b.f2); +------+------+------+------+ | f1 | f2 | f1 | f2 | +------+------+------+------+ |3|3|3|3| |4|4|4|4| |5|5|5|5| |6|6|6|6| +------+------+------+------+
Q1
1 2 3 4 5 6 7
mysql> EXPLAIN SELECT*FROM a LEFTJOIN b ON (a.f1=b.f1) AND (a.f2=b.f2); +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | id | select_type |table| partitions | type | possible_keys | key | key_len |ref|rows| filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ |1| SIMPLE | a |NULL|ALL|NULL|NULL|NULL|NULL|6|100.00|NULL| |1| SIMPLE | b |NULL|ALL|NULL|NULL|NULL|NULL|6|100.00|Usingwhere; Usingjoin buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
驱动表是表a,被驱动表是表b,与使用STRAIGHT_JOIN的效果一致
由于表b的字段f1上没有索引,所以使用的是BNL算法
把表a的内容读入join_buffer中
因为是SELECT *,所以字段f1和字段f2都被放入到join_buffer中
顺序扫描表b,对于每一行数据,判断JOIN条件(a.f1=b.f1 and a.f2=b.f2)是否满足
如果满足条件,作为结果集的一行返回
如果语句中有WHERE字句,先判断WHERE部分满足条件后,再返回
表b扫描完成后,对于没有被匹配的表a的行,把剩余字段补上NULL,再放入到结果集
Q2
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
mysql> EXPLAIN SELECT*FROM a LEFTJOIN b ON (a.f1=b.f1) WHERE (a.f2=b.f2); +----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+ | id | select_type |table| partitions | type | possible_keys | key | key_len |ref|rows| filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+ |1| SIMPLE | b |NULL|ALL|NULL|NULL|NULL|NULL|6|100.00|Usingwhere| |1| SIMPLE | a |NULL|ref| f1 | f1 |5| test.b.f1 |1|16.67|Usingwhere| +----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+ 2rowsinset, 1 warning (0.00 sec)
mysql>SHOW WARNINGS\G; ***************************1.row*************************** Level: Note Code: 1003 Message: /* select#1 */select `test`.`a`.`f1` AS `f1`,`test`.`a`.`f2` AS `f2`,`test`.`b`.`f1` AS `f1`,`test`.`b`.`f2` AS `f2` from `test`.`a` join `test`.`b` where ((`test`.`a`.`f1` = `test`.`b`.`f1`) and (`test`.`a`.`f2` = `test`.`b`.`f2`))
-- Q3 mysql> EXPLAIN SELECT*FROM a JOIN b ON (a.f1=b.f1) AND (a.f2=b.f2); +----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+ | id | select_type |table| partitions | type | possible_keys | key | key_len |ref|rows| filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+ |1| SIMPLE | b |NULL|ALL|NULL|NULL|NULL|NULL|6|100.00|Usingwhere| |1| SIMPLE | a |NULL|ref| f1 | f1 |5| test.b.f1 |1|16.67|Usingwhere| +----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+
mysql>SHOW WARNINGS\G; ***************************1.row*************************** Level: Note Code: 1003 Message: /* select#1 */select `test`.`a`.`f1` AS `f1`,`test`.`a`.`f2` AS `f2`,`test`.`b`.`f1` AS `f1`,`test`.`b`.`f2` AS `f2` from `test`.`a` join `test`.`b` where ((`test`.`a`.`f2` = `test`.`b`.`f2`) and (`test`.`a`.`f1` = `test`.`b`.`f1`))
-- Q4 mysql> EXPLAIN SELECT*FROM a JOIN b ON (a.f1=b.f1) WHERE (a.f2=b.f2); +----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+ | id | select_type |table| partitions | type | possible_keys | key | key_len |ref|rows| filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+ |1| SIMPLE | b |NULL|ALL|NULL|NULL|NULL|NULL|6|100.00|Usingwhere| |1| SIMPLE | a |NULL|ref| f1 | f1 |5| test.b.f1 |1|16.67|Usingwhere| +----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+ 2rowsinset, 1 warning (0.00 sec)
mysql>SHOW WARNINGS\G; ***************************1.row*************************** Level: Note Code: 1003 Message: /* select#1 */select `test`.`a`.`f1` AS `f1`,`test`.`a`.`f2` AS `f2`,`test`.`b`.`f1` AS `f1`,`test`.`b`.`f2` AS `f2` from `test`.`a` join `test`.`b` where ((`test`.`a`.`f1` = `test`.`b`.`f1`) and (`test`.`a`.`f2` = `test`.`b`.`f2`))
Q3和Q4都被改写成
SELECT * FROM a JOIN b WHERE (a.f1=b.f1) AND (a.f2=b.f2)