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

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

字段

单表

表名

select

56 replies • 2021-06-17 20:25:35 +08:00

gz911122

Apr 12, 2020

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

jugelizi

Apr 12, 2020

不要分页 page 传最后最大 ID

odirus

Apr 12, 2020

有主键吧？

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

gz911122

Apr 12, 2020

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

odirus

Apr 12, 2020

如果使用最后一条记录 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

Apr 12, 2020

查询语句可以带主键(趋势递增才可以), 比如原来的语句是 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

Apr 12, 2020

现在还有人用 myisam ？

mornone

Apr 12, 2020

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

eq06

Apr 12, 2020

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

iffi

Apr 12, 2020

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

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

Apr 12, 2020

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

xiaoidea

Apr 13, 2020

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

soseek

Apr 13, 2020 via Android

@xiaoidea 减少了数据体积了👀

bigbigroll

Apr 13, 2020 via Android

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

ddup

Apr 13, 2020 via Android

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

atonku

Apr 13, 2020

全查出来，代码分

ohao

Apr 13, 2020

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

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

luckylvke

Apr 13, 2020

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

hyd8323268

Apr 13, 2020

@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

Apr 13, 2020

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

hosaos

Apr 13, 2020

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

hyd8323268

Apr 13, 2020

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

hyd8323268

Apr 13, 2020

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

cloudzhou

Apr 13, 2020

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

Apr 13, 2020

@cloudzhou 没走索引

cloudzhou

Apr 13, 2020

@hyd8323268 那肯定要走索引啊

b821025551b

Apr 13, 2020

@xiaoidea #12
@soseek #13

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

c4pt0r

Apr 13, 2020

其实用 tidb 会好一些...

encro

Apr 13, 2020

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

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

cloudzhou

Apr 13, 2020

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

Jooooooooo

Apr 13, 2020

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

只能妥协

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

barbery

Apr 13, 2020

改成走游标就行啦

brader

Apr 13, 2020

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

dizun

Apr 13, 2020 via Android

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

hyd8323268

Apr 13, 2020

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

hyd8323268

Apr 13, 2020

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

chq3272991

Apr 13, 2020

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

iffi

Apr 13, 2020

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

iffi

Apr 13, 2020

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

gz911122

Apr 13, 2020

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

iffi

Apr 13, 2020

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

freemoon

Apr 13, 2020

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

hyd8323268

Apr 13, 2020

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

brader

Apr 13, 2020

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

laoba

Apr 13, 2020

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

xiaochun41

Apr 13, 2020

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

UFc8704I4Bv63gy2

Apr 13, 2020 via Android

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

hosaos

Apr 13, 2020

@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

Apr 13, 2020

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

UFc8704I4Bv63gy2

Apr 14, 2020 via Android

@hyd8323268 上 es

gz911122

Apr 14, 2020

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

qyvlik

Apr 14, 2020

先为表建好时间戳的索引，时间戳字段是否唯一，关系不大。只要不是说 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

Apr 14, 2020

看一看

hosaos

Apr 15, 2020

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

gz911122

Apr 15, 2020

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

tmackan

Jun 17, 2021

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

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