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

一对多的两张表，一般是一张表的外键关联到另一个表的主键。但也有不一般的情况，也就是两个表并非通过其中一个表的主键关联。例如：create table t_team
（
tid int primary key,
tname varchar（100）
）;create table t_people
（
pid int primary key,
pname varchar（100）,
team_name varchar（100）
）;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://www.linuxidc.com/Linux/2013-03/81974.htm ）本人通过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;
ENDSQL语句执行效率：连接查询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 sOracle job不执行的分析处理MySQL查询优化:LIMIT 1避免全表扫描相关资讯 MySQL查询优化

MySQL查询优化：profile功能（05/18/2015 08:56:56）
MySQL不支持子查询优化一例（08/29/2014 21:29:25）
MySQL查询优化-Show命令（03/23/2013 08:13:36）

MySQL 索引及查询优化（04/25/2015 11:01:57）
MySQL查询优化:LIMIT 1避免全表扫（03/30/2013 12:38:13）

本文评论查看全部评论（0）