mysql大表的深度分页慢sql案例(跳页分页)

1 背景

有一张表，内容是 redis缓存中的key信息，数据量约1000万级，

expiry列上有一个普通B+树索引。

-- test.top definitionCREATE TABLE `top` (`database` int(11) DEFAULT NULL,`type` varchar(50) DEFAULT NULL,`key` varchar(500) DEFAULT NULL,`size_in_bytes` varchar(50) DEFAULT NULL,`encoding` varchar(50) DEFAULT NULL,`num_elements` int(11) DEFAULT NULL,`len_largest_element` varchar(50) DEFAULT NULL,`expiry` varchar(50) DEFAULT NULL,`idid` int(11) DEFAULT NULL,KEY `top_key_IDX` (`key`) USING BTREE,KEY `top_expiry_IDX` (`expiry`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我们的需求是按照expiry进行降序排序，分页查询10条数据，当执行的页码比较深pageNo=25000页时，就会出现深度分页问题。

1.1 原始深度分页sql

 select * from top order by expiry  desc limit  250000,10;

 深度分页会引起索引失效，走全表扫描的现象 。

1.2 原始深度分页sql执行耗时

共耗时 1m24s

 

 

 2、解决方案

2.1 优化之后sql

select * from top where expiry <= (select expiry from top  order by expiry desc     limit 250000,1 )order by expiry desc  limit  1,10;

充分利用索引覆盖的特性，扫描索引结构，避免全表扫描。

注意事项：

1、mysql在子查询中是不支持这个order by limit的除了，limit 1，只返回单条数据这个是支持的；

 2.2 优化之后sql执行耗时

328ms 

3、总结

     深度分页往往会带来全表扫描查询慢的问题，我们一定要分析执行计划，要么利用连续分页特性解决问题、要么利用索引扫描来解决问题。