The disadvantages of the MySQL index and what the MySQL index does in practice

The following articles focus on the shortcomings of the MySQL index and what is important to note about the MySQL index in practice. We may not know that too much use of the index can lead to abuse. So the MySQL index has its drawbacks:
While indexes greatly speed up queries, they slow down the process of updating tables, such as INSERT, UPDATE, and DELETE. Because when you update a table, MySQL not only saves the data, it also saves 1 index file.
Index files that take up disk space to index. This is not a serious problem in the general case, but if you create multiple composite indexes on a large table, the index file will swell very quickly.
Indexes are only one factor in improving efficiency. If your MySQL has a large data table, you need to spend time researching the best MySQL indexes or optimizing queries.
Considerations for using indexes
Here are some tips and considerations when using an index:
1. The index will not contain columns with NULL values
Any column containing an NULL value will not be included in the MySQL index, and any column containing an NULL value in a composite index will not be valid for the composite index. So we don't want the default value of the field to be NULL when we design the database.
2. Use a short index
Index a string, specifying a prefix length if possible. For example, if you have a column of CHAR(255), do not index the entire column if the first 10 or 20 characters are all 1. Short indexes not only improve query speed but also save disk space and I/O operations.
3. Index column sorting
The MySQL query USES only one index, so if the index is already used in the where clause, then order
Columns in by do not use indexes. So the database default sort can meet the requirements do not use the sort operation; Try not to include multiple column sorts, and it is best to create composite indexes for these columns if necessary.
4. like statement operation
1. like operation is generally not encouraged. If you have to use it, how to use it is also a problem. like "%aaa%" does not use the MySQL index, while like "aaa%" does.
5. Don't calculate on columns
select * from users where YEAR(adddate) < 2007; The operation will be performed on each row, which will cause the index to fail for a full table scan, so we can change to
select * from users where adddate < '2007-01-01'; NOT IN and < > operation
Above, the MySQL index type is introduced.


This paper introduces the database index, its advantages and disadvantages. The characteristics and application of MySQL index are described in detail. It analyzes how to avoid the failure of MySQL, how to use EXPLAIN to analyze query statements, and how to optimize the application of MySQL indexes.
An index is a special file (the index on the InnoDB data table is a component of the table space) that contains a reference pointer to all the records in the table.
Note: the [1] index is not a panacea! Indexes can speed up data retrieval operations but slow down data modification operations. Each time a data record is modified, the index must be refreshed once. To compensate for this in some way, many SQL commands have an DELAY_KEY_WRITE entry. The effect of this option is to temporarily prevent MySQL from refreshing the index immediately after the command inserts a new record and changes an existing record. The refresh of the index will wait until all records have been inserted/modified. The DELAY_KEY_WRITE option will be useful in situations where many new records need to be inserted into a table. [2] in addition, indexes take up a considerable amount of space on the hard disk. Therefore, you should index only the data columns that are most frequently queried and sorted. Note that if a data column contains a lot of duplicate content, indexing it does not have much practical effect.
Theoretically, it is possible to have one index for each field in the table, but MySQL limits the total number of indexes in the same table to 16.
1. Index of InnoDB data table
Indexes are much more important for InnoDB data than MyISAM tables. On the InnoDB data table, indexes are much more important to the InnoDB data table. On the InnoDB data table, indexes not only play a role in searching data records, but also serve as the basis for row-level locking mechanism. Row-level locking means that individual records that are being processed are locked during the execution of a transaction and are not accessible to other users. This locking will affect (but not be limited to)SELECT... LOCK IN SHARE MODE, SELECT... The FOR UPDATE command and the INSERT, UPDATE and DELETE commands.
For efficiency reasons, row-level locking of the InnoDB data tables actually occurs on their indexes, not on the data tables themselves. Obviously, the row-level locking mechanism only works if the relevant data table has a suitable index for locking.
2. Limit
If the query condition for the WEHERE clause has inequality (WHERE coloum! =)... , MySQL will not be able to use the index.
Similarly, if the query condition of the WHERE clause USES a function (WHERE DAY(column) =...) , MySQL will also not be able to use the index.
In JOIN operations (when data needs to be extracted from multiple data tables), MySQL can use indexes only if the primary and foreign key data types 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. 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 indexes.
In the ORDER BY operation, MySQL USES an index only if the sort condition is not a query conditional expression. (however, in queries involving multiple tables, even if indexes are available, those indexes are not helpful in speeding up ORDER BY.)
Even indexing a data column that contains many duplicate values will not work well. For example, there is no need to create an index for a data column if it contains nothing more than "0/1" or "Y/N" equivalents.

Normal index, exclusive index, and primary index
1. General index
The only task of a normal index (an index defined by the keyword KEY or INDEX) is to speed up access to the data. Therefore, only those conditions that appear most frequently in the query (WHERE column =...) should be used. Or the data column in the sort condition (ORDER BY column) creates the index. Whenever possible, you should select one of the data columns with the neatest and most compact data (such as one of the integer types) to create the index.
2. Exclusive index
Normal indexes allow indexed data columns to contain duplicate values. For example, because a person may have the same name, the same name may appear twice or more in the same "employee profile" data sheet.
If you can be sure that a data column will contain only different values from each other, you should use the keyword UNIQUE to define it as a unique index when indexing the data column. The benefits: 1. It simplifies MySQL's management of this index, which makes it more efficient; 2. 2 is that when a new record is inserted into the data table, MySQL will automatically check whether the value of this field of the new record has already appeared in this field of a certain record. If so, MySQL will refuse to insert that new record. That is, a one-only index guarantees the one-only nature of the data record. In fact, in many cases, one-only indexes are created not to speed up access, but simply to avoid duplication of data.
3. The main index
As stated many times before, you must create an index for the primary key field, which is called the primary index. The only difference between a primary index and a unique index is that the primary index is defined using the keyword PRIMARY instead of UNIQUE.
4. Foreign key index
If a foreign key constraint is defined for a foreign key field, MySQL defines an internal index to help it manage and use foreign key constraints in the most efficient way.
5. Composite index
Indexes can cover multiple data columns, such as INDEX(columnA, columnB) indexes. The feature of this index is that MySQL can optionally use one of these indexes. The composite index INDEX(columnA, columnB) can be used if the query operation requires only one index on the columnA data column. However, this usage applies only to combinations of data columns that are placed first in a composite index. For example, INDEX(A, B, C) can be used as an index of A or (A, B), but not as an index of B, C or (B, C).
6. Length of index
When you define an index for a data column of type CHAR and VARCHAR, you can limit the length of the index to a given number of characters (this number must be smaller than the maximum number of characters allowed for this field). The advantage of this is that you can generate a smaller, faster index file. In most applications, the string data in the database is dominated by a variety of names, and an index length of 10 to 15 characters is enough to narrow the search down to a few data records.
When creating an index for a data column of type BLOB and TEXT, you must limit the length of the index; The maximum index length allowed by MySQL is 255 characters.

The full text indexing
A normal index on a text field can only speed up the retrieval of the string (that is, the character at the beginning of the field content) that appears first in the field content. If the field contains large chunks of text made up of a few or more words, the normal index is useless. This retrieval is often in the form of LIKE %word%, which is very complex for MySQL, and if the amount of data that needs to be processed is large, the response time will be long.
This is where the full-text index (full-text index) comes in. When generating this type of index, MySQL will create a list of all the words that appear in the text, and the query operation will retrieve the relevant data records based on this list. Full-text indexes can either be created with table 1 or added later if necessary using the following command:
ALTER tablename ADD FULLTEXT(column1, column2)
With a full-text index, you can use the SELECT query command to retrieve data records containing one or more given words. Here is the basic syntax for such query commands:
SELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST('word1', 'word2', 'word3')
The above command will query all the data records with word1, word2 and word3 in column1 and column2 fields.
Note: the InnoDB data table does not support full-text indexing.

Optimization of queries and indexes
Only when there is enough test data in the database can its performance test results be of practical value. If there are only a few hundred data records in the test database, they tend to be loaded into memory after the first query command is executed, which makes subsequent query commands execute very quickly -- with or without indexes. The database performance test results only make sense if the database has more than 1,000 records and the total amount of data exceeds the total amount of memory on the MySQL server.
People often get some help from the EXPLAIN SELECT command when they are not sure which data columns to index. This is simply prefixing a normal SELECT command with the EXPLAIN keyword. With this keyword, MySQL will not execute the SELECT command, but will analyze it. MySQL lists in a table information such as the execution of the query and the indexes (if any) used.
In the output of the EXPLAIN command, column 1 lists the names of the tables read from the database in the order in which they were read. The type column specifies the association between this data table and other data tables (JOIN). Of the various types of associations, the most efficient is system, followed by const, eq_ref, ref, range, index, and All.
The possible_keys data column gives the various indexes that MySQL can use when searching for data records. The key data column is the actual index used by MySQL, which is given in bytes in the key_len data column. For example, for an index of an INTEGER data column, this byte length would be 4. If a composite index is used, you can also see what parts of it are used by MySQL in the key_len data column. As a rule of 1, 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 the other table in the association relationship. The row data column is the number of rows that MySQL is expected to read from the table when executing the query. The product of all the Numbers in the row data column gives us an idea of how many combinations the query needs to process.
Finally, the extra data column provides more information about the JOIN operation. For example, if MySQL had to create a temporary data table to execute this query, you would see the word using temporary in the extra column.