mysql-索引

MySQL-索引 (MySQL - 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.

数据库索引是一种数据结构，可以提高表中操作的速度。 可以使用一个或多个列创建索引，这为快速随机查找和对记录访问的有效排序提供了基础。

While creating index, it should be taken into consideration which all columns will be used to make SQL queries and create one or more indexes on those columns.

创建索引时，应考虑将所有列都用于进行SQL查询并在这些列上创建一个或多个索引。

Practically, indexes are also a 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 the Database Search Engine to locate records very fast.

用户看不到索引，它们仅用于加速查询，并且数据库搜索引擎将使用它们非常快速地定位记录。

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

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

简单唯一索引 (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,...);

You can use one or more columns to create an index.

您可以使用一个或多个列来创建索引。

For example, we can create an index on tutorials_tbl using tutorial_author.

例如，我们可以使用tutorial_author在tutorials_tbl上创建索引。

CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author)

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

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

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

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

mysql> CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author DESC)

ALTER命令添加和删除INDEX (ALTER command to add and drop INDEX)

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

有四种类型的语句可向表添加索引-

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

  ALTER TABLE tbl_name ADD PRIMARY KEY(column_list) -此语句添加了PRIMARY KEY ，这意味着索引值必须唯一且不能为NULL。

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

  ALTER TABLE tbl_name ADD UNIQUE index_name(column_list) -此语句创建一个索引，该索引的值必须唯一(NULL值除外，该值可能出现多次)。

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

  ALTER TABLE tbl_name添加索引index_name(column_list) -这将添加一个普通索引，其中任何值都可能出现多次。

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

  ALTER TABLE tbl_name添加FULLTEXT index_name(column_list) -这将创建一个特殊的FULLTEXT索引，该索引用于文本搜索。

The following code block is an example to add index in an existing table.

以下代码块是在现有表中添加索引的示例。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

You can drop any INDEX by using the DROP clause along with the ALTER command.

您可以使用DROP子句以及ALTER命令删除任何INDEX。

Try out the following example to drop the above-created index.

尝试以下示例删除上面创建的索引。

mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

You can drop any INDEX by using the DROP clause along with the ALTER command.

您可以使用DROP子句以及ALTER命令删除任何INDEX。

ALTER命令添加和删除PRIMARY KEY (ALTER Command to add and drop the PRIMARY KEY)

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

您也可以以相同的方式添加主键。 但是，请确保主键可以在非NULL的列上使用。

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

以下代码块是在现有表中添加主键的示例。 这将使列NOT NOT NULL首先出现，然后将其添加为主键。

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

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

您可以使用ALTER命令来删除主键，如下所示：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

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

要删除不是PRIMARY KEY的索引，必须指定索引名称。

显示索引信息 (Displaying INDEX Information)

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

您可以使用SHOW INDEX命令列出与表关联的所有索引。 垂直格式输出(由\ G指定)通常对于此语句很有用，以避免长行换行-

Try out the following example −

尝试以下示例-

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

翻译自: https://www.tutorialspoint.com/mysql/mysql-indexes.htm

mysql-索引