代码之家 › 专栏 › 技术社区 › mson

MySQL与SQL Server Express性能比较

comparison performance sql-server mysql

mson · 技术社区 · 17 年前

我有一个有点复杂的查询,大约有10万行。

查询在SQL Server Express中运行13秒(在我的开发设备上运行)

在MySQL 5.1上运行具有相同索引和表的相同查询需要超过15分钟的时间(在我的生产环境中运行——功能更强大,经过100%资源测试),有时查询会因内存不足错误而导致机器崩溃。

我在MySQL中做错了什么?为什么需要这么长时间?

select e8.*
from table_a e8
inner join (
    select max(e6.id) as id, e6.category, e6.entity, e6.service_date
    from (
        select e4.* 
        from table_a e4
        inner join (
            select max(e2.id) as id, e3.rank, e2.entity, e2.provider_id, e2.service_date
            from table_a e2
            inner join (
                select min(e1.rank) as rank, e1.entity, e1.provider_id, e1.service_date
                from table_a e1
                where e1.site_id is not null
                group by e1.entity, e1.provider_id, e1.service_date 
            ) as e3
            on e2.rank= e3.rank
            and e2.entity = e3.entity
            and e2.provider_id = e3.provider_id
            and e2.service_date = e3.service_date
            and e2.rank= e3.rank
            group by e2.entity, e2.provider_id, e2.service_date, e3.rank
        ) e5
        on e4.id = e5.id
        and e4.rank= e5.rank                            
    ) e6
    group by e6.category, e6.entity, e6.service_date 
) e7
on e8.id = e7.id and e7.category = e8.category

5 回复 | 直到 17 年前

Cade Roux 17 年前

我最初试图发布这个答案给你删除的问题,但这并不表明这是MySQL的问题。我仍然会继续使用SQL Server使用CTE重构查询,然后转换回嵌套查询(如果还有)。很抱歉格式问题,Jeff Atwood给我发了原始发布的文本,我不得不重新格式化。

没有数据、预期结果和好的名称是很难做到的,但我会将所有嵌套查询转换为CTE,将它们堆叠起来,有意义地命名并重构——从排除不使用的列开始。删除列不会带来改进,因为优化器非常聪明,但它会让你有能力改进你的查询,可能会排除部分或全部CTE。我不确定你的代码在做什么,但你可能会发现新的RANK()类型函数很有用,因为你似乎在使用一种具有所有这些自连接的回寻类型的模式。

所以,从这里开始吧。我已经为您研究了e7的改进,e7中未使用的列可能表明存在缺陷或对分组可能性的思考不完整,但如果这些列真的是不必要的,那么这可能会一直渗透到e6、e5和e3中的逻辑中。如果e7中的分组是正确的,那么您可以在结果和连接中删除除max(id)之外的所有内容。我不明白为什么每个类别都有多个MAX(id),因为这会在你加入时使你的结果相乘,所以MAX(id)在类别中必须是唯一的,在这种情况下,类别在加入中是多余的。

WITH e3 AS (
select min(e1.rank) as rank,
e1.entity,
e1.provider_id,
e1.service_date
from table_a e1
where e1.site_id is not null
group by e1.entity, e1.provider_id, e1.service_date
)

,e5 AS (
select max(e2.id) as id,
e3.rank,
e2.entity,
e2.provider_id,
e2.service_date
from table_a e2
inner join e3
on e2.rank= e3.rank
and e2.entity = e3.entity
and e2.provider_id = e3.provider_id
and e2.service_date = e3.service_date
and e2.rank= e3.rank
group by e2.entity, e2.provider_id, e2.service_date, e3.rank
)

,e6 AS (
select e4.* -- switch from * to only the columns you are actually using
from table_a e4
inner join e5
on e4.id = e5.id
and e4.rank= e5.rank
)

,e7 AS (
select max(e6.id) as id, e6.category -- unused, e6.entity, e6.service_date
from e6
group by e6.category, e6.entity, e6.service_date
-- This instead
-- select max(e6.id) as id
-- from e6
-- group by e6.category, e6.entity, e6.service_date
)

select e8.*
from table_a e8
inner join e7
on e8.id = e7.id
and e7.category = e8.category
-- THIS INSTEAD on e8.id = e7.id

dkretz 17 年前

如果有效的索引可用,100000行不应该花费13秒。我怀疑这种差异是由于SQL server的查询优化器比MySQL强大得多。MySQL所拥有的更多的是SQL解析器,而不是优化器。

对于初学者来说,您需要提供更多信息——所有参与表的完整模式,以及每个表的完整索引列表。

然后,了解数据是关于什么的,以及查询的目的是什么。一个用例级别的东西。

duffymo 17 年前

与双方解释计划,看看有什么不同,这会很有趣。我不确定这是不是苹果和橙子的比较,但我很好奇。

我不知道如果 this 可以提供帮助,但这是首次搜索“mysql查询优化器”。

duffymo 17 年前

这是 another one 这可能是值得的。

Hugues Van Landeghem 17 年前

据我所知,唯一拥有CTE的开源数据库是Firebird( http://www.firebirdsql.org/rlsnotesh/rlsnotes210.html#rnfb210-cte )

我认为Postgres将在8.4中