第六章 查询性能优化

高性能必不可少的条件：查询优化、索引优化、库表结构优化需要齐头并进，一个不落。

设计最优的库表结构、建立最好的索引。

还需要合理的设计查询。如果查询写得很糟糕，即使库表结构再合理、索引再合适，也无法实现高性能。

查询速度慢的原因

如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快。

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

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

优化查询的目的：减少和消除这些操作所花费的时间。

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

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

a、确认应用程序是否在检索大量超过需要的数据。是不是访问了太多的行或者太多的列。

b、确认MySql服务器层是否在分析大量超过需要的数据行。

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

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

如果能提高相同代码片段的复用性，这种做法也是值得考虑的，如果应用程序使用了某种缓存机制，或者有其他考虑。获取并缓存所有的列的查询，相比多个独立的只获取部分列的查询可能就更有好处。

重复查询相同的数据

很容易不小心出现这样的错误：不断地重复执行相同的查询，然后每次都返回完全相同的数据。例如，在用户评论的地方需要查询用户头像的URL，那么用户多次评论的时候，可能就会反复查询这个数据。比较好的方案是，当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能显然会更好。

MySql是否在扫描额外的记录

最简单的衡量查询开销的三个指标：响应时间、扫描的行数、返回的行数。这三个指标都会记录到MySql的慢日志中，所以检查慢日志记录是找出扫描行数过多的查询的好办法。

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

不同类型的应用压力下，响应时间都会被存储引擎的锁（表锁、行锁）、高并发资源竞争、硬件响应等因素影响。--快速上限估计。

扫描行数和返回行数

理想状态下扫描行数和返回行数是相同的。关联查询需要扫描多行才能生成结果集中的一行。

扫描的行数和访问类型

EXPLAIN语句中的type列反映了访问类型。访问类型有权标扫描、索引扫描、范围扫描、唯一索引查询、常数引用等。需要明白扫描表、扫描索引、范围访问和单值访问的概念。如果查询没有办法找到合适的访问类型，那么最好的解决办法就是增加一个合适的索引。索引对于查询优化非常重要。索引让MySql以最高效、扫描行数最少的方式找到需要的记录。

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

a、在索引中使用where条件来过滤不匹配的记录。这是在存储引擎层完成的。

b、使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySql服务器层完成的，但无须再回表查询记录。

c、从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using Where）。这在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤。

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

a、使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果了。

b、改变库表结构。例如使用单独的汇总表。

c、重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询。

重构查询的方式

一个复杂查询还是多个简单查询。相比之下，在其他条件都相同的时候，使用尽可能少的查询当然是更好的。有的时候将一个大查询分解为多个小查询是很有必要的。

不过在应用设计时，如果一个查询能够胜任时还写成多个独立查询是不明智的。例如，有些应用对一个数据表做10次 独立的查询来返回10行数据，每个查询返回一条结果，查询10次！

切分查询

有时候对于一个大查询我们需要“分而治之”，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一笑部分查询结果。

分解关联查询

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

select * from tag JOIN tag_post On tag_post.tag_id = tag.id JOIN post ON tag_post.post_id = post.id WHERE tag.tag='mysql';

可以分解为：select * from tag_post where tag_id =1;select * from tag_post where tag_id =1223; select * from post Where post.id in(123,456,667,8788);

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

让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。例如，上面查询中的tag已经被缓存了，那么应用就可以跳过第一个查询。再例如，应用中已经缓存了id为123，323，434的内容，那么第三个查询的IN（）中就可以少几个id。另外，对MySql的查询缓存来说，如果关联中的某个表发生了裱花，那么就无法使用查询缓存了，而拆分后，如果某个表很少该表，那么基于该表的查询就可以重复利用查询缓存结果了。

查询执行的基础