MySql:如何处理条件排序

Question

我有一个存储过程，它接受几个参数。我需要返回根据我收到的输入参数的值按特定顺序排序的结果。我面临的问题是，如果我尝试按 order by 子句编写带有 switch-case block 的单个查询，则该查询将不会使用任何索引。示例结构如下

create table test_table(
f1 int,
f2 int,
f3 int,
key (f1),
key(f2)
);

-- now insert 2M rows in the table

-- query #1
mysql> explain select f1, f2 from test_table order by f1 desc limit 1000;
+----+-------------+------------+-------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type  | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+-------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test_table | index | NULL          | f1   | 5       | NULL | 1000 | NULL  |
+----+-------------+------------+-------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

-- query #2
mysql> explain select f1, f2 from test_table order by f2 desc limit 1000;
+----+-------------+------------+-------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type  | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+-------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test_table | index | NULL          | f2   | 5       | NULL | 1000 | NULL  |
+----+-------------+------------+-------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

set @a = 1, @b = 'd';

--query #3
mysql> explain select f1, f2 from test_table
   order by
    case when @a = 1 and @b = 'd' then f1
         when @a = 2 and @b = 'd' then f2 end desc,
    case when @a = 1 and @b = 'a' then f1
         when @a = 2 and @b = 'a' then f2 end asc
      limit 1000;
+----+-------------+------------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+------------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | test_table | ALL  | NULL          | NULL | NULL    | NULL | 1995435 | Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+---------+----------------+
1 row in set (0.00 sec)

如果您看到查询 1 或查询 2，则说明输出符合预期。但是，当我尝试在运行时决定排序列时，它无法使用任何索引。实现 #3 的一种替代方法是编写 if-else block 并在那里编写单独的查询，但如果存在多种可能性，它会变得很难看。在上面的示例中，它将导致 4 个具有不同 order by 子句的相似 block

我的问题:是否有其他方法可以完成查询#3，它的优点和缺点是什么？

Answer 1

处理 SELECT 变化的最佳方法是在应用程序代码中动态构造查询。

一个常见的例子是最终用户可以指定(或不指定)各种内容。应用程序可以使用指定的内容构造一个 WHERE 子句。它甚至可以根据用户输入构造 IN 或 BETWEEN 子句等。

您的案例与ORDER BY有关。同样，应用程序代码可以非常轻松地构造所需的 ORDER BY。这将导致高效的查询。

唉，SQL 中没有任何构造可以替代它。