What index types are there in mysql?

Index types include: 1. B-tree index, so that each row in the table will have a corresponding value on the index; 2. Hash index, which can be obtained based on the hash value corresponding to the index column. The record row of the table; 3. Ordinary index, which allows inserting duplicate values ​​and null values ​​​​in the columns that define the index; 4. Unique index, which can avoid duplication of data; 5. Primary key index, which is an index created for the primary key field; 6. Spatial index is an index established on fields of spatial data type; 7. Full-text index is used to find keywords in text; 8. Single-column index, that is, the index only contains one column of the original table.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Detailed explanation of MySQL index types

The type of index is related to the storage engine, and the index types supported by each storage engine are not necessarily exactly the same. MySQL indexes can be classified from a storage method, a logical perspective, and a practical usage perspective.

Storage method distinction

According to different storage methods, commonly used indexes in MySQL are physically divided into B-tree indexes There are two types of indexes: and HASH indexes. Each of the two different types of indexes has its own different scope of application.

1) B-tree index

B-tree index is also called BTREE index, currently most Indexes are stored using B-tree indexes.

B-tree index is a typical data structure, which mainly contains the following components:

• Leaf nodes: The entries contained directly point to the data rows in the table. Leaf nodes are connected to each other, and one leaf node has a pointer to the next leaf node.
• Branch node: Contains entries pointing to other branch nodes or leaf nodes in the index.
• Root node: A B-tree index has only one root node, which is actually the branch node located at the top of the tree.

Based on this tree data structure, each row in the table will have a corresponding value on the index. Therefore, when performing data query in the table, the row where the data is located can be located step by step according to the index value.

B-tree index can perform full key value, key value range and key value prefix query, and can also ORDER BY sort the query results. However, the B-tree index must follow the left prefix principle and the following constraints must be considered:

• The query must start from the leftmost column of the index.
• The query cannot skip an index column and must be matched from left to right.
• The storage engine cannot use the columns to the right of the range condition in the index.

2) Hash Index

Hash (Hash) is generally translated as "hash", and some are directly transliterated into "hash", that is The input of any length (also called pre-mapping, pre-image) is transformed into a fixed-length output through a hash algorithm, and the output is the hash value.

Hash Index is also called Hash Index or HASH Index. MySQL currently only supports this type of index for the MEMORY storage engine and HEAP storage engine. Among them, the MEMORY storage engine can support B-tree indexes and HASH indexes, and uses HASH as the default index.

HASH index is not based on a tree data structure to find data, but obtains the record rows of the table based on the hash value corresponding to the index column. The biggest feature of hash index is fast access speed, but it also has the following shortcomings:

• MySQL needs to read the value of the index column in the table to participate in hash calculation. Hash calculation It is a relatively time-consuming operation. In other words, building a hash index will take more time than a B-tree index.
• Cannot use HASH index sorting.
• HASH index only supports equality comparison, such as "=", "IN()" or "96b4fef55684b9312718d5de63fb7121".
• HASH index does not support partial matching of keys, because the HASH value is calculated through the entire index value.

Logical distinction

According to the specific purpose of the index, the indexes in MySQL are logically divided into the following 5 categories:

1) Ordinary index

Ordinary index is the most basic index type in MySQL. It has no restrictions and its only task is to speed up the system's processing of data. Access speed.

Normal indexes allow duplicate values ​​and null values ​​to be inserted into the columns in which the index is defined.

When creating a normal index, the keywords usually used are INDEX or KEY.

Example 1

The following creates an index named index_id on the id field in the tb_student table.

CREATE INDEX index_id ON tb_student(id);

2) Unique index

唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。

唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。

创建唯一索引通常使用 UNIQUE 关键字。

例 2

下面在 tb_student 表中的 id 字段上建立名为 index_id 的索引，SQL 语句如下：

CREATE UNIQUE INDEX index_id ON tb_student(id);

其中，id 字段可以有唯一性约束，也可以没有。

3) 主键索引

顾名思义，主键索引就是专门为主键字段创建的索引，也属于索引的一种。

主键索引是一种特殊的唯一索引，不允许值重复或者值为空。

创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。

4) 空间索引

空间索引是对空间数据类型的字段建立的索引，使用 SPATIAL 关键字进行扩展。

创建空间索引的列必须将其声明为 NOT NULL，空间索引只能在存储引擎为 MyISAM 的表中创建。

空间索引主要用于地理空间数据类型 GEOMETRY。对于初学者来说，这类索引很少会用到。

例 3

下面在 tb_student 表中的 line 字段上建立名为 index_line 的索引，SQL 语句如下：

CREATE SPATIAL INDEX index_line ON tb_student(line);

其中，tb_student 表的存储引擎必须是 MyISAM，line 字段必须为空间数据类型，而且是非空的。

5) 全文索引

全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。

全文索引允许在索引列中插入重复值和空值。

不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。

创建全文索引使用 FULLTEXT 关键字。

例 4

在 tb_student 表中的 info 字段上建立名为 index_info 的全文索引，SQL 语句如下：

CREATE FULLTEXT INDEX index_info ON tb_student(info);

其中，index_info 的存储引擎必须是 MyISAM，info 字段必须是 CHAR、VARCHAR 和 TEXT 等类型。

实际使用区分

索引在逻辑上分为以上 5 类，但在实际使用中，索引通常被创建成单列索引和组合索引。

1）单列索引

单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段进行索引。

单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

例 5

下面在 tb_student 表中的 address 字段上建立名为 index_addr 的单列索引，address 字段的数据类型为 VARCHAR(20)，索引的数据类型为 CHAR(4)。SQL 语句如下：

CREATE INDEX index_addr ON tb_student(address(4));

这样，查询时可以只查询 address 字段的前 4 个字符，而不需要全部查询。

2）多列索引

组合索引也称为复合索引或多列索引。相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

例如，在表中的 id、name 和 sex 字段上建立一个多列索引，那么，只有查询条件使用了 id 字段时，该索引才会被使用。

例 6

下面在 tb_student 表中的 name 和 address 字段上建立名为 index_na 的索引，SQL 语句如下：

CREATE INDEX index_na ON tb_student(name,address);

该索引创建好了以后，查询条件中必须有 name 字段才能使用索引。

提示：一个表可以有多个单列索引，但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。比如，在一个表中创建了一个组合索引(c1，c2，c3)，在实际查询中，系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1，c2)和多列索引(c1，c2，c3)。

【相关推荐：mysql视频教程】

The above is the detailed content of What index types are there in mysql?. For more information, please follow other related articles on the PHP Chinese website!