MySQL深度分页问题+优化-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_46189247/article/details/145013379

问题描述

项目中一个导出Excel数据的功能，遇到导出的数据有几十万条的时候，会触发慢查询报警。导致的原因就是深度分页查询。

问题复现

表结构

数据量有1千万条

CREATE TABLE `test1` (
  `id` int NOT NULL,
  `num1` int NOT NULL DEFAULT '0',
  `num2` varchar(11) NOT NULL DEFAULT '',
  `type1` int NOT NULL DEFAULT '0',
  `type2` int NOT NULL DEFAULT '0',
  `str1` varchar(100) NOT NULL DEFAULT '',
  `str2` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `num1` (`num1`),
  KEY `num2` (`num2`),
  KEY `type1` (`type1`),
  KEY `str1` (`str1`),
  KEY `str2` (`str2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

量少的分页查询

查询耗时为0.002s，并且使用到了索引

深度分页查询

查询耗时为12.387s，而且没有使用到索引变成了全表扫描

原因分析

SELECT * FROM test1 ORDER BY num1 DESC LIMIT m,n

先从二级索引(辅助索引)中找到符合条件的m+n条数据。
由于辅助索引的叶子节点保存的是主键ID，于是从拿着m+n条数据的主键ID到主键索引中找到符合ID条件的数据，然后丢弃m条，返回n条数据。这个过程产生回表操作，如果m+n的值越大回表操作就越频繁，性能就会严重下降。
同时m+n的值很大时，Mysql的优化器会认为全表扫描的性能要优于使用索引，所以上面深度分页时候并没有使用到索引。但是如果查询中使用到了覆盖索引，依然还是会使用索引。

解决方案

子查询

由于 SELECT id FROM test1 ORDER BY num1 LIMIT 100000,1 使用到了覆盖索引，直接返回ID，并没有产生回表查询。

SELECT * FROM test1  WHERE id >= 
(SELECT id FROM test1 ORDER BY num1 LIMIT 100000,1)
LIMIT 10

关联查询

原理也是利用覆盖索引，不产生回表查询

SELECT t1.* FROM test1 AS t1
INNER JOIN (SELECT id FROM test1 ORDER BY num1 DESC LIMIT 100000,10) AS t2
ON t1.id = t2.id
LIMIT 10

使用游标

每一次可以使用上一次查询的10条数据的最大ID作为值去查询，但是这种情况比较受限要看SQL条件。

SELECT * FROM test1  WHERE id > 100000
ORDER BY id ASC
LIMIT 10