很慢的处理MySQL语句，如何使它表现得更好？
我有下面的MySQL语句，当产品数量增加时，它的执行速度非常慢。目前的说明如下：
SELECT DISTINCT products.*, colors.value FROM products
LEFT OUTER JOIN product_fields colors ON colors.product_id = products.id AND colors.name = 'color'
GROUP BY products.id
ORDER BY
CASE WHEN merchant IN ('Merchant 1') THEN -1 ELSE RAND(1617116433) END,
CASE WHEN category IN ('Category 1', 'Category 2') THEN -1 ELSE RAND(1617116433) END
LIMIT 0, 30为了说明这一点:有一个product和一个product_fields表。对于每一个product，product_fields表中都有零条或多条记录。product_fields表中的字段之一是字段：name，其值为color，这是结果中唯一需要的字段值。
创建ORDER BY语句是因为我希望首先显示某个商家的所有产品，然后随机显示来自其他商家的其余产品。其次，来自某个类别(类别1和类别2)的所有产品必须首先显示在结果中。在此之后，随机显示其他类别的产品。
对于随机，我已经使用一个固定的数字来随机化所有的东西。不一定每次都是不同的。我只想要其余的产品在商品1和类别1之后随机列出，类别2的产品显示。
目前，的声明工作正常，虽然在大数据集上运行非常慢，但我认为ORDER BY正在使它变慢，但是我不知道如何解决这个问题。希望有人能在这个问题上给我指明正确的方向。
现在我已经为上面的语句运行了>>，这就是结果：
+----+-------------+--------------+------+---------------+-------------+---------+----------------------------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+-------------+---------+----------------------------------------------+-------+---------------------------------+
| 1 | SIMPLE | products | ALL | NULL | NULL | NULL | NULL | 10402 | Using temporary; Using filesort |
| 1 | SIMPLE | colors | ref | product_key | product_key | 767 | dbname.products.id | 1 | |
+----+-------------+--------------+------+---------------+-------------+---------+----------------------------------------------+-------+---------------------------------+
2 rows in set (0.02 sec)编辑2>>来澄清一些事情:使用RAND只是因为我希望在显示了来自特定“商人”和“类别”的所有产品之后，每个产品都被随机显示出来。但是下一次当用户访问这个网站的时候，订单可能是一样的，尽管我很在意。我只想让所有的，其他的产品，不要按特定的商人或类别分类。这就是RAND的意义所在。
感谢@spencer7593的精彩回答，我认为这一切归结为使用Using filesort选项对整个结果集进行排序(请参阅上面的解释)。因此，现在我如何解决这个问题，保持一种方式随机的结果，如上文一段所解释的。
回答 2
Stack Overflow用户
发布于 2015-02-04 15:34:27
表达式RAND(1617116433)将在每次计算时返回相同的常量值。对于每一行都将返回相同的确切值。也就是说，用大于-1的文字数值替换该表达式将产生一个等效的结果。
如果确实希望为每一行分配伪随机值，则需要从函数中移除种子值。您需要使用RAND()为每一行获取不同的值。
作为一个示范，比较以下方面的结果：
SELECT RAND(1617116433), RAND(1617116433), RAND(1617116433) ;
SELECT RAND(1617116433), RAND(), RAND() ;(请注意，第二条语句将返回相同的值序列，每次运行。RAND()是一个伪随机数生成器，而不是真正的随机数生成器。)
无论哪种方式，在您的查询中，函数都将对每一行进行计算，然后对整个结果集进行排序。(“解释”将显示“使用文件短”)。
最后应用LIMIT子句，对整个结果集进行排序，然后从排序集返回前30行。(这可以避免返回大量的行，但MySQL服务器仍在准备整个集合。)
这可能是你的查询“慢”的最大原因。
DISTINCT的用法有点奇怪，您已经有了一个GROUP BY子句，它确保来自产品的id是唯一的。规范模式是在colors.value子句中包括GROUP BY。
另外，是否需要从products表返回每一列？我们更希望看到要返回在SELECT列表中枚举的列列表，而不是依赖*。
对product_fields表进行适当的索引可以提高联接操作的性能。
... ON `product_fields` (`product_id`, `name`, `value`)(我们预计解释输出应该显示该表的“使用索引”。)
但是，这并不能满足访问products表中每一行的需要，并为products.id的每个不同值计算RAND()函数(两次)。
( products表上的覆盖索引也可能带来很小的好处，但我认为这是可以忽略的。)
我会像这样写这个查询，但这不能解决“大石头”性能问题：
SELECT p.id
, p.???
, p.???
, c.value
FROM (SELECT RAND(1617116433)) i
CROSS
JOIN products p
LEFT
JOIN product_fields c
ON c.product_id = p.id
AND c.name = 'color'
GROUP BY p.id, c.value
ORDER
BY CASE WHEN p.merchant IN ('Merchant 1') THEN -1 ELSE RAND() END
, CASE WHEN p.category IN ('Category 1', 'Category 2') THEN -1 ELSE RAND() END
LIMIT 0, 30Stack Overflow用户
发布于 2015-02-04 15:03:30
尝试在explain前面使用select关键字运行查询。它将告诉您使用了什么索引(如果有的话)。
索引是在MySQL中具有良好性能的关键。在本例中，您需要颜色索引(product_id，name)。
即使如此，这将始终运行一个完整的表扫描产品。您应该尝试在查询中添加限制的where-statement。
https://stackoverflow.com/questions/28324431
复制相似问题
腾讯云开发者
