MySQL查询优化之查询优化器phpma.com

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

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

SELECT * FROM tbl_name WHERE 0; phpma.com

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

mysql> 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）信息。

　　优化器是如何工作的phpma.com

　　MySQL查询优化器有几个目标，但是其中最主要的目标是尽可能地使用索引，并且使用最严格的索引来消除尽可能多的数据行。你的最终目标是提交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，因为这样做开销更小。phpma.com

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

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

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

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

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

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

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

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

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