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>kill thread_id; kill掉第一个锁表的进程, 依然没有改善. 既然不改善, 咱们就想办法将所有锁表的进程kill掉吧, 简单的脚本如下. #!/bin/bashmysql -u root -e “show processlist” | grep -i “Locked” >> locked_log.txt for …

    MySQL 2023年4月12日
    00
  • 关于mysql主备切换canal出现的问题解决

    关于”关于mysql主备切换canal出现的问题解决”的攻略,我们可以分成以下几个步骤来进行解释。 1. 背景介绍 首先,我们需要了解一下什么是mysql主备切换以及canal,以及它们在系统中的作用和重要性。mysql主备切换是指当前业务时刻只有一个数据库实例在工作,而其他的数据库实例则在备份模式下工作。当主实例出现故障时,备份实例会接管服务。canal是…

    MySQL 2023年5月18日
    00
  • mysql修改sql_mode报错的解决

    下面是关于“mysql修改sql_mode报错的解决”的完整攻略。 问题背景 在MySQL数据库中,我们可以使用set命令来修改sql_mode的值,如下所示: set global sql_mode=’blahblah’; 但是,在修改sql_mode时,可能会遇到如下错误提示: ERROR 1231 (42000): Variable ‘sql_mode…

    MySQL 2023年5月18日
    00
  • SQL Server 出现Error: 1326错误(管理器无法连接远程数据库)问题解决方案

    以下是详细讲解“SQL Server 出现Error: 1326错误(管理器无法连接远程数据库)问题解决方案”的完整攻略: 问题描述 在使用SQL Server Management Studio连接SQL Server数据库时,可能会出现Error: 1326错误的情况,无法连接远程数据库。 问题原因 这种错误通常是由于以下原因之一造成的: DNS解析问题…

    MySQL 2023年5月18日
    00
  • MySQL错误ERROR 1615 解决方法

    当我们使用MySQL进行开发时,有时可能会遇到ERROR 1615的错误。这种错误提示通常是由于我们试图更改具有自动递增值的主键列。以下是解决此类问题的方法: 问题产生的根本原因 该错误通常在我们尝试向具有自动递增值的主键列添加行时发生。可以通过更改表中主键列的值来解决此问题。但是,我们不希望出现这种情况,因为主键应该始终是唯一的。因此,我们需要想出其他方法…

    MySQL 2023年5月18日
    00
  • Mysql巧用join优化sql的方法详解

    Mysql巧用join优化sql的方法详解 什么是join 在Mysql中,join指的是基于两个或多个表之间的共同字段进行查询的方法。通过join,可以将不同表中的数据和信息结合在查询结果中,达到获取更全面的信息的目的。 如何巧用join优化sql语句 使用内联接代替子查询 在一些需要复杂查询的场景下,为了获取准确的查询结果,可能会使用子查询。然而,子查询…

    MySQL 2023年5月19日
    00
  • 一文教你学会定位线上MySQL锁超时问题

    以下是“一文教你学会定位线上MySQL锁超时问题”的完整攻略。 问题背景 在MySQL中,为了保证并发性,当某个事务要修改数据时,会自动为要修改的行加上锁,防止其他事务同时访问,这种锁就叫做行锁。而当一些事务互相等待对方释放锁时,就会产生死锁,这时MySQL会自动检测到死锁,并选择其中一个事务进行回滚以解锁。 然而,在极端情况下,如果某个事务一直无法获得所需…

    MySQL 2023年5月18日
    00
  • 解决mysql创建数据库后出现:Access denied for user ‘root’@’%’ to database ‘xxx’的问题

    当在mysql中创建新的数据库时,有可能会遇到以下错误提示:Access denied for user ‘root’@’%’ to database ‘xxx’,意思是这个用户没有权限操作这个数据库。 要解决这个问题,可以尝试以下几个步骤: 首先尝试重置root用户的密码: mysql> SET PASSWORD FOR ‘root’@’%’=PAS…

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