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

Question

两张表，2张表分别都有1000条数据，它们的共同点就是相同的id。
1.我想知道在一个查询语句里写（跨表）查速度快，还是分为子查询速度快？
2.跨表查是1000*1000=1000000次？ 而子查询是只查了1000次？

Answer 1

在有索引的情况下 
select a.*,b.* from a inner join b on a.id=b.id 会快。

Answer 2

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

Answer 3

我一直不太明白，下面这两个语句有什么本质的区别，效率上怎么谁更好
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

Answer 4

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秒往后,有时候还在页面中的数据输出达到几分钟之久。。这速度太慢了。怎么写才能让速度快点了

Answer 5

1、在查询字段ID上建立索引；
2、用INNER JOIN显式内连接试试。

Answer 6

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.id4. 在MySQL中利用explain看一下MySQL是如何针对你的SQL语句进行计划的。

Answer 7

就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的效率不一定就高，对不

Answer 8

用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 不能这样写~~~~~

Answer 9

抱歉，我的失误~修正后：
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秒效率还是比跨表连接查询慢些~~~~~~~~
难道直白的连接跨表查询还是比较快的？？？

Answer 10

是的。从来没人敢说 "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; 

Answer 11

楼主你应该有概念是 SQL 调优没有一个固定模式，原因在于各个数据库自身的数据分布不同，结构组成不同和数据库系统配置不同。
上述的三大因素很影响SQL 调优的具体手段。 所以，无论是什么人对SQL 进行调优一般都是在实际生成环境上进行。而我们进行调优都是根据一些基本理论的基础上去尝试，若发现一些比较特异情况就需要一些特别方式进行。

Answer 12

在数据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秒

Answer 13

楼主为什么一直不肯用explain看一下MySQL的执行方案？

Answer 14

因为你没告诉我要用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                    |
+----+-------------+------------+------+---------------+------+---------+------+

Answer 15

:-) 回贴太多了，也不知道对哪位说过了。不过的确忘了提醒你看一下explain了。从上面这个结果看，什么index都没用到。possible_keys都为 NULL再看看其它几句SQL的explain. 对比一下。

Answer 16

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                    |
+----+-------------+------------+------+---------------+------+---------+------+

Answer 17

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 |
+----+-------------+-------+------+---------------+------+---------+------+-----

Answer 18

这意思是不是 这不适合使用  join  查询 ，会造成查询慢