数据量太大，分页查询变慢，有什么优化查询的方法吗？

Question

由于用户记录太多（300万），导致分页查询变得很慢，如何优化语句或者索引？大家有什么优化方案吗？

补充内容：
谢谢大家的方案，我看的很受启发，但结合我的这个情况来说比较特殊，不太适用。具体情况如下：
1当用户打开参与记录页面时候，要显示最新参与的10个人的记录。（这10个人的记录并不是用户参与记录表之中最新的的那十条数据而是加WHERE条件筛选出来的十条数据。）
2最新的参与10个人的数据是从两个表中拿取得（用户信息表，用户参与记录表）。
3要保证用户能翻下一页数据，上一页数据。
现在查数据是连接两个表，WHERE两个表的条件，然后limit的数据。这样太慢了，所以想问下有什么优化方案？

Answer 1

1.不要在不加条件的情况下用limit，limit 1000,20会从0~10002扫描
2.记住上一页最后一个用户主键，分页用select * from user where uid>lastUid limit pagesize
3.数据一致性要求不高，考虑应用于数据库中间加一层缓存

Answer 2

EXPLAIN SELECT * FROM lagou where id <=100

EXPLAIN SELECT * FROM lagou LIMIT 100

注意type 一个是range,一个是all

总结：多么痛的领悟~

Answer 3

我个人认为你这个就是一个分页查询优化，只不过是两张表join之后分页

典型的分页优化是：

普通写法：

select * from buyer where sellerid=100 limit 100000,20

普通limit M, N 的翻页写法，在越往后翻页的过程中速度越慢，原因mysql会读取表中前M+N条数据，M越大，性能就越差

优化写法：

select t1.* from buyer t1, (select id from buyer where sellerid=100 limit 100000,20 ) t2 where t1.id=t2.id

需要在t表中的sellerid字段中创建索引，id为表的主键

Answer 4

MySQL LIMIT分页优化(其中id是posts表自增主键):
SELECT * FROM posts ORDER BY id DESC LIMIT 20 OFFSET 10000
扫描10020行,跳过前面的10000行,返回最后的20行,速度慢.
一个更好的设计是将具体的页数换成"下一页"按钮.
假设每页显示20条记录,那么我们每次查询的时候都是LIMIT返回21条记录并只显示20条.
如果第21条存在,那么我们就显示"下一页"或者"AJAX加载更多"按钮.

使用"上一页"和"下一页"进行分页:
上一页(id:60~41)
当前页(id:40~21)
下一页(id:20~01)

上一页(新文章20篇):
//page.php?id=40 正数表示上一页新文章,这里的40表示当前页最上面的文章的ID.
SELECT * FROM posts WHERE id > 40 ORDER BY id ASC LIMIT 20;
这里得到的是升序ID序列,PHP使用array_reverse反转数组实现降序输出即可.

下一页(旧文章20篇):
//page.php?id=-21 负数表示下一页旧文章,这里的21表示当前页最下面的文章的ID.
SELECT * FROM posts WHERE id < 21 ORDER BY id DESC LIMIT 20;
觉得负数不好看的话,可以额外加一个参数,比如 page.php?id=21&next

Answer 5

两张表关联，而且显示最新参与的10个人的记录。这样关联的时候，查询的sql会现的复杂。所以建议通过两条sql+php的遍历实现。查询需要的个人记录一个sql，查询会员一个sql，然后遍历数组生成需要的数组。总之就是将复杂的sql拆分成简单的sql

Answer 6

如果id主键是自增且连续的（未出现删除记录）那么可以改用where between

Answer 7

假设user_id是自增主键

SELECT * FROM user
WHERE user_id >= (
SELECT user_id FROM user
LIMIT pageIndex, 1
)
LIMIT pageSize;

参考：https://mp.weixin.qq.com/s?__biz=MjM5NzMyMjAwMA==&mid=2651477279&idx=1&sn=4a799a4aa2f8563d482973e262e906b8&scene=1&srcid=0629Qe4RKqmpBhwOUzFJeK1B&key=77421cf58af4a65302d0d5987b2f5d1610469f10ba9f2ac129747c2578d1a68463e33f8c7f388537de80a7bc38d00c05&ascene=0&uin=ODYxODM1MDE4&devicetype=iMac+MacBookAir6%2C2+OSX+OSX+10.9.5+build(13F34)&version=11020012&pass_ticket=aZDru5jCHRXbpBvmRG0m%2BkUwb4sJZ2hIfzTt6LDC1xnW2ZMj0mgDjwuyZgisQJxA

Answer 8

请根据自己的查询条件定义where子句，同时不要反复count记录数量。
1- 记忆当前页的最后一条记录，下次查询的时候根据order by添加对应的where子句代替跳过N行
2- 数据量大的时候总共多少页在实际使用的时候已经失去意义，但是count的消耗却很大，确实需要的话首次查询时获得一次就好了。

Answer 9

同意楼上的意见

1.完善搜索及索引建立
2.设计数据表默认排序作为排序规则，降低查询排序耗时
3.在id为有序数字情况下，利用最后一次id作为下一次检索条件，极大降低返回数据集
4.减少count的次数和精度，超大数据可以缓存count值
5.更大型数据可根据分页规则、类型等对数据表进行拆分，降低单次查询数据量

Answer 10

1 你减少sql语句的函数使用
2 减少order by 和group by
3 对查询条件的字段添加索引
4 300w的记录应该操作记录，不是实时需要展示的数据，那么可以做数据缓存。
5 js使用$(obj).load("url #id") 进行ajax局部刷新翻页，这样可以避免你的资源重新加载
我知道的就这么多了

Answer 11

按我的理解你的情况是限制条件在用户参与记录表中，而用户信息表实际上只要按前一个表的id取数据就可以了。我估计你索引已经做了，剩下能有大的改观的就是数据分区和预排序，参与记录表按主要查询条件，用户信息表按id分区。然后参与记录表一定要预排序，如果到查询时再排序，再怎么样也快不起来。

Answer 12

增加where条件 主键>0 然后只取主键，这个是分页用，取内容用 where IN 直接取主键，比你查的快多了

Answer 13

把某个用户的信息缓存，分页查询就查用户参与记录表

Answer 14

建立合适的索引
查询先分页主键，然后通过主键结果查内容 曾经做过7kw的日志数据查询，分页很快的。

Answer 15

对数据实效性要求不高的话 可以用sphinx做查询

Answer 16

limit 基数比较大时使用 between , 取比较后面的数据时用 desc 反向查找 .

Answer 17

可以写个定时脚本，10分钟跑一次这样的，将取到的数据保存起来，用户打开页面时就从这个表里取。只是展示最新参与的话，来个几分钟的延迟应该是可以接受的

Answer 18

先仔细审查下你的SQL有没有优化余地；再考虑数据库调优，缓存、读写分离的办法

Answer 19

https://www.percona.com/pdf-c...

Answer 20

翻页

(select id,title from article_table where id < 100000 order by id desc limit 1) union all (select id,title from article_table where id > 100000 order by id asc limit 1)