（四）查询性能优化

首先抛出一个问题：为什么查询速度会慢？

相信很多人不能一气呵成的说完整吧？ 其实我也不行 ，行的话，我有何必大篇幅整理呢？

是吧，哈哈。

1.  总括

查询性能低下的最基本的原因是访问的数据太多？——这个回答有点像脑筋急转弯吧，呵呵。

一般分析低性能查询的从两方面着手：

1) 确认应用程序是否在检索大量超过需要的数据（如额外的数据行或者列）。

2) 确认MySQL服务器层是否在分析大量超过需要的数据行。

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

通常的做法是分页亦或是利用 LIMIT 限制数据行数量。

3.  MySQL是否在扫描额外的数据

一般我们在WHERE条件中使用三种方式来过滤数据，从好到坏依次为：

1) 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。

2) 使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录，直接从索引中过滤不需

要的记录并返回命中的结果。，这是在MySQL服务器层完成的，但无需在再次回表查询记录。

3) 从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using Where）。这在

MySQL服务器层完成，MySQL需要先从数据表中堵住数据然后过滤。、

下面是经常使用到的一些技巧：

如果发现查询需要扫描大量的数据但是只返回少数的行，那么可以考虑如下手段：

1) 使用索引覆盖扫描，把所有需要用到的列都放到索引中，这样存储引擎无须回表获取对应行就

可以返回结果了。

2) 改变数据库表结构设计。例如使用单独的汇总表。

3) 重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询（到后面回头看这个）。

4.  重构查询

概述：有时候分解查询非常重要，也很有必要。等会我会举两个场景。

4.1  切分查询

将一个很大的DELETE语句切分为多个较小的查询可以尽可能小地影响MySQL性能。如果每次删除数据

后，都暂停一下再做下一次删除，这样也可以将服务器上原本一次性的压力分散到一个很长的时间段

中，就可以大大降低对服务器的影响，还可以减少删除时锁的持有时间。

说明：上面话中将DELETE切分为查询，相信有人会疑惑吧？——UPDATE、DELETE本质上都是查询，

因为先要查找出来才能做吧。

4.2  查询执行的基础

我们发一条SQL，这条SQL的执行步骤是什么呢？ 看如下示意图：

 

步骤描述如下：

a. 客户端发送一条SQL到服务器。

b. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。

c. 服务器端口进行SQL解析、预处理、再有优化器生成对应的执行计划（Exec plan）。

d. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。

5.将结果返回（并存储在缓存中）。

4.2.1  查询优化器

MySQL和Oracle一样，现在都是是使用的基于成本的优化器。Oracle中叫CBO（成本优化器）。

可以使用  SHOW STATUS  LIKE  'last_query_cost'; 来查看当前（最后一次的查询）的查询成本。

 

与Oracle一样，我们可以使用Hint来强制改变优化器选择。后续会分析。

4.2.2  关联查询

关联查询可以参考额的Oracle系列里面的关联查询。

4.2.3  排序优化

无论如何排序都是一个成本很高的操作，所以从性能的角度，应该尽可能避免排序或者尽可能避免对

大量数据进行排序。

当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中排序，如果

数据量大就需要使用磁盘，不过MySQL把这个过程统一称为文件排序（filesort），即使完全是内存排

序不需要任何磁盘文件时也是如此。

5.  在同一个表上查询和更新

先来看一个SQL：

6.  COUNT() 优化

    在统计行数的时候，最好使用COUNT(*)。这种情况下通配符 * 并不会像我想象的那样扩展成所有列，实

际上会忽略所有的列而直接统计所有的行数。再说 COUNT(*) 这样的写法清晰，性能也很不错的。

     面临复杂的COUNT(*) 优化，特别是在大数据量的情况下，就可能要使用汇总表了。

7.  用户自定义变量

    用户自定义变量是一个用来存储内容的临时容器，在连接MySQL的整个过程中都存在。

    下面了解下常见的用法：

8.  查询优化前的提示

这里我从网上找些资料，贴在下面参考下：