联接查询-驱动表
1.概念
驱动表,即需要从驱动表中拿出来每条记录,去与被驱动表的所有记录进行匹配探测。驱动表,作为外层循环
被驱动表,即里层循环,由于需要不断的拿外层循环传进来的每条记录去匹配,所以是随机读取的,走索引查询会大大提高速度。
2.判断驱动表与被驱动表
最有效确定驱动表的方式-EXPLAIN
EXPLAIN select * from dept_emp as d inner join salaries as s on s.to_date=d.to_date;
+----+-------------+-------+------------+------+---------------+-------------+---------+----------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+----------------+------+----------+-------+
| 1 | SIMPLE | d | NULL | ALL | NULL | NULL | NULL | NULL | 110 | 100.00 | NULL |
| 1 | SIMPLE | s | NULL | ref | to_date_ids | to_date_ids | 3 | test.d.to_date | 33 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------------+---------+----------------+------+----------+-------+
使用 EXPLAIN 分析后,在上面的表就是驱动表,下面的表就是被驱动表</