首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >一个400 One的表,一个查询需要调优思想(SQL2005)

问一个400 One的表,一个查询需要调优思想(SQL2005)
EN

Stack Overflow用户
提问于 2008-12-08 18:02:56
回答 24查看 2.8K关注 0票数 13

我有一张大桌子,我想优化一下。我使用的是MS 2005服务器。我将尝试描述它是如何使用的,如果有人有任何建议,我将非常感谢它。

该表大约有400 is,有1亿行,每天插入100万行。该表有8列、1列data col和7列用于查找/排序。

代码语言:javascript
复制
 k1 k2 k3 k4 k5 k6 k7 d1

哪里

代码语言:javascript
复制
 k1: varchar(3), primary key - clustered index, 10 possible values
 k2: bigint, primary key - clustered index, total rows/10 possible values
 k3: int, 10 possible values
 k4: money, 100 possible values
 k5: bool
 k6: bool
 k7: DateTime

只运行一个select查询,如下所示:

代码语言:javascript
复制
 SELECT TOP(g) d1 FROM table WITH(NOLOCK)
  WHERE k1 = a
  AND k3 = c
  AND k4 = d
  AND k5 = e
  AND k6 = f
  ORDER BY k7

在g=100万左右的地方,这个查询每天大约运行10次(通常是在插入时),大约需要5-30分钟。

因此,我目前只有两个主键列的聚集索引。我的问题是:我应该添加哪些索引来提高这个查询的性能?

在每一列上分开索引是一个好的选择吗?我认为一个单一的指数将占用大约5-8GB。DB服务器的内存总数为8GB。

请不要说最好的事情是实验。这类似于“我不知道,把你自己弄出来”)

任何小费都非常感谢!

​

由傻瓜编辑--

​

您已经在这里引发了一场过早的优化,即使不是直接的建议,“最好的事情是实验”。如果你想得到有用的帮助,你需要澄清一些问题。

-笨蛋

编辑:到目前为止,关于帖子的评论现在和查询计划一起张贴在下面-- Flibble先生

​

你可能是受I/O约束的

是的,它不是CPU绑定的。磁盘访问权限很高。所有可用的RAM似乎都被使用了。它是否被明智地使用仍有待观察。

你说你不能分割数据,因为所有的数据都被使用了:不可能

我的意思是,所有数据都是在某个时候使用的,而不是每个查询中的每个用户都使用所有数据。我当然可以拆分数据,但到目前为止,我不明白为什么对表进行分区比使用聚集索引更好。

为什么选择这些类型,VARCHAR可能应该是INT,因为它只能是几个值。其余的都很明智,金钱代表了现实生活中的金钱价值,而bigint则是一个ID,而bools是一种简单的、非官方的东西:) 我们可以随时查看insert语句,或TSQL或bulkinsert。

TSQL它基本上插入到表值中(k1、k2、k3、k4、k5、k6、d1)。唯一有趣的是,尝试了许多重复插入,并且使用k1 & k2 PK约束来防止重复数据进入数据库。我相信,在设计时(现在),这是一种快速的方法来寻找重复的数据。

你能告诉你的插入每10分钟发生一次吗?大约每10分钟运行一次插入(ADO.NET),一次大概10K,花几分钟。我估计,目前一天的插入花费了一天40%的时间。 DateTime字段是否包含插入号的日期。实际上还有另一个DateTime列,但是在任何SELECT查询中都没有检索到它,所以为了简单起见,我没有提到它。 你是怎么在这多个男人的日子里想到。 如果您只对最后的数据感兴趣,删除/归档无用的数据可能是有意义的(每天早上从头开始)

我对最近的数据并不感兴趣。查询可能会选择插入到表中的第一批数据,直到几分钟前插入的数据。但是,当数据被过滤时,这并不意味着数据库中的所有数据都是在该查询中请求的。

如果只有一个“插入器”和一个“读取器”,您可能希望在编程语言中切换到一个专门类型(hashmap/list/deque/堆栈)或更详细的类型。

我可能会暂时坚持使用MSSQL。它还没坏,只是有点慢。

liggett78,您建议对列k1、k4、k5、k6、k3或非聚集索引进行聚集索引吗?

我现在的主要问题是,是将当前的聚集索引扩展为包含k4 (这是下一个最可能的值),还是只向k4添加一个非聚集索引。

是否可以将所有k1-k6添加到聚集索引中?然后在DateTime列上为ORDER有一个单独的非聚集索引?我是否正确地认为这不会导致DB大小的大幅度增加,而只会影响插入时间。有人能猜到这会对插入物产生什么样的影响吗?

我认为,如果向所有列添加索引将使DB大小翻倍,那么如果没有大的索引(即。硬件)改变。

运行以下计划时,日期列上有一个索引(非群集)。

编辑:不确定是否可以看到下面的XML,所以这里有一个指向它的链接:http://conormccarthy.com/box/queryplan.sqlplan.txt

代码语言:javascript
复制
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.1399.06" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="11111" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="625.754" StatementText="SELECT TOP(11111) d1 FROM hands WITH (NOLOCK) &#xD;&#xA;                                WHERE k4 = '10' &#xD;&#xA;                                AND k6 = 1 &#xD;&#xA;                                AND k5 = 1  &#xD;&#xA;                                AND k1 = 'IPN'  &#xD;&#xA;                                AND k3 BETWEEN 2 AND 10  &#xD;&#xA;                                ORDER BY k7 DESC&#xD;&#xA;&#xD;&#xA;" StatementType="SELECT">
          <StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="36">
            <MissingIndexes>
              <MissingIndexGroup Impact="81.7837">
                <MissingIndex Database="[MYDB]" Schema="[dbo]" Table="[Hands]">
                  <ColumnGroup Usage="EQUALITY">
                    <Column Name="[k1]" ColumnId="1" />
                    <Column Name="[k4]" ColumnId="7" />
                    <Column Name="[k5]" ColumnId="9" />
                    <Column Name="[k6]" ColumnId="10" />
                  </ColumnGroup>
                  <ColumnGroup Usage="INEQUALITY">
                    <Column Name="[k3]" ColumnId="6" />
                  </ColumnGroup>
                  <ColumnGroup Usage="INCLUDE">
                    <Column Name="[d1]" ColumnId="3" />
                    <Column Name="[k7]" ColumnId="4" />
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
            </MissingIndexes>
            <RelOp AvgRowSize="75" EstimateCPU="0.0011111" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11111" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="625.754">
              <OutputList>
                <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="d1" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="11111" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <Top RowCount="false" IsPercent="false" WithTies="false">
                <TopExpression>
                  <ScalarOperator ScalarString="(11111)">
                    <Const ConstValue="(11111)" />
                  </ScalarOperator>
                </TopExpression>
                <RelOp AvgRowSize="83" EstimateCPU="135.557" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11111" LogicalOp="Filter" NodeId="1" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="625.753">
                  <OutputList>
                    <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="d1" />
                    <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k7" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="11111" ActualEndOfScans="0" ActualExecutions="1" />
                  </RunTimeInformation>
                  <Filter StartupExpression="false">
                    <RelOp AvgRowSize="96" EstimateCPU="318.331" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="195691" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="625.404">
                      <OutputList>
                        <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="d1" />
                        <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k7" />
                        <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k3" />
                        <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k4" />
                        <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k5" />
                        <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k6" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="341958" ActualEndOfScans="0" ActualExecutions="1" />
                      </RunTimeInformation>
                      <NestedLoops Optimized="false" WithOrderedPrefetch="true">
                        <OuterReferences>
                          <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k1" />
                          <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="HandId" />
                          <ColumnReference Column="Expr1003" />
                        </OuterReferences>
                        <RelOp AvgRowSize="32" EstimateCPU="330.366" EstimateIO="790.88" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="195691" LogicalOp="Index Scan" NodeId="4" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="2.88444">
                          <OutputList>
                            <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k1" />
                            <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="HandId" />
                            <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k7" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="341958" ActualEndOfScans="0" ActualExecutions="1" />
                          </RunTimeInformation>
                          <IndexScan Ordered="true" ScanDirection="BACKWARD" ForcedIndex="false" NoExpandHint="false">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k1" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="HandId" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k7" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Index="[ix_dateplayed]" />
                            <Predicate>
                              <ScalarOperator ScalarString="[MYDB].[dbo].[Hands].[k1]=N'IPN'">
                                <Compare CompareOp="EQ">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k1" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Const ConstValue="N'IPN'" />
                                  </ScalarOperator>
                                </Compare>
                              </ScalarOperator>
                            </Predicate>
                          </IndexScan>
                        </RelOp>
                        <RelOp AvgRowSize="88" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="195691" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="621.331">
                          <OutputList>
                            <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="d1" />
                            <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k3" />
                            <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k4" />
                            <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k5" />
                            <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k6" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="341958" ActualEndOfScans="0" ActualExecutions="341958" />
                          </RunTimeInformation>
                          <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="d1" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k3" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k4" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k5" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k6" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Index="[PK_Hands]" TableReferenceId="-1" />
                            <SeekPredicates>
                              <SeekPredicate>
                                <Prefix ScanType="EQ">
                                  <RangeColumns>
                                    <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k1" />
                                    <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="HandId" />
                                  </RangeColumns>
                                  <RangeExpressions>
                                    <ScalarOperator ScalarString="[MYDB].[dbo].[Hands].[k1]">
                                      <Identifier>
                                        <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k1" />
                                      </Identifier>
                                    </ScalarOperator>
                                    <ScalarOperator ScalarString="[MYDB].[dbo].[Hands].[HandId]">
                                      <Identifier>
                                        <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="HandId" />
                                      </Identifier>
                                    </ScalarOperator>
                                  </RangeExpressions>
                                </Prefix>
                              </SeekPredicate>
                            </SeekPredicates>
                          </IndexScan>
                        </RelOp>
                      </NestedLoops>
                    </RelOp>
                    <Predicate>
                      <ScalarOperator ScalarString="[MYDB].[dbo].[Hands].[k4]=($10.0000) AND [MYDB].[dbo].[Hands].[k6]=(1) AND [MYDB].[dbo].[Hands].[k5]=(1) AND [MYDB].[dbo].[Hands].[k3]&gt;=(2) AND [MYDB].[dbo].[Hands].[k3]&lt;=(10)">
                        <Logical Operation="AND">
                          <ScalarOperator>
                            <Compare CompareOp="EQ">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k4" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Const ConstValue="($10.0000)" />
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Compare CompareOp="EQ">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k6" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Const ConstValue="(1)" />
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Compare CompareOp="EQ">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k5" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Const ConstValue="(1)" />
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Compare CompareOp="GE">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k3" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Const ConstValue="(2)" />
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Compare CompareOp="LE">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[MYDB]" Schema="[dbo]" Table="[Hands]" Column="k3" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Const ConstValue="(10)" />
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </Logical>
                      </ScalarOperator>
                    </Predicate>
                  </Filter>
                </RelOp>
              </Top>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>
EN

回答 24

Stack Overflow用户

发布于 2008-12-08 23:01:19

正如我在一条评论中所暗示的那样,我这样做是因为一个Oracle表接近8 TB,其中包含20亿行,每天增长4千万行。然而,在我的例子中,用户是两百万(并且在不断增长的)客户,通过web访问这些数据,24x7,实际上,任何行都会被访问。哦,新的行必须在实时的两分钟内添加。

您可能是I/O绑定,而不是CPU或内存绑定,因此优化磁盘访问至关重要。你的内存很好--足够了。如果I/O不是并行化的,那么使用多个核是有帮助的,但也是有限的。

有几个人建议对数据进行分割,这一点应该认真对待,因为它比任何其他解决方案都要好得多,也更有效(没有什么比不触及数据更快了)。

你说你不能分割数据,因为所有的数据都被使用了:不可能!您的用户不可能每天分页浏览100万行或总计1亿行。因此,了解您的用户是如何实际使用数据的--查看本例中的每个查询。

更重要的是,我们不是说你应该删除数据,我们是说要分割数据。将表结构克隆到多个类似名称的表中,可能是基于时间(可能每个表一个月)。将数据复制到相关表并删除原始表。创建一个对新表执行联合的视图,其名称与原始表相同。将您的插入处理更改为针对最新的表(假设它是适当的),您的查询应该仍然与新视图相反。

您的精明用户现在可以开始对表的子集发出查询,甚至可能是最新的查询。不明智的用户可以继续对所有表使用该视图。

现在您有了一种数据管理策略,其形式是归档最老的表并删除它(当然,更新视图定义)。同样,您还需要定期创建一个新表,并更新该数据端的视图定义。

期望不能使用唯一的索引:它们不会扩展到大约1到200万行。你也可能需要修改一些其他的策略/建议。在1亿行和400 GB的情况下,您进入了另一个处理领域。

除此之外,使用其他建议-使用Server和OS中已有的许多工具来分析实际性能。应用许多著名的调优技术,这些技术可以在网上或书本上轻易获得.

但是,不要做实验!有了这么多数据,你就没有时间做实验了,而且风险太大了。仔细研究可用的技术和实际的性能细节,然后一次选择一个步骤,并给每个步骤几个小时到几天的时间来揭示它的影响。

票数 17
EN

Stack Overflow用户

发布于 2008-12-08 18:15:35

帮我更多地了解这张桌子。如果您的PK是k1,k2,则不需要通过任何其他列进行选择才能获得完全唯一的记录。

你是说k1到7是PK吗?如果是这样的话,请将其声明为聚集索引。查询性能应该大大提高。

订单增加了大量的开销。考虑寻找一个更好的选项,可以返回一组较小的数据。了解您为什么需要返回大约100万条记录可能会帮助我提供更好的解决方案。

编辑:我有一种感觉,我并不是唯一怀疑开始优化的最佳地点是你的物理表设计。你能控制这件事吗?不知道每一列存储了什么,我不能提供非常具体的想法,但下面是一种非常一般的方法:将K1、3、4、5和6 (k2似乎与表中的值直接相关)放在自己的表中,使用一个惟一的int作为PK。然后创建一个返回到此表的FK关系。然后主表上的PK将包含这个字段,k2 & k7。现在,您的查询将优化在新表中执行相当便宜的查找,返回单个记录,然后只通过PK对主表执行索引查找。

票数 8
EN

Stack Overflow用户

发布于 2008-12-08 18:20:06

看来你只想要最早的"g“唱片?也许只有最近的"g“记录?

基本上,您希望您的查询只读取最近/最古老的记录。您不想查询整个400 to的数据,对吗?如果是这样的话,您可能会考虑将400 of的大部分存档,或者将最近插入的记录保存在您可以查询的“当前”表中。您可以通过双插入或通过表上的触发器(颤抖)来保存当前表中的记录。但是,基本的前提是您对尽可能小的表运行查询。这基本上是穷人的表分区。

票数 5
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/350318

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档