Types and pros and cons of MySQL indexes

Today, my colleague asked me about the MySQL index, I told him the index is very helpful for querying large data, but it has an impact on the data modification and addition, we used to add the index when processing the big data of the website this January, but because of the recent real development not much, many things have been forgotten, So come back and review it. Now, we introduce the database index, its advantages and disadvantages. The characteristics and application of MySQL index are described in detail. This paper analyzes how to avoid MySQL unusable, how to use explain to analyze query statements, and how to optimize the application of MySQL index. This digest is from section 8.9 of the "MySQL 5 authoritative Guide" (3rd). An index is a special kind of file (an index on a InnoDB data table is an integral part of a table space), and they contain reference pointers to all records in the datasheet.

Note: [1] The index is not omnipotent! Indexing can speed up data retrieval operations, but it slows down data modification operations. Each time a data record is modified, the index must be refreshed once. In order to compensate for this flaw in some program, many SQL commands have a delay_key_write entry. The purpose of this option is to temporarily stop MySQL from updating the index immediately after each new record is inserted and after each modification of the command, the refresh of the index will wait until all records have been inserted/modified. The Delay_key_write option is very obvious when you need to insert many new records into a data table. [2] In addition, the index will occupy a considerable amount of space on the hard disk. Therefore, you should only index the most frequently queried and most frequently sorted columns of data. Note that if a data column contains a lot of duplicate content, indexing it does not have much practical effect. Theoretically, you can create an index for each field in the data table, but MySQL limits the total number of indexes in the same table to 16.

1. Index of the INNODB data table

Indexes are significantly more important to INNODB data than MyISAM data tables. On the InnoDB data table, the index is much more important to the INNODB data table. On the InnoDB data table, the index will not only play a role in searching data records, but also the Acenaphthene and foundation of the data row-level locking mechanism. "Data row-level locking" means that the individual records being processed are locked during the execution of a transactional operation and are not accessible to other users. This lock will affect (but is not limited to) SELECT ... LOCK in SHARE MODE, SELECT ... The for UPDATE command and the INSERT, UPDATE, and delete commands. For efficiency reasons, data 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 works only when the relevant data table has an appropriate index to lock on. 2. Restrictions

If the Wehere clause has a non-equal sign in the query condition (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 data needs to be extracted from multiple data tables), MySQL can use the index only if it has the same data type as the primary key and foreign key. 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 '? C ', 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. (Even so, in the case of multiple data-table queries, those indexes do little to speed up the ORDER by if there is an index available) if a data column contains many duplicate values, even indexing it will not work well. For example, there is no need to create an index for a data column if it contains a list of values such as "0/1″ or" y/n. Normal index, unique index, and primary index 1. Normal index

The only task for a normal index (an index defined by the keyword key or index) is to speed up access to the data. Therefore, it should be only for those that most often appear in the query condition (where column = ...). or the data column in the sort condition (order by column) to create the index. Whenever possible, you should choose a data column that is the most tidy and compact data (such as an integer type of data column) to create an index. 2. Unique index

A normal index allows the indexed data column to contain duplicate values. For example, because a person might have the same name, it may occur 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 creating an index for that data column. The advantage of this is that it simplifies MySQL's management of the index, which makes it more efficient, and that MySQL automatically checks to see if the value of this field in the new record has already appeared in the field of a record when a new record is inserted into the data table; MySQL will refuse to insert that new record. In other words, a unique index guarantees the uniqueness of the data record. In fact, in many cases, the goal of creating a unique index is often not to improve access speed, but to avoid duplication of data. 3. Primary index

It has been repeated several 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 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 foreign key constraints in the most efficient way. 5. Composite Index

An index can overwrite multiple data columns, such as indexes such as index (ColumnA, COLUMNB). The feature of this index is that MySQL can selectively use one such index. If the query operation only needs an index on the ColumnA data column, you can use the composite Index, index (ColumnA, COLUMNB). However, this usage applies only to combinations 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 to B, C, or (b, c). 6. Length of index

When you define an index for a data column of char and varchar type, 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 this field). The advantage of this is that you can generate an index file that is smaller in size and faster to retrieve. In most applications, the string data in the database is dominated by a variety of names, and the length of the index is set to 10~15 characters enough to narrow the search to a few data records.

People can often get some help from the explain select command when they are unsure of which data columns to create indexes on. This simply adds a explain keyword as a prefix to an ordinary select command. With this keyword, MySQL is not going to execute that select command, but to parse it. MySQL will list 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 by the order in which they were read. The Type column specifies the association relationship (JOIN) between this data table and other data tables. Among the various types of associations, the most efficient system, followed by const, EQ_REF, ref, range, index, and all, means that all records in the data table must be read once, corresponding to each record in the previous data table- This situation can often be avoided by an index).

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 mysql-selected index, which is given in the Key_len data column for the length of the byte count. For example, for an index of an integer data column, the byte length would be 4. If a composite index is used, the Key_len data joins can also see which parts of MySQL it uses. 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 the other data table in the association relationship. The row data column is the number of data rows that MySQL expects to read from this data table when it executes this query. The product of all the numbers in the row data column gives us a rough idea of how many combinations this query needs to handle.

Finally, the extra data column provides more information about the join operation, for example, if MySQL had to create a temporary data table when executing the query, it would see the word using temporary in the extra column.

Types and pros and cons of MySQL indexes