MySQL百万数据深度分页优化思路分析

2023-05-1907:54:33数据库教程Comments1,013 views字数 1101阅读模式

业务场景

一般在项目开发中会有很多的统计数据需要进行上报分析,一般在分析过后会在后台展示出来给运营和产品进行分页查看最常见的一种就是根据日期进行筛选。这种统计数据随着时间的推移数据量会慢慢的变大,达到百万、千万条数据只是时间问题。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/40267.html

瓶颈再现

创建了一张user表,给create_time字段添加了索引。并在该表中添加了100w条数据。MySQL百万数据深度分页优化思路分析文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/40267.html

我们这里使用limit分页的方式查询下前5条数据和后5条数据在查询时间上有什么区别。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/40267.html

查询前10条基本上不消耗什么时间
MySQL百万数据深度分页优化思路分析文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/40267.html

我们从第50w+开始取数据的时候,查询耗时1秒。
MySQL百万数据深度分页优化思路分析文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/40267.html

SQL_NO_CACHE <br/>
这个关键词是为了不让SQL查询走缓存。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/40267.html

同样的SQL语句,不同的分页条件,两者的性能差距如此之大,那么随着数据量的增长,往后页的查询所耗时间按理会越来越大。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/40267.html

问题分析

回表

我们一般对于查询频率比较高的字段会建立索引。索引会提高我们的查询效率。我们上面的语句使用了SELECT * FROM user,但是我们并不是所有的字段都建立了索引。当从索引文件中查询到符合条件的数据后,还需要从数据文件中查询到没有建立索引的字段。那么这个过程称之为回表文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/40267.html

覆盖索引

如果查询的字段正好创建了索引了,比如 SELECT create_time FROM user,我们查询的字段是我们创建的索引,那么这个时候就不需要再去数据文件里面查询,也就不需要回表。这种情况我们称之为覆盖索引文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/40267.html

IO

回表操作通常是IO操作,因为需要根据索引查找到数据行后,再根据数据行的主键或唯一索引去聚簇索引中查找具体的数据行。聚簇索引一般是存储在磁盘上的数据文件,因此在执行回表操作时需要从磁盘读取数据,而磁盘IO是相对较慢的操作。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/40267.html

LIMTI 2000,10 ?

你有木有想过LIMIT 2000,10会不会扫描1-2000行,你之前有没有跟我一样,觉得数据是直接从2000行开始取的,前面的根本没扫描或者不回表。其实这样的写法,一个完整的流程是查询数据,如果不能覆盖索引,那么也是要回表查询数据的。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/40267.html

现在你知道为什么越到后面查询越慢了吧!文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/40267.html

问题总结

我们现在知道了LIMIT 遇到后面查询的性能越差,性能差的原因是因为要回表,既然已经找到了问题那么我们只需要减少回表的次数就可以提升查询性能了。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/40267.html

解决方案

既然覆盖索引可以防止数据回表,那么我们可以先查出来主键id(主键索引),然后将查出来的数据作为临时表然后 JOIN 原表就可以了,这样只需要对查询出来的5条结果进行数据回表,大幅减少了IO操作。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/40267.html

优化前后性能对比

我们看下执行效果:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/40267.html

  • 优化前:1.4s
    MySQL百万数据深度分页优化思路分析
  • 优化后:0.2s
    MySQL百万数据深度分页优化思路分析

查询耗时性能大幅提升。这样如果分页数据很大的话,也不会像普通的limit查询那样慢。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/40267.html

  • 本站内容整理自互联网,仅提供信息存储空间服务,以方便学习之用。如对文章、图片、字体等版权有疑问,请在下方留言,管理员看到后,将第一时间进行处理。
  • 转载请务必保留本文链接:https://www.cainiaoxueyuan.com/sjk/40267.html

Comment

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定