MySQL Efficient index coverage index

Concept

if The index contains all of the data that satisfies the query, the index becomes the overwrite index (covering index), which is what you usually say does not require a table operation.

Judging criteria

with explain, you can tell by the output of the extra column that for an index overlay query to be displayed as using index, the MySQL query optimizer determines whether an index overwrites the query before executing the query Note1, the overwrite index also does not apply to any index type, the index must store the value of the column2. Hash and Full-text indexes do not store values, so MySQL can only use B-tree3, and different storage engine implementation of the coverage index is different4. Not all storage engines support them5, if you want to use the overlay index, be sure to note that the select list value to remove the required column, not a SELECT *, because if all the fields are indexed will cause the index file is too large, the query performance is degraded, can not be used in order to take advantage of the overwrite index  InnoDB1. When overriding index queries, you can use their default clustered index columns In addition to the included columns of the index itself 2, This is related to the index structure of Innob, the primary index is B + Tree index storage, that is, we say the data row is indexed, index is the data3, for the secondary index of InnoDB, its leaf node stores the index value and the location to the primary key index, and then needs to query the table's field values through the primary key, so the secondary index stores the value of the primary key4, the overlay index can also be used on the InnoDB default clustered index5.All InnoDB engines have a primary key ID, a transaction ID, a rollback pointer, a non-primary key ID, and his query will be overwritten to get the primary key ID.overwriting an index is a very powerful tool that can greatly improve query performance by simply reading the index without having to read the data with some of the following advantages
1. Index entries are usually smaller than records, so MySQL accesses less data
2. Indexes are stored in order of value, less I/O is required relative to random access records
3, most of the engine can better cache index, such as MyISAM only cache index
4. Overwriting an index is especially useful for innodb tables because InnoDB uses clustered indexes to organize data, and if the two-level index contains the data required for a query, it is no longer necessary to look in the clustered index for
In Sakila's inventory table, there is a composite index (STORE_ID,FILM_ID), and for queries that only need access to these two columns, MySQL can use the index, as followsTable Structure

CREATE TABLE ' inventory ' (  ' inventory_id ' mediumint (8) unsigned not NULL auto_increment,  ' film_id ' smallint (5) unsigned not NULL,  ' store_id ' tinyint (3) unsigned not NULL,  ' last_update ' timestamp not null DEFAULT current_time STAMP on UPDATE current_timestamp,  PRIMARY key (' inventory_id '),  key ' idx_fk_film_id ' (' film_id '),  key ' idx_store_id_film_id ' (' store_id ', ' film_id '),  CONSTRAINT ' fk_inventory_film ' FOREIGN KEY (' film_id ') REFERENCES ' Film ' (' film_id ') on UPDATE CASCADE,  CONSTRAINT ' Fk_inventory_store ' FOREIGN KEY (' store_id ') REFERENCES ' store ' (' St ore_id ') on UPDATE CASCADE) engine=innodb auto_increment=4582 DEFAULT Charset=utf8 |

Query statements

mysql>  EXPLAIN SELECT store_id, film_id from sakila.inventory\g*************************** 1. Row *************           id:1  select_type:simple        table:inventory         type:indexpossible_keys:NULL          key: idx_store_id_film_id      key_len:3          ref:null         rows:4581        extra:using index1 row in Set (0.03 sec)

In most engines, indexes are overwritten only if the columns that the query statement accesses are part of the index. However, InnoDB is not limited to this, InnoDB's level two index stores the value of primary key in the leaf node. Therefore, the Sakila.actor table uses InnoDB, and there are indexes on the last_name, so the indexes can overwrite the queries that access actor_id, as follows

Mysql> EXPLAIN SELECT actor_id, last_name from  sakila.actor WHERE last_name = ' HOPPER ' \g************************ 1. Row ***************************           id:1  select_type:simple        table:actor         type:refpossible_keys:idx_ Actor_last_name          key:idx_actor_last_name      key_len:137          ref:const         rows:2        extra:using where; Using index1 Row in Set (0.00 sec)

Sorting using an index

In MySQL, there are two ways to generate an ordered result set: one is to use Filesort, and the other is to scan by index order

Sorting with an index is very fast, and you can use the same index to find and sort operations simultaneously. Indexes can be used when the order of the indexes is the same as the order of the columns in order BY and all columns are in the same direction (all ascending or all descending), and if the query is connected to more than one table, the index is used only if all the columns in the order by are columns of the first table. Other situations will use Filesort

CREATE TABLE ' actor ' (  ' actor_id ' int (ten) unsigned NOT NULL auto_increment,  ' name ' varchar (+) NOT null DEFAULT ' ,  ' password ' varchar (+) not NULL DEFAULT ' ',  PRIMARY key (' actor_id '),  key ' name ' (' name ')) Engine=innodb DE FAULT Charset=utf8; insert into actor (Name,password) VALUES (' cat01 ', ' 1234567 '), (' cat02 ', ' 1234567 '), (' ddddd ', ' 1234567 '), (' aaaaa ', ' 1234567 ');

1. Explain select actor_id from actor order by actor_id \g

Mysql> Explain select actor_id from actor order by actor_id \g*************************** 1. Row ***************************           id:1  select_type:simple        table:actor         Type:indexpossible_keys: NULL          key:primary      key_len:4          ref:null         rows:4        extra:using index1 row in Set (0.00 sec)

2. Explain select actor_id from actor order by password \g

Mysql> Explain select actor_id from actor order by password \g*************************** 1. Row ***************************           id:1  select_type:simple        table:actor         Type:ALLpossible_keys:NULL          key:null      key_len:null          ref:null         rows:4        extra:using filesort1 row in Set (0.00 sec)

3. Explain select actor_id from actor order by name \g

Mysql> Explain select actor_id from actor order by name \g*************************** 1. Row ***************************           id:1  select_type:simple        table:actor         Type:indexpossible_keys: NULL          key:name      key_len:50          ref:null         rows:4        extra:using index1 row in Set (0.00 sec)

When MySQL cannot use an index for sorting, it uses its own sorting algorithm (fast sorting algorithm) to sort the data in memory (sort buffer), and if memory is not loaded, it blocks the data on the disk, then sorts the individual blocks, The blocks are then combined into an ordered set of results (which is actually an out-of-order)

There are two kinds of sorting algorithms for Filesort,mysql

1, two times scanning algorithm (passes)

This is done by first removing the fields that need to be sorted and the pointer information that can be directly anchored to the relevant row data, then sorting in the set memory (via the parameter sort_buffer_size setting), and then the required columns by the row pointer information after the sorting is completed.
Note: This algorithm is the algorithm used before 4.1, it requires two access data, especially the second read operation will result in a large number of random I/O operations. On the other hand, memory overhead is small

2, one-time scanning algorithm (single pass)

The algorithm takes all the required columns out at once and outputs the result directly after sorting in memory.
Note: This algorithm is used starting with MySQL version 4.1. It reduces the number of I/O, is more efficient, but also has a large memory overhead. If we take out the columns that we don't need, we're going to waste a lot of the memory needed for the sequencing process. In the version after MySQL 4.1, you can control whether MySQL chooses the first sorting algorithm or the second by setting the Max_length_for_sort_data parameter. MySQL chooses to use the first sorting algorithm when the total size of all large fields is larger than the Max_length_for_sort_data setting, whereas the second is selected. In order to improve the sorting performance as much as possible, we naturally prefer to use the second sorting algorithm, so it is necessary to simply take out the required Columns in Query.

When the join operation is sorted, if order by simply refers to the column of the first table, MySQL filesort the table and then connects processing, at which point the explain output "Using filesort"; MySQL must generate a temporary table for the result set of the query and perform a filesort operation after the connection is complete, at which point the explain output "Using temporary; Using Filesort "

MySQL Efficient index coverage index