查询性能优化

6.1 为什么查询速度会慢

查询的生命周期：从客户端、到服务端，然后再服务器上进行解析，生成执行计划，执行，并返回结果给客户端。

查询需要在不同的地方花费时间：包括网络、CPU计算，生成统计信息和执行计划，锁等待（互斥等待）等操作，尤其是向底层存储引擎检索数据的调用操作。

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

查询性能低下的最基本原因是访问的数据太多。

对于低效的查询，下面的两个步骤很有用：

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

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

有些查询会请求查过实际需要的数据，然后这些多余的数据会被应用程序丢弃。这会给Mysql服务器带来额外的负担，并增加网络开销。典型案例：

查询不需要的记录
多表关联时返回全部列
总是取出全部的列
重复查询相同的数据

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

对于Mysql,最简单的衡量查询开销的三个指标如下：

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

响应时间

响应时间是两个部分之和：服务时间和排队时间。服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间-可能是等待I/O操作完成，也可能是等待行锁。

通过“快速上限估计”法来估算查询的响应时间:获得一个大概参考值来判断当前响应时间是不是一个合理的值。

扫描的行数和返回的行数

理想情况下扫描的行数和返回的行数应该是相同的。扫描的行数对返回的行数的比率通常很小，一般在1:1和10:1之间，不过有时候这个值也可能非常大。

扫描的行数和访问类型

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

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

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

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

使用索引覆盖扫描，把所有需要的列表都放到索引中，这样存储引擎无须会表获取对应行就可以返回结果了。
改变库表结构。
重写这个复杂的查询，让Mysql优化器能够已更优化的执行这个查询。

6.3 重构查询的方式

在优化有问题的查询时，目标应该是找到一个更优的方法获得实际需要的结果——而不一定总是需要从Mysql获取一模一样的结果集。可以改sql、方式完成查询。

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

在其他条件都相同的时候，使用尽可能少的查询当然是更好的。但是有时候将一个大查询分解为多个小查询是很有必要的。

6.3.2 切分查询

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

6.3.3 分解关联查询

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

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

6.4 查询执行的基础

Mysql执行一个查询的过程：

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

6.4.1 Mysqlk客户端/服务器通信协议

Mysql客户端和服务器之间的通信协议是“半双工”的，这意味着每一个时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据。

当客户端从服务器取数据时，看起来是一个拉数据的过程，但实际上是Mysql在向客户端推送数据的过程。客户端不断地接收从服务器推送的数据，客户端也没法让服务器停下来。

查询状态

对于Mysql连接，或者说一个线程，任何时刻都有一个状态，该状态表示了Mysql当前正在做什么。Show Full ProcessList命令返回command中的状态,状态列表：

Sleep：线程正在等待客户端发送新的请求；
Query:线程正在执行查询或者正在将结果发送给客户端；
Locked:在Mysql服务器层，该线程正在等待表锁。
Analyzing and statistics:线程正在收集存储引擎的统计信息，并生成查询的执行计划。
Copying to tmp table [on disk]:线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态一般要么是在做Group by操作，要么是文件排序操作，或者是Union操作。如果这个状态后面还有“on disk”标记，那表示Mysql正在将一个内存临时表放到磁盘上。
Sorting result:线程正在对结果集进行排序
sending data:线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。

6.4.2 查询缓存

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

6.4.3 查询优化处理

查询的生命周期的下一步是将一个SQL转换成一个执行计划，Mysql再依照这个执行计划和存储引擎进行交互。这包括多个子阶段：解析SQL、预处理、优化SQL执行计划。

语法解析器和预处理

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

查询优化器

一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到其中最好的执行计划。

Mysql使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划是的成本，并选择其中成本最小的一个。可以通过查询当前的会话的last_query_cost的值来得知Mysql计算的当前查询的成本。这是根据一系列的统计信息计算得到的：每个表或者索引的页面个数、索引的基数、索引和数据行的长度，索引分布情况。

有很多种原因会导致Mysql优化器选择错误的执行计划，如下所示：

统计信息不准确。Mysql依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息是准确的，有的偏差很大。
执行计划中的成本估算不等同于实际执行的成本。
Mysql的最优可能和你想的最优不一样。
Mysql从不考虑其他并发执行的查询，这可能会影响到当前的查询的速度。
Mysql也并不是任何时候都是基于成本的优化。
Mysql不会考虑不受其控制的操作的成本。

下面是一些Mysql能够处理的优化类型：

重新定义关联表的顺序：决定关联的顺序是优化器很重要的一部分
将外链接转化成内连接：并不是所有的Outer join语句都必须已外链接的方式的方式执行了。
使用等价变换规则：Mysql可以使用一些等价变换来简化并规范表达式。
优化count()、min()和Max():索引和列是否可以为空通常可以帮助Mysql优化这表达式。
预估并转化为常数表达式：当Mysql检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理。
覆盖索引扫描：当索引中的列包含所有查询中需要使用的列的时候，Mysql就可以使用索引返回需要的数据，而无须查询对应的数据行。
子查询优化
提前终止查询
等值传播：如果两个列的值通过等式关联，那么Mysql能够把qi中一个列的where条件传递到另一个列上。
列表In()的比较：Mysql将In()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O(log n)复杂度的操作，等价地转换成Or查询的复杂度为O(n)

数据和索引的统计信息

统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息。

Mysql如何执行关联查询

Mysql对任何关联都执行嵌套循环关联操作，即Mysql先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，一次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。Mysql会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多的行以后，Mysql返回到上一层次关联表，是否鞥能够找到更多的匹配更多的记录，一次类推迭代执行。

执行计划

Mysql生成查询的一颗指令树，然后通过存储引擎执行完成这颗指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果对某个查询执行Explain Extended后，再执行Show Warnings，就可以看到重构出的查询。

关联查询优化器

它决定了多个表关联时的顺序。Mysql会将关联顺讯倒转过来，这种方式会让查询进行更少的嵌套循环和回溯操作。

排序优化

尽可能避免排序或者尽可能避免对大量数据进行排序。Mysql的排序过程统称为文件排序（filesort），即使完全是内存排序不需要任何磁盘文件时也是如此。

Mysql有如下两种排序算法：

两次传输排序：读取行指针和需要的排序的字段，对其进行排序，然后在根据排序结果读取所需要的数据行。
单次传输排序：先读取查询需要的所有列，然后根据给定列进行排序，最后直接返回排序结果。

6.4.4 查询执行引擎

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

6.4.5 返回结果给客户端

查询执行的最后一个阶段是将结果返回给客户端。

6.5 Mysql查询优化器的局限性

6.5.1 关联子查询

关联子查询通常性能都是比较差的，但是某些情况下会比关联查询速度快。因此最终还是以测试来验证查询语句。

6.5.2 union的限制

如果希望union的各个子句都能够根据limit取部分结果集，或者希望能够先排好序再合并结果集的话，就需要在Union的各个子句中分别使用这些子句。

6.5.3 索引合并优化

Mysql访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

第六章查询性能优化