Never too late to learn.

0%

高性能MySQL-读书笔记3

高性能MySQL第三版

[美] Baron Scbwartz, Peter Zaitsev, Vadim Tkacbenko

第6章 查询性能优化

6.1 为什么查询速度会慢

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

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

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

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

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

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

6.2.2 MySQL是否在扫描额外的记录

在确定查询只返回需要的数据以后,接下来应该看看查询为了返回结果是否扫描了过多的数据。

对于MySQL,最简单的衡量查询开销的三个指标:

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

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

响应时间时两个部分之和:服务时间和排队时间。

在EXPLAIN语句中的type列反应了访问类型。访问类型有很多种,从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。速度从慢到快,扫描的行数从多到少。

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

6.3 重构查询的方式

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

设计查询的时候一个需要考虑的重要问题是,是否需要将一个复杂的查询分成多个简单的查询。

6.3.2 切分查询

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

6.3.3 分解关联查询

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

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

6.4 查询执行的基础

mysql-search.jpg

查询执行路径:

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

6.4.1 MySQL客户端/服务端通信协议

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

查询状态

对于一个MySQL连接,或者说一个线程,任何时刻都有一个状态,该状态表示了MySQL当前正在做什么。使用SHOW FULL PROCESSLIST命令查看当前的状态。在一个查询的生命周期,状态会变化很多次;

  • Sleep: 线程正在等待客户端发送新的请求
  • Query: 线程正在执行查询或者正在将结果发送给客户端
  • Locked:在MySQL服务器层,该线程正在等待表锁。在存储引擎级别实现的锁,并不会体现在线程状态中。
  • Analyzing and statistics: 线程正在收集存储引擎的统计信息,并生成查询的执行计划。
  • Copying to tmp table [on disk]: 线程正在执行查询,并将其结果集都复制到一个临时表中,这种状态一般要么是在做GROUP BY操作,要么是文件排序操作,或者是UNION操作
  • Sorting result: 线程正在对结果集进行排序。
  • Sending data: 线程可能在多个状态之间传送数据,或者在生成结果集,或者在向客户端返回数据。

6.4.2 查询缓存(Query Cache)

在解析一个查询语句之前,如果查询缓存时打开的,那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检车时通过一个对大小写敏感的哈希查找实现的。

如果当前查询命中了查询缓存,如果权限没有问题,mysql会跳过所有其他阶段,直接从缓存中拿到结果并返回给客户端。这种情况下,查询不会被解析,不用生成执行计划,不会被执行。

6.4.3 查询优化处理

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

  • 语法解析器和预处理

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

  • 查询优化器

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

MySQL使用基于成本的优化器,它将尝试预测一个查询使用某种执行计划时的成本,并选择其中成本最小的一个。

MySQL能够处理的优化类型

  • 重新定义关联表的顺序
  • 将外连接转化成内连接
  • 使用等价变换规则
  • 优化COUNT(), MIN()和MAX()
  • 预估并转化为常数表达式
  • 覆盖索引扫描
  • 子查询优化
  • 提前终止查询
  • 等值传播
  • 列表IN()的比较
  • 数据和索引的统计信息

MySQL架构的多个层次之种,在服务器层有查询优化器,却没有保存数据和索引的统计信息。统计信息由存储引擎实现,不同的的存储引擎可能会存储不同的统计信息(也可以按照不同的格式存储统计信息)。

因为服务层没有任何统计信息,所以MySQL查询优化器在生成查询的执行计划时,需要向存储迎请获取相应的统计信息。优化器根据这些信息选择一个最优的执行计划。

  • MySQL如何执行关联查询

MySQL任务任何一个查询都是一次“关联”—并不仅仅是一个查询需要到两个表匹配才叫关联,所以在MySQL中,每一个查询,每一个片段都可能是关联。

  • 执行计划

和很多其他关系数据库不同,MySQL并不会生成查询字节码来执行查询。MySQL生成查询的一棵指令树,然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。

MySQL的执行计划是一棵左侧深度优先的树。

  • 关联查询优化器

MySQL优化器最重要的一部分就是关联查询优化,它决定了多个表关联式的顺序。通常多表关联的时候,可以有多种不同的关联顺序来获得相同的执行结果。关联查询优化器则通过评估不同顺序时的成本来选择一个代价最小的关联顺序。

  • 排序优化

无论如何排序都是一个成本很高的操作,所以从性能角度考虑,应尽可能避免排序或者尽可能避免对大量数据进行排序。

MySQL可以通过索引进行排序。当不能使用索引生成排序结果的时候,MySQL需要自己进行排序,如果数据量小则在内存中进行,如果数据量大则需要使用磁盘,不过MySQL将这个过程统一称为文件排序(filesort).

6.4.4 查询执行引擎

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

在根据执行计划逐步执行的过程中,有大量的操作需要通过调用存储引擎实现的接口来完成,这些接口也就是我们称为“handler API”的接口。查询中的每一个表由一个handler的实力表示。

6.4.5 返回结果给客户端

6.5 MySQL查询优化器的局限性

6.6 查询优化器的提示(hint)

如果对优化器选择的执行计划不满意,可以使用优化器提供的几个提示(hint)来控制最终的执行计划。

6.7 优化特定类型的查询

Coffee? ☕