【MySQL】重构查询方式

骑猪看日落 2022-09-30 13:52 112阅读 0赞

> 摘自《高性能的MySQL》

在优化有问题的查询时，目标应该是找到一个更优的方法获得实际需要的结果–而不一定总要从MySQL获取一模一样的结果集。有时候可以查询转换一种写法让其返回一样的结果，但是性能更好。但也可以通过修改应用代码，用另一种方式完成查询，最终达到一样的目的。这里我们将介绍如何通过这种方式来重构查询，并展示何时需要使用这样的技巧。

# 用一个复杂的查询还是多个简单的查询。 #

设计查询的时候一个需要考虑的重要问题是：是否需要将一个复杂的查询分成多个简单的查询。在传统实现中，总是强调需要数据库层完成尽可能多的工作，这样做的逻辑在于以前总是认为网络通信，查询解析和优化是一件代价很高的事情。

但是这样的想法对于MySQL并不适用，MySQL从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面效率很高效。现代的网络速度比之前的要快很多，无论是带宽还是延迟。在某些版本的MySQL上，即使在一个通用服务器上，也能够运行每秒超过10W的查询，即使是一个千兆网卡也能轻松满足每秒超过2000次的查询。所以运行多个小查询现在已经不是很大的问题了。

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

# 切分查询 #

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

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

DELETE FROM message WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH);

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

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

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

# 分解关联查询 #

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

SELECT * FROM tag JOIN tag_post on tag_post.tag_id = tag.tag_id JOIN post ON tag_post.post_id = post.id WHERE tag.tag = 'mysql'

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

SELECT * FROM tag where tag = 'mysql';
    
    　　SELECT * FROM tag_post WHERE tagid=1234;
    
    　　SELECT * FROM post WHERE post_id in (123,234,435,342);

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

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

在很多场景下，通过重构查询将关联查询放到应用程序中将会更加高效，这样的场景有很多，比如：当应用能够方便的缓冲单个查询的结果的时候，可以将数据分布到不同的MySQL服务器上的时候，当能够使用in（）的方式代替关联查询的时候，当查询中使用同一个数据表的时候。