【学习笔记】查询性能优化：重构查询方式

来源：互联网发布：raphael js 流程图编辑：程序博客网时间：2024/05/16 15:40

本文属于读书笔记，大部分内容摘抄于《高性能MYSQL》，摘抄内容版权属于原作者。

在优化有问题的查询时，目标应该是找一个更优的方法获得实际需要的结果——而不一定总是需要从mysql获得一模一样的结果集。有时候可以将查询转换一种写法让其返回一样的记过的但是性能更好。但也可以通过修改应用代码，用另一种方式完成查询，最终达到一样的目的。

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

设计查询的时候一个需要考虑的重要问题是，是否需要将一个复杂的查询分成多个简单的查询。在传统实现中，总是强调需要数据库层完成尽可能多的工作，这样的逻辑在与以前总是认为网络通信、查询解析和优化是一件代价很高的事情。但是这样的想法对于mysql并不适用，mysql从设计上让链接和断开链接都是很轻量级，在返回一个小的查询结果方面很搞笑。现代的网络熟读比以前要快很多，无论是贷款还是延迟。在某些版本的mysql上，即使在一个通用服务器上，也能够运行美妙超过10万的查询，即使在一个千兆网卡也能够轻松满足每秒超过2000次的查询。所以运行多个小的查询现在已经不是大问题了。

mysql内部能够扫描内存中上百万数据，相比之下，mysql响应数据给客户端就慢得多了。在其他条件都相同的时候，使用尽可能少的查询当然是更好。但是有时候，将一个大查询分解为多个肖查询是很有必要的。别害怕这样做，好好很亮一下这样做是不是会减少工作量。

切分查询

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

删除旧的数据就是一个很好的例子。定期的清楚大量数据时，如果用一个大于局一次性完成，则可能需要一次锁住很多数据、占满整个事务日志、好近系统资源、阻塞很多小但是很重要的查询。将一个大的delete语句切分成多个较小的查询可以尽可能小弟影响mysql性能，同时还可以减少mysql复制的延迟。例如，我们需要每个月运行一次下面的查询：

mysql>DELETE FROM messages WHERE created<DATE_SUB(NOW(),INTERVAL 3 MONTH);

那么可以用类似下面的办法来完成工作：

rows_affected=0
do {
    rows_affected = do_query(
        "DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH)     
        LIMIT 10000"
} where rows_affected > 0

一次删除一万行数据一般来说是一个比较高效而且对服务器影响也最小的做法（如果是事务型引擎，很多时候小事务能够更高效）。同事需要注意的是，如果每次删除数据后都站定一会再做一下次删除，这样可以将服务器上原本一次性的压力分散到一个很长的时间段中，就可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间。

分解关联查询

很多高性能的应用都会对关联查询进行分解。简单地说，可以对每一个表进行一次单标查询，然后将结果在应用程序中进行关联。例如，西面这个查询：

mysql> 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"

可以分解成下面这些查询来替代：

mysql> SELECT * FROM tag WHERE tag="mysql";
mysql> SELECT * FROM tag_post WHERE tag_id=1234;
mysql> SELECT * FROM tag_post WHERE post.id in (123,456,789,9098.8904);

到底为什么要这么做呢？乍一看，这样做并没有什么好处，原本一条查询，这里却变成了多条查询，返回结果又是一模一样。事实上，用分解关联查询的方式重构查询有如下的优势：

让缓存的效率更高。许多应用程序可以方便的缓存单标查询对应的记过对象。例如，上面查询中的tag已经被缓存了，那么应用就可以跳过第一个查询。再例如，应用已经缓存了id为123,456,789， 9098的内容，那么第三个查询的IN()就可以少几个id。另外对mysql的查询缓存来说，如果关联中的某个表发生了变化，那么就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了。
将查询分解后，秩序你单个查询可以减少锁的竞争。
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展性。
查询本身的效率也可能会有所提升。这个例子中，使用IN()代替广联查询，可以让mysql按照id顺序进行查询，这可能比随机关联更搞笑。
可以减少冗余记录的查询。在应用层做关联查询，因为这对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消耗。
更进一步，这样做相当于在应用中实现了哈希关联，而不是使用mysql的嵌套循环关联。某些场景哈希关联的效率要高很多。

0 0