Mysql覆盖索引及其使用注意事项

创业点子 阅读(1460)
欢迎关注我的头条号:Wooola,10年Java软件开发及架构设计经验,专注于Java、Golang、微服务架构,致力于每天分享原创文章、快乐编码和开源技术。

作者 | 奔跑吧CTO 来源:微信公众号 授权头条首发

Mysql 覆盖索引及其使用注意事项

五, 覆盖索引优化示例

有这么一张表:

 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= DEFAULT CHARSET=utf8

看AUTO_INCREMENT就知道数据并不多,75万条。然后是一条简单的查询:

SELECT SQL_NO_CACHE uid FROM user_group WHERE group_id = 245;

该条语句查询需要0.15s左右。但是真实的业务需求比这个复杂,查询需要2.2s,最终定位到问题症结是在这条SQL。

Explain的结果是:

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

看上去已经用上索引了,好像已经没有办法优化了。实际上不然,当我们再创建一个如下联合索引时(注意,创建该索引后要把原来的group_id索引删除,因为按照定义的先后顺序,where条件会优先使用group_id索引而不是联系索引查询):

ALTER TABLE user_group ADD INDEX group_id_uid (group_id, uid);

然后,不可思议的事情发生了……这句SQL查询的性能发生了巨大的提升,居然已经可以跑到0.00s左右了。经过优化的SQL再结合真实的业务需求,也从之前2.2s下降到0.05s。

再Explain一次:

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

可以看到使用了覆盖索引,MySQL只需要通过索引就可以返回查询所需要的数据,而不必在查到索引之后再去查询数据,SQL执行性能得到很大优化。所以平时对于覆盖索引的使用也是Mysql比较重要的一部分。