MySQL查询优化程序
时间：2008-05-29 来源：剑心通明
4.2 MySQL查询优化程序
在发布一个选择行的查询时， MySQL进行分析，看是否能够对它进行优化，使它执行更快。本节中，我们将研究查询优化程序怎样工作。更详细的信息，可参阅MySQL参考指南中的“Getting Maximum Performance from MySQL”，该章描述了MySQL采用的各种优化措
施。该章中的信息会不断变化，因为MySQL的开发者不断对优化程序进行改进，因此，有必要经常拜访一下该章，看看是否有可供利用的新技巧。（http://www.mysql.com/ 处的MySQL联机参考指南在不断地更新。）
MySQL查询优化程序利用了索引。当然，它也利用了其他信息。例如，如果发布下列查询，MySQL将非常快地执行它，不管相应的表有多大：
SELECT * FROM tb1_name WHERE 1= 0
在此情形中，MySQL考察WHERE 子句，如果认识到不可能有满足该查询的行，就不会对该表进行搜索。可利用EXPLAIN 语句知道这一点，EXPLAIN 语句要求MySQL显示某些有关它应该执行一条SELECT 查询，而实际没有执行的信息。为了使用E X P L A I N，只需要SELECT 语句前放置EXPLAIN 即可，如下所示：
EXPLAIN SELECT * FROM tb1_name WHERE 1= 0
通常，EXPLAIN 返回的信息比这个多，包括将用来扫描表的索引、将要使用的连接类型以及需要在每个表中扫描的行数估计等等。
4.2.1优化程序怎样工作
MySQL查询优化程序有几个目标，但其主要目标是尽量利用索引，而且尽量使用最具有限制性的索引以排除尽可能多的行。这样做可能会适得其反，因为发布一条SELECT 语句的目的是寻找行，而不是拒绝它们。优化程序这样工作的原因是从要考虑的行中排除行越快，那么找到确实符合给出标准的行就越快。如果能够首先进行最具限制性的测试，则查询可以进行得更快。假如有一个测试两列的查询，每列上都有一个索引：
WHERE coll = "some value" AND col2 = "some other value"
还假定，与col1上的测试相符的有900 行，与col2 上的测试相符的有300 行，而两个测试都通过的有30 行。如果首先测试c o l 1，必须检查900 行以找到也与col2 值相符的30 行。那么测试中有870 将失败。如果首先测试c o l 2，要找到也与col1值相符的30 行，只需检查300 行。测试中有失败270 次，这样所涉及的计算较少，磁盘I/O 也较少。遵循下列准则，有助于优化程序利用索引：
■ 比较具有相同类型的列。在比较中利用索引列时，应该使用那些类型相同的列。例如，CHAR(10) 被视为与CHAR(10) 或VARCHAR(10) 相同，但不同于CHAR(12) 和VARCHAR( 12 )。INT 与BIGINT 不同。在MySQL3.23 版以前，要求使用相同类型的
列，否则列上的索引将不起作用。自3.23 版后，不严格要求这样做，但相同的列类型比不同类型提供更好的性能。如果所比较的两列类型不同，可使用ALTER TABLE语句修改其中之一使它们的类型相配。
■ 比较中应尽量使索引列独立。如果在函数调用或算术表达式中使用一个列，则MySQL不能使用这样的索引，因为它必须对每行计算表达式的值。有时，这是不可避免的，但很多时候，可以重新编写只取索引列本身的查询。下面的WHERE 子句说明了怎样进行这项工作。第一行中，优化程序将简化表达式4/2 为值2，然后使用my_col 上的索引快速地找到小于2 的值。而在第二个表达式中，MySQL必须检索出每行的my_col 值，乘以2，然后将结果与4 比较。没索引可用，因为列中的每个值都要检索，以便能对左边的表达式求值：
WHERE my_col < 4/2
WHERE my_col * 2 < 4
让我们考虑另一个例子。假如有一个索引列date _ c o l。如果发布如下的查询，相应的索引未被使用：
SELECT * FROM my_tb1WHERE YEAR(date_col) < 1990
其中表达式并不将索引列与1990 比较，而是将从列值计算出的值用于比较，而且必须计算每行的这个值。结果是， date_col 上的索引不可能得到使用。怎样解决？使用一个文字日期即可，这时将会使用date_col 上的索引：
WHERE date_col < "1990-01-01"
但是假如没有特定的日期值，那么可能会对找到具有出现在距今一定天数内的日期的记录感兴趣。有几种方法来编写这样的查询，但并非所有方法都很好。三种可能的方法如下:
其中第一行不能利用索引， 因为必须为每行检索列， 以便能够计算TO _ DAYS(date_col) 的值。第二行要好一些。c ut o ff 和TO _ DAY S ( CURRENT _ DATE) 两者都是常量，因此比较表达式的右边可在查询处理前由优化程序一次计算出来，而不是每行计算一次。但date_col 列仍然出现在一个函数调用中，因此，没有使用索引。第三行是最好的方法。比较表达式的右边可在执行查询前作为常量一次计算出来，但现在其值是一个日期。这个值可直接与date_col 的值进行比较，不再需要转换为天数，可以利用索引。
■ 在LIKE 模式的起始处不要使用通配符。有时，有的人会用下列形式的WHERE 子句来搜索串：
WHERE col_name LIKE "%string%"
如果希望找到s t r i n g，不管它出现在列中任何位置，那么这样做是对的。但不要出于习惯在串的两边加“ %”。如果实际要查找的只是出现在列的开始处的串，则不应该要第一个“%”号。例如，如果在一个包含姓的列中查找“ M a c”起始的姓，应该编写如下的WHERE 子句：
WHERE last_name LIKE "Mac%"
优化程序考虑模式中的开始的文字部分，然后利用索引找到相符合的行。不过宁可写成如下的表达式，它允许使用last_name 上的索引：
WHERE last_name >= "Mac" AND last_name < "Mad"
这种优化对使用REGEXP 操作符的模式匹配不起作用。
■ 帮助优化程序更好地评估索引的有效性。缺省时，如果将索引列中的值与常量进行比较，优化程序将假定键字是均匀地分布在索引中的。优化程序还将对索引进行一个快速的检查，以估计在确定相应的索引是否应该用于常量的比较时要使用多少条目。可利用myisamchk 或isamchk 的--analyze 选项给优化程序提供更好的信息，以便分析键值的分布。myisamchk 用于MyISAM 表，isamchk 用于ISAM 表。为了完成键值分析，必须能够登录到MySQL服务器主机中，而且必须对表文件具有写访问权限。
■ 利用EXPLAIN 检验优化程序操作。检查用于查询中的索引是否能很快地排除行。如果不能，那么应该试一下利用STRAIGHT_JOIN 强制按特定次序使用表来完成一个连接。查询的执行方式不那么显然；MySQL可能会有很多理由不以您认为最好的次序使用索引。
■ 测试查询的其他形式，而且不止一次地运行它们。在测试一个查询的其他形式时，应该每种方法运行几次。如果对两个不同方法中的每种只运行查询一次，通常会发现第二个查询更快，因为来自第一个查询的信息在磁盘高速缓存中，不需要实际从磁盘上读出。还应该尽量在系统负载相对平稳的时候运行查询，以避免受系统中其他活动的影响。
4.2.2 忽略优化
这可能听起来有点奇怪，但在以下情况中，要废除MySQL的优化功能：
■ 强迫MySQL慢慢地删除表的内容。在需要完全删空一个表时，利用无WHERE 子句的DELETE 语句删除整个表的内容是最快的，如下所示：
DELETE FROM tb1_name
MySQL对这种特殊情况的DELETE 进行优化；它利用表信息文件中的表说明从头开始创建空数据文件和索引文件。这种优化使DELETE 操作极快，因为MySQL无需单独地删除每一行。但在某些情况下，这样做会产生一些不必要的负作用：
■ MySQL报告所涉及的行数为零，即使表不为空也是如此。很多时候这没有关系（虽然，如果事先没有思想准备，会感到困惑不解），但对于那些确实需要知道真实行数的应用程序来说，这是不恰当的。
■ 如果表含有一个AUTO_INCREMENT 列，则该列的顺序编号会以1从头开始。这是真实的事情，即使在MySQL3.23 中对AUTO_INCREMENT 的处理进行了改进后也是这样。关于这个改进的介绍请参阅第2章中的“使用序列”小节。可增加WHERE 1> 0 子句对DELETE 语句“不优化”。
DELETE FROM tb1_name WHERE 1> 0
这迫使MySQL进行逐行的删除。相应的查询执行要慢得多，但将返回真正删除的行数。它还将保持当前的AUTO_INCREMENT 序列的编号，不过只对MyISAM 表（MySQL3.23 以上的版本可用）有效。而对于ISAM 表，序列仍将重置。
■ 避免更新循环不终止。如果更新一个索引列，如果该列用于WHERE 子句且更新将索引值移入至今尚未出超的取值范围内时，有可能对所更新的行进行不终止的更新。假如表my_tbl 有一个索引了的整数列key _ c o l。下列的查询会产生问题：
在发布一个选择行的查询时， MySQL进行分析，看是否能够对它进行优化，使它执行更快。本节中，我们将研究查询优化程序怎样工作。更详细的信息，可参阅MySQL参考指南中的“Getting Maximum Performance from MySQL”，该章描述了MySQL采用的各种优化措
施。该章中的信息会不断变化，因为MySQL的开发者不断对优化程序进行改进，因此，有必要经常拜访一下该章，看看是否有可供利用的新技巧。（http://www.mysql.com/ 处的MySQL联机参考指南在不断地更新。）
MySQL查询优化程序利用了索引。当然，它也利用了其他信息。例如，如果发布下列查询，MySQL将非常快地执行它，不管相应的表有多大：
SELECT * FROM tb1_name WHERE 1= 0
在此情形中，MySQL考察WHERE 子句，如果认识到不可能有满足该查询的行，就不会对该表进行搜索。可利用EXPLAIN 语句知道这一点，EXPLAIN 语句要求MySQL显示某些有关它应该执行一条SELECT 查询，而实际没有执行的信息。为了使用E X P L A I N，只需要SELECT 语句前放置EXPLAIN 即可，如下所示：
EXPLAIN SELECT * FROM tb1_name WHERE 1= 0
通常，EXPLAIN 返回的信息比这个多，包括将用来扫描表的索引、将要使用的连接类型以及需要在每个表中扫描的行数估计等等。
4.2.1优化程序怎样工作
MySQL查询优化程序有几个目标，但其主要目标是尽量利用索引，而且尽量使用最具有限制性的索引以排除尽可能多的行。这样做可能会适得其反，因为发布一条SELECT 语句的目的是寻找行，而不是拒绝它们。优化程序这样工作的原因是从要考虑的行中排除行越快，那么找到确实符合给出标准的行就越快。如果能够首先进行最具限制性的测试，则查询可以进行得更快。假如有一个测试两列的查询，每列上都有一个索引：
WHERE coll = "some value" AND col2 = "some other value"
还假定，与col1上的测试相符的有900 行，与col2 上的测试相符的有300 行，而两个测试都通过的有30 行。如果首先测试c o l 1，必须检查900 行以找到也与col2 值相符的30 行。那么测试中有870 将失败。如果首先测试c o l 2，要找到也与col1值相符的30 行，只需检查300 行。测试中有失败270 次，这样所涉及的计算较少，磁盘I/O 也较少。遵循下列准则，有助于优化程序利用索引：
■ 比较具有相同类型的列。在比较中利用索引列时，应该使用那些类型相同的列。例如，CHAR(10) 被视为与CHAR(10) 或VARCHAR(10) 相同，但不同于CHAR(12) 和VARCHAR( 12 )。INT 与BIGINT 不同。在MySQL3.23 版以前，要求使用相同类型的
列，否则列上的索引将不起作用。自3.23 版后，不严格要求这样做，但相同的列类型比不同类型提供更好的性能。如果所比较的两列类型不同，可使用ALTER TABLE语句修改其中之一使它们的类型相配。
■ 比较中应尽量使索引列独立。如果在函数调用或算术表达式中使用一个列，则MySQL不能使用这样的索引，因为它必须对每行计算表达式的值。有时，这是不可避免的，但很多时候，可以重新编写只取索引列本身的查询。下面的WHERE 子句说明了怎样进行这项工作。第一行中，优化程序将简化表达式4/2 为值2，然后使用my_col 上的索引快速地找到小于2 的值。而在第二个表达式中，MySQL必须检索出每行的my_col 值，乘以2，然后将结果与4 比较。没索引可用，因为列中的每个值都要检索，以便能对左边的表达式求值：
WHERE my_col < 4/2
WHERE my_col * 2 < 4
让我们考虑另一个例子。假如有一个索引列date _ c o l。如果发布如下的查询，相应的索引未被使用：
SELECT * FROM my_tb1WHERE YEAR(date_col) < 1990
其中表达式并不将索引列与1990 比较，而是将从列值计算出的值用于比较，而且必须计算每行的这个值。结果是， date_col 上的索引不可能得到使用。怎样解决？使用一个文字日期即可，这时将会使用date_col 上的索引：
WHERE date_col < "1990-01-01"
但是假如没有特定的日期值，那么可能会对找到具有出现在距今一定天数内的日期的记录感兴趣。有几种方法来编写这样的查询，但并非所有方法都很好。三种可能的方法如下:
其中第一行不能利用索引， 因为必须为每行检索列， 以便能够计算TO _ DAYS(date_col) 的值。第二行要好一些。c ut o ff 和TO _ DAY S ( CURRENT _ DATE) 两者都是常量，因此比较表达式的右边可在查询处理前由优化程序一次计算出来，而不是每行计算一次。但date_col 列仍然出现在一个函数调用中，因此，没有使用索引。第三行是最好的方法。比较表达式的右边可在执行查询前作为常量一次计算出来，但现在其值是一个日期。这个值可直接与date_col 的值进行比较，不再需要转换为天数，可以利用索引。
■ 在LIKE 模式的起始处不要使用通配符。有时，有的人会用下列形式的WHERE 子句来搜索串：
WHERE col_name LIKE "%string%"
如果希望找到s t r i n g，不管它出现在列中任何位置，那么这样做是对的。但不要出于习惯在串的两边加“ %”。如果实际要查找的只是出现在列的开始处的串，则不应该要第一个“%”号。例如，如果在一个包含姓的列中查找“ M a c”起始的姓，应该编写如下的WHERE 子句：
WHERE last_name LIKE "Mac%"
优化程序考虑模式中的开始的文字部分，然后利用索引找到相符合的行。不过宁可写成如下的表达式，它允许使用last_name 上的索引：
WHERE last_name >= "Mac" AND last_name < "Mad"
这种优化对使用REGEXP 操作符的模式匹配不起作用。
■ 帮助优化程序更好地评估索引的有效性。缺省时，如果将索引列中的值与常量进行比较，优化程序将假定键字是均匀地分布在索引中的。优化程序还将对索引进行一个快速的检查，以估计在确定相应的索引是否应该用于常量的比较时要使用多少条目。可利用myisamchk 或isamchk 的--analyze 选项给优化程序提供更好的信息，以便分析键值的分布。myisamchk 用于MyISAM 表，isamchk 用于ISAM 表。为了完成键值分析，必须能够登录到MySQL服务器主机中，而且必须对表文件具有写访问权限。
■ 利用EXPLAIN 检验优化程序操作。检查用于查询中的索引是否能很快地排除行。如果不能，那么应该试一下利用STRAIGHT_JOIN 强制按特定次序使用表来完成一个连接。查询的执行方式不那么显然；MySQL可能会有很多理由不以您认为最好的次序使用索引。
■ 测试查询的其他形式，而且不止一次地运行它们。在测试一个查询的其他形式时，应该每种方法运行几次。如果对两个不同方法中的每种只运行查询一次，通常会发现第二个查询更快，因为来自第一个查询的信息在磁盘高速缓存中，不需要实际从磁盘上读出。还应该尽量在系统负载相对平稳的时候运行查询，以避免受系统中其他活动的影响。
4.2.2 忽略优化
这可能听起来有点奇怪，但在以下情况中，要废除MySQL的优化功能：
■ 强迫MySQL慢慢地删除表的内容。在需要完全删空一个表时，利用无WHERE 子句的DELETE 语句删除整个表的内容是最快的，如下所示：
DELETE FROM tb1_name
MySQL对这种特殊情况的DELETE 进行优化；它利用表信息文件中的表说明从头开始创建空数据文件和索引文件。这种优化使DELETE 操作极快，因为MySQL无需单独地删除每一行。但在某些情况下，这样做会产生一些不必要的负作用：
■ MySQL报告所涉及的行数为零，即使表不为空也是如此。很多时候这没有关系（虽然，如果事先没有思想准备，会感到困惑不解），但对于那些确实需要知道真实行数的应用程序来说，这是不恰当的。
■ 如果表含有一个AUTO_INCREMENT 列，则该列的顺序编号会以1从头开始。这是真实的事情，即使在MySQL3.23 中对AUTO_INCREMENT 的处理进行了改进后也是这样。关于这个改进的介绍请参阅第2章中的“使用序列”小节。可增加WHERE 1> 0 子句对DELETE 语句“不优化”。
DELETE FROM tb1_name WHERE 1> 0
这迫使MySQL进行逐行的删除。相应的查询执行要慢得多，但将返回真正删除的行数。它还将保持当前的AUTO_INCREMENT 序列的编号，不过只对MyISAM 表（MySQL3.23 以上的版本可用）有效。而对于ISAM 表，序列仍将重置。
■ 避免更新循环不终止。如果更新一个索引列，如果该列用于WHERE 子句且更新将索引值移入至今尚未出超的取值范围内时，有可能对所更新的行进行不终止的更新。假如表my_tbl 有一个索引了的整数列key _ c o l。下列的查询会产生问题：
相关阅读 更多 +
