sql组合索引和独立索引_SQL索引概述和策略

sql组合索引和独立索引

A SQL index is used to retrieve data from a database very fast. Indexing a table or view is, without a doubt, one of the best ways to improve the performance of queries and applications.

SQL索引用于非常快速地从数据库检索数据。 毫无疑问，索引表或视图是提高查询和应用程序性能的最佳方法之一。

A SQL index is a quick lookup table for finding records users need to search frequently. An index is small, fast, and optimized for quick lookups. It is very useful for connecting the relational tables and searching large tables.

SQL索引是一种快速查找表，用于查找用户需要频繁搜索的记录。 索引小，快速，并且针对快速查找进行了优化。 这对于连接关系表和搜索大型表非常有用。

SQL indexes are primarily a performance tool, so they really apply if a database gets large. SQL Server supports several types of indexes but one of the most common types is the clustered index. This type of index is automatically created with a primary key. To make the point clear, the following example creates a table that has a primary key on the column “EmployeeId”:

SQL索引主要是一种性能工具，因此，如果数据库很大，它们确实适用。 SQL Server支持几种类型的索引，但是最常见的一种类型是聚集索引。 此类索引是使用主键自动创建的。 为了清楚说明这一点，下面的示例创建一个表，该表在“ EmployeeId”列上具有主键：

CREATE TABLE dbo.EmployeePhoto
(EmployeeId      INT NOT NULL PRIMARY KEY, 
 Photo           VARBINARY(MAX) NULL, 
 MyRowGuidColumn UNIQUEIDENTIFIER NOT NULL
                                  ROWGUIDCOL UNIQUE
                                             DEFAULT NEWID()
);

You’ll notice in the create table definition for the “EmployeePhoto” table, the primary key at the end of “EmployeeId” column definition. This creates a SQL index that is specially optimized to get used a lot. When the query is executed, SQL Server will automatically create a clustered index on the specified column and we can verify this from Object Explorer if we navigate to the newly created table, and then the Indexes folder:

您会在“ EmployeePhoto”表的创建表定义中注意到，“ EmployeeId”列定义末尾的主键。 这将创建一个经过特别优化以大量使用SQL索引。 执行查询时，SQL Server将在指定的列上自动创建聚簇索引，如果我们导航到新创建的表，然后导航到Indexes文件夹，则可以从Object Explorer进行验证。

Notice that not only creating a primary key creates a unique SQL index. The unique constraint does the same on the specified columns. Therefore, we got one additional unique index for the “MyRowGuidColumn” column. There are no remarkable differences between the unique constraint and a unique index independent of a constraint. Data validation happens in the same manner and the query optimizer does not differentiate between a unique SQL index created by a constraint or manually created. However, a unique or primary key constraint should be created on the column when data integrity is the objective because by doing so the objective of the index will be clear.

请注意，不仅创建主键还会创建唯一SQL索引。 唯一约束在指定列上执行相同的操作。 因此，我们为“ MyRowGuidColumn”列获得了另一个唯一索引。 唯一约束和独立于约束的唯一索引之间没有显着差异。 数据验证以相同的方式发生，并且查询优化器不会区分由约束创建或手动创建的唯一SQL索引。 但是，当以数据完整性为目标时，应在列上创建唯一或主键约束，因为这样做可以使索引的目标明确。

• Note: The detailed explanation of each type of these SQL indexes can be found in the following article: How to create and optimize SQL Server indexes for better performance

  注意：在下面的文章中可以找到这些SQL索引的每种类型的详细说明： 如何创建和优化SQL Server索引以获得更好的性能

So, if we use a lot of joins on the newly created table, SQL Server can lookup indexes quickly and easily instead of searching sequentially through potentially a large table.

因此，如果我们在新创建的表上使用大量联接，则SQL Server可以快速，轻松地查找索引，而不是顺序搜索潜在的大表。

SQL indexes are fast partly because they don’t have to carry all the data for each row in the table, just the data that we’re looking for. This makes it easy for the operating system to cache a lot of indexes into memory for faster access and for the file system to read a huge number of records simultaneously rather than reading them from the disk.

SQL索引之所以快速，部分原因是它们不必携带表中每一行的所有数据，而只需要携带我们要查找的数据。 这使操作系统很容易将大量索引缓存到内存中，以加快访问速度，并使文件系统更容易同时读取大量记录，而不是从磁盘读取它们。

Additional indexes can be created by using the Index keyword in the table definition. This can be useful when there is more than one column in the table that will be searched often. The following example creates indexes within the Create table statement:

可以使用表定义中的Index关键字来创建其他索引。 当表中经常搜索多个列时，此功能很有用。 下面的示例在Create table语句中创建索引：

CREATE TABLE Bookstore2
(ISBN_NO    VARCHAR(15) NOT NULL PRIMARY KEY, 
 SHORT_DESC VARCHAR(100), 
 AUTHOR     VARCHAR(40), 
 PUBLISHER  VARCHAR(40), 
 PRICE      FLOAT, 
 INDEX SHORT_DESC_IND(SHORT_DESC, PUBLISHER)
);