单表 800w 条,myisam 引擎。 需要根据一个值为时间戳的字段进行排序,时间戳有重复值,普通索引。 刚开始是采用的:select 字段 from 表名 order by 时间戳 desc,id asc limit 0,10; 但是特别慢,前几页还好,越到后面越慢。 百度了几个方案试了都感觉没什么特别大的变化。
1
gz911122 2020-04-12 20:59:54 +08:00
假设时间戳字段叫 ctime,
那么直接 ctime > ? limit ? 就行 |
2
jugelizi 2020-04-12 20:59:56 +08:00
不要分页 page 传最后最大 ID
|
3
odirus 2020-04-12 21:00:54 +08:00
有主键吧?
"select * from t limit 10000, 10" 变为 "select * from t where pk > ${上一页的最大值} limit 10" 这种方案试过没有呢? |
4
gz911122 2020-04-12 21:01:31 +08:00
@gz911122 order by ctime,id where ctime > 上次查询最后一条的 ctime id > 最后一条的 id limit 分页大小
|
5
odirus 2020-04-12 21:43:20 +08:00 1
如果使用最后一条记录 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 ),特别注意那个等号 |
6
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+树中的搜索 |
7
hooopo 2020-04-12 22:14:25 +08:00
现在还有人用 myisam ?
|
9
eq06 2020-04-12 22:21:37 +08:00
另建分页表,将分页和时间戳关联起来
|
10
iffi 2020-04-12 22:24:29 +08:00 1
|
11
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 去主键索引拿到所有列的值?
|
14
bigbigroll 2020-04-13 08:14:08 +08:00 via Android
@hooopo 可能数据库做了读写分离?
|
15
ddup 2020-04-13 08:48:41 +08:00 via Android 1
用分区表 根据时间范围建立分区表试试
|
16
atonku 2020-04-13 08:50:09 +08:00
全查出来,代码分
|
17
ohao 2020-04-13 08:57:50 +08:00
|
19
hyd8323268 OP |
20
pumily 2020-04-13 09:35:28 +08:00
我前几天刚遇到过,但是数据量还没楼主的那么大,但是感觉可以提供一个参考思路。
https://blog.csdn.net/qq_41348754/article/details/105422383 |
22
hyd8323268 OP @hosaos 我用 select id from 表名 where id < [上一页最小 id] order by [时间] desc,id asc limit 10; 这样查的话相反了,越到后面页越快,第一页大概需要 2s 左右,还在想有没有更好的办法呢。
|
23
hyd8323268 OP @jugelizi 如果要支持直接跳转到某页的话,还得保证 id 连续性,才能计算准确起始 id
|
24
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) |
25
hyd8323268 OP @cloudzhou 没走索引
|
26
cloudzhou 2020-04-13 10:15:57 +08:00
@hyd8323268 那肯定要走索引啊
|
27
b821025551b 2020-04-13 10:21:40 +08:00
|
28
c4pt0r 2020-04-13 10:24:45 +08:00
其实用 tidb 会好一些...
|
29
encro 2020-04-13 10:31:43 +08:00
基本是无解的:
1,count 本来就慢,无解; 2,采用>,<限制查询范围,如果需要精准就无解; 3,采用按时间分区表,也许有用,试试看,估计效果不会很明显,写入性能下降; 所以如果能业务限制采用 2,如果机器资源没有限制采用 3,如果需要实时 count 则需要采用缓存小范围 count |
31
Jooooooooo 2020-04-13 10:38:28 +08:00
带条件的大分页是无解问题
只能妥协 有种办法是用 id 作为游标去翻页, 比如现在这一页最大 id 是 A, 那么下一页就是 id>A |
32
barbery 2020-04-13 10:41:16 +08:00
改成走游标就行啦
|
33
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; 的效率还是能接受的 |
34
dizun 2020-04-13 10:47:20 +08:00 via Android
redis 吧。然后按照楼上说的用程序分。这种级别的用缓存我认为是最优。另外楼上也提到了 io 问题,注意备份数据库。
|
35
hyd8323268 OP @brader 执行第一条 sql 时慢,myisam 是自动记录总条数的。下一页和跳转指定页都要有的。如果不加时间戳排序的话是走索引的,所以快,但是时间戳是有重复的所以走不了索引了。我目前只能考虑用 id 游标来解决,应用层稍微麻烦点。
|
36
hyd8323268 OP @odirus 这种方法的话前几页会查询比较慢,第一页大概需要 2s,页码越大越快。总体来说还可以接受吧。而且,已经根据时间戳排序了,where 中就不需要再加时间戳了吧我觉得。跳转指定页也是个问题 .... 谢谢了
|
37
chq3272991 2020-04-13 11:26:40 +08:00
可以尝试下,底层基于日期分区,将大表分区为多个小分区,按日期查询的时候尽量能定位到其中一个分区
|
39
iffi 2020-04-13 11:35:19 +08:00
@hyd8323268 排序也可以走索引,你需要排序的字段要用到索引覆盖才行。explain 一下,看看 Extra 有没有用到 Using temporary 、Using filesort,最好用到 Using Index
|
41
iffi 2020-04-13 11:46:27 +08:00
@#27 楼 确实,在 MyISAM 下,优化不明显。看看能不能在业务层面优化,优化思路无非就是不查、少查、走索引、少取
|
42
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 的条件字段建立复合索引,避免回表,就最大化利用索引了。 |
43
hyd8323268 OP @lasuar 重点是排序,我知道这些的
|
44
brader 2020-04-13 15:50:23 +08:00
@hyd8323268 如果你需求一定要时间排序的话,我觉得你可以试试,给时间戳建立索引,然后使用微秒级时间戳,看下这样能不能避免时间戳太多重复的情况?这个就要看你业务了
|
45
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)
感觉还是这个靠谱 |
46
xiaochun41 2020-04-13 16:26:44 +08:00
单从技术上讲,可能没有太好的解决方案。
根据我个人的经验看,可以从业务的角度做一些优化或者妥协。 比如:真的需要一页一页翻么? |
47
weiqk 2020-04-13 17:34:03 +08:00 via Android
不要脱离业务谈技术,结合实际业务才有解决办法,是什么类型的数据?日志还是财务数据
|
48
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 |
49
hyd8323268 OP @weiqk 日志类的,访问记录,也就在后台看看,目前没有删除功能。
|
50
weiqk 2020-04-14 05:04:38 +08:00 via Android
@hyd8323268 上 es
|
52
qyvlik 2020-04-14 09:58:53 +08:00 1
先为表建好时间戳的索引,时间戳字段是否唯一,关系不大。只要不是说 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 表上建立索引。 |
53
neverxian 2020-04-14 11:10:20 +08:00
看一看
|
54
hosaos 2020-04-15 09:18:58 +08:00
@gz911122 id 带上就没问题了 但是这种时候 带上时间条件是多余的把 带 id 必须按 id 排序 否则按时间排序 id 不连续 没有意义
|
56
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 并且并行的请求,后台还是通过游标的方式处理 但是数据重复的问题,需要调用方 去重 |