驱动表与被驱动表
驱动表和被驱动表有什么差异
基于索引的join
join查询中如何选择驱动表
不使用join，执行效率是否会更高
join 是进行两个或多个数据表进行关联查询的过程中，经常使用的一种查询手段。提到join，你一定会想到"笛卡尔积",当数据量很大的时候，"笛卡尔积"运算量会成倍的增加，在我们的印象中，join是一种运算效率不高的查询语句。
除了定性的判断join慢之外，你能定量的判断join的执行效率吗？
经过下面对join执行效率定量分析后，可能你会改变对join的认识，不在想当然的认为join就一定很慢了。
驱动表与被驱动表进行join操作的两个表，分别称为驱动表和被驱动表，到底哪个是驱动表，哪个是被驱动表是不确定的，这个是mysql优化器来决定，和sql语句中两个表的位置没有关系。
如果我们想要强制指定两个表的对应关系，可以将sql中的join替换成 straight_join，替换后，在straight_join前的表称为驱动表，在straight_join后的表，称为被驱动表。
驱动表和被驱动表有什么差异在join语句执行的过程中，驱动表和被驱动表所执行的操作是不同的。同是驱动表或被驱动表，在不同的join类型中，所执行操作也是不同的。
下面我们分析一下，不同join类型下，驱动表和被驱动表所做的操作的具体内容。
为了方便下面问题的讨论，我们建立如下的表结构:
create table 'table1' (
'id' int(11) NOT NULL,
'a' int(11) DEFAULT NULL,
'b' int(11) DEFAULT NULL,
PRIMARY KEY ('id'),
KEY 'a' ('a')
) engine = Innodb;
insert into table1 values(1,1,1)
insert into table1 values(2,2,2)
...
insert into table1 values(1000,1000,1000) // 也可以使用存储过程来实现大批量数据的插入
create table table2 like table1;
insert into t2 (select * from t2 where id <= 100)
建立表结构完全相同的两个表table1和table2，共有三个字段：id为主键字段，索引字段a和普通字段b。向table1中插入了1000行自增的数据，将table1中的前100行数据插入到table2中。
基于索引的join如果在join过程中，使用到了索引，这种join又被称为 Index Nested-Loop Join(NLJ)。
如下面这个语句：
select * from table2 straight_join table1 on table2.a = table1.a;
为了便于明确驱动表和被驱动表，我们使用 straight_join 代替 join，这样就可以明确 table2 为驱动表，table1为被驱动表。
因为在被驱动表 table1上有索引a字段，在join的时候，会使用到这个索引，具体可以通过查看上面sql的执行计划：
explain select * from table2 straight_join table1 on table2.a = table1.a;
执行计划图：
该条语句的执行过程如下：
1.从table2中，读入一行R。
2.从该数据行R中取出字段a，到table1中去查找满足a=$R.a的数据行，因为在table1表中，字段a上有索引，所以这个查询效率很高。
3.将从2中查询返回的结果和R，构成结果集中一行。
4.重复步骤1到3，直到遍历完table2中的所有数据行。
这个过程遍历 table2中的所有数据行，取出每一行中的a值，然后去table1中查找满足条件的数据行，将table1中满足条件的数据和table2中遍历到的数据，组合成结果集中的数据。
在整个过程中：
驱动表table2所做的操作：被逐行遍历，也就是进行全表扫描，该过程要扫描100行数据。
被驱动表table1所做的操作：基于索引字段进行数据查询，因为table1中，没有a值相同的两行数据，所以每次搜索过程只会扫描一行数据。因为table2中有100行数，所以在table1中要执行100次搜索过程，也就是在table1中，也要扫描100行数据。
所以这个join语句整个执行下来 要扫描200行数。
如果让 table1作为驱动表，table2作为被驱动表的话，执行语句如下：
select * from table1 straight_join table2 on table2.a = table1.a;
和前者有和区别呢？
根据上面的分析，驱动表需要进行全表扫描，被驱动表基于索引字段进行数据搜索。
table1作为驱动表时，sql语句执行计划如下图：
当 table1作为驱动表，table2作为被驱动表时：
驱动表table1需要被扫描 1000行。被驱动表table2需要进行 1000次搜索，但是最终只能成功搜索到100行数据。总的所有数据行数1100行。
这样对比下来，table2作为驱动表，table1作为被驱动表执行的效率，要比table1作为驱动表，table2作为被驱动表的执行效率要高一些。
join查询中如何选择驱动表除了分析扫描行数，我们可以对NLJ执行过程中，总的时间复杂度计算一下，看一下哪个因素对join查询效率影响比较大，进而来对我们选择驱动表提供参考。
我们假设驱动表中的数据行数是N，被驱动表中的数据行数为M，因为在被驱动表中查询一行数据，要先搜索普通索引a，然后再回表到主键索引，才能获取完整的一行数据。
表中数据行数为M，通过主键索引树和普通索引树查找一行数据的时间复杂度都是log2M，所以查找一行数据的时间复杂度为2*log2M。驱动表中有N行数，因此驱动表要扫描N行，驱动表中的每行数据都要到被驱动表中进行一次搜索。所以当驱动表数据行数为N，被驱动表数据行数为M的情况下，一次基于索引的join查询的近似时间复杂度为 O = N + N*2*log2M。
整个join语句的时间复杂度，与驱动表中行数的关系为： O = (1+2*log2M)*N ，是线性关系。和被驱动表中行数的关系为：O = N*2*log2M +N 是对数函数关系。
基于数学知识，我们知道 "驱动表中行数"对整个sql执行时间复杂度的影响 要比"被驱动表中行数" 影响要大。因此在 基于索引的join(NLJ)中，我们应该尽量使用 数据量小的表作为驱动表。这样可以减少扫描的行数，以及整体的时间复杂度。
不使用join，执行效率是否会更高如果不使用join的情况下，要想实现下图类似功能，
select * from table2 join table1 on table2.a = table1.a;
我们需要把 table2中的数据全部取出来，
select * from table2; // 扫描100行数据
共100行数据，然后循环遍历这100行数据，取出每行数据中的a值$R.a，去执行
select * from table1 where a = $R.a // 扫描1行数据
把该条语句返回的结果 和R拼接在一起，构成结果集中的一行数据。
这种不使用join的方式，也会扫描200行数据，只不过要执行的sql语句会有101条，而使用join语句的情况下，却只有1条。相比使用join，不使用join，会增加100次与mysql的交互过程，整体的执行效率相比使用join反而更低。
由此可见，在被驱动表上可以使用到索引的情况下，join操作的效率还是比较高的。读到这里，你是否会改变对join的认识呢？还会想当然的认为join执行效率很低吗？
可能你会问，如果join的过程中，被驱动表上没有索引呢？的确，当被驱动表上没有索引的情况下，join的执行效率会变慢很多，显然，"join执行的效率低"这个认知，不是空穴来风，但是变慢的原因是什么呢？感兴趣的老铁可以看一下，本篇文章。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持易知道(ezd.cc)。
