请教一个SQL多表查询的效率问题

Question

例如下面的两种写法：

第一种：

SELECT
    * 
FROM 
    ( SELECT * FROM t_a WHERE xx=1 ) a 
    LEFT JOIN t_b b ON a.id = b.bid

第二种：

SELECT 
    *
FROM
    t_a a 
    LEFT JOIN t_b b ON a.id = b.bid
WHERE
    a.xx = 1

因为我现在没有什么大数据量的表，也没法测；
这两种写法速度上会差很多么？
感觉当 t_a 数据量大的时候，第一种会快很多吧

Answer 1

理论上两者的效率一样，因为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估计没有）