MySQL查询优化器--逻辑查询优化技术（二）--子查询优化（四）

最新推荐文章于 2022-05-11 20:56:29 发布
最新推荐文章于 2022-05-11 20:56:29 发布
阅读量855 收藏 1
点赞数
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/fly2nn/article/details/61924378
版权
5 MySQL支持的子查询可优化类型

 

从句式的形式看，子查询分为特殊格式子查询和非特殊格式子查询，特殊格式的子查询中又包括IN、ALL、ANY、SOME、EXISTS等类型的子查询，对于有的类型的子查询，MySQL有的支持优化，有的不支持，具体情况如下。

 

示例一，MySQL不支持对EXISTS类型的子查询的优化：

EXISTS类型的相关子查询，查询执行计划如下：

mysql> 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 | key  | Extra       |

+----+--------------------+-------+------+------+-------------+

|  1 | PRIMARY            | t1    | ALL  | NULL | Using where |

|  2 | DEPENDENT SUBQUERY | t2    | ALL  | NULL | Using where |

+----+--------------------+-------+------+------+-------------+

2 rows in set, 2 warnings (0.00 sec)

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`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))

)

从查询执行计划看，子查询存在，MySQL没有进一步做子查询的优化工作。

另外的一个EXISTS类型的相关子查询，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.b1= t2.b2 AND t1.a1=10);

+----+--------------------+-------+------+------+-------------+

| id | select_type        | table | type | key  | Extra       |

+----+--------------------+-------+------+------+-------------+

|  1 | PRIMARY            | t1    | ALL  | NULL | Using where |

|  2 | DEPENDENT SUBQUERY | t2    | ALL  | NULL | Using where |

+----+--------------------+-------+------+------+-------------+

2 rows in set, 3 warnings (0.02 sec)

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`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`.`b1` = `test`.`t2`.`b2`) and (`test`.`t1`.`a1` = 10))

)

从查询执行计划看，子查询存在，MySQL没有进一步做子查询的优化工作。

 

示例二，MySQL不支持对NOT EXISTS类型的子查询的优化：

NOT EXISTS类型的相关子查询，查询执行计划如下：

mysql> 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 | key  | Extra       |

+----+--------------------+-------+------+------+-------------+

|  1 | PRIMARY            | t1    | ALL  | NULL | Using where |

|  2 | DEPENDENT SUBQUERY | t2    | ALL  | NULL | Using where |

+----+--------------------+-------+------+------+-------------+

2 rows in set, 2 warnings (0.00 sec)

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`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))))

)

从查询执行计划看，子查询存在，MySQL没有进一步做子查询的优化工作。

 

另外的一个NOT EXISTS类型的相关子查询，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t1.b1= t2.b2 AND t1.a1=10);

+----+--------------------+-------+------+------+-------------+

| id | select_type        | table | type | key  | Extra       |

+----+--------------------+-------+------+------+-------------+

|  1 | PRIMARY            | t1    | ALL  | NULL | Using where |

|  2 | DEPENDENT SUBQUERY | t2    | ALL  | NULL | Using where |

+----+--------------------+-------+------+------+-------------+

2 rows in set, 3 warnings (0.00 sec)

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`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`.`b1` = `test`.`t2`.`b2`) and (`test`.`t1`.`a1` = 10))))

)

从查询执行计划看，子查询存在，MySQL没有进一步做子查询的优化工作。

 

示例三，MySQL支持对IN类型的子查询的优化，按也有不支持的情况存在：

IN非相关子查询，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 IN (SELECT a2 FROM t2 WHERE t2.a2>10);

+----+--------------+-------------+------+------+----------------------------------------------------+

| id | select_type  | table       | type | key  | Extra   |

+----+--------------+-------------+------+------+----------------------------------------------------+

|  1 | SIMPLE       | <subquery2> | ALL  | NULL | NULL   |

|  1 | SIMPLE       | t1          | ALL  | NULL | Using where; Using join buffer (Block Nested Loop) |

|  2 | MATERIALIZED | t2          | ALL  | NULL | Using where   |

+----+--------------+-------------+------+------+----------------------------------------------------+

3 rows in set, 1 warning (0.00 sec)

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

    `test`.`t1`.`b1` AS `b1`

from `test`.`t1` semi join (`test`.`t2`)

where ((`test`.`t1`.`a1` = `<subquery2>`.`a2`) and (`test`.`t2`.`a2` > 10))

从查询执行计划看，表t2被物化后，与表t1执行了半连接（semi join）。尽管有“subquery2”这样的内容看起来是子查询，但是表t2已经被上拉到表t1层执行了半连接，所以MySQL支持IN子查询优化为半连接操作。

 

另外一个IN非相关子查询，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 IN (SELECT a2 FROM t2 WHERE t2.a2=10);

+----+--------------+-------------+------+------+----------------------------------------------------+

| id | select_type  | table       | type | key  | Extra   |

+----+--------------+-------------+------+------+----------------------------------------------------+

|  1 | SIMPLE       | <subquery2> | ALL  | NULL | Using where   |

|  1 | SIMPLE       | t1          | ALL  | NULL | Using where; Using join buffer (Block Nested Loop) |

|  2 | MATERIALIZED | t2          | ALL  | NULL | Using where   |

+----+--------------+-------------+------+------+----------------------------------------------------+

3 rows in set, 1 warning (0.02 sec)

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

    `test`.`t1`.`b1` AS `b1`

from `test`.`t1` semi join (`test`.`t2`)

where ((`<subquery2>`.`a2` = 10) and (`test`.`t1`.`a1` = 10) and (`test`.`t2`.`a2` = 10))

从查询执行计划看，子查询不存在，表t1和t2直接做了块嵌套循环半连接（Block Nested Loop），把子查询上拉到父查询中用嵌套循环半连接完成IN操作。另外，由于子查询上拉，使得增加连接条件“a1=a2”，而原先的条件“a2=10”可以利用常量传递优化技术，使得“a1=a2=10”，所以查询执行计划中，两个索引扫描的条件分别为：a1 = 10、a2 = 10。

 

另外一个IN非相关子查询，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 IN (SELECT a2 FROM t2 WHERE t1.a1=10);

+----+-------------+-------+------+------------------------------------------------------------------+

| id | select_type | table | type | Extra           |

+----+-------------+-------+------+------------------------------------------------------------------+

|  1 | SIMPLE      | t2    | ALL  | Using where; Start temporary           |

|  1 | SIMPLE      | t1    | ALL  | Using where; End temporary; Using join buffer (Block Nested Loop)|

+----+-------------+-------+------+------------------------------------------------------------------+

2 rows in set, 2 warnings (0.00 sec)

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`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))

从查询执行计划看，子子查询不存在，表t1和t2直接做了块嵌套循环连接（Block Nested Loop），但属于半连接操作（semi join），把子查询上拉到父查询中用嵌套循环半连接完成IN操作。

 

示例四，MySQL支持对NOT IN类型的子查询的优化

NOT IN非相关子查询，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 NOT IN (SELECT a2 FROM t2 WHERE t2.a2>10);

+----+-------------+-------+------+------+-------------+

| id | select_type | table | type | key  | Extra       |

+----+-------------+-------+------+------+-------------+

|  1 | PRIMARY     | t1    | ALL  | NULL | Using where |

|  2 | SUBQUERY    | t2    | ALL  | NULL | Using where |

+----+-------------+-------+------+------+-------------+

2 rows in set, 1 warning (0.02 sec)

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`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`))

        )

     )

    ))

)

从查询执行计划看，表t2做了子查询（SUBQUERY）。而子查询被物化（materialize）。所以，MySQL对于NOT IN子查询采用了物化的优化方式，但不支持子查询的消除。

 

另外一个NOT IN非相关子查询，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 NOT IN (SELECT a2 FROM t2 WHERE t2.a2=10);

+----+-------------+-------+------+------+-------------+

| id | select_type | table | type | key  | Extra       |

+----+-------------+-------+------+------+-------------+

|  1 | PRIMARY     | t1    | ALL  | NULL | Using where |

|  2 | SUBQUERY    | t2    | ALL  | NULL | Using where |

+----+-------------+-------+------+------+-------------+

2 rows in set, 1 warning (0.00 sec)

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`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`))

        )

    )

    ))

)

从查询执行计划看，表t2做了子查询（SUBQUERY）。而子查询被物化（materialize）。所以，MySQL对于NOT IN子查询采用了物化的优化方式，但不支持子查询的消除。

 

示例五，MySQL支持对ALL类型的子查询的优化：

不相关的ALL子查询，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 >ALL (SELECT a2 FROM t2 WHERE t2.a2>10);

+----+-------------+-------+------+------+-------------+

| id | select_type | table | type | key  | Extra       |

+----+-------------+-------+------+------+-------------+

|  1 | PRIMARY     | t1    | ALL  | NULL | Using where |

|  2 | SUBQUERY    | t2    | ALL  | NULL | Using where |

+----+-------------+-------+------+------+-------------+

2 rows in set, 1 warning (0.00 sec)

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`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)

    )

))

从查询执行计划看，出现了子查询（SUBQUERY），但是，子查询被“<= <max>”操作符限制，而子查询中的被查询列a2上存在唯一索引，所以可以利用索引求最值，所以MySQL支持“>ALL”式的子查询优化，子查询只被执行一次即可求得最大值。

 

不相关的ALL子查询，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 =ALL (SELECT a2 FROM t2 WHERE t2.a2=10);

+----+--------------------+-------+------+------+-------------+

| id | select_type        | table | type | key  | Extra       |

+----+--------------------+-------+------+------+-------------+

|  1 | PRIMARY            | t1    | ALL  | NULL | Using where |

|  2 | DEPENDENT SUBQUERY | t2    | ALL  | NULL | Using where |

+----+--------------------+-------+------+------+-------------+

2 rows in set, 1 warning (0.00 sec)

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`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)

    )

))

从查询执行计划看，出现了子查询（SUBQUERY），但是被查询优化器处理后的语句中包含“exists”，这表明MySQL对于“=ALL”式的子查询优化用“EXISTS strategy”方式优化，所以MySQL支持“=ALL”式的子查询优化。

 

不相关的ALL子查询，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 <ALL (SELECT a2 FROM t2 WHERE t2.a2=10);

+----+-------------+-------+------+------+-------------+

| id | select_type | table | type | key  | Extra       |

+----+-------------+-------+------+------+-------------+

|  1 | PRIMARY     | t1    | ALL  | NULL | Using where |

|  2 | SUBQUERY    | t2    | ALL  | NULL | Using where |

+----+-------------+-------+------+------+-------------+

2 rows in set, 1 warning (0.00 sec)

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`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)

    )

))

从查询执行计划看，出现了子查询（SUBQUERY），但是，子查询被“>= <min>”操作符限制，而子查询中的被查询列a2上存在唯一索引，所以可以利用索引求最值，所以MySQL支持“<ALL”式的子查询优化，子查询只被执行一次即可求得最小值。

 

示例六，MySQL支持对SOME类型的子查询的优化：

使用了“>SOME”式子的子查询被优化，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 >SOME (SELECT a2 FROM t2 WHERE t2.a2>10);

+----+-------------+-------+------+------+-------------+

| id | select_type | table | type | key  | Extra       |

+----+-------------+-------+------+------+-------------+

|  1 | PRIMARY     | t1    | ALL  | NULL | Using where |

|  2 | SUBQUERY    | t2    | ALL  | NULL | Using where |

+----+-------------+-------+------+------+-------------+

2 rows in set, 1 warning (0.05 sec)

被查询优化器处理后的语句为：

 /* select#1 */ select `test`.`t1`.`id1` AS `id1`,`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)

)))

从查询执行计划看，出现了子查询（SUBQUERY），但是，子查询被“min”函数限制，而子查询中的被查询列a2上存在唯一索引，所以可以利用索引求最值，所以MySQL支持“>SOME”式的子查询优化，子查询只被执行一次即可求得最大值。

 

使用了“=SOME”式子的子查询被优化，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 =SOME (SELECT a2 FROM t2 WHERE t2.a2=10);

+----+--------------+-------------+------+------+----------------------------------------------------+

| id | select_type  | table       | type | key  | Extra   |

+----+--------------+-------------+------+------+----------------------------------------------------+

|  1 | SIMPLE       | <subquery2> | ALL  | NULL | Using where   |

|  1 | SIMPLE       | t1          | ALL  | NULL | Using where; Using join buffer (Block Nested Loop) |

|  2 | MATERIALIZED | t2          | ALL  | NULL | Using where   |

+----+--------------+-------------+------+------+----------------------------------------------------+

3 rows in set, 1 warning (0.01 sec)

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

`test`.`t1`.`b1` AS `b1`

from `test`.`t1` semi join (`test`.`t2`)

where ((`<subquery2>`.`a2` = 10) and (`test`.`t1`.`a1` = 10) and (`test`.`t2`.`a2` = 10))

从查询执行计划看，没有出现了子查询，表t2被物化，与表t1进行了半连接。

 

使用了“<SOME”式子的子查询被优化，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 <SOME (SELECT a2 FROM t2 WHERE t2.a2=10);

+----+-------------+-------+------+------+-------------+

| id | select_type | table | type | key  | Extra       |

+----+-------------+-------+------+------+-------------+

|  1 | PRIMARY     | t1    | ALL  | NULL | Using where |

|  2 | SUBQUERY    | t2    | ALL  | NULL | Using where |

+----+-------------+-------+------+------+-------------+

2 rows in set, 1 warning (0.00 sec)

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`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 (`test`.`t2`.`a2` = 10)

        )

    )

)

从查询执行计划看，出现了子查询（SUBQUERY），但是，子查询被“max”函数限制，而子查询中的被查询列a2上存在唯一索引，所以可以利用索引求最值，所以MySQL支持“<SOME”式的子查询优化，子查询只被执行一次即可求得最大值。

 

示例七，MySQL支持对ANY类型的子查询的优化：

使用了“>ANY”式子的子查询被优化，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 >ANY (SELECT a2 FROM t2 WHERE t2.a2>10);

+----+-------------+-------+------+------+-------------+

| id | select_type | table | type | key  | Extra       |

+----+-------------+-------+------+------+-------------+

|  1 | PRIMARY     | t1    | ALL  | NULL | Using where |

|  2 | SUBQUERY    | t2    | ALL  | NULL | Using where |

+----+-------------+-------+------+------+-------------+

2 rows in set, 1 warning (0.00 sec)

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`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)

        )

    )

)

从查询执行计划看，出现了子查询（SUBQUERY），但是，子查询被“min”函数限制，而子查询中的被查询列a2上存在唯一索引，所以可以利用索引求最值，所以MySQL支持“>ANY”式的子查询优化，子查询只被执行一次即可求得最小值。

 

使用了“=ANY”式子的子查询被优化，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 =ANY (SELECT a2 FROM t2 WHERE t2.a2>10);

+----+--------------+-------------+------+------+----------------------------------------------------+

| id | select_type  | table       | type | key  | Extra   |

+----+--------------+-------------+------+------+----------------------------------------------------+

|  1 | SIMPLE       | <subquery2> | ALL  | NULL | NULL   |

|  1 | SIMPLE       | t1          | ALL  | NULL | Using where; Using join buffer (Block Nested Loop) |

|  2 | MATERIALIZED | t2          | ALL  | NULL | Using where   |

+----+--------------+-------------+------+------+----------------------------------------------------+

3 rows in set, 1 warning (0.02 sec)

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

    `test`.`t1`.`b1` AS `b1`

from `test`.`t1` semi join (`test`.`t2`)

where ((`test`.`t1`.`a1` = `<subquery2>`.`a2`) and (`test`.`t2`.`a2` > 10))

从查询执行计划看，没有出现了子查询，表t2被物化，与表t1进行了半连接。

 

使用了“<ANY”式子的子查询被优化，查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 <ANY (SELECT a2 FROM t2 WHERE t2.a2>10);

+----+-------------+-------+------+------+-------------+

| id | select_type | table | type | key  | Extra       |

+----+-------------+-------+------+------+-------------+

|  1 | PRIMARY     | t1    | ALL  | NULL | Using where |

|  2 | SUBQUERY    | t2    | ALL  | NULL | Using where |

+----+-------------+-------+------+------+-------------+

2 rows in set, 1 warning (0.00 sec)

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`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 (`test`.`t2`.`a2` > 10)

        )

    )

)

从查询执行计划看，出现了子查询（ SUBQUERY ），但是，子查询被“ max ”函数限制，而子查询中的被查询列 a2 上存在唯一索引，所以可以利用索引求最值，所以 MySQL 支持“ <ANY ”式的子查询优化，子查询只被执行一次即可求得最大值。
那海蓝蓝
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Subquery 子查询
SDS_19的博客
07-17 239
即SELECT语句中嵌套SELECT语句 WHERE语句中嵌套子查询 FROM语句中嵌套子查询 SELECT语句中嵌套子查询 1. WHERE Problem: 找出高于平均薪资的员工信息。 mysql> SELECT avg(sal) FROM emp; +-------------+ | avg(sal) | +-------------+ | 2073.214286 | +-------------+ 1 row in set (0.00 sec) mysql> SEL
MySQL优化从执行计划开始(explain超详细)，高级面试题+解析
m0_64384350的博客
12-10 1208
查询缓存：当一个SQL进来时，如果开启查询缓存功能，MySQL会优先去查询缓存中检查是否有数据匹配，如果匹配上，就不会再去解析对应的SQL啦，但如果语句中有用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表时，都不会走缓存； 对于查询缓存来说，在MySQL8.0已经去除，官方回应的是在一定场景上，查询缓存会导致性能上的瓶颈。 解析器：对于一个SQL语句，MySql根据语法规则需要对其进行解析，并生成一个内部能识别的解析树； 优化器：负责对解析器得到的解析树进行优化，MySQL会根据内部算法找
MySQL优化从执行计划开始(explain超详细)
zyq025的专栏
03-17 228
前言 小伙伴一定遇到过这样反馈：这页面加载数据太慢啦，甚至有的超时了，用户体验极差，需要赶紧优化； 反馈等同于投诉啊，多有几次，估计领导要找你谈话啦。 于是不得不停下手里头的活，赶紧进行排查，最终可能是程序处理的问题、也可能是并发量大导致排队问题、也可能是SQL查询性能导致等；而在很多时候，SQL查询缓慢是最直接拖慢系统的罪魁祸首，同样是实现一个功能，有的小伙伴毫秒级呈现效果，有的却要好几秒，而调优需要的花费时间不容小觑，最终可能就体现到个人业务能力上和形象上：哇，真牛逼，分分钟搞定； 菜鸟，居然写出
【mysql知识点整理】--- mysql执行计划详解
nrsc
03-01 1097
文章目录1 是什么？ 能干嘛？怎么用？ 官网：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html 1 是什么？ 能干嘛？怎么用？ 下面这句话是官网对EXPLAIN语句的描述： The EXPLAIN statement provides information about how MySQL executes state...
mysql子查询从句_MySQL的子查询及相关优化学习教程
weixin_35801828的博客
01-28 196
一、子查询1、where型子查询(把内层查询结果当作外层查询的比较条件)#不用order by 来查询最新的商品select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);#取出每个栏目下最新的产品(goods_id唯一)select cat_id,goods_id,goods_n...
MySQL子查询(subquery)分类
06-25 4151
分类标准： 子查询出现的位置： 子查询的返回值形式： 1 single-value or scalar subquery(子查询作为标量操作数) 2 (single) column subquery 3 (single) row  subquery (行子查询)     SELECT * FROM t1 WHERE (1,2) = (SELECT column1, column
MYSQL专题-查询优化-使用索引-安全隐患-事务与锁.docx
11-28
MYSQL专题-查询优化-使用索引-安全隐患-事务与锁 MYSQL专题中最重要的一环节就是查询优化，如何优化数据库查询以提高查询速度和数据库空间利用率变得尤为重要。本文将从多个方面对查询优化进行分析和讲解。 一、...
简单谈谈MySQL优化利器-慢查询
12-15
MySQL优化是数据库管理中的关键环节，特别是在处理大量数据时，高效的查询能显著提升系统的响应速度和用户体验。慢查询是数据库性能下降的一个重要原因，它指的是执行时间超过预设阈值的SQL查询。通过开启慢查询日志...
Mysql查询优化详解（含示例）
最新发布
05-06
### MySQL查询优化详解 #### 一、使用EXPLAIN分析查询 **EXPLAIN** 是MySQL中一个非常有用的工具，用于分析SQL语句的执行计划。它能够揭示MySQL如何处理查询，帮助我们理解查询的效率和潜在的性能瓶颈。下面是一些...
MySQL查询优化实践-最终版.pdf
12-20
对于复杂查询优化，比如涉及多个表连接的查询，应考虑使用连接顺序、索引覆盖和子查询优化等策略。连接顺序对查询性能影响很大，MySQL会选择最小代价的执行计划。索引覆盖是指查询中所有需要的数据都可以直接从索引...
mysql关联子查询的一种优化方法分析
09-10
在数据库查询优化中，关联子查询是一种常见的操作，但它的性能往往不如预期。当涉及到大型数据表时，关联子查询可能导致显著的延迟。本文将深入探讨MySQL关联子查询的优化方法，通过实例来展示如何改进查询效率。 ...
MySQL进阶篇
xiaoyou0723的博客
05-11 597
MySQL进阶篇学习笔记
MySQL EXPLAIN 独立子查询dependent subquery 优化示例
10-23 9865
（本例创建表脚本在文章底部） 对于mysql的出现的子查询语句，大部分都是不太好的，尤其 in() 的子查询语句，如下： select * from test.tabname where id in(select id from test.tabname2 where name='love'); 一般会认为mysql会先执行子查询，返回所有包含 test.tabname2 中所有符合条件的
explain执行计划详解
eagle89的专栏
05-24 2万+
explain执行计划详解1.  Explain表的读取顺序，数据读取操作的类型，哪些索引可以使用，哪些索引实际使用了，表之间的引用，每张表有多少行被优化器查询等信息。下面是使用explain 的例子：  1.1. explain执行计划包含的信息mysql&gt; explain select * from mysql.user;+----+-------------+-------+-----...
Mysql 执行计划分析（Explain关键字使用）
邵小雨的博客
03-19 5300
2018/3/19 初次分析explainmysql执行计划分析主要从一下几个关键字入手：1.idid标识sql语句中表的执行顺序，id越大越先执行；如果id相同，按照从上到下的顺序执行；如果id为null，表示当列是一个结果集。2.select_type2.1 simplesimple 表示该表上对应的操作是一个简单的select，没有union、子查询等。EXPLAIN SELECT t2.`...
MySQL Explain 详解
蓝色梦想
04-28 1万+
MySQL Explain 详解 实验环境： 操作系统：centos 7.4 操作系统内核版本：3.10.0-693.el7.x86_64 MySQL数据库：5.7.20-log MySQL Community Server (GPL) 名词： QEP: Query Execution Plan，查询执行计划。 语法： EXPLAIN [explain_type] {explai...
【MySQL】MySQL性能优化之Block Nested-Loop Join(BNL)
cojm55771的博客
06-09 1991
一 介绍 相信许多开发/DBA在使用MySQL的过程中，对于MySQL处理多表关联的方式或者说性能一直不太满意。对于开发提交的含有join的查询，一般比较抗拒，从而建议将join拆分，避免join可能带来的性能问题，同时也...
mysql Using join buffer (Block Nested Loop) join连接查询优化
热门推荐
u013958151的博客
01-11 4万+
最近在优化链表查询的时候发现就算链接的表里面不到1w的数据链接查询也需要10多秒，这个速度简直不能忍受 通过EXPLAIN发现，extra中有数据是Using join buffer (Block Nested Loop)，而其它关联的表都是Using index，最后使用排除法发现当extra为Using join buffer (Block Nested Loop)是最慢的，就算加了索...
MySQL高级优化：查询逻辑与WHERE条件详解
5. **优化策略**：文档可能还会讨论如何通过使用合适的数据结构（如B树索引、覆盖索引）、减少查询的复杂性、避免N+1问题（针对子查询优化）等方式，提高查询的执行效率。 6. **总结与问答环节**：最后部分通常会对...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值