当然,MySQL查询优化器也利用了索引,但是它也使用了其它一些信息。例如,如果你提交如下所示的查询,那么无论数据表有多大,MySQL执行它的速度都会非常快:
SELECT * FROM tbl_name WHERE 0;
|
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 |
SELECT col3 FROM mytable
WHERE col1 = ’some value’ AND col2 = ’some other value’; |
尽量比较数据类型相同的数据列。当你在比较操作中使用索引数据列的时候,请使用数据类型相同的列。相同的数据类型比不同类型的性能要高一些。例如,INT与BIGINT是不同的。CHAR(10)被认为是CHAR(10)或VARCHAR(10),但是与CHAR(12)或VARCHAR(12)不同。如果你所比较的数据列的类型不同,那么可以使用ALTER TABLE来修改其中一个,使它们的类型相匹配。
尽可能地让索引列在比较表达式中独立。如果你在函数调用或者更复杂的算术表达式条件中使用了某个数据列,MySQL就不会使用索引,因为它必须计算出每个数据行的表达式值。有时候这种情况无法避免,但是很多情况下你可以重新编写一个查询让索引列独立地出现。
下面的WHERE子句显示了这种情况。它们的功能相同,但是对于优化目标来说就有很大差异了:
WHERE mycol < 4 / 2
WHERE mycol * 2 < 4 |
我们看另外一个例子。假设你对date_col列进行了索引。如果你提交一条如下所示的查询,就不会使用这个索引:
SELECT * FROM mytbl WHERE YEAR(date_col) < 1990;
|
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%’
|
WHERE last_name LIKE ’Mac%’
|
WHERE last_name >= ’Mac’ AND last_name < ’Mad’
|
使用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 ... ; |
你还可以使用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’; |
相反类型的比较操作(把字符串列与数值比较)也会阻止索引的使用。假设你编写了如下所示的查询:
SELECT * FROM mytbl WHERE str_col = 4;
|
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) |
mysql> ALTER TABLE member ADD INDEX (expiration);
|
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 |
与此形成对比的是,第三个语句的结果显示,采用这种方式编写的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 | ... |
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 |
这段信息告诉我们,优化器没有为提高执行查询的效率找到任何有用的信息:
· 它将对t1表进行全表扫描。
· 对于t1中的每一行,它将执行t2的全表扫描,使用WHERE子句中的信息识别出合格的行。
行数值显示了优化器估计的每个阶段查询需要检查的行数。T1的估计值是1000,因为1000可以完成全表扫描。相似地,t2的估计值也是1000,但是这个值是对于t1的每一行的。换句话说,优化器所估计的处理该查询所需要检查的数据行组合的数量是1000×1000,也就是一百万。这会造成很大的浪费,因为实际上只有1000个组合符合WHERE子句的条件。
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 |
· 类型从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 |
我们可以运行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 |
这个过程听起来多余,但是有时候你还是希望去掉某些MySQL优化行为的:
重载优化器的表联结次序。使用STRAIGHT_JOIN强迫优化器按照特定的次序使用数据表。在这样操作的时候,你必须对数据表进行排序,这样才能保证第一张表是被选择的行数最少的表。如果你不能确定被选择行数最少的是哪一张表,那么就把行数最多的放到第一的位置。换句话说,试着对表进行排序,使最有约束力的选择出现在最前面。你对可能的备选数据行缩小地越早,执行查询的性能就越好。请确保在带有STRAIGHT_JOIN和不带STRAIGHT_JOIN的时候分别执行该查询。有时候由于某些原因的存在,优化器没有按照你认定的方式联结数据表,STRAIGHT_JOIN也可能没有实际的帮助作用。
另一个可能性是在联结的数据表列表中的某个表的后面使用FORCE INDEX、USE INDEX和IGNORE INDEX调节符来告诉MySQL如何使用索引。这在优化器没有做出正确选择的时候是有用处的。
以最小的代价清空一张表。当需要完全地清空一张MyISAM数据表的时候,最快的方法是删除它并利用它的.frm文件中存储的脚本来重新建立它。使用TRUNCATE TABLE语句实现:
TRUNCATE TABLE tbl_name;
|
但是TRUNCATE TABLE也带来了一些副作用,在某些环境中是不符合要求的:
· TRUNCATE TABLE不一定能够计算出被删除的数据列的精确数量。如果你需要这个数值,请使用不带WHERE子句的DELETE语句:
DELETE FROM tbl_name;
|
DELETE FROM tbl_name WHERE 1;
|