MySQL 5.0 数据库的新特点的存储历程[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表达式永久不会被优化.

帮忙优化器更好的判断索引的效率.在默许情形下,当你把索引列的值与常量举行对比的时刻,优化器会假定键值在索引内部是均匀分布的.在决意举行常量对比能否利用索引的时刻,优化器会快速地查抄索引,预计出会用到多少个实体（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是索引的,那么触及到范例转换的对比操作不会利用索引.