mysql优化超多分页场景

 

一，造数据

 

-- CREATE TABLE 
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 关闭索引造数据
-- 关闭：
ALTER TABLE `test` DISABLE KEYS ;
-- 开启：
ALTER TABLE `test` ENABLE KEYS;

--  过程造数据
create procedure my_procedure()
begin
  DECLARE n int DEFAULT 1;
  WHILE n < 1000000 DO
    insert into test (id) value (n); 
    set n = n + 1;
  END WHILE;
end

-- 调用过程
call my_procedure();

-- 删除过程
drop procedure if exists my_procedure;

-- 查看数量
select  MAX(id)  from test;

 

二，大概思路

想到有 子查询，延迟关联 ， IN  ，BETWEEN AND。具体效果看执行计划的EXPLAIN 。

另外可以做逻辑分页，例如百万数据前提下，10%可以用正常分页，50% 设置正反双向，然后加上对应的分页优化。

先看看阿里推荐的

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

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

正例：先快速定位需要获取的ID段，然后再关联

select a* from table1 a ,(select id from table1 where 条件 limit 100000,20)b where a.id=b.id;

 

三，执行计划反馈

navict执行

-- 子查询方式，索引扫描  
EXPLAIN SELECT id FROM test WHERE id >= (SELECT id FROM test ORDER BY id LIMIT 500000 , 1) LIMIT 5;
-- 阿里  
-- 利用延迟关联或者子查询优化超多分页场景
EXPLAIN select a.id FROM test a ,(select id from test  limit 500000,5)b  where a.id=b.id;

-- in
EXPLAIN SELECT id FROM test WHERE id >= (SELECT id FROM test where id  in (500000))  LIMIT 5;

EXPLAIN SELECT id FROM test  WHERE id in (500000,500001,500002,500003,500004);
-- join 
EXPLAIN SELECT id FROM test a  INNER JOIN (select id from test where id >500000 limit 10) b USING(id);

-- BETWEEN AND
EXPLAIN SELECT id FROM test  WHERE  id BETWEEN 500000 AND 500004;

 

 

-- 子查询方式，索引扫描
SELECT id FROM test WHERE id >= (SELECT id FROM test ORDER BY id LIMIT 500000 , 1) LIMIT 5;

 

-- 阿里 
-- 利用延迟关联或者子查询优化超多分页场景
select a* FROM test a ,(select id from test where limit 100000,20)b  where 1.id=b.id;

-- in
SELECT id FROM test WHERE id >= (SELECT id FROM test where id  in (500000)  LIMIT 5;

SELECT id FROM test  WHERE id in (500000,500001,500002,500003,500004)

 

-- join 
EXPLAIN SELECT id FROM test a  INNER JOIN (select id from test where id >500000 limit 10) b USING(id);

-- BETWEEN AND
EXPLAIN SELECT id FROM test  WHERE  id BETWEEN 500000 AND 500004;

 

cmd

 

耗时

 

四，结论

BETWEEN AND  如果int不连续，无法使用

IN   使用in会先查询出来一条id，之后再去和下面进行匹配，会进行表进行全表扫描！，但是如果知道确定的具体的对应的5个id，这个应该是最快的。

综合来讲阿里的方案是最有效，最符合实际开发的，效率也相对高。

 

小编最近又得到一种方式，假设是这样的，我们的表数据都有对应的add_time（创建时间），1百万的数据，分页的话记录最后一条数据的add_time，那么sql就变成以下这样：

SELECT id FROM test where add_time > 记录的时间 LIMIT 10

根据对应条件的索引，快速找到位置，并只有10条检索信息，对于这种情况，每次都是检索10条数据，大大加快分页效率