Detailed explanation of MySQL database index

Today I will continue to introduce you to MySQL related knowledge. The main content of this article is MySQL index related content.

1. Introduction to MySQL Index

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

2. Detailed explanation of five types of MySQL indexes

1. General index

A common index is a common index in the MySQL database. There are no special requirements for the data in the column to which a common index is added. The role of a common index is to speed up differential queries.
The following is an example of adding a common index SQL statement when creating a data table:

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 adding an index, followed by the index name, and the columns in parentheses are the columns to which the index is to be added.
In all index-related SQL statements introduced in this article, if there is no special explanation, 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 a data table, an example of adding a new common index SQL statement to the table is as follows:

alter table exp add index exp_id(id);

The execution results are as follows:

The following is an example of a SQL statement to delete a common index after creating a data table:

alter table drop index exp_name;

The execution results are 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 name of the index in the table, we can execute the following SQL command to query:

show index from exp;

Among them, exp is the table name, and the command execution results are 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 MUL on the Key column, which means that a normal index has been added to the column.

(II) Unique Index

A unique index is a common index that requires that all values ​​in the column to which the index is added can only appear once. Unique indexes are usually added to fields such as ID number and student number, but cannot be added to fields such as name.
Adding a unique index is almost the same as adding a normal index, except that the keywords key and index of the normal index are replaced with unique key and unique index.
The following is an example of a SQL statement to add a unique index when creating a data table:

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

The execution results of the above command are as follows:

It can be seen that when the desc command is used to query the table structure of a field with a unique index added, UNI will be displayed in the Key column, indicating that a unique index has been added to the field.
An example of a SQL statement to add a unique index after creating a data table is as follows:

The following is an example of a SQL statement to delete a unique index:

alter table exp drop index name;

The execution results are as follows:

(III) Primary key index

The primary key index has the fastest query speed among all indexes in the database, and each data table can only have one primary key index column. The columns of the primary key index are not allowed to have 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);

When you add a primary key index to a column, PRI will be displayed in the Key column when you view the table structure in desc, as shown below:

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 by index.
Sometimes, when we try to delete the primary key index, MySQL refuses. This may be because the auto_increment attribute is added to the field. We can delete the primary key index of the field by removing the field modifier, as shown below:

(IV) Composite Index

If we want to create an index that contains different columns, we can create a composite index. In fact, composite indexes are frequently used in business scenarios. For example, if we want to record the content of a data packet, we need to use the IP and port number as the basis for identifying the data packet. In this case, we can create a composite index for the IP address column and the port number column. The following are examples of SQL statements for creating composite, adding, and deleting indexes:

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;

Note that a composite index is equivalent to a multi-column primary key index. Therefore, any column to which the composite index is added cannot have empty data, and the data in these columns cannot be exactly the same, otherwise the MySQL database will report an error. As shown below:

(V) Full-text index

Full-text indexing is mainly used to solve the problem of fuzzy matching in large data volumes. If the amount of data in a field in the database is very large, the speed will become very slow if we want to use the like+wildcard method to search. In this case, we can use full-text indexing to speed up fuzzy queries. The principle of full-text indexing is to analyze the keywords and their frequency of occurrence in the text through word segmentation technology, and then create indexes in turn. The use of full-text indexes is closely related to the database version, data table engine, and even field type. The main limitations are as follows:
1. Full-text indexing is only supported after MySQL version 3.2.
2. MySQL version 5.7 and later had a built-in ngram plug-in, and full-text indexing began to support Chinese.
3. In versions prior to MySQL 5.6, only the MyISAM engine supports full-text indexing.
4. In versions after MySQL 5.6, both MyISAM engine and InnoDB engine support full-text indexing.
5. Only fields with data types of char, varchar, and text support adding full-text indexes.
The SQL commands for creating, adding, and deleting full-text indexes are as follows:

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;

Some of the execution results are as follows:

After creating a full-text index, you cannot use the like+wildcard method for fuzzy search. The use of full-text index has its own specific syntax, as shown below:

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

The fields in the brackets after match contain the full-text index, and the fields in the brackets after against contain the content to be fuzzy matched.
In addition, the role of full-text index is not unique. In many scenarios, we do not use the built-in full-text index of the MySQL database, but use similar third-party indexes to achieve the same function.

3. MySQL index usage principles

1. Indexing is a typical "trading space for time" strategy. It consumes computer storage space but speeds up queries.
2. Although adding indexes speeds up the query speed, it slows down the insertion and deletion speeds. Because additional index operations are required when inserting and deleting data.
3. The more indexes, the better. There is no need to add indexes when the amount of data is not large.
4. If the values ​​of a table need to be frequently inserted and modified, it is not suitable to create an index. On the contrary, if the values ​​of a field in a table need to be frequently queried, sorted, and grouped, an index needs to be created.
5. If a field meets the conditions for creating a unique index, do not create a normal index.

Summarize

This is the end of this article about the detailed explanation of MySQL database index. For more relevant MySQL index content, please search 123WORDPRESS.COM’s previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!