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

Question

最近在做数据分析，遇到了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版的大佬们还在不在？SQL性能优化数据库海量数据查询优化

Answer 1

这个多个subselect还有left join,没兴趣看了
一个语句的化,性能估计也差不多了.可以考虑使用零时表,或者表变量处理.

Answer 2

（1）请高手们首先看看这个SQL语句有没有优化的空间，即有没有更高效的写法。（2）对于这种大数据表（几十万条或几百万条记录）的关联查询，有没有什么更高效率的查询机制。谢谢 ！

Answer 3

To  luckyrandom: 
    使用了子查询是因为有很多张表（表的个数是不固定的）。发这个帖子在论坛只是和大家一起探讨探讨，不是有偿的。 我很多年没有来CSDN了，现在论坛讨论问题都需要付费吗？
   

Answer 4

也许付费能提供更好的服务我分析了一下你的SQL，你上面的SQL跟我下面重写的SQL有什么区别吗？    Select [LinkTable].[Year], 
       MIN((CASE WHEN ([LinkTable].[Year] = 2009 OR [LinkTable].[Year] =  2009-1) THEN   [Sales].Sales / [Sales].EUR END)) AS [Sales], 
       Sum([Budget].Budget_EUR * [Budget].[_Projected]) AS [Projected] 
   FROM [LinkTable] LEFT JOIN [Sales]  ON [LinkTable].[SalesKey] = [Sales].[SalesKey]
                LEFT JOIN [Budget] ON [LinkTable].[BudgetKey]= [Budget].[BudgetKey]
       GROUP BY [Year]     我不知道你的INDEX怎么建的？  本来INDEXED VIEW效率最好，但是你的SQL用INDEXED VIEW比较困难，你看看能不能修改一下让其满足INDEXED VIEW的建立需求。

Answer 5

SQL Server是什么版本的？？用自带的索引分析的看一下缺少什么索引。

Answer 6

To SQL_Beginner:
    谢谢你的回复。 其实我的SQL是用程序【自动】建立的（因为表的个数是不固定的），所以可能没有手动写的那么高效。我会测试一下你的SQL的运行效率与我的SQL的运行效率的差别。    我目前只是分别对Year，SalesKey，BudgetKey这三个字段单独建立了索引。 你所说的INDEXED VIEW 是什么意思我还不太明白。To zbdzjx：
    我目前是在SQLite 上做测试,只有SQLite测试通过后，才会移植到SQLServer上测试。

Answer 7

楼主语法有错误哦，另外建议不要用SQL的保留字哦（如：year等）

Answer 8

--这个是我测试通过的，不过我还没有优化，楼主没有字段说明，还没有明白你的意思
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) A 
LEFT JOIN Budget ON A.BudgetKey = Budget.BudgetKey
) t1
group by [year]

Answer 9

--优化后如下：
SELECT t1.[Year],
MIN((CASE WHEN (t1.[Year] = 2009 OR t1.[Year] = 2008) THEN t2.Sales / t2.EUR END)) AS Sales, Sum(t3.Budget_EUR * t3._Projected) AS Projected
FROM LinkTable t1
LEFT JOIN Sales t2 ON t1.SalesKey = t2.SalesKey 
LEFT JOIN Budget t3 ON t1.BudgetKey = t3.BudgetKey
group by t1.[year]

Answer 10

如果特别在意查询时间，可以创建一个表A，然后在Budget、LinkTable、Sales上放触发器。每次INSERT/UPDATE/DELETE时，将要查询的结果，放入表A中。这样会增加对这三个表的操作时间，但是能节省查询时间。

Answer 11

如果是mssql2005或以上版本，几十万条的关联，只要有正确的索引，不是什么问题
sqlite，可能就比较麻烦了，它对这种关联的考虑可能比较少，而且内存占用量也不是一个数量级的

Answer 12

To SQL_Beginner:
   我测试了一下你的SQL语句，结果如下：
   （1）你的SQL语句比我的快0.5秒
   （2） 但是你的SQL语句与我的SQL其实是不同的。我的SQL语句返回三条结果（Year = 2007, 2008, 2009),而你的SQL语句只返回一条结果（Year = 2009）
 

Answer 13

To rockyljt：
   （1）你说得对，我的SQL语句在SQLServer 上运行时，需要为最外一层语句加上表别名，但SQLite上不需要。    （2）你所优化后的SQL语句与SQL_Beginner网友的SQL语句类似，但经我的测试，你和SQL_Beginner网友的SQL语句的执行结果与我的SQL语句的返回结果是不同的。
     不过，我目前在SQLServer上简单的输入几条数据，测试又是相同的，待我用真实的数据在SQLite和SQLServer上同时测试后，再把结果告诉大家。     （3）表名不要用SQLServer的关键字，这是很好的建议，不过这些表名是国外用户取的，有的数据表是直接从Excel里倒入的，所以难以规定用户的命名规范。     （4） 你用t1,t2,...tn 作为表的别名的写法比我用A, B，C...要更清晰，值得借鉴，谢谢。

Answer 14

To misterliwei：
   这是对别人的数据库中的数据做分析，所以暂时没办法使用你的办法，但将查询结果保存在一张临时表中也是一种思路，谢谢。

Answer 15

   是的，如果是SQLServer2005或2008会好办一点，但我必须现在SQLite上测试通过才会考虑往SQLServer上移植。

Answer 16

把你的代码格式化一下：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]

  )A 
  LEFT JOIN [Budget] 
         ON [A].[BudgetKey] = [Budget].[BudgetKey])
)A
GROUP BY [Year] 

Answer 17

你可以尝试增加查询提示，比如merge或者hash，比如：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 hash JOIN [Sales] 
ON [LinkTable].[SalesKey] = [Sales].[SalesKey]

  )A 
  LEFT hash JOIN [Budget] 
         ON [A].[BudgetKey] = [Budget].[BudgetKey]
         
)A
GROUP BY [Year]  

Answer 18

经过你的格式化后，SQL语句清晰了很多，非常感谢。你所说的【增加查询提示，比如merge或者hash】这个我还没有用过，我会试一试的。你建议表最好设置一个聚集索引，可以定义一个主键，这个也可以考虑。因为数据表是国外客户自己定义的，我们不方便修改，但我还是会测试一下，如果建立主键会不会进一步的提高查询速度（因为我已经对各个表的关联字段和GroupBy字段建立索引了）

Answer 19

To rockyljt 和 SQL_Beginner:    你们优化后的SQL语句与我的语句在SQLite上的结果有些不同，但这可能是SQLite本身的问题，我会想办法解决。我现在谈谈我为什么要在SQL语句中用多层子查询吧：
（1）我在数据分析的过程中并不是需要LEFT JOIN，而是需要 FULL JOIN，但SQLite 不支持FULL JOIN。因此我必须以很复杂的SQL 实现FULL JOIN，为了让SQL简单一些，使网友看得更清楚，所以我就用了LEFT JOIN 做为示例。 SQLite 的FULL JOIN的实现方法如下：SELECT employee.*, department.*
FROM   employee 
       LEFT JOIN department 
          ON employee.DepartmentID = department.DepartmentID
UNION ALL
SELECT employee.*, department.*
FROM   department
       LEFT JOIN employee
          ON employee.DepartmentID = department.DepartmentID
WHERE  employee.DepartmentID IS NULL这只是两张表的FULL JOIN语句，如果是三张表或更多的表的FULL JOIN，就更复杂了。请rockyljt 、 SQL_Beginner、yupeigu和其他高手看看，如果采用FULL JOIN的形式，我的问题中的SQL语句应该如何写，如何优化，多谢 ！（2）如果多张表有相同的字段，我需要将这几个相同的字段的值放在同一个字段中，例如：
     employee和salary表中都有字段EmpID，二者进行Full JOIN时的语句如下：SELECT employee.EmpID, employee.Name,Salary.Pension
FROM   employee 
       LEFT JOIN salary 
          ON employee.EmpID = Salary.EmpID
UNION ALL
SELECT salary.EmpID, employee.Name,Salary.Pension
FROM   salary
       LEFT JOIN employee
          ON employee.EmpID = salary.EmpID
WHERE  employee.EmpID IS NULL当需要与第三张表进行FULL JOIN时，EmpID字段就不能区分具体的表了，所以我就用了子查询：SELECT EmpID, Name,Pension 
FROM 
(SELECT employee.EmpID, employee.Name,Salary.Pension
FROM   employee 
       LEFT JOIN salary 
          ON employee.EmpID = Salary.EmpID
UNION ALL
SELECT salary.EmpID, employee.Name,Salary.Pension
FROM   salary
       LEFT JOIN employee
          ON employee.EmpID = salary.EmpID
WHERE  employee.EmpID IS NULL) A请大家看看对于这种多张表的Full JOIN的情况，有没有更优化的写法，或其它更好的解决办法呢？ 谢谢大家的帮助 ！

Answer 20

建议：
1）、把公共部分抽取出来，封装到临时表或视图中。然后对单个表操作！2）、LEFT JOIN 好像也是比较影响因能的。

Answer 21

你所说的【把公共部分抽取出来...】能否更详细一些，谢谢。另外，由于是要做数据分析，所以使用的是FULL JOIN ,其效率比LEFT JOIN还要低，但又没有更好的解决办法。

Answer 22

试试这个：
SELECT [LinkTable].[Year], min([Sales].[Sales]/[Sales].[EUR]) as [Sales],
sum([Budget].[Budget_EUR]*[Budget].[_Projected]) AS [Projected] ,
FROM [LinkTable] [LinkTable]
LEFT JOIN [Sales] [Sales] ON [LinkTable].[SalesKey] = [Sales].[SalesKey]
LEFT JOIN [Budget] [Budget] ON [LinkTable].[BudgetKey] = [Budget].[BudgetKey]
where [LinkTable].[Year]<=2009 and [LinkTable].[Year]>=2009-1
UNION ALL
SELECT [LinkTable].[Year],0 as [Sales],
sum([Budget].[Budget_EUR]*[Budget].[_Projected]) AS [Projected] ,
FROM [LinkTable] [LinkTable]
LEFT JOIN [Sales] [Sales] ON [LinkTable].[SalesKey] = [Sales].[SalesKey]
LEFT JOIN [Budget] [Budget] ON [LinkTable].[BudgetKey] = [Budget].[BudgetKey]
where [LinkTable].[Year]>2009 and [LinkTable].[Year]<2009-1

Answer 23

我晚上回去测试一下你的SQL语句，谢谢 ！

Answer 24

果然很高深啊，最近还有个用GPU通用计算优化的

Answer 25

建议你不要在SQL里面做运算。

Answer 26

你说的计算速度慢，其实很多时候是由于I/O造成的假象。像你这样的数据量，SQL优化不优化差别不大。一般上百万条数据，在MS SQL里的表现就需要优化了。

Answer 27

sqlite我记得join类型都是loop join.
在sql server 运行这个，我很难相信会花这么长的时间。
如果只是考虑查询性能的话，这里面有很多值可以聚合掉，把这些能聚合的值做成一个view，让后这个view上建立索引，然后用这个view的时候还要看一下有没有用这个视图索引，如果没用的话加noexpand强制使用索引。

Answer 28

建议使用sql server里的SSAS组件，专门用来做数据分析的。百万条记录秒出是没有压力的。

Answer 29

弱弱的问一下 能解释一下你的具体需求吗？别让看你的SQL了，不一定能准确表达出你的实际需求。看的头晕的嵌套。有兴趣的话描述一下你的表中有用的字段含义和需求。

Answer 30

可不可以這樣，不優化你的SQL語句。將每個年度(Year)的查詢結果保存在另外的表(如BudgetStatistic)。每次查詢時，先查BudgetStatistic表。如果BudgetStatistic中沒有某個年度的記錄，再執行你原先的SQL語句，執行完後同時將查詢結果保存至BudgetStatistic表。

Answer 31

先去看看sql优化的一些文章，然后自己在查询分析器中尝试，不但解决了问题，还提升了自己的能力。总叫别人帮写作业也不是办法

Answer 32

我是希望先把SQL语句优化到极限（即无法再优化后），再寻找其它的优化方法。

Answer 33

谢谢，我想先在SQlite里优化完成后，再放到SQLServer里测试，其实最后还可能要到MySQL或Oracle里测试。所以，不能限定某一种数据库。

Answer 34

我没有用过SSAS，但我感到好奇的是，为什么SSAS可以做到【百万条记录秒出】？ 我就是想学这样的技术（想自己写代码实现类似的问题）

Answer 35

谢谢回复。我很少在SQL中用函数。以你的经验看来，用函数来代替子查询，效率大概可以提升多少呢？ 

Answer 36

我没有用过SSAS，但我感到好奇的是，为什么SSAS可以做到【百万条记录秒出】？ 我就是想学这样的技术（想自己写代码实现类似的问题）
因为基本假设是数据不会被修改，不像SQL那样必须考虑到数据可以任意被修改。因此SSAS里可以做很多深层次的专门为了查询而做的优化，这些优化都是SQL里做不到的。

Answer 37

我没有用过SSAS，但我感到好奇的是，为什么SSAS可以做到【百万条记录秒出】？ 我就是想学这样的技术（想自己写代码实现类似的问题）
因为基本假设是数据不会被修改，不像SQL那样必须考虑到数据可以任意被修改。因此SSAS里可以做很多深层次的专门为了查询而做的优化，这些优化都是SQL里做不到的。我现在所做的数据分析就是肯定【数据不会被修改】的，而我现在想做的就是【很多深层次的专门为了查询而做的优化】，只不过我不想使用SQLServer里的专有功能，因为我使用的数据库有很多种，比如：SQLite， MySQL、Oracle和SQLServer， 所以我希望能自己开发一个类似SSAS的功能，即使达不到SSAS的效率，只要能将我的这几十万条记录【秒出】就可以了。 请继续指教，多谢 ！

Answer 38

你好，我测试了你的SQL语句（注：最后一个Where条件中的 AND 应该改成 OR ），效率又在rockyljt 和 SQL_Beginner 网友的基础上提高了5%。，谢谢。 你的思路就是将MIN 函数中的条件（Case When） 放到SQL语句的外围，并用UNION ALL 进行补充，这样的确使SQL的查询效率提高了一些，但有一个问题：
（1） 如果这个SQL语句中的SUM函数也有条件（Case When）的话，则可能需要使用到多个UNION ALL ，这样可能会使效率下降。
（2） 如果zhege SQL语句中有更多的带条件的查询分析字段，则你的方式所优化的SQL语句将更加复杂。但不管怎么说，你的方式证明了：将SQL语句中的函数（如MIN，SUM）中的条件（Case When) 移到SQL语句的最外围(末尾）会提高SQL的查询效率，谢谢你 ！

Answer 39

我没有用过SSAS，但我感到好奇的是，为什么SSAS可以做到【百万条记录秒出】？ 我就是想学这样的技术（想自己写代码实现类似的问题）
因为基本假设是数据不会被修改，不像SQL那样必须考虑到数据可以任意被修改。因此SSAS里可以做很多深层次的专门为了查询而做的优化，这些优化都是SQL里做不到的。我现在所做的数据分析就是肯定【数据不会被修改】的，而我现在想做的就是【很多深层次的专门为了查询而做的优化】，只不过我不想使用SQLServer里的专有功能，因为我使用的数据库有很多种，比如：SQLite， MySQL、Oracle和SQLServer， 所以我希望能自己开发一个类似SSAS的功能，即使达不到SSAS的效率，只要能将我的这几十万条记录【秒出】就可以了。 请继续指教，多谢 ！
SSAS的数据源不一定非要用SQL Server。大部分SQL源都可以用来做数据源。oracle是肯定可以的。mysql sqlite估计也能找到办法能让SSAS读取。如果用SSAS的tabular model的话，甚至连文本文件、excel数据都可以当做数据源的。

Answer 40

有道理。
我没有用过SSAS，但我感到好奇的是，为什么SSAS可以做到【百万条记录秒出】？ 我就是想学这样的技术（想自己写代码实现类似的问题）
因为基本假设是数据不会被修改，不像SQL那样必须考虑到数据可以任意被修改。因此SSAS里可以做很多深层次的专门为了查询而做的优化，这些优化都是SQL里做不到的。我现在所做的数据分析就是肯定【数据不会被修改】的，而我现在想做的就是【很多深层次的专门为了查询而做的优化】，只不过我不想使用SQLServer里的专有功能，因为我使用的数据库有很多种，比如：SQLite， MySQL、Oracle和SQLServer， 所以我希望能自己开发一个类似SSAS的功能，即使达不到SSAS的效率，只要能将我的这几十万条记录【秒出】就可以了。 请继续指教，多谢 ！
SSAS的数据源不一定非要用SQL Server。大部分SQL源都可以用来做数据源。oracle是肯定可以的。mysql sqlite估计也能找到办法能让SSAS读取。如果用SSAS的tabular model的话，甚至连文本文件、excel数据都可以当做数据源的。可能我没有表达清楚我的想法，我不是指SSAS的数据源，而是指我现在暂时不想使用第三方的数据分析部件（即使是数据库自带的），希望自己能开发一个类似SSAS的东西。因为客户有时是在网络及服务器上进行数据分析，有时是在单机上进行数据分析的，我们很难要求客户在自己的单机上安装SQLServer2005 或SQLServer2008. 

Answer 41

没上楼主那么大的数据做测试我提供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个思路给楼主
结果等楼主测试了谢谢