请问使用join或where对于关联表的查询，它们具体的查询效率是否有区别?

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>