MariaDB数据库优化,实现百万级数据环境快速翻页

[文章作者:磨延城 转载请注明原文出处: https://mo2g.com/view/82/ ]

在通常情况下,MariaDB(mysql的一个衍生版本)数据库是使用offset指定数据偏的移量与limit获取一定数量的数据,来实现翻页的功能.这个方法很普遍也很使用,因为mysql最初就是定位为小型数据库,所以在数据量不大的数据表中,通过limit与offset的方式来实现翻页功能,响应时间是可以接受的.

在通常情况下,MariaDB(mysql的一个衍生版本)数据库是使用offset指定数据偏的移量与limit获取一定数量的数据,来实现翻页的功能。这个方法很普遍也很实用,因为mysql最初就是定位为小型数据库,所以在数据量不大的数据表中,通过limit与offset的方式来实现翻页功能,响应时间是可以接受的。

如果每页有10条数据,经过测试,给innodb_fuffer_pool_size分配2G的缓存,在innodb单表数据量达到100W的时候,使用limit $intLeng offset $intStart(等价于limit $intStart,$intLeng) 的查询方式翻页,第一次执行查询需要16~29秒,之后是1.6秒。这个测试结果,并不是说明MariaDB的瓶颈,只是单方面的给出了一个数据,证实了偏移量越大,翻页的时间将会让人无法忍受。

使用limit $intLeng offset $intStart的翻页方式,偏移量与执行时间成正比,如果我们要查看第90000页的10条数据,MariaDB会先获取900010条数据,然后从中筛选出我们需要的10条数据,其余的900000条数据就作废了。对于单表10W以上的数据,这样的翻页查询方式,查询效率已经能感觉得出来不顺畅,更重要的是加重了服务器的压力。

为了在100W数据环境下实现快速翻页,我查阅了《高性能MysQl第三版》一书,再参考前辈们的思路,最后选用了一个可以接受的优化方法:

1)根据要查看的页数计算出起始ID

2)通过计算出来的ID为起点获取数据

使用上述方法,在100W数据中翻页,第一次的相应时间为0.76ms,之后都是0.00s,即使转到500W的数据表中进行翻页,速度依然飞快。这种方式之所以能快速的翻页,是牺牲了数据的精确性——在数据不连续的情况下,会出现同一条数据在两个页面中显示。

连续的数据:

12345678910
11121314151617181920
21222324252627282930

不连续的数据:

12345678
10
1112131415161718
20
2122232425262728
30

正常情况下,在不连续的数据中进行翻页,应该得到如下数据:

第一页20212223242526272830
第二页8101112131415161718
第三页1234567


为了高性能牺牲了数据的精确性,得到的是如下数据:

第一页20212223242526272830
第二页10111213141516171820
第三页1234567810

两份数据对比一下,其实差别不是很大,只是20跟10出现了两次,8偏移到了第3页,如果你能容忍这点小瑕疵,那么这种翻页方式在大数据环境中带来的性能提升是非常happy的。

最后,相信你还会考虑做一个优化:在翻页前判断总数据量,10W以下使用偏移的方式来翻页,10W以上的数据改用优化后的查询方式。

    评论:

    1. 暂无评论...
    2. 我来说两句:

        切换  

      磨途歌检测发现,您当前使用的浏览器版本过低,要想使用画板模式,请先更新浏览器

        切换  

      磨途歌随机验证码