SQL 如何索引数据库列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1156/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I index a database column
提问by Xenph Yan
Hopefully, I can get answers for each database server.
希望我能得到每个数据库服务器的答案。
For an outline of how indexing works check out: How does database indexing work?
有关索引如何工作的概述，请查看：数据库索引如何工作？
回答by John Downey
The following is SQL92 standard so should be supported by the majority of RDMBS that use SQL:
以下是 SQL92 标准，因此大多数使用 SQL 的 RDMBS 应该支持：
CREATE INDEX [index name] ON [table name] ( [column name] )
回答by Eric Z Beard
Sql Server 2005gives you the ability to specify a covering index. This is an index that includes data from other columns at the leaf level, so you don't have to go back to the table to get columns that aren't included in the index keys.
Sql Server 2005使您能够指定覆盖索引。这是一个包含来自叶级别其他列的数据的索引，因此您不必返回表来获取未包含在索引键中的列。
create nonclustered index my_idx on my_table (my_col1 asc, my_col2 asc) include (my_col3);
This is invaluable for a query that has my_col3in the select list, and my_col1and my_col2in the where clause.
这是无价的，有一个查询my_col3在选择列表，并my_col1和my_col2在where子句。
回答by tdc
For python pytables, indexes don't have names and they are bound to single columns:
对于 python pytables，索引没有名称，它们绑定到单个列：
tables.columns.column_name.createIndex()
回答by David Manheim
In SQL Server, you can do the following: (MSDN Linkto full list of options.)
在 SQL Server 中，您可以执行以下操作：（指向完整选项列表的MSDN 链接。）
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> ( column [ ASC | DESC ] [ ,...n ] )
[ INCLUDE ( column_name [ ,...n ] ) ]
[ WHERE <filter_predicate> ]
(ignoring some more advanced options...)
（忽略一些更高级的选项......）
The name of each Index must be unique database wide.
每个索引的名称在数据库范围内必须是唯一的。
All indexes can have multiple columns, and each column can be ordered in whatever order you want.
所有索引都可以有多个列，并且可以按您想要的任何顺序对每一列进行排序。
Clustered indexes are unique - one per table. They can't have INCLUDEd columns.
聚集索引是唯一的 - 每个表一个。他们不能有INCLUDEd 列。
Nonclustered indexes are not unique, and can have up to 999 per table. They can have included columns, and where clauses.
非聚集索引不是唯一的，每个表最多可以有 999 个。它们可以包含列和 where 子句。
回答by Sharvari
To create indexes following stuff can be used:
可以使用以下内容来创建索引：
Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name ON table_name (column_name)Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name ON table_name (column_name)Clustered Index:
CREATE CLUSTERED INDEX CL_ID ON SALES(ID);- Non-clustered index:
CREATE NONCLUSTERED INDEX NONCI_PC ON SALES(ProductCode);
在表上创建索引。允许重复值：
CREATE INDEX index_name ON table_name (column_name)在表上创建唯一索引。不允许重复值：
CREATE UNIQUE INDEX index_name ON table_name (column_name)聚集索引：
CREATE CLUSTERED INDEX CL_ID ON SALES(ID);- 非聚集索引：
CREATE NONCLUSTERED INDEX NONCI_PC ON SALES(ProductCode);
Refer: http://www.codeproject.com/Articles/190263/Indexes-in-MS-SQL-Serverfor details.
有关详细信息，请参阅：http: //www.codeproject.com/Articles/190263/Indexes-in-MS-SQL-Server。
回答by Looking_for_answers
CREATE INDEX name_index ON Employee (Employee_Name)On a multi column:
CREATE INDEX name_index ON Employee (Employee_Name, Employee_Age)
CREATE INDEX name_index ON Employee (Employee_Name)在多列上：
CREATE INDEX name_index ON Employee (Employee_Name, Employee_Age)
回答by mdeora
Since most of the answers are given for SQL databases, I am writing this for NOSQL databases, specifically for MongoDB.
由于大多数答案是针对 SQL 数据库给出的，因此我正在为 NOSQL 数据库（特别是 MongoDB）编写此内容。
Below is the syntax to create an index in the MongoDB using mongo shell.
以下是使用 mongo shell 在 MongoDB 中创建索引的语法。
db.collection.createIndex( <key and index type specification>, <options> )
example - db.collection.createIndex( { name: -1 } )
例子 - db.collection.createIndex( { name: -1 } )
In the above example an single key descending index is created on the name field.
在上面的示例中，在 name 字段上创建了一个单键降序索引。
Keep in mind MongoDB indexes uses B-tree data structure.
请记住，MongoDB 索引使用 B 树数据结构。
There are multiple types of indexes we can create in mongodb, for more information refer to below link - https://docs.mongodb.com/manual/indexes/
我们可以在 mongodb 中创建多种类型的索引，有关更多信息，请参阅以下链接 - https://docs.mongodb.com/manual/indexes/
回答by SriniV
An index is not always needed for all the databases. For eg: Kognitio aka WX2 engine doesn't offer a syntax for indexing as the database engine takes care of it implicitly. Data goes on via round-robin partitioning and Kognitio WX2 gets data on and off disk in the simplest possible way.
并非所有数据库都需要索引。例如： Kognitio 又名 WX2 引擎不提供索引语法，因为数据库引擎会隐式地处理它。数据通过循环分区处理，而 Kognitio WX2 以最简单的方式获取磁盘上和磁盘外的数据。
回答by krishna kirti
We can use following syntax to create index.
我们可以使用以下语法来创建索引。
CREATE INDEX <index_name> ON <table_name>(<column_name>)
If we do not want duplicate value to be allowed then we can add UNIQUEwhile creating index as follow
如果我们不希望允许重复值，那么我们可以在创建索引时添加UNIQUE如下
CREATE UNIQUE INDEX <index_name> ON <table_name>(<column_name>)
We can create index on multiple column by giving multiple column name separated by ','
我们可以通过给出由 ' ,'分隔的多个列名来在多个列上创建索引
