MySQL聚族索引、辅助索引、回表和索引覆盖

聚族索引与辅助索引的概念

聚簇索引:将数据存储与索引放到了一块,找到索引也就找到了数据。表数据按照索引的顺序来存储的,也就是说索引项的顺序与表中记录的物理顺序一致。
辅助索引/非聚簇索引:将数据存储与索引分开,叶结点包含索引字段值及指向数据页数据行的逻辑指针,其行数量与数据表行数据量一致。
主要针对InnoDB引擎,InnoDB的数据文件本身就是按照B+树方式存放的;而MyISAM的数据文件是放在MYD文件中,索引是放在MYI文件中,都是辅助索引

什么是索引覆盖,哪些情况需要回表

索引覆盖:只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快。explain的输出结果Extra字段为Using index时,能够触发索引覆盖。
回表查询:先定位主键值,再定位行记录,它的性能较扫一遍索引树更低。
辅助索引上没有需要的数据时需要回表,辅助索引的数据包括:主键、索引字段

使用索引覆盖/延迟关联减少回表次数

如果要查询较大数据表的最后几页(偏移量很大),则查询时是先查询满足where条件的记录、回表,再放弃前N页记录的,这样回表次数很多
延迟关联则是:通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。使用方法类似下面sql:

select * from user a join (select id from user limit 20000, 10) b on a.id=b.id;
select * from user where id in (select id from user limit 20000, 10);

实例:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `department` varchar(255) NOT NULL,
  `passwd` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_u` (`name`,`department`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

insert into user (name,department,passwd) values ('hubery', 'ued', '123');

1、select department from user where name='ued'会用到索引覆盖

2、select department from user where name='ued' and department='ued'会用到索引覆盖

3、select passwd from user where name='ued'不会使用索引覆盖,需要回表

4、select passwd from user where passwd='123'不会使用索引覆盖,需要回表

5、select name from user会用到索引覆盖

6、select passwd from user不会使用索引覆盖

总结:会用到索引覆盖的情况:
1、查询条件命中索引,且查询字段是这个索引的字段或者主键;
2、没有查询条件,但查询字段是某个索引的字段或者是主键

参考:https://www.cnblogs.com/pufeng/p/11750495.html

原文地址:https://www.cnblogs.com/hubery/p/15682539.html