MySQL分页offset过大性能问题与优化 - DaemonCoder

最近被DBA报了一个慢查询，一条sql要执行几秒甚至十几秒，直接导致cpu idle报警，幸好线上没有出事故。这里记录下问题的详细信息和解决方案。

Mysql表结构大概可以简化为这样：

CREATE TABLE `t` (

`a` int(11) NOT NULL,

`b` int(11) DEFAULT NULL,

`c` int(11) DEFAULT NULL,

PRIMARY KEY (`a`),

KEY `idx_c` (`c`)

) ENGINE=InnoDB

a、b、c三列，a列是主键，c列上有索引（当然线上真实的表结构比这个更复杂，此处只是为了说明问题，略去不相关字段）。

这个表的数据量已经达到千万级别。

导致慢查询的sql为：

SELECT b FROM t WHERE c<1000 LIMIT 2000000, 500

可以看到这个一个分页查询，从位置2000000处开始，取500条数据，问题的原因正是这个过大的分页起点导致。mysql分页查询会并不是直接跳过前2000000再取出500条数据，而是把前2000000条和后面的500条都取出来，再把前2000000条抛弃，这样的话，上面的慢查询相当于从表中取2000500条数据，这么大的数据量必然会慢。

sql修改为：

SELECT b FROM (SELECT a FROM t WHERE c<1000 LIMIT 2000000, 500) ta INNER JOIN t tb ON ta.a = tb.a

这种方式先用一个子查询表的主键（还是和原来一样带有过大分页），结果做为一个临时表，再和原来的t表JOIN，查出需要的字段。

这种方式不仔细看的话，也是要查出2000500条数据，因为子任务的where和limit设置和原来一样，关键就在于子任务SELECT出来的是a字段（t表的主键），而不是像原来直接b字段，这样查出500条数据后再和原有的表join再查出需要的数据字段b，下面详细分析下这个细节带来的性能差异。

基础知识：innodb的索引分为聚集索引和辅助索引，innodb是用聚集索引组织数据的，辅助索引上只存了一个主键，按辅助索引查询数据时，先从辅助索引对应记录的主键，再用主键去聚集索引查具体的数据字段。（这里不详细分析两个种索引的区别，不了解可以自行百度）

上面的慢sql会从辅助索上查2000500条数据，对于每一条数据还要从聚集索引上查一次。修改后的sql会从辅助索引上查出2000500条主键，由于辅助索引上本身就有主键，所以这2000500无需再去聚集索引查，生成临时表后再把这500条数据去聚集索引查出b字段，sql从聚集索引中查2000500条数据变成了只需要查500条，并且b字段在真实的情况往往是大量数据的字段，因此修改前后的sql性能差别很大（这里我理解修改前的sql按辅助索引顺序查询时，再去查聚集索引就不再是顺序读了，而是随机的离散读，也是一部分性能差的原因，具体只是自己的猜测，没有验证）。

修改前后的sql耗时从几秒降到了几十毫秒（这个差异和实际情况有关）。