由于用户记录太多(300万),导致分页查询变得很慢,如何优化语句或者索引?大家有什么优化方案吗?
补充内容:
谢谢大家的方案,我看的很受启发,但结合我的这个情况来说比较特殊,不太适用。具体情况如下:
1当用户打开参与记录页面时候,要显示最新参与的10个人的记录。(这10个人的记录并不是用户参与记录表之中最新的的那十条数据而是加WHERE条件筛选出来的十条数据。)
2最新的参与10个人的数据是从两个表中拿取得(用户信息表,用户参与记录表)。
3要保证用户能翻下一页数据,上一页数据。
现在查数据是连接两个表,WHERE两个表的条件,然后limit的数据。这样太慢了,所以想问下有什么优化方案?
由于用户记录太多(300万),导致分页查询变得很慢,如何优化语句或者索引?大家有什么优化方案吗?
补充内容:
谢谢大家的方案,我看的很受启发,但结合我的这个情况来说比较特殊,不太适用。具体情况如下:
1当用户打开参与记录页面时候,要显示最新参与的10个人的记录。(这10个人的记录并不是用户参与记录表之中最新的的那十条数据而是加WHERE条件筛选出来的十条数据。)
2最新的参与10个人的数据是从两个表中拿取得(用户信息表,用户参与记录表)。
3要保证用户能翻下一页数据,上一页数据。
现在查数据是连接两个表,WHERE两个表的条件,然后limit的数据。这样太慢了,所以想问下有什么优化方案?
1.不要在不加条件的情况下用limit,limit 1000,20会从0~10002扫描
2.记住上一页最后一个用户主键,分页用select * from user where uid>lastUid limit pagesize
3.数据一致性要求不高,考虑应用于数据库中间加一层缓存
EXPLAIN SELECT * FROM lagou where id <=100
EXPLAIN SELECT * FROM lagou LIMIT 100
注意type 一个是range,一个是all
总结:多么痛的领悟~
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
我个人认为你这个就是一个分页查询优化,只不过是两张表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为表的主键
如果id主键是自增且连续的(未出现删除记录)那么可以改用where between
假设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
请根据自己的查询条件定义where子句,同时不要反复count记录数量。
1- 记忆当前页的最后一条记录,下次查询的时候根据order by添加对应的where子句代替跳过N行
2- 数据量大的时候总共多少页在实际使用的时候已经失去意义,但是count的消耗却很大,确实需要的话首次查询时获得一次就好了。
同意楼上的意见
1.完善搜索及索引建立
2.设计数据表默认排序作为排序规则,降低查询排序耗时
3.在id为有序数字情况下,利用最后一次id作为下一次检索条件,极大降低返回数据集
4.减少count的次数和精度,超大数据可以缓存count值
5.更大型数据可根据分页规则、类型等对数据表进行拆分,降低单次查询数据量
1 你减少sql语句的函数使用
2 减少order by 和group by
3 对查询条件的字段添加索引
4 300w的记录应该操作记录,不是实时需要展示的数据,那么可以做数据缓存。
5 js使用$(obj).load("url #id") 进行ajax局部刷新翻页,这样可以避免你的资源重新加载
我知道的就这么多了
按我的理解你的情况是限制条件在用户参与记录表中,而用户信息表实际上只要按前一个表的id取数据就可以了。我估计你索引已经做了,剩下能有大的改观的就是数据分区和预排序,参与记录表按主要查询条件,用户信息表按id分区。然后参与记录表一定要预排序,如果到查询时再排序,再怎么样也快不起来。
增加where条件 主键>0 然后只取主键,这个是分页用,取内容用 where IN 直接取主键,比你查的快多了
把某个用户的信息缓存,分页查询就查用户参与记录表
两张表关联,而且显示最新参与的10个人的记录。这样关联的时候,查询的sql会现的复杂。所以建议通过两条sql+php的遍历实现。查询需要的个人记录一个sql,查询会员一个sql,然后遍历数组生成需要的数组。总之就是将复杂的sql拆分成简单的sql
建立合适的索引
查询先分页主键,然后通过主键结果查内容 曾经做过7kw的日志数据查询,分页很快的。
对数据实效性要求不高的话 可以用sphinx做查询
limit 基数比较大时使用 between , 取比较后面的数据时用 desc 反向查找 .
可以写个定时脚本,10分钟跑一次这样的,将取到的数据保存起来,用户打开页面时就从这个表里取。只是展示最新参与的话,来个几分钟的延迟应该是可以接受的
先仔细审查下你的SQL有没有优化余地;再考虑数据库调优,缓存、读写分离的办法
已抢15828个
抢已抢48833个
抢已抢3934个
抢已抢1636个
抢已抢1960个
抢已抢3148个
抢