关于mysql:MySQL百万数据深度分页优化思路分析

业务场景

个别在我的项目开发中会有很多的统计数据须要进行上报剖析,个别在剖析过后会在后盾展现进去给经营和产品进行分页查看最常见的一种就是依据日期进行筛选。这种统计数据随着工夫的推移数据量会缓缓的变大,达到百万、千万条数据只是工夫问题。

瓶颈再现

创立了一张user表,给create_time字段增加了索引。并在该表中增加了100w条数据。

咱们这里应用limit分页的形式查问下前5条数据和后5条数据在查问工夫上有什么区别。

查问前10条基本上不耗费什么工夫

咱们从第50w+开始取数据的时候,查问耗时1秒。

SQL_NO_CACHE <br/>
这个关键词是为了不让SQL查问走缓存。

同样的SQL语句,不同的分页条件,两者的性能差距如此之大,那么随着数据量的增长,往后页的查问所耗时间按理睬越来越大。

问题剖析

回表

咱们个别对于查问频率比拟高的字段会建设索引。索引会进步咱们的查问效率。咱们下面的语句应用了SELECT * FROM user,然而咱们并不是所有的字段都建设了索引。当从索引文件中查问到符合条件的数据后,还须要从数据文件中查问到没有建设索引的字段。那么这个过程称之为回表

笼罩索引

如果查问的字段正好创立了索引了,比方 SELECT create_time FROM user,咱们查问的字段是咱们创立的索引,那么这个时候就不须要再去数据文件外面查问,也就不须要回表。这种状况咱们称之为笼罩索引

IO

回表操作通常是IO操作,因为须要依据索引查找到数据行后,再依据数据行的主键或惟一索引去聚簇索引中查找具体的数据行。聚簇索引个别是存储在磁盘上的数据文件,因而在执行回表操作时须要从磁盘读取数据,而磁盘IO是绝对较慢的操作。

LIMTI 2000,10 ?

你有木有想过LIMIT 2000,10会不会扫描1-2000行,你之前有没有跟我一样,感觉数据是间接从2000行开始取的,后面的基本没扫描或者不回表。其实这样的写法,一个残缺的流程是查问数据,如果不能笼罩索引,那么也是要回表查问数据的。

当初你晓得为什么越到前面查问越慢了吧!

问题总结

咱们当初晓得了LIMIT 遇到前面查问的性能越差,性能差的起因是因为要回表,既然曾经找到了问题那么咱们只须要缩小回表的次数就能够晋升查问性能了。

解决方案

既然笼罩索引能够避免数据回表,那么咱们能够先查进去主键id(主键索引),而后将查出来的数据作为长期表而后 JOIN 原表就能够了,这样只须要对查问进去的5条后果进行数据回表,大幅缩小了IO操作。

优化前后性能比照

咱们看下执行成果:

  • 优化前:1.4s
  • 优化后:0.2s

查问耗时性能大幅晋升。这样如果分页数据很大的话,也不会像一般的limit查问那样慢。

【腾讯云】轻量 2核2G4M,首年65元

阿里云限时活动-云数据库 RDS MySQL  1核2G配置 1.88/月 速抢

本文由乐趣区整理发布,转载请注明出处,谢谢。

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据