8.8 了解查询执行计划
8.8.1 使用 EXPLAIN 优化查询
该EXPLAIN语句提供有关 MySQL 如何执行语句的信息:
- EXPLAIN与 SELECT, DELETE, INSERT, REPLACE, 和 UPDATE语句一起使用。
- 当EXPLAIN与可解释语句一起使用时,MySQL 显示来自优化器的有关语句执行计划的信息。也就是说,MySQL 解释了它将如何处理该语句,包括有关表如何连接以及以何种顺序连接的信息。有关使用 EXPLAIN获取执行计划信息的信息,请参阅第 8.8.2 节,“EXPLAIN 输出格式”。
- 当EXPLAIN与 而不是可解释的语句一起使用时,它显示在命名连接中执行的语句的执行计划。请参阅第 8.8.4 节,“获取命名连接的执行计划信息”。
FOR CONNECTION
connection_id
- 对于SELECT语句, EXPLAIN生成可以使用显示的附加执行计划信息 SHOW WARNINGS。请参阅 第 8.8.3 节,“扩展 EXPLAIN 输出格式”。
- EXPLAIN对于检查涉及分区表的查询很有用。请参阅 第 22.3.5 节,“获取有关分区的信息”。
- 该
FORMAT
选项可用于选择输出格式。TRADITIONAL
以表格格式显示输出。如果没有FORMAT
选项,这是默认设置。JSON
format 以 JSON 格式显示信息。
在 的帮助下EXPLAIN,您可以查看应该在哪里为表添加索引,以便通过使用索引查找行来更快地执行语句。您还可以使用 EXPLAIN检查优化器是否以最佳顺序连接表。要提示优化器使用与语句中表命名顺序相对应的连接顺序,请以 SELECT语句开头,SELECT STRAIGHT_JOIN
而不仅仅是SELECT. (请参阅 第 13.2.9 节,“SELECT 语句”。)但是, STRAIGHT_JOIN
可能会阻止使用索引,因为它禁用了半连接转换。看 第 8.2.2.1 节,“使用半连接转换优化子查询、派生表和视图引用”。
优化器跟踪有时可能会提供与EXPLAIN. 但是,优化器跟踪格式和内容可能会因版本而异。有关详细信息,请参阅 MySQL 内部:跟踪优化器。
如果您认为索引没有被使用时遇到问题,请运行ANALYZE TABLE以更新表统计信息,例如键的基数,这可能会影响优化器所做的选择。请参阅 第 13.7.2.1 节,“ANALYZE TABLE 语句”。
笔记
EXPLAIN也可用于获取有关表中列的信息。 是和 的同义词。有关更多信息,请参阅第 13.8.1 节,“DESCRIBE 语句”和 第 13.7.5.5 节,“SHOW COLUMNS 语句”。 EXPLAIN tbl_nameDESCRIBE
tbl_name
SHOW COLUMNS FROM
tbl_name
8.8.2 解释输出格式
该EXPLAIN语句提供有关 MySQL 如何执行语句的信息。 EXPLAIN与 SELECT, DELETE, INSERT, REPLACE, 和 UPDATE语句一起使用。
EXPLAINSELECT为语句中使用的每个表返回一行信息 。它按照 MySQL 在处理语句时读取它们的顺序列出输出中的表。MySQL 使用嵌套循环连接方法解析所有连接。这意味着 MySQL 从第一个表中读取一行,然后在第二个表、第三个表中找到匹配的行,依此类推。处理完所有表后,MySQL 会输出选定的列并通过表列表回溯,直到找到匹配行较多的表。从此表中读取下一行,并继续处理下一个表。
EXPLAIN输出包括分区信息。此外,对于SELECT 语句,EXPLAIN生成可以显示的扩展信息 SHOW WARNINGS( EXPLAIN请参阅 第 8.8.3 节,“扩展 EXPLAIN 输出格式”)。
笔记
在较旧的 MySQL 版本中,分区和扩展信息是使用 EXPLAIN PARTITIONS和 生成的EXPLAIN EXTENDED。这些语法仍然被认为具有向后兼容性,但现在默认启用分区和扩展输出,因此PARTITIONS
andEXTENDED
关键字是多余的并且已弃用。使用它们会导致警告;EXPLAIN期望它们在未来的 MySQL 版本中 从语法中删除。
您不能在同一 语句中同时使用 deprecatedPARTITIONS
和关键字。此外,这些关键字都不能与 选项一起使用。 EXTENDED
EXPLAINFORMAT
笔记
MySQL Workbench 具有 Visual Explain 功能,可提供 EXPLAIN输出的可视化表示。请参阅 教程:使用 Explain 提高查询性能。
本节介绍由 生成的输出列 EXPLAIN。后面的部分提供了有关 type 和 Extra 列的附加信息。
来自的每个输出行EXPLAIN 提供有关一个表的信息。每一行都包含 表 8.1 “解释输出列”中总结的值,并在表后进行了更详细的描述。列名显示在表格的第一列;第二列提供了 FORMAT=JSON
使用时输出中显示的等效属性名称。
柱子 | JSON 名称 | 意义 |
|
| |
没有任何 |
| |
| 输出行的表 | |
| 匹配的分区 | |
| 联接类型 | |
| 可供选择的索引 | |
| 实际选择的索引 | |
| 所选密钥的长度 | |
| 与索引比较的列 | |
| 估计要检查的行数 | |
| 按表条件过滤的行百分比 | |
没有任何 | 附加信息 |
笔记
JSON 格式的输出 NULL
中未显示的 JSON 属性。EXPLAIN
SELECT标识符 。这是查询中的序号 SELECT。NULL
如果该行引用其他行的联合结果,则该值可以是。在这种情况下,该 table
列显示一个值 ,表示该行是指具有 和值 的行的并 集。 <union
M
,
N
>id
MN
的类型SELECT,可以是下表中显示的任何一种。JSON 格式将类型EXPLAIN
公开 SELECT
为 a 的属性 query_block
,除非它是 SIMPLE
or PRIMARY
。JSON 名称(如果适用)也显示在表中。
| JSON 名称 | 意义 |
| 没有任何 | |
| 没有任何 | 最外层SELECT |
没有任何 | ||
|
| |
|
| 的结果UNION。 |
没有任何 | 首先SELECT在子查询中 | |
|
| 首先SELECT在子查询中,依赖于外部查询 |
| 没有任何 | 派生表 |
|
| 物化子查询 |
|
| 一个子查询,其结果无法缓存,必须为外部查询的每一行重新计算 |
|
| UNION 属于不可缓存子查询 的第二个或以后的选择(请参阅 参考资料 |
DEPENDENT
通常表示使用相关子查询。请参阅 第 13.2.10.7 节,“相关子查询”。
DEPENDENT SUBQUERY
评价不同于UNCACHEABLE SUBQUERY
评价。对于DEPENDENT SUBQUERY
,子查询仅针对其外部上下文中变量的每组不同值重新评估一次。对于 UNCACHEABLE SUBQUERY
,为外部上下文的每一行重新评估子查询。
子查询的可缓存性不同于查询缓存中查询结果的缓存(在 第 8.10.3.1 节“查询缓存如何操作”中进行了描述)。子查询缓存发生在查询执行期间,而查询缓存仅用于在查询执行完成后存储结果。
当您指定FORMAT=JSON
with EXPLAIN
时,输出没有直接等同于 select_type
;的单一属性。该 query_block
属性对应于给定的SELECT
. 与刚刚显示的大多数子查询类型等效的属性SELECT
都可用(例如 materialized_from_subquery
for MATERIALIZED
),并在适当时显示。SIMPLE
or没有 JSON 等价物 PRIMARY
。
select_type
非语句 的值SELECT显示受影响表的语句类型。例如,select_type
is DELETE
for DELETE语句。
输出行所引用的表的名称。这也可以是以下值之一:
-
<union
M
,
N
>
:该行是指具有 和id
值 的行的M
并 集N
。<derived
N
>
:该行是指值为 的行的派生表id
结果N
。例如,派生表可能来自FROM
子句中的子查询。<subquery
N
>
:该行指的是id
值为的行的具体化子查询的结果N
。请参阅 第 8.2.2.2 节,“使用物化优化子查询”。
partitions
(JSON 名称partitions
:)
查询将匹配记录的分区。该值适用NULL
于非分区表。请参阅 第 22.3.5 节,“获取有关分区的信息”。
联接类型。有关不同类型的描述,请参阅 EXPLAIN
连接类型。
该possible_keys
列指示 MySQL 可以选择从中查找此表中的行的索引。请注意,此列完全独立于输出中显示的表格顺序 EXPLAIN。这意味着某些键在possible_keys
实际中可能无法与生成的表顺序一起使用。
如果此列是NULL
(或在 JSON 格式的输出中未定义),则没有相关索引。在这种情况下,您可以通过检查WHERE
子句来检查它是否引用了适合索引的某些列或列,从而提高查询的性能。如果是这样,请创建一个适当的索引并 EXPLAIN再次检查查询。请参阅 第 13.1.8 节,“ALTER TABLE 语句”。
要查看表有哪些索引,请使用. SHOW INDEX FROM
tbl_name
该key
列指示 MySQL 实际决定使用的键(索引)。如果 MySQL 决定使用其中一个possible_keys
索引来查找行,则该索引被列为键值。
可以key
命名值中不存在的索引 possible_keys
。如果没有possible_keys
索引适合查找行,但查询选择的所有列都是其他索引的列,则可能会发生这种情况。也就是说,命名索引覆盖了选定的列,因此虽然它不用于确定要检索哪些行,但索引扫描比数据行扫描更有效。
对于InnoDB
,即使查询还选择了主键,二级索引也可能覆盖选定的列,因为InnoDB
每个二级索引都存储了主键值。如果 key
是NULL
,则 MySQL 没有找到可用于更有效地执行查询的索引。
要强制 MySQL 使用或忽略列中列出的索引 ,请在查询中possible_keys
使用 FORCE INDEX
、USE INDEX
或IGNORE INDEX
。请参阅第 8.9.4 节,“索引提示”。
对于MyISAM
表,运行 ANALYZE TABLE有助于优化器选择更好的索引。对于 MyISAM
表,myisamchk --analyze 也是如此。请参阅 第 13.7.2.1 节,“ANALYZE TABLE 语句”和 第 7.6 节,“MyISAM 表维护和崩溃恢复”。
该key_len
列指示 MySQL 决定使用的密钥的长度。的值 key_len
使您能够确定 MySQL 实际使用的多部分键的多少部分。如果key
列说 NULL
,key_len
列也说NULL
。
由于密钥存储格式的原因,列的密钥长度可能NULL
比NOT NULL
列的大一。
该ref
列显示将哪些列或常量与列中指定的索引进行比较以 key
从表中选择行。
如果值为func
,则使用的值是某个函数的结果。要查看哪个功能,请使用 SHOW WARNINGS以下 功能EXPLAIN查看扩展 EXPLAIN输出。该函数实际上可能是一个运算符,例如算术运算符。
该rows
列指示 MySQL 认为它必须检查以执行查询的行数。
对于InnoDB表格,这个数字是一个估计值,可能并不总是准确的。
该filtered
列指示按表条件过滤的表行的估计百分比。最大值为 100,这意味着没有过滤行。从 100 开始减小的值表示过滤量增加。 rows
显示检查的估计行数,rows
× filtered
显示与下表连接的行数。例如,如果 rows
是 1000 并且 filtered
是 50.00 (50%),则要与下表连接的行数是 1000 × 50% = 500。
此列包含有关 MySQL 如何解析查询的附加信息。有关不同值的描述,请参阅 EXPLAIN
额外信息。
没有与该 Extra
列对应的单个 JSON 属性;但是,此列中可能出现的值将作为 JSON 属性或属性文本公开message
。
输出列描述type
了 EXPLAIN表是如何连接的。在 JSON 格式的输出中,这些是作为access_type
属性的值找到的。以下列表描述了连接类型,按从最佳到最差的顺序排列:
该表只有一行(= 系统表)。const
这是连接类型 的一个特例 。
该表最多有一个匹配行,在查询开始时读取。因为只有一行,所以这一行中列的值可以被优化器的其余部分视为常量。 const表非常快,因为它们只被读取一次。
constPRIMARY KEY
当您将 a或 UNIQUE
索引的所有部分与常量值进行比较时使用。在以下查询中,tbl_name
可以用作const 表:
SELECT
*
FROM
tbl_name
WHERE
primary_key=1;
SELECT
*
FROM
tbl_name
WHERE
primary_key_part1=1
AND
primary_key_part2=2;
对于先前表中的每个行组合,从该表中读取一行。除了 systemand const类型,这是最好的连接类型。当连接使用索引的所有部分并且索引是 a PRIMARY KEY
或UNIQUE NOT NULL
索引时使用它。
eq_ref可用于使用 =
运算符比较的索引列。比较值可以是常量或使用在此表之前读取的表中的列的表达式。在以下示例中,MySQL 可以使用 eq_ref连接来处理 ref_table
:
SELECT
*
FROM
ref_table,other_table
WHERE
ref_table.key_column=other_table.column;
SELECT
*
FROM
ref_table,other_table
WHERE
ref_table.key_column_part1=other_table.column
AND
ref_table.key_column_part2=1;
对于先前表中的每个行组合,从该表中读取具有匹配索引值的所有行。ref如果连接仅使用键的最左前缀或键不是 aPRIMARY KEY
或 UNIQUE
索引(换句话说,如果连接不能基于键值选择单行),则使用。如果使用的键只匹配几行,这是一个很好的连接类型。
ref可用于使用 =
or<=>
运算符比较的索引列。在以下示例中,MySQL 可以使用 ref连接来处理 ref_table
:
SELECT
*
FROM
ref_table
WHERE
key_column=expr;
SELECT
*
FROM
ref_table,other_table
WHERE
ref_table.key_column=other_table.column;
SELECT
*
FROM
ref_table,other_table
WHERE
ref_table.key_column_part1=other_table.column
AND
ref_table.key_column_part2=1;
这种连接类型类似于 ref,但另外 MySQL 会额外搜索包含NULL
值的行。这种连接类型优化最常用于解析子查询。在以下示例中,MySQL 可以使用 ref_or_null连接来处理ref_table
:
SELECT
*
FROM
ref_table
WHERE
key_column=expr
OR
key_column
IS
NULL;
此连接类型表明使用了索引合并优化。在这种情况下,key
输出行中的列包含所用索引的列表,并 key_len
包含所用索引的最长键部分的列表。有关更多信息,请参阅 第 8.2.1.3 节,“索引合并优化”。
此类型替换 以下形式eq_ref的某些 IN
子查询:
value
IN
(SELECT
primary_key
FROM
single_table
WHERE
some_expr)
unique_subquery只是一个索引查找功能,完全替代子查询以提高效率。
此连接类型类似于 unique_subquery. 它替换IN
子查询,但它适用于以下形式的子查询中的非唯一索引:
value
IN
(SELECT
key_column
FROM
single_table
WHERE
some_expr)
仅检索给定范围内的行,使用索引选择行。输出行中的key
列指示使用了哪个索引。key_len
包含使用的最长的关键部分。该ref
列适用 NULL
于这种类型。
range可以在使用 , , , , , , , , , , 或 运算符中的任何 =一个 <>将 >键 >=列 <与 <=常量 IS NULL进行 <=>比较 BETWEEN时 LIKE使用 IN():
SELECT
*
FROM
tbl_name
WHERE
key_column
=
10;
SELECT
*
FROM
tbl_name
WHERE
key_column
BETWEEN
10
and
20;
SELECT
*
FROM
tbl_name
WHERE
key_column
IN
(10,20,30);
SELECT
*
FROM
tbl_name
WHERE
key_part1
=
10
AND
key_part2
IN
(10,20,30);
连接类型与index
相同 ALL,只是扫描了索引树。这有两种方式:
-
- 如果索引是查询的覆盖索引并且可以用于满足表中所需的所有数据,则仅扫描索引树。在这种情况下,该
Extra
列 显示Using index
。仅索引扫描通常比仅索引扫描更快,ALL
因为索引的大小通常小于表数据。 - 使用从索引中读取以按索引顺序查找数据行来执行全表扫描。
Uses index
没有出现在Extra
列中。
- 如果索引是查询的覆盖索引并且可以用于满足表中所需的所有数据,则仅扫描索引树。在这种情况下,该
当查询仅使用属于单个索引的列时,MySQL 可以使用此连接类型。
对先前表中的每个行组合进行全表扫描。如果该表是第一个未标记的表 ,这通常不好,并且在所有其他情况下const通常 非常糟糕。通常,您可以 ALL通过添加索引来避免基于先前表中的常量值或列值从表中检索行。
输出列Extra
包含 EXPLAIN有关 MySQL 如何解析查询的附加信息。下面的列表解释了可以出现在此列中的值。每个项目还为 JSON 格式的输出指示哪个属性显示该Extra
值。对于其中一些,有一个特定的属性。其他显示为message
属性的文本。
如果您想尽可能快地进行查询,请注意 and 的列Extra
值,或者,在 JSON 格式的输出中,对于 和 属性等于 。 Using filesortUsing temporaryEXPLAINusing_filesortusing_temporary_tabletrue
Child of '
table
' pushed join@1
(JSON:message
文本)
该表被引用为 table
可以下推到 NDB 内核的连接中的子项。仅在启用下推连接时适用于 NDB Cluster。ndb_join_pushdown有关更多信息和示例, 请参阅服务器系统变量的描述 。
const row not found
(JSON 属性const_row_not_found
:)
对于诸如 的查询,表是空的。 SELECT ... FROM
tbl_name
Deleting all rows
(JSON 属性message
:)
对于DELETE,一些存储引擎(例如MyISAM)支持一种处理程序方法,该方法以简单快速的方式删除所有表行。Extra
如果引擎使用此优化,则会显示 此值。
Distinct
(JSON 属性distinct
:)
MySQL 正在寻找不同的值,因此它在找到第一个匹配行后停止为当前行组合搜索更多行。
FirstMatch(
tbl_name
)
(JSON 属性first_match
:)
semijoin FirstMatch 连接快捷策略用于tbl_name
.
Full scan on NULL key
(JSON 属性message
:)
当优化器无法使用索引查找访问方法时,子查询优化会发生这种情况作为回退策略。
Impossible HAVING
(JSON 属性message
:)
该HAVING
子句始终为 false,不能选择任何行。
Impossible WHERE
(JSON 属性message
:)
该WHERE
子句始终为 false,不能选择任何行。
Impossible WHERE noticed after reading const tables
(JSON 属性message
:)
MySQL 已读取所有 const(and system) 表并注意到该WHERE
子句始终为 false。
LooseScan(
m
..
n
)
(JSON 属性message
:)
使用半连接 LooseScan 策略。 m
并且 n
是关键部件号。
No matching min/max row
(JSON 属性message
:)
没有行满足查询的条件,例如 . SELECT MIN(...) FROM ... WHERE
condition
no matching row in const table
(JSON 属性message
:)
对于带有连接的查询,有一个空表或没有满足唯一索引条件的行的表。
No matching rows after partition pruning
(JSON 属性message
:)
对于DELETEor UPDATE,优化器在分区修剪后没有发现要删除或更新的内容。Impossible WHERE
它与forSELECT语句 的含义相似。
No tables used
(JSON 属性message
:)
查询没有FROM
子句,或有 FROM DUAL
子句。
For INSERTor REPLACE语句, EXPLAIN当没有SELECT 部分时显示此值。例如,它出现 forEXPLAIN INSERT INTO t VALUES(10)
是因为 that 等价于 EXPLAIN INSERT INTO t SELECT 10 FROM DUAL
。
Not exists
(JSON 属性message
:)
MySQL 能够对LEFT JOIN
查询进行优化,并且在找到与条件匹配的行后,不会检查该表中的前一行组合的更多行LEFT JOIN
。以下是可以通过这种方式优化的查询类型的示例:
SELECT
*
FROM t1
LEFT
JOIN t2
ON t1
.id
=t2
.id
WHERE t2
.id
IS
NULL;
假设t2.id
定义为 NOT NULL
。在这种情况下,MySQL 使用 的值 扫描t1
并查找行 。如果 MySQL 在 中找到匹配的行 ,它就知道 永远不可能 ,并且不会扫描具有相同值的其余行。换句话说,对于 中的每一行,MySQL 只需要在 中进行一次查找,而不管在 中实际匹配了多少行。 t2t1.idt2t2.idNULLt2idt1t2t2
Plan isn't ready yet
(JSON 属性:无)
EXPLAIN FOR CONNECTION当优化器尚未完成为在命名连接中执行的语句创建执行计划时, 会出现此值。如果执行计划输出包含多行,则任何或所有行都可能具有此 Extra
值,具体取决于优化器在确定完整执行计划时的进度。
Range checked for each record (index map:
N
)
(JSON 属性message
:)
MySQL 没有找到可以使用的好的索引,但发现某些索引可能会在之前表中的列值已知后使用。对于前面表格中的每个行组合,MySQL 检查是否可以使用rangeor index_merge访问方法来检索行。这不是很快,但比执行完全没有索引的连接要快。适用性标准如 第 8.2.1.2 节“范围优化”和 第 8.2.1.3 节“索引合并优化”中所述,但上表的所有列值都是已知的并被视为常量。
索引从 1 开始编号,顺序与表中所示的相同SHOW INDEX。索引映射值 N
是指示哪些索引是候选索引的位掩码值。例如,值0x19
(二进制 11001)表示考虑索引 1、4 和 5。
Scanned
N
databases
(JSON 属性message
:)
这表示在处理表查询时服务器执行了多少目录扫描 INFORMATION_SCHEMA
,如第 8.2.3 节“优化 INFORMATION_SCHEMA 查询”中所述。的值N
可以是 0、1 或 all
。
Select tables optimized away
(JSON 属性message
:)
优化器确定 1) 最多应该返回一行,以及 2) 要生成这一行,必须读取一组确定性的行。当在优化阶段可以读取要读取的行时(例如,通过读取索引行),在查询执行期间不需要读取任何表。
当查询被隐式分组(包含聚合函数但没有 GROUP BY
子句)时,第一个条件得到满足。当每个使用的索引执行一次行查找时,满足第二个条件。读取的索引数决定了要读取的行数。
考虑以下隐式分组查询:
SELECT
MIN(c1
),
MIN(c2
)
FROM t1
;
假设MIN(c1)
可以通过读取一个索引行MIN(c2)
来检索它,并且可以通过从不同的索引读取一行来检索它。也就是说,对于每一列c1
和 c2
,都存在一个索引,其中该列是索引的第一列。在这种情况下,通过读取两个确定性行来返回一行。
Extra
如果要读取的行不确定,则不会出现 此值。考虑这个查询:
SELECT
MIN(c2
)
FROM t1
WHERE c1
<=
10;
假设这(c1, c2)
是一个覆盖索引。使用此索引,c1 <= 10
必须扫描所有行以找到最小值 c2
。相比之下,考虑这个查询:
SELECT
MIN(c2
)
FROM t1
WHERE c1
=
10;
在这种情况下,第一个索引行c1 = 10
包含最小值c2
。只需读取一行即可生成返回的行。
对于维护每个表的精确行数的存储引擎(例如MyISAM
,但不是 InnoDB
),对于缺少子句或始终为真且没有子句的查询,Extra
可能会出现此值 。(这是一个隐式分组查询的实例,其中存储引擎影响是否可以读取确定的行数。) COUNT(*)WHEREGROUP BY
Skip_open_table
,Open_frm_only
,Open_full_table
(JSON 属性:message
)
这些值表示适用于INFORMATION_SCHEMA
表查询的文件打开优化,如 第 8.2.3 节“优化 INFORMATION_SCHEMA 查询”中所述。
-
Skip_open_table
: 表格文件不需要打开。通过扫描数据库目录,查询中的信息已经可用。Open_frm_only.frm
:只需要打开 表的文件。Open_full_table
: 未优化的信息查找。、.frm
和 文件必须打开.MYD
。.MYI
Start temporary
,End temporary
(JSON 属性:message
)
这表明临时表用于 semijoin Duplicate Weedout 策略。
unique row not found
(JSON 属性message
:)
对于诸如 的查询,没有行满足 索引或表的条件。 SELECT ... FROM
tbl_name
UNIQUEPRIMARY KEY
Using filesort
(JSON 属性using_filesort
:)
MySQL 必须做一个额外的过程来找出如何按排序顺序检索行。排序是通过根据连接类型遍历所有行并存储排序键和指向与WHERE
子句匹配的所有行的行的指针来完成的。然后对键进行排序,并按排序顺序检索行。请参见 第 8.2.1.14 节,“按优化排序”。
Using index
(JSON 属性using_index
:)
仅使用索引树中的信息从表中检索列信息,而无需执行额外的查找来读取实际行。当查询仅使用属于单个索引的列时,可以使用此策略。
对于InnoDB
具有用户定义的聚集索引的表,即使列Using index
中不存在 该索引,也可以使用该索引Extra
。如果 type
is index和 key
is就是这种情况PRIMARY
。
Using index condition
(JSON 属性using_index_condition
:)
通过访问索引元组并首先对其进行测试以确定是否读取完整的表行来读取表。这样,除非有必要,否则索引信息用于延迟( “下推” )读取全表行。请参阅 第 8.2.1.5 节,“索引条件下推优化”。
Using index for group-by
(JSON 属性using_index_for_group_by
:)
与Using index
表访问方法类似,Using index for group-by
表明 MySQL 找到了一个索引,该索引可用于检索 aGROUP BY
或 DISTINCT
查询的所有列,而无需对实际表进行任何额外的磁盘访问。此外,索引以最有效的方式使用,因此对于每个组,只读取几个索引条目。有关详细信息,请参阅 第 8.2.1.15 节,“GROUP BY 优化”。
Using join buffer (Block Nested Loop)
,Using join buffer (Batched Key Access)
(JSON 属性:using_join_buffer
)
来自早期连接的表被部分读入连接缓冲区,然后从缓冲区中使用它们的行来执行与当前表的连接。 (Block Nested Loop)
指示使用块嵌套循环算法并(Batched Key Access)
指示使用批量密钥访问算法。也就是说, EXPLAIN输出前一行的表中的键被缓冲,匹配的行从出现的行所代表的表中批量提取 Using join buffer
。
在 JSON 格式的输出中, 的值 using_join_buffer
始终是Block Nested Loop
或 之一Batched Key Access
。
有关这些算法的更多信息,请参阅 Block Nested-Loop Join Algorithm和 Batched Key Access Joins。
Using MRR
(JSON 属性message
:)
使用多范围读取优化策略读取表。请参阅第 8.2.1.10 节,“多范围读取优化”。
Using sort_union(...)
,Using union(...)
,Using intersect(...)
(JSON 属性:message
)
index_merge这些指示显示如何为连接类型 合并索引扫描的特定算法 。请参阅第 8.2.1.3 节,“索引合并优化”。
Using temporary
(JSON 属性using_temporary_table
:)
为了解析查询,MySQL 需要创建一个临时表来保存结果。如果查询包含以不同方式列出列 的GROUP BY
和 子句,通常会发生这种情况。ORDER BY
Using where
(JSON 属性attached_condition
:)
子句用于限制与下一个WHERE
表匹配或发送到客户端的行。Extra
除非您特别打算从表中获取或检查所有行,否则如果值不是 Using where
并且表连接类型是 ALLor ,则您的查询可能有问题index。
Using where
在 JSON 格式的输出中没有直接对应项;该 attached_condition
属性包含WHERE
使用的任何条件。
Using where with pushed condition
(JSON 属性message
:)
此项仅适用于NDB 表格。这意味着 NDB Cluster 正在使用 Condition Pushdown 优化来提高非索引列和常量之间直接比较的效率。在这种情况下,条件被“下推”到集群的数据节点,并同时在所有数据节点上进行评估。这消除了通过网络发送不匹配行的需要,并且可以将此类查询速度提高 5 到 10 倍,比可以使用但不使用条件下推的情况。有关详细信息,请参阅 第 8.2.1.4 节,“发动机工况下推优化”。
Zero limit
(JSON 属性message
:)
该查询有一个LIMIT 0
子句,不能选择任何行。
通过获取输出rows
列中 值的乘积,您可以很好地了解连接的好坏。EXPLAIN这应该大致告诉您 MySQL 必须检查多少行才能执行查询。如果您使用 max_join_size系统变量限制查询,则此行积还用于确定SELECT 要执行哪些多表语句以及要中止哪些语句。请参见 第 5.1.1 节,“配置服务器”。
以下示例显示了如何根据提供的信息逐步优化多表连接 EXPLAIN。
假设您有 SELECT此处显示的语句,并且您计划使用以下命令对其进行检查 EXPLAIN:
EXPLAIN
SELECT tt
.TicketNumber
, tt
.TimeIn
,
tt
.ProjectReference
, tt
.EstimatedShipDate
,
tt
.ActualShipDate
, tt
.ClientID
,
tt
.ServiceCodes
, tt
.RepetitiveID
,
tt
.CurrentProcess
, tt
.CurrentDPPerson
,
tt
.RecordVolume
, tt
.DPPrinted
, et
.COUNTRY
,
et_1
.COUNTRY
,
do.CUSTNAME
FROM tt
, et
, et
AS et_1
,
do
WHERE tt
.SubmitTime
IS
NULL
AND tt
.ActualPC
= et
.EMPLOYID
AND tt
.AssignedPC
= et_1
.EMPLOYID
AND tt
.ClientID
=
do.CUSTNMBR
;
对于此示例,请做出以下假设:
- 被比较的列已声明如下。
桌子 | 柱子 | 数据类型 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
- 这些表具有以下索引。
桌子 | 指数 |
|
|
|
|
|
|
|
|
|
|
- 这些
tt.ActualPC
值不是均匀分布的。
最初,在执行任何优化之前,该 EXPLAIN语句会生成以下信息:
table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
do ALL PRIMARY NULL NULL NULL 2135
et_1 ALL PRIMARY NULL NULL NULL 74
tt ALL AssignedPC, NULL NULL NULL 3872
ClientID,
ActualPC
Range checked for each record (index map: 0x23)
因为type
对于 ALL每个表,这个输出表明 MySQL 正在生成所有表的笛卡尔积;也就是说,行的每个组合。这需要相当长的时间,因为必须检查每个表中行数的乘积。对于手头的情况,这个产品是 74 × 2135 × 74 × 3872 = 45,268,558,720 行。如果桌子更大,您只能想象需要多长时间。
这里的一个问题是,如果将列声明为相同的类型和大小,MySQL 可以更有效地使用列上的索引。在这种情况下,如果它们被声明为相同的大小,则认为它们是相同的 VARCHAR。 被声明为 and is ,因此存在长度不匹配。 CHARtt.ActualPCCHAR(10)et.EMPLOYIDCHAR(15)
要修复列长度之间的这种差异,请使用 ALTER TABLE将 10 个字符延长 ActualPC
到 15 个字符:
mysql>
ALTER
TABLE tt
MODIFY ActualPC
VARCHAR(15);
现在tt.ActualPC
和 et.EMPLOYID
都是 VARCHAR(15)
。再次执行该 EXPLAIN语句会产生以下结果:
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC, NULL NULL NULL 3872 Using
ClientID, where
ActualPC
do ALL PRIMARY NULL NULL NULL 2135
Range checked for each record (index map: 0x1)
et_1 ALL PRIMARY NULL NULL NULL 74
Range checked for each record (index map: 0x1)
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
这并不完美,但要好得多:这些 rows
值的乘积小了 74 倍。这个版本在几秒钟内执行。
可以进行第二次更改以消除tt.AssignedPC = et_1.EMPLOYID
和tt.ClientID = do.CUSTNMBR
比较的列长度不匹配:
mysql>
ALTER
TABLE tt
MODIFY AssignedPC
VARCHAR(15),
MODIFY ClientID
VARCHAR(15);
修改后, EXPLAIN产生如下所示的输出:
table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
tt ref AssignedPC, ActualPC 15 et.EMPLOYID 52 Using
ClientID, where
ActualPC
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
在这一点上,查询几乎被尽可能地优化了。剩下的问题是,默认情况下,MySQL 假定tt.ActualPC
列中的值是均匀分布的,而表并非如此tt
。幸运的是,告诉 MySQL 分析密钥分布很容易:
mysql>
ANALYZE
TABLE tt
;
使用附加的索引信息,连接是完美的并 EXPLAIN产生以下结果:
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC NULL NULL NULL 3872 Using
ClientID, where
ActualPC
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
输出中的rows
列 EXPLAIN是来自 MySQL 连接优化器的有根据的猜测。rows
通过将产品与查询返回的实际行数进行比较,检查这些数字是否更接近事实 。如果数字完全不同,您可能会通过 STRAIGHT_JOIN
在 SELECT语句中使用并尝试在 FROM
子句中以不同的顺序列出表来获得更好的性能。(但是, STRAIGHT_JOIN
可能会阻止使用索引,因为它禁用了半连接转换。请参阅 第 8.2.2.1 节,“使用半连接转换优化子查询、派生表和视图引用”.)
EXPLAIN SELECT在某些情况下,当与子查询一起使用 时,可以执行修改数据的语句;有关更多信息,请参阅第 13.2.10.8 节,“派生表”。
8.8.3 扩展 EXPLAIN 输出格式
对于SELECT语句, EXPLAIN语句会产生额外的(“扩展”)信息,这些信息不是 EXPLAIN输出的一部分,但可以通过在.SHOW WARNINGS 后面发出语句来查看EXPLAIN。输出中的 Message
值SHOW WARNINGS显示优化器如何限定 SELECT语句 中的表名和列名,SELECT应用重写和优化规则后的样子,以及可能有关优化过程的其他注释。
仅针对语句生成 可显示SHOW WARNINGS以下语句 的扩展信息 。 显示其他可解释语句(、 、 和 )的空结果。 EXPLAINSELECTSHOW WARNINGSDELETEINSERTREPLACEUPDATE
笔记
在较旧的 MySQL 版本中,扩展信息是使用EXPLAIN EXTENDED. 该语法仍被认为具有向后兼容性,但现在默认启用扩展输出,因此该EXTENDED
关键字是多余的且已弃用。使用它会导致警告;EXPLAIN期望它在未来的 MySQL 版本中 从语法中删除 。
这是扩展 EXPLAIN输出的示例:
mysql>
EXPLAIN
SELECT t1
.a
, t1
.a
IN
(SELECT t2
.a
FROM t2
)
FROM t1\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t1
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 4
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 2
select_type: SUBQUERY
table: t2
type: index
possible_keys: a
key: a
key_len: 5
ref: NULL
rows: 3
filtered: 100.00
Extra: Using index
2 rows in set, 1 warning (0.00 sec)
mysql>
SHOW
WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a` AS `a`,
<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in
( <materialize> (/* select#2 */ select `test`.`t2`.`a`
from `test`.`t2` where 1 having 1 ),
<primary_index_lookup>(`test`.`t1`.`a` in
<temporary table> on <auto_key>
where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) AS `t1.a
IN (SELECT t2.a FROM t2)` from `test`.`t1`
1 row in set (0.00 sec)
因为显示的语句SHOW WARNINGS可能包含特殊标记以提供有关查询重写或优化器操作的信息,所以该语句不一定是有效的 SQL,并且不打算执行。输出还可能包含带有 Message
值的行,这些值提供有关优化器所采取的操作的附加非 SQL 解释性说明。
以下列表描述了可以出现在由 显示的扩展输出中的特殊标记SHOW WARNINGS:
<auto_key>
为临时表自动生成的键。
<cache>(
expr
)
表达式(例如标量子查询)执行一次,结果值保存在内存中供以后使用。对于包含多个值的结果,可能会创建一个临时表,而您可能会看到它<temporary table>
。
<exists>(
query fragment
)
将子查询谓词转换为 EXISTS
谓词,并对子查询进行转换,以便它可以与 EXISTS
谓词一起使用。
<in_optimizer>(
query fragment
)
这是一个没有用户意义的内部优化器对象。
<index_lookup>(
query fragment
)
使用索引查找来处理查询片段以查找符合条件的行。
<if>(
condition
,
expr1
,
expr2
)
如果条件为真,则计算为 expr1
,否则 为expr2
。
<is_not_null_test>(
expr
)
验证表达式不计算为 的测试 NULL
。
<materialize>(
query fragment
)
使用子查询实现。
`materialized-subquery`.
col_name
col_name
对内部临时表中 列的引用具体 化以保存评估子查询的结果。
<primary_index_lookup>(
query fragment
)
使用主键查找来处理查询片段以查找符合条件的行。
<ref_null_helper>(
expr
)
这是一个没有用户意义的内部优化器对象。
/* select#
N
*/
select_stmt
SELECT
与非扩展EXPLAIN输出中id
值为 的行相关联N
。
outer_tables
semi join (
inner_tables
)
半连接操作。 inner_tables
显示未拉出的表。请参阅第 8.2.2.1 节,“使用半连接转换优化子查询、派生表和视图引用”。
<temporary table>
这表示为缓存中间结果而创建的内部临时表。
当某些表属于const orsystem类型时,涉及这些表中的列的表达式由优化器提前求值,而不是显示语句的一部分。但是,使用 时FORMAT=JSON
,某些 const表访问会显示为ref使用 const 值的访问。
8.8.4 获取命名连接的执行计划信息
要获取在命名连接中执行的可解释语句的执行计划,请使用以下语句:
EXPLAIN
[options]
FOR
CONNECTION
connection_id;
EXPLAIN FOR CONNECTION返回EXPLAIN当前用于在给定连接中执行查询的信息。由于对数据(和支持统计信息)的更改,它可能会产生与 EXPLAIN在等效查询文本上运行不同的结果。这种行为差异可用于诊断更多瞬态性能问题。例如,如果您在一个会话中运行需要很长时间才能完成的语句,则EXPLAIN FOR CONNECTION在另一个会话中使用可能会产生有关延迟原因的有用信息。
connection_id
是从 INFORMATION_SCHEMA
PROCESSLIST表或 SHOW PROCESSLIST语句中获得的连接标识符。如果您有PROCESS权限,您可以指定任何连接的标识符。否则,您只能为自己的连接指定标识符。
如果命名连接没有执行语句,则结果为空。否则,EXPLAIN FOR CONNECTION
仅当在命名连接中执行的语句是可解释的时才适用。这包括 SELECT、 DELETE、 INSERT、 REPLACE和 UPDATE。(但是, EXPLAIN FOR CONNECTION
不适用于准备好的语句,即使是那些类型的准备好的语句。)
如果命名连接正在执行一个可解释的语句,那么输出就是您 EXPLAIN
在语句本身上使用所获得的结果。
如果命名连接正在执行无法解释的语句,则会发生错误。例如,您不能为当前会话命名连接标识符,因为 EXPLAIN
无法解释:
mysql>
SELECT
CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
| 373 |
+-----------------+
1 row in set (0.00 sec)
mysql>
EXPLAIN
FOR
CONNECTION
373;
ERROR 1889 (HY000): EXPLAIN FOR CONNECTION command is supported
only for SELECT/UPDATE/INSERT/DELETE/REPLACE
Com_explain_other
status 变量表示执行的 语句EXPLAIN FOR CONNECTION数。
8.8.5 估计查询性能
在大多数情况下,您可以通过计算磁盘寻道次数来估计查询性能。对于小型表,通常可以在一次磁盘查找中找到一行(因为索引可能已缓存)。对于更大的表,您可以估计,使用 B-tree 索引,您需要这么多次查找才能找到一行: . log(
row_count
) / log(
index_block_length
/ 3 * 2 / (
index_length
+
data_pointer_length
)) + 1
在 MySQL 中,索引块通常为 1,024 字节,数据指针通常为 4 字节。对于一个 500,000 行的表,键值长度为 3 个字节(大小为 MEDIUMINT),公式表示 log(500,000)/log(1024/3*2/(3+4)) + 1
= 4
seeks。
该索引需要大约 500,000 * 7 * 3/2 = 5.2MB 的存储空间(假设典型的索引缓冲区填充率为 2/3),因此您可能在内存中有很多索引,因此只需要一两次调用读取数据以查找行。
但是,对于写入,您需要四个查找请求来查找放置新索引值的位置,通常需要两次查找来更新索引并写入行。
前面的讨论并不意味着您的应用程序性能会因 log 而缓慢下降 N
。只要一切都被操作系统或 MySQL 服务器缓存,随着表变大,事情只会稍微变慢。在数据变得太大而无法缓存后,事情开始变得慢得多,直到您的应用程序仅受磁盘搜索(增加 log N
)的约束。为避免这种情况,请随着数据的增长增加密钥缓存大小。对于MyISAM
表,键缓存大小由 key_buffer_size系统变量控制。请参见第 5.1.1 节,“配置服务器”。
根据您的表、列、索引和WHERE子句中的条件的详细信息,MySQL 优化器会考虑许多技术来有效地执行 SQL 查询中涉及的查找。可以在不读取所有行的情况下对一个巨大的表执行查询;可以在不比较每个行组合的情况下执行涉及多个表的连接。优化器选择执行最有效查询的一组操作称为“查询执行计划”,也称为 EXPLAIN计划。你的目标是认识到 EXPLAIN计划表明查询已优化好,并学习 SQL 语法和索引技术以改进计划,如果您发现一些低效的操作。
8.9 控制查询优化器
8.9.1 控制查询计划评估
查询优化器的任务是找到执行 SQL 查询的最佳计划。因为“好”和“坏”之间的性能差异计划可以是数量级的(即几秒与几小时甚至几天),大多数查询优化器,包括 MySQL 的优化器,在所有可能的查询评估计划中执行或多或少详尽的搜索以找到最佳计划。对于连接查询,MySQL 优化器调查的可能计划的数量随着查询中引用的表的数量呈指数增长。对于少量表(通常少于 7 到 10 个),这不是问题。但是,当提交较大的查询时,查询优化所花费的时间很容易成为服务器性能的主要瓶颈。
一种更灵活的查询优化方法使用户能够控制优化器在搜索最佳查询评估计划时的详尽程度。一般的想法是优化器调查的计划越少,编译查询所花费的时间就越少。另一方面,由于优化器跳过了一些计划,它可能会错过找到最佳计划。
优化器对其评估的计划数量的行为可以使用两个系统变量来控制:
- 该optimizer_prune_level 变量告诉优化器根据对每个表访问的行数的估计跳过某些计划。我们的经验表明,这种“有根据的猜测”很少会错过最佳计划,并且可能会大大减少查询编译时间。这就是为什么此选项
optimizer_prune_level=1
默认启用 ( ) 的原因。但是,如果您认为优化器错过了更好的查询计划,则可以关闭此选项(optimizer_prune_level=0
) 存在查询编译可能需要更长时间的风险。请注意,即使使用了这种启发式方法,优化器仍会探索大致呈指数级的计划数。 - 该变量告诉优化器应该评估每个不完整计划的“未来”optimizer_search_depth 多远,以评估它是否应该进一步扩展。较小的值 可能会导致查询编译时间减少几个数量级。例如,具有 12、13 或更多表的查询可能很容易需要数小时甚至数天的时间来编译,如果 接近查询中的表数。同时,如果编译用 optimizer_search_depthoptimizer_search_depthoptimizer_search_depth 等于 3 或 4,优化器可以在不到一分钟的时间内编译相同的查询。如果您不确定 的合理值是多少 optimizer_search_depth,可以将此变量设置为 0 以告诉优化器自动确定该值。
8.9.2 可切换优化
系统optimizer_switch变量可以控制优化器的行为。它的值是一组标志,每个标志都有一个值on
或off
表示相应的优化器行为是启用还是禁用。此变量具有全局值和会话值,可以在运行时更改。全局默认值可以在服务器启动时设置。
要查看当前的优化器标志集,请选择变量值:
mysql>
SELECT
@@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,mrr_cost_based=on,
block_nested_loop=on,batched_key_access=off,
materialization=on,semijoin=on,loosescan=on,
firstmatch=on,duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,derived_merge=on,
prefer_ordering_index=on
要更改 的值 optimizer_switch,请分配一个由一个或多个命令的逗号分隔列表组成的值:
SET
[GLOBAL|SESSION] optimizer_switch
='command[,command]...';
每个command
值应具有下表中显示的一种形式。
命令语法 | 意义 |
| 将每个优化重置为其默认值 |
| 将命名优化设置为其默认值 |
| 禁用命名优化 |
| 启用命名优化 |
值中命令的顺序无关紧要,尽管default
如果存在该命令,则首先执行该命令。设置opt_name
标志以 default
将其设置 为默认值on
或off
默认值。opt_name
不允许在值中多次指定任何给定值,这会导致错误。value 中的任何错误都会导致分配失败并出现错误,而 value optimizer_switch保持不变。
以下列表描述了允许的 opt_name
标志名称,按优化策略分组:
- 批量密钥访问标志
- batched_key_access (默认
off
)
- batched_key_access (默认
控制 BKA 连接算法的使用。
- 要batched_key_access在设置为 时产生任何效果
on
,mrr
标志也必须是on
。目前,MRR的成本估算过于悲观。因此,也有必要mrr_cost_based
使用off
BKA。 - 有关更多信息,请参阅 第 8.2.1.11 节,“阻止嵌套循环和批量密钥访问连接”。
- 阻止嵌套循环标志
- block_nested_loop (默认
on
)
- block_nested_loop (默认
控制 BNL 连接算法的使用。
- 有关更多信息,请参阅 第 8.2.1.11 节,“阻止嵌套循环和批量密钥访问连接”。
- 条件过滤标志
- condition_fanout_filter (默认
on
)
- condition_fanout_filter (默认
控制条件过滤的使用。
- 有关详细信息,请参阅 第 8.2.1.12 节,“条件过滤”。
- 派生表合并标志
- derived_merge(默认
on
)
- derived_merge(默认
控制将派生表和视图合并到外部查询块中。
- 该derived_merge标志控制优化器是否尝试将派生表和视图引用合并到外部查询块中,假设没有其他规则阻止合并;例如,
ALGORITHM
视图指令优先于 derived_merge设置。默认情况下,该标志是on
启用合并。 - 有关更多信息,请参阅 第 8.2.2.4 节,“使用合并或实现优化派生表和查看引用”。
- 发动机状态下推标志
- engine_condition_pushdown (默认
on
)
- engine_condition_pushdown (默认
控制发动机状态下推。
- 有关详细信息,请参阅 第 8.2.1.4 节,“发动机工况下推优化”。
- 索引条件下推标志
- index_condition_pushdown (默认
on
)
- index_condition_pushdown (默认
控制索引条件下推。
- 有关更多信息,请参阅 第 8.2.1.5 节,“索引条件下推优化”。
- 索引扩展标志
- use_index_extensions (默认
on
)
- use_index_extensions (默认
控制索引扩展的使用。
- 有关更多信息,请参阅 第 8.3.9 节,“索引扩展的使用”。
- 索引合并标志
- index_merge(默认
on
)
- index_merge(默认
控制所有索引合并优化。
-
- index_merge_intersection (默认
on
)
- index_merge_intersection (默认
控制索引合并交叉点访问优化。
-
- index_merge_sort_union (默认
on
)
- index_merge_sort_union (默认
控制索引合并排序联合访问优化。
-
- index_merge_union (默认
on
)
- index_merge_union (默认
控制索引合并联合访问优化。
- 有关更多信息,请参阅 第 8.2.1.3 节,“索引合并优化”。
- 限制优化标志
- prefer_ordering_index (默认
on
)
- prefer_ordering_index (默认
控制在查询具有 ORDER BY
or子句GROUP BY
的情况下LIMIT
,优化器是否尝试使用有序索引而不是无序索引、文件排序或其他一些优化。每当优化器确定使用它可以更快地执行查询时,默认情况下都会执行此优化。
因为做出这种决定的算法不能处理所有可能的情况(部分原因是假设数据的分布总是或多或少是均匀的),所以在某些情况下,这种优化可能是不可取的。在 MySQL 5.7.33 之前,无法禁用此优化,但在 MySQL 5.7.33 及更高版本中,虽然它仍然是默认行为,但可以通过将 prefer_ordering_index 标志设置为 来禁用它off
。
- 有关更多信息和示例,请参阅 第 8.2.1.17 节,“限制查询优化”。
- 多范围读取标志
- mrr(默认
on
)
- mrr(默认
控制多范围读取策略。
-
- mrr_cost_based (默认
on
)
- mrr_cost_based (默认
如果 .则控制基于成本的 MRR 的使用 mrr=on。
- 有关详细信息,请参阅 第 8.2.1.10 节,“多范围读取优化”。
- 半连接标志
- duplicateweedout (默认
on
)
- duplicateweedout (默认
控制 semijoin Duplicate Weedout 策略。
-
- firstmatch(默认
on
)
- firstmatch(默认
控制半联接 FirstMatch 策略。
-
- loosescan(默认
on
)
- loosescan(默认
控制半连接 LooseScan 策略(不要与 Loose Index Scan for 混淆GROUP BY
)。
-
- semijoin(默认
on
)
- semijoin(默认
控制所有半连接策略。
- 、semijoin、 firstmatch和 标志启用对半连接策略的控制loosescan。 duplicateweedout该 semijoin标志控制是否使用半连接。如果设置为
on
,则 firstmatch和 loosescan标志可以更好地控制允许的半连接策略。 - 如果duplicateweedout 半连接策略被禁用,除非所有其他适用的策略也被禁用,否则不会使用它。
- Ifsemijoin和
materialization
are bothon
,半连接在适用的情况下也使用物化。这些标志是on
默认的。 - 有关详细信息,请参阅第 8.2.2.1 节,“使用半连接转换优化子查询、派生表和视图引用”。
- 子查询实现标志
- materialization (默认
on
)
- materialization (默认
控制物化(包括半连接物化)。
使用基于成本的实现选择。
- 该materialization标志控制是否使用子查询实现。If semijoin和 materializationare both
on
,半连接在适用的情况下也使用物化。这些标志是on
默认的。 - 该 subquery_materialization_cost_based 标志可以控制子查询实现和子查询转换之间的
IN
选择EXISTS
。如果标志是(默认),优化器在子查询实现和子查询转换on
之间执行基于成本的选择( 如果可以使用任何一种方法)。如果标志是,则优化器选择子查询实现而不是 子查询转换。INEXISTSoffINEXISTS
- 有关更多信息,请参阅 第 8.2.2 节,“优化子查询、派生表和视图引用”。
当您为 分配值时 optimizer_switch,未提及的标志将保留其当前值。这使得可以在单个语句中启用或禁用特定的优化器行为而不影响其他行为。该语句不依赖于存在哪些其他优化器标志以及它们的值是什么。假设启用了所有索引合并优化:
mysql>
SELECT
@@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,mrr_cost_based=on,
block_nested_loop=on,batched_key_access=off,
materialization=on,semijoin=on,loosescan=on,
firstmatch=on,duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,derived_merge=on,
prefer_ordering_index=on
如果服务器对某些查询使用 Index Merge Union 或 Index Merge Sort-Union 访问方法,并且您想检查优化器在没有它们的情况下是否执行得更好,请像这样设置变量值:
mysql>
SET optimizer_switch
='index_merge_union=off,index_merge_sort_union=off';
mysql>
SELECT
@@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=off,
index_merge_sort_union=off,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,mrr_cost_based=on,
block_nested_loop=on,batched_key_access=off,
materialization=on,semijoin=on,loosescan=on,
firstmatch=on,duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,derived_merge=on,
prefer_ordering_index=on
8.9.3 优化器提示
控制优化器策略的一种方法是设置 optimizer_switch系统变量(参见第 8.9.2 节,“可切换优化”)。对此变量的更改会影响所有后续查询的执行;要对一个查询产生不同的影响,有必要 optimizer_switch在每个查询之前进行更改。
控制优化器的另一种方法是使用优化器提示,它可以在单个语句中指定。因为优化器提示适用于每个语句,所以它们对语句执行计划的控制比使用 optimizer_switch. 例如,您可以在语句中启用对一个表的优化,并禁用对不同表的优化。语句中的提示优先于 optimizer_switch标志。
例子:
SELECT
/*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
FROM t3
WHERE f1
>
30
AND f1
<
33;
SELECT
/*+ BKA(t1) NO_BKA(t2) */
*
FROM t1
INNER
JOIN t2
WHERE
...;
SELECT
/*+ NO_ICP(t1, t2) */
*
FROM t1
INNER
JOIN t2
WHERE
...;
SELECT
/*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */
*
FROM t1
...;
EXPLAIN
SELECT
/*+ NO_ICP(t1) */
*
FROM t1
WHERE
...;
笔记
默认情况下,mysql客户端会从发送到服务器的 SQL 语句(包括优化器提示)中删除注释,直到 MySQL 5.7.7 更改为将优化器提示传递给服务器。如果您将旧版本的mysql客户端与理解优化器提示的服务器版本 一起使用,为了确保优化器提示不会被剥离,请使用 该 选项 调用mysql 。--comments
此处描述的优化器提示与第 8.9.4 节“索引提示” 中描述的索引提示不同。优化器和索引提示可以单独或一起使用。
优化器提示适用于不同的范围级别:
- 全局:提示影响整个语句
- 查询块:提示影响语句中的特定查询块
- 表级:提示影响查询块中的特定表
- 索引级别:提示影响表中的特定索引
下表总结了可用的优化器提示、它们影响的优化器策略以及它们适用的范围。稍后给出更多细节。
提示名称 | 描述 | 适用范围 |
影响批量密钥访问加入处理 | 查询块、表 | |
影响块嵌套循环连接处理 | 查询块、表 | |
限制语句执行时间 | 全球的 | |
影响多范围读取优化 | 表、索引 | |
影响索引条件下推优化 | 表、索引 | |
影响范围优化 | 表、索引 | |
为查询块分配名称 | 查询块 | |
半连接策略 | 查询块 | |
影响物化, | 查询块 |
禁用优化会阻止优化器使用它。启用优化意味着如果策略适用于语句执行,优化器可以自由使用该策略,而不是优化器必须使用它。
MySQL 支持 SQL 语句中的注释,如 第 9.6 节,“注释”中所述。必须在/*+ ... */
注释中指定优化器提示。也就是说,优化器提示使用/* ... */
C 样式注释语法的变体,在注释打开序列+
之后有一个字符。/*
例子:
/*+ BKA(t1) */
/*+ BNL(t1, t2) */
/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */
/*+ QB_NAME(qb2) */
+
字符 后允许有空格。
SELECT解析器在、 UPDATE、 INSERT、 REPLACE和 DELETE语句 的初始关键字之后识别优化器提示注释。在这些情况下允许使用提示:
- 在查询和数据更改语句的开头:
- SELECT
- INSERT
- REPLACE
- UPDATE
DELETE
/*+ ... */
...
- 在查询块的开头:
- (SELECT
- (SELECT
- (SELECT
- UPDATE
x
IN
INSERT
...
SELECT
/*+ ... */
...
- 在以 . 开头的可提示语句中 EXPLAIN。例如:
- EXPLAIN
EXPLAIN
UPDATE
...
WHERE x
IN
(SELECT
/*+ ... */
...)
这意味着您可以使用 EXPLAIN来查看优化器提示如何影响执行计划。SHOW WARNINGS之后立即使用 EXPLAIN以查看如何使用提示。以下显示的扩展EXPLAIN
输出SHOW WARNINGS指示使用了哪些提示。不显示忽略的提示。
一个提示注释可以包含多个提示,但一个查询块不能包含多个提示注释。这是有效的:
SELECT
/*+ BNL(t1) BKA(t2) */
...
但这是无效的:
SELECT
/*+ BNL(t1) */
/* BKA(t2) */
...
当提示注释包含多个提示时,存在重复和冲突的可能性。以下一般准则适用。对于特定的提示类型,可能会应用附加规则,如提示描述中所示。
- 重复提示:对于诸如 的提示
/*+ MRR(idx1) MRR(idx1) */
,MySQL 使用第一个提示并发出有关重复提示的警告。 - 冲突提示:对于诸如 的提示
/*+ MRR(idx1) NO_MRR(idx1) */
,MySQL 使用第一个提示并发出有关第二个冲突提示的警告。
查询块名称是标识符,并遵循关于哪些名称有效以及如何引用它们的通常规则(请参阅 第 9.2 节,“模式对象名称”)。
提示名称、查询块名称和策略名称不区分大小写。对表和索引名称的引用遵循通常的标识符区分大小写规则(请参阅 第 9.2.3 节,“标识符区分大小写”)。
表级提示会影响块嵌套循环 (BNL) 和批量密钥访问 (BKA) 连接处理算法的使用(请参阅 第 8.2.1.11 节,“块嵌套循环和批量密钥访问连接”)。这些提示类型适用于特定表或查询块中的所有表。
表级提示的语法:
hint_name([@query_block_name]
[tbl_name
[,
tbl_name]
...])
hint_name([tbl_name@query_block_name
[,
tbl_name@query_block_name]
...])
语法指的是这些术语:
笔记
要使用 BNL 或 BKA 提示为外连接的任何内表启用连接缓冲,必须为外连接的所有内表启用连接缓冲。
tbl_name
:语句中使用的表的名称。该提示适用于它命名的所有表。如果提示没有命名表,则它适用于出现它的查询块的所有表。
如果表有别名,则提示必须引用别名,而不是表名。
提示中的表名不能用模式名限定。
query_block_name
:提示适用的查询块。如果提示不包含前导 ,则提示适用于它出现的查询块。对于 语法,提示适用于命名查询块中的命名表。要为查询块分配名称,请参阅 命名查询块的优化器提示。@
query_block_nametbl_name
@
query_block_name
例子:
SELECT
/*+ NO_BKA(t1, t2) */ t1
.*
FROM t1
INNER
JOIN t2
INNER
JOIN t3
;
SELECT
/*+ NO_BNL() BKA(t1) */ t1
.*
FROM t1
INNER
JOIN t2
INNER
JOIN t3
;
表级提示适用于从以前的表接收记录的表,而不是发送方表。考虑这个陈述:
SELECT
/*+ BNL(t2) */
FROM t1
, t2
;
如果优化器选择先处理,它会通过在开始读取之前 缓冲行来 t1
应用块嵌套循环连接 。如果优化器选择先处理,则提示无效,因为它是发送者表。 t2t1t2t2t2
索引级提示会影响优化器对特定表或索引使用的索引处理策略。这些提示类型会影响索引条件下推 (ICP)、多范围读取 (MRR) 和范围优化的使用(请参阅 第 8.2.1 节,“优化 SELECT 语句”)。
索引级提示的语法:
hint_name([@query_block_name]
tbl_name
[index_name
[,
index_name]
...])
hint_name(tbl_name@query_block_name
[index_name
[,
index_name]
...])
语法指的是这些术语:
hint_name
: 这些提示名称是允许的:- MRR, NO_MRR: 为指定的表或索引启用或禁用 MRR。MRR 提示仅适用于
InnoDB
和MyISAM
表。 - NO_ICP: 对指定的表或索引禁用 ICP。默认情况下,ICP 是一种候选优化策略,因此没有启用它的提示。
- NO_RANGE_OPTIMIZATION:禁用指定表或索引的索引范围访问。此提示还禁用表或索引的索引合并和松散索引扫描。默认情况下,范围访问是一种候选优化策略,因此没有启用它的提示。
- MRR, NO_MRR: 为指定的表或索引启用或禁用 MRR。MRR 提示仅适用于
当范围数量可能很高并且范围优化需要很多资源时,此提示可能很有用。
tbl_name
:提示适用的表。index_name
:命名表中索引的名称。该提示适用于它命名的所有索引。如果提示没有指定索引,则它适用于表中的所有索引。
要引用主键,请使用 name PRIMARY
。要查看表的索引名称,请使用SHOW INDEX.
query_block_name
:提示适用的查询块。如果提示不包含前导 ,则提示适用于它出现的查询块。对于 语法,提示适用于命名查询块中的命名表。要为查询块分配名称,请参阅 命名查询块的优化器提示。@
query_block_nametbl_name
@
query_block_name
例子:
SELECT
/*+ MRR(t1) */
*
FROM t1
WHERE f2
<=
3
AND
3
<= f3
;
SELECT
/*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
FROM t3
WHERE f1
>
30
AND f1
<
33;
INSERT
INTO t3
(f1
, f2
, f3
)
(SELECT
/*+ NO_ICP(t2) */ t2
.f1
, t2
.f2
, t2
.f3
FROM t1
,t2
WHERE t1
.f1
=t2
.f1
AND t2
.f2
BETWEEN t1
.f1
AND t1
.f2
AND t2
.f2
+
1
>= t1
.f1
+
1);
子查询提示影响是否使用半连接转换以及允许使用哪些半连接策略,以及在不使用半连接时,是否使用子查询物化或 IN
到EXISTS
转换。有关这些优化的更多信息,请参阅第 8.2.2 节,“优化子查询、派生表和视图引用”。
影响半连接策略的提示语法:
hint_name([@query_block_name]
[strategy
[,
strategy]
...])
语法指的是这些术语:
hint_name
: 这些提示名称是允许的:- SEMIJOIN, NO_SEMIJOIN: 启用或禁用命名的半连接策略。
strategy
:要启用或禁用的半连接策略。这些策略名称是允许的:DUPSWEEDOUT
,FIRSTMATCH
,LOOSESCAN
,MATERIALIZATION
.
对于SEMIJOIN提示,如果没有命名策略,则尽可能根据 optimizer_switch系统变量启用的策略使用半连接。如果策略已命名但不适用于该语句,DUPSWEEDOUT
则使用。
对于NO_SEMIJOIN提示,如果没有命名策略,则不使用半连接。如果命名的策略排除了该语句的所有适用策略, DUPSWEEDOUT
则使用。
如果一个子查询嵌套在另一个子查询中,并且两者都合并到外部查询的半连接中,则最内层查询的任何半连接策略规范都将被忽略。 SEMIJOIN并且 NO_SEMIJOIN提示仍可用于启用或禁用此类嵌套子查询的半连接转换。
如果DUPSWEEDOUT
禁用,有时优化器可能会生成远非最佳的查询计划。这是由于贪婪搜索期间的启发式修剪而发生的,这可以通过设置来避免 optimizer_prune_level=0。
例子:
SELECT
/*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */
*
FROM t2
WHERE t2
.a
IN
(SELECT
/*+ QB_NAME(subq1) */ a
FROM t3
);
SELECT
/*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */
*
FROM t2
WHERE t2
.a
IN
(SELECT
/*+ QB_NAME(subq1) */ a
FROM t3
);
IN
影响是否使用子查询实现或转换 的提示语法 EXISTS
:
SUBQUERY
([@query_block_name]
strategy)
提示名称始终为 SUBQUERY.
对于SUBQUERY提示, strategy
允许使用以下值: INTOEXISTS
, MATERIALIZATION
.
例子:
SELECT id
, a
IN
(SELECT
/*+ SUBQUERY(MATERIALIZATION) */ a
FROM t1
)
FROM t2
;
SELECT
*
FROM t2
WHERE t2
.a
IN
(SELECT
/*+ SUBQUERY(INTOEXISTS) */ a
FROM t1
);
对于半连接和SUBQUERY 提示,前导 指定提示适用的查询块。如果提示不包含前导 ,则提示适用于它出现的查询块。要为查询块分配名称,请参阅 命名查询块的优化器提示。 @
query_block_name
@
query_block_name
如果提示注释包含多个子查询提示,则使用第一个。如果有其他该类型的以下提示,它们会产生警告。以下其他类型的提示会被默默忽略。
MAX_EXECUTION_TIME提示仅允许用于SELECT 语句 。它对N
在服务器终止语句之前允许执行多长时间设置一个限制(以毫秒为单位的超时值):
MAX_EXECUTION_TIME
(N)
超时时间为 1 秒(1000 毫秒)的示例:
SELECT
/*+ MAX_EXECUTION_TIME(1000) */
*
FROM t1
INNER
JOIN t2
WHERE
...
该 提示将语句执行超时设置为 毫秒。如果此选项不存在或为 0,则应用系统变量 建立的语句超时 。MAX_EXECUTION_TIME(N)NN
max_execution_time
MAX_EXECUTION_TIME提示适用如下 :
- 对于具有多个
SELECT
关键字的语句,例如联合或带有子查询的语句, MAX_EXECUTION_TIME 适用于整个语句,并且必须出现在第一个之后SELECT。 - 它适用于只读 SELECT语句。非只读语句是那些调用存储函数的语句,该函数将修改数据作为副作用。
- 它不适用于SELECT 存储程序中的语句并被忽略。
表级、索引级和子查询优化器提示允许将特定查询块命名为其参数语法的一部分。要创建这些名称,请使用 QB_NAME提示,它会为它出现的查询块分配一个名称:
QB_NAME
(name)
QB_NAME提示可用于以明确的方式明确其他提示适用的查询块。它们还允许在单个提示注释中指定所有非查询块名称提示,以便更容易理解复杂的语句。考虑以下语句:
SELECT
...
FROM
(SELECT
...
FROM
(SELECT
...
FROM
...))
...
QB_NAME提示为语句中的查询块分配名称:
SELECT
/*+ QB_NAME(qb1) */
...
FROM
(SELECT
/*+ QB_NAME(qb2) */
...
FROM
(SELECT
/*+ QB_NAME(qb3) */
...
FROM
...))
...
然后其他提示可以使用这些名称来引用适当的查询块:
SELECT
/*+ QB_NAME(qb1) MRR(@qb1 t1) BKA(@qb2) NO_MRR(@qb3t1 idx1, id2) */
...
FROM
(SELECT
/*+ QB_NAME(qb2) */
...
FROM
(SELECT
/*+ QB_NAME(qb3) */
...
FROM
...))
...
产生的效果如下:
- MRR(@qb1 t1)适用
t1
于查询块 中的表qb1
。 - BKA(@qb2)适用于查询块
qb2
。 - NO_MRR(@qb3 t1 idx1, id2)适用 于查询块中的索引
idx1
和idx2
表中。t1qb3
查询块名称是标识符,并遵循关于哪些名称有效以及如何引用它们的通常规则(请参阅 第 9.2 节,“模式对象名称”)。例如,必须引用包含空格的查询块名称,这可以使用反引号来完成:
SELECT
/*+ BKA(@`my hint name`) */
...
FROM
(SELECT
/*+ QB_NAME(`my hint name`) */
...)
...
如果ANSI_QUOTES启用了 SQL 模式,也可以在双引号内引用查询块名称:
SELECT
/*+ BKA(@"my hint name") */
...
FROM
(SELECT
/*+ QB_NAME("my hint name") */
...)
...
8.9.4 索引提示
索引提示为优化器提供有关如何在查询处理期间选择索引的信息。此处描述的索引提示与第 8.9.3 节“优化器提示”中描述的优化器提示不同 。索引和优化器提示可以单独使用,也可以一起使用。
索引提示在表名之后指定。(有关在语句中指定表的一般语法 SELECT,请参阅 第 13.2.9.2 节,“JOIN 子句”。)引用单个表的语法,包括索引提示,如下所示:
tbl_name
[[AS]
alias]
[index_hint_list]
index_hint_list:
index_hint
[index_hint]
...
index_hint:
USE {
INDEX|KEY}
[FOR {
JOIN|ORDER
BY|GROUP
BY}
]
([index_list])
| {
IGNORE|FORCE} {
INDEX|KEY}
[FOR {
JOIN|ORDER
BY|GROUP
BY}
]
(index_list)
index_list:
index_name
[,
index_name]
...
该提示告诉 MySQL 仅使用一个命名索引来查找表中的行。替代语法告诉 MySQL 不要使用某些特定的索引或索引。如果显示 MySQL 使用了可能索引列表中的错误索引, 这些提示很有用。USE INDEX (
index_list
)IGNORE INDEX (
index_list
)
EXPLAIN
提示的FORCE INDEX
行为类似于,此外假定表扫描 非常昂贵。换句话说,只有在无法使用命名索引之一来查找表中的行时才使用表扫描。 USE INDEX (
index_list
)
每个提示都需要索引名称,而不是列名称。要引用主键,请使用 name PRIMARY
。要查看表的索引名称,请使用SHOW INDEX语句或 INFORMATION_SCHEMA.STATISTICS 表。
index_name
值不必是完整的索引名称 。它可以是索引名称的明确前缀。如果前缀不明确,则会发生错误。
例子:
SELECT
*
FROM table1
USE
INDEX
(col1_index
,col2_index
)
WHERE col1
=1
AND col2
=2
AND col3
=3;
SELECT
*
FROM table1
IGNORE
INDEX
(col3_index
)
WHERE col1
=1
AND col2
=2
AND col3
=3;
索引提示的语法具有以下特征:
- 省略
index_list
for在语法上是有效的USE INDEX
,这意味着“不使用索引。” 省略orindex_list
是 语法错误。FORCE INDEXIGNORE INDEX
FOR
您可以通过向提示添加子句来 指定索引提示的范围 。这为查询处理的各个阶段提供了对优化器选择执行计划的更细粒度的控制。要仅影响 MySQL 决定如何在表中查找行以及如何处理连接时使用的索引,请使用FOR JOIN
. 要影响对行进行排序或分组的索引使用,请使用FOR ORDER BY
或FOR GROUP BY
。- 您可以指定多个索引提示:
SELECT
*
FROM t1
USE
INDEX
(i1
)
IGNORE
INDEX
FOR
ORDER
BY
(i2
)
ORDER
BY a
;
在多个提示中命名相同的索引不是错误(即使在相同的提示中):
SELECT
*
FROM t1
USE
INDEX
(i1
)
USE
INDEX
(i1
,i1
);
但是,对于同一个表 ,混合USE INDEX
和 是错误的:FORCE INDEX
SELECT
*
FROM t1
USE
INDEX
FOR
JOIN
(i1
)
FORCE
INDEX
FOR
JOIN
(i2
);
如果索引提示不包含FOR
子句,则提示的范围适用于语句的所有部分。例如,这个提示:
IGNORE
INDEX
(i1
)
相当于这种提示组合:
IGNORE
INDEX
FOR
JOIN
(i1
)
IGNORE
INDEX
FOR
ORDER
BY
(i1
)
IGNORE
INDEX
FOR
GROUP
BY
(i1
)
在 MySQL 5.0 中,没有FOR
子句的提示范围仅适用于行检索。FOR
要在不存在子句时使服务器使用此旧行为,请old在服务器启动时启用系统变量。请注意在复制设置中启用此变量。使用基于语句的二进制日志记录,源和副本具有不同的模式可能会导致复制错误。
处理索引提示时,它们按类型(USE
, FORCE
, IGNORE
)和范围(FOR JOIN
, FOR ORDER BY
, FOR GROUP BY
)收集在单个列表中。例如:
SELECT
*
FROM t1
USE
INDEX
()
IGNORE
INDEX
(i2
)
USE
INDEX
(i1
)
USE
INDEX
(i2
);
相当于:
SELECT
*
FROM t1
USE
INDEX
(i1
,i2
)
IGNORE
INDEX
(i2
);
然后按以下顺序将索引提示应用于每个范围:
{USE|FORCE} INDEX
如果存在则应用。(如果不是,则使用优化器确定的索引集。)IGNORE INDEX
应用于上一步的结果。例如,以下两个查询是等效的:
- SELECT
t1
USEi1
)i2
)i2
);
SELECT
*
FROM t1
USE
INDEX
(i1
);
对于FULLTEXT
搜索,索引提示的工作方式如下:
- 对于自然语言模式搜索,索引提示会被默默忽略。例如,
IGNORE INDEX(i1)
在没有警告的情况下被忽略并且索引仍然被使用。 - 对于布尔模式搜索,带有
FOR ORDER BY
或FOR GROUP BY
的索引提示会被忽略。FOR JOIN
尊重有或没有FOR
修饰符的索引提示。与提示如何应用于非FULLTEXT
搜索相反,提示用于查询执行的所有阶段(查找行和检索、分组和排序)。即使为非FULLTEXT
索引给出提示也是如此。
例如,以下两个查询是等效的:
SELECT
*
FROM t
USE
INDEX
(index1
)
IGNORE
INDEX
FOR
ORDER
BY
(index1
)
IGNORE
INDEX
FOR
GROUP
BY
(index1
)
WHERE
...
IN
BOOLEAN
MODE
...
;
SELECT
*
FROM t
USE
INDEX
(index1
)
WHERE
...
IN
BOOLEAN
MODE
...
;
8.9.5 优化器成本模型
为了生成执行计划,优化器使用一个成本模型,该模型基于对查询执行期间发生的各种操作的成本的估计。优化器有一组内置的默认“成本常量”可供它用来做出有关执行计划的决策。
优化器还有一个成本估算数据库,可在执行计划构建期间使用。这些估计值存储在系统数据库的server_cost
和 engine_cost
表中, mysql
并可随时配置。这些表的目的是使优化器在尝试到达查询执行计划时使用的成本估计可以轻松调整。
可配置的优化器成本模型是这样工作的:
- 服务器在启动时将成本模型表读入内存,并在运行时使用内存中的值。表中指定的任何非
NULL
成本估算优先于相应的编译默认成本常数。任何NULL
估计都向优化器表明使用编译的默认值。 - 在运行时,服务器可能会重新读取成本表。这发生在动态加载存储引擎或FLUSH OPTIMIZER_COSTS 执行语句时。
- 成本表使服务器管理员能够通过更改表中的条目轻松调整成本估算。通过将条目的成本设置为 也很容易恢复为默认值
NULL
。优化器使用内存中的成本值,因此应该对表进行更改 FLUSH OPTIMIZER_COSTS才能生效。 - 客户端会话开始时的当前内存中成本估计适用于整个会话,直到它结束。特别是,如果服务器重新读取成本表,则任何更改的估计仅适用于随后启动的会话。现有会话不受影响。
- 成本表特定于给定的服务器实例。服务器不会将成本表更改复制到副本。
优化器成本模型数据库由mysql
系统数据库中的两个表组成,其中包含查询执行期间发生的操作的成本估算信息:
该server_cost
表包含以下列:
cost_name
成本模型中使用的成本估算的名称。名称不区分大小写。如果服务器在读取此表时无法识别成本名称,则会将警告写入错误日志。
cost_value
成本估算值。如果该值为非NULL
,则服务器将其用作成本。否则,它使用默认估计值(编译值)。DBA 可以通过更新此列来更改成本估算。如果服务器在读取此表时发现成本值无效(非正数),则会将警告写入错误日志。
要覆盖默认成本估算(对于指定 的条目NULL
),请将成本设置为非NULL
值。要恢复为默认值,请将值设置为NULL
。然后执行FLUSH OPTIMIZER_COSTS告诉服务器重新读取成本表。
last_update
最后一行更新的时间。
comment
与成本估算相关的描述性注释。DBA 可以使用此列来提供有关成本估算行存储特定值的原因的信息。
表的主键server_cost
是cost_name
列,因此不可能为任何成本估算创建多个条目。
服务器识别表cost_name
的这些值server_cost
:
disk_temptable_create_cost
(默认 40.0),disk_temptable_row_cost
(默认 1.0)
存储在基于磁盘的存储引擎( InnoDB
或MyISAM
)中的内部创建的临时表的成本估算。增加这些值会增加使用内部临时表的成本估算,并使优化器更喜欢使用较少的查询计划。有关此类表的信息,请参阅 第 8.4.4 节,“MySQL 中的内部临时表使用”。
memory_temptable_create_cost
与相应内存参数 ( , ) 的默认值相比,这些磁盘参数的默认值memory_temptable_row_cost
越大,反映了处理基于磁盘的表的成本越高。
key_compare_cost
(默认 0.1)
比较记录键的成本。增加此值会导致比较许多键的查询计划变得更加昂贵。例如, filesort
与避免使用索引进行排序的查询计划相比,执行 a 的查询计划变得相对昂贵。
memory_temptable_create_cost
(默认 2.0),memory_temptable_row_cost
(默认 0.2)
存储在MEMORY
存储引擎中的内部创建的临时表的成本估算。增加这些值会增加使用内部临时表的成本估算,并使优化器更喜欢使用较少的查询计划。有关此类表的信息,请参阅 第 8.4.4 节,“MySQL 中的内部临时表使用”。
disk_temptable_create_cost
与相应磁盘参数 ( , ) 的默认值相比,这些内存参数的默认值越小,disk_temptable_row_cost
反映了处理基于内存的表的成本越低。
row_evaluate_cost
(默认 0.2)
评估记录条件的成本。与检查较少行的查询计划相比,增加此值会导致检查许多行的查询计划变得更加昂贵。例如,与读取较少行的范围扫描相比,表扫描变得相对昂贵。
该engine_cost
表包含以下列:
engine_name
此成本估算适用的存储引擎的名称。名称不区分大小写。如果值为 default
,则适用于所有没有自己的命名条目的存储引擎。如果服务器在读取此表时无法识别引擎名称,则会将警告写入错误日志。
device_type
此成本估算适用的设备类型。该列旨在为不同的存储设备类型指定不同的成本估算,例如硬盘驱动器与固态驱动器。目前,未使用此信息,并且 0 是唯一允许的值。
cost_name
与server_cost
表中相同。
cost_value
与server_cost
表中相同。
last_update
与server_cost
表中相同。
comment
与server_cost
表中相同。
表的主键engine_cost
是包含 ( cost_name
, engine_name
, device_type
) 列的元组,因此无法为这些列中的任何值组合创建多个条目。
服务器识别表cost_name
的这些值engine_cost
:
io_block_read_cost
(默认 1.0)
从磁盘读取索引或数据块的成本。与读取较少磁盘块的查询计划相比,增加此值会导致读取许多磁盘块的查询计划变得更加昂贵。例如,与读取较少块的范围扫描相比,表扫描变得相对昂贵。
memory_block_read_cost
(默认 1.0)
类似于io_block_read_cost
,但表示从内存数据库缓冲区中读取索引或数据块的成本。
如果io_block_read_cost
和 memory_block_read_cost
值不同,则执行计划可能会在同一查询的两次运行之间发生变化。假设内存访问的成本小于磁盘访问的成本。在这种情况下,在数据被读入缓冲池之前的服务器启动时,您可能会得到与运行查询后不同的计划,因为那时数据在内存中。
对于希望从默认值更改成本模型参数的 DBA,请尝试将值加倍或减半并衡量效果。
更改io_block_read_cost
和 memory_block_read_cost
参数最有可能产生有价值的结果。这些参数值使数据访问方法的成本模型能够考虑从不同来源读取信息的成本;也就是说,从磁盘读取信息与读取内存缓冲区中的信息的成本。例如,在所有其他条件相同的情况下,设置 io_block_read_cost
为大于 的值 memory_block_read_cost
会导致优化器更喜欢读取已保存在内存中的信息的查询计划,而不是必须从磁盘读取的计划。
此示例显示如何更改默认值 io_block_read_cost
:
UPDATE mysql
.engine_cost
SET cost_value
=
2.0
WHERE cost_name
=
'io_block_read_cost';
FLUSH
OPTIMIZER_COSTS;
此示例显示如何更改 io_block_read_cost
仅为 InnoDB
存储引擎的值:
INSERT
INTO mysql
.engine_cost
VALUES
('InnoDB',
0,
'io_block_read_cost',
3.0,
CURRENT_TIMESTAMP,
'Using a slower disk for InnoDB');
FLUSH
OPTIMIZER_COSTS;
MySQL 通过影响查询计划评估方式、可切换优化、优化器和索引提示以及优化器成本模型的系统变量来提供优化器控制。
8.10 缓冲和缓存
8.10.1 InnoDB 缓冲池优化
InnoDB维护一个称为缓冲池的存储区域, 用于在内存中缓存数据和索引。了解 InnoDB
缓冲池是如何工作的,并利用它来将经常访问的数据保存在内存中,是 MySQL 调优的一个重要方面。
有关 InnoDB
缓冲池内部工作原理的说明、其 LRU 替换算法的概述以及一般配置信息,请参阅第 14.5.1 节,“缓冲池”。
有关其他InnoDB
缓冲池配置和调整信息,请参阅以下部分:
- 第 14.8.3.4 节,“配置 InnoDB 缓冲池预取(预读)”
- 第 14.8.3.5 节,“配置缓冲池刷新”
- 第 14.8.3.3 节,“使缓冲池扫描抗性”
- 第 14.8.3.2 节,“配置多个缓冲池实例”
- 第 14.8.3.6 节,“保存和恢复缓冲池状态”
- 第 14.8.3.1 节,“配置 InnoDB 缓冲池大小”
8.10.4 准备好的语句和存储程序的缓存
对于客户端可能在会话期间多次执行的某些语句,服务器将语句转换为内部结构并缓存该结构以在执行期间使用。缓存使服务器能够更有效地执行,因为它避免了在会话期间再次需要语句时重新转换语句的开销。这些语句发生转换和缓存:
- 准备好的语句,包括在 SQL 级别处理的(使用PREPARE语句)和使用二进制客户端/服务器协议(使用 mysql_stmt_prepare()C API 函数)处理的语句。系统变量控制服务器缓存的 max_prepared_stmt_count 语句总数。(所有会话中准备好的语句数的总和。)
- 存储程序(存储过程和函数、触发器和事件)。在这种情况下,服务器会转换并缓存整个程序体。stored_program_cache系统变量指示服务器在每个会话中缓存的存储程序的大致数量 。
服务器在每个会话的基础上维护准备好的语句和存储的程序的缓存。为一个会话缓存的语句不能被其他会话访问。当会话结束时,服务器会丢弃为其缓存的所有语句。
当服务器使用缓存的内部语句结构时,它必须注意该结构不会过时。语句使用的对象可能会发生元数据更改,从而导致当前对象定义与内部语句结构中表示的定义不匹配。DDL 语句会发生元数据更改,例如创建、删除、更改、重命名或截断表,或者分析、优化或修复表的语句。表内容更改(例如,使用INSERT或 UPDATE)不会更改元数据,SELECT语句也不会。
这是问题的说明。假设客户准备了以下语句:
PREPARE s1
FROM
'SELECT * FROM t1';
在SELECT *
内部结构中扩展为表中的列列表。如果用 修改了表中的列集ALTER TABLE
,则准备好的语句将过期。如果下次客户端执行时服务器没有检测到这种变化s1
,则准备好的语句返回不正确的结果。
为避免准备好的语句引用的表或视图的元数据更改引起的问题,服务器会检测这些更改并在下次执行时自动重新准备该语句。即服务器重新解析语句并重建内部结构。在从表定义缓存中刷新引用的表或视图之后也会发生重新解析,或者隐式地为缓存中的新条目腾出空间,或者显式地由于FLUSH TABLES.
类似地,如果存储程序使用的对象发生变化,服务器会重新解析程序中受影响的语句。
服务器还检测表达式中对象的元数据更改。这些可能用于特定于存储程序的语句,例如DECLARE CURSOR
或流控制语句,如 IF、 CASE和 RETURN。
为了避免重新解析整个存储的程序,服务器仅在需要时重新解析程序中受影响的语句或表达式。例子:
- 假设表或视图的元数据已更改。重新分析发生
SELECT *
在程序中访问表或视图的 a 上,但不会发生SELECT *
在不访问表或视图的 a 上。 - 当一条语句受到影响时,如果可能,服务器只重新解析它的一部分。考虑这个 CASE陈述:
- CASE
END
CASE
如果元数据更改仅影响,则重新解析该表达式。 并且其他表达式不会重新解析。 WHEN
when_expr3
case_expr
WHEN
重新解析使用对原始转换为内部形式有效的默认数据库和 SQL 模式。
服务器最多尝试重新解析 3 次。如果所有尝试都失败,则会发生错误。
重新解析是自动的,但就其发生的程度而言,会降低准备好的语句和存储程序的性能。
对于准备好的语句, Com_stmt_reprepare 状态变量跟踪重新准备的次数。
MySQL 使用多种策略在内存缓冲区中缓存信息以提高性能。
8.11 优化锁定操作
8.11.1 内部锁定方法
本节讨论内部锁定;也就是说,在 MySQL 服务器内部执行锁定以管理多个会话对表内容的争用。这种类型的锁定是内部的,因为它完全由服务器执行,不涉及其他程序。有关其他程序对 MySQL 文件执行的锁定,请参阅第 8.11.5 节,“外部锁定”。
MySQL对表使用行级锁定InnoDB
来支持多个会话的同时写入访问,使其适用于多用户、高并发和 OLTP 应用程序。
为避免在单个表上执行多个并发写入操作时出现 死锁,请在事务开始时通过为预期要修改的每组行InnoDB
发出语句来获取必要的锁,即使数据更改语句在事务中稍后出现。SELECT ... FOR UPDATE
如果事务修改或锁定多个表,则在每个事务中以相同的顺序发出适用的语句。死锁会影响性能而不是表示严重错误,因为它 会InnoDB
自动 检测 死锁情况并回滚受影响的事务之一。
在高并发系统上,当多个线程等待同一个锁时,死锁检测会导致速度变慢。innodb_lock_wait_timeout 有时,禁用死锁检测并在发生死锁时依赖事务回滚设置可能更有效 。可以使用 innodb_deadlock_detect 配置选项禁用死锁检测。
行级锁定的优点:
- 当不同的会话访问不同的行时,更少的锁冲突。
- 回滚的更改更少。
- 可以长时间锁定单行。
MySQL对、 和表使用表级锁定, 一次只允许一个会话更新这些表。这种锁定级别使这些存储引擎更适合只读、主要读取或单用户应用程序。 MyISAMMEMORYMERGE
这些存储引擎 通过始终在查询开始时一次请求所有需要的锁并始终以相同的顺序锁定表来避免死锁。权衡是这种策略降低了并发性;其他想要修改表的会话必须等到当前数据更改语句完成。
表级锁定的优点:
- 需要的内存相对较少(行锁定需要每行或每组锁定的行的内存)
- 在表的大部分上使用时速度很快,因为只涉及一个锁。
- 如果您经常
GROUP BY
对大部分数据进行操作或必须经常扫描整个表,则速度很快。
MySQL 授予表写锁如下:
- 如果表上没有锁,则在其上放置写锁。
- 否则,将锁请求放入写锁队列。
MySQL 授予表读锁如下:
- 如果表上没有写锁,则在其上放置读锁。
- 否则,将锁请求放入读锁队列。
表更新的优先级高于表检索。因此,当一个锁被释放时,锁对写锁队列中的请求可用,然后对读锁队列中的请求可用。这确保了即使在表有大量活动时,对表的更新也不会“饿死” 。SELECT但是,如果一个表有很多更新, SELECT语句会一直等待,直到没有更多更新。
有关更改读取和写入优先级的信息,请参阅第 8.11.2 节,“表锁定问题”。
您可以通过检查 Table_locks_immediate和 Table_locks_waitedstatus 变量来分析系统上的表锁争用情况,这些变量分别表示可以立即授予表锁请求的次数和必须等待的次数:
mysql>
SHOW
STATUS
LIKE
'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited | 15324 |
+-----------------------+---------+
Performance Schema 锁定表还提供锁定信息。请参阅 第 25.12.12 节,“性能模式锁定表”。
MyISAM
存储引擎支持并发插入以减少给定表的读取器和写入器之间的争用:如果 表MyISAM
在数据文件的中间没有空闲块,则始终在数据文件的末尾插入行。在这种情况下,您可以为没有锁的表自由混合并发 INSERT和 SELECT语句 。MyISAM
也就是说,您可以将行插入到MyISAM
表同时其他客户端正在读取它。空洞可能是由于表中间的行被删除或更新造成的。如果有空洞,并发插入将被禁用,但在所有空洞都被新数据填充后会自动再次启用。要控制此行为,请使用 concurrent_insert系统变量。请参阅第 8.11.3 节,“并发插入”。
如果您使用 显式获取表锁 LOCK TABLES,则可以请求 READ LOCAL
锁而不是 READ
锁,以使其他会话在您锁定表时执行并发插入。
要在无法同时插入时 对表 执行许多操作 INSERT, 您可以将行插入临时表并使用临时表中的行更新真实表: SELECTt1temp_t1
mysql>
LOCK
TABLES t1
WRITE, temp_t1
WRITE;
mysql>
INSERT
INTO t1
SELECT
*
FROM temp_t1
;
mysql>
DELETE
FROM temp_t1
;
mysql>
UNLOCK
TABLES;
通常,在以下情况下,表锁优于行级锁:
- 该表的大多数语句都是读取。
- 表的语句是读取和写入的混合,其中写入是可以通过一个键读取获取的单行的更新或删除:
- UPDATE
DELETE
FROM
tbl_name
WHERE
unique_key_col=key_value;
使用更高级别的锁,您可以通过支持不同类型的锁来更轻松地调整应用程序,因为锁开销低于行级锁。
行级锁定以外的选项:
- 版本控制(例如在 MySQL 中用于并发插入的版本),其中可以有一个写入器同时拥有多个读取器。这意味着数据库或表根据访问开始的时间支持不同的数据视图。其他常用术语是 “时间旅行”、 “写时复制” 或“按需复制”。”
- 按需复制在许多情况下优于行级锁定。但是,在最坏的情况下,它可以使用比使用普通锁更多的内存。
- 除了使用行级锁,您还可以使用应用程序级锁,例如 MySQL 提供的 GET_LOCK()和 RELEASE_LOCK()在 MySQL 中提供的锁。这些是咨询锁,因此它们仅适用于相互协作的应用程序。请参见 第 12.15 节,“锁定功能”。
8.11.2 表锁定问题
InnoDB
表使用行级锁定,以便多个会话和应用程序可以同时读取和写入同一个表,而不会让彼此等待或产生不一致的结果。对于此存储引擎,请避免使用该LOCK TABLES语句,因为它不提供任何额外的保护,反而会降低并发性。自动行级锁定使这些表适用于包含最重要数据的最繁忙的数据库,同时还简化了应用程序逻辑,因为您不需要锁定和解锁表。因此, InnoDB
存储引擎是 MySQL 中的默认值。
MySQL 对除 InnoDB
. 锁定操作本身并没有太多开销。但是因为在任何时候只有一个会话可以写入表,为了获得这些其他存储引擎的最佳性能,主要将它们用于经常查询并且很少插入或更新的表。
在选择是使用还是不同的存储引擎创建表时 InnoDB
,请记住表锁定的以下缺点:
- 表锁定允许多个会话同时从表中读取,但是如果一个会话想要写入一个表,它必须首先获得独占访问,这意味着它可能必须等待其他会话首先完成表。在更新期间,想要访问此特定表的所有其他会话必须等到更新完成。
- 当会话等待时,表锁定会导致问题,因为磁盘已满并且在会话可以继续之前需要可用空间。在这种情况下,所有想要访问问题表的会话也都处于等待状态,直到有更多磁盘空间可用。
- SELECT运行时间较长 的语句会阻止其他会话同时更新表,从而使其他会话显得缓慢或无响应。当一个会话等待获取对表的独占访问权以进行更新时,其他发出 SELECT语句的会话在它后面排队,即使是只读会话也会降低并发性。
以下项目描述了避免或减少表锁定引起的争用的一些方法:
- 考虑将表切换到
InnoDB
存储引擎,或者CREATE TABLE ... ENGINE=INNODB
在设置期间使用,或者ALTER TABLE ... ENGINE=INNODB
用于现有表。有关此存储引擎的更多详细信息,请参阅 第 14 章,InnoDB 存储引擎。 - 优化SELECT语句以更快地运行,以便它们锁定表的时间更短。您可能必须创建一些汇总表来执行此操作。
- 用. _ _ --low-priority-updates对于仅使用表级锁定(例如 、 和 )的存储引擎
MyISAM
,MEMORY
这MERGE
会赋予所有更新(修改)表的语句低于 SELECT语句的优先级。在这种情况下,SELECT 前面场景中的第二个语句将在该UPDATE语句之前执行,并且不会等待第一个语句 SELECT完成。 - 要指定在特定连接中发布的所有更新都应以低优先级完成,请将 low_priority_updates 服务器系统变量设置为 1。
- 要赋予特定INSERT的 UPDATE、 或 DELETE语句较低的优先级,请使用该
LOW_PRIORITY
属性。 - 要赋予特定SELECT 语句更高的优先级,请使用该
HIGH_PRIORITY
属性。请参阅 第 13.2.9 节,“SELECT 语句”。 - 以系统变量的低值 启动mysqldmax_write_lock_count , 以强制 MySQL 在发生特定数量的表写入锁后临时提升所有SELECT 正在等待表的语句的优先级(例如,对于插入操作)。这允许在一定数量的写锁之后读锁。
- 如果您在混合 SELECT和 DELETE语句方面遇到问题,
LIMIT
选项 to DELETE可能会有所帮助。请参阅 第 13.2.2 节,“DELETE 语句”。 - 使用
SQL_BUFFER_RESULT
with SELECT语句有助于缩短表锁的持续时间。请参阅 第 13.2.9 节,“SELECT 语句”。 - 将表内容拆分为单独的表可能会有所帮助,因为它允许对一个表中的列运行查询,而更新仅限于不同表中的列。
- 您可以更改锁定代码
mysys/thr_lock.c
以使用单个队列。在这种情况下,写锁和读锁将具有相同的优先级,这可能有助于某些应用程序。
8.11.3 并发插入
MyISAM存储引擎支持并发插入以减少给定表的读写器之间的争用:如果一个 表MyISAM在数据文件中没有空洞(中间删除了行), INSERT则可以执行一条语句将行添加到表的末尾同时 SELECT语句正在从表中读取行。如果有多个 INSERT语句,它们将与 SELECT语句同时排队并按顺序执行。并发的结果INSERT可能不会立即可见。
concurrent_insert可以设置系统变量来修改并发插入处理 。默认情况下,该变量设置为AUTO(或 1),并发插入的处理方式如前所述。如果 concurrent_insert设置为 NEVER(或 0),则禁用并发插入。如果变量设置为ALWAYS (或 2),即使对于已删除行的表,也允许在表末尾进行并发插入。另见concurrent_insert系统变量的描述。
如果您使用的是二进制日志,则并发插入将转换为CREATE ... SELECTor INSERT ... SELECT语句的正常插入。这样做是为了确保您可以通过在备份操作期间应用日志来重新创建表的精确副本。请参阅第 5.4.4 节,“二进制日志”。此外,对于那些语句,读锁被放置在选定的表上,以便阻止插入到该表中。结果是该表的并发插入也必须等待。
使用LOAD DATA,如果您指定 CONCURRENT一个MyISAM 满足并发插入条件的表(即,它中间不包含空闲块),则其他会话可以在LOAD DATA执行时从该表中检索数据。使用该 CONCURRENT选项会影响 LOAD DATA一点性能,即使没有其他会话同时使用该表。
如果您指定HIGH_PRIORITY,如果服务器是使用该选项启动的,它会覆盖该选项的效果 --low-priority-updates。它还会导致不使用并发插入。
对于,和LOCK TABLE之间的区别在于允许 在 持有锁时执行非冲突语句(并发插入)。但是,如果您要在持有锁的同时使用服务器外部的进程来操作数据库,则不能使用此功能。 READ LOCALREADREAD LOCALINSERT
8.11.4 元数据锁定
MySQL 使用元数据锁定来管理对数据库对象的并发访问并确保数据一致性。元数据锁定不仅适用于表,还适用于模式、存储程序(过程、函数、触发器、调度事件)、表空间、使用 GET_LOCK()函数获取的用户锁(参见 第 12.15 节,“锁定函数”),以及使用第 5.5.6.1 节“锁定服务”中描述 的锁定服务。
Performance Schema metadata_locks表公开了元数据锁信息,这对于查看哪些会话持有锁、被阻塞等待锁等等很有用。有关详细信息,请参阅 第 25.12.12.1 节,“元数据锁表”。
元数据锁定确实涉及一些开销,随着查询量的增加而增加。多个查询尝试访问相同对象的次数越多,元数据争用就越多。
元数据锁定不是表定义缓存的替代品,它的互斥量和锁与 LOCK_open
互斥量不同。以下讨论提供了有关元数据锁定如何工作的一些信息。
如果给定锁有多个等待者,则首先满足最高优先级的锁请求,但与 max_write_lock_count系统变量相关的异常除外。写锁请求的优先级高于读锁请求。但是,如果 max_write_lock_count设置为某个较低的值(例如,10),如果读锁定请求已经被传递给 10 个写锁定请求,则读锁定请求可能优于挂起的写锁定请求。通常不会发生此行为,因为 max_write_lock_count默认情况下具有非常大的值。
语句一个一个地获取元数据锁,而不是同时获取,并在过程中进行死锁检测。
DML 语句通常按照语句中提及表的顺序获取锁。
DDL 语句LOCK TABLES和其他类似语句尝试通过按名称顺序获取显式命名表上的锁来减少并发 DDL 语句之间可能出现的死锁数量。对于隐式使用的表(例如也必须锁定的外键关系中的表),可能会以不同的顺序获取锁。
例如,RENAME TABLE是一个按名称顺序获取锁的 DDL 语句:
- 此RENAME TABLE语句重命名
tbla
为其他内容,并重命名tblc
为tbla
:
RENAME
TABLE tbla
TO tbld
, tblc
TO tbla
;
该语句按顺序获取元数据锁 on tbla
, tblc
, and tbld
(因为tbld
按照tblc
名称顺序):
- 这个略有不同的语句也重命名
tbla
为其他内容,并重命名tblc
为tbla
:
RENAME
TABLE tbla
TO tblb
, tblc
TO tbla
;
在这种情况下,该语句按顺序获取元数据锁, on tbla
, tblb
, and tblc
(因为在名称顺序 tblb
之前 ):tblc
两个语句都按顺序在tbla
和 上获取锁tblc
,但在剩余表名上的锁是在 之前还是之后获取的不同tblc
。
当多个事务同时执行时,元数据锁获取顺序会影响操作结果,如下例所示。
从两个具有相同结构的表x
开始 。x_new
三个客户发出涉及这些表的语句:
客户 1:
LOCK
TABLE x
WRITE, x_new
WRITE;
x
该语句在和 上按名称顺序请求并获取写锁x_new
。
客户 2:
INSERT
INTO x
VALUES(1);
该语句请求并阻塞等待写锁定 x
。
客户 3:
RENAME
TABLE x
TO x_old
, x_new
TO x
;
x
该语句在、x_new
和 上按名称顺序请求排他锁 x_old
,但会阻塞等待上锁 x
。
客户 1:
UNLOCK
TABLES;
x
该语句释放和 上的写锁定x_new
。客户端 3的独占锁请求 x
比客户端 2 的写锁请求具有更高的优先级,因此客户端 3 在 上获取锁x
,然后在x_new
and上获取锁x_old
,执行重命名,并释放其锁。客户端 2 然后获取它的锁 x
,执行插入,并释放它的锁。
锁获取顺序导致 RENAME TABLE在INSERT. x
插入发生的表是客户 x_new
端 2 发出插入时命名x
并由客户端 3 重命名的表:
mysql>
SELECT
*
FROM x
;
+------+
| i |
+------+
| 1 |
+------+
mysql>
SELECT
*
FROM x_old
;
Empty set (0.01 sec)
现在从命名的x
并且 new_x
具有相同结构的表开始。同样,三个客户端发出涉及这些表的语句:
客户 1:
LOCK
TABLE x
WRITE, new_x
WRITE;
new_x
该语句在和 上按名称顺序请求并获取写锁x
。
客户 2:
INSERT
INTO x
VALUES(1);
该语句请求并阻塞等待写锁定 x
。
客户 3:
RENAME
TABLE x
TO old_x
, new_x
TO x
;
new_x
该语句在、old_x
和 上按名称顺序请求排他锁 x
,但会阻塞等待上锁 new_x
。
客户 1:
UNLOCK
TABLES;
x
该语句释放和 上的写锁定new_x
。对于x
,唯一挂起的请求是由客户端 2 发出的,所以客户端 2 获取它的锁,执行插入,然后释放锁。对于 new_x
,唯一挂起的请求来自客户端 3,它被允许获取该锁(以及 上的锁old_x
)。x
在客户端 2 插入完成并释放其锁定之前,重命名操作仍会阻止锁定。然后客户端 3 获取 上的锁x
,执行重命名,并释放它的锁。
在这种情况下,锁获取顺序会导致 INSERT在 RENAME TABLE. x
插入发生的 是原来的, x
现在 old_x
通过重命名操作重命名为:
mysql>
SELECT
*
FROM x
;
Empty set (0.01 sec)
mysql>
SELECT
*
FROM old_x
;
+------+
| i |
+------+
| 1 |
+------+
如果并发语句中的锁获取顺序对应用程序的操作结果有影响,如前面的示例,您可以调整表名以影响锁获取的顺序。
为确保事务可串行化,服务器不得允许一个会话对另一个会话中未完成的显式或隐式启动事务中使用的表执行数据定义语言 (DDL) 语句。服务器通过获取事务中使用的表上的元数据锁并将这些锁的释放推迟到事务结束时来实现这一点。表上的元数据锁可防止更改表的结构。这种锁定方法意味着一个会话中的事务正在使用的表在事务结束之前不能由其他会话在 DDL 语句中使用。
这个原则不仅适用于事务表,也适用于非事务表。假设一个会话开始一个使用事务表 t
和非 事务表的事务nt
,如下所示:
START
TRANSACTION;
SELECT
*
FROM t
;
SELECT
*
FROM nt
;
服务器在这两个上都持有元数据锁t
,nt
直到事务结束。如果另一个会话尝试对任一表执行 DDL 或写入锁定操作,它会阻塞直到在事务结束时释放元数据锁定。例如,如果第二个会话尝试以下任何操作,则它会阻塞:
DROP
TABLE t
;
ALTER
TABLE t
...;
DROP
TABLE nt
;
ALTER
TABLE nt
...;
LOCK
TABLE t
...
WRITE;
相同的行为适用于 The LOCK TABLES ... READ。也就是说,显式或隐式启动的事务会更新任何表(事务性或非事务性)阻塞并被LOCK TABLES ... READ
该表阻塞。
如果服务器获取语法上有效但在执行期间失败的语句的元数据锁,它不会提前释放锁。锁释放仍然延迟到事务结束,因为失败的语句被写入二进制日志并且锁保护了日志的一致性。
在自动提交模式下,每个语句实际上都是一个完整的事务,因此为语句获取的元数据锁只保留到语句的末尾。
PREPARE一旦准备好语句,即使准备发生在多语句事务中,在语句 期间获取的元数据锁 也会被释放。
8.11.5 外部锁定
外部锁定是使用文件系统锁定来管理MyISAM多个进程对数据库表的争用。外部锁定用于不能假定单个进程(如 MySQL 服务器)是需要访问表的唯一进程的情况。这里有些例子:
- 如果您运行使用相同数据库目录的多台服务器(不推荐),则每台服务器都必须启用外部锁定。
- 如果您使用myisamchk对表执行表维护操作 MyISAM,则必须确保服务器未运行,或者服务器已启用外部锁定,以便它在必要时锁定表文件以与myisamchk协调以 访问表。使用 myisampack打包 MyISAM表格也是如此。
如果服务器在启用外部锁定的情况下运行,您可以随时使用myisamchk进行读取操作,例如检查表。在这种情况下,如果服务器尝试更新 myisamchk正在使用的表,则服务器会等待 myisamchk完成后再继续。
如果使用myisamchk进行修复或优化表等写入操作,或者使用 myisampack打包表, 则必须始终确保 mysqld服务器没有使用该表。如果您不停止mysqld,至少 在运行myisamchk之前执行 mysqladmin flush-tables。如果服务器和 myisamchk同时访问表, 您的表可能会损坏。
在外部锁定生效的情况下,每个需要访问表的进程都会在继续访问表之前为表文件获取文件系统锁。如果无法获得所有必要的锁,则阻止进程访问表,直到可以获得锁(在当前持有锁的进程释放它们之后)。
外部锁定会影响服务器性能,因为服务器有时必须等待其他进程才能访问表。
如果您运行单个服务器来访问给定的数据目录(这是通常的情况)并且如果在服务器运行时没有其他程序(例如myisamchk )需要修改表,则不需要外部锁定。如果您只 使用其他程序读取表,则不需要外部锁定,尽管如果服务器在myisamchk读取 表时更改表, myisamchk 可能会报告警告 。
在禁用外部锁定的情况下,要使用 myisamchk ,您必须在myisamchk执行时停止服务器,或者在运行myisamchk之前锁定并刷新表。(请参阅第 8.12.1 节,“系统因素”。)为避免此要求,请使用CHECK TABLE andREPAIR TABLE语句检查和修复MyISAM表。
对于mysqld,外部锁定由 skip_external_locking系统变量的值控制。启用此变量时,将禁用外部锁定,反之亦然。默认情况下禁用外部锁定。
使用--external-lockingor --skip-external-locking 选项可以在服务器启动时控制外部锁定的使用。
如果您确实使用外部锁定选项来启用对 MyISAM来自多个 MySQL 进程的表的更新,则必须确保满足以下条件:
- 不要将查询缓存用于使用由另一个进程更新的表的查询。
- 不要在 delay_key_write系统变量设置为的情况下启动服务器,也不要对任何共享表
ALL
使用DELAY_KEY_WRITE=1
table 选项。否则,可能会发生索引损坏。
满足这些条件的最简单方法是始终 与和 --external-locking一起使用 。(默认情况下不这样做,因为在许多设置中混合使用上述选项很有用。) --delay-key-write=OFF--query-cache-size=0
MySQL 使用锁定 管理表内容的争用 :
- MySQL 服务器内部执行内部锁定,以管理多个线程对表内容的争用。这种类型的锁定是内部的,因为它完全由服务器执行,不涉及其他程序。请参阅 第 8.11.1 节,“内部锁定方法”。
当服务器和其他程序锁定MyISAM表文件以在它们之间协调哪个程序可以在何时访问表时,就会发生外部锁定。请参见第 8.11.5 节,“外部锁定”。