开发者社区> 小目标青年> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

Mysql 我随手造200W条数据,给你们讲讲分页优化。

简介: Mysql 我随手造200W条数据,给你们讲讲分页优化。
+关注继续查看

前言



【问诊一】


image.png


MySql 索引失效、回表解析 【陈先生们,先看这篇】


【问诊二】


image.png


正文



话不多说,先当场整点货,搞个200w条数据:


准备一张表:


image.png 


建个函数来造数据:


image.png


估计很多初学者可能很少写这些,没关系,可以一起动手试着玩玩:


CREATE DEFINER=`mytoor`@`%` FUNCTION `JcTestData`() RETURNS int(11)

BEGIN                                

   DECLARE num INT DEFAULT 2000000;        

   DECLARE i INT DEFAULT 0;

   

   WHILE i < num DO            

   INSERT INTO test_order(`platform_sn`,`third_sn`,`type`,`create_time`)

        VALUES(CONCAT('SN',i),UUID(),1,now());

       SET i =  i + 1;

   END WHILE;        

   RETURN i;

END


简单说一嘴,看明白的可以无视这个(还是那句话学习,是先知后知而已): image.png


 点击运行:


image.png


29秒造完,还可以。


 image.png


货到齐了,准备开搞:


image.pngimage.png


开始演练:


先给type加个索引,模拟一下真实查询场景: 


image.pngimage.png 


再把几条数据的type数据稍微改一下,:


image.png


先按照正常的场景分页查询 limit 0,50:


sql:


select * FROM test_order  where type=1  limit 0,50;

可以看到查询 limit 0,50  速度是很快的,0.022秒: 


image.png


我们接下来模拟成查询N页之后的数据 limit  1200000,50:


120W 条后,偏移 50条数据,roll出来


sql:


select * FROM test_order  where type=1  limit 1200000,50;


看看效果,用了3.765秒:


image.png


explain:


image.png

可以看到已经命中了索引 index_type ,但是还是比较慢,为什么呢?


慢的原因 ①


① 索引 index_type 是非聚簇索引, 而我们查询的语句 是select * ,包含了其他字段。


通过非聚簇索引 index_type  roll出来的数据列,只有type 和 id ,那么为了拿其他字段,


就会通过先取聚簇索引 id ,再根据id 拿出所有列值,这也就是回表操作。


慢的原因 ②


limit 第一个数,第二个数


limit 的roll数据规则是什么 ?


是确定sql的符合条件的数据,

根据 limit的 第一个数 + 第二个数,先计算出总共需要扫描拿到的数据总量是多少,

再去从头开始遍历到第一个数的数据行,开始丢到返回集,

丢多少条呢? 第二个数决定着丢多少条。


limit 示例讲解


也就是limit 0,50 :


计算 0+ 50  =50 , 拿出符合条件的50条 , 从头开始匹对第一个数  0,OK,从0开始就可以把数据丢到返回集。


丢多少?  第二个数是 50,所以会一条条丢,丢50条 ,最后返回数据。


那么如果是我们文章里面执行的  limit 1200000,50 :


120w+50  ....


意味着为了拿50条数据,需要扫描出 1200050 条数据,然后开始迅速得检索第一个数是120W,开始丢掉前面120W条没有意义的数据,然后确定第二个数是50,开始整50条数据丢到返回集里面,最后返回数据。


那么既然知道了这个情况, 我们可以开始玩优化操作。



方案1 :


针对回表方面做优化


如果我们能拿到我们知道返回数据的 id 集,作为条件,这样通过命中非聚簇索引type的时候,直接就能拿到id,这样通过id拿数据列,这样就方便了。


sql:


select * FROM test_order  where id in


(



select id from (select id FROM test_order  where type=1 limit 1200000,50) child



)


看下效果:


image.png


3.765秒 变成了 1.56秒 !!!

3.765秒 变成了 1.56秒 !!!

3.765秒 变成了 1.56秒 !!!


为啥? 


explain看看:


image.png


这种情形已经是快了很多了, 但是还有没有操作空间?


回答 : 有。


可以看到当前的优化sql ,其实 还是涉及到了 limit 1200000,50 ,这也就是主要耗费时间的地方。


方案2:使用最小条件值


分页查询避免跳页查询, 我们把上一页的id,作为下一页的起始条件。


上面分析这个sql的规则 :


image.png


看一下这个limit 120W,50的数据情况:


image.png


如果我们加上 条件  id >=1200008  , 是不是就非常nice了 。


sql:


select * FROM test_order  where type=1 and id >=1200008 limit 50;


看看效果,0.022秒,就跟直接 limit 0,50 一样了效率了:


image.png


explain:


都中了主键索引了,key:PRINARY


image.png服务端使用方案2:


这种情况,如果是我们代码里面去使用,只需要在操作大批量数据的时候,将上一批数据的最后一条数据的id拿出来。


给到下一批筛选数据,作为最小ID的 条件即可。


其实也就是之前我写过一篇文章留下来的分页优化操作空间:


Springboot 手动分页查询,分批批量插入数据_小目标青年的博客-CSDN博客


可以在这篇基础上,加上 上一批次的最小ID作为条件,效率翻倍!


配合前端使用方案2:


给大家看一个现成的大系统的一个触发机制调用图:image.png


随着滑动,自动加载下一页数据, 起始也就是把上一页的最小ID值(可以传其他条件值也可以)透传到下一页。这种其实我们产品体验上的滑动加载,瀑布流形式。

好了,该篇就到这,关注我,点赞,收藏(给我知道你们在,给我知道你们懂我)。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
mac系统下安装mysql 和phpmyadmin
用惯了在Windows上一个appserv或phpStudy压缩包搞定,要在MAC OSX 10.10上捣腾一个PHP开发环境还不大习惯。但自己亲自配一下环境还是有所收获的。众所周知OSX上自带了apache和php,所以这两个搞起来还比较顺利。
1088 0
Ubuntu下安装mysql与mysql workbench
其他相关链接 Ubuntu 安装jdk:[链接] Ubuntu安装eclipse:[链接] Ubuntu下安装mysql与mysql workbench:[链接] Ubuntu配置tomcat9:[链接] 1.sudo apt-get install mysql-server 安装完成,下面测试一下,输入: mysql -u
1812 0
手把手教你在CentOS 6.8中安装mysql
@(Linux)[centos,mysql,安装] 卸载掉原有mysql 查看该操作系统上是否已经安装了mysql数据库:rpm -qa | grep mysql   若有则卸载:rpm -e mysql  // 普通删除模式 或者:rpm -e --...
1568 0
MySQL在Windows上安装多个实例的方法
MySQL在Windows上安装多个实例的方法 Step 1: 解压MySQL安装包,配置my.ini文件,注意port不能设置为默认的3306,这里我们设置为3307端口: 点击(此处...
852 0
Ubuntu16.04 通过源码编译安装 MySQL 5.7.17
参考: ubuntu16.04 编译安装mysql5.7.x,以及配置使用 http://www.cnblogs.com/xz1024/p/5802637.html MySQL root密码重置报错:mysqladmin: connect to server at ‘localhost’ failed的解决方案! http://blog.
1618 0
在ubuntu Mysql 5.7 安装InnoDB Memcached 插件
在ubuntu Mysql 5.7 安装Memcached 插件 参考:https://dev.mysql.com/doc/refman/5.7/en/innodb-memcached-setup.
1087 0
腾讯云服务器安装mysql
ubuntu16.04上安装mysql非常简单只需要几条命令就可以完成。 先更新源sudo apt-get update sudo apt-get install mysql-server 安装过程中遇到Y/N 时都选Y  在安装过程中需要你输入MyS...
1466 0
+关注
小目标青年
分享不仅为了他人,也为了自己 。--JCccc
326
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载