如何针对一个海量的MySQL表中的计算列进行ORDER BY优化

Question

我有一张非常大（8000万+行）的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       |
+-----------+-------------+--------------+--------------+

我正在尝试优化一个查询，该查询对每个PARAM列应用权重（权重在0到1之间），然后平均它们以得出计算的分数SCORE。 然后我想按计算出的SCORE列进行排序。

例如，假设PARAM1的权重为.5，PARAM2的权重为.23，PARAM3的权重为.76，则会得到类似以下内容：

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

ORDER BY SCORE DESC LIMIT 10

通过适当的索引，基本查询可以快速完成，但我无法想出一个好的方法来加快对如此大的表格进行上述查询。

细节：

• 每个PARAM值介于0和1之间
• 应用于PARAMS的每个权重都介于0和1 s之间

--编辑--

问题的简化版本如下。

这个运行速度比较合理：

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？

- cbrumelle

---

您可以使用“010001”按钮格式化代码。<code>标签不被识别。我已经为您完成了。 - Álvaro González

你目前有哪些索引？EXPLAIN 对此有何看法？我意识到你可能无法在精简版中获取这些信息，但如果可能的话，这将非常有帮助。 - Brian Hooper

我在这个表上有几个索引，它们实际上被用于其他查询。ID 有一个索引，然后有 (ID, PARAM1) 和 (ID, PARAM2) 的索引，让我可以运行查询，在给定的 ID 下获取按 PARAM1 排序的前 10 行。从 EXPLAIN 中看，问题是在使用“ORDER BY”时发生的文件排序，该排序在动态计算的（未索引的）列上进行。我不确定是否有解决方案。 - cbrumelle

+1，问题提得好，非常清晰。欢迎来到 SO。 - Jim Garrison

请查看这里的解决方案，虽然有些复杂：http://dba.stackexchange.com/questions/11841/how-can-i-speed-up-a-query-that-orders-by-a-calculated-field - allenwlee

Answer 1

我发现了两个（有点显而易见）的东西，可以帮助加快这个查询的速度，使其达到令人满意的水平：

1. Minimize the number of rows that need to be sorted. By using an index on the 'id' field and a subselect to trim the number of records first, the file sort on the computed column is not that bad. Ie:

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

2. Try increasing sort_buffer_size in my.conf to speed up those filesorts.

- cbrumelle

Answer 2

我知道这个问题很老，但最近我遇到了这个问题，我想到的解决方法是使用派生表。在派生表中，创建你的计算列，在外部查询中，你可以通过它进行排序。对于我的工作负载，它似乎运行速度快得多（数量级）。

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

- siride

Answer 3

MySQL缺少许多能帮助你解决这个问题的吸引人的功能。也许你可以添加一个带有计算排名的列，对其创建索引，并编写一些触发器来定期更新它。

- Álvaro González

---

问题在于用于计算SCORE的加权值是基于用户输入的 - 直到运行时才知道。因此，没有办法（轻松地）提前计算分数。 一个可能的解决方案是将PARAMS的加权从浮点数更改为已知一组值（0、.2、.4、.6、.8），但是那些计算出的值的存储要求将是巨大的。 - cbrumelle