SQL 如何在select语句中使用索引？

How to use index in select statement?

提问by Vivek

Lets say in the employee table, I have created an index(idx_name) on the emp_namecolumn of the table.

假设在员工表中，我emp_name在表的列上创建了一个索引（idx_name）。

Do I need to explicitly specify the index name in select clause or it will automatically used to speed up queries.

我是否需要在 select 子句中明确指定索引名称，否则它将自动用于加速查询。

If it is required to be specified in the select clause, What is the syntax for using index in select query ?

如果需要在select子句中指定，在select查询中使用索引的语法是什么？

采纳答案by Jason

If you want to test the index to see if it works, here is the syntax:

如果要测试索引以查看它是否有效，请使用以下语法：

SELECT *
FROM Table WITH(INDEX(Index_Name))

The WITH statement will force the index to be used.

WITH 语句将强制使用索引。

回答by Tudor Constantin

Good question,

好问题，

Usually the DB engine should automatically select the index to use based on query execution plans it builds. However, there are some pretty rare cases when you want to force the DB to use a specific index.

通常，数据库引擎应该根据它构建的查询执行计划自动选择要使用的索引。但是，当您想要强制数据库使用特定索引时，有一些非常罕见的情况。

To be able to answer your specific question you have to specify the DB you are using.

为了能够回答您的特定问题，您必须指定您正在使用的数据库。

For MySQL, you want to read the Index Hint Syntaxdocumentation on how to do this

对于 MySQL，您需要阅读有关如何执行此操作的索引提示语法文档

回答by VdeX

How to use index in select statement?
this way:

如何在select语句中使用索引？
这边走：

   SELECT * FROM table1 USE INDEX (col1_index,col2_index)
    WHERE col1=1 AND col2=2 AND col3=3;

SELECT * FROM table1 IGNORE INDEX (col3_index)
WHERE col1=1 AND col2=2 AND col3=3;

SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX (i2) USE INDEX (i2);

And many more ways check this

还有更多方法检查这个

Do I need to explicitly specify?

我需要明确指定吗？

• No, no Need to specify explicitly.
• DB engine should automatically select the index to use based on query execution plans it builds from @Tudor Constantin answer.
• The optimiser will judge if the use of your index will make your query run faster, and if it is, it will use the index. from @niktrl answer

• 不，不需要明确指定。
• 数据库引擎应该根据它从@Tudor Constantin 答案构建的查询执行计划自动选择要使用的索引。
• 优化器会判断使用你的索引是否会让你的查询运行得更快，如果是，它就会使用索引。来自@niktrl 的回答

回答by Damien_The_Unbeliever

In general, the index will be used if the assumed cost of using the index, and then possibly having to perform further bookmark lookups is lower than the cost of just scanning the entire table.

在一般，如果使用索引，然后可能不必执行进一步书签查找的成本假定不仅仅是扫描整个表的成本低的索引将被使用。

If your query is of the form:

如果您的查询采用以下形式：

SELECT Name from Table where Name = 'Boris'

And 1 row out of 1000 has the name Boris, it will almost certainly be used. If everyone's name is Boris, it will probably resort to a table scan, since the index is unlikely to be a more efficient strategy to access the data.

1000 行中有 1 行的名字是鲍里斯，几乎肯定会被使用。如果每个人的名字都是 Boris，它可能会求助于表扫描，因为索引不太可能是访问数据的更有效策略。

If it's a wide table (lot's of columns) and you do:

如果它是一个宽表（很多列）并且你这样做：

SELECT * from Table where Name = 'Boris'

Then it may still choose to perform the table scan, if it's a reasonable assumption that it's going to take more time retrieving the other columns from the table than it will to just look up the name, or again, if it's likely to be retrieving a lot of rows anyway.

然后它可能仍然选择执行表扫描，如果合理的假设是从表中检索其他列比只查找名称需要更多的时间，或者再次，如果它可能正在检索一个反正很多行。

回答by niktrs

The optimiser will judge if the use of your index will make your query run faster, and if it is, it will use the index.

优化器会判断使用你的索引是否会让你的查询运行得更快，如果是，它就会使用索引。

Depending on your RDBMS you can force the use of an index, although it is not recommended unless you know what you are doing.

根据您的 RDBMS，您可以强制使用索引，但不建议这样做，除非您知道自己在做什么。

In general you should index columns that you use in table join's and where statements

通常，您应该索引您在表连接和 where 语句中使用的列

回答by MD Sayem Ahmed

Generally, when you create an index on a table, database will automatically use that index while searching for data in that table. You don't need to do anything about that.

通常，当您在表上创建索引时，数据库会在搜索该表中的数据时自动使用该索引。你不需要做任何事情。

However, in MSSQL, you can specify an index hintwhich can specify that a particular index should be used to execute this query. More information about this can be found here.

但是，在 MSSQL 中，您可以指定 一个index hint可以指定应使用特定索引来执行此查询。可以在此处找到有关此的更多信息。

Index hintis also seems to be available for MySQL. Thanks to Tudor Constantine.

Index hint似乎也可用于MySQL。感谢都铎君士坦丁。

回答by ChrisBint

By using the column that the index is applied to within your conditions, it will be included automatically. You do not have to use it, but it will speed up queries when it is used.

通过在您的条件内使用索引应用于的列，它将自动包含在内。您不必使用它，但它会在使用时加快查询速度。

SELECT * FROM TABLE WHERE attribute = 'value'

Will use the appropriate index.

将使用适当的索引。

回答by Cory

The index hint is only available for Microsoft Dynamics database servers. For traditional SQL Server, the filters you define in your 'Where' clause should persuade the engine to use any relevant indices... Provided the engine's execution plan can efficiently identify how to read the information (whether a full table scan or an indexed scan) - it must compare the two before executing the statement proper, as part of its built-in performance optimiser.

索引提示仅适用于 Microsoft Dynamics 数据库服务器。对于传统的 SQL Server，你在“Where”子句中定义的过滤器应该说服引擎使用任何相关的索引......前提是引擎的执行计划可以有效地识别如何读取信息（无论是全表扫描还是索引扫描) - 作为其内置性能优化器的一部分，它必须在正确执行语句之前比较两者。

However, you can force the optimiser to scan by using something like

但是，您可以使用类似的方法强制优化器进行扫描

    Select  *
    From    [yourtable] With (Index(0))
    Where   ...

Or to seek a particular index by using something like

或者通过使用类似的东西来寻找特定的索引

    Select  *
    From    [yourtable] With (Index(1))
    Where   ...

The choice is yours. Look at the table's index properties in the object panel to get an idea of which index you want to use. It ought to match your filter(s).

这是你的选择。在对象面板中查看表的索引属性以了解要使用的索引。它应该与您的过滤器相匹配。

For best results, list the filters which would return the fewest results first. I don't know if I'm right in saying, but it seems like the query filters are sequential; if you get your sequence right, the optimiser shouldn't have to do it for you by comparing all the combinations, or at least not begin the comparison with the more expensive queries.

为获得最佳结果，首先列出返回最少结果的过滤器。我不知道我说得对不对，但查询过滤器似乎是顺序的；如果你的序列正确，优化器就不必通过比较所有组合来为你做这件事，或者至少不要从更昂贵的查询开始比较。