mysql上亿数据秒级查询_MySQL大量数据分页查询，如何优化？

作者：悠悠i

cnblogs.com/youyoui/p/7851007.html

一 背景

大部分开发和DBA同行都对分页查询非常非常了解，看帖子翻页需要分页查询，搜索商品也需要分页查询。那么问题来了,遇到上千万或者上亿的数据量怎么快速的拉取全量，比如大商家拉取每月千万级别的订单数量到自己独立的ISV做财务统计;或者拥有百万千万粉丝的公众大号,给全部粉丝推送消息的场景。本文讲讲个人的优化分页查询的经验，抛砖引玉。

二 分析

在讲如何优化之前我们先来看看一个比较常见错误的写法

SELECT * FROM table

where kid=1342 and type=1 order id asc limit 149420 ,20;

该SQL是一个非常典型的排序+分页查询:

order by col limit N,M

MySQL 执行此类SQL时需要先扫描到N行，然后再去取M行。对于此类操作，获取前面少数几行数据会很快，但是随着扫描的记录数越多，SQL的性能就会越差，因为N的值越大，MySQL需要扫描越多的数据来定位到具体的N行，这样耗费大量的 IO 成本和时间成本。一图胜千言，我们使用简单的图来解释为什么 上面的sql 的写法扫描数据会慢。

t 表是一个索引组织表，key idxkidtype(kid,type) 。

符合kid=3 and type=1 的记录有很多行,我们取第 9，10行。

select * from t where kid =3 and type=1 order by id desc 8,2;

MySQL 是如何执行上面的sql 的？对于Innodb表，系统是根据 idxkidtype 二级索引里面包含的主键去查找对应的行。对于百万千万级别的记录而言，索引大小可能和数据大小相差无几，cache在内存中的索引数量有限，而且二级索引和数据叶子节点不在同一个物理块