优化mySQL查询以更好地扩展

Question

我想查询数据库以检索名称列表(名称列表由用户在python中提供)。我查找这些名字的数据的标准如下:结果应该按照用户提供的名字列表的顺序出现，所以如果我说...WHERE name = "Bob" OR name = "Alice"，我希望Bob的结果首先出现，然后是Alice的结果。第二个标准是，如果搜索一个名字两次，那么结果也应该包含两次，所以我想要一种方法来写下...WHERE name = 'Bob' OR name = 'Bob'，以便结果也包含两次Bob的行。

我得到了以下查询:

SELECT * FROM
(SELECT *, 1 order_position FROM table WHERE name = 'Alice'
UNION ALL
SELECT *, 2 order_position FROM table WHERE name = 'Bob'
UNION ALL
SELECT *, 3 order_position FROM table WHERE name = 'Charlie'
UNION ALL 
SELECT *, 4 order_position FROM table WHERE name = 'Dan'
) r ORDER BY order_position

这个查询工作得很好，但是当用户提交数百个名字并且有数百个UNION ALL节时，查询就会变得非常慢。是否有一种方法可以在保持前面提到的两个标准的同时提高查询的性能?

Answer 1

SELECT *, CASE name WHEN 'Alice'   THEN 1
WHEN 'Bob'     THEN 2
WHEN 'Charlie' THEN 3
WHEN 'Dan'     THEN 4
END AS order_position 
FROM table 
WHERE name IN ('Alice', 'Bob', 'Charlie', 'Dan')
ORDER BY order_position;

或不加列:

SELECT *
FROM table 
WHERE name IN ('Alice', 'Bob', 'Charlie', 'Dan')
ORDER BY CASE name WHEN 'Alice'   THEN 1
WHEN 'Bob'     THEN 2
WHEN 'Charlie' THEN 3
WHEN 'Dan'     THEN 4
END;

p。对于这些名称设置ORDER BY name就足够了。

---

这如何处理重复某些结果的要求?- - - - - -威廉Renzema

如果需要重复，则必须将列表转换为行集。

SELECT table.*
FROM table
JOIN ( SELECT 1 pos, 'Alice'   name UNION ALL
SELECT 2    , 'Bob'          UNION ALL
SELECT 3    , 'Charlie'      UNION ALL
SELECT 4    , 'Bob'          UNION ALL
SELECT 5    , 'Charlie' ) names USING (name)
ORDER BY names.pos

Answer 2

您必须以某种方式使用每个名称的order_position来构建名称列表。
您可以在使用UNION ALL来保留重复名称的查询中这样做:

SELECT 'Alice' name, 1 order_position UNION ALL
SELECT 'Bob', 2 UNION ALL
SELECT 'Charlie', 3 UNION ALL
SELECT 'Dan', 4 UNION ALL
SELECT 'Alice', 1 UNION ALL
SELECT 'Bob', 2 UNION ALL
...............................

那么你所要做的就是将它加入到表中:

SELECT t.* 
FROM tablename t
INNER JOIN (
SELECT 'Alice' name, 1 order_position UNION ALL
SELECT 'Bob', 2 UNION ALL
SELECT 'Charlie', 3 UNION ALL
SELECT 'Dan', 4 UNION ALL
SELECT 'Alice', 1 UNION ALL
SELECT 'Bob', 2 UNION ALL
...............................
) n ON n.name = t.name
ORDER BY n.order_position;

在MySql 8.0+中，你可以使用CTE:

WITH cte(name, order_position) AS (VALUES 
ROW('Alice', 1), ROW('Bob', 2), ROW('Charlie', 3), 
ROW('Dan', 4), ROW('Alice', 1), ROW('Bob', 2),
...................................................
)
SELECT t.* 
FROM tablename t INNER JOIN cte c 
ON c.name = t.name
ORDER BY c.order_position;

Answer 3

如果name被索引:

WHERE name IN             ('Alice', 'Bob', 'Charlie', 'Dan')
ORDER BY FIND_IN_SET(name, 'Alice,Bob,Charlie,Dan')

注意Where和Order的语法差异。

下面的代码可能会稍微慢一些，因为它不能使用任何索引，但更简单:

WHERE    FIND_IN_SET(name, 'Alice,Bob,Charlie,Dan')
ORDER BY FIND_IN_SET(name, 'Alice,Bob,Charlie,Dan')

注意FIND_IN_SET中的限制，逗号不能在条目中使用。

在任何情况下CASE或FIND_IN_SET()都不会使用索引。(Cf"sargable"

如果有多个"Bobs"，那么它们中的每一个都与上面的效果完全相同:

name IN ('Alice', 'Bob', 'Charlie', 'Bob', 'Dan')
FIND_IN_SET(name, 'Alice,Bob,Charlie,Bob,Dan')

也就是说，所有的bob将列在所有charlie之前的输出中。此外，没有单独的行被列出两次。