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

Source: Internet
Author: User

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.