MySQL查询优化:用子查询代替非主键连接查询

一对多的两张表，一般是一张表的外键关联到另一个表的主键。但也有不一般的情况，也就是两个表并非通过其中一个表的主键关联。

例如：

[sql]  view plain copy

1. create table t_team  
2. (  
3. tid int primary key,  
4. tname varchar(100)  
5. );  
6.   
7. create table t_people  
8. (  
9. pid int primary key,  
10. pname varchar(100),  
11. team_name varchar(100)  
12. );  

team表和people表是一对多的关系，team的tname是唯一的，people的pname也是唯一的，people表中外键team_name和team表的tname关联，并不是和主键id关联。

 

（PS：先不说这样的设计合不合理，但如果真的摊上这事儿…..很多表的设计是每个表有一个id和uuid，id作为主键，uuid作关联，和上面情况类似）

 

现在要查询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, t_team t WHERE t.tname=p.team_name LIMIT 1;

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

 

(PS:另外，使用LIMIT 1也可以提高查询效率，详细：http://blog.csdn.net/xiao__gui/article/details/8726272 )

本人通过3条SQL测试两种查询方式的效率：

准备1万条team数据，准备100万条people数据。

造数据的存储过程：

[sql]  view plain copy

1. BEGIN  
2. DECLARE i INT;  
3. START TRANSACTION;  
4.   
5. SET i=0;  
6. WHILE i<10000 DO  
7.     INSERT INTO t_team VALUES(i+1,CONCAT('team',i+1));  
8.     SET i=i+1;  
9. END WHILE;  
10.   
11. SET i=0;  
12. WHILE i<1000000 DO  
13.     INSERT INTO t_people VALUES(i+1,CONCAT('people',i+1),CONCAT('team',i%10000+1));  
14.     SET i=i+1;  
15. END WHILE;  
16.   
17. COMMIT;  
18. END  

SQL语句执行效率：

连接查询

SELECT * FROM t_team t,t_people p WHERE t.tname=p.team_nameAND 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://blog.csdn.net/xiao__gui/article/details/8737910