即日起在codingBlog上分享您的技术经验即可获得积分,积分可兑换现金哦。

拜托,在数据库中运行计算

微信 数据库开发 28℃ 0评论

(点击上方公众号,可快速关注)

英文:jooq

译者:伯乐在线 – unblock

链接:http://blog.jobbole.com/62924/

如果一个计算应该在数据库端运行,而却没有这么做,那将是大错特错。

我们并非提倡将所有业务逻辑盲目地移到数据库,但当我在 StackOverflow 看到一个这样的问题时,就有种冲动想让你看下这篇热文——  10 Common Mistakes Java Developers Make When Writing SQL ——中的第二点,链接是https://blog.jooq.org/2013/07/30/10-common-mistakes-java-developers-make-when-writing-sql/。

那个 stack overflow 上的问题,本质上来说是这样的:

在如下的中型表中,我想统计每个 App ID 中 status 为 0 或 1 的 doc 数

AppID | DocID | DocStatus

——+——-+———-

1     | 100   | 0

1     | 101   | 1    

2     | 200   | 0    

2     | 300   | 1

   |    |

我该使用 Hibernate 吗?

答案是:不!别用 hibernate 来做(除非是写 native query),应该用 SQL 来做 。S——Q——L !使用SQL Server的话,有很多简单的做法来实现这个查询,而且时间会比将全部数据不作聚集就装到 java 内存少很多。

以 SQL Server 来举个例:

使用 GROUP BY

这是最简单的做法,但它不一定完全符合你的要求。即是,子集合不在同一行。

SELECT [AppID], [DocStatus], count(*)

FROM [MyTable]

GROUP BY [AppID], [DocStatus]

如 SQLFiddle 的例子,会查出这样的东西:

| APPID | DOCSTATUS | COLUMN_2 |

|——-|———–|———-|

|     1 |         0 |        2 |

|     2 |         0 |        3 |

|     1 |         1 |        3 |

|     2 |         1 |        2 |

使用 nested select

这可能就是那个特殊用户所要的效果。他们可能想每个集合分别一列,而nest select就是最通用的实现手法之一。记住这种做法在某些数据库上是有点慢的,需要花些时间制定执行计划。

SELECT [AppID],

       (SELECT count(*) FROM [MyTable] [t2]

        WHERE [t1].[AppID] = [t2].[AppID]

        AND [DocStatus] = 0) [Status_0],

       (SELECT count(*) FROM [MyTable] [t2]

        WHERE [t1].[AppID] = [t2].[AppID]

        AND [DocStatus] = 1) [Status_1]

FROM [MyTable] [t1]

GROUP BY [AppID]

例如 SQLFiddle ,跑出这样的东西:

| APPID | STATUS_0 | STATUS_1 |

|——-|———-|———-|

|     1 |        2 |        3 |

|     2 |        3 |        2 |

使用 sum()

这可能是最佳的作法。它跟前面那个nest select一样,但只能用于简单的查询,而前者更灵活。

SELECT [AppID],

       SUM(IIF([DocStatus] = 0, 1, 0)) [Status_0],

       SUM(IIF([DocStatus] = 1, 1, 0)) [Status_1]

FROM [MyTable] [t1]

GROUP BY [AppID]

例如 SQLFiddle ,结果如上。

使用 PIVOT

这种方案是为 SQL 迷而准备的。它使用了 T-SQL 的 PIVOT 子句!

SELECT [AppID], [0], [1]

FROM (

    SELECT [AppID], [DocStatus]

    FROM [MyTable]

) [t]

PIVOT (

    count([DocStatus])

    FOR [DocStatus]

    IN ([0], [1])

) [pvt]

SQL 迷就该用 PIVOT !

例如 SQLFiddle ,结果亦如上。

结论

以上建议任君选择,而且我也相信还有更多方案。它们都比基于java的聚合好几个数量级,即使是在少量数据的情况下。我们将多次重申这个观点。

正如 Gavin King 所说的:

你在使用 Hibernate,并不就意味着你把它套到所有事情上。这是我说了十几年的话了。

转为我们的说法就是:

该用 SQL 的时候就用 SQL ,它的适应性超乎你想象。

—————— 推荐 ———-——–


范品社推出了十几款程序员、电影、美剧和物理题材的极客T恤单件 ¥59.9、两件减¥12、四件减¥28,详见网店商品页介绍。


网店地址:https://fanpinshe.taobao.com/

淘口令:复制以下红色内容,然后打开手淘即可购买

范品社,使用¥极客T恤¥抢先预览(长按复制整段文案,打开手机淘宝即可进入活动内容)

转载请注明:CodingBlog » 拜托,在数据库中运行计算

喜欢 (0)or分享 (0)
发表我的评论
取消评论

*

表情
(6)个小伙伴在吐槽
  1. 神马啊!见过很多写sql动辄几百行,那真是讲业务逻辑写到sql里了。在sql里,做神马内存表、行列置换、各种分支判断,优化业务逻辑、扩展需求都是坑。像本文列举的例子,如果真写到程序里,那真就走向极端了。
    无涯2016-08-30 14:33 回复
  2. 楼上所说的及是,多表关联,数据量大的,能取出市数据都是问题了
    陈志强2016-08-30 14:52 回复
  3. 没有看到效果的对比 缺少一些支撑 我打算谨慎观望下
    lucia🌾2016-08-30 15:25 回复
  4. 没有什么坑不坑,只有懂不懂哪样是哪样技术的优势,没有银弹
    CX2016-08-30 16:00 回复
  5. 我就是能写到sql就写的,几十层嵌套循环也是家常便饭,千行存储过程也不在少数,然而并不影响业务逻辑更改扩展啥的,资源省多了。
    邵明2016-08-30 21:34 回复
  6. 在大多数情况下 数据库就是简单的存取,越简单越好
    雨生于霜后2016-08-31 04:34 回复