Mysql 千万级数据在后台管理的展示问题

[Boywus]

现在有一张钱包明细表，项目运行了两个月，已经达到了一千万的级别了，现在碰到了一个问题：

后台管理要求显示：

1. 这个表的总条数
2. 对这个表进行分页， 然后要显示总页数

第二个要求应该是基于第一个要求的，所以就是取决于

select count(*) from wallet_trans_detail where ...

这个 count 操作现在耗时还能接受，但是已经有点慢了，如果再运行两个月，分页的加载将会特别慢。

而且最近有分表计划，按照 user_id 取模进行分表放进不同的钱包明细表。

这样子有好几个表的内容使得总览的时候分页处理更加麻烦了。

各位大哥有什么好方法？

[mringg]

重点是 where 后

[iWicky]

建一个索引表，只存全量 ID

[ywisax]

同 1L。where 都走了索引的话，查询起来应该不会很慢，就是插入比较蛋疼

[ft7138]

用 MySQL 做存储，引入 palo 等 OLAP 做查询分析。

[qinxi]

你可以按时间分表啊.

[lihongjie0209]

单独维护一个计数表很难吗？

计数表又不一定准确，每次更新主表的时候发送一个事件更新计数表

[kimchan]

@lihongjie0209 #6 如果 select count 后面的 where 条件多种多样的话, 维护计数表会不会反而更麻烦?

[love]

显示千万级别的表的精确总条数 99.9999 ％是个伪需求，大至就可以了。
分页同理，你真的要翻到后面 1 万页？

[lihongjie0209]

@kimchan 我说的是维护一个总数量的计数，如果是 where 的计数，性能太差的话就不要做了， 直接给个下一页按钮，如果没数据了就提示没数据就好了

[k9982874]

algolia elasticsearch 了解一下

[ChoateYao]

采用 count() 索引字段。

限制分页数最大 100 页，想查看大于 100 的分页数据用搜索条件来缩小范围。

如果非要分页 1000 万数据，那么可以采用
09 年雅虎分享的解决方案 https://www.slideshare.net/Eweaver/efficient-pagination-using-mysql

[Boywus]

@love @lihongjie0209 然而需求就是要求要有总条数，以及总页数。 管理员经常会翻到最后一页看一下这个用户的前面的钱是怎么进来的。 翻到最后一页本身就有 offset 的性能问题，现在勉强用 beween and 加速了下查询。

搜索的指标而且还有很多，所以维护 where 计数有点麻烦，至于维护总数放到 redis 里面是可以的。

但是现在分表后，聚合几个表的数据不知道怎么分页了。
难道是一个总表，然后几个分表，总表内仍然包含所有数据给管理员查询， 分表只给应用端查询？

[Vegetable]

count 一般不会慢,慢的是你这个索引建的好不好

后台管理和客户端共用一张表的话,随便改索引好像也不太合适.数据按照 userid 分表应该是一个面向业务的设计,mod 这样最多分 10 个表,如果你当下遇到了性能问题,10 个表也坚持不了多久吧.

感觉需求有些问题,这种情况下和产品运营沟通一下.要数据的实时性,就要牺牲性能.要性能就要牺牲部分数据的精确度.
总不能为了这个屁大点的需求付出太高的成本吧

[ben1024]

索引
缓存
ES
千万可以考虑分表

[lihongjie0209]

@Boywus 你提到 ‘这个用户’， 既然精确到用户级别，那么数据量应该不多吧？只有在用户被选中的情况下才提供详细的分页信息

[Vegetable]

@Boywus #12

你第一段描述的问题挺有代表性的,一个 xy 问题的变种.
设计产品的人想知道资金来源,想知道首冲日期,他不直接和你说这个需求,也不直接设计这个功能,而是要求你必须能直接翻到最后一页.然后自己去看.
我认为这真真是错误的设计.分表再合并起来分页更是有点滑稽,合并操作开销太大了,因为没办法排序.根本就没办法优雅分页.
我有点心疼你了兄弟

[iyaozhen]

看看 phpmyadmin 怎么做的吧，全表大小 MySQL 自己有统计，取下值就行。


分表和总览的需求就是相违背的

[cctvsun]

楼主不会是 钱迹 吧？

[laozhoubuluo]

如果不带 where 的情况下建议缓存总交易数解决，如果带简单 where 条件的话建议优化索引，一般都尚能接受。
如果条件很复杂或者量很大，建议给后台读操作单独建一个只读的 Slave，这样大查询卡一点不影响业务。
如果后台分析人员随便一查就是几十万条命中的话，建议砍掉带 where 情况下的总条数和分页。

看到楼上写说单用户交易记录很多，那不如直接把搜索建表，完了把查表结果序列化存到数据库或者 redis 里，对同一个搜索翻页直接取查询结果。

[laozhoubuluo]

如果用户需求是：1. 关心总交易量 2. 关心单个用户钱流向 3. 特定条件下的全局查询少
那肯定是直接缓存总交易数，完了 uid%1000 甚至 10000 分表最省事。

[kimchan]

@lihongjie0209 #9 因为我看原文中就说了带 where 😂😂. 以为你说的这个方案是已经考虑了这块

[love]

@Boywus 谁说翻到最后一页要用 offset，直接按照日期倒叙排序就行，有索引在没性能问题

[tedcon]

翻最后一页面 完全可以 oder by id asc， 针对常用场景优化是一个基本操作吧。
翻页操作用 where id > lastId limit pageSize 来做， 常规优化

[reus]

加个开关，想要显示时才计数，没必要为了不常用的操作，拖慢所有操作

也就是默认只有前后翻页，计算总条数当作独立的功能

[vance]

explain select XXX 有个 rows 字段，显示的是大致的总数

[lcy630409]

如果只有你说的哪两个需求 你可以直接获取最后一条数据的 id 除以分页数
如果你的数据可能删除，哪就做定时缓存，每 3/6/12/24 小时 获取一次总条数，缓存在另外的表的字段里

[lcy630409]

带有 where 的话，目前做好索引，用空间换速度，千万条数据 也很快的，前台基本感觉不出来，如果很慢的话 估计就是你的索引没做好

[Boywus]

@vance 那个似乎不支持 where 查询吧

[Boywus]

@lcy630409 where 获取数据很快，where 情况下的 count 也能接受。

由于历史原因，id 数有过断层，这个方案行不通。

需求就是在总览的时候要求能够显示总条数，现在已经条数异步了，只能给上下页，然后查看指定用户的时候再提供分页。

[kltt22]

存个修正参数就可以用 ID 数了

[zhihhh]

为啥才 1000w 就要分表了 - - 第二个分页的需求没看懂。如果一定要精确的 count 不大的情况下直接 count(*) 如果量大维护一个索引值 可以放 redis 也可以搞个表存。不需要精确的 可以使用执行计算的 rows。

[qianlifeng]

influxdb

[vjnjc]

有了动态 where 后，你们还怎么把 count 缓存下来啊？
建议常用的 where 组合建索引

[jaky666]

elassearch

[shangfabao]

才 1000 万,索引就能解决了吧

[cs8814336]

分页有 2 种,1 种是扶梯式, select * from xxx where id>xxx
2.第二种就是你这种精确分页了. select * from xxx limit xx,n

通常第二种大数量的时候性能会很差,带上分表可能会更复杂. 一般做法是对于 select * from xxx where xxx limit xxx,n
的查询, 进行多个分表同时查询 select * from xxx where xxx limit xxx,n, 对 m 个表查出来的最多 n*m 个数据进行人工排序,取前 n 个.