Mysql series (5) index function

Indexes are special files (the indexes on the InnoDB data table are a component of the table space). They contain reference pointers to all records in the data table. Indexes are not a panacea. Indexes can speed up data retrieval operations, but they can slow down data modification operations. Every time a data record is modified, the index must be refreshed. To compensate to some extent for this shortcoming, many SQL commands have a DELAY_KEY_WRITE entry. 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 have been inserted/modified. The DELAY_KEY_WRITE option is very useful when many new records need to be inserted into a data table. In addition, indexes take up considerable space on the hard drive. 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. Indexes of InnoDB data tables

Compared with InnoDB data tables, indexes on InnoDB data tables are much more important. On InnoDB data tables, indexes not only play a role in searching data records, but are also the basis for the data row-level locking mechanism. "Data row-level locking" means locking individual records being processed during the execution of transaction operations, preventing other users from accessing them. This locking affects (but is not limited to) SELECT, LOCKINSHAREMODE, SELECT, FORUPDATE commands, and INSERT, UPDATE, and DELETE commands. For efficiency reasons, row-level locking of InnoDB tables actually occurs on their indexes, not on the 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 WHERE clause, MySQL will not be able to use the index. Similarly, if the 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 type of the primary key and foreign key is the same.

If the comparison operators LIKE and REGEXP are used in the query condition of the WHERE clause, MySQL can only use the index 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 the ORDER BY operation, MySQL only uses the index if the sort condition is not a query condition expression. (However, in queries involving multiple data tables, even if indexes are available, those indexes will have little 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.

Index Category

1． Ordinary index

The only task of an ordinary 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

Ordinary 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 be sure 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 benefits 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. Primary 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 former 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 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 kind of index is that MySQL can selectively use such an index. If the query operation only requires 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 into A or (A, B), but not as an index into B, C or (B, C).

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~15 characters is enough to narrow the search scope to a few data records. . When creating indexes for BLOB and TEXT type data columns, limits must be placed on the length of the index; the maximum index allowed by MySQL is a normal index on a full-text index text field that can only speed up the search for the string that appears at the front of the field content ( That is, the characters at the beginning of the field content) to perform the search operation. If the field stores a larger piece of text composed of several or even multiple words, ordinary indexes will be of little use. This retrieval often comes in the form of , which is complex for MySQL and can result in long response times if the amount of data that needs to be processed is large.

This type of situation is where full-text index (full-textindex) can show its talents. When generating this type of index, MySQL will create a list of all words that appear in the text, and query operations will use this list to retrieve relevant data records. The full-text index can be created together with the data table, or you can use the following command to add it when necessary in the future:

ALTER TABLE tablename ADD FULLTEXT (column1, column2) With the full-text index, you can use the SELECT query command to search Data records containing 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 combine column1 and column2 All data records containing word1, word2 and word3 in the field are queried.

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

Query and indexing

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 created on which indexes should be created, 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 the query execution process and indexes used in 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 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 once - this can often be avoided with 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.

ref The 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 you 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 must create a temporary data table when executing this query, you will see the words usingtemporary in the extra column.

The above is the content of the Mysql series (5) index function. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!