6、查询性能优化

为什么查询速度会慢

在编写快速查询之前，需要清楚一点，真正重要的是响应时间。如果把查询看作是一个任务，那么他由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行的更快。

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

查询性能低下最基本的原因是访问的数据太多。某些查询不可避免地需要筛选大量数据，但这并不常见。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，我们发现可以通过下面两个步骤来分析：

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

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

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

Mysql是否在扫描额外的记录

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

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

重构查询的方式

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

设计查询的时候一个需要考虑的重要问题是：是否需要将一个复杂的查询分成多个简单的查询。在传统实现中，总是强调需要数据库完成尽可能多的工作，这样做的逻辑在于以前总是认为网络通信，查询解析和优化是一件代价很高的事情。但是这样的想法对于Mysql并不适用，Mysql从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效。
Mysql内部每秒能够扫描内存中上百万行数据，相比之下，Mysql响应数据给客户端就慢的多了。在其他条件都相同的时候，使用尽可能少的查询当然是更好的。

切分查询

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

分解关联查询

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

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

查询执行的基础

当希望Mysql能够以更高的性能运行查询时，最好的方法就是弄清楚Mysql是如何优化和执行查询的。
我们可以看看当向Mysql发送一个请求时，Mysql到底做了些什么：

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

Mysql客户端/服务端通信协议

Mysql客户端和服务端之间的通信协议是“半双工”的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。
当客户端从服务器取数据时，看起来是一个拉数据的过程，但实际上是Mysql在向客户端推送数据。客户端不断地接收从服务器推送的数据，客户端也没法让服务器停下来。

查询缓存

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

查询优化处理

语法解析器和预处理

Mysql通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。Mysql解析器将使用Mysql语法规则验证和解析查询。
预处理器则根据一些Mysql规则进一步检查解析数是否合法。

查询优化器

Mysql使用基于成本的优化器，他将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。
有很多种原因会导致Mysql优化器选择错误的执行计划：

  - 统计信息不准确
  - 执行计划中的成本估算不等同于实际执行的成本。所以即使统计信息精准，优化器给出的执行计划也可能不是最优的
  - Mysql的最优可能和你想的最优不一样，你可能希望执行时间尽可能的短，但是Mysql只是基于其成本模型选择最优的执行计划
  - Mysql从不考虑其他并发执行的查询，这可能会影响到当前查询的速度
  - Mysql并不是任何时候都是基于成本的优化
  - Mysql不会考虑不受其控制的操作的成本
  - 优化器有时候无法去估算所有可能的执行计划

Mysql能够处理的优化类型：

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

数据和索引的统计信息

服务器层优查询优化器，却没有保存数据和索引的统计信息。统计信息是由存储引擎实现，不同的存储引擎可能会存储不同的统计信息。
因为服务器层没有任何统计信息，所以Mysql查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息。

关联查询优化器

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

排序优化

无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。
Mysql有以下两种排序算法：
两次传输排序：读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。
单词传输排序：先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。

查询执行引擎

在解析和优化阶段，Mysql将生成查询对应的执行计划，Mysql的查询执行引擎则根据这个执行计划来完成整个查询。
相对于查询优化阶段，查询执行阶段不是那么复杂，Mysql只是简单地根据执行计划给出的指令逐步执行。为了执行查询，Mysql只需要重复执行计划中的各个操作，直到完成所有的数据查询。

返回结果给客户端

查询执行的最后一个阶段就是将结果返回给客户端。即使查询不需要返回结果集给客户端，Mysql仍然会返回这个查询的一些信息，如该查询影响到的行数。如果查询可以被缓存，那么Mysql在这个阶段也会将结果存放到查询缓存中。

Mysql查询优化器的局限性

关联子查询

Mysql的子查询实现的非常糟糕。最糟糕的一类查询是WHERE条件中包含IN()的子查询语句。

UNION的限制

有时，Mysql无法将限制条件从外层下推到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。

索引合并优化

当WHERE子句中包含多个复杂条件的时候，Mysql能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

等值传递

某些时候，等值传递会带来一些意想不到的额外消耗。

并行执行

Mysql无法利用多核特性来并行执行查询。

哈希关联

Mysql并不支持哈希关联，Mysql所有的关联都是嵌套循环关联。

松散索引扫描

Mysql不支持松散索引扫描。

最大值和最小值优化

对于MAX()和MIN()查询，Mysql的优化做的并不好。

在同一个表上查询和更新

Mysql不允许对同一张表同时进行查询和更新，这其实不是优化器的限制。

查询优化器的提示

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

HIGH_PRIORITY 和 LOW_PRIORITY

这个提示告诉Mysql，当多个语句同时访问某一个表的时候，哪些语句的优先级相对高些，哪些语句的优先级相对低些

DELAYED

这个提示对 INSERT 和 REPLACE 有效。Mysql会将使用该提示的语句立即返回给客户端，并将插入的行数据放入到缓冲区，，然后再表空闲时批量将数据写入。

STRAIGHT_JOIN

这个提示可以放置在SELECT语句的SELECT关键字之后，也可能放置在任何两个关联表名字之间。第一个用法是让查询中所有的表按照在语句中出现的顺讯进行关联。第二个用法则是固定其前后两个表的关联顺序。

SQL_SMALL_RESULT 和 SQL_BIG_RESULT

这两个提示只对SELECT 语句有效，他们告诉优化器对 GROUP BY和DISTINCT 查询如果使用临时表及排序。SQL_SMALL_RESULT 告诉优化器结果集会很小，可以将结果集放在内存中的索引临时表，以避免排序操作。如果是 SQL_BIG_RESULT ，则告诉优化器结果集可能会非常大，建议使用磁盘临时表做排序操作。

SQL_BUFFER_RESULT

这个提示告诉优化器将查询结果放入到一个临时表，然后尽可能快低释放表锁。

SQL_CACHE 和 SQL_NO_CACHE

这个提示告诉 Mysql 这个结果集是否应该缓存在查询缓存中。

SQL_CALC_FOUND_ROWS

会让Mysql返回的结果集包含更多的信息。

FOR UPDATE 和 LOCK IN SHARE MODE

这两个提示主要控制 SELECT 语句的锁机制，但只对实现了行级锁的存储引擎有效。使用该提示会对符合查询条件的数据行加锁。

USE INDEX、IGNORE INDEX 和 FORE INDEX

这几个提示会告诉优化器使用或者不使用哪些索引来查询记录。

优化特定类型的查询

优化COUNT()查询

COUNT() 有两种非常不同的作用：可以统计某个列值的数据，也可以统计行数。

优化关联查询

确保ON或者USING子句中的列上有索引
确保任何的GROUP BY和 ORDER BY 中的表达式只涉及到一个表中的列。

优化子查询

尽可能使用关联查询代替

优化GROUP BY 和 DISTINCT

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

优化 LIMIT 分页

在系统中需要进行分页操作的时候，通常会使用 LIMIT 加上偏移量的办法实现，同时加上合适的 ORDER BY 子句。如果有对应的索引，通常效率会不错。

优化 SQL_CALC_FOUND_ROWS

优化 UNION 查询

Mysql总是通过创建并填充临时表的方式来执行UNION查询。因此很多优化策略在UNION查询中都没法很好的使用。经常需要手动将WHERE等子句下推到UNION的各个子查询中。
除非确实需要服务器消除重复的行，否则就一定要使用 UNOIN ALL。如果没有ALL关键字，Mysql会给临时表加上 DISTINCT关键字，这会导致对整个临时表的数据做唯一性检查。

静态查询分析

使用用户自定义变量