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

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

有些查询会请求超过实际需要的数据,让后这些数据会被应用程序丢弃.这会给**MySQL服务器带来额外的开销,并增加额外的开销(**应用程序和数据库不在一台主机上就显得很夸张了).

1.查询不需要的记录.

一些开发者总是喜欢使用select查询大量的数据,然后获取前面的N行后关闭结果集.他们认为MySQL会执行查询,并只会返回他们需要的N条数据,然后结束查询.实际上MySQL会查询出全部的结果集,客户端的应用程序会接收全部的结果集,然后抛弃不需要的部分.最简单有效的解决办法就是在这样的查询后面加上LIMIT

2.多表关联时返回全部的列

只取自己需要的列即可

3.总是取出全部的列

同上,只取出自己需要的列.取出全部的列,会让优化器无法完成索引覆盖扫描这类优化,还会为服务器带来额外的I/O,内存和CPU的消耗.

如果应用程序使用了某种缓存机制，或者有其他考虑，获取超过需要的数据也可能有其好处，但不要忘记这样做的代价是什么。获取并缓存所有的列的查询，相比多个独立的只获取部分列的查询可能就更有好处。

重复查询相同的数据

不断地重复执行相同的查询，然后每次都返回完全相同的数据.比较好的方案是，当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能显然会更好。

MySQL是否在扫描额外的记录

对于MySQL,最简单的衡量查询开销的三个指标如下**:响应时间,扫描的行数,返回的行数.**

响应时间

响应时间只是一个表面上的值,是两个部分之和:服务时间和排队时间.

服务时间是指数据库处理这个查询真正花了多少时间.

排队时间是指服务器因为等待某些资源而没有真正执行查询的时间--可能是等I/O操作完成,也可能使等待行锁.

所以在不同类型的应用压力下,响应时间并没有什么一致的规律或者公式.诸如存储引擎的锁,高并发资源竞争,硬件响应时间等诸多因素都会影响响应时间.所以,响应时间既可能是一个问题的结果也可能使一个问题的原因.

扫描的行数和返回的行数

分析查询时,查看该查询扫描的行数是非常有帮助的.

对于找出那些“糟糕”的查询，这个指标可能还不够完美，因为并不是所有的行的访问代价都是相同的。较短的行的访问速度更快，内存中的行也比磁盘中的行的访问速度要快得多。

理想情况下,扫描的行数和返回的行数应该是相同的.但实际情况中这种好事并不多.例如,在做一个关联查询时,服务器必须要扫描多行才能生成结果集中的一行.

扫描的行数和访问类型

在评估查询开销的时候，需要考虑一下从表中找到某一行数据的成本,**在EXPLAIN语句中的type列反应了访问类型。**访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。

如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引.

一般MySQL会使用如下三种方式应用where条件,从好到坏依次为:

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

2.使用索引覆盖扫描(在Extra列中出现了Using index)来返回记录,直接从索引中过滤不需要的记录并返回命中的结果.这是在MySQL服务器完成的,但无须再回表查询记录.

3.从数据表中返回数据,然后过滤不满足条件的记录(在Extra列中出现Using Where).这在MySQL服务器完成,MySQL需要先从数据表读出记录然后过滤.

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

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

2.该表库表结构.例如使用单独的汇总表.

3.重写这个复杂的查询,让MySQL优化器能够以更优化的方式执行这个查询.

重构查询的方式

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

MySQL从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效.在其他条件都相同的时候,使用尽可能少的查询当然是更好的,但是有时候,将一个大查询分解为多个小查询是很有必要的.

切分查询

有时候对于一个大查询我们需要分而为之,将大查询切分成小查询,每个查询功能完全一样.,只完成一小部分,每次只返回一小部分查询结果(分页?).

分解关联查询

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

优势:

1.让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。另外，对MySQL的查询缓存来说，如果关联中的某个表发生了变化，那么就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了。

2.将查询分解后,执行单个查询可以减少锁的竞争.

3.在应用层做关联,可以更容易对数据库做拆分,更容易做到高性能和可扩展.

4.查询本身的效率也会有所提升.

5.可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消耗。

6.更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多.

查询执行的基础