sql执行顺序问题

Question

sql执行顺序from,on,join。参考文章

有两个表posts 10000条数据，post_slugs 14条记录，posts，两个表通过post_id关联。使用mysql

SELECT * from posts,post_slugs where post_slugs.post_id=posts.post_id,按sql执行顺序，from之后是两个表的迪卡尔积，因此where条件实际操作的数据应该是10000X14条记录。

SELECT * from posts join post_slugs on post_slugs.post_id=posts.post_id按sql执行顺序，from读取posts表的数据生成虚表T1,在根据on条件读取post_slugs满足条件的数据生成虚表T2,再根据join方式，插入需要保留的数据，那实际上join之后的数据量应该是14条记录。

但是，实际上两条语句执行时间没有什么差异。这是因为mysql优化器处理了还是说执行顺序理解有误？

Answer 1

join 有 left join，right join，inner join ；
join就是inner join这个是内链接
inner join和where运行结果是一样的 除非是用left join，right join所产生的结果才不一样 就是一那个为主表查询如
A left join B ON a.id = b.id
这里就是A为主表 A表的数据都会查询查询出来

Answer 2

SELECT * from posts,post_slugs 两个表的迪卡尔积
SELECT * from posts,post_slugs where post_slugs.post_id=posts.post_id 后面有条件自动优化成下面的语句了
SELECT * from posts inner join post_slugs on post_slugs.post_id=posts.post_id
二者区别就是join必须带条件on，前者可以无条件。
在postgresql中用explain analyze分析前后二者行为完全一致，join时间略优0.05ms左右，估计大概是是多表优化的时间

explain analyze select * from t_xmxx_x a, t_shwzcmx b where a.f_xmid=b.f_xmid;
+----------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                             |
|----------------------------------------------------------------------------------------------------------------------------------------|
| Nested Loop  (cost=0.29..8514.09 rows=27592 width=1412) (actual time=0.017..53.627 rows=27586 loops=1)                                 |
|   ->  Seq Scan on t_xmxx_x a  (cost=0.00..2578.49 rows=6149 width=955) (actual time=0.003..1.977 rows=6149 loops=1)                    |
|   ->  Index Scan using t_shwzcmx_index on t_shwzcmx b  (cost=0.29..0.89 rows=8 width=457) (actual time=0.002..0.005 rows=4 loops=6149) |
|         Index Cond: ((f_xmid)::text = (a.f_xmid)::text)                                                                                |
| Total runtime: 55.362 ms                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN
Time: 0.061s


explain analyze select * from t_xmxx_x a join t_shwzcmx b on a.f_xmid=b.f_xmid;
+----------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                             |
|----------------------------------------------------------------------------------------------------------------------------------------|
| Nested Loop  (cost=0.29..8514.09 rows=27592 width=1412) (actual time=0.020..53.727 rows=27586 loops=1)                                 |
|   ->  Seq Scan on t_xmxx_x a  (cost=0.00..2578.49 rows=6149 width=955) (actual time=0.002..1.879 rows=6149 loops=1)                    |
|   ->  Index Scan using t_shwzcmx_index on t_shwzcmx b  (cost=0.29..0.89 rows=8 width=457) (actual time=0.002..0.005 rows=4 loops=6149) |
|         Index Cond: ((f_xmid)::text = (a.f_xmid)::text)                                                                                |
| Total runtime: 55.319 ms                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN
Time: 0.061s

Answer 3

数据太小 基本无区别
100万以上数据试试

Answer 4

SELECT * from posts,post_slugs where post_slugs.post_id=posts.post_id
SELECT * from posts join post_slugs on post_slugs.post_id=posts.post_id
以上只是语法上两种不同的写法，含义和执行都是一样的。
第一条SQL并不是先通过笛卡尔积再where筛选，以下的才是
select * from (SELECT a.post_id as ida,b.post_id as idb from posts a,post_slugs b) c where c.ida=c.idb;