MySQL 查询由于 ORDER BY 和存储函数而变慢 [英] MySQL query slow because of ORDER BY with Stored Functions

问题描述

当我删除以下查询中的 ORDER BY 时，我的查询从 15 秒变为 0.05 秒:

My query goes from 15 seconds to 0.05 seconds when I remove the ORDER BY in the following query:

简化版:

SELECT field1, fiedl2, field 3,  
    FUNC1(1, 2) AS score1,  
    FUNC2(1, 2) AS score2,  
    FUNC3(1, 2) AS score3,  
    FUNC4(1, 2) AS score4  
FROM table  
WHERE field1 = 1  
ORDER BY (score1 * 1 + score2 * 2 + score3 * 2 + score4 * 4) DESC;

我有几个计算子分数的存储函数.除了我必须根据总分对结果进行排序.在 ORDER BY 中，我使用 * 2 为子分数添加一些权重以影响总分.

I have a couple of stored functions that calculate sub-scores. Except I have to order the result based on the total score. In the ORDER BY I use * 2 to add some weight to the subscores to influence the total score.

我使用 MySQL 5.6.13

I use MySQL 5.6.13

有没有人知道我如何制作 ORDER BY，但又不减慢速度?是否可以存储 score# 字段并汇总它们?

Has anybody has an idea how I can make the ORDER BY, but not slow it down? Like is it possible to and store the score# fields and sum them up?

谢谢！

推荐答案

时间的不同是因为 MySql 需要创建一个排序的临时表并填充数据.由于使用 order by 时查询的运行速度要慢得多，因此问题可能出在存储临时数据的磁盘中.你没有提到你从这个查询返回了多少行.您也可以尝试手动执行 MySql 可能正在执行的步骤，因此创建一个带有主键 (order_by_result int, n int auto_increment) 的临时表并将您的选择结果插入其中:

The difference in time is because MySql needs to create a sorted temp table and fill it with data. As your query is running much slower when using order by, the problem might be in disk where temp data is stored. You haven't mentioned how many rows you are returning from this query. You may also try manually perform the steps that MySql is probably doing, so create a temp table with primary key (order_by_result int, n int auto_increment) and insert into it your select results:

Insert into t(order_by_result, n, ...)
select (score1 * 1 + score2 * 2 + score3 * 2 + score4 * 4),null,...

并检查 hpw 是否快速运行 - 如果问题出在您的存储上，您也可以通过这种方式进行检查.

and check hpw fast it runs - you may also check this way if the problem lies in your storage.

这篇关于MySQL 查询由于 ORDER BY 和存储函数而变慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！