理论上两者的效率一样,因为mysql优化器会将第一种写法优化成第二种(mysql 5.7.16)。
测试下,使用explain
和show warnings
dba test>explain select * from (select * from t_order where username='abul') a left join t_user b on a.user_id=b.user_id;
+----+-------------+----------+------------+--------+---------------+----------+---------+------------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+--------+---------------+----------+---------+------------------------+------+----------+-------+
| 1 | SIMPLE | t_order | NULL | ref | username | username | 182 | const | 8 | 100.00 | NULL |
| 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 8 | test.t_order.user_id | 1 | 100.00 | NULL |
+----+-------------+----------+------------+--------+---------------+----------+---------+------------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
dba test>show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `test`.`t_order`.`order_id` AS `order_id`,`test`.`t_order`.`user_id` AS `user_id`,`test`.`t_order`.`username` AS `username`,`test`.`t_order`.`money` AS `money`,`test`.`b`.`user_id` AS `user_id`,`test`.`b`.`username` AS `username`,`test`.`b`.`password` AS `password` from `test`.`t_order` left join `test`.`t_user` `b` on((`test`.`b`.`user_id` = `test`.`t_order`.`user_id`)) where (`test`.`t_order`.`username` = 'TinTRT') |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
show warnings
就是优化器优化后的SQL,会发现已经自动转换成第二种SQL的写法了。(这是在mysql 5.7.16版本上测试,早期5.5估计没有)
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…