Sql 简明教程

SQL - Indexes

The SQL Indexes

SQL Indexes 是特殊查找表，用于加速数据检索过程。它们保存了引用数据库中存储数据的指针，这使得在数据库表中更轻松地查找所需的数据记录。

SQL Indexes are special lookup tables that are used to speed up the process of data retrieval. They hold pointers that refer to the data stored in a database, which makes it easier to locate the required data records in a database table.

虽然索引可以加快数据检索查询（SELECT 语句）的性能，但它会降低数据输入查询（UPDATE 和 INSERT 语句）的性能。然而，这些索引对数据没有任何影响。

While an index speeds up the performance of data retrieval queries (SELECT statement), it slows down the performance of data input queries (UPDATE and INSERT statements). However, these indexes do not have any effect on the data.

SQL 索引需要在数据库中拥有它们自己的存储空间。尽管如此，用户无法物理查看它们，因为它们只是性能工具。

SQL Indexes need their own storage space within the database. Despite that, the users cannot view them physically as they are just performance tools.

The CREATE INDEX Statement

可以使用 CREATE INDEX 语句在 SQL 中创建索引。该语句允许您对索引进行命名，指定表和要建立索引的列，并指示索引是升序还是降序。

An index in SQL can be created using the CREATE INDEX statement. This statement allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in an ascending or descending order.

最好在经常被查询以进行数据检索的大型表中创建索引。

Preferably, an index must be created on column(s) of a large table that are frequently queried for data retrieval.

Syntax

CREATE INDEX 的基本语法如下 −

The basic syntax of a CREATE INDEX is as follows −

CREATE INDEX index_name ON table_name;

Types of Indexes

使用 CREATE INDEX 语句可以创建多种类型的索引。它们如下所示：

There are various types of indexes that can be created using the CREATE INDEX statement. They are:

  1. Unique Index

  2. Single-Column Index

  3. Composite Index

  4. Implicit Index

Unique Indexes

唯一索引不仅用于提升性能，还用于保障数据完整性。唯一索引不允许在表中插入任何重复值。当对数据库表应用 PRIMARY 和 UNIQUE 约束时会自动创建唯一索引，以防止用户在索引表列中插入重复值。基本语法如下所示。

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. It is automatically created by PRIMARY and UNIQUE constraints when they are applied on a database table, in order to prevent the user from inserting duplicate values into the indexed table column(s). The basic syntax is as follows.

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Single-Column Indexes

单列索引仅在单一表列上创建。语法如下所示。

A single-column index is created only on one table column. The syntax is as follows.

CREATE INDEX index_name
ON table_name (column_name);

Composite Indexes

复合索引可在表的两列或多列上创建的索引。其基本语法如下所示。

A composite index is an index that can be created on two or more columns of a table. Its basic syntax is as follows.

CREATE INDEX index_name
on table_name (column1, column2);

Implicit Indexes

隐式索引是对象创建时由数据库服务器自动创建的索引。例如，在 MySQL 数据库中，当在表上创建主键和唯一约束时，会自动创建索引。

Implicit indexes are indexes that are automatically created by the database server when an object is created. For example, indexes are automatically created when primary key and unique constraints are created on a table in MySQL database.

The DROP INDEX Statement

可以使用 SQL DROP 命令删除索引。删除索引会影响数据库中的查询性能。因此，只有在绝对必要时才需要删除索引。

An index can be dropped using SQL DROP command. Dropping an index can effect the query performance in a database. Thus, an index needs to be dropped only when it is absolutely necessary.

基本语法如下 −

The basic syntax is as follows −

DROP INDEX index_name;

When should indexes be avoided?

尽管索引旨在增强数据库的性能，但有时应避免使用索引。

Although indexes are intended to enhance a database’s performance, there are times when they should be avoided.

以下准则指示应何时重新考虑使用索引。

The following guidelines indicate when the use of an index should be reconsidered.

  1. Indexes should not be used on small tables.

  2. They should not be used on tables that have frequent, large batch updates or insert operations.

  3. Indexes should not be used on columns that contain a high number of NULL values.

  4. Columns that are frequently manipulated should not be indexed.