- 磁盘搜索。需要花时间从磁盘上找到一个数据,用在现代磁盘的平均时间通常小于10ms,因此理论上我们能够每秒大约搜索1000次。这个时间在新磁盘上提高不大并且很难为一个表进行优化。优化它的方法是将数据分布在多个磁盘上。
- 磁盘读/写。当磁盘放入正确位置后,我们需要从中读取数据。对于现代的磁盘,一个磁盘至少传输10-20Mb/s的吞吐。这比搜索要容易优化,因为你能从多个磁盘并行地读。
- CPU周期。我们将数据读入内存后,需要对它进行处理以获得我们需要的结果。表相对于内存较小是最常见的限制因素。但是对于小表,速度通常不成问题。
为了帮助MySQL更好地优化查询,在一个装载数据后的表上运行ANALYZE TABLE或myisamchk --analyze。这样为每一个索引更新指出有相同值的行的平均行数的值(当然,如果只有一个索引,这总是1。)MySQL使用该方法来决定当你联接两个基于非常量表达式的表时选择哪个索引。你可以使用SHOW INDEX FROM tbl_name并检查Cardinality值来检查表分析结果。myisamchk --description --verbose可以显示索引分布信息。
· 要想根据一个索引排序一个索引和数据,使用myisamchk --sort-index --sort-records=1(如果你想要在索引1上排序)。如果只有一个索引,想要根据该索引的次序读取所有的记录,这是使查询更快的一个好方法。但是请注意,第一次对一个大表按照这种方法排序时将花很长时间!
- 对于MyISAM和HEAP表,在一个单个表上的没有一个WHERE的COUNT(*)直接从表中检索信息。当仅使用一个表时,对NOT NULL表达式也这样做。
- 无效常数表达式的早期检测。MySQL快速检测某些SELECT语句是不可能的并且不返回行。
- 如果不使用GROUP BY或分组函数(COUNT()、MIN()……),HAVING与WHERE合并。
- 对于联接内的每个表,构造一个更简单的WHERE以便更快地对表进行WHERE计算并且也尽快跳过记录。
- 所有常数的表在查询中比其它表先读出。常数表为:
- 空表或只有1行的表。
- 与在一个PRIMARY KEY或UNIQUE索引的WHERE子句一起使用的表,这里所有的索引部分使用常数表达式并且索引部分被定义为NOT NULL。
· 对于BTREE和HASH索引,当使用=、<=>、IN、IS NULL或者IS NOT NULL操作符时,关键元素与常量值的比较关系对应一个范围条件。
· 对于BTREE索引,当使用>、<、>=、<=、BETWEEN、!=或者<>,或者LIKE 'pattern'(其中'pattern'不以通配符开始)操作符时,关键元素与常量值的比较关系对应一个范围条件。
· 对于所有类型的索引,多个范围条件结合OR或AND则产生一个范围条件。
例子 SELECT * FROM t1
WHERE key_col > 1
AND key_col < 10;(OR key_col IN (15,18,20); OR key_col BETWEEN 'bar' AND 'foo';)
总的来说(如前面的例子所述),用于范围扫描的条件比WHERE子句限制少。MySQL再执行检查以过滤掉满足范围条件但不完全满足WHERE子句的行。
范围条件提取算法可以处理嵌套的任意深度的AND/OR结构,并且其输出不依赖条件在WHERE子句中出现的顺序。
(2)多元素索引的范围访问方法
多元素索引的范围条件是单元素索引的范围条件的扩展。多元素索引的范围条件将索引记录限制到一个或几个关键元组内。使用索引的顺序,通过一系列关键元组来定义关键元组区间。
· 交集
· 联合
· 排序并集
(1)索引合并交集访问算法
该访问算法可以用于当WHERE子句结合AND被转换为不同的关键字的几个范围条件,每个条件为下面之一:
· 以这种形式,即索引有确切的N部分(即包括了所有索引部分):
· key_part1=const1 AND key_part2=const2 ... AND key_partN=constN
· 任何InnoDB或BDB表的主键的范围条件。
该访问算法可以用于当WHERE子句结合OR被转换为不同的关键字的几个范围条件,但索引合并方法联合算法并不适用的时候。
下面是一些例子:
SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20;
SELECT * FROM tbl_name
WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col=30;
排序联合算法和联合算法的区别是排序联合算法必须先索取所有记录的行ID,然后在返回记录前对它们进行排序。
在大多数情况下,DISTINCT子句可以视为GROUP BY的特殊情况。例如,下面的两个查询是等效的:
SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 > const;
SELECT c1, c2, c3 FROM t1 WHERE c1 > const GROUP BY c1, c2, c3;
由于这个等效性,适用于GROUP BY查询的优化也适用于有DISTINCT子句的查询
SELECT * FROM t1
ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1
WHERE key_part1=constant
ORDER BY key_part2;
SELECT * FROM t1
ORDER BY key_part1 DESC, key_part2 DESC;
文件排序优化不仅用于记录排序关键字和行的位置,并且还记录查询需要的列。这样可以避免两次读取行。文件排序算法的工作象这样:
1. 读行匹配WHERE子句的行,如前面所示。
2. 对于每个行,记录构成排序关键字和行位置的一系列值,并且记录查询需要的列。
3. 根据排序关键字排序元组
4. 按排序的顺序检索行,但直接从排序的元组读取需要的列,而不是再一次访问表。
如果想要增加ORDER BY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能,可以尝试下面的策略:
· 增加sort_buffer_size变量的大小。
· 增加read_rnd_buffer_size变量的大小。
· 更改tmpdir指向具有大量空闲空间的专用文件系统。该选项接受几个使用round-robin(循环)模式的路径。