子查询优化
测试版本：
percona server 5.6.19-67.0-log Percona Server (GPL), Release 67.0, Revision 618
mariadb 10.0.12-MariaDB-log MariaDB Server
总结：
1、mysql不支持子查询合并和聚合函数子查询优化，mariadb对聚合函数子查询进行物化优化；
2、mysql不支持from子句子查询优化，mariadb对from子句子查询进行子查询上拉优化；
3、mysql和mariadb对子查询展开提供有限的支持，如对主键的操作才能进行上拉子查询优化；
4、mysql不支持exists子查询优化，mariadb对exists关联子查询进行半连接优化，对exists非关联子查询没有进一步进行优化；
5、mysql和mariadb不支持not exists子查询优化；
6、mysql和mariadb对in子查询，对满足半连接语义的查询进行半连接优化，再基于代价评估进行优化，两者对半连接的代价评估选择方式有差异；
7、mysql不支持not in子查询优化，mariadb对非关联not in子查询使用物化优化，对关联not in子查询不做优化；
8、mysql和mariadb对>all非关联子查询使用max函数，<all非关联子查询使用min函数，对=all和非关联子查询使用exists优化；
9、对>some和>any非关联子查询使用min函数，对<some和<any非关联子查询使用max函数，=any和=some子查询使用半连接进行优化，对>some和>any关联子查询以及<some和<any关联子查询只有exists优化。
1、子查询合并，内部不支持
root@[test] 10:37:57>explain format=json select * from t1 where a1<4 and (exists (select a2 from t2 where t2.a2 < 5 and t2.b2 = 2) or exists (select a2 from t2 where t2.a2 < 5 and t2.b2=2))\G;
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"table": {
"message": "Impossible WHERE"
},
"optimized_away_subqueries": [
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 3,
"table": {
"table_name": "t2",
"access_type": "range",
"possible_keys": [
"t_idx_t2"
],
"key": "t_idx_t2",
"used_key_parts": [
"a2"
],
"key_length": "5",
"rows": 3,
"filtered": 100,
"index_condition": "(`test`.`t2`.`a2` < 5)",
"attached_condition": "(`test`.`t2`.`b2` = 2)"
}
}
},
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"table": {
"table_name": "t2",
"access_type": "range",
"possible_keys": [
"t_idx_t2"
],
"key": "t_idx_t2",
"used_key_parts": [
"a2"
],
"key_length": "5",
"rows": 3,
"filtered": 100,
"index_condition": "(`test`.`t2`.`a2` < 5)",
"attached_condition": "(`test`.`t2`.`b2` = 2)"
}
}
}
]
}
}
1 row in set, 1 warning (0.00 sec)
mariadb 10.0.2：
root@[test] 10:38:40>explain extended select * from t1 where a1<4 and (exists (select a2 from t2 where t2.a2 < 5 and t2.b2 = 2) or exists (select a2 from t2 where t2.a2 < 5 and t2.b2=2));
+------+-------------+-------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
| 3 | SUBQUERY | t2 | range | t_idx_t2 | t_idx_t2 | 5 | NULL | 3 | 100.00 | Using index condition; Using where |
| 2 | SUBQUERY | t2 | range | t_idx_t2 | t_idx_t2 | 5 | NULL | 3 | 100.00 | Using index condition; Using where |
+------+-------------+-------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
3 rows in set, 1 warning (0.00 sec)
root@[test] 10:38:46>show warnings;
+-------+------+-----------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where 0 |
+-------+------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
两条子查询都是独立执行，没有进行子查询合并操作。
手工合并后：只进行一次子查询扫描
root@[test] 18:46:32>explain extended select * from t1 where a1<4 and (exists (select a2 from t2 where t2.a2 < 5 and (t2.b2 = 1 or t2.b2 =3)));
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | PRIMARY | t1 | range | t_idx_t1 | t_idx_t1 | 5 | NULL | 2 | 100.00 | Using where |
| 2 | SUBQUERY | t2 | range | t_idx_t2 | t_idx_t2 | 5 | NULL | 3 | 100.00 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 18:49:26>show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where (`test`.`t1`.`a1` < 4) |
+-------+------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
在t2上只执行了一次扫描
2、子查询反嵌套(子查询展开)
子查询在from子句：
root@[test] 10:54:44>explain extended select * from t1,(select * from t2 where t2.a2 > 10) v_t2 where t1.a1 < 10 and v_t2.a2<20;
+----+-------------+------------+-------+---------------+----------+---------+------+-------+----------+----------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+-------+---------------+----------+---------+------+-------+----------+----------------------------------------------
| 1 | PRIMARY | t1 | range | t_idx_t1 | t_idx_t1 | 5 | NULL | 8 | 100.00 | Using index condition |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 5078 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| 2 | DERIVED | t2 | ALL | t_idx_t2 | NULL | NULL | NULL | 10157 | 50.00 | Using where |
+----+-------------+------------+-------+---------------+----------+---------+------+-------+----------+----------------------------------------------
3 rows in set, 1 warning (0.00 sec)
root@[test] 10:58:20>show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------
| Level | Code | Message
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------
| Note | 1003 | /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`v_t2`.`a2` AS `a2`,`v_t2`.`b2` AS `b2` from `test`.`t1` join (/* select#2 */ select `test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (`test`.`t2`.`a2` > 10)) `v_t2` where ((`test`.`t1`.`a1` < 10) and (`v_t2`.`a2` < 20)) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
子查询单独执行，子查询没有消除
mariadb 10.0.2:
root@[test] 10:54:54>explain extended select * from t1,(select * from t2 where t2.a2 > 10) v_t2 where t1.a1 < 10 and v_t2.a2<20;
+------+-------------+-------+-------+---------------+----------+---------+------+------+----------+--------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+-------+---------------+----------+---------+------+------+----------+--------------------------------------------------
| 1 | SIMPLE | t1 | range | t_idx_t1 | t_idx_t1 | 5 | NULL | 8 | 100.00 | Using index condition |
| 1 | SIMPLE | t2 | range | t_idx_t2 | t_idx_t2 | 5 | NULL | 8 | 100.00 | Using index condition; Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+-------+---------------+----------+---------+------+------+----------+--------------------------------------------------
2 rows in set, 1 warning (0.00 sec)
root@[test] 10:58:18>show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------
| Level | Code | Message
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------
| Note | 1003 | select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a1` < 10) and (`test`.`t2`.`a2` < 20) and (`test`.`t2`.`a2` > 10)) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
而mariadb对该查询进行优化，将子查询上拉，条件下推到了外层查询where条件中。
in子查询：
root@[test] 11:04:32>explain extended select * from t1 where t1.a1<100 and a1 in (select a2 from t2 where t2.a2>10);
+----+-------------+-------+-------+---------------+----------+---------+------------+------+----------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------------+------+----------+-------------------------------------------+
| 1 | SIMPLE | t2 | range | t_idx_t2 | t_idx_t2 | 5 | NULL | 88 | 100.00 | Using where; Using index; Start temporary |
| 1 | SIMPLE | t1 | ref | t_idx_t1 | t_idx_t1 | 5 | test.t2.a2 | 1 | 100.00 | End temporary |
+----+-------------+-------+-------+---------------+----------+---------+------------+------+----------+-------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 11:07:13>show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------
| Level | Code | Message
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------
| Note | 1003 | /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a1` = `test`.`t2`.`a2`) and (`test`.`t2`.`a2` < 100) and (`test`.`t2`.`a2` > 10)) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
修改条件：
root@[test] 11:31:25>explain extended select * from t1 where t1.a1<100 and a1 in (select a2 from t2 where t2.a2<10);
+----+--------------+-------------+--------+---------------+------------+---------+------------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+--------+---------------+------------+---------+------------+------+----------+--------------------------+
| 1 | SIMPLE | t1 | ALL | t_idx_t1 | NULL | NULL | NULL | 13 | 61.54 | Using where |
| 1 | SIMPLE | <subquery2> | eq_ref | <auto_key> | <auto_key> | 5 | test.t1.a1 | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | t2 | range | t_idx_t2 | t_idx_t2 | 5 | NULL | 8 | 100.00 | Using where; Using index |
+----+--------------+-------------+--------+---------------+------------+---------+------------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)
root@[test] 11:31:26>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` semi join (`test`.`t2`) where ((`<subquery2>`.`a2` = `test`.`t1`.`a1`) and (`test`.`t1`.`a1` < 100) and (`test`.`t2`.`a2` < 10))
1 row in set (0.00 sec)
ERROR:
No query specified
子查询被物化或单独执行，没有上拉。
添加主键：默认使用Innodb内建的主键
root@[test] 11:07:14>alter table t1 add primary key(a1);
Query OK, 10000 rows affected (0.07 sec)
Records: 10000 Duplicates: 0 Warnings: 0
root@[test] 11:10:41>alter table t2 add primary key(a2);
Query OK, 10000 rows affected (0.07 sec)
Records: 10000 Duplicates: 0 Warnings: 0
root@[test] 11:10:54>alter table t3 add primary key(a3);
Query OK, 10000 rows affected (0.08 sec)
Records: 10000 Duplicates: 0 Warnings: 0
root@[test] 11:10:59>explain extended select * from t1 where t1.a1<100 and a1 in (select a2 from t2 where t2.a2>10);
+----+-------------+-------+--------+------------------+---------+---------+------------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------+------------------+---------+---------+------------+------+----------+-------------+
| 1 | SIMPLE | t1 | range | PRIMARY,t_idx_t1 | PRIMARY | 4 | NULL | 88 | 100.00 | Using where |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY,t_idx_t2 | PRIMARY | 4 | test.t1.a1 | 1 | 100.00 | Using index |
+----+-------------+-------+--------+------------------+---------+---------+------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 11:12:24>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`a2` = `test`.`t1`.`a1`) and (`test`.`t1`.`a1` < 100) and (`test`.`t1`.`a1` > 10))
1 row in set (0.00 sec)
ERROR:
No query specified
只有当子查询是针对主键列时，才能把子查询上拉为内连接。
mariadb 10.0.2:
root@[test] 11:04:29>explain extended select * from t1 where t1.a1<100 and a1 in (select a2 from t2 where t2.a2>10);
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+----------+--------------------------+
| 1 | PRIMARY | t1 | range | t_idx_t1 | t_idx_t1 | 5 | NULL | 88 | 100.00 | Using index condition |
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100.00 | |
| 2 | MATERIALIZED | t2 | range | t_idx_t2 | t_idx_t2 | 5 | NULL | 88 | 100.00 | Using where; Using index |
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)
root@[test] 11:07:08>show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------
| Level | Code | Message
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------
| Note | 1003 | select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a1` < 100) and (`test`.`t2`.`a2` > 10)) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
root@[test] 11:10:08>alter table t1 add primary key(a1);
Query OK, 10000 rows affected (0.07 sec)
Records: 10000 Duplicates: 0 Warnings: 0
root@[test] 11:10:11>alter table t2 add primary key(a2);
Query OK, 10000 rows affected (0.07 sec)
Records: 10000 Duplicates: 0 Warnings: 0
root@[test] 11:10:21>alter table t3 add primary key(a3);
Query OK, 10000 rows affected (0.07 sec)
Records: 10000 Duplicates: 0 Warnings: 0
root@[test] 11:10:26>desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a1 | int(11) | NO | PRI | 0 | |
| b1 | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
root@[test] 11:10:32>explain extended select * from t1 where t1.a1<100 and a1 in (select a2 from t2 where t2.a2>10);
+------+-------------+-------+--------+------------------+---------+---------+------------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+--------+------------------+---------+---------+------------+------+----------+-------------+
| 1 | PRIMARY | t1 | range | PRIMARY,t_idx_t1 | PRIMARY | 4 | NULL | 88 | 100.00 | Using where |
| 1 | PRIMARY | t2 | eq_ref | PRIMARY,t_idx_t2 | PRIMARY | 4 | test.t1.a1 | 1 | 100.00 | Using index |
+------+-------------+-------+--------+------------------+---------+---------+------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 11:12:00>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`a2` = `test`.`t1`.`a1`) and (`test`.`t1`.`a1` < 100) and (`test`.`t1`.`a1` > 10))
1 row in set (0.00 sec)
ERROR: No query specified
mariadb也类似，但是mariadb在子查询返回表的99%行(总共10000行，返回9990行)时也进行了物化，而mysql单独执行子查询，使用临时表。
3、聚集子查询消除，内部不支持
root@[test] 11:40:42>explain extended select * from t1 where t1.a1>(select min(t2.a2) from t2);
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
| 1 | PRIMARY | t1 | ALL | t_idx_t1 | NULL | NULL | NULL | 10157 | 50.00 | Using where |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 11:40:46>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where (`test`.`t1`.`a1` > (/* select#2 */ select min(`test`.`t2`.`a2`) from `test`.`t2`))
1 row in set (0.00 sec)
ERROR:
No query specified
mariadb 10.0.2:
root@[test] 11:46:47>explain extended select * from t1 where t1.a1>(select min(t2.a2) from t2);
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
| 1 | PRIMARY | t1 | ALL | t_idx_t1 | NULL | NULL | NULL | 10157 | 50.00 | Using where |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 11:46:48>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where (`test`.`t1`.`a1` > (select min(`test`.`t2`.`a2`) from `test`.`t2`))
1 row in set (0.00 sec)
ERROR: No query specified
子查询存在，没有消除。
聚合函数子查询在in条件中：
root@[test] 11:40:48>explain extended select * from t1 where t1.a1 in (select min(t2.a2) from t2);
+----+--------------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+--------------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 11:50:54>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a1`,<exists>(/* select#2 */ select min(`test`.`t2`.`a2`) from `test`.`t2` having (<cache>(`test`.`t1`.`a1`) = <ref_null_helper>(min(`test`.`t2`.`a2`)))))
1 row in set (0.00 sec)
ERROR:
No query specified
mariadb 10.0.2：
root@[test] 11:50:16>explain extended select * from t1 where t1.a1 in (select min(t2.a2) from t2);
+------+--------------+-------------+--------+---------------+----------+---------+-------+------+----------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------+-------------+--------+---------------+----------+---------+-------+------+----------+------------------------------+
| 1 | PRIMARY | <subquery2> | system | NULL | NULL | NULL | NULL | 1 | 100.00 | |
| 1 | PRIMARY | t1 | ref | t_idx_t1 | t_idx_t1 | 5 | const | 1 | 100.00 | Using index condition |
| 2 | MATERIALIZED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+------+--------------+-------------+--------+---------------+----------+---------+-------+------+----------+------------------------------+
3 rows in set, 1 warning (0.00 sec)
root@[test] 11:50:42>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where (`test`.`t1`.`a1` = <cache>(1))
1 row in set (0.00 sec)
ERROR: No query specified
子查询存在，没有消除。mariadb进行物化优化。
4、特殊格式子查询
包括IN、ALL、ANY、SOME、EXISTS等类型的子查询
EXISTS子查询：相关子查询
root@[test] 12:03:23>explain extended select * from t1 where exists(select 1 from t2 where t1.a1=t2.a2 and t2.a2>10);
+----+--------------------+-------+------+---------------+----------+---------+------------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------+---------------+----------+---------+------------+-------+----------+--------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | ref | t_idx_t2 | t_idx_t2 | 5 | test.t1.a1 | 1 | 100.00 | Using where; Using index |
+----+--------------------+-------+------+---------------+----------+---------+------------+-------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)
root@[test] 13:38:18>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1276
Message: Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where exists(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t1`.`a1` = `test`.`t2`.`a2`) and (`test`.`t2`.`a2` > 10)))
2 rows in set (0.00 sec)
ERROR:
No query specified
子查询存在，没有进行优化。
mariadb 10.0.2：
root@[test] 13:39:51>explain extended select * from t1 where exists(select 1 from t2 where t1.a1=t2.a2 and t2.a2>10);
+------+--------------+-------------+--------+---------------+--------------+---------+------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------+-------------+--------+---------------+--------------+---------+------+-------+----------+--------------------------+
| 1 | PRIMARY | t1 | ALL | t_idx_t1 | NULL | NULL | NULL | 10157 | 50.00 | |
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100.00 | |
| 2 | MATERIALIZED | t2 | range | t_idx_t2 | t_idx_t2 | 5 | NULL | 5078 | 100.00 | Using where; Using index |
+------+--------------+-------------+--------+---------------+--------------+---------+------+-------+----------+--------------------------+
3 rows in set, 2 warnings (0.00 sec)
root@[test] 13:40:00>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1276
Message: Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`a2` > 10))
2 rows in set (0.00 sec)
ERROR: No query specified
mariadb对t2表上拉到上层与t1表进行半连接，同时进行物化优化
EXISTS子查询：非相关子查询
root@[test] 13:44:08>explain extended select * from t1 where exists(select 1 from t2 where t2.a2>10);
+----+-------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | NULL |
| 2 | SUBQUERY | t2 | range | t_idx_t2 | t_idx_t2 | 5 | NULL | 5078 | 100.00 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 13:46:09>show warnings;
+-------+------+--------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where 1 |
+-------+------+--------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mariadb 10.0.2：
root@[test] 13:44:05>explain extended select * from t1 where exists(select 1 from t2 where t2.a2>10);
+------+-------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | |
| 2 | SUBQUERY | t2 | range | t_idx_t2 | t_idx_t2 | 5 | NULL | 5078 | 100.00 | Using where; Using index |
+------+-------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 13:45:52>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where <in_optimizer>(1,exists(select 1 from `test`.`t2` where (`test`.`t2`.`a2` > 10)))
1 row in set (0.00 sec)
ERROR: No query specified
对非相关exists子查询语句，子查询还存在，内部没有做进一步优化。
总结：
mysql 5.6对exists子查询没有做进一步优化操作，而mariadb 10.0.2对exists关联子查询进行半连接优化，对exists非关联子查询没有进一步进行优化。
5、NOT EXISTS子查询：相关子查询
root@[test] 13:51:59>explain extended select * from t1 where not exists (select 1 from t2 where t1.a1=t2.a2 and t2.a2>10);
+----+--------------------+-------+------+---------------+----------+---------+------------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------+---------------+----------+---------+------------+-------+----------+--------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | ref | t_idx_t2 | t_idx_t2 | 5 | test.t1.a1 | 1 | 100.00 | Using where; Using index |
+----+--------------------+-------+------+---------------+----------+---------+------------+-------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)
root@[test] 13:55:18>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1276
Message: Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where (not(exists(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t1`.`a1` = `test`.`t2`.`a2`) and (`test`.`t2`.`a2` > 10)))))
2 rows in set (0.00 sec)
ERROR:
No query specified
子查询存在，没有进一步进行优化。
mariadb 10.0.2：
root@[test] 13:51:55>explain extended select * from t1 where not exists (select 1 from t2 where t1.a1=t2.a2 and t2.a2>10);
+------+--------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | MATERIALIZED | t2 | range | t_idx_t2 | t_idx_t2 | 5 | NULL | 5078 | 100.00 | Using where; Using index |
+------+--------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)
root@[test] 13:55:29>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1276
Message: Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where (not(((`test`.`t1`.`a1` is not null) and <expr_cache><`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (select `test`.`t2`.`a2` from `test`.`t2` where ((`test`.`t2`.`a2` is not null) and (`test`.`t2`.`a2` > 10)) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `<subquery2>`.`a2`)))))))))
2 rows in set (0.00 sec)
ERROR: No query specified
mariadb进行物化优化。
NOT EXISTS子查询：非相关子查询
root@[test] 13:59:43>explain extended select * from t1 where not exists (select 1 from t2 where t2.a2>10);
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
| 2 | SUBQUERY | t2 | range | t_idx_t2 | t_idx_t2 | 5 | NULL | 5078 | 100.00 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 13:59:46>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where 0
1 row in set (0.00 sec)
ERROR:
No query specified
mariadb 10.0.2：
root@[test] 13:55:36>explain extended select * from t1 where not exists (select 1 from t2 where t2.a2>10);
+------+-------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | |
| 2 | SUBQUERY | t2 | range | t_idx_t2 | t_idx_t2 | 5 | NULL | 5078 | 100.00 | Using where; Using index |
+------+-------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 13:59:59>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where (not(<in_optimizer>(1,exists(select 1 from `test`.`t2` where (`test`.`t2`.`a2` > 10)))))
1 row in set (0.00 sec)
ERROR: No query specified
子查询存在，没有进一步进行优化。
总结：
对not exists子查询，mysql和mariadb都没有进一步优化。
6、IN子查询：非相关子查询
root@[test] 14:03:03>explain extended select * from t1 where t1.a1 in (select a2 from t2 where t2.a2>10);
+----+--------------+-------------+--------+---------------+------------+---------+------------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+--------+---------------+------------+---------+------------+-------+----------+--------------------------+
| 1 | SIMPLE | t1 | ALL | t_idx_t1 | NULL | NULL | NULL | 10157 | 50.00 | Using where |
| 1 | SIMPLE | <subquery2> | eq_ref | <auto_key> | <auto_key> | 5 | test.t1.a1 | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | t2 | range | t_idx_t2 | t_idx_t2 | 5 | NULL | 5078 | 100.00 | Using where; Using index |
+----+--------------+-------------+--------+---------------+------------+---------+------------+-------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)
root@[test] 14:05:49>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` semi join (`test`.`t2`) where ((`<subquery2>`.`a2` = `test`.`t1`.`a1`) and (`test`.`t2`.`a2` > 10))
1 row in set (0.00 sec)
ERROR:
No query specified
mariadb 10.0.2：
root@[test] 14:03:13>explain extended select * from t1 where t1.a1 in (select a2 from t2 where t2.a2>10);
+------+--------------+-------------+--------+---------------+--------------+---------+------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------+-------------+--------+---------------+--------------+---------+------+-------+----------+--------------------------+
| 1 | PRIMARY | t1 | ALL | t_idx_t1 | NULL | NULL | NULL | 10157 | 50.00 | |
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100.00 | |
| 2 | MATERIALIZED | t2 | range | t_idx_t2 | t_idx_t2 | 5 | NULL | 5078 | 100.00 | Using where; Using index |
+------+--------------+-------------+--------+---------------+--------------+---------+------+-------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)
root@[test] 14:06:01>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`a2` > 10))
1 row in set (0.00 sec)
ERROR: No query specified
将t2表物化后，与t1表进行半连接。
非关联子查询，子查询返回标量：
root@[test] 14:20:33>explain extended select * from t1 where t1.a1 in (select a2 from t2 where t2.a2=10);
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------------+
| 1 | SIMPLE | t1 | ref | t_idx_t1 | t_idx_t1 | 5 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | t2 | ref | t_idx_t2 | t_idx_t2 | 5 | const | 1 | 100.00 | Using index; FirstMatch(t1) |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 14:21:07>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a1` = 10) and (`test`.`t2`.`a2` = 10))
1 row in set (0.00 sec)
ERROR:
No query specified
mariadb 10.0.2:
root@[test] 14:06:03>explain extended select * from t1 where t1.a1 in (select a2 from t2 where t2.a2=10);
+------+-------------+-------+------+---------------+----------+---------+-------+------+----------+------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+----------+---------+-------+------+----------+------------------------+
| 1 | PRIMARY | t2 | ref | t_idx_t2 | t_idx_t2 | 5 | const | 1 | 100.00 | Using index; LooseScan |
| 1 | PRIMARY | t1 | ref | t_idx_t1 | t_idx_t1 | 5 | const | 1 | 100.00 | |
+------+-------------+-------+------+---------------+----------+---------+-------+------+----------+------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 14:08:19>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`a2` = 10) and (`test`.`t1`.`a1` = 10))
1 row in set (0.00 sec)
ERROR: No query specified
子查询不存在，都使用半连接优化，但是在基于代价评估时选择了不同的策略，mysql选择FirstMatch,mariadb选择LooseScan；
IN子查询：相关子查询
root@[test] 14:21:09>explain extended select * from t1 where t1.a1 in (select a2 from t2 where t1.a1=10);
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------------+
| 1 | SIMPLE | t1 | ref | t_idx_t1 | t_idx_t1 | 5 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | t2 | ref | t_idx_t2 | t_idx_t2 | 5 | const | 1 | 100.00 | Using index; FirstMatch(t1) |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------------+
2 rows in set, 2 warnings (0.00 sec)
root@[test] 14:34:54>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1276
Message: Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a1` = 10) and (`test`.`t2`.`a2` = 10))
2 rows in set (0.00 sec)
ERROR:
No query specified
mariadb 10.0.2:
root@[test] 14:20:21>explain extended select * from t1 where t1.a1 in (select a2 from t2 where t1.a1=10);
+------+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------------+
| 1 | PRIMARY | t1 | ref | t_idx_t1 | t_idx_t1 | 5 | const | 1 | 100.00 | |
| 1 | PRIMARY | t2 | ref | t_idx_t2 | t_idx_t2 | 5 | const | 1 | 100.00 | Using index; FirstMatch(t1) |
+------+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------------+
2 rows in set, 2 warnings (0.00 sec)
root@[test] 14:34:36>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1276
Message: Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a1` = 10) and (`test`.`t2`.`a2` = 10))
2 rows in set (0.00 sec)
ERROR: No query specified
子查询不存在，使用半连接进行优化
总结：
mysql和mariadb对in子查询，对满足半连接语义的查询进行半连接优化，然后对满足某些条件的，再基于代价评估进行优化。
7、NOT IN子查询：非关联子查询
root@[test] 14:39:25>explain extended select * from t1 where t1.a1 not in (select a2 from t2 where t2.a2>10);
+----+-------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | SUBQUERY | t2 | range | t_idx_t2 | t_idx_t2 | 5 | NULL | 5078 | 100.00 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 14:39:51>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a2` from `test`.`t2` where (`test`.`t2`.`a2` > 10) having 1 ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on <auto_key> where ((`test`.`t1`.`a1` = `materialized-subquery`.`a2`)))))))
1 row in set (0.00 sec)
ERROR:
No query specified
mariadb 10.0.2：
root@[test] 14:35:38>explain extended select * from t1 where t1.a1 not in (select a2 from t2 where t2.a2>10);
+------+--------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | MATERIALIZED | t2 | range | t_idx_t2 | t_idx_t2 | 5 | NULL | 5078 | 100.00 | Using where; Using index |
+------+--------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 14:40:11>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where (not(<expr_cache><`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (select `test`.`t2`.`a2` from `test`.`t2` where (`test`.`t2`.`a2` > 10) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `<subquery2>`.`a2`))))))))
1 row in set (0.00 sec)
ERROR: No query specified
对子查询进行物化优化，子查询存在。
NOT IN子查询：关联子查询
root@[test] 14:44:08>explain extended select * from t1 where t1.a1 not in (select a2 from t2 where t1.a1=10);----+--------------------+-------+----------------+---------------+----------+---------+------+-------+----------+------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra|----+--------------------+-------+----------------+---------------+----------+---------+------+-------+----------+------------------------------------
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where|
| 2 | DEPENDENT SUBQUERY | t2 | index_subquery | t_idx_t2 | t_idx_t2 | 5 | func | 2 | 100.00 | Using index; Using where; Full scan on NULL key |----+--------------------+-------+----------------+---------------+----------+---------+------+-------+----------+------------------------------------
2 rows in set, 2 warnings (0.00 sec)
root@[test] 14:44:48>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1276
Message: Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`a1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a1`) in t2 on t_idx_t2 checking NULL where (`test`.`t1`.`a1` = 10) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a2`), true))))))
2 rows in set (0.00 sec)
ERROR:
No query specified
mariadb 10.0.2：
root@[test] 14:44:15>explain extended select * from t1 where t1.a1 not in (select a2 from t2 where t1.a1=10);
------+--------------------+-------+----------------+---------------+----------+---------+------+-------+----------+----------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra|
------+--------------------+-------+----------------+---------------+----------+---------+------+-------+----------+----------------------------------
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where|
| 2 | DEPENDENT SUBQUERY | t2 | index_subquery | t_idx_t2 | t_idx_t2 | 5 | func | 2 | 100.00 | Using index; Using where; Full scan on NULL key |
------+--------------------+-------+----------------+---------------+----------+---------+------+-------+----------+----------------------------------
2 rows in set, 2 warnings (0.00 sec)
root@[test] 14:44:33>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1276
Message: Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where (not(<expr_cache><`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t1`.`a1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a1`) in t2 on t_idx_t2 checking NULL where (`test`.`t1`.`a1` = 10) having trigcond(<is_not_null_test>(`test`.`t2`.`a2`))))))))
2 rows in set (0.00 sec)
ERROR: No query specified
对not in子查询，子查询存在，没有做优化。
8、ALL子查询
>ALL：不相关子查询
root@[test] 14:44:50>explain extended select * from t1 where t1.a1 > all (select a2 from t2 where t2.a2>10);
+----+-------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | SUBQUERY | t2 | range | t_idx_t2 | t_idx_t2 | 5 | NULL | 5078 | 100.00 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 14:49:47>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where <not>((`test`.`t1`.`a1` <= <max>(/* select#2 */ select `test`.`t2`.`a2` from `test`.`t2` where (`test`.`t2`.`a2` > 10))))
1 row in set (0.00 sec)
ERROR:
No query specified
mariadb 10.0.2：
root@[test] 14:44:35>explain extended select * from t1 where t1.a1 > all (select a2 from t2 where t2.a2>10);
+------+-------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | SUBQUERY | t2 | range | t_idx_t2 | t_idx_t2 | 5 | NULL | 5078 | 100.00 | Using where; Using index |
+------+-------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 14:50:05>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where <not>(<in_optimizer>(`test`.`t1`.`a1`,(<max>(select `test`.`t2`.`a2` from `test`.`t2` where (`test`.`t2`.`a2` > 10)) >= <cache>(`test`.`t1`.`a1`))))
1 row in set (0.00 sec)
ERROR: No query specified
子查询使用max函数限制，利用索引有最大值
>ALL：相关子查询
root@[test] 14:49:56>explain extended select * from t1 where t1.a1 > all (select a2 from t2 where t1.a1>10);
+----+--------------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | index | NULL | t_idx_t2 | 5 | NULL | 10157 | 100.00 | Using where; Using index |
+----+--------------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)
root@[test] 14:52:48>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1276
Message: Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where <not>(<in_optimizer>(`test`.`t1`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t1`.`a1` > 10) and <if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a1`) <= `test`.`t2`.`a2`) or isnull(`test`.`t2`.`a2`)), true)) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a2`), true))))
2 rows in set (0.00 sec)
ERROR:
No query specified
mariadb 10.0.2：
root@[test] 14:50:08>explain extended select * from t1 where t1.a1 > all (select a2 from t2 where t1.a1>10);
+------+--------------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | index | NULL | t_idx_t2 | 5 | NULL | 10157 | 100.00 | Using where; Using index |
+------+--------------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)
root@[test] 14:52:35>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1276
Message: Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t1`.`a1`,<exists>(select `test`.`t2`.`a2` from `test`.`t2` where ((`test`.`t1`.`a1` > 10) and trigcond(((<cache>(`test`.`t1`.`a1`) <= `test`.`t2`.`a2`) or isnull(`test`.`t2`.`a2`)))) having trigcond(<is_not_null_test>(`test`.`t2`.`a2`))))))
2 rows in set (0.00 sec)
ERROR: No query specified
对关联>all子查询，子查询存在，使用exists 方式优化。
=ALL：非相关子查询
root@[test] 14:56:16>explain extended select * from t1 where t1.a1=all(select a2 from t2 where t2.a2=10);
+----+--------------------+-------+------+---------------+----------+---------+-------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------+---------------+----------+---------+-------+-------+----------+--------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | ref | t_idx_t2 | t_idx_t2 | 5 | const | 1 | 100.00 | Using where; Using index |
+----+--------------------+-------+------+---------------+----------+---------+-------+-------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 14:56:20>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where <not>(<in_optimizer>(`test`.`t1`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a2` = 10) and <if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a1`) <> 10) or <cache>(isnull(10))), true)) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a2`), true))))
1 row in set (0.00 sec)
ERROR:
No query specified
mariadb 10.0.2：
root@[test] 14:52:37>explain extended select * from t1 where t1.a1=all(select a2 from t2 where t2.a2=10);
+------+--------------------+-------+------+---------------+----------+---------+-------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------------+-------+------+---------------+----------+---------+-------+-------+----------+--------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | ref | t_idx_t2 | t_idx_t2 | 5 | const | 1 | 100.00 | Using where; Using index |
+------+--------------------+-------+------+---------------+----------+---------+-------+-------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 14:56:29>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t1`.`a1`,<exists>(select `test`.`t2`.`a2` from `test`.`t2` where ((`test`.`t2`.`a2` = 10) and trigcond(((<cache>(`test`.`t1`.`a1`) <> `test`.`t2`.`a2`) or isnull(`test`.`t2`.`a2`)))) having trigcond(<is_not_null_test>(`test`.`t2`.`a2`))))))
1 row in set (0.00 sec)
ERROR: No query specified
子查询存在，使用exists进行优化
<ALL：不相关子查询
root@[test] 15:00:30>explain extended select * from t1 where t1.a1 < all(select a2 from t2 where t2.a2=10);
+----+-------------+-------+------+---------------+----------+---------+-------+-------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+-------+----------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | SUBQUERY | t2 | ref | t_idx_t2 | t_idx_t2 | 5 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------+---------------+----------+---------+-------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 15:01:29>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where <not>((`test`.`t1`.`a1` >= <min>(/* select#2 */ select `test`.`t2`.`a2` from `test`.`t2` where (`test`.`t2`.`a2` = 10))))
1 row in set (0.00 sec)
ERROR:
No query specified
mariadb 10.0.2：
root@[test] 15:00:17>explain extended select * from t1 where t1.a1 < all(select a2 from t2 where t2.a2=10);
+------+-------------+-------+------+---------------+----------+---------+-------+-------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+----------+---------+-------+-------+----------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | SUBQUERY | t2 | ref | t_idx_t2 | t_idx_t2 | 5 | const | 1 | 100.00 | Using index |
+------+-------------+-------+------+---------------+----------+---------+-------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 15:01:37>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where <not>(<in_optimizer>(`test`.`t1`.`a1`,(<min>(select `test`.`t2`.`a2` from `test`.`t2` where (`test`.`t2`.`a2` = 10)) <= <cache>(`test`.`t1`.`a1`))))
1 row in set (0.00 sec)
ERROR: No query specified
子查询使用min函数限制，利用索引求最小值。
总结：
mysql和mariadb对>all非关联子查询使用max函数，<all非关联子查询使用min函数，对=all和非关联子查询使用exists优化。
9、some和any子查询：
>some和>any非关联子查询
root@[test] 15:03:10>explain extended select * from t1 where t1.a1 > some(select a2 from t2 where t2.a2 >10);
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 15:10:51>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where <nop>((`test`.`t1`.`a1` > (/* select#2 */ select min(`test`.`t2`.`a2`) from `test`.`t2` where (`test`.`t2`.`a2` > 10))))
1 row in set (0.00 sec)
ERROR:
No query specified
root@[test] 15:10:53>explain extended select * from t1 where t1.a1 > any(select a2 from t2 where t2.a2 >10);
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 15:11:47>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where <nop>((`test`.`t1`.`a1` > (/* select#2 */ select min(`test`.`t2`.`a2`) from `test`.`t2` where (`test`.`t2`.`a2` > 10))))
1 row in set (0.00 sec)
ERROR:
No query specified
mariadb 10.0.2：
root@[test] 15:10:10>explain extended select * from t1 where t1.a1 > some(select a2 from t2 where t2.a2 >10);
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 15:10:21>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a1`,((select min(`test`.`t2`.`a2`) from `test`.`t2` where (`test`.`t2`.`a2` > 10)) < <cache>(`test`.`t1`.`a1`))))
1 row in set (0.00 sec)
ERROR: No query specified
子查询存在，但是使用min进行限制，利用索引求最小值。
>some和>any关联子查询
root@[test] 15:14:19>explain extended select * from t1 where t1.a1 > any(select a2 from t2 where t1.a1 >10);
+----+--------------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | index | NULL | t_idx_t2 | 5 | NULL | 10157 | 100.00 | Using where; Using index |
+----+--------------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)
root@[test] 15:15:01>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1276
Message: Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t1`.`a1` > 10) and <if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a1`) > `test`.`t2`.`a2`) or isnull(`test`.`t2`.`a2`)), true)) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a2`), true))))
2 rows in set (0.00 sec)
ERROR:
No query specified
mariadb 10.0.2：
root@[test] 15:10:22>explain extended select * from t1 where t1.a1 > any(select a2 from t2 where t1.a1 >10);
+------+--------------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | index | NULL | t_idx_t2 | 5 | NULL | 10157 | 100.00 | Using where; Using index |
+------+--------------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)
root@[test] 15:15:13>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1276
Message: Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where <nop>(<expr_cache><`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t1`.`a1`,<exists>(select `test`.`t2`.`a2` from `test`.`t2` where ((`test`.`t1`.`a1` > 10) and trigcond(((<cache>(`test`.`t1`.`a1`) > `test`.`t2`.`a2`) or isnull(`test`.`t2`.`a2`)))) having trigcond(<is_not_null_test>(`test`.`t2`.`a2`))))))
2 rows in set (0.00 sec)
ERROR: No query specified
对关联子查询，子查询存在，使用了exists优化。
=some和=any非关联子查询
root@[test] 15:17:21>explain extended select * from t1 where t1.a1 = any(select a2 from t2 where t2.a2 =10);
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------------+
| 1 | SIMPLE | t1 | ref | t_idx_t1 | t_idx_t1 | 5 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | t2 | ref | t_idx_t2 | t_idx_t2 | 5 | const | 1 | 100.00 | Using index; FirstMatch(t1) |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 15:17:35>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a1` = 10) and (`test`.`t2`.`a2` = 10))
1 row in set (0.00 sec)
ERROR:
No query specified
mariadb 10.0.2：
root@[test] 15:17:01>explain extended select * from t1 where t1.a1 = any(select a2 from t2 where t2.a2 =10);
+------+-------------+-------+------+---------------+----------+---------+-------+------+----------+------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+----------+---------+-------+------+----------+------------------------+
| 1 | PRIMARY | t2 | ref | t_idx_t2 | t_idx_t2 | 5 | const | 1 | 100.00 | Using index; LooseScan |
| 1 | PRIMARY | t1 | ref | t_idx_t1 | t_idx_t1 | 5 | const | 1 | 100.00 | |
+------+-------------+-------+------+---------------+----------+---------+-------+------+----------+------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 15:17:42>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`a2` = 10) and (`test`.`t1`.`a1` = 10))
1 row in set (0.00 sec)
ERROR: No query specified
子查询不存在，使用半连接进行优化，但是mysql和mariadb在进行代价评估时使用了不同的优化方法。
=some和=any关联子查询
root@[test] 15:17:36>explain extended select * from t1 where t1.a1 = any(select a2 from t2 where t1.a1 =10);
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------------+
| 1 | SIMPLE | t1 | ref | t_idx_t1 | t_idx_t1 | 5 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | t2 | ref | t_idx_t2 | t_idx_t2 | 5 | const | 1 | 100.00 | Using index; FirstMatch(t1) |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------------+
2 rows in set, 2 warnings (0.00 sec)
root@[test] 15:20:22>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1276
Message: Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a1` = 10) and (`test`.`t2`.`a2` = 10))
2 rows in set (0.00 sec)
ERROR:
No query specified
mariadb 10.0.2：
root@[test] 15:17:43>explain extended select * from t1 where t1.a1 = any(select a2 from t2 where t1.a1 =10);
+------+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------------+
| 1 | PRIMARY | t1 | ref | t_idx_t1 | t_idx_t1 | 5 | const | 1 | 100.00 | |
| 1 | PRIMARY | t2 | ref | t_idx_t2 | t_idx_t2 | 5 | const | 1 | 100.00 | Using index; FirstMatch(t1) |
+------+-------------+-------+------+---------------+----------+---------+-------+------+----------+-----------------------------+
2 rows in set, 2 warnings (0.00 sec)
root@[test] 15:20:06>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1276
Message: Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a1` = 10) and (`test`.`t2`.`a2` = 10))
2 rows in set (0.00 sec)
ERROR: No query specified
子查询不存在，使用半连接进行优化。
<some和<any非关联子查询：
root@[test] 15:21:57>explain extended select * from t1 where t1.a1<some(select a2 from t2 where t2.a2=10);
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 15:24:09>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where <nop>((`test`.`t1`.`a1` < (/* select#2 */ select max(`test`.`t2`.`a2`) from `test`.`t2` where multiple equal(10, `test`.`t2`.`a2`))))
1 row in set (0.00 sec)
ERROR:
No query specified
root@[test] 15:24:11>explain extended select * from t1 where t1.a1<any(select a2 from t2 where t2.a2=10);
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 15:25:56>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where <nop>((`test`.`t1`.`a1` < (/* select#2 */ select max(`test`.`t2`.`a2`) from `test`.`t2` where multiple equal(10, `test`.`t2`.`a2`))))
1 row in set (0.00 sec)
ERROR:
mariadb 10.0.2：
root@[test] 15:21:53>explain extended select * from t1 where t1.a1<some(select a2 from t2 where t2.a2=10);
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)
root@[test] 15:24:19>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a1`,((select max(`test`.`t2`.`a2`) from `test`.`t2` where multiple equal(10, `test`.`t2`.`a2`)) > <cache>(`test`.`t1`.`a1`))))
1 row in set (0.00 sec)
ERROR: No query specified
子查询存在，但是还有max函数限制，利用索引求最大值。
<some和<any关联子查询：
root@[test] 15:26:05>explain extended select * from t1 where t1.a1<any(select a2 from t2 where t1.a1=10);
+----+--------------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | index | NULL | t_idx_t2 | 5 | NULL | 10157 | 100.00 | Using where; Using index |
+----+--------------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)
root@[test] 15:26:33>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1276
Message: Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t1`.`a1` = 10) and <if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a1`) < `test`.`t2`.`a2`) or isnull(`test`.`t2`.`a2`)), true)) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a2`), true))))
2 rows in set (0.00 sec)
ERROR:
No query specified
mariadb 10.0.2：
root@[test] 15:24:22>explain extended select * from t1 where t1.a1<any(select a2 from t2 where t1.a1=10);
+------+--------------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | index | NULL | t_idx_t2 | 5 | NULL | 10157 | 100.00 | Using where; Using index |
+------+--------------------+-------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)
root@[test] 15:26:41>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1276
Message: Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where <nop>(<expr_cache><`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t1`.`a1`,<exists>(select `test`.`t2`.`a2` from `test`.`t2` where ((`test`.`t1`.`a1` = 10) and trigcond(((<cache>(`test`.`t1`.`a1`) < `test`.`t2`.`a2`) or isnull(`test`.`t2`.`a2`)))) having trigcond(<is_not_null_test>(`test`.`t2`.`a2`))))))
2 rows in set (0.00 sec)
ERROR: No query specified
对关联子查询，子查询存在，使用了exists优化。
总结：
对>some和>any非关联子查询使用min函数，对<some和<any非关联子查询使用max函数，=any和=some子查询使用半连接进行优化，对>some和>any关联子查询以及<some和<any关联子查询只有exists优化。
3257
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
