日期：2011-05-02 15:44:00 来源：本站整理
MySQL查询优化系列讲座之查询优化器[MySQL防范]
本文“MySQL查询优化系列讲座之查询优化器[MySQL防范]”是由七道奇为您精心收集，来源于网络转载，文章版权归文章作者所有，本站不对其观点以及内容做任何评价，请读者自行判断，以下是其具体内容：
当你提交一个查询的时刻,MySQL会解析它,看能否可以做一些优化使处理该查询的速度更快.这一部份将介绍查询优化器是若何工作的.假如你想知道MySQL采取的优化手段,可以查看MySQL参考手册.
当然,MySQL查询优化器也操纵了索引,但是它也利用了别的一些信息.比方,假如你提交以下所示的查询,那么无论数据表有多大,MySQL履行它的速度城市非常快:
| SELECT * FROM tbl_name WHERE 0; |
在这个例子中,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）信息.
优化器是若何工作的
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,因为这样做开销更小.
你可以通过下面一个指导帮忙优化器更好地操纵索引:
尽大概对比数据范例相同的数据列.当你在对比操作中利用索引数据列的时刻,请利用数据范例相同的列.相同的数据范例比差别范例的性能要高一些.比方,INT与BIGINT是差别的.CHAR(10)被认为是CHAR(10)或VARCHAR(10),但是与CHAR(12)或VARCHAR(12)差别.假如你所对比的数据列的范例差别,那么可以利用ALTER TABLE来改正此中一个,使它们的范例相匹配.
尽大概地让索引列在对比表达式中独立.假如你在函数调用大概更复杂的算术表达式条件中利用了某个数据列,MySQL就不会利用索引,因为它必须计算出每个数据行的表达式值.有时刻这种情形无法避免,但是很多情形下你可以重新编写一个查询让索引列独立地呈现.
下面的WHERE子句显示了这种情形.它们的功效相同,但是关于优化目标来说就有很大差别了:
| WHERE mycol < 4 / 2 WHERE mycol * 2 < 4 |
关于第一行,优化器把表达式4/2简化为2,接着利用mycol上的索引来快速地查找小于2的值.关于第二个表达式,MySQL必须检索出每个数据行的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] 下一页
帮忙优化器更好的判断索引的效率.在默许情形下,当你把索引列的值与常量举行对比的时刻,优化器会假定键值在索引内部是均匀分布的.在决意举行常量对比能否利用索引的时刻,优化器会快速地查抄索引,预计出会用到多少个实体（entry）.对应MyISAM、InnoDB和BDB数据表来说,你可以利用ANALYZE TABLE让服务器履行对键值的解析.它会为优化器供应更好的信息.
利用EXPLAIN考证优化器的操作.EXPLAIN语句可以奉告你能否利用了索引.当你试图用别的的方法编写语句或查抄增添索引能否会提高查询履行效率的时刻,这些信息对你是有帮忙的.
在必要的时刻给优化器一些提醒.正常情形下,MySQL优化器安闲地决意扫描数据表的次序来最快地检索数据行.在有些场所中优化器没有作出最佳挑选.假如你发觉这种现象发生了,便可以利用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大概因为什么缘由没有按照你认为最好的次序利用索引（你可以利用EXPLAIN来查抄MySQL处理每个语句的履行筹划）.
你还可以利用FORCE INDEX、USE INDEX或IGNORE INDEX来指导服务器若何利用索引.
操纵优化器越发完善的区域.MySQL可以履行联合和子查询,但是子查询是近来才支持的,是在MySQL 4.1中增添的.因而在很多情形下,优化器春联合操作的调整比对子查询的调整要好一些.当你的子查询履行地很慢的时刻,这就是一条实际的提醒.有一些子查询可以利用逻辑上相等的联合来重新表达.在可行的情形下,你可以把子查询重新改写为联合,看能否履行地快一些.
测试查询的备用情势,多次运行.当你测试查询的备用情势的时刻（比方,子查询与平等的联合操作比较）,每种方法都应当多次运行.假如两种情势都只运行了一次,那么你普通会发现第二个查询比第一个快,这是因为第一个查询得到的信息仍旧保存在缓存中,以至于第二个查询没有真正地从磁盘上读取数据.你还应当在系统负载相对平稳的时刻运行查询,以避免系统中别的的事件影响后果.
避免过度地利用MySQL自动范例转换.MySQL会履行自动的范例转换,但是假如你可以避免这种转换操作,你得到的性能就更好了.比方,假如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’）.辨别哪些值符合要求的唯一办法是读取每个数据行并履行对比操作.
上一页 [1] [2] [3] [4] [5] 下一页
利用EXPLAIN来查抄优化器的操作
EXPLAIN关于理解优化器生成的、用于处理语句的履行筹划的内部信息是很有帮忙的.在这一部份中,我们将注释EXPLAIN的两种用处:
· 查看采取差别的方法编写的查询能否影响了索引的利用.
· 查看向数据表增添索引对优化器生成高效率履行筹划的本领的影响.
这一部份只谈论与示例相关的EXPLAIN输入字段.
前面,在"优化器是若何工作的"部份中我们得出的概念是,你编写表达式的方法将决意优化器能否能利用可用的索引.分外是上面的谈论利用了下面三个逻辑相等的WHERE子句的例子,只有第三个答应利用索引:
| 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) |
EXPLAIN答应你查看编写表达式的某种方法能否比别的的方法好一些.为了看到后果,让我们辨别用这三个WHERE子句搜索成员表中过期的数据列值,把cutoff值设为30天.为了看到索引的利用和表达式编写方法之间的关系,我们首先对expiration摆列行索引:
| mysql> ALTER TABLE member ADD INDEX (expiration); |
接着在每个表达式情势上利用EXPLAIN,看优化器生成了什么样的履行筹划:
| mysql> EXPLAIN SELECT * FROM MEMBER -> WHERE TO_DAYS(expiration) - TO_DAYS(CURDATE()) < 30\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: MEMBER type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 102 Extra: Using where mysql> EXPLAIN SELECT * FROM MEMBER -> WHERE TO_DAYS(expiration) < 30 + TO_DAYS(CURDATE())\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: MEMBER type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 102 Extra: Using where mysql> EXPLAIN SELECT * FROM MEMBER -> WHERE expiration < DATE_ADD(CURDATE(), INTERVAL 30 DAY)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: MEMBER type: range possible_keys: expiration key: expiration key_len: 4 ref: NULL rows: 6 Extra: Using where |
上面的后果显示,前面两个语句没有利用索引.范例（type）值表明了将若何从数据表中读失信息.ALL意味着"将查抄全部的记录".也就是说,它会履行全表扫描,没有操纵索引.每个与键相关的列都是NULL也表明没有利用索引.
与此形成比较的是,第三个语句的后果显示,采取这种方法编写的WHERE子句,优化器可以利用expiration列上的索引:
· 范例（type）值表明它可以利用索引来搜索特定范围的值（小于右边表达式给定的值）.
· 大概键（possible_keys）和键（key）值显示expiration上的索引已经被考虑作为备选索引,并且它也是真正利用的索引.
· 行数（rows）值显示优化器预计自己需求查抄6个数据行来处理该查询.这比前面两个履行筹划的102小很多.
EXPLAIN的第二种用处是查看增添索引能否能帮忙优化器更高效率地履行语句.我将利用两个未被索引的数据表.它充足显示成立索引的效率.相同的法则可以利用于触及多表的越发复杂的联合操作.
假定我们有两个数据表t1和t2,每个有1000行,包含的值从1到1000.下面的查询查找出两个表中值相同的数据行:
| mysql> SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2; +------+------+ | i1 | i2 | +------+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | ... |
两个表都没有索引的时刻,EXPLAIN产生下面的后果:
| mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 Extra: Using where |
范例列中的ALL表明要举行查抄全部数据行的全表扫描.大概键列中的NULL表明没有找到用于提高查询速度的备选索引（键、键长度和参考列都是NULL也是因为贫乏符合的索引）.Using where表明利用WHERE子句中的信息来辨认合格的数据行.
这段信息奉告我们,优化器没有为提高履行查询的效率找到任何有效的信息:
· 它将对t1表举行全表扫描.
· 关于t1中的每一行,它将履行t2的全表扫描,利用WHERE子句中的信息辨认出合格的行.
行数值显示了优化器预计的每个阶段查询需求查抄的行数.T1的预计值是1000,因为1000可以完成全表扫描.类似地,t2的预计值也是1000,但是这个值是关于t1的每一行的.换句话说,优化器所预计的处理该查询所需求查抄的数据行组合的数目是1000×1000,也就是一百万.这会造成很大的浪费,因为实际上只有1000个组合符合WHERE子句的条件.
上一页 [1] [2] [3] [4] [5] 下一页
为了使这个查询的效率更高,给此中一个联合列增添索引并重新履行EXPLAIN语句:
| mysql> ALTER TABLE t2 ADD INDEX (i2); mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: i2 key: i2 key_len: 5 ref: sampdb.t1.i1 rows: 10 Extra: Using where; Using index |
我们可以看到性能提高了.T1的输出没有改变（表明还是需求举行全表扫描）,但是优化器处理t2的方法就有所差别了:
· 范例从ALL改变成ref,意味着可以利用参考值（来自t1的值）来履行索引查找,定位t2中合格的数据行.
· 参考值在参考（ref）字段中给出了:sampdb.t1.i1.
· 行数值从1000降低到了10,显示出优化器相信关于t1中的每一行,它只需求查抄t2中的10行（这是一个悲观的预计值.实际上,在t2中只有一行与t1中数据行匹配.我们在背面会看到若何帮忙优化器改进这个预计值）.数据行组合的全部预计值使1000×10＝10000.它比前面的没有索引的时刻预计出来的一百万好多了.
对t1举行索引有代价吗?实际上,关于这个特定的联合操作,扫描一张表是必要的,因此没有必要对t1成立索引.假如你想看到效果,可以索引t1.i1并再次运行EXPLAIN:
| mysql> ALTER TABLE t1 ADD INDEX (i1); mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: index possible_keys: i1 key: i1 key_len: 5 ref: NULL rows: 1000 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: i2 key: i2 key_len: 5 ref: sampdb.t1.i1 rows: 10 Extra: Using where; Using index |
上面的输出与前面的EXPLAIN的输出类似,但是增添索引对t1的输出有一些改变.范例从NULL改成了index,附加（Extra）从空的改成了Using index.这些改变表明,固然对索引的值仍旧需求履行全表扫描,但是优化器还是可以直接从索引文件中读取值,按照不需求利用数据文件.你可以从MyISAM表中看到这类后果,在这种情形下,优化器知道自己只询问索引文件就可以够得到全部需求的信息.关于InnoDB 和BDB表也有这样的后果,在这种情形下优化器可以单独利用索引中的信息而不用搜索数据行.
我们可以运行ANALYZE TABLE使优化器进一步优化预计值.这会惹起服务器生成键值的静态分布.解析上面的表并再次运行EXPLAIN得到了更好的预计值:
| mysql> ANALYZE TABLE t1, t2; mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: index possible_keys: i1 key: i1 key_len: 5 ref: NULL rows: 1000 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: i2 key: i2 key_len: 5 ref: sampdb.t1.i1 rows: 1 Extra: Using where; Using index |
在这种情形下,优化器预计在t2中与t1的每个值匹配的数据行只有一个.
上一页 [1] [2] [3] [4] [5] 下一页
重载优化历程
这个历程听起来多余,但是有时刻你还是但愿去掉某些MySQL优化行为的:
重载优化器的表联合次序.利用STRAIGHT_JOIN逼迫优化器按照特定的次序利用数据表.在这样操作的时刻,你必须对数据表举行排序,这样才能保证第一张表是被挑选的行数最少的表.假如你不能肯定被挑选行数最少的是哪一张表,那么就把行数最多的放到第一的位置.换句话说,试着对表举行排序,使最有约束力的挑选呈目前最前面.你对大概的备选数据行缩小地越早,履行查询的性能就越好.请确保在带有STRAIGHT_JOIN和不带STRAIGHT_JOIN的时刻辨别履行该查询.有时刻由于某些缘由的存在,优化器没有按照你认定的方法联合数据表,STRAIGHT_JOIN也大概没有实际的帮忙作用.
另一个大概性是在联合的数据表列表中的某个表的背面利用FORCE INDEX、USE INDEX和IGNORE INDEX疗养符来奉告MySQL若何利用索引.这在优化器没有做出精确挑选的时刻是有效处的.
以最小的代价清空一张表.当需求完好地清空一张MyISAM数据表的时刻,最快的办法是删除它并操纵它的.frm文件中存储的脚本来重新成立它.利用TRUNCATE TABLE语句实现:
| TRUNCATE TABLE tbl_name; |
通太重新成立MyISAM数据表来清空它的这种服务器优化办法使该操作非常快,因为不需求单独地逐行删除.
但是TRUNCATE TABLE也带来了一些副作用,在某些环境中是不符合要求的:
· TRUNCATE TABLE不一定可以计算出被删除的数据列的切确数目.假如你需求这个数值,请利用不带WHERE子句的DELETE语句:
| DELETE FROM tbl_name; |
· 但是,通太重新成立来清空数据表,它大概会把序号的起始值设置为1.为了避免这种情形,请利用"不优化的"全表DELETE语句,它带有一个恒为真的WHERE子句:
| DELETE FROM tbl_name WHERE 1; |
增添WHERE子句会逼迫MySQL举行逐行删除,因为它必须计算出每一行的值来判断能否可以删除它.这个语句履行的速度很慢,但是它却保存了当前的AUTO_INCREMENT序号.
上一页 [1] [2] [3] [4] [5]
| 本文地址： | 与您的QQ/BBS好友分享! |
- 好的评价 如果您觉得此文章好，就请您
0%(0) 
- 差的评价 如果您觉得此文章差，就请您
0%(0) 
中查找“MySQL查询优化系列讲座之查询优化器”更多相关内容
中查找“MySQL查询优化系列讲座之查询优化器”更多相关内容评论内容只代表网友观点，与本站立场无关！
评论摘要(共 0 条，得分 0 分，平均 0 分)
查看完整评论