MySQL 数据上亿以后，查询分页问题

[Features]

查询分页一般要最少要执行两条 SQL 语句

` SELECT COUNT(*) FROM tablename WHERE columnName = 'xx' `
` SELECT * FROM tablename WHERE columnName = 'xx' limit 0,10`

因为查询条件是动态的，所以不太可能做缓存
这种情况应该是必须要分表了吧？
有什么数据库能在不分表，走索引的情况下实现良好的体验呢？

[sch1111878]

最近也遇到了这个问题, mysql 已经分表, 单表 2000 万数据, 聚合查询要 20 多分钟, 中间改用过 es, 但是不熟, 其他同事写的也乱, 实在不行就准备换 clickhouse 了,

上面也有说业务表和聚合查询应该要分开的, 不然锁表或者慢 sql 都会业务影响比较大

[thinkershare]

@Features explain 完全不能用来统计行数量，高频更新下返回的数据属于基本不可用垃圾数据。如果需要精确的分页。而且有复杂的查询，我的使用经验是 2000W+后就需要分表（如果是机械硬盘 500w ，有高频写入就需要分表了).
如果数据库是只读的(包括软删除）模式，使用自增 id 是一个解。其它模式我也没找到好办法再 mysql 中实现靠谱的分页。

[sampeng]

1.cursor 法，上亿的数据，就算你 20 一页吧，产品经理麻烦你来给我翻到第 1234567 页谢谢。。。其实都这么多数据了。total 返回一个固定值估计都没人发现。。哈哈哈哈哈
2.分区。哦。不好意思。。窜台了。mysql 没分区。那就只能分表了。
3.外置表。也就是其他的解决方案，这就可以随意脑洞大开的想方案了。问题是很容易成另一坨屎山。任何新技术的引进都会导致复杂度的上升，我相信绝大多数用 mysql 到几亿了才想到分页怎么搞的 team ，玩 clickhouse 好，玩 es 也罢，都是会踩你没踩过的其他的雷的。

[sampeng]

如果是可以迁移库。。有一说一，迁移 pg 保平安。。最少一个分区下去就能解决你大部分问题。。

[ShuA1]

@Features 看业务吧，一般业务用 explain 就够了， 不需要太精确

[NickX]

如果数据量大，第一条 count 做实时其实没太大必要（目测只是展示作用，可以接受一段时间的延迟），可以缓存起来定时查询。第二条，columnName 字段加索引一下子能查出来。

[fiveStarLaoliang]

可以 count 上缓存，再上分表，旧数据分表存储，还可以加个冗余表，存储对应时间范围的主键，尽量降低表数据量，尽量走主键索引

[ninjashixuan]

百万以上大 offset 就很慢了吧

[edk24]

用 es 查询关联索引，分页结果拿到后再取数据库结果补充完整信息

用 es 的话， 会存在 count 不准确的情况 好像是超过 10w 条还是多少就不准确， 但这个不重要 提高搜索精度比如手机号，身份证类似的减少这种情况的发生


数据量大一点的系统都没有去搞 count 这个事情，都是直接偏移游标查下一个位置的数据， 那玩意太耗时了

[edk24]

@edk24 这个是我们维护贵州核酸系统总结来的经验，用 es 来查索引很快

你每页也就大不了 100 条数据， 用 id 去数据库查出来补充其他展示数据 很快的

[egqpwU3F2beQ8V77]

@ShuA1 可以啊 这骚操作学会了

[heliotrope]

我是先执行第二条 SQL 分页前端异步查
场景是 亿级别的数据量 自用的 有各种筛选条件要过滤
目前是查列表
第一页几乎无感 count 要 16 秒左右
用户能接受 先显示出列表 主要是看列表总数用户愿意等
主要是他不愿意花钱上 ES
业务不允许切割的情况下几乎无解 缓存之类的 用户输入的搜索词每次都不一样根本就没办法缓存
EXPLAIN 完全不准 不加任何条件都不准 能差出一个数量级

[illbehere]

搞个 mpp 数据库做从库吧

[dzdh]

zincsearch 、manticoresearch 、sphinx 、solr 、es 、opensearch

[akinoowari]

@edk24 "track_total_hits":true 用这个不会不准,不然默认最大 10000

[MineDog]

记得大学时候还是用 mysql SQL_CALC_FOUND_ROWS 返回总计行，当时理解查一遍开销应该更小才对，后来在工作中就好像都是查两遍了 。 看了文档，原来 8.0 都废弃了 https://dev.mysql.com/worklog/task/?id=12615

[edk24]

@akinoowari 学到了大佬

[jowan]

这个问题很简单 你搜索淘宝和京东的时候看看最多给你多少数据就知道了 业务端可以显示 1W+ 10W
上亿条数据 一页一页的去分页 可以重新考虑一下这个业务是否合理

[zoharSoul]

无需

[dyv9]

@sampeng 很多产品经理只知道要实现的功能点，但他们不具备设计能力，喜欢把不同用户角色的功能混在一个页面上做，导致一个查询包办很多种不同的细分功能，难以优化，就像这个返回说有几千页时明显在提示你这功能设计就有问题，没有对数据做限制，甚至用户啥条件都不输入也允许他们仅用隐含的状态之类的条件查所有数据，这是荒唐的软件设计。