利用索引覆盖+延迟关联优化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：索引下推。存储引擎使用该索引条件对索引进行过滤

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条。 - 执行计划：执行计划中type=ALL，表示对表进行了全扫描。找出所有age>90的记录，然后选择前50010条数据，最后返回后10条。 - 执行结果：图片说明

给age添加索引

ALTER TABLE users ADD INDEX index_age(age);

- 执行计划：

图片说明 Using index condition表示对索引进行了条件查询、Using MRR表示将随机磁盘读转换为顺序磁盘读。添加索引后，在index_age索引树中检索满足age>90的所有数据，然后选择其中50010条主键，将这些主键暂存后排序（Using MRR），并按照顺序扫描主键索引树获取对应的数据50010条，然后返回后10条。因为索引是以索引文件的形式存储在磁盘中的，当文件很大时只能有一部分索引页存在于内存当中。当通过主键随机访问主键索引时，可能发生多次索引文件磁盘读操作；通过将主键排序后再顺序访问主键索引，可以减少磁盘读操作，提高性能。 - 执行结果：图片说明

延迟关联所谓延迟关联，是指先通过子查询从二级索引中获取目标数据的主键构成临时表，并对临时表中的数据进行筛选，再通过原表与临时表进行关联操作获取对应的数据。即：先对二级索引的结果进行筛选，然后再扫描主键索引。通过延迟二级索引与主键索引之间的关联操作，减少关联数据量，从而提高性能。

select s.* from users s inner join (select id from users where age>90 limit 50000,10)as temp on (temp.id=s.id);

- 执行计划：

根据执行计划的id得知，上图中的执行顺序为（~~2、3、1~~）3、1、2。第3行：首先从index_age索引树中检索出所有age>90的数据，并选择前50010条数据构成一张临时表；第1行：从临时表中选择最后10条数据。第2行：将这10条数据与s表(users表的别名)进行关联操作，通过主键值扫描主键索引树获取数据。 - 执行结果：