84669 Lernen von Personen
152542 Lernen von Personen
20005 Lernen von Personen
5487 Lernen von Personen
7821 Lernen von Personen
359900 Lernen von Personen
3350 Lernen von Personen
180660 Lernen von Personen
48569 Lernen von Personen
18603 Lernen von Personen
40936 Lernen von Personen
1549 Lernen von Personen
1183 Lernen von Personen
32909 Lernen von Personen
两张表.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. 实验如下: