MySQL join原理与优化

内连接:

mysql中内连接写法比较多,下面的写法作用都是一样的:

select * from a,b where xxx;
select * from a join b on xxx where xxx;
select * from a cross join b on xxx where xxx;
select * from a inner join b on xxx where xxx;

在内连接中,on与where的作用是一样的都是起到过滤作用,a与b哪个是驱动表是由优化器去选择的.

外连接:

select * from a left join b on  xxx where xxx;
select * from a right join b on  xxx where xxx;

外连接中,on与where 作用不一样,左外选取左边为驱动表,右外选取右边作为驱动表

原理

两个表join1与join2的表结构如下

CREATE TABLE `join1` (
  `id` int(11) NOT NULL,
  `num1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `join2` (
  `id` int(11) NOT NULL,
  `num2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

sql1:
select * from join1 a join join2 b on a.id=b.id
对于sql1来说,是通过主键(或者索引也是一样的)进行连接查询的,假设选取join1表作为驱动表,那么就会先查询驱动表中的一条满足条件的数据,然后去join2中进行匹配,也就是有多少满足条件的join1(驱动表)的记录,就需要查询多少次join2表,虽然查询join2表次数很多,但是每次查询join2表都是通过索引而不是通过全表扫描的方式,所以速度很快.

sql2:
select * from join1 a join join2 b on a.num=b.num
对于sql2来说,没有索引可以优化执行,所以每次查询完join1表中的数据就去查询一次join2的话,join2进行的是全表扫描,如果数据量很大, 那么速度就感人了,所以设计出了join buffer概念.
join buffer只有当无法使用索引进行连接查询时才有效,此时不会每扫描到一条join1表中的数据就去全表扫描一次join2表,而是先将join1表中的满足条件的数据添加到join buffer中,然后再全表扫描join2表,与join buffer中join1表中的数据进行匹配,省略了大量的磁盘io,这join buffer默认大小是256k

show variables like 'join_buffer_size';=>262144

join buffer每个线程都有一份.
所以连接查询中如果可以使用索引,就添加索引提高连接速度,如果不能创建索引,就增大join_buffer_size提高速度.