我做了个试验,比如有如下语句 select * from table1 t1 inner join table2 t2 on t1.XX=t2.XX inner join table3 t3 on t2.XX =t3.XX ,
看他的执行计划,XX这个列是有索引的,但他的第一步是对table3进行全表扫描,然后再用索引去扫描table2, 也就是说,inner join的执行顺序是从后往前,先把table3的数据提取出来,再通过条件去找table2,所以在写SQL语句的时候,最好把能排除大量数据的条件放到最后
将 上句改为:select * from table1 t1 inner join table2 t2 on t1.XX=t2.XX inner join table3 t3 on t2.XX =t3.XX where t3.XX between A AND B,这样效率就上去了,不用在每个inner join后面都加条件,另外 每次inner join 一个表,就会先对这个表进行全表扫描,然后他们再联表,如果多个表,或表的行数很多,他们的效率很低,消耗几乎是每个表的行数相乘,所以有条件尽量在inner join的时候就用,别用在最后,
将上句改为:select * from (select * from table1 where XX between A AND B ) t1 inner join (select * from table2 where XX between A AND B)t2 on t1.XX=t2.XX inner join(select * from table3 where XX between A AND B)t3 on t2.XX =t3.XX 这样的执行效率会高很多