具有多个索引的 SQL 查询 - SQL Server 2000

发布于 2024-08-19 10:29:52 字数 238 浏览 5 评论 0原文

我使用类似的查询，这样

select.....from.. with... (INDEX=IX_TABLE_1, INDEX=IX_TABLE_2)...

我收到以下错误

每个表只有一个索引提示列表是允许的

这似乎与 SQL Server 2005 配合良好。这是 SQL Server 的问题吗？

原文

I use a similar query like this

select.....from.. with... (INDEX=IX_TABLE_1, INDEX=IX_TABLE_2)...

I get the following error

Only one list of index hints per table
is allowed

This seems to be working well with SQL Server 2005. Is it an issue with SQL server?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

骷髅 2024-08-26 10:29:52

我认为这可能是由于您使用的语法所致。

而不是 (INDEX=IX_TABLE_1, INDEX=IX_TABLE_2)，请尝试：

(INDEX=IX_TABLE_1, IX_TABLE_2)

我认为事实上您有 2 个“INDEX=”部分。

另外，我建议只使用索引提示作为最后的手段，因为查询优化器通常应该选择要使用的最佳计划/索引。这就是为什么通常您应该信任优化器。如果您确实使用索引提示，那么最好经常查看它们，因为它们可能会随着时间的推移而变得更糟（例如，随着数据量的增长，最初使用提示表现更好的内容可能会开始表现更差）。

I think it may be because of the syntax you're using.

Instead of (INDEX=IX_TABLE_1, INDEX=IX_TABLE_2), try:

(INDEX=IX_TABLE_1, IX_TABLE_2)

I think it is the fact you have 2 "INDEX=" parts.

Also, I would recommend only using index hints as a last resort as the query optimiser should generally choose the best plan/indexes to use. This is why generally, you should trust the optimizer. If you do use index hints, it's a good idea to review them fairly frequently as they may become worse over time (e.g. as data volumes grow, what did originally perform better with the hint, may start performing worse).

回复收藏 0 原文

愛放△進行李 2024-08-26 10:29:52

实际上，您不应该首先给出索引提示。

解释

SQL 语言背后的目标之一是将“什么”与“如何”分开。换句话说;您的查询应该指示您所需的结果集的规则，而不是数据访问路径（这正是索引提示的作用）。
通过将查询绑定到特定索引，您将无法通过添加更好的索引来提高性能。即您还必须修改您的查询。
许多提示选项是特定于平台的；当你使用它们时，你会降低便携性。
查询优化器已被编写为考虑所有索引、各种连接场景，最重要的是；表中数据的统计信息。即使您能够自己涵盖所有这些基础，并确定今天要使用的理想索引；在 6 个月的时间内，数据库中某些值、记录、引用的统计频率可能会发生变化 - 并且您的索引选择可能不再有任何好处！

旁注

如果优化器似乎在使用哪些索引方面做出了愚蠢的选择；这通常需要进一步调查。

作为第一步；您的表格统计数据是否是最新的？

其次，请确保优化器不会拒绝特定索引，因为事实上该索引会降低性能。例如，您可能会想要执行以下操作之一：

选择 Col1、Col2、Col3、...
来自客户 (INDEX=IndexByName)
WHERE 拳名如“A%”

选择列 1、列 2、列 3，...
来自客户 (INDEX=IndexByName)
按名字排序

索引提示看起来完全合乎逻辑；但是：

如果索引是聚集索引或覆盖索引：无论如何都会使用它 - 没有提示。
如果索引是非聚集且非覆盖的：检索的每个记录都需要书签查找。这会产生大量的开销；特别是在磁盘寻道活动上。因此，索引毕竟是一个糟糕的选择。

最后

我不确定是否是这样；但你的问题并不表明它是一个复杂的多表查询。那么它实际上可能像下面这样微不足道？

SELECT  Col1, Col2, ...
FROM    ATable WITH (INDEX=Index1, INDEX=Index2)

无论什么情况，为单个表提示多个索引肯定没有任何意义（除非通过自联接多次使用）。你说：

这似乎与 SQL Server 2005 配合良好。

我不得不问：你确定吗？
我尝试了一下；尽管它没有导致错误消息，但它严重迷惑了优化器。它迫使优化器遍历同一个表两次（不必要）并将结果集相互连接 - 产生巨大的开销！

Actually, you shouldn't be giving index hints in the first place.

Explanation

One of the objectives behind the SQL language is to separate the 'what' from the 'how'. In other words; your queries should indicate the rules of the result sets you require and not the data access paths (which is precisely what index hints do).
By binding your queries to specific indexes, you lose the ability to gain performance improvements by adding better indexes. I.e. You also have to modify your queries.
Many of the hint options are platform specific; you reduce portability when yo use them.
The query optimiser has been written to consider all indexes, various join scenarios, and most importantly; statistical information on the data in your tables. Even if you are able to cover all these bases yourself, and determine the ideal indexes to use today; in 6 months time the statistical frequency of certain values, records, references within your database may have changed - and your index selection may no longer be any good!

Side Note

If the optimser appears to be making a silly choice as to which indexes to use; this generally warrants further investigation.

As a first step; are your table statistics reasonably up to date?

Secondly, be sure that the optimiser isn't rejecting a particular index because in actual fact said index reduces performance. For example, you might be tempted to do one of the following:

SELECT  Col1, Col2, Col3, ...
FROM    Customers WITH (INDEX=IndexByName)
WHERE   FistName LIKE 'A%'

SELECT  Col1, Col2, Col3, ...
FROM    Customers WITH (INDEX=IndexByName)
ORDER BY FirstName

The index hints seem perfectly logical; however:

If the index is clustered, or a covering index: it would be used anyway - without the hint.
If the index is non-clustered and non-covering: Bookmark-lookups would be required for each record retrieved. This incurs a tremendous amount of overhead; especially on disk seek activity. So consequently the indexes are a poor choice after all.

Finally

I'm not sure if it is the case; but your question doesn't indicate it being a complex multi table query. So it may in fact be as trivial as follows?

SELECT  Col1, Col2, ...
FROM    ATable WITH (INDEX=Index1, INDEX=Index2)

Whatever the situation, it certainly doesn't make any sense to hint multiple indexes for a single table (unless it is used multiple times with self-joins). You said:

This seems to be working well with SQL Server 2005.

And I have to ask: Are you sure?
I tried it out; and as much as it didn't cause an error message - it seriously confused the optimiser. It forced the optimiser to traverse the same table twice (unnecessarily) and join the result sets back to each other - incurring a tremendous overhead!!

回复收藏 0 原文

~没有更多了~