SQL from多表和 inner join的区别

Question

这是inner join写法

SELECT
    B.dict_data_name,
    SUM(A.pv) AS pv
FROM
    shw_mo_health_news A
INNER JOIN bas_dict_data B ON A.third_name_dictid = B.item_id
WHERE
    A.class_level = 3
AND B.class_id = 1012
AND A.collect_date >= '2016-04-01'
AND A.collect_date <= '2016-05-31'
GROUP BY
    A.third_name_dictid
ORDER BY
    pv DESC;

实际查询时间用户0.6S左右

用explain 查看:

---

这是from 多表写法

SELECT
    B.dict_data_name,
    A.PV
FROM
    (
        SELECT
            hn.third_name_dictid,
            SUM(hn.pv) AS PV
        FROM
            shw_mo_health_news hn
        WHERE
            hn.class_level = 3
        AND hn.collect_date >= '2016-04-01'
        AND hn.collect_date <= '2016-05-31'
        GROUP BY
            hn.third_name_dictid
    ) A,
    (
        SELECT
            dd.item_id,
            dd.dict_data_name
        FROM
            bas_dict_data dd
        WHERE
            dd.class_id = 1012
    ) B
WHERE
    A.third_name_dictid = B.item_id
ORDER BY
    PV DESC

实际用时0.03s左右

Explain查看

---

为什么这两种查询方式的效率会有这么大的差别呢?
网上很多都说这两种写法的效率是差不多的;但是我这一个是0.6 和一个0.03,两者差别还是挺大的,这是什么原因造成的呢?是因为我写的SQl语句有问题还是别的原因?

Answer 1

应该是连接损耗吧，我觉得是因为你使用的条件都没有索引造成的。如果字段进行了适当的索引，由于mysql会自动对sql语句优化的原因，最终查询语句是一样的，性能相同，而没有索引的的时候所谓的优化也就不存在了，这个时候最终查询语句基本等同于你提交的sql。
你可以试试

SELECT
    B.dict_data_name,
    SUM(A.pv) AS pv
FROM
    shw_mo_health_news A
INNER JOIN bas_dict_data B ON A.class_level = 3 and A.collect_date >= '2016-04-01'
AND A.collect_date <= '2016-05-31' and B.class_id=1012 and  A.third_name_dictid = B.item_id

GROUP BY
    A.third_name_dictid
ORDER BY
    pv DESC;

我觉得性能会明显不同。

Answer 2

一样的，属于 SQL-89 与 SQL-92 不同规范。参见 https://en.wikipedia.org/wiki...

---

查到一个相关问答，其中有个回答恰好是您这个问题的 https://community.microstrate...

Answer 3

这里第2个SQL由于有子查询会有额外的开销(临时表)。
第2个SQL为什么会比第1个SQL，根据执行计划并没有看出什么，感觉只是个例外并不能说明什么

Answer 4

理论上说，子查询和JOIN没有本质区别，在查询分析器合理的优化之后应该是等效的。但是也正是由于查询分析器的各种缺陷，有些时候有些版本的数据库对子查询支持得更好，有些则对JOIN支持得更好。MySQL来说我见过的大部分版本子查询和JOIN是等效的，但是要小心的是子查询位于WHERE中的场景，比如：

-- 查询1
SELECT * FROM table_a
WHERE A IN (
    SELECT A FROM table_b
    WHERE B = 'x'
)
-- 查询2
SELECT table_a.* FROM table_a A
    INNER JOIN table_b B ON a.A = b.A
WHERE B.B = 'x'

这就是一个典型的MySQL查询分析器失效的场景。table_a的A字段有索引的情况下理论上查询1和查询2应该等效，但实际上MySQL 5.x的版本中查询2的性能要明显优于查询1。这在Stackoverflow上有人讨论，是一个存在近10年的已知问题。必须要升级到6.0.x才会得到修复。所以，对于哪个更优的问题，最可靠的办法只有自己去看explain的结果再下结论。
对于你的两个查询，其实执行计划都是不一样的，很明显第二个的消耗要更大，时间差不多只是因为多出来的两步得到的数据量并不太大。再者这两个查询其实也不是等价的，都无从比较。

Answer 5

大哥，明显第二个你先做了子查询，把两个表的数据都变小了然后关联，肯定快啊。如果inner join 两边的表也是两个子查询出来的表，肯定也快啊。