MySQL overlay index (Dick's ruthless, increase speed)

So there is a table:

CREATE TABLE ' User_group ' (   ' id ' int (one) not null auto_increment,   ' uid ' int (one) ' NOT null,   ' group_id ' int (one) N OT NULL,   PRIMARY key  (' id '),   key ' uid ' (' uid '),   key ' group_id ' (' group_id '),) Engine=innodb auto_ increment=750366 DEFAULT Charset=utf8

See Auto_increment to know that the data is not many, 750,000 article. Then there is a simple query:

SELECT Sql_no_cache uid from user_group WHERE group_id = 245;

It's simple, right? The weird part is:

If you switch to MyISAM as the storage engine, the query takes only 0.01s, and InnoDB will be about 0.15s

If just so little difference is not really a big deal, but the real business needs than this complex, resulting in a large gap: MyISAM only need to 0.12s,innodb 2.2s., and finally locate the problem is in this SQL.

The result of explain is:

+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+ | ID | Select_type | Table      | type | possible_keys | key      | key_len | ref   | rows | Extra | +----+-------------+------------+------+---------------+----------+---------+-------+------+-------+ |  1 | Simple      | user_group | ref  | group_id      | group_id | 4       | const | 5544 |       | +----+-------------+----------- -+------+---------------+----------+---------+-------+------+-------+

It looks like the index has been used, and this SQL statement is so simple that I can no longer optimize it. Finally asked the former colleague Gaston diagnosis, he thought: The data distribution, group_id the same more, the UID hash is more uniform, the effect of the index is general, but it is recommended that I try to add a multi-column index:

ALTER TABLE user_group ADD INDEX group_id_uid (group_id, UID);

Then, something incredible happened ... This SQL query performance has been greatly improved, incredibly can run to about 0.00s. Optimized SQL, combined with real business requirements, has dropped to 0.05s from the previous 2.2s.

Explain once more:

+----+-------------+------------+------+-----------------------+--------------+---------+-------+------+------- ------+ | ID | Select_type | Table      | type | Possible_keys         | key          | key_len | ref   | rows | Extra       | +----+-------------+------------+------+-----------------------+--------------+---------+-------+--- ---+-------------+ |  1 | Simple      | user_group | ref  | group_id,group_id_uid | group_id_uid | 4       | const | 5378 | Using Index | +----+-------------+------------+------+-----------------------+--------------+---------+-------+------+------- ------+

Originally this is called the Overlay Index (covering), MySQL only need to pass the index to return the data required by the query, and do not have to find the index after the query data, so that is quite fast!! However, it is also required that the queried field must be overwritten by the index, and in the case of explain, if there is a "using index" in the output extra information, the query uses an overwrite index.

However, there is another problem that cannot be explained is that, without overwriting the index, why is it so much faster to use MyISAM, and InnoDB is so much slower? Ask the truth ...

Original source: http://xiaobin.net/201109/strange-sql-performance-problem/

MySQL overlay index (Dick's ruthless, increase speed)