Mysql优化之覆盖索引

时间:2017-04-25作者:klpeng分类:数据库浏览:1956评论:0

现在有这样一个表,主要是关联用户id和用户组的,表结构如下:

CREATE TABLE `p_user_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `gid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_uid` (`uid`),
  KEY `idx_gid` (`gid`)
) ENGINE=InnoDB AUTO_INCREMENT=387939 DEFAULT CHARSET=utf8;

数据其实不多,387939条,下面有一个需求是取出某一个组id的所有用户uid,我们用一个很简单的查询:

select uid from p_user_role where gid=22;

上面这条查询语句,实际测试之后有一个问题:

使用MyISAM存储引擎的时候,查询耗时只需要0.002s,用InnoDB却需要0.08s左右,而在真实业务环境下,前者0.06秒,后者1.1秒;

因为真实业务环境下,运维组为了统一部署,要求数据表全部为InnoDB存储引擎,那么我们来定位问题,从这条sql入手,explain分析:

Mysql优化之覆盖索引

从结果看来,已经用上了索引,而且这条sql语句也没法再继续优化了,原因到底出在哪呢,要搞明白这个问题,我们首先来分析下,MyISAM和InnoDB的索引实现:

MyISAM存储引擎的索引文件和数据文件是分离的,索引文件保存数据文件的物理地址,可以理解为,MyISAM索引文件的data直接存储了一个指向数据物理地址的指针。

而InnoDB索引文件的data则是保存了数据的主键,在InnoDB使用到索引的时候需要再根据主键做二次查找,这主要跟InnoDB数据文件存储方式有关。


那么回到我们问题上面,既然InnoDB要二次查找,又不能换MyISAM,这个问题是不是无解?

答案当然不是,下面我们来看,利用覆盖索引,来避免掉二次查找:

alter table p_user_role add index idx_gid_uid (gid,uid);

然后我们再执行一次查询,这次只需要0.001s,真实业务环境也从1.1s优化为0.05s;


覆盖索引(covering index),MySQL只需要通过索引就可以返回查询所需要的数据,而不必在查到索引之后再去查询数据,但是需要注意的是所查询的字段必须被索引覆盖到。

在explain的时候,输出的extra信息中如果有"Using index",表示这条查询使用了覆盖索引:

Mysql优化之覆盖索引

打赏
文章版权声明:除非注明,否则均为彭超的博客原创文章,转载或复制请以超链接形式并注明出处。
相关推荐

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

猜你喜欢