What is the use of indexes in MySQL database

1. Introduction to MySQL indexes

The index is a "directory" that the MySQL database adds to one or several columns in the table in order to speed up data query. The index of MySQL is a special file, but the index of the table of the InnoDB type engine (we will explain the MySQL engine in a future article) is an integral part of the table space.
MySQL database supports a total of 5 types of indexes, namely ordinary indexes, unique indexes, primary key indexes, composite indexes and full-text indexes. Below, I will introduce these four types of indexes one by one.

2. Detailed explanation of the five types of MySQL indexes

(1) Ordinary index

Ordinary index is an ordinary index in the MySQL database. Add the column pairs of the ordinary index. There are no special requirements for the data. The role of ordinary indexes is to speed up poor queries.
An example of adding a common index SQL statement when creating a data table is as follows:

create table exp(id int , name varchar(20),index exp_name(name));

Or replace index with key, as follows:

create table exp (id ,int , name varahcr (20) ,  key exp_name(name));

In the above SQL command, key or index means Add an index, followed by the index name, followed by the column in parentheses to add the index.
All index-related SQL statements introduced in this article, if there is no special instructions, index can be replaced by key. In order to save the length of the article, this point will not be repeated in the future.
In addition, we can also add an index without specifying the name of the index. In this case, MySQL will automatically add an index name with the same name as the field to the index.
The execution results are as follows:

After creating the data table, add a new ordinary index to the table. The SQL statement example is as follows:

alter table exp add index exp_id(id);

The execution results are as follows:

An example of a SQL statement to delete a common index after creating a data table is as follows:

alter table drop index exp_name;

The execution result is as follows:

Note that in the above command, exp_name is the name of the index rather than the name of the field containing the index. If we forget the index name in the table, we can execute the following SQL command to query:

show index from exp;

Among them, exp is the table name. The execution result of this command is as follows:

#As can be seen from the above pictures, after adding a normal index, when using desc to view the table structure, You will find that MUL appears on the Key column, which means that a normal index is added to the column.

(2) Unique index

The unique index is based on the ordinary index. It is required that all values ​​of the column added to the index can only appear once. Commonly used terms for unique indexes are added in fields such as ID number and student number, but cannot be added in fields such as name and name.
The addition of a unique index is almost identical to that of an ordinary index, except that the keyword key and index of the ordinary index must be replaced by unique key and unique index.
An example of the SQL statement to add a unique index when creating a data table is as follows:

create table exp (id int, name varchar(20), unique key (name))；

The execution result of the above command is as follows:

As can be seen, Add a unique index field. When using the desc command to query the table structure, UNI will be displayed in the Key column, indicating that a unique index has been added to the field.
The SQL statement example to add a unique index after creating the data table is as follows:

The SQL statement example to delete the unique index is as follows:

alter table exp drop index name;

The execution results are as follows:

(3) Primary key index

The primary key index is the fastest query among all indexes in the database, and each data table There can only be 1 primary key index column. Columns indexed by the primary key do not allow duplicate data or null values.
Adding and deleting primary key indexes are very similar to ordinary indexes and unique indexes, except that the key is replaced by the primary key. The relevant SQL commands are as follows:

create table exp(id int ,name varchar(20), primary key (id));alter table exp add primary key (id);

Added the column of the primary key index, the PRI will be displayed on the Key column when desc views the table structure, as follows:

To delete the primary key index, you can execute the command:

alter table exp drop primary key;

Note that in this SQL statement, key cannot be replaced with index.
Sometimes, when we try to delete the primary key index, MySQL rejects it. This may be because the auto_increment attribute is added to the field. We can delete the field modifier and delete the primary key index of the field, as follows Shown:

（四）复合索引

如果想要创建一个包含不同的列的索引，我们就可以创建符合索引。其实，复合索引在业务场景中应用的非常频繁，比如，如果我们想要记录数据包的内容，则需要将IP和端口号作为标识数据包的依据，这时就可以把IP地址的列和端口号的列创建为复合索引。复合、添加和删除索引创建SQL语句示例如下：

create table exp (ip varchar(15),port int ,primary key (ip,port));
alter table exp add pirmary key(ip ,port);
alter table exp dorp priamary key;

复合索引在创建后，在使用desc查看数据表结构时，会在Key列中发现多个PRI，这就表示这些含有PRI的列就是复合索引的列了。如下所示：

注意，复合索引相当于一个多列的主键索引，因此，添加复合索引的任何一个列都不允许数据为空，并且这些列不允许数据完全相同，否则MySQL数据库会报错。如下所示：

（五）全文索引

全文索引主要是用于解决大数据量的情况下模糊匹配的问题。如果数据库中某个字段的数据量非常大，那么如果我们想要使用like+通配符的方式进行查找，速度就会变得非常慢。针对这种情况，我们就可以使用全文索引的方式，来加快模糊查询的速度。全文索引的原理便是通过分词技术，分析处文本中关键字及其出现的频率，并依次建立索引。全文索引的使用，与数据库版本、数据表引擎乃至字段类型息息相关，主要限制如下：
1、MySQL3.2版本以后才支持全文索引。
2、MySQL5.7版本以后MySQL才内置ngram插件，全文索引才开始支持中文。
3、MySQL5.6之前的版本，只有MyISAM引擎才支持全文索引。
4、MySQL5.6以后的版本，MyISAM引擎和InnoDB引擎都支持全文索引。
5、只有字段数据类型为char、varchar、以及text的字段才支持添加全文索引。
创建、添加以及删除全文索引SQL命令如下：

create table exp (id int ,content text ,filltext key (content))engine=MyISAM;
alter table exp add fulltext index (content);
alter table exp drop index content;

部分执行结果如下：

在创建了全文索引后，也不能够使用like+通配符的方式进行模糊查询，全文索引的使用有其特定的语法，如下所示：

select * from exp where match(content) against ('a');

其中，match后面的括号里是含有全文索引的字段，against后面的括号里是要模糊匹配的内容。
此外，全文索引的作用并不是唯一的，在很多场景下，我们并不会使用MySQL数据库内置的全文索引，而是使用第三方类似的索引以实现相同的功能。

三、MySQL索引使用原则

1、索引是典型的“以空间换时间”的策略，它会消耗计算机存储空间，但是会加快查询速度。
2、索引的添加，尽管加快了在查询时的查询速度，但是会减慢在插入、删除时的速度。因为在插入、删除数据时需要进行额外的索引操作。
3、索引并非越多越好，数据量不大时不需要添加索引。
4、如果一个表的值需要频繁的插入和修改，则不适合建立索引，反制，如果一个表中某个字段的值要经常进行查询、排序和分组的字段则需要建立索引。
5、如果一个字段满足建立唯一性索引的条件，就不要建立普通索引。

The above is the detailed content of What is the use of indexes in MySQL database. For more information, please follow other related articles on the PHP Chinese website!