MySQL分页查询优化——延时关联

转自《mysql延时关联

描述

mysql有种sql优化方式,叫延时关联,即通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据,尤其在大分页查询的场景下,可以提高查询效率。例如下面两行SQL查询语句,在使用覆盖索引的大分页查询场景下,第二条的执行速度要远远快于第一条SQL。

select * from table where xxx limit a,b;

select * from table where id in (select id from table where xxx limit a,b);

在覆盖索引的场景下,第一条的执行逻辑是

  1. 通过索引找到(a+b)条符合查询条件的记录id
  2. 再通过(a+b)个id回表查询这(a+b)条记录
  3. 最后按分页条件给用户返回b条记录

而第二条SQL的执行逻辑则是

  1. 通过索引找到(a+b)条符合查询条件的记录id
  2. 按分页条件取b个记录id,然后回表查询这b条记录
  3. 最后给用户返回b条记录

不难看出,第二条SQL在覆盖索引的场景下,减少了大量的回表执行次数,从而提高了执行效率。而在非索引覆盖的场景下,延时关联失效,两种SQL的执行速度没有多少区别。

测试

创建一个测试表,并插入近200万条测试数据。

CREATE TABLE `salary_static` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `school_id` int(11) NOT NULL COMMENT '学校id',
  `student_id` int(11) NOT NULL COMMENT '毕业生id',
  `salary` int(11) NOT NULL DEFAULT '0' COMMENT '毕业薪水',
  `year` int(11) NOT NULL COMMENT '毕业年份',
  PRIMARY KEY (`id`),
  KEY `year_key` (`year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='毕业生薪水数据统计';
delimiter  //
CREATE PROCEDURE init_salary_static() 
BEGIN 
	DECLARE year INT;
	DECLARE schid INT;
	DECLARE stuid INT;
	SET year = 2000;
	WHILE year < 2020 DO
		START TRANSACTION; 
		SET schid = 1;
		WHILE schid < 100 DO
			SET stuid = 1;
			WHILE stuid < 1000 DO
				insert into salary_static(school_id,student_id,salary,year) values (schid,stuid,floor(rand()*10000),year);
				SET stuid = stuid + 1;
			END WHILE;
			SET schid = schid + 1;
		END WHILE;
		SET year = year + 1;
		COMMIT; 
	END WHILE;
END //
delimiter ;

call init_salary_static();

覆盖索引的场景

select * from salary_static where year < 2010 limit 500000,10;

select s.* from salary_static s join 
(select id from salary_static where year < 2010 limit 500000,10)
as tmp on (tmp.id = s.id);

在这里插入图片描述

由图可见,优化前sql查询平均需要大约0.8秒的时间,而通过延时关联优化后,则仅需要大约0.1秒的时间。即在覆盖索引的场景下,延时关联对于大分页查询可以有效提高查询效率。

回表的场景

select * from salary_static where year < 2010 and salary < 8000 limit 500000,10;

select s.* from salary_static s join 
(select id from salary_static where year < 2010 and salary < 8000 limit 500000,10)
as tmp on (tmp.id = s.id);

在这里插入图片描述

由图可见,优化前sql查询平均需要大约1.2秒的时间,而通过延时关联优化后,依然需要大约1.2秒的时间。即在非覆盖索引的场景下,延时关联不能提高查询效率。

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值