利用索引覆盖+延迟关联优化Limit查询
1、MySQL执行计划
MySQL提供了Explain工具，用于查看SQL语句的执行计划。
- 使用语法
explain SQL语句;
- 数据类型
- id：查询的序列号，标识执行的顺序
- id 相同，执行顺序由上至下
- id 不同，如果是子查询，id会递增，id值越大优先级越高，越先被执行
- select_type:查询类型
- type:连接类型。system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
- possible_keys:可能使用到的索引
- key:实际使用的索引
- ref:与key中索引进行匹配的值或列
- rows:将要遍历的记录行数的估计值
- Extra:额外信息
- Using index：查询的数据包含在了索引中，不用再扫描表中的记录。即：索引覆盖
- Using where：检索数据后进行过滤
- Using filesort：对检索结果进行文件排序
- Using index condition：索引下推。存储引擎使用该索引条件对索引进行过滤
- id：查询的序列号，标识执行的顺序
2、索引覆盖
如果索引的叶子节点中包含所要查询的数据，那么就不需要回表查询。换句话说，如果一个索引包含了所有需要查询的数据，就称为索引覆盖。MySQL官方认为：使用explain查看SQL语句执行计划时，如果【Extra】的值为【Using index】则表示索引覆盖了所需查询的数据。对于事务中被修改的表，无法使用索引覆盖。索引覆盖的优点如下：
- 索引条目通常远远小于数据行大小，只扫描索引能够减少数据访问量。
- 索引是按照列值顺序存储的（单个页内），范围查询时扫描索引能够减少排序耗时。
InnoDB引擎使用了聚簇索引和二级索引。聚簇索引根据索引列的值对数据进行排序，并将叶子节点指向对应的行记录。每张表有且只有一个聚簇索引；二级索引的叶子节点保存了主键值，查询数据时仍需根据主键值扫描聚簇索引查询数据。如果聚簇索引和二级索引可以包含所有需要查询的数据，即可实现索引覆盖。
3、延迟关联
3.1、测试数据准备
- 假设有一张users表
CREATE TABLE `users` (
`id` int NOT NULL AUTO_INCREMENT,
`age` int DEFAULT NULL,
`username` varchar(16) DEFAULT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB
- 添加一些数据:创建如下存储过程并执行
DELIMITER $$
USE `数据库名`$$
DROP PROCEDURE IF EXISTS `init_data_2`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `init_data_2`()
BEGIN
DECLARE age INT;
DECLARE username INT;
SET age = 21;
WHILE age <= 100 DO
START TRANSACTION;
SET username = 1;
WHILE username <= 10000 DO
INSERT INTO users(age,username) VALUES (age,CONCAT('username',username));
SET username = username + 1;
END WHILE;
SET age = age + 1;
COMMIT;
END WHILE;
END$$
DELIMITER ;
3.2、分页查询及其优化
- 对users表进行分页查询：
SELECT * FROM users WHERE age >90 LIMIT 50000,10;
MySQL的limit操作，先查询50010条记录，丢弃前50000条，返回最后10条。 - 执行计划： 
- 给age添加索引
ALTER TABLE users ADD INDEX index_age(age);
- 执行计划：
- 延迟关联 所谓延迟关联，是指先通过子查询从二级索引中获取目标数据的主键构成临时表，并对临时表中的数据进行筛选，再通过原表与临时表进行关联操作获取对应的数据。即：先对二级索引的结果进行筛选，然后再扫描主键索引。通过延迟二级索引与主键索引之间的关联操作，减少关联数据量，从而提高性能。
select s.* from users s inner join (select id from users where age>90 limit 50000,10)as temp on (temp.id=s.id);
- 执行计划：
2、3、1）3、1、2。第3行：首先从index_age索引树中检索出所有age>90的数据，并选择前50010条数据构成一张临时表；第1行：从临时表中选择最后10条数据。第2行：将这10条数据与s表(users表的别名)进行关联操作，通过主键值扫描主键索引树获取数据。 - 执行结果：
投递柠檬微趣等公司10个岗位 >
