时间戳

首页 > 解决方案 > MySQL：在任何子查询中

mysql - MySQL：在任何子查询中

问题描述

如何重组此查询：

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中选择，但它们各自选择不同的列。

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

表结构：

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

标签： mysqlsqlselectsubqueryin-subquery

解决方案

integers对只有字段i（和 2621441 行）的表进行快速测试：

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

推荐阅读