海量数据分页查询怎么优化？

背景

一般我们数据量大的时候，然后就需要进行分页，一般分页语句就是limit offset，rows。这种分页数据量小的时候是没啥影响的，一旦数据量越来越大随着offset的变大，性能就会越来越差。下面我们就来实验下：

准备数据

• 建一个测试表引擎为MyISAM（插入数据没有事务提交，插入速度快）的表。

​
CREATE TABLE USER (
id INT ( 20 ) NOT NULL auto_increment,
NAME VARCHAR ( 20 ) NOT NULL,
address VARCHAR ( 20 ) NOT NULL,
PRIMARY KEY ( id ) 
) ENGINE = MyISAM;​

• 写一个批量插入的存储过程

delimiter //
# 删除表数据
TRUNCATE TABLE t;
# 如果已经有sp_test_batch存储过程，将其删除，后面重新创建
DROP PROCEDURE IF EXISTS sp_test_batch;
# 创建存储过程，包含num和batch输入，num表示插入的总行数，batch表示每次插入的行数
CREATE PROCEDURE sp_test_batch(IN num INT,IN batch INT)
BEGIN
	SET @insert_value = '';
	# 已经插入的记录总行数
  SET @count = 0;
	# 
	SET @batch_count = 0;
	WHILE @count < num DO
		# 内while循环用于拼接INSERT INTO t VALUES (),(),(),...语句中VALUES后面部分
		WHILE (@batch_count < batch AND @count < num) DO
			IF @batch_count>0
			THEN 
				SET @insert_value = concat(@insert_value,',');
			END IF;
			SET @insert_value = concat(@insert_value,"('name", @count, "','address", @count, "')");
			SET @batch_count = @batch_count+1;	
		END WHILE;
 
		SET @count = @count + @batch_count;
		# 拼接SQL语句并执行
		SET @exesql = concat("insert into user(name,address) values ", @insert_value);	
		PREPARE stmt FROM @exesql;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
		# 重置变量值
		SET @insert_value = '';
		SET @batch_count=0;
	END WHILE;
	# 数据插入完成后，查看表中总记录数
	SELECT COUNT(id) FROM user;
END

• 插入100w数据

测试性能

下面我们分别针对于offset等于不同的值来进行实验：

• offset等于10000时耗时

  

• offset等于100000时耗时：

  

• offset等于1000000时耗时

  

• offset等于5000000时耗时

  

• offset等于10000000时耗时

  

  从上图可以得出随着offset的值越大耗时就越来越多。这还只是1000w数据，如果我们上亿数据呢，可想而知这时候查询的效率有多差。下面我们来进行优化。

性能优化

子查询的分页方式：

SELECT * FROM user WHERE  id >=  
(SELECT id FROM user  ORDER BY id LIMIT 9000000, 1) LIMIT 10
从图可以得出子查询确实速度快了一倍。

JOIN分页方式：

SELECT * FROM user t1 INNER join
(SELECT id FROM user  ORDER BY id LIMIT 9000000, 10) t2 on t2.id =t1.id

• join的方式比子查询性能在稍微好点。

依赖当前页ID优化：

这个时间性能是最好的。这种优化必须要依赖前一次的查询的最大ID，如果是那种分页直接可以指定多少页的是不行的，必须是只能后一页，后一页这么点击。

SELECT id FROM user  where id > 9000000 ORDER BY id  LIMIT 10;

终极优化

• 通过伪列对ID进行分页，可以多线程同时查询，这个适合分页把全量数据加载到缓存。
• 得到ID的范围 ​​

  

select id from(
SELECT @rownum:=@rownum+1 AS rownum, id FROM   user as t1 ,(SELECT @rownum:=0) t2 order
by t1.id asc
) t3 where t3.rownum%5000=0

多个线程执行不同的sql：

select * from user where id >0 and id<=5000 一直到最大的id

结束

• 由于自己才疏学浅，难免会有纰漏，假如你发现了错误的地方，还望留言给我指出来,我会对其加以修正。

• 如果你觉得文章还不错，你的转发、分享、赞赏、点赞、留言就是对我最大的鼓励。

• 感谢您的阅读,十分欢迎并感谢您的关注。

  

  ​​​​