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

业务场景

一般在项目开发中会有很多的统计数据需要进行上报分析,一般在分析过后会在后台展示出来给运营和产品进行分页查看最常见的一种就是根据日期进行筛选。这种统计数据随着时间的推移数据量会慢慢的变大,达到百万、千万条数据只是时间问题。

瓶颈再现

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

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

我们这里使用limit分页的方式查询下前5条数据和后5条数据在查询时间上有什么区别。

查询前10条基本上不消耗什么时间
MySQL百万数据深度分页优化思路分析

我们从第50w+开始取数据的时候,查询耗时1秒。
MySQL百万数据深度分页优化思路分析

SQL_NO_CACHE

这个关键词是为了不让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
    MySQL百万数据深度分页优化思路分析

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

查询耗时性能大幅提升。这样如果分页数据很大的话,也不会像普通的limit查询那样慢。

原文链接:https://www.cnblogs.com/bingfengdada/p/17384958.html

本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:MySQL百万数据深度分页优化思路分析 - Python技术站

(0)
上一篇 2023年5月8日
下一篇 2023年5月9日

相关文章

  • 一文搞懂MySQL运行机制原理

    一文搞懂MySQL运行机制原理 MySQL是目前最流行的关系型数据库管理系统之一,其高性能和稳定性在各大互联网企业中得到了广泛的应用。但是,要想深入了解MySQL运行机制,需要掌握数据库的相关知识和底层原理。本文将从MySQL的体系结构、查询语句的处理过程、存储引擎等方面进行详细分析,让你全面了解MySQL的运行机制原理。 MySQL的体系结构 MySQL的…

    MySQL 2023年5月18日
    00
  • mysql 1449 : The user specified as a definer (‘root’@’%’) does not exist ,mysql 赋给用户权限 grant all privileges on

    mysql 1449 : The user specified as a definer (‘root’@’%’) does not exist 解决方法 遇到了 SQLException: access denied for  @’localhost’ (using password: no)   遇到了 SQLException: access deni…

    MySQL 2023年4月13日
    00
  • 一文搞懂MySQL持久化和回滚的原理

    一文搞懂MySQL持久化和回滚的原理 1. 持久化和回滚的概念 在数据库中,持久化指的是在数据被写入磁盘后仍能保持其状态的能力,即使数据库服务器意外关闭,也能够恢复之前的状态。而回滚指的是当操作失误或出错时,撤销已完成的操作,回到未完成操作前的状态。 2. MySQL持久化技术 MySQL使用了多种持久化技术来确保数据的可靠性,其中最常用的是日志和InnoD…

    MySQL 2023年5月19日
    00
  • navicat连接mysql出现2059错误的解决方法

    Navicat连接MySQL出现2059错误的解决方法 现象描述 在使用Navicat连接MySQL时,可能会出现2059错误,提示“Authentication plugin ‘caching_sha2_password’ cannot be loaded”. 原因分析 这个问题主要是因为MySQL服务器使用了caching_sha2_password插件…

    MySQL 2023年5月18日
    00
  • mysql升级后报Table ‘mysql.servers’ doesn’t exist

    解决Table ‘mysql.servers’ doesn’t exist 今天遇到一事,就是我在升级mysql数据库后进入数据建立一远程用户,结果报错了。 mysql> flush privileges; ERROR 1146 (42S02): Table ‘mysql.servers’ doesn’t exist   这是由于升级完数据库后丢失原有…

    MySQL 2023年4月12日
    00
  • MySQL怎么过滤重复数据

    本篇内容主要讲解“MySQL怎么过滤重复数据”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“MySQL怎么过滤重复数据”吧! 方法1:加关键字 DISTINCT 在mysql中,可以利用“SELECT”语句和“DISTINCT”关键字来进行去重查询,过滤掉重复的数据,语法“SELECT DISTINCT 字段名 F…

    MySQL 2023年4月16日
    00
  • 详解MySQL的5种整数类型

    MySQL支持多种整数类型,每种类型的范围大小和存储空间不同。 下面是MySQL的整数类型及其说明: TINYINT类型 TINYINT类型从-128到127的有符号范围或0到255的无符号范围。大小为1字节。 使用实例: CREATE TABLE test_tinyint ( id INT PRIMARY KEY, t TINYINT SIGNED, u …

    MySQL 2023年3月9日
    00
  • MySQL DeadLock故障排查全过程记录

    MySQL DeadLock故障排查全过程记录 背景 在MySQL数据库系统中,当两个或多个事务互相持有对方需要的资源,却无法释放自己持有的资源时,便会导致死锁(DeadLock)问题。这种情况下,数据库系统会自动选择一个事务进行回滚,以保证系统不会永久阻塞。但是,死锁问题的出现依旧会影响系统的性能甚至可用性。 为了解决这类问题,我们需要进行DeadLock…

    MySQL 2023年5月18日
    00
合作推广
合作推广
分享本页
返回顶部