SQL 中的索引是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2955459/
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
What is an index in SQL?
提问by Surya sasidhar
What is an indexin SQL? Can you explain or reference to understand clearly?
SQL 中的索引是什么？能不能解释一下或者参考一下理解清楚？
Where should I use an index?
我应该在哪里使用索引？
采纳答案by Emil Vikstr?m
An index is used to speed up searching in the database. MySQL have some good documentation on the subject (which is relevant for other SQL servers as well): http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
索引用于加速数据库中的搜索。MySQL 有一些关于这个主题的很好的文档（这也与其他 SQL 服务器相关）：http: //dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
An index can be used to efficiently find all rows matching some column in your query and then walk through only that subset of the table to find exact matches. If you don't have indexes on any column in the WHEREclause, the SQLserver has to walk through the whole tableand check every row to see if it matches, which may be a slow operation on big tables.
索引可用于高效地查找与查询中某个列匹配的所有行，然后仅遍历表的该子集以查找完全匹配。如果WHERE子句中的任何列都没有索引，则SQL服务器必须遍历整个表并检查每一行是否匹配，这在大表上可能是一个缓慢的操作。
The index can also be a UNIQUEindex, which means that you cannot have duplicate values in that column, or a PRIMARY KEYwhich in some storage engines defines where in the database file the value is stored.
索引也可以是UNIQUE索引，这意味着该列中不能有重复的值，或者PRIMARY KEY在某些存储引擎中定义了该值在数据库文件中的存储位置。
In MySQL you can use EXPLAINin front of your SELECTstatement to see if your query will make use of any index. This is a good start for troubleshooting performance problems. Read more here:
http://dev.mysql.com/doc/refman/5.0/en/explain.html
在 MySQL 中，您可以EXPLAIN在SELECT语句前使用来查看您的查询是否会使用任何索引。这是解决性能问题的良好开端。在此处阅读更多信息：http:
//dev.mysql.com/doc/refman/5.0/en/explain.html
回答by Dave Hilditch
A clustered index is like the contents of a phone book. You can open the book at 'Hilditch, David' and find all the information for all of the 'Hilditch's right next to each other. Here the keys for the clustered index are (lastname, firstname).
聚集索引就像电话簿的内容。您可以在“Hilditch, David”中打开这本书，并在旁边找到所有“Hilditch”的所有信息。这里聚集索引的键是 (lastname, firstname)。
This makes clustered indexes great for retrieving lots of data based on range based queries since all the data is located next to each other.
这使得聚集索引非常适合基于基于范围的查询检索大量数据，因为所有数据都位于彼此相邻的位置。
Since the clustered index is actually related to how the data is stored, there is only one of them possible per table (although you can cheat to simulate multiple clustered indexes).
由于聚集索引实际上与数据的存储方式有关，因此每个表只能使用其中一个（尽管您可以欺骗以模拟多个聚集索引）。
A non-clustered index is different in that you can have many of them and they then point at the data in the clustered index. You could have e.g. a non-clustered index at the back of a phone book which is keyed on (town, address)
非聚集索引的不同之处在于，您可以拥有多个索引，然后它们指向聚集索引中的数据。例如，您可以在电话簿的背面设置一个非聚集索引，该索引以（城镇、地址）为键
Imagine if you had to search through the phone book for all the people who live in 'London' - with only the clustered index you would have to search every single item in the phone book since the key on the clustered index is on (lastname, firstname) and as a result the people living in London are scattered randomly throughout the index.
想象一下，如果您必须搜索所有住在“伦敦”的人的电话簿 - 仅使用聚集索引，您将不得不搜索电话簿中的每一项，因为聚集索引上的键是（姓，名字），因此居住在伦敦的人随机分散在整个索引中。
If you have a non-clustered index on (town) then these queries can be performed much more quickly.
如果您在 (town) 上有非聚集索引，则可以更快地执行这些查询。
Hope that helps!
希望有帮助！
回答by Arun Kumar M
A very good analogy is to think of a database index as an index in a book. If you have a book regarding countries and you are looking for India, then why would you flip through the entire book – which is the equivalent of a full table scan in database terminology – when you can just go to the index at the back of the book, which will tell you the exact pages where you can find information on India. Similarly, as a book index contains a page number, a database index contains a pointer to the row containing the value that you are searching for in your SQL.
一个很好的类比是将数据库索引视为书中的索引。如果你有一本关于国家的书并且你正在寻找印度，那么你为什么要翻阅整本书——这相当于数据库术语中的全表扫描——当你可以直接去阅读后面的索引时书，它会告诉您可以找到有关印度信息的确切页面。类似地，由于书籍索引包含页码，数据库索引包含指向包含您在 SQL 中搜索的值的行的指针。
回答by Mitch Wheat
An index is used to speed up the performance of queries. It does this by reducing the number of database data pages that have to be visited/scanned.
索引用于加快查询的性能。它通过减少必须访问/扫描的数据库数据页的数量来实现这一点。
In SQL Server, a clusteredindex determines the physical order of data in a table. There can be only one clustered index per table (the clustered index IS the table). All other indexes on a table are termed non-clustered.
在 SQL Server 中，聚集索引确定表中数据的物理顺序。每个表只能有一个聚集索引（聚集索引就是表）。表上的所有其他索引都称为非聚集索引。
回答by Suresh
Indexes are all about finding data quickly.
索引都是关于快速查找数据。
Indexes in a database are analogous to indexes that you find in a book. If a book has an index, and I ask you to find a chapter in that book, you can quickly find that with the help of the index. On the other hand, if the book does not have an index, you will have to spend more time looking for the chapter by looking at every page from the start to the end of the book.
数据库中的索引类似于您在书中找到的索引。如果一本书有索引，我让你在这本书中找到一章，你可以在索引的帮助下快速找到。另一方面，如果这本书没有索引，你将不得不花更多的时间通过从书的开头到结尾查看每一页来查找章节。
In a similar fashion, indexes in a database can help queries find data quickly. If you are new to indexes, the following videos, can be very useful. In fact, I have learned a lot from them.
以类似的方式，数据库中的索引可以帮助查询快速查找数据。如果您不熟悉索引，以下视频可能非常有用。事实上，我从他们那里学到了很多东西。
Index Basics
Clustered and Non-Clustered Indexes
Unique and Non-Unique Indexes
Advantages and disadvantages of indexes
回答by Voice
Well in general index is a B-tree. There are two types of indexes: clustered and nonclustered.
总的来说，索引是一个B-tree. 有两种类型的索引：聚集的和非聚集的。
Clusteredindex creates a physical order of rows (it can be only one and in most cases it is also a primary key - if you create primary key on table you create clustered index on this table also).
聚集索引创建行的物理顺序（它可以只有一个，在大多数情况下它也是一个主键——如果你在表上创建主键，你也会在这个表上创建聚集索引）。
Nonclusteredindex is also a binary tree but it doesn't create a physical order of rows. So the leaf nodes of nonclustered index contain PK (if it exists) or row index.
非聚集索引也是二叉树，但它不会创建行的物理顺序。所以非聚集索引的叶节点包含PK（如果存在）或行索引。
Indexes are used to increase the speed of search. Because the complexity is of O(log N). Indexes is very large and interesting topic. I can say that creating indexes on large database is some kind of art sometimes.
索引用于提高搜索速度。因为复杂度是 O(log N)。索引是一个非常庞大而有趣的话题。我可以说在大型数据库上创建索引有时是一种艺术。
回答by RAGU
INDEXES- to find data easily
INDEXES- 轻松查找数据
UNIQUE INDEX- duplicate values are not allowed
UNIQUE INDEX- 不允许重复值
Syntax for INDEX
语法为 INDEX
CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN);
Syntax for UNIQUE INDEX
语法为 UNIQUE INDEX
CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(COLUMN);
回答by Kravi
First we need to understand how normal (without indexing) query runs. It basically traverse each rows one by one and when it finds the data it returns. Refer the following image. (This image has been taken from this video.)
首先，我们需要了解正常（没有索引）查询是如何运行的。它基本上一一遍历每一行，当它找到它返回的数据时。参考下图。（这张图片是从这个视频中截取的。）
So suppose query is to find 50 , it will have to read 49 records as a linear search.
所以假设查询要找到 50 ，它必须读取 49 条记录作为线性搜索。
Refer the following image. (This image has been taken from this video)
参考下图。（这张图片是从这个视频中截取的）
When we apply indexing, the query will quickly find out the data without reading each one of them just by eliminating half of the data in each traversal like a binary search. The mysql indexes are stored as B-tree where all the data are in leaf node.
当我们应用索引时，查询将快速找到数据，而无需像二进制搜索一样在每次遍历中消除一半的数据。mysql 索引存储为 B 树，其中所有数据都在叶节点中。
回答by nayeemDotNetAuthorities
INDEX is a performance optimization technique that speeds up the data retrieval process. It is a persistent data structure that associated with a Table (or View) in order to increase performance during retrieving the data from that table (or View).
INDEX 是一种性能优化技术，可加快数据检索过程。它是一种与表（或视图）相关联的持久数据结构，以提高从该表（或视图）检索数据期间的性能。
Index based search is applied more particularly when your queries include WHERE filter. Otherwise, i.e, a query without WHERE-filter selects whole data and process. Searching whole table without INDEX is called Table-scan.
当您的查询包含 WHERE 过滤器时，更特别地应用基于索引的搜索。否则，即没有 WHERE-filter 的查询选择整个数据和过程。在没有 INDEX 的情况下搜索整个表称为 Table-scan。
You will find exact information for Sql-Indexes in clear and reliable way: follow these links:
您将以清晰可靠的方式找到 Sql-Indexes 的确切信息：请访问以下链接：
- For cocnept-wise understanding: http://dotnetauthorities.blogspot.in/2013/12/Microsoft-SQL-Server-Training-Online-Learning-Classes-INDEX-Overview-and-Optimizations.html
- For implementation-wise understanding: http://dotnetauthorities.blogspot.in/2013/12/Microsoft-SQL-Server-Training-Online-Learning-Classes-INDEX-Creation-Deletetion-Optimizations.html
- 对于 cocnept-wise 理解：http://dotnetauthorities.blogspot.in/2013/12/Microsoft-SQL-Server-Training-Online-Learning-Classes-INDEX-Overview-and-Optimizations.html
- 对于实现方面的理解：http: //dotnetauthorities.blogspot.in/2013/12/Microsoft-SQL-Server-Training-Online-Learning-Classes-INDEX-Creation-Deletetion-Optimizations.html
回答by Senseful
An indexis used for several different reasons. The main reason is to speed up querying so that you can get rows or sort rows faster. Another reason is to define a primary-key or unique index which will guarantee that no other columns have the same values.
使用索引有多种不同的原因。主要原因是加快查询速度，以便您可以更快地获取行或对行进行排序。另一个原因是定义一个主键或唯一索引，以保证没有其他列具有相同的值。
