转载请备注来源: 《MySQL性能优化学习笔记-(3)查询优化》 | shuwoom.com

一、前言

前面我们介绍了如何设计最优的库表结构如何建立最好的索引,这些对于高性能来说必不可少。但这还不够—还需要合理的查询设计。

查询优化、索引优化、库表结构优化需要齐头并进,一个不落。

这一部分内容主要是帮助大家更加深刻理解mysql如何真正地执行查询,并明白高效和低效的原因何在,这样才能充分发挥mysql的优势,并避开它的弱点。

二、慢查询基础:优化数据访问

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

  1. 确认应用是否在检索大量超过需要的数据,这通常意味着访问了太多的行,但有时又也可能访问了太多的列。常见的情况有:查询不需要的记录、多表关联时返回全部列、总是取出全部列(select * from ….)、重复查询相同的数据。这些都会给MySQL服务器带来额外的负担,并增加网络开销,另外也会消耗应用服务器的CPU和内存资源。
  2. 确认MySQL服务器层是否在分析大量超过需要的数据行。对于MySQL,最简单的衡量查询开下的三个指标如下:响应时间、扫描的行数、返回的行数。分析查询时,查看该查询扫描的行数时非常有帮助的,这在一定程度上能够说明该查询找到需要的数据的效率高不高。

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

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

但也不是说增加索引就能让扫描的行数等于返回的行数。如下面的使用聚合函数COUNT()的查询:


这个查询需要读取几千行的数据,但是仅返回200行结果。没有什么索引能够让这样的查询减少需要扫描的行数。

如果发现查询需要扫描大量的数据但只返回少数的行,那么通常可以尝试下面的技巧去优化它:

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

三、重构查询的方式

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

设计查询的时候一个需要考虑的重要问题是,是否需要将一个复杂的查询分成多个简单的查询。在传统实现中,总是强调需要数据库层完成尽可能多的工作,这样做的逻辑在与以前总是认为网络通信、查询解析和优化时一件代价很高的事情。但是这样的想法对于MySQL并不适用,MySQL从设计上让连接和断开连接都很轻量级,在返回一个小的查询结果方面很高效。而且现代的网络速度比以前要快很多。

MySQL内部能够扫描内存中上百万行数据,相比之下,MySQL相应给客户端就慢很多。在其他条件都相同的时候,使用尽可能少的查询当然是最好的,但是有时候,将一个大查询分解为多个小查询时很有必要的。这个需要好好衡量以下是不是会减少工作量。

2. 切分查询

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

删除旧的数据就是一个很好的例子。定期地清除大量数据时,如果用一个大的语句一次性完成的话,则可能需要一次锁住很多数据、占满整个事物日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL功能,同时还可以减少MySQL复制的延迟。

一次删除一万行数据一般来说是一个比较高效而且对服务器影响也最小的做法。同时需要注意的是,如果每次删除数据后,都暂停一会儿再做下一次删除,这样也可以将服务器上一次性的压力分散到一个很长的时间段中,就可以大大降低服务器的影响,还可以大大减少删除时锁定的特有时间。

3. 分解关联查询

很多高性能的应用都会对关联查询进行分解。简单地,可以对每一个表进行一次单表查询,然后将结果都再应用程序中进行管理。如下这个查询:

可以分解成下面这些查询来代替:

用分解关联查询的方式重构查询有如下的优势:

  • 让缓存的效率更高。
  • 将查询分解后,执行单个查询可以减少锁的竞争。
  • 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展。
  • 查询效率也有可能会有所提升。
  • 可以减少冗余记录的查询。
  • 更进一步,这样做相当于在应用层中实现了哈希关联,而不是是使用MySQL的嵌套循环关联。

在很多场景下,通过重构查询将关联放到应用程序中将会更加高效,这样的场景有很多,如:当应用能够方便地缓存单个查询的结果的时候、当可以将数据分不到不同的MySQL服务器上的时候、当能够使用IN()的方式代替关联查询的时候、当查询中使用同一个数据表的时候。

四、查询执行的基础

当希望MySQL能够以更高的性能运行查询时,最好的办法就是弄清楚MySQL是如何优化和执行查询的。

如下图,我们可以看到当向MySQL发送一个请求的时候,MySQL到底做了些什么:

mysql查询的运行过程
  1. 客户端发送一条查询给服务器
  2. 服务器先检查查询缓存,如果命中了缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段。
  3. 服务器进行SQL解析、预处理,再由优化器生成对应的执行计划
  4. MySQL根据优化器生成的执行机会,调用存储引擎的API来执行查询
  5. 将结果返回给客户端

1. MySQL客户端/服务器通信协议

MySQL客户端和服务器之间的通信时“半双工”的,则意味着,在任何一个时刻,要么时有服务器向客户端发送数据,要么是由客户端向服务器发送数据,这两个动作不能同时发生。

2. 查询缓存

在解析一个查询语句之前,如果查询缓存时打开的,那么MySQL会优先检查这个查询是否命中缓存中的数据。

3. 查询优化处理

查询的生命周期的下一步是将一个SQL转换成一个执行计划,MySQL在依照这个执行计划和存储引擎进行交互。这包括多个子阶段:解析SQL、预处理、优化SQL执行计划。这个过程中任何错误都可能终止查询。

(1) 语法解析器和预处理

首先,MySQL通过关键字将SQL语句进行解析,并生成一颗对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。

预处理则根据一些MySQL规则进一步检查解析树是否合法。

(2) 查询优化器

现在语法树被认为是合法的了,并且由优化器将其转化成执行计划。一条查询可以由很多种执行方式,最后对返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

(3) 数据和索引的统计信息

因为服务器层没有任何统计信息,所以MySQL查询优化器在生成查询的执行计划时,需要向存储引擎获取相应的统计信息。存储引擎则提供给优化器对应的统计信息,包括:每个表或者索引有多个页面、每个表的每个索引基数时多少、数据行和索引长度、索引的分布信息等。优化器根据这些信息来选择一个最优的执行计划。

4. 查询执行引擎

在解析和优化阶段,MySQL将生成查询对应的执行计划,MySQL的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构。

MySQL只是简单地根据执行计划给出的指令执行逐步执行。在根据执行计划逐步执行的过程中,有大量的操作需要通过调用存储引擎实现的接口来完成,这些接口也就是我们称为”handler API“的接口。

5. 返回结果给客户端

查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端,MySQL仍然会返回这个查询的一些信息,如该查询影响的行数。

MySQL将结果集返回客户端是一个增量、逐步返回的过程。

这样处理由两个好处:服务器端无需存储太多的结果,也就不会因为要返回太多结果而消耗太多内存。另外,这样的处理也让MySQL客户端第一时间获得返回的结果。

五、优化特定类型的查询

1. 优化COUNT()查询

COUNT()是一个特殊的函数,由两种非常不同的作用:它可以统计某个列值的数量,也可以统计行数。在统计列值时要求列值是非空的(不统计NULL)。最简单的就是当我们使用COUNT(*)的时候,这种情况下通配符*并不会像我们猜想的那样扩展成所有的列,实际上,它会忽略所有的列而直接统计所有的行数。

一个最常见的错误:在括号内制定了一个列却希望统计结果集的行数。如果希望知道的是结果集的行数,最好使用COUNT(*),这样写意义清晰,性能也会很好。

一个容易产生的误解:MyISAM的COUNT()函数总是非常快,不过这是有前提条件的,即只有没有任何WHERE条件的COUNT(*)才非常快,因为此时无需实际地计算表的行数。

当统计带WHERE子句的结果集行数,可以是统计某个列值的数量时,MyISAM的count()和其他存储引擎没有任何不同,就不再有神话般的速度了。

(1) 使用近似值

有时候某些业务场景并不要求完全精确的COUNT值,此时可以用近似值来代替。

很多时候,计算精确值的成本非常高,而计算近似值则非常简单。

更进一步优地优化则可以尝试删除DISTINCT这样的约束来避免文件排序。这样重写过的查询要比原来的精确统计的查询快很多,而返回的结果则几乎相同。

(2) 更复杂的优化

通常来说,count()都需要扫描大量的行才能获得精确的结果,因此是很难优化的。除了前面的方法,在MySQL层面还能做的就只有索引覆盖扫描。如果这还不够,就需要考虑修改应用的架构,可以增加汇总表或者增加类似Memcached这样的外部缓存系统。

2. 优化关联查询

  • 确保ON或者USING子句中的列上有索引
  • 确保任何的GROUP BY和ORDER BY中的表达式中只涉及到一个表中的列,这样MySQL才有可能使用索引来优化这个过程。

3. 优化子查询

关于子查询优化我们给出的最重要的优化建议就是尽可能使用关联查询代替,如果使用的是MySQL5.6或更新的版本或者MariaDB,那么就可以直接忽略关于子查询的这些建议了。

4. 优化GROUP BY和DISTINCT

在很多场景下,MySQL都使用同样的办法优化这两种查询,事实上,MySQL优化器会在内部处理的时候相互转化这两类查询。它们都可以使用索引来优化,这也是最有效的优化办法。

在MySQL中,当无法使用索引的时候,GROUP BY使用两种策略来完成:使用临时表或者和文件排序来分组。对于任何查询语句,这两种策略的性能都有可以提升的地方。

如果需要对关联查询做分组(GROUP BY),并且是按照查找查找表中的某个列进行分组,那么通常采用查找表的标识列分组的效率会比其他列更高。例如下面的查询就不会很好:

如果查询按照下面的写法效率则会更高:

如果没有通过ORDER BY子句显示地制定排序列,当查询使用GROUP BY子句的时候,这结果集会自动按照分组的字段进行排序。如果不关心结果集的顺序,而这种默认排序又导致了需要文件排序,则可以使用ORDER BY NULL,让MySQL不再进行文件排序。

5. 优化LIMIT分页

在系统中需要进行分页操作的时候,我们通常会使用LIMIT加上偏移量的办法实现,同时加上合适的ORDER BY子句。如果又对应的索引,通常效率会很不错,否则,MySQL需要做大量的文件排序操作。

一个非常常见又令人头痛的问题就是,在偏移量非常大的时候,例如可能是LIMIT 1000,20这样的查询,这时MySQL需要查询10020条记录然后只返回最后20条,前面1000条记录都被抛弃,这样的代价非常高。如果所有的页面被访问的频率相同,那么这样的查询平均需要访问半个表的数据。要优化这种查询,要么是在页面中限制分页的数量,要么是优化大偏移量的性能。

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描,而不是查询所有的列。考虑下面的查询:

如果这个表非常大,那么这个查询最好改写成下面的样子:

这里的”延迟关联“将大大提升查询效率,它让MySQL扫描尽可能少的页面,获取需要访问的记录后再根据关联列回元表查询需要的所有列。这个技术也可以用于优化关联查询中的LIMIT子句。

LIMIT和OFFSET的问题,其实是OFFSET的问题,它会导致MySQL扫描达赖给你不需要的行然后再抛弃掉。如果可以使用书签记录上次取数据的位置,那么下次就可以直接从该书签记录得位置开始扫描,这样就可以避免使用OFFSET。如下查询:

假设上面得查询返回得是主键为16049到16030的租借记录,那么下一页查询就可以从16030这个点开始:

该技术的好处是无论翻页到多么后面,其性能都会很好。

其他优化办法还包括使用预先计算的汇总表,或者关联到一个冗余表,冗余表只包含主键列和需要做排序的数据列。还可以使用Sphinx优化一些搜索操作。

6. 优化UNION查询

MySQL总是通过创建并填充临时表的方式来执行UNION查询。因此很多优化策略在UNION查询中都没法很好地使用。经常需要手工地将WHERE、LIMIT、ORDER BY等子句”下推到“UNION的各个子查询中,以便优化器可以充分利用这些条件进行优化。

除非确实需要服务器消除重复的行,否则一定要使用UNION ALL。如果没有ALL关键字,MySQL会给临时表加上DISTINCT选项,这回导致对这个临时表的数据做唯一性检查。这样做的代价非常高。即使有ALL关键字,MySQL仍然会使用临时表存储结果。事实上,MySQL总是将结果放入临时表,然后再读出,再返回给客户端。

7. 静态查询分析

Percona Toolkit中的pt-query-advisor能够解析查询日志、分析查询模式,然后给出所有可能存在潜在问题的查询,并给出足够详细的建议。

六、总结

理解查询时如何被执行的以及时间都消耗在哪些地方,这依然时前面我们介绍的响应时间的一部分。再加上一些诸如解析和优化过程的知识,就可以更进一步地理解前两部分讨论的MySQL如何访问表和索引的内容了。这也从另一个维度帮助读者理解MySQL在访问表和索引时查询和索引的关系。

转载请备注来源: 《MySQL性能优化学习笔记-(3)查询优化》 | shuwoom.com

打赏

发表评论

电子邮件地址不会被公开。