MySQL查询性能优化

1、为啥查询速度会变慢

如果把查询看作是一个任务，那么它是由一序列子任务组成，每个子任务都会消耗一定的时间，这样需要优化查询，实际就是优化子任务:
（1） 消除一些子任务；
（2）减少子任务的执行次数；
（3）让子任务运行得更快；

通常来说，查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后再服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中"执行"可以认为是整个生命周期中最重要的阶段，这其中包括大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序，分组等。
在完成这些任务的时候，查询需要在不同的地方浪费时间，包括网络，CPU计算，生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向底层存储引擎检索数据的调用操作，这些调用需要在内存操作、CPU操作和内存不足时导致的IO操作上消耗时间。根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用。

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

2、优化数据访问

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

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

一些经典案例：
一个常见的错误是常常会误以为MySQL会返回需要的数据，实际上MySQL却返回全部结果集再进行计算。
例如：
有些开发者，先使用select语句查询大量的结果，然后获取前面的N行后关闭结果集。

（2）多表关联时返回全部列
（3）总是取出全部列
（4）重复查询相同的数据

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

对于MySQL，最简单的衡量查询开销的三个指标：响应时间、扫描行数、返回行数。
（1）响应时间；
响应时间是两个部分之和：服务时间和排队时间。
服务时间：是指数据库处理这个查询真正花了多长时间。
排队时间：是指服务器因为等待某些资源而没有真正执行查询的时间，可能是等I/O操作完成，可能是等待行锁。
当看到一个·查询的响应时间的时候，首先需要分析，这个响应时间是否是一个合理的值。了解这个查询需要哪些索引以及它的执行计划是什么，然后计算大概需要多少顺序和随机I/O,再用其以在具体条件下一次I/O的消耗时间，最后把所有消耗，就可以获得一个大概参考值来判断当前响应时间是不是一个合理时间。

（2）扫描行数：
查看查询的扫描行数，在一定程度上能够说明该查询找到需要的数据的效率高不高。
较短的行的访问速度更快，内存中的行也比磁盘中的行访问速度更快。

（3）返回行数：
理想情况下，扫描的行数和返回的行数应该是相同的。但实际情况下，例如在做一个关联查询时，服务器必须扫描多行才能生成结果集中的一行。

（4）扫描的行数和访问类型
在评估查询开销的时候，需要考虑一下从表中找出某一行数据的成本。
有的访问方式可以查找并返回一行结果，有些访问可能需要扫描很多行才能返回一行结果。

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

索引让MySQL以最高效、扫描行数最少的方式找到需要的记录。

一般MySQL能够以如下三种方式应用WHERE条件，从好到坏依次为：

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

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

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

3 重构查询的方式

（1）一个复杂查询还是多个简单查询
（2）切分查询；
（3）分解关联查询