mysql求助请问where a.id=b.id 和join on a.id=b.id 在效率上的区别 [问题点数：40分，结帖人anydy2008]

Question

两张表.manytoone的关系.查询many,通过外键随便把one端得信息查出.我们可以通过left outer join on鞥等的join查询进行抓取.或者可以通过 where many端得外键id=one端得主键效果是一样的.那他们具体的查询效率是否有区别?

Answer 1

看情况，如果你的devicespec1.id是主键或者索引什么的，那么join效率高。看这个就清楚了：

mysql> explain select test_tb1.id, test_tb2.value from test_tb1, test_tb2 where test_tb1.id = test_tb2.id;
+----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra                          |
+----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | test_tb1 | index | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using index                    |
|  1 | SIMPLE      | test_tb2 | ALL   | PRIMARY       | NULL    | NULL    | NULL |    4 | Using where; Using join buffer |
+----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------------+
2 rows in set (0.01 sec)

mysql> explain select test_tb1.id, test_tb2.value from test_tb1 left join test_tb2 on test_tb1.id = test_tb2.id;
+----+-------------+----------+--------+---------------+---------+---------+------------------+------+-------------+
| id | select_type | table    | type   | possible_keys | key     | key_len | ref              | rows | Extra       |
+----+-------------+----------+--------+---------------+---------+---------+------------------+------+-------------+
|  1 | SIMPLE      | test_tb1 | index  | NULL          | PRIMARY | 4       | NULL             |    3 | Using index |
|  1 | SIMPLE      | test_tb2 | eq_ref | PRIMARY       | PRIMARY | 4       | test.test_tb1.id |    1 |             |
+----+-------------+----------+--------+---------------+---------+---------+------------------+------+-------------+
2 rows in set (0.00 sec)

第一次查询要遍历两张表，第二次查询使用eq_ref不用遍历第二张表

Answer 2

楼上的答案不靠谱. 1. 楼主明确说了,这里用外键关联, 实验里应该没有; 2. 只用了区区3,4条数据来做实验.

做了实验, mysql5.5.24 两张表每张10w数据. "where进行关联" 实际上被转为join. 实验如下:

mysql> show create table x;
+-------+---------------------------------------------------------------------------------------------------------+
| Table | Create Table    |
+-------+---------------------------------------------------------------------------------------------------------+
| x     | CREATE TABLE `x` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table y;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table             |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| y     | CREATE TABLE `y` (
  `id` int(11) NOT NULL,
  `xid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_y_x` (`xid`),
  CONSTRAINT `fk_y_x` FOREIGN KEY (`xid`) REFERENCES `x` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

mysql> select count(*) from x;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.04 sec)

mysql> select count(*) from y;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.04 sec)

mysql> explain extended select x.id, y.id from x,y where x.id=y.xid;
+----+-------------+-------+-------+---------------+---------+---------+------------+-------+----------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref        | rows  | filtered | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------------+-------+----------+--------------------------+
|  1 | SIMPLE      | x     | index | PRIMARY       | PRIMARY | 4       | NULL       | 95012 |   100.00 | Using index              |
|  1 | SIMPLE      | y     | ref   | fk_y_x        | fk_y_x  | 5       | test1.x.id |     1 |   100.00 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------------+-------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                           |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test1`.`x`.`id` AS `id`,`test1`.`y`.`id` AS `id` from `test1`.`x` join `test1`.`y` where (`test1`.`y`.`xid` = `test1`.`x`.`id`) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain extended select x.id, y.id from x left join y on x.id=y.xid;
+----+-------------+-------+-------+---------------+---------+---------+------------+-------+----------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref        | rows  | filtered | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------------+-------+----------+-------------+
|  1 | SIMPLE      | x     | index | NULL          | PRIMARY | 4       | NULL       | 95012 |   100.00 | Using index |
|  1 | SIMPLE      | y     | ref   | fk_y_x        | fk_y_x  | 5       | test1.x.id |     1 |   100.00 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                      |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test1`.`x`.`id` AS `id`,`test1`.`y`.`id` AS `id` from `test1`.`x` left join `test1`.`y` on((`test1`.`x`.`id` = `test1`.`y`.`xid`)) where 1 |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Answer 3

关于 “A LEFT JOIN B ON 条件表达式” 的一点提醒

ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行，即使on中包含有A表中的列的限制条件，也不会过滤A的任何数据（A的数据只会通过where过滤）。

如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据

在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。

让我们看一个 LFET JOIN 示例：

01    

mysql> CREATE TABLE `product`
 (    

02    

  `id` int(10)
 unsigned NOT NULL auto_increment,    

03    

  `amount` int(10)
 unsigned default NULL,    

04    

  PRIMARY KEY  (`id`)    

05    

)
 ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1    

06    

07    

mysql> CREATE TABLE `product_details`
 (    

08    

  `id` int(10)
 unsigned NOT NULL,    

09    

  `weight` int(10)
 unsigned default NULL,    

10    

  `exist` int(10)
 unsigned default NULL,    

11    

  PRIMARY KEY  (`id`)    

12    

)
 ENGINE=MyISAM DEFAULT CHARSET=latin1    

13    

14    

mysql> INSERT INTO product
 (id,amount)    

15    

       VALUES (1,100),(2,200),(3,300),(4,400);    

16    

Query
 OK, 4 rows affected
 (0.00 sec)    

17    

Records:
 4  Duplicates: 0  Warnings: 0    

18    

19    

mysql> INSERT INTO product_details
 (id,weight,exist)    

20    

       VALUES (2,22,0),(4,44,1),(5,55,0),(6,66,1);    

21    

Query
 OK, 4 rows affected
 (0.00 sec)    

22    

Records:
 4  Duplicates: 0  Warnings: 0    

23    

24    

mysql> SELECT * FROM product;    

25    

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

26    

|
 id | amount |    

27    

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

28    

| 
 1 |    100 |    

29    

| 
 2 |    200 |    

30    

| 
 3 |    300 |    

31    

| 
 4 |    400 |    

32    

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

33    

4 rows in set (0.00
 sec)    

34    

35    

mysql> SELECT * FROM product_details;    

36    

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

37    

|
 id | weight | exist |    

38    

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

39    

| 
 2 |     22 |     0 |    

40    

| 
 4 |     44 |     1 |    

41    

| 
 5 |     55 |     0 |    

42    

| 
 6 |     66 |     1 |    

43    

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

44    

4 rows in set (0.00
 sec)    

45    

46    

mysql> SELECT * FROM product LEFT JOIN product_details    

47    

       ON (product.id
 = product_details.id);    

48    

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

49    

|
 id | amount | id   | weight | exist |    

50    

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

51    

| 
 1 |    100 | NULL |   NULL |  NULL |    

52    

| 
 2 |    200 |    2 |     22 |     0 |    

53    

| 
 3 |    300 | NULL |   NULL |  NULL |    

54    

| 
 4 |    400 |    4 |     44 |     1 |    

55    

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

56    

4 rows in set (0.00
 sec)    

ON 子句和 WHERE 子句有什么不同？

一个问题：下面两个查询的结果集有什么不同么？

1    

1. SELECT * FROM product LEFT JOIN product_details    

2    

         ON (product.id
 = product_details.id)    

3    

         AND   product_details.id=2;    

4    

2. SELECT * FROM product LEFT JOIN product_details    

5    

         ON (product.id
 = product_details.id)    

6    

         WHERE product_details.id=2;    

用例子来理解最好不过了：

01    

mysql> SELECT * FROM product LEFT JOIN product_details    

02    

       ON (product.id
 = product_details.id)    

03    

       AND product_details.id=2;    

04    

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

05    

|
 id | amount | id   | weight | exist |    

06    

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

07    

| 
 1 |    100 | NULL |   NULL |  NULL |    

08    

| 
 2 |    200 |    2 |     22 |     0 |    

09    

| 
 3 |    300 | NULL |   NULL |  NULL |    

10    

| 
 4 |    400 | NULL |   NULL |  NULL |    

11    

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

12    

4 rows in set (0.00
 sec)    

13    

14    

mysql> SELECT * FROM product LEFT JOIN product_details    

15    

       ON (product.id
 = product_details.id)    

16    

       WHERE product_details.id=2;    

17    

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

18    

|
 id | amount | id | weight | exist |    

19    

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

20    

| 
 2 |    200 |  2 |     22 |     0 |    

21    

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

22    

1
 row in set (0.01
 sec)    

第一条查询使用 ON 条件决定了从 LEFT JOIN的 product_details表中检索符合的所有数据行。

第二条查询做了简单的LEFT JOIN，然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。

再来看一些示例：

01    

mysql>    

02    

mysql> SELECT * FROM product LEFT JOIN product_details    

03    

       ON product.id
 = product_details.id    

04    

       AND product.amount=100;    

05    

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

06    

|
 id | amount | id   | weight | exist |    

07    

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

08    

| 
 1 |    100 | NULL |   NULL |  NULL |    

09    

| 
 2 |    200 | NULL |   NULL |  NULL |    

10    

| 
 3 |    300 | NULL |   NULL |  NULL |    

11    

| 
 4 |    400 | NULL |   NULL |  NULL |    

12    

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

13    

4 rows in set (0.00
 sec)    

所有来自product表的数据行都被检索到了，但没有在product_details表中匹配到记录（product.id = product_details.id AND product.amount=100 条件并没有匹配到任何数据）

01    

mysql> SELECT * FROM product LEFT JOIN product_details    

02    

       ON (product.id
 = product_details.id)    

03    

       AND product.amount=200;    

04    

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

05    

|
 id | amount | id   | weight | exist |    

06    

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

07    

| 
 1 |    100 | NULL |   NULL |  NULL |    

08    

| 
 2 |    200 |    2 |     22 |     0 |    

09    

| 
 3 |    300 | NULL |   NULL |  NULL |    

10    

| 
 4 |    400 | NULL |   NULL |  NULL |    

11    

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

12    

4 rows in set (0.01
 sec)    

同样，所有来自product表的数据行都被检索到了，有一条数据匹配到了。

来自  https://zhidao.baidu.com/question/203079318880733485.html

写法不一样而且，效果一样。
join这里指inner join.

你们总监挺水的。

回复于： 2015-08-27 17:04:01#1 得分：0

对我有用[0] 丢个板砖[0] 引用 | 举报 | 管理

账号:jinfengyiye 昵称:gw6328 关注博客空间最新帖子: MVC Cookie问题 CSDN一万年前的bug还未修复 BI工具安装问题更多 >> jinfengyiye gw6328	也不能这么说，人无完人，我以前老大也是left join 与inner join 没分清楚。
	回复于： 2015-08-27 17:04:42#2 得分：0 对我有用[0] 丢个板砖[0] 引用 \| 举报 \| 管理

u012665454 还多一点	引用 1 楼 jinfengyiye 的回复: 写法不一样而且，效果一样。 join这里指inner join. 你们总监挺水的。
	回复于： 2015-08-27 17:34:30#3 得分：0 对我有用[0] 丢个板砖[0] 引用 \| 举报 \| 管理

yupeigu 不想长大啊更多勋章	这个效率不一定，where是用来过滤数据库的，而join 也是可以用来过滤数据的，这个要分情况，你可以问问你的老大具体在什么情况下where的效率更高
	回复于： 2015-10-30 14:42:56#4 得分：0 对我有用[0] 丢个板砖[0] 引用 \| 举报 \| 管理

rucypli rucypli	where和jnner join是一样的可以explain看执行计划是一样的但是和left join ，right join不一样逻辑不一样
	回复于： 2015-10-30 15:00:50#5 得分：0 对我有用[0] 丢个板砖[0] 引用 \| 举报 \| 管理

yangb0803 道玄希言	不管是 where 或者 inner join ，还是得执行计划来说话了。小数据，不建索引，我测试了下，两个的执行计划一样。。。大数据量，添加索引的情况沒试， ^_^
	回复于： 2015-10-30 15:42:58#6 得分：0 对我有用[0] 丢个板砖[0] 引用 \| 举报 \| 管理

: u011575570; rick-he

测试，讲证据

来自 http://bbs.csdn.net/topics/391818159

You are here

mysql求助请问where a.id=b.id 和join on a.id=b.id 在效率上的区别 [问题点数：40分，结帖人anydy2008]

对于关联表的查询使用join或where进行关联.具体的效率有没有很大区别?

2个回答

来自 https://segmentfault.com/q/1010000000094700

mysql where和 join on 哪个效率高

友情链接

xuzuning xuzuning 更多勋章	你的第一式是左链接，因无其他过滤条件结果集中将会有左表（ecs_goods）的全部记录你的第二式是逗号连接（INNER JOIN 的简写) 结果集中只会出现符合连接条件的记录两者的作用是不同的，不能做效率比较
	回复于： 2013-12-01 17:14:03#1 得分：40 对我有用[0] 丢个板砖[0] 引用 \| 举报 \| 管理

xuzuning xuzuning 更多勋章	由于连接外有过滤条件，所以 mysql 会将你的查询指令优化成内连接。因此就不存在效率的对比了当然这里可以对比的是：在商品表中查品牌商品和在品牌表中查商品两者的效率是不一样的因为品牌表显然要比商品表小
	回复于： 2013-12-01 20:44:52#5 得分：0 对我有用[0] 丢个板砖[0] 引用 \| 举报 \| 管理

搜索表单

用户登录

You are here

mysql求助 请问where a.id=b.id 和join on a.id=b.id 在效率上的区别 [问题点数：40分，结帖人anydy2008]

对于关联表的查询使用join或where进行关联.具体的效率有没有很大区别?

2个回答

来自 https://segmentfault.com/q/1010000000094700

mysql where和 join on 哪个效率高

友情链接

mysql求助请问where a.id=b.id 和join on a.id=b.id 在效率上的区别 [问题点数：40分，结帖人anydy2008]