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

核心提示： 帮助优化器更好的判断索引的效率，在默认情况下，MySQL查询优化系列讲座之查询优化器(2)，当你把索引列的值与常量进行比较的时候，优化器会假设键值在索引内部是均匀分布的，因为在把str_col中的字符串值转换成数值的时候，可能有很多值等于4（例如’4’、’4.0’和’4th’），在决定进行常量比较是否使用索引的

　　帮助优化器更好的判断索引的效率。在默认情况下，当你把索引列的值与常量进行比较的时候，优化器会假设键值在索引内部是均匀分布的。在决定进行常量比较是否使用索引的时候，优化器会快速地检查索引，估计出会用到多少个实体（entry）。对应MyISAM、InnoDB和BDB数据表来说，你可以使用ANALYZE TABLE让服务器执行对键值的分析。它会为优化器提供更好的信息。

　　使用EXPLAIN验证优化器的操作。EXPLAIN语句可以告诉你是否使用了索引。当你试图用另外的方式编写语句或检查添加索引是否会提高查询执行效率的时候，这些信息对你是有帮助的。

　　在必要的时候给优化器一些提示。正常情况下，MySQL(和PHP搭配之最佳组合)优化器自由地决定扫描数据表的次序来最快地检索数据行。在有些场合中优化器没有作出最佳选择。如果你察觉这种现象发生了，就可以使用STRAIGHT_JOIN关键字来重载优化器的选择。带有STRAIGHT_JOIN的联结类似于交叉联结，但是强迫数据表按照FROM子句中指定的次序来联结。

　　在SELECT语句中有两个地方可以指定STRAIGHT_JOIN。你可以在SELECT关键字和选择列表之间的位置指定，这样会对语句中所有的交叉联结产生影响；你也可以在FROM子句中指定。下面的两个语句功能相同：

SELECT STRAIGHT_JOIN ... FROM t1, t2, t3 ... ; SELECT ... FROM t1 STRAIGHT_JOIN t2 STRAIGHT_JOIN t3 ... ;

　　分别在带有STRAIGHT_JOIN和不带STRAIGHT_JOIN的情况下运行这个查询；MySQL(和PHP搭配之最佳组合)可能因为什么原因没有按照你认为最好的次序使用索引（你可以使用EXPLAIN来检查MySQL(和PHP搭配之最佳组合)处理每个语句的执行计划）。

　　你还可以使用FORCE INDEX、USE INDEX或IGNORE INDEX来指导服务器如何使用索引。

　　利用优化器更加完善的区域。MySQL(和PHP搭配之最佳组合)可以执行联结和子查询，但是子查询是最近才支持的，是在MySQL(和PHP搭配之最佳组合) 4.1中添加的。因而在很多情况下，优化器对联结操作的调整比对子查询的调整要好一些。当你的子查询执行地很慢的时候，这就是一条实际的提示。有一些子查询可以使用逻辑上相等的联结来重新表达。在可行的情况下，你可以把子查询重新改写为联结，看是否执行地快一些。

　　测试查询的备用形式，多次运行。当你测试查询的备用形式的时候（例如，子查询与等同的联结操作对比），每种方式都应该多次运行。如果两种形式都只运行了一次，那么你通常会发现第二个查询比第一个快，这是因为第一个查询得到的信息仍然保留在缓存中，以至于第二个查询没有真正地从磁盘上读取数据。你还应该在系统负载相对平稳的时候运行查询，以避免系统中其它的事务影响结果。

　　避免过度地使用MySQL(和PHP搭配之最佳组合)自动类型转换。MySQL(和PHP搭配之最佳组合)会执行自动的类型转换，但是如果你能够避免这种转换操作，你得到的性能就更好了。例如，如果num_col是整型数据列，那么下面这些查询将返回相同的结果：

SELECT * FROM mytbl WHERE num_col = 4; SELECT * FROM mytbl WHERE num_col = ’4’;

　　但是第二个查询涉及到了类型转换。转换操作本身为了把整型和字符串型转换为双精度型进行比较，使性能恶化了。更严重的情况是，如果num_col是索引的，那么涉及到类型转换的比较操作不会使用索引。

　　相反类型的比较操作（把字符串列与数值比较）也会阻止索引的使用。假设你编写了如下所示的查询：

SELECT * FROM mytbl WHERE str_col = 4;

　　在这个例子中，不会使用str_col上的索引，因为在把str_col中的字符串值转换成数值的时候，可能有很多值等于4（例如’4’、’4.0’和’4th’）。分辨哪些值符合要求的唯一办法是读取每个数据行并执行比较操作。