84669 person learning
152542 person learning
20005 person learning
5487 person learning
7821 person learning
359900 person learning
3350 person learning
180660 person learning
48569 person learning
18603 person learning
40936 person learning
1549 person learning
1183 person learning
32909 person learning
两张表.manytoone的关系.查询many,通过外键随便把one端得信息查出.我们可以通过left outer join on鞥等的join查询进行抓取.或者可以通过 where many端得外键id=one端得主键 效果是一样的.那他们具体的查询效率是否有区别?
业精于勤,荒于嬉;行成于思,毁于随。
看情况,如果你的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不用遍历第二张表
楼上的答案不靠谱. 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>
看情况,如果你的devicespec1.id是主键或者索引什么的,那么join效率高。看这个就清楚了:
第一次查询要遍历两张表,第二次查询使用eq_ref不用遍历第二张表
楼上的答案不靠谱. 1. 楼主明确说了,这里用外键关联, 实验里应该没有; 2. 只用了区区3,4条数据来做实验.
做了实验, mysql5.5.24 两张表每张10w数据. "where进行关联" 实际上被转为join. 实验如下: