Sqlite 简明教程

SQLite - Indexes

索引是数据库搜索引擎可用于加快数据检索速度的特殊查找表。简单来说， index 是表格中数据的指针。数据库中的索引与书籍末尾的索引非常相似。

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

例如，如果您想引用书中讨论特定主题的所有页面，您首先参考索引，该索引按字母顺序列出所有主题，然后参考一个或多个特定页码。

For example, if you want to reference all pages in a book that discuss a certain topic, you first refer to the index, which lists all topics alphabetically and are then referred to one or more specific page numbers.

索引有助于加快 SELECT 查询和 WHERE 子句的速度，但会减慢数据输入速度，以及 UPDATE 和 INSERT 语句。可以在不影响数据的情况下创建或删除索引。

An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data.

创建索引涉及 CREATE INDEX 语句，它允许您命名索引，指定要索引的表格和列，以及指定索引是按升序还是降序排列。

Creating an index involves the CREATE INDEX statement, which 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.

索引也可以是唯一的，类似于 UNIQUE 约束，其中索引禁止在列或有索引列组合中重复条目。

Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there’s an index.

The CREATE INDEX Command

以下是有 CREATE INDEX 的基本语法。

Following is the basic syntax of CREATE INDEX.

CREATE INDEX index_name ON table_name;

Single-Column Indexes

单列索引是基于仅一个表列创建的索引。基本语法如下 -

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

CREATE INDEX index_name
ON table_name (column_name);

Unique Indexes

唯一索引不仅用于提高性能，还用于数据完整性。唯一索引不允许将任何重复值插入到表格中。基本语法如下 -

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. The basic syntax is as follows −

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Composite Indexes

复合索引是在一个表格的两个或多个列上的索引。基本语法如下 -

A composite index is an index on two or more columns of a table. The basic syntax is as follows −

CREATE INDEX index_name
on table_name (column1, column2);

无论创建单列索引还是复合索引，都要考虑在查询的 WHERE 子句中可能非常频繁地用作筛选条件的列。

Whether to create a single-column index or a composite index, take into consideration the column(s) that you may use very frequently in a query’s WHERE clause as filter conditions.

如果只使用了一列，则应该选择单列索引。如果在 WHERE 子句中经常将两列或更多列用作筛选条件，那么复合索引将是最佳选择。

Should there be only one column used, a single-column index should be the choice. Should there be two or more columns that are frequently used in the WHERE clause as filters, the composite index would be the best choice.

Implicit Indexes

隐式索引是对象创建时由数据库服务器自动创建的索引。对于主键约束和唯一约束会自动创建索引。

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

Example

以下是我们将 COMPANY 表中的工资列创建索引的一个示例 −

Following is an example where we will create an index in COMPANY table for salary column −

sqlite> CREATE INDEX salary_index ON COMPANY (salary);

现在，让我们使用 .indices 命令列出 COMPANY 表中所有可用的索引，如下所示 −

Now, let’s list down all the indices available in COMPANY table using .indices command as follows −

sqlite> .indices COMPANY

这将产生以下结果，其中 sqlite_autoindex_COMPANY_1 是在创建表时创建的隐式索引。

This will produce the following result, where sqlite_autoindex_COMPANY_1 is an implicit index which got created when the table itself was created.

salary_index
sqlite_autoindex_COMPANY_1

你可以按如下方式列出数据库范围内所有索引 −

You can list down all the indexes database wide as follows −

sqlite> SELECT * FROM sqlite_master WHERE type = 'index';

The DROP INDEX Command

可以使用 SQLite DROP 命令删除索引。删除索引时应小心，因为性能可能会变慢或提高。

An index can be dropped using SQLite DROP command. Care should be taken when dropping an index because performance may be slowed or improved.

以下是基本语法 −

Following is the basic syntax is as follows −

DROP INDEX index_name;

你可以使用以下语句来删除先前创建的索引。

You can use the following statement to delete previously created index.

sqlite> DROP INDEX salary_index;

When Should Indexes Be Avoided?

虽然索引旨在提高数据库性能，但有时应避免使用它们。以下准则指出应该重新考虑使用索引的情况。

Although indexes are intended to enhance the performance of a database, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered.

不应在以下情况下使用索引 −

Indexes should not be used in −

Small tables.
Tables that have frequent, large batch update or insert operations.
Columns that contain a high number of NULL values.
Columns that are frequently manipulated.