优化SQL 查询性能

为什么查询会很慢

如果把查询看作是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。要优化查询，实际上是要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快。

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

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

优化数据访问

大部分性能低下的查询都可以通过减少访问数据量的方式进行优化。对于低效的查询，我们发现通过下面两个步骤来分析总是很有效：

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

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

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

查询不需要的记录，如获取前一百条记录：select * from sales_order where region_no = 86; 这种情况MySQL会返回全部结果集，造成资源浪费，可在查询之后加上Limit。
单/多表关联查询时，只获取需要的列,避免使用select *
对于查询频率高的数据，可以将其缓存起来

MySQL是否在扫描额外的记录

在确定查询只返回需要的数据以后，接下来应该看看查询为了返回结果是否扫描了过多的数据，最简单的两个指标为：扫描行数、返回行数。但对于找出那些“糟糕”的查询，这两个指标可能还不够完美，因为并不是所有的行的访问代价都是相同的。较短的行的访问速度更快，内存中的行也比磁盘中的行的访问速度要快得多。

扫描的行数和访问类型

在EXPLAIN 语句中的type 列反应了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列的这些，速度是从慢到快，扫描的行数也是从小到大。如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引。

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

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

在使用聚合函数时，MySQL 需要扫描的行与实际返回的行数往往差异很大，对于这种扫描大量数据但只返回少数的行，通常可以尝试使用下面的技巧来优化：

使用索引覆盖扫描，无需回表
改变库表结构，如使用单独的汇总表
重写复杂查询，让Mysql能够以更优化的方式执行查询

重构查询方式

有时候，可以将查询转换一种写法让其返回一样的结果，但是性能更好；也可以通过修改应用代码，用另一种方式完成查询。

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

MySQL内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢得多了。在其他条件都相同的时候，使用尽可能少的查询是更好的。但随着网络带宽的提升，延迟的下降，Mysql在应对多个小查询也不是什么问题了，在必要时，将一个大查询拆分为多个小查询也是能接受的。

切分查询

有时候对于一个大查询我们需要“分而治之”，将大查询切分成小查询。删除旧的数据就是一个很好的例子。定期地清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL性能，同时还可以减少MySQL复制的延迟。如每个月运行一次的查询：

delete from warn_log where entry_datetime < DATE_SUB(NOW(),INTERVAL 3 MONTH)

可使用以下方式来完成相同的工作：对于事务型存储引擎，很多时候小事务更加高效，将原本一次性删除的数据分担到多次来删除，也减轻了服务器的压力。

rows_affected = 0

 do {

 rows_affected = do_query(

 "DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH)

 LIMIT 10000")

 } while rows_affected > 0

分解关联查询

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

乍一看，这样做并没有什么好处，原本一条查询，这里却变成多条查询，返回的结果又是一模一样的。事实上，用分解关联查询的方式重构查询有如下的优势：

让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。
将查询分解后，执行单个查询可以减少锁的竞争。
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和高扩展。
可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。

在很多场景下，通过重构查询将关联放到应用程序中将会更加高效，比如：当应用能够方便地缓存单个查询的结果的时候、当可以将数据分布到不同的MySQL服务器上的时候、当能够使用IN() 的方式代替关联查询的时候、当查询中使用同一个数据表的时候。

巴特西