MySQL index details

An index is a special file (an index on an InnoDB data table is an integral part of a tablespace) that contains reference pointers to all records in the data table.

Note:

[1]Indexing is not omnipotent!

Indexing speeds up data retrieval, but slows down data modification. Each time a data record is modified, the index must be refreshed once. To compensate for this defect in a program, many SQL commands have a DELAY_KEY_WRITE entry. This option is used to temporarily stop MySQL from refreshing the index immediately after each new record is inserted in the command and each existing record is modified, refresh the index until all records are inserted/modified. When many new records need to be inserted into a data table, the DELAY_KEY_WRITE option will play a significant role.

[2] In addition,Indexes also occupy a considerable amount of space on the hard disk..

Therefore, you should create an index for the data columns that are most frequently queried and sorted. Note: If a data column contains a lot of duplicate content, creating an index for it will 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 data table to 16.

1. InnoDB data table Index

Compared with MyISAM data tables, indexes are much more important to InnoDB data. In the InnoDB data table, the index is more important to the InnoDB data table. In the InnoDB data table, indexing not only plays a role in searching data records, but also serves as the basis for the data row-level locking mechanism ." "Row-level locking" means locking individual records being processed during the execution of transaction operations, so that other users are not allowed to access them. This lock will affect (but not limited to) SELECT... Lock in share mode, SELECT... For update and INSERT, UPDATE, and DELETE commands.

For efficiency, row-level locking of InnoDB Data Tables actually occurs on their indexes, rather than on the data tables themselves. Apparently, the row-level locking mechanism is effective only when the relevant data table has a suitable index available for locking.

2. Restrictions

If the query condition of the wehere clause contains a non-equal sign (WHERE coloum! = ...), MySQL cannot use indexes.

Similarly, if the WHERE clause query condition uses the function (where day (column) = ...), MySQL will also be unable to use indexes.

In the JOIN Operation (when data needs to be extracted from multiple data tables), MySQL can use indexes only when the data types of primary keys and Foreign keys are the same.

If the query conditions of the where clause use the comparison operators LIKE and REGEXP, MySQL can use the index only when the first character of the search template is not a wildcard. For example, if the query condition is LIKE 'abc % ', MySQL uses an index. If the query condition is LIKE' % abc', MySQL does not use an index.

In the order by operation, MySQL uses indexes only when the sorting condition is not a query condition expression. (Even if indexes are available in queries involving multiple data tables, those indexes do not play a role in accelerating order)

If a data column contains many duplicate values, creating an index for it will not be very effective. For example, if a data column contains values such as "0/1" or "Y/N", there is no need to create an index for it.

Common Index, unique index, and primary index

1. Common Index

The only task of a common INDEX (INDEX defined by the KEY or INDEX keyword) is to speed up data access. Therefore, the query condition (WHERE column =…) must be ...) Or create an index for the data column in the order by column. If possible, you should select the most neat and compact data column (such as an integer data column) to create an index.

2. Unique 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 times in the same "employee profile" data table.
If you can determine that a data column will only contain different values, you should use the keyword UNIQUE to define it as a UNIQUE index when creating an index for this data column. The advantage of doing so: First, it simplifies MySQL's management of this index, and this index becomes more efficient. Second, MySQL inserts a data table with a new record, automatically checks whether the value of this field of the new record has already exists in this field of a record; if yes, MySQL rejects the insert of that new record. That is to say, the unique index can ensure the uniqueness of data records. In fact, in many cases, the purpose of creating a unique index is not to speed up access, but to avoid data duplication.

3. Primary Index

I have already repeatedly stressed that an index must be created for the primary key field. This index is called a "primary index ". The only difference between a PRIMARY index and a UNIQUE index is that the keywords used by the former during definition are PRIMARY rather than UNIQUE.

4. Foreign Key Index

If a foreign key constraint is defined 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

Indexes can cover multiple data columns, such as INDEX (columnA, columnB) indexes. This index features that MySQL can selectively use such an index. If you only need to use an INDEX on the columnA data column for the query operation, you can use a composite INDEX (columnA, columnB ). However, this method is only applicable to the combination of data columns in the composite index. For example, INDEX (A, B, C) can be used as an INDEX of A or (A, B), but cannot be used as B, C or (B, C).

6. Index Length

When defining indexes for CHAR and VARCHAR data columns, you can limit the index length to a given number of characters (This number must be less than the maximum number of characters allowed by this field ). The advantage of this is that you can generate an index file with a relatively small size and fast retrieval speed. In most applications, most of the string data in the database is based on a variety of names, and the index length is set to 10 ~ 15 characters is enough to narrow the search range to a few data records.

The maximum index length allowed by MySQL is 255 characters.

Full-text index

A common index on a text field can only accelerate the retrieval of strings (characters starting with the field content) at the top of the field content. If a field contains a large text segment consisting of several or even multiple words, the normal index will be useless. This kind of search usually appears in the form of LIKE % word %, which is very complicated for MySQL. If the amount of data to be processed is large, the response time will be very long.

This type of scenario is where full-text indexes can be used. When an index of this type is generated, MySQL creates a list of all the words that appear in the text, and searches for relevant data records based on the list. The full-text index can be created along with the data TABLE, or you can use the following command to ADD it later if necessary: alter table tablename add fulltext (column1, column2) has full-text index, you can use the SELECT query command to retrieve data records containing one or more given words.

The basic syntax for such query commands is as follows:

 

SELECT * FROM tablename where match (column1, column2) AGAINST ('word1 ', 'word2', 'word3 ′)

 

The preceding command will query all the data records of word1, word2, and word3 in column1 and column2 fields.

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

Query and INDEX OPTIMIZATION

The performance test results are of practical reference only when the database has enough test data. If there are only several hundred data records in the test database, they are usually loaded into the memory after the first query command is executed, this will make subsequent query commands run very fast-no matter whether or not indexes are used. The database performance test results are meaningful only when the number of records in the database exceeds 1000 and the total amount of data exceeds the total memory on the MySQL server.

People often get some help from the explain select command when they are not sure which columns should be used to create indexes. In fact, this simply adds an EXPLAIN keyword to a common SELECT command as the prefix. With this keyword, MySQL will not execute the SELECT command, but analyze it. MySQL lists the query execution process and indexes (if any) in the form of tables.

In the output result of the EXPLAIN command, the 1st columns are the names of the data tables read from the database, which are listed in the read order. The type column specifies the JOIN relationship between the data table and other data tables ). Among various types of associations, the most efficient is system, followed by const, eq_ref, ref, range, index, and All (All means: corresponding to each record in the data table at the upper level, all records in the data table must be read once-this situation can be avoided by a single line ).

The possible_keys data column lists the indexes available for MySQL to search for data records. The key data column is the index actually selected by MySQL. The length of this index calculated in bytes is provided in the key_len data column. For example, for an INTEGER data column index, the length of this Byte will be 4. If a composite index is used, you can also see which parts of MySQL are used in the key_len data column. As a general rule, the smaller the value in the key_len data column, the better (faster ).

The ref data column provides 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 the data table when executing this query. The product of all numbers in the row data column gives us a general idea of the number of combinations to be processed in this query.

Finally, the extra data column provides more information related to the JOIN operation. For example, if MySQL must create a temporary data table when executing this query, the using temporary is displayed in the extra column.

Note:

[1]Indexing is not omnipotent!

Indexing speeds up data retrieval, but slows down data modification. Each time a data record is modified, the index must be refreshed once. To compensate for this defect in a program, many SQL commands have a DELAY_KEY_WRITE entry. This option is used to temporarily stop MySQL from refreshing the index immediately after each new record is inserted in the command and each existing record is modified, refresh the index until all records are inserted/modified. When many new records need to be inserted into a data table, the DELAY_KEY_WRITE option will play a significant role.

[2] In addition,Indexes also occupy a considerable amount of space on the hard disk..

Therefore, you should create an index for the data columns that are most frequently queried and sorted. Note: If a data column contains a lot of duplicate content, creating an index for it will 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 data table to 16.

1. InnoDB data table Index

Compared with MyISAM data tables, indexes are much more important to InnoDB data. In the InnoDB data table, the index is more important to the InnoDB data table. In the InnoDB data table, indexing not only plays a role in searching data records, but also serves as the basis for the data row-level locking mechanism ." "Row-level locking" means locking individual records being processed during the execution of transaction operations, so that other users are not allowed to access them. This lock will affect (but not limited to) SELECT... Lock in share mode, SELECT... For update and INSERT, UPDATE, and DELETE commands.

For efficiency, row-level locking of InnoDB Data Tables actually occurs on their indexes, rather than on the data tables themselves. Apparently, the row-level locking mechanism is effective only when the relevant data table has a suitable index available for locking.

2. Restrictions

If the query condition of the wehere clause contains a non-equal sign (WHERE coloum! = ...), MySQL cannot use indexes.

Similarly, if the WHERE clause query condition uses the function (where day (column) = ...), MySQL will also be unable to use indexes.

In the JOIN Operation (when data needs to be extracted from multiple data tables), MySQL can use indexes only when the data types of primary keys and Foreign keys are the same.

If the query conditions of the where clause use the comparison operators LIKE and REGEXP, MySQL can use the index only when the first character of the search template is not a wildcard. For example, if the query condition is LIKE 'abc % ', MySQL uses an index. If the query condition is LIKE' % abc', MySQL does not use an index.

In the order by operation, MySQL uses indexes only when the sorting condition is not a query condition expression. (Even if indexes are available in queries involving multiple data tables, those indexes do not play a role in accelerating order)

If a data column contains many duplicate values, creating an index for it will not be very effective. For example, if a data column contains values such as "0/1" or "Y/N", there is no need to create an index for it.

Common Index, unique index, and primary index

1. Common Index

The only task of a common INDEX (INDEX defined by the KEY or INDEX keyword) is to speed up data access. Therefore, the query condition (WHERE column =…) must be ...) Or create an index for the data column in the order by column. If possible, you should select the most neat and compact data column (such as an integer data column) to create an index.

2. Unique 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 times in the same "employee profile" data table.
If you can determine that a data column will only contain different values, you should use the keyword UNIQUE to define it as a UNIQUE index when creating an index for this data column. The advantage of doing so: First, it simplifies MySQL's management of this index, and this index becomes more efficient. Second, MySQL inserts a data table with a new record, automatically checks whether the value of this field of the new record has already exists in this field of a record; if yes, MySQL rejects the insert of that new record. That is to say, the unique index can ensure the uniqueness of data records. In fact, in many cases, the purpose of creating a unique index is not to speed up access, but to avoid data duplication.

3. Primary Index

I have already repeatedly stressed that an index must be created for the primary key field. This index is called a "primary index ". The only difference between a PRIMARY index and a UNIQUE index is that the keywords used by the former during definition are PRIMARY rather than UNIQUE.

4. Foreign Key Index

If a foreign key constraint is defined 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

Indexes can cover multiple data columns, such as INDEX (columnA, columnB) indexes. This index features that MySQL can selectively use such an index. If you only need to use an INDEX on the columnA data column for the query operation, you can use a composite INDEX (columnA, columnB ). However, this method is only applicable to the combination of data columns in the composite index. For example, INDEX (A, B, C) can be used as an INDEX of A or (A, B), but cannot be used as B, C or (B, C).

6. Index Length

When defining indexes for CHAR and VARCHAR data columns, you can limit the index length to a given number of characters (This number must be less than the maximum number of characters allowed by this field ). The advantage of this is that you can generate an index file with a relatively small size and fast retrieval speed. In most applications, most of the string data in the database is based on a variety of names, and the index length is set to 10 ~ 15 characters is enough to narrow the search range to a few data records.

The maximum index length allowed by MySQL is 255 characters.

Full-text index

A common index on a text field can only accelerate the retrieval of strings (characters starting with the field content) at the top of the field content. If a field contains a large text segment consisting of several or even multiple words, the normal index will be useless. This kind of search usually appears in the form of LIKE % word %, which is very complicated for MySQL. If the amount of data to be processed is large, the response time will be very long.

This type of scenario is where full-text indexes can be used. When an index of this type is generated, MySQL creates a list of all the words that appear in the text, and searches for relevant data records based on the list. The full-text index can be created along with the data TABLE, or you can use the following command to ADD it later if necessary: alter table tablename add fulltext (column1, column2) has full-text index, you can use the SELECT query command to retrieve data records containing one or more given words.

The basic syntax for such query commands is as follows:

 

SELECT * FROM tablename where match (column1, column2) AGAINST ('word1 ', 'word2', 'word3 ′)

 

The preceding command will query all the data records of word1, word2, and word3 in column1 and column2 fields.

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

Query and INDEX OPTIMIZATION

The performance test results are of practical reference only when the database has enough test data. If there are only several hundred data records in the test database, they are usually loaded into the memory after the first query command is executed, this will make subsequent query commands run very fast-no matter whether or not indexes are used. The database performance test results are meaningful only when the number of records in the database exceeds 1000 and the total amount of data exceeds the total memory on the MySQL server.

People often get some help from the explain select command when they are not sure which columns should be used to create indexes. In fact, this simply adds an EXPLAIN keyword to a common SELECT command as the prefix. With this keyword, MySQL will not execute the SELECT command, but analyze it. MySQL lists the query execution process and indexes (if any) in the form of tables.

In the output result of the EXPLAIN command, the 1st columns are the names of the data tables read from the database, which are listed in the read order. The type column specifies the JOIN relationship between the data table and other data tables ). Among various types of associations, the most efficient is system, followed by const, eq_ref, ref, range, index, and All (All means: corresponding to each record in the data table at the upper level, all records in the data table must be read once-this situation can be avoided by a single line ).

The possible_keys data column lists the indexes available for MySQL to search for data records. The key data column is the index actually selected by MySQL. The length of this index calculated in bytes is provided in the key_len data column. For example, for an INTEGER data column index, the length of this Byte will be 4. If a composite index is used, you can also see which parts of MySQL are used in the key_len data column. As a general rule, the smaller the value in the key_len data column, the better (faster ).

The ref data column provides 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 the data table when executing this query. The product of all numbers in the row data column gives us a general idea of the number of combinations to be processed in this query.

Finally, the extra data column provides more information related to the JOIN operation. For example, if MySQL must create a temporary data table when executing this query, the using temporary is displayed in the extra column.