MySQL查询优化程序[MySQL防范]

在公布一个挑选行的查询时, MySQL举行解析,看能否可以对它举行优化,使它履行更快.本文我们将研究查询优化程序怎样工作.更具体的信息,可参阅MySQL参考指南中的"Getting Maximum Performance from 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 返回的信息比这个多,包含将用来扫描表的索引、将要利用的衔接范例以及需求在每个表中扫描的行数预计等等.

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 也较少.遵守下列原则,有助于优化程序操纵索引:

1 对比具有相同范例的列.在对比中操纵索引列时,应当利用那些范例相同的列.比方,CHAR(10) 被视为与CHAR(10) 或VARCHAR(10) 相同,但差别于CHAR(12) 和VARCHAR( 12 ).INT 与BIGINT 差别.在MySQL3.23 版从前,要求利用相同范例的列,不然列上的索引将不起作用.自3.23 版后,不严峻要求这样做,但相同的列范例比差别范例供应更好的性能.假如所对比的两列范例差别,可以利用ALTER TABLE语句改正此中之一使它们的范例相配.

2  对比中应尽大概使索引列独立.假如在函数调用或算术表达式中利用一个列,则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大概会有很多来由不以您认为最好的次序利用索引.

■ 测试查询的其他情势,并且不止一次地运行它们.在测试一个查询的其他情势时,应当每种办法运行几次.假如对两个差别办法中的每种只运行查询一次,普通会发现第二个查询更快,因为来自第一个查询的信息在磁盘高速缓存中,不需求实际从磁盘上读出.还应当尽大概在系统负载相对平稳的时刻运行查询,以避免受系统中其他活动的影响.

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.下列的查询会产生问题:

这个问题的办理办法是在WHERE 子句中将key_col 用于一个表达式,使MySQL不能利用索引:

实际上,还有别的的办法,即进级到MySQL3.23.2 或更高的版本,它们已包办理了这样的问题.

以随机次序检索后果.自MySQL3.23.3 以来,可以利用ORDER BY RAND( ) 随机地对后果举行排序.另一技术对MySQL更旧的版本很有效处,那就是挑选一个随机数列,然后在该列上举行排序.但是,假如按以下编写查询,优化程序将会让您的希望落空:

这里的问题是MySQL认为该列是一个函数调用,将认为呼应的列值是一个常数,而对ORDER BY 子句举行优化,使此查询失效.可在表达式中引用某个表列来蒙骗优化程序.比方,假如表中有一个名为age 的列,可编写以下查询:

忽视优化程序的表衔接次序.可操纵STRIGHT_JOIN 逼迫优化程序以特定的次序利用表.假如这样做,应当规定表的次序,使第一个表为从中挑选的行数最少的表.（假如不能必定哪个表满意这个要求,可将行数最多的表作为第一个表.）换句话说,应尽大概规定表的次序,使最有限制性的挑选先呈现.解除大概的候选行越早,查询履行得就越快.要保证测试呼应的查询两次;大概会有某些缘由使优化程序不以您所想像的方法对表举行衔接,并且STRAIGHT_JOIN 也大概实际上不起作用.