MySQL（五）｜《千万级大数据查询优化》第二篇：查询性能优化（

2018-04-09 本文已影响1700人小怪聊职场

MySQL优化一般是需要索引优化、查询优化、库表结构优化三驾马车齐头并进。
本章节开始讲查询优化。

一、为什么查询速度会慢

可以把查询当作一个任务，它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上是优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快。
MySQL在执行查询的时候有哪些子任务，这个是有一定的方法进行剖析的，具体方法下回单独拿一个章节来分析。
通常来说，查询的生命周期大致可以按照顺序来看：从客户端，到服务端，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。
在完成这些任务的时候，查询需要在不同的地方花费时间，包括网络，CPU计算，生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向底层存储引擎检索数据的调用操作，这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间。
在每一个消耗大量时间的查询案例中，都能看到一些不必要的额外操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。优化查询的目的就是减少和消除这些操作所花费的时间。

一、首选要优化数据访问

查询性能底下最基本的原因是访问的数据太多。所以，对于低效的查询，一般通过两个步骤来分析：

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

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

在访问数据库时，应该只请求需要的行和列，请求多余的行和列会消耗MySQL服务器的CPU和内存资源，并增加网络开销。
1、在处理分页时，应该使用LIMIT限制MySQL只返回需要的数据，而不是向应用程序返回全部数据后，再由应用程序过滤不需要的行。
2、多表关联时，或获取单表数据时，尽量避免不加思考地使用SELECT *
3、当一些数据被多次使用时可以考虑将数据缓存起来，避免每次使用都要到MySQL查询。

1.2、MySQL是否在扫描额外的记录，应该让MySQL使用最合适的方式查询数据

对于MySQL，最简单的衡量查询开销有三个指标：响应时间、扫描的行数和返回的行数。这里主要考虑提高扫描的方式，即查询数据的方式。
查询数据的方式有全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用等。这些查询方式，速度从慢到快，扫描的行数也是从多到少。可以通过EXPLAIN语句中的type列反应查询采用的是哪种方式。
通常可以通过添加合适的索引改善查询数据的方式，使其尽可能减少扫描的数据行，加快查询速度。
例如，当发现查询需要扫描大量的数据行但只返回少数的行，那么可以考虑使用覆盖索引，即把所有需要用到的列都放到索引中。这样存储引擎无须回表获取对应行就可以返回结果了。

二、重构查询的方法

设计查询的时候需要考虑是否需要把一个复杂的查询分成多个简单的查询。在我的印象中，曾经无数次听到一个经验法则：可以在数据库中做的事不要放在应用程序中，数据库比我们想象的要厉害的多。这个经验法则是在华夏基金使用Oracle编写SQL时一位Oracle牛人告诉我的，后来我把它使用到MySQL上，真是吃尽苦头。
当然这其中的原因有Oracle和MySQL原本就不是一样的处理逻辑，并且现在的网络通信、查询解析和优化的代价并没有以前那么高啦。再次说明，经验法则有在某种特定笼子里才有效。
分解复杂的查询：
可以将一个大查询切分成多个小查询执行，每个小查询只完成整个查询任务的一小部分，每次只返回一小部分结果。
删除旧的数据是一个很好的例子。
如果只用一条语句一次性执行一个大的删除操作，则可能需要一次锁住很多数据，占满整个事务日志，耗尽系统资源、阻塞很多小的但重要的查询。将一个大的删除操作分解成多个较小的删除操作可以将服务器上原本一次性的压力分散到多次操作上，尽可能小地影响MySQL性能，减少删除时锁的等待时间，同时也减少了MySQL主从复制的延迟。这个方法我一直在用。
另一个例子是分解关联查询，即对每个要关联的表进行单表查询，然后将结果在应用程序中进行关联。我在之前一家公司和一位在阿里待过很多年的同事一起编码时，他就是这么干的。后来我在心中默默地鄙视着他，因为我心里有这么一个经验法则（可以在数据库中做的事不要放在应用程序中，数据库比我们想象的要厉害的多），并且我在行动上也是保持能用一个SQL解决的事绝对不会用两个SQL。这么做当然处理经验法则的原因之外还有一个原因是：获取数据的逻辑尽量与业务代码分离，这样以后在切换数据库时也很方便。实际上是这样吗？未必啊。那次的无知让我吃尽苦头啊，后来因为SQL的性能问题再把我写的大部分SQL进行分解。
用分解关联查询的方式重构查询有如下的优势：

让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。
将查询分解后，执行单个查询可以减少锁的竞争。
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
查询本身效率也可能会有所提升。
可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消耗。
更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多。

三、查询执行的基础

当向MySQL发送一个请求之后，MySQL到底做了些什么？如下图所示：

查询执行路径

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

上述的每一步都比想象的复杂。我们在下一章节来进行分析。