[sql] 记一次查询优化

加入新的创业团队不知不觉已经2个月了,感觉这下子,自己才正式成为程序猿,接受行业的洗礼了,尤其在创业团队,这一切似乎又来得更猛烈了一点。加班什么的,已经成为家常便饭。不过好在大家用的技术是新的,对于更新的技术,心态也是开放的,同事的水平也是杠杠的。而之前的给电力三产的工作,则完全可以称之为是“传统行业”,过去一个月作的事情,没有现在一个星期干得多。过去给领导验收用的项目,如今是正儿八经有人在用了,感觉完全不一样。新东西也接触了很多,就是苦于业余时间太少,还没有太多的时间仔细研究。

言归正传,笔者还是一个蛮喜欢摆弄 sql 的人,毕竟 sql 功能强大,再配合许多内置的函数和语法,一段 sql 就已经能实现很多的功能了。而我的思路则是尽量用 sql 来解决的问题,因为数据库能把这些功能给到我们,终究比我们细分步骤,再来来回回折腾几趟效率要高吧。同时,我也非常不喜欢冗余,能算统计出来的尽量都用统计出来的。比方说如下功能:

SELECT  uid, 
        id,
        filename,
        ifnull(bill_count, 0) as bill_count,
        upload_on,
        status
FROM    excel
LEFT    JOIN
       (SELECT  count(id) bill_count, 
                eid 
        FROM    bill 
        GROUP   BY eid ) t
ON      excel.id = t.eid
HAVING  excel.uid = ?
AND     DATE(from_unixtime(upload_on)) = CURDATE() - INTERVAL ? DAY
AND     status != ?
ORDER   BY id DESC;

这是一段从 excel 文件导入数据的代码,概要放在 excel 表,而每行的具体内容放在 bill 表,中间用 excel.id = bill.eid 作关联。而这段 sql 就是根据查询 某用户项下、某日上传、某状态下的 excel 文件列表,并显示各个文件的记录数。

一开始其实跑得都还不错的。可是当网站运行了一阵子之后,bill 的数据已经达到 30 万行。与此同时,前端目前是采用轮训的方式来执行查询,轮询的间隔也较短(主要是在导入过程中让用户看到导入到什么进度)。当数量达到这个量级以后,突然发现网站竟然快跑不动了。同事就指出八成就是这个查询的问题。

explain 一下,更是不得了,原来每次对于最大的 bill 表都进行了全表扫描。小的表是无所谓,而表一大,立马慢下来了。为什么呢?

其实问题就在

       (SELECT  count(id) bill_count, 
                eid 
        FROM    bill 
        GROUP   BY eid ) t

这行,因为 bill 表里面可能完全没有数据,所有用 inner join(也就是默认联合查询)会出现数据遗漏的情况,所以就用 Left Join,而在左联之前,这个子查询又要先计算出来,所有就出现了全表搜索,因为这个查询里面是不带 where 的,而后面的 where 条件,只是对整体结果进行了裁剪,却没有直接对这个子查询发生作用。

改进倒也不难,改进后的 sql 语句是这样

SELECT  uid,
        id,
        filename,
        upload_on,
        status,
        ifnull(
           (SELECT  count(bill.id)
            FROM    bill
            WHERE   bill.eid = excel.id
            GROUP   BY bill.eid), 0) AS bill_count
FROM    excel
WHERE   excel.uid = ?
AND     DATE(from_unixtime(upload_on)) = CURDATE() - INTERVAL ? DAY
AND     status != ?
ORDER   BY id DESC;

将 bill_count 放入列中进行查询,而不是作为联合查询的一张表。这样后续的检索条件,就缩小了 count 的次数和范围

就这样罗,一个 0.139秒 的查询可以在 0.033 秒咯~效率提供4倍数 ^_^

关于aGuegu

阿古 真名:官微宏,技术Geek,玩Arduino,玩Linux,爱Google,爱开源;现居福州
此条目发表在db分类目录,贴了, , 标签。将固定链接加入收藏夹。
  • n3xtchen

    你试试把你原先的 having 改称 where,expain 下看看性能,(where是在查询前做条件限制,having是在查询后的结果集上做条件限制)!

    第二个语句的优化迟早会有问题,如果 excel id 变多了,你同样会遇到瓶颈,会不断的扫描 bill 表!

    DATE(from_unixtime(upload_on)) = CURDATE() – INTERVAL ? DAY
    这样的语句将用不到 upload_on 的索引,如果你有建索引的话,尽量不要对限制的字段用函数!