将一条表关联的长SQL拆分为多条小SQL

2018-04-21 21:21:25 字数 2497 阅读 929 评论 0

问题:需要把一个复杂的查询分成多个简单的查询?

由于公司有高并发应用场景,所以需要将一条表关联的长SQL拆分为多条小SQL。在相当长的一段时间里,我一直顾虑这样做会产生额外的网络通讯、以及查询解析和优化是一件代价很高的事。 后来终于在《高性能MySQL》一书中得到了答案。

MySQL内部每秒能够扫描上百万行数据,相比之下,MySQL响应数据给客户端就慢多了,在其它条件相同的时候,使用尽可能少的查询当然是更好的。但有时将一个大查询分解为多个小查询是很有必要的。

MySQL从设计上让连接和断开连接都很轻量级,再返回一个小的查询结果方面很高效。现代的网络速度比以前要快很多,无论是带宽还是延迟。在某些版本的MySQL上,即使是一个千兆网卡也能轻松满足每秒超过2000次的查询,所以运行多个小查询现在已经不是大问题了。

切分查询

有时候对于一个大查询我们需要“分而治之”,将大查询切分成小查询,每个查询功能完全一样,只完成一小部分,每次只返回一小部分查询结果。 删除旧的数据库就是一个很好的例子。定期地清除大量数据时,如果用一个大的语句一次性完成的话,则可能需要一次性锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的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")  
}while rows_affected > 0  

# MySQL DATE_SUB()函数从日期减去指定的时间间隔。
# 语法:DATE_SUB(date,INTERVAL expr type)

# date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

# type参数请参考网址:MySQL DATE_SUB() 函数

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

分解关联查询

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

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的嵌套循环关联。某些场景哈希关联的效率要高很多。

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

总结

结尾,我简单归纳下实际工作中拆写小SQL的一些优缺点:

大SQL:

  • 优点:一目了然,程序代码简洁,只需要建立一个连接,在数据量小的情况下效率会高很多。

  • 缺点:在数据量大的情况下响应慢,可能一条大SQL就把整个数据库堵死。垂直拆分情况下sql改动会很大。

小SQL:

  • 优点:在数据量大的情况下效率高。对垂直拆分友好,索引优化方便,容易添加缓存优化。
  • 缺点:程序代码会变得异常啰嗦,难以维护,数据量小的时候,效率较低。
标签: MySQL

评论(0)

请先登陆,参与评论。

    聚合标签