MySQL 优化：优化 SQL 语句：优化子查询，派生表和视图引用 - 版本历史

2023年3月31日 (五) 15:17 Eijux

2023-03-31T15:17:01Z

←上一版本		2023年3月31日 (五) 23:17的版本
第1行：		第1行：
	[[category:~~MySQL~~]]		[[category:MySQL文档]]

	== 关于 ==		== 关于 ==

Eijux：/* 使用EXISTS策略优化子查询【？？？？？？？】 */

2021-05-07T14:50:29Z

使用EXISTS策略优化子查询【？？？？？？？】

←上一版本		2021年5月7日 (五) 22:50的版本
第154行：		第154行：

	考虑以下子查询比较：		考虑以下子查询比较：
	<syntaxhighlight lang="mysql">		: <syntaxhighlight lang="mysql">
	outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)		outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
	</syntaxhighlight>		</syntaxhighlight>
	: MySQL“从外到内”评估查询。也就是说，它首先获取外部表达式 outer_expr 的值，然后运行子查询并捕获其产生的行。		: MySQL“从外到内”评估查询。也就是说，它首先获取外部表达式 outer_expr 的值，然后运行子查询并捕获其产生的行。
	一个非常有用的优化是“通知”子查询仅感兴趣的行是那些内部表达式 inner_expr 等于 outer_expr 的行。这是通过'''将适当的等式推入子查询的 WHERE 子句'''以使其更具限制性来完成的。		一个非常有用的优化是“通知”子查询仅感兴趣的行是那些内部表达式 inner_expr 等于 outer_expr 的行。这是通过'''将适当的等式推入子查询的 WHERE 子句'''以使其更具限制性来完成的。
	: ~~转换后的比较如下所示：~~		: 转换后：
	'''<syntaxhighlight lang="mysql">		: '''<syntaxhighlight lang="mysql">
	EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)		EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)
	</syntaxhighlight>'''		</syntaxhighlight>'''
	转换后，MySQL 可以使用下推式相等性来限制为评估子查询而必须检查的行数。		: 转换后，MySQL 可以使用下推式相等性来限制为评估子查询而必须检查的行数。
			更一般而言，将 N 值与返回 N-value 行的子查询进行比较将经历相同的转换。
			: 如果 oe_i 和 ie_i 表示相应的外部和内部表达式值，则此子查询比较：
	更一般而言，将 N 值与返回 N-value ~~行的子查询进行比较将经历相同的转换。如果~~ oe_i 和 ie_i 表示相应的外部和内部表达式值，则此子查询比较：		: <syntaxhighlight lang="mysql">
	<syntaxhighlight lang="mysql">		(oe_1, ..., oe_N) IN (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)
	(oe_1, ..., oe_N) IN
	(SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)
	</syntaxhighlight>		</syntaxhighlight>
	变为:		: 转换后：
	<syntaxhighlight lang="mysql">		: <syntaxhighlight lang="mysql">
	EXISTS (SELECT 1 FROM ... WHERE subquery_where		EXISTS (SELECT 1 FROM ... WHERE subquery_where
	AND oe_1 = ie_1		AND oe_1 = ie_1
第182行：		第180行：
	刚刚描述的转换有其局限性。仅当我们忽略可能的“NULL”值时才有效。也就是说，只要满足以下两个条件，就可以使用“下推”策略：		刚刚描述的转换有其局限性。仅当我们忽略可能的“NULL”值时才有效。也就是说，只要满足以下两个条件，就可以使用“下推”策略：
	# '''outer_expr 和 inner_expr 不能为 NULL'''		# '''outer_expr 和 inner_expr 不能为 NULL'''
	# 无需将 NULL 与 FALSE ~~子查询结果区分开。~~		# '''无需将 NULL 与 FALSE 子查询结果区分开'''。
	#: 如果子查询是 WHERE 子句中 OR 或 AND 表达式的一部分，则 MySQL ~~假定您不在乎。优化器注意到不需要区分~~ NULL 和 FALSE 子查询结果的另一个实例是以下结构：		#: 如果子查询是 WHERE 子句中 OR 或 AND 表达式的一部分，则 MySQL 假定您不在乎。
	#:<syntaxhighlight lang="mysql">		#: 优化器注意到不需要区分 NULL 和 FALSE 子查询结果的另一个实例是以下结构：
			#: <syntaxhighlight lang="mysql">
	... WHERE outer_expr IN (subquery)		... WHERE outer_expr IN (subquery)
	</syntaxhighlight>		</syntaxhighlight>
	#: 这种情况下，无论“IN (subquery)”返回 NULL 还是 FALSE，WHERE 子句都拒绝该行。		#: 这种情况下，无论“IN (subquery)”返回 NULL 还是 FALSE，WHERE 子句都拒绝该行。

	当这些条件中的任何一个或两个都不成立时，优化将更加复杂。		当这些条件中的任何一个或两个都不成立时，优化将更加复杂。

			: 假定 outer_expr 是一个非 NULL 值，但是子查询不会产生 outer_expr = inner_expr 的行。然后“outer_expr IN (SELECT ...)”评估如下：
	假定 outer_expr 是一个非 NULL 值，但是子查询不会产生 outer_expr = inner_expr 的行。然后“outer_expr IN (SELECT ...)”评估如下：		:# NULL，如果 SELECT 产生 inner_expr 为 NULL 的任何行
	# NULL，如果 SELECT 产生 inner_expr 为 NULL 的任何行		:# FALSE，如果 SELECT 只产生非 NULL 值或什么都不产生
	# FALSE，如果 SELECT 只产生非 NULL 值或什么都不产生		: 在这种情况下，使用“outer_expr = inner_expr”查找行的方法不再有效。有必要查找这样的行，但是如果找不到，则还要查找 inner_expr 为 NULL 的行。
	~~在这种情况下，使用 outer_expr~~ = ~~inner_expr 查找行的方法不再有效。有必要查找这样的行，但是如果找不到，则还要查找~~ inner_expr 为 NULL ~~的行。粗略地讲，子查询可以转换为如下形式：~~		: 粗略地讲，子查询可以转换为如下形式：
	<syntaxhighlight lang="mysql">		: <syntaxhighlight lang="mysql">
	EXISTS (SELECT 1 FROM ... WHERE subquery_where AND		EXISTS (SELECT 1 FROM ... WHERE subquery_where AND (outer_expr=inner_expr OR inner_expr IS NULL))
	(outer_expr=inner_expr OR inner_expr IS NULL))
	</syntaxhighlight>		</syntaxhighlight>
	需要评估额外的“IS NULL”条件是 MySQL 具有 ref_or_null 访问方法的原因：		: 需要评估额外的“IS NULL”条件是 MySQL 具有 ref_or_null 访问方法的原因：
	<syntaxhighlight lang="mysql">		: <syntaxhighlight lang="mysql">
	mysql> EXPLAIN		mysql> EXPLAIN
	SELECT outer_expr IN (SELECT t2.maybe_null_key		SELECT outer_expr IN (SELECT t2.maybe_null_key
第222行：		第221行：
	Extra: Using where; Using index		Extra: Using where; Using index
	</syntaxhighlight>		</syntaxhighlight>
			* unique_subquery 和 index_subquery 特定于子查询的访问方法也具有“OR NULL”变体。【？？？】
			** 附加的“OR ... IS NULL”条件使查询的执行稍微复杂一些（并且子查询中的某些优化变得不适用），但这通常是可以容忍的。

			当 outer_expr 可以是 NULL 时，情况就更糟了。
	~~unique_subquery 和 index_subquery 特定于子查询的访问方法也具有“OR NULL”变体。~~		: 根据 NULL 的 SQL 解释为“UNKNOWN”，“NULL IN (SELECT inner_expr ...)”的计算结果应为：
			:# NULL，如果 SELECT 产生任何行
	~~附加的“OR ... IS NULL”条件使查询的执行稍微复杂一些(并且子查询中的某些优化变得不适用)，但这通常是可以容忍的。~~		:# FALSE，如果 SELECT 不产生任何行
			: 为了进行正确的评估，必须能够检查 SELECT 是否已产生任何行，因此“outer_expr = inner_expr”无法下推到子查询中。这是一个问题，因为许多现实世界中的子查询会变得非常缓慢，除非可以降低相等性。

	当 outer_expr 可以是 NULL ~~时，情况就更糟了。根据~~ NULL 的 SQL 解释为“UNKNOWN”，“NULL IN (SELECT inner_expr ...)”的计算结果应为：
	# NULL，如果 SELECT 产生任何行
	# FALSE，如果 SELECT 不产生任何行
	为了进行正确的评估，必须能够检查 SELECT 是否已产生任何行，因此“outer_expr = inner_expr”无法下推到子查询中。这是一个问题，因为许多现实世界中的子查询会变得非常缓慢，除非可以降低相等性。


第261行：		第257行：
	转换为：		转换为：
	'''<syntaxhighlight lang="mysql">		'''<syntaxhighlight lang="mysql">
	EXISTS (SELECT 1 FROM ... WHERE subquery_where		EXISTS (SELECT 1 FROM ... WHERE subquery_where AND trigcond(outer_expr=inner_expr))
	AND trigcond(outer_expr=inner_expr))
	</syntaxhighlight>'''		</syntaxhighlight>'''
	更一般而言，如果子查询比较基于几对外部和内部表达式，则转换将采用以下比较：		更一般而言，如果子查询比较基于几对外部和内部表达式，则转换将采用以下比较：
第349行：		第344行：


	~~“optimizer_switch”系统变量的“subquery_materialization_cost_based”标志可控制子查询实现和“IN”到“EXISTS”子查询转换之间的选择。~~		“optimizer_switch”系统变量的“'''subquery_materialization_cost_based'''”标志可控制子查询实现和“IN”到“EXISTS”子查询转换之间的选择。

	== 通过'''合并'''或'''实体化'''来优化派生表和视图引用【？？？？？？？】 ==		== 通过'''合并'''或'''实体化'''来优化派生表和视图引用【？？？？？？？】 ==

Eijux：/* 通过实体化来优化子查询【？？？？？？？】 */

2021-05-07T14:29:17Z

通过实体化来优化子查询【？？？？？？？】

←上一版本		2021年5月7日 (五) 22:29的版本
第97行：		第97行：
	* 用于实现的临时表由“select_type”值为“MATERIALIZED”的行和表值为“<subqueryN>”的行指示。		* 用于实现的临时表由“select_type”值为“MATERIALIZED”的行和表值为“<subqueryN>”的行指示。

	== 通过'''实体化'''~~来优化子查询【？？？？？？？】~~ ==		== 通过'''实体化'''来优化子查询 ==
	实体化通过生成子查询结果作为'''临时表'''（通常在内存中）来加快查询执行速度：		实体化通过生成子查询结果作为'''临时表'''（通常在内存中）来加快查询执行速度：
	: '''MySQL 第一次需要子查询结果时，会将结果具体化为临时表。任何随后的需要结果的时间，MySQL 都会再次引用临时表'''。优化器可以使用'''哈希索引'''对表进行索引，以使查找快速，廉价。索引包含唯一值，以消除重复项并使表更小。		: '''MySQL 第一次需要子查询结果时，会将结果具体化为临时表。任何随后的需要结果的时间，MySQL 都会再次引用临时表'''。优化器可以使用'''哈希索引'''对表进行索引，以使查找快速，廉价。索引包含唯一值，以消除重复项并使表更小。
	* 子查询实体化在可能的情况下使用'''内存'''中的临时表，如果表太大，则会退回到磁盘上的存储。		* 子查询实体化在可能的情况下使用'''内存'''中的临时表，如果表太大，则会退回到磁盘上的存储。
			* 如果'''未使用实体化'''，则优化器有时会'''将不相关的子查询重写为相关的子查询'''。
			*: 例如，以下“IN”子查询是不相关的（where_condition 仅涉及 t2 而不是 t1 的列）：
	如果'''未使用实体化'''，则优化器有时会'''将不相关的子查询重写为相关的子查询'''。		*: <syntaxhighlight lang="mysql">
	: 例如，以下“IN”子查询是不相关的（where_condition 仅涉及 t2 而不是 t1 的列）：
	: <syntaxhighlight lang="mysql">
	SELECT * FROM t1		SELECT * FROM t1
	WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);		WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);
	</syntaxhighlight>		</syntaxhighlight>
	: 优化器可能将此重写为 EXISTS 相关子查询：【！！！！！！！！！！！】		*: 优化器可能将此重写为 EXISTS 相关子查询：【！！！！！！！！！！！】
	: '''<syntaxhighlight lang="mysql">		*: '''<syntaxhighlight lang="mysql">
	SELECT * FROM t1		SELECT * FROM t1
	WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);		WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);
	</syntaxhighlight>'''		</syntaxhighlight>'''
	使用'''临时表的子查询实现避免了这样的重写'''，并使得只可能执行一次子查询，而不是对外部查询的每一行执行一次。		** 使用'''临时表的子查询实现避免了这样的重写'''，并使得只可能执行一次子查询，而不是对外部查询的每一行执行一次。


	为了使子查询实现在 MySQL ~~中使用，必须启用“optimizer_switch”系统变量“materialization”标志。~~		为了使子查询实现在 MySQL 中使用，必须启用“optimizer_switch”系统变量“'''materialization'''”标志。
	启用 materialization 标志，实现适用于出现在以下任何一种情况下的谓词：在任何位置（在选择列表 WHERE，ON，GROUP BY，HAVING 或 ORDER BY ~~中）出现的子查询谓词：【！！！】~~		: 启用 materialization 标志，实现适用于出现在以下任何一种情况下的谓词：在任何位置（在选择列表 WHERE，ON，GROUP BY，HAVING 或 ORDER BY 中）出现的子查询谓词：
	* 当没有外部表达式 oe_i 或内部表达式 ie_i 为空时，谓词具有这种形式。（N 为 1 或更大）。		* 当没有外部表达式 oe_i 或内部表达式 ie_i 为空时，谓词具有这种形式。（N 为 1 或更大）。
	*: <syntaxhighlight lang="mysql">		*: <syntaxhighlight lang="mysql">
第128行：		第126行：
	</syntaxhighlight>		</syntaxhighlight>
	* 谓词是“'''IN'''”或“'''NOT IN'''”，并且“UNKNOWN(NULL)”的结果与“FALSE”的结果具有相同的含义。		* 谓词是“'''IN'''”或“'''NOT IN'''”，并且“UNKNOWN(NULL)”的结果与“FALSE”的结果具有相同的含义。
			*: 以下示例说明了 UNKNOWN 和 FALSE 谓词评估的等价要求如何影响是否可以使用子查询实现。假定 where_condition 仅包含来自 t2 而不是 t1 的列，因此子查询是不相关的。
	以下示例说明了 UNKNOWN 和 FALSE 谓词评估的等价要求如何影响是否可以使用子查询实现。假定 where_condition 仅包含来自 t2 而不是 t1 的列，因此子查询是不相关的。		*: 此查询需要具体实现：
	: 此查询需要具体实现：		*: <syntaxhighlight lang="mysql">
	: <syntaxhighlight lang="mysql">
	SELECT * FROM t1		SELECT * FROM t1
	WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);		WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);
	</syntaxhighlight>		</syntaxhighlight>
	: 在这里，IN 谓词返回 UNKNOWN 或 FALSE 都没有关系。无论哪种方式，t1 中的行都不会包含在查询结果中。		*: 在这里，IN 谓词返回 UNKNOWN 或 FALSE 都没有关系。无论哪种方式，t1 中的行都不会包含在查询结果中。
	: 以下查询是不使用子查询实现的示例，其中 t2.b 是可为空的列：		*: 以下查询是不使用子查询实现的示例，其中 t2.b 是可为空的列：
	: <syntaxhighlight lang="mysql">		*: <syntaxhighlight lang="mysql">
	SELECT * FROM t1		SELECT * FROM t1
	WHERE (t1.a,t1.b) NOT IN (SELECT t2.a,t2.b FROM t2		WHERE (t1.a,t1.b) NOT IN (SELECT t2.a,t2.b FROM t2 WHERE where_condition);
	WHERE where_condition);
	</syntaxhighlight>		</syntaxhighlight>

Eijux：/* 使用半联接转换优化子查询，派生表和视图引用【？？？？？？？】 */

2021-05-07T14:18:51Z

使用半联接转换优化子查询，派生表和视图引用【？？？？？？？】

←上一版本		2021年5月7日 (五) 22:18的版本
第32行：		第32行：
	: 假设 class_num 是 class 表中的主键，则可以通过使用“SELECT DISTINCT”来抑制重复，但是先生成所有匹配的行仅用于稍后消除重复是无效的。		: 假设 class_num 是 class 表中的主键，则可以通过使用“SELECT DISTINCT”来抑制重复，但是先生成所有匹配的行仅用于稍后消除重复是无效的。
	: 可以通过使用子查询获得相同的无重复结果：		: 可以通过使用子查询获得相同的无重复结果：
	<syntaxhighlight lang="mysql">		: <syntaxhighlight lang="mysql">
	SELECT class_num, class_name		SELECT class_num, class_name
	FROM class		FROM class
	WHERE class_num IN (SELECT class_num FROM roster);		WHERE class_num IN (SELECT class_num FROM roster);
	</syntaxhighlight>		</syntaxhighlight>
	在这里，优化器可以识别“IN”子句要求子查询仅返回 roster ~~表中每个班级号的一个实例。在这种情况下，查询可以使用半联接。也就是说，该操作仅返回~~ class 中的每一行的一个实例，该实例与 roster ~~中的行匹配。~~		: 在这里，优化器可以识别“IN”子句要求子查询仅返回 roster 表中每个班级号的一个实例。在这种情况下，查询可以使用'''半联接'''。
			: 也就是说，'''该操作仅返回 class 中的每一行的一个实例，该实例与 roster 中的行匹配'''。

			* 外部查询规范中允许使用外部联接和内部联接语法，并且表引用可以是基表，派生表或视图引用。

	~~外部查询规范中允许使用外部联接和内部联接语法，并且表引用可以是基表，派生表或视图引用。~~

			在 MySQL 中，子查询必须满足以下条件才能作为“半联接”进行处理：【！！！】
	在 MySQL ~~中，子查询必须满足以下条件才能作为半联接进行处理：【！！！】~~
	# 它必须是出现在“WHERE”或“ON”子句的顶级（可能作为“AND”表达式中的术语）的“IN”（或“=ANY”）子查询。例如：		# 它必须是出现在“WHERE”或“ON”子句的顶级（可能作为“AND”表达式中的术语）的“IN”（或“=ANY”）子查询。例如：
	#: <syntaxhighlight lang="mysql">		#: <syntaxhighlight lang="mysql">
第58行：		第58行：
	# “STRAIGHT_JOIN”修饰符不能存在。		# “STRAIGHT_JOIN”修饰符不能存在。
	# 外部表和内部表的总数必须小于联接中允许的最大表数。		# 外部表和内部表的总数必须小于联接中允许的最大表数。
	子查询可以是相关的或不相关的。除非已使用“ORDER BY”，否则允许“DISTINCT”，也允许“LIMIT”。		# 子查询可以是相关的或不相关的。除非已使用“ORDER BY”，否则允许“DISTINCT”，也允许“LIMIT”。
			如果子查询满足上述条件，MySQL 会将其转换为半联接并，从以下策略中进行基于成本的选择：
			# 将子查询转换为联接，或使用表提取，并将查询作为子查询表与外部表之间的内部联接运行。（表提取：将表从子查询中拉出到外部查询）
	如果子查询满足上述条件，MySQL ~~会将其转换为半联接并从以下策略中进行基于成本的选择：~~
	# ~~将子查询转换为联接，或使用表提取，并将查询作为子查询表与外部表之间的内部联接运行。表提取将表从子查询中拉出到外部查询。~~
	# 重复删除：像运行连接一样运行半连接，并使用临时表删除重复记录。		# 重复删除：像运行连接一样运行半连接，并使用临时表删除重复记录。
	# ~~FirstMatch：当扫描内部表中的行组合并且给定值组有多个实例时，请选择一个而不是全部返回。这种“快捷方式”扫描可以消除不必要行的产生。~~		# FirstMatch（首次匹配）：当扫描内部表中的行组合并且给定值组有多个实例时，选择一个返回而不是全部返回。（这种“快捷方式”扫描可以消除不必要行的产生）
	# LooseScan（松散扫描）：使用索引扫描子查询表，该索引允许从每个子查询的值组中选择一个值。		# LooseScan（松散扫描）：使用索引扫描子查询表，该索引允许从每个子查询的值组中选择一个值。
	# 将子查询实体化到用于执行联接的索引临时表中，在该临时表中，索引用于删除重复项。当将临时表与外部表连接时，该索引以后也可能用于查找。如果不是，则扫描表。		# 将子查询“实体化”到用于执行联接的索引临时表中，在该临时表中，索引用于删除重复项。当将临时表与外部表连接时，该索引以后也可能用于查找。如果不是，则扫描表。


第77行：		第75行：


	优化器将视图和派生表的处理差异最小化。这会影响使用“STRAIGHT_JOIN”修饰符的查询以及带有“IN”子查询的视图，该视图可以转换为半联接。以下查询说明了这一点，因为处理中的更改导致转换中的更改，从而导致不同的执行策略：		优化器将视图和派生表的处理差异最小化。这会影响使用“STRAIGHT_JOIN”修饰符的查询以及带有“IN”子查询的视图，该视图可以转换为半联接。

			以下查询说明了这一点，因为处理中的更改导致转换中的更改，从而导致不同的执行策略：
	<syntaxhighlight lang="mysql">		<syntaxhighlight lang="mysql">
	CREATE VIEW v AS		CREATE VIEW v AS
第85行：		第85行：
	FROM t2);		FROM t2);

	SELECT STRAIGHT_JOIN *		SELECT STRAIGHT_JOIN * FROM t3 JOIN v ON t3.x = v.a;
	FROM t3 JOIN v ON t3.x = v.a;
	</syntaxhighlight>		</syntaxhighlight>
	优化器首先查看视图，并将“IN”子查询转换为半联接，然后检查是否有可能将视图合并到外部查询中。因为外部查询中的'''“STRAIGHT_JOIN”修饰符防止半联接'''，所以优化器拒绝合并，从而导致使用物化表进行派生表评估。		优化器首先查看视图，并将“IN”子查询转换为半联接，然后检查是否有可能将视图合并到外部查询中。因为外部查询中的'''“STRAIGHT_JOIN”修饰符防止半联接'''，所以优化器拒绝合并，从而导致使用物化表进行派生表评估。

Eijux：/* 关于 */

2021-05-07T14:08:17Z

关于

←上一版本		2021年5月7日 (五) 22:08的版本
第10行：		第10行：
	## 实体化		## 实体化
	## EXISTS 策略		## EXISTS 策略
	# ~~对于派生表，优化器具有以下选择（这也适用于视图引用）：~~		# 对于“派生表”（“from”中的“select”），优化器具有以下选择（这也适用于视图引用）：
	## 将派生表'''合并'''到外部查询块中		## 将派生表'''合并'''到外部查询块中
	## 将派生表'''实体化'''为内部临时表		## 将派生表'''实体化'''为内部临时表

Eijux：/* 关于 */

2021-05-07T14:07:28Z

关于

←上一版本		2021年5月7日 (五) 22:07的版本
第11行：		第11行：
	## EXISTS 策略		## EXISTS 策略
	# 对于派生表，优化器具有以下选择（这也适用于视图引用）：		# 对于派生表，优化器具有以下选择（这也适用于视图引用）：
	## ~~将派生表合并到外部查询块中~~		## 将派生表'''合并'''到外部查询块中
	## ~~将派生表实体化为内部临时表~~		## 将派生表'''实体化'''为内部临时表


	* 使用子查询修改单个表的“UPDATE”和“DELETE”语句的局限性在于，优化器不使用半联接或物化子查询优化。解决方法是，尝试将它们重写为使用联接而不是子查询的多表“UPDATE”和“DELETE”语句。		* 使用子查询修改单个表的“UPDATE”和“DELETE”语句的局限性在于，优化器不使用半联接或物化子查询优化。
			*: 解决方法是，尝试将它们重写为使用'''联接'''而不是子查询的多表“UPDATE”和“DELETE”语句。

	== 使用'''半联接'''转换优化子查询，派生表和视图引用【？？？？？？？】 ==		== 使用'''半联接'''转换优化子查询，派生表和视图引用【？？？？？？？】 ==

2021年4月26日 (一) 04:14 Eijux

2021-04-26T04:14:00Z

←上一版本		2021年4月26日 (一) 12:14的版本
第17行：		第17行：
	* 使用子查询修改单个表的“UPDATE”和“DELETE”语句的局限性在于，优化器不使用半联接或物化子查询优化。解决方法是，尝试将它们重写为使用联接而不是子查询的多表“UPDATE”和“DELETE”语句。		* 使用子查询修改单个表的“UPDATE”和“DELETE”语句的局限性在于，优化器不使用半联接或物化子查询优化。解决方法是，尝试将它们重写为使用联接而不是子查询的多表“UPDATE”和“DELETE”语句。

	== 使用'''半联接'''~~转换优化子查询，派生表和视图引用~~ ==		== 使用'''半联接'''转换优化子查询，派生表和视图引用【？？？？？？？】 ==
	半联接是准备时转换，它启用多种执行策略，例如：表提取，重复删除，首次匹配，松散扫描和实体化。		半联接是准备时转换，它启用多种执行策略，例如：表提取，重复删除，首次匹配，松散扫描和实体化。

第97行：		第97行：
	* 用于实现的临时表由“select_type”值为“MATERIALIZED”的行和表值为“<subqueryN>”的行指示。		* 用于实现的临时表由“select_type”值为“MATERIALIZED”的行和表值为“<subqueryN>”的行指示。

	== 通过'''实体化'''~~来优化子查询~~ ==		== 通过'''实体化'''来优化子查询【？？？？？？？】 ==
	实体化通过生成子查询结果作为'''临时表'''（通常在内存中）来加快查询执行速度：		实体化通过生成子查询结果作为'''临时表'''（通常在内存中）来加快查询执行速度：
	: '''MySQL 第一次需要子查询结果时，会将结果具体化为临时表。任何随后的需要结果的时间，MySQL 都会再次引用临时表'''。优化器可以使用'''哈希索引'''对表进行索引，以使查找快速，廉价。索引包含唯一值，以消除重复项并使表更小。		: '''MySQL 第一次需要子查询结果时，会将结果具体化为临时表。任何随后的需要结果的时间，MySQL 都会再次引用临时表'''。优化器可以使用'''哈希索引'''对表进行索引，以使查找快速，廉价。索引包含唯一值，以消除重复项并使表更小。
第153行：		第153行：
	* 对于扩展的 EXPLAIN 输出，随后的“SHOW WARNINGS”显示的文本包括“materialize”和“materialized-subquery”。		* 对于扩展的 EXPLAIN 输出，随后的“SHOW WARNINGS”显示的文本包括“materialize”和“materialized-subquery”。

	== 使用'''EXISTS策略'''~~优化子查询~~ ==		== 使用'''EXISTS策略'''优化子查询【？？？？？？？】 ==
	某些优化适用于使用“IN”（或“=ANY”）运算符测试子查询结果的比较。本节讨论这些优化，尤其是针对“NULL”值所带来的挑战。		某些优化适用于使用“IN”（或“=ANY”）运算符测试子查询结果的比较。本节讨论这些优化，尤其是针对“NULL”值所带来的挑战。

第355行：		第355行：
	“optimizer_switch”系统变量的“subquery_materialization_cost_based”标志可控制子查询实现和“IN”到“EXISTS”子查询转换之间的选择。		“optimizer_switch”系统变量的“subquery_materialization_cost_based”标志可控制子查询实现和“IN”到“EXISTS”子查询转换之间的选择。

	== 通过'''合并'''或'''实体化'''~~来优化派生表和视图引用~~ ==		== 通过'''合并'''或'''实体化'''来优化派生表和视图引用【？？？？？？？】 ==
	优化器可以使用两种策略来处理派生表引用（这也适用于视图引用）：		优化器可以使用两种策略来处理派生表引用（这也适用于视图引用）：
	# 将派生表'''合并'''到'''外部查询块'''中		# 将派生表'''合并'''到'''外部查询块'''中

Eijux：/* 使用EXISTS策略优化子查询 */

2021-04-26T04:13:00Z

使用EXISTS策略优化子查询

显示更改

Eijux：/* 通过合并或实体化来优化派生表和视图引用 */

2021-04-26T03:39:32Z

通过合并或实体化来优化派生表和视图引用

←上一版本		2021年4月26日 (一) 11:39的版本
第156行：		第156行：

	== 通过'''合并'''或'''实体化'''来优化派生表和视图引用 ==		== 通过'''合并'''或'''实体化'''来优化派生表和视图引用 ==
			优化器可以使用两种策略来处理派生表引用（这也适用于视图引用）：
			# 将派生表'''合并'''到'''外部查询块'''中
			#: <syntaxhighlight lang="mysql">
			SELECT * FROM (SELECT * FROM t1) AS derived_t1;
			</syntaxhighlight>
			#: 通过合并派生表 derived_t1，该查询的执行类似于：
			#: <syntaxhighlight lang="mysql">
			SELECT * FROM t1;
			</syntaxhighlight>
			# 将派生表'''实体化'''为'''内部临时表'''
			#: <syntaxhighlight lang="mysql">
			SELECT *
			FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1
			WHERE t1.f1 > 0;
			</syntaxhighlight>
			#: 通过合并派生表 derived_t2，该查询的执行类似于：
			#: <syntaxhighlight lang="mysql">
			SELECT t1.*, t2.f1
			FROM t1 JOIN t2 ON t1.f2=t2.f1
			WHERE t1.f1 > 0;
			</syntaxhighlight>
			* 优化器以相同的方式处理派生表和视图引用：尽可能避免不必要的实现，这可以将条件从外部查询下推到派生表，并产生更有效的执行计划。
			* 如果合并将导致一个外部查询块引用超过 61 个基本表，则优化程序将选择实现。


			如果满足以下所有条件，则优化器将派生表或视图引用中的“ORDER BY”子句传播到外部查询块：
			# 外部查询未分组或聚合。
			# 外部查询未指定“DISTINCT”，“HAVING”或“ORDER BY”。
			# 外部查询将此派生表或视图引用作为“FROM”子句中的唯一源。
			否则，优化器将忽略“ORDER BY”子句。


			可以使用以下方法来影响优化器是否尝试将派生表和视图引用合并到外部查询块中：
			* 假定没有其他规则阻止合并，则可以使用“optimizer_switch”系统变量的“derived_merge”标志。
			** 默认情况下，该标志启用以允许合并。禁用该标志可防止合并并避免“ER_UPDATE_TABLE_USED”错误。
			** derived_merge标志还适用于不包含 ALGORITHM 子句的视图。因此，如果使用与子查询等效的表达式的视图引用发生 ER_UPDATE_TABLE_USED 错误，则在视图定义中添加“ALGORITHM=TEMPTABLE”可以防止合并并优先于 derived_merge 值。【？？？】
			* 可以通过在子查询中使用任何阻止合并的构造来禁用合并，尽管这些构造对实现的影响不那么明显。
			*: 防止合并的构造与派生表和视图引用相同：
			** 聚合函数（SUM()，MIN()，MAX()，COUNT()等）
			** DISTINCT
			** GROUP BY
			** HAVING
			** LIMIT
			** UNION 或 UNION ALL
			** 选择列表中的子查询
			** 分配给用户变量
			** 仅引用 Literals 值（在这种情况下，没有基础表）


			如果优化器选择实体化策略而不是合并派生表，则它将按以下方式处理查询：
			* 优化程序将派生表的'''实体化推迟'''到'''查询执行期间需要其内容之前'''。这会提高性能，因为延迟实现可能会导致根本不必这样做。考虑一个将派生表的结果连接到另一个表的查询：如果优化器首先处理该另一个表并发现它不返回任何行，则不需要进一步执行联接，并且优化器可以完全跳过实体化派生表。【延迟实体化】
			* 在查询执行期间，优化器可以将'''索引'''添加到派生表中，以加快从中获取行的速度。【添加索引】


			【延迟实体化】
			# 考虑以下 EXPLAIN 语句，用于包含派生表的 SELECT 查询：
			#: <syntaxhighlight lang="mysql">
			EXPLAIN SELECT * FROM (SELECT * FROM t1) AS derived_t1;
			</syntaxhighlight>
			#: 优化程序通过将其推迟到 SELECT 执行期间需要结果之前来避免实体化派生表。在这种情况下，查询不会执行（因为它发生在 EXPLAIN 语句中），因此永远不需要结果。
			# 即使对于已执行的查询，派生表实体化的延迟也可以使优化程序完全避免实现。发生这种情况时，查询执行将比实现实现所需的时间更快。考虑以下查询，该查询将派生表的结果连接到另一个表：
			#:<syntaxhighlight lang="mysql">
			SELECT *
			FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2
			ON t1.f2=derived_t2.f1
			WHERE t1.f1 > 0;
			</syntaxhighlight>
			#:如果最先进行优化的过程 t1 和 WHERE 子句产生的结果为空，则联接必须一定为空，并且派生表不必实现。
			【添加索引】
			# 对于派生表需要实体化的情况，优化器可以向实现表添加索引以加快对其的访问。如果这样的索引启用对表的 ref 访问，则可以大大减少查询执行期间读取的数据量。考虑以下查询：
			#:<syntaxhighlight lang="mysql">
			SELECT *
			FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2
			ON t1.f1=derived_t2.f1;
			</syntaxhighlight>
			#:如果这样做可以使 ref 访问用于最低成本的执行计划，则优化器将在 derived_t2 的 f1 列上构造索引。添加索引后，优化器可以将物化派生表与具有索引的常规表相同，并且它从生成的索引中也可以得到类似的好处。与没有索引的查询执行成本相比，索引创建的开销可以忽略不计。如果 ref 访问会比其他访问方法带来更高的成本，则优化器不会创建索引，也不会丢失任何内容。


			对于优化程序跟踪输出，未将合并的派生表或视图引用显示为节点。仅其基础表出现在顶部查询的计划中。【？？？】

Eijux：/* 通过实体化来优化子查询 */

2021-04-26T03:20:21Z

通过实体化来优化子查询

@@ 第98行： / 第98行： @@
 == 通过'''实体化'''来优化子查询 ==
 == 使用'''EXISTS策略'''优化子查询 ==
 == 通过'''合并'''或'''实体化'''来优化派生表和视图引用 ==

MySQL 优化：优化 SQL 语句：优化子查询，派生表和视图引用 - 版本历史

2023年3月31日 (五) 15:17 Eijux

Eijux：​/* 使用EXISTS策略优化子查询【？？？？？？？】 */

Eijux：​/* 通过实体化来优化子查询【？？？？？？？】 */

Eijux：​/* 使用半联接转换优化子查询，派生表和视图引用【？？？？？？？】 */

Eijux：​/* 关于 */

Eijux：​/* 关于 */

2021年4月26日 (一) 04:14 Eijux

Eijux：​/* 使用EXISTS策略优化子查询 */

Eijux：​/* 通过合并或实体化来优化派生表和视图引用 */

Eijux：​/* 通过实体化来优化子查询 */

Eijux：/* 使用EXISTS策略优化子查询【？？？？？？？】 */

Eijux：/* 通过实体化来优化子查询【？？？？？？？】 */

Eijux：/* 使用半联接转换优化子查询，派生表和视图引用【？？？？？？？】 */

Eijux：/* 关于 */

Eijux：/* 关于 */

Eijux：/* 使用EXISTS策略优化子查询 */

Eijux：/* 通过合并或实体化来优化派生表和视图引用 */

Eijux：/* 通过实体化来优化子查询 */