当前位置:Gxlcms > PHP教程 > MySQL索引优化之覆盖索引

MySQL索引优化之覆盖索引

时间:2021-07-01 10:21:17 帮助过:14人阅读

最近处理老的业务代码遇到个问题,本文主要和大家分享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索引优化方法

mysql的索引优化如何使用

Mysql优化之深入了解存储引擎,进行索引优化

以上就是MySQL索引优化之覆盖索引的详细内容,更多请关注Gxl网其它相关文章!

人气教程排行