limit入坑指南一：回表查询

limit 原理

第一步.先查询offset+count条数据；
第二步.再抛弃前offset条数据

Limit效率高：数据库的数量很大，但是只需要查询一部分数据的情况。
　　高效率的原理是：避免全表扫描，提高查询效率。

上图员工表的查询即全表扫描，那么怎么避免全表扫描

加上LIMIT 1，只要找到了对应的一条记录，就不会继续向下扫描了，效率会大大提高。

imit的效率低：只使用limit来查询语句，并且偏移量特别大的情况
　　现在有一个订单表，数据量达到千万级表（真正不会这么查询，这里只是举例）
语句1：
　　select * from order limit 4,2 用时0.086s
语句2：
select * from order limit 4000000,2 用时8.379s 　　
为什么语句2比语句1慢了将近100倍，这个数字会随着偏移量增加而增加

mysql索引

InnoDB的聚集索引

聚集索引的叶子节点存储每条记录数据
InnoDB必须要有且只有一个聚集索引，下面聚集索引的生成规则：

如果表定义了PK（Primary Key，主键），那么PK就是聚集索引。
如果表没有定义PK，则第一个NOT NULL UNIQUE的列就是聚集索引。
否则InnoDB会另外创建一个隐藏的ROWID作为聚集索引。

这种机制使得基于PK的查询速度非常快，因为直接定位行记录。

InnoDB普通索引

InnoDB普通索引的叶子节点存储主键值。想拿到行数据，还得去聚集索引中扫描索引树。

注意，不是存储行记录头指针，MyISAM的索引叶子节点存储记录指针。

回表查询

如果索引的列在 select 所需获得的列中或者根据一次索引查询就能获得记录就不需要回表，如果 select 所需获得列中有大量的非索引列，索引就需要到表中找到相应的列的信息，这就叫回表。

举个例子，员工表我要查询id为1的员工
select * from employee where id = 1
因为id是主键（聚集索引)，根据主键的查询方式，则只需要搜索 ID 这棵 B+ 树。主键是唯一的，根据这个唯一的索引，MySQL 就能确定搜索的记录。

而下面的语句则需要进行徽标查询，虽然emp_no是普通索引，但是查询了除emp_no以外的字段
selelct * from employee where emp_no = ‘zs‘
原因是通过 emp_no 这个普通索引查询方式，则需要先搜索 emp_no 索引树，然后得到主键 ID 的值为 1，再到 ID 索引树搜索一次。这个过程虽然用了索引，但实际上底层进行了两次索引查询，这个过程就称为回表。

所以limit随偏移量增加而增加是因为进行了多次的回表查询

那么怎么避免回表呢？索引覆盖

如果一个索引覆盖（包含）了所有需要查询的字段的值，这个索引就是覆盖索引，因为覆盖索引必须要存储索引的列值，而哈希索引、空间索引和全文索引等都不存储索引列值，所以MySQL只能使用B-Tree索引做覆盖索引。

覆盖索引就是把所有需要查询的字段都放到普通索引中，这样普通索引查到的叶子结点中已经能够得到所需的所有字段，就不会再去聚集索引中再查询。即sql改为
selelct emp_no from employee where emp_no = ‘zs‘

覆盖索引不仅可以通过减少查询字段只查询索引有的字段来实现，还可以通过联合索引来将多个字段设置为一个索引来实现

偏移量大的sql怎么优化呢

有的时候索引覆盖也不能解决上述问题，那么我们就应该优化语句做到避免回表查询

1 采用where…limit…

where id > offset limit count
巧妙利用聚集索引定位记录行避开回表
但不是所有的表主键都是id，也不是所有的id都是连续的

2.延迟关联或者子查询
select emp.* from employee emp
inner join（select id from employee limit 4000000）tmp
on emp.id = tmp.id
用时 0.860S 速度提升10倍