我有一张大桌子,我想优化一下。我使用的是MS 2005服务器。我将尝试描述它是如何使用的,如果有人有任何建议,我将非常感谢它。
该表大约有400 is,有1亿行,每天插入100万行。该表有8列、1列data col和7列用于查找/排序。
k1 k2 k3 k4 k5 k6 k7 d1哪里
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查询,如下所示:
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
<?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) 
 WHERE k4 = '10' 
 AND k6 = 1 
 AND k5 = 1 
 AND k1 = 'IPN' 
 AND k3 BETWEEN 2 AND 10 
 ORDER BY k7 DESC

" 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]>=(2) AND [MYDB].[dbo].[Hands].[k3]<=(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>发布于 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中已有的许多工具来分析实际性能。应用许多著名的调优技术,这些技术可以在网上或书本上轻易获得.
但是,不要做实验!有了这么多数据,你就没有时间做实验了,而且风险太大了。仔细研究可用的技术和实际的性能细节,然后一次选择一个步骤,并给每个步骤几个小时到几天的时间来揭示它的影响。
发布于 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对主表执行索引查找。
发布于 2008-12-08 18:20:06
看来你只想要最早的"g“唱片?也许只有最近的"g“记录?
基本上,您希望您的查询只读取最近/最古老的记录。您不想查询整个400 to的数据,对吗?如果是这样的话,您可能会考虑将400 of的大部分存档,或者将最近插入的记录保存在您可以查询的“当前”表中。您可以通过双插入或通过表上的触发器(颤抖)来保存当前表中的记录。但是,基本的前提是您对尽可能小的表运行查询。这基本上是穷人的表分区。
https://stackoverflow.com/questions/350318
复制相似问题