Shulou(Shulou.com)06/01 Report--

This article will explain in detail about the use of the index in the MySQL database. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

I. brief introduction of MySQL index

Index is a kind of "catalog" added by MySQL database to one or several columns of the table in order to speed up the data query. The index of the MySQL is a special file, but the index of the table of the InnoDB type engine (which we will explain in a future article on the engine of MySQL) is an integral part of the tablespace.

MySQL database supports five types of indexes, namely, general index, unique index, primary key index, composite index and full-text index. Below, I will introduce each of these four types of indexes.

II. Detailed explanation of five types of MySQL Index (1) General Index

Ordinary index is a kind of common index in MySQL database. There are no special requirements for data when adding ordinary index. The function of ordinary index is to speed up the speed of differential query.

An example of adding a normal index SQL statement when creating a datasheet 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 to add an index, followed by the name of the index, followed by the column to be added in parentheses.

All the index-related SQL statements introduced in this article can be replaced with key without special instructions, which will not be repeated later in order to save the length of the article.

In addition, we can add an index without specifying the name of the index, and MySQL will automatically add an index name with the same name as the field.

The implementation results are as follows:

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

Alter table exp add index exp_id (id)

The implementation results are as follows:

An example of a SQL statement that deletes a normal index after creating a data table is as follows:

Alter table drop index exp_name

The implementation 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 that contains the index, and if we forget the index name in the table, we can execute the following SQL command entry query:

Show index from exp

Where exp is the table name, and the execution result of this command is as follows:

As you can see from the above pictures, after adding a normal index, when you use desc to view the table structure, you will find that MUL appears on the Key column, which means that the column has a normal index.

(II) uniqueness index

A unique index, based on a general index, requires that all values of the column in which the index is added can only appear once. Unique index common words are added to the injected ID number, student number and other fields, but not in the injected name, and other fields.

The addition of a unique index is almost exactly the same as a normal index, except that the keywords key and index of the ordinary index are replaced with unique key and unique index.

An example of an SQL statement that adds a unique index when creating a datasheet 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 you can see, for a field that adds a unique index, when you use the desc command to query the table structure, UNI appears in the Key column, indicating that the field has added a unique index.

An example of a SQL statement that adds a unique index after creating a datasheet is as follows:

An example of a SQL statement to delete a uniqueness index is as follows:

Alter table exp drop index name

The implementation results are as follows:

(3) Primary key index

The primary key index is the fastest query of all the indexes in the database, and each data table can only have 1 primary key index column. Columns indexed by the primary key do not allow duplicate data or null values.

Adding and removing primary key indexes is very similar to normal indexes and unique indexes, except that key is replaced with 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)

A column with a primary key index displays PRI on the Key column when desc views the table structure, as follows:

The deletion of the primary key index 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, we reject it after MySQL. This may be because the auto_increment attribute has been added to the field. We can delete the field modifier and delete the primary key index of the field, as shown below:

(IV) Composite index

If we want to create an index that contains different columns, we can create a matching index. In fact, composite indexes are frequently used in business scenarios. For example, if we want to record the contents of a packet, we need to use IP and port number as the basis for identifying the packet. In this case, we can create a composite index of the column of the IP address and the column of the port number. Examples of compound, add, and delete index creation SQL statements are as follows:

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

After the composite index is created, when you use desc to view the structure of the data table, you will find multiple PRI in the Key column, which means that these columns containing PRI are the columns of the composite index. As follows:

Note that the composite index is equivalent to a multi-column primary key index, so no column adding the composite index allows data to be empty, and these columns do not allow the data to be exactly the same, otherwise the MySQL database will report an error. As follows:

(5) full-text index

Full-text index is mainly used to solve the problem of fuzzy matching in the case of large amount of data. If the amount of data in a field in the database is very large, then if we want to find it using like+ wildcards, it will be very slow. In view of this situation, we can use full-text index to speed up the speed of fuzzy query. The principle of full-text indexing is to analyze the keywords and their occurrence frequency in the text through word segmentation technology, and establish the index in turn. The use of full-text indexing is closely related to database version, datasheet engine and even field types. The main restrictions are as follows:

1. Full-text indexing is not supported until after MySQL3.2 version.

2. The ngram plug-in is not built into MySQL until after the MySQL5.7 version, and the full-text index begins to support Chinese characters.

3. In previous versions of MySQL5.6, only the MyISAM engine supports full-text indexing.

4. In later versions of MySQL5.6, both MyISAM engine and InnoDB engine support full-text indexing.

5. Full-text indexing is supported only for fields with field data types of char, varchar, and text.

The create, add, and delete full-text index SQL commands 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

The partial implementation results are as follows:

After creating a full-text index, you cannot use like+ wildcards to make fuzzy queries. The use of full-text indexes has its own specific syntax, as shown below:

Select * from exp where match (content) against ('a')

Among them, the parenthesis after match is the field containing the full-text index, and the parenthesis after against is the content to be blurred matching.

In addition, the role of full-text indexing is not unique, in many scenarios, we do not use the full-text index built into the MySQL database, but use third-party similar indexes to achieve the same function.

III. Principles for the use of MySQL indexes

1. Index is a typical "space for time" strategy, which consumes computer storage space, but speeds up the query speed.

2. The addition of the index will speed up the query speed when querying, but will slow down the speed when inserting and deleting. Because additional indexing operations are required when inserting and deleting data.

3. The more indexes, the better. When the amount of data is small, there is no need to add indexes.

4. If the value of a table needs to be inserted and modified frequently, it is not suitable to establish an index. If the value of a field in a table needs to be queried, sorted and grouped frequently, it needs to be indexed.

5. If a field meets the conditions for establishing a unique index, do not build a general index.

This is the end of the article on "what is the use of indexing in MySQL database". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.