Detailed Explanation of Types and Advantages and Disadvantages of Mysql Index

IndexIt is a special file (the indexes on InnoDB data tables are a part of the tablespace), which contains pointers to all records in the data table.
Note:
[1]Indexes are not万能的！Indexes can speed up data retrieval operations, but they will slow down data modification operations. Each time a data record is modified, the index must be refreshed. To compensate for this defect to some extent, many SQL commands have a DELAY_KEY_WRITE item. This option temporarily prevents MySQL from refreshing the index immediately after each new record is inserted and each existing record is modified in the command, and the index refresh will be delayed until all records are inserted/After the modification is completed. In the case of needing to insert many new records into a data table, the DELAY_KEY_WRITE option will be very obvious.
[2In addition, indexes will also occupy a considerable amount of space on the hard disk. Therefore, indexes should only be established for data columns that are frequently queried and sorted. Note that if a data column contains a lot of repeated content, establishing an index for it will not have much practical effect.
In theory, it is completely 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 to16Individual.

1. Indexes of InnoDB data tables

Compared to MyISAM data tables, the importance of indexes to InnoDB data is much greater. On InnoDB data tables, the importance of indexes to InnoDB data tables is even greater. Indexes not only play a role in searching for data records but also serve as the foundation of the row-level locking mechanism. 'Row-level locking' refers to locking individual records being processed during the execution of a transaction operation, preventing other users from accessing them. This locking will affect (but is not limited to) SELECT…LOCK IN SHARE MODE, SELECT…FOR UPDATE commands, as well as INSERT, UPDATE, and DELETE commands.
For efficiency considerations, the row-level locking of InnoDB data tables actually occurs on their indexes, not on the data table itself. It is obvious that the row-level locking mechanism can only take effect when there is a suitable index available for locking on the relevant data table.

2. Limitation

If the WHERE clause of the query condition contains an inequality sign (WHERE column != ...), MySQL will be unable to use the index.
Likewise, if the WHERE clause query condition uses a function (WHERE DAY(column) = …), MySQL will also be unable to use the index.
Similarly, if the WHERE clause query condition uses a function (WHERE DAY(column) = …), MySQL will also be unable to use the index.
If the WHERE clause query condition uses comparison operators LIKE and REGEXP, MySQL can only use an index when the first character of the search pattern is not a wildcard. 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 the ORDER BY operation, MySQL will only use an index when the sorting condition is not a query condition expression. (Although so, in multiple table queries, even if indexes are available, those indexes are not very helpful in speeding up ORDER BY)
Even if an index is created for a data column that contains many duplicate values, it will not be very effective. For example, if a data column contains only values such as '0'/1′ or 'Y'/There is no need to create an index for 'N' values.　

Regular index, unique index, and primary index

1Regular index

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

2Unique index
A regular 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 personal information' data table.
If it can be determined that a certain data column will only contain unique values, when creating an index for this data column, it should be defined as a unique index using the keyword UNIQUE. The benefits of this approach are: first, it simplifies MySQL's management of this index, making it more efficient; second, MySQL will automatically check whether the value of this field in the new record has already appeared in the field of some record when a new record is inserted into the data table. If so, MySQL will reject the insertion of that new record. In other words, a unique index can ensure the uniqueness of data records. In fact, in many cases, the purpose of creating a unique index is often not to improve access speed, but simply to avoid data duplication.

3. Primary index

As has been emphasized repeatedly before: an index must be created for the primary key field, which is known as the 'primary index'. The only difference between the primary index and the unique index is that the keyword used when defining the former is PRIMARY rather than UNIQUE.

4. Foreign key index

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

5. Composite index

Indexes can cover multiple data columns, such as the 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, it can use the composite index INDEX(columnA, columnB). However, this usage is only applicable to combinations of data columns arranged in the composite index. For example, INDEX(A, B, C) can be used as an index for A or (A, B), but not as an index for B, C, or (B, C).

6. Index length

When defining indexes for CHAR and VARCHAR type data columns, the length of the index can be limited to a given number of characters (this number must be less than the maximum number of characters allowed by the field). The advantage of this is that it can generate a smaller index file with faster search speed. In most applications, string data in the database is mainly composed of various names, so the length of the index can be set to10~15characters is already enough to narrow the search range to a very few data records.
When creating indexes for BLOB and TEXT type data columns, it is necessary to limit the length of the index; the maximum index length allowed by MySQL is255characters.
Full-text index

　　 A common index on a text field can only speed up the search operation for strings appearing at the beginning of the field content (that is, the first character of the field content). If the field stores a larger segment of text composed of several, even multiple, words, the common index is not very effective. Such searches often appear in the form of LIKE %word%, which is complex for MySQL, and if a large amount of data needs to be processed, the response time will be very long.
Such occasions are exactly where full-text indexes (full}}-text index) can be fully utilized. When creating this type of index, MySQL will create a list of all words appearing in the text, and the query operation will retrieve relevant data records based on this list. Full-text indexes can be created with the data table, or used later when necessary.

The following command adds:
ALTER TABLE tablename ADD FULLTEXT(column1, column2)

With full-text indexes, you can use SELECT query commands to retrieve data records that contain one or more given words. The basic syntax of such query commands is as follows:
SELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST(‘word1′, ‘word2′, ‘word3′)

The following command will take column1and column2There is word in the field1, word2and word3All data records are queried out.

NoteInnoDB data tables do not support full-text indexes.

Optimization of queries and indexes

　　　　　 Only when there is enough test data in the database, its performance test results have practical reference value. If there are only a few hundred data records in the test database, they are often loaded into memory after executing the first query command, which will make subsequent query commands execute very quickly – regardless of whether an index is used. Only when the records in the database exceed1000, and the total amount of data also exceeds the total amount of memory on the MySQL server, the performance test results of the database are meaningful.

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

The possible_keys column gives the various indexes that MySQL can use when searching for data records. The key column is the index actually selected by MySQL, and the length of this index in bytes is given in the key_len column. For example, for an INTEGER data column index, this byte length will be4If a composite index is used, you can also see which parts of it MySQL specifically uses in the key_len column. As a general rule, the smaller the value in the key_len column, the better (meaning faster).
The ref column indicates the name of the data column in another table in the relationship. The row column is the number of data rows that MySQL is expected to read from this table when executing this query. The product of all numbers in the row column can give us a rough idea of how many combinations the query needs to handle.
Finally, the extra column provides more information related to 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.

That's all for the content of this article. I hope it will be helpful to everyone's learning and that everyone will support the Yelling Tutorial more.

Declaration: The content of this article is from the Internet, the copyright belongs to the original author. The content is contributed and uploaded by Internet users spontaneously. This website does not own the copyright, has not been manually edited, and does not assume any relevant legal liability. If you find any content suspected of copyright infringement, please send an email to notice#w3Please report any infringement by sending an email to codebox.com (replace # with @ when sending emails), and provide relevant evidence. Once verified, this site will immediately delete the infringing content.