Understanding MySQL database overwriting Indexes

Respect the author and change fromXiao BinHttp://www.searchdatabase.com.cn/showcontent_53221.htm

There is such a table:

Create Table 'user _ group' ( 'id' int (11) not null auto_increment, 'uid' int (11) not null, 'group _ id' int (11) not null, Primary Key ('id'), key 'uid' ('uid'), key 'group _ id' ('group _ id'), ) engine = InnoDB auto_increment = 750366 default charset = utf8

You can check auto_increment to find that there are not many data records, 0.75 million. Then there is a simple query:

Select SQL _no_cache uid from user_group where group_id = 245;

Very simple, right? The weird thing is:

If MyISAM is used as the storage engine, the query takes only 0.01 s, but InnoDB is about S.

If this is the only difference, it is not a big deal, but the real business needs are more complex than this, resulting in a big gap: MyISAM only needs 0.12 s, innoDB requires 2.2 S ., finally, the crux of the problem was found in this SQL statement.

The explain result is:

+ ---- + ------------- + ------------ + ------ + --------------- + ---------- + --------- + ------- + ------ + ------- + | ID | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------------ + ------ + --------------- + ---------- + --------- + ------- + ------ + ------- + | 1 | simple | user_group | ref | group_id | 4 | const | 5544 | + ---- + ------------- + ------------ + ------ + --------------- + ---------- + --------- + ------- + ------ + ------- +

It seems that the index has been used, and this SQL statement is too simple for me to optimize it. Finally, I asked the former colleague Gaston to make a diagnosis. He thought: On the data distribution, there are many identical group_id values, and the UID hash values are uniform, so the indexing effect is average, however, we recommend that you add a multi-column index:

Alter table user_group add index group_id_uid (group_id, UID );

Then something incredible happened ...... The performance of this SQL query has been greatly improved, and it can be around S. The optimized SQL combined with actual business needs also dropped from 2.2s to 0.05 s.

Explain again:

+ ---- + ------------- + ------------ + ------ + ----------------------- + -------------- + --------- + ------- + ------ + ------------- + | ID | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------------ + ------ + ----------------------- + -------------- + --------- + ------- + ------ + ------------- + | 1 | simple | user_group | ref | group_id, group_id_uid | 4 | const | 5378 | using index | + ---- + ------------- + ------------ + ------ + ----------------------- + -------------- + --------- + ------- + ------ + ------------- +

It turns out that this is the Covering Index. MySQL only needs to use the index to return the data required for the query, instead of querying the data after the index is found, so that's pretty fast !! However, it is also required that the queried field must be covered by the index. In the case of explicit, if the output extra contains "Using Index ", this indicates that this query uses the overwriting index.

Understanding MySQL database overwriting Indexes