跨表查询和子查询的效率问题？

mlsml 2009-04-07 08:31:04
两张表，2张表分别都有1000条数据，它们的共同点就是相同的id。
1.我想知道在一个查询语句里写（跨表）查速度快，还是分为子查询速度快？
2.跨表查是1000*1000=1000000次？ 而子查询是只查了1000次？
...全文
783 26 打赏 收藏 转发到动态 举报
写回复
用AI写文章
26 条回复
切换为时间正序
请发表友善的回复…
发表回复
amsea 2009-12-18
  • 打赏
  • 举报
 回复
这么好的帖子怎么不继续讨论了？

上述问题的关键在于没有使用到索引。是否建立了索引？
ACMAIN_CHM 2009-04-13
  • 打赏
  • 举报
 回复
[Quote=引用 19 楼 mlsml 的回复:]
在数据5万和1万的隐式的内联还是比显示内联要快。

select a.*
from (select xm,sj,dzyj,byear,bmonth,zgxl,zgxlzy,date,userid from peopledata where date>='2009-01-01' and date <='2009-04-01') a ,peopqz b
where a.userid=b.userid
and b.gw like '%销售%' limit 10; 3.69秒


select a.*
from (select xm,sj,dzyj,byear,bmonth,zgxl,zgxlzy,date,userid from peopledata where date>='2009-01-01'…
[/Quote]

楼主为什么一直不肯用explain看一下MySQL的执行方案？
mlsml 2009-04-13
  • 打赏
  • 举报
 回复
在数据5万和1万的隐式的内联还是比显示内联要快。

select a.*
from (select xm,sj,dzyj,byear,bmonth,zgxl,zgxlzy,date,userid from peopledata where date>='2009-01-01' and date <='2009-04-01') a ,peopqz b
where a.userid=b.userid
and b.gw like '%销售%' limit 10; 3.69秒


select a.*
from (select xm,sj,dzyj,byear,bmonth,zgxl,zgxlzy,date,userid from peopledata where date>='2009-01-01' and date <='2009-04-01') a inner join peopqz b on a.userid=b.userid
where b.gw like '%销售%' limit 10; 6.36秒

mlsml 2009-04-13
  • 打赏
  • 举报
 回复
这意思是不是 这不适合使用 join 查询 ，会造成查询慢。。。。。。。。。
mlsml 2009-04-13
  • 打赏
  • 举报
 回复 


explain select xm,sj,dzyj,byear,bmonth,zgxl,zgxlzy,date from peopledata as p,peopqz as z where p.userid=z.userid and z.gw like '%销售%' and left(z.datetime,10)>='2009-01-01' and  left(z.datetime,10)<='2009-04-01'  limit 10;



| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows

  | Extra                          |

+----+-------------+-------+------+---------------+------+---------+------+-----

--+--------------------------------+

|  1 | SIMPLE      | z     | ALL  | NULL          | NULL | NULL    | NULL |  959

6 | Using where                    |

|  1 | SIMPLE      | p     | ALL  | NULL          | NULL | NULL    | NULL | 7463

4 | Using where; Using join buffer |

+----+-------------+-------+------+---------------+------+---------+------+-----
mlsml 2009-04-13
  • 打赏
  • 举报
 回复 


explain select a.* 

from (select xm,sj,dzyj,byear,bmonth,zgxl,zgxlzy,date,userid from peopledata where date>='2009-01-01' and date <='2009-04-01') a ,peopqz b 

where a.userid=b.userid 

and b.gw like '%销售%' limit 10;





| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows  | Extra                          |

+----+-------------+------------+------+---------------+------+---------+------+

-------+--------------------------------+

|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |  8314 |                                |

|  1 | PRIMARY     | b          | ALL  | NULL          | NULL | NULL    | NULL |  9596 | Using where; Using join buffer |

|  2 | DERIVED     | peopledata   | ALL  | NULL          | NULL | NULL    | NULL | 74634 | Using where                    |

+----+-------------+------------+------+---------------+------+---------+------+
ACMAIN_CHM 2009-04-13
  • 打赏
  • 举报
 回复
[Quote=引用 21 楼 mlsml 的回复:]
因为你没告诉我要用explain看一下MySQL的执行方案，我自己也不知道~~~~~~

explain select a.*
from (select xm,sj,dzyj,byear,bmonth,zgxl,zgxlzy,date,userid from peopledata where date>='2009-01-01' and date <='2009-04-01') a inner join peopqz b on a.userid=b.userid
where b.gw like '%销售%' limit 10;

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Ext…
[/Quote]

:-) 回贴太多了，也不知道对哪位说过了。不过的确忘了提醒你看一下explain了。

从上面这个结果看，什么index都没用到。possible_keys都为 NULL

再看看其它几句SQL的explain. 对比一下。
mlsml 2009-04-13
  • 打赏
  • 举报
 回复
因为你没告诉我要用explain看一下MySQL的执行方案，我自己也不知道~~~~~~

explain select a.*
from (select xm,sj,dzyj,byear,bmonth,zgxl,zgxlzy,date,userid from peopledata where date>='2009-01-01' and date <='2009-04-01') a inner join peopqz b on a.userid=b.userid
where b.gw like '%销售%' limit 10;

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+
-------+--------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 8314 | |
| 1 | PRIMARY | b | ALL | NULL | NULL | NULL | NULL | 9596 | Using where; Using join buffer |
| 2 | DERIVED | persdata | ALL | NULL | NULL | NULL | NULL | 74634 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+
ljf_ljf 2009-04-12
  • 打赏
  • 举报
 回复
楼主你应该有概念是 SQL 调优没有一个固定模式，原因在于各个数据库自身的数据分布不同，结构组成不同和数据库系统配置不同。
上述的三大因素很影响SQL 调优的具体手段。 所以，无论是什么人对SQL 进行调优一般都是在实际生成环境上进行。而我们进行调优都是根据一些基本理论的基础上去尝试，若发现一些比较特异情况就需要一些特别方式进行。
ACMAIN_CHM 2009-04-12
  • 打赏
  • 举报
 回复
[Quote=引用 14 楼 mlsml 的回复:]
这说明join on的效率不一定就高，对不
[/Quote]

是的。从来没人敢说 "join on a.id=b.id" 比 from a,b where a.id=b.id 速度快。虽然大部分情况下会快一些。但在很多数据库中这两句基本上是相同的，因为数据的SQL语句分析和优化阶段会优化成相同的语句。 数据库自身的SQL优化不是万能的，它只是根据某些策略比如（基于成本或基于时间）来做数据库自己认为是最优的执行方案。 所以有时还是需要程序员自己进行一些调整以便于数据库能得到最优的SQL。

你可以再试试。

select a.* 

from (select xm,sj,dzyj,byear,bmonth,zgxl,zgxlzy,date,userid from peopledata where date>='2009-01-01' and date <='2009-04-01') a ,peopqz b 

where a.userid=b.userid 

and b.gw like '%销售%' limit 10; 


select a.* 

from (select xm,sj,dzyj,byear,bmonth,zgxl,zgxlzy,date,userid from peopledata where date>='2009-01-01' and date <='2009-04-01') a inner join peopqz b on a.userid=b.userid 

where  b.gw like '%销售%' limit 10;
mlsml 2009-04-09
  • 打赏
  • 举报
 回复
抱歉，我的失误~

修正后：
select a.* from (select xm,sj,dzyj,byear,bmonth,zgxl,zgxlzy,date,userid from peopledata where date>='2009-01-01' and date <='2009-04-01') a inner join peopqz b where a.userid=b.userid and b.gw like '%销售%' limit 10;

只要5.26秒

效率还是比跨表连接查询慢些~~~~~~~~
难道直白的连接跨表查询还是比较快的？？？
mlsml 2009-04-09
  • 打赏
  • 举报
 回复
用ACMAIN_CHM 的方法改成我要的：

select a.* from (select xm,sj,dzyj,byear,bmonth,zgxl,zgxlzy,date from peoperdata where date>='2009-01-01' and date <='2009-04-01') a inner join peopqz b where a.userid=b.userid and b.gw like '%销售%';

报错：ERROR 1054 (42S22): Unknown column 'a.userid' in 'where clause'

这的a.userid=b.userid 不能这样写~~~~~

mlsml 2009-04-09
  • 打赏
  • 举报
 回复
就peopledata（5万数据），peopqz（1万数据）两张表 根据岗位要求是‘销售’时间在2009-01-01到2009-04-01之间共同的userid查，我做的这两个时间在mysql下分别是 2.75秒 和 15.25秒

select xm,sj,dzyj,byear,bmonth,zgxl,zgxlzy,date from peopledata as p,peopqz as z where p.userid=z.userid and z.gw like '%销售%' and left(z.datetime,10)>='2009-01-01' and left(z.datetime,10)<='2009-04-01' limit 10; 2.75秒

select xm,sj,dzyj,byear,bmonth,zgxl,zgxlzy,date from peopledata join peopqz on (peopqz.userid=peopledata.userid) where peopqz.gw like '%销售%' and left(peopqz.datetime,10)>='2009-01-01' and left(peopqz.datetime,10)<='2009-04-01' limit 10; 15.25秒

这说明join on的效率不一定就高，对不
ACMAIN_CHM 2009-04-09
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 mlsml 的回复:]
select a.xm,a.xb,a.xl,a.age from table1 a,table2 b where a.id=b.id and a.date>='2009-01-01' and a.date <='2009-02-01';

用这个查询a表数据5万条，b表数据1万条，查询结果用的30秒往后,有时候还在页面中的数据输出达到几分钟之久。。。。。这速度太慢了。怎么写才能让速度快点了
[/Quote]

1. 两表上对id字段，date字段建index
2. 由于MySQL自动优化的问题，建议使用 from table1 a inner join table2 b on a.id=b.id, MySQL的优化很多情况下并不理想，需要人工对SQL进一步优化
3. 如果a.date>='2009-01-01' and a.date <='2009-02-01'的记录很少。则建议
select a.*

from (

	select xm,xb,xl,age

	from table1

	where date>='2009-01-01' and date <='2009-02-01'

) a inner join table2 b where a.id=b.id


4. 在MySQL中利用explain看一下MySQL是如何针对你的SQL语句进行计划的。

wwwwb 2009-04-09
  • 打赏
  • 举报
 回复
1、在查询字段ID上建立索引；
2、用INNER JOIN显式内连接试试。
mlsml 2009-04-09
  • 打赏
  • 举报
 回复
select a.xm,a.xb,a.xl,a.age from table1 a,table2 b where a.id=b.id and a.date>='2009-01-01' and a.date<='2009-02-01';

用这个查询a表数据5万条，b表数据1万条，查询结果用的30秒往后,有时候还在页面中的数据输出达到几分钟之久。。。。。这速度太慢了。怎么写才能让速度快点了
wwwwb 2009-04-09
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 mlsml 的回复:]
引用 8 楼 cxyy_ 的回复:
我一直不太明白，下面这两个语句有什么本质的区别，效率上怎么谁更好
select a.*,b.* from table1 a inner join table2 b on a.id=b.id
select a.*,b.* from table1 a,table2 b where a.id=b.id


对，我就这意思，这两个的速度谁快，在表中数据有1000条，或者10000条时~~~~
[/Quote]
相差不多，第一个显式内连接，第二个隐式内连接
mlsml 2009-04-09
  • 打赏
  • 举报
 回复
[Quote=引用 8 楼 cxyy_ 的回复:]
我一直不太明白，下面这两个语句有什么本质的区别，效率上怎么谁更好
select a.*,b.* from table1 a inner join table2 b on a.id=b.id
select a.*,b.* from table1 a,table2 b where a.id=b.id
[/Quote]

对，我就这意思，这两个的速度谁快，在表中数据有1000条，或者10000条时~~~~
ACMAIN_CHM 2009-04-08
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 mlsml 的回复:]
我说的子查询是把原先能写成一句查询的分解成2句写，并且表是没有索引的。。。。
[/Quote]

贴出你的语句的样本，这样可以方便测试分析。否则也猜不出你的所谓子查询是怎么实现的。
select * from a where id in (select id from b)
还是
select * from a where exists (select id from b where id=a.id)

???
wwwwb 2009-04-08
  • 打赏
  • 举报
 回复
有相同的ID，用内连接更快，子查询速度慢一些。
加载更多回复（6）
MySQL 查询与高级查询（多表、嵌套和正则表达式）
Re： MySQL 查询与高级查询（多表、嵌套和正则表达式）  （查询的能力，显示DBA的功力） ============================================= 数据库管理系统的个最重要的功能就是数据查询，数据查询不应只是简单...
连表查询和子查询在实际中的使用问题和分析
2.子查询就更别用了，效率太差，执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。 3.如果是JOIN的话，它...
Django之ORM跨表查询、join查询、聚合查询、分组查询
跨表查询分为两类: 基于对象查询(子查询) 基于双下划线查询(join查询) 1、基于对象的跨表查询(sql语句：子查询） 子查询： 基于一个查询结果作为另一个查询的条件 1.1 一对多 正向查询(按字段) # 查询西游记...
oracle 跨分区查询效率,Oracle分区表做跨分区查询
问：有一张大表，其中按时间字段(TIME_ID)进行表分区(按季度分区)，但是如果业务人员做跨季度的大批量数据的查询时，未能走TIME_ID分区索引，导致全表扫描。此种情况该如何处理？示例解析：1、我们根据oracle中sh...
MySQL子查询的优缺点_为什么MySQL不推荐使用子查询和join
2.子查询就更别用了，效率太差，执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。3.如果是JOIN的话，它是走嵌套查询...
MySQL

56,677

社区成员

56,708

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章