The drawbacks of the MySQL index and what the MySQL index does in the actual operation _mysql

The following article is mainly about the shortcomings of the MySQL index and the MySQL index in the actual operation of what is worth our attention, we may not know that too much of the use of the index will cause abuse. Therefore, the MySQL index will also have its disadvantages:
Although indexing greatly improves query speed, it reduces the speed at which tables are updated, such as INSERT, UPDATE, and delete tables. Because when you update the table, MySQL not only saves the data, but also saves the index file.
Index files that create indexes that consume disk space. Generally this is not a serious problem, but if you create multiple combinations of indexes on a large table, the index files will swell up very quickly.
Indexing is just a factor in improving efficiency, and if your MySQL has a large data table, it takes time to study the creation of the best MySQL index, or optimize the query statement.
Considerations for using Indexes
when using indexes, there are some tips and considerations：
1, the index will not contain columns with null values
This column is not valid for this composite index as long as the column contains null values that will not be included in the MySQL index, as long as one column in the composite index contains a null value. So we don't want the default value of the field to be null when designing the database.
2. Using Short Index
Index A string column, if possible, to specify a prefix length. For example, if you have a column with char (255), if most values are unique within the first 10 or 20 characters, do not index the entire column. Short indexing can not only improve query speed but also save disk space and I/O operations.
3, indexed column sorting
The MySQL query uses only one index, so if an index is already used in the WHERE clause, then the order
Columns in by is not indexed. Therefore, do not use sort operations when the database default sort meets the requirements, and try not to include sorting of multiple columns, preferably if you need to create a composite index for these columns.
4, like statement operation
It is generally discouraged to use like operations, and how to use them is also a problem if not used. Like "%aaa%" does not use the MySQL index and like "aaa%" can use the index.
5. Do not perform operations on columns
SELECT * from users where year (adddate) <2007; will be performed on each row, which will cause the index to fail with a full table scan, so we can change it to
SELECT * from users where adddate< ' 2007-01-01 '; Do not use not in and <> operations
Above, on which the MySQL index type is introduced.


This paper introduces the database index, and its advantages and disadvantages. The characteristics and application of MySQL index are described in detail. This paper analyzes how to avoid MySQL, how to use explain to analyze query statements, and how to optimize the application of MySQL index.
An index is a special file (an index on a INNODB datasheet is an integral part of a tablespace) that contains a reference pointer to all records in the datasheet.
Note: [1] The index is not omnipotent! Indexing speeds up data retrieval operations, but slows down data modification operations. Each time a data record is modified, the index must be refreshed once. Many SQL commands have a delay_key_write entry in order to make up for this flaw in some kind of program. The effect of this option is to temporarily prevent MySQL from refreshing the index every time a new record is inserted and an existing one is modified, the refresh of the index will wait until all records have been inserted/modified. The Delay_key_write option works very well when you need to insert many new records into a datasheet. [2] In addition, the index will also occupy considerable space on the hard disk. Therefore, you should only index the most frequently queried and most frequently sorted data columns. Note that if a data column contains a lot of duplicate content, indexing it doesn't have much of a real effect.
Theoretically, each field in the data table can be indexed separately, but MySQL limits the total number of indexes in the same data table to 16.
　　 1. Index of INNODB datasheet
Indexes are significantly more important to INNODB data than MyISAM data tables. On INNODB data tables, indexes are much more important to innodb data tables. On the InnoDB datasheet, the index not only plays a role in searching the data records, but also is the Acenaphthene and foundation of the data row-level locking mechanism. Data row-level locking means locking individual records that are being processed and not being accessed by other users during the execution of a transaction operation. This lock will affect, but not limited to, SELECT ... LOCK in SHARE MODE, SELECT ... For UPDATE command and INSERT, UPDATE, and delete commands.
For efficiency reasons, the data row-level locking of the INNODB data table actually occurs on their index, not on the datasheet itself. Obviously, the data row-level locking mechanism only works when the relevant data table has a suitable index to lock.
　　 2. Limit
If there is an inequality in the query condition of the Wehere clause (WHERE coloum!= ...), MySQL will not be able to use the index.
Similarly, if a function is used in the query condition of the WHERE clause (where day (column) = ...), MySQL will not be able to use the index.
In a JOIN operation (when you need to extract data from multiple data tables), MySQL can use the index only if the primary key and the foreign key have the same data type.
If you use the comparison operator like and regexp,mysql in the query condition of the WHERE clause, you 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, and if the query condition is like '%abc ', MySQL will not use the index.
In an order by operation, MySQL uses indexes only if the sort criteria are not a query condition expression. (however, in a query involving multiple tables, even if there are indexes available, those indexes have no effect in speeding up the order by aspect)
If a data column contains many duplicate values, it does not have a good effect even if an index is built. For example, there is no need to create an index for a data column if it contains a net of something like "0/1" or "y/n" equivalent.
　　
　　 Normal index, unique index, and primary index
　　 1. General Index
The only task for a normal index (indexed by the keyword Key or index) is to speed access to the data. Therefore, only those that appear most frequently in the query condition (where column = ...) or the data column in the sort condition (order by column). Whenever possible, you should select a data column that is the most uncluttered and compact, such as a data column of an integer type, to create an index.
　　 2. Unique index
The normal index allows the indexed data column to contain duplicate values. For example, because a person may have the same name, the same name may appear two or more times in the same employee profile data table.
If you can determine that a data column will contain only values that are different from each other, you should define it as a unique index with the keyword unique when you create an index for that data column. The advantage of this: one is to simplify the management of MySQL on this index, this index is also becoming more efficient; the second is that MySQL automatically checks that the value of the field in the new record appears in this field of a record when a new record is inserted into the datasheet; MySQL will refuse to insert the new record. In other words, a unique index guarantees the uniqueness of the data record. In fact, on many occasions, the purpose of creating a unique index is often not to improve the speed of access, but to avoid duplication of data.
　　 3. Primary index
It has been repeatedly stressed 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 first keyword used in the definition is primary rather than unique.
　　 4. Foreign KEY Index
If you define a FOREIGN key constraint for a foreign key field, MySQL defines an internal index to help you manage and use the foreign key constraints in the most efficient way.
　　 5. Composite Index
Indexes can overwrite multiple data columns, such as index (ColumnA, COLUMNB). The feature of this index is that MySQL can selectively use one such index. If the query operation requires only one index on the ColumnA data column, you can use the composite Index index (ColumnA, COLUMNB). However, this usage applies only to the combination of data columns that are arranged in the composite index. For example, index (a, B, c) can be used as an index to 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 less than the maximum number of characters allowed for the field). The advantage of this is that you can generate an index file with smaller size and faster retrieval speed. In most applications, the string data in the database is mostly in a variety of names, the length of the index set to 10~15 characters is enough to narrow the search to a few data records.
When you create an index for a data column of a BLOB and text type, you must limit the length of the index; MySQL allows the maximum index length of 255 characters.

　　 Full-text Indexing
A normal index on a text field can only speed up retrieval of the string that appears at the top of the field's content, which is the character at the beginning of the field's content. If a field is a large piece of text that consists of several or even multiple words, the normal index has no effect. This search is often in the form of like%word%, which is very complex for MySQL, if the amount of data to be processed is very large, response time will be very long.
Such occasions are the places where full text indexing (Full-text index) can be used. When this type of index is generated, MySQL creates all the words that appear in the text as a list, and the query operations retrieve the relevant data records based on the list. The Full-text index can be created with the datasheet, or you can add it by using the following command later if necessary:
ALTER TABLE tablename ADD fulltext (Column1, Column2)
With Full-text indexing, you can use the Select query command to retrieve data records that contain one or more given words. The following are the basic syntax for this type of query command:
SELECT * FROM TableName
WHERE MATCH (Column1, Column2) against (' Word1 ', ' word2 ', ' Word3 ')
The above command will query the Column1 and Column2 fields for the word1, Word2, and Word3 data records.
　　 Annotations: InnoDB data table does not support Full-text indexing.

　　 Query and index optimization
Only when there is enough test data in the database, its performance test results have practical reference value. If there are only hundreds of 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 run very fast-regardless of whether an index is used or not. The performance test results of the database are meaningful only when the records in the database exceed 1000 and the total amount of data exceeds the total memory on the MySQL server.
When unsure about which data columns should be indexed, people can often get some help from the explain select command. This is simply to prefix a common select command with a explain keyword. With this keyword, MySQL is not going to execute the Select command, but to analyze it. MySQL lists information such as the execution of the query and the index (if any) that is used in the form of a table.
In the output of the explain command, the 1th column is the name of the data table read from the database, sorted in the order in which they were read. The Type column specifies the association (JOIN) between this data table and other data tables. The most efficient of the various types of association relationships is system, followed by const, EQ_REF, ref, range, index, and all, which means that each record in the previous data table corresponds to the All of the records in this datasheet must be read again-a situation that can often be avoided by an index.
The Possible_keys data column gives the various indexes that MySQL can use to search for data records. The Key data column is the actual selection of the MySQL index, which is given in the Key_len data column in bytes length. For example, for an integer data column index, this byte length will be 4. If you use a composite index, you can see in Key_len data column what parts of MySQL use it specifically. 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 association relationship. The row data column is the number of data rows that MySQL expects to read from this table when it executes the query. The product of all the numbers in the row data column gives us a general idea of how many combinations the query needs to handle.
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, it will see the using temporary typeface in the extra column.