MySQL索引优化之覆盖索引

小云云原创: 2018-03-22 09:29:531742浏览

最近处理老的业务代码遇到个问题，本文主要和大家分享MySQL索引优化之覆盖索引，希望能帮助到大家。

最近处理老的业务代码遇到这么个例子：

表结构如下：

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的数据，这么一个语句：

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

慢查询日志里居然很多用了2秒的，Explain的结果是：

+----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------------+ 
| 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    | 
+----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------------+

从Explain的结果可以看出，查询已经使用了索引，但为什么还这么慢？

分析：首先，该语句ORDER BY 使用了Using filesort文件排序，查询效率低；其次，查询字段不在索引上，没有使用覆盖索引，需要通过索引回表查询；最后，数据分布上，gid相同的比较多，uid散列比较均匀，只利用二级索引的效果一般（不清楚索引分类请点击：MySQL索引分类介绍）。

解决方案：由于只需查询uid字段，添加一个联合索引便可以避免回表和文件排序，利用覆盖索引提升查询速度，同时利用索引完成排序。

覆盖索引：MySQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。

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

再Explain一次：

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信息已经有'Using Index'，表示已经使用了覆盖索引（平时SELECT *的同事很多，坑）。

为什么语句需要手动指定使用哪个索引？因为MySQL查询优化器有可能使用了idx_gid这个索引，除非把它删除。

经过索引优化之后，线上的查询基本不超过0.001秒。

最后一个问题：如果这个表用的是MyISAM引擎呢，那么实际情况又会如何？

MySQL索引优化之覆盖索引

相关文章