MySQL Optimizer的子查询优化行为

MySQL Optimizer

先谈谈Optimizer。

Optimizer即数据库优化器，在MySQL中大家普遍称之为查询优化器。在任何一款数据库中，Optimizer一直占据着最为核心的角色，有些人将它比喻为数据库的大脑，真是太贴切了，因为从一定程度上来讲优化器的设计原则决定着一款数据库的行为。那么优化的前提是了解优化器，优化的作用是辅助优化器

MySQL Optimizer属于CBO，并且在不断地改进中已经渐渐开始支持各种主流的优化技术，在生成执行计划之前进行部分优化工作。mySQL中的SQL处理过程大致如下，


语法分析器处理的结果是将SQL语句解析为查询树（select_lex），优化器以JOIN::prepare方法作为入口，最终将语法树转变为最优的执行计划。期间交错进行着逻辑优化与物理优化，各个组件及数据结构彼此配合，共同完成查询优化以及执行计划生成这一个完整的工作流程。

再来说一下逻辑优化与物理优化。

逻辑优化，主要功能是基于关系代数以及启发式规则，找出SQL语句等价的变换形式，使得SQL执行更高效；物理优化，即根据代价估算模型，选择最优的表连接顺序以及数据访问方式，这个阶段依赖于对数据的了解。

从我们的角度来看，逻辑优化就是优化器对我们的SQL进行了一次改进优化，那么优化器就不单单只是简单地解析SQL，为了让生活更美好，它会选择自己更乐意接受的语法形式

子查询优化技术

在逻辑优化阶段，其中一个核心工作就是进行子查询的优化，因为子查询实在是太消耗性能了，MySQL查询优化器在演进历程中渐渐支持了子查询的优化，这还是相当有必要的。在MySQL Optimizer中，子查询的处理贯穿在整个优化器代码中，

在JOIN::prepare查询准备阶段就进行了一部分特殊子查询的优化，如

remove_redundant_subquery_clauses //去冗余子句
resolve_subquery //简单格式的semi-join优化，否则调用select_transformer
select_transformer //其他类型子查询优化，如IN/ALL/ANY等价转换

JOIN::optimize方法中，融合了semi-join、Materialization以及EXISTS strategy这三种主要的优化策略，如

flatten_subqueries //子查询展开，即子查询上拉，无法上拉则使用EXISTS strategy方式
make_join_statistics //完成多表连接，此函数会进行嵌套连接的Materialization优化，并确定子查询的优化策略

子查询优化的主流技术有三种：

子查询合并。即把多个子查询合并成一个子查询，作用是减少元组扫描的次数

子查询反嵌套。也称作子查询上拉，即将子查询重写为等价的多表连接

聚集子查询消除。即聚集函数上推，将消除聚集函数的子查询与父查询做外连接

其中，子查询反嵌套是一种最常用的子查询优化技术，同时MySQL中也只支持这种优化技术。MySQL 5.6中处理子查询的思路是，基于查询重写技术的规则，尽可能将子查询转换为连接（semi-join Semi-Join Transformations），并配合基于代价估算的Materialize、exists 等优化策略让子查询执行更优

接下来会对各种技术以及实现场景进行测试验证，测试用表如下，

[root@sakila 11:49:54]> desc customer_t;
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| | smallint(5) unsigned | NO | PRI | NULL | |
...
...
9 rows in set (0.07 sec)

[root@sakila 02:38:29]> desc payment_t;
+--------------+----------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------------------+------+-----+-------------------+-----------------------------+
| payment_id | smallint(5) unsigned | NO | PRI | NULL | |
|  | smallint(5) unsigned | NO | MUL | NULL | |
...
...
7 rows in set (0.00 sec)

不支持的优化技术

1、子查询合并技术

对于两个相似的子查询作为where条件的查询，MySQL不支持合并处理。如下，

[root@sakila 05:00:22]> explain extended select customer_t.store_id  from customer_t where customer_id in (select customer_id from payment_t where payment_id>1000) or  customer_id in (select customer_id from payment_t where payment_id<10);
+----+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | customer_t | ALL   | NULL          | NULL    | NULL    | NULL |  644 |   100.00 | Using where |
|  3 | SUBQUERY    | payment_t  | range | PRIMARY       | PRIMARY | 2       | NULL |    9 |   100.00 | Using where |
|  2 | SUBQUERY    | payment_t  | range | PRIMARY       | PRIMARY | 2       | NULL | 8272 |   100.00 | Using where |
+----+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

改写后的SQL语句如下，

/* select#1 */ select `sakila`.`customer_t`.`store_id` AS `store_id` from `sakila`.`customer_t` where (<in_optimizer>(`sakila`.`customer_t`.`customer_id`,`sakila`.`customer_t`.`customer_id` in ( <materialize> (/* select#2 */ select `sakila`.`payment_t`.`customer_id` from `sakila`.`payment_t` where (`sakila`.`payment_t`.`payment_id` > 1000) ), <primary_index_lookup>(`sakila`.`customer_t`.`customer_id` in <temporary table> on <auto_key> where ((`sakila`.`customer_t`.`customer_id` = `materialized-subquery`.`customer_id`))))) or <in_optimizer>(`sakila`.`customer_t`.`customer_id`,`sakila`.`customer_t`.`customer_id` in ( <materialize> (/* select#3 */ select `sakila`.`payment_t`.`customer_id` from `sakila`.`payment_t` where (`sakila`.`payment_t`.`payment_id` < 10) ), <primary_index_lookup>(`sakila`.`customer_t`.`customer_id` in <temporary table> on <auto_key> where ((`sakila`.`customer_t`.`customer_id` = `materialized-subquery`.`customer_id`))))))

从转换结果来看，优化器使用了多种优化策略来获得更快的元组扫描速率，但是两个子查询仍然单独进行，表payment_t被扫描了两次，而没有将两个子查询进行合并处理

2、聚集子查询消除。

子查询包含聚集函数，MySQL不支持消除，如下，

[root@sakila 09:38:54]> explain extended select * from customer_t where customer_id in (select min(customer_id) from payment_t);
+----+-------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | PRIMARY | customer_t | ALL | NULL | NULL | NULL | NULL | 644 | 100.00 | Using where |
| 2 | SUBQUERY | payment_t | ALL | NULL | NULL | NULL | NULL | 15448 | 100.00 | NULL |
+----+-------------+------------+------+---------------+------+---------+------+-------+----------+-------------+

优化后语句如下，

/* select#1 */ select `sakila`.`customer_t`.`customer_id` AS `customer_id`,`sakila`.`customer_t`.`store_id` AS `store_id`,`sakila`.`customer_t`.`first_name` AS `first_name`,`sakila`.`customer_t`.`last_name` AS `last_name`,`sakila`.`customer_t`.`email` AS `email`,`sakila`.`customer_t`.`address_id` AS `address_id`,`sakila`.`customer_t`.`active` AS `active`,`sakila`.`customer_t`.`create_date` AS `create_date`,`sakila`.`customer_t`.`last_update` AS `last_update` from `sakila`.`customer_t` where <in_optimizer>(`sakila`.`customer_t`.`customer_id`,`sakila`.`customer_t`.`customer_id` in ( <materialize> (/* select#2 */ select min(`sakila`.`payment_t`.`customer_id`) from `sakila`.`payment_t` having 1 ), <primary_index_lookup>(`sakila`.`customer_t`.`customer_id` in <temporary table> on <auto_key> where ((`sakila`.`customer_t`.`customer_id` = `materialized-subquery`.`min(customer_id)`)))))

可以发现被优化后的查询仍然有子查询，select_type值为SUBQUERY，这里用到了materialize、auto_key等各种优化手段，但是没有实现聚集子查询消除

特殊类型子查询的优化

1.IN类型。

MySQL支持简单in查询的优化，主要策略为semi-join、Materialization，否则会使用EXISTS strategy优化。这里用两个测试场景来分析优化行为，

场景一：... out_expr in (select primary_key ... )

[root@sakila 08:30:46]> explain extended select * from payment_t where customer_id in (select customer_id from customer_t);
+----+-------------+------------+--------+---------------+---------+---------+------------------------------+-------+----------+-------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref                          | rows  | filtered | Extra       |
+----+-------------+------------+--------+---------------+---------+---------+------------------------------+-------+----------+-------------+
|  1 | SIMPLE      | payment_t  | ALL    | NULL          | NULL    | NULL    | NULL                         | 15448 |   100.00 | NULL        |
|  1 | SIMPLE      | customer_t | eq_ref | PRIMARY       | PRIMARY | 2       | sakila.payment_t.customer_id |     1 |   100.00 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+------------------------------+-------+----------+-------------+

改写后的语句如下， 

 /* select#1 */ select `sakila`.`payment_t`.`payment_id` AS `payment_id`,`sakila`.`payment_t`.`customer_id` AS `customer_id`,`sakila`.`payment_t`.`staff_id` AS `staff_id`,`sakila`.`payment_t`.`rental_id` AS `rental_id`,`sakila`.`payment_t`.`amount` AS `amount`,`sakila`.`payment_t`.`payment_date` AS `payment_date`,`sakila`.`payment_t`.`last_update` AS `last_update` from `sakila`.`customer_t` join `sakila`.`payment_t` where (`sakila`.`customer_t`.`customer_id` = `sakila`.`payment_t`.`customer_id`)

通过优化器的转换结果，我们发现这是一个标准的子查询反嵌套，子查询被上拉至外部表，句式被转换成内表与外表的inner join，并且执行计划中也没有subquery。

场景二：... primary_key in (select inner_expr ... )

[root@sakila 08:30:21]> explain extended select * from customer_t where customer_id in (select customer_id from payment_t);
+----+--------------+-------------+--------+---------------+------------+---------+-------------------------------+-------+----------+-------------+
| id | select_type  | table       | type   | possible_keys | key        | key_len | ref                           | rows  | filtered | Extra       |
+----+--------------+-------------+--------+---------------+------------+---------+-------------------------------+-------+----------+-------------+
|  1 | SIMPLE       | customer_t  | ALL    | PRIMARY       | NULL       | NULL    | NULL                          |   644 |   100.00 | Using where |
|  1 | SIMPLE       | <subquery2> | eq_ref | <auto_key>    | <auto_key> | 2       | sakila.customer_t.customer_id |     1 |   100.00 | NULL        |
|  2 | MATERIALIZED | payment_t   | ALL    | NULL          | NULL       | NULL    | NULL                          | 15448 |   100.00 | NULL        |
+----+--------------+-------------+--------+---------------+------------+---------+-------------------------------+-------+----------+-------------+

转换后的语句如下，
/* select#1 */ select `sakila`.`customer_t`.`customer_id` AS `customer_id`,`sakila`.`customer_t`.`store_id` AS `store_id`,`sakila`.`customer_t`.`first_name` AS `first_name`,`sakila`.`customer_t`.`last_name` AS `last_name`,`sakila`.`customer_t`.`email` AS `email`,`sakila`.`customer_t`.`address_id` AS `address_id`,`sakila`.`customer_t`.`active` AS `active`,`sakila`.`customer_t`.`create_date` AS `create_date`,`sakila`.`customer_t`.`last_update` AS `last_update` from `sakila`.`customer_t` semi join (`sakila`.`payment_t`) where (`<subquery2>`.`customer_id` = `sakila`.`customer_t`.`customer_id`)

这个语句和前面一个很类似，但是处理结果有一些区别。从执行计划看，好像还是有subquery，但是结合转换结果看，这种子查询被物化处理，然后进行了与外部表的semi join转换。

这两个语句的差别在哪里呢？

语句本身无差异，但是转换成join就会有很大区别，就是场景二的SQL转换为join之后还需要进行消重处理，这里的处理策略是物化为临时表，然后semi join消重。在这种场景下MySQL优化器还提供多种优化策略，如FirstMatch、LooseScan等，根据成本选择。

这两种场景都实现了MySQL的子查询反嵌套技术，只是在后一种场景下进行了更细致的处理，让SQL的处理消耗更少的资源。在MySQL中，与各种独特优化策略相结合的子查询上拉技术，被统称为Semi-Join Transformations。

2.ANY/ALL/SOME。

MySQL对于ANY/ALL/SOME类型的优化，主要策略是基于查询重写技术，将查询转换成更易被优化的min()/max()，= ANY则等价于in，= ALL一般会通过EXISTS strategy进行优化

场景一：outer_expr > ANY(select inner_expr ... )

[root@sakila 10:32:10]> explain extended select * from customer_t where customer_id > ANY(select customer_id from payment_t);
+----+-------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | PRIMARY     | customer_t | ALL  | NULL          | NULL | NULL    | NULL |   644 |   100.00 | Using where |
|  2 | SUBQUERY    | payment_t  | ALL  | NULL          | NULL | NULL    | NULL | 15448 |   100.00 | NULL        |
+----+-------------+------------+------+---------------+------+---------+------+-------+----------+-------------+

转换后的语句如下，
/* select#1 */ select `sakila`.`customer_t`.`customer_id` AS `customer_id`,`sakila`.`customer_t`.`store_id` AS `store_id`,`sakila`.`customer_t`.`first_name` AS `first_name`,`sakila`.`customer_t`.`last_name` AS `last_name`,`sakila`.`customer_t`.`email` AS `email`,`sakila`.`customer_t`.`address_id` AS `address_id`,`sakila`.`customer_t`.`active` AS `active`,`sakila`.`customer_t`.`create_date` AS `create_date`,`sakila`.`customer_t`.`last_update` AS `last_update` from `sakila`.`customer_t` where <nop>((`sakila`.`customer_t`.`customer_id` > (/* select#2 */ select min(`sakila`.`payment_t`.`customer_id`) from `sakila`.`payment_t`)))

从等价转换后的句式可以看到，子查询还是存在的，但是ANY被转换为>min()。那么这种转换的意义在哪里呢？

现在对payment_t表添加一个索引：alter table payment_t add key(customer_id)，然后再看看这条查询的执行计划

[root@sakila 12:02:16]> explain extended select * from customer_t where customer_id > ANY(select customer_id from payment_t);
+----+-------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | PRIMARY     | customer_t | ALL  | NULL          | NULL | NULL    | NULL |  644 |   100.00 | Using where                  |
|  2 | SUBQUERY    | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

可以发现，在customer_id字段添加了索引之后，优化器直接通过索引获取min()，Extra信息为Select tables optimized away。那么优化器的转换思路就是，朝更容易被使用索引进行物理优化的方向进行改写。

场景二：... outer_expr < ALL(select inner_expr ... )

[root@sakila 12:02:21]> explain extended select * from customer_t where customer_id < ALL(select customer_id from payment_t);

转换后的语句如下，
/* select#1 */ select `sakila`.`customer_t`.`customer_id` AS `customer_id`,`sakila`.`customer_t`.`store_id` AS `store_id`,`sakila`.`customer_t`.`first_name` AS `first_name`,`sakila`.`customer_t`.`last_name` AS `last_name`,`sakila`.`customer_t`.`email` AS `email`,`sakila`.`customer_t`.`address_id` AS `address_id`,`sakila`.`customer_t`.`active` AS `active`,`sakila`.`customer_t`.`create_date` AS `create_date`,`sakila`.`customer_t`.`last_update` AS `last_update` from `sakila`.`customer_t` where <not>((`sakila`.`customer_t`.`customer_id` >= (/* select#2 */ select min(`sakila`.`payment_t`.`customer_id`) from `sakila`.`payment_t`)))

和场景一类似，将其改写成更易获取的min()形式

场景三：... outer_expr = ANY(select inner_expr ... )

[root@sakila 12:06:15]> explain extended select * from customer_t where customer_id = ANY (select customer_id from payment_t);
+----+--------------+-------------+--------+---------------+------------+---------+-------------------------------+-------+----------+-------------+
| id | select_type  | table       | type   | possible_keys | key        | key_len | ref                           | rows  | filtered | Extra       |
+----+--------------+-------------+--------+---------------+------------+---------+-------------------------------+-------+----------+-------------+
|  1 | SIMPLE       | customer_t  | ALL    | PRIMARY       | NULL       | NULL    | NULL                          |   644 |   100.00 | Using where |
|  1 | SIMPLE       | <subquery2> | eq_ref | <auto_key>    | <auto_key> | 2       | sakila.customer_t.customer_id |     1 |   100.00 | NULL        |
|  2 | MATERIALIZED | payment_t   | ALL    | NULL          | NULL       | NULL    | NULL                          | 15448 |   100.00 | NULL        |
+----+--------------+-------------+--------+---------------+------------+---------+-------------------------------+-------+----------+-------------+

转换后如下，
/* select#1 */ select `sakila`.`customer_t`.`customer_id` AS `customer_id`,`sakila`.`customer_t`.`store_id` AS `store_id`,`sakila`.`customer_t`.`first_name` AS `first_name`,`sakila`.`customer_t`.`last_name` AS `last_name`,`sakila`.`customer_t`.`email` AS `email`,`sakila`.`customer_t`.`address_id` AS `address_id`,`sakila`.`customer_t`.`active` AS `active`,`sakila`.`customer_t`.`create_date` AS `create_date`,`sakila`.`customer_t`.`last_update` AS `last_update` from `sakila`.`customer_t` semi join (`sakila`.`payment_t`) where (`<subquery2>`.`customer_id` = `sakila`.`customer_t`.`customer_id`)

此场景下，= ANY被作为IN处理，然后使用物化策略以及半连接转换进行了优化

场景四：... outer_expr = ALL(select inner_expr ... )

[root@sakila 12:06:21]> explain extended select * from customer_t where customer_id = ALL (select customer_id from payment_t);
+----+--------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type        | table      | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+--------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | PRIMARY            | customer_t | ALL  | NULL          | NULL | NULL    | NULL |   644 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | payment_t  | ALL  | NULL          | NULL | NULL    | NULL | 15448 |   100.00 | Using where |
+----+--------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
转换后句式如下，
/* select#1 */ select `sakila`.`customer_t`.`customer_id` AS `customer_id`,`sakila`.`customer_t`.`store_id` AS `store_id`,`sakila`.`customer_t`.`first_name` AS `first_name`,`sakila`.`customer_t`.`last_name` AS `last_name`,`sakila`.`customer_t`.`email` AS `email`,`sakila`.`customer_t`.`address_id` AS `address_id`,`sakila`.`customer_t`.`active` AS `active`,`sakila`.`customer_t`.`create_date` AS `create_date`,`sakila`.`customer_t`.`last_update` AS `last_update` from `sakila`.`customer_t` where <not>(<in_optimizer>(`sakila`.`customer_t`.`customer_id`,<exists>(/* select#2 */ select 1 from `sakila`.`payment_t` where (<cache>(`sakila`.`customer_t`.`customer_id`) <> `sakila`.`payment_t`.`customer_id`))))

从优化后的查询语句来看，= ALL被转换为exists语句，这说明使用了EXISTS strategy进行优化

非特殊子查询

1.Derived Tables。

MySQL支持对派生表形式的子查询进行优化。

对于派生类子查询的处理，优化器进一步优化了Materialization策略，只有当派生表中的数据必须加载时才可能被物化；对于join派生表的子查询类型，MySQL Optimizer支持auto_key策略优化，该策略会在物化的子查询结果集上添加索引，从而加速访问效率。

模拟SQL场景如下，

[root@sakila 02:32:16]> explain extended select customer_t.customer_id,derived_t.payment_id from customer_t join (select * from payment_t)
as derived_t on customer_t.customer_id=derived_t.customer_id where derived_t.customer_id <100;
+----+-------------+------------+-------+---------------+-------------+---------+-------------------------------+-------+----------+--------------------------+
| id | select_type | table      | type  | possible_keys | key         | key_len | ref                           | rows  | filtered | Extra                    |
+----+-------------+------------+-------+---------------+-------------+---------+-------------------------------+-------+----------+--------------------------+
|  1 | PRIMARY     | customer_t | range | PRIMARY       | PRIMARY     | 2       | NULL                          |    98 |   100.00 | Using where; Using index |
|  1 | PRIMARY     | <derived2> | ref   | <auto_key0>   | <auto_key0> | 2       | sakila.customer_t.customer_id |    23 |   100.00 | NULL                     |
|  2 | DERIVED     | payment_t  | ALL   | NULL          | NULL        | NULL    | NULL                          | 15448 |   100.00 | NULL                     |
+----+-------------+------------+-------+---------------+-------------+---------+-------------------------------+-------+----------+--------------------------+

优化器转换后如下，
/* select#1 */ select `sakila`.`customer_t`.`customer_id` AS `customer_id`,`derived_t`.`payment_id` AS `payment_id` from `sakila`.`customer_t` join
(/* select#2 */ select `sakila`.`payment_t`.`payment_id` AS `payment_id`,`sakila`.`payment_t`.`customer_id` AS `customer_id`,
`sakila`.`payment_t`.`staff_id` AS `staff_id`,`sakila`.`payment_t`.`rental_id` AS `rental_id`,`sakila`.`payment_t`.`amount` AS `amount`,
`sakila`.`payment_t`.`payment_date` AS `payment_date`,`sakila`.`payment_t`.`last_update` AS `last_update` from `sakila`.`payment_t`) `derived_t`
where ((`derived_t`.`customer_id` = `sakila`.`customer_t`.`customer_id`) and (`sakila`.`customer_t`.`customer_id` < 100))

可以看到，payment_t表首先被扫描，而在join操作时，派生表被使用索引方式访问，大大减少了不必要的扫描开销。但实际上这个SQL我们可以进行更高性能的上拉改写，从逻辑优化角度看，优化器能做到的并不多。

2.其他复杂子查询

MySQL Optimizer对具有UNION、ORDER BY ...LIMIT、GROUP BY等操作的子查询支持的也不是很好。大家可以找几个复杂的例子做下测试。

另外，对于NULL值的忠告，当一个涉及多表操作的子查询的外部表达式包含NULL值时（如NULL in (select inner_expe ...)），NULL值将会在join optimizer中进行单独处理，无法享受到优化待遇，大量NULL值可能会带来严重的性能问题。

辅助优化策略

知道了优化器怎么做，我们才知道如何让它更好的工作，那么想要构造高性能的子查询，使用策略主要是以下几点，

1、NOT NULL约束。不管是否为子查询，NULL值得处理都会是一个棘手的问题

2、建立合适的索引。优化器总是朝更容易利用索引进行优化的方向进行转换

3、多用连接而不是子查询。优化器处理子查询总是想方设法的将它转换为join，看来join才是他更喜欢的形式

4、不要太复杂。要知道，目前的MySQL查询优化器对复杂查询的优化支持的并不好

时间： 2024-10-10 12:02:59

MySQL Optimizer的子查询优化行为的相关文章

浅谈MySQL中的子查询优化技巧_Mysql

mysql的子查询的优化一直不是很友好,一直有受业界批评比较多,也是我在sql优化中遇到过最多的问题之一,你可以点击这里 ,这里来获得一些信息,mysql在处理子查询的时候,会将子查询改写,通常情况下,我们希望由内到外,也就是先完成子查询的结果,然后在用子查询来驱动外查询的表,完成查询,但是恰恰相反,子查询不会先被执行:今天希望通过介绍一些实际的案例来加深对mysql子查询的理解: 案例:用户反馈数据库响应较慢,许多业务动更新被卡住:登录到数据库中观察,发现长时间执行的sql: | 10437

MySQL中IN子查询会导致无法使用索引

原文:MySQL中IN子查询会导致无法使用索引   今天看到一个博客园的一篇关于MySQL的IN子查询优化的案例,一开始感觉有点半信半疑(如果是换做在SQL Server中,这种情况是绝对不可能的,后面会做一个简单的测试.)随后动手按照他说的做了一个表来测试验证,发现MySQL的IN子查询做的不好,确实会导致无法使用索引的情况(IN子查询无法使用所以,场景是MySQL,截止的版本是5.7.18) MySQL的测试环境 测试表如下 create table test_table2 ( id int

PostgreSQL子查询优化详解

1.2.2 PostgreSQL 1.2.2.1 S6 语句 查看查询执行计划,子查询被优化(采用物化的方式,使得子查询结果被缓存,既子查询只执行一次). postgres=# EXPLAIN SELECT * FROM t3 WHERE b3 >= ANY (SELECT b1 FROM t1); Nested Loop Semi Join   (cost=0.00..41707.39 rows=680 width=12) Join Filter: (t3.b3 >= t1.b1) ->

将SQL优化做到极致 - 子查询优化

编辑手记:子查询是SQL中比较重要的一种语法,恰当地应用会很大程度上提高SQL的性能,若用的不得当,也可能会带来很多问题.因此子查询也是SQL比较难优化的部分.今天一起来学习最常见的几种优化子查询到方式. 作者简介: 韩锋 精通包括Oracle.MySQL.informix等多种关系型数据库,有丰富的数据库架构设计开发经验.就职于宜信. 针对子查询,优化器支持了多种优化策略.Oracle查询转换功能主要有启发式(基于规则)查询转换以及基于Cost的查询转换两种,针对子查询主要有Subquery

总结MySQL建表、查询优化的一些实用小技巧

本篇文章是对MySQL建表以及查询优化的一些实用小技巧进行了详细的分析介绍,需要的朋友参考下   MySQL建表阶段是非常重要的一个环节,表结构的好坏.优劣直接影响着后续的管理维护,赶在明天上班前分享总结个人MySQL建表.MySQL查询优化积累的一些实用小技巧. 技巧一.数据表冗余记录添加时间与更新时间 我们用到的很多数据表大多情况下都会有表记录的"添加时间 (add_time)",我建议大家再新增一个记录"更新时间(update_time)"字段,在我的工作里需

SQL审核：OR展开与子查询优化案例详解

黄廷忠(网名:认真就输) 云和恩墨技术专家 个人博客:http://www.htz.pw/ 本篇整理内容是黄廷忠在"云和恩墨大讲堂"微信分享中的讲解案例,SQL 优化及 SQL审核,是从源头解决性能问题的根本手段,无论是开发人员还是 DBA,都应当持续深入的学习 SQL 开发技能,从而为解决性能问题打下根基. 第一篇为:性能为王:SQL标量子查询的优化案例分析 本篇为系列案例之二:OR展开与子查询优化案例详解. 本案例 SQL 是15年给一个省电信系统做优化时遇到的. SQL性能问题诊

Oracle数据库中基本的查询优化与子查询优化讲解_oracle

1. 查询条件合理排序 Oracle采用自下而上的顺序解析WHERE字据,从优化性能角度考虑,建议将那些可以过滤掉大量记录行的条件写在WHERE子句的末尾,而将表 之间的连接条件置于其他WHERE子句之前,即对易排查的条件先做判断处理,这样在过滤掉尽可能多的记录后再进行等值连接,可以提高检索效率. 例如: SELECT empno, ename, job, sal, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno

Mysql使用索引实现查询优化_Mysql

索引的目的在于提高查询效率,可以类比字典,如果要查"mysql"这个单词,我们肯定需要定位到m字母,然后从下往下找到y字母,再找到剩下的sql.如果没有索引,那么你可能需要把所有单词看一遍才能找到你想要的. 1.索引的优点 假设你拥有三个未索引的表t1.t2和t3,每个表都分别包含数据列i1.i2和i3,并且每个表都包含了1000条数据行,其序号从1到1000.查找某些值匹配的数据行组合的查询可能如下所示: SELECT t1.i1, t2.i2, t3.i3 FROM t1, t2,

Mysql第七天 查询优化2

接上文,其他的一些Mysql对于查询的优化,或者Mysql的不足,我们应该注意怎样优化. 关联子查询 Mysql的子查询实现比较有问题, 特别是对于IN(子查询),这样的方式. 比如:查询一个订单表中,所有支付方式为4也就是京东网银钱包的订单: EXPLAIN SELECT * FROM virtual_order vo WHERE vo.jd_order_id IN (SELECT jd_order_id FROM biz_pay_task p WHERE p.pay_type=4); //