MySQL:在任何子查询中

Question

如何重构这个查询:

SELECT * FROM tbl t
WHERE (
       t.id IN <subquery1>
    OR t.id IN <subquery2>
    OR t.id IN <subquery3>
)

...变成更像下面这样的东西:

SELECT * FROM tbl t
WHERE t.id IN (<subquery1> OR <subquery2> OR <subquery3>)

注意:所有 3 个子查询都从相同的 tbl t 中进行选择，但是它们各自选择了一个不同的列。

通过一些具体示例进一步阐明子查询:

• 子查询 1:SELECT col1 FROM tbl WHERE value=100
• 子查询 2:SELECT col2 FROM tbl WHERE value=200
• 子查询 3:SELECT col3 FROM tbl WHERE value=300

表结构:

CREATE TABLE tbl (
    id      INTEGER   PRIMARY KEY,
    col1    INTEGER   not null,
    col2    INTEGER   not null,
    col3    INTEGER   not null,
    value   INTEGER   not null
);

Answer 1

对只有字段 i(和 2621441 行)的 integers 表进行快速测试:

SELECT i 
FROM integers
WHERE (
   i in (SELECT i FROM integers WHERE i = 100)
   OR
   i in (SELECT i FROM integers WHERE i = 200)
   OR 
   i in (SELECT i FROM integers WHERE i = 1000)
)
ORDER BY i;

+----+-------------+----------+------------+-------+---------------+---------+---------+-------+---------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows    | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+---------+----------+--------------------------+
|  1 | PRIMARY     | integers | NULL       | index | NULL          | PRIMARY | 4       | NULL  | 2615753 |   100.00 | Using where; Using index |
|  4 | SUBQUERY    | integers | NULL       | const | PRIMARY       | PRIMARY | 4       | const |       1 |   100.00 | Using index              |
|  3 | SUBQUERY    | integers | NULL       | const | PRIMARY       | PRIMARY | 4       | const |       1 |   100.00 | Using index              |
|  2 | SUBQUERY    | integers | NULL       | const | PRIMARY       | PRIMARY | 4       | const |       1 |   100.00 | Using index              |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+---------+----------+--------------------------+
4 rows in set, 1 warning (0.01 sec)

以上返回结果大约需要 2 秒。

SELECT i 
FROM integers
WHERE i in (
   SELECT i FROM integers WHERE i = 100
   UNION ALL
   SELECT i FROM integers WHERE i = 200
   UNION ALL
   SELECT i FROM integers WHERE i = 1000
)
ORDER BY i;

+----+--------------------+----------+------------+-------+---------------+---------+---------+-------+---------+----------+--------------------------+
| id | select_type        | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows    | filtered | Extra                    |
+----+--------------------+----------+------------+-------+---------------+---------+---------+-------+---------+----------+--------------------------+
|  1 | PRIMARY            | integers | NULL       | index | NULL          | PRIMARY | 4       | NULL  | 2615753 |   100.00 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | integers | NULL       | const | PRIMARY       | PRIMARY | 4       | const |       1 |   100.00 | Using index              |
|  3 | DEPENDENT UNION    | integers | NULL       | const | PRIMARY       | PRIMARY | 4       | const |       1 |   100.00 | Using index              |
|  4 | DEPENDENT UNION    | integers | NULL       | const | PRIMARY       | PRIMARY | 4       | const |       1 |   100.00 | Using index              |
+----+--------------------+----------+------------+-------+---------------+---------+---------+-------+---------+----------+--------------------------+
4 rows in set, 1 warning (0.00 sec)

以上返回结果大约需要 1.35 秒

SELECT i 
FROM integers
WHERE i in (
   SELECT i FROM integers WHERE i = 100
   UNION
   SELECT i FROM integers WHERE i = 200
   UNION 
   SELECT i FROM integers WHERE i = 1000
)
ORDER BY i;

+----+--------------------+--------------+------------+-------+---------------+---------+---------+-------+---------+----------+--------------------------+
| id | select_type        | table        | partitions | type  | possible_keys | key     | key_len | ref   | rows    | filtered | Extra                    |
+----+--------------------+--------------+------------+-------+---------------+---------+---------+-------+---------+----------+--------------------------+
|  1 | PRIMARY            | integers     | NULL       | index | NULL          | PRIMARY | 4       | NULL  | 2615753 |   100.00 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | integers     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |       1 |   100.00 | Using index              |
|  3 | DEPENDENT UNION    | integers     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |       1 |   100.00 | Using index              |
|  4 | DEPENDENT UNION    | integers     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |       1 |   100.00 | Using index              |
| NULL | UNION RESULT       | <union2,3,4> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    NULL |     NULL | Using temporary          |
+----+--------------------+--------------+------------+-------+---------------+---------+---------+-------+---------+----------+--------------------------+
5 rows in set, 1 warning (0.00 sec)

上面的结果在 1.6 秒内返回。

“赢家”是 UNION ALL 😉