MySQLサブクエリの原理は何ですか

01 序文

サブクエリの一般的な説明は、クエリ ステートメント内に別のクエリ ステートメントがネストされているというものです。日常業務で MySQL に触れる学生は皆、サブクエリを知っているか、使用したことがあると思いますが、サブクエリはどのように実装されているのでしょうか? クエリの効率はどれくらいですか? これらについてよくわかっていない人が多いと思います。これら 2 つの問題について見てみましょう一緒に、一度だけ。

02 準備内容

このタスクでは 3 つのテーブルを使用する必要があります。これら 3 つのテーブルにはすべて主キーのインデックス ID とインデックス a がありますが、単語 b にはインデックスがありません。ストアド プロシージャ idata() は、100 行のデータをテーブル t1 に挿入し、1000 行のデータをテーブル t2 および t3 に挿入します。テーブル作成ステートメントは次のとおりです。

CREATE TABLE `t1` (
    `id` INT ( 11 ) NOT NULL,
    `t1_a` INT ( 11 ) DEFAULT NULL,
    `t1_b` INT ( 11 ) DEFAULT NULL,
PRIMARY KEY ( `id` ),
KEY `idx_a` ( `t1_a` )) ENGINE = INNODB;

CREATE TABLE `t2` (
    `id` INT ( 11 ) NOT NULL,
    `t2_a` INT ( 11 ) DEFAULT NULL,
    `t2_b` INT ( 11 ) DEFAULT NULL,
PRIMARY KEY ( `id` ),
KEY `idx_a` ( `t2_a` )) ENGINE = INNODB;

CREATE TABLE `t3` (
    `id` INT ( 11 ) NOT NULL,
    `t3_a` INT ( 11 ) DEFAULT NULL,
    `t3_b` INT ( 11 ) DEFAULT NULL,
PRIMARY KEY ( `id` ),
KEY `idx_a` ( `t3_a` )) ENGINE = INNODB;

-- 向t1添加100条数据
-- drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100)do
        insert into t1 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

-- 向t2添加1000条数据
drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=101;
  while(i<=1100)do
        insert into t2 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

-- 向t2添加1000条数据，且t3_a列的值为倒叙
drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=101;
  while(i<=1100)do
        insert into t3 values(i, 1101-i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

03 サブクエリの文法形式と分類

3.1 文法形式

サブクエリの構文では、サブクエリが外部クエリ内に存在できることが規定されています。さまざまな位置が表示されますが、ここでは一般的に使用されるいくつかの位置のみを紹介します。 FROM 句の

3.1.1

(例: SELECT m, n FROM (SELECT m2 1 AS m) , n2 AS n FROM t2 WHERE m2 > 2) AS t;

この例のサブクエリは次のとおりです: (SELECT m2 1 AS m, n2 AS n FROM t2 WHERE m2 > ; 2 ) のように、FROM 句に置かれたサブクエリはテーブルに相当しますが、通常使用するテーブルとは少し異なり、サブクエリの結果セットから構成されるこのテーブルを派生テーブルと呼びます。

3.1.2 WHERE 句または IN 句内の

例: SELECT * FROM t1 WHERE m1 = (SELECT MIN(m2) FROM t2);

SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);

その他には、SELECT 句、ORDER BY 句、GROUP BY 句が含まれますが、構文は次のとおりです。サポートされていますが、あまり意味がないので、これらの状況については詳しく説明しません。

3.2 分類

3.2.1 返された結果セットに従って

スカラー サブクエリを区別する単一の値のみを返すサブクエリは、次のようにスカラー サブクエリと呼ばれます。

SELECT * FROM t1 WHERE m1 = (SELECT m1 FROM t1 LIMIT 1);

Row サブクエリは 1 つのレコードのみを返すサブクエリですが、このレコードは複数の列が含まれます (列が 1 つだけ含まれる場合は、スカラー サブクエリになります)。例: SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);

列サブクエリは 1 つの列のデータのみを返しますが、これは列 データには複数のレコードが含まれている必要があります (レコードが 1 つだけ含まれている場合は、スカラー サブクエリになります)。例: SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);

テーブル サブクエリとは、サブクエリの結果に多数のレコードと多数の列の両方が含まれることを意味します。たとえば、:

SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2);

(SELECT m2, n2 FROM t2) はテーブルのサブクエリが必要です行サブクエリでは、LIMIT 1 を使用して、サブクエリの結果が 1 つのレコードだけを持つようにします。

3.2.2 外部クエリとの関係に応じて無関係なサブクエリを区別する

無関係なサブクエリとは、外部クエリの値に依存せずにサブクエリを独立して実行して結果を生成できることを意味します。このサブクエリを無関係なサブクエリと呼ぶことができます。
相関サブクエリは、外部クエリの値に依存する必要があるサブクエリであり、相関サブクエリと呼ばれます。例: SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 WHERE n1 = n2);

04MySQL でのサブクエリの実行方法

4.1 スカラー サブクエリ、行サブクエリの実行方法

4.1.1 無相関サブクエリ

次のクエリ文:

mysql root@localhost:test> explain select * from t1 where t1_a = (select t2_a from t2 limit 1);
+----+-------------+-------+-------+---------------+-------+---------+--------+------+-------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref    | rows | Extra       |
+----+-------------+-------+-------+---------------+-------+---------+--------+------+-------------+
| 1  | PRIMARY     | t1    | ref   | idx_a         | idx_a | 5       | const  | 1    | Using where |
| 2  | SUBQUERY    | t2    | index | <null>        | idx_a | 5       | <null> | 1000 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+--------+------+-------------+

その実行方法:

最初に個別に実行 (t2 制限 1 から t2_a を選択) このサブクエリ。

次に、前のサブクエリの結果を外部クエリのパラメーターとして使用し、外部クエリ select * from t1 where t1_a = ... を実行します。

言い換えると、無関係なスカラー サブクエリまたは行サブクエリを含むクエリ ステートメントの場合、MySQL は、2 つの単一テーブル クエリと同様に、外側のクエリとサブクエリを独立して実行します。

4.1.2 関連するサブクエリ

たとえば、以下のクエリ:

mysql root@localhost:test> explain select * from t1 where t1_a = (select t2_a from t2 where t1.t1_b=t2.t2_b  limit 1);
+----+--------------------+-------+------+---------------+--------+---------+--------+------+-------------+
| id | select_type        | table | type | possible_keys | key    | key_len | ref    | rows | Extra       |
+----+--------------------+-------+------+---------------+--------+---------+--------+------+-------------+
| 1  | PRIMARY            | t1    | ALL  | <null>        | <null> | <null>  | <null> | 100  | Using where |
| 2  | DEPENDENT SUBQUERY | t2    | ALL  | <null>        | <null> | <null>  | <null> | 1000 | Using where |
+----+--------------------+-------+------+---------------+--------+---------+--------+------+-------------+

実行方法は次のようになります:

外層からの最初のクエリt1 テーブルからレコードを取得する: この例では、まず t1 テーブルからレコードを取得します。

次に、前の手順で取得したレコードからサブクエリに含まれる値、つまり t1 テーブルの t1.t1_b 列の値を見つけて、サブクエリを実行します。

最後に、サブクエリのクエリ結果に基づいて、外側のクエリの WHERE 句の条件が true であるかどうかを検出し、true であれば外側のクエリのレコードを結果に追加します。設定されている場合、それ以外の場合は破棄されます。

次に、t1 内のすべてのレコードが一致するまで、上記の手順を繰り返します。

4.2 IN サブクエリ

4.2.1 マテリアライズ

サブクエリの結果セット内のレコード数が非常に少ない場合、サブクエリと外部クエリが考慮されます。 2 つの個別の単一テーブル クエリの効率は非常に高いですが、サブクエリを個別に実行した後に結果セットが多すぎると、次の問題が発生します。

結果セットが多すぎます。 ~

外部クエリの場合、サブクエリの結果セットが多すぎる場合は、IN 句のパラメータが多すぎることを意味し、結果は次のようになります。

1）无法有效的使用索引，只能对外层查询进行全表扫描。

2）在对外层查询执行全表扫描时，由于 IN 子句中的参数太多，这会导致检测一条记录是否符合和 IN 子句中的参数匹配花费的时间太长。

因此，可以将非相关子查询的结果集写入临时表中，而不直接将其作为外层查询的参数。写入临时表的过程是这样的：

该临时表的列就是子查询结果集中的列。

写入临时表的记录会被去重，让临时表变得更小，更省地方。

一般情况下子查询结果集不大时，就会为它建立基于内存的使用 Memory 存储引擎的临时表，而且会为该表建立哈希索引。

如果子查询的结果集非常大，超过了系统变量 tmp_table_size或者 max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为 B+ 树索引。

将子查询结果集中的记录保存到临时表中的过程被称为物化（Materialize）。我们可以称存储子查询结果集的临时表为物化表，以便更加便利。正因为物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有 B+ 树索引），通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。

mysql root@localhost:test> explain select * from t3 where t3_a in (select t2_a from t2);
+----+--------------+-------------+--------+---------------+------------+---------+--------------+------+-------------+
| id | select_type  | table       | type   | possible_keys | key        | key_len | ref          | rows | Extra       |
+----+--------------+-------------+--------+---------------+------------+---------+--------------+------+-------------+
| 1  | SIMPLE       | t3          | ALL    | idx_a         | <null>     | <null>  | <null>       | 1000 | Using where |
| 1  | SIMPLE       | <subquery2> | eq_ref | <auto_key>    | <auto_key> | 5       | test.t3.t3_a | 1    | <null>      |
| 2  | MATERIALIZED | t2          | index  | idx_a         | idx_a      | 5       | <null>       | 1000 | Using index |
+----+--------------+-------------+--------+---------------+------------+---------+--------------+------+-------------+

其实上边的查询就相当于表 t3 和子查询物化表进行内连接：

mysql root@localhost:test> explain select * from t3 left join t2 on t3.t3_a=t2.t2_a;
+----+-------------+-------+------+---------------+--------+---------+--------------+------+--------+
| id | select_type | table | type | possible_keys | key    | key_len | ref          | rows | Extra  |
+----+-------------+-------+------+---------------+--------+---------+--------------+------+--------+
| 1  | SIMPLE      | t3    | ALL  | <null>        | <null> | <null>  | <null>       | 1000 | <null> |
| 1  | SIMPLE      | t2    | ref  | idx_a         | idx_a  | 5       | test.t3.t3_a | 1    | <null> |
+----+-------------+-------+------+---------------+--------+---------+--------------+------+--------+

此时 MySQL 查询优化器会通过运算来选择成本更低的方案来执行查询。

虽然，上面通过物化表的方式，将IN子查询转换成了联接查询，但还是会有建立临时表的成本，能不能不进行物化操作直接把子查询转换为连接呢？直接转换肯定不行。
-- 这里我们先构造了3条记录，其实也是构造不唯一的普通索引

+------+------+------+
| id   | t2_a | t2_b |
+------+------+------+
| 1100 | 1000 | 1000 |
| 1101 | 1000 | 1000 |
| 1102 | 1000 | 1000 |
+------+------+------+
-- 加限制条件where t2.id>=1100是为了减少要显示的数据
mysql root@localhost:test> select * from t3 where t3_a in (select t2_a from t2 where t2.id>=1100);
+-----+------+------+
| id  | t3_a | t3_b |
+-----+------+------+
| 101 | 1000 | 101  |
+-----+------+------+
1 row in set
Time: 0.016s
mysql root@localhost:test> select * from t3 left join t2 on t3.t3_a=t2.t2_a where t2.id>=1100;
+-----+------+------+------+------+------+
| id  | t3_a | t3_b | id   | t2_a | t2_b |
+-----+------+------+------+------+------+
| 101 | 1000 | 101  | 1100 | 1000 | 1000 |
| 101 | 1000 | 101  | 1101 | 1000 | 1000 |
| 101 | 1000 | 101  | 1102 | 1000 | 1000 |
+-----+------+------+------+------+------+
3 rows in set
Time: 0.018s

所以说 IN 子查询和表联接之间并不完全等价。而我们需要的是另一种叫做半联接 (semi-join) 的联接方式 ：对于 t3 表的某条记录来说，我们只关心在 t2 表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配，最终的结果集中也只保留 t3 表的记录。

注意：semi-join 只是在 MySQL 内部采用的一种执行子查询的方式，MySQL 并没有提供面向用户的 semi-join 语法。

4.2.2 半联接的实现：

• Table pullout （子查询中的表上拉）

当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表上拉到外层查询的 FROM 子句中，并把子查询中的搜索条件合并到外层查询的搜索条件中，比如这个：

mysql root@localhost:test> select * from t3 where t3_a in (select t2_a from t2 where t2.id=999)
+-----+------+------+
| id  | t3_a | t3_b |
+-----+------+------+
| 102 | 999  | 102  |
+-----+------+------+
1 row in set
Time: 0.024s
mysql root@localhost:test> select * from t3 join t2 on t3.t3_a=t2.t2_a where t2.id=999;
+-----+------+------+-----+------+------+
| id  | t3_a | t3_b | id  | t2_a | t2_b |
+-----+------+------+-----+------+------+
| 102 | 999  | 102  | 999 | 999  | 999  |
+-----+------+------+-----+------+------+
1 row in set
Time: 0.028s
mysql root@localhost:test> explain select * from t3 where t3_a in (select t2_a from t2 where t2.id=999)
+----+-------------+-------+-------+---------------+---------+---------+-------+------+--------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+--------+
| 1  | SIMPLE      | t2    | const | PRIMARY,idx_a | PRIMARY | 4       | const | 1    | <null> |
| 1  | SIMPLE      | t3    | ref   | idx_a         | idx_a   | 5       | const | 1    | <null> |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+--------+

• FirstMatch execution strategy （首次匹配）

FirstMatch 是一种最原始的半连接执行方式，跟相关子查询的执行方式是一样的，就是说先取一条外层查询的中的记录，然后到子查询的表中寻找符合匹配条件的记录，如果能找到一条，则将该外层查询的记录放入最终的结果集并且停止查找更多匹配的记录，如果找不到则把该外层查询的记录丢弃掉。然后再开始取下一条外层查询中的记录，重复上边这个过程。

mysql root@localhost:test> explain select * from t3 where t3_a in (select t2_a from t2 where t2.t2_a=1000)
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------+
| 1  | SIMPLE      | t3    | ref  | idx_a         | idx_a | 5       | const | 1    | <null>                      |
| 1  | SIMPLE      | t2    | ref  | idx_a         | idx_a | 5       | const | 4    | Using index; FirstMatch(t3) |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------+

• DuplicateWeedout execution strategy （重复值消除）

转换为半连接查询后，t3 表中的某条记录可能在 t2 表中有多条匹配的记录，所以该条记录可能多次被添加到最后的结果集中，为了消除重复，我们可以建立一个临时表，并设置主键id，每当某条 t3 表中的记录要加入结果集时，就首先把这条记录的id值加入到这个临时表里，如果添加成功，说明之前这条 t2 表中的记录并没有加入最终的结果集，是一条需要的结果；如果添加失败，说明之前这条 s1 表中的记录已经加入过最终的结果集，直接把它丢弃。

• LooseScan execution strategy （松散扫描）

这种虽然是扫描索引，但只取值相同的记录的第一条去做匹配操作的方式称之为松散扫描。

4.2.3 半联接的适用条件

当然，并不是所有包含IN子查询的查询语句都可以转换为 semi-join，只有形如这样的查询才可以被转换为 semi-join：

SELECT ... FROM outer_tables 
    WHERE expr IN (SELECT ... FROM inner_tables ...) AND ...

-- 或者这样的形式也可以：

SELECT ... FROM outer_tables 
    WHERE (oe1, oe2, ...) IN (SELECT ie1, ie2, ... FROM inner_tables ...) AND ...

用文字总结一下，只有符合下边这些条件的子查询才可以被转换为 semi-join：

1. 该子查询必须是和IN语句组成的布尔表达式，并且在外层查询的 WHERE 或者 ON 子句中出现

2. 外层查询也可以有其他的搜索条件，只不过和 IN 子查询的搜索条件必须使用AND 连接起来

3. 该子查询必须是一个单一的查询，不能是由若干查询由 UNION 连接起来的形式

4. 该子查询不能包含 GROUP BY 或者 HAVING 语句或者聚集函数

4.2.4 转为 EXISTS 子查询

可以将 IN 子查询转换为 EXISTS 子查询，无论该子查询是相关的还是不相关的。这里提供了一个通用的例子：可以将任何一个 IN 子查询转换为 EXISTS 子查询

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
-- 可以被转换为：
EXISTS (SELECT inner_expr FROM ... WHERE subquery_where AND outer_expr=inner_expr)

当然这个过程中有一些特殊情况，比如在 outer_expr 或者 inner_expr 值为 NULL 的情况下就比较特殊。如果表达式中有任何一个操作数的值为 NULL，结果通常是 NULL。例如：

mysql root@localhost:test> SELECT NULL IN (1, 2, 3);
+-------------------+
| NULL IN (1, 2, 3) |
+-------------------+
| <null>            |
+-------------------+
1 row in set

EXISTS 子查询的结果肯定是布尔型，值为 TRUE 或 FALSE。但是现实中我们大部分使用 IN 子查询的场景是把它放在 WHERE 或者 ON 子句中，而 WHERE 或者 ON 子句是不区分 NULL 和 FALSE 的，比方说：

mysql root@localhost:test> SELECT 1 FROM s1 WHERE NULL;
+---+
| 1 |
+---+
0 rows in set
Time: 0.016s
mysql root@localhost:test> SELECT 1 FROM s1 WHERE FALSE;
+---+
| 1 |
+---+
0 rows in set
Time: 0.033s

所以只要我们的IN子查询是放在 WHERE 或者 ON 子句中的，那么 IN ->  EXISTS 的转换就是没问题的。为什么需要进行转换呢？因为如果不进行转换，可能无法使用索引，例如下面的查询语句：

mysql root@localhost:test> explain select * from t3 where t3_a in (select t2_a from t2 where t2.t2_a>=999) or t3_b > 1000;
+----+-------------+-------+-------+---------------+--------+---------+--------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key    | key_len | ref    | rows | Extra                    |
+----+-------------+-------+-------+---------------+--------+---------+--------+------+--------------------------+
| 1  | PRIMARY     | t3    | ALL   | <null>        | <null> | <null>  | <null> | 1000 | Using where              |
| 2  | SUBQUERY    | t2    | range | idx_a         | idx_a  | 5       | <null> | 107  | Using where; Using index |
+----+-------------+-------+-------+---------------+--------+---------+--------+------+--------------------------+

但是将它转为 EXISTS 子查询后却可以使用到索引：

mysql root@localhost:test> explain select * from t3 where exists (select 1 from t2 where t2.t2_a>=999 and t2.t2_a=t3.t3_a) or t3_b > 1000;
+----+--------------------+-------+------+---------------+--------+---------+--------------+------+--------------------------+
| id | select_type        | table | type | possible_keys | key    | key_len | ref          | rows | Extra                    |
+----+--------------------+-------+------+---------------+--------+---------+--------------+------+--------------------------+
| 1  | PRIMARY            | t3    | ALL  | <null>        | <null> | <null>  | <null>       | 1000 | Using where              |
| 2  | DEPENDENT SUBQUERY | t2    | ref  | idx_a         | idx_a  | 5       | test.t3.t3_a | 1    | Using where; Using index |
+----+--------------------+-------+------+---------------+--------+---------+--------------+------+--------------------------+

需要注意的是，如果 IN 子查询不满足转换为 semi-join 的条件，又不能转换为物化表如果将其转换为物化表成本过高，那么就需要使用 EXISTS 查询。如果将其转换为物化表成本过高，那么就需要使用 EXISTS 查询。

以上がMySQLサブクエリの原理は何ですかの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。