挑战SQL极限——SQL优化（大数据量）

[MyAngel]

最近在做数据分析，遇到了SQL查询优化的问题，想请教大家：

【场景】
（1）三张表：Budget（2.5万条记录） LinkTable（15万条记录） Sales（30万条记录）
（2）用SQL语句对着三张表进行一个关联汇总查询时，竟然需要35-40秒钟
（3）对三张表的关联字段和汇总字段建立索引后，查询仍然需要10秒钟

【目的】
希望能将查询时间控制在1秒钟内，也就是希望通过对现有SQL语句的优化（或其它办法）将现有的查询效率提高10倍。

【SQL查询语句】

SELECT [Year],
MIN((CASE WHEN (Year = 2009 OR Year = 2009-1) THEN Sales / EUR END)) AS [Sales],
Sum(Budget_EUR * _Projected) AS [Projected]
FROM (SELECT [A].[Year], [A].[Sales], [A].[EUR], [Budget].[Budget_EUR], [Budget].[_Projected],
[A].[SalesKey], [A].[BudgetKey]
FROM ((SELECT [LinkTable].[Year], [Sales].[Sales], [Sales].[EUR], [LinkTable].[SalesKey],
[LinkTable].[BudgetKey]
FROM ([LinkTable] LEFT JOIN [Sales] ON [LinkTable].[SalesKey] = [Sales].[SalesKey]))
AS A LEFT JOIN [Budget] ON [A].[BudgetKey] = [Budget].[BudgetKey]))
GROUP BY [Year]

请大家检查一下以上SQL语句有哪些地方可以优化，谢谢。

【数据库结构】

CREATE TABLE [Budget] ([BudgetKey] VARCHAR(21) NULL, [_Projected] INTEGER, [Budget_SEK] INTEGER, [Budget_GBP] INTEGER, [Budget_JPY] INTEGER, [Budget_EUR] INTEGER, [Budget_USD] REAL, [Projected_SEK] INTEGER, [Projected_GBP] INTEGER, [Projected_JPY] INTEGER, [Projected_EUR] INTEGER);

CREATE TABLE [LinkTable] ([SalesKey] VARCHAR(21) NULL, [Year] INTEGER, [Month] VARCHAR(23) NULL, [MonthYear] VARCHAR(25) NULL, [_History] INTEGER, [Quarter] VARCHAR(27) NULL, [QtrYear] VARCHAR(29) NULL, [Week] INTEGER, [Weekstart] VARCHAR(31) NULL, [Weekend] VARCHAR(33) NULL, [Customer Number] VARCHAR(35) NULL, [BudgetKey] VARCHAR(37) NULL);

CREATE TABLE [Sales] ([Date] VARCHAR(12) NULL, [Address Number] VARCHAR(14) NULL, [Sales Rep Number] INTEGER, [Item Number] INTEGER, [Invoice Date] VARCHAR(16) NULL, [Promised Delivery Date] VARCHAR(18) NULL, [Invoice Number] INTEGER, [Order Number] INTEGER, [Item Desc] VARCHAR(39) NULL, [Sales Qty] REAL, [Open Qty] INTEGER, [OpenOrder] REAL, [GrossSales] REAL, [Sales] REAL, [BackOrder] REAL, [Cost] REAL, [Margin] REAL, [SEK] VARCHAR(41) NULL, [GBP] VARCHAR(43) NULL, [AUD] VARCHAR(45) NULL, [JPY] VARCHAR(47) NULL, [EUR] VARCHAR(49) NULL, [USD] INTEGER, [SalesKey] VARCHAR(51) NULL, [# of Days Late] INTEGER, [# of Days to Ship] INTEGER);

请大家多多指教 ！
很多年没有来这个论坛了，不知以前SQLServer版的大佬们还在不在？

[MyAngel]

收到测试数据的网友，有没有什么测试结果呀

引用 113 楼 MingleLui 的回复:

发到我的邮箱[e-mail]。我来帮忙测试下看看。

收到测试数据的网友，有没有什么测试结果呀 ？

[oreoconansisu]

引用 102 楼 MyAngel 的回复:

[quote=引用 95 楼 oreoconansisu 的回复:] 没上楼主那么大的数据做测试 我提供2个思路 楼主测试看效率如何 1.改变查询表的顺序 Budget（2.5万条记录） LinkTable（15万条记录） Sales（30万条记录） LinkTable作为关联表 每次都是第一个select的对象 可以试下从Budget->LinkTable->Sales(数据量由小到大) 或从Sales->LinkTable->Budget(数据量由大到小) 我觉得表数据量对查询先后顺序可能会有影响 还是要通过楼主测试 2.可将Group前的数据 先插入临时表#Temp 再对#Temp添加Year索引 最后查询出来 因为数据量比较大，我设想分2布 通过临时表效率可能会更高 我不是DBA，但也对大数据量的数据做过优化 提供2个思路给楼主 结果等楼主测试了 谢谢

非常感谢你提供的思路，我将按你的思路测试，并把测试结果反馈回来。 [/quote] 楼主有结果没

[MyAngel]

引用 124 楼 oreoconansisu 的回复:

[quote=引用 102 楼 MyAngel 的回复:] [quote=引用 95 楼 oreoconansisu 的回复:] 没上楼主那么大的数据做测试 我提供2个思路 楼主测试看效率如何 1.改变查询表的顺序 Budget（2.5万条记录） LinkTable（15万条记录） Sales（30万条记录） LinkTable作为关联表 每次都是第一个select的对象 可以试下从Budget->LinkTable->Sales(数据量由小到大) 或从Sales->LinkTable->Budget(数据量由大到小) 我觉得表数据量对查询先后顺序可能会有影响 还是要通过楼主测试 2.可将Group前的数据 先插入临时表#Temp 再对#Temp添加Year索引 最后查询出来 因为数据量比较大，我设想分2布 通过临时表效率可能会更高 我不是DBA，但也对大数据量的数据做过优化 提供2个思路给楼主 结果等楼主测试了 谢谢

非常感谢你提供的思路，我将按你的思路测试，并把测试结果反馈回来。 [/quote] 楼主有结果没[/quote] 还没有，这段时间我被一个临时的、紧急的项目给搞得焦头烂额，这个临时项目要到5月底才完工。所以我只有6月份才能将测试结果反馈回来，非常抱歉了。 希望其他收到测试数据的网友也能发一下测试结果，谢谢 ！

[niss]

唉,一堆代码,真心看不下去,我几张200W的表联合查询加分组聚合,1秒内

[hgwyl]

默默关注这个很多天了……帮忙顶一下……学习学习……

[MyAngel]

引用 128 楼 jack11430 的回复:

LZ的思路有问题，既然是数据分析，那么就不必在意数据冗余，把需要的字段直接建立一张表，然后把数据插入，再统计，大多数数据仓库做分析的时候都是这么搞得。

我一点也没有在意数据冗余，我只是要速度，即查询速度（分析速度）。你所说的【把需要的字段直接建立一张表，然后把数据插入，再统计】的确会提高分析速度，但问题是，SQL语句有很多，而且是动态变化的（用户自己设置的），我事先并不能知道需要哪些字段。

[最爱午夜]

这就涉及事实表的建立， 程序上面有什么功能，你总知道吧，这些功能需要查询那些数据你也知道吧，根据不同的需求建立不同的事实表，然后进行统计，这样就形成了数据仓库。

[刀剑分天下]

学习

[最爱午夜]

LZ的思路有问题，既然是数据分析，那么就不必在意数据冗余，把需要的字段直接建立一张表，然后把数据插入，再统计，大多数数据仓库做分析的时候都是这么搞得。

[software_artisan]

引用 122 楼 MyAngel 的回复:

[quote=引用 119 楼 xuanbg 的回复:]

SELECT [Year], 
MIN((CASE WHEN (Year = 2009 OR Year =  2009-1) THEN   Sales / EUR END)) AS [Sales], 
Sum(Budget_EUR * _Projected) AS [Projected] 
FROM (
  SELECT L.[Year], S.[Sales], S.[EUR], B.Budget_EUR, B._Projected
  FROM LinkTable L
  LEFT JOIN Sales S ON S.SalesKey = L.SalesKey
  LEFT JOIN Budget B ON B.[BudgetKey] = L.[BudgetKey]) T1
GROUP BY [Year]

简化了一下，去掉了不必要的字段。Sales表和LinkTable通过SalesKey关联，同时LinkTable表和Budget表通过BudgetKey关联，我不知道实际的数据需不需要left join，如果有两个表可以用join的话，换一下顺序，把能够join的放前面，性能应该会提升很多。还有，楼主这个sql完全没有条件，适当加条件也可以大大减少查询时间

表是不确定的，很难知道到底哪张表符合Join条件。 这个SQL是没有条件，因为用户要查询全部结果。如果说有条件，那就是：CASE WHEN (Year = 2009 OR Year = 2009-1) THEN Sales / EUR END) 这个也算是条件。 实际过程是需要 FULL JOIN，而不是LEFT JOIN，也就是说实际过程的查询效率更低，因此，SQL急需要优化。[/quote] 如果这样子的话，优化也无从谈起了。。。连表关系都不明确，数据特征更是不清不楚，谈何优化？优化之所以叫优化，就是因为需要确定的前提。如果可以套用通用模式，数据库直接就提供了，哪里还需要优化！

[duoxu1983]

3张表这么点数据量 直接本地建个sqlserver测试实例做下测试嘛。

[sunylf]

我想要一份你的数据库备份，[e-mail]

[MyAngel]

引用 134 楼 sunylf 的回复:

我想要一份你的数据库备份，[e-mail]

测试数据已经发到你的邮箱了，请查收！

[拥抱开源]

真心不错的帖子

[mclly2000]

不错的帖子，新手学习了

[MyAngel]

很抱歉，密码忘掉了，一直不能登录论坛，现在终于想起来了。 CSDN 总是提示要更改密码，我只好把用了N年的密码改了，谁知，一个星期不上网，密码忘掉了。一个月后，重新试了试，居然登录进来了。 这一段时间我会将测试结果放到网上，谢谢大家的参与 ！

[kobemadi]

简直是大辩论啊，学习了。

[T0M]

引用 4 楼 MyAngel 的回复:

对于这种大数据表（几十万条或几百万条记录）的关联查询，有没有什么更高效率的查询机制。 谢谢 ！

遇到过一个存储过程几十个聚集函数夹杂着几十个表格的反复左联内联，还有各种编程函数，数据量也很大。这种情况下根本不要想用SQL来做查询，一个查询至少几分钟。 我是用Cube + MDX解决的。

[xiaoxiangqing]

估计要更新硬件，优化语句同时进行

[MyAngel]

经过一段时间的测试，发现通过对SQL的优化只可以把速度提高10%左右，不可能把速度提高一个数量级（即提高10倍以上），而且SQL的过度优化还会降低SQL语句的扩展性（因为SQL语句是自动生成的），所以放弃了持续对SQL进行优化的思路。 谢谢各位网友的热情参与，现在结贴。