如何在 SQL 查询中使用索引

How to use index in SQL query

提问by omkar patade

Well i am new to this stuff ..I have created an index in my SP at start like follows

好吧，我是这个东西的新手..我在开始时在我的 SP 中创建了一个索引，如下所示

Create Index index_fab
ON TblFab (Fab_name)

Now i have query under this

现在我在这个下面有查询

select fab_name from TblFab where artc = 'x' and atelr = 'y'.

now Is it necessary to use this index name in select clause or it will automatically used to speed up queries

现在是否有必要在 select 子句中使用此索引名称，否则它将自动用于加速查询

Do i have to use something like

我必须使用类似的东西吗

select fab_name from TblFab WITH(INDEX(index_fab)) where artc = 'x' and atelr = 'y'.

or any other method to use this index in query

或在查询中使用此索引的任何其他方法

and also how to use index if we are using join on this table?

以及如果我们在这个表上使用连接，如何使用索引？

回答by Neville Kuyt

Firstly, do you mean you're creating the index in a stored procedure? That's a bad idea - if you run the stored procedure twice, it will fail because the index already exists.

首先，您的意思是在存储过程中创建索引吗？这是一个坏主意 - 如果您运行存储过程两次，它将失败，因为索引已经存在。

Secondly, your query doesn't use the column mentioned in the index, so it will have no impact.

其次，您的查询不使用索引中提到的列，因此不会产生任何影响。

Thirdly, as JodyT writes, the query analyzer (SQL Server itself) will decide which index to use; it's almost certainly better at it than you are.

第三，正如 JodyT 所写，查询分析器（SQL Server 本身）将决定使用哪个索引；它几乎肯定比你更擅长。

Finally, to speed up the query you mention, create an index on columns artc and atelr.

最后，为了加快您提到的查询速度，请在 artc 和 atelr 列上创建一个索引。

回答by JodyT

The Query Optimizer of SQL Server will decide if it the index is suitable for the query. You can't forceit to use a specific index. You can give hints on which you want it to use but it won't be a guarantee that it will use it.

SQL Server 的查询优化器将决定该索引是否适合查询。你不能force使用特定的索引。您可以给出想要使用它的提示，但不能保证它会使用它。

回答by curiousBoy

As the other people answered your question to help you to understand better, my opinion is, you should first understand why you need to use indexes. As we know that indexes increase the performance , they could also cause performance issues as well. Its better to know when you need to use indexes, why you need to use indexes instead of how to use indexes.

由于其他人回答了您的问题以帮助您更好地理解，我的意见是，您应该首先了解为什么需要使用索引。我们知道索引会提高性能，但它们也可能导致性能问题。最好知道什么时候需要使用索引，为什么需要使用索引而不是如何使用索引。

You can read almost every little detail from here.

您可以从这里阅读几乎所有的小细节。

Regarding your example, your query's index has no impact. Because it doesn't have the mentioned column in your query's where clause.

关于您的示例，您查询的索引没有影响。因为它在查询的 where 子句中没有提到的列。

You can also try:

你也可以试试：

CREATE INDEX yourIndexName ON yourTableName (column_you_are_looking_for1,column_you_are_lookingfor2)

CREATE INDEX yourIndexName ON yourTableName (column_you_are_looking_for1,column_you_are_lookingfor2)

Also good to know: If no index exists on a table, a table scan must be performed for each table referenced in a database query. The larger the table, the longer a table scan takes because a table scan requires each table row to be accessed sequentially. Although a table scan might be more efficient for a complex query that requires most of the rows in a table, for a query that returns only some table rows an index scan can access table rows more efficiently. (source from here)

还很高兴知道：如果表上不存在索引，则必须对数据库查询中引用的每个表执行表扫描。表越大，表扫描所需的时间就越长，因为表扫描需要按顺序访问每个表行。尽管对于需要表中大部分行的复杂查询，表扫描可能更有效，但对于仅返回某些表行的查询，索引扫描可以更有效地访问表行。（来源来自这里）

Hope this helps.

希望这可以帮助。

回答by Serge

You're index use "Fab_name" column which you don't filter on in your select statement, so it's of no use.

您是索引使用“Fab_name”列，您不会在选择语句中对其进行过滤，因此它没有用。

Since you're new to this, you might benefit from an index like this :

由于您是新手，您可能会从这样的索引中受益：

Create Index index_fab
ON TblFab (artc, atelr)

or maybe like this

或者像这样

Create Index index_fab
ON TblFab (atelr, artc)

...yes there are a lot of subtleties to learn.

...是的，有很多微妙之处需要学习。

回答by Nrusingha

For better performance: List out the columns /tables which are frequently used, Create index on those tables/columns only.

为了获得更好的性能：列出经常使用的列/表，仅在这些表/列上创建索引。

回答by DrCopyPaste

An index should be used by default if you run a query against the table using it. But I think in the query you posted it will not be used, because you are not filtering your data by the column you created your index on. I think you would have to create the index for the artc and atelr columns to profit from that. To see wether your index is used take a look at the execution plan that was used in the SQL Management Studio.

如果您对使用它的表运行查询，则默认情况下应使用索引。但是我认为在您发布的查询中不会使用它，因为您没有按创建索引的列过滤数据。我认为您必须为 artc 和 atelr 列创建索引才能从中获利。要查看您的索引是否被使用，请查看 SQL Management Studio 中使用的执行计划。

more info on indices: use the index luke

有关索引的更多信息：使用索引 luke

回答by DevelopmentIsMyPassion

You dont need to include index in your query. Its managed by sql server. Also you dont need to include index in select if you want to make join to this table. Hope its clear.

您不需要在查询中包含索引。它由sql server管理。如果要连接到该表，也不需要在 select 中包含索引。希望它清楚。

回答by Adam Luniewski

If index is properly set up, optimizer will use it automatically. By properly set up, I mean that it's selective enough, can effectively help the query etc. Read about it. You can check by yourself if index is being used by using "include actual execution plan" option in ssms. It's generally not advised to use with(index()) hints and let optimizer decided by itself, except from very special cases when you just know better ;).

如果索引设置正确，优化器将自动使用它。通过正确设置，我的意思是它具有足够的选择性，可以有效地帮助查询等。阅读它。您可以通过使用 ssms 中的“包含实际执行计划”选项自行检查是否正在使用索引。通常不建议使用 with(index()) 提示并让优化器自行决定，除非在非常特殊的情况下您知道得更好;)。