mysql百万级数据分页

技术 Jan 15, 2020

随着公司越来越大,业务量越来越多。

公司的数据量当然也是随之剧增。mysql作为一款社区数据库并非商业数据库。想要用它做几百万的数据分页。光靠limit这种东西是不靠谱的。当然不是诋毁mysql,mysql作为开源插拔式存储引擎数据库,已经是可以满足绝大部分的应用场景需求。使用mysql管理1000tb也不是问题。但是使用方式却是一个问题。

limit接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。

limit在偏移量小于10w时性能还勉强可以接受,但随着偏移量越来越大,性能急剧下降。

公司单表账务数据已经到达230w ,做分页limt  来查询最后一页的数据,怕是没有个20秒是查询不出来的。当然具体的时间也要根据是否有索引,字段数量,数据内容而定,查询条件而定。

为了解决分页效率问题,我采用方案是:

select id from table limit 100000,20 (带上where条件)

由于主键id原本就是主键索引,所以limit的速度效率很高。并把条件字段加入复合索引,效率才会有质量的提升 。

但如果有排序,性能也会极具下降,目测30w数据排序id或时间需要1-2秒时间。

最好的方式是直接放弃 limit 的偏移量 使用where id > xxx 来更快速的定位目标数据id位置 之后排序 再 limit 20 条数据即可 这样的效率百万级基本可以支撑。

先查询出需要查询的id,之后执行不带条件的in idList查询

select 字段 from table where id in (idList) (不带条件,有排序还是排序,但排序没关系,因为数据已经很少,就算是外部排序也很快)

查询列表时只需要 where in id 即可,其他的条件在 select id 的时候已经加上,列表数据查询时就不需要进行任何条件,但是排序还是要加的 。

这样就可以极快的进行分页查询,并且如果列表字段不多,可以做覆盖索引,查询效率更上一层楼。但一般列表字段都比较多10几个,20几个都很正常,这些字段都加索引是不可取的,那么索引体积太大,新增数据,修改数据时效率将会因为去维护索引而降低,可能会反而降低表的性能。所以做查询也需要考虑字段个数,排序规则,条件个数,索引类型,哪些字段做索引来做权衡。

表拼接这样的操作在大数据量下基本不考虑使用。最好做冗余字段。

查询sql 通过 explain 查看执行过程,是否走了索引,索引类型是什么。是否进行了回表,扫描数据行数等等重要信息,只要掌握好了这些,你的数据库性能才会有质量上的提升。

经过测试,这样的分页效率 100w 每页20条数据 基本可以在1s内请求下来数据,基本为600ms左右的请求时间。

如果单表数据量已经过500w,已经可以考虑进行水平分表。

对于业务逻辑来讲,为了增加单库性能,可以考虑 读写分离,主写,从读,多从等方式。

根据业务对库进行垂直拆分, 分离热数据,冷数据进行分配服务器资源。

tanzhuo

咸鱼一只