【MySQL】查询性能优化

0.前言

前面的章节我们介绍了如何设计最优的库表结构、如何建立最好的索引，这些对于高性能来说是必不可少的，但是这些还不够，还需要合理的设计查询。如果查询写的糟糕，即使表结构再合理、索引再合适，也无法实现高性能。
查询优化、索引优化、表结构优化需要齐头并进，一个不落。在获得编写Mysql查询经验的同时，也将学习到如何为高效查询设计表和索引。同样的，也可以学习到在优化库表结构时会影响到哪些类型的查询。

1.为什么查询速度会慢

查询任务最重要的是响应时间，如果把查询任务看做是一个任务，那么它是由一些列子任务组成的，每个子任务都会消耗一些时间，因此要优化查询时间就需要优化子任务，要么消除一些子任务，要么减少子任务的执行次数。

通常来说查询的生命周期可以按照：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用，以及调用后的数据处理，包括排序、分组等。

在完成这些任务的时候，查询需要在不同的地方花费时间，包括网络，CPU计算，生成统计信息和执行计划，锁等待等操作。尤其是向底层存储引擎检索数据的调用操作，这些调用操作需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间。根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用

在每一个消耗大量时间的查询案例中，我们都能看到一些不必要的额外操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。优化查询的目的就是减少和消除这些操作所花费的时间。

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

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

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

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

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

2.2 MYSQL是否在扫描额外的记录

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

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

响应时间

响应时间是服务时间和排队时间这两部分之和。

服务时间：数据库处理这个查询真正花费的时间。

排队时间：服务器因为等待某些资源没有真正执行查询的时间。可能是等待I/O操作完成，也可能是等待行锁等等。

扫描的行数和返回的行数

3. 重构查询方式

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

3.2 切分查询

3.3 分解关联查询

很多高性能的应用都会对关联查询进行分解。简单地，可以对每一个表进行一次单表查询，然后将结果在应用程序关联。