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?
以及如果我们在这个表上使用join,如何使用索引?
8 个解决方案
#1
8
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列上创建索引。
#2
3
The Query Optimizer of SQL Server will decide if it the index is suitable for the query. You can't force it 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的查询优化器将决定索引是否适合查询。您无法强制它使用特定索引。您可以提供您希望它使用的提示,但不能保证它会使用它。
#3
3
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)
在yourTableName上创建INDEX yourIndexName(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.
希望这可以帮助。
#4
2
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
#5
2
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中包含索引。希望清楚。
#6
2
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”列,你没有在你的select语句中过滤,所以它是没用的。
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.
......是的,需要学习很多细微之处。
#7
2
For better performance: List out the columns /tables which are frequently used, Create index on those tables/columns only.
为了获得更好的性能:列出经常使用的列/表,仅在这些表/列上创建索引。
#8
1
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中使用“include actual execution plan”选项来自行检查是否正在使用索引。通常不建议使用(index())提示并让优化器自己决定,除非你知道更好的非常特殊情况;)。
#1
8
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列上创建索引。
#2
3
The Query Optimizer of SQL Server will decide if it the index is suitable for the query. You can't force it 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的查询优化器将决定索引是否适合查询。您无法强制它使用特定索引。您可以提供您希望它使用的提示,但不能保证它会使用它。
#3
3
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)
在yourTableName上创建INDEX yourIndexName(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.
希望这可以帮助。
#4
2
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
#5
2
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中包含索引。希望清楚。
#6
2
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”列,你没有在你的select语句中过滤,所以它是没用的。
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.
......是的,需要学习很多细微之处。
#7
2
For better performance: List out the columns /tables which are frequently used, Create index on those tables/columns only.
为了获得更好的性能:列出经常使用的列/表,仅在这些表/列上创建索引。
#8
1
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中使用“include actual execution plan”选项来自行检查是否正在使用索引。通常不建议使用(index())提示并让优化器自己决定,除非你知道更好的非常特殊情况;)。