limit mysql 取最后_分页场景（limit,offset）特别慢，有什么办法可以优化？
aries910 · 2021-06-07 14:19:21 +08:00 · 3009 次点击这是一个创建于 1299 天前的主题，其中的信息可能已经有所发展或是发生改变。
目前的场景就是
单表大概 20 万左右的数据，需要分页，排序（浏览量、新增时间，点赞等等）
laravel 生成的 sql
select id, logo, title, supports, collections, created_at, views, user_id, summary from table where status > 0 order by supports desc, created_at desc limit 20 offset 164920;
因为 offset 就执行的很慢 求大牛给点解决方案加速下，最好就是通过修改 sql 的方式
PS:机器是单核小主机。。。
 |
1
loophole12 2021-06-07 14:24:34 +08:00 via Android  1
可以先把拉取的 id 都取出来，再根据 id 用 in 拉取整行数据
|
 |
2
sheeta 2021-06-07 14:39:24 +08:00 3
取上次分页最后的结果 id, > id limit 20 。缺点，不能跳到指定的页。
|
 |
3
ksedz 2021-06-07 14:48:01 +08:00
加索引试试
|
 |
4
2kCS5c0b0ITXE5k2 2021-06-07 14:48:53 +08:00
Select * From table_name Where id in (Select id From table_name where status > 0) order by supports desc, created_at desc limit 20 offset 164920;
|
|
5
2kCS5c0b0ITXE5k2 2021-06-07 14:49:27 +08:00
不过 20 万就干趴下 看看慢日志
|
 |
8
aries910 OP @loophole12 试过了，没啥用
|
|
9
aries910 OP @ksedz 上面的 status 和 supports 都加了单独的索引，没啥用给，我看网上有人说 mysql 查询的时候，where 和 order 只会用一个索引，所以就算都有索引也没用，我去尝试了也确实没用（我不太考虑复合索引，毕竟小机器，怕后面数据更大了更新索引占资源）
|
|
10
2kCS5c0b0ITXE5k2 2021-06-07 15:15:00 +08:00
@aries910 SELECT * FROM table INNER JOIN( SELECT id FROM table status > 0 order by supports desc, created_at desc LIMIT 10000,100 ) b USING(id) 试下? 我这边单表快 40 万, 也就 0.1 秒
|
 |
11
Jooooooooo 2021-06-07 15:29:20 +08:00
大数分页是无解问题
一个妥协的方案是用 id 当游标 |
|
14
2kCS5c0b0ITXE5k2 2021-06-07 15:52:20 +08:00
@aries910 我这边二次执行就快了.
|
|
15
2kCS5c0b0ITXE5k2 2021-06-07 15:52:45 +08:00
|
|
16
2kCS5c0b0ITXE5k2 2021-06-07 15:53:18 +08:00
这个也行. 因为没完整的表 只能这样了.
|
 |
18
cnoder 2021-06-07 16:29:01 +08:00
楼上方法叫 延迟关联
|
 |
19
notejava 2021-06-07 16:35:06 +08:00
思路拓宽一点，从查询流程上优化，例如提前然用户填筛选条件筛选掉部分数据，再做分页。
|
|
20
2kCS5c0b0ITXE5k2 2021-06-07 16:47:42 +08:00
@aries910 explain 看下索引
|
 |
21
lostvincent 2021-06-07 17:03:07 +08:00
相关知识 https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
#1 方法是可以的，你还是慢可能是哪里没写对 步骤 1: select id where {condition}; 步骤 2: select * where id in (步骤 1 查出来的); 最关键是步骤 1 只 select id，顺带 where 相关的字段的索引安排 |
|
23
2kCS5c0b0ITXE5k2 2021-06-07 17:35:23 +08:00
@aries910 我这边 500 万单表 都没问题. 1s 多.
|
|
24
2kCS5c0b0ITXE5k2 2021-06-07 17:36:06 +08:00
我半桶水解决不了. 等大牛把.
|
 |
25
JasonLaw 2021-06-07 17:41:19 +08:00
还是在附言上添加一下 expalin 的结果吧。没有足够的信息，所有人都是靠猜的。
|
 |
26
Amit 2021-06-07 17:42:59 +08:00
楼上先查出 id 再关联查询数据应该是最好的办法了,limit 写在子查询中只查 id 查的是索引没有回表,小主机应该也是没问题的啊,楼主要不要再检查下
|
 |
27
mikeguan 2021-06-07 17:56:44 +08:00 via Android
取消分页吧。
我们最终采用分页时最多读取 1 万条数据 数据量大了，分页真的扛不住 |
 |
28
napsterwu 2021-06-07 18:04:28 +08:00 via iPhone
因为如果有 20w 数据，offset 19w，也会遍历 19w 行。只能是用 id 去过滤
|
 |
29
zpfhbyx 2021-06-07 18:45:38 +08:00
#2 正解
|
 |
30
calpes 2021-06-07 19:20:41 +08:00
这楼里一群人干哈呢，1 楼就是完美解决方案了，查到后边 offset 大了慢是因为你表里有 text，先查 id 再取其他字段就 ok 了
|
|
32
2kCS5c0b0ITXE5k2 2021-06-07 19:59:45 +08:00
@calpes 所以我说我是半桶水啊 XD
|
 |
33
dawniii 2021-06-07 20:12:47 +08:00
楼上很多说先取 id 的，只取 id 的话，是不用 offset 条件了吗？慢的原因不就是 offset 的值太大了，问题不还是存在吗？
|
 |
34
ebingtel 2021-06-08 09:33:14 +08:00
@emeab 有的时候 不一定是 offset 慢，先看看把 orderby 去掉，是不是快了……如果是 orderby 的原因，相关字段加上索引……如果没走索引，再用 FORCE INDEX
|
|
36
aries910 OP @ebingtel 确实是的，上面有很多小伙伴提议先用 id 取区间，再 select 完整数据
这种方法时好时坏，我替换了 orderby 后也会变快 关键查资料的时候看到个说法 ： where status=1 orderby views desc 这种情况这种会用一个索引，就算 status 和 views 都索引了，也并没啥用 求确认 |
 |
37
xiaochong0302 2021-06-08 15:16:45 +08:00
只能看前 N 页就好了，简单粗暴
``` public function getPage() { $page = $this->request->getQuery('page', ['trim', 'int'], 1); return $page > 100 ? 100 : $page; } public function getLimit() { $limit = $this->request->getQuery('limit', ['trim', 'int'], 12); return $limit > 100 ? 100 : $limit; } ``` |
Select Language