Mysqli 简明教程

MySQLi - Indexes

数据库索引是一种数据结构，可以提高表中操作的速度。可以使用一个或多个列创建索引，既可以快速随机查找，也可以有效地对记录访问进行排序。

A database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.

在创建索引时，应考虑创建 SQL 查询和在这些列上创建或多个索引的列。

While creating index, it should be considered that what are the columns which will be used to make SQL queries and create one or more indexes on those columns.

实际上，索引也是表的一种类型，它保留主键或索引字段和指向实际表中每条记录的指针。

Practically, indexes are also type of tables, which keep primary key or index field and a pointer to each record into the actual table.

用户无法看到索引，它们仅用于加速查询，并将由数据库搜索引擎用来非常快速地查找记录。

The users cannot see the indexes, they are just used to speed up queries and will be used by Database Search Engine to locate records very fast.

INSERT 和 UPDATE 语句在具有索引的表上花费更多时间，而 SELECT 语句在这些表上变得快速。原因是在执行插入或更新时，数据库也需要插入或更新索引值。

INSERT and UPDATE statements take more time on tables having indexes where as SELECT statements become fast on those tables. The reason is that while doing insert or update, database need to insert or update index values as well.

Simple and Unique Index

可以在表上创建唯一索引。唯一索引意味着，两行不能有相同的索引值。以下是在表上创建索引的语法。

You can create a unique index on a table. A unique index means that two rows cannot have the same index value. Here is the syntax to create an Index on a table.

CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...);

你可以使用一列或多列来创建索引。例如，我们可以使用 NAME_INDEX 在 tutorials_inf 上创建一个索引。

You can use one or more columns to create an index. For example, we can create an index on tutorials_inf using NAME_INDEX.

CREATE UNIQUE INDEX NAME_INDEX ON tutorials_inf(name);

你可以在表上创建一个简单索引。只需从查询中省略 UNIQUE 关键字即可创建简单索引。简单索引允许表中有重复值。

You can create a simple index on a table. Just omit UNIQUE keyword from the query to create simple index. Simple index allows duplicate values in a table.

如果你想按降序对列中的值进行索引，可以在列名后添加保留字 DESC。

If you want to index the values in a column in descending order, you can add the reserved word DESC after the column name.

mysql> CREATE UNIQUE INDEX NAME_INDEX ON tutorials_inf (name DESC);

ALTER command to add and drop INDEX

有四类用于向表中添加索引的语句 −

There are four types of statements for adding indexes to a table −

  1. ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) − This statement adds a PRIMARY KEY, which means that indexed values must be unique and cannot be NULL.

  2. ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) − This statement creates an index for which values must be unique (with the exception of NULL values, which may appear multiple times).

  3. ALTER TABLE tbl_name ADD INDEX index_name (column_list) − This adds an ordinary index in which any value may appear more than once.

  4. ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) − This creates a special FULLTEXT index that is used for text-searching purposes.

以下是将索引添加到现有表中的示例。

Here is the example to add index in an existing table.

mysql> ALTER TABLE tutorials_inf ADD INDEX (id);

你还可以使用 DROP 子句和 ALTER 命令以相同的方式删除主键。尝试以下示例以删除上述创建的索引。

You can drop any INDEX by using DROP clause along with ALTER command. Try out the following example to drop above-created index.

mysql> ALTER TABLE tutorials_inf DROP INDEX (c);

你还可以使用 DROP 子句和 ALTER 命令以相同的方式删除主键。尝试以下示例以删除上述创建的索引。

You can drop any INDEX by using DROP clause along with ALTER command. Try out the following example to drop above-created index.

ALTER Command to add and drop PRIMARY KEY

你也可以添加主键。但请确保主键作用于 NOT NULL 列。

You can add primary key as well in the same way. But make sure Primary Key works on columns, which are NOT NULL.

以下为向现有表格添加主键的示例。这将首先使列成为非空，再将其添加为主键。

Here is the example to add primary key in an existing table. This will make a column NOT NULL first and then add it as a primary key.

mysql>  ALTER TABLE tutorials_inf MODIFY id INT NOT NULL;
mysql> ALTER TABLE tutorials_inf ADD PRIMARY KEY (id);

您可以使用 ALTER 命令放弃主键，如下所示：

You can use ALTER command to drop a primary key as follows:

mysql> ALTER TABLE tutorials_inf DROP PRIMARY KEY;

要删除一个不是主键的索引，你必须指定索引名称。

To drop an index that is not a PRIMARY KEY, you must specify the index name.

Displaying INDEX Information

您可以使用 SHOW INDEX 命令列出与表格关联的所有索引。在这种情况下，垂直格式输出（由 \G 指定）通常非常有用，以避免在长行中进行换行−

You can use SHOW INDEX command to list out all the indexes associated with a table. Vertical-format output (specified by \G) often is useful with this statement, to avoid long line wraparound −

尝试以下示例

Try out the following example

mysql> SHOW INDEX FROM table_name\G
........