Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

MySQL SQL Optimization-override Index (covering index)

2025-04-02 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

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

Look at AUTO_INCREMENT to know that there is not much data, 750,000. Simple query:

SELECT SQL_NO_CACHE uid FROM user_group WHERE group_id = 245;

-- SQL_NO_CACHE does not use cache hints

The result of explaining 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 seems that the index has been used. In terms of data distribution, there are many with the same group_id, and the uid hash is relatively uniform. The effect of adding an index is general. Try adding a multi-column index:

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

This SQL query performance has been greatly improved, and it can actually run to about 0.00s. Optimized SQL combined with real business requirements also dropped from 2.2s to 0.05s.

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 | group_id_uid | 4 | const | 5378 | Using index |

+----+-------------+------------+------+-----------------------+--------------+---------+-------+------+-------------+

This is called covering index, MySQL only needs to return the data needed by the query through the index, and does not have to query the data after finding the index, so it is quite fast!! But at the same time, it also requires that the query field must be covered by the index. When explaining, if there is "Using Index" in the output Extra information, it means that the query uses an overlapping index.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 232

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report