blocks|key|1268553|text|你能从执行计划中判断出它是否在使用索引吗？如果没有，表/索引最后一次分析是什么时候？如果它从未被分析过，也许SQL+server仍然认为其中只有100行，所以进行表扫描会更快一些。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1268554|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Can you tell from the execution plan if it is using the index or not? If not, when was the table/index last analyzed? If it's never been analyzed, maybe SQL server still thinks there's only 100 rows in it and it would just be faster to do a table scan.

blocks|key|524930|text|我有一个具有类似日期-时间字段的表(假设这是您的数据类型)和4M行--是您的行的1%25，但是当我单击“execute”时，几乎就返回了相同的查询。我的索引几乎和你的一样：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|524931|CREATE+NONCLUSTERED+INDEX+[IX_PartViewTrack_SearchDate]+ON+[dbo].[PartViewTrack]+

(
[SearchDate]+ASC
)WITH+(PAD_INDEX++=+OFF,+STATISTICS_NORECOMPUTE++=+OFF,+SORT_IN_TEMPDB+=+OFF,+++++
IGNORE_DUP_KEY+=+OFF,+DROP_EXISTING+=+OFF,+ONLINE+=+OFF,+ALLOW_ROW_LOCKS++=+ON,+
ALLOW_PAGE_LOCKS++=+ON)+ON+[PRIMARY]|code-block|syntax|javascript|524932|执行计划显示了索引扫描。|524933|只是为了露齿而笑-删除索引，看看需要多长时间？|524934|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I have a table with a similar date-time field (assuming that is your datatype) and 4M rows - 1% of yours, but the same query came back almost as soon as I clicked 'execute'. My index is almost the same as yours:

<pre><code>CREATE NONCLUSTERED INDEX [IX_PartViewTrack_SearchDate] ON [dbo].[PartViewTrack] 

(
[SearchDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
</code></pre>

The execution plan shows an index scan.

Just for grins - drop the index and see how long it takes?

blocks|key|524936|text|SELECT+MAX([LastModifiedTime])+FROM+Workflow+with(nolock)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|524937|这样会更快吗？|unstyled|524938|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>SELECT MAX([LastModifiedTime]) FROM Workflow with(nolock)
</code></pre>

Is this any faster?

blocks|key|2011924|text|一个非黑即白的问题，(对我来说)没有明显的答案。这里有一些相当愚蠢的想法，只是为了澄清一下。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2011925|2011926|这是一张桌子吗？如果是一个视图，底层结构可能会成为障碍。(我忘了，您可以在实例化视图上构建非聚集索引吗？)|unordered-list-item|2011927|是否存在争用？如果其他用户在表上有长期锁，这可能会减慢它的速度。此外，非常频繁的更新可能会使其中断。|2011928|查询计划显示正在运行的索引扫描。索引引用是这个索引吗？(还有其他索引吗？A聚簇索引？可能不是问题，但是在这里寻找想法。)|2011929|您使用模式吗？索引在dbo.Workflow上，但查询未指定"dbo“。(就像我说的，在这里寻找想法。)|2011930|2011931|所有这些听起来都很差劲，但如果没有亲身实践，这些问题可能很难解决。|2011932|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|R|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|S|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|T|8|@]|9|@]|A|$]]|$1|H|3|I|5|E|7|U|8|@]|9|@]|A|$]]|$1|J|3|K|5|E|7|V|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|P|$]]

A black-and-white problem, without (to me) an obvious answer. Here are some pretty dumb ideas, just to clear the air.

<ul>
<li>Is it a table? If a view, underlying
structure might get in the way. (I
forget, can you build a nonclustered
index on a materialized view?)</li>
<li>Is there contention? If other users
have long-standing locks on the
table, that might slow it down. 
Also, extremely frequent updates
might throw it off.</li>
<li>The query plan shows an index scan
being run. Is the index reference
this index? (Are there other indexes? A clustered indexes?
Probably not an issue, but scrabbling
for ideas here.)</li>
<li>Do you use schemas? The index is on
dbo.Workflow, but the query does not
specify "dbo". (Like I said,
scrabbling for ideas here.)</li>
</ul>

All of those sound pretty lame, but without hands-on access these kinds of problems can be pretty tough to figure out.

blocks|key|523754|text|我发现Oracle有时需要一个字段不允许空值，然后才会对其使用索引。SQL+Server中是否有类似的限制？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|523755|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Here is a longshot: I have found that Oracle sometimes requires a field to not allow nulls before it will use an index on it. Are there any similar restrictions in SQL Server?

blocks|key|1268620|text|查询优化器的方式很神秘...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1268621|如果可能，我建议您像这样更改查询：|1268622|SELECT+TOP+(1)+[LastModifiedTime]
FROM+Workflow+
ORDER+BY+[LastModifiedTime]+DESC;|code-block|syntax|javascript|1268623|这在语义上是相同的，优化器将不再考虑使用MAX+aggregate和scan+(显然它现在正在考虑)。它可能会考虑在工作表中进行排序，但希望这样的计划的估计成本将比反向排序的成本大得多。|1268624|至于为什么优化器选择一个明显糟糕的计划，通常涉及许多因素，仅从SO帖子很难诊断。通常，拥有ASC索引并不总是能弥补缺少DESC索引的情况，并且您的特定列统计信息(分布)可能已经达到了查询优化器内部的某个临界点，它决定选择scan%2Baggregate而不是相反的scan%2Btop。|1268625|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Mysterious are the ways of the query optimizer...

If is possible, I'd recommend you change the query like this:

<pre><code>SELECT TOP (1) [LastModifiedTime]
FROM Workflow 
ORDER BY [LastModifiedTime] DESC;
</code></pre>

This is semantically identical and the optimizer will no longer consider using the MAX aggregate and scan (which apparently it does right now). It may consider doing a SORT in a worktable, but hopefully the estimated cost of such a plan would be much bigger than the cost of the reverse order seek.

As to why does the optimizer choose what apparently is an obviously bad plan, there are usually many many factors involved and is hard to diagnose just from a SO post. In general, having an ASC index does not always substitute for the lack of a DESC index and your particular column statistics (distribution) may had hit some tipping point inside the query optimizer where it decided to choose the scan+aggregate instead of the reverse scan+top.

blocks|key|3419649|text|首先使用命令分析您的索引|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3419650|DBCC+SHOW_STATISTICS+(table_name,+index_name)|code-block|syntax|javascript|3419651|检查索引是否覆盖了整个表。如果不是，请尝试使用更新统计数据|3419652|UPDATE+STATISTICS+table_name|3419653|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Analyse your index first using the command

<pre><code>DBCC SHOW_STATISTICS (table_name, index_name)
</code></pre>

Check if your index is covering the whole table. If not, try updating your statistics using

<pre><code>UPDATE STATISTICS table_name
</code></pre>

I have the following query:

<pre><code>SELECT MAX([LastModifiedTime]) FROM Workflow
</code></pre>

There are approximately 400M rows in the Workflow table. There is an index on the LastModifiedTime column as follows:

<pre><code>CREATE NONCLUSTERED INDEX [IX_Workflow_LastModifiedTime] ON [dbo].[Workflow] 
(
 [LastModifiedTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100)
</code></pre>

The above query takes 1.5 minutes to execute. Why wouldn't SQL Server use the above index and simply retrieve the last row in the index to get the maximum value?

BTW, the query plan for this query shows an <code>index scan</code> being done on the above index.

Thanks.

Help with simple query - why isn't an index being used?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有以下疑问：SELECT MAX([LastModifiedTime]) FROM Workflow工作流表中大约有400M行。LastModifiedTime列上的索引如下所示：CREATE NONCLUSTERED INDEX [IX_Workflow_LastModifiedTime] ON [dbo].[Wo...

问帮助进行简单的查询--为什么没有使用索引？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问帮助进行简单的查询--为什么没有使用索引？EN