mysql sql性能测试

1.mysql 逗号会优化为inner join,没有区别
EXPLAIN select test.id id,test.Name name,test_ordervy.Id id2,test_ordervy.Name name2 from test,test_ordervy where test.id=test_ordervy.Id;

EXPLAIN select test.id id,test.Name name,test_ordervy.Id id2,test_ordervy.Name name2 from test inner join test_ordervy on test.id=test_ordervy.Id;


2.inner join 时条件写到on 和where都行
EXPLAIN select test.id id,test.Name name,test_ordervy.Id id2,test_ordervy.Name name2 from test inner join test_ordervy on test.id=test_ordervy.Id
and test.Id=2;

EXPLAIN select test.id id,test.Name name,test_ordervy.Id id2,test_ordervy.Name name2 from test inner join test_ordervy on test.id=test_ordervy.Id
where test.Id=2;

3. left join right join ，条件需要写到where 后，写到on有性能问题和结果集不合符逾期问题

EXPLAIN select test.id id,test.Name name,test_ordervy.Id id2,test_ordervy.Name name2 from test left join test_ordervy on test.id=test_ordervy.Id
and test.id=2;

EXPLAIN select test.id id,test.Name name,test_ordervy.Id id2,test_ordervy.Name name2 from test left join test_ordervy on test.id=test_ordervy.Id
where test.id=2;

EXPLAIN select test.id id,test.Name name,test_ordervy.Id id2,test_ordervy.Name name2 from test right join test_ordervy on test.id=test_ordervy.Id
and test.id=2;

EXPLAIN select test.id id,test.Name name,test_ordervy.Id id2,test_ordervy.Name name2 from test right join test_ordervy on test.id=test_ordervy.Id
where test.id=2;

4.尽量以小结果集为驱动表，left join 坐表为驱动表，right join 右表为驱动表