高性能的MySQL（6）查询慢与重构查询

只有好的库表结构、合理的索引还不够，我们还需要合理的设计查询，齐头并进，一个不少才能充分发挥MySQL的优势。

一、查询为什么会慢？

每一个查询由一系列的子任务组成，每个子任务都会消耗一定的时间。这个我们在之前的单个查询分析时已经简单介绍了，当然还有额外的因素，比方说包括网络，CPU计算，统计信息，执行计划，锁等待等操作，或者底层引擎在调用内存，CPU操作，I/O操作等上的消耗时间。、

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

查询性能低下的最基本原因是访问的数据太多，大部分的性能低下的查询可以通过减少访问的数据量进行优化，一般有2个简单的分析方法：

1、确认应用程序是否返回了大量超过需要的数据，这就是说访问了太多的行，也有时候是因为访问了太多的列，这会增加很多额外的开销，包括，网络，CPU，内存等。

一些常见的例子：

a、查询不需要的记录

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

b、多表关联时返回全部列

比如，要查找所有在电影hreo中出现的演员，不要这样去写

1

2

3

4

<code>select</code> <code>* </code><code>from</code> <code>actor</code>

<code>inner</code> <code>join</code> <code>film_actor using(actor_id)</code>

<code>inner</code> <code>join</code> <code>film using(film_id)</code>

<code>where</code> <code>film.title = </code><code>"hreo"</code><code>;</code>

这会返回三个表的全部列，应该只返回需要的列

<code>select</code> <code>actor.* </code><code>from</code> <code>actor</code>

c、不要总是取出全部的列

<code>select</code> <code>* </code><code>from</code> <code>actor....</code>

d、重复查询相同的数据

例如在用户评论的地方需要查询用户的头像，如果用户多次评论，可能会反复查询这个数据，可以先缓存起来，这样会更好。

2、确认MySQL服务层是否在返回前检索大量超过需要的数据行。

如果查询为了返回结果扫描过多的数据，那么就不合适了，一般看3个指标：

a、响应时间

响应时间分为服务时间和排队时间。这个很难细分，如果是在一个合理的值，那就可以接受。

b、扫描的行和返回的行

这个在一定程度上能够说明该查询找到需要的数据效率怎么样。理想的情况下，扫描的行和返回的行是相同的，不过实际中这是很难的，特别是做关联查询时。

c、扫描的行和访问类型

在explain语句中的type列反应了访问类型。从全表扫描到索引扫描，范围扫描，唯一索引查询，常数引用等，速度从慢到快，扫描的行从大到小。一般我们增加一个合适的索引就可以很高效了。

5

6

7

8

9

10

11

12

<code>CREATE</code> <code>TABLE</code> <code>`emp5` (</code>

<code>  </code><code>`id` </code><code>int</code><code>(11) </code><code>NOT</code> <code>NULL</code> <code>DEFAULT</code> <code>'0'</code><code>,</code>

<code>  </code><code>`</code><code>name</code><code>` </code><code>varchar</code><code>(100) </code><code>NOT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`job` </code><code>varchar</code><code>(100) </code><code>NOT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`num1` </code><code>int</code><code>(10) </code><code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`num2` </code><code>int</code><code>(10) </code><code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`num3` </code><code>int</code><code>(10) </code><code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`job_num` </code><code>int</code><code>(10) </code><code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`d` </code><code>date</code> <code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>PRIMARY</code> <code>KEY</code> <code>(`id`),</code>

<code>  </code><code>KEY</code> <code>`job_num` (`job_num`)</code>

<code>) ENGINE=MyISAM </code><code>DEFAULT</code> <code>CHARSET=utf8;</code>

<a href="http://blog.51cto.com/attachment/201310/174912329.png" target="_blank"></a>

这里说明使用了索引类型，如果去掉了索引

<a href="http://blog.51cto.com/attachment/201310/175032172.png" target="_blank"></a>

访问类型为全表扫描（ALL）

一般MySQL能够使用如下三种方式应用where,从好到坏依次是

a、在索引中使用where过滤不匹配的数据，引擎层完成。

b、使用索引覆盖扫描，在Extra中出现了Using index,直接从索引中过滤掉不需要的记录，服务层完成，不需回表取数据。

c、从数据表返回数据，然后过滤，在Extra中出现了Using where ，在服务层完成。

二、重构查询

一个复杂查询还是多个简单查询，是否需要将一个复杂的查询分成多个简单的查询，这是一个需要好好衡量的问题了。

1、切分查询

删除数据就是一个很好的例子。定期的清楚大量的数据，可能需要锁住大量的数据，占满整个事务，耗尽资源，阻塞很多小的查询，切分是一个很好的办法。

比方说：把如下的句子

<code>delete</code> <code>from</code> <code>message </code><code>where</code> <code>create_time &lt; date_sub(now(),interval 3 </code><code>month</code><code>);</code>

换成如下：

<code>rows_affected=0</code>

<code>do{</code>

<code>    </code><code>rows_affected = do_query(</code>

<code>    </code><code>"delete from message where create_time &lt; date_sub(now(),interval 3 month limit 10000"</code><code>)</code>

<code>    </code><code>)</code>

<code>}while rows_affected &gt; 0</code>

一次删除10000行，影响就会很小，压力就会分担开来了。

2、分解关联查询

把关联查询进行分解，例如下面的查询：

<code>select</code> <code>* </code><code>from</code> <code>tag</code>

<code>join</code> <code>tag_post </code><code>on</code> <code>tag_post.tag_id = tag_id</code>

<code>join</code> <code>post </code><code>on</code> <code>tag_post.post_id = post.id</code>

<code>where</code> <code>tag.tag = </code><code>'hreo'</code><code>;</code>

分解为：

<code>select</code> <code>* </code><code>from</code> <code>tag </code><code>where</code> <code>tag = </code><code>'hreo'</code><code>;</code>

<code>select</code> <code>* </code><code>from</code> <code>tag_post </code><code>where</code> <code>tag_id=1234;</code>

<code>select</code> <code>* </code><code>from</code> <code>post </code><code>where</code> <code>id </code><code>in</code> <code>(123,546,432);</code>

乍一看，我们好像复杂化了，但是分解后还是有很多好处的，有的时候我们的却是需要这样做的 。

a、让缓存的效率更高，如果第一个查询的结果已经缓存了，那么就可以跳过第一个查询，另外对一MySQL的查询缓存Query Cache来说，如果关联的表发生了修改，就无法使用缓存了，拆分后，那么一个表的改变不会影响其他表的缓存。

b、单个查询可以减少锁的竞争。

c、在应用层做关联，有更好的扩展性。

d、可以减少冗余记录的查询，因为数据库关联查询时，可能需要重复的访问一部分数据。

e、这个相当于实现了哈希关联，而不是MySQL的嵌套循环关联，某些时候哈希关联效率高很多，这点以后会有介绍。

这个效果，在负载均衡，或者数据分布在不同的数据库是更明显。

本文转自shayang8851CTO博客，原文链接：http://blog.51cto.com/janephp/1314620，如需转载请自行联系原作者