利用延迟关联或者子查询优化超多分页的场景

首先创建测试表test，然后分配两个字段，id是逻辑自增主键，然后一个name的varchar字符串

DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
CREATE TABLE test.test (
	id INT (10) NOT NULL AUTO_INCREMENT,
	username VARCHAR (10) NOT NULL,
	content VARCHAR (10) NOT NULL,
	description VARCHAR (10) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE INNODB DEFAULT CHARSET utf8 COMMENT '测试表';

然后我们往表中插入1W条测试数据，这里利用MySQL的存储过程来做

TRUNCATE TABLE test.test;

# 定义存储过程
delimiter $$
DROP PROCEDURE IF EXISTS insert_test_val;
CREATE PROCEDURE insert_test_val()
BEGIN

DECLARE i INT DEFAULT 1;
WHILE i <= 10000 DO
	INSERT INTO test.test VALUES (NULL, "test", "x", "x");
	SET i = i + 1;
END WHILE;

END
$$

# 调用存储过程
CALL insert_test_val();

进入第一步创建的test数据库执行上面的脚本就行了

时间: 35.632s

存储过程插入挺慢呀，我插入1W行用了35秒

然后先看下面原始的limit语句执行分析

EXPLAIN SELECT * FROM test LIMIT 9990,10;

然后使用延迟关联。先快速定位需要获取的id段，然后再自关联或join关联

EXPLAIN SELECT * FROM test a, (SELECT id FROM test LIMIT 9990,10) b WHERE a.id=b.id;

可以看到使用了覆盖索引，不是全表扫描了

MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行。返回N行。那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。