mysql 近千万级数据表，在分页时有什么好的方案吗。

单表 800w 条，myisam 引擎。需要根据一个值为时间戳的字段进行排序,时间戳有重复值，普通索引。刚开始是采用的：select 字段 from 表名 order by 时间戳 desc,id asc limit 0,10; 但是特别慢,前几页还好，越到后面越慢。百度了几个方案试了都感觉没什么特别大的变化。

字段

单表

表名

select

56 条回复 • 2021-06-17 20:25:35 +08:00

gz911122

2020-04-12 20:59:54 +08:00

假设时间戳字段叫 ctime,
那么直接 ctime > ? limit ? 就行

jugelizi

2020-04-12 20:59:56 +08:00

不要分页 page 传最后最大 ID

odirus

2020-04-12 21:00:54 +08:00

有主键吧？

"select * from t limit 10000, 10" 变为 "select * from t where pk > ${上一页的最大值} limit 10" 这种方案试过没有呢？

gz911122

2020-04-12 21:01:31 +08:00

@gz911122 order by ctime,id where ctime > 上次查询最后一条的 ctime id > 最后一条的 id limit 分页大小

odirus

2020-04-12 21:43:20 +08:00

如果使用最后一条记录 ID 来查询的话貌似要漏数据，假设数据如下
ctime, id
1, 8
1, 9
2, 10
2, 11
3, 12
3, 13

如果按照 order by ctime desc, id asc 的话查询结果是（ 3, 12 ）
后续页面按照 "order by ctime desc, id asc where ctime < ${最后一条记录的 ctime} and id < ${最后一条记录的 id}" 的话查询结果是（ 2, 10 ）

所以我建议查询方式是 order by ctime desc, id desc，第一页查询结果是（ 3, 13 ）
后续页面按照 "order by ctime desc, id desc where ctime <= ${最后一条记录的 ctime} and id < ${最后一条记录的 id}" ，查询结果是（ 3, 12 ），特别注意那个等号

Aresxue

2020-04-12 21:58:43 +08:00

查询语句可以带主键(趋势递增才可以), 比如原来的语句是 select * from table limit 1000,10(单页 10 条), 现在给改成
1.select * from table where id >= (select id from table 1imit 1000,1) limit 10
2.Select id from table limit 10000, 10;Select * from table where id in (123,345....);
3.select * from table INNER JOIN (select id from table limit 10000,1) using (id);
4.在代码里记录最后一条的 id, select * from table where id >= ? limit10;
简单来说就是只扫描主键不扫描行记录, 减少一次 B+树中的搜索

hooopo

2020-04-12 22:14:25 +08:00

现在还有人用 myisam ？

mornone

2020-04-12 22:19:35 +08:00

@Aresxue 这个方法是可行的，使用主键条件缩小查询范围

eq06

2020-04-12 22:21:37 +08:00

另建分页表，将分页和时间戳关联起来

iffi

2020-04-12 22:24:29 +08:00

大数据量下分页优化 - 延迟关联

select id,name from user limit 500000,10
优化后：
select id,name from user u inner join (select id from user limit 500000,10)as tmp
on tmp.id = u.id

littlewing

2020-04-12 22:37:42 +08:00

有一个疑问，InnoDB 下，select * from t order by idx_1 limit 100, 10 这种语句，idx_1 是索引，扫描前 100 行的时候会回表扫描主键索引拿到所有列吗？还是只是扫描 idx_1 这个索引的前 100 行，然后后面从 101 行开始取 10 行再分别回表用 id 去主键索引拿到所有列的值？

xiaoidea

2020-04-13 00:07:04 +08:00

@iffi 这是啥原理，mark 下明天试试

soseek

2020-04-13 02:08:07 +08:00 via Android

@xiaoidea 减少了数据体积了👀

bigbigroll

2020-04-13 08:14:08 +08:00 via Android

@hooopo 可能数据库做了读写分离？

ddup

2020-04-13 08:48:41 +08:00 via Android

用分区表根据时间范围建立分区表试试

atonku

2020-04-13 08:50:09 +08:00

全查出来，代码分

ohao

2020-04-13 08:57:50 +08:00

my.oschina.net/luanwu/blog/3138110
可以参考这个

上次这么多数据还是弄过期域名的时候亿级的
min max 的方式来分页效率还可以的

luckylvke

2020-04-13 08:58:34 +08:00

@xiaoidea 只查主键索引减少回表，缩小范围后，内连出来（我猜的。。。

hyd8323268

2020-04-13 09:28:49 +08:00

@iffi 最重要的问题是要排序，需要根据时间戳排序，并且时间戳是有重复的。
用你优化后的 sql 中的子查询的话是不用回表的，但是如果得加排序。
select id,name from user u inner join (select id from user order by 时间戳 desc,id asc limit 500000,10)as tmp
on tmp.id = u.id 。这样的话覆盖索引是不生效的，特别慢。

pumily

2020-04-13 09:35:28 +08:00

我前几天刚遇到过，但是数据量还没楼主的那么大，但是感觉可以提供一个参考思路。
https://blog.csdn.net/qq_41348754/article/details/105422383

hosaos

2020-04-13 09:40:10 +08:00

@gz911122 按时间这样是不对的时间重复的时候无法处理可以按 id 来

hyd8323268

2020-04-13 10:03:25 +08:00

@hosaos 我用 select id from 表名 where id < [上一页最小 id] order by [时间] desc,id asc limit 10; 这样查的话相反了，越到后面页越快，第一页大概需要 2s 左右，还在想有没有更好的办法呢。

hyd8323268

2020-04-13 10:08:50 +08:00

@jugelizi 如果要支持直接跳转到某页的话，还得保证 id 连续性，才能计算准确起始 id

cloudzhou

2020-04-13 10:09:02 +08:00

select 字段 from 表名 order by 时间戳 desc,id asc limit 0,10;
如果我没有猜错的话，走到了主键 id 的索引，建立 create index xxx on table (时间戳 desc , id asc)
然后 select 字段 from 表名 (force index xxx)

hyd8323268

2020-04-13 10:13:58 +08:00

@cloudzhou 没走索引

cloudzhou

2020-04-13 10:15:57 +08:00

@hyd8323268 那肯定要走索引啊

b821025551b

2020-04-13 10:21:40 +08:00

@xiaoidea #12
@soseek #13

offset 越大，主键索引访问越频繁，对磁盘 IO 占用越高，这种优化就是干掉频繁的主键索引；
不过 @iffi #10，LZ 所提的是 MyISAM，这种优化只在 InnoDB 下有效。

c4pt0r

2020-04-13 10:24:45 +08:00

其实用 tidb 会好一些...

encro

2020-04-13 10:31:43 +08:00

基本是无解的：
1，count 本来就慢，无解；
2，采用>,<限制查询范围，如果需要精准就无解；
3，采用按时间分区表，也许有用，试试看，估计效果不会很明显，写入性能下降；

所以如果能业务限制采用 2，如果机器资源没有限制采用 3，如果需要实时 count 则需要采用缓存小范围 count

cloudzhou

2020-04-13 10:33:14 +08:00

@c4pt0r 那是屠龙术，千万数据 mysql 还是可以的

Jooooooooo

2020-04-13 10:38:28 +08:00

带条件的大分页是无解问题

只能妥协

有种办法是用 id 作为游标去翻页, 比如现在这一页最大 id 是 A, 那么下一页就是 id>A

barbery

2020-04-13 10:41:16 +08:00

改成走游标就行啦

brader

2020-04-13 10:44:31 +08:00

请问你是执行 select 字段 from 表名 order by 时间戳 desc,id asc limit 0,10; 的时候慢，还是获取表的总行数的时候慢？可以提供你的具体分页需求吗？是只做下一页，还是需要做页码的？
就我所知，千万级，select 字段 from 表名 order by 时间戳 desc,id asc limit 0,10; 的效率还是能接受的

dizun

2020-04-13 10:47:20 +08:00 via Android

redis 吧。然后按照楼上说的用程序分。这种级别的用缓存我认为是最优。另外楼上也提到了 io 问题，注意备份数据库。

hyd8323268

2020-04-13 11:00:47 +08:00

@brader 执行第一条 sql 时慢，myisam 是自动记录总条数的。下一页和跳转指定页都要有的。如果不加时间戳排序的话是走索引的，所以快，但是时间戳是有重复的所以走不了索引了。我目前只能考虑用 id 游标来解决，应用层稍微麻烦点。

hyd8323268

2020-04-13 11:09:36 +08:00

@odirus 这种方法的话前几页会查询比较慢，第一页大概需要 2s，页码越大越快。总体来说还可以接受吧。而且，已经根据时间戳排序了，where 中就不需要再加时间戳了吧我觉得。跳转指定页也是个问题 .... 谢谢了

chq3272991

2020-04-13 11:26:40 +08:00

可以尝试下，底层基于日期分区，将大表分区为多个小分区，按日期查询的时候尽量能定位到其中一个分区

iffi

2020-04-13 11:32:38 +08:00

@xiaoidea 看一下 Clustered Indexes 和 Secondary Indexes 的区别就明白了

iffi

2020-04-13 11:35:19 +08:00

@hyd8323268 排序也可以走索引，你需要排序的字段要用到索引覆盖才行。explain 一下，看看 Extra 有没有用到 Using temporary 、Using filesort，最好用到 Using Index

gz911122

2020-04-13 11:46:09 +08:00

@hosaos 可以的,你仔细看一下,重复也无所谓的

iffi

2020-04-13 11:46:27 +08:00

@#27 楼确实，在 MyISAM 下，优化不明显。看看能不能在业务层面优化，优化思路无非就是不查、少查、走索引、少取

lasuar

2020-04-13 14:22:35 +08:00

通过子查询优化
select * from table where id>=(select id from table limit 10000,1) limit 100
另外大表慎用 select *，一般都查询指定字段，把这些经常查询的字段和 where 的条件字段建立复合索引，避免回表，就最大化利用索引了。

hyd8323268

2020-04-13 15:45:35 +08:00

@lasuar 重点是排序，我知道这些的

brader

2020-04-13 15:50:23 +08:00

@hyd8323268 如果你需求一定要时间排序的话，我觉得你可以试试，给时间戳建立索引，然后使用微秒级时间戳，看下这样能不能避免时间戳太多重复的情况？这个就要看你业务了

laoba

2020-04-13 16:09:01 +08:00

select * from articles where id between (select id from articles limit 170000,1) and (select id from articles limit 170500,1)
感觉还是这个靠谱

xiaochun41

2020-04-13 16:26:44 +08:00

单从技术上讲，可能没有太好的解决方案。
根据我个人的经验看，可以从业务的角度做一些优化或者妥协。
比如：真的需要一页一页翻么？

weiqk

2020-04-13 17:34:03 +08:00 via Android

不要脱离业务谈技术，结合实际业务才有解决办法，是什么类型的数据？日志还是财务数据

hosaos

2020-04-13 19:44:49 +08:00

@gz911122 假设数据如下，分页 pageSize 也是 1，不针对 id，只针对时间是查不出 id 为 2 的数据的，第二页的条件不是 time<2019-07-12 15:14:42 limit 1?
id time
1 2019-07-12 15:14:42
2 2019-07-12 15:14:42
3 2019-07-12 15:14:42

hyd8323268

2020-04-13 20:27:48 +08:00

@weiqk 日志类的，访问记录，也就在后台看看，目前没有删除功能。

weiqk

2020-04-14 05:04:38 +08:00 via Android

@hyd8323268 上 es

gz911122

2020-04-14 09:53:12 +08:00

@hosaos id 也要带上的,你看下我回复的#4 楼

qyvlik

2020-04-14 09:58:53 +08:00

先为表建好时间戳的索引，时间戳字段是否唯一，关系不大。只要不是说 800w 记录，时间戳的取值集合就百来个，这个时间戳就有建立索引的价值。

1. 摈弃传统的精确分页，直接走瀑布流加载方案
- 取第一页：select * from logs order by create_time desc limit 10
- 可以在前端将 min(create_time) 取出，作为第二第三页的查询条件
- select * from logs where create_time < pre_min_time order by create_time desc limit 10

2. 仍然想使用精确分页
1. 使用时间区间将要扫描的条数减少，例如一天的日志数量
2. 使用一些 sql 查询进行优化，例如 max, min 查找出对应的时间范围、id 范围

3. 使用其他的查询方案，例如 es 、mongo 、redis

ps: 如果一些索引效果不明显，考虑将 myisam 表数据导入新的 InnoDB 的表，在 InnoDB 表上建立索引。

neverxian

2020-04-14 11:10:20 +08:00

看一看

hosaos

2020-04-15 09:18:58 +08:00

@gz911122 id 带上就没问题了但是这种时候带上时间条件是多余的把带 id 必须按 id 排序否则按时间排序 id 不连续没有意义

gz911122

2020-04-15 10:12:31 +08:00

@hosaos 不多余,先时间排序再 id 排序, 有意义的. 是为了避免重复问题.

tmackan

2021-06-17 20:25:35 +08:00

比如要导出全部账单的话
1.传统分页的话，offset 可以算出来，这样可以精确分页，但是 offset 越大越慢，调用方第一次拿到 total 总数后，可以并发的请求后续分页数据
2.游标的方式的话，利用 select * from table where id < cursor order by time desc limit 1000
调用方通过串行的方式，每次用前一页的最小 ID 来作为下一页的 cursor
优点是可以保证数据不重复，缺点是无法并行请求，只能串行每次获取游标

折中的做法
调用方自己计算 cursor 并且并行的请求，后台还是通过游标的方式处理
但是数据重复的问题，需要调用方去重