mysql - 如何为 MASSIVE MySQL 表上的计算列优化 ORDER BY

Question

我有一个非常大的(超过 80 万行)非规范化 MySQL 表。简化的架构如下所示:

+-----------+-------------+--------------+--------------+|    ID     |   PARAM1    |   PARAM2     |   PARAM3     |+-----------+-------------+--------------+--------------+|    1      |   .04       |    .87       |    .78       |+-----------+-------------+--------------+--------------+|    2      |   .12       |    .02       |    .76       |+-----------+-------------+--------------+--------------+|    3      |   .24       |    .92       |    .23       |+-----------+-------------+--------------+--------------+|    4      |   .65       |    .12       |    .01       |+-----------+-------------+--------------+--------------+|    5      |   .98       |    .45       |    .65       |+-----------+-------------+--------------+--------------+

I'm trying to see if there's a way to optimize a query in which I apply a weight to each PARAM column (where weight is between 0 and 1) and then average them to come up with a computed value SCORE. Then I want to ORDER BY that computed SCORE column.

For example, assuming the weighting for PARAM1 is .5, the weighting for PARAM2 is .23 and the weighting for PARAM3 is .76, you would end up with something similar to:

SELECT ID, ((PARAM1 * .5) + (PARAM2 * .23) + (PARAM3 * .76)) / 3 AS SCORE 

ORDER BY SCORE DESC LIMIT 10

通过一些适当的索引，这对于基本查询来说是很快的，但是我想不出一个好方法来加快对这么大的表的上述查询。

详细信息:

• 每个PARAM值都在0到1之间
• 应用于 PARAMS 的每个权重都在 0 到 1 秒之间

--编辑--

问题的简化版本如下。

这会在合理的时间内运行:

SELECT value1, value2 
FROM sometable 
WHERE id = 1 
ORDER BY value2

这不会在合理的时间内运行:

 SELECT value1, (value2 * an_arbitrary_float) as value3 
 FROM sometable 
 WHERE id = 1 
 ORDER BY value3

使用上面的例子，是否有任何解决方案可以让我在不提前计算 value3 的情况下执行 ORDER BY？

Answer 1

我发现 2 个(有点明显)有助于将此查询速度提高到令人满意的水平:

1. 尽量减少需要排序的行数。通过使用“id”字段上的索引和子选择来首先修剪记录数，计算列上的文件排序并没有那么糟糕。即:

   SELECT t.value1, (t.value2 * an_arbitrary_float) as SCORE
   FROM (SELECT * FROM sometable WHERE id = 1) AS t 
   ORDER BY SCORE DESC
   

2. 尝试增加sort_buffer_size在 my.conf 中加速这些文件排序。