记一次数据库查询语句的优化


周六的时候,运维告诉我一个sql慢查询一直报警,他把sql语句给到我,让我优化下。

select 
  很多字段
from ad_order
where state = 1
and deleteflag=0
and commentflag = -1
and ordertype in (0,3)
and (confirmtime + 30*60*1000)  <= 1502519143335


这条没有携带关联的查询,居然耗时达到4s。

看下这个sql查询,我看到了四处错误

数据库做了无关的事情


比如 confirmtime + 30*60*1000 <= 1502519143335,这个首先可以
confirmtime <= (1502519143335-30*60*1000) ,进而把加减计算放到php或者java里面去,而不是让数据库来算

表达式让索引失效


还是上面的例子,如果confirmtime字段是一个索引字段,那么上面的表达式会导致索引没有办法发挥效果,从而拖慢查询进度

in的使用


where语句中有一个 ordertype in (0,3),这里有必要说一下业务,ordertype是订单类型,最开始我们的设计 会员水吧订单(0),会员网费订单(1),会员钱包订单(2),服务员水吧订单(3),服务员网费订单(4),服务员钱包订单(5)。这个设计不好,可能当时是第一次做订单系统,踩了这个坑。其实想下,会员水吧订单和服务员水吧订单应该是同一个订单类型,只是来源不一样而已,于是我们把订单类型ordertype优化了之剩下三个,同时添加了一个订单属性叫做订单来源,分别来自会员(0),来自服务员(1),来自老板(2)。

所以这里的in也可以优化掉,那就是ordertype=0就好了

优化组合的使用


数据库表ad_order 已经有组合索引 KEY state type (state,ordertype,deleteflag), 在这个查询中,这个组合索引没起作用,
是因为我们的where的顺序存在问题。
于是我们调整为
where state=1
and ordertype=0
and deleteflag=0
and commentflag=-1
and confirmtime <= (1502519143335 - 30*60*1000)

优化完毕

posted @ 2017-08-13 09:42 archy_yu 阅读(...) 评论(...) 编辑 收藏