MySQL index optimization covering index

I recently encountered a problem when dealing with old business code. This article mainly shares with you the covering index of MySQL index optimization. I hope it can help you.

I recently encountered this example when dealing with old business code:

The table structure is as follows:

CREATE TABLE `group_user` ( 
  `id` int(11) NOT NULL auto_increment, 
  `uid` int(11) NOT NULL, 
  `username` varchar(16) NOT NULL,
  `gid` int(11) NOT NULL, 
  `create_time` int(10) NOT NULL, 
  `update_time` int(10) NOT NULL, 
  PRIMARY KEY  (`id`), 
  KEY `idx_uid` (`uid`), 
  KEY `idx_gid` (`gid`) 
) ENGINE=InnoDB AUTO_INCREMENT=1530312 DEFAULT CHARSET=utf8

150w of data, such a statement:

SELECT SQL_NO_CACHE uid FROM group_user WHERE gid = 2 ORDER BY create_time ASC LIMIT 10;

There are many slow query logs that take 2 seconds. The result of Explain is:

+----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------------+ 
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                          | 
+----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------------+ 
|  1 | SIMPLE      | group_user | ref  | idx_gid       | idx_gid  | 4       | const | 6535 | Using where; Using filesort    | 
+----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------------+

It can be seen from the result of Explain that the query has used the index, but why is it still so slow?

Analysis: First, the statement ORDER BY uses Using filesort file sorting, and the query efficiency is low; secondly, the query field is not on the index, and the covering index is not used, so it needs to be queried through the index back to the table; finally, the data distribution , there are more gids that are the same, uid hashes are relatively even, and the effect of using only secondary indexes is average (if you don’t know the index classification, please click: Introduction to MySQL Index Classification).

Solution: Since only the uid field is queried, adding a joint index can avoid table return and file sorting, use the covering index to improve the query speed, and use the index to complete the sorting.

Covered index: MySQL only needs to use the index to return the data required for the query, without having to find the primary key through the secondary index and then query the data.

ALTER TABLE group_user ADD INDEX idx_gid_ctime_uid (gid, create_time, uid);

Explain again:

EXPLAIN SELECT SQL_NO_CACHE uid FROM group_user USE INDEX(idx_gid_ctime_uid) WHERE gid = 2 ORDER BY create_time ASC LIMIT 10;

+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+--------------------------+ 
| id | select_type | table      | type | possible_keys     | key               | key_len | ref   | rows | Extra                    |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | group_user | ref  | idx_gid_ctime_uid | idx_gid_ctime_uid | 4       | const | 6375 | Using where; Using index |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+--------------------------+

Extra information already has 'Using Index', which means that the covering index has been used (usually there are many colleagues who SELECT *, which is a pitfall).

Why does the statement need to manually specify which index to use? Because the MySQL query optimizer may use the idx_gid index unless it is deleted.

After index optimization, online queries basically do not exceed 0.001 seconds.

The last question: If this table uses the MyISAM engine, what will the actual situation be like?

Related recommendations:

Mysql index optimization method

How to use mysql index optimization

Mysql optimization: in-depth understanding of storage engines and index optimization

The above is the detailed content of MySQL index optimization covering index. For more information, please follow other related articles on the PHP Chinese website!