MySQL查询性能优化

查询性能优化

为什么查询速度会慢？

如果把查询看作是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数。

通常来说，查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后再服务器上进行解析，生成执行计划，执行，返回结果给客户端。其中“执行”是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。

慢查询基础：优化数据访问

查询性能低下最基本的原因是访问的数据太多。大部分性能低下的查询都可以通过 减少访问的数据量 的方式进行优化。对于低效的查询，可以通过下面两个步骤来分析：

1. 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。
2. 确认MySQL服务器层是否在分析大量超过需要的数据行。

是否向数据库请求了不需要的数据

有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃。这会给MySQL服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的CPU和内存资源。

典型案例：

查询不需要的记录：

一些人会先使用SELECT语句查询大量的结果，然后获取前面的N行后关闭结果集（例如取出100条记录只在页面显示前10条）。他们任务MySQL会执行查询，并只返回他们需要的10条数据，然后停止查询。实际上MySQL会查出全部的结果集，应用程序会接受全部的数据，然后抛弃大部分数据。解决方法就是在这样的查询后面加上 LIMIT。

多表关联时返回全部列：

如果查询所有在电影 Academy Dinosaur 中出现的电影，千万不要这样编写查询：

SELECT * FROM actor
INNER JOIN film_actor USING(actor_id)
INNER JOIN film USING(film_id)
WHERE film.title = 'Academy Dinosaur';

这将返回这三个表的全部数据列。正确的方式应该只取需要的列

SELECT actor.* FROM actor...;

总是取出全部列

**取出全部列会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O、内存和CPU的消耗。**因此应该禁止 SELECT * 的写法，这样做有时候还能避免某些列被修改带来的问题。

不过，查询返回超过需要的数据也不总是坏事。这种有点浪费数据库资源的方式可以简化开发，因为能提高相同代码片段的复用性。如果应用程序使用了某种缓存机制，或是有其他考虑，获取超过需要的数据也有可能有其好处，但不要忘记这样做的代价时什么。获取并缓存所有列的查询，相比多个独立的只获取部分列的查询可能就更有好处。

重复查询相同的数据

不断地重复执行相同的查询，然后每次都返回完全相同的数据。例如，在用户评论的地方需要查询用户头像的URL，那么用户多次评论的时候，可能就会反复查询这个数据。解决方案是：**当初次查询时将这个数据缓存起来，需要的时候从缓存中取出，这样性能显然会更好。

MySQL是否在扫描额外的记录

在确定查询只返回需要的数据以后，接下来应该看看查询为了返回结果是否扫描了过多的数据。对于MySQL，最简单的衡量查询开销的三个指标如下：

• 响应时间
• 扫描的行数
• 返回的行数

这三个指标都会记录到MySQL的慢日志中，所以检查慢日志记录时找出扫描行数过多的查询的好办法。

响应时间

响应时间 = 服务时间 + 排队时间。

服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间——可能是等I/O操作完成，也可能是等待行锁，等等。一般最常见和重要的等待是I/O和锁等待。

扫描的行数和访问类型

MySQL有好几种访问方式可以查找并返回一行结果，有些访问方式可能需要扫描很多行才能返回一行结果，也有些访问方式可能无需扫描就能返回结果。

访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。

如果查询没有办法找到合适的访问类型，那么解决的最好办法通常是增加一个合适的索引。索引让MySQL以最高效、扫描行数最少的方式找到需要的记录。

一般MySQL能够使用如下三种方式应用WHERE条件，从好到坏依次为：

• **在索引中使用WHERE条件来过滤不匹配的记录。**这是在存储引擎层完成的。
• 使用索引覆盖扫描来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须再回表查询记录。
• 从数据表中返回数据，然后过滤不满足条件的记录。这在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤。

如果发现查询需要扫描大量的数据但只返回少数的行，可以尝试下面的技巧来优化它：

• 使用索引覆盖扫描，把所有需要的列都放在索引中，这样存储引擎无须回表获取对应行就可以返回结果了。
• 改变库表结构。例如使用单独的汇总表。
• 重写这个复杂的查询，然MySQL优化器能够以更优化的方式执行这个查询。

重构查询的方式

一个复杂查询还是多个简单查询

切分查询

有时候对于一个大查询我们需要“分而治之”，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一部分查询结果。

分解关联查询

可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。例如：

SELECT * FROM tag
	JOIN tag_post ON tag_post.tag_id = tag.id
	JOIN post ON tag_post.post_id = post.id
WHERE tag.tag = 'mysql';

可以分解为以下查询来代替：

SELECT * FROM tag WHERE tag = 'mysql';
SELECT * FROM tag_post WHERE tag_id = 1234;
SELECT * FROM post WHERE post.id in (123,456,789,9098,8904);

这样做的返回结果一模一样。用分解关联查询的方式重构查询有如下的优势：

• 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应地结果对象。
• 将查询分解后，执行单个查询可以减少锁的竞争。
• 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
• 查询本身效率也可能会有所提升。
• 可以减少冗余记录的查询。

使用场景：如当应用能够方便地缓存单个查询的结果的时候、当可以将数据库分布到不同的MySQL服务器上的时候、当能够使用IN()的方式代替关联查询的时候、当查询中使用同一个数据表的时候。