慢查询基础：优化数据访问
查询性能低下的基本原因是访问的数据太多。某些查询可能不避免地需要筛选大量的数据，但这并不常见。大部分查询性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，通过下面两个步骤来分析很有效:
- 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能访问了太多的列。
- 确认MySQL 服务器是否在分析大量超过需要的数据行。
是否向数据库请求了不需要的数据
有些查询的请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃。这会给MySQL 服务器带来额外的负担，并增加网络开销，另外也消耗服务器的 CPU 和内存资源。
案例：
查询不需要的记录：
使用 SELECT 查询出大量的结果，但是只使用少数。解决办法是在查询后面加上 LIMIT
多表关联时返回全部列:
总是取出全部列
如果使用了缓存机制时取出全部的列比单独取出的列更有好处，正常情况下只需要取出需要的列。
重复查询相同的数据
可以将常用的查询结果进行缓存，以避免重复查询已有的相同数据。
MySQL 是否在扫描额外的数据
对于MySQL 最简单的查询开销的三个指标为：
- 响应时间
- 扫描的行数
- 返回的行数
没有那个指标能够完美地衡量查询的开销，但他们大致反映了 MySQL 在内部执行查询时需要访问多少数据，并可以大概推算出查询运行的时间。这三个指标都会记录到 MySQL 的慢日志中，所以检查慢日志记录是找出扫描行数过多的查询的好办法。
响应时间
响应时间是一个相对的值，它有两部分时间组成：服务时间和排队时间。服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待资源而没有真正执行查询的时间
——可能是等I/O操作完成，也可能是等待行锁，等等。我们无法把响应时间细分到上面这些部分。一般最常见和最重要的等待是 I/O 和锁等待，但实际情况更加复杂。我们可以大概估计一下，期望的查询响应时间。扫描的行数和返回的行数
分析查询时，查看该查询扫描的行数是非常有帮助的。 这在一定程度上能够说明该查询找到需要的数据的效率高不高。
理想情况下扫描的行数和返回的行数应该是相同的。但实际上这种情况很少，例如在做一个关联查询时，服务器必须要扫描多行才能生成结果集的一行。扫描的行数对返回的行数的比率通常很小，一般在 1:1 和10:1 之间，不过有时候这个值也可能非常大。
扫描的行数和返回类型
在评估查询开销的时候，需要考虑一下从表中找到某一行数据的成本。MySQL 有好几种访问方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才能返回一行结果，也有一些访问方式可能无需扫描就能返回结果。
在 EXPLAIN 语句中的 type 列反映了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列出的速度是从慢到快，扫描的行数也是从小到大。需要明白这些概念。
如果查询没有办法知道合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引。
在 EXPLAIN 查询时 Extra 表示附加信息，其中 using where 表示MySQL 将通过 WHERE 条件来筛选出存储引擎返回的记录。
一般MySQL 能够使用如下三种方式应用 WHERE 条件，从好到坏依次为：
- 在索引中使用 WHERE 条件来过滤不匹配的记录。这是在存储引擎层完成的。
- 使用索引覆盖扫描（在 Extra 列中出现了 Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在 MySQL 服务器层完成的，但无需在回表查询记录。
- 从数据表中返回数据，然后过滤不满足条件的记录（Extra列中出现 Using Where）。这在MySQL 服务器层完成，MySQL 需要先从数据表读出记录然后过滤。
如果发现索引需要扫描大量的数据但只返回少数的行，那么通常尝试下面的技巧去优化它：
- 使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎可以无须回表获取行就可以返回结果了。
- 改变库表的结构，例如使用单独的汇总表。
- 重写这个复杂的查询。
重构查询的方式
在优化有问题的查询的时候，目标是找到一个更优的办法找到实际需要的结果——而不是一定总是要获取到一模一样的结果集。可以通过重构来提高查询的性能。
一个复杂查询还是多个简单查询
设计查询的时候一个需要考虑的问题是，是否需要将一个复杂的查询分成多个简单的查询。在传统实现中，总是强调需要数据库层完成尽可能多的工作，这样做的逻辑在于以前总是认为网络通信、查询解析和优化是一件代价很高的事情。
但是这样的想法对于MySQL 并不适用,MySQL 从设计上让连接和断开都很轻量级，在返回一个小的查询结果方面很高效。而且现代的网络速度比以前要快很多，无论是带宽还是延迟。
MySQL 内部每秒能够扫描内存中上百万行数据，相比之下，MySQL 响应给客户端就慢得多了。在其他条件都相同的时候，使用极可能少的查询当然是更好的。但是有时候，将一个大查询分解为多个小查询是很有必要的。
切分查询
有时候对于一个大查询我们需要“分而治之”，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。
删除旧的数据就是很好的例子。定期地清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事物日制、耗尽系统资源、阻塞很多小的但很重要的查询。将一个大的 DELETE 语句切分成多个叫较小的查询可以尽可能小地影响MySQL 性能，同时还可以减少MySQL 复制的延迟。
分解关联查询
很多高性能的应用都会对关联查询进行分解。简单地，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。
使用分解关联查询的方式重构查询有如下优势：
- 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。如果将关联查询拆分之后，某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了。
- 将查询分解后，执行单个查询可以减少锁的竞争。
- 在应用层做关联，可以更容易对数据库进行拆分，更容易做做到高性能和扩展。
- 查询本身效率可能会有所提升。
- 可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会较少网络和内存的消耗。
- 更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL 的嵌套循环关联，某些场景哈希关联的效率要高很多。
在很多场景下，通过重构查询将关联放到应用程序中将会更加高效，这样的场景有很多，比如：当应用能够方便地缓存单个查询的结果的时候，当可以将数据分布到不同的MySQL服务器上的时候。当能够使用 in函数的方式代替关联查询的时候、当查询中使用同一个数据表的时候。