MySQL SQL Optimization-override Index (covering index)
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.
