【性能优化】MySQL百万数据深度分页优化思路分析

这篇具有很好参考价值的文章主要介绍了【性能优化】MySQL百万数据深度分页优化思路分析。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

业务场景

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

一、数据准备(生成百万数据)

sql:将your_table_name 改成自己的表名,目前我的表中有id,name,password、create_time四个字段(这个是生成一百万数据的,会有点影响性能,插入比较耗时)

INSERT INTO `your_table_name ` (name, password, create_time, age)
SELECT 
    SUBSTRING(MD5(RAND()), 1, 10),
    SUBSTRING(MD5(RAND()), 1, 10),
    NOW() - INTERVAL FLOOR(RAND() * 31536000) SECOND,
    FLOOR(RAND() * 100) + 1
FROM
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t3,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t4,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t5,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t6,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t7,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t8,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t9,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t10;

可以选择每次插入10万条数据,多次插入效果比一次插入效果更好。

【性能优化】MySQL百万数据深度分页优化思路分析,性能优化,# MySQL,关系型数据库,性能优化,mysql,数据库

建表SQL:

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(50) DEFAULT NULL COMMENT '名字',
  `password` varchar(50) DEFAULT NULL COMMENT '密码',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

二、场景复现

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

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(50) DEFAULT NULL COMMENT '名字',
  `password` varchar(50) DEFAULT NULL COMMENT '密码',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询前10条基本上不消耗什么时间

SELECT SQL_NO_CACHE * 
FROM `user`
WHERE create_time BETWEEN '2023-01-01' AND '2023-05-23'
LIMIT 1,10;

【性能优化】MySQL百万数据深度分页优化思路分析,性能优化,# MySQL,关系型数据库,性能优化,mysql,数据库

从第100w+开始取数据的时候,查询耗时1.5秒。

SELECT SQL_NO_CACHE * 
FROM `user`
WHERE create_time BETWEEN '2023-01-01' AND '2023-05-23'
LIMIT 1000000,10;

【性能优化】MySQL百万数据深度分页优化思路分析,性能优化,# MySQL,关系型数据库,性能优化,mysql,数据库

SQL_NO_CACHE
这个关键词是为了不让SQL查询走缓存。

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

三、问题分析

1、回表

我们一般对于查询频率比较高的字段会建立索引。索引会提高我们的查询效率。我们上面的语句使用了SELECT * FROM user,但是我们并不是所有的字段都建立了索引。当从索引文件中查询到符合条件的数据后,还需要从数据文件中查询到没有建立索引的字段。那么这个过程称之为回表

2、覆盖索引

如果查询的字段正好创建了索引了,比如 SELECT create_time FROM user,我们查询的字段是我们创建的索引,那么这个时候就不需要再去数据文件里面查询,也就不需要回表。这种情况我们称之为覆盖索引

3、IO

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

4、问题衍生

当我们查询 LIMIT 2000,10 会不会扫描1-2000行,你之前有没有跟我一样,觉得数据是直接从2000行开始取的,前面的根本没扫描或者不回表。其实这样的写法,一个完整的流程是查询数据,如果不能覆盖索引,那么也是要回表查询数据的。

所以越到后面大概率是会查询越慢!

四、问题总结

我们现在知道了LIMIT 遇到后面查询的性能越差,性能差的原因是因为要回表,既然已经找到了问题那么我们只需要减少回表的次数就可以提升查询性能了。

五、解决方案

既然覆盖索引可以防止数据回表,那么我们可以先查出来主键id(主键索引),然后将查出来的数据作为临时表然后 JOIN 原表就可以了,这样只需要对查询出来的5条结果进行数据回表,大幅减少了IO操作。

六、优化前后性能对比

我们看下执行效果:

  • 优化前:1.5s 

SELECT SQL_NO_CACHE * 
FROM `user`
WHERE create_time BETWEEN '2003-01-01' AND '2003-05-23'
LIMIT 1000000,10;

【性能优化】MySQL百万数据深度分页优化思路分析,性能优化,# MySQL,关系型数据库,性能优化,mysql,数据库 

  • 优化后:0.6s 

SELECT SQL_NO_CACHE * 
FROM `user`
WHERE create_time BETWEEN '2003-01-01' AND '2023-05-23'
LIMIT 1000000,10;

SELECT SQL_NO_CACHE *
FROM (SELECT SQL_NO_CACHE id 
FROM `user`
WHERE create_time BETWEEN '2003-01-01' AND '2023-05-23'
LIMIT 1000000,10) AS temp
INNER JOIN `user` AS u ON u.id = temp.id; 

【性能优化】MySQL百万数据深度分页优化思路分析,性能优化,# MySQL,关系型数据库,性能优化,mysql,数据库

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

总结:

        其实实际业务场景数据达到百万了都会选择三方工具了,比如:ES,本文只是拿分页数据做例子,探讨一下SQL的查询效率。文章来源地址https://www.toymoban.com/news/detail-618970.html

到了这里,关于【性能优化】MySQL百万数据深度分页优化思路分析的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 掌握MySQL分库分表(一)数据库性能优化思路、分库分表优缺点

    不能⼀上来就说分库分表! 根据实际情况分析,两个角度思考:不分库分表、分库分表 软优化 数据库参数调优 分析慢查询SQL语句,分析执行计划,进行sql改写和程序改写 优化数据库索引结构 优化数据表结构优化 引入NOSQL和程序架构调整 硬优化 提升系统硬件(更快的IO、更

    2023年04月19日
    浏览(57)
  • 百万数据慢慢读?Pandas性能优化法速读百万级数据无压力

    作为数据分析工作者,我们每天都要处理大量数据,这时Pandas等工具的读取性能也就备受关注。特别是当数据集达到百万行以上时,如何提高读取效率,让数据分析工作跑上“快车道”?本文将详细分析Pandas读取大数据的性能优化方法,以及一些建议和经验。 1. 使用SQL进行预处理 可

    2024年02月09日
    浏览(48)
  • MySQL深度分页优化问题

    ☆* o(≧▽≦)o *☆嗨~我是小奥🍹 📄📄📄个人博客:小奥的博客 📄📄📄CSDN:个人CSDN 📙📙📙Github:传送门 📅📅📅面经分享(牛客主页):传送门 🍹文章作者技术和水平有限,如果文中出现错误,希望大家多多指正! 📜 如果觉得内容还不错,欢迎点赞收藏关注哟!

    2024年01月19日
    浏览(43)
  • MySQL 性能优化思路和工具

    作为架构师或者开发人员,说到数据库性能优化,你的思路是什么样的? 或者具体一点,如果在面试的时候遇到这个问题:你会从哪些维度来优化数据库,你会怎么回答? 我们说到性能调优,大部分时候想要实现的目标是让我们的查询更快。一个查询的动作又是由很多个环

    2023年04月09日
    浏览(33)
  • Mysql深度分页优化的一个实践

    问题简述: 最近在工作中遇到了大数据量的查询场景, 日产100w左右明细, 会查询近90天内的数据, 总数据量约1亿, 业务要求支持分页查询与导出. 无论是分页或导出都涉及到深度分页查询, mysql通过limit/offset实现的深度分页查询会存在全表扫描的问题, 比如offset=1000w, limit=10, 那么

    2024年01月19日
    浏览(31)
  • 面试官:Mysql千万级大表如何进行深度分页优化?

    假如有一张千万级的订单表,这张表没有采用分区分表,也没有使用ES等技术,分页查询进行到一定深度分页之后(比如1000万行后)查询比较缓慢,我们该如何进行优化? 订单表结构如下: 其中 Mysql 版本为8.0。我们使用Python脚本向表中插入2000万条数据。 导出数据时我们需

    2024年02月19日
    浏览(44)
  • MySQL性能深度优化

    这里的深度优化是指,除了建索引、左匹配索引等等其他的优化手段。 文章涉及到操作系统连接数、IO、Mysql本身的某些参数设置,值得记录下来。 CPU:48C 内存:128G DISK:3.2TSSD innodb_thread_concurrency=32 表示SQL经过解析后,允许同时有32个线程去innodb引擎取数据,如果超过32个,

    2024年02月09日
    浏览(54)
  • 千万级数据深分页查询SQL性能优化实践

    如何在Mysql中实现上亿数据的遍历查询?先来介绍一下系统主角:关注系统,主要是维护京东用户和业务对象之前的关注关系;并对外提供各种关系查询,比如查询用户的关注商品或店铺列表,查询用户是否关注了某个商品或店铺等。但是最近接到了一个新需求,要求提供查

    2024年02月11日
    浏览(56)
  • 【QT性能优化】QT性能优化之QT6框架高性能模型视图代理框架千万级数据表分页查询优化

    QT性能优化之QT6框架高性能模型视图代理框架千万级数据表分页查询优化 简介 本文介绍了QT模型视图代理框架中的QT表格控件和QT数据库模块中的QT数据库查询模型结合使用的一个应用实践案例:QT高性能表格控件分页展示千万行数据。本文介绍了这个应用实践案例的运行效果

    2024年02月14日
    浏览(51)
  • 达梦8数据库性能优化思路浅谈

        作为一名DBA,SQL优化是我们工作中必不可少的技能,在投产的系统中,存在大量的SQL语句需要我们分析和快速做出处理。很是考验我们的经验。这些慢SQL的原因有很多,有SQL编码不规范,设计有缺陷,SQL场景未考虑全面,数据量未评估等,预先需要建立索引的表而未设计建

    2024年02月06日
    浏览(43)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包