MySQL查询优化程序

剑心通明

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 = "Mac" AND last_name  0 子句对DELETE 语句“不优化”。
    DELETE FROM tb1_name WHERE 1> 0
    这迫使MySQL进行逐行的删除。相应的查询执行要慢得多，但将返回真正删除的行数。它还将保持当前的AUTO_INCREMENT 序列的编号，不过只对MyISAM 表（MySQL3.23 以上的版本可用）有效。而对于ISAM 表，序列仍将重置。
    ■ 避免更新循环不终止。如果更新一个索引列，如果该列用于WHERE 子句且更新将索引值移入至今尚未出超的取值范围内时，有可能对所更新的行进行不终止的更新。假如表my_tbl 有一个索引了的整数列key _ c o l。下列的查询会产生问题：


本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u/4206/showart_716000.html