php覆盖索引,Mysql优化之覆盖索引

现在有这样一个表，主要是关联用户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分析：

从结果看来，已经用上了索引，而且这条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"，表示这条查询使用了覆盖索引：

打赏

微信扫一扫，打赏作者吧～