什么是覆盖索引，覆盖索引如何加快超大分页查询

什么是覆盖索引，覆盖索引如何加快超大分页查询

在介绍什么是覆盖索引之前，要先介绍什么是聚集索引，什么是二级索引

聚集索引（聚簇索引）就是在索引数据结构（一般为b+树，下面我直接称为索引树）中直接包含整行数据的，一个表只有一个聚集索引，聚集索引有下面的推举规则，优先级一次递减

1. 如果表有主键，使用主键索引
2. 如果没有主键，使用第一个唯一索引做聚集索引
3. 如果表中没有合适的filed做聚集索引，mysql会自建一个rowid作为覆盖索引

二级索引就是在索引树种不包括表中原始数据，仅存储行数据的聚集索引值，如果使用二级索引查询数据，mysql会现在二级索引种取得查询数据的聚集索引值，然后再去聚集索引树种查询原始行数据

而整个从二级索引种获取聚集索引值（一般为主键值），然后再拿这个聚集索引值去聚集索引树中查原始数据的过程，就叫做回表

什么是覆盖索引

知道前面的概念，覆盖索引就是使用的索引树里面直接就有原数据，查询操作不需要徽标操作，我总结有下面集中情况是覆盖所有

1. 使用聚集索引查询数据，一定是覆盖索引
2. 使用二级索引查询索引本身的值，或者聚簇索引值

第二点我举个例子，我有一张User表,UserId 为主键，UserName为二级索引，还有其它字段

下面的查询语句为覆盖索引

select * from user where user_id = 1

select user_id , user_name from user where user_name = 'LiuWenchao'

下面的查询语句不是覆盖索引，在二级索引树中找到聚集索引值(user_id )后会拿user_id 到user_id树中查询原始数据，存在回表操作

select * from user where user_name = 'LiuWenchao'

使用覆盖索引加速超大分页查询

MySQL Limit 语法格式：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

比如下面的查询语句

select * from user limit 1000000, 10 

使用explain语句可以看见mysql 的查询计划，两个不同查询计划分别如下

可以看到直接查询的话没有命中索引，而且是全表查询，效率非常低，而且这整个过程是存储引擎会将1000010条数据全部查询并返回Serve层，Serve层再更具limit参数将前面1000000条舍弃，相当于这个语句查了1000010条数据！

那么我们可以利用id覆盖索引来加快进程，比如将查询语句修改为下面语句

select * from user
where id >= (SELECT user_id FROM user limit 100000, 1)
LIMIT 10

上面的语句先用子查询SELECT user_id FROM user limit 100000, 1获取第100000条数据的id值，然后再根据id取第100000-100010条数据，走的都是覆盖索引，而且仅查询了十条数据，性能比之前的语句提升非常大，但是有个缺点，这里面的id必须是自增的，否则两个语句查出来的结果会不同

下面提供另外两种优化方式，原理都是一样的，但是语句不一样

SELECT u1.* FROM user u1
WHERE u1.user_id in (SELECT u2.user_id from (SELECT user_id FROM user limit 100000, 10) as u2)

SELECT * FROM user u1 
JOIN (SELECT user_id from user WHERE status=1 
limit 100000, 10) u2
ON u1.id = u2.id