用子查询来代替非主键连接查询

现在要查询pname是 xxg的people和team信息

select * from t_team t,t_people p where t.tname=p.team_name
 and p.pname='xxg' limit 1;

SELECT * FROM t_team t INNER JOIN t_people p 
ON t.tname=p.team_name WHERE p.pname='xxg' LIMIT 1; 

执行下可以查询出结果，但如果数据量大的情况下，效率很低，执行很慢。
对于这种连接查询，用子查询来替代，查询结果相同，但效率会更高

select * from(select * from t_people where pname='xxg' limit 1) p,
team_t t where t.tname=p.team_name limit 1;

子查询中过滤了大量的数据(仅保留一条)，再将结果来连接查询，效率会大大提高。

实例检测3条SQL语句的查询效率
准备1万条team数据，准备100万条people数据

BEGIN 
DECLARE i INT; 
START TRANSACTION; 
SET i=0; 
WHILE i<10000 DO 
INSERT INTO t_team VALUES(i+1,CONCAT('team',i+1)); 
SET i=i+1; 
END WHILE; 
SET i=0; 
WHILE i<1000000 DO 
INSERT INTO t_people VALUES(i+1,CONCAT('people',i+1),CONCAT('team',i%10000+1)); 
SET i=i+1; 
END WHILE; 
COMMIT; 
END 

连接查询

SELECT * FROM t_team t,t_people p WHERE t.tname=p.team_name
AND p.pname='people20000' LIMIT 1; 

Time:12.594 s

连接查询

SELECT * FROM t_team t INNER JOIN t_peoplep ON t.tname=p.team_name
 WHERE p.pname='people20000' LIMIT 1; 

Time:12.360 s

子查询

SELECT * FROM (SELECT * FROM t_people WHEREpname='people20000'
 LIMIT 1) p, t_team t WHERE t.tname=p.team_name LIMIT 1; 

Time:0.016 s

参考博客：http://www.jb51.net/article/35772.htm