首頁猿問請問使用join或where對于關(guān)...

請問使用join或where對于關(guān)聯(lián)表的查詢，它們具體的查詢效率是否有區(qū)別?

MySQL

喵喔喔 2023-04-04 12:13:32

兩張表.manytoone的關(guān)系.查詢many,通過外鍵隨便把one端得信息查出.我們可以通過left outer join on鞥等的join查詢進(jìn)行抓取.或者可以通過 where many端得外鍵id=one端得主鍵效果是一樣的.

查看完整描述

2 回答

慕斯王

TA貢獻(xiàn)1864條經(jīng)驗(yàn) 獲得超2個(gè)贊

看情況，如果你的devicespec1.id是主鍵或者索引什么的，那么join效率高?？催@個(gè)就清楚了：

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不用遍歷第二張表

反對回復(fù) 2023-04-07

絕地?zé)o雙

TA貢獻(xiàn)1946條經(jīng)驗(yàn) 獲得超4個(gè)贊

1. 樓主明確說了,這里用外鍵關(guān)聯(lián), 實(shí)驗(yàn)里應(yīng)該沒有; 2. 只用了區(qū)區(qū)3,4條數(shù)據(jù)來做實(shí)驗(yàn).

做了實(shí)驗(yàn), mysql5.5.24 兩張表每張10w數(shù)據(jù). "where進(jìn)行關(guān)聯(lián)" 實(shí)際上被轉(zhuǎn)為join. 實(shí)驗(yàn)如下:

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>

反對回復(fù) 2023-04-07