MySQL查询优化:用子查询代替非主键连接查询实例介绍
程序员文章站
2024-02-22 11:25:46
一对多的两张表,一般是一张表的外键关联到另一个表的主键。但也有不一般的情况,也就是两个表并非通过其中一个表的主键关联。 例如: 复制代码 代码如下: create tabl...
一对多的两张表,一般是一张表的外键关联到另一个表的主键。但也有不一般的情况,也就是两个表并非通过其中一个表的主键关联。
例如:
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