数据库优化的问题

 最近发现其实sql的优化真的非常非常重要，有时写出来的语句能得到正确的查询结果并非是最重要的，以前按照我自己的理解是只要能写出正确的sql查询基本就差不多了，但是现在发现我的这个想法太幼稚了。有时候sql的优化比结果更重要，因为当你的数据量较大的情况下，你的一个查询语句没有写好,造成了数据库查询卡死，最后导致整个程序挂掉。往往在数据库开始庞大的时候对于这一点才会有更加深刻的认识。接下来我就说说最近遇到的问题。

         首先每次我在写完sql的时候会在测试库里跑一遍，检查查询数据的正确性，因为测试库的数据量相对于正式线上真实的数据量要小的多，所以看不到数据库执行查询所消耗的时间，一般执行完的时间基本都是秒查的内种，然后就commit提交了，昨天上午，我们组长把我叫过去，说你看看你写的sql，把服务器给堵死了，服务器的cpu一下飙到了90%多，我一看，我去，还真是。。。当然，堵的原因就是因为我的那条sql语句，我当时就在想，之前我执行的时候速度蛮快的，所以没想到在正式上会造成这么严重的问题，于是乎我马上下去找到我开始写的那条sql进行优化。

        下面我就展示下当时的那条sql，当时的需求就是查询会员卡号，会员名称，会员可使用的优惠券数量和会员已使用的优惠券数量，sql如下：

SELECT 
vip.vid AS vipid,
vip.vipname,
(SELECT COUNT(v.vid) FROM ls_bs_vipinfo v LEFT JOIN ls_bs_coupon c ON c.vipid=v.vid WHERE c.ctype='0' AND c.vipid=vip.vid)AS vipremaincoupons,
(SELECT COUNT(v.vid) FROM ls_bs_vipinfo v LEFT JOIN ls_bs_coupon c ON c.vipid=v.vid WHERE c.ctype='1' AND c.vipid=vip.vid)AS vipalreadycoupons
FROM ls_bs_vipinfo vip left join ls_bs_coupon cou on cou.vipid=vip.vid where vip.eid=#{p.eid}  

                GROUP BY vip.vid

              下面是结果和执行的时间：

                

               

              这是当时在测试库上面跑出来的结果以及执行所消耗的时间，基本属于秒查的内种，但是由于在正式上面数据量非常庞大，所以我这样在字段上加子查询会造成临时表全表扫描，由于用到了2个字段的子查询，则执行消耗时间*2，如果是在数据量大的情况下基本就是卡死

             于是我下去重新开始对这条sql进行优化，因为优惠券关联会员那张表的字段上有一个状态，0代表未使用，1代表已使用，所以我就开始尝试使用case when 重新写出了下面这条sql：

SELECT v.vid AS vipid,
v.vipname,
COUNT(CASE cou.CTYPE WHEN '0' THEN 'vipremaincoupons' END)AS vipremaincoupons,
COUNT(CASE cou.CTYPE WHEN '1' THEN 'vipalreadycoupons' END)AS vipalreadycoupons  
FROM ls_bs_vipinfo v 
LEFT JOIN ls_bs_coupon cou ON cou.vipid=v.vid 
WHERE v.eid=#{p.eid}

               GROUP BY v.vid

              这条sql一执行，把我自己都吓一跳：以下是结果和执行的时间：

              

              

            第一条sql的执行是096毫秒，第二条执行的是016毫秒，快了近6倍多，我当时心想这是测试库，在数据量小的情况下差距居然这么大，那数据量庞大的话差距会更明显，我马上就把第二条写好的sql提交，对我们组长说我写好了，组长把正式的数据库给我，说你在正式上面跑一下吧，我就兴奋的把我的第二条sql拿到正式上面去跑，结果可想而知，20分钟都没有查询出来，你没有听错，20分钟都没有查询出结果，这简直出乎了我的意料，一时也想不起更好的优化方法了，所以我就想先看下到底表里有多少数据，一个简单的查询居然会卡成翔

        select count(*) from ls_bs_vipinfo    99550

       select count(*) from ls_bs_coupon   77547

     

     最后分析 这2张表的数据总和超过15W的数据，而一个非常普通的单表查询都需要近5秒左右，而我使用的关联和子查询等操作，相当于在没有主键和索引的情况下，数据库引擎会进行全表扫描，这样的结果对于数据库来说就是致命的，因为查询的卡死最终会造成无法响应，程序堵死等情况，所以最后只能使用一种方式，那就是拆，拆开查询，然后通过程序进行拼接，sql如下：

      SELECT vip.vid as vipid,vip.vipname,COUNT(cou.CID) as vipremaincoupons FROM ls_bs_vipinfo vip 
      LEFT JOIN ls_bs_coupon cou ON cou.vipid=vip.vid 
      WHERE vip.eid=#{p.eid} AND cou.CTYPE= #{p.ctype}  GROUP BY vip.vid

      传了一个变量ctype进去，先查出已使用的，因为我们有分页插件，所以在执行sql的时候会在末尾自动加入limit 。然后通过程序在把未使用的查出来，最后合并 

     

        

        先查出他的已使用数量，然后再次查询未使用的，通过在实现层里面进行拼接，以上是在正式库执行查询出剩余优惠券的条目，然后在下面在调用一次查询 查询已使用的条目，在for里进行拼接，当然这里的循环并不是循环查询数据库。。。

       

     

            通过这次的事情，让我明白有时候并不是说所有的事情都交给sql完成是最好的，在最开始的时候我往往会在程序上做判断，但是有的时候可以把条件交给数据库来处理会更快，但这一次显然并不是这样的，仅仅15W的数据，一个查询没有写好，就会出现这样的情况，所以有时候需要权衡，到底是哪种方式能更高效的执行，就使用那一种，这也是通过自己总结出来的最佳实践！