一对多的两张表,一般是一张表的外键关联到另一个表的主键。但也有不一般的情况,也就是两个表并非通过其中一个表的主键关联。
例如:
复制代码 代码如下:
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://blog.csdn.net/xiao__gui/article/details/8726272 )
本人通过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
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
例如:
复制代码 代码如下:
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://blog.csdn.net/xiao__gui/article/details/8726272 )
本人通过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
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
标签:
优化,子查询,主键
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件!
如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
白云城资源网 Copyright www.dyhadc.com
暂无“MySQL查询优化:用子查询代替非主键连接查询实例介绍”评论...
更新日志
2024年07月03日
2024年07月03日
- 单依纯《纯情歌》2024最新 开盘母带1:1直刻[547M]
- 永劫无间手游沈妙怎么样 技能奥义介绍
- 三角洲行动穿透机制介绍 穿透等级穿透效果一览
- 吟游战记巨刃特蕾值得培养吗 角色技能属性详解
- 逆水寒手游创意工坊玩法介绍 逆水寒创意工坊怎么玩
- 逆水寒手游雀隐重楼玩法介绍 逆水寒手游雀隐重楼是什么
- 逆水寒手游神器系统玩法攻略 逆水寒手游神器系统怎么玩
- PS5首席架构师提交新专利:PS5有望原生运行PS3游戏?
- 数据显示:《地狱潜者2》在PC平台已失去90%的玩家
- 质疑和守望先锋太像?《星鸣特攻》开发商:我们与众不同
- 【休闲沙发】VA-2024-CalmReflections:ChilloutYourMind(FLAC)
- 【迷幻沙发】Koan(公案)-2024-TheQueenofSpades(SideA)(FLAC)
- 群星2006-银河映像1996-20052CD[香港首版][WAV+CUE]
- 外媒盘点游戏里十大最可怕外星生物:第一名是老熟人
- 阿拉贡演员谈参演《咕噜》新电影:要视情况而定