一个查询执行的不是很理想,大部分原因都是由于数据量过大。很多查询都筛选了大量的数据并且并没有什么作用。其实大部分不好的语句都可以访问更少的数据。我们可以通过两个步骤来分析性能差的查询语句。
- 找出你的应用程序所获取的数据是否超出了你的需求。意思就是它访问了过多的数据,但是它也可能访问了过多的列。
- 找出MySQL服务器是否分析了过多的行。
从数据库获得的数据是否超出了你的需要?
-> INNER JOIN sakila.film_actor USING(actor_id)
-> INNER JOIN sakila.film USING(film_id)
-> WHERE sakila.film.title = 'Academy Dinosaur';
MySQL是否检查了过多的数据?
一旦你确定了你的查询获取了所需的数据,你就可以查看查询是否检查了太多的数据。在MySQL中,最简单的消耗指标是:
- 执行时间
- 所检查的行数
- 返回的行数
没有一个指标可以完美的衡量查询的消耗。但是它们能反映出MySQL执行一个查询所访问的数据并且能大约的推算出查询运行的速度。这三个指标都记录在慢查询日志中,因此要想知道是否检查了过多的数据,查看慢查询日志是最好的方法。
执行时间
在第二章我们已经讨论过了,在MySQL5.0以及之前的版本中,慢查询日志有很多的限制。包括了缺乏更细颗粒度的日志。
幸运的是,有许多补丁可以使你记录和测量微秒级别的查询。它们都包含在了MySQL5.1服务器之中了,但是如果你用的是老版本的话,只能打补丁了。要小心的是,不要过度的看重执行时间。看这个指标的原因是它是一个目标的指标,但是在变化的条件下它并不是一直不变的。其他的因素-比如存储引擎的锁,高并发,并且还有硬件-都可能影响到执行的时间。这个指标非常有助于f发现那些对应用响应时间或者服务器取读取的影响的查询语句,但是它不能给出实际的执行时间。
检查和返回的行
当分析查询语句时,考虑检查的行数是非常有用的。因为你能查看查询是怎样找到你所需要的数据的。然而,就像执行时间,这个指标来发现不好的语句并不是很完美。并不是所有的行的访问是相同的。短的行访问时间很快,并且从内存中获取行要比从硬盘中更快。
理想的情况下,所检查的行数和返回的行数相同,但是在实际中并不太可能。举个例子,当join构建的行时候,很多行一定被访问用来在结果集中生成行。检查和返回行的比例一般来说都很小,在1:1和10:1之间,但是有的时候也可能会很大。
行的检查和访问类型
当你思考一个查询的消耗,要考虑在一个表中查找一个单独行的消耗。MySQL会使用许多访问方法来找到和返回一行。一些查询检查很多的行,但是其他的可能没有检查任何行就会生成结果。
这个访问类型在EXPLAIN输出的TYPE列中显示。这个访问类型从全表扫描(full table scan)到,索引扫描(index scans),范围扫描(range scans),唯一索引查找(unique index lookups),以及常量(constants)。它们的速度是依次递增的,因为它需要访问很少的数据。你不比记下这些类型,但是你应该明白扫描表,扫描一个索引,范围访问,以及单值访问的基本概念。
如果语句的访问类型不好,最佳的方法就是添加一个合适的索引。我们已经在前一章讨论了索引。现在你应该明白为什么索引对于查询优化如此重要了吧。索引可以让MySQL更高效的找到所需的行并且检查更少的数据。
让我们看看Sakila数据库的例子:
mysql> SELECT * FROM sakila.film_actor WHERE film_id = 1;
这个查询会返回10行,并且EXPLAIN显示了MySQL使用了ref访问类型。
mysql> EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: const
rows: 10
Extra:
EXPLAIN显示了MySQL仅仅要访问10行。换句话说,查询优化器知道选择合适的访问类型来满足这个高效的查询。如果没有合适的索引会怎样呢。MySQL可能会使用一个性能差点的访问类型。让我们先删除索引,再执行这个查询。
mysql> ALTER TABLE sakila.film_actor DROP FOREIGN KEY fk_film_actor_film;
mysql> ALTER TABLE sakila.film_actor DROP KEY idx_fk_film_id;
mysql> EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5073
Extra: Using where
和预想的一样,访问类型已经变为全表扫描了(All),并且MySQL检查了5073行来满足这个查询。“Using Where”的意思是,在存储引擎读取行之后,MySQL服务器使用了WHERE条件了过滤行。
一般情况下,MySQL以三种方式来应用一个WHERE,顺序为最佳到最差。
- 在索引查找操作上来应用条件,便于去掉不匹配的行。这个操作在存储引擎一层。
- 使用一个覆盖索引(在Extra列显示为“using index”)来避免行的访问,并且在从索引获取结果之后,再过滤掉不匹配的行。这发生在服务器层,但是它不需要从表中读取行。
- 从表中获取行,之后过滤掉不符合的行。(Extra为“Using Where”)。这发生在服务器层,并且需要服务器在过滤结果之前从表中读取行。
- 使用覆盖索引,它们存储这你要的数据,因此存储引擎就没必获取全部的行。
- 改变数据库模型。一个例子就是使用汇总表。
- 重写这个复杂的查询,因此MySQL优化器能更好的进行优化。(以后会详细讨论)