mysql 大表小表驱动的问题

Question

有1个应用
select a.* , b.* from a, b
where a.id=b.id说明  a是200万记录  b是700万记录 加入b是200万记录  a是700万记录 
是否该语句一定要换为下面的
sqlselect a.* , b.* from  b，a
where b.id=a.id
（mysql 的驱动原则  小表带大表）

Answer 1

不需要，MySQL会自行分析，会从中选择小的表开始的。

Answer 2

不需要，
mysql的优化器会自动实现的

Answer 3

刚好有两个表 t1, t_06, 
t1 表 10000 记录， t_06 表 499999 记录
下面测试中，可以很明显看出，无论 t1, t_06 表顺序如何，MYSQL均会以同一方案执行。 另外也说明了在MySQL中不会象文件型数据库中那样有什么 a inner join b on a.id=b.id  比 from a,b where a.id=b.id 效率高之说。mysql> select count(*) from t1,t_06 where t1.id=t_06.id;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (1.89 sec)mysql> select count(*) from t_06;
+----------+
| count(*) |
+----------+
|   499999 |
+----------+
1 row in set (0.02 sec)mysql>mysql> explain select * from t1,t_06 where t1.id=t_06.id;
+----+-------------+-------+--------+---------------+---------+---------+------------+--------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref     | rows   | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------------+--------+-------+
|  1 | SIMPLE      | t1    | ALL    | PRIMARY       | NULL    | NULL    | NULL     | 100000 |       |
|  1 | SIMPLE      | t_06  | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.id |      1 |       |
+----+-------------+-------+--------+---------------+---------+---------+-------
-----+--------+-------+
2 rows in set (0.00 sec)mysql> explain select * from t_06,t1 where t_06.id=t1.id;
+----+-------------+-------+--------+---------------+---------+---------+------------+--------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref     | rows   | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------------+--------+-------+
|  1 | SIMPLE      | t1    | ALL    | PRIMARY       | NULL    | NULL    | NULL     | 100000 |       |
|  1 | SIMPLE      | t_06  | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.id |      1 |       |
+----+-------------+-------+--------+---------------+---------+---------+------------+--------+-------+
2 rows in set (0.00 sec)mysql>
mysql> explain select * from t1 inner join t_06 using (id);
+----+-------------+-------+--------+---------------+---------+---------+------------+--------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref     | rows   | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------------+--------+-------+
|  1 | SIMPLE      | t1    | ALL    | PRIMARY       | NULL    | NULL    | NULL     | 100000 |       |
|  1 | SIMPLE      | t_06  | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.id |      1 |       |
+----+-------------+-------+--------+---------------+---------+---------+------------+--------+-------+
2 rows in set (0.00 sec)mysql> explain select * from t_06 inner join t1 using (id);
+----+-------------+-------+--------+---------------+---------+---------+------------+--------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref     | rows   | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------------+--------+-------+
|  1 | SIMPLE      | t1    | ALL    | PRIMARY       | NULL    | NULL    | NULL     | 100000 |       |
|  1 | SIMPLE      | t_06  | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.id |      1 |       |
+----+-------------+-------+--------+---------------+---------+---------+------------+--------+-------+
2 rows in set (0.00 sec)mysql>