MySQL大表分页查询的坑以及解决方案

最近在做一个需求，需求内容中有一个功能点是查询指定用户标签里的用户id，这里做了分页查询，分页查询是用mysql的LIMIT设置offset和size值来实现的。在程序执行过程中会发现，如果查询的用户标签数据量很大时会出现慢查询告警，这里已经对mysql表的标签名称和用户id字段都加了索引，并且limit的数量也不大，为什么还会出现慢查询呢？经过排查，发现了问题主要出现在分页查询的Limit语句上。

慢查询语句：

?

1	SELECT * FROM user_cluster_name WHERE cluster_name = "test" LIMIT 1100000,1000

慢查询的原因：

limit会扫描全部索引，偏移量越大，速度越慢。

解决办法：

查询时按用户id排序，用uid做游标，查询下一页时，传入上一页最后一个id，不再扫描已经查询过的数据。优化后的sql语句如下：

SELECT * FROM user_cluster_name WHERE cluster_name = "test" and uid > 12345  ORDER BY uid ASC LIMIT 0,1000 

查找出uid大于12345之后的1000个uid。因为我们已经对cluster_name字段和uid字段都加了索引，上面的语句走了索引分页查询也不需要每次都从第一个数据开始扫描，直接从uid>xx的字段开始扫描取出数据，在大表的场景下这种优化的效果会非常显著。

附上Golang代码：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

func (m *Client) BatchGetUserCluster(     ctx context.Context,     clusterName string,     bizType BizType,     cursor int64,     size int, ) (     items []*UserCluster,     isEnd bool,     nextCursor int64,     err error, ) {     items = make([]*UserCluster, 0)     query := m.Client.WithContext(ctx).Table(UserClusterTable).Where("cluster_name=? AND biz_type=?",         clusterName, bizType)     if cursor > 0 {         query = query.Where("uid>?", to.Int64(cursor))     }       err = query.Order("uid ASC").Limit(size).Find(&items).Error     if err != nil {         logrus.Errorf("query rec list failed . %s", err)         return     }     lenItems := len(items)     if lenItems < size {         isEnd = true     } else if lenItems > 0 {         nextCursor = items[lenItems-1].UID     }     return }

服务代码优化后重新发布，慢查询告警再也没出现过了。sql语句执行时间也降到了正常值范围内，慢查询问题算是解决了。

思考：

从上面的结果来看，分页用游标比用offset偏移量好用，为什么mysql还会有offset偏移量做分页的功能呢？

在MySQL中，使用LIMIT和OFFSET进行分页查询确实可能导致偏移量越大时查询速度越慢，这是因为随着偏移量的增加，数据库需要扫描的数据量也相应地增加，从而导致查询性能下降。

尽管存在这种性能问题，LIMIT和OFFSET仍然是非常有用的分页机制，原因如下：

1. 简单性：使用LIMIT和OFFSET提供了一种简单、直观的方式来分页。你只需要指定每页的大小（LIMIT）和要跳过的记录数（OFFSET），然后数据库会为你处理剩下的部分。
2. 灵活性：你可以根据需要随时更改每页的大小或跳过的记录数，而无需更改查询的结构或访问数据的方式。
3. 与SQL标准兼容：LIMIT和OFFSET是许多SQL方言中常见的分页方法，因此使用它们可以帮助确保你的代码具有更好的可移植性。
4. 与程序语言集成：许多编程语言和框架都提供了对LIMIT和OFFSET的支持，这使得在应用程序中实现分页变得相对容易。
5. 易于缓存：如果你对结果进行了适当的缓存，并且使用了诸如Memcached或Redis之类的缓存解决方案，那么LIMIT和OFFSET可以帮助你轻松地处理缓存失效的情况。

尽管LIMIT和OFFSET有其用途，但在处理大量数据时，它们可能不是最高效的分页机制。对于大型数据集，可能需要考虑其他分页策略，例如基于游标、基于位置或基于哈希的分页方法。这些方法通常更适用于大数据场景，并能够提供更好的性能。