Mysql 简明教程

MySQL - INDEXES

索引是一种数据结构，可提升数据库表格上操作的速度。这是一种特殊类型的查找表格，指向数据。索引可以创建于一个或多个栏位中，为快速随机查找和记录存取的有效排序提供基础。

An index is a data structure that improves the speed of operations on a database table. They are a special type of lookup tables pointing to the data. Indexes can be created on one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.

用户无法看见索引，它们仅用于加速查询，并会被数据库搜寻引擎用于以极快的速度查找记录。

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.

Types of MySQL Indexes

索引可定义于 MySQL 表格的单一或多个栏位中。决定于表格栏位中新增索引的决定依赖于需要搜寻的数据类型。MySQL 提供以下类型的索引：

Indexes can be defined on single or multiple columns of a MySQL table. The decision to add indexes on a table column(s) depends on the type of data that needs to be searched. MySQL provides the following types of indexes −

  1. Simple Index

  2. Unique Index

  3. Primary Key Index

  4. Fulltext Index

  5. Descending Index

Simple Index

简单索引是一种基本类型的索引，在此索引中插入栏位的值很容易就能被搜寻到。在这种情况下，此栏位可能包含重复值或 NULL。

A simple index is a basic type of index where the values inserted into the column, containing this index, are searched easily. In such case, the column can contain duplicate values or NULL.

Unique Index

唯一索引不允许将任何重复值插入表格栏位 (已定义索引的栏位)。它可以新增到表格的单一或多个栏位。如果新增到单一栏位，则此栏位的值必须唯一。但如果新增到多个栏位，则这些栏位中的值组合必须唯一。

A Unique index does not allow any duplicate values to be inserted into a table column (where the index is defined on).It can be added to single or multiple columns of a table. If it is added to a single column, the values of that column must be unique. But if it is added to multiple columns, the combination of values in these columns must be unique.

Primary Key Index

主键索引是唯一索引的延伸，因为主键栏位一定包含唯一值且这些值不可是 NULL。主键可以设定为数据库表格的单一栏位，或多个栏位 (不推荐)。

Primary Key Index is an extension of unique index, as the primary key column must always contain unique values and these values must not be NULL. Primary key can be set to a single column of a database table, or multiple columns as well (which is not recommended).

Fulltext Index

在数据库中，有时你需要搜寻一整块文字，而不是某个记录。你可以针对它使用全文索引。顾名思义，此索引用于让表格中的文字搜寻变得更为容易。

In a database, sometimes you would have to search for a blob of text instead of a record. You can use fulltext index for it. As its name suggests, it is used to make the text searches in a table easier.

Descending Index

下降索引仅适用于 MySQL 的 8.0 之后的版本。它是一种用于以相反顺序存储数据的简单索引。使用此索引，我们可轻松搜寻插入数据库表格的最新值。

The descending index is only available in MySQL versions after 8.0. It is simple index used to store data in a reverse order. Using this index, it is easy to search for the latest values inserted into the database table.