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 1864 主从错误解决方法

    Mysql 1864 主从错误解决方法 背景 在使用 MySQL 主从复制的过程中,可能会出现 1864 错误。具体错误信息如下: 1864: Transaction was rolled back or aborted due to out of log space when connecting to the upper level dispatcher…

    MySQL 2023年5月18日
    00
  • MySQL转义字符的使用方法

    MySQL转义字符是一些特殊字符,用于告诉MySQL将其视为普通字符,而不是语句的一部分。常见的转义字符包括反斜杠“\”、“单引号” ‘ ’、“双引号” " "、“换行符” \n、“制表符” \t等。以下是MySQL转义字符的使用方法及实例说明。 使用反斜杠转义特殊字符 反斜杠是MySQL中最常用的转义字符。它可以转义各种特殊字符,如单引…

    MySQL 2023年3月9日
    00
  • MySQL 5.6 中TIMESTAMP with implicit DEFAULT value is deprecated错误

    当使用MySQL 5.6版本时,在没有指定默认值的情况下,使用TIMESTAMP类型的列,可能会出现“TIMESTAMP with implicit DEFAULT value is deprecated”错误。这是因为MySQL 5.6版本之后,TIMESTAMP类型的列默认值已被设置为CURRENT_TIMESTAMP,需要显式指定默认值才能避免错误的发…

    MySQL 2023年5月18日
    00
  • mysql插入记录INSERT与多表更新

    1、第一种:INSERT [INTO] tbl_name[ (col_name, … ) ]  {VALUES | VALUE}({expr |default}, … ), (…), … 如果为自动编号的字段赋值的话,可以采用NULL或者DEFAULT让其采用默认的递增的形式来实现。 INSERT users VALUES(DEFAULT, …

    MySQL 2023年4月16日
    00
  • Navicat MySql 连不上 本地开发环境 MySQL8.0

          原因:   新版mysql数据库的加密方式改变,进而导致Navicat连接输入的密码不能与安装时输入的密码匹配,那如何解决这个问题呢?很简单,只需要一句代码的事儿~ 1、打开MySQL 8.0 Command Line Client           2、输入密码3、更改密码         ALTER USER root@localhost …

    MySQL 2023年4月13日
    00
  • 基于mysql多实例安装的深入解析

    基于mysql多实例安装的深入解析 为什么需要多实例? 在一台服务器上启动多个mysql实例的主要原因是:需要在同一台服务器上运行多个不同版本的mysql,并希望它们可以同时运行。另一个原因是,我们可能需要启动不同的mysql实例,以在不同的端口号上监听TCP连接,从而应对不同的应用场景。 安装前的准备 在开始多实例安装之前,我们需要进行以下准备工作: 1.…

    MySQL 2023年5月18日
    00
  • 通过MySQL慢查询优化MySQL性能的方法讲解

    通过MySQL慢查询优化MySQL性能的方法讲解 MySQL慢查询就是指在查询数据时,查询所花费的时间相对比较长,即查询速度慢,影响MySQL性能,因此需要对其进行优化。本文将介绍一些通过MySQL慢查询优化MySQL性能的方法。 了解MySQL慢查询 在进行MySQL慢查询优化之前,我们需要了解MySQL慢查询发生的原因以及如何定位和分析慢查询语句。 原因…

    MySQL 2023年5月19日
    00
  • 关于mysql 8.0.13zip包安装方法

    下面是关于mysql 8.0.13zip包安装方法的完整攻略,分为以下几个步骤: 1. 下载mysql 8.0.13 zip包 首先需要下载mysql 8.0.13 zip包,可以从官网下载:https://dev.mysql.com/downloads/mysql/5.7.html#downloads,也可以从其他网站下载。下载完成后需要解压缩,将解压后的…

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