Detailed explanation of the types, advantages and disadvantages of Mysql index_MySQL

Index is a special file (the index on the InnoDB data table is a component of the table space), they contain reference pointers to all records in the data table.
Note:
[1] Indexes are not omnipotent! Indexes can speed up data retrieval operations but make data modification operations slower. Every time a data record is modified, the index must be refreshed. In order to compensate for this shortcoming in a certain program, many SQL commands have a DELAY_KEY_WRITE item. The function of this option is to temporarily prevent MySQL from refreshing the index immediately after each new record is inserted and each existing one is modified by this command. The refresh of the index will wait until all records are inserted/modified. In situations where many new records need to be inserted into a data table, the role of the DELAY_KEY_WRITE option will be very obvious.
[2] In addition, indexes take up considerable space on the hard disk. Therefore, only the most frequently queried and most frequently sorted data columns should be indexed. Note that if a data column contains a lot of duplicate content, indexing it will not have much practical effect.
Theoretically, it is possible to build an index for each field in the data table, but MySQL limits the total number of indexes in the same data table to 16.

1. Index of InnoDB data table

Compared with MyISAM data tables, indexes are much more important for InnoDB data. On InnoDB data tables, indexes are much more important to InnoDB data tables. On InnoDB data tables, indexes not only play a role in searching data records, but are also the basis of the data row-level locking mechanism. "Data row-level locking" means locking individual records being processed during the execution of transaction operations to prevent other users from accessing them. This locking will affect (but is not limited to) the SELECT...LOCK IN SHARE MODE, SELECT...FOR UPDATE commands, and the INSERT, UPDATE, and DELETE commands.
For efficiency reasons, row-level locking of InnoDB data tables actually occurs on their indexes, not on the data table itself. Obviously, the data row-level locking mechanism can only be effective when the relevant data table has a suitable index for locking.

2. Restrictions

If there is an inequality sign (WHERE coloum != ...) in the query condition of the WEHERE clause, MySQL will not be able to use the index.
Similarly, if a function (WHERE DAY(column) = ...) is used in the query condition of the WHERE clause, MySQL will not be able to use the index.
In a JOIN operation (when data needs to be extracted from multiple data tables), MySQL can only use indexes when the data types of the primary key and foreign key are the same.
If the comparison operators LIKE and REGEXP are used in the query conditions of the WHERE clause, MySQL can use the index only if the first character of the search template is not a wildcard character. For example, if the query condition is LIKE 'abc%', MySQL will use the index; if the query condition is LIKE '%abc', MySQL will not use the index.
In an ORDER BY operation, MySQL uses the index only if the sort condition is not a query condition expression. (However, in queries involving multiple data tables, even if indexes are available, those indexes have no effect in speeding up ORDER BY)
If a data column contains many duplicate values, even if it is indexed, it will not have good results. For example, if a data column contains only values ​​such as "0/1" or "Y/N", there is no need to create an index for it.

Normal index, unique index and primary index

1. Ordinary index

The only task of a normal index (an index defined by the keyword KEY or INDEX) is to speed up access to data. Therefore, indexes should only be created for those data columns that appear most frequently in query conditions (WHERE column = ...) or sorting conditions (ORDER BY column). Whenever possible, you should choose a data column with the neatest and most compact data (such as an integer type data column) to create an index.

2. Unique index
Normal index allows the indexed data column to contain duplicate values. For example, because people may have the same name, the same name may appear twice or more in the same "Employee Profile" data table.
If you can determine that a certain data column will only contain values ​​that are different from each other, you should use the keyword UNIQUE to define it as a unique index when creating an index for this data column. The advantages of this are: First, it simplifies MySQL's management of this index, and the index becomes more efficient; second, MySQL will automatically check the value of this field in the new record when a new record is inserted into the data table. Whether this field has already appeared in a record; if so, MySQL will refuse to insert the new record. In other words, a unique index can ensure the uniqueness of data records. In fact, on many occasions, the purpose of creating unique indexes is not to increase access speed, but just to avoid data duplication.

3. Main Index

It has been emphasized many times before: an index must be created for the primary key field. This index is the so-called "primary index". The only difference between a primary index and a unique index is that the keyword used when defining the former is PRIMARY instead of UNIQUE.

4. Foreign key index

If a foreign key constraint is defined for a foreign key field, MySQL will define an internal index to help it manage and use the foreign key constraints in the most efficient way.

5. Composite index

Index can cover multiple data columns, such as INDEX(columnA, columnB) index. The characteristic of this index is that MySQL can selectively use such an index. If the query operation only needs to use an index on the columnA data column, you can use the composite index INDEX (columnA, columnB). However, this usage only applies to the combination of data columns that are ranked first in the composite index. For example, INDEX(A, B, C) can be used as an index of A or (A, B), but cannot be used as an index of B, C or (B, C).

6. Index length

When defining indexes for CHAR and VARCHAR type data columns, you can limit the length of the index to a given number of characters (this number must be less than the maximum number of characters allowed in this field). The advantage of this is that it can generate an index file that is smaller in size and faster in retrieval speed. In most applications, the string data in the database are mostly based on various names. Setting the index length to 10 to 15 characters is enough to narrow the search scope to a few data records. .
When creating indexes for BLOB and TEXT type data columns, the length of the index must be limited; the maximum index length allowed by MySQL is 255 characters.
Full text index

A normal index on a text field can only speed up the retrieval of the string that appears at the front of the field content (that is, the characters at the beginning of the field content). If the field stores a larger piece of text composed of several or even multiple words, ordinary indexes will be of little use. This kind of retrieval often appears in the form of LIKE %word%, which is very complicated for MySQL. If the amount of data that needs to be processed is large, the response time will be very long.
This is where full-text indexes can come in handy. When generating this type of index, MySQL will create a list of all words that appear in the text, and query operations will retrieve relevant data records based on this list. The full-text index can be created together with the data table, or it can be used later when necessary

Add the following command:
ALTER TABLE tablename ADD FULLTEXT(column1, column2)

With a full-text index, you can use the SELECT query command to retrieve data records that contain one or more given words. The following is the basic syntax of this type of query command:
SELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST(‘word1′, ‘word2′, ‘word3′)

The above command will query all the data records containing word1, word2 and word3 in the column1 and column2 fields.

Note: InnoDB data tables do not support full-text indexing.

Optimization of queries and indexes

Only when there is enough test data in the database, its performance test results will have actual reference value. If there are only a few hundred data records in the test database, they are often all loaded into memory after the first query command is executed, which will make subsequent query commands execute very quickly - regardless of whether indexes are used or not. The database performance test results are meaningful only when the number of records in the database exceeds 1,000 and the total amount of data exceeds the total memory on the MySQL server.

When people are not sure which data columns should be used to create indexes, people can often get some help from the EXPLAIN SELECT command. This is actually just prefixing an ordinary SELECT command with the EXPLAIN keyword. With this keyword, MySQL will not execute the SELECT command, but analyze it. MySQL will list information such as the execution process of the query and the indexes used (if any) in the form of a table.
In the output of the EXPLAIN command, column 1 is the name of the data table read from the database, and they are arranged in the order they are read. The type column specifies the association relationship (JOIN) between this data table and other data tables. Among various types of relationships, the most efficient one is system, followed by const, eq_ref, ref, range, index and All (All means: corresponding to each record in the upper-level data table, this data All records in the table must be read - this can often be avoided using an index).

possible_keys data column gives the various indexes that MySQL can use when searching for data records. The key data column is the index actually selected by MySQL. The length of this index in bytes is given in the key_len data column. For example, for an index on an INTEGER data column, the byte length would be 4. If a composite index is used, you can also see which parts of it are used by MySQL in the key_len data column. As a general rule, the smaller the value in the key_len data column, the better (meaning faster).
The ref data column gives the name of the data column in another data table in the relationship. The row data column is the number of data rows that MySQL expects to read from this data table when executing this query. The product of all the numbers in the row data column gives us an idea of ​​how many combinations this query needs to process.
Finally, the extra data column provides more information about the JOIN operation. For example, if MySQL must create a temporary data table when executing this query, you will see the words using temporary in the extra column.

The above is the entire content of this article. I hope it will be helpful to everyone’s study and I hope you will support me a lot.