表连接优化

两表连接优化

explain select * from class left join book on class.card=book.card;

索引添加应该添加book表,如果添加class表效果没book好,
这是由左连接特性决定的.
left join条件用于确定如何从右表搜索,左边一定有,索引反着建

三表连接优化

explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card;

后2行的type都是ref而且总rows优化很好,效果不错.
因此索引最好设置在需要经常查询的字段中.

尽可能减少join语句中的NestedLoop循环总次数,用小结果集驱动大结果集

user表10000条数据，class表20条数据

select * from user u left join class c u.userid=c.userid

这样则需要用user表循环10000次才能查询出来，而如果用class表驱动user表则只需要循环20次就能查询出来

select * from class c left join user u c.userid=u.userid

优先优化NestedLoop的内层循环
保证join语句中被驱动表上join字段已经被索引
当无法保证被驱动表的join字段被索引而且内存资源充足的前提下,不要吝啬joinbuffer的设置

小表驱动大表

原理

select * from A where id in (select id from B)
等价于
for select id from B
for select * from A where A.id = B.id

当B表的数据集必须小于A表的数据集时,用in优于exists

select * from A where exists (select 1 from B where B.id = A.id)
等价于
for select * from A
for select * from B where B.id = A.id

当A表的数据集小于B表的数据集时,用exists优于in
注意: A表与B表的ID字段应建立索引