重构sql查询方式
一 一个复杂的关联查询 VS 多个简单查询
如果这个复杂查询的所需要的扫描的行数本来就很小,换言之,直接使用复杂查询MySQL很容易完成,则不必多说,直接用一个复杂的即可。这里之所以讨论是否分解为多个简单查询,就是因为在复杂查询涉及的表大,扫描行多时,看能否进行性能上的优化。
先看一个例子:
单个复杂查询:
多个简单查询:
当然,这里注意,从第二行开始的每个简单查询的where条件都是之前得到的,涉及到具体操作时,可以在应用程序(代码中)分为多个步骤去查询,每次存储本次查询结果,再带着此结果进行下一次sql。并不建议做MySQL的缓存,因为关联的任意表发生变化,MySQL缓存都会失效。
那么分解关联查询有哪些好处呢?
① 单个查询减少锁的竞争(读期间不能写);
② 在应用层做缓存可以重复利用,提高性能。
二 切分查询
场景:定期删除大量数据(很常见的需求),不作限制的一次性sql会一次锁住很多行数据(可能会跨越多张表),占满整
个事务日志,耗尽系统资源,一直执行当前的大数据删除操作,会阻塞住很多小的但重要的查询。
解决方案:每次DELETE LIMIT 10000,分多次删除(中间有间隔)。
这个策略其实和事务日志是一个道理 —— 延迟持久化,将一个非常耗时(占用锁)的任务的操作延迟进行,分摊在系统“空闲”时间内。当然,顺便提一句,DELETE操作本身也是被封装成事务延迟刷回内存的,更为具体是,将原有记录的删除版本号更新(不是真正地物理删除),虽然系统能够将用户傻瓜式的一次性删除操作分时进行,即这个大数据量删除的真正执行(硬盘物理层面)其实也还是分时进行的,但是这种过于依赖MySQL自身的优化(存储引擎级别的优化),作为一名合格的使用者,还是应该在了解了运作机制之后,手动最大限度在sql层面做优化,自己指定每次删除的时间,条数。