查询性能优化

高性能的SQL需要查询优化、索引优化、库表结构优化齐头并进，一个不落。快速的查询真正重要的是相应时间。如果把查询看作是一个任务，那么由一系列子任务组成，每个子任务都会消耗一定的时间。优化查询，实际上是要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行的更快。
通常来说，查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后在服务器上解析，生成执行计划，执行，并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。查询需要在不同的地方花费时间，包括网络，CPU计算，生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向底层存储引擎索引数据的调用操作，这些调用需要在内存操作、CPU操作和内存不足导致的I/O操作上消耗时间。根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用。

优化数据访问

查询性能低下基本是访问的数据太多，大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，可以通过两个步骤来分析：

1. 确认应用程序是否在检索大量超过需要的数据。（访问了太多的行，或者是访问了太多的列）
2. 确认MySQL服务层是否在分析大量超过需要的数据行

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

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

   查询不需要的记录

   一个常见错误是常常误以为MySQL会只返回需要的数据，实际上MySQL却是先返回全部结果集再进行计算。
   可以使用最简单有效的解决方法就是这样的查询后面加上LIMIT。

   多表关联时返回全部列

   多表关联时，只取需要的列。

   总是取出全部列

   关于 SELECT * ，取出全部列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O、内存和CPU的消耗。但相对的查询超过需要的数据可能会简化开发，提高代码的复用性。如果应用程序使用了某种缓存机制，或有其他的考虑，获取超过需要的数据也可能有其好处。

   重复查询相同的数据

   不断执行相同的查询，然后每次都返回相同的数据。将数据缓存出来。

   2.MySQL 是否在扫描额外的记录

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

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

这三个指数大致反映了的MySQL在内部执行查询时访问多少数据，并可以大概推算出查询的时间。

响应时间

相应时间是服务时间和排队时间之和。服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间（可能是等I/O操作完成，也可能是等待行锁，等等。）

扫描的行数和返回的行数

查询的行数一定程度上能够说明查询找到需要的数据效率高不高。较短的行访问的速度较快，内存中的行也比磁盘中的行访问速度要快得多。例如在做一个关联查询时， 服务器必须扫描多行才能生成结果集中的一行。扫描的行数对返回的行数比率通常很小，一般在1:1和10:1之间，不过有时候这个值也会可能非常大。

扫描的行数和访问类型

在 EXPLAIN 语句中的 type 列反应了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、索引唯一查询、常数引用等。速度是从慢到快，扫描的行数也是从小到大。
如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引。索引让MySQL以最高效、扫描行数最少的方式找到需要的记录。
一般MySQL能够使用如下三种方式使用WHERE条件，从好到坏依次：

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

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

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

3.重构查询方式

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

3.2 切分查询

删除旧数据就是一个很好的例子。定期地清除大量的数据时，如果用一个很大的语句一次性完成的话，则可能需要一次锁住很多数据，占满整个事物日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELTE语句切分多个较小的查询可以尽可能地影响MySQL性能，同时减少MySQL复制的延迟。如果每次删除数据后，都暂停一会儿再做下一次删除，这样也可以将服务器上原本一次性的压力分散到一个很长的时间中去，可以大大降低对服务器的影响，减少删除时锁的持有时间。

3.3 分解关联查询

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

用分解关联查询的方式重构查询的优势：

• 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。
• 将查询分解后，执行单个查询可以减少锁的竞争。
• 在应用层做关联，可以更容易对数据进行拆分，更容易做到高性能和扩展。
• 查询本身效率可能会有所提升。这个例子中，使用IN（）代替关联查询，可以MySQL按照ID顺序进行查询，比随机关联更要高效。
• 可以减少容易记录查询。在应用层关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中查询做关联查询，则可能需要重复地访问一部分数据。
• 这样做相当于在应用中实现了 哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联效率要高很多

  3.4 查询执行的基础

  当我们向 MySQL 发送一个请求的时候，MySQL 到底做了什么？
  

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

   3.4.1 MySQL客户端/服务器通信协议

   MySQL客户端与服务器之间的通信协议是半双工的。在任意时刻，要么是从服务器发向客户端，要么就是客户端发向服务器。
   查询状态
   对于一个MySQL连接，或是一个线程，任意时刻都有一个状态，该状态表示了MySQL当前正在做什么。使用 SHOW FULL PROCESSLIST 命令可以查看。

3.4.2 查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数 据。这个检查是通过一个对大小写敏感的哈希查找实现的。如果当前查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限。如果权限没有问题，MySQL会跳过所有其他阶段，直接从缓存中拿到结果返回给客户端。这种查询不会被解析，不用生成执行计划，不会被执行。

3.4.3 查询优化处理

查询的生命周期的下一步是将一个 SQL 转换成一个执行计划， MySQL 再依照这个执行计划和存储引擎交互。包括：解析SQL、预处理、优化SQL执行计划。这个过程中任何错误都有可能终止查询。
语法解析器和预处理
MySQL通过关键字将SQL语句进行验证，并生成一颗对应的“解析树”。预处理器根据一些MySQL规则验证解析树是否合法。
查询优化器
语法树被认为是合法的了，并由优化器将其转化为执行计划。一条查询可以有很多种执行方式，优化器的作用就是找到其中最好的执行计划。MySQL使用基于’成本’的优化器，最初成本最小单位是随机读取一个4K数据页的成本，后来（成本计算公式）变得更复杂，并且引入了一些’因子’来估算某些操作的代价。

MySQL处理的优化类型：
重新定义关联表顺序
数据表的关联并不总是按照查询中的顺序进行。决定关联的顺序是优化器很重要的一部分功能。
将外连接转化为内连接
并不是所有的OUTER JOIN语句都必须以外连接的方式执行。
使用等价变换原则
MySQL可以使用一些等价变化来简化表达式。可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断。
优化COUNT()、MIN()、MAX()
索引列是否为空通过可以帮助MySQL优化这类表达式。例如，找到某一列的最小值，只需要查询对应B-Tree索引最左端的记录。同理找到一个列的最大值则需要查询对应B-Tree索引最右端的记录。如果MySQL使用这种类型的优化，那么在EXPLAIN中可以看到”Select table optimized away”。类似的，没有WHERE 条件的COUNT()查询也可以使用存储引擎提供的优化。
预估并转化为常数表达式

MySQL分两步执行这个查询，也就是上面执行计划的两行输出。第一步先从film表中找到需要的行。因为在film_id字段上有主键索引，所以MySQL优化器只会返回一行数据，优化器在生成执行计划的就已经通过索引信息知道将返回多少行数据。因为优化器已经明确知道有多少个值需要索引查询，所以这里访问的类型是const。
在执行计划的第二步，MySQL将第一步中返回的film_id列当作一个已知取值的列来处理。因为优化器清楚在第一步执行完成后，该值就会是明确的了。注意到正如第一步中一样，使用film_actor字段对表的访问也是const。
*覆盖索引扫描
当索引中的列包含所有查询所需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无需查询对应的数据行。

子查询优化
MySQL在某些情况下可以将子查询转换成一种效率更高的形式，从而减少多个查询多次数据进行访问。
提前终止查询
在发现已满足查询需求的时候，MySQL总是能够立刻终止查询。一个典型的例子就是使用LIMIT子句的时候。
等值传播
如果两个列的值通过等式关联，那么MySQL能够把其中一个列的WHERE条件传递到另一列上。

等同于

列表IN()比较
在很多数据库中IN()完全等同于多个OR条件的子句，因为这两者是完全等价的。在MySQL中是不成立的，MySQL将IN()列表中的数据先进行排序，然后通过二分法查找的方式来确定列表中的值是否满足条件。

MySQL如何执行关联查询
当前MySQL关联执行的策略很简单：MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有的表中匹配的行为止。然后根据每个表的匹配行，返回查询中需要的各个列。MySQL会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多的行以后，MySQL返回到上一层次关联表，看是否能找到更多的匹配记录，依次类推迭代执行。

MySQL不会生成字节码来查询，而是生成一颗指令树，然后通过存储引擎执行完成这颗指令树并返回结果。
MySQL总是从一个表开始一直循环嵌套、回溯完成所有表关联。所以，MySQL计划的指令树总是一颗左侧深度优先的树。

关联查询优化器
关联顺序倒转，倒转的关联顺序会让查询进行更少的嵌套循环和回溯操作。MySQL关联优化器会尝试在所有关联顺序中选择一个成本最小的执行计划树。如果搜索空间非常大时，优化器则会使用“贪婪”搜索的方式查找“最优”的关联顺序。
排序优化
可以使用索引进行排序优化。当不能使用索引时，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘。MySQL将这一过程统称为文件排序(filesort)。如果排序的数据量小于“排序缓冲区”，则在内存中“快速排序”。如果内存不够，那么MySQL先将数据分块，对每个独立块使用“快速排序”，然后将各个排序好的块进行合并，返回排序结果。

4.优化特定类型的查询

4.1优化count()查询

count()作用，可以统计某个列的数量，也可以统计行数。在统计列值时要求列值为是非空的（不统计NULL）。如果在COUNT()的括号中指定了列的或者列的表达式， 则统计的就是这个表达式有值的结果数。
MyISAM的神话，一个错误的理解就是MyISAM COUNT()函数非常快，不过这个是有前提条件的，即只没有任何WHERE条件的COUNT()才非常快，此时不需要实时地计算表的行数。可以直接利用存储特性获取这个值。
简单优化，有时候可以使用MyISAM在COUNT()全表非常快的这个特性，来加速一些特定条件的COUNT()查询。
使用近似值，有时候某些业务场景不要求完全精确的COUNT值，此时可以使用近似值来代替。EXPLAIN出来的优化器估算的行数就是一个完全不错的近似值。
更复杂的优化，可以使用Redis或Memcache这样的外部缓存系统，但其实又会掉到一个只能“快速、精准、实现简单”，三选二的逻辑。

4.2优化关联查询

• 确保在ON或者USING子句中的列上有索引，避免多余的索引列，但要确保被关联的表对应的字段上有索引
• 连表时，GROUP BY 或 ORDER BY 中的表达式只涉及到其中的一个表，才能使用索引来优化这个SQL

  4.3优化LIMIT分页

  使用“延迟关联”将大大提升查询效率，它让MySQL扫描尽可能少的页面，获取需要访问的记录后再根据关联列回原表查询需要的列。或是根据根据自增的主键计算出主键范围内的数据，优点是无论翻页到多么后面，性能都会很好。

  MySQL的复制

  复制如何工作?

1. 在主库把数据更改记录到二进制日志(Binary Log)中
2. 备库将主库上的日志复制到中继日志中
3. 备库读取中继日中的事件，将其重放到备库数据上

第一步主库上记录二进制日志。在每次准备提交事务完成数据更新前，主库将数据更新的事件济洛路到二进制日志中。MySQL会按事务提交的顺序，而非每条语句的执行顺序来记录二进制日志。在记录二进制日志后，主库会告诉存储引擎可以提交事务了。
下一步，备库将主库的二进制日志复制到其本地的中继日志中。首先，备库启动一个工作线程，称为I/O线程，I/O线程跟主库建立一个普通的客户端连接，然后在主库上启动一个特殊的二进制转储（binlog dump）线程，这个二进制转储线程会读取主库上二进制日志中的事件。它不会对事件进行轮询。如果该线程追赶上了主库，它将进入睡眠状态，直到主库发送信号量通知其有新的事件才会被唤醒，备库I/O线程将会接收到到的事件记录到中继日志中。
备库的SQL线程执行最后一步，该线程从中继日志中读取事件并在备库执行，从而实现备库的数据更新。当SQL线程追赶上I/O线程时，中继日志通常已经在系统缓存中，所以中继日志的开销很低。SQL线程执行事件也可以通过配置选项来决定是否写入其自己的二进制日志。

总结