MySQL查询优化系列讲座之查询优化器

　2007-11-11 13:40:45　来源：WEB开发网　　　

核心提示：·一个身份证升位号码查询系统的源码·将查询结果导出到Excel的另一个方法·C#通过查询结果进行分页·五种查询Internet连接状态[含IP]的方·Access的跨库查询·Hibernate对多表关联查询·SQL应用--

　　当你提交一个查询的时候，MySQL(和PHP搭配之最佳组合)会分析它，看是否可以做一些优化使处理该查询的速度更快。这一部分将介绍查询优化器是如何工作的。如果你想知道MySQL(和PHP搭配之最佳组合)采用的优化手段，可以查看MySQL(和PHP搭配之最佳组合)参考手册。

　　当然，MySQL(和PHP搭配之最佳组合)查询优化器也利用了索引，但是它也使用了其它一些信息。例如，如果你提交如下所示的查询，那么无论数据表有多大，MySQL(和PHP搭配之最佳组合)执行它的速度都会非常快：

SELECT * FROM tbl_name WHERE 0;

　　在这个例子中，MySQL(和PHP搭配之最佳组合)查看WHERE子句，认识到没有符合查询条件的数据行，因此根本就不考虑搜索数据表。你可以通过提供一个EXPLAIN语句看到这种情况，这个语句让MySQL(和PHP搭配之最佳组合)显示自己执行的但实际上没有真正地执行的SELECT查询的一些信息。如果要使用EXPLAIN，只需要在EXPLAIN单词放在SELECT语句的前面：

MySQL(和PHP搭配之最佳组合)> EXPLAIN SELECT * FROM tbl_name WHERE 0\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE

　　通常情况下，EXPLAIN返回的信息比上面的信息要多一些，还包括用于扫描数据表的索引、使用的联结类型、每张数据表中估计需要检查的数据行数量等非空（NULL）信息。

　　优化器是如何工作的

　　MySQL(和PHP搭配之最佳组合)查询优化器有几个目标，但是其中最主要的目标是尽可能地使用索引，并且使用最严格的索引来消除尽可能多的数据行。你的最终目标是提交SELECT语句查找数据行，而不是排除数据行。优化器试图排除数据行的原因在于它排除数据行的速度越快，那么找到与条件匹配的数据行也就越快。如果能够首先进行最严格的测试，查询就可以执行地更快。假设你的查询检验了两个数据列，每个列上都有索引：

SELECT col3 FROM mytable
WHERE col1 = ’some value’ AND col2 = ’some other value’;

　　假设col1上的测试匹配了900个数据行，col2上的测试匹配了300个数据行，而同时进行的测试只得到了30个数据行。先测试Col1会有900个数据行，需要检查它们找到其中的30个与col2中的值匹配记录，其中就有870次是失败了。先测试col2会有300个数据行，需要检查它们找到其中的30个与col1中的值匹配的记录，只有270次是失败的，因此需要的计算和磁盘I/O更少。其结果是，优化器会先测试col2，因为这样做开销更小。

　　你可以通过下面一个指导帮助优化器更好地利用索引：

　　尽量比较数据类型相同的数据列。当你在比较操作中使用索引数据列的时候，请使用数据类型相同的列。相同的数据类型比不同类型的性能要高一些。例如，INT与BIGINT是不同的。CHAR(10)被认为是CHAR(10)或VARCHAR(10)，但是与CHAR(12)或VARCHAR(12)不同。如果你所比较的数据列的类型不同，那么可以使用ALTER TABLE来修改其中一个，使它们的类型相匹配。

　　尽可能地让索引列在比较表达式中独立。如果你在函数调用或者更复杂的算术表达式条件中使用了某个数据列，MySQL(和PHP搭配之最佳组合)就不会使用索引，因为它必须计算出每个数据行的表达式值。有时候这种情况无法避免，但是很多情况下你可以重新编写一个查询让索引列独立地出现。

　　下面的WHERE子句显示了这种情况。它们的功能相同，但是对于优化目标来说就有很大差异了：

WHERE mycol < 4 / 2
WHERE mycol * 2 < 4

　　对于第一行，优化器把表达式4/2简化为2，接着使用mycol上的索引来快速地查找小于2的值。对于第二个表达式，MySQL(和PHP搭配之最佳组合)必须检索出每个数据行的mycol值，乘以2，接着把结果与4进行比较。在这种情况下，不会使用索引。数据列中的每个值都必须被检索到，这样才能计算出比较表达式左边的值。

　　我们看另外一个例子。假设你对date_col列进行了索引。如果你提交一条如下所示的查询，就不会使用这个索引：

SELECT * FROM mytbl WHERE YEAR(date_col) < 1990;

　　这个表达式不会把1990与索引列进行比较；它会把1990与该数据列计算出来的值比较，而每个数据行都必须计算出这个值。其结果是，没有使用date_col上的索引，因为执行这样的查询需要全表扫描。怎么解决这个问题呢？只需要使用文本日期，接着就可以使用date_col上的索引来查找列中匹配的值了：

WHERE date_col < ’1990-01-01’

　　但是，假设你没有特定的日期。你可能希望找到一些与今天相隔固定的几天的日期的记录。表达这种类型的比较有很多种方法--它们的效率并不同。下面就有三种：

WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < cutoff
WHERE TO_DAYS(date_col) < cutoff + TO_DAYS(CURDATE())
WHERE date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY)

　　对于第一行，不会用到索引，因为每个数据行都必须检索以计算出TO_DAYS(date_col)的值。第二行要好一些。Cutoff和TO_DAYS(CURDATE())都是常量，因此在处理查询之前，比较表达式的右边可以被优化器一次性计算出来，而不需要每个数据行都计算一次。但是date_col列仍然出现在函数调用中，它阻止了索引的使用。第三行是这几个中最好的。同样，在执行查询之前，比较表达式的右边可以作为常量一次性计算出来，但是现在它的值是一个日期。这个值可以直接与date_col值进行比较，再也不需要转换成天数了。在这种情况下，会使用索引。

　　在LIKE模式的开头不要使用通配符。有些字符串搜索使用如下所示的WHERE子句：

WHERE col_name LIKE ’%string%’

　　如果你希望找到那些出现在数据列的任何位置的字符串，这个语句就是对的。但是不要因为习惯而简单地把"%"放在字符串的两边。如果你在查找出现在数据列开头的字符串，就删掉前面的"%"。假设你要查找那些类似MacGregor或MacDougall等以"Mac"开头的名字。在这种情况下，WHERE子句如下所示：

WHERE last_name LIKE ’Mac%’

　　优化器查看该模式中词首的文本，并使用索引找到那些与下面的表达式匹配的数据行。下面的表达式是使用last_name索引的另一种形式：

WHERE last_name >= ’Mac’ AND last_name < ’Mad’

　　这种优化不能应用于使用了REGEXP操作符的模式匹配。REGEXP表达式永远不会被优化。

1 2 3 4 5 下一页